日々のExcel作業で、見た目は空っぽなのに実は数式が入っているセルに悩まされた経験はありませんか。最初は「ちょっとしたテクニックがあれば解決できるかな」と思っていても、貼り付け時に空白セルのはずが上書きされなかったり、スキップされずに混乱したりと、意外と手間がかかります。今回は、そんな「見かけ上の空白」を本当の空白に変える方法や、マクロで大規模に処理するアイデア、ちょっとした裏技などを、私の体験談も交えながら整理してご紹介していきます。
Excelの見かけ上の空白とは
ExcelでIF関数などを使うと、条件が満たされないときにダブルクオートだけを返すパターンがあります。ぱっと見は空のセルに見えるのですが、数式バーを見るとIF関数が入っていたり、値を確認するとダブルクオートが返っていたりします。いわゆる「見かけ上の空白」は、実際にはセルに数式や文字列が存在していて、完全な空白セルとは認識されません。
なぜ問題になるのか
空白セルかと思って「空白スキップ」で貼り付けをしても、きちんとスキップされずに上書きされてしまったり、あるいは逆にスキップしてほしいセルを上書きできなかったりする事態が発生します。私も何度か「空白セルとして扱ってほしかったのに」という場面で戸惑い、一気に効率が落ちた経験があります。IF関数の便利さは捨てがたいのですが、貼り付け特別の場面で痛い目を見がちです。
ISBLANK関数での確認
ISBLANKでセルが空かどうか判定すると、多くの場合FALSEが返ってきます。つまりExcel的には「ダブルクオートを返す数式」が入っているセルは空ではないというわけです。これが「見かけ上の空白」ならではのやっかいなポイントです。
実際の空白セルにする手動での対処
検索と選択で数式を一括削除
「見かけ上の空白」を一気に掃除して、完全に中身のない状態にしたいときは、検索と置換機能を使う方法があります。私が最初にこのテクニックを知ったときは「あ、Excel上で完全に削除できるんだ！」と地味に感動しました。主な手順としては以下の流れになりますが、あえて手順書風に書いてみます。
検索と削除の手順
1. 処理したい範囲を選択して「ホーム」タブから「検索と選択」へ。
2. 「検索する文字列」を空欄に設定する。
3. 「オプション」を開き、「検索場所」を「値」に切り替える。
4. 「すべて検索」をクリックすると、一覧に該当セルが並ぶ。
5. 検索結果をCtrl + Aで全選択。
6. ダイアログを閉じてDeleteキーを押す。
この操作でダブルクオートによる「見かけ上の空白」のセルから数式そのものが取り除かれ、中身が本当に空になります。ただし、この際に必要な数式まで消してしまわないよう注意しましょう。私は大規模な表でやったときに、本当に必要な計算式まで削除してしまい、やり直すハメになりました。
数式を残しつつ本当の空白を欲しい場合の工夫
値貼り付けを活用
数式は消したくないけど、見た目の空白を本当の空白として扱いたい。そんなニーズがあるときは、まず「値貼り付け」を使って数式を文字列に変換する手があります。作業用の別シートや新しい列に、いったん値として貼り付けておくと数式がなくなり、IF関数が返すダブルクオートがそのまま文字列になることがあります。その後、前述の検索と削除のテクニックで空欄文字列を探して削除すれば、完全に中身のないセルにできます。
分割して入力していた数式の整理
複数列にわたってIF関数が細切れに配置されている場合、一列ずつ検証してダブルクオートを空白にするのは面倒です。このとき、あらかじめ別の列に「統合用の数式」を用意してから値貼り付けや検索削除をするのがおすすめです。
私が実際にやった方法として、まず複数のIF関数を結合した大きなIF文を作り、結果を一列にまとめました。その列をほかのシートへコピーして、貼り付け特別で値としてペースト。その後に検索機能を使ってダブルクオートを一掃しました。思った以上に作業時間が短縮できましたよ。
複数条件をまとめるネストIFやIFS関数
ネストIFの活用
複数のIFを組み合わせて、一つのセルでさまざまな条件を判断し、結果を返す方法があります。たとえばこんなイメージです。
=IF(
ISNUMBER(SEARCH("hg", BL3)), "HG",
IF(
ISNUMBER(SEARCH("br", BS3)), "BM",
IF(
ISNUMBER(SEARCH("xxxx", BS4)), "XX",
""
)
)
)最終的にはダブルクオートを返す可能性があるものの、複数の列に散らばる判断ロジックをまとめてしまえば、列を分けて管理しなくて済むケースもあります。これは私がいくつかの検査項目を統合したい時に使った方法で、最終的なセルだけを調整すればよい分、管理がずいぶん楽になりました。
IFS関数を使えるバージョンならもっとシンプル
比較的新しいバージョンのExcelではIFS関数を使えば、ネストが深くなりすぎる問題を多少軽減できます。
例えば、
=IFS(
ISNUMBER(SEARCH("hg", BL3)), "HG",
ISNUMBER(SEARCH("br", BS3)), "BM",
ISNUMBER(SEARCH("xxxx", BS4)), "XX",
TRUE, ""
)のように書けるため、IFのかっこが深くなりにくいメリットがあります。
定義された名前で複雑な数式を再利用
名前の管理を活用
複数行や複数列にまたがる同じロジックを繰り返し利用したいなら、定義された名前に数式を登録するのも便利です。名前の管理画面で数式や範囲を登録しておき、セルには =登録した名前 とだけ入力すれば、同じ処理を手軽に呼び出せます。
相対参照にも対応
定義された名前を作成するとき、参照方式を相対にすれば、貼り付ける行ごとに動的に参照セルを変えることが可能です。私も一度に200行以上のデータを判定する式を作ったときに、この名前の管理を活用してだいぶ時間を短縮しました。
マクロ(VBA)で一括処理する方法
手動操作が大変なときの自動化
大量の「見かけ上の空白」を削除する必要があり、かつ数式部分は保持するなど、条件が複雑になるケースもあるかと思います。そんなときは、VBAマクロを作成して自動化してしまうのも一案です。
マクロのサンプルコード
簡単な例ですが、「選択範囲のセルを順番に確認し、IF関数だけが入っていてダブルクオートしか返していないセル」を空白にするイメージのコードを載せておきます。
Sub RemoveDoubleQuoteBlanks()
Dim c As Range
For Each c In Selection
If c.HasFormula Then
If c.Value = "" And InStr(c.Formula, "IF") > 0 Then
c.ClearContents
End If
End If
Next c
End Sub
このサンプルはあくまで一例なので、実務で使うときは自分の要件に合わせて調整してください。たとえばIF関数の中でもさまざまな条件を返す場合や、セル内容が空かどうかの判定の仕方を変える必要があるかもしれません。私の場合、さらに「特定の列のみ処理する」などの条件分岐を加えて使っていました。
個人用マクロブック(Personal.xlsb)での管理
自分専用の定型作業をどのブックでも使えるようにしたいときは、Excelを起動するときに読み込まれる個人用マクロブック(Personal.xlsb)にマクロを保存すると便利です。私の場合も、今回の「見かけ上の空白を一括削除」マクロをこの個人用マクロブックに入れておき、どのExcelファイルを開いていてもワンクリックで起動できるようにしています。
ユーザー事例とトラブルシューティング
実際の作業シナリオ
あるユーザーは、商品在庫管理表の中で、多岐にわたるステータスをIF関数で表示していました。販売終了の場合には空白を返す仕様だったので、空白セルにだけ「未仕入れデータ」をコピペで上書きしようとしても、空白をスキップせずに貼り付けられてしまい大混乱。結局、まずは別シートへ値貼り付け、そのあと検索と削除で空白セル化することで、ようやく正常に在庫データを取り込めたそうです。
私もデータ分析用に複数ブックから集計を取る作業をしていましたが、引用先のIF関数が返す空白が原因でデータ連携に不備が生じたことがあります。改めて全部チェックするのは大変でしたが、一括削除のマクロを組んでからはだいぶ楽になりました。
表を用いたイメージ
以下のように、数式入りと本当の空白セルが混在していると、空白スキップの挙動がややこしくなることがあります。
| セル | 見た目 | 中身 | ISBLANK? |
|---|---|---|---|
| A1 | (IF関数がダブルクオートを返す) | FALSE | |
| A2 | (Deleteキーで完全削除済) | TRUE | |
| A3 | サンプル文字 | サンプル文字 | FALSE |
見た目はA1とA2どちらも空っぽに見えますが、IF関数の入ったA1は本当の空白ではありません。この差に気づかず作業すると、空白スキップの操作が思ったように動かないことがあります。
ExcelにNULLセル機能がない現状
Excelでは現時点で、セルを本当に空の状態で返す専用関数(いわゆるNULL)は提供されていません。そのため、IF関数で空に見せたいときはダブルクオートで表示を調整するしかありません。もっと直感的な方法があればいいのに、と思いつつも、私もかれこれ十数年ずっとこの方法でやりくりしています。
Microsoftへの要望もあり
空白スキップやデータ集計などで本当の空白が必要になる場面は意外と多いです。もし心からExcelにこの機能があったら便利だと感じている方は、Microsoftへのフィードバックから要望を出すのも選択肢かもしれません。将来的には新しい関数が実装される可能性もあります。
まとめと今後の対処法
「見かけ上の空白」で困る場面に遭遇したら、まずは数式を削除するのか、それとも値だけを残すのか、といった作業手順をはっきりさせることが大事です。あらかじめネストしたIF関数やIFS関数で列を統合してしまうのも一つの手ですし、自動化の必要があればマクロを検討するのも良いでしょう。私自身も、一度仕組みづくりに手間をかけてしまえば、同じ問題に毎回イライラすることはなくなりました。Excelをうまく使いこなして、快適なデータ処理ライフを送っていただければうれしいです。
私も最初は「Excelがなんとかしてくれるでしょ」と思っていましたが、結局は手動の調整か、工夫した数式やマクロを使うしかないと痛感しました。慣れてしまえば逆に自由度が高いとも言えます。
コメント