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

質問

エクセル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,"連日","")))

回答No.8

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

お礼

何度もご親切にありがとうございました。

実は、質問文の式をあれこれ試行錯誤していじっていたら、先ほどから意図するように機能するようになりました。
重複はもともとできていたので、問題は連日の場合だったのですが、＞＝１とするところを＞1で入れていたり、少し書き間違えがありました。

何にしろ、複雑な操作を今回はありがとうございました。

回答者様の式も入れみたのですが、うまくできました。

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

今回はすでに解決していますが、見たことのない表記だったものでして。。

回答No.11

kagakusuki

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

お礼

詳しい説明をしていただき、ありがとうございます。
また一つ勉強になりました。

回答No.10

kagakusuki

回答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関数を利用してデータが入力されている最下段の行を求めて、繰り返し計算の回数が無駄に多くならない様にしている訳です。

お礼

遅くなりましたが、大変詳しい解説をしていただき、ありがとうございます。
MATCH関数にはそういった使い方があるのですね。
何度か拝読し、ようやく理解できた感じです。

NO８で書いていただいた関数についてですが、考え方としては、重複の条件に合致していれば「、重複」の文字を返し、連日の条件に合致していれば「、連日」の文字をそれに合体させ、最初の一文字を削るということですよね。

そこでまたわからない点があったのですが、よろしいでしょうか^^;
「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)))」
と記載されており、IF関数で「>=1」などで締めくくっていないようですが、値が真か偽かの判定がどのようにされているのでしょうか。値がマイナスの数でなければ真と判定するなどのルールがエクセルの仕様としてあるのでしょうか？

回答No.9

bunjii

>今日も試行錯誤したら、なんとかなりました。
それは良かったですね。
努力をすればなんとか解決するものです。
根気が大切です。
尚、提示の関数式を訂正すると添付画像のようになりました。
論理を分かり易くするために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,"連日","")))

お礼

ありがとうございます。
関数式はそんな感じです。

回答No.7

bunjii

>エクセルを添付するわけにはいかないし、サンプルとは以下のような感じで良いでしょうか？
根本的に論理式を見直さないと正しい判定にならないようです。
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),"重複","")

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

お礼

ありがとうございます。

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

回答No.6

kagakusuki

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

お礼

何度もほんとうにありがとうございます。

回答No.5

bunjii

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

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

お礼

エクセルを添付するわけにはいかないし、サンプルとは以下のような感じで良いでしょうか？
質問文の関数式を入れると、以下のように表示されてしまいます。
「連日」と「重複、連日」の表示がおかしいです。

田中　一郎　12/1　12/2　重複
田中　一郎　12/2　12/3　重複
田中　一郎　12/4　12/4　連日
田中　四朗　12/11 　12/13
田中　一郎　12/5　12/6　連日
田中　一郎　10/5　10/5　連日
田中　一郎　12/24　12/24　重複
田中　一郎　11/15 　11/15　連日
鈴木　五郎　12/25 　12/26
田中　一郎　12/24 　12/25　重複

回答No.4

kagakusuki

回答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に対するお礼欄では

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

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

お礼

ご親切にありがとうございます。考えてくださってありがとうございます。

＞と書かれておられるという事は、明記されてはおられないものの、おそらくこの場合は「重複、連続」と表示するという事だと思います。

おっしゃるとおりです。

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

NO1の回答のケースでは、出発日が１行目と重複しているため「重複」に該当しているのに加え、３行目と連続しているため「連日」となります。
対して、1/5・1/6の出張と1/6.1/7の出張の場合は、「重複」のみにしたいと思います。

定義としては、以下のような感じです。

重複：出張期間の一部、又は全部が他の出張の期間と重複している。

連日：到着日の翌日に他の出張の出発日がある場合、及び出発日の前日が他の出張の到着日だった場合。

重複、及び連日；「重複」「連日」の両方を満たす場合（1/5・1/6の出張と1/6.1/7の出張の場合は
　上記の連日の要件を満たしておりません）
　　別の１件の出張との関係のみで、「重複、連日」となることはなく、最低でも２件の出張と関係する　ことが必要となります。

回答No.3

kagakusuki

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

お礼

せっかく組んでいただいたのに大変、申し訳ありません。

