INDEX関数とMATCH関数の組み合わせでExcelを最強に！小売業のデータ検索・抽出術

Excel

INDEX関数とMATCH関数 – なぜこの組み合わせが最強なのか？

小売業において、蓄積された膨大なデータから必要な情報を迅速かつ正確に探し出し、活用することは、売上分析、顧客管理、在庫最適化など、あらゆるビジネス上の意思決定において不可欠です。Excelは、そのための強力なツールであり、VLOOKUP関数をはじめとする様々な検索・抽出関数が用意されています。

しかし、VLOOKUP関数にはいくつかの制約があります。例えば、検索値は指定範囲の左端の列に存在しなければならない、列番号を固定で指定するため柔軟性に欠ける、などの点です。これらの制約が、複雑なデータ構造や多様な検索ニーズに対応する上で課題となることがあります。

そこで登場するのが、INDEX関数とMATCH関数の組み合わせです。この2つの関数は、それぞれ単独でも非常に強力ですが、連携することで、VLOOKUP関数の持つ制約を克服し、より高度で柔軟なデータ検索・抽出を実現する「最強のコンビ」となります。

INDEX関数とMATCH関数のそれぞれの役割

  • INDEX関数: 指定された範囲（配列）の中から、行番号と列番号に対応するセルの値を返します。住所を指定するように、データの「番地」を指定して値を取り出すイメージです。
    • 書式：=INDEX(配列, 行番号, [列番号])
  • MATCH関数: 指定された範囲の中で、検索条件に一致する値が最初に見つかった位置（行番号または列番号）を返します。データの中から特定のキーワードを探し出し、それが何番目にあるかを教えてくれるイメージです。
    • 書式：=MATCH(検索値, 検索範囲, [照合の種類])

なぜこの組み合わせが最強なのか？

INDEX関数は「どの行の、どの列の値が欲しいか」を指定しますが、その「行番号」や「列番号」を固定値で指定するだけでは、柔軟な検索は実現できません。そこで、MATCH関数の出番です。MATCH関数は、検索したい値に基づいて、INDEX関数に必要な「行番号」や「列番号」を動的に計算してくれます。

この連携により、以下のことが可能になります。

  • 柔軟な検索: 検索値が左端の列にある必要がなくなり、どの列をキーにしても検索できます。
  • 可変な列指定: MATCH関数を使って列番号を動的に指定できるため、列の挿入や移動があっても関数を修正する必要が少なくなります。
  • 複数条件検索: 複数の条件を組み合わせて、より絞り込んだデータ抽出が可能になります。
  • 左方向検索: VLOOKUP関数では困難だった、検索値よりも左側の列のデータを抽出できます。

この記事では、INDEX関数とMATCH関数の基本的な使い方から、小売業の現場で直面する様々なデータ検索・抽出のニーズに対応するための実践的な応用例までを詳しく解説します。この最強コンビをマスターすることで、Excelでのデータ操作が一段とレベルアップし、業務効率の向上、そしてより深いデータ分析へと繋がるでしょう。

ターゲット読者

  • 小売業でデータ分析や管理を担当している方
  • ExcelでVLOOKUP関数を利用しているが、その限界を感じている方
  • Excelのデータ検索・抽出スキルを向上させたい中級者以上のユーザー

記事の概要と構成

この記事では、まずINDEX関数とMATCH関数の基本的な書式と使い方を丁寧に解説します。次に、小売業でよくあるデータ検索のシナリオを例に、この2つの関数を組み合わせた実践的な活用方法をステップバイステップで紹介します。さらに、複数条件検索や左方向検索といった応用的なテクニックにも触れ、INDEX関数とMATCH関数の真の力を引き出す方法を解説します。

【基本の徹底】INDEX関数とMATCH関数の書式と使い方

この章では、INDEX関数とMATCH関数の基本的な書式と使い方をそれぞれ詳しく解説します。これらの関数の個々の機能を理解することで、続く章での組み合わせによる強力なデータ検索・抽出テクニックの習得がスムーズになります。

1. INDEX関数の書式と使い方

INDEX関数は、指定した配列（セルの範囲）の中から、指定された行番号と列番号に位置するセルの値を返します。

