1. Home » 
2. エクセル関数一覧表 » 
3. FILTER関数で指定した列だけを表示する

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

2022/10/26

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

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

• FILTER関数はExcel for Microsoft 365 (サブスクリプション型)で使えます。
  また、Excel2021(永続ライセンス版)でも使用できるようです。
• ちなみに、Excel2016(永続ライセンス版),Excel2019(永続ライセンス版)では使用できませんので、ご注意ください。
• FILTER関数の詳細な使い方は　FILTER関数でデータを抽出する　をご覧ください。

• 定義した条件に基づいてデータの範囲をフィルター処理できます。　　　topへ
  　フィルター
  =FILTER(配列,含む,[空の場合])

  • 引数の表現(日本語)がわかりにくいのでよく見てください。
    

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

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

• CHOOSECOLS関数がExcel for Microsoft 365 (サブスクリプション型)で使えるようになりました。 (2022/9/2に確認しました。)
  詳細な使い方は　指定された行・列の配列を返すCHOOSEROWS関数・CHOOSECOLS関数の使い方　をご覧ください。

• 指定された列の配列を返す
  　チューズ　コール(カラムズ)
  =CHOOSECOLS(array,col_num1,[col_num2],...)

  • 関数の引数は現時点では英語表記になっています。
    CHOOSECOLS関数の引数
    

    引数		意味
    array	必須	セル範囲や配列
    col_num1	必須	返される最初の列番号。
    col_num2	省略可	返される追加の列番号

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

• FILTER関数で指定した列だけを取り出すのに、CHOOSECOLS関数が使用できるようになり、ちょっとスマートな感じの数式にすることができるようになりました。
• C14セルの数式は　=CHOOSECOLS(FILTER(B3:H11,H3:H11>=300),XMATCH(C13:F13,B2:H2))　としています。
  FILTER(B3:H11,H3:H11>=300)　では合計が300以上のデータを取り出しています。
  この取り出したデータから、氏名、算数、理科、合計の列のデータをCHOOSECOLS関数とXMATCH関数で取り出します。
  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}　が返されるからです。
  

配列やCOUNTIF関数で指定した列だけを表示する　　　topへ

• 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関数で指定する方法

• タイトル行の有無をCOUNTIF関数で COUNTIF(C13:F13,B2:H2) と計算して配列 {0,1,0,1,1,0,1} を返すこともできます。
  =FILTER(FILTER(B3:H11,H3:H11>=300),COUNTIF(C13:F13,B2:H2))
  • COUNTIF関数の使い方は　条件に一致するセルをカウントする(COUNTIF関数,COUNTIFS関数)　をご覧ください。
  
  • COUNTIF関数の部分の結果を調べてみます。(デバッグしてみます)
    数式の一部分 COUNTIF(C13:F13,B2:H2) を選択して、[F9]キーを押します。
    
    ↓
    配列 {0,1,0,1,1,0,1} が返されることがわかります。
    確認できたら、[Esc]キーを押して数式を元に戻します。
    
  • [数式]タブの[数式の検証]を利用して確認することもできます。
    
• ただし、COUNTIF関数を使う方法には欠点もあります。
  列見出しが元のデータの列見出しと同じ順番であれば問題は無いですが、順番に違いがあると期待したデータが返されません。
  理科と算数が元のデータの列見出しと順番が入違っていますが、COUNTIF関数では同じ配列が返されます。
  よって、期待したデータが返されません。
  このような場合は、Excel for Microsoft365でしか使えませんが、上記のCHOOSECOLS関数を利用してください。
  下記のCHOOSE関数でもOKなことを確認しました。
  

CHOOSE関数で指定した列だけを表示する　　　topへ

• 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関数で指定した列だけを表示する　　　topへ

• XLOOKUP関数では1列のデータを取り出すことができます。
  C15セルの数式は　
  =XLOOKUP(C14,$C$2:$H$2,
  FILTER($C$3:$H$11,$H$3:$H$11>=300))
  と入力しています。
  この数式を右方向へコピーしています。
  

スポンサードリンク

PageViewCounter

Since2006/2/27