FILTER関数を使えば、元の表にフィルター操作して直接に表示を変えなくても必要なデータを抽出することができます。
今回は、過去の記事でご紹介した方法を参照しながら、より実用的なデータ抽出の一例をご紹介します。
例によって下図の表データを使ってみます。
数式の意味合いがより分かりやすくなるので、この表を「売上表」という名前のテーブルにしました。
その右側のセルH1には「担当者」の姓リストによるプルダウンリストで選択できるようにしました。
ここは、「Excel FILTER関数とUNIQUE関数を使ってドロップダウンリスト」記事でご紹介した方法で、「担当者」列のデータから氏名リストを作ってもよいのですが、姓だけとし、ワイルドカード抽出も試してみます。
そして、セルI1に「売上日」、J1に「機種コード」、K1に「売上額」という見出しを作っておきます。
それでは、セルH1の姓を検索キーにして、I列、J列、K列にデータ抽出してみます。
まず、セルI2に次のように入力します：
=FILTER(売上表[売上日],ISNUMBER(FIND(H1,売上表[担当者])))
FILTER関数自体については、「Excel FILTER関数」記事でご紹介しました。
また、ワイルドカードを使った FILTER関数の使い方は、「Excel Filter関数でワイルドカードを使う」記事でご紹介した中の 1つの方法を使いました。
上記の数式を実行すると、当初は標準形式で表示されますので、ここでは単に表示形式を「日付」としました。
次に、セルJ2に次のように入力します：
=FILTER(売上表[機種コード],ISNUMBER(FIND(H1,売上表[担当者])))
戻り値が「機種コード」という文字列ですので、標準形式のまま表示させています。
最後に、セルK2に次のように入力します：
=FILTER(売上表[売上額],ISNUMBER(FIND(H1,売上表[担当者])))
この数式の戻り値は「売上額」という数値ですので、これに ￥マークと3桁ごとの区切りを付記するため、表示形式を「通貨」としました。
そして、これら 3つの列をまとめておいたほうが後の処理が楽になるので HSTACK関数でまとめます：
=HSTACK(
FILTER(売上表[売上日],ISNUMBER(FIND(H1,売上表[担当者]))),
FILTER(売上表[機種コード],ISNUMBER(FIND(H1,売上表[担当者]))),
FILTER(売上表[売上額],ISNUMBER(FIND(H1,売上表[担当者])))
)
これで、担当者の姓を指定すると、その「売上日」「機種コード」「売上額」データが抽出され、一覧表として表示されます。（下図）
今回は、これに加え、抽出されたデータの直下にその「売上額」データの合計を表示してみます。
担当者によって抽出されたデータの数（行数）が違いますので、その直下に合計行を付けるのは VSTACK関数で行ないます。
その前に、抽出されたデータの「売上額」データの合計を求めておきます。
これは単に、SUM関数を使って次のようになります：
=SUM(FILTER(売上表[売上額],ISNUMBER(FIND(H1,売上表[担当者]))))
この値を含めて 3つのセルを横並びにして
=HSTACK("合計","",SUM(FILTER(売上表[売上額],ISNUMBER(FIND(H1,売上表[担当者])))))
この横並びの配列を、上記抽出されたデータの直下に付けます：
=VSTACK(
HSTACK(
FILTER(売上表[売上日],ISNUMBER(FIND(H1,売上表[担当者]))),
FILTER(売上表[機種コード],ISNUMBER(FIND(H1,売上表[担当者]))),
FILTER(売上表[売上額],ISNUMBER(FIND(H1,売上表[担当者])))),
HSTACK(
"合計",
"",
SUM(FILTER(売上表[売上額],ISNUMBER(FIND(H1,売上表[担当者])))))
)
これで、完成です。（下図）
セルH1の姓リストを変更すれば、抽出データの行数に応じて合計金額の行がその直下に表示されます。（下図）
なお、上記の数式はずいぶん長くなって複雑そうに見えますが、LET関数や LAMBDA関数などを使ってもう少し見通しの良い表現とすることもできます。