FILTER関数は｢含む｣がキモ!書き方･注意点を解説[MOS365Excel Expertでの出題例付き]

この記事ではExcelのFILTER関数について

• フィルター機能との違い
• 引数の指定方法
• MOS365 Excel Expertでの出題例
• 注意点

を解説します｡

FILTER関数はMOS(Microsoft Office Specialist)365 Excel Expertの試験範囲です｡
MOSの学習を通じて､FILTER関数を何度も入力し､使い方をマスターできます｡

FILTER関数は､フィルター機能の関数版

まず､FILTER関数は､フィルター機能の関数版です｡
表から条件に合致するデータだけを抽出できます｡

フィルター機能:元の表自体を絞り込める

まずフィルターとは､表を絞り込んだり､並び替えできる機能です｡

(元の表)

(価格で並び替えた場合)

(フィルター機能でカテゴリがモバイルデバイスだけのデータに絞り込んだ場合)

Filter関数:元の表とは別の場所に表示できる

Filter関数の､フィルター機能との最大の違いは､元の表はそのままに､別の場所にデータを表示できること｡
絞り込み前の表と､絞り込み後の表で比較したい場合はFILTER関数一択となります｡

(FILTER関数とフィルター機能の違い)

	FILTER関数	フィルター機能
元の表と･･	別	一緒
絞り込み条件の変更	複雑	簡単
2019以前のExcel	非対応	対応

(FILTER関数で絞り込んだ場合)

一方で､絞り込み条件を変更するには式を書き換える必要があり､少し複雑です｡
(条件変更時に変える変更がある箇所)

また､FILTER関数は比較的最近に追加された関数であり､Microsoft365かOFFICE2021のみ対応しています｡OFFICE2019以前のExcelでは､使えないので､注意しましょう｡

[活用例]Filter関数は､メンテナンスするシートを一箇所にまとめられる

Filter関数を活用すると、データのメンテナンスを一元化できます。

例:生徒の班分け

例えば、生徒一覧を元に班分けを行う場合｡
以下が､生徒一覧です｡

班ごとにシートを分けようとしています｡

生徒一覧のデータからフィルター関数で表示すれば、変更があった際にも自動的に反映されます。

(Filter関数を使用して､シート1に1班の生徒を表示)

(｢生徒一覧｣のデータを修正した場合)

(シート1にも自動で反映される)

活用事例:オンライン教材のクーポン管理

私は､オンライン教材のクーポンの管理に､Filter関数を活用しています｡
｢MOS関連の最新クーポンのシートだけを､MOS受講生に渡す｣などが簡単に実現でき､非常に役立っています｡
(メンテナンス用の表)

(FILTER関数を使用して､MOSの講座の割引リンクだけを表示しているシート)

※金額はダミーです｡
(メンテナンス用のシートを修正した場合､クーポンのシートにも反映される)

画像ではコース名を修正していますが､実際の運用ではリンク先を更新させています｡
月に1回クーポン再発行のタイミングでリンク先を変更する必要があり､本来は分類分けしている4シートをすべて更新する必要があるのですが･･
Fileter関数のおかげで､1シートを更新すれば他のシートすべてに反映されるため､非常に助かっています｡

FILTER関数の入力方法

FILTER関数の便利さをお伝えしたところで､続いて入力方法を解説します｡
FILTER関数の引数は､以下の3つです｡

1. 配列
2. 含む
3. 空の場合

それぞれ､簡単に解説します｡

1.配列

配列には､絞り込みを行う表のデータ範囲を指定します｡
注意点は､｢タイトル行は含まない｣こと｡
データ範囲だけを指定します｡

2.含む

絞り込みを行う列と､式を記載します｡
カテゴリの列が､｢モバイルデバイス｣のレコードを抽出したい場合､以下のように記述します｡

ガジェット一覧[カテゴリ]=”モバイルデバイス”

=の後ろには､値ではなくセルを指定することも可能です｡
以下､セルB13を指定した例です｡

3.空の場合

データが存在しなかった場合に､表示する内容を指定します｡
必須ではなく､省略した場合はデータが存在しない場合に「#CALC!」が表示されます｡

何も表示したくない場合は､｢””｣を指定します｡

FILTER関数の作成手順とMOS365 Excel Expertでの例題

FILTER関数は､Microsoft Office Specialist(MOS)365 Excel Expertという試験の出題範囲です｡
FILTER関数の具体的な作成手順を､MOS365の例題に沿って解説します｡

▶演習用ファイルのダウンロードはこちら

問題 

