「""」と「=""」を区別する
セルに何も入力されていない状態を"ブランクセル"とか"空白セル"や"空欄セル"などと呼びます。セルが空欄セルかどうかを調べるにはISBLANK関数が便利です。ISBLANK関数は、引数に指定したセルが空欄セルのときはTRUEを返し、空欄ではなかったらFALSEを返します。
空欄かどうかを判定するには、もうひとつ「空欄と等しい？」を調べる手もあります。一般的にExcelでは、空欄を「""」のように、ダブルコーテーションを2つ続けて表します。
空欄セルが何個あるかを調べるのなら、COUNTBLANK関数で簡単に分かります。
もちろん、COUNTIF関数でも数えられますね。
さて、ここまではよろしいですか？では、本題です。今度は逆に、空欄セルではない「何かが入力されているセル」をカウントしてみましょう。使うのはCOUNTA関数です。
ん？変ですね。セル範囲A1:A6の"空欄セル"は3つです。そして、文字列が入力されているセルも3つです。しかし、COUNTA関数は「6」を返しています。計算が合いません。ちなみにCOUNTA関数は、ヘルプで次のように記載されています。
じゃ、3つですよね。あ、でも、ヘルプをよく読むと、次のような記載もあります。
実は上図のデータには、ちょっと細工をしてありました。空欄セルに見えるセルには「=""」という数式を入力していたんです。見慣れない数式だと思いますが、要するにこれ、数式の結果が空欄("")を返していたのです。一般的には「IF(条件,何か,"")」みたいな数式だと考えてください。
こうなってくると、そもそも"空欄セル"とは何か？が怪しくなってきます。何も入力されていないのが"空欄セル"だとしたら、確かに「=""」という数式が入力されているセルは"空欄セル"ではありません。だからCOUNTA関数はカウントしました。でも、その数式の結果が返しているのが"空欄("")"です。そこで、COUNTBLANK関数は"空欄"としてカウントしました。何だか、混乱してきますよね。これ実は、実務でもやっかいな問題です。
A列には日付が入力されています。B列には「もし曜日が月曜だったら"○"、水曜だったら"△"、金曜だったら"□"、それ以外だったら空欄("")」という数式を入力しました。B列に表示されている記号の数をカウントしようとして、次のようにすると失敗します。
じゃ、COUNTIF関数でやってみましょうか。カウントする記号は複数あるので、ワイルドカードを使ってみましょう。
このへんまでは、関数ビギナーでも思いつきますけど、どちらもうまくいきません。まぁ、方法がないわけではないです。FILTER関数で「B列が空欄ではない」で絞り込み、結果の行数をROWS関数で調べればいけます。
でもこの数式、すぐに書けますか？FILTER関数は最強ですけど、今回はちょっと変わった使い方をしています。さらに、FILTER関数の結果をROWS関数で調べるなんて、けっこう難しいです。そもそもROWS関数なんて、実務で使う機会がほとんどないため、存在を知っている人は少ないでしょう。
ちなみにこれ、裏技を使うとカウントできます。上図のCOUNTIF関数ではワイルドカードを使いましたが、条件を「空欄ではない」という意味で「COUNTIF(Data[記号],"<>")」とも考えられます。
まぁ、この書き方でも失敗するんですけどねｗ ところが、次のようにすると成功するんです。
やったね！v(^<>^) でも、こうして見てると、もう条件が顔文字にしか見えません(*◜><◝*)
この書き方は、ハッキリ覚えていませんが5～6年くらい前かな、海外のYouTube動画で知りました。最初は私も意味が分からなかったのですが、ちょっと考えてみたら納得しました。まさに裏技ですね。詳しくはけっこう難しい話なので、なぜうまくいくのか、ここでは超簡単に解説します。
まず、上の「<>」は、数学でいう"ノットイコール"で「等しくない」を表す比較演算子です。数学では「≠」という記号を使いますが、パソコンのキーボードに「≠」が存在しないため、Excelでは不等号を2つ使って「<>」と書きます。本来「≠」は「A≠B」のようにして「AとBは等しくない」を表しますが、最初のCOUNTIF関数では「<>」と、演算子しか書いていません。つまり「AとBは等しくない」の"A"と"B"がありません。まず左辺の"A"に該当するものが書かれていないのは、これはCOUNTIF関数の文法的な理由です。この"A"に該当する左辺には、判定するセルが1つずつ内部で指定されます。今回でしたら「セルB2<>」「セルB3<>」「セルB4<>」…みたいな感じです。そして、"B"にあたる右辺にも何も書いていません。何もないんです。何もない、ということは"空欄(空白)"という意味です。したがって「<>」は「判定するセルが 何もない(空欄) ではない」→「セルに何かしらの文字が入力されている」となります。まぁ、これは基本中の基本です。
さて、問題は下の「><」です。まず結論から言うと「><」は「2文字からなる演算子ではない」ということです。「<>」がノットイコールを表す1つの演算子なので、何となく「><」も、そういう(隠された、知られていない)演算子なのでは？って感じるかもしれませんが、正しくは「>」という大きさを比較する演算子に「<」という文字を指定しているに過ぎません。このへん、もう少し詳しく例を挙げて解説します。
そもそもCOUNTIF関数やSUMIF関数では、条件に「○○より大きい」とか「○○より小さい」などの条件を指定できます。
上図は、セル範囲A1:A5の中で「30より大きい」値を探しています。ここでは「40」と「50」の2つですね。このように「>」や「<」などの演算子を使って大きさを比較するものって、一般的には"数値"です。でも実は、"文字"に対して大小を比較することも可能っちゃ可能です。たとえば、こんな感じです。
"C"は"B"よりも大きいって、ちょっと何言ってるか分かりませんよね。このように"文字"に対して大小を比較すると、その文字の"文字コード"が比較の対象になります。試しに、文字コードを表示してみましょう。
B列に表示されている「65」や「66」などが"文字コード"です。または"ASCII(アスキー)コード"とも呼ばれます[*1(下記参照)]。上図のCOUNTIF関数で指定した条件「>B」というのは、いわば「(文字コードが)66より大きい」という意味なので「C(67)」「D(68)」「E(69)」が該当します。
ASCII(アスキー)コードですが、詳しくはネットで検索してみてください。対応表の一覧などが、すぐに見つかります。ああ、ちなみにASCII(アスキー)コードの解説には、2進数や16進数、上位ビットや下位ビットなどが普通に出てきますので、そのつもりで。ここでは、その一部をご紹介します。
今回指定した、顔文字のような「><」というのは「"<"の文字コードより大きい」という意味です。だから、何らかの文字が表示されているセルが該当します。もちろん、"A"以下のコードだったらOKなので、次のように書いても成功します。[*2(下記参照)]
ではなぜ「>？」や「>＠」ではなく「><」としたのか？おそらく、顔文字みたいでカワイイからでしょうｗ(知らんけど)。
「""」と「=""」を区別する
さて、本稿のテーマである「セルに何も入力されてない"空欄セル"」と「セルに入力されている数式によって空欄が返されている"空欄セル"」を区別するには、どうしたらいいでしょう。ここでは、次のようなケースで考えてみます。
それぞれ「文字：4, 空欄：3, 数式：2」となっています。混乱しないように、便宜上"空欄"と"空白"という単語を使っていますが、厳密な意味で使い分けていません。イメージで理解してください。
計算しやすいように、セル範囲をテーブルにしました。名前は「Data」です。あと「=""」という数式が入力されているセルには色を塗っています。
・何も入力されていないセル
まずは、1セルずつ判定してみます。
まぁ、これはISBLANK関数で一発ですね。ISBLANK関数は、引数に指定したセルが"何も入力されていない"セルだったらTRUEを返します。「=""」のような数式が入力されているセルは該当しません。同じような用途で使われる「空欄("")と等しいかどうか」を判定すると、両方が該当してしまうので注意してください。
では、1セルずつ判定するのではなく、全部でいくつあるかを調べてみます。こちらは、COUNTBLANK関数だと失敗します。
いろいろな方法がありますけど、ここではISBLANK関数を使ったやり方をご紹介します。
ISBLANK関数の結果に1を掛け算している理由は、下記のコンテンツで詳しく解説しているのでご覧ください。
・数式の結果が空欄になっているセル
こちらも、まずは1セルずつの判定ですが、ちょっと難しいです。今回カウントしたいのは、次の条件を満たしているセルです。
- セルが空欄である
- セルに数式が入力されている
今回のケースに限って考えれば、2.だけでOKです。しかし実務では、何らかの数式が、空欄ではない数値や文字列を返している可能性もあります。さて、1.はよいとして、問題は2.の判定です。実は、これを調べるドンピシャの関数があります。ISFORMULA関数です。ISFORMULA関数は、引数に指定したセルに数式が入力されていたらTRUEを返し、数式が入力されていなかったらFALSEを返します。Excel 2013で追加された関数なので、あまり知られていません。
このISFORMULA関数と、1.の条件をAND関数で組み合わせます。
やってみれば分かりますが、このとき「AND(ISBLANK([@値]),ISFORMULA([@値]))」としてはいけません。理由は、落ち着いて考えれば理解できるはずです。前項で試したように「ISBLANK([@値])」には、数式の結果が空欄のセルが含まれないからです。
ではこちらも、全部でいくつかあるかをカウントしてみます。今回のケースでしたら、COUNTBLANK関数の結果から「SUM(ISBLANK(Data[値])*1)」の結果を引けば、答えの「2」が分かります。
でも、それじゃ、おもしろくないですｗ ここはロジカルに「数式の結果が空欄になっているセル」だけをカウントしてみましょう。まず思いつくのは、1セルずつ判定したときに使ったISFORMULA関数ですかね。
う～ん、イマイチですね～。そもそも私、SUMPRODUCT関数が嫌いですからｗ まぁ、ここはオーソドックスにFILTER関数を使いましょうか。
こんな感じです。「""」と「=""」って見た目は同じですし、ほとんどの場合は同一視して問題ないのですが、今回のように、ときどき両者を厳密に分けて考えたいケースもあります。そんなときのために、ぜひ覚えておいてください。
補足
(＊1)CODE関数で調べた文字コードを"ASCII(アスキー)コード"と呼ぶ、と書きましたが正しくは違います。ヘルプによれば、CODE関数が返すのは「ASCIIコードまたはJISコード」となっています。そうでなければ、漢字など日本語の文字コードは調べられないですからね。本稿では「文字コードって、なぁ～に？」というレベルの方に向けて、まぁ最初はそう認識していれば、いいんじゃね？という意味で"ASCII(アスキー)コード"と呼ぶ、と言い切りました。文字コードって、メチャクチャ奥が深いですから、沼に潜る勇気があるのなら、ご自身で勉強してみてください。参考までに、半角アルファベットの"A"と、"田"という漢字をCODE関数で調べた結果をお見せします。
CODE関数が返す文字コードは10進数ですので、DEC2HEX関数で16進数に変換しました。では、"A"と"田"の文字コードについて、もう少し詳細に調べてみましょう。下図は秀丸エディタで調べた結果です。
"田"の文字コード(16進)が、ちゃんとJISコードになってますね。あと、誤解しないでいただきたいのですが、ここまでの"文字コード"とは、あくまで「Excel内で表示する」ときの話です。こうした文字をファイル(*.xlsxなど)で保存すると、そのファイルはUTF-8で保存されます。
(＊2)すみません、面倒くさいし本題じゃないので、厳密に検証していません。まぁ、仕組みを理解できればいっかな？程度に考えて書きました。