-
Home »
-
エクセル関数一覧表 »
- FILTER関数で指定した列だけを表示する
更新:2024/12/17;作成: 2022/10/26
- FILTER関数で指定した列だけを取り出すのに、CHOOSECOLS関数が使用できるようになり、ちょっとスマートな感じの数式にすることができるようになりました。
- FILTER関数で指定した列だけを取り出すのに、以前は配列やCOUNTIF関数で取り出す列位置を指定したりしていました。
FILTER関数が利用できるExcelの種類(バージョンについて)
- FILTER関数はExcel for Microsoft 365 (サブスクリプション型)、Excel2021,Excl2024(永続ライセンス版)で使えます。
ちなみに、Excel2016,Excel2019(永続ライセンス版)では使用できませんので、ご注意ください。 - Filter関数の構文：=FILTER(配列,含む,[空の場合]
引数の表現(日本語)がわかりにくいのでよく見てください。
| 配列 |
フィルターするデータの範囲または配列 |
| 含む |
フィルターする条件を書きます(フィルターで残すデータの条件を書きます) |
| 空の場合 |
(省略可)フィルターした後にデータがない場合に表示するテキスト
(#CALC! エラーとなるときに表示する文字列を指定します) |
FILTER関数の詳細な使い方は FILTER関数でデータを抽出する をご覧ください。
CHOOSECOLS関数が利用できるExcelの種類(バージョンについて)
- CHOOSECOLS関数がExcel for Microsoft 365 (サブスクリプション型),Excl2024(永続ライセンス版)で使えるようになりました。 (2022/9/2に確認しました。)
詳細な使い方は 指定された行・列の配列を返すCHOOSEROWS関数・CHOOSECOLS関数の使い方 をご覧ください。
- CHOOSECOLS関数の構文：=CHOOSECOLS(array,col_num1,[col_num2],...)
関数の引数は現時点では英語表記になっています。
CHOOSECOLS関数の引数
| 引数 |
|
意味 |
| array |
必須 |
セル範囲や配列 |
| col_num1 |
必須 |
返される最初の列番号。 |
| col_num2 |
省略可 |
返される追加の列番号 |
- FILTER関数、CHOOSECOLS関数の利用できるバージョン
|
Spill(スピル) |
FILTER関数 |
CHOOSECOLS関数 |
| Excel2016,Excel2019 |
× |
× |
× |
| Excel2021 |
○ |
○ |
× |
| Excel2024 |
○ |
○ |
○ |
| Excel for Microsoft365 |
○ |
○ |
○ |
- 現時点ではこの方法がスマートではないかと思います。
COUNTIF関数を使う方法には欠点もありますのでご注意ください。 - 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)と指定しています。
不連続な列位置をCOUNTIF関数で指定する方法
- タイトル行の有無をCOUNTIF関数で COUNTIF(J2:M2,B2:H2) と計算して配列 {0,1,0,1,1,0,1} を返すこともできます。
=FILTER(FILTER(B3:H11,H3:H11>=300),COUNTIF(J2:M2,B2:H2))
- COUNTIF関数の部分の結果を調べてみます。(デバッグしてみます)
数式の一部分 COUNTIF(J2:M2,B2:H2) を選択して、[F9]キーを押します。
↓
配列 {0,1,0,1,1,0,1} が返されることがわかります。
確認できたら、[Esc]キーを押して数式を元に戻します。
- [数式]タブの[数式の検証]を利用して確認することもできます。
- ただし、COUNTIF関数を使う方法には欠点もあります。
列見出しが元のデータの列見出しと同じ順番であれば問題は無いですが、順番に違いがあると期待したデータが返されません。
理科と算数が元のデータの列見出しと順番が入違っていますが、COUNTIF関数では同じ配列が返されます。
よって、期待したデータが返されません。
このような場合は、Excel for Microsoft365でしか使えませんが、上記のCHOOSECOLS関数を利用してください。
下記のCHOOSE関数でもOKなことを確認しました。
- 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) となります。
- XLOOKUP関数では1列のデータを取り出すことができます。
J3セルの数式は
=XLOOKUP(J2,$C$2:$H$2,
FILTER($C$3:$H$11,$H$3:$H$11>=300))
と入力しています。
この数式を右方向へコピーしています。
スポンサードリンク
よねさんのWordとExcelの小部屋 »
エクセル関数一覧表 » FILTER関数で指定した列だけを表示する
PageViewCounter
Since2006/2/27