いきなり答える備忘録

Google Workspace・Microsoft 365・LibreOfficeなどに関するメモ

(Excel)FILTER関数で空白セルが0にならないようにする

 ExcelのFILTER関数で空白セル(値がない)を抽出すると「0」になりますが、これに代わって空文字列(="")を出力させる方法についてです。空文字列に限らず任意の値を出力できます。

  • FILTER関数の第1引数内で空白セルを空文字列に変換しておけば、「0」ではなく空文字列を出力させることができます。

手順

 まずは問題の再現です。
 画像ではG3セルにFILTER関数を入力し、B3:E7の範囲から「番号が2以上であるもの」を抽出しています。このとき空白セル(値がないセル)は「0」として出力されます。
 この例では空白セルを抽出したものであることがわかりやすいですが、普通に「0」が意味のあるデータとして入力されている場合を考えるとなかなか厄介な問題です。


 そこで、式に少し手を加えて空白セルを空文字列(「=""」や「'」と同じ)として出力させています。

 G3セル

=FILTER(IF(B3:E7="","",B3:E7),B3:B7>=2)

 FILTER関数で参照する段階(第1引数)で、IF関数を使い空白セル(及び空文字列)を空文字列に変換しています。これで空白セルは空文字列とみなされ、空文字列として出力されます。
 式の意図としては「B3:E7=""」は「ISBLANK(B3:E7)」とした方が正確ですが結果的には同じです。


 なお、FILTER関数の出力値0を空文字列に変換するような方法は誤りで、もとから「0」だったセルまで空文字列になってしまいます。
 また、FILTER関数の第3引数(空の場合の出力)で指定すればよいのではないかと思えますが、この引数は「1件も該当しなかった場合の表示」なのでうまくいきません。