SUMPRODUCT関数の使い方と注意点

前へ 次へ
このQ&Aのポイント
  • SUMPRODUCT関数を使用して一覧表を作成する方法について説明します。
  • SUMPRODUCT関数が正しく機能しない場合の対処方法について解説します。
  • ExcelのバージョンによってSUMPRODUCT関数の使い方が異なる場合があるので注意が必要です。
回答を見る
  • ベストアンサー

SUMPRODUCT関数について

よろしくお願いいたします。EXCEL2013使用です。 上の画像、出張者リスト（名前、出発日、帰国日）から、別のシートに一目でわかる一覧表を作りたいのです。例えば、11/06-11/15まで出張だったとしたら、下の表で6日から15日までを黄色背景にする、等です。 下の表で、日付セルの下の各セルに次のような関数を入力しましたがうまく動作しません。 =SUMPRODUCT((リスト!$B$2:$B$300=$A4)*(INT(リスト!$C$2:$C$300)>=INT(B$2))*(INT(リスト!$D$2:$D$300)<=INT(B$2))) ※下の画像は12月ですが、その上に11月があり、上記関数内のセルのアドレスが間違っている訳ではありません。 SUMPRODUCT関数は大変便利なのでこれまで多用してきましたが、今回は完全に頓挫してしまいました。 どこがいけないのでしょうか。おわかりの方、是非ご教示下さいませ。 どうぞよろしくお願いいたします。

画像を拡大する

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

  • ベストアンサー
  • f272
  • ベストアンサー率46% (7996/17095)
回答No.2

不等号の無期が逆で =SUMPRODUCT((リスト!$B$2:$B$300=$A4)*(INT(リスト!$C$2:$C$300)<=INT(B$2))*(INT(リスト!$D$2:$D$300)>=INT(B$2))) じゃないの？

aloosh
質問者

お礼

コメントありがとうございました。 (^^;　ご指摘ありがとうございます。 自分でもこんなに思い込みが激しいとは思いませんでした。 全く気づきませんでしたです。 本当にありがとうございました。

その他の回答 (1)

  • 9don
  • ベストアンサー率21% (14/65)
回答No.1

INT(B$2)がよくわかりませんが 12月の日付12345・・・を 日付セル指定で12/1、12/2・・に変更 B$2 を　B$1へ ※表の行番号が不確かなので INT(B$2)の部分の符号を逆にすれば ご期待の”1”が取得できます。

aloosh
質問者

お礼

コメントありがとうございました。 論理式の符号が逆でした。 迂闊にもそこに思い至りませんでしたです。 本当にありがとうございました。

  1. カテゴリ
  2. パソコン・スマートフォン
  3. ソフトウェア
  4. Excel（エクセル）

関連するQ&A

  • 旅費を二重払いしないための関数SUMPRODUCT

    エクセル2010を使っている者です。 たとえば以下のようにエクセルの表において、旅費精算をする１つの旅行（出張）について 一行で書いたシートがあったとします。 NO(A列）　　名前（B列）　　　　出発日（C列）　　　帰着日（D列） 1 田中　一郎　　　　11/3　　　　　　　　11/5 2 田中　一郎　　　　11/2　　　　　　　　11/4 3 田中　三郎　　　　11/5　　　　　　　　11/6 4 田中　四郎　　　　11/2　　　　　　　　11/2 5 田中　五郎　　　　11/2　　　　　　　　11/5 6 田中　六郎　　　　11/9　　　　　　　　11/12 8 田中　一郎　　　　11/6　　　　　　　　11/7 9 田中　一郎　　　　11/3　　　　　　　　11/3 10 田中　一郎　　　　11/10　　　　　　　11/11 　11 　田中 　一郎　　　 11/14 11/14 12 田中　一郎　　　　11/15　　　　　　　11/15 田中一郎さんについて見ていただきたいのですが、この場合、機械的に作業すると 二重に旅費を支払ってしまいそうな場合に関係する行は、１０行目以外のすべてです。 氏名が同じで、出発日、到着日が重なっている部分がある場合はもちろんのこと、 連続して出張している場合（１１月５日、１１月６日）もその危険性があります。 （たとえば、１１月３日から５日が東京→仙台、１１月６日・７日が青森出張だった 場合、５日は東京に戻らずに仙台から青森まで行った可能性があるからです） こうした場合に、指定したセル（列）に「確認」という文字が入るように以下の関数を 組んで下にドラッグしているのですが、完全にはうまく動いてくれません。 １２行目の１１月１５日の出張についても「確認」と入ってほしいのですが、１１行目の１４日の 行については「確認」と入るのに、１２行目には入りません。 =IF(SUMPRODUCT((B$1:B$1000=B1)*(C$1:C$1000<=D1+1)*(D$6:D$1000>=C1))>1,"確認","")) うまくいかなかったので、以下のようにしたのですが、もっとダメでした。 上の関数はずっと前にここの掲示板で教えていただいたものを少しだけ変えたものなのですが、 そもそもなぜ、sumproduct関数の値を「>=1」ではなく「>1」としているのでしょうか？ =,IF(OR(SUMPRODUCT((B$6:B$1000=B1)*(C$1:C$1000<=D1+1)*(D$1:D$1000>=C1))>=1,SUMPRODUCT((B$1:B$1000=B1)*(D$1:D$1000=C1-1))>=1),"確認","")) よろしくお願いいたします。

  • COUNTIFやSUMPRODUCTの関数について

    画像にあるようなＥｘｃｅｌの表について 下記の関数を使って出来たことを、もっとＢ２２～Ｃ２４（セル）を入力せず出来る関数はないのか 教えて頂きたい。また、以下のところに今使っている関数と、求めたいことについて記載しました。 　SUMPRODUCT(($A$2:$A$15=$B$22)*(C2:C15=$C$22))の関数を使って 　　１．Ｃ１７セルに表示された個数は、Ａ列の２～１５の中のＡランクの人の「１」の入力された 　　　　ところだけのを表すことになっている。 　以下もＣ列のＢ・Ｃことである。 　　２．このことを踏まえて、ＢランクはＣ１８に「０」が入る。（１の入力されたセルがないため） 　　３．Ｃランクは、「長島」に「１」が入力されているので、C１９には「１」が入いる。 　　４．イ～オ列のＤ１７～Ｇ１９まで同じように表示させてある。 　なお、あくまでもア～オの全体の個数の中のＡ・Ｂ・Ｃのランクの個数をカウントしたいのです。 　　

  • 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関数

    データベース抽出とSUMPRODUCT関数 エクセル2007で以下のような表を作って 関数の勉強をしています。 下の画像の上段のシート（シート1）の表が検索フォームです。 下段のシート（シート2）が購入商品リストです。 シート1のA3セルに請求書NOを入力させることで、 B列からE列までにそれぞれのデータを表示させたいのですが シート2に表示されているように、同じ購入者に複数の 請求書NOが付番されているために、購入者ごとに金額を上手く合計できませんでした。 sumproduct関数も使ってみたのですが、エラーが出てしまいました。 例えばAさんは、請求書が1と2がありますが、請求書は 1枚にしたいので、購入金額を合算して、検索表示したいのです。 加えて、請求書NOと購入商品も下の画像のように 一括にして表示することはできるのでしょうか。 初心者で申し訳ないですが、お願いします。

  • SUMPRODUCT関数を用いた最小値

    お世話になっております。 SUMPRODUCT関数の最小値の求め方で壁を越えられないため 皆様のお知恵をお貸しください。 下のような表があります。 　　A　B　C（セル列） 1　青　○　100 2　赤　○　95 3　青　×　75 4　青　○　200 5　赤　×　65 6　赤　×　80 7　青　○　105 8　青　×　85 9　赤　○　110 10　赤　×　70 青かつ○の最大値（200）は =SUMPRODUCT(MAX((A1:A10="青")*(B1:B10="○")*(C1:C10))) にて求められるのですが 青かつ○の最小値（100）は =SUMPRODUCT(MIN((A1:A10="青")*(B1:B10="○")*(C1:C10))) とすると、0が返ってしまいます。 googleで検索しては検証しを繰り返しましたが どうにもうまくいきません。 Shift+Ctrl+Enterによる {=MIN(IF((A1:A10="青")*(B1:B10="○"),C1:C10))} では求められますが、出来れば配列でない方法を探しています。 宜しくお願い致します。

  • エクセル　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関数について

    SUMPRODUCT関数を使って「タテ100行ヨコ10列のデータの入った表」からA1*B1+A2*B2+A3*B3+……+A99*B99+A100*B100の計算と A1*C1+A2*C2+A3*C3+……A99*C99+A100*C100というように10列共計算しました。 さらに各々の掛け算の結果を小数点以下を切り捨てたいのでTRUNC関数を使おうと思いましたが上手くできません。 他の関数でも良いのですが良い知恵をお貸しください。

  • ＳＵＭPRODUCTでエラーになってしまいます。

    関数を少しづつ勉強しながら集計表作成の業務を同時進行しています。 初心者です。 今作ってるのは複数条件の合計金額を求めたくて、 SUMPRODUCT(('4月'!D4:D38="*"&$C135&"*")*('4月'!$H4:H38=C58)*('4月'!E4:E38)) を作ったのですが、どうやらSUMPRODUCTだと「～を含む」の機能が使えないみたいなので 色々調べて SUMPRODUCT((ISNUMBER(FIND($C$135,'4月!$D$4:$D$38)))*('4月!H4:H38=C61)*('4月!E4:E38)) が完成しました！ そして、更に右にコピーするとシートの月も５月、6月・・と変わるようにしたかったので 前回ここで教えてもらった関数を参考に SUMPRODUCT((ISNUMBER(FIND($C$135,INDIRECT(MOD(COLUMN(C1),12)+1&"月!$D$4:$D$38))))*(INDIRECT(MOD(COLUMN(C1),12)+1&"月!H4:H38=C61))*(INDIRECT(MOD(COLUMN(C1),12)+1&"月!E4:E38))) を作りました。 でも・・・エラーになってしまうのです。 セルにエラー表示がされるのではなく正しく直るまで「修正しなさい」って出るバージョンの エラーです。。。。 もう何がいけないのかさっぱりで・・・・・ あと一番上の原型の関数からなのですが、セル番地を列で指定したいのですが、 D:DとかH:Hにするとセルのエラーになってしまいます。 こちらも何でエラーになるのかわからなくて困っています。 どなたか詳しい方教えて下さい。

  • SUMPRODUCT関数について

    http://oshiete1.goo.ne.jp/qa5621207.html で質問していたものですが お答え頂いた関数について質問させて下さい。 =SUMPRODUCT(($A$2:$A$100+($B$2:$B$100>"21:00"*1)=E2)*($C$2:$C$100=F2)) についてなのですが、SUMPRODUCT関数の中で使われる＋には どういった意味があるのでしょうか？ 前半部分（=E2まで）の意味が分からず、困っております。 どなたか解説をお願いします。

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

注目のQ&A

カテゴリ

専門家に質問してみよう

職業から探して質問する

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

質問する