• ベストアンサー

エクセル関数（SUMPRODUCT）について教えてください。

SUMPRODUCTを使って、件数を出しています。 　　Ａ　　　　Ｂ（抽出条件を入力） １都道府県　　● ２職業　　　　● ３性別　　　　● ４購買日　　　● 　　　Ａ　　　　Ｂ　　　　Ｃ　　　Ｄ　　　　 11　都道府県　　職業　　　性別　　購買日　　 12　東京　　　　会社員　　男　　　20080901　 13　・　　　　　　・　　　・　　　　・　　　　 14　・　　　　　　・　　　・　　　　・　　　　 15　・　　　　　　・　　　・　　　　・　　　　 上記のような表を作り、A11:E100までデータが入っています。 B1:B5に条件を入れて、C1：C4に =SUMPRODUCT(($A$12:$A$100=$B$1)*($B$12:$B$100=$B$2)*($C$12:$C$100=$B$3)*($C$12:$D$100=$B$4)*1) で件数をカウントしています。 抽出条件をすべて指定すると、件数が出てきますが、特に指定しないで 空欄のままで件数をカウントする方法はないでしょうか？ （たとえば、職業を絞らないでほかの条件だけで抽出するとか） 何か解決策を教えてください。

  • 4to4
  • お礼率50% (2/4)

質問者が選んだベストアンサー

  • ベストアンサー
  • sige1701
  • ベストアンサー率28% (74/260)
回答No.1

こんな感じでいいかな たとえば、職業を絞らないで =SUMPRODUCT(($A$12:$A$100=$B$1)*(($B$12:$B$100=$B$2)+($B$2=""))*($C$12:$C$100=$B$3)*($C$12:$D$100=$B$4))

4to4
質問者

お礼

遅くなりましたが、大変参考になりました。 ありがとうございました。

その他の回答 (3)

  • A88No8
  • ベストアンサー率52% (834/1602)
回答No.4

4to4さん、こんにちは 一般人として私の対応だったらカウント（計数）して分析するならピボットテーブルを使うし、複数条件で抽出（絞り込み）して分析するならフィルターを使います。 すいません、当たり前のことを書いてしまってm(__;m 常に縛られている４つの計数条件にとらわれずに自由にデータをいじり回したい！という意味に受け止めれたので。

  • sige1701
  • ベストアンサー率28% (74/260)
回答No.3

No2さんの回答もおもしろいですね 因みに、ISERRORよりもISNUMBERを使用した方が式が短くなるかな =SUMPRODUCT(ISNUMBER(FIND($B$1,$A$12:$A$100))*ISNUMBER(FIND($B$2,$B$12:$B$100))*ISNUMBER(FIND($B$3,$C$12:$C$100))*ISNUMBER(FIND($B$4,$C$12:$D$100)))

  • hallo-2007
  • ベストアンサー率41% (888/2115)
回答No.2

めちゃくちゃ長くなりますが =SUM(IF(ISERROR(FIND($B$1,$A$12:$A$100)),0,1)*IF(ISEROR(FIND($B$2,$B$12:$B$100)),0,1)*IF(ISERROR(FIND($B$3,$C$12:$C$100)),0,1)*IF(ISERROR(FIND($B$4,$C$12:$D$100)),0,1)) Ctrl+Shift+Enter それぞれのセルで、ISERRORとFIND関数で　文字が含まれると　1 含まれないと　0 にして合計します。 抽出条件が空白のときは　全てのセルが　1　になります。

4to4
質問者

お礼

遅くなりましたが、大変参考になりました。 ＩＳＥＲＲＯＲ、面白いです。 ありがとうございます。

  1. カテゴリ
  2. パソコン・スマートフォン
  3. ソフトウェア
  4. オフィス系ソフト

関連するQ&A

  • エクセル　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?)

    ご存知の方がいらっしゃいましたら教えていただけたら幸いです。 　A列　B列　　C列　　D列… 1　ID　部名　課名　都道府県… 2　1　　A　　　a　　　北海道 3　2　　A　　　c　　　大阪 4　3　　A　　　b　　　東京 5　4　　A　　　a　　　東京 6　5　　A　　　a　　　福島 [シート名：入力] 　　A列　　B列　　C列　D列　　E列… 1　地域　　　a　　　b　　c　　　合計(←上記シート「入力」の課名) 2　北海道　2(Q2)　0　　0　　　2(Q3) 　　東北 3　東京　　1(Q1)　1　　0　　　2 4　関西　　0　　　　0　　1　　　1 [シート名：合計] (Q1)=SUMPRODUCT((入力!$C$2:$C$6="a")*(入力!$D$2:$D$6="東京")) で、地名を１つにしている場合は問題なく反映されています。 (Q2)★ここが一番聞きたいところです★ =SUMPRODUCT((入力!$C$2:$C$6="a")*(入力!$D$2:$D$6="北海道")*(入力!$D$2:$D$6="青森")*(入力!$D$2:$D$6="岩手")*(入力!$D$2:$D$6="宮城")*(入力!$D$2:$D$6="秋田")*(入力!$D$2:$D$6="山形")*(入力!$D$2:$D$6="福島")) で、エラーがでてしまいます。（イヤミではないんですが…）東京など大きなところでは(1)の処理でよいのですが、例えばここだと北海道や青森、秋田…とあまりその都道府県だけでは数の少ないところは「地域」として「北海道・東北」とまとめたく、上の式では、 「aの課で、且つ北海道か、青森か、岩手か、宮城か、秋田か、山形か、福島であればカウントする」としたいのです。 ちなみに(2)の式を入力すると、エラーは出ないのですが該当するものがあるにも関わらずゼロ（２ではなく０）と表示されてしまいます。 (Q3)よって、本当であればここの関数も 「aの部で、且つaの課で、且つ北海道か、青森か、岩手か、宮城か、秋田か、山形か、福島であればカウントする」としたいところ、(2)でつまずいてしまっているため、E2のセルの表示「2」はそのシートのB2-D2のSUM関数で合計を出しているという状況です。 (3)は以上のような対処で問題はないかと思いますが、(2)の部分はどうしても表示されません。 前任者のデータを引き継ぐ形となり、去年とは少し違う形式になるに伴い出てきた問題です。できれば去年のものをできるだけ引き継ぎたいと考えているのですが…何か良い方法があれば教えて頂ければ幸いです。

  • エクセル関数SUMPRODUCTについて

    エクセル2000です。 A列とB列の和にC列を乗じたものの合計は、 =SUMPRODUCT(A1:A10,$C$1:$C$10)+SUMPRODUCT(B1:B10,$C$1:$C$10) または =SUM(SUMPRODUCT(A1:A10,$C$1:$C$10),SUMPRODUCT(B1:B10,$C$1:$C$10)) のような長ったらしいものになるのでしょうか？ =SUMPRODUCT((A1:B10)*C1:C10) は、表中に文字列が入る場合があるのでエラーになります。 ご教示いただければ幸いです。

  • 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円

  • エクセル SUMPRODUCT関数でOR検索

    添付画像のような表があります。 '=SUMPRODUCT((B:B="男")*(C:C="新潟")*(D:D="B")*ROW(A:A)) で、B列が男、C列が新潟、B列がB　のデータがある行番号を返してくれます。 B列がBではなく、B列がAまたはABのデータがある番号を検索する場合はどうかなと思い、 '=SUMPRODUCT((B:B="男")*(C:C="新潟")*OR((D:D="A"),(D:D="AB"))*ROW(A:A)) としてみたら、該当データがないにもかかわらずB列がBの行番号が帰りました。SUMPRODUCTで検索する場合、ORを使う場合はどのように書けばよいのでしょうか？ ’=SUM(SUMPRODUCT((B:B="男")*(C:C="新潟")*(D:D="A")*ROW(A:A)),SUMPRODUCT((B:B="男")*(C:C="新潟")*(D:D="AB")*ROW(A:A))) のようにそれぞれを求めて合算するしかないのでしょうか？

  • 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))の意味を教えていただきたく。

  • エクセルのSUMPRODUCT関数についての質問

    SUMPRODUCT関数を使って、１列おきにカウントさせる式をつくっていて、 セルの指定方法について疑問をもちましたので質問させていただきます。 A1からJ1に数字がはいっているとして、偶数列だけカウントします。 =SUMPRODUCT((MOD(COLUMN(A:J),2)=0)*(A1:J1<>0)) =SUMPRODUCT((MOD(COLUMN(A:J),2)=0)*((A1:J1)<>0)*1) 上の式で答えはでるのですが、＊以降の指定の違いがわかりません。 どなたかお分かりになる方おられましたら、よろしくお願いします。

  • SUMPRODUCT関数について

    こんにちは！ ついさっきですが、ＡとＢとＣとＤを数える方法を質問したところ、 =SUMPRODUCT((A1:A10={"a","b","c","d"})*1) という回答を頂きまして、それは解決したのですが その範囲をB4:M4にしたとたん、エラー#N/Aになりました なぜでしょうか？

  • SUMPRODUCTとワイルドカード

    こんばんは。 ＜例＞ 　　　 A　 　　　B　 　C　　 　(D) 1　北海道　　500　 100　 =B1*C1 2　青森県　　400　 150　 =B2*C2 3　新潟県　　200 　200　 =B3*C3 4　東京都　　200 　100　 =B4*C4 5　沖縄県　　250 　100　 =B5*C5 　　　　　　　　　　 　　合計=SUM(D1:D5) 作業列(D列)を作らず合計を求めるには、 =SUMPRODUCT(B1:B5,C1:C5) でできると思います。 ただ、ここに「県のみ合計」という条件が入った場合について質問します。 例のように作業列（D列）を作り、SUMIFですれば簡単にできるのですが、「作業列を作らず、SUMPRODUCTとワイルドカード（*県）を使って」数式を組み立てるにはどうすればいいのでしょうか？ ステップ(D列)を踏まず、ひとつの数式だけで完結したいのです。 試行錯誤していますが、簡単そうなのになかなかうまくいきません…。 D1=COUNTIF(A1,"*県")で以下オートフィルコピーし、 =SUMPRODUCT(B1:B5,C1:C5,D1:D5) とすればできますが、結局作業列（D列）を作ってしまっていますし…。 あと…たとえば最初に示した数式なら、 =SUMPRODUCT((B1:B5)*(C1:C5)) という数式の組み方を回答でよく見かけますが、 =SUMPRODUCT(B1:B5,C1:C5) =SUMPRODUCT(B1:B5*C1:C5) としないのは、何か意味の違いがあるのでしょうか？ お詳しい方よろしくお願いします。 Vista Excel2007です。

  • 《エクセル2000》SUMPRODUCT関数、この式のどこがいけないのか…

    こんにちは。 Ａ列が１であり、Ｂ列とＣ列に共に数字が入っている（空白でない）ものをカウントしたいと思い、以下の関数を書きました。 =SUMPRODUCT((A1:A50=1)*(B1:B50<>"")*(C1:C50<>"")) ですがこの式ですと、「Ｂ列とＣ列が共に空白なもの」の個数が返って来てしまいます。 「<>」が怪しい気がするのですが、具体的に何がいけないのでしょうか？ また、欲しい数字を出すようにするにはどうすればいいのでしょうか。 よろしくお願いします…

注目のQ&A

カテゴリ

専門家に質問してみよう

職業から探して質問する

あなたにピッタリな商品が見つかる！ OKWAVEセレクト

質問する