SUMPRODUCT関数を使った条件分岐ができない

Question

エクセル2010を使っておりますが、IFとSUMPRODUCTを使った関数のことで伺います。

旅費の精算をしておりまして、I列に氏名、J列に出発日、K列に到着日が入っております。
（１行と１つの出張について入力してあります）

同じ氏名で、他の出張と出発日又は到着日が一部、又は全部重なる場合は「重複」、帰ってきた翌日から出張の場合は「連日」、両方の条件を満たすときは「重複、連日」と表示されるようにしたく、以下の関数式を組みました。

しかし、「重複、連日」と「連日」がうまく表示されません。
連日に関しては、I列に氏名が複数入力されていれば表示されてしまいます。

どこをどう直せば、意図するように表示されるのか、教えていただけないでしょうか。
長文の関数で大変申し訳ないのですが、よろしくお願いいたします。

=IF(AND(SUMPRODUCT((I$6:INDEX(I:I,1000)=I6)*(J$6:INDEX(J:J,1000)<=K6)*(K$6:INDEX(K:K,1000)>=J6))>1,OR(SUMPRODUCT((I$6:INDEX(I:I,1000)=I6)*(K$6:INDEX(K:K,1000)=J6-1))>=1,SUMPRODUCT((I$6:INDEX(I:I,1200)=I6),(J$6:INDEX(J:J,1200)>=K6+1))>=1)),"重複、連日",IF(SUMPRODUCT((I$6:INDEX(I:I,1000)=I6)*(J$6:INDEX(J:J,1000)<=K6)*(K$6:INDEX(K:K,1000)>=J6))>1,"重複",IF(SUMPRODUCT((I$6:INDEX(I:I,1000)=I6)*OR((K$6:INDEX(K:K,1000)=J6-1),(J$6:INDEX(J:J,1000)>=K6+1)))>1,"連日","")))

kagakusuki · Accepted Answer

回答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,))

kagakusuki · Answer

　回答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関数では、上記の様な計算を繰り返し行い、それらの結果の合計を返す関数ですので、結果として複数条件のカウントを行う事が出来る訳です。

kagakusuki · Answer

回答No.1、3、4、6、8です。

>式の中で出てくる「ISERROR(1/DAY($J6)/DAY($K6))・・」というところと、「MATCH(9E+307,$J:$J))」という表記がわからなかったのですが、伺っても良いでしょうか。

「ISERROR(1/DAY($J6)/DAY($K6))」の所では、J列の「出発日」欄とK列の「到着日」欄の両方に、日付データが入力されているかどうかを判定しており、どちらか一方でも日付が入力されていない場合には、その部分はTRUEを返しますので、IF関数は[真の場合]の処理へと進む事になり、一方、両方に日付が入力されている場合にのみ、その部分はFALSEを返しますので、IF関数は[偽の場合]の処理へと進む事になります。
　例えば、J列の「出発日」欄やK列の「到着日」欄の所に、万が一、負の数や単なる時刻データ、或いは文字列データ等が入力されていた場合には、それらのデータをそのまま日付データとして扱おうとして「重複」や「連日」を表示するための関数で処理しますと、エラーとなったり、誤った結果が表示されたりしてしまう恐れがあります。
　そこで、ISERROR関数を使用して、日付がJ列の「出発日」欄とK列の「到着日」欄の両方に、日付データが入力されているかどうかを判定させています。(正確には「J列とK列の両方に日付が入力されている訳ではない」か否かを判定させています)
　ISERROR関数は、その中に入っている関数がエラーとなる場合にTRUEを返す関数です。
　もし、J列の「出発日」欄やK列の「到着日」欄に文字列データや負の数が入力された場合には、それらのデータを日付に変換する事は出来ませんから、DAY($J6)やDAY($K6)の所でエラーが発生しますので、ISERROR関数はTRUEを返す事になります。
　又、J列の「出発日」欄やK列の「到着日」欄に数値の0や、(例えば0.5などの様な)1よりも小さく0よりは大きい数値が入力された場合には、DAY関数は0日を返しますから、

1/DAY($J6)/DAY($K6)

