行全体の検索と取得

特定の値に一致するデータの行全体を検索・取得するには、INDEX 関数とMATCH 関数を組み合わせて、配列数式を作成できます。

特定の値に基づいて行全体を検索・取得
特定の値に基づいて行全体を合計
特定の値に基づいて行全体をさらに分析

特定の値に基づいて行全体を検索・取得

Jimmy が達成した売上リストを上記の表から取得するには、まず MATCH 関数で Jimmy の売上がある位置を特定し、その結果を INDEX 関数に渡して該当位置の値を取得します。

一般的な構文

=INDEX()return_range,MATCH()lookup_value,lookup_array,0),0)

√ 注：これは配列数式であり、Ctrl+Shift+Enterで入力する必要があります。

Jimmy が達成した売上リストを取得するには、以下の数式をセル I6 にコピーまたは入力し、Ctrl+Shift+Enterを押した後、セルをダブルクリックしてF9を押して結果を表示させてください：

=INDEX()C5:F11,MATCH()「Jimmy」,B5:B11,0),0)

あるいは、数式を動的にするにはセル参照を使用します：

=INDEX()C5:F11,MATCH()I5,B5:B11,0),0)

数式の説明

=INDEX()C5:F11,MATCH(I5,B5:B11,0),0)

• MATCH(I5,B5:B11,0):match_type 0により、MATCH 関数はJimmy（I5セルの値）をB5:B11の範囲内で検索し、その位置を返します。結果は7です。なぜなら、Jimmyはこの範囲の7 番目の位置にあるからです。
• INDEX()C5:F11,MATCH(I5,B5:B11,0),0) = INDEX(C5:F11INDEX 関数は、7 行目のC5:F11 範囲内のすべての値を、次のような配列で返します：{6678,3654,3278,8398}。なお、Excel でこの配列を表示させるには、数式を入力したセルをダブルクリックしてからF9キーを押してください。

特定の値に基づいて行全体を合計

Jimmy が達成した年間売上高を取得するには、Jimmy の売上情報をすべて取得した上で、リスト内の売上値を合計するために数式に SUM 関数を追加します。

一般的な構文

=SUM(INDEX())return_range,MATCH()lookup_value,lookup_array,0),0))

この例では、Jimmy が達成した年間売上高を取得するには、以下の数式をセル I7 にコピーまたは入力し、Enterを押して結果を取得してください：

=SUM(INDEX())C5:F11,MATCH()I5,B5:B11,0),0))

数式の説明

=SUM()INDEX()C5:F11,MATCH(I5,B5:B11,0),0))

• MATCH(I5,B5:B11,0):match_type 0により、MATCH 関数はJimmy（I5セルの値）をB5:B11の範囲内で検索し、その位置を返します。結果は7です。なぜなら、Jimmyはこの範囲の7 番目の位置にあるからです。
• INDEX()C5:F11,MATCH(I5,B5:B11,0),0)=C5:F117:INDEX 関数は、7 行目のC5:F11 範囲内のすべての値を、次のような配列で返します：{6678,3654,3278,8398}。なお、Excel でこの配列を表示させるには、数式を入力したセルをダブルクリックしてからF9キーを押してください。
• SUM()INDEX()C5:F11,MATCH(I5,B5:B11,0)),0) = SUM({6678,3654,3278,8398}):SUM 関数は配列内のすべての値を合計し、Jimmy の年間売上高として$22,008を算出します。

特定の値に基づいて行全体をさらに分析

Jimmy の売上に対してさらに処理を行うには、数式に SUM、AVERAGE、MAX、MIN、LARGE などの他の関数を簡単に追加できます。

たとえば、各四半期におけるジミーの売上平均を取得するには各四半期ごとのジミーの売上平均、次の数式を使用できます。

=AVERAGE(INDEX())C5:F11,MATCH()I5,B5:B11,0),0))

ジミーが達成した最低売上を調べるには、以下のいずれかの数式を使用してください。

=MIN(INDEX())C5:F11,MATCH()I5,B5:B11,0),0))
または
=SMALL(INDEX())C5:F11,MATCH()I5,B5:B11,0),0),1)

関連する関数

関連する数式