【QuickTips 33】Googleスプレッドシートで 空白が置換出来ない？時の対策と作業列なしで上の値で空白を埋める方法

シンプルでちょっと便利な Quick Tipsシリーズです。これまでのTipsをマガジンにまとめてます。

先週のnoteが Googleスプレッドシート、Googleドキュメント、Googleスライドの「検索」を検証したものだったので、

この流れでGoogleスプレッドシートの

• 空白を置換する（空白セルを埋める）方法

• 空白の置換が出来ない理由と対策

• 作業列なしで上の値で空白セルを埋める方法

これらのTipsを紹介します。

Googleスプレッドシートで空白を置換する（空白セルを埋める）

👆こんな表があった時に、空白セルのままだと「記入漏れ」なのか「値なし」なのかわからないから「空白セルを 0 や - で埋めて欲しい」と言われることがあります。

2ヵ所程度なら手作業で十分ですが、大きな表で数十セル、数百セルの空白セルを埋めるとなると 仕組みで対応したいですよね。

Googleスプレッドシートは、正規表現で空白セルを置換できる

こんな時に使えるのが「検索と置換」で正規表現を使って、空白セルを特定の値に置換する方法です。

こうすることで

空白セルを特定の値で埋めることが出来ます。

Googleスプレッドシートは 「検索」が未入力だと検索も置換も出来ない

正規表現の ^ は行の先頭を、 $ は 行の末尾を意味します。

^$ とすることで

行の先頭と末尾の間に何もない = 空白（スプレッドシートだと空白セル）

という意味合いになります。

※ ^$は、空文字が入ったセルも空白セルと同じと見なします。

インストール版のExcelでは 検索と置換で、「検索する文字列」に

何もいれないことで、空白セル（空文字のセル）を検索・置換できます。（Web版Excelは残念ながら 空白セルの検索、ジャンプ機能で空白セルを選択する、どちらも出来ません）

しかし、Googleスプレッドシートでは「検索」に何も入れないままだと、

検索、置換、すべて置換のボタンがグレーアウトして押すことが出来ません。

その為この、正規表現を使った空白を検索するテクニックが必須となります。

検索範囲に注意する（必ず先に範囲を選択しておく）

Googleスプレッドシートの検索と置換のダイアログは、「検索」と書かれた項目が2つあります。

なんで？？

となりますよね。

この辺りがGoogleらしいダメなところなんですが、下の「検索」は「検索範囲」を意味します。

元々は（英語だと）

上は Find で 下は Search となっています。（まぁ確かにどちらも「検索」ですが。。）

で、注意すべきは下の検索（検索範囲）の初期値が「すべてのシート」となっている点。

このまま空白埋めの置換をしてしまうと、表範囲以外の空白セルや 他のシートの空白セルまで 置換されてしまって、顔面蒼白となります。

落ち着いて「元に戻す」 Ctrl + Zをすればいいんですが、不慣れな人は慌ててブラウザの「戻る」を押してしまったりして、非常に面倒なことになります。

これを防止する意味でも必ず先に対象範囲を選択してから、検索と置換を立ち上げるようにしましょう！

2セル以上のセル範囲を選択した状態で「検索と置換」を開けば、

選択した範囲を 検索範囲とした状態で立ち上がります。

空白の置換が出来ない理由と対策

Googleスプレッドシートで 空白セルを特定の値で埋めるテクニック（正規表現の ^$を使う方法）を紹介しましたが、上の手順通りにやったのに、

一致するものはありません

と表示されてしまい、なぜかうまくいかないケースがあります。

👆上の画像だと範囲に2ヵ所、空白セルがあります。でも、これらが 正規表現 ^$でヒットしません。

一切触れていないセルは検索対象外となる

空白セルが検索ヒットしない、この理由は

👆コレです。

一切触れていないセルとは、

何か入力した
フォントや書式（太字など）を変更した
罫線を引いた
セルを塗りつぶしした

これらが一度もないセルを指します。セルの幅や高さ変更は該当しません。

これに関しては AIは誤った回答を返すことが多く、あまり役に立ちません。

このAIの回答はどちらも誤りで、

Googleスプレッドシートの「検索と置換」は、完全に未入力のセル = 真の空白セル（null）でも、検索対象として扱います！

ただし一切触れてないセルを検索範囲から除外する仕様

ってことです。

空白の置換をする為にすべきこと（対策）

セル範囲に対してなにかしらの加工をするといっても、色や太字は範囲内で特定のセルで使ってる場合は、一括で変えるのは避けたいですし、フォントも複数使ってると一括設定するのはちょっと・・・ってなりますよね。

影響がほぼ無い操作としておススメなのが、

テキストの折り返し

もしくは

垂直方向の配置です。

どちらも一度違う設定に変えてから、再度元の設定に変えればOK。

この2つは初期値のまま使っていることが多い、もしくは範囲全体で同じ設定としていることが多いので、影響が少ないかと思います。

実際に効果があることを見てみましょう。

範囲内の空白セルは、最初に「すべて置換」とした時は「一致するものはありません」となります。

しかし、一度 範囲に対して「テキストを切り詰める」として、すぐに「テキストをはみ出す」に戻すことで、選択範囲（表内のすべてのセル）が一度触れたことのあるセルとなり、同じ条件で再度「すべて置換」すると空白セル2件がヒットし 0に置換されました。

ただし 一度変更して元の設定に戻す際に、Ctrl+Z（元に戻す）を使ってしまうと、触れたこと自体が無かったことになってしまうので、これはNGです。