の所で1を0で割る事になりますのでエラーとなり、ISERROR関数はTRUEを返す事になります。
　又、J列の「出発日」欄やK列の「到着日」欄が空欄となっている場合には、Excelでは空欄を数値の0と同じ値として扱いますから、0が入力された場合と同じ結果となります。
　私が提示した関数の場合では、IF関数の[真の場合]の所では空欄が表示される様になっていますから、J列の「出発日」欄やK列の「到着日」欄の所に、万が一、負の数や単なる時刻データ、或いは文字列データが入力されていた場合であっても、誤った結果が表示されたり、エラーとなったりする事を回避出来ますし、J列の「出発日」欄やK列の「到着日」欄の所が空欄となっている場合には、同じく空欄が返る事になります。

次に、「MATCH(9E+307,$J:$J))」に関してですが、MATCH関数は

MATCH(検査値, 検査範囲, [照合の型])

という形式で記述される関数であり、[照合の型]の所に1を指定するか、或いは[照合の型]の指定を省略しますと、検査範囲のデータが昇順に並べられている場合において、検査値以下の最大の値が入力されているセルの位置が返されます。
　では、もし、検査範囲のデータが昇順に並んではいなかった場合にはどうなると思いますか？
　その場合は、検査範囲を下から順に見て行った際に、検査値以下のデータが初めて現れた処の位置が返される事になります。(そのため、検査範囲のデータが昇順に並んでいる場合には、結果として「検査値以下の最大の値」が入力されている位置を返す事になる訳です)
　つまり、検査値の所に「J列に入力されている最大の数よりも更に大きな数値」を指定しておきますと、J列を下から順に見て行った場合において、検査値よりも小さな数値が初めて現れる場所は、必ず、「J列において数値が入力されているセルの中で、最も下のセルの位置」という事になります。
　Excelではシリアル値と言って、日付や時刻のデータは、1899年12月31日の午前0時(Mac版ExcelのExcel2003以前のバージョンでは19004年1月1日の午前0時)から経過した日数を表す数値として扱われています。
　ですから、Excelにおいてセルに入力する事が出来る最大の数値である9.99999999999999E+307を検査値として指定しておきますと、「J列において日付が入力されている最も下のセルの位置」を求める事が出来る訳です。
　尤も、現実的には9E+307よりも大きな数を扱う様な場面などはまずありませんから、「.99999999999999」等を入れる事で数式が長くなる事や、入力する「9」の数を間違えて多過く入力してしまい、「9.999999999999999E+307」という「Excelでは数値として扱う事が出来ない数」になってしまう事を避けるために、9.99999999999999E+307ではなく、9E+307としております。
　そして何故、最も下の行など求めているのかと申しますと、回答No.1で述べております様に、SUMPRODUCT関数は繰り返し計算を行う関数で、その繰り返しの回数が多くなると処理が重くなります。
　未だ、1000行の所にまでデータが入力されている訳でもないというのに、1000行の所まで計算を行ったのでは、空欄となっている行の所までも計算を行っている事になり、無駄に長い時間をかけて必要のない計算まで行っている事になります。
　そのため、MATCH関数を利用してデータが入力されている最下段の行を求めて、繰り返し計算の回数が無駄に多くならない様にしている訳です。

bunjii · Answer

>今日も試行錯誤したら、なんとかなりました。それは良かったですね。努力をすればなんとか解決するものです。根気が大切です。尚、提示の関数式を訂正すると添付画像のようになりました。論理を分かり易くするためにSUMPRODUCT関数の各配列を分離して動作の状況を確認できるようにしました。 =IF(SUMPRODUCT((I$6:INDEX(I:I,1000)=I6)*1,(J$6:INDEX(J:J,1000)<=K6)*1,(K$6:INDEX(K:K,1000)>=J6)*1,(K$6:INDEX(K:K,1000)>=J6-1)*1,(J$6:INDEX(J:J,1000)<=K6+1)*1)>1,"重複、連日",IF(SUMPRODUCT((I$6:INDEX(I:I,1000)=I6)*1,(J$6:INDEX(J:J,1000)<=K6)*1,(K$6:INDEX(K:K,1000)>=J6)*1)>1,"重複",IF(SUMPRODUCT((I$6:INDEX(I:I,1000)=I6)*1,(K$6:INDEX(K:K,1000)>=J6-1)*1,(J$6:INDEX(J:J,1000)<=K6+1)*1)>1,"連日","")))

