エクセル2010でSUMPRODUCT関数を使った条件分岐ができない

質問

• エクセル2010を使っていますが、IFとSUMPRODUCTを使った関数で問題が発生しています。
• 旅費の精算をしており、重複や連日の出張を判定するための関数を作成しましたが、一部の条件でうまく表示されません。
• どの部分で修正すれば意図した表示ができるか教えてください。長い関数ですが、よろしくお願いいたします。

回答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関数で考えていただけるととても助かります。

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