2012年11月01日

はじめに-中級

中級はExcelが用意しているツールの使い方の練習です。

表の検索で基本となる
LOOKUP関数から、条件付書式/入力規則/データベース/ピボットテーブル/マクロ機能などを学習。

ここでも全て
問題形式なのでスキルを確認しながら能力を高めていきます。


補足1:それぞれの問題はエクセルのシート形式になっています。ほとんどは、複数のシートで構成されていますので、シートの移動は、画面下側のシート見出しをクリックして下さい。

補足2:
回答の正否判定やシートの移動などにマクロを使用(内容が赤文字の問題)している場合がありますので、ダウンロード後にブックを開く場合は「マクロを有効」にして下さい。


知りたい機能別/項目別の一覧もあります。ご利用下さい。


LOOKUP関数

内容 概要 見本/DL
VLOOKUP関数1 VLOOKUP関数はある任意の一意のコードを検索して、それに関連付けられたデータを参照したいという場合にはを使用すると便利な関数
VLOOKUPは表検索でもっとも基本的な関数
 ・従業員名簿で所属Noから所属名を表示
 ・商品台帳を参照して商品番号を入力すると商品名を表示
 ・申込書でコースCDを入力するとコース名/テキスト代および合計金額を表示
 ・割引額テーブルを参照して顧客別の割引金額を表示
 ・見積書作成-品番と数量から商品名/単価/小計/税抜き小計/消費税/合計
見本と
ダウンロード

VLOOKUP関数例 VLOOKUP関数を使用した実際の請求書例
問1~9に順に回答
していく事で、見積書を作成する
 ・製品IDと数量から製品名/単価/金額/小計が自動で表示される
見本と
ダウンロード

HLOOKUP関数 HLOOKUP(水平参照)関数の使い方
垂直(縦)方向のVLOOKUPに対して水平(横)方向に検索する関数
見本と
ダウンロード

| コメント (0)

条件付書式と表示形式

内容 概要 見本/DL
条件付書式 条件付書式によって、数式の結果や入力データにより、文字フォント罫線セルの色など予め設定された書式を自動的に適用してセルを強調表示する機能。セルのデータが見やすくなり、データの分類表示にも絶大な威力を発揮!
 ・セルの色を売上金額が5000円以上で水色、3000円以下はピンクにする
 ・達成率が100%以上は緑色、未満はピンクにする
 ・目標額と達成額の差が20万以上は緑、0未満は赤、以外は青色
見本と
ダウンロード

スタイル スタイルとは、 セルの書式設定に関する情報に名前を付けて 管理しているもの。同じ書式を何箇所かに設定する場合、「スタイル」として登録しておくと便利。設定方法と実例
 ・スタイルの作成と適用、変更
 ・ユーザー定義書式の作成
見本と
ダウンロード

表示形式 標準表示形式とユーザー設定の表示形式
表示形式でデータを変えることなく自由に表示状態を設定できます。
 ・表示形式の書式記号
 ・数値「1」を「0001」と表示する
 ・小数点桁区切り、負の数は括弧付き赤
 ・表示形式を利用した総合問題
見本と
ダウンロード

表示形式2 ユーザー定義のスタイルを用いた問題。正負0の場合で表示形式を分ける方法、値により表示形式を変える、ユーザー定義の表示形式で進捗管理表を作る
 ・達成状況が正の場合は青字負の場合はマイナスを付けた赤字
 ・0の値を”無料”と表示
 ・20万以上は緑色、0未満は赤色、以外は青色
 ・値により色分け、および「0,000千円」の形式で表示
 ・ユーザー定義の表示形式と条件付き書式の混合
 ・ユーザー定義の表示形式を使って「プロジェクト進捗状況グラフ」を作成
■表示形式で使用される記号の一覧
見本と
ダウンロード

条件付書式(数式) 参照に数式を使用した高度な条件付書式の例と練習問題
数式型の条件で条件付書式の範囲が百倍に拡大!
 ・目標達成率が120%以上は「明るい緑」色を、「80%」を未満は「黄色」
 ・売上額が平均以下のセルの色を「薄い黄色」
 ・「ルームタイプ」別に色分け
 ・条件付き書式(数式)を使って「プロジェクト進捗状況」を作成
見本と
ダウンロード

条件付書式応用 条件付書式を使用した高度なシート作成の例
ヒントから本番までステップで分けた実践型難問
 ・各店舗の月別売上-売上最高/最低の月を色分け
 ・受講管理表-1~4の講座番号が逆行したらセルの色をピンク
見本と
ダウンロード

| コメント (2)

入力規則

内容 概要 見本
入力規則 入力規則とは、セルに入力するデータの制限等を設け、入力をコントロールし、ミスやエラーを減らす事のできる機能
 ・商品台帳のリストから選択してセルに入力出来るようにする
 ・注文伝票の数量未満を入力したら”10冊以上です!”と表示して入力停止
 ・数値入力セルをアクティブにした時「日本語入力」を自動的にオフ
  入力メッセージを表示、不正入力時はエラーメッセージを表示して入力停止
