エクセルの小技 条件を満たすデータの数
前回に続き、職場のエクセルで気になった点。
あまり細かい事を書いてしまうと色々とアレなのですが…
ざっくり言うと、従業員情報を性別や立場（身分）別に
集計した表があるんだけど、その数が合っていませんよ。と。
直接、元データを操作してみた訳じゃないけど…
私（心の声）「これ（手動で）数えて入力してるでしょ？」と（苦笑
そんな訳で「条件を満たすデータの数」の出し方を。
まぁ、条件が一つなら「COUNTIF」で問題無く済むんですけどね。
あ、あと…
エクセルが2007以降なら「COUNTIFS」があるのですが…
私のメイン機は、まだ2003なので。 ヾ(;´▽｀A``
そんな訳で別の方法を。
まずは普通に「COUNTIF」
A列に果物の種類（品名）
B列に産地が入ってる表です。
で、それぞれの果物名が入ってるデータ数を数える場合。
=COUNTIF(A2:A7,D2)と、なります。
※参照固定用の$は省略
D2のセル「みかん」と一致するデータの数を
A2:A7から数えろ。って意味ですね。
同様に「りんご」「メロン」も数えなさい。と。
まぁ、ここまでは普通。
次に同じ「みかん」でも産地毎に絞って数えたい場合。
「COUNTIFS」が無い、2003では「SUMPRODUCT」を使います。
正直… 今となっては、完全なレアケースだと思いますが。 ┐(^-^;)┌
本来の「SUMPRODUCT」関数の使い方とはちょっと違いますが…
=SUMPRODUCT((A2:A7=D6)*(B2:B7=E6))
と、こんな感じに。
ちなみに本来の「SUMPRODUCT」関数の使い方は…
単価を欄を追加して
=SUMPRODUCT(E2:E4,F2:F4)
配列1（個数）と配列2（単価）を掛けながら足す。って感じ。
ややこしいけど。 ┐(^-^;)┌
細かく書くと
・みかん 3×100 ＝300
・りんご 1×120 ＝120
・メロン 2×600 ＝1200
◆300＋120＋1200 ＝1620
ざっくり言うと、小計を書かず（表示させず）に合計が出せます。
で、配列を1つだけ（この場合は個数）指定した場合は、単なる和が表示される。と。
※画像では、E10セルの“6”の事
話を=SUMPRODUCT((A2:A7=D6)*(B2:B7=E6))に戻すと
A2:A7=D6 の部分で品名からD6（みかん）と一致するか？
B2:B7=E6 の部分で産地からE6（静岡）と一致するか？
を順に確認し、条件を満たすと“True（真）”が返ってくる。と。
Trueの場合は“1”、False（偽）の場合は“0”となるので、
それを掛ける事で、どちらか一方でも条件を満たしていないと
0×0・1×0・0×1となり、結果“0”になります。
逆に両方の条件を満たした場合、1×1 ＝1となり…
最後に和を計算した結果、条件を満たすデータ数と等しくなる。と。
ん～ ややこしい。 ┐(^-^;)┌
更にややこしくなるのが…
SUMPRODUCTは、データが数値型じゃないと“0”になってしまう。という事。
なので、=SUMPRODUCT(A2:A7=D6,B2:B7=E6)
だと“0”になってしまうのです。 ｡･ﾟ･(ﾉД`)･ﾟ･
※データ型がブーリアン型（真か偽か）になる為
一応、1を掛ける事でブーリアン型を数値型に変え、結果を得る事もできますが…
=SUMPRODUCT((A2:A7=D6)*1,(B2:B7=E6)*1)
=SUMPRODUCT((A2:A7=D6)*(B2:B7=E6))
より僅かにですが、文字数が増えてしまう結果に（苦笑
ん～ ややこしい。 ┐(-_-;)┌
まぁ、ぶっちゃけると…
自分用＆データ数が莫大な量でないならば…
IF関数だけでも、どうにかなっちゃうんですけどね。
美しくは無いけど。 ≧∇≦ブハハハハハ
過去記事：曜日入力（変換）