Excel で 1 行または 1 列のデータから 空白 や 0 を除外して、結果を左詰め・上詰めで並べ直したいときは、FILTER 関数を使うのが簡単です。この記事では、空白と 0 をまとめて除外する式 と 空白だけを除外する式 を、行方向・列方向それぞれの形でまとめます。
結論: まず使う式
最初に、よく使う式だけをまとめます。
空白と 0 をまとめて除外する
1 行のデータを左詰めにしたいとき:
=FILTER(A1:F1, (A1:F1<>0)*(A1:F1<>""), "")1 列のデータを上詰めにしたいとき:
=FILTER(A1:A13, (A1:A13<>0)*(A1:A13<>""), "")空白だけを除外する
0 は残し、空白だけを除外したいときは次の式です。
=FILTER(A1:F1, A1:F1<>"", "")=FILTER(A1:A13, A1:A13<>"", "")末尾の第3引数 "" は、すべて除外されて結果が 0 件になったときに空白を返すための指定です。省略すると #CALC! になることがあります。詳しくは FILTER 関数の #CALC! エラー記事 も参考になります。
空白や 0 を除外して左詰め・上詰めにする考え方
FILTER 関数は、条件に一致した値だけを抜き出してスピル表示します。これを 1 行または 1 列に対して使うと、不要な値を除いた結果が自然に左詰め・上詰めの形になります。
Microsoft 公式の FILTER 関数ヘルプでも、FILTER は条件に合う値だけを返す関数であり、何も返さない可能性があるときは第3引数 [if_empty] を使うよう案内されています。今回の用途では、この性質を使って「不要な値を除外した一覧」をそのまま作ります。
サンプルデータ
次のような範囲を例にします。
| 72 | 46 | 0 | 38 | 75 | |
| 57 | 94 | 53 | 0 | 19 | |
| 60 | 16 | 72 | 31 | 34 | |
| 76 | 25 | 26 | 97 | 14 |
このような「0 と空白が混在した 1 行 / 1 列データ」を詰め直したいときに FILTER 関数が向きます。
空白と 0 をまとめて除外する式
1 行を左詰めにする
1 行目を左詰めにするなら次の式です。
=FILTER(A1:F1, (A1:F1<>0)*(A1:F1<>""), "")(A1:F1<>0) で 0 以外、(A1:F1<>"") で空白以外を判定し、両方を満たす値だけを返します。結果はスピルするので、左側から順に詰められた形で表示されます。
1 列を上詰めにする
1 列を上詰めにしたいときは、範囲を縦方向に変えるだけです。
=FILTER(A1:A13, (A1:A13<>0)*(A1:A13<>""), "")この式を使うと、0 と空白を除いた値だけが上から順に並びます。
空白だけを除外して 0 は残す式
0 は意味のある数値なので残したい、空白だけ消したい、という場合は条件を <>"" だけにします。
1 行を左詰めにする
=FILTER(A1:F1, A1:F1<>"", "")1 列を上詰めにする
=FILTER(A1:A13, A1:A13<>"", "")この形なら、0 を含む値はそのまま残ります。`0 も不要か、0 は残したいか` で式を使い分けてください。
使うときの注意点
- FILTER 関数だけで扱いやすいのは、基本的に
1 行または1 列のデータです。 - 複数行・複数列の表全体を一度に同じ形のまま詰め直す用途には、そのままでは向きません。
- すべての値が除外される可能性があるなら、第3引数は省略しない方が安全です。
- 数式セルが
""を返しているケースもあるので、空白判定は<>""を明示した方が分かりやすいです。
まとめ
Excel で空白や 0 を除外して左詰め・上詰めにしたいときは、FILTER 関数で必要な値だけを返す形にすると整理しやすくなります。
- 空白と 0 を除外したい:
=FILTER(範囲,(範囲<>0)*(範囲<>""),"") - 空白だけを除外したい:
=FILTER(範囲,範囲<>"","")
検索意図としては `空白や 0 を除外する方法` が中心なので、まずはこの 2 パターンを使い分けると迷いにくくなります。
コメント