入力日付のリストを自動作成およびチェック
見本と
ダウンロード

入力規則応用1 入力規則を使用した出勤管理表の作成
難問をステップ1~4に分けて判り易く作成していく。
日付計算条件付書式表示形式入力規則を駆使
 ・入力規則設定-日本語入力/入力メッセージ/エラーメッセージ
 ・計算式と各種の表示形式を設定
 ・日付を扱った計算式(合計勤務時間)、給与合計/交通費合計の設定
 ・曜日別色分け-条件付き初期で土曜日:ペールブルー、日曜日:ローズ
見本と
ダウンロード

入力規則応用2 下記の条件で受講管理表を作成。
 ・講座番号は1~10まで、1日4限まで受講可能。受講しなくても良い。
 ・講座番号は重複、後戻りは出来ない。
 ・入力メッセージを表示、日本語入力を自動でオフ
 ・不正入力時はエラーメッセージを表示して入力停止
入力規則の中で数式を設定するなどの高度な技を駆使する難問。
見本と
ダウンロード

| コメント (4)

データベース

内容 概要 見本
オートフィルタ オートフィルタは、リストから特定のデータのセットをすばやく抽出して作業するときに便利な機能。抽出されたリストには、特定の列で指定した検索条件に一致する行だけが表示され、簡単に表のデータ処理が行える
 ・特定項目のみ抽出
 ・多段階抽出-[月日]で 2000/5/1~20005/31 のデータを抽出し、さらに
  [品名]が[デスクトップパソコン]または[ノートパソコン]であるデータを抽出
 ・トップテン機能を使って上位20%のみを抽出
 ・オプション機能を使ってデータを抽出
   ・住所が横浜市もしくは町田市、年齢が45歳以上の人
   ・住所が東京23区内で年齢が45歳~50歳
見本と
ダウンロード

SUBTOTAL オートフィルタで抽出されて、表示されている項目のみ集計
また、集計方法を数値で指定して動的に計算方法を変更可
見本と
ダウンロード

フィルタオプション フィルタ オプションを使用すると、1 つの列に複数の検索条件を適用したり、複数の列に複数の検索条件を適用できる。フィルタオプションでは実現できない複雑なデータ抽出はフィルタオプションで実現
 ・排他抽出-交際費以外
 ・多条件抽出-住宅情報から抽出
   ・最寄駅が、”渋谷、原宿、恵比寿”のいずれかで、”賃料が10万円以下”
   ・「沿線」が丸の内線、駅から「徒歩」10分以内かつ「間取り」が1DK
見本と
ダウンロード

フィルタオプション2 フィルタオプションの練習問題。結構むずかしい!
複雑な抽出条件を設定する
 ・「商品一覧表」から
   「商品名」に”セット”の文字列を含み「売上金額」が150,000以上または
   「商品名」が懐中電灯で「販売形態」が外商
 ・「華道部」の表から
  年齢が40歳以上かつ東京都在住の部員、もしくは
  年齢が40歳未満かつ東京都に在住でない部員
見本と
ダウンロード

データベース関数1 条件付集計関数SUMIF/COUNTIFの使い方、
 ・売上履歴表から、
   パソコン/周辺機器/ソフトの売上を合計
   順位を指定すると、その順位以内の売上金額だけを合計
 ・受講管理名簿から
   東京都23/都下/神奈川県/埼玉県/千葉県の受講者数
   50歳以上の受講者数合計
見本と
ダウンロード

データベース関数2 データベース (またはリスト) に格納されているデータを分析するDSUMなどのデータベース関数に関する説明と使用方法
 ・売上履歴表から
   商品区分と販売員で売上を合計-DSUM
   商品区分と販売員を複数指定して売上を合計-DSUM
   売上日の期間と販売員を複数指定して売上を合計-DSUM
   商品区分と販売員で売上の平均を算出-DAVERAGE
   売上日と販売員を指定して最大売上金額を算出-DMAX
   売上日/製品名/店舗を指定して売上件数を算出-DCOUNT
見本と
ダウンロード

| コメント (5)

分析ツール

内容 概要 見本
ワークシート分析 ワークシート分析の説明と使用例
シートエラーや他人の作ったシートの分析に便利
 ・セルを参照しているセルにトレース矢印を表示
 ・エラーが生じているセルにトレース矢印を表示
 ・セルを直接/間接的に参照している全ての参照元にトレース矢印
 ・入力規則に沿わない無効データにマークする
 ・ワークシート分析機能を使ってエラーの原因を探し修正する
見本と
ダウンロード

ピボットテーブル ピボットテーブルでのクロス集計
クロス集計とは、与えられたデータのうち、2つないし3つ程度の項目に着目してデータの分析や集計を行うこと。
 ・商品売上一覧表から集計表を作成する
   「いつ、誰が、何を、いくら売った」を集計
見本と
ダウンロード

ピボットテーブルレポート ピボットテーブルレポートを作成
集計フィールドを追加し「売上金額-売上原価」を表示する

 
見本と
ダウンロード
ピボットグラフ ピボットグラフでのクロス集計
グラフ上でクロス集計の項目を変更できる便利モノ!
見本と
ダウンロード