bunjii · Answer

>エクセルを添付するわけにはいかないし、サンプルとは以下のような感じで良いでしょうか？
根本的に論理式を見直さないと正しい判定にならないようです。
IF関数の多重入れ子になっていますので外側から順次判定され分岐して終了する要素と次のIF関数へ渡される要素を誤解していませんか？
また、SUMPRODUCT関数を多用していますが配列の前処理に誤りがあるようです。
新たに組み直すには労力と時間が掛かりますので無料奉仕は難しいです。

尚、サンプルデータは出発日が時系列になっていないと処理が難しくなりますのでソートが必要でしょう。
目視判定ですが重複は「田中　一郎」の「12月1日～12月2日」と「12月2日～12月3日」、「田中　一郎」の「12月24日～12月24日」と「12月24日～12月25日」となれば正解でしょうか？
連続は「田中　一郎」の「12月3日～12月4日」と「12月4日～12月4日」更に「12月4日～12月5日」と判断します。

判定結果は重複または連続の出発日の行へ表示するだけで良いと思います。
つまり、重複の場合は「田中　一郎」の「12月2日～12月3日」の行と「12月24日～12月15日」の行です。

取り敢えず重複のみの判定を以下の数式で確認してみると良いでしょう。

=IF((INDEX(K$1:K$1000,SUMPRODUCT(MAX((I5:I$6=I6)*ROW(I5:I$6))))>=J6)*(I5:I5=I6),"重複","")

他の判定要素については準用してください。

kagakusuki · Answer

>NO1の回答のケースでは、出発日が１行目と重複しているため「重複」に該当しているのに加え、３行目と連続しているため「連日」となります。

　ようやく解りました。
　又、

>連続の場合（1/5・1/6の出張と1/7.1/9の出張の場合等）も両方の出張に「連続」と文字が入るようにしたいと思っております。

との事ですので、その場合には、次の様な関数となります。

=IF(OR($I6="",ISERROR(1/DAY($J6)/DAY($K6))),"",REPLACE(IF(COUNTIFS($I:$I,$I6,$J:$J,"<"&INT($K6)+1,$K:$K,">="&INT($J6))-1,"、重複","")&IF(COUNTIFS($I:$I,$I6,$K:$K,">="&INT($J6)-1,$K:$K,"<"&INT($J6))+COUNTIFS($I:$I,$I6,$J:$J,">="&INT($K6)+1,$J:$J,"<"&INT($K6)+2),"、連日",""),1,1,))

　これを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))<INT($K6)+1)*($K$5:INDEX($K:$K,MATCH(9E+307,$J:$J))>=INT($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))>=INT($J6)-1)*($K$5:INDEX($K:$K,MATCH(9E+307,$J:$J))<INT($J6))+($J$5:INDEX($J:$J,MATCH(9E+307,$J:$J))>=INT($K6)+1)*($J$5:INDEX($J:$J,MATCH(9E+307,$J:$J))<INT($K6)+2))),"、連日",""),1,1,))

　尚、上記の2つの関数は、出発日欄や到着日欄の所に日付けだけではなく、例えば「2014/2/12 14:47」等の様に時刻も含んだデータが入力されている場合にも対応する様になっております。
　そのため、日付けだけが入力されている場合に用いるには、少し余計な処理も行う様になっております。
　もし、時刻を含んだ日時データが入力される事は無い場合には、次の様に少し簡略化する事も出来ます。

=IF(OR($I6="",ISERROR(1/DAY($J6)/DAY($K6))),"",REPLACE(IF(COUNTIFS($I:$I,$I6,$J:$J,"<="&$K6,$K:$K,">="&$J6)-1,"、重複","")&IF(COUNTIFS($I:$I,$I6,$K:$K,$J6-1)+COUNTIFS($I:$I,$I6,$J:$J,$K6+1),"、連日",""),1,1,))

bunjii · Answer