書式（配列形式）:

=INDEX(配列, 行番号, [列番号])
  • 配列: データを格納しているセルの範囲を指定します。
  • 行番号: 配列内で値を返したい行の番号を指定します。
  • [列番号]: 配列内で値を返したい列の番号を指定します（省略可能。配列が1列の場合や、行全体を返したい場合に省略します）。

基本的な使い方:

以下の表のような商品データがあったとします。

ABC
1商品ID商品名価格
2P001りんご100
3P002バナナ150
4P003みかん80
  • 例1：2行目の商品名を取得する =INDEX(A2:C4, 2, 2) この関数は、範囲 A2:C4 の2行目の2列目にある値、「バナナ」を返します。
  • 例2：3行目の価格を取得する =INDEX(A2:C4, 3, 3) この関数は、範囲 A2:C4 の3行目の3列目にある値、「80」を返します。
  • 例3：2行目のすべてのデータ（商品ID、商品名、価格）を返す =INDEX(A2:C4, 2) 配列が複数列にわたる場合、列番号を省略すると、指定した行のすべてのセルが配列として返されます。この場合、数式が複数のセルにまたがって入力される（スピル機能があるExcelのバージョン）か、他の関数と組み合わせて使用されることが一般的です。

INDEX関数とVLOOKUP関数の範囲の選び方の違い

  • VLOOKUP関数:
    • 検索したい値がある列を左端に含む範囲を選択する必要があります。
    • 指定した範囲内で、左端の列を基準に検索を行い、指定した列番号の値を返します。
    • 範囲は、検索値のある列から、抽出したい値のある列までを含める必要があります。
  • INDEX関数:
    • 抽出したい値がある範囲を任意に選択できます。検索値の列位置は問いません。
    • 行番号と列番号で抽出するセルを指定するため、検索値がある列と抽出したい値がある列を別々に指定できます（MATCH関数と組み合わせて使用する場合）。

簡潔にまとめると、VLOOKUP関数は「検索値が左端」という制約があるのに対し、INDEX関数は抽出範囲を自由に選択でき、MATCH関数と組み合わせることで検索列の位置に制約を受けない点が大きな違いです。

2. MATCH関数の書式と使い方

MATCH関数は、指定された値（検索値）が、指定された範囲（検索範囲）の中で最初に見つかった位置を数値で返します。

書式:

=MATCH(検索値, 検索範囲, [照合の種類])
  • 検索値: 検索する値を指定します。セル参照、直接入力された値、または数式の結果を使用できます。
  • 検索範囲: 検索を行う1行または1列のセル範囲を指定します。
  • [照合の種類]: 検索方法を指定する数値です（省略可能）。
    • 1 (以下): 検索値以下の最大の値を見つけます。検索範囲は昇順に並んでいる必要があります。
    • 0 (完全一致): 検索値と完全に一致する最初の値を見つけます。検索範囲の並び順は任意です。
    • -1 (以上): 検索値以上の最小の値を見つけます。検索範囲は降順に並んでいる必要があります。

基本的な使い方:

上記の商品データテーブルを例に考えます。

  • 例1：「バナナ」が商品名列（B列）の何番目にあるかを取得する（完全一致） =MATCH("バナナ", B2:B4, 0) この関数は、「バナナ」が範囲 B2:B4 の中で2番目にあるため、「2」を返します。
  • 例2：価格「150」が価格列（C列）の何番目にあるかを取得する（完全一致） =MATCH(150, C2:C4, 0) この関数は、「150」が範囲 C2:C4 の中で2番目にあるため、「2」を返します。
  • 例3：商品ID「P003」が商品ID列（A列）の何番目にあるかを取得する（完全一致） =MATCH("P003", A2:A4, 0) この関数は、「P003」が範囲 A2:A4 の中で3番目にあるため、「3」を返します。

この章で解説したように、INDEX関数は指定された行番号と列番号に基づいて値を抽出し、MATCH関数は指定された値が範囲内のどこにあるかの位置（番号）を返します。次の章では、この2つの関数を組み合わせることで、いかに柔軟かつ強力なデータ検索・抽出が可能になるのかを、小売業の具体的な事例を通して見ていきましょう。

