- ベストアンサー
エクセル2010でSUMPRODUCT関数を使った条件分岐ができない
kagakusukiの回答
回答No.1、3、4、6です。 もし、J列やK列に入力されるデータが日付だけのデータのみであり、時刻を含んだ日時データが入力される事は無い場合において、「重複」や「連日」、「重複、連日」といった表示を行う際に、SUMPRODUCT関数を利用するのでしたら、次の様な関数となります。 =IF(OR($I6="",ISERROR(1/DAY($J6)/DAY($K6))),"",REPLACE(IF(SUMPRODUCT(($I$5:INDEX($I:$I,MATCH(9E+307,$J:$J))=$I6)*($J$5:INDEX($J:$J,MATCH(9E+307,$J:$J))<=$K6)*($K$5:INDEX($K:$K,MATCH(9E+307,$J:$J))>=$J6))-1,"、重複","")&IF(SUMPRODUCT(($I$5:INDEX($I:$I,MATCH(9E+307,$J:$J))=$I6)*(($K$5:INDEX($K:$K,MATCH(9E+307,$J:$J))=$J6-1)+($J$5:INDEX($J:$J,MATCH(9E+307,$J:$J))=$K6+1))),"、連日",""),1,1,))
関連するQ&A
- SUMPRODUCT関数の使い方
いつもお世話になっております。SUMPRODUCT関数の使い方が解らず困っています。 どなたか、教えてください。 I列（3～1034）に101と入力されており、F列（3～1034）に○と入力されているセルの数を数えたくて、 SUMPRODUCT関数を使いましたが、答えが0になるか、NAME？になるかで、正答が表示されません。 関数が苦手なので、SUMPRODUCTは、関数の表示ダイアログボックスを使って作成しています。 すると、配列1、配列2、配列3と出てくるので、 配列1に I3：I1034＝101 配列2に F3：F1034＝○ （絶対参照して）入力すると =SUMPRODUCT($I$3:$I$1034=101,$F$3:$F$1034=○) となり、答えは ＃NAME？ と出てしまいます。 また、やり方が解らないのですが、他の質問の回答をみて、SUMPRODUCT関数を手入力し、 =SUMPRODUCT（($I$3:$I$1034=101）*（$F$3:$F$1034=○)） と修正入力すると、答えは 0 になってしまいます。 （だいたい、どうして * が出てくるのかも理屈が解りません。） 101や○を””でくくっても、うまくいきません。 もはや何がどう良くないのかわかりません（泣） どなたか、素人の私にも解るようにご解説お願いいたします。
- ベストアンサー
- オフィス系ソフト
- 旅費を二重払いしないための関数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),"確認","")) よろしくお願いいたします。
- ベストアンサー
- Excel（エクセル）
- sumproductの複数条件
=SUMPRODUCT((貼り付け用!$J$2:$J$15000="3")*(貼り付け用!$K$2:$K$15000={"3","4","5","6","7","8","9"})*(貼り付け用!$I$2:$I$15000={"通過","NG"}))とやると#N/Aと表示されますが、何が間違っているのでしょうか？
- 締切済み
- オフィス系ソフト
- エクセル IF関数 条件を満たす行に印をつける
A1：J30までにランダムな数値が入力されている表があります。M1:N4には検索値を入力できる箇所があり、条件に該当する行があればK列に○を付ける、ということがしたいです。その条件は、 (1)M1かN1に該当する (2)M2かN2に該当する (3)M3かN3かM4かN4に該当する この３条件のすべてを満たす行があればK列に○です。 いろいろ調べて、IF関数とsumproductを組み合わせてやってみたのですが、正しい結果にならずお手上げ状態です。（ちなみにこれ↓長すぎなのもネック。。。） =IF(AND(SUMPRODUCT((A1:J1=$M$1)+(A1:J1=$N$1))>=0,SUMPRODUCT((A1:J1=$M$2)+(A1:J1=$N$2))>=0,SUMPRODUCT((A1:J1=$M$3)+(A1:J1=$N$3)+(A1:J1=$M$4)+(A1:J1=$N$4)>=0)),"○","") IF関数でなくてもできれば何でも良いので、教えてください。 よろしくお願いいたします。
- ベストアンサー
- オフィス系ソフト
- 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関数は大変便利なのでこれまで多用してきましたが、今回は完全に頓挫してしまいました。 どこがいけないのでしょうか。おわかりの方、是非ご教示下さいませ。 どうぞよろしくお願いいたします。
- ベストアンサー
- Excel（エクセル）
- エクセルの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) 上の式で答えはでるのですが、＊以降の指定の違いがわかりません。 どなたかお分かりになる方おられましたら、よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- ２つ以上の条件でエクセルで重複したデータを抽出したい
下記のようなエクセルのデーターがあったとします。 「氏名が重複していてかつ個数も重複するもの」 を抽出したいのですがどうすればよいでしょうか？ いろいろ検索したところIF(SUMPRODUCT(($A$３:A３=A３)*($B$３:B３=B３))>1,"同じ人","") という風にすればよいようなのですが、この式自体がよくわかりません。ＩＦ関数の中にＳＵＭＰＲＯＤＵＣＴを ネストするところまではわかるのですがその後の式「:A３=A３)*($B$３:B３=B３))」までがわかりません。 何を選んでこういった式になるのでしょうか？？初歩的な質問で申し訳ないのですが、教えてください。 よろしくお願いします。 Ａ列 Ｂ列 氏名 個数 加藤 １ 新妻 ２ 加藤 １ 鈴木 ３ 加藤 ６ 小島 ８
- ベストアンサー
- オフィス系ソフト
- 関数の訂正
I8からI250まで数量データが入っています。 J8からJ250まで単価データが入っています。 その合計を計算するのに下記の関数を入れています。 =SUMPRODUCT(I8:I250,J8:J250) 数量のところが数量ではなく、25kみたいな重量を入れたい時があります。 そのときも、25と認識して計算してもらいたいのです。 数量 単価 10 50 20k 100 計算結果は、2500となるような関数をご教授くださいませ。 よろしくお願いします。 =SUMPRODUCT(SUBSTITUTE(I8:I250,"k","")*1,J8:J250) でやってみたのですが、valueエラーがでます。 8行目から250行目までデータがないとダメなんでしょうか？
- ベストアンサー
- オフィス系ソフト
- エクセル 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列 名 目標 実績 いちご 100 123 ぶどう 120 115 みかん 110 130 ばなな 110 120 人数 上記の表があり、実績（C列）が目標（B列）を上回った人数を関数を使ってカウントして、人数欄に表示したいです。 どのように求めたらいいのでしょうか？ SUMPRODUCT関数かCOUNTIF関数かなと思ったのですが、どのように作成すればいいのかわかりません。 教えて下さい。よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
お礼
何度もご親切にありがとうございました。 実は、質問文の式をあれこれ試行錯誤していじっていたら、先ほどから意図するように機能するようになりました。 重複はもともとできていたので、問題は連日の場合だったのですが、＞＝１とするところを＞1で入れていたり、少し書き間違えがありました。 何にしろ、複雑な操作を今回はありがとうございました。 回答者様の式も入れみたのですが、うまくできました。 式の中で出てくる「ISERROR(1/DAY($J6)/DAY($K6))・・」というところと、「MATCH(9E+307,$J:$J))」という表記がわからなかったのですが、伺っても良いでしょうか。 今回はすでに解決していますが、見たことのない表記だったものでして。。