Excelでデータを扱っていると、
・空白セルを含む行を除外したい
・対象範囲の途中に空白行がある場合、その行を除外したい
というケースに直面することはありませんか？
COUNTBLANKやCOUNTAだけではうまく判定できず、VBAを使うほどでもない…
そんな悩みを解決できるのが、
FILTER × BYROW × LAMBDA × COUNTBLANK 関数の組み合わせです。
この記事では、範囲途中の空白を除外しつつ、有効な行だけを判定・抽出する方法を、図解でわかりやすく解説します。
- COUNTBLANK/BYROW/LAMBDA関数の基本構文と使い方
- 空白セルを含む行を除外する方法
- 対象範囲途中にある空白行を除外する方法
範囲途中の空白行を除外したい場面とは？
実務では、次のようなデータがよくあります。
- 入力途中の表（未入力セルが混在）
- フォーム出力やCSV取込データ
- 将来入力予定の列が空白のまま残っている表
このような表で、
- 完全な空行だけ除外したい
- 空白セルがある行を除外したい（もしくはそれだけ抽出したい）
という要件は非常に多いです。
しかし、従来の関数ではこれを正確に判定するのが困難でした。
COUNTBLANK関数で空白判定は可能だが、手間がかかる
範囲途中の空白行を除外するためには、COUNTBLANK関数を使用する必要があります。
COUNTBLANK関数は、セル範囲にある空白セルの数をカウントする関数です。
COUNTBLANK関数の構文は以下の通りです。
「range」部分でセル範囲を指定することにより、その範囲にある空白セルをカウントすることができます。
=COUNTBLANK(range)COUNTBLANK関数のみで空白行（正確には空白セル数）を判定することは可能です。
以下の通り、対象範囲行でCOUNTBLANK関数を計算すると、空白セル数がカウントされるので、1以上の行が空白セルを含む行となります。
ですが、
・判定用の列を新しく追加しないといけない
・フィルターをかける必要がある
と、少し手間がかかってしまいます。データによっては加工することが難しいケースもありますよね。
ここで活躍するのが、COUNTBLANK関数に加え、FILTER×BYROW×LAMBDA関数を組み合わせる方法です。
BYROW×LAMBDA関数の説明・構文
見慣れない関数が出てきたと思いますので、一つずつ解説していきます。
BYROW関数の説明・構文
BYROW関数は、lambdaを各行に適用し、結果の配列を返します。
BYROW関数の構文は以下の通りです。
=BYROW(array,lambda(row))それぞれの引数の説明は以下の通りです。
LAMBDA関数については別途解説します。
| 引数 | 内容 |
|---|---|
| array | 範囲を指定 |
| lambda | 行を 1 つのパラメーターとして受け取り、1 つの結果を計算するLAMBDA |
| row | 配列からの行 |
LAMBDA関数の説明・構文
LAMBDA関数は、カンタンに言うとExcelの中に自分専用の小さな関数を作れる仕組みです。
同じ計算を何度も書くのが面倒だから、名前を付けて使い回すイメージの関数です。
LAMBDA関数の構文は以下の通りです。
=LAMBDA（parameter,calculation)それぞれの引数の説明は以下の通りです。
| 引数 | 内容 | 詳細 |
|---|---|---|
| parameter | セル参照、文字列、数値などの関数に渡す値 | 最大253個のパラメータを設定可能 ※省略可能 |
| calculation | 関数の結果として実行して返す数式 |
Excel範囲途中の空白セルを含む行を除外する方法
今回使用する関数を説明しましたが、これではイメージがわかないですよね。
次からは、イメージしやすいよう図解で説明していきます。
BYROW関数で空白セルがあるか、行ごとに判定する
まずはBYROW関数を使い、範囲を1行ずつ処理していきます。
その場合の基本構文は以下の通りです。
=BYROW(範囲, LAMBDA(行, 処理))例えば、以下のようなリストがあり、空白セルを含む行のみを除外したいとします。
この場合、以下の数式で空白セルを含むかどうかを配列で判定することができます。
=BYROW(A2:E14,LAMBDA(r,COUNTBLANK(r)=0))この計算がどのような結果を返すか、G列に追記してみました。
以下のように、空白セルが一つもない（COUNTBLANK(r)=0)場合、TRUEを返していることが確認できます。
この数式を説明すると、
・BYROW関数で、2行目～14行目を行ごとにチェックしている
・LAMBDA関数で、行ごとのデータ（r）に空白セルが1つもなければ「TRUE」を返す
という処理を行っているということになります。
- 「r」は変数のため、「x」や「row」などで記載してもOKです。
FILTER関数と組み合わせて、有効データのみ抽出する
判定できたら、次はFILTER関数と組み合わせます。
FILTER関数については、以下の記事で詳しく解説していますので、こちらもぜひご覧ください。
先ほど作成したBYROW関数を、FILTER関数で囲うだけで有効データのみ抽出することができます。
=FILTER(A2:E14,BYROW(A2:E14,LAMBDA(r,COUNTBLANK(r)=0)))結果、空白セルを含んだ行が除外されたリストが表示されているのが確認できますね。
この組み合わせの良いところは、
・VBAのような複雑な作業は不要
・COUNTBLANK関数のみのときのように、補助列を作る必要がない
・動的配列なので、空白セルの増減に合わせ、表示されるリストも自動更新される
という、非常に実用的な方法です。
試しに空白セルを増やしてみると、以下の通りちゃんとリストが自動更新されています。
空白行のみを除外する方法
上記では、空白セルを含む行を除外する方法を紹介しました。
では、完全に空白行のみを除外したい！という場合はどのようにしたらよいか？
その場合は、先ほどの数式を以下のように変更します。
=FILTER(A2:E14,BYROW(A2:E14,LAMBDA(r,COUNTBLANK(r)<>COLUMNS(r))))こうすることで、空白行のみを除外することができます。
対象行の空白セル数（COUNTBLANK(r))が、対象行の列数（COLUMNS(r))と一致しなければ「TRUE」を返す、という意味ですね。
（空白行であれば、空白セル数と列数が一致するはずなので）
逆に空白行のみ抽出したい場合は、「<>」を「=」に変更するだけでOKです。
=FILTER(A2:E14,BYROW(A2:E14,LAMBDA(r,COUNTBLANK(r)=COLUMNS(r))))
状況に応じて使い分けしてみてください。
まとめ
今回は、FILTER × BYROW × LAMBDA × COUNTBLANK 関数を使って、
- 範囲途中の空白セルを含む行を除外
- 完全な空行のみを除外
- 完全な空行のみを抽出
する方法を紹介しました。
このテクニックは、TRIMRANGE関数で手が届かないデータ途中の空白にも対応でき、とても応用が効く方法です。
「COUNTBLANK関数のみでは物足りない」、「TRIMRANGE関数で対応できない」と感じていた方は、ぜひこの方法を試してみてください。
最後まで読んでいただき、ありがとうございました。
コメント