モジュールＭＥ-１７
ＭＥ-１７
データベース機能による
データの抽出と集計
○ データベース機能 ＥＸＣＥＬには簡単なデータベース機能があり、表のデータから条件に合致するレコードを抽出す ることなどができる。本来がデータベースソフトウェアではないので、専用のソフトと比べるとそ の機能は劣るが、単なる表引きや計算ではできないことを可能にし、非常に便利な利用をすること ができる。 １ データベースとしての表 成績データから特定のデータを抽出してみる。 ※ここでは「成績一覧表.xls」のデータを利用してデータベース機能を演習する。 ・「成績一覧表.xls」のファイルを開く。シート「data」をコピーしてから、新規ワークシートを 挿入し、シート「sheet1」に貼り 付ける。 ・表のデータをデータベースとして扱うときには、いくつかの注意点がある。 (1)表は縦の列ごとに同じ種類のデータを扱うこと。縦の列を「フィールド」横の行を「レコード」という呼ぶ。 ※ＥＸＣＥＬでは同じフィールドに文字や数値が混在していても動作はする。ただし、結果を処理するときに 内容の一貫性がなくなってしまうことがあるので注意すること。 (2)表の１番上の行にあるデータを「フィールド名」（各フィールドを区別するために表す名前）として扱う。 フィールド名に同じ名前が含まれてはいけない。（区別できなくなる） また、自動的に表の範囲を設定するときには、表の周囲を空白セルで囲んでおく必要がある。 表のフィールド名が正確に入力されていれば、その範囲をデータベース機能で処理できる。 ・「国語」などの各教科名に同じものが存在しているので、新たにフィールド名を設定する。 9 行：10 行を選択して 1 行上にドラッグして移動する。 （結合解除の確認メッセージが表示されるがＯＫをクリックする。） 空いた A1O セルに「F1」と入力 し、オートフィルで横ヘコピー して「F42」まで広げる。
・A9：L1O をコピーしてから、シート「Sheet2」を選択し、B2，B1O セルに貼り付ける。 ・検索の条件を設定する。ここでは H4 セルに「欠」を入力する。 ・F1O：M11 セルのどれかを選択しておく。 ・メニューから「データ」→「フィルタオプションの設定」をクリックする。 ・「フィルタオプションの設定画面」で各項目の 設定を行う。 ※「抽出先」は「指定した範囲」に設定する。 ・「リスト範囲」では、シートタブで「Sheet1」を 選択してから、フィールド名が先頭になるよう に範囲（Sheet1!$A$10：$AP$130）を選ぶ。 ・「検索条件」ではシート「Sheet2」の B3：M4 を選択する。 ・「抽出範囲」ではシート「Sheet2」の B11：M11 を選択する。 ・「OK」をクリックすると、検索条件に合致するデータが指定した範囲に抽出される。 ※ここでは国語（「F7」なので 1 学期中間の国語）を欠席した者が抽出される。
データベースで抽出機能を用いるときには、「データ」→「フィルタオプションの設定」 を行っているワークシートにしか出力できない。他のワークシート上にデータを抽出す るときには、この例のようにあらかじめ抽出先のワークシートを選択して、そのシート 上で作業を行い、「リスト範囲」で元の表を設定する必要がある。 演習 検索条件範囲として国語が６０点未満の者を抽出してみよう。条件を「<60」とすればよい。 条件は半角英数字で入力すること。 ２ 複雑な検索条件による抽出 検索条件は１件でなくとも、任意に増やすことができる。さらに、ＡＮＤ（かつ）、ＯＲ（または） などの指定を組み合わせて複雑な条件でデータを調べることができる。 成績条件・性別などを指定して複雑な条件での抽出を行ってみる。 ・今までの抽出条件をクリアする。（「削除」ではセルの位置関係がずれることがあるので注意） ※Ｂ４：Ｍ４を選択し、右クリックで「数式と値のクリア｣を選択する。 ・条件を設定する。今回は例として数学が６０点未満または英語が５０点以下の者を抽出する。 Ｉ４：「<60」 Ｊ５：「<=50」と、半角英数字で入力する。行が変わっていることに注意する。 データベースからデータの抽出を行う。 ・Ｆ１０：Ｍ１１セルのどれかを選択状態にしておく。 ・メニューから「データ」→「フィルタオプションの設定」をクリックする。 ・「フィルタオプションの設定画面」で各項目の設定を行う。 ※「抽出先」を「指定した範囲」に設定する。 ・「リスト範囲」では、シートタブで「Sheet１」を選択してから、フィールド名が先頭になるよう に範囲を選ぶ。 ・「抽出範囲」ではシート「Sheet２」の B11:M11 を選択する。 ・「検索条件」ではシート「Sheet２」の B3：M5 を選択する。 ※検索条件が２行になったので、選択範囲はフィールド名 を含めて３行に広げなければならない。
・条件にあったデータが抽出される。 ここでは「または」の条件なので、 数学・英語の両方に該当 英語のみに該当 数学のみに該当 の 3 種類の該当データが 表示される。 ※抽出条件の設定では、 （１）同じ行の条件はＡＮＤ条件（「～かつ～」の条件） （２）別の行の条件はＯＲ条件（「～または～」の条件） で設定する。 例えば、数学が 60 以下で、かつ、英語が 50 以下の場合には次のように設定する。 ※検索条件範囲は B3：M4 で設定する。 また、同じ項目を細かく設定したい場合にはフィールド名を追加・変更すればよい。 数学で 30 以上、60 未満の者を抽出したい場合には次のようにフィールド名を変更し、設定する。 ※数学（Ｆ８）のフィールド名を英語の位置にコピーしている。 同一行なので「～かつ～」の条件となる。Ｆ８（数学〉の 項目が 30 以上かつ 60 未満となる。 文字の場合には、「あいまいな」検索条件も設定できる。 例えば、名前に「島」の字を含む者を抽出するときには次のように「＊島＊」を設定する。 ※「＊」の文字は任意の文字列を表す。つまり「島」の前に何があっても、 後に何があっても、その長さがいくらでも条件に合致することを認める。 同様の文字に「？」がある。「？」は任意の 1 文字を表す。
３ データベース関数 データベース機能では抽出の操作だけではなく、該当するデータに対して集計計算を行うことが できる。以下のような閑数がよく利用される。 DCOUNT 検索条件にあったデータで、数値が入力されているセルの個数を返す。 DCOUNTA 〃 空白でないセルの個数を返す。 DSUM 〃 数値の合計を返す。 DAVERAGE 〃 数値の平均を返す。 DMAX 〃 数値の最大を返す。 DMIN 〃 数値の最小を返す。 これらの関数は表をデータベースとして扱うので、抽出操作と同じようにリスト範囲や検索条件 範囲などを指定する必要がある。 例として、先ほどまでの表、設定を用いて計算を行ってみる。 ※10 行以下の内容を削除しておく。 ・検索条件として、性別に「１」、国語に「<60」を設定する。 ・E1O，F1O から次のように数式を入力し、確認する。
E1O：「個数」 F1O：「＝DCOUNT(Sheet1!A1O:AP130，”F7”，Sheet2!F3:H4）」 E11：「合計」 F11：「＝DSUM(Sheet1!A1O:AP130，”F7’’，Sheet2!F3:H4)」 E12：「平均」 F12：「＝DAVERAGE(Sheet1!A1O:AP130，”F7”，Sheet2!F3:H4）」 E13：「最大」 F13：「＝DMAX(Sheet1!A10:AP130，”F7”，Sheet2!F3:H4)」 E14：「最小」 F14：「＝DMIN(Sheet1!A1O:AP130，”F7”，Sheet2!F3:H4)」
・結果は次のようになる。 ※データベース関数を扱う場合には、関数に「リスト範囲」、 「対象フィールド」、「検索条件範囲」を設定する。 上式で各関数に与える引数（括弧内の設定）の３つが、 それぞれに対応している。 ※「対象フィールド」は集計するフィールドを設定する。 例ではＦ７（国語）を集計している。 検索条件を複数準備しておけば、一つの表から、様々な条件での集計表を作成すること ができる。ピボットテーブルと大きく異なるのは、検索条件にあわないものを 0 として 表示して、表の形式を整えることができる点である。 データベース機能を用いて抽出操作を行うとき、１度だけ実行することはあまりなく、 何度も同じ抽出操作を繰り返すことが多くなる。 このようなときに「マクロの記録」機能を用いれば、半自動化することもできる。
■ Microsoft,Windows,Windows NT, Microsoft Office,Microsoft Outlook,Internet Explorer,MSN は米国Microsoft Corporation の米国およびその他の国における登録商標または商標です。 ■ Netscape, Netscape Navigator, Netscape ONE, Netscape の N ロゴおよび操舵輪のロゴは、米国
およびその他の諸国のNetscape Communications Corporation 社の登録商標です。 ■ 一太郎、ATOK は、株式会社ジャストシステムの登録商標です。
■ その他、本書に掲載したプログラム名、システム名、CPU などは一般に各社の登録商標です。本 文中では、TM などのマークは明記していません。
■ 本文中では「Microsoft® Internet Explorer」のことを「Internet Explorer 5.0」または「IE5.0」、 「Internet Explorer」と記述しています。 平成１３年１１月１日 初版発行