INDEX関数とMATCH関数の黄金コンビ！小売業のデータ検索・抽出実践＆応用編

この章では、前章で学んだINDEX関数とMATCH関数の基本的な使い方を組み合わせ、小売業の現場で頻繁に発生するデータ検索・抽出の課題を解決する実践的な方法を解説します。VLOOKUP関数の制約を克服し、より柔軟で強力なデータ操作スキルを習得しましょう。

1. 基本的な検索・抽出：VLOOKUP関数の代替として

INDEX関数とMATCH関数の組み合わせは、VLOOKUP関数と同様の基本的な検索・抽出を行うことができます。

例1：商品コードから商品名を抽出する

ABC
1商品ID商品名価格
2P001りんご100
3P002バナナ150
4P003みかん80

商品コード「P002」に対応する商品名を抽出する関数式は以下のようになります。

=INDEX(B2:B4, MATCH("P002", A2:A4, 0))
  • MATCH("P002", A2:A4, 0): 範囲 A2:A4 の中で “P002” と完全に一致するセルの位置（行番号）を返します。この場合、「2」が返ります。
  • INDEX(B2:B4, 2): 範囲 B2:B4 の2番目の行の値、「バナナ」を返します。

2. 柔軟な列検索：MATCH関数で列番号を可変にする

VLOOKUP関数では、抽出したい列の番号を固定で指定するため、列の挿入や移動があると関数を修正する必要がありました。INDEX関数とMATCH関数の組み合わせでは、MATCH関数を使って列番号を動的に指定できるため、このような問題が解消されます。

例2：ドロップダウンリストで選択した項目を抽出する

ABCDEF
1商品ID商品名価格抽出項目
2P001りんご100商品名りんご
3P002バナナ150
4P003みかん80

F2セルに入力する関数式は以下のようになります。

=INDEX(B2:C4, MATCH("P001", A2:A4, 0), MATCH(E1, B1:C1, 0))
  • MATCH("P001", A2:A4, 0): 商品ID “P001” が範囲 A2:A4 の中で見つかる行番号（「2」）を返します。
  • MATCH(E1, B1:C1, 0): ドロップダウンリストで選択された項目（E1セル）が、見出し行 B1:C1 の中で見つかる列番号を返します。もし “商品名” が選択されていれば「1」、”価格” が選択されていれば「2」が返ります。
  • INDEX(B2:C4, 2, [MATCH関数の結果]): 範囲 B2:C4 の2行目の、MATCH関数で特定された列の値を返します。

3. 複数条件による検索

INDEX関数とMATCH関数を組み合わせることで、複数の条件に合致するデータを抽出することも可能です。ここでは、作業列を使用する方法と、より応用的な配列数式を使う方法を紹介します。

方法1：作業列を使用する

例3：特定期間かつ特定商品の売上を抽出する

ABCDEF
1日付商品売上作業列検索項目2025/4/20
22025/4/20りんご1002025/4/20りんご検索項目りんご
32025/4/21りんご1202025/4/21りんご
42025/4/20バナナ1502025/4/20バナナ
52025/4/21バナナ1802025/4/21バナナ

D列に、日付と商品を結合した作業列を作成します（D2セルに =A2&B2 と入力し、下までコピー）。

G2セルに入力する関数式は以下のようになります。

=INDEX(C2:C5, MATCH(F1&F2, D2:D5, 0))
  • F1&F2: 検索日付（F1）と検索商品（F2）の値を結合した文字列を作成します（例: “2025/4/20りんご”）。
  • MATCH(F1&F2, D2:D5, 0): 作業列 D2:D5 の中で、結合された検索文字列と完全に一致する最初の行番号を返します。
  • INDEX(C2:C5, [MATCH関数の結果]): 売上列 C2:C5 の、MATCH関数で見つかった行の値を返します。

方法2：配列数式を使用する

作業列を使わずに複数条件で検索することも可能です。配列数式を使うと、複数の条件を同時に評価できます。

