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)

元のデータの並びに戻しなさい。企業規模をA，B, C の３クラスに分ける項目を挿入し各社のデータを適当に作成しなさい。次いで，「企業規模」の昇順を最優先されるキー（主キー）に，「国内販売」の降順を第 ２キーに指定してソートしなさい。

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. 「第１表の教材ファイル（d-h12cen1a001.csv）」 を取得し，各自のマイドキュメントに「国勢調査2000」という名前のExcelファイルで保存する。シート名を「第１表」とし， 岩手県と宮崎県の間のデータは次の公開情報から取得して，A列のデータ項目「コード」を含め，一覧表を完成する。
2. 統計センターのwwwサイトにある「平成12年国勢調査 第1次基本集計結果（全国結果）統計表」 の第1表「人口，人口増減，面積及び人口密度」を各自のマイドキュメントに保存する。参照して作業するため，このシートを(i)の「国勢調査2000」に 移動する。参照が終わったらこのシートは削除してよい。
3. 「国勢調査2000」のすべての都道府県および特別市区について，人口増減率（%表示）と人口密度（人/km^2）を小数点以下２桁まで計算 する。 計算に際して注記の１）〜３）は考慮し，４）と５）は考慮しないでよい。
4. データベース機能を利用するために，データ項目名を9行目に移動し，レコード行は10行目（a00 全国）から始まるように調整する。水平方 向の「ウィンドウ枠を固定」する方法（ 教科書(2)の3.1.5節 ）で 1〜9行目までの表見出しを固定しなさい。印刷時に２ページ以内に納め，かつ一覧表を見やすいように「ページ設定」などでレイアウトする。

データベースの操作練習

1. 都道府県別の人口密度の昇順（低い順）に並べ替える。新しいシートを作るのではなく，まず「a00全国」から「a02郡部」の3レコード行を 「c402 福岡市」の後に移動してから操作する。
2. オートフィルタ機能を用いて，人口密度が341人未満で面積が6千平方キロ以上の都道府県を抽出する。
3. 人口が百万人以上，人口密度が340人以上の都道府県および特別市・区部を抽出し，人口増加率の降順に並べ替える。

• 日本の各種統計データを入手したい時は総務省統計局統計センターへ （http://www.stat.go.jp/）  *人口推計、消費者物価指数、労働力調査等のデータ等各種統計データが入手可能。 最近の平成12年国勢調査の結果(2000年10月調査）を探す。 
• 日本の地理・地勢について知りたい時は(建設省 => 国土交通省)国土地理院地図と国土の情報へ （http://www.gsi.go.jp/MAP/）  * 都道府県別面積(最新版)が入手できます。
• 留学生が日本について知りたい時は，Study in Japan ( About Japan ) へ   http://www.studyjapan.go.jp/jp/

練習問題 (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節に、完成イメージ図は総務庁統計局の「抽出速報集計結果」の結果の概要の「５．産業」にある。
• 注：データマップはExcelのXP版では利用できなくなった。