空白なのに空白じゃないセルを空白にしたい

今回は、空白のように見えて実は見えない値があるというやっかいなセルについて調べました。

見えないけれどいるんだよ

見かけは何もない空白セルのはずなのに、Excel には何かが見えているらしいという奇妙な現象に遭遇することがあります。

COUNTA関数が実際より多い数になったり、空白なのにISBLANK関数がFALSEを返したり、Ctrl+矢印のジャンプで何もないと所で止まるなど、 目には見えないのに確かに何かいるという、まるで幽霊のようなセルです。

時にはこれが、数式や集計を狂わせ、CSVや印刷で大量の空行を吐き出すなどの災いをももたらします。

この気味の悪い幽霊セルを退治する方法はないのでしょうか。

幽霊セルとは

幽霊セルとはどのような現象なのでしょうか。

幽霊セルは、セルに画面上表示されない値が入力されているのが原因でおこるようです。 調べてみると、以下のようないくつかの種類があります。

• スペースのみのセル
• 空文字列のセル
• シングルクォーテーションのみのセル

スペースのみのセル

スペース（空白文字）のみが入力されたセルは、見かけ上、空白セルと区別がつきません。 半角スペース()や全角スペース(　)といったスペースは、文字間の隙間を開けるための文字で、それ自体は目にみえないものだからです。

空文字列のセル

空文字列とはどういうことでしょうか。

普通なら、セルのテキストデータを削除していって文字が１個も失くなれば、セルは空の状態、つまり空白セルになります。 ところがどうにかすると、「文字が1個もない文字列」という変な値を持つセルになってしまうことがあるのです。

このような値を「空文字列」といい、「長さ0の文字列」とか「0文字からなる文字列」とも言い表されます。 文字が１個もなくても、セルの値としてはテキストデータが存在するものとして扱われます。

ではどうしたときに空文字列になってしまうのでしょうか。

よくあるのは、数式セルをコピー＆ペーストした影響です。 たとえば次のようにIF関数などを使った数式をよく見かけます。

=IF(A1="", "", A1*B1)

この数式では、入力のない行の計算結果に0を表示させないよう、A1セルが空のときにあえて空文字列（""）を設定しています。

このように数式で空文字列を設定したセルをコピーし、別のセルに「値のみ貼り付け」をすると、その空文字列（長さ0の文字列）を値として保持するセルができてしまうのです。

シングルクォーテーションのみのセル

シングルクォーテーションマーク（'）（アポストロフィー）はそれ１個だけ入力してもなにも表示されません。 テキストの先頭にある'は特別な意味を持っているからです。

たとえば「0001」のような文字列を普通にセルに入力すると、0が消されて数値データの「1」に変換されてしまいますが、先頭にシングルクォーテーションをつけて「'0001」のように入力すればそれを防ぐことができて、「0001」がテキストデータとして入力されます。 このとき、このシングルクォーテーションはセルのデータとしては無視されます。

つまり、シングルクォーテーションを１個だけ入力したセルの値は、結果的に「長さ0の文字列」、つまり空文字列を入力したことと同じ意味になります。

---

---

幽霊セルを除去するには

幽霊セルを通常の空白セルにするには、Deleteキーでクリア（削除）するだけです。 ただ、幽霊は見えないのでどこに幽霊セルがあるかはすぐにはわかりません。

幽霊セルをあぶり出すには、「選択オプション」で「空白セル」で空白セルのみを選択状態なにします。 選択オプションは「ホーム」タブ⇒「検索と選択」メニュー⇒「ジャンプ」⇒「セル選択」ボタンで開きます。 これで通常の空白セルはグレーの選択状態になりますが、幽霊セルは空白なのに白く抜けたままです。

また、CSVや印刷で無駄な空行が追加されていることもあります。 その場合、シート上でCtrl+Endを押してみてください。 これは、シートの使用範囲の最後のセルを選択するショートカットです。

通常は、表などで使用している範囲のいちばん右下隅のセルが選択されるはずですが、その外の下のほうの何もないところにポツンと選択されたら、そこまでのどこかに Excel にしか見えないセルがあることになります。 そのセルまでを範囲選択でクリアすればOKです。 なぜかそのあと、Ctrl+End をしても選択される位置が変わりませんが、それは Excel が気付いていないだけです。 ブックを上書き保存（Ctrl+S）をするとちゃんと反映されます。

さて、手作業で除去できる程度ならいいのですが、あちこちに数多く出現したり、大きなデータの前処理などをしたい時には、一括で処理できたいところです。

調べると一括処理には以下の2つの方法が使えそうです。 だた、どちらもシート丸ごとはなく列ごとの除去となります。