シート「ガジェット一覧」のセルA15に、テーブル「ガジェット一覧」から「カテゴリ」が「モバイルデバイス」のデータをすべて抽出する数式を入力してください。セルB13のカテゴリが変更されても、再計算されるようにします。

解説

1. セルA15を選択し､｢=fil｣を入力してTabキー
   *Tabキーで候補を選択できます｡
2. Shift+F3
   *Shift+F3で､関数の引数ウィンドウを起動します｡
3. テーブル｢ガジェット一覧｣のすべての列のデータ範囲を選択
   *データをすべて抽出するので､データ範囲を全選択します｡
   *左上のセルを選択し､Ctrl+Shift+→､Ctrl+Shift+↓で素早く選択できます｡
   
4.  〈含む〉に移動し､カテゴリの列のデータ範囲を選択
   
5.   続けて｢=｣を入力し､セルB13を選択し､Enter
   

(実行結果)

FILTER関数はMOS(Microsoft Office Specialist)365 Excel Expertの試験範囲です｡
MOSの学習を通じて､FILTER関数を何度も入力し､使い方をマスターできます｡

FILTER関数の注意点

FILTER関数は､シンプルに見えてクセが強いです｡
利用時に注意すべき点をお伝えします｡

タイトル行は別途用意する

FILTER関数は､条件に合致したレコードだけを返します｡
作成する際は､関数を入力するセルの上の行にタイトルを用意しておきましょう｡

｢含む｣の指定方法が独特(セル範囲=条件)

FILTER関数の｢含む｣は､指定方法が独特です｡

ガジェット一覧[カテゴリ]=”モバイルデバイス”

上記のように｢範囲=条件｣の形で記載しますが､他の関数ではこの形で指定することはありません｡

例えば･･

• IF･IFS関数の場合
  ｢B4=”はい”｣など､｢セル=条件｣の形で指定する
• SUMIFSなどのIFS○○関数の場合
  ｢条件範囲｣と｢条件｣を別の引数で指定する
  

範囲指定した上で､その後ろに続けて｢=”モバイルデバイス”｣と条件を書くような指定方法は､FILTER関数のみ｡

関数	指定方法	指定例
IF･IFS	セル=条件	B4=”はい”
○○IF･○○IFS系(SUMIFSなど)	(範囲指定と条件の引数が別に存在する)	条件範囲	A1:A5
		条件	“再試験”
FILTER	セル範囲=条件	A1:A5=”再試験”

FILTER関数を作成する際は｢含む｣の指定が特殊だったことを思い出しましょう｡
そして､他の関数を作成する際に､FILTER関数の｢含む｣のような指定をしないよう､混乱しないことです｡

FILTER関数の応用:並び替えとAnd/Or条件

最後に､応用的な使い方として､並び替えとAND/OR条件の指定方法をお伝えします｡

並び替えにはSORT関数

フィルター関数の結果を並び替えるには、SORT関数を組み合わせます。

=SORT(FILTER(ガジェット一覧,ガジェット一覧[カテゴリ]=B13),1,1)

上記のように記述すると「ガジェットID（1列目）の昇順（1）」で並び替えます。

(参考)sort関数について詳しくは
https://mos.japason.co.jp/excel-sort/

Andは｢*｣､Orは｢+｣

FILTER関数の｢含む｣内で､式同士を*もしくは+で繋げて指定することで､And条件､OR条件を指定可能です｡｢AND｣｢OR｣はそのまま使えないので注意しましょう｡

条件	式	入力方法
AND	*	Shiftを押しながら｢L｣の2つ右のキー
OR	+	Shiftを押しながら｢L｣の1つ右のキー

例:｢カテゴリ列がモバイルデバイス｣ かつ､｢価格(円)列が80000｣
(含む内の記述)

(ガジェット一覧[カテゴリ]=B13)*(ガジェット一覧[価格 (円)]=80000)

(式全体)

=FILTER(ガジェット一覧,(ガジェット一覧[カテゴリ]=B13)*(ガジェット一覧[価格 (円)]=80000))

例:｢カテゴリ列がモバイルデバイス｣もしくは､｢カテゴリ列がコンピュータ｣

(含む内の記述)

(ガジェット一覧[カテゴリ]=”モバイルデバイス”)+(ガジェット一覧[カテゴリ]=”コンピュータ”)

(式全体)

=FILTER(ガジェット一覧,(ガジェット一覧[カテゴリ]=”モバイルデバイス”)+(ガジェット一覧[カテゴリ]=”コンピュータ”))

FILTER関数はMOS(Microsoft Office Specialist)365 Excel Expertの試験範囲です｡
MOSの学習を通じて､FILTER関数を何度も入力し､使い方をマスターできます｡