スピル系の関数で空欄を0にしない

  1. Home
  2. Excel
  3. Excel Tips

やってみたら、意外と簡単にできたので、ご紹介します。

上図のように、SORT関数やFILTER関数(だけじゃありませんが)など、結果がスピるような関数の結果では、空欄セルは0になります。ちなみに、これはExcelの仕様です。

Excelは、空欄セルを参照すると0になります。これは昔からです。まぁ、仕様と言われればしかたないのですけど、なんか"できない"ってのは嫌です。何とかならないのでしょうか。特に冒頭の図で「空欄のセル」と「0が入力されているセル」が区別つかないってのは、ものすごく嫌です。これ、要するに「元データ→(何かの計算)→セル」という流れだと0になってしまうので、間にひとつ挟んで「元データ→(何かの計算)→配列→セル」とすれば、Excelによる「0への自動変換」が起きないのではないかと。計算結果をそのまま別の配列に変換するのでしたら、MAP関数で、いけそうです。

上図は確認です。すべてのセルを配列にできました。だったら、次のようにしてやりましょう。

最初の「Data」をFILTER関数に書き換えます。つまり、FILTER関数の結果を1セルずつチェックして、もし空欄("")だったら空欄("")のまま、空欄ではなかったら、そこには何らかの値(0という数値を含む)が入っているのですから、そのままとします。

「空欄のセル」は空欄に、「0が入力されているセル」は0のままです。これでスッキリしました。あぁ、書き忘れましたが、セル範囲A1:C11はテーブル「Data」です。

そのほかのスピル系関数

"スピルありき"で追加された超絶便利な関数では、ほとんどのケースで、この「空欄が0になっちゃう問題」が発生します。凝ったことをしないのであれば、基本的には上述した

のパターンでいけます。いくつか確認してみました。

・XLOOKUP関数

空欄("")を0にしない
=MAP(XLOOKUP(F2,A2:A5,B2:D5),LAMBDA(a,IF(a="","",a)))

・列を抽出する系

空欄("")を0にしない
=MAP(CHOOSECOLS(A2:D5,{2,3}),LAMBDA(a,IF(a="","",a)))

空欄("")を0にしない
=MAP(INDEX(A2:D5,0,1),LAMBDA(a,IF(a="","",a)))

空欄("")を0にしない
=MAP(TAKE(A2:D5,,-1),LAMBDA(a,IF(a="","",a)))

・TRIMRANGE関数(トリム参照)

空欄("")を0にしない
=MAP(A:.C,LAMBDA(a,IF(a="","",a)))

・VSTACK関数

空欄("")を0にしない
=MAP(VSTACK(A2:C4,A7:C10),LAMBDA(a,IF(a="","",a)))

UNIQUE関数は別の考え方もある

もうひとつ、UNIQUE関数でも、空欄が0になります。

こちらも、同じようにやってみましょう。

できました。できましたけど、どうなんでしょう。上図の結果では、空欄("")もひとつの値として、全部で4つの値が抽出されています。もちろん、そうしたいのでしたら結構です。だけど、こういうケースって、空欄("")を除いて、3つの値だけにしたくなりませんか。

もし、上図のようにしたいのでしたら、方針を変えます。今までのMAP関数を使った処理は、あくまで「空欄("")を0にしないで空欄("")のままにする」という考え方です。結果に空欄("")が必要でした。しかし、UNIQUE関数で"重複しない"値を抽出するときって、そもそも「空欄("")は必要ない。空欄("")は不要。空欄("")は除外したい。」という場合もあります。そんなときは、UNIQUE関数で処理をする前の元データから、空欄("")だけを取り除いてあげます。これはFILTER関数で一発です。

ここから、"重複しない"値をUNIQUE関数で抽出します。

どちらが良いということではなく、どういう結果を望むのかです。空欄("")も、"田中"や"小原"のように、ひとつの値だとしたいのならMAP関数です。そうではなく、空欄("")は除去して、何らかの文字列だけを値としたいのでしたらFILTER関数を使います。ケースに応じて使い分けてください。

VLOOKUP関数で0を返さない(2)

この「空欄を0にしない」といえば、以前書いた「VLOOKUP関数で0を返さない」にも応用できます。この解説は、かなり昔に書いたのですが、簡単にいうと次のような内容です。

VLOOKUP関数で表引きするとき、該当する値が空欄("")だと、0になっちゃうよねと。これだと、値としての0と、もともと空欄だった0の区別がつかないよねと。こんなときは、空欄("")を文字列結合するといいよって話です。

だけど、文字列結合した結果は"文字列"になるので、上図のように数値が左寄せで表示されちゃいます。なので、VLOOKUP関数の結果(ここではセル範囲E2:E7)の書式を変更して"右寄せ"にすると、見た目的にもスッキリします。

これは、30年近く前のパソコン通信時代から鉄板なので、このWebサイトを始めて、かなり初期の頃に書いたネタです。画像を見ると、Excel 2003ですねｗ

と、まぁ、そういうことなのですが、いくら見た目を変えても、E列の結果は文字列です。それは変わりません。なので、次のように計算しようとすると失敗します。

こんなときも、今回の発想が役立ちます。