Excel動的配列でスピル範囲の空白セルを除去する効果的なテクニック

Excel の FILTER で空白を除いたのにスペースが残る場合は、「空白だけの行を結果から除外する処理」と「残す文字列のスペースを整える処理」が混同されているかもしれません。FILTER の条件だけで TRIM を使っても、返す配列が元の範囲なら、その範囲の文字列が返ります。

ここでは Excel for Microsoft 365、Excel 2024・2021 の動的配列を対象に、基本式、スペースの種類の確認、元の値を残す式と加工した値を返す式を説明します。数式で別の場所に結果を表示する方法なので、元データのセルや行そのものは削除しません。

まずは FILTER で空セルと空文字を結果から除外する

A2:A100 にデータがある場合、元の範囲と重ならない空いているセル、たとえば D2 に次の式を入力します。結果が広がる範囲も空けておきます。

=FILTER(A2:A100,A2:A100<>"","")

式の部分	意味
A2:A100	結果として返す元データ（array）。
A2:A100<>""	空文字と等しくない行を残す判定（include）。
最後の ""	条件に合う行がない場合の表示（if_empty）。

この式は、未入力セルや数式が返す空文字 "" を除外する基本形です。半角スペース、全角スペース、改行などが入っているセルは、空文字と同じではありません。見た目が空白でも結果に残る場合は、後述の文字確認へ進みます。

第 3 引数を省略すると、該当するデータがない場合に空配列の #CALC! が発生します。"" は該当なしを空文字で表示する指定であり、数式の入ったセルを物理的な空セルに変える指定ではありません。詳細はMicrosoft の FILTER 関数の仕様を参照してください。

行を絞る場合は、元範囲と条件範囲の高さをそろえる

A 列が空でない行について A～C 列を返すなら、次のようにします。A2:C100 と A2:A100 は列数が違っても、どちらも 2～100 行という同じ高さです。

=FILTER(A2:C100,A2:A100<>"","")

固定範囲の A2:A100 は、101 行目以降を自動で取り込みません。継続して行が増える一覧では、元データを Excel テーブルにし、構造化参照で参照範囲を管理する方法があります。

空白だけの行を除く式と、文字を整える式の違い

半角スペースだけの行を除外し、残す値はそのまま返す

=FILTER(A2:A100,TRIM(A2:A100&"")<>"","")

判定用の文字列だけを TRIM で整え、空になる行を除外します。第 1 引数は A2:A100 のままなので、採用された文字列の前後のスペースは結果にも残ります。&"" は判定用に文字列として扱うための記述です。

結果の半角スペースも整えたい場合は加工後の配列を返す

=LET(
    src,A2:A100,
    cleaned,TRIM(src&""),
    FILTER(cleaned,cleaned<>"","")
)

こちらは文字列の一覧を整える例です。cleaned に加工結果をまとめ、FILTER の第 1 引数にも cleaned を指定します。LET 関数を使うと、元データと途中の計算に名前を付けて、どちらを返しているか確認しやすくなります。

TRIM は前後の半角スペースを取り除き、単語間の連続する半角スペースは 1 個に整えます。すべてのスペースをなくす関数ではありません。次の表の［半角空白］は、説明のために空白の位置を示したものです。

入力の例	元の配列を返す式	加工後を返す式
半角空白だけ	結果から除外	結果から除外
［半角空白］東京［半角空白］	前後の空白を残す	東京
New［半角空白 2 個］York	空白 2 個を残す	New York

加工後の配列は文字列になります。数値・日付が混ざる列や、空白に意味がある識別子には一律に適用せず、元の値を返す式を選ぶか、加工対象の列を限定してください。元セルの表示形式まで FILTER がコピーするわけでもありません。

TRIM で消えない NBSP・全角スペースを確認する

TRIM が対象にする空白は ASCII 値 32 の半角スペースです。Web からコピーした文字に含まれる NBSP（改行しないスペース、Unicode 値 160）は、TRIM 単独では取り除けません。全角スペースも別の文字なので、同じように消えるとは考えないでください。Microsoft の TRIM の注意点にも、NBSP の扱いが明記されています。

見た目が空白でも文字数があるか調べる

=LEN(A2)

結果が 1 以上なら何らかの文字が入っています。0 の場合は未入力セルだけでなく、数式が返した空文字も含みます。LEN の結果だけで「数式もない本当の空セル」とは区別できません。

Unicode の値は UNICODE で調べる

空文字を避けて先頭文字を確認する例です。CODE は環境の文字セットに基づくコードを返すため、Unicode の値を調べる用途では UNICODE を使います。

=IF(A2="","",UNICODE(A2))

2 文字目を調べる場合は、文字数を確認してから MID で取り出します。ほかの位置を調べる際も、実際の文字数以内の位置を指定します。

=IF(LEN(A2)<2,"",UNICODE(MID(A2,2,1)))

Unicode の値	確認する文字
32	半角スペース
160	NBSP（改行しないスペース）
12288	全角スペース
9／10／13	タブ／改行 LF／復帰 CR

UNICODE 関数は文字列の先頭 1 文字のコードポイントを返します。1 回調べただけでセル内のすべての不可視文字を検出したことにはならないため、必要に応じて確認位置を変えます。

必要な文字だけ置換し、元の値を残すか選ぶ

NBSP・全角スペースだけの行を除外する例