• 区切り位置を使う
• フラッシュフィルを使う

区切り位置を使う

「区切り位置」を使うと、列ごとに幽霊セルを除去することができます。 幽霊セルを含む列を選択後、区切り位置ウィザードを開いたらそのまま「完了」ボタンを押すだけです。

区切り位置は、列のテキストを区切り文字で分割する機能です。 もし、列のテキストに区切り文字が一つもなかったら実行しても意味はないのですが、その副作用として、空文字列と先頭シングルクォーテーションが除去されるので、今回はそれを利用します。

デフォルトではスペースのみの幽霊セルが除去されません。 「元のデータ形式」で2番目の「スペースによって～」を選択すれば、さらに「半角スペース」のみも空文字列やシングルクォーテーションと一緒に除去できます。 残念ながら「全角スペース」は残ってしまいます。

対象セルが数値データのみの場合、この方法で問題ないでしょう。

対象セルがテキストデータの場合、テキストにカンマやスペースなどの区切り文字が入っていると、分割されてデータが壊されてしまうので注意が必要です。

その場合、「カンマやタブなどの〜」でなら、「次へ」ボタンを押して次の画面の「区切り文字」でチェックを全て外すことで分割を防ぐことができます。

また、先頭シングルクオートでテキストを保護しているデータがある場合も、シングルクォーテーションが削除されてしまい保護が解除されてしまうので、あらかじめセルの表示形式を「文字列」にするなどの対処が必要です。

【手順例】

1. 幽霊セルのある列を選択します
2. 「区切り位置指定」ウィザードを開きます
   • マウス操作：「データ」タブ⇒「区切り位置」ボタン
   • キーアクセス：Alt+A⇒E
3. 必要に応じて「元のデータ形式」を選択します
   • 「カンマやタブなどの〜」⇒ 空文字列、シングルクオートのみを除去
   • 「スペースによって〜」⇒ 半角スペースのみ、空文字列、シングルクオートのみを除去
4. 「完了」ボタンを押します（Enter）

フラッシュフィルを使う

どうしても全角スペースも含めた除去をしたい場合には、「フラッシュフィル」を使ってみてください。

「フラッシュフィル」とは、Excel 2013 からある入力支援機能で、ユーザが示した「お手本」に従って残りの行のデータ抽出や変換を自動で行ってくれる機能です。

単に列のデータをコピーするだけのお手本を用意してフラッシュフィルを実行すれば、結果的に幽霊セルが除去されたデータを得ることができます。

【手順例】

1. 幽霊セルのある列の隣に空列を挿入します
2. 値のあるセルを一つ選び、お手本として隣の空列にコピーします。
3. そのままフラッシュフィルを実行します
   • ショートカット： Ctrl+E
4. 元のデータ列を削除します

半角スペース、空白文字列、シングルクォーテーションのほか、全角スペースも除去されたデータがコピーできていると思います。

フラッシュフィルは便利ですが慣れていないと思うように抽出できないかもしれません。 たとえばお手本があいまいだと、別の列の値がコピーされてしまったり、データの一部が壊れることがあります。 その時はさらにお手本を追加してみるか、一時的に空列を両側に挿入して表を分断することで引用範囲を狭めておくと改善されます。 また、数値もテキストのように扱われることも注意しましょう。

またこの方法でも、先頭シングルクオートによるテキスト保護が解除されてしまう副作用があるので、そのような列では区切り位置と同様の対処が必要です。　

---

---

幽霊セルを除去するマクロ

これまで紹介した区切り位置やフラッシュフィルを使う方法は、列ごとの処理となるので、シート上のデータ全体をきれいにしたいときには何回も繰り返す必要があります。

そこでシート全体の幽霊セルを一括で除去するのに使えるマクロをいくつか作成してみました。

今回用意したマクロは以下の3つです。

• シートの空文字列を除去
• テキスト置換_スペースのみの文字列を削除
• セルのクリア_選択範囲以外をクリア

「シートの空文字列を除去」マクロは実行すると、シート上の「空白文字列」と「シングルクォーテーションのみ」を一括除去します。「スペースのみ」は除去しません。

通常のデータや数式は影響を受けません。先頭シングルクオートによるテキスト保護は解除されるので注意してください。

本マクロは現在開いているシートを処理対象としますが、シートの複数選択により、複数シートを一括で処理することもできます。

「テキスト置換_スペースのみの文字列を削除」マクロを実行すると、選択範囲内にある「スペースのみ」の文字列を削除します。

テキスト置換により「半角スペース」と「全角スペース」の両方を削除するものです。 また結果的に「空白文字列」と「シングルクォーテーションのみ」も除去されます。