| コメント (2)

マクロ機能

内容 概要 見本
マクロ基礎 マクロとは?Excel で同じ作業を繰り返し実行する場合、マクロを使うと作業を自動化することができる。マクロを使って単純な作業を効率化!
 ・セルを塗り潰し、罫線を引くマクロの作成
 ・マクロをオートシェイプに登録-クリックするとマクロが実行される
 ・セル範囲をクリアするマクロの作成、実行
 ・フォントを変更するマクロの作成とオートシェイプへの登録
 ・作成済みのマクロを修正
 ・実用的なデータ消去マクロ
 ・ユーザーツールバーの作成とマクロの登録
見本と
ダウンロード

相対参照マクロ 相対参照形式のマクロの記録方法。
個人専用のツールバーの作成とカスタマイズ、マクロの登録
 ・画面設定と元に復元するマクロの作成
   枠線/行列番号/シート見出し/水平/垂直スクロールバーを表示しない
    背景画像の設定
   元の状態に復元
 ・自動実行マクロ
   シートがアクティブになったら自動でマクロが実行される
   VisualBasicエディタを使う。
見本と
ダウンロード

担当者別集計 マクロを使った担当者別集計表
 ・オートフィルタ機能の並べ替え、集計をマクロに記録-ボタンに登録
 ・並べ替えと集計をリセットするマクロを記録-ボタンに登録
見本と
ダウンロード

マクロ応用 マクロのカスタマイズと汎用化
VBを少し組みいれることで一気にマクロの世界が広がる
 ・オートフィルタ機能の操作をマクロ登録
  日付別集計表と担当者別集計表をマクロで一発変換
 ・フィルタオプション機能を使ってさらに複雑な集計を自動化
 ・抽出したデータを別のワークシートに自動的に保存するマクロ
見本と
ダウンロード

| コメント (0)

シミュレーション

内容 概要 見本
ゴールシーク1 ゴールシークとは数式の計算結果が目的の値となるように、数式に代入する値の最適値を求める方法。すなわち目標値から設定値を逆算!
様々な例題を練習
 ・借入金額/返済期間(月)から毎月の返済額が9万になる利率を計算
 ・「利益見込み」を1500万円にするには「小売価格」をいくらにすればよいか
 ・カタログ製作費の合計を7百万とするためにはページ数を何ページとするか
 ・総旅行費用から宿泊費の上限を算出
 ・損益分岐点における販売数を求める
 ・様々な条件の下で、損益分岐点での販売数を算出
見本と
ダウンロード

ゴールシーク2 給与分析。ゴールシークで給与から手取額を逆算。
複数の解が発生するパターンあり
見本と
ダウンロード

ソルバー1 ソルバーを使えば、複数の条件から最適解を求めることが出来る。ゴールシークの拡張版
 ・鶴亀算-人間/牛/タコ/イカ/ムカデの足の総数からそれぞれ頭数を求める
 ・仕入総数/仕入数合計/仕入金額からそれぞれ仕入数を求める
 ・カタログ製作の各種制約条件から印刷部数/写真点数を求める
 ・ライセンス数で金額が異なるソフトを複数PCで使う場合のそれぞれの最適購入数
見本と
ダウンロード

ソルバー2 ソルバーの実践的使用例
生産・金種支払・人事・物流などへのソルバーの応用
 ・利益を最大にする生産計画
 ・最少支払枚数演算
 ・利益を最大にする販売計画
 ・スーパーの人件費を最少にするシフト計画
 ・コストを最少にする物流経路
見本と
ダウンロード

ソルバー3 魔方陣の作成。3x3、4x4 - シンプレックス法
縦横斜めの合計が全て等しくなるように、自動的に重複しない数字を配置する。注:EXCEL2010以上要
見本と
ダウンロード
ソルバー4 魔方陣の作成。3x3、4x4 -エボリューショナリー法
進化論的手法であるエボリューショナリー機能を用いて魔方陣を作成
注:EXCEL2010以上要
見本と
ダウンロード
     
シナリオ シナリオとは、代入値を組み合わせてワークシート モデルの結果を予想するもの。分析値比較やデモに活用
複数の代入値の組み合わせに名前を付けてワークシートと一緒に保存できる。ワークシートで異なる値のグループをシナリオとして作成および保存して、これらのシナリオを切り替えて、その結果を確認することが可能。
見本と
ダウンロード

データテーブル データ テーブルを使用すると、1 回の操作で複数の数値に変更した場合の計算結果を確認したり、その結果をワークシート上で表示して比較したりすることができる。 見本と
ダウンロード

| コメント (8)

2011年01月01日

中級その他

内容 概要 見本
WEBページ保存 エクセルで作成したシートをウェブページとして保存。見出し(アドレス)は数式でシート内に表示しておく。IFRAMEとしてメインページに埋め込み、jQueryでIFRAMEの高さを自動調整する。 見本

| コメント (0)