この記事は Akatsuki Advent Calendar 2020 2日目の記事です。
はじめに
Googleスプレッドシート で凝ったものを作りたい時、実装方法を検索することがありますよね？
大抵の場合、関数の書き方を載せてくれているサイトや記事が見つかると思うのですが、その関数には空文字列 "" を使っているケースがよく見られます。
例えば IF関数 で「空欄だったら何もしない」という実装をしたい場合、以下のような書き方をされがちです。
=IF(A2 = "", "", "A2が空欄でない場合の処理")
・↓あまり良くない実装例
上記の書き方でも普通に動作するため、一見問題は無さそうです。
ですが、この空文字列""を使った書き方、実はあまり良い書き方とは言えないんです。
関数の計算結果としてセルに空文字列""が出力されると、本記事で解説するデメリットがあるからです。
空文字列""を避けた実装例は以下の通りで、IF関数の第2引数を""とせず、何も書かない記法がオススメです。
=IF(ISBLANK(A2),, "A2が空欄でない場合の処理")
・↓良い実装例
個人的には第1引数を ISBLANK関数 に変えることもオススメしたいですが、無理に変更するほどではないです。
( 第1引数では「条件」として "" を使っており、最終的に "" がセルに出力されるわけではないため)
それでは、空文字列""が良くない理由について、大きく2つの観点から説明していきます。
理由1. ISBLANK関数が使えない
こちらが最大の理由では無いのですが、2番目に挙げる理由よりも分かりやすい話のため、先に取り上げます。
1. 空文字列""があるセルは「空欄」ではない
ISBLANK関数 はその名の通り、指定したセルが何も入っていない「 空欄かどうか ( is blank ) 」を検証する関数なのですが、指定したセルに空文字列""が入っていると、空欄のように見えて「空欄ではない(FALSE)」と判断されてしまいます。(※1)
そのため、空文字列""が出力される数式を書いてしまうと、ISBLANK関数を使う際に、下記のような意図しない挙動が起きてしまうことがあります。
・↓A2セルに""が出力されている際の挙動
「私が空欄の判定を行うときは絶対に [セル名] = "" の形で書きます！ ISBLANK関数なんて使いません！」というスタンスであれば確かに上記のような問題は起こらないとも言えます。
しかし、そのシートを他の方が使うというケースもあると思いますので、「そもそも空文字列""をセルに出力する関数を作らない」と普段から習慣付けておくのが無難でしょう。
複数人でオンライン編集可能であるのがGoogleスプレッドシートの特徴ですし、常に考慮しておきたい点です。
-
(※1) 空文字列""と空欄での挙動の違い
[セル名] = "" と ISBLANK([セル名])の判定の違いに関する詳しい説明は、別の方が分かりやすく纏めてくださっていたので、そちらを参照していただけると理解が深まると思います。
参考: （Gスプレッドシート）空白セルを返す・判定する・数える - いきなり答える備忘録
理由2. IMPORTRANGE関数と相性が悪い
こちらがあまり知られていないであろう、私が空文字列""を避けるべきと考える最大の理由です。
他のスプレッドシートから情報を読み取る IMPORTRANGE関数 を使う際には、空文字列""が次の2つの点で悪さをすることがあります。
2-1. インポートする際の容量が増える
IMPORTRANGE関数はインポートする容量が大きくなるほどインポートに時間がかかり、またインポートできる最大の容量が設定されているため、一定量(※2)を超過すると「結果が大きすぎます」というエラーが返されます。
実験として、空文字列""を1,000,000行1列で並べた別のスプレッドシートと、同様に空欄を5,000,000行1列で並べた別のスプレッドシートを作成し、その一部分をインポートしてみます。
まずは空文字列""を大量にインポートした結果の一例を以下に示します。
この結果により、1列に並んだ空文字列""のインポートは477,375行が限界であることが分かります。
対して、空欄を大量にインポートした結果は以下のようになります。
なんと、こちらは 5,000,000行1列 (=スプレッドシートの限界セル数) でもエラーが出ずにインポートが成功しています。(※3)
以上の結果から、空文字列""をインポートすると、空欄よりもインポート容量が圧倒的に大きくなってしまい、今回のようなインポート容量オーバーが起きたり、単純にインポートにかかる時間が増えて重くなったり、「インポート範囲の内部エラーです。」(※4)というエラーが発生しやすくなったりします。
他のスプレッドシートからインポートされる部分に関しては、空文字列""の利用は絶対に避けるべきです。
-
(※2) インポートの最大許容容量
以前に独自で行った実験により、インポートできる容量の限界は10MBなのではないかと推定しています。
が、推測の域を出ないため確定的な情報ではありません。
(※3) 空欄では5,000,000セルのインポートが可能
2019年の12月頃に実験したときは空欄のセルでも500,000~600,000行程度が限界でした(但し、これでも空文字列""よりは多くの行数をインポート成功していました)。
しかし、いつの間にかアップデートがあったのか、現在は5,000,000行インポートできるようになっています。
ちなみに空文字列""のインポートは477,375行が限界であることは当時から変化していません。
空欄セルをインポートする際は上手く圧縮するよう、仕様変更が最近あったのでしょうか？
(※4) 「インポート範囲の内部エラーです。」
このエラーは原因不明ですが、インポート容量が大きめで、シート自体の処理が重い時に発生しやすい印象です。
インポートがタイムアウトした際に発生するエラーなのかもしれません。
2-2. シートの自動拡張が意図せず発生する
正確にはIMPORTRANGE関数に限った現象ではないのですが、IMPORTRANGE関数で発生しやすい現象です。
スプレッドシートには、計算結果が複数セルに渡る際、現在のシートの大きさよりも多くのセルに結果を展開したい場合にはシートの自動拡張を行う機能があります。
現状のシートサイズよりも大きい範囲をインポートする際、内容が「空欄」であれば何も起きないのですが、空文字列""の場合にはシートが無理矢理拡張されてしまいます。
・↓空欄を10行インポートしてもシート自体は5行のまま
・↓空文字列を10行インポートしたら511行まで自動拡張されてしまった
さらに、空文字列のインポートが続く限り、無駄に自動拡張されたセルは削除することができません(削除後、即座に自動拡張が再発生して元に戻ります)。
その上、自動拡張が起こったシートに、ARRAYFORMULAで最終行まで計算している箇所がある場合には、増えた行数分の再計算が走り始めます。
重めの再計算が同時に複数走ったりすると、シートがフリーズして作業が止まってしまうこともあるため、繰り返しにはなりますが他のスプレッドシートからインポートされる部分に関しては、空文字列""の利用は絶対に避けるべきです。
まとめ
以上、
・ 空欄と見間違えてISBLANK関数が動かない
・ インポートする容量が無駄に増え、インポート自体が重くなる
・ インポート時にシートが無駄に自動拡張されてしまう時がある
といった、空文字列""を利用した際のデメリットを挙げていきました。
私はスプレッドシートに触る機会が非常に多いのですが、「空文字列""を使うメリットなんて1つも無いのではないか？」と思うほどに、空文字列""は広く使われている割にデメリットの多い記法だと思っています。
Googleスプレッドシートを日々活用する皆様が、本記事で得たものがあれば幸いです。
お読みいただきありがとうございました。
--
コラム. [セル名] = "" と ISBLANK([セル名]) の記法の違いによる計算時間比較
本記事で取り上げた [セル名] = "" と ISBLANK([セル名]) は大体同じ挙動を示しますが、計算時間に違いはあるのでしょうか？
計算時間に大幅な差があれば、やはり短い方を採用したくなるため、計算時間を比較してみます。
まずA列に100万セルの空欄を用意し、B列に以下の数式を書きます。
・空文字列: =ARRAYFORMULA(IF(A1:A1000000 = "", "空白", "空白でない"))
・ISBLANK: =ARRAYFORMULA(IF(ISBLANK(A1:A1000000), "空白", "空白でない"))
この時、関数を入力(=エンターキーを押下)した瞬間から結果が出力されるまでの時間を測ります。
両方とも5回程度試しましたが、私の環境では全て10~11秒程度で完了し、計算時間は変わりませんでした。
空文字列""との比較でもISBLANK関数でも、処理の重さでの優劣は無さそうです。