[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『SUMPRODUCT関数について』（ranmal）
このサイトで教えていただき、とても便利にかつ助けられ 使用している関数ですが、 一つの範囲から、2つの条件を指定して カウントする方法が分からなくて（出来なくて） 困っています。
例えば下記のようなデータがあって、3月で、M102とS111の製品型番は いくつあるか(何件あるか）を出したいのです。
A B 1 月 製品型番 3月 4月 5月 2 3 M102 M102,S111 3 3 L101 L101 4 3 M102 0666 5 3 S111 6 3 O666 7 4 M102 8 4 M102 9 4 L101 10 4 M102 11 4 O666
実際のデータは2000件くらいあり、半年分あるので、3月から8月分まで 製品型番は10種類くらいです。通常一つの型番ごとにカウントするのですが、 この例では「S111」にあたる製品が極端に少ないので、他の製品に混ぜて カウントすることになったのです。 自分なりに考えて、以下のような式を作成してみたのですが
=SUMPRODUCT(($A$1:$A$11="3")*(OR($B$1:$B$11="M102",$B$1:$B$11="S111"))） =SUMPRODUCT(($A$1:$A$11="3")*($B$1:$B$11="M102")*($B$1:$B$11="S111"))
「3」とカウントすべきなのに、両方とも「0」になってしまいます。 A列が「3」でB列が「M102」か「S111]という意味で単純に考えた式です。 ご指導、宜しくお願いいたします。
[Excel2002、2003][WindowsXP]
こういうことでしょうか？ =SUMPRODUCT(($A$1:$A$11=3)*(($B$1:$B$11="M102")+($B$1:$B$11="S111"))) （キリキ）（〃⌒o⌒)b
キリキさん 括弧が・・・・。(汗)
(HANA)
えっ？ 何のこと？？？ （；￣з￣）〜〜♪ 壁|；）彡ササッ （キリキ）（〃⌒o⌒)b＜HANAさん、いつもありがと〜☆
良かったです。ドキドキしました。(笑)
(HANA)
出来ました〜。 キリキさん、HANAさん、ありがとうございます。 HANAさん、毎度お世話になっております。 m(__)m
*（かける）のではなく+（たす）のですね。
助かりました〜。
せっかく教えてもらった方法(関数）を応用できず、使いこなせないで 申し訳ないです。
以前薦められた、DCOUNT関数でも試してみたのですが、 DCOUNT関数は、もっと分からなかった(出来なかった）です。 これからも、宜しくお願いいたします。 (ranmal)
今日、仕事で教えていただいた方法で試してみたら、なぜか出来ないんです。
実際の数より、多くカウントされてしまいます。 実際の表は文字なので、そのせいも多少あるのかと 単純にＡ列に3、4、5、と数字を入れ、B列にA、B、C、とアルファベットを入力し、 行を30行位に増やして試したところ、やっぱり多くカウントされてしまいました。
=SUMPRODUCT((A1:A30="4")*(B1:B30="A")+(B1:B30="C"))
実際は、10なのに13とカウントされて今います。 会社のExcelが2002で家のが2003なので、そのせいもあるのかと 家でも試しましたが、同じ結果でした。 範囲が短いとちゃんとカウントしてくれます。 (ranmal）
実際のデータが見えないので、どうなっているのかわかりませんが、、、
=SUMPRODUCT((A1:A30="4")*(B1:B30="A")+(B1:B30="C"))
~~~
"4" ですと、文字列の「4」ですが大丈夫ですか？
また、検証の方法として
数式の入っているセルをアクティブにし、
ツール → ワークシート分析 → 数式の検証
【検証】のボタンを押すごとに、数式を紐解くことが出来ますので何処が違うかを
確認することが出来ます。
（キリキ）（〃⌒o⌒)b
数字の4(=4）や4を入力したセルを参照(=E5や=A10等）させても同じ結果でした。
検証をやってみたのですが、"4"や参照したセルに下線がつき 4の数だけFALSEとなっています。13というのは4の数でした。 "A"は7個FALSEになり、"C"は3個FALSEになります。 最終的に=13になります。 以下が実際の試したデータです。幅を取ってすみません。
A列 B列 3 A 3 B 3 B 3 B 3 C 3 C 3 C 3 A 3 C 4 B 4 C 4 C 4 B 4 C 4 A 4 C 4 C 4 C 4 B 4 A 4 A 4 C 5 C 5 B 5 B 5 B 5 A 5 A 5 B 5 B 5 A 5 C
(ranmal）
あぁぁぁ〜〜〜 ranmalさ〜ん！ σ(^o^;)と同じ間違いしてる・・・ かっこの位置が。。。 （すぐに気が付かないσ(^o^;)って・・・orz） 更に、やっぱり "4"（文字） と、4（数字）は別物です。 だから、数式の検証でも FALSE になるのです。 =SUMPRODUCT((A1:A30=4)*((B1:B30="A")+(B1:B30="C"))) コレでどう？ （キリキ）（〃⌒o⌒)b
わ〜〜〜できました〜〜〜(^O^)／
最所にHANAさんが言っていた・・括弧ですか？ 4・・も・・原因・・ エラーにならないので、間違いないと思い込んでいました。
ごめんなさい。何度も教えてもらっているのに〜。 そして、ありがとうございます。
今日はもうひとつ出来ないことがあって・・ それは、SUMPRODUCT関数ではないので、新規に質問させていただきます。
本当に本当にありがとうございましたm(__)m （ranmal)
出来てよかった＾＾ 初めから、σ(^o^;)がちゃんとしたものを提示していれば・・・ ごめんなさいね＾＾； （キリキ）（；⌒o⌒)b
解決しちゃいましたね。 （いや、決して残念がっているわけでは無いのですよ。）
>以前薦められた、DCOUNT関数でも試してみたのですが、 DCOUNT関数は、一つの関数の条件を指定するときに 重複せずに条件の見出しが必要になるようなので 今回のケースには向かない様な気がしてきました。 これは、今度機会があったときに使ってみて下さい。 (「この条件の時にどんな値になるか調べたい」とか言うときに 使えるような気がします。ちなみに、今回のを言葉にすると 「各条件の時にどんな値になるか調べたい」ですかね・・・。）
その変わりと言っては変わりになるか分かりませんが 「ピボットテーブル」とか言うものもあったりしますよ。
そうそう、(4も原因ですが)括弧の位置ですよ。
最初の式
>=SUMPRODUCT((A1:A30=4)*(B1:B30="A")+(B1:B30="C"))
では、 A1:A30が4×B1:B30がA ＋ B1:B30がC
~~~~~~~~(1)^^^^^^^^(2) ~~~~~~~~~(3)
(1)と(2)が共に成立したものに(3)が成立したものを足し算したのが結果になります。
「A1:A30が４でB1:B30がAのものと、B1:B30がCのものの件数」
今回は(1)*(2)=3 と (3)=12 を足したので間違った結果15
>=SUMPRODUCT((A1:A30=4)*((B1:B30="A")+(B1:B30="C")))
では、 A1:A30が4 × (B1:B30がA ＋ B1:B30がC)
~~~~~~~~~(1) ^^^^^^^^^^(2) ~~~~~~~~~~(3)
(1)と(2)あるいは(3)が共に成立したのが結果です。
「A1:A30が４で、B1:B30がAかB1:B30がCのものの件数」
(2)と(3)が同時に成立することがないので、例えば (1)と(3)の条件が満たされたものが１件、 (3)の条件のみ満たされたものが１件あった場合 最初の式では、1* 0+1 +0* 0+1 =1*0 +1 + 0*0 +1 =2 後の式では、 1*(0+1)+0*(0+1)=1*1 + 0*1 =1 結果が違ってきます。
とても単純に行くなら =SUMPRODUCT((A1:A30=4)*(B1:B30="A"))+SUMPRODUCT((A1:A30=4)*(B1:B30="C")) でも良いかもしれません。 同じ所は X*a + X*b = X(a+b) みたいな要領で纏めると後の式と同じになります。
・・・ってな感じですがどうでしょう？
(2)と(3)が同時に成立する場合は、足しておいてから両方成立した分を 引く必要が出てきます。
(HANA)
とんでもないです。きりきさん。私こそ教えてもらって ちゃんと、確認していれば、よく読めば分かることだったのです。 でも、そのおかげで？より深くSUMPRODUCT関数のことが理解できたし 良かったと思っています。
HANAさん、いつもお手数おかけします。m(__)m とても、分かりやすい解説です。以前にリンク貼っていただいたところの 説明だと文字のカウントではなく、計算が主体のものだったので、 いまいち分かりにくかっのですが、HANAさんの解説は分かりやすく 理解できました。本当にいつもありがとうございます。
別の質問になってしまいますが、ピボットテーブルは、簡単なものだと 集計等簡単に出来るので 好きで良く使います。 今回のデータ集計でも、どうにか使えないかチャレンジしましたが、 あきらめました。 データをそのままピボットテーブルにしようとするとエラーになってしまい 例えば、データアイテムの所など金額とか集計ではなく、カウントするのは 無理だと思い（あきらめ）ました。 ああ〜、なんか変なこと(意味不明なこと）言ってますよね。うまく説明できません。 あとで、考えてることまとめて、新規に質問しますので、宜しくお願いします。
今後とも宜しくお願いします。 (ranmal）
おっと・・・書いている間に。
ちなみに、一番最初の下側の式 >=SUMPRODUCT(($A$1:$A$11=3)*($B$1:$B$11="M102")*($B$1:$B$11="S111")) "3"→3 にしても、上手く行きません。 条件を全てかけ算してしまうと 「A列が3で、B列がM102で、B列がS111 のものの件数」 を計算してしまう為です。 B列にM102とS111が同時に成立することは無いので、 （一方がTRUEになっても、もう一方が必ずFALSE） 最終結果はやっぱり ０ になってしまいます。
一番最初の上側の式
>=SUMPRODUCT(($A$1:$A$11=3)*(OR($B$1:$B$11="M102",$B$1:$B$11="S111"))）
なのですが、 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
上手くいきそうなのに、"3"→3 にしても上手く行きません。
OR関数は、一つでもTRUEならTRUEなので TRUE
「A列が3で、B列にM102かS111が有った場合 その件数」
を計算してしまう為と思います。
B列にM102かS111のどちらかは存在しており(OR関数常にTRUE)
A1:A11の中に3が５つあるので、結果は５になると思います。
A列の3を増やしたり減したり、B列のM102とS111を全て消したりすると
「こんな事かな？」と想像出来る結果が表示されると思います。
ピボットテーブルで >例えば、データアイテムの所など金額とか集計ではなく、カウントするのは >無理だと思い（あきらめ）ました。 フィールド上で右クリック。「フィールドの設定」の中の 集計の方法で、「データの個数」とか選べますが、 それは試してみてダメだったのですかね？
(HANA)
・・・・今回も追記。
ピボットテーブルを使用しようとしたときに もしも、月の所が日付で入力されている場合は こんな感じでグループ化をすれば良いと思います。 [[20060928210249]]『ピボットテーブルについて』（dokken）
ただし、こんな事もあるので元データには注意が必要です。 [[20031219144825]]『ピポットテーブルで行をグループ化したい』（tamaura310）
ピボットテーブル入門 https://www.excel.studio-kazu.jp/lib/e2d/e2d.html ・・・こちらもカウントではなく合計する手順ですが 「ライブラリ」のリンクです。
(HANA)
HANAさん、 OR関数の疑問まですっきりと解決していただき ありがとうございます。他のことは今までの解説でとても良く 理解できたのですが、OR関数は何故駄目なのか、すっきりとしませんでした。 すっきりです。 今日は、お蔭様で全国のデータを一つのシートで地域をリストから選択すれば 地域ごとに自動集計できるという、すばらしい表が完成しました。 HANAさんたちの指導がなかったら、絶対に無理でした。 これからも、更に進化させた表を考えながら力をお借りしながら 作成しようと思っています。 ピボットテーブルを利用して作成する時間(余裕）もできたので、 挑戦してみようと思います。
> 集計の方法で、「データの個数」とか選べますが、 あぁ〜試してないです・・。 試してみます。 今は、「フィールドの名前が 無効です・・」というエラーが出て ピボット化すらできないので、ピボット用にフィールドを整え 挑戦してみます。
分からないことがあったら、また新規に「ピボット」で質問して 教えていただきたいと思いますので、是非是非宜しくお願いいたします。 (ranmal）
HANAさん、データの個数で出来ました。(^.^)
「フィールドの名前が無効です」はフィールドの名前が無い 列(集計用の計算式の列）があることに気づき、名前を付けたら ピボット化出来ました。そして、出来たんです。 「データの個数」でちゃんとカウントしてくれました。 これから、色々集計表を作成していこうと思いますので まだまだ壁にぶつかることもあると思いますが、 HANAさんの助言で、ピボットでカウントでき、道が出来たので まずは、ご報告まで。
(ranmal）
それは良かったです。
どんどん頑張って下さいね。
(HANA)
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.