- ベストアンサー
SUMPRODUCT関数について
アンケートの集計をするにあたって、 SUMPRODUCT関数を使いました。 シートが2つあります。 【データ】シート・・・アンケートのデータ A:No. B：データ 1 0 2 1 3 2 4 3 5 空欄が入っている ・ ・ 【集計】シート・・・B列の空欄、1～3の個数を集計する B列に数式 =SUMPRODUCT((データ!$A$4:$A$40000<>"")*(データ!B$4:B$40000=$A4)) を入れました。 A B 0 2 ・・・・※ 1 1 2 1 3 1 空欄 2・・・・※ ０（ゼロ）と空欄を区別しないで同じと計算してしまいます。 データに、ゼロと空欄が混在している場合、SUMPRODUCTは使えないのでしょうか？ この場合、どの数式を使うのがベストでしょうか？ 分かりにくくて申し訳ありませんが、 宜しくお願い致します。
- katakko
- お礼率90% (85/94)
- オフィス系ソフト
- 回答数5
- ありがとう数4
- みんなの回答 （5）
- 専門家の回答
質問者が選んだベストアンサー
こんにちは。maruru01です。 =SUMPRODUCT((データ!$A$4:$A$40000<>"")*(データ!B$4:B$40000=$A4)) ↓ =SUMPRODUCT((データ!$B$4:$B$40000<>"")*(データ!B$4:B$40000=$A4)) は、単なる記述ミスですか？ 下の式なら、「0」と空欄は区別されると思いますが。 ちなみに、空欄の方は、別の数式で、 =SUMPRODUCT((データ!$B$4:$B$40000="")*1) になります。 ひょっとして、 =SUMPRODUCT((データ!$A$4:$A$40000<>"")*(データ!$B$4:$B$40000<>"")*(データ!B$4:B$40000=$A4)) =SUMPRODUCT((データ!$A$4:$A$40000<>"")*(データ!$B$4:$B$40000="")) ということかな。
その他の回答 (4)
- kaisendon
- ベストアンサー率44% (114/257)
２です ＞（計算方法手動は他のブックにも反映されてしまうので 勘違いでした。 他のブックには反映されません。 他のシートには反映されます。 失礼しました～ o(_ _)o
- mshr1962
- ベストアンサー率39% (7418/18948)
=SUMPRODUCT((データ!$A$4:$A$40000<>"")*(データ!B$4:B$40000&""=$A4&"")) でブランクを0にしないようにするか =SUMPRODUCT((データ!$A$4:$A$40000<>"")*(データ!B$4:B$40000<>"")*(データ!B$4:B$40000=$A4)) とブランク行を排除する。
お礼
早速の回答、ありがとうございます。 （行番号初めをずっと$4としていましたが、$1の間違いです。申し訳ありません。） >=SUMPRODUCT((データ!$A$4:$A$40000<>"")*(データ!B$4:B$40000&""=$A4&"")) >でブランクを0にしないようにするか すっきり出来ました！ 色々できるのですね。 ありがとうございました。
- BLUEPIXY
- ベストアンサー率50% (3003/5914)
=SUMPRODUCT((データ!$A$4:$A$40000<>"")*(EXACT(データ!B$4:B$40000,$A4))) でどうでしょう？
お礼
早速の回答、ありがとうございます。 そして、BLUEPIXYさんも、以前お世話になりました。ありがとうございます。 （行番号初めをずっと$4としていましたが、$1の間違いです。申し訳ありません。） 同じ数式でB1～B5がしっかり計算できました！ ありがとうございました。
- kaisendon
- ベストアンサー率44% (114/257)
こんにちは =SUMPRODUCT((データ!$A$4:$A$40000<>"")*(データ!B$4:B$40000="")) という式を作ってその数を引くというのは如何でしょうか？ 同様の考え方で 集計用シートのB4だけ（B5以降はご質問の式でいいです） =SUMPRODUCT((データ!$A$4:$A$40000<>"")*(データ!B$4:B$40000=$A4))-SUMPRODUCT((データ!$A$4:$A$40000<>"")*(データ!B$4:B$40000="")) 蛇足ですが。。。 ただでさえ重いと思います。 オプションで計算方法を手動に設定して F9で再計算させた方が入力が楽になると思います。 （計算方法手動は他のブックにも反映されてしまうので 処理が終わったら「自動」に戻しておく事をお忘れなく＾＾）
お礼
早速の回答、ありがとうございます。 （行番号初めをずっと$4としていましたが、$1の間違いです。申し訳ありません。） >集計用シートのB4だけ（B5以降はご質問の式でいいです） >=SUMPRODUCT((データ!$A$4:$A$40000<>"")*(データ!B$4:B$40000=$A4))-SUMPRODUCT((データ!$A$4:$A$40000<>"")*(データ!B$4:B$40000="")) うまく出来ませんでした・・・。 >ただでさえ重いと思います。 そうなんです！重いんです！ 計算している時、すごいパソコンががんばってるんです！ 気をつけます。 ありがとうございました。
関連するQ&A
- SUMPRODUCT関数で複数条件適用されない
Sheet1 A列：日付 B列：注文番号 C列：品名 D列：整理番号 E列：数量 集計 A列：品名 B～AF列：日付（1～31日） 【集計】B～AF列の日付、A列の品名が 【Sheet1】A列の日付、C列の品名と一致したら 【Sheet1】E列の数量を【集計】の該当するセルに抽出する 式をSUMPRODUCT関数で作成しました。 例）10/1 りんご 20 【集計】B3セルに以下の数式を入れましたが なぜか数量が「0」のままになります。 =SUMPRODUCT('Sheet1'!$A$3:$A$200='集計'!B$2)*('Sheet1'!$C$3:$C$200="りん ご")*('Sheet1'!$E$3:$E$200) 特に数式のミスはないと思うのですが どうしても正しい値が入りません。 数式に間違いがあればご指摘いただけませんでしょうか。 また、他に良き方法があればご教示いただけるとありがたいです。
- ベストアンサー
- その他MS Office製品
- SUMPRODUCT関数で0(ゼロ)と
はじめまして。arumikan091と申します。 （Excel初心者です(^^;） ExcelのSUMPRODUCT関数について質問があります。 A B (1) 2 0 (2) 2 4 (3) 2 (空白) (4) 2 3 (5) 2 0 (6) 3 4 (7) 3 (空白) というシートの中で、 "A列が2で、かつB列が0の個数を数える" ということをしようと思い =SUMPRODUCT((A1:A7=2)*(B1:B7=0)) と入力したのですが、 これだとB列の0(ゼロ)と一緒に""(空白)も 数えられてしまうようです。 (上記シートだと式の期待値は2なのですが、 実際値は3が返ってきます) このような場合、0(ゼロ)だけを数えられる ようにするにはどのようにすればいいか 教えていただけたら幸いです。 一応、自分でも少ない知識の中で色々トライしてみて、 =SUMPRODUCT((A1:A7=2)*(B1:B7=0))-SUMPRODUCT((A1:A7=2)*(B1:B7="")) のように式を入力するとちゃんと0(ゼロ)だけを 数えてくれるようなのですが、さらに簡単な 方法がありましたら教えてください。 Windows ME、Excel2000です。 ※１ B列単独で =COUNTIF(B1:B7,0)とするとちゃんと 0(ゼロ)だけを数えてくれます。 ※２ 一応既存のQ/A集で同じ質問がないか探しましたが、 (「SUMPRODUCT 空白セル」のANDで） 同様、類似の質問が既にありましたら 申し訳ありません。 長文失礼しました(m_ _m
- 締切済み
- Windows Me
- エクセル SUMPRODUCT関数について教えてください
エクセルで作成した表から ○○かつ●●のデータ個数を求めたく、 SUMPRODUCT関数を こちらで調べておりましたが どうしても解決しない部分があります。 どうぞ よろしくお願いいたします。 下記8桁数字は、日付です。 カウントしたい数は、B列（終了）が、A1セルに入力された年月かつ、区分（C列）が「A」の数です。 =SUMPRODUCT((LEFT(B2:B100,6)=A1)*(C2:C100="A")) こちらで、『B列=A1セル かつ C列="A"』のみのカウントは出来たのですが、 終了延長（D列）が空欄でない場合は、D列日付データの方をカウントする。 早期終了（E列）が空欄でない場合は、D列・B列よりも E列を優先し、カウントする。という条件を付けたいのですが、 Sheet1 A1セルに「200810（数を調べたい年月を入力）」 （開始） （終了） （区分） （終了延長） （早期終了） A列 B列 C列 D列 E列 20061001 20081001 A - - 20070926 20081020 A - - 20071102 20081009 A 20081201 - 20080101 20081013 B - - 20080208 20081010 A - 20080210 ・・・ この場合、「２」となるようにです。 IF、OR関数あたりかな と考えておりますが、SUMPRODUCT関数のどの部分で条件付ければ良いか解りません。 説明下手で大変申し訳ございませんが、ご助力いただければ幸いです。
- 締切済み
- オフィス系ソフト
- SUMPRODUCT関数の値としての#NUM!
Sheet2のB2に、 =SUMPRODUCT((A2=Sheet1!A:A)*1) というような式を入れると、#NUM!が出ます。 Sheet2のA2と一致する、Sheet1のA列の値の個数を求めるにはどうすればいいでしょうか（もっといえば、一致する行のK列の合計を求めたい）。 うえの簡単な式のどこがまちがってるのかわからなくて困ってます。
- ベストアンサー
- オフィス系ソフト
- SUMPRODUCT関数の使い方
Ａ列 aaa aaa bbb bbb ccc ccc と入力して、 「aaa」の個数と「bbb」の個数の合計を求める式を教えてください。 見た限りの答えは、「aaa」が2個、「bbb」が2個で4なのですが Ｂ１に 「=SUMPRODUCT(A:A="aaa",A:A="bbb")」 としたのですが、「0」が返ってきてしまいます。 Ｂ２に 「=SUMPRODUCT(COUNTIF(A:A,"aaa"),COUNTIF(A:A,"bbb"))」 だと、 4が返ってきますが Ａ列を aaa aaa bbb にすると「３」が返ってきてほしいのに、２になってしまいます。 個数の合計を返す関数式をご教授ください。
- ベストアンサー
- オフィス系ソフト
- SUMPRODUCT関数で困っています
エクセル2007にて、SUMPRODUCT関数を使ってデータ集計をしたいが 下記の現象で困っています。 SUMPRODUCTの特徴として参照先(sheet1)の指定範囲 「($B$2:$E$6)の範囲を($B$2:$G$20)」を変更すると SUMPRODUCT関数がn/aになり値が表示できない。 (やりたいこと) sheet1に元データが入力されています。 A B C D E 1 NO 社名 商品 購入日 金額 2 01 A商事 肉 1/3 50円 3 02 B電気 野菜 1/10 70円 4 02 B電気 肉 1/12 50円 5 03 C工業 魚 1/20 60円 6 03 C工業 肉 1/30 50円 sheet2はsheet1より必要なデータを、NOをキーにして、 社名・商品・金額を VLOOKUP関数にて取ってきて表示しています。 A B C D E 1 NO 社名 商品 金額 2 01 A商事 肉 50円 3 02 B電気 野菜 70円 4 02 B電気 肉 50円 5 03 C工業 魚 60円 6 03 C工業 肉 50円 6 03 C工業 鉄 90円 sheet3はsheet2より必要なデータを、NOをキーにしてSUMPRODUCT関数にて取ってきて表示しています。 次の式をB1に入力しています (=SUMPRODUCT((sheet2!$A$2:$A$6=A2)*(sheet2!$B$2:$B$6=B2<>"鉄")*(sheet2!$D$2:$D$6=D2)) A B C D E 1 NO 金額 2 01 50円 3 02 120円 4 03 110円 sheet3はsheet2より必要なデータを、NOをキーにしてSUMPRODUCT関数にて取ってきて表示しています。 次の式をB1に入力しています (=SUMPRODUCT((sheet2!$A$2:$A$6=A2)*(sheet2!$B$2:$B$6=B2"鉄")*(sheet2!$D$2:$D$6=D2)) A B C D E 1 NO 金額 2 03 90円
- 締切済み
- その他MS Office製品
- エクセル SUMPRODUCT と OFFSET
いつもお世話になります。 SUMPRODUCT関数で集計したいのですが、OFFSET関数を組合せてＡ1に関数を入力し、右にひっぱるだけで36ヶ月分を集計したいと思い、下記の計算式を入力したのですが#VALUE!になり困ってます。 A1=SUMPRODUCT((OFFSET(data!$A$1,1,COLUMN(A1)*3-3,99,1)=$G$1)*(OFFSET(data!$B$1,1,COLUMN(A1)*3-3,99,1)=$H$1),(OFFSET(data!$C$1,1,COLUMN(A1)*3-3,99,1))) dataシートには、Ａ列：商品CD、 Ｂ列：営業所CD、 Ｃ列：売上金額 の3列のデータが、36か月分108列あります。 集計するシートのＧ1に商品CDを、Ｈ1に営業所CDを入力すると、Ａ1～Ａ36に集計結果を返したいのですが、教えてくださいませんか
- ベストアンサー
- オフィス系ソフト
- SUMPRODUCT関数について
excel2010 SUMPRODUCT関数で、式の内容が理解できず教えてください。 sheet1に、A22からAM1047までデータがあります。B,E列を参照し、 sheet2のA、Cに設定された内容で抽出するという内容になっています。 具体例を下記に示します。 sheet1の構成 A1~AM21までは題目が記載されています。抽出したいデータ対象ではありません。 A列は見出しの内容でほとんど空欄です。 A427セルに本体、以降空欄が続きA490セルにヘッド、また空欄が続きA544セルに 見出しの内容といった感じです。 B22~B30セルにX001という識別コード（同じものが９） B30~B39セルにX002という識別コード（同じものが９） 以降、識別コードがB1047セルまで入ります。 E22~E30までは、a,b,c,d,e,f,g,h,iという検索内容が入ります。 以降同じ検索内容が続きます。 H22~H1047まで数字のデータが入ります。 上記以外の列は無関係なので説明省略。 下記のイメージです。 A1からAM20までにも何かしら内容が入っていますが、関係ないので説明省略。 A21の様に記載しているのはセルアドレス、その直下は入っている内容です。 A21 B21 E21 H21 名称 コード 検索 3月2日 A22 B22 E22 H22 準備 X001 a 0 X001 b 3 X001 c 2 X001 d 5 X001 e 2 X001 f 0 X001 g 3 X001 h 0 X001 i 0 X002 a 4 X002 b 7 X002 c 0 X002 d 0 X002 e 1 X002 f 3 X002 g 0 X002 h 0 X002 i 3 … A427 B427 E427 H427 本体 X701 a 0 X701 b 0 X701 c 1 X701 d 0 X701 e 2 X701 f 3 X701 g 0 X701 h 0 X701 i 1 … A490 ヘッド X792 a 4 X792 b 7 X792 c 2 X792 d 0 X792 e 1 X792 f 3 X792 g 0 X792 h 0 X792 i 3 sheet2は A1 C1 D1 コード 検索 X001 c 2 X002 c 0 X003 c 0 X004 c 0 X005 c 0 X006 c 0 X007 c 0 X008 c 0 X009 c 0 … X701 c 1 X792 c 2 の様にコードと検索条件でD列にsheet1からデータをひっぱってきたいのです。 下記はsheet2のD27セルに設定した内容です。 =IF(SUMPRODUCT((Sheet1!$B$22:$B$1047=$A27)*(Sheet1!$E$22:$E$1047=$C27)), INDEX(Sheet1!H$22:H$1047,SUMPRODUCT((Sheet1!$B$22:$B$1047=$A27)*(Sheet1!$E$22:$E$1047=$C27)*(ROW(sheet1!$A$1:$A$1026)))),"－") webや過去のokwebのsumproduct関数について調べた上で上記式にたどり着き、この内容で、sheet1の内容をひっぱってくるので良いのですが、 最後の*(ROW(sheet1!$A$1:$A$1026))の部分が理解できません。 疑問の内容は、下記２点 (1)単独でROW(sheet1!$A$1:$A$1026)を実施すると1になります。 しかしながら、 =IF(SUMPRODUCT((Sheet1!$B$22:$B$1047=$A27)*(Sheet1!$E$22:$E$1047=$C27)), INDEX(Sheet1!H$22:H$1047,SUMPRODUCT((Sheet1!$B$22:$B$1047=$A27)*(Sheet1!$E$22:$E$1047=$C27)*1)),"－") としてフィルハンドコピーすると、全ての行が27行目の値になってしまいます。 何故1ではだめなのでしょう？ (2)データの範囲はA22からAM1047なので範囲を下記の様に同じにしてみました。 *(ROW(sheet1!$A$1:$A$1026))→*(ROW(sheet1!$A$22:$A$1047)) とすると全く正しく抽出されません。0になってしまいます。 *(ROW(sheet1!$A$1:$A$1026))が*(ROW(sheet1!$A$1:$A$1025)) の様に設定すると#N/Aとなり、抽出する設定条件で必要なのですが、 この*(ROW(sheet1!$A$1:$A$1026))の意味を教えていただきたく。
- ベストアンサー
- Excel（エクセル）
- SUMPRODUCT関数について
SUMPRODUCT関数について いつもお世話になっています。 以前こちらでSUMPRODUCT関数を教えていただき大変統計がしやすくなり、 仕事が効率化しました。ありがとうございます。 ところで、SUMPRODUCTだと思うのですが、計算式がうまく組み立てられないのでご質問させていただきます。 A列に部門名、B列に実施日が入力されているエクセルシートがあり、 部門ごとの実施件数を出したいのです。 SUMPRODUCT（（A2:A100="○部門"）＊（CAUNTA(B2:B100)）） としてみましたがうまくいきません。 たぶん ＊（CAUNTA(B2:B100)） の部分がいけないのだろうとは思うのですが、どう変化させればいいのでしょうか？ 関数の選択が間違っているのかもしれない、とは思うのですが、適切なものが分かりません。 ご教授お願いします
- ベストアンサー
- オフィス系ソフト
- SUMPRODUCT関数について
SUMPODUCT関数を使ったとき、並び替えをすると参照する値がずれて困っています。 たとえば、ある会に参加した人を調べたいとします。 A B C 1 参加日 名前 出身 2 12/1 aaa 東京 3 bbb 千葉 4 ccc 埼玉 ・ ・ ・ 50 12/4 kkk 千葉 51 ppp 静岡 E F G 名前 出身 参加数 1 aaa 東京 １ 2 bbb 千葉 1 3 ccc 埼玉 2 ・ ・ 50 kkk 千葉 1 51 ppp 静岡 2 A・B列にデータの一覧が入っていて、この中から「aaa」さんかつ「東京」が何回参加したかを調べたいのでG列に、＝SUMPRODUCT(($A$1:$A$2000=E2)*($B$2:$B$2000=F2))という数式を入れています。 A～C列のデータは今は２００くらいなんですが、今後も増えていく予定で２０００は行く予定です。 A～C列はその日ごとの参加者が書いてあるデータ欄で E～Gは参加者の一覧（集計欄）にしています。 ランダムに名前を打っているのであいうえお順に並び替えるために E～G列を名前優先で並びかえをするとG列の参照範囲がずれてしまいます。 これがずれないようにすることはできないでしょうか？ 参加者を数える作業は会が終わったごとにするので随時A・Bのデータが増えていくことになります。 excel2003を使用しています。 参照範囲がずれないようする方法がありましたらお聞きしたいです。 また、いまいちどうやって集計するのが効率的なのかわからず、考えた末この方法にしたのですが もっと簡単に集計できる方法がありましたら教えていただきたいです＞＜ よろしくお願いいたします。
- 締切済み
- その他（ビジネス・キャリア）
お礼
早速の回答、そして、maruru01さん、以前もお世話になり、ありがとうございます。 （行番号初めをずっと$4としていましたが、$1の間違いです。申し訳ありません。） >単なる記述ミスですか？ いえ、そうではなく 【（データ!$A$4:$A$40000<>""）】に関しては、 うまく説明できないのですが 私の考えとしては、 「4000レコードのうちデータがここまであります」と判断させるために、 【（データ!$A$4:$A$40000<>""）】を数式に付け足しました。 >=SUMPRODUCT((データ!$A$4:$A$40000<>"")*(データ!$B$4:$B$40000<>"")*(データ!B$4:B$40000=$A4)) >ということかな。 【(データ!$B$4:$B$40000<>"")】 を付け足せばよかっただけだったのですね。 >ちなみに、空欄の方は、別の数式で、 >=SUMPRODUCT((データ!$B$4:$B$40000="")*1) 【""】だとちゃんと計算されるのですが、 【""】を他の式同様【$A5】とすると、ゼロも一緒に数えてしまうのです。 空欄のみ数式をかえればいいのですが、 こういうことになると、キチッキチッとしてるのが好きなもので・・・。（普段はだらしないですが） ありがとうございました。