>足りない情報等、ございましたら、ご指摘いただけると幸いです。
前回の回答で「提示の数式を解読して誤りを見つけるにはサンプルが必要です。」と申し上げています。
サンプルを当方で用意するほど手間を掛けたくありません。
各SUMPRODUCT関数の引数を再確認して動作が目的に合っていない部分を調べるのは実データを持っているあなた自身で行ってください。

関数を組み合わせて目的の処理を行うことは小さなプログラムを組むようなものです。
論理を正しく指定しなければ思ったように処理できなくなります。
もしかして、他人が組んだ関数式を丸呑みで利用しているのでは？

kagakusuki · Answer

回答No.1、3です。

回答No.1、3に対するお礼欄で御教え頂いた「連続」と表示するための条件は、互いに矛盾している様に思えます。
　回答No.1に対するお礼欄で

>たとえば、
>１行目　田中　一郎12/112/2
>２行目　田中　一郎12/212/3
>３行目　田中　一郎12/412/4
>４行目　田中　一郎12/512/6
>５行目　田中　一郎10/510/5
>６行目　田中　一郎12/2412/24
>７行目　田中　一郎12/2412/25

>という場合、２行目は、出発日が１行目と重複しているうえに、３行目と連続しています。

と書かれておられるという事は、明記されてはおられないものの、おそらくこの場合は「重複、連続」と表示するという事だと思います。
　それに対して、回答No.3に対するお礼欄では

>なお、重複していた場合（1/5・1/6の出張と1/6.1/7の出張の場合も一部重複しているので「重複」と入るようにしたいと思います（この場合は「連続」ではありません）。また、両方の出張に「重複」と入るようにしたいと思います。

と書かれておられます。
　どちらも、出発日が別の出張の到着日と重複している事に変わりがないにも関わらず、回答No.1に対するお礼欄では「連続」と表示しなければならないかのように受け取れる内容を書かれておられるのに対し、回答No.3に対するお礼欄では

>（この場合は「連続」ではありません）

と書いておられるのはどの様な訳なのでしょうか？
　質問者様がどの様な条件の場合にどの様な表示とされたいのかという事を、御説明頂いた内容に矛盾があったのでは、関数の改良案を提示する事なの出来る筈も御座いませんので、条件を明確にして頂く様御願い致します。

kagakusuki · Answer

　回答No.1です。

　前の回答で提示させて頂いた関数は、結果を求める際に少々無駄の多いやり方となっておりましたので、次の関数と差し替えて下さい。(表示される結果自体は回答No.1のものと変わりありません)

=IF(OR($I6="",ISERROR(1/DAY($J6)/DAY($K6))),"",REPLACE(IF(COUNTIFS($I:$I,$I6,$J:$J,"<"&INT($K6)+1,$K:$K,">="&INT($J6))-1,"、重複","")&IF(COUNTIFS($I:$I,$I6,$K:$K,">="&INT($J6)-1,$K:$K,"<"&INT($K6)),"、連日",""),1,1,))

bunjii · Answer

>どこをどう直せば、意図するように表示されるのか、教えていただけないでしょうか。
SUMPRODUCT関数の性質を知らずに使っているようです。
SUM（合計）PURODUCT（製品）と言う語呂から数値を合計する関数と考えてください。
引数の配列は複数を扱えますが数値でない配列値では 0 が返ります。
SUMPRODUCT関数の引数を個々に調べるとIF関数で振り分けられない理由を見つけられます。

提示の数式を解読して誤りを見つけるにはサンプルが必要です。

SUMPRODUCT関数を使った条件分岐ができない

回答No.1、3、4、6です。

回答No.1、3、4、6、8、10です。

回答No.1、3、4、6、8です。

>今日も試行錯誤したら、なんとかなりました。

>エクセルを添付するわけにはいかないし、サンプルとは以下のような感じで良いでしょうか？

>NO1の回答のケースでは、出発日が１行目と重複しているため「重複」に該当しているのに加え、３行目と連続しているため「連日」となります。

>足りない情報等、ございましたら、ご指摘いただけると幸いです。

回答No.1、3です。

回答No.1です。

>どこをどう直せば、意図するように表示されるのか、教えていただけないでしょうか。

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング

　回答No.1、3、4、6です。

　回答No.1、3、4、6、8、10です。

　回答No.1、3、4、6、8です。

　回答No.1、3です。

　回答No.1です。