旅費を二重払いしないための関数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),"確認",""))

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

回答No.1

foomufoomu · ベストアンサー

考えをまとめるため、はじめに、重複判定の方法を解説しておきます。

AとBで範囲が重複しているデータというのは、

A　　　＝＝＝＝＝
B＝＝＝＝＝

A＝＝＝＝＝
B　　　＝＝＝＝＝

A　＝＝＝＝＝＝＝
B　　＝＝＝＝＝

A　　＝＝＝＝＝
B　＝＝＝＝＝＝＝

以上のパターンになります（端がそろっているものも同じパターンとみることにします）

これに共通している条件は
Aの始端<=Bの終端　かつ　Bの始端<=Aの終端
です。
（念のため、重複しないデータは
（A　＝＝＝＝
（B　　　　　　　＝＝＝＝
（Bの始端<=Aの終端の条件に合わないことがわかります。

さらに連続データも含めるため、検討するデータは両端を１日ずつ大きくして考えることにします。

これを式にすると (2行目に書く式の場合)
{=if( sumproduct( (B$2:B$1000=B2) * (C$2:C$1000<=D2+1) * (C2-1<=D$2:D$1000) ) > 1,"確認","" ) }
(1行目は、見出しが書かれているので、2行目からの検討にしています。)

この式では、かならず自分自身のデータも比較されてしまう（たとえばC2=<D2+1）ので、ほんとなら条件に合わない場合でもsumproductの結果は１になってしまいます。なので、条件に合う時の値は２以上なので最後の比較は >=1 でなく >1 になっています。

お礼

ありがとうございます。
非常にわかりやすく、適切に書いていただき、大変助かりました。

回答No.3

kagakusuki

Excel2010を御使いなら、SUMPRODUCT関数ではなく、COUNTIFS関数を使った方が計算処理の負荷が軽くなるかと思います。
　次の関数を「確認」と表示させたい列のセル（行番号は問いません）に入力してから、そのセルをコピーして、「確認」と表示させたい欄の他の全てのセルに貼り付けて下さい。

=IF(AND(INDEX($B:$B,ROW())<>"",COUNT(INDEX($C:$C,ROW()),INDEX($D:$D,ROW()))=2),SUBSTITUTE(IF(COUNTIFS($B:$B,INDEX($B:$B,ROW()),$C:$C,"<="&INDEX($C:$C,ROW()),$D:$D,">="&INDEX($C:$C,ROW())-1)>1,"出発確認","")&IF(COUNTIFS($B:$B,INDEX($B:$B,ROW()),$D:$D,">="&INDEX($D:$D,ROW()),$C:$C,"<="&INDEX($D:$D,ROW())+1)>1,"帰着確認",""),"確認帰","・帰"),"")

　すると、
出発日が「他の出張の、出発日から『帰着日の翌日』までの期間」に重なる場合には「出発確認」と表示され、
帰着日が「他の出張の、『出発日の前日』から帰着日までの期間」に重なる場合には「帰着確認」と表示され、
出発日が「他の出張の、出発日から『帰着日の翌日』までの期間」に重なっていて、尚且つ帰着日が「他の出張の、『出発日の前日』から帰着日までの期間」に重なっている場合には「出発・帰着確認」と表示されます。

お礼

素晴らしい関数をありがとうございます。
なんとか解読できました。

回答No.2

picopico_7

Ｎｏ.１の方がとても分かりやすくまとめて下さっているのでお気づきになったと思いますが、３つの条件を満たす解は最低１つは必ず存在します。
また、質問者さんは「11行目には確認が入るが12行目には入ってくれない」とおっしゃっておられましたが12行目の出発日(11/15)以上である到着日はデータ上１つ(12行自身)しかないので「確認」の文字が入りません。
もしまだ解決されていないようでしたら条件を箇条書きにして補足に書いてみて下さい。

お礼

ありがとうございます。
回答していただき、理解できました。

画像付きでありがとうございました。