パーソナルBI入門 第二回 Excelで分析データを準備する：これからのビジネスマンに必須のツール（2/2 ページ）

[米野宏明（マイクロソフト），ITmedia]

とっても便利なVLOOKUP関数

　いよいよテーブル同士をつなぐ。今回のように単純なコピー＆ペーストではうまく連結できない場合は「VLOOKUP関数」を使うとよい。これは指定した範囲の先頭列を検索し、ヒットした行から指定列の値を返す関数だ。VはVertical、つまり縦（行）方向に検索するという意味で、横（列）方向に検索するHLOOKUP関数もある。Excelでのデータ抽出に大変重宝するのでぜひ覚えたい。

　ここでは販売履歴テーブルの顧客番号を検索キーとして顧客属性テーブルを検索、ヒットした顧客データを取り出す。Sheet 1が1000名分の顧客情報が入った顧客属性テーブル、Sheet 2が販売履歴テーブル、それぞれが先に挙げたような列構造だとすると、販売履歴テーブルの例えばG2セルに、

=VLOOKUP($A2, Sheet1!$A$1:$J$1001, COLUMN()-5, FALSE)

と入力する。これでG2セルには、A2セルの顧客番号「11003」を検索キーとし、Sheet1のA1:I1001範囲の先頭列であるA列を縦に検索（5行目がヒットする）、G2セル自身の列番号（COLUMN（）=7）から5を引いた値2が列番号であるB列のセルB5の値「田中 恵子」が返される。なお検索キーの列や範囲の行列に$（絶対参照）を付けているのは、別のセルにコピーしてもキー列や範囲の位置がずれないようにするためだ。あとはG2セルを選択するとその右下に現れる四角い「フィルハンドル」をマウスでつかみ右の7列分にドラッグ、さらにそのまま最終行までドラッグすれば式のコピーは完了である。同じように1行目にも式をコピーすると、列名も引っ張ってくる。最後にシート全体を選択してコピーし「形式を選択して貼り付け」で「値」として貼り付ければ、式は数字に置換され、行や列を並べ替えても値が崩れなくなる。

2つのテーブルを顧客番号で連結

さらに便利なピボットテーブル

　ピボットテーブルは、元のデータを壊さずに数値列の集計（合計や平均など）ができるExcelの標準機能である。例えば性別や年齢、年収ごとの購入金額平均を出したり、購入商品でフィルタをかけたりといったような対話操作による多角的な集計ができる。Excel 2003以前なら「データ」メニューから、Excel 2007なら「挿入」タブからアクセスできる。このツールさえあればあらゆる集計操作ができるのでぜひ覚えたい。

　このピボットテーブルを使って、連結テーブルを集計する。例えば年収レンジごと、顧客ごと、子供の数ごとに販売金額を集計したいなら、「行ラベル」の位置に「年収」と「顧客番号」（もしくは「顧客名」）、「列ラベル」の位置に「子供の数」、「値」の位置に「購入金額」を配置する。すると表側には年収ごとに該当する顧客番号が整理され、表頭には子供の数が並び、これら3つの属性がクロスするところに集計値が表示される。なお初期状態では集計方法が「合計」となっているが、年収や子供の数のような値の場合は足してはダメなので、「合計 ／ XXX」と表示されている部分を右クリックして「平均」などに変える。

ピボットテーブルによる集計

　このようにピボットテーブルを使えば、元のデータを壊すことなく、さまざまな属性を軸とした多次元集計（クロス集計）ができるのだ。

　文字に起こすと長く見えるが、作業手順は少ないのですぐに終わるだろう。これで準備は完了したので、次回は分析作業を追っていくことにする。