通常のデータや数式は影響を受けずそのままですが、先頭シングルクオートによるテキスト保護は解除されるので注意してください。

「セルのクリア_選択範囲以外をクリア」マクロは選択範囲の外側にある全てのセルを一括でクリアします。

空行などデータ範囲の周囲にゴミデータある場合に使ってみてください。 セルの内容（データ）だけでなく、書式やスタイル等もクリアされます。

選択範囲を間違えると、必要なデータまでけ消されて元に戻せませんので慎重にして実行してください。

Option Explicit

Sub シートの空文字列を除去()
    Dim sht As Object
    For Each sht In ActiveWindow.SelectedSheets
        If TypeName(sht) = "Worksheet" Then
            sht.UsedRange.Formula = sht.UsedRange.Formula
        End If
    Next
End Sub

Sub テキスト置換_スペースのみの文字列を削除()
    If TypeName(Selection) <> "Range" Then Beep: Exit Sub
    Dim rng As Range
    Set rng = Intersect(Selection, ActiveSheet.UsedRange)
    If rng Is Nothing Then Exit Sub
    
    Application.ScreenUpdating = False
    Call rangeTextReplaceAll(rng, "^( |　)+$", "")
    Application.ScreenUpdating = True
End Sub

Private Sub rangeTextReplaceAll(rng As Range, rePattern As String, reReplace As String)
    Dim re As Object
    Set re = CreateObject("VBScript.RegExp")
    re.Global = True
    re.Pattern = rePattern
    
    Dim col As Range
    For Each col In rng.Columns
        Dim vals As Variant
        If col.Cells.Count > 1 Then
            vals = col.Formula
        Else
            vals = col.Resize(, 2).Formula
        End If
        
        Dim i As Long
        For i = 1 To col.Rows.Count
            If Left(vals(i, 1), 1) <> "=" Then
                vals(i, 1) = re.Replace(vals(i, 1), reReplace)
            End If
        Next
        col.Formula = vals
    Next
End Sub

Sub セルのクリア_選択範囲以外をクリア()
    If TypeName(Selection) <> "Range" Then Beep: Exit Sub
    If 1 = Selection.Cells.CountLarge Then Beep: Exit Sub
    
    Application.ScreenUpdating = False
    Dim diff As Range
    Set diff = rangeDiff(ActiveSheet.UsedRange, Selection)
    If Not diff Is Nothing Then
        diff.Clear
    End If
    Application.ScreenUpdating = True
End Sub

Private Function rangeDiff(rng1 As Range, rng2 As Range) As Range
    Dim tmp As Range
    Set tmp = Intersect(rng1, rng2)
    If tmp Is Nothing Then Set rangeDiff = rng1: Exit Function
    If tmp.Address = rng1.Address Then Set rangeDiff = Nothing: Exit Function
    
    With ActiveWorkbook.Worksheets.Add
        .Range(rng1.Address).Value = 1
        .Range(rng2.Address).Clear
        On Error Resume Next
        Set rangeDiff = rng1.Worksheet.Range( _
            .Range(rng1.Address).SpecialCells(xlCellTypeConstants).Address)
        On Error GoTo 0
        Application.DisplayAlerts = False
        .Delete
        Application.DisplayAlerts = True
    End With
End Function

【使い方】

1. 新規モジュールを開き上記VBAプログラムをコピー＆ペーストします
   • 処理対象とは別の新規ブックに用意することをお勧めします
2. 処理対象を選択します
   • 「シートの空文字列を除去」マクロ ⇒ 対象シート
   • 「テキスト置換_スペースのみの文字列を削除」マクロ ⇒ 対象セル範囲を範囲選択
   • 「セルのクリア_選択範囲以外をクリア」マクロ ⇒ データのある（クリアしない）セル範囲を範囲選択
3. マクロを実行します

【注意】 マクロの実行結果を「元に戻す（Undo）」することはできません。 必ず、あらかじめバックアップを取ってから実行するようにしてください。 また、本記事のマクロの不具合や誤操作、意図しない動作結果などでデータが壊れたり失われることによる、いかなる損害や損失についても、筆者は責任を負いかねますのでご了承ください。

2020/20/25 修正 「セルのクリア_選択範囲以外をクリア」マクロにあった不具合を修正しました。データ範囲より大きな範囲を選択するとデータが失われる可能性がありました。

まとめ

本記事では空白に見えて空白ではない幽霊セルについて調べました。

