行と列のそれぞれのデータを条件として、クロスする箇所のデータを取り出す方法を知っていますか？ 今回はExcelで「クロス集計」を実現する関数の組み合わせについて解説します。
【エクセル時短】縦横で交わるデータを取り出す！「クロス抽出」を実現する関数の組み合わせとは？
公開
2018年、仕事始めから数日がたちました。今年も本連載【エクセル時短】をよろしくお願いいたします！
さて、Excelで作成した表には、縦方向（行）と横方向（列）にデータが並んでいますよね。その表から「特定の値に対応するデータを取り出す」と聞いて、真っ先に思い浮かぶのは「VLOOKUP（ブイ・ルックアップ）関数」だと思います。
しかし、特定の行と列がクロスする箇所、つまり「2つの値に対応するデータを取り出す」場合、VLOOKUP関数だけでは、ちょっとやりにくいのです。表の形式によっては、VLOOKUP関数が使えないこともあります。
【エクセル時短】第42回では、このような「クロス抽出」を関数の組み合わせで実現するテクニックを紹介します！
クロス抽出でVLOOKUP関数をどう使う？
ここでは例として、以下のような表を用意しました。縦方向に「日付」、横方向に「地区」があり、それぞれを値として指定すると「担当者」が取り出される、という具合です。
セルH2に「1月29日」、セルH3に「名古屋」という2つの値を指定すると、セルH5に担当者として「加藤」が抽出されるようにします。
ここで、VLOOKUP関数の構文をあらためて見てみます。
VLOOKUP（検索値, 範囲, 列番号, 検索の型）
「名古屋」は3列目なので「3」と指定すれば担当者を求められますが、いちいち何列目かを数えて関数式を修正するのはスマートではありません。この問題は、もう1つの関数「MATCH（マッチ）関数」が解決してくれます！
MATCH関数でデータの位置を数値化
MATCH関数は、「指定した条件に一致するデータがセル範囲の何番目にあるか」を求める関数です。構文は以下のとおり。
MATCH（検索値, 検査範囲, 照合の種類）
引数［検査範囲］の先頭のセルの位置を1として数え、引数［検索値］が何番目にあるかを数えます。完全一致のデータを検索する場合、引数［照合の種類］には「0」を指定します。
先ほどの表で「地区」が何番目にあるかを求めるには、以下のような関数式になります。これをVLOOKUP関数と組み合わせる手順を見てみましょう。
=MATCH（H3, A2:E2, 0）
1MATCH関数で「地区」を検索する
まずはMATCH関数の働きをわかりやすくするため、条件となるセルの右側にMATCH関数だけを入力して結果を確認します。セルI3に「=MATCH(H3,A2:E2,0)」と入力します。ここではMATCH関数の結果をVLOOKUP関数の引数に利用したいので、引数［検査範囲］には「A2:E2」と指定しています。
2MATCH関数の結果を確認する
MATCH関数の結果として、セルI3に「3」と表示されました。「名古屋」の列番号が数値として取り出されたことにより、VLOOKUP関数の引数として使えます。
3VLOOKUP関数とMATCH関数を組み合わせる
セルH5に、VLOOKUP関数とMATCH関数を組み合わせた以下のような関数式を入力します。引数［列番号］をMATCH関数で指定するわけですね。
=VLOOKUP（H2, A2:E13, MATCH(H3,A2:E2,0), FALSE）
4VLOOKUP関数とMATCH関数でクロス抽出ができた
意図どおり、クロス抽出が行えました！
条件を変更しても、正しくデータが取り出されることがわかります。
INDEX関数＋MATCH関数も定番の組み合わせ
ここまではVLOOKUP関数を使いましたが、クロス抽出を実現する関数の組み合わせとしては、INDEX（インデックス）関数とMATCH関数もあります。INDEX関数の構文は以下のとおり。
INDEX（参照, 行番号, 列番号, 領域番号）
INDEX関数は「配列形式」と「セル参照形式」の2つの使い方ができる関数で、上記はセル参照形式での構文です。引数［範囲］のうち、指定した引数［行番号］と引数［列番号］が交わる箇所のデータを取り出します。まさにクロス抽出のための関数ですね。参照するセル範囲が1つの場合、引数［領域番号］は省略しても結果は変わりません。
まずはINDEX関数の働きを見てみましょう。
表の「第5回」に相当する5行目、「広島」に相当する3列目のデータを取り出す、という使い方です。「=INDEX(B3:E13,5,3)」と指定すると、担当者として「大村」が得られます。
ただ、引数［行番号］に「5」、引数［列番号］に「3」などと、いちいち数値に変換して指定するのでは実践的ではありませんよね。そこで、MATCH関数の出番です。INDEX関数とMATCH関数を以下のように組み合わせれば、1つの関数式でクロス抽出が可能になります。
=INDEX（B3:E13, MATCH(I2,F3:F13,0), MATCH(I3,B2:E2,0)）
セルH5に、上記の関数式を入力します。INDEX関数の引数［行番号］と引数［列番号］をMATCH関数で指定するわけですね。
1つの関数式で、意図どおりのクロス抽出ができました！
ちなみに、VLOOKUP関数＋MATCH関数の例とは異なり、表の左端にある「日付」ではなく、右端に追加した「開催回数」を条件にしていることにお気づきでしょうか？
VLOOKUP関数では引数［検索値］が表の左端にある必要がありますが、INDEX関数なら、そうした制約はありません。INDEX関数＋MATCH関数のほうが、より汎用的に使える組み合わせと覚えておいてください。
VLOOKUP関数で範囲を縦方向に検索する
商品番号の一覧表を検索して商品の価格を取り出すなど、範囲を縦方向に検索して一致する値を求めるVLOOKUP（ブイ・ルックアップ）関数の使い方を解説します。
MATCH関数で検査値の相対位置を求める
指定した検査値が、指定した検査範囲のなかの何番目のセルであるかを求める、MATCH関数の使い方を解説します。
INDEX関数で指定した位置の値を求める
配列のなかの、指定した行番号と列番号の位置にある値を求める、INDEX関数の使い方を解説します。
【エクセル時短】は「少しでも早く仕事を終わらせたい！」というビジネスパーソンのみなさんに、Excelの作業効率をアップするワザをお届けする連載です。毎週木曜日更新。
過去の記事一覧【エクセル時短まとめ】
この記事が気に入ったら
いいね！しよう
できるネットから最新の記事をお届けします。