=INDEX(C2:C5, MATCH(1, (A2:A5=F1)*(B2:B5=F2), 0))
  • (A2:A5=F1): A列の日付が検索日付（F1）と一致するかどうかを評価し、TRUEまたはFALSEの配列を返します。
  • (B2:B5=F2): B列の商品が検索商品（F2）と一致するかどうかを評価し、TRUEまたはFALSEの配列を返します。
  • (A2:A5=F1)*(B2:B5=F2): 上記2つの配列の対応する要素を掛け算します。TRUEは1、FALSEは0として扱われるため、両方の条件がTRUEの行は1、それ以外は0の配列になります。
  • MATCH(1, ..., 0): 結果が1の最初の位置（両方の条件が一致する最初の行）を返します。
  • INDEX(C2:C5, [MATCH関数の結果]): 売上列の該当する行の値を返します。

この数式は、入力後に Ctrl + Shift + Enter を押して配列数式として確定する必要があります。

4. 左方向検索

VLOOKUP関数では、検索値よりも左側の列のデータを直接抽出することはできませんでしたが、INDEX関数とMATCH関数の組み合わせではこれが可能です。

例4：商品名から商品コードを抽出する

ABC
1商品ID商品名価格
2P001りんご100
3P002バナナ150
4P003みかん80

商品名「バナナ」に対応する商品IDを抽出する関数式は以下のようになります。

=INDEX(A2:A4, MATCH("バナナ", B2:B4, 0))
  • MATCH("バナナ", B2:B4, 0): 範囲 B2:B4 の中で “バナナ” と完全に一致するセルの位置（行番号）を返します。この場合、「2」が返ります。
  • INDEX(A2:A4, 2): 範囲 A2:A4 の2番目の行の値、「P002」を返します。

5. 複数の検索条件の結果をまとめて抽出

INDEX関数とMATCH関数を組み合わせることで、複数の条件に合致する複数の結果を一度に抽出することも可能です。これには、配列数式を活用する方法があります。

例5：特定期間に売れたすべての商品の商品名と売上を抽出する

ABC
1日付商品売上
22025/4/20りんご100
32025/4/21りんご120
42025/4/20バナナ150
52025/4/21バナナ180
62025/4/20みかん80

複数の結果を抽出するため、ここでは複数のセルに出力することを前提とします。まず、商品名を抽出する最初のセル（例えばE2セル）に以下の配列数式を入力し、必要に応じて下方向にコピーします。

=INDEX(B2:B6, SMALL(IF(A2:A6="2025/4/20", ROW(A2:A6)-ROW(A2)+1, FALSE), ROW(A1)))

この数式は、入力後に Ctrl + Shift + Enter を押して配列数式として確定する必要があります。

  • IF(A2:A6="2025/4/20", ROW(A2:A6)-ROW(A2)+1, FALSE): A列の日付が “2025/4/20” と一致する場合、その行番号を返します。ROW(A2:A6)-ROW(A2)+1 は、範囲内での相対的な行番号（1, 2, 3, …）を生成します。一致しない場合はFALSEを返します。これにより、条件に合致する行番号とFALSEからなる配列が生成されます。
  • SMALL(..., ROW(A1)): SMALL関数は、配列の中でn番目に小さい値を返します。ROW(A1) は1を返し、下方向にコピーすると2, 3, …と変化するため、条件に合致する行番号を小さい順に取り出します。FALSEは無視されます。
  • INDEX(B2:B6, ...): SMALL関数で得られた行番号を使って、商品名（B列）を抽出します。

同様に、売上を抽出する最初のセル（例えばF2セル）に以下の配列数式を入力し、E列に合わせて下方向にコピーします。

=INDEX(C2:C6, SMALL(IF(A2:A6="2025/4/20", ROW(A2:A6)-ROW(A2)+1, FALSE), ROW(A1)))

こちらも入力後に Ctrl + Shift + Enter で確定します。

6. 他の関数との連携：条件付き集計

INDEX関数とMATCH関数は、SUMIF関数やAVERAGEIF関数などの条件付き集計関数と組み合わせることで、より柔軟なデータ分析を可能にします。

例6：特定の商品カテゴリの売上合計を算出する

ABC
1商品IDカテゴリ売上
2P001食品100
3P002電化製品150
4P003食品80
5P004食品120
6P005電化製品200

特定の商品カテゴリ（例えばE1セルに「食品」と入力）の売上合計を算出する関数式は以下のようになります。

