エントリー

エクセルの小技 条件を満たすデータの数

前回に続き、職場のエクセルで気になった点。

あまり細かい事を書いてしまうと色々とアレなのですが…
ざっくり言うと、従業員情報を性別や立場(身分)別に
集計した表があるんだけど、その数が合っていませんよ。と。

直接、元データを操作してみた訳じゃないけど…
私(心の声)「これ(手動で)数えて入力してるでしょ?」と(苦笑

そんな訳で「条件を満たすデータの数」の出し方を。

まぁ、条件が一つなら「COUNTIF」で問題無く済むんですけどね。
あ、あと…
エクセルが2007以降なら「COUNTIFS」があるのですが…

私のメイン機は、まだ2003なので。 ヾ(;´▽`A``
そんな訳で別の方法を。

20170917101454.jpgまずは普通に「COUNTIF」
A列に果物の種類(品名)
B列に産地が入ってる表です。

で、それぞれの果物名が入ってるデータ数を数える場合。
=COUNTIF(A2:A7,D2)と、なります。
※参照固定用の$は省略

D2のセル「みかん」と一致するデータの数を
A2:A7から数えろ。って意味ですね。
同様に「りんご」「メロン」も数えなさい。と。
まぁ、ここまでは普通。

次に同じ「みかん」でも産地毎に絞って数えたい場合。
「COUNTIFS」が無い、2003では「SUMPRODUCT」を使います。

正直… 今となっては、完全なレアケースだと思いますが。 ┐(^-^;)┌

本来の「SUMPRODUCT」関数の使い方とはちょっと違いますが…
=SUMPRODUCT((A2:A7=D6)*(B2:B7=E6))
と、こんな感じに。

20170917101456.jpgちなみに本来の「SUMPRODUCT」関数の使い方は…
単価を欄を追加して
=SUMPRODUCT(E2:E4,F2:F4)

配列1(個数)と配列2(単価)を掛けながら足す。って感じ。
ややこしいけど。 ┐(^-^;)┌

細かく書くと
・みかん 3×100 =300
・りんご 1×120 =120
・メロン 2×600 =1200
◆300+120+1200 =1620

ざっくり言うと、小計を書かず(表示させず)に合計が出せます。

で、配列を1つだけ(この場合は個数)指定した場合は、単なる和が表示される。と。
※画像では、E10セルの“6”の事

20170917101455.jpg話を=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))
より僅かにですが、文字数が増えてしまう結果に(苦笑

ん~ ややこしい。 ┐(-_-;)┌

20170917101457.jpgまぁ、ぶっちゃけると…

自分用&データ数が莫大な量でないならば…
IF関数だけでも、どうにかなっちゃうんですけどね。

美しくは無いけど。 ≧∇≦ブハハハハハ

過去記事:曜日入力(変換)

Excel関数逆引き辞典パーフェクト

関連エントリー

ページ移動

コメント投稿

登録フォーム

:

:

:

:

ユーティリティ

- - - 1 2 3 4
5 6 7 8 9 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29 30 31 -

ページ

  • ページが登録されていません。

新着エントリー

新着コメント

Re:初のスキルLV.10武将 の続き 2
2014/09/24 from DSHata(管理人)
Re:伊東市の鳥
2014/05/16 from DSHata(管理人)
Re:【訃報】
2014/04/26 from DSHata(管理人)
Re:4147 根岸兎角
2014/01/19 from DSHata(管理人)
Re:娘、散財開始
2014/01/08 from DSHata(管理人)

過去ログ