次は、NBSP と全角スペースを判定上は半角スペースとして扱う例です。UNICHARで対象文字を明示します。氏名・住所などの内部の空白を勝手に消さないよう、まずは元の値を返します。

=LET(
    src,A2:A100,
    cleaned,TRIM(SUBSTITUTE(SUBSTITUTE(src&"",UNICHAR(160)," "),UNICHAR(12288)," ")),
    FILTER(src,cleaned<>"","")
)

この式は、指定した空白文字だけで構成された行を除外し、残った行については元の値を返します。全角スペースを含む氏名なども、その文字列自体は変更しません。ここで対応しているのは指定した空白文字であり、すべての不可視文字ではありません。

置換・TRIM 後の文字列を出力する例

文字列の前後や内部のスペースを統一してよいことを確認できた場合は、最後の FILTER の第 1 引数を変更します。数値や日付の列を文字列に変えたくない場合は、この加工結果を返す式を使わないでください。

=LET(
    src,A2:A100,
    cleaned,TRIM(SUBSTITUTE(SUBSTITUTE(src&"",UNICHAR(160)," "),UNICHAR(12288)," ")),
    FILTER(cleaned,cleaned<>"","")
)

この例では NBSP と全角スペースが半角に変わり、さらに TRIM によって前後が除かれ、内部の連続する半角スペースが 1 個になります。名前や住所の区切りをすべて詰める処理とは異なりますが、空白の種類に意味があるデータではこの統一も避けます。

変換前と変換後を別の列で見比べ、期待する表記か確認してから利用します。元データを残しておけば、必要だった区切りまで変えていないか確認できます。

CLEAN は改行などに使えるが、万能な除去ではない

CLEAN は ASCII の最初の 32 個の非表示文字（0～31）を取り除きます。NBSP や、Unicode のすべての不可視文字が対象ではありません。Microsoft もデータ整理では TRIM・CLEAN・SUBSTITUTE を使い分けるよう案内しています。

タブや改行を不要な制御文字として削除してよい文字列であれば、補助列で次のように確認できます。

=TRIM(CLEAN(A2))

改行で分かれた語に CLEAN を使うと、その区切りがなくなって語がつながります。改行を区切りとして残したい場合は、確認した改行文字を SUBSTITUTE で半角スペースなどに置換する設計にします。たとえば LF の場合は次の式です。CR やタブを含む場合は、それぞれの文字を調べてから対応を追加します。

=TRIM(SUBSTITUTE(A2,UNICHAR(10)," "))

「見えない文字を全部消す」という方針で、氏名・住所・商品コードの空白を一括削除しないでください。条件判定のための加工と、保存・表示する値の変更を分けることが大切です。

エラーは該当なし・条件・出力場所を分けて調べる

症状	確認すること
#CALC!	条件に合う行がない場合の第 3 引数を指定しているか。ほかの #CALC! の原因と区別する。
#VALUE! など	元の範囲と条件範囲の高さ・幅が合っているか。条件配列にエラーが含まれていないか。
#SPILL!	結果が広がる場所に別の値や数式がないか。出力数式を Excel テーブルの外に置いているか。
#NAME?・未対応の関数表示	利用版が FILTER・LET に対応するか。関数名や名前の綴りも確認する。
詰めた結果にスペースが残る	返す配列が元データか、加工後データか。TRIM の対象外の文字がないか。

条件の include にエラーがあると、FILTER もエラーを返します。IFERROR で式全体を空文字にする前に、元データと判定式のどこでエラーになっているか確認すると、欠損を隠さずに済みます。

スピル先の値は、必要なら別の場所へ移して出力領域を空けます。スピル結果の途中のセルを 1 個ずつ修正するのではなく、左上の数式を編集します。詳しくは動的配列とスピルの動作を参照してください。

Excel 2021・2024 でも使える？

Microsoft の FILTER と LET の適用先には、Microsoft 365 に加えて Excel 2021・2024 とそれぞれの Mac 版が記載されています。「Microsoft 365 専用」とは限りません。Excel 2019・2016 などの旧版では、この記事の FILTER・LET の式をそのまま使える前提にしないでください。

旧版を含む共有先がある場合は、利用する関数の対応を先に確認します。対応しない環境では、補助列で空白判定や文字列加工を行って通常のフィルターで絞る方法など、共有先で扱える手段を選びます。

よくある質問

空白行を除いたのに、文字の前後にスペースが残るのはなぜ？

FILTER の include だけで TRIM を使い、array に元の範囲を指定している可能性があります。文字も整える目的なら、加工した配列を第 1 引数として返します。元の値を守りたい場合は、そのまま返す設計で問題ありません。

結果が空文字なら、集計で空セルとして扱ってよいですか？

空文字を返す数式がある状態と、未入力セルは同じではありません。後続の集計や判定では、使う関数が空文字をどう扱うかも確認してください。

結合してから分割し直す必要はありますか？

空白だけの行を結果から除外する目的なら、元の配列に FILTER を適用すれば処理できます。いったん文字列へ結合する手順を増やすより、残す条件と返す値を明確にした式から始めると確認しやすくなります。

基本式で空文字を除外し、残る文字を調べ、必要な置換だけ追加します。加工後の値を返すかどうかを最後に確認すれば、空白の除外と文字列の変更を取り違えにくくなります。