エクセルで値を参照して入力したい

質問

画像左上のように、出発コード、到着コードと呼ばれるものが存在するとします。
これらは２つの数字（例えば県と市）でできています。
距離と書かれた欄はあいていて、ここに数字を入れていきたいと思います。

出発コード、到着コードの組み合わせと、距離に関しては表が用意してあり、
そこを参照して当てはまる数値を入れていきたいのです。
（完成図は左下）

実際に作業をしたい表では、出発コードと到着コードはランダムで並んでいますが、
かならず右の表のどれかには当てはまるように数値が用意されています。

この作業を数万回行わなければならないため、一度にできる方法を教えてください。

回答No.1

keithin · ベストアンサー

普段使いの関数だけで結果を出します。

（添付図）
推奨：
Ｋ列に
Ｋ２：
=G2&H2&I2&J2
以下コピーを準備

Ｅ２：
=IF(A2="","",VLOOKUP(A2&B2&C2&D2,K:L,2,FALSE))
以下コピーします

何かの理由でどうしてもＫ列を準備できない場合：
Ｍ列に
Ｍ２：
=G2&H2&I2&J2
以下コピーを準備
＃Ｍ列は，実際には邪魔にならないＮ列でもＸ列でもＡＢ列でもどこに置いても構いません。

Ｅ２：
=IF(A2="","",INDEX(L:L,MATCH(A2&B2&C2&D2,M:M,0)))
以下コピーします

回答No.2

kagakusuki

切り取り&ペースト、セルの挿入、削除、等の編集作業を行なっても、正常に動作可能な関数による方法です。

【方法その1】関数と作業列を使用る方法
　まず、適当な列（ここでは仮にM列とします）の中の適当なセルに、次の数式を入力して下さい。

=IF(OR(INDEX($G:$G,ROW())="",INDEX($H:$H,ROW())="",INDEX($I:$I,ROW())="",INDEX($J:$J,ROW())="",INDEX($K:$K,ROW())=""),"",INDEX($G:$G,ROW())&"@"&INDEX($H:$H,ROW())&"@"&INDEX($I:$I,ROW())&"@"&INDEX($J:$J,ROW()))

　次に、上記の関数を入力したセルをコピーして、M列の2行目以下に貼り付けて下さい。
　次に、E2セルに次の数式を入力して下さい。

=IF(OR(INDEX($A:$A,ROW())="",INDEX($B:$B,ROW())="",INDEX($C:$C,ROW())="",INDEX($D:$D,ROW())=""),"",IF(COUNTIF($M:$M,"="&INDEX($A:$A,ROW())&"@"&INDEX($B:$B,ROW())&"@"&INDEX($C:$C,ROW())&"@"&INDEX($D:$D,ROW()))=1,INDEX($K:$K,MATCH(INDEX($A:$A,ROW())&"@"&INDEX($B:$B,ROW())&"@"&INDEX($C:$C,ROW())&"@"&INDEX($D:$D,ROW()),$M:$M,0)),IF(COUNTIF($M:$M,"="&INDEX($A:$A,ROW())&"@"&INDEX($B:$B,ROW())&"@"&INDEX($C:$C,ROW())&"@"&INDEX($D:$D,ROW())),"重複有","該当無")))

　次に、E2セルをコピーして、E3以下に貼り付けて下さい。

【方法その2】関数のみで処理する方法（作業列は不要）
　※但し、右側の表の行数が、何千行にも及ぶ場合には、計算が終了して結果が表示されるまでに、時間が掛かります。
　　　（2000行前後程度までならば、大して時間は掛らないと思います）
　まず、E2セルに次の数式を入力して下さい。

=IF(OR(INDEX($A:$A,ROW())="",INDEX($B:$B,ROW())="",INDEX($C:$C,ROW())="",INDEX($D:$D,ROW())=""),"",IF(SUMPRODUCT(($G$1:INDEX($G:$G,MATCH(9^9,$K:$K))=INDEX($A:$A,ROW()))*($H$1:INDEX($H:$H,MATCH(9^9,$K:$K))=INDEX($B:$B,ROW()))*($I$1:INDEX($I:$I,MATCH(9^9,$K:$K))=INDEX($C:$C,ROW()))*($J$1:INDEX($J:$J,MATCH(9^9,$K:$K))=INDEX($D:$D,ROW())))=1,SUMPRODUCT((INDEX($G:$G,ROW($K$1)+1):INDEX($G:$G,MATCH(9^9,$K:$K))=INDEX($A:$A,ROW()))*(INDEX($H:$H,ROW($K$1)+1):INDEX($H:$H,MATCH(9^9,$K:$K))=INDEX($B:$B,ROW()))*(INDEX($I:$I,ROW($K$1)+1):INDEX($I:$I,MATCH(9^9,$K:$K))=INDEX($C:$C,ROW()))*(INDEX($J:$J,ROW($K$1)+1):INDEX($J:$J,MATCH(9^9,$K:$K))=INDEX($D:$D,ROW()))*(INDEX($K:$K,ROW($K$1)+1):INDEX($K:$K,MATCH(9^9,$K:$K)))),IF(SUMPRODUCT(($G$1:INDEX($G:$G,MATCH(9^9,$K:$K))=INDEX($A:$A,ROW()))*($H$1:INDEX($H:$H,MATCH(9^9,$K:$K))=INDEX($B:$B,ROW()))*($I$1:INDEX($I:$I,MATCH(9^9,$K:$K))=INDEX($C:$C,ROW()))*($J$1:INDEX($J:$J,MATCH(9^9,$K:$K))=INDEX($D:$D,ROW()))),"重複有","該当無")))

　次に、E2セルをコピーして、E3以下に貼り付けて下さい。