Excelにおける一対多の関係性の解析：INDEX、SMALL、IF関数の組み合わせ

Excelは、数値計算だけでなく、データの分析や検索といった多岐にわたる作業を行うための強力なツールです。特に、一対多の関係性を持つデータを扱う際には、その強力さが発揮されます。今回は、INDEX関数、SMALL関数、IF関数を組み合わせることで、一対多の関係性を持つデータに対して第n番目の値を検索する方法について解説します。

INDEX関数、SMALL関数、IF関数の基本

まずは、使用する関数の基本について簡単に説明します。

• INDEX関数は、指定した範囲内の特定の位置にある値を返すExcel関数です。
• SMALL関数は、指定した配列または範囲内でn番目に小さい数値を返すExcel関数です。
• IF関数は、指定した条件が真であるか偽であるかに応じて、2つの異なる結果を返すExcel関数です。
• ROW関数は、指定したセルの行番号を返すExcel関数です。

これらの関数を組み合わせることで、一対多の関係性を持つデータに対して特定の条件に一致する第n番目の値を効率的に検索することが可能です。

一対多の関係性を持つデータから第n番目の値を検索

以下に、これらの関数を組み合わせて使用する具体的な例を示します。ここでは、商品（B列）とその供給元（A列）をリスト化した以下の表を使用します。

特定の商品（たとえば”リンゴ”）の特定の順番（n番目、たとえば2番目）の供給元を検索するには、以下の配列数式を使用します。Excel 2019以前のバージョンでは確定時に{Ctrl}+{Shift｝+{Enter}を使用してください。

n番目

=INDEX(A1:A10, SMALL(IF(B1:B10="リンゴ", ROW(A1:A10)-ROW(INDEX(A1:A10,1,1))+1), n))

2番目

=INDEX(A1:A10, SMALL(IF(B1:B10="リンゴ", ROW(A1:A10)-ROW(INDEX(A1:A10,1,1))+1), 2))

これらの式は、”リンゴ”という商品のn番目の供給元を検索します。nに「2」を入れて確認すれば、”供給元4″という結果を返します。

式の詳細な動作

この式がどのように動作するかを詳しく見ていきましょう。

1. IF(B1:B10=”リンゴ”, ROW(A1:A10)-ROW(INDEX(A1:A10,1,1))+1): この部分は配列を返します。B列の各セルが”リンゴ”であるかどうかを評価し、”リンゴ”である場合は、そのセルの行番号を返します。行番号はROW(A1:A10)-ROW(INDEX(A1:A10,1,1))+1によって1から始まるように調整されています。”リンゴ”でない場合、このIF関数はFALSEを返します。
2. SMALL( …, n): SMALL関数は、指定した配列または範囲内でn番目に小さい数値を返します。ここでは、前のIF関数によって生成された配列（”リンゴ”の行番号のリスト）を受け取り、その中からn番目に小さい値（つまり、”リンゴ”がn番目に現れる行番号）を返します。
3. INDEX(A1:A10, …): INDEX関数は、指定した範囲内の特定の位置にある値を返します。ここでは、前のSMALL関数によって得られた行番号を使用して、A列から値を取得します。

どのような場面で役立つのか？

この式は、一対多の関係を持つデータを扱う多くの場面で役立ちます。たとえば、顧客と購入商品のリストから、特定の顧客がn番目に購入した商品を検索したり、特定の商品をn番目に購入した顧客を検索したりすることが可能です。また、生徒と参加クラブのリストから、特定の生徒がn番目に参加したクラブ、または特定のクラブにn番目に参加した生徒を検索することも可能です。

まとめ

ExcelのINDEX関数、SMALL関数、IF関数を組み合わせることで、一対多の関係性を持つデータに対して第n番目の値を検索する新たな可能性が広がりました。これにより、データ分析やレポート作成の作業が一層容易になります。Excelの可能性はまだまだ広がっています。これらの機能を活用して、より効率的なデータ操作を行いましょう。