9日目 11日目

10日目

目標

• エクセルのデータベース機能を理解する (2:抽出)
• エクセルのデータベース機能を理解する (3:条件付き計算)
• セルに対する入力規則を設定する
• ピボットテーブルを理解する

課題

• 課題21抽出
• 課題22条件付き計算と入力規則
• 課題23ピボットテーブル

課題 21

抽出(フィルタ)

以下の表から次の操作を行え。

1. 施設名順に並び替えよ
2. 所在地が福井市内のものを抽出せよ
3. 入場料が100円以下か勝山市に存在する施設を抽出せよ。(入場料が"-"の施設は含めない)

抽出

エクセルでデータの抽出を行うには「フィルタ」機能を利用する。 エクセルで利用可能なフィルタ機能としては「オートフィルタ」と 「フィルタオプション」の2つがある。

• オートフィルタ

  「データ(D)」→「フィルタ(F)」→「オートフィルタ(F)」を実行すると 各列の見出しに▼ボタンが表示され、オートフィルタが設定される。

  

  見出しの▼ボタンを押すことによりフィルタ条件を選択できる。 デフォルト以外のフィルタを設定するために、次の２つが用意されている。

  • (オプション)を選択し、条件を入力
  • (トップテン)を選択し、上位(下位)の指定件数を入力
  

  フィルタが選択されている列は▼の色が異なる。 上の図では「入場料」に対してフィルタが選択されており、「電話番号」にはフィルタが選択されていない。 フィルタを解除するには設定されている列で「(すべて)」を選択する。

• フィルタオプション

  オートフィルタよりややこしいので、オートフィルタで対応できないような条件を指定する際に利用すればよい。 (たとえば、同一列で3つ以上の条件を指定したい場合や、複数列の or など。)

  1. 事前準備

     条件を設定するための領域として見出し語と同じ内容を入力したセルを準備する。

     条件を列記する。1行に記述された複数条件は and が取られ、 複数行に渡って条件が記述された場合は各行の条件の or が取られる。

     たとえば、「入場料が100円以下もしくは勝山市にある施設」を抽出するには、

     • 「入場料」が100以下
     • 「入場料」が「無料」
     • 「市町村」が「勝山市」

     以上の 3つの条件の "OR" を取ることが必要である。そのため、条件欄は 次の図のように指定することとなる。

     
  2. 実行

     条件が設定できれば、「データ」→「フィルタ」→「フィルタオプションの設定」を実行する。

     
  3. 結果

     「選択範囲内」を選んだ場合は、条件に一致しないものが隠される。 「指定範囲」を選んだ場合には抽出範囲で指定した場所を左上としてコピーされる。(他のシート等も可能)

     「選択範囲内」を選んだ場合の実行結果は次の通りである。

     なお、フィルタを解除するには、「データ」→「フィルタ」→「すべて表示」を実行する。

     

小ネタ

ハイパーリンク

このページからコピーした表にはハイパーリンクが設定されているはずである。 このようなハイパーリンクを独自で設定したい場合は、「挿入(I)」→「ハイパーリンク(I)」を実行する。 削除するためには、設定されたハイパーリンク上で右クリックを行い、「ハイパーリンク(H)」→「ハイパーリンクの削除(R)」とたどる。

課題 22

条件付き計算と入力規則

次の表は、本店を福井に、金沢、東京、大阪、名古屋に支店を置く A 社の 1月から 3月までの経費一覧である。

4月以降も、引き続きこの表を利用していくことを考えて、次のように改良せよ。

1. 「科目」や「請求支店名」に適切な入力規則を設定する
2. 「科目」や「請求支店名」ごとの合計を求めることができるようにする (ただしピボットテーブル機能は利用せず、関数DSUMを利用すること)

入力規則

あらかじめ、入力される内容が決まっている場合、 誤ったデータ入力を防いだりする目的で、セルに対する入力規則を 設定することができる。

入力規則を設定する領域を選択したあと、 「データ(D)」→「入力規則」を選択すると、次のようなウィンドウが表示される。

