スピル系の関数で空欄を0にしない
やってみたら、意外と簡単にできたので、ご紹介します。
上図のように、SORT関数やFILTER関数(だけじゃありませんが)など、結果がスピるような関数の結果では、空欄セルは0になります。ちなみに、これはExcelの仕様です。
Excelは、空欄セルを参照すると0になります。これは昔からです。まぁ、仕様と言われればしかたないのですけど、なんか"できない"ってのは嫌です。何とかならないのでしょうか。特に冒頭の図で「空欄のセル」と「0が入力されているセル」が区別つかないってのは、ものすごく嫌です。これ、要するに「元データ→(何かの計算)→セル」という流れだと0になってしまうので、間にひとつ挟んで「元データ→(何かの計算)→配列→セル」とすれば、Excelによる「0への自動変換」が起きないのではないかと。計算結果をそのまま別の配列に変換するのでしたら、MAP関数で、いけそうです。
上図は確認です。すべてのセルを配列にできました。だったら、次のようにしてやりましょう。
最初の「Data」をFILTER関数に書き換えます。つまり、FILTER関数の結果を1セルずつチェックして、もし空欄("")だったら空欄("")のまま、空欄ではなかったら、そこには何らかの値(0という数値を含む)が入っているのですから、そのままとします。
「空欄のセル」は空欄に、「0が入力されているセル」は0のままです。これでスッキリしました。あぁ、書き忘れましたが、セル範囲A1:C11はテーブル「Data」です。[*1(下記参照)]
そのほかのスピル系関数
"スピルありき"で追加された超絶便利な関数では、ほとんどのケースで、この「空欄が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列の結果は文字列です。それは変わりません。なので、次のように計算しようとすると失敗します。
こんなときも、今回の発想が役立ちます。
(追記)
(*1)ちょっと"言い訳"です。本稿のアイデアは、かなり昔に思いつきました。スピル系の関数が追加されて、すぐに「空欄が0になっちゃう問題」に気づき、その後LAMBDA関数が追加されて、さらにヘルパー関数のMAP関数などが追加されて。そんなこんなで、本稿のやり方を思いついたのですが、それから長いことLAMBDA関数などを触っていたら、もっと簡単に「これでいけんじゃね？」というやり方にも気づきました。本稿は、とりあえず残すとして、その簡単な方法もご紹介します。
まず普通にスピル系の関数(ここではFILTER関数)を使うと、結果はこうなります。
さて問題は「どのタイミングで空欄が0に置換されるのか」です。もっと簡単なケースで確認してみましょう。
この「=A1:A5」は動的配列数式です。結果はスピってます。スピル系のFILTER関数なども、これと同じことが起きています。さて、では次のように確認してみます。
何をしているのか、よく考えてくださいね。INDEX関数を使っています。元の配列は「A1:A5」です。もっと正確にいうと「A1:A5の結果」つまり「A1:A5という参照式が返す配列」です。その配列の、2番目の要素が「空欄("")と等しい」を判定したところ"TRUE"になりました。ということは、「A1:A5」が返す配列内では、空欄("")は空欄("")のままで、0に置換されていないということです。空欄("")が0に置換されるのは、その後に行われる「配列をセルに代入」するところです。
さて、本稿の冒頭で「これはExcelの仕様」と書きました。もう少し詳しくいうと「何も入っていないブランクセルを参照すると0になる」というルールです。しかし、セルに「空欄が入力されている」ときは違います。
厳密に言うと、空欄("")は「長さ0の文字列」などと呼ばれます。何も入力されていないブランクセルと、空欄という値は、まったく意味が異なります。したがって、参照式が返す配列のうち、ブランクセルだったら、そこを空欄("")という値に置換しちゃえばいいんです。
スピル系関数の結果でも、同じ発想でいけます。
上図では、同じFILTER関数が2回登場しますので、式を短くするためにLET関数を使いました。