TLC：sumproductのcountif的な使い方

エクセルのcountif関数は時々、紙一重でかゆいところに手が届かないときがある。

たとえばそれは、ifで数える条件を分けたいときに、その条件が2つ以上ある場合
countifではそれができない。


エクセルのバージョン次第では解決されているらしいのだけど
従来のエクセルでも、実はその代わりになる関数が存在したりする。
sumproduct関数というものだ。

sumはsubではなくsumだから引き算ではなく足し算
productは掛け算だから、

(a+b)^2を展開したa^2+2ab+b^2のように、掛け算した系列を全部足す、いわゆる「多項式」のような格好を取った関数だと考えればよい。






＝＝＝＝＝＝＝＝
ところで、
a1セルにプリン、b1セルにケーキが格納されているとき
c1に「=a1」と入力した場合はa1のプリンが継承されるだろう
同様に「=b1」と入力した場合はb1のケーキが継承されるだろう

じゃあ、「=a1=b1」だったらどうなるか？
イコールが2つもあるんだからその式はおかしい
みたいな感じでエラーメッセージが出るかと思えば、不意をついてエラーメッセージは出ない。

この式は成立しているのである。

実は1つ目のイコールと2つ目のイコールは意味合いが違う。
1つ目は「このセルにイコール以降を代入するよ～」って意味合いの代入のイコールなのに対し
2つ目は「＝の右と左は等しいですか～」って意味合いの条件のイコールと、役割が違ったりする。
この場合はa1のプリンとb1のケーキが違うので、「条件に合っていませんよ～」の意味で
「FALSE(偽)」という結果がc1セルに表示される。

ちなみに2つ目のイコールの代わりに「<>」をつけると
「＜＞の右と左は等しく「ない」ですか～」って意味合いの条件を表す式になり
イコールだったときと結果が逆になる。
この場合はa1のプリンとb1のケーキが違うので、「条件に合ってますよ～」の意味で
「TRUE(真)」という結果がc1セルに表示される。

ためしに、b1セルをケーキではなくプリンにして、「＝」と「＜＞」の場合の両方でc1を表示してみてほしい
a1=b1=プリンになるので先ほどとは結果が逆になって
「＝」→「TRUE」
「＜＞」→「FALSE」

になるだろう。
(「＜＞」は≠と同じ意味だ。
「！＝」と表記するときもある。
条件の「＝」は代入の「＝」と違うよって見やすくするため「＝＝」であらわすこともある
いうなれば≠は＝のNOTだな。)


このtrueとかfalseとかいうのは「論理値(boolean)」と呼ばれるもので、条件の真偽を確かめるものだ。
と同時に、1桁の2進数とも言える。
trueが1、falseが0という約束事がたいていは通じる。


エクセルではand関数やor関数があるのに、時々andを掛け算、orを足し算で表現することがある。
条件付書式のときなんかもそうだったと思う。
まあ最初は気持ち悪いだろうが、だんだん慣れてくるし、意味を理解すればなんてことはない。





＝＝＝＝＝＝＝＝
ここでsumproduct関数の話に戻ろう。
sumproduct関数はa^2+2ab+b^2のような多項式を計算するような関数だった。
では、多項式のaもbも論理値だったらどうなるだろうか？
aがtrueでbもtrueだったら
a^2+2ab+b^2はどうなるのか？

先ほども言ったように、論理値というのは1桁しかない2進数でもある
1桁しかないということは、2(10進)以上の値が存在しないということだ。

a=b=1で2abを計算しようが相変わらず1になる。
a　and　b　だ。2なんて係数はつかない。

が！
1^2+1+1^2の計算はしっかりと3になる！
true　or　true　or　trueの1ではない。

また、b=0だとしたら、sumproductの結果はa^2の1になる。
途中の掛け算まで論理値として計算し、最終的に足し算するときに論理値ではなく普通の数として計算しているということだ。

なるほどこれは、複数の条件に一致したものの数を数える、countifの拡張版として使えそうだ。


(a+b)(a+b)といったように、因数分解した形(和同士の積)を論理値で計算したものを情報乗法標準形と呼ぶ
これに対し、多項式のように
a^2+2ab+b^2みたいに積同士の和を論理値で計算したものを加法標準形と呼ぶ。

しかしながら、sumproductは計算途中が論理値なのに計算結果が論理値ではないため、加法標準形とも多項式ともビミョーに違う。


イメージとしてはそうだな
あいまい検索のような感じだ。

今ちょっといい例が思い浮かばなかったので、とりあえず目の前にある「糸楊枝」を例に話そう。

糸楊枝について検索したいとき

あいまいでない検索では
「糸楊枝」についてだけ検索する。

「糸楊枝　糸楊枝」でもヒット数は変わらない。

しかし、「いとようじ」や「糸ようじ」についても検索してほしい場合は
昔なら「糸楊枝ORいとようじOR糸ようじ」などのようにして検索したかもしれない。
ORを使うとその分検索の条件がゆるくなってヒット数が増える。
ヒット数が1桁の2進数なわけはなかっただろう。
しっかりとフツーの、何桁かの10進数だったはずだ。


また、窓を複数開いてそれぞれの窓で
「糸楊枝」「いとようじ」「糸ようじ」などといった感じで検索するのもアリだったかもしれない


今では、「いとようじ」で検索すると、勝手に「糸楊枝」や「糸ようじ」なんかを含めてくれるかもしれない。
まあ、そのワードの知名度にもよるよな。

にほんブログ村