エクセルで運賃を 車種別に出したいのです。

質問

運送系の仕事に使う　運賃表を作っています。
１つ目の質問は

１ 　　 A 　　　　 B 　　　 C 　　　　　 D
２　車種　　　　発　　 　　着 　　　　運賃
３　４ｔ　　　　　札幌　　　大阪　　　15000
４　１０ｔ　　　　名古屋 　 仙台　　　23000
５ クール　　　小樽　　　帯広　　　 3500

と　このような表で　発と着をプルダウンで選んだら　Ｄに運賃が出るようにはできました。
例=IFERROR(INDEX($Y$25:$AG$33,MATCH(E20,$Y$24:$AG$24,0),MATCH(F20,$X$25:$X$33,0)),"")
※セルの数値などは気にしないでください。今は試しに作っているので　同じシート内に運賃表を入れて参照しています。

問題は　運賃が　４ｔ　１０ｔ　１５ｔ　トレーラ　クール等で違うことです。
また　発・着それぞれ　２５拠点位あるので　１つの表にしてプルダウンにするのは
かえって大変そうで（各車種別に　２５拠点あるので）
できたら　Ａ列の　４ｔ　１０ｔ等をプルダウンで選んで
別のシートにそれそれ　４ｔ運賃表　１０ｔ運賃表等を作り　それを参照して　
発/着を参照して　運賃を自動的に表示したいのです。

A列が　１０ｔの時は　１０ｔのシートの運賃表をみて　=IFERROR(INDEX($Y$25:$AG$33,MATCH(E20,$Y$24:$AG$24,0),MATCH(F20,$X$25:$X$33,0)),"")を　出しなさい
って　なるのが理想です。

二つ目は　運賃表ですが　INDEXの参照の時　
列と行の数値や値が一緒でなくてはならないと書いてあったのですが、
書いてある順番も一緒でなくては駄目ですか？
たとえば　
行は　札幌　東京　名古屋　大阪
列は　大阪　東京　札幌　名古屋
という並びだと　エラーになりますか？

正直　簡単な関数しか知らず　１つ目の質問のINDEXもここで調べて作りました。
当然マクロも知りません。
できるだけ　わかりやすく教えてください。
お願いします。

ちなみにＸＰ　オフィス2007で作っています。

説明不足があれば　すぐ補足しますので
何卒　ご指導お願いいたします。

回答No.4

hallo-2007

データの持ち方で一案ですが
一枚のシートに添付の様に

車種　　　場所　　　札幌　　　仙台　　　東京　　名古屋　　　大阪
４ｔ　　　札幌　　　　　　　　200　　　300　　　500　　　600
　　　　　仙台　　　200　　　　　　　　200　　　400　　　500
　　　　　東京　　　300　　　200　　　　　　　　200　　　300
　　　　　名古屋　　500　　　400　　　200　　　　　　　　100
　　　　　大阪　　　600　　　500　　　300　　　100

１０ｔ　　札幌　　　　　　　　150　　　250　　　450　　　550
　　　　　仙台　　　150　　　　　　　　150　　　350　　　450
　　　　　東京　　　250　　　150　　　　　　　　150　　　250
　　　　　名古屋　　450　　　350　　　150　　　　　　　　　50
　　　　　大阪　　　550　　　450　　　250　　　　50
・・・・・・・
と横方向に着名を25列、行方向は　25行＊車種数をとった一覧にしてみます。
別途
車種一覧の範囲に
４ｔ
１０ｔ
１５ｔ
・・・と準備ます。
仮に　K3～M3　セルに　入力規則で　プルダウンで選択しているとします。
最初に、車種が出てくる　行番号は　=MATCH(K3,A:A,FALSE)　
発地の名称が何行目にでてくるかを　=MATCH(L3,B3:B7,FALSE)
着地の名称が何列目に出てくるかを　=MATCH(M3,A2:G2,FALSE)
といったようなMatch関数で得られます。
金額は　INDEX関数で行番号、列番号を指定して　=INDEX(A:G,K6+L6-1,M6)
で求めます。
もちろん式が長くなりますが、作業に使用したセルを省くこともできます。

回答No.3

tom04

No.2です！
たびたびごめんなさい。

もう一度質問文を読み返して・・・
質問の2番目の回答をしていませんでしたね。

数式でMATCH関数を使っているということは列・行項目の並びが変わっても問題ないはずです。
ただ、
数式を拝見すると行と列が逆ではないのでは？

INDEX関数は
配列（表の範囲）
行番号（表内の何行目か？）
列番号（表内の何列目か？）
の順になります。

※　前回の方法の場合は同じ並びでないとデタラメなデータが表示されてしまいます。

それから数式では拠点が9拠点の表配置のような気がします。
というコトは、車種によって拠点数が違うのですかね？

その場合は前回の方法でも構わないのですが、表の空白部分がかなり出ると思います。

それを回避する方法として、
A列の車種によってB・C列のリスト表示を変更することが出来ます。
長くなるので今回はこの程度で・・・

ごめんなさいね。m(_ _)m

回答No.2

tom04

こんにちは！
一案です。

車種別に↓の画像のように表を作成しておきます。
25拠点あるということですが、今回は5拠点での方法です。
やり方は一緒です。

各拠点の表の拠点の並びはすべて同じで、運賃データのみ変更しておきます。
そしてSheet1のF列に拠点を羅列（この並びも各表の並びと一緒）しておく。
※　F列が目障りであれば、遠く離れた列にするか、F列を非表示にしておきます。

準備（1）
車種別毎のデータに名前定義しておきます。
画像では「4ｔ」SheetのB2～F6セルを範囲指定（データ部のみ）　→　名前ボックスに
_4t
と定義
（数値・アルファベット等は名前定義できない場合がありますので、最初に「_」アンダーバーを入れておきます。）

同様に「10ｔ」SheetのB2～F6セルを範囲指定　→　_10t　と名前定義
これをすべての車種で行います。

そしてSheet1のD2セルに
=IF(COUNTBLANK(A2:C2),"",INDEX(INDIRECT("_"&A2),MATCH(B2,$F$1:$F$5,0),MATCH(C2,$F$1:$F$5,0)))
という数式を入れオートフィルで下へコピーすると
画像のような感じになります。

これで何とか希望に近い形にならないでしょうか？m(_ _)m

回答No.1

aokii

運賃表を作ってからvlookupを使って運賃を自動抽出してみて下さい。