「入力値の種類(A)」としては、右のようなものを選択することができる。 もともとは、「すべての値」が選択されており、 どのような内容でも入力することが可能である。 「リスト」とは、配列に含まれるもののみの入力を許す形式である。 具体的には、ある領域にあらかじめ入力を許可する内容を列記しておき、 「元の値(S)」(リストを選択したときに現れる)に、その領域を指定する。

「エラーメッセージ」のタブを選ぶと、誤った入力が行われたときの振る舞いを 決めることができる。右の例では、入力エラーという名称のポップアップを作成し、 登録されていない科目名であると表示する。誤った入力を禁止するためには スタイルを「停止」に設定すること。

「入力値の種類」で、リストを選択した場合、そのセルがアクティブになると、 右横に▼が表示され、クリックすると右のように入力できるもののリストが 表示される。

なお、この入力規則は、セルにデータが直接入力された場合だけに有効であり、 すでに入力されているものや、数式の結果や貼りつけられたデータ、 フィルコマンドで入力された場合には無効である。

事後チェックのためには、「ツール」→「ワークシート分析」から 「ワークシート分析ツールバー」を表示させたあと、「無効データのマーク」を 実行する。

条件付き計算

特定の条件を満たすセルに対してのみ計算を行うには、分類「データベース」に 含まれる先頭が D で始まる関数を利用する。これらの関数は、先頭の D を除いた 関数名に対応しており、ある条件を満たすセルについての結果を返すものである。

例として、関数 DSUM を取り上げる。DSUM の引数は「Database」、「フィールド」、 「Criteria」の三つである。 「Database」欄に、計算を行いたい表全体を、「フィールド」欄に、計算を行いたい列を、 「Criteria」欄には、条件が設定されている領域を指定する。条件の設定方法は 課題21で行った、フィルタオプションでの条件設定と同じである。

課題 23

ピボットテーブル

課題22の経費一覧のデータを、ピボットテーブルを用いて分析を行え。

例

ピボットテーブル

クロス集計のような「複雑な」集計作業を行ったり、内容の分析を行う際にはエクセルの「ピボットテーブル(pivot table)」機能が利用できる。

1. 「データ」→「ピボットテーブルとピボットグラフレポート」を起動

2. データのある場所と種類(ピボットテーブル)を指定する。

   

3. データ範囲の指定(確認)

   

4. 作成場所の指定

   

5. 空の「ピボットテーブル」が挿入される

   

6. 「ピボットテーブルツールバー」から集計したい項目を目的のフィールドにドラッグする。 たとえば、列に「科目」を、行に「請求支店名」を、データに金額をドラッグしたのが下の図。

   
7. 書式の変更は「項目」をドラッグすることにより変更できる。不要な場合は「ピボットテーブルツールバー」へドラッグする。 ドラッグ中の状態はマウスアイコンの形で判別できる。
    
   

   	データフィールド		行フィールド		列フィールド
   	ページフィールド		外す場合

8. 列(行)に複数の項目をドラッグする事も可能。
9. この例で「ページ」フィールドに「月」をドラッグすることにより、月別の集計を出すことができる。

やってみること

1. ウィザードを利用したレイアウトの変更(例:科目別集計を月別集計に変更してみる)
   なお、ウィザードは右クリックメニューなどからも起動できる。
   
2. ドラッグによるレイアウトの変更
3. 行列の入れ替え
4. 数値の表示形式の変更(例:3桁ごとの区切り記号を入れてみる).
   右クリック後、「フィールドの設定」を選択する。
    
5. データ集計方法の変更(例:合計を全体に占める割合に変更してみる)
   
   
6. フィールド名の変更(例:「合計:金額」を「請求金額」としてみる)
7. 支店ごとの金額の合計の作成
   
8. (行)フィールドのアイテムの絞り込み(例:表示する店を福井と金沢の2店だけに変更してみる)
   
9. (行)フィールドのグループ化(東京、大阪、名古屋を3大都市圏、福井、金沢を北陸地区としてまとめてみる)
    
10. 各地区ごとの総計の表示(フィールドの設定で集計を指定する)
11. データの詳細表示の設定および解除
12. 月別の集計表の作成(ページフィールドの利用)
13. データの更新(例:元の表で A支店の研修費の額を変更してみる)