列全体を検索して取得する

特定の値に一致する列全体を検索して取得するには、INDEX関数とMATCH関数を使用した数式が役立ちます。

特定の値に基づいて列全体を検索して取得する
特定の値に基づいて列全体を合計する
特定の値に基づいて列全体をさらに分析する

特定の値に基づいて列全体を検索して取得する

上記の表に基づいて第2四半期（Q2）の売上リストを取得するには、まずMATCH関数を使用してQ2売上の位置を返し、それをINDEXに渡してその位置にある値を取得します。

汎用構文

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

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

Q2売上のリストを取得するには、以下の数式をセルI6にコピーまたは入力し、Ctrl + Shift + Enterを押してから、セルをダブルクリックしてF9キーを押して結果を得てください。

=INDEX(C5:F11,0,MATCH("Q2",C4:F4,0))

または、セル参照を使用して数式を動的にします：

=INDEX(C5:F11,0,MATCH(I5,C4:F4,0))

数式の説明

=INDEX(C5:F11,0,MATCH(I5,C4:F4,0))

• MATCH(I5,C4:F4,0): match_type 0は、MATCH関数に範囲C4:F4内のI5の値であるQ2の位置を返すように強制します。この場合、それは2です。
• INDEX(C5:F11C5:F11,0,MATCH(I5,C4:F4,0)) = INDEX(C5:F11C5:F11,0,2): INDEX関数は、範囲C5:F11の2番目の列にあるすべての値を次のような配列として返します：{7865;4322;8534;5463;3252;7683;3654}。配列をExcelで表示させるには、数式を入力したセルをダブルクリックしてから F9キーを押す必要があります。

特定の値に基づいて列全体を合計する

現在、売上リストを持っているので、第2四半期（Q2）の総売上を取得するのは簡単な作業です。必要なのは、リストからすべての売上値を合計するためにSUM関数を数式に追加することだけです。

汎用構文

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

この例では、Q2の総売上を取得するには、以下の数式をセルI8にコピーまたは入力し、Enterキーを押して結果を得てください。

=SUM(INDEX(C5:F11,0,MATCH(I5,C4:F4,0)))

数式の説明

=SUM(INDEX(C5:F11,0,MATCH(I5,C4:F4,0)))

• MATCH(I5,C4:F4,0): match_type 0は、MATCH関数に範囲C4:F4内のI5の値であるQ2の位置を返すように強制します。この場合、それは2です。
• INDEX(INDEX(C5:F11C5:F11,0,,0,MATCH(I5,C4:F4,0))) = INDEX(INDEX(C5:F11C5:F11,0,,0,2)): INDEX関数は、範囲C5:F11の2番目の列にあるすべての値を次のような配列として返します：{7865;4322;8534;5463;3252;7683;3654}。
• SUM(INDEX(C5:F11,0,MATCH(I5,C4:F4,0))) = SUM({7865;4322;8534;5463;3252;7683;3654}): SUM関数は配列内のすべての値を合計し、Q2の総売上として$40,773を得ます。

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

Q2売上リストに対して追加処理を行うには、単純にSUM、AVERAGE、MAX、MIN、LARGEなどの他の関数を数式に追加できます。

例えば、Q2中の平均売上を得るには、次の数式を使用します：

=AVERAGE(INDEX(C5:F11,0,MATCH(I5,C4:F4,0)))

Q2中の最高売上を調べるには、以下のいずれかの数式を使用します：

=MAX(INDEX(C5:F11,0,MATCH(I5,C4:F4,0)))
または
=LARGE(INDEX(C5:F11,0,MATCH(I5,C4:F4,0)),1)

関連する関数

関連する数式