【Googleスプレッドシート時短術】19,767個の空白セルを3秒で「0」にする小技

2021.01.06

突然ですが、こんなことはないでしょうか。

「売上数の平均を出したいけど、売上数が「0」と入力されている場合と、空欄になっている場合だと、AVERAGE関数の計算結果が異なる。」

上記の表だと、2行目の計算結果は「4.57」、3行目の計算結果は「4.00」になっています。

AVERAGE関数は、参照範囲の数値を足し合わせてから、データの個数で割ってくれます。表のように、売上データは8日分あっても、「12/4」の空白セルの分はデータの個数としてカウントされません。すなわち、「=AVERAGE(B2:I2)」は8日分の売上データを「7」で割った計算結果になってしまっています。こんなときは、「12/4」の空白セルに「0」を入力するか、「=SUM(B2:I2)/8」のように、絶対に「8」で割る計算式を作ると解決できます。

しかし、データ数が多くなってくると、ちょっとした変更が大変になりますよね。実際、私は以下のような「18列×2,840行」の表を目の前にして、途方に暮れてしまいました…。空白セルを数えてみたら「19,767」個あります。フィルターをかけて、1列ずつ空白セルに「0」を入力するのもありですが…もっと別の方法はないものだろうかと思いました。

そこで! 空白セルを一気に「0」に替えることにしました。手順は次のとおりです。

  1. command + F を押して、検索ウィンドウを開く
  2. 検索ウィンドウの「その他のオプション」(3点アイコン)をクリックする。
  3. 「検索」の欄に「$^」を入力する。
  4. 「置換後の文字列」の欄に「0」を入力する。
  5. 「検索」のプルダウンから検索したい範囲を選択する。
  6. 「正規表現を使用した検索」にチェックを入れる(自動的に「大文字と小文字の区別」にもチェックが入ります)。
  7. 「すべて置換」をクリックする。

(1.command + F を押して、検索ウィンドウを開く)

(2. 検索ウィンドウの「その他のオプション」(3点アイコン)をクリックする。)

(3.「検索」の欄に「$^」を入力する。)

(4. 「置換後の文字列」の欄に「0」を入力する。)

(5. 「検索」のプルダウンから検索したい範囲を選択する。)

(6. 「正規表現を使用した検索」にチェックを入れる(自動的に「大文字と小文字の区別」にもチェックが入ります)。)

(7. 「すべて置換」をクリックする。)

これで、空白セルに「0」が入力されます。

この方法を使えば、先ほどの19,767個の空白セルも…

3秒で「0」の入力が完了します。

今回は、3秒で「0」が入力されましたが、データ数や検索するシートが多いと、「0」を入力し終わるまでに時間がかかることがあります。また、もしこの方法で空白セルが「0」に変わらないときは、「0」を入力したい範囲を選択して、書式を「自動」に揃えてから実行してみてください。

処理時間がかかったとしても、空白セルに自分で「0」を入力するよりは手間と作業時間が省けると思います。ぜひ使ってみてください

「あさすけ先生の、超役に立つGoogleスプレッドシート解説」関連記事

 
【スプレッドシート時短術】19,767個の空白セルを3秒で「0」にする小技(本記事)
計算式のコピペ漏れを防ぐ! 気難しそうに見えて案外シンプルないいヤツ 〜ARRAYFORMULA関数〜
VLOOKUP関数のエラー原因を探すのに2時間もかかっていた私が、いろいろな関数を使いこなせるようになったコツを考えてみた。
Googleスプレッドシートに週ごとにタスクをまとめて、その週の未完了タスク・完了タスク・タスクの達成率がひと目でわかるようにする
IMPORTRANGE関数で休日設定シートを転記すればすべてが丸く収まった
値ペーストと書式ペーストを使いこなすと作業が格段に速くなる – エクセルやGoogleスプレッドシート
Googleスプレッドシートでデータの結合・分割の幅を広げたい。改行するなら CHAR(10) を使おう!
「$」マークを使いこなして表をパパッと作る 〜相対参照と絶対参照の復習〜
わざわざ使いたい、転置して貼り付けができるGoogleスプレッドシートの「TRANSPOSE関数」
地味にはまっている、GoogleスプレッドシートでUNIQUE関数とSORT関数で入力規則(プルダウン)用のデータリストをつくる方法
Googleスプレッドシートのフィルタ表示機能が便利
Googleスプレッドシートでチェックリストを5分で作ってみる
営業日の異なる日本と中国のスケジュール作成ツールをGoogleスプレッドシートで作ってみた

このブログを書いたスタッフ

経営企画

あさすけ

とてつもない悩み性であったが、この会社に入ってから「死なない! 大丈夫!」が合い言葉に。日々、色々な人に助けられながら元気に働いている。透き通った歌声の、トリニティの看板娘。

あさすけのブログ一覧

コメントを投稿

ログイン

登録

会員登録せず購入する

ログインせずに投稿する場合には名前とメールアドレスを入力してください。


管理者の承認後、コメントが表示されます。

カテゴリー

コメント

このページをシェアする

ブランドから探す

機種から探す

  • Tablets
  • Smartphones
  • Laptop
  • AirPods
  • Apple Watch
  • iPad
  • iPhone

iPhoneから探す

iPadから探す

iPodから探す

Apple Watchから探す

Laptopから探す

AirPodsから探す

Smartphoneから探す

Walkmanから探す

Tabletsから探す

カテゴリから探す