また、「交互の背景色」を設定していたり、テーブルだったり、罫線をみっちり引いた表であれば、一度触れたことのあるという条件を満たしているので、こういった対策は必要ありません。

空白セルを特定の値で埋める方法、うまくいかない時の対処法、わかりましたでしょうか？

作業列なしで上の値で空白セルを埋める方法

最後にこの空白置換を使った応用技、作業列なしで上の値で空白セルを埋める方法を紹介します。

見栄え重視で 👆 こんな風にカテゴリごとの先頭行にのみ値が入っていたり、セル結合を使用している表を、集計する為に上の値で空白埋めがしたい！

こんな時、皆さんならどうしますか？

基本は作業列を使って数式で対応

作業列を用意して数式で処理するって人が多いんじゃないでしょうか？

👆詳しくは過去のnoteで触れていますが、基本は

こんな式を作業用のE列、 E2に入れて下にフィルコピーするか

シート関数に詳しい場合は、

SCAN関数を使って1つの式で処理する、なんて人もいるかもしれません。

でも、諸事情で作業列が使えない、作業列を使いたくないという場合は、どうすればよいでしょうか？

（普通に A列の値を別のスプレッドシートにコピペして、作業してから値貼付けで戻すってのもアリなんですがｗ）

検索と置換は数式に置換も出来る

検索と置換は、置換後の文字列を =から始まる数式とすることで、空白セルに数式を一括で入れることが出来ます。

しかし 👆の場合、空白セル A3に入れる式 =A2（一つ上のセルを参照）としても、フィルコピーと違って勝手に相対参照はされないので、

A4セル、A5セル、A6セル は 空白が =A2 に置換され、 aと表示されるので問題ありませんが、

A8セル、A9セル、A12セル・・・ これらも 全て =A2 となり aと表示されしまいます。

普通に数式を入れただけでは、検索と置換は相対参照の動きにはなりません。

式を入れたセルを起点とできる INDIRECT関数

これを解決するのが INDIRECT関数です。

INDIRECT関数といえば、文字列で書いたセル参照、例えば "シート3! B5" という文字列を参照として機能させる関数ってイメージですよね。

しかし、もう一つ INDIRECT関数の第2引数を FALSE指定することで、 R1C1表記でセルを参照する機能があります。

👆 R5C2 は、5行2列目という指定、つまり B5セルと同じ意味合いです。

で、ここからもう一歩踏み込むんですが、公式のINDIRECT関数のページには記載されていませんが、R1C1参照には式をいれた自分自身のセルを起点として その一つ上や一つ下を OFFSET参照する記述法があります。

E4セルに入れた数式で B5セルを参照したい場合は、

式を入れたセルの 1つ下（正の方向）に1行、3つ左（負の方向）に -3列 移動したセルを参照するってことなので、

こんな式になります。

これが空白を一つ上のセルで埋める数式への一括置換に利用できます！

検索と置換で上の値で空白セルを埋める

空白セルを上の値で埋める手順です。

1. 空白セルを上の値で埋めたい範囲を選択

2. Ctrl+H で検索と置換を立ち上げる

3. 検索： ^$

4. 置換後の文字列： =INDIRECT("R[-1]C",FALSE)

5. ✅正規表現を使用した検索にチェックを入れる

6. 「すべて置換」をクリック

ポイントは置換後の文字列を INDIRECTのR1C1参照を使った数式とすることで、

同じ式で、常に式を入れたセルの一つ上を相対参照させるという点です。

作業列なし「検索と置換」一発で 空白セルを上の値で埋めることが出来ました。

ただしこれをやると、揮発性の INDIRECT関数が大量に入った状態となる為、シートが重くなる可能性があります。

置換後に Ctrl+C → Ctrl+Shift+V でINDIRECTの数式を値にしておくのがおススメです。

【オマケ】「Geminiで列を補完」でも上の値で空白埋めは出来る

実は スプレッドシートで Geminiが利用できる環境（Gemini in Sheets が利用可能）であれば、「Geminiで列を補完」を使って、さらに簡単に 空白セルを上の値で埋める が出来ます。

実際にやってみた動画はこんな感じ 👇

空白セルを 

というAI関数で埋めてくれて、AIが「ああ、上の値で埋めたいってことね」と察してくれます。

非常に簡単ではありますが、

• AI関数は一度に実行できる上限が最大200セル

• 出力されるまで結構時間がかかる

• 上の値で埋まっていない（AIが間違える）リスクがある

のを考えると、大量データやクリティカルなデータでは使用に不安があります。

大事なデータ、大量データほど この「検索と置換」を使った 空白セルを上の値で埋めるTipsが役立つんじゃないでしょうか？

今回のQuickTips関連note

今回は Googleスプレッドシートで

• 空白を置換する（空白セルを埋める）方法

• 空白の置換が出来ない理由と対策

• 作業列なしで上の値で空白セルを埋める方法

を紹介しました。

実はこのネタ過去にもnoteで触れてます。焼き直しってわけではないんですが・・・

Googleスプレッドシートの「検索と置換」の超絶テクニックをさらに学びたい方は 👇こちらのマガジンをチェック！

Geminiで補完、AI関数で出来ることをもっと知りたい方は 👇こちらの note

SCAN関数、REDUCE関数については登場した時に紹介したnote

置換を失敗したら、とりあえず Ctrl+Zで戻しましょう。