- ベストアンサー
エクセル2010でSUMPRODUCT関数を使った条件分岐ができない
kagakusukiの回答
- kagakusuki
- ベストアンサー率51% (2610/5101)
回答No.1、3、4、6、8、10です。 >値がマイナスの数でなければ真と判定するなどのルールがエクセルの仕様としてあるのでしょうか？ はい、御座います。 ExcelのIF関数では論理式の所に入っている数値が0の場合は[偽の場合]の処理へと進み、0以外の場合には[真の場合]の処理へと進む様になっております。 負の数や小数値の場合も、0ではありませんから、[真の場合]の処理へと進みます。 因みに、Excelにおいては判定式の判定結果であるTRUEやFALSEも、数値データとして扱われます。 例えば、A1セルに =B1>C1 と入力しておいてから、B1セルに2を、C1セルに1を入力しますと、 B1>C1 の式は正しい事になりますから、A1セルには「TRUE」と表示されます。 この時、D1セルに =A1+3 と入力しますと、ExcelではTRUEは数値の1として扱われますから、D1セルには数値の4が表示されます。 又、E1セルに =(B1>C1)+4 と入力しますと、 =1+4 と同じ事になりますから、E1セルには数値の5が表示されます。 一方、B1セルに2を、C1セルに3を入力しますと、 B1>C1 の式は誤っている事になりますから、A1セルには「FALSE」と表示されますが、ExcelではFALSEは数値の0として扱われますので、D1セルには数値の3が、E1セルには数値の4が、それぞれ表示される事になります。 SUMPRODUCT関数を使って複数条件のカウントを行う場合も、上記と同じ考え方が使われています。 例えば、回答No.6に添付されている画像の様なデータがあったとします。 その際、 =SUMPRODUCT((I$6:I$16=I7)*(J$6:J$16<=K7)) という関数を作ったとしますと、セルに入力されている「Aさん」という値は、I7セルに入力されている「Aさん」という値と等しいので、 I$6=I7 の結果はTRUEになります。 又、J6セルには2014/1/1という日付が入力されていて、この値は、K7セルに入力されている2014/1/11という日付データの値よりも小さい値ですから、 J$6<=K7 の結果はTRUEになります。 そのため、 (I$6=I7)*(J$6<=K7) という計算を行いますと、 (I$6=I7)*(J$6<=K7) ↓ TRUE*TRUE ↓ 1*1 ↓ 1 という結果になります。 同様に、I7セルに入力されている「Aさん」という値は、I7セルに入力されている「Aさん」という値と等しいので、 I$7=I7 の結果はTRUEになります。 又、J7セルには2014/1/7という日付が入力されていて、この値は、K7セルに入力されている2014/1/11という日付データの値よりも小さな値ですから、 J$7<=K7 の結果はTRUEになります。 そのため、 (I$7=I7)*(J$7<=K7) という計算を行いますと、 (I$7=I7)*(J$7<=K7) ↓ TRUE*TRUE ↓ 1*1 ↓ 1 という結果になります。 同様に、I16セルに入力されている「Cさん」という値は、I7セルに入力されている「Aさん」という値とは異なっていますので、 I$16=I7 の結果はFALSEになります。 又、J16セルには2014/1/27という日付が入力されていて、この値は、K7セルに入力されている2014/1/11という日付データの値よりも大きな値ですから、 J$16<=K7 の結果はFALSEになります。 そのため、 (I$16=I7)*(J$16<=K7) という計算を行いますと、 (I$16=I7)*(J$16<=K7) ↓ FALSE *FALSE ↓ 0*0 ↓ 0 という結果になります。 同じ事をSUMPRODUCT関数内で指定されているセル範囲の全ての行に対しても行いますと、 6行目:(I$6=I7)*(J$6<=K7)=TRUE*TRUE=1*1=1 7行目:(I$7=I7)*(J$7<=K7)= TRUE*TRUE=1*1=1 8行目:(I$8=I7)*(J$8<=K7)= TRUE*TRUE=1*1=1 9行目:(I$9=I7)*(J$9<=K7)= TRUE* FALSE=1*0=0 10行目:(I$10=I7)*(J$10<=K7)=TRUE*FALSE=1*0=0 11行目:(I$11=I7)*(J$11<=K7)=TRUE*FALSE=1*0=0 12行目:(I$12=I7)*(J$12<=K7)=TRUE*FALSE=1*0=0 13行目:(I$13=I7)*(J$13<=K7)=TRUE*FALSE=1*0=0 14行目:(I$14=I7)*(J$14<=K7)=TRUE*FALSE=1*0=0 15行目:(I$15=I7)*(J$15<=K7)=FALSE*FALSE=0*0=0 16行目:(I$16=I7)*(J$16<=K7)=FALSE*FALSE=0*0=0 という具合に、I列の値がI7セルの値と等しく、J列の値がK7セルの値以上になっている行の所だけが1となります。 そして、I列の値がI7セルの値と異なっているか、J列の値がK7セルの値未満になっている行の所では0となります。 SUMPRODUCT関数では、上記の様な計算を繰り返し行い、それらの結果の合計を返す関数ですので、結果として複数条件のカウントを行う事が出来る訳です。
関連する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関数かなと思ったのですが、どのように作成すればいいのかわかりません。 教えて下さい。よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
お礼
詳しい説明をしていただき、ありがとうございます。 また一つ勉強になりました。