=SUMIF(B2:B6, E1, C2:C6)

この例では、INDEX関数とMATCH関数は直接的には使われていませんが、SUMIF関数の条件範囲や合計範囲を、INDEX関数とMATCH関数を使って動的に指定することも可能です。

例えば、カテゴリの列が固定されておらず、MATCH関数でカテゴリ列の番号を検索し、その列をSUMIF関数の条件範囲としてINDEX関数で指定する、といった応用が考えられます。

=SUMIF(INDEX(B1:C6,,MATCH("カテゴリ",B1:C1,0)), E1, INDEX(B1:C6,,MATCH("売上",B1:C1,0)))
  • MATCH("カテゴリ",B1:C1,0): 見出し行から「カテゴリ」という文字列がある列番号を取得します。
  • INDEX(B1:C6,,[MATCHの結果]): B1:C6の範囲から、MATCH関数で特定された列全体を条件範囲として指定します。
  • 同様に、「売上」列もMATCH関数とINDEX関数で特定し、SUMIF関数の合計範囲として指定します。
  • E1: 検索したいカテゴリ（例: “食品”）が入力されたセルを参照します。

これらの実践例と応用例を通して、INDEX関数とMATCH関数の組み合わせが、小売業における多様なデータ検索・抽出のニーズに対応できる強力なツールであることが改めて理解できたかと思います。次の章では、これらの知識をさらに深め、データ分析の幅を広げるためのまとめを行います。

まとめ：INDEX関数とMATCH関数をマスターして、データ検索・抽出の達人に！

この記事では、ExcelのINDEX関数とMATCH関数の基本的な使い方から、小売業における実践的なデータ検索・抽出の応用例までを詳しく解説しました。VLOOKUP関数の制約を克服し、より柔軟で強力なデータ操作を実現するこの「最強のコンビ」を使いこなすことで、日々のデータ分析業務を効率化し、より深い洞察を得ることが可能になります。

今回解説した内容の要点を再確認

  • INDEX関数は、指定した範囲の行番号と列番号に基づいて値を抽出する関数であること。
  • MATCH関数は、指定した値が範囲内で最初に見つかった位置（行番号または列番号）を返す関数であること。
  • この2つの関数を組み合わせることで、VLOOKUP関数では難しかった柔軟な列検索、複数条件検索、左方向検索などが可能になること。
  • 小売業における具体的な活用例として、商品コードからの商品名抽出、ドロップダウンリストと連携した抽出、複数条件での売上抽出、商品名からの商品コード抽出などを紹介したこと。
  • 複数の検索条件の結果をまとめて抽出する方法や、SUMIF関数などの他の関数と連携して条件付き集計を行う応用例も解説したこと。
  • 複数条件検索においては、配列数式を使うことで、データ範囲全体に対して複数の条件を同時に評価し、目的の行を特定できることを説明したこと。

INDEX関数とMATCH関数の組み合わせのメリット

  • 柔軟な検索: 検索キーが左端の列にある必要がなく、どの列でも検索キーとして使用できます。
  • 可変な列指定: MATCH関数で列番号を動的に指定できるため、列の挿入や移動による影響を受けにくいです。
  • 高度な条件検索: 複数の条件を組み合わせて、より複雑なデータ抽出が可能です。
  • VLOOKUPの制約を克服: 左方向検索など、VLOOKUP関数では実現できなかった検索が可能になります。
  • データ分析の幅を拡大: 他の関数と組み合わせることで、より高度なデータ分析や集計が実現できます。

今後の学習へ

INDEX関数とMATCH関数の組み合わせは、Excelにおけるデータ検索・抽出の強力な武器となります。この記事で解説した基本的な使い方と応用例をマスターすることで、小売業におけるデータ分析のスキルは飛躍的に向上するでしょう。

さらに、Excelには、今回紹介した以外にも、より高度なデータ操作や分析を可能にする様々な関数や機能が存在します。これらの知識を習得し、組み合わせることで、データ活用の可能性は無限に広がります。

ぜひ、今回学んだINDEX関数とMATCH関数の知識を土台として、他の関数や機能の学習にも積極的に取り組んでください！

コメント

タイトルとURLをコピーしました