3. Excelの主な機能とその応用

  表計算，グラフ作成，データベース機能，およびマクロ機能の４つがExcel の主な機能である。 １章では，表計算とグラフ作成について復習した。ここではデータベース機能について整理しておこう。 青い教科書2)の第3，4章，および白い教科書1)のExcel編の第9，10章（第21, 22章）を併用する。 また，Excel の活用を容易にする代表的な関数機能についても慣れておこう。
  マクロ機能については第５章で学ぶ。教科書1)のExcel編の第8章（20章）にも詳しい説明があるので併用する。

1. データの並び替え (ソート)

データの数が多くなると，その特徴をみるために，ある項目に関して大小順に並べ替えたり，必要なデータを検索することがよく行われる。 このようなデータの活用を可能にする体系的な仕組みをデータベース (database) と呼ぶ。
  Excelのデータベース機能を利用するには，データをレコード(record, 行またはタプル) とフィールド (field, 列名，データ項目または属性) からなる表の形で記録したワークシートを準備する。 ここでは第1章で作成した「自動車97.XLS」を開いて自動車生産の企業データを利用する。レコード数は少ないがデータベースの基本機能を学ぶのが目的である。（ 教科書2)の4.2節）

1. 元のデータを保存しておく為に，元のシートのセル範囲A1〜I14のデータを新しいワークシートに，形式を指定して複写で，"値コピー"しておく。新しいシート名を「データベース」に変更し，セルA2に文字列”データベース”と入力する。
2. 社名フィールドの前に，新しく"列を挿入"し，データ項目を「記号」とする。トヨタと日産の記号をそれぞれ，「car001」と「car002」とする。それ以下の会社の記号は"オートフィル機能"（ 教科書2)の1.1節）で入力できる。

用語メモ：  A列のデータ項目にはデータベースのレコード番号に相当する役割を持たせる。 データベースでは，レコード番号はレコードの記入の順番とは無関係です。

3. ここからが，ソートの操作を始める手順です。 データ項目と全レコードを含めるように，セル範囲A3〜J14（行3〜14）を選択する。データベース機能を利用するにはデータ項目が必要です。
4. 〔データ（D）〕−〔並べ替え（S）...〕− で並べ替えダイアログボックスが開き，
5. 〔最優先されるキー（S）〕のリストボックス〔▽〕から『売上シェア』を選び，ソート順を指定するラジオボタン〔○〕の〔降順〕を選ぶ。 ここで，〔OK〕ボタンをクリックすると，次のように降順に並び替えられた結果が得られる。

• 【ブック名＝自動車97，シート名＝データベース】

それには元のデータの順序を表すようなデータ項目，例えば，いまのデータベースではレコード番号に相当する「記号」を〔最優先されるキー〕にして〔昇順〕にソートすればよい。 このようなデータ項目が無い時には，Excelでは元のデータの並びに戻すことは容易ではない。

複数キーによるデータの並べ替えを行なうには，練習１(e.)で，２番目に優先されるキーも指定する。または，練習１でソートされた結果のレコードの並びを対象に，別の項目に対して同様な操作を繰り返せばよい。

参考：  教科書1)の第21章に，ツールバーのボタンを用いた手軽な並べ替えの方法が紹介されている。

セル（数式や定数が定義された）やセル範囲（項目とデータの一覧表や条件範囲）にその内容を表す名前を付け、これを使ってセルやセル範囲の値を参照することができる。

データベースの一覧表では、通常は一覧表内のどれか１つのセルを選択するだけでデータベースの全レコードがソート等の対象となる。全レコードおよび上位６社までのレコードを対象範囲として名前を付けてみる。
1. 名前を指定するセル範囲を選択する。まず、セル範囲 A3 : J14 を選択する。
2. 名前ボックス（数式バー f _xの左横にある）をクリックする。 名前ボックスに適当な名前を入力する。ここでは「自動車11社」を入力する。    A3   　|▼    f _x
3. また、セル範囲 B3 : J9 を選択し、「自動車上位6社」という名前を付ける。
4. 名前を付けたセル範囲を参照するには、名前ボックスの右横の▼をクリックして開いたリストボックスから、「自動車11社」または「自動車上位6社」を選択すればよい。

練習問題 (2-1)

自動車メーカーの企業データベースを元のデータの並びに戻しなさい。次いで，従業員数にしたがって降順にソートしなさい。

練習問題 (2-2)

元のデータの並びに戻しなさい。次いで，「社名」の昇順を最優先されるキー（主キー）に，「国内販売」の降順を第２キーに指定してソートしなさい。

2. データの検索と抽出

レコード数が画面に納まらないくらい多くなると，検索と抽出というデータベース機能を利用すると便利である。検索条件を指定して，ワークシート上の元のデータのある場所は変えないで該当するデータのみを表示（他はマスクする）したり， 別の指定された場所に必要な項目だけを選択して抽出して書き出す（複写）することもできる。（ 教科書2)の4.2節）

練習５:  経常利益がマイナスの企業をオートフィルタ機能を使って検索し抽出する。

1. データベースの全体のセル範囲を指定する。元のデータの並びに戻しなさい。
2. メニューバーで，〔データ (D)〕−〔フィルタ(F)〕−〔オートフィルタ (F)〕−の順に選択する。すると，
3. 各フィールド（データ項目）のセル内の右側にリスト選択のマーカー（オートフィルタ・ボタン▼）が表示される。経常利益のオートフィルタ・ボタンをクリックする。
   

   

   
   

4. 開いたリストから[（オプション）]を選択する。下の図のようなオートフィルタオプションの画面が開く。
5. 条件を設定する編集欄の窓で， 検索条件「経常利益＜０」を指定する。(注：これはOffice95版のExcelの場合である。図のように、Office97版のExcelでは条件を設定する編集欄の窓が日本語で，「経常利益, ０ , より小さい」等と指定するように変更された。)

   

6. これで次のような抽出の結果が表示される。行番号に注目してほしい。条件に合わない行は非表示になっている。

   

7. オートフィルタで抽出された状態から元の画面に戻すには，(c.)の経常利益のオートフィルタ・ボタンをクリックして，開いたリストから[（すべて）] を選択すればよい。

参考：複合条件を指定してデータを抽出することができる。上の(d.)と(e.)のオートフィルタオプションの画面において，２つの条件の論理演算を（ANDとOR）で指定する。教科書1)の22章の発展課題および教科書2)の4.2.3節を参照

練習６:  経常利益がマイナスの企業について，必要な項目のみを抽出する。

1. 準備として，経常利益が負であるという条件（(d.)の下図のように）を適当なセル，D16とD17，に設定する。
2. 抽出したい項目名，例えば，会社名，営業利益と経常利益，をデータ項目の行からセル範囲B17〜D17に複写しておく。
3. さて，必要な項目のみ抽出を行なうには，ツールバーから，〔データ（D）〕−〔フィルタ（F）〕−〔フィルタオプションの設定（A）...〕を選択する。下図のフィルタオプションの設定の窓が開く。
    

     まず，抽出先のラジオボタンを選択，
   すでに, リスト範囲は指定されている

     抽出範囲をマウスで指定
    

   
    

4. そこで上図のように，リスト，検索条件，抽出範囲の３つを指定する。抽出されるレコード数はあらかじめ分らないが２行分以上を指定すればよい。〔OK〕を選択すれば抽出の結果が17行以下（抽出範囲）に得られる。
    

   
    

   この方法ではデータベースのレコードは非表示にならない。また，抽出範囲をデータベースの記録されたシートとは別のシートに設定することもできる。練習６ではデータベースでは，経常利益がマイナスの企業について会社名と経常利益を12行目から抽出したところ，該当レコードが２社あったことが分かる。

練習問題 (2-3)

経常利益が500億円以下および従業員数が1万人以上という，複数の条件を同時に満たす自動車会社のレコードを，オートフィルタ機能を使って抽出しなさい。

練習問題 (2-4) 

第1章の練習問題(1-3)で求めた「輸出の割合」の結果を自動車会社の企業データベースに追加しなさい。新しいデータベースにおいて，売上高に占める輸出の割合が50%以上の会社の，「社名」と「売上高」と「輸出の割合」の３項目のみのデータを抽出しなさい。検索条件範囲と抽出範囲はデータベースのあるシートに設定すること。

練習問題 (2-5)

教科書2)の４章の例題１から例題３（4.2節）を実際に解いてみなさい。必要なデータは下記の場所からダウンロードして用意すること。

1. 第1表 人口，人口増減，面積及び人口密度のCSVファイルを開き、Excelファイル「国勢調査2000」という名前で保存する。6〜9行目の項目名に適当な罫線を引きなさい。C列（C6〜C72）の右側に罫線を引きなさい。 1〜9行目までの表見出しを水平方向のウィンドウ枠を固定する方法（ 教科書2)の3.1.5節）で固定しなさい。また、A, B列の垂直方向のウィンドウ枠を固定しなさい。

• 平成12年国勢調査の結果または人口推計(最新版) は，総務庁統計局から捜す。http://www.stat.go.jp/（最近の国勢調査は平成7年と平成12年の10月に行われた）  このサイトの2000年第1次基本調査結果の「統計表」の第1表（Excelファイル）から作成した第1表教材ファイル（人口，人口増減，面積及び人口密度）を利用してよい。
• 都道府県別面積(最新版)は，(建設省 => 国土交通省)国土地理院から捜す。http://www.gsi-mc.go.jp/MAP/(地図と国土の情報)
  上の第1表教材ファイルには2000年度の都道府県別面積が含まれている。

練習問題 (2-6)

1. 2000年第2次基本調査結果の「統計表」の第6表（Excelファイル）から作成した第6表　産業(大分類)別，就業者数のCSVファイルをダウンロードし、上の第1表 人口Excelファイルの新しいワークシートに移動しなさい。
2. 第6表 就業者数のワークシートにおいて、都道府県の第1次産業、第2次産業、第3次産業別の就業者数を集計し、３分類産業別の就業者数の割合を計算しなさい。
3. 第1表 人口のワークシートを別の新しいワークシートにコピーする。シート名を「３分類産業別」とする。47都道府県の第1次産業、第2次産業、第3次産業別の就業者数と３分類産業別就業者数の割合のデータを、このワークシートの各都道府県の行に merge（併合）しなさい。 これで、人口、面積から産業別就業者数をフィールドとする大きなデータベースが得られた。
4. 都道府県の第1次産業、第2次産業、第3次産業別の就業者数の割合の国内分布状況を、データマップで表現しなさい。操作法は教科書2)の4.3.3節に、完成イメージ図は総務庁統計局の「抽出速報集計結果」の結果の概要の「５．産業」にある。