発地、着地、出荷希望日を入力することで、自動的にETAが表示される関数

発地、着地、出荷希望日を入力することで、自動的にETAが表示される関数を求めています。

貿易事務をやっています。エクセルで、下記のような関数を作るにはどうすればよろしいでしょうか。

テーブル　サンプル
発地A　着地B　ETD 　　ETA
東京　米国　10月1日　10月15日
東京　米国　10月7日　10月22日
東京　米国　10月14日　10月29日
上海　米国　10月3日　10月23日
上海　米国　10月10日　10月30日
上海　米国　10月14日　11月3日
東京　英国　10月5日　11月15日
東京　英国　10月9日　11月19日
東京　英国　10月20日　11月30日

上のような船便のカレンダーがあります。発地-着地-ETD(発送予定日-ETA(到着予定)の順番で
下方向に時系列にずらっと並んでいます。（発地-着地は上では３つづつならんでいますが実際はランダムであり、組み合わせは３０くらいあり）

お客様から来たオーダーには、発地、着地、出荷希望日が分かります。

よって発地、着地、出荷希望日を入力することで、自動的に直近のETDを検索し、さらにETAが表示される関数を求めています。

例えば東京発、英国行きで希望出荷日10月7日ならば、直近のETD　10月9日を検索し、ETA11月19日を返す、という流れです。

Vlookup、Maxあたりを調べてみましたが思った結果が得られません。
どなたか詳しい方のご助言をいただきたく、よろしくお願いいたします。

No.1 回答者： mshr1962 回答日時： 2010/04/22 23:48

上記の表がA1:D1000にあるとして

条件がG2,H2,I2に入力で
=IF(SUMPRODUCT(($A$2:$A$1000=$G$2)*($B$2:$B$1000=$H$2)*($C$2:$C$1000>=$I$2)),SUMPRODUCT(MIN(2000^((($A$2:$A$1000=$G$2)+($B$2:$B$1000=$H$2)+($C$2:$C$1000>=$I$2))<>3)*($D$2:$D$1000))),"")

No.2 回答者： keithin 回答日時： 2010/04/23 00:03

シート１のＡＢＣＤ列に船便カレンダー，とりあえず２０００行ほど蓄積してあるとして。

シート２のＡ２，Ｂ２，Ｃ２にオーダーの発地、着地、出荷希望日として。
数式はシート２に
=MIN(IF((Sheet1!A1:A2000=A2)*(Sheet1!B1:B2000=B2)*(Sheet1!C1:C2000>=C2),Sheet1!D1:D2000))
と記入し，コントロールキーとシフトキーを押しながらEnterで入力する。
（ただしETD当日の出荷希望日でも可の場合。）

数式のセルを右クリックしてセルの書式設定の表示形式からユーザー定義を選び
yyyy/mm/dd;;"NA"
と設定しておく。セミコロンなので間違えないこと。

No.3 回答者： tom04 回答日時： 2010/04/23 00:14

こんばんは！

一例です。

条件として、日付は昇順に並んでいるものとします。
↓の画像で説明させていただきます。

Sheet2に条件を入力すれば表示出来るようにしてみました。
Sheet1に作業用の列を設けています。

作業列E2セルに
=IF(COUNTBLANK(Sheet2!$A$2:$C$2),"",IF(AND(A2=Sheet2!$A$2,B2=Sheet2!$B$2,C2>=Sheet2!$C$2),ROW(),""))
という数式を入れ、オートフィルで下へずぃ～～～！っとコピーします。

そして、Sheet2のD2セルに
=IF(COUNTBLANK(A2:C2),"",INDEX(Sheet1!C:C,SMALL(Sheet1!$E:$E,1)))
という数式を入れ、隣のE2セルまでオートフィルでコピーすると
画像のような感じになります。

以上、参考になれば良いのですが
他に良い方法があれば読み流してくださいね。m(__)m

No.4 回答者： KURUMITO 回答日時： 2010/04/23 11:21

発地などの入力を次々に打ち込んでいくときに関連するデータが連続して表示されることが必要でしょう。

そのためには多少式が複雑になりますが次のように対応すればよいでしょう。
例えばＡ列からＤ列までの1行目には項目名があるとして、下方には関連するデータがあるとします。
Ｆ列は作業列としてＦ2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(A2="","",IF(OR(A1<>A2,B1<>B2),A2&B2&1,IF(OR(A2<>A3,B2<>B3),A2&B2&2,"")))

答えの表ですが例えばＨ1セルに発地、Ｉ1セルに着地、Ｊ1セルに希望出荷日、Ｋ1セルにＥＴＤ、Ｌ1セルにＥＴＡとそれぞれ項目名があるとして、Ｈ列からＪ列にデータが入力されたときにＫ列からＬ列にその日付を表示させるとしたら次のようにします。

Ｋ2セルには次の式を入力します。

=IF(COUNTIF(F:F,H2&I2&1)=0,"",IF(J2>INDEX(C:C,MATCH(H2&I2&2,F:F,0)),"ETDの最終日を超えた希望日です",INDEX(C:C,MATCH(H2&I2&1,F:F,0)+IF(COUNTIF(INDEX(C:C,MATCH(H2&I2&1,F:F,0)):INDEX(C:C,MATCH(H2&I2&2,F:F,0)),J2)>0,MATCH(J2,INDEX(C:C,MATCH(H2&I2&1,F:F,0)):INDEX(C:C,MATCH(H2&I2&2,F:F,0)),0)-1,MATCH(J2,INDEX(C:C,MATCH(H2&I2&1,F:F,0)):INDEX(C:C,MATCH(H2&I2&2,F:F,0)),1)))))

Ｌ2セルには次の式を入力します。

=IF(ISNUMBER(K2),INDEX(D:D,MATCH(H2&I2&1,F:F,0)-1+MATCH(K2,INDEX(C:C,MATCH(H2&I2&1,F:F,0)):INDEX(C:C,MATCH(H2&I2&2,F:F,0)),0)),"")

Ｋ2およびＬ2セルを選択してそれらの式を下方にオートフィルドラッグします。

最後にＫおよびＬ列を選択してセルの表示形式を日付にします。

これでデータを次々に入力することで、関連のデータも連続して表示させることができます。

No.5 回答者： web2525 回答日時： 2010/04/23 11:57

検出された結果を他のセルで参照して処理を行う必要がある場合以外で、単に目視による確認だけが必要なのであれば

複雑な計算式を利用せずにオートフィルターで抽出する方が実用的かと思われます