- 9月
- 2024年10月
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」に替えることにしました。手順は次のとおりです。
- command + F を押して、検索ウィンドウを開く
- 検索ウィンドウの「その他のオプション」(3点アイコン)をクリックする。
- 「検索」の欄に「$^」を入力する。
- 「置換後の文字列」の欄に「0」を入力する。
- 「検索」のプルダウンから検索したい範囲を選択する。
- 「正規表現を使用した検索」にチェックを入れる(自動的に「大文字と小文字の区別」にもチェックが入ります)。
- 「すべて置換」をクリックする。
(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スプレッドシートで作ってみた
あわせて読みたい
このブログを書いたスタッフ
経営企画
あさすけ
とてつもない悩み性であったが、この会社に入ってから「死なない! 大丈夫!」が合い言葉に。日々、色々な人に助けられながら元気に働いている。透き通った歌声の、トリニティの看板娘。
コメントを投稿
ログイン
登録
他のサービスIDでログイン
Log in with Facebook Log in with Twitter Log in with Googleログインせずに投稿する場合には名前とメールアドレスを入力してください。
管理者の承認後、コメントが表示されます。