FILTERで商品名が空白の行を除外するには、条件に「商品名の範囲<>空文字」を指定します。大切なのは、どの列が空白なら除外するかを決めることです。数量0の行まで消さないよう、判定する列と条件を分けて考えます。
画像をタップすると拡大
商品名が空白の行を除外する基本の式
サンプルではA9:C14が元データで、A列が商品名です。E9に次の式を入力しています。
- A9:C14：取り出す表全体。
- A9:A14<>””：商品名が空文字ではない行を選ぶ条件。
- 「該当なし」：残る行が1件もない場合の表示。
初期状態では、りんご・みかん・ぶどうの3行が残ります。数量が0のみかんは、商品名が入力されているので残ります。A14に「もも」を入れると、その行も条件を満たし、結果の4行目に加わります。
画像をタップすると拡大
まず動かしてみよう｜この記事のサンプルをその場で試せます
練習用ファイルをダウンロードしなくても、記事と同じデータ・同じセル番地のサンプルをここで動かせます。黄色いセルを書き換えると、結果がその場で変わります。各解説の最後にある「▶ この式を試す」を押すと、このツールの該当セルへ移動します。
完全な空白・数式の空文字・スペースの違い
| セルの状態 | 基本式での扱い | サンプル |
|---|---|---|
| 何も入力していない | 除外する | A10 |
| 数式が空文字列を返す | 除外する | A12の式は =”” |
| スペースだけ入っている | 除外されない | 文字がある扱いになる |
| 数量が数値の0 | 商品名があれば残す | みかんの行 |
「商品名がない行」と「全列が空の行」は違います。サンプルの14行目は数量と備考が入っていますが、商品名が空なので除外されます。業務で必要な行まで消さないように、基準とする列を先に決めてください。
画像をタップすると拡大
複数の必須項目がそろった行だけ残す
商品名と備考の両方が入っていることを条件にするなら、条件どうしを掛け算します。これはAND条件です。
どちらか一方が入っていれば残す場合は、足し算して1つ以上の条件が成立するかを調べます。
後者では、商品名が空でも備考がある14行目が残ります。ただし抽出結果の空セルが0として見える場合があります。行を除外する条件と、残った行の空セルをどう表示するかは別の問題です。条件の考え方はFILTERのAND・OR条件でも解説しています。
▶ 2項目がそろった行だけ残す式（J9）を、練習ツールで試す
スペースだけのセルも除外したい場合
半角スペースや全角スペースが混じっているなら、判定用の列を1列用意すると原因を見つけやすくなります。サンプルをコピーし、空いているI9に次の式を入力してI14までコピーします。
全角スペースを半角に置き換え、TRIMで前後の半角スペースなどを整えた後、文字が残るか調べます。結果はTRUEまたはFALSEです。改行やタブ、Webからコピーした別の空白文字まで、すべて取り除く式ではありません。
E9の式を次の形に置き換えると、判定用の列を使えます。
この方法は抽出条件の確認用です。抽出された商品名そのもののスペースは元のまま残ります。商品名も整えたい場合は、空白を削除する方法で元データの整形を検討してください。
【実機で検証】6種類の「空に見えるセル」で式を比べた結果
文字・本当の空・数式の “”・半角スペース・全角スペース・数値の 0 の 6 行で、どの式で何が残るかを Excel の実機で試しました。
| セルの中身 | <>”” | LEN(TRIM())>0 | <>0 | NOT(ISBLANK()) |
|---|---|---|---|---|
| 文字 | 残る | 残る | 残る | 残る |
| 本当に空 | 除外 | 除外 | 除外 | 除外 |
| 数式の “” | 除外 | 除外 | 残る | 残る |
| 半角スペース | 残る | 除外 | 残る | 残る |
| 全角スペース | 残る | 除外 | 残る | 残る |
| 数値の 0 | 残る | 残る | 除外 | 残る |
スペースも含めて空として除外するなら LEN(TRIM()) です。試した Excel では、TRIM だけで全角スペースも消えました。ただし、UNICHAR(160) で入れた特殊な空白は TRIM で消えませんでした。スペースに見えるのに除外できないときは、この空白を疑います。
抽出結果が広がらない・行が足りないとき
| 症状 | 確認すること |
|---|---|
| #SPILL!／#スピル! | E9から右と下の展開先に、文字や数式、結合セルがないか。 |
| #VALUE! | 抽出範囲と条件範囲の行数が同じか。 |
| 追加した行が出ない | 元データの終端と条件範囲の終端を、同じ最終行まで広げたか。 |
| 空白の行が残る | スペースが入っていないか、別の列で判定していないか。 |
| 必要な数量0の行が消える | 数量>0など、本来の目的と違う条件を指定していないか。 |
Excel 2019・2016で同じことをするには？
FILTER関数は使えないため、見出しを含む表に「データ」→「フィルター」を設定し、商品名の候補から「空白セル」を外します。この方法は表の行を非表示にする操作です。別の場所へ動的な一覧を作るFILTER関数とは更新の仕方が異なります。
サンプルで結果を確かめる
本文のセル位置はダウンロードファイルにそろえています。まず黄色いセルを変更して動きを確認し、次に自分の表に合う範囲へ置き換えてください。
関連する解説・参考資料
基本から確認したいときは、エクセルの空白行を削除する方法もあわせてご覧ください。
