空文字と空の境界（Googleスプレッドシートなら 空白を使おう！）
Googleスプレッドシートユーザーの みなさ～ん！！ 数式で空（空白）を使ってますか～？？
Excel脳から脱却できず、いまだに
=IF(C2="","",B2*C2)
こんな感じの式を作って、空文字 "" を返してませんか？
Googleスプレッドシートは 空（空白）を数式で返せます！
=IF(C2="",,B2*C2)
これが Excel に対して Googleスプレッドシートが使い勝手の良い点の一つであり、実は 同じように見える 空文字と 空（空白） には境界（大きな違い）があります。
空文字と空の境界を理解して、是非 Googleスプレッドシートの 空の境地へと辿りつきましょう！
タイトルの元ネタはコレですｗ
前回は Googleスプレッドシートの フィルタ表示がフィルタビューに変わったという noteを書きました。
違う世界線へ！ Googleスプレッドシートのフィルタビュー が超絶便利 5（フィルタ表示が覚醒しました）
Googleスプレッドシートは 空（空白）を空のまま返せる
Excel職人だったmirは、10年くらい前に 初めてGoogleスプレッドシートを触った時、Excelとの違いに衝撃を受けました。
それが
複数人が同時に開いて編集が出来る（リアルタイム共同作業）
一つの式で複数のセルに配列で結果を展開できる（スピル機能）
数式で空（空白）をそのまま返せる
この3つです。（もちろん、無料でここまで出来るのか！って驚きもありましたが）
その後 Excelも大きく進化したので 、現在は 1、2は Excel側でも実装されています。
しかし、いまだにExcelでは対応出来ないのが「数式で空（空白）をそのまま返せる」ことです。
Excelは 数式で 空白を返せない
・Excelは数式で空白セルを参照すると 0 を返す
・Excelは数式では空白を返せない
これは Excelを長く使ってる人だと「こういうもんんだ」って慣れてしまった仕様ですが、先にGoogleスプレッドシートから入った若い人だと 違和感があるんじゃないでしょうか？
上のようにA1セルが空白だった時、隣のA2セルに
=A1
とした時、0が返ります。（ 0化現象）
これは、どんな関数を使っても、最新のスピル対応 Excelでも変わりません。
Excelユーザーは数式を組む時にこの「空白セルへの対応」が常識になっており、
■ セルの表示形式で対応
■ &"" を使う
■ =IF(A1="","",A1) 空なら空文字を返すと IFで分岐させる
こんな方法で対応しています。
で、これを見ると 「Excelは数式では空白を返せない」って書いてるけど、空白返せてるじゃない。って言う人がいるかもしれませんが、画像のB1セルは 空白ではなく「空文字」なのです。
空文字は空（空白）とは違う
「空文字」は "" (ダブルクォート2個）で表します。
「空文字って空（空白）と一緒でしょ？」と言う人は、上の画像をみてください。
空白セルのA2、数式で空文字を返したB2セル、見た目はどちらも空のように見えますし、=A2=B2 でも TRUE を返していますが、 ISBLANK関数を使うと
■ISBLANK関数の判定
セルが空（空白）の場合 ・・・ TRUE
セルに空文字が入ってる場合 ・・・ FALSE
このようになってますね。
ISBLANK関数は 真の空白 でのみ TRUEを返し、空文字の場合は BLANKと見なさないということです。
この仕様は Googleスプレッドシートの ISBLANK関数でも同様です。
ISBLANK関数以外にも 空白と空文字を別モノとして扱う 関数が、ExcelやGoogleスプレッドシートには幾つかあって、この挙動を理解していないと 誤った結果になるケースがあります。これは後ほど解説します。
Googleスプレッドシートは 空のセルを空のまま返せる
「空（カラ）のままの～ セルを返せるのよ～、空でいいの～ 書き出してみるの～♪」
というわけで、Excelとは違って Googleスプレッドシートは 空白セルが数式を通ると 0になる 0化現象は発生しません！
Googleスプレッドシートは 数式で 空白セルを空白のまま 返せます！
もちろん関数を使った場合や配列を返す式でも、空白は空白のまま結果を返せます。
参照するセル範囲に 空白セルが含まれていても、式を組む上で空白の対処を気にしなくていいのは めっちゃ楽です。
さらに Googleスプレッドシートは、数式で 空や指定したサイズの 空の配列を生成することができます。
Googleスプレッドシートで 空（空白） を掌握する
・Googleスプレッドシートは数式で空白セルを参照して空白を返せる
・Googleスプレッドシートは数式では空白を返せる（生成できる）
Googleスプレッドシートは 空白セルを式で参照して、そのまま 空白として返せる（ =A1） だけではなく、数式で空白や空白の配列を生成することが出来ます。
さらに、関数による空文字と空の挙動の違いを理解すれば、空を完全に制御しサーヴァントのごとく自由に使役することも可能となります！
数式で空（空白）を返すことで
数式の文字数を減らし シンプルに出来る
空文字と空白の扱いが違う関数と組み合わせた時でも安心
GASで 正しく範囲が取得できる
このようなメリットがあります。
まずは空の生成方法から見ていきましょう。
数式で空（空白）を生成する
="" で空文字を返せるなら、 = だけなら空白を返せるか？ というと、それは出来ません。
Googleスプレッドシートでは セルに = だけ入れることは NGで、入力エラーとなります。
セル参照なしに 空白を生成する為には、なんらかの関数を組み合わせる必要があります。
関数で空白を返す際にもっとも利用するのが IF関数 ですが、一番シンプルな（短い） 空白を返す式が
=IF(1,)
こんな式です。
これは第1引数の条件式部分を 1（TRUE）扱いとしてる為、必ず 第2引数の TRUEの時の結果を返すのですが、そこが ,) と何も入っていない為 空白を返すことになります。
結果の引数の部分には Excelだと ""（空文字）を入れますが、ここに 何も入れない ことで 空白を返すことが出来ます。
マニアックなユーザーだと T関数の方が短い式で空白返せるんじゃ？って思うかもしれませんが、
=T(0) という式で返る結果、つまり T関数の引数がテキストではなかった場合の結果は 空白ではなく 空文字です。
Googleスプレッドシートの IFERROR関数、IFNA関数は 第2引数を省略で空白を返せる
ちなみにGoogleスプレッドシートの IFERROR関数 や IFNA関数は特殊で、
Excel だと
=IFNA(VLOOKUP(C2,A1:B10,2,FALSE),"")
=IFERROR(VLOOKUP(C2,A1:B10,2,FALSE),"")
こんな感じで VLOOKUPと組み合わせて、見つからなかった時に 空文字を返すという使い方をよくしますが、
Googleスプレッドシートでは
=IFNA(VLOOKUP(C2,A1:B10,2,FALSE))
=IFERROR(VLOOKUP(C2,A1:B10,2,FALSE))
このようにIFNAやIFERRORの第2引数をカットしてしまうことで、エラーだった場合に 空白を返す という処理を短縮して記述できます。（Excelの場合は 第2引数省略は出来ない）
簡潔で便利ですね！
もちろん、あえて「空文字」を返したい時はExcelと同じように 第2引数に "" を指定する記述も可能です。
数式で 一次元の空配列を生成する
これも マニアでないと使う機会はほぼないんですが、Googleスプレッドシートは 空配列を生成できます。
一次元配列（1行、または1列）の空配列が必要な場合は
=WRAPROWS(,5,) ・・・ 1行5列（横に5つ）の空配列を生成
=WRAPCOLS(,4,) ・・・ 4行1列（縦に4つ）の空配列を生成
こんな記述で対応できます。
WRAPROWS関数やWRAPCOLS関数は、指定した行・列（サイズ）で一次元配列を折り返した結果を返す「配列操作系関数」ですが、第3引数で折り返し部分までの 足りない部分を埋める文字を指定できます。
第1引数、第3引数を空白とすることで、指定したサイズの 空の 一次元空配列を生成しています。
ちなみにこの1回目の折り返しに満たない場合も 第3引数で埋めてくれる挙動は Googleスプレッドシートだけです。
ExcelのWRAPROWS関数、WRAPCOLS関数は 1回目の折り返しに満たない場合は、そのまま第1引数が結果として返ります。
空白の配列が 数式で返っているというのが、目に見えないのでちょっとわかりにくいですが、なにも入っていないように見えるセルに適当な値を入力すると、
このように 数式が展開（スピル）できず #REF! エラーが発生するのがわかりますね。
見えない空白が確かにある！ってことです。
VSTACK関数やHSTACK関数を使っても一次元の空配列を生成できます。
=HSTACK(,,) ・・・ カンマ2個で 3つの空白を横結合 （1行3列の空配列）
=VSTACK(,,,) ・・・ カンマ3個で 4つの空白を縦結合（4行1列の空配列）
これもわかりにくい式ですが、空を横（または縦）に複数結合しているってことです。
中カッコの場合は 空文字なら連結ができますが、
={,} ={;} これらは、どちらもエラーとなります。空は結合できません。
WRAPROWS関数や VSTACK関数 について詳しく知りたい方は、過去 noteを参照ください。
数式で 二次元空配列を生成する
複数行、複数列の 空配列を作りたい場合はどうすれば良いか？
シンプルな方法だと、配列を生成する MAKEARRAY関数を使う方法と SEQUENCE関数を IF関数と組み合わせる方法の 2通りが考えられます。
=MAKEARRAY(3,4,LAMBDA(a,b,))
MAKEARRAY関数は、LAMBDAヘルパー関数の一つで、指定したサイズの配列を生成できる特殊な関数です。
上のように記述することで、3行4列の空配列を生成できます。
=ARRAYFORMULA(IF(SEQUENCE(3,4),))
同じく SEQUENCE関数も 縦横に指定したサイズで連番を展開させることが出来る関数なので、これと IF関数で 0以外の数値が TRUE として扱われる特性を活かして上のように記述できます。
IFを配列処理させる為に ARRYFORMULAを付けるんで、どうしても式が長くなりますが・・・。
これらは Excelでも 空文字配列を生成する方法として利用できますが、ぶっちゃけExcelだったら EXPAND関数を使った方が簡単です。
EXPAND関数は、なぜかGoogleスプレッドシートに輸入されなかったんですよね。。2024年6月時点では、Excelでのみ使える関数です。
で、空文字じゃなくて空の配列作れると何かいいことあるの？
って聞かれると・・・、ぶっちゃけ空配列じゃないと駄目ってケースは 滅多にないですｗ
Googleスプレッドシート 空文字と空を区別する関数
数式で 空（空白）を返すメリットとして、
数式の文字数を減らし シンプルに出来る
空文字と空白の扱いが違う関数と組み合わせた時でも安心
GASで 正しく範囲が取得できる
と書きました。
このうちの1は 実はたいしたことありません。
たとえば
空文字を返す式 19文字
=IF(C2="","",B2*C2)
▼
空を返す式 17文字
=IF(C2="",,B2*C2)
と2文字削減できるだけです。
わかりやすさという点でも、""が付いてた方が「空文字を返す」というのが 一目でわかってよい、という声もあります。
というわけで 数式で空白を返す メリットを実感できるのは 2,3となります。
ISBLANK関数以外の 空文字と空を区別する関数を見ていきましょう。
SORT関数では 空文字は 文字扱い
空文字と空の扱いの違いで 大きい影響がある関数の一つに、 SORT関数があります。
数値と文字、空文字、空白が混在する A列をキーとして A1:B13を SORT関数で TRUE（昇順）で並べ替えると、
数値 ⇒ 空文字 ⇒ 文字 ⇒ 空白
このような順になります。 FALSE（降順）で並べた場合は
文字 ⇒ 空文字 ⇒ 数値 ⇒ 空白
この順ですね。わかりますでしょうか？
空白は SORT関数では昇順でも降順でも 一番下に寄せられる（対象外となる）のに対して、空文字は 文字と同じ扱いになってますね。
つまり、
合計金額が大きい順に並べようとして SORT関数を使った場合、合計金額の列（C列）が、
=ARRAYFORMULA(IF(A3:A11="","",A3:A11*B3:B11))
このように単価の列（A列）が空欄の時に 空文字を返す式だと、最大金額より空文字のセルが 上にきてしまうわけです。
合計金額の列を空を返す式にしておけば
=ARRAYFORMULA(IF(A3:A11="",,A3:A11*B3:B11))
このように簡単に解消できます。
SORT関数で並び替える時は、空を返す式を使うべきですね！
TOCOL関数は 空文字を無視できない
一次元配列（1列または1行）のデータを 空白除外して上（左）に詰めて出力する際に便利なのが、TOCOL関数、TOROW関数です。
TOROW(array_or_range, [ignore], [scan_by_column])
TOCOL(array_or_range, [ignore], [scan_by_column])
array_or_range ・・・ 配列または範囲,
[ignore]・・・ [無視] （省略時は 0）
0: すべての値を保持する
1: 空白を無視する
2: エラーを無視する
3: 空白とエラーを無視する
[scan_by_column]・・・[列でスキャン（するか？）] 省略時は false
True: 配列を列方向（上から下）にスキャンする
False: 配列を行方向（左から右）にスキャンする
第2引数を 1 とすることで 空白を無視（除外）することが出来ます。（3として 空白とエラーを無視しても可）
しかし、ここで無視される空白は 真の空白のみで、空文字は対象外となります。
データの中に 数式で空文字を返しているセルが含まれる場合は、 =TOCOL(A1:A13,1) としても 空文字が残ってしまいます。
関数を組み合わせた処理の中で、TOCOLやTOROWを使った空白除去は結構使うことが多いテクニックです。
その手前の処理で、空文字ではなく空白を返す式を組むべきってのがわかりますね。
※ Excelの場合は 空白が返せないので、 NA関数を使って TOCOLの 2または3で エラーを無視を使う方法があります。
逆に あえて空文字を返して セルを残す、というテクニックもあります。
これは、「あいうえお 50音表」を生成する際の「 や ▢ ゆ ▢ よ 」の1文字空けでも使いました。
UNIQUE関数は 空白と空文字を区別する
割と最近 noteで 登場した、データの重複を削除して 一意の値にする UNIQUE関数も 空白と 空文字を 区別する関数です。
で、A:Aといった 列まるごとの範囲指定をして、 空白を含まない一意の値を出力したい時は UNIQUE + TOCOL という組み合わせを使うことが多いんですが
=TOCOL(UNIQUE(A:A),1)
こんな式を組んでも、空文字は残ってしまいます。
元データの中に空文字を返す式が使われているから発生する問題なんで、これも 最初から空を返す式にしておけば解決ですね。
COUNTBLANK 、COUTIFで 空白だけや 空文字だけを カウントする
空白セルを数える COUNTBLANKという関数があります。
関数の名前的に COUNTBLANK関数も ISBLANK関数と同じく 空文字と空白を区別しそうなイメージなんですが、COUNTBLANK関数は 空文字セルを区別せず 空白と合わせてしてカウントします。
上の画像だと A1:A14の範囲内に 空白セルが2、空文字セルが 3 存在していますが、ISBLANK関数では 2ヵ所の空白のみ TRUEを返しています。
しかし、COUNTBLANK関数だと 空白と空文字の両方を合算した 5を返しているのがわかりますね。
ISBLANKとCOUNTBLANK 判定基準が違うので少し紛らわしいです。。
続いて COUNTIF関数の 空白、空文字の扱いを見ていきましょう。
=COUNTIF(A1:A14,"")
この式でも COUNTBLANKと同じで、空文字・空白の両方をカウントして 5を返します。
では、空文字を除く 真の 空白のみをカウントするにはどうすればよいか？
=COUNTIF(A1:A14,"=")
じつは "" ではなく、 "=" とすることで 空白のみをカウントできます。
第2引数の条件を何も入れない =COUNTIF(A1:A14,) は機能しません。
ん、じゃあ空白以外って条件でよく使う "<>" だと・・・
=COUNTIF(A1:A14,"<>")
そう！実は COUNTIFで "<>" を条件に使うと 空文字セルを含んで カウントされてしまうのです。上の場合は A1:A14のうち 空白セルの2つを除く 12が返ってます。
ちなみに 値が入っているセルを数える COUNTA関数も 空文字セルを含めてカウントしてしまいます。
=COUNTA(A1:A14)
では、空文字だけをカウントしたい場合はどうすれば？ 実はこれは COUNTIFでは出来ません。
=COUNTIFS(A1:A14,"",A1:A14,"<>")
このようにCOUNTIFS関数で 2つの条件を使用します。
しかし、 "" を満たし、かつ "<>" を満たすって・・・わけわからないですね。。
まとめると
■空白・空文字を両方カウント
=COUNTBLANK(範囲）
=COUNTIF(範囲,"")
■空白のみカウント
=COUNTIF(範囲,"=")
■空白以外をカウント（空文字を含む）
=COUNTIF(範囲,"<>")
=COUNTA(範囲)
■空文字だけをカウント（空白は除く）
=COUNTIFS(範囲,"",範囲,"<>")
こんな感じになります。 空白と空文字を除いた 真の値の入ったセルをカウントしたい場合は、COUNTIF、COUNTIFS を使うよりは
=SUMPRODUCT(A1:A14<>"")
古典的手法ですが、SUMPRODUCT関数を使った方が早そうです。
いずれにせよ、こんな複雑なことを意識せずとも Googleスプレッドシートなら 空文字を使わず、式で空白を返せば 済む話ですね。
数式で空白を返せば、空文字の時の挙動を気にせず 他の関数が使える！これは大きなメリットと言えるでしょう。
GASで範囲取得した時の 空文字と空の違い
最後に メリットの3番目のGASについて書きましょう。
数式の文字数を減らし シンプルに出来る
空文字と空白の扱いが違う関数と組み合わせた時でも安心
GASで 正しく範囲が取得できる
GASを使うと、見た目や 手作業では気にならなかった 空文字と空 の境界（大きな違い）を実感できます。
空白を返せば getLastRow で 正しく最終行が取得できる
//最終行を返す式
function lastRow() {
const sheet = SpreadsheetApp.getActiveSheet();
console.log(sheet.getLastRow());
}上のコードは、アクティブなシートの データのある範囲の最終行を返す関数となっています。
たとえば 15行までデータが入ったシートを開いて実行すると、このように ログで sheet.getLastRow() は 15を返します。
しかし、
=ARRAYFORMULA(IF(C2:C="","",B2:B*C2:C))
ARRAYFORMULAで範囲の お尻を決めず 「空文字」を返す式を使っていた場合
式が空文字を返している範囲が 全て使われている範囲とみなされてしまい、sheet.getLastRow() は シートの最終行である 1000を返してしまいます。
これだとせっかく sheet.getLastRow() で最終行を取得する意味がありませんし、このまま getRange() , getValues() した時に無駄が多いです。
これは 数式を「空文字」ではなく「空」を返すように修正するだけで解決できます。
=ARRAYFORMULA(IF(C2:C="",,B2:B*C2:C))
結果が表示されている 最終行である 4が返りました！
GASのコードでこねこねして対処するよりも 簡単ですね。
空白を返せば getDataRange で正しく範囲が取得できる
//データ範囲を選択する
function dataRangeActive() {
const sheet = SpreadsheetApp.getActiveSheet();
const dataRange = sheet.getDataRange();
dataRange.activate();
}getDataRange()という シート内のデータが入力されている（有効な）セル範囲を取得するメソッドでも同様です。
上のコードは、データが入力されているセル範囲を アクティブ（選択状態）にする ものですが、
Arrayformula で シートの一番下まで 空文字を返す式を使っている場合
シートの最終行 1000行目までをデータ範囲と認識してしまいます。
一方、空文字ではなく 空白を返す式であれば
空白ではない 値のある セル範囲（A1:E4）だけが データ範囲として アクティブになります。
数式で 空白を返すことの GASでのメリット、実感いただけたでしょうか？
【オマケ】Excel は XLOOKUPで空白を探索できる
数式で 空白を返せる Googleスプレッドシートは、Excelに比べて より柔軟に 空文字と空白を使い分けて活用することが出来ます。
しかし、Googleスプレッドシートでは出来ない、空白の探索が出来る関数が Excelにはあります。
それは XLOOKUP関数（XMATCH関数）です。
Excel の XLOOKUP、XMATCH で空白を見つける
=XLOOKUP(,A1:A14,B1:B14,"")
▶ A1:A14を上から走査して 最初に見つかった 空白セルの 隣 B列の値を返す
=XMATCH(,A1:A14)
▶ A1:A14を上から走査して 最初に見つかった 空白セルの位置（何セル目か？）を返す
Excelでは このように 第1引数 に何もいれずに XLOOKUPやXMATCHを使うことで、空白を探すことができます。
=XLOOKUP(,A1:A14,B1:B14,"",,-1)
=XMATCH(,A1:A14,,-1)
引数の 検索モードを -1として 逆方向から（下から）走査した場合、一番下の 空白3の セルがヒットしているのもわかりますね。
第1引数を "" とした場合は、空白セルと区別して 空文字を見つけてくれます。
うーん、優秀ですね。
空文字の探索は VLOOKUP、MATCHでも出来るんですが、空白の探索が出来るのは XLOOKUP、XMATCH だけです。
=VLOOKUP(,A1:B14,2,FALSE)
=MATCH(,A1:A14,0)
このようにすると、空白ではなく 0が入ったセルにヒットしてしまいます。
ちなみに 範囲に 0が無いと
空白セルにはヒットせず、#N/Aエラーを返します。
VLOOKUPや MATCH関数では、空文字は探索できても 空白の探索は出来ないってことですね。
Googleスプレッドシートの XLOOKUPは 空白セルを見つけられない
残念ながら Googleスプレッドシートの XLOOKUP関数、XMATCH関数は Excelのように 空白を探すことが出来ません。
VLOOKUP関数、MATCH関数と同じ挙動で 0にヒットし、
範囲内に 0が存在しない場合は #N/Aエラーとなります。
空文字は 探索できますが、これはVLOOKUP、MATCHでも出来ますしね。
Googleスプレッドシートの XLOOKUP,XMATCHは、VLOOKUPやMATCHと比べて 特段 空白に対しての違い（優位性）は無さそうです。
空白まで見つけられる Excelの XLOOKUPは凄いですね！
今後、さらにExcelでは XLOOKUPが正規表現も扱える予定とのことで、ますます強力になっていきます。
Googleスプレッドシート側の やや存在感の薄い XLOOKUPもパワーアップを期待したいところです！
Googleスプレッドシートは 式で空白を返そう！
今回は「空文字と空の境界」ってことで、Googleスプレッドシートは Excelと違って 空白セルをそのまま 空白として出力できる、そして数式では 空文字ではなく 空白を返した方がメリットがある。
数式の文字数を減らし シンプルに出来る
空文字と空白の扱いが違う関数と組み合わせた時でも安心
GASで 正しく範囲が取得できる
という ネタを書きました。
もちろん Excel形式で ダウンロードする可能性があるシートであれば、互換性を考慮して 空文字を使った方が良い場合もあります。
でも、単に Excelで慣れてるって理由だけで "" （空文字）を使う Excel脳からは 脱却して、Googleスプレッドシート使いなら 空の使い手を目指しましょう！
次回は・・・、画像関連のネタの続きを書こうかなと思います。
この記事が気に入ったらサポートをしてみませんか？