[[20070618223029]]　『SUMPRODUCT』（はな）　ページの最後に飛ぶ

『SUMPRODUCT』（はな）

 お世話になります。
 SUMPRODUCTについて、いろいろ検索しており、
 自分のやりたいところまで近づいたと思ってはいるのですが
 どうしても最後の結果が出せないで居ます。
 ご指導いただければと思いますのでよろしくお願いします。

 SHEET1　は台帳になっています。
 日々あがってくる伝票を、あがってきた順番に採番し入力していきます。
 台帳のB列が項目（みかん、りんご、ばなな、いちご、なし）とあります。
 D列には、その項目のための伝票を起票した日にちが入ります。

 　A　　B　　　C　　D
 　　　みかん　　　6月4日
 　　　りんご　　　6月5日
 　　　みかん　　　6月5日
 　　　みかん　　　6月5日

 こんな感じです。

 SHEET2　は集計シートです。
 A列には、6月1日から9月30日までの日付が入っています。
 B列～F列の2行目以降は、台帳に記入されている項目及び日付ごとの
 件数が入るようにしたいのです。

 　A　　　　B　　　C　　　　D　　　E　　　F
 　　　　みかん　りんご　ばなな　いちご　なし
 6月4日　　1
 6月5日　　2　　　1

 たとえば、B3のセルに
 =SUMPRODUCT((Sheet1!$D$2:$D$399=$A3)*1)
 と日付（この場合6月5日）による件数を確認する関数式を入れれば、答えは「3」と出ます。

 また、B3のセルに
 =SUMPRODUCT((Sheet1!$B$6:$B$399=$C1)*1)
 と、項目による件数を確認する関数式を入れれば、
 この場合「りんご」を数えて答えは「1」と出ます。

 ところが、合体させて
 A列の日付の1行目の項目について確認しようと
 =SUMPRODUCT((Sheet1!$B$2:$B$399=$B1)*(Sheet1!$D$6:$D$399=$A2))
 という式を入れると、「0」になってしまうのです。

 どこが間違っているのか検討がつかないのです。
 教えていただけますか。

 式をよく見てください。
 Sheet1!D列の範囲が間違っています。
 Sheet2!B1の絶対参照も間違っています。
 =SUMPRODUCT((Sheet1!$B$2:$B$399=Sheet2!B$1)*(Sheet1!$D$2:$D$399=Sheet2!$A2))
 (wisemac21）

 衝突しましたが、そのまま載せておきます。

 御提示の表を使った場合での御話しですかね？
 それとも、御提示の表は　あくまでもサンプルであり
 上手く行かない表とは別の表ですかね？

 もしも、同じ表で上手く行かないと言うのであれば
 もう一度、上手く行くところまで正しくご説明下さい。

 ＞たとえば、B3のセルに
 ＞=SUMPRODUCT((Sheet1!$D$2:$D$399=$A3)*1)
 これは、C3のセルに入れた時も「３」となりますか？

 ＞また、B3のセルに
 ＞=SUMPRODUCT((Sheet1!$B$6:$B$399=$C1)*1)
 「りんご」を数えて・・・と有りますので
 C3のセルに入れた時の話しですよね？
 そして、これは「１」となるのですよね？
 （Sheet1の範囲がB6から始まっているのが不可解ですが・・・。）

 ＞ところが、合体させて
 ＞=SUMPRODUCT((Sheet1!$B$2:$B$399=$B1)*(Sheet1!$D$6:$D$399=$A2))
 ひとまずC3のセルに、合体させた式
=SUMPRODUCT((Sheet1!$D$2:$D$399=$A3)*(Sheet1!$B$6:$B$399=$C1))
 を試してみるとどうなりますか？
 やはり「0」になりますかね？

 (HANA)

wisemac21様、HANA様

 ありがとうございます。

 Sheet1!D列の範囲、すみません。
 HANAさんの仰るとおり、
 こちらの質問ボードに載せるために、実際に入力している数字（6行目）から
 2行目に編集したのですが、編集漏れでした。
 ですので、使っているD列の範囲は、間違っていない状況です。

 さらにHANAさんの、

 ＞＞たとえば、B3のセルに
 ＞＞=SUMPRODUCT((Sheet1!$D$2:$D$399=$A3)*1)
 ＞これは、C3のセルに入れた時も「３」となりますか？

 これは、「３」となります。
 C列にこの式をドラッグして入れると、絶対参照が$A4、$A5、$A6・・・となり
 A列で示している日をきちんと拾います。

 ＞＞また、B3のセルに
 ＞＞=SUMPRODUCT((Sheet1!$B$6:$B$399=$C1)*1)
 ＞「りんご」を数えて・・・と有りますので
 ＞C3のセルに入れた時の話しですよね？
 ＞そして、これは「１」となるのですよね？
 ＞（Sheet1の範囲がB6から始まっているのが不可解ですが・・・。）

 ここも、焦って質問してしまいました。
 そうです、B3のセルではなく、C3のセルで「りんご」を数えます。

 wisemacさんの仰る、Sheet2!B1の絶対参照　
 これについては、この場合は確かに 行が絶対参照ですね。間違いました。
 また、Sheet2!　もつけてみました。(つけた方が確実ですか？）

 上記の点を修正しても、今のところ結果は「0」で、期待する値にならないのです。

 ちなみに、「6月4日」の「みかん」の数を計算する場合に
 B2のセルに、絶対参照をやめて
 =SUMPRODUCT((Sheet1!B2:B399=Sheet2!B1)*(Sheet1!D2:D399=Sheet2!A2))
 と入力しても「0」なのです。

 根本的に何か間違っているのでしょうか？　（はな）

 Sheet1の表は何行目から始まっていますか？
 もしも、B1にみかん　D1に6月4日　と入っている
 （１行目から始まっている）なら
 6月4日のみかん（１行目）を数えるために
 ＞=SUMPRODUCT((Sheet1!B2:B399=Sheet2!B1)*(Sheet1!D2:D399=Sheet2!A2))
 この式では数えられない
 のは普通だと思いますが？

 使用しているシート構成を教えてください。

 また、まずは同じシートで試してみてはどうでしょう。
 Sheet2に該当する表を例えば、F列から作る　等して。

 (HANA)

 一番ご確認なさりたいことに答えていませんでした。
「うまくいかない」とご提示の
 ＞=SUMPRODUCT((Sheet1!B2:B399=Sheet2!B1)*(Sheet1!D2:D399=Sheet2!A2))
 この式ですが、
 Sheet2のB1とA2と同じ文字がSheet1のB列D列の同じ行にそれぞれ存在すれば
 求めるものは得られると思います。
（特に変な式だとは思いません。）

 ためしに
 Sheet2!B1をSheet1!B2
 Sheet2!A2をSheet1!D2
 に貼り付けてみてはどうでしょう。
 はなさんが「同じ内容」だと思っていても
 エクセル君は「違う内容」だと思っている可能性もあります。

 また、
 ＞また、Sheet2!　もつけてみました。(つけた方が確実ですか？）
 これは必要ないと思います。ただしあったからといって
 致命的なことになる可能性はかなり低いと思います。
 「式が長くなって嫌だ」と思えば無くて良いでしょうし
 「あった方が分かりやすい」と思えば、あっても良いと思います。

 (HANA)

 HANAさん

 ご丁寧に、本当にありがとうございました。
 中途半端に理解し（したつもりになって）SUMPRODUCTを使っていたのですが
 今日、HANAさんの書いてくださった内容を読み、
 もう一度自分の作った式を見て、さらに、台帳になっているSheet1をまじまじと眺めました。

 そうしたら、とんでもない、お話しするのも申し訳ないような勘違いに気づいてしまいました。

 私、台帳のD列には、記入者とその記入日を2行に書いて
 2行で１項目としていたのです。
 そして、SUMPRODUCTで「配列」なのに対応させる行がずれていたのです。
 つまり、「日付だけ」「項目だけ」を見るような式では
 探しだすのは１つの条件なのできちんと数えられたのですが、
 複数の条件の組み合わせにした途端、「0」になってしまうのは
 B列の2行目（項目）とD列の2行目（記入者）を見ていたのです。
 対象となる同じ行に日付がこないといけないということに、本当に先ほど気づきました。

 ここが、中途半端に分かった気になっていた私の落とし穴でした。

 その後、日付を2行目に入力し直し、
 =SUMPRODUCT((Sheet1!B2:B399=Sheet2!B1)*(Sheet1!D2:D399=Sheet2!A2))
 この式を入力したら、無事、期待する結果を得ることができました。

 wisemac21さん、HANAさんに、いろいろ考えていただき、本当に申し訳ありませんでした。

 自分の持っている情報を忠実に再現していれば
 もっと先の段階で「同じ行に見たい条件を入れないと数えない」など
 教えていただけたのだろう、と、反省しております。

 実は、一番最初はSUMPRODUCTにワイルドカードを入れて式を作ってみたところから
 今回の作業はスタートしたのです。
 その後、こちらのサイトの検索でSUMPRODUCTの色々な視点からのアドバイスを読み
 そして、wisemac21さん、HANAさんに教えていただきながら勉強することができました。

 本当にありがとうございました。

 表が
	[A]	[B]	[C]	[D]
[1]	A	B	C	D
[2]		みかん		HANA
[3]				6月4日
[4]		りんご		HANA
[5]				6月5日
[6]		みかん		HANA
[7]				6月5日
[8]		みかん		HANA
[9]				6月5日
 この様になっていた　と言うことですかね？
「上から数えて同じ順番」になればよいので
 D列の参照範囲をずらすだけでも良いかもしれませんね。
=SUMPRODUCT((Sheet1!B2:B399=Sheet2!B1)*(Sheet1!D3:D400=Sheet2!A2))
　　　　　　　　　　　　　　　　　　　　　　　 ~~~~~~~
 この様にすると、
 　Sheet1のB2 = B1　と　Sheet1のD3 = A2 が
 　共にTRUEの時「1」
 と言った感じで計算されていきます。
 最後のD400は不必要に感じるかもしれませんが、B399の相方ですので欠かせません。

 ただ、今後エクセルで計算（集計）をしたいと思うので有れば
 一行一レコードの形にしておくのが良いと思います。
	[A]	[B]	[C]	[D]	[E]
[1]	A	B	C	D	E
[2]		みかん		HANA	6月4日
[3]		りんご		HANA	6月5日
[4]		みかん		HANA	6月5日
[5]		みかん		HANA	6月5日
 エクセルへの指示の出し方が格段に簡単になります。

 (HANA)

 HANA様

 参照範囲をずらすだけで良いなんて！！！！！
 ものすごくびっくりしました。
 絶対「同じ行同士を対照させないといけない」と思い込んでいました。
 本当に、まったくSUMPRODUCTを分かっていませんね、私。

 ＞ただ、今後エクセルで計算（集計）をしたいと思うので有れば
 ＞一行一レコードの形にしておくのが良いと思います。

 はい、今回はしみじみ思いました。

 一歩も二歩も踏み込んだアドバイスを、本当にありがとうございました。
 とても　ためになりました。

 （はな）

 配列を扱う物は難しいですからね。
 せっかくですのでもう少し書いておきます。
 例えば「6/5 の　みかん」を探したいとします。

 参照範囲の始まりを同じにした時
 SUMPRODUCT関数は、目に見えない所で
 E,F列の様に条件を判断し、
 G列の様にそれぞれをかけ算し、その合計を求めます。
	[B]	[D]	[E]	[F]	[G]
[1]	B	D	みかん？	6/5？	E2*F2
[2]	みかん	HANA	TRUE	FALSE	0
[3]		6月4日	FALSE	FALSE	0
[4]	りんご	HANA	FALSE	FALSE	0
[5]		6月5日	FALSE	TRUE	0
[6]	みかん	HANA	TRUE	FALSE	0
[7]		6月5日	FALSE	TRUE	0
[8]	みかん	HANA	TRUE	FALSE	0
[9]		6月5日	FALSE	TRUE	0
 G列を足し算した物が、SUMPRODUCT関数の結果です。
 B列がTRUEとなったとしても、その行は必ずFLASEです
（日付でないため）ので、結果も必ず「0」になります。

 そこで、F列を「一行下が6/5か？」と言う確認に変えてみます。
	[B]	[D]	[E]	[F]	[G]
				１行下	
[1]	B	D	みかん？	6/5？	E2*F2
[2]	みかん	HANA	TRUE	FALSE	0
[3]		6月4日	FALSE	FALSE	0
[4]	りんご	HANA	FALSE	TRUE	0
[5]		6月5日	FALSE	FALSE	0
[6]	みかん	HANA	TRUE	TRUE	1
[7]		6月5日	FALSE	FALSE	0
[8]	みかん	HANA	TRUE	TRUE	1
[9]		6月5日	FALSE	FALSE	0
[10]					
 これで、G列を足し算で　求める結果が得られます。
「一行下が」と言う考え方が、D2:D399→D3:D400
 に変更した原因です。

 今回は、D列を一行下にシフトさせましたが
 B列を一行上にシフトさせても同じ結果になります。

 (HANA)

 HANA様

 参照する行のスタートを設定するんですね。
 B列の2行目とD列の3行目から始めて、順に見ていくということですね。
 HANAさんのご説明で、しくみがとても良くわかりました。

 これからは、もう少し応用利かせられるような気がします。

 ちなみに、今回の複数条件で数を数える場合は
 SUMPRODUCTが一番適切ですか。

 どうしても出来なかったとき、他の関数を使ってみようかとも思ったのですが
 結局できませんでした。

 COUNTIFだと一つの条件だけど、入れ子にすると出来るのか？
 （↑思ってみただけで、式すら出来上がらなかった）
 DOCUNTAは？　など。

 他の式では、結局できませんでしたが、あれこれ悩んで頭使って、
 エクセル、本当に面白いですよね。
 関数ばっちりの方だと、もう、面白いなんて通り越しているのかな？

 （はな）

 一番理解しやすいのは、
「作業列を使って、COUNTIF関数」かもしれません。
 C列を作業列にしてC2に
=A2&"_"&B2　として下にコピーします。
 B9に
=COUNTIF($C$2:$C$5,B$8&"_"&$A9)
 として右,下にコピー。
	[A]	[B]	[C]
[1]	項目	日付	作業列
[2]	みかん	6月4日	みかん_39237
[3]	りんご	6月5日	りんご_39238
[4]	みかん	6月5日	みかん_39238
[5]	みかん	6月5日	みかん_39238
[6]			
[7]			
[8]		みかん	りんご
[9]	6月4日	1	0
[10]	6月5日	2	1

 作業列もSUMPRODUCT関数も使わない　となると
 B9に
=SUM(IF($A$2:$A$5=B$8,1,0)*IF($B$2:$B$5=$A9,1,0))
 として、【Ctrl + Shift + Enter】で確定後
 右,下にコピー。
 これは「配列数式」と呼ばれる物です。
 上で、E列F列にTRUE,FALSEと書きましたが
 IF関数を使い、TRUE=1,FALSE=0に置き換えた状態です。
 IF関数の結果をそれぞれかけ算して、足し算します。

 上の二つの式は、B列に名前も一緒に入っている場合
 を想定するなら、同じ要領でシフトさせてください。

 DCOUNTAは、一つの式毎に調べたい項目が並んでいる
 必要があると思いますので、今回のような表に
 集計したいときは使いづらいと思います。

 もしも、１行１レコードの形でデータが作って有れば
 ピボットテーブル　を使うのが簡単かもしれません。
https://www.excel.studio-kazu.jp/lib/e2d/e2d.html

 エクセル、おもしろいですよね。
 どれが一番適切か　は、色々なやり方がありますし
 使う方の考え方によっても変わってきます。
 扱うデータ量によっても変わってきます。
 いろいろな手法を知っておいて、その時々で色々試してみて
 状態に合う方法が見つかったとき、漸くそれが
 その時に一番適切だ　と言えるのかもしれません。

 (HANA)

 HANA様

 結局、ひとっとびにラクしようと思わないで、
 作業列作るなど地道な作業が、確実っていうことですよね。

 ＞作業列もSUMPRODUCT関数も使わない　となると
 ＞B9に
 ＞=SUM(IF($A$2:$A$5=B$8,1,0)*IF($B$2:$B$5=$A9,1,0))
 ＞として、【Ctrl + Shift + Enter】で確定後
 ＞右,下にコピー。
 ＞これは「配列数式」と呼ばれる物です。

 これも、中途半端に知っていただけなので、全然完成しなかった式の一つです。
 今、こうやって解説していただくと、とても良く分かります。

 このところ、梅雨だからなのか、
 湿度も高く、じとっとした暑さの日が続いています。
 体調など崩さぬよう、ご自愛ください。

 （はな）

コメント返信：

[ 一覧(最新更新順) ]

[[20070618223029]] 『SUMPRODUCT』（はな） ページの最後に飛ぶ

[[20070618223029]]　『SUMPRODUCT』（はな）　ページの最後に飛ぶ