幽霊セルは結局、意味のない無効な値、つまりゴミデータによるものでした。 表示だけならそのままでも問題にはなりませんが、シートを統計計算やデータ分析の元データと使うときには不可解なエラーの原因となるやっかいな存在で、あらかじめ除去しておく必要があります。

データの集計や分析には前処理という工程がどうしても欠かせないものですが、Excel データではそのさらに前処理が必要になるというわけです。

Excel を使っているというだけで余計な仕事が増えるというのも困りものですが、今回紹介したノウハウで少しでもその手間が軽減されればと思います。

本記事は Windows 10 の Excel 2013 で動作検証しました。

関連記事

www.shegolab.jp

www.shegolab.jp

Excel で日本語をふりがななしで並べ替えたい

今回の記事では、日本語の並べ替えでふりがなを使わない方法について調べます。

日本語の並び順がヘン

日本語データを Excel で並べ替え（ソート）したときに、並び順が微妙に揃わなくて困ったことはありませんか。

たとえば氏名や会社名などを昇順や降順で並べ替えをすると、同じ文字なのに飛びとびになったり、表によって並び位置が食い違っていたりします。 特に漢字のある日本語データ列でおかしなことになるようです。

データ内容の抜け漏れをチェックしたいときなど、これでは使い物になりません。 Excel で日本語の並び順を一定にすることはできないのでしょうか。

応答なし中でも Excel を使いたい

今回は、Excel が応答なしになって作業ができない状態でも、Excelを使えるようにする方法を紹介します。

仕事もフリーズ

Excel が固まってなにも反応せず、応答なしになってしまうことがよくあります。

青いわっかカーソルがクルクル回りっぱなしのまま、Excel 画面が白っぽくなると、フリーズしてもうなにも操作できません。 あとは強制終了するのみです。

Excel ヘビーユーザーのみなさんなら、しょっちゅう経験していることかと思います。

ただ、Excel が応答なしになったからといって、必ずしも異常があってフリーズしたとは限りません。

単に処理に時間がかかっているだけかもしれません。 たとえば、非常に大きなデータを読み込んだり、大量のセルを処理するマクロを走らせたときも応答なし状態なります。

その場合処理の終了を待つだけです。

待つのはいいとしても、問題は、いったんこうなると Excel で何も操作できなくなることです。 同時に開いていた別の Excel ウィンドウも道連れにフリーズされてしまい、見ることさえできません。 処理待ち中に別の作業を進めたくても Excel が使えないことには仕事にならずイライラが募ります。 青いクルクルを見つめるのも恨めしく、もう家に帰りたくなります。

Excel のセルを分割したい【フラッシュフィル編】

今回は、フラッシュフィルを使ってセルの内容を分割したり結合する方法を紹介します。

残念ながら

残念ながら Excel で本当にセルを分割することはできません（WordやPowerPointの表ならできるのですが・・・）。

やりたいのは、セルに入力されているテキストを切り分けて、別々のセルに入れたいということです。 具体的には、名簿の表の「氏名」が入力されている列を、「姓」と「名前」の列に分けたいときなどです。

もちろん手作業で１個ずつカット＆ペーストでやれなこともないですが、件数が多いと大変です。Excel なら何かいいやり方があるはずです。

セルの内容がカンマやスペースのような区切り文字のあるテキストである場合、一括で分割する方法がいくつか知られています。

それらのうち今回は特に「フラッシュフィル」を使うやり方を紹介します。 フラッシュフィルは、分割だけでなく、いろいろな入力の効率化ができる高度な機能なのですが、あまり詳しい情報がネットに見当たらないからです。

本記事では、「フラッシュフィル」がどういう機能で、何ができるのか基本の紹介も含めて、深堀していきたいと思います。

保護ビューは解除しても大丈夫なのか

今回は、Excel ファイルを開いたときによく表示される黄色い警告「保護ビュー」とはいったい何なのか、これを解除しても問題ないのか、調べてみました。

黄色いメッセージ

最近、 Excel ファイルを開いたときに、「保護ビュー」とかいう黄色いメッセージバーに出くわすことが増えているような気がしませんか。

保護ビューになってしまうと、シート編集もコマンドもブロックされて何もできません。

「編集を有効にする」ボタンを押せば解除できるのですが、意味の分からない不穏なメッセージにちょっと勇気がいります。

とは言え、そのままでは仕事にならないので、結局、ボタンを押すしかありません。

余分な操作をさせられるうえに、知らない自己責任を押し付けられたようで心理的にも余計な負担です。

この「保護ビュー」とはいったい何なのでしょうか。そのまま解除しても大丈夫なのでしょうか。

セキュリティに詳しいわけではありませんが、調べて理解できた範囲で解説します。