FILTER関数で指定した列だけを表示する:Excel関数

スポンサードリンク

更新:2024/4/29;作成: 2022/10/26

FILTER関数で指定した列だけを取り出すのに、CHOOSECOLS関数が使用できるようになり、ちょっとスマートな感じの数式にすることができるようになりました。
FILTER関数で指定した列だけを取り出すのに、以前は配列やCOUNTIF関数で取り出す列位置を指定したりしていました。

FILTER関数が利用できるExcelの種類(バージョンについて)

FILTER関数はExcel for Microsoft 365 (サブスクリプション型)、Excel2021(永続ライセンス版)で使えます。
ちなみに、Excel2016(永続ライセンス版),Excel2019(永続ライセンス版)では使用できませんので、ご注意ください。

Filter関数の構文：=FILTER(配列,含む,[空の場合]
引数の表現(日本語)がわかりにくいのでよく見てください。

配列	フィルターするデータの範囲または配列
含む	フィルターする条件を書きます(フィルターで残すデータの条件を書きます)
空の場合	(省略可)フィルターした後にデータがない場合に表示するテキスト (#CALC! エラーとなるときに表示する文字列を指定します)

FILTER関数の詳細な使い方は　FILTER関数でデータを抽出する　をご覧ください。

CHOOSECOLS関数がExcel for Microsoft 365 (サブスクリプション型)で使えるようになりました。 (2022/9/2に確認しました。)
詳細な使い方は　指定された行・列の配列を返すCHOOSEROWS関数・CHOOSECOLS関数の使い方　をご覧ください。
CHOOSECOLS関数の構文：=CHOOSECOLS(array,col_num1,[col_num2],...)
関数の引数は現時点では英語表記になっています。
CHOOSECOLS関数の引数

引数意味

array 必須セル範囲や配列

col_num1 必須返される最初の列番号。

col_num2 省略可返される追加の列番号

現時点ではこの方法がスマートではないかと思います。
FILTER関数で指定した列だけを取り出すのに、CHOOSECOLS関数が使用できるようになり、ちょっとスマートな感じの数式にすることができるようになりました。
C14セルの数式は
=CHOOSECOLS(
FILTER(B3:H11,H3:H11>=300),
XMATCH(C13:F13,B2:H2))
としています。
Filter関数で取り出したデータから、氏名、算数、理科、合計の列のデータをCHOOSECOLS関数とXMATCH関数で取り出します。
FILTER(B3:H11,H3:H11>=300)　では合計が300以上のデータを取り出しています。
XMATCH(C13:F13,B2:H2)では氏名、算数、理科、合計の列見出しの位置　{2,4,5,7}　を調べています。
CHOOSECOLS関数で取り出したデータの列の位置　{2,4,5,7}　のデータを取り出しています。
【おすすめポイント】列の並び順が異なっても同じ数式でデータの抽出ができます。
理科と算数が元のデータの列見出しと順番が入違っていますが、CHOOSECOLS関数とXMATCH関数を組み合わせた場合は期待したデータを返すことができます。
XMATCH(C13:F13,B2:H2)で　{2,5,4,7}　が返されるからです。

CHOOSECOLS関数が使えるようになる以前は、配列やCOUNTIF関数で取り出す列位置を指定したりしていました。
条件に合致したデータをすべて(連続した列)で抽出するには
C15セルに　=FILTER(C3:H11,H3:H11>=300)
と入力するだけで、下図のように氏名～合計までの連続した列を抽出することができます。
では、合計が300以上のデータの、氏名と算数と理科と合計の不連続な列のデータを表示するにはどうすればよいのでしょう？

C14セルに　=FILTER(FILTER(B3:H11,H3:H11>=300),{0,1,0,1,1,0,1})　と入力します。
B3:H11 の各列について、{0,1,0,1,1,0,1} と、表示する列は 1(TRUE)、表示しない列は 0(FALSE)と指定しています。
- Googleスプレッドシートのヘルプに使い方の例(行の例です)として挙げてあります。
  FILTER ソース範囲をフィルタ処理して、指定した条件を満たす行または列のみを返します。
  https://support.google.com/docs/answer/3093197?hl=ja

Countif関数では不都合なことがわかりましたので、調べてみたところCHOOSE関数で解決できることがわかりました。
CHOOSECOLS関数を使った場合に比べて一寸見た目が悪いのですが・・・
数式を見やすくするためにデータリストをテーブルに変換しています。
テーブル名は「成績表」としています。
また、SORT関数で合計の大きい方からデータを並べ替えています。
C14セルの数式は
=SORT(
FILTER(
CHOOSE({1,2,3,4},成績表[氏名],成績表[理科],成績表[算数],成績表[合計])
,成績表[合計]>=300)
,4,-1)
としています。
- 取り出す列が氏名、理科、算数、合計の4列なので、　CHOOSE({1,2,3,4}　と4列分の列を確保します。
  それぞれの列に、CHOOSE({1,2,3,4},成績表[氏名],成績表[理科],成績表[算数],成績表[合計]) とセル範囲を順番に当てはめていきます。
  テーブルでない場合は　CHOOSE({1,2,3,4},C3:H11,F3:F11,E3:E11,H3:H11)　となります。