モジュール ME-17 ME-17 データベース機能による データの抽出と集計 岡山県情報教育センター
データベース機能 EXCEL には簡単なデータベース機能があり 表のデータから条件に合致するレコードを抽出することなどができる 本来がデータベースソフトウェアではないので 専用のソフトと比べるとその機能は劣るが 単なる表引きや計算ではできないことを可能にし 非常に便利な利用をすることができる 1 データベースとしての表成績データから特定のデータを抽出してみる ここでは 成績一覧表.xls のデータを利用してデータベース機能を演習する 成績一覧表.xls のファイルを開く シート data をコピーしてから 新規ワークシートを挿入し シート sheet1 に貼り付ける 表のデータをデータベースとして扱うときには いくつかの注意点がある (1) 表は縦の列ごとに同じ種類のデータを扱うこと 縦の列を フィールド 横の行を レコード という呼ぶ EXCELでは同じフィールドに文字や数値が混在していても動作はする ただし 結果を処理するときに内容の一貫性がなくなってしまうことがあるので注意すること (2) 表の1 番上の行にあるデータを フィールド名 ( 各フィールドを区別するために表す名前 ) として扱う フィールド名に同じ名前が含まれてはいけない ( 区別できなくなる ) また 自動的に表の範囲を設定するときには 表の周囲を空白セルで囲んでおく必要がある 表のフィールド名が正確に入力されていれば その範囲をデータベース機能で処理できる 国語 などの各教科名に同じものが存在しているので 新たにフィールド名を設定する 9 行 :10 行を選択して 1 行上にドラッグして移動する ( 結合解除の確認メッセージが表示されるがOKをクリックする ) 空いた A1O セルに F1 と入力し オートフィルで横ヘコピーして F42 まで広げる 2
A9:L1O をコピーしてから シート Sheet2 を選択し B2,B1O セルに貼り付ける 検索の条件を設定する ここでは H4 セルに 欠 を入力する F1O:M11 セルのどれかを選択しておく メニューから データ フィルタオプションの設定 をクリックする フィルタオプションの設定画面 で各項目の設定を行う 抽出先 は 指定した範囲 に設定する リスト範囲 では シートタブで Sheet1 を選択してから フィールド名が先頭になるように範囲 (Sheet1!$A$10:$AP$130) を選ぶ 検索条件 ではシート Sheet2 の B3:M4 を選択する 抽出範囲 ではシート Sheet2 の B11:M11 を選択する OK をクリックすると 検索条件に合致するデータが指定した範囲に抽出される ここでは国語 ( F7 なので 1 学期中間の国語 ) を欠席した者が抽出される 3
データベースで抽出機能を用いるときには データ フィルタオプションの設定 を行っているワークシートにしか出力できない 他のワークシート上にデータを抽出するときには この例のようにあらかじめ抽出先のワークシートを選択して そのシート上で作業を行い リスト範囲 で元の表を設定する必要がある 演習検索条件範囲として国語が 60 点未満の者を抽出してみよう 条件を <60 とすればよい 条件は半角英数字で入力すること 2 複雑な検索条件による抽出検索条件は 1 件でなくとも 任意に増やすことができる さらに AND( かつ ) OR( または ) などの指定を組み合わせて複雑な条件でデータを調べることができる 成績条件 性別などを指定して複雑な条件での抽出を行ってみる 今までの抽出条件をクリアする ( 削除 ではセルの位置関係がずれることがあるので注意 ) B4:M4 を選択し 右クリックで 数式と値のクリア を選択する 条件を設定する 今回は例として数学が 60 点未満または英語が 50 点以下の者を抽出する I4: <60 J5: <=50 と 半角英数字で入力する 行が変わっていることに注意する データベースからデータの抽出を行う F10:M11 セルのどれかを選択状態にしておく メニューから データ フィルタオプションの設定 をクリックする フィルタオプションの設定画面 で各項目の設定を行う 抽出先 を 指定した範囲 に設定する リスト範囲 では シートタブで Sheet1 を選択してから フィールド名が先頭になるように範囲を選ぶ 抽出範囲 ではシート Sheet2 の B11:M11 を選択する 検索条件 ではシート Sheet2 の B3:M5 を選択する 検索条件が2 行になったので 選択範囲はフィールド名を含めて3 行に広げなければならない 4
条件にあったデータが抽出される ここでは または の条件なので 数学 英語の両方に該当英語のみに該当数学のみに該当の 3 種類の該当データが表示される 抽出条件の設定では (1) 同じ行の条件は AND 条件 ( ~ かつ ~ の条件 ) (2) 別の行の条件は OR 条件 ( ~ または ~ の条件 ) で設定する 例えば 数学が 60 以下で かつ 英語が 50 以下の場合には次のように設定する 検索条件範囲は B3:M4 で設定する また 同じ項目を細かく設定したい場合にはフィールド名を追加 変更すればよい 数学で 30 以上 60 未満の者を抽出したい場合には次のようにフィールド名を変更し 設定する 数学 (F8) のフィールド名を英語の位置にコピーしている 同一行なので ~かつ~ の条件となる F8( 数学 の項目が 30 以上かつ 60 未満となる 文字の場合には あいまいな 検索条件も設定できる 例えば 名前に 島 の字を含む者を抽出するときには次のように * 島 * を設定する * の文字は任意の文字列を表す つまり 島 の前に何があっても 後に何があっても その長さがいくらでも条件に合致することを認める 同様の文字に? がある? は任意の 1 文字を表す 1 年 2 年 3 年 は? 年 では全て該当することになる 検索条件に空白行を入れると 全てのフィールドで 条件なし になり 元のデータが全て抽出されてしまう 抽出をかける場合には 検索条件範囲の設定時に空白行を含めないよう注意することが必要である 5
3 データベース関数データベース機能では抽出の操作だけではなく 該当するデータに対して集計計算を行うことができる 以下のような閑数がよく利用される DCOUNT 検索条件にあったデータで 数値が入力されているセルの個数を返す DCOUNTA 空白でないセルの個数を返す DSUM 数値の合計を返す DAVERAGE 数値の平均を返す DMAX 数値の最大を返す DMIN 数値の最小を返す これらの関数は表をデータベースとして扱うので 抽出操作と同じようにリスト範囲や検索条件範囲などを指定する必要がある 例として 先ほどまでの表 設定を用いて計算を行ってみる 10 行以下の内容を削除しておく 検索条件として 性別に 1 国語に <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) 結果は次のようになる データベース関数を扱う場合には 関数に リスト範囲 対象フィールド 検索条件範囲 を設定する 上式で各関数に与える引数 ( 括弧内の設定 ) の3つが それぞれに対応している 対象フィールド は集計するフィールドを設定する 例ではF7( 国語 ) を集計している 検索条件を複数準備しておけば 一つの表から 様々な条件での集計表を作成することができる ピボットテーブルと大きく異なるのは 検索条件にあわないものを 0 として表示して 表の形式を整えることができる点である データベース機能を用いて抽出操作を行うとき 1 度だけ実行することはあまりなく 何度も同じ抽出操作を繰り返すことが多くなる このようなときに マクロの記録 機能を用いれば 半自動化することもできる 6
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 と記述しています 平成 13 年 11 月 1 日初版発行 発行 岡山県情報教育センター 703-8288 岡山市赤坂本町 3 番 15 号電話 (086)272-1405( 総務課 ) 272-4608( 研修課 ) 交通機関市内電車岡山駅前 ( 東山行 ) 乗車東山 ( 終点 ) 下車, 南へ徒歩 7 分市内バス岡山駅前 ( 東山行 ) 乗車東高前下車, 西へ徒歩 2 分 禁無断転載 2001 岡山県情報教育センター