式の考え方からして理解できないため、まずはSUMPRODUCTの修正を考えていただけると、大変助かります。

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

回答No.2

bunjii

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

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

お礼

ありがとうございます。

「I列に氏名、J列に出発日、K列に到着日が入っております。
また、５行目まではタイトル等が入っております。

入力して、「連日」と「重複、連日」が意図するとおりに表示されなかった関数は質問文に書いた以下の式です。

=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,"連日","")))

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

よろしくお願いいたします。

回答No.1

kagakusuki

御質問の条件には矛盾があります。

>帰ってきた翌日から出張の場合は「連日」、両方の条件を満たすときは「重複、連日」

との事ですが、「帰ってきた翌日から」新たな出張が始まる場合が「連日」になるという事は、「帰って来た日」に新たな出張が始まる場合や、「帰って来る前」に新たな出張が始まる場合は、「連日」とはならないという事になります。
　そして、前の回の出張は「帰ってきた日」で終了しているのですから、「帰ってきた翌日」は「前の回の出張期間」とは重なっていない事になりますので、「連日」になる場合には、絶対に「重複」とはなり得ない事になります。
　従いまして、

>両方の条件を満たすときは「重複、連日」と表示される

等という事はあり得ない事になります。
　もしかしますと、「連日」と表示する条件は、「帰ってきた翌日から出張の場合」等ではなく、「帰ってきた日の翌日も別の出張がある場合」或いは「出発日の前日にも別の出張に行っていた場合で、尚且つ、前の出張が終了した後も新しい方の出張が継続する場合」の間違いではないでしょうか？
　ですから、取り敢えずの話として、「出発日の前日にも別の出張に行っていた場合で、尚且つ、前の出張が終了した後も新しい方の出張が継続する場合」において「連日」と表示するものとして回答させて頂きます。
　それと、SUMPRODUCT関数は、関数内で指定されているセル範囲内に含まれているセルの個数と同じ回数だけ、繰り返し計算を行う関数ですので、例えば6行目～1000行目を計算対象とした場合には、996回も似た様な計算が繰り返される事になります。
　このため、広いセル範囲を処理する際にSUMPRODUCT関数を使用しますと、計算処理に要するコンピュータの負荷が大きくなり、結果が表示されるまでに掛かる時間が長くなる傾向があります。

　ですから、御質問の「重複」や「連日」を表示する際の条件分岐を決める部分には、SUMPRODUCT関数ではなく、COUNTIFS関数を使われる事を御勧め致します。

　今仮に、「重複」や「連日」等の結果をL列に表示させるものとします。
　まず、F6セルに次の関数を入力して下さい。

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

　そして、F6セルをコピーして、「重複」や「連日」等の結果を表示させるための欄内にある、全てのセルに貼り付けて下さい。

　以上です。

お礼

丁寧なご回答ありがとうございます。

＞質問の条件には矛盾があります。
＞「帰ってきた翌日から」新たな出張が始まる場合が「連日」になるという事は、「帰って来た日」に新たな出張が始まる場合や、「帰って来る前」に新たな出張が始まる場合は、「連日」とはならないという事になります。

おっしゃるとおりです。
帰ってきた日に別の新たな出張がある場合などは、到着日と出発日が同じことになるので、「重複」と表示されることになります。
なお、その場合、最初の出張も新たな出張も、ともに「重複」と表示させたいと思います。

＞そして、前の回の出張は「帰ってきた日」で終了しているのですから、「帰ってきた翌日」は「前の回の出張期間」とは重なっていない事になりますので、「連日」になる場合には、絶対に「重複」とはなり得ない事になります。

たとえば、
１行目　田中　一郎 12/1 12/2
２行目　田中　一郎 12/2 12/3
３行目　田中　一郎 12/4 12/4
４行目　田中　一郎 12/5 12/6
５行目　田中　一郎 10/5 10/5
６行目　田中　一郎 12/24 12/24
７行目　田中　一郎 12/24 12/25

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

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

３番の回答もそうですが、センスと技術の光る式を組んでいただいたのに誠、申し訳ありません。
理解できない点が多いので、まずは、途中まで理解して、あれこれ苦闘した質問文のSUMPRODUCT関数で考えていただけるととても助かります。

よろしくお願いいたします。