エクセルで運賃検索表を作りたいのですが・・・

運賃表を作ろうと勉強しながらなのですが、全く上手くいかないのでご教授願います。

作りたいものは、https://takuhai.yahoo.co.jp/ypack/yp201
こちらの「都道府県からの検索」のようなもので
発送元は同じなので、発送元の部分は都道府県を入力でき
発送先になっている所で「市」が選べ、
重量を入れれば金額が出てくる、というものが作りたいのです。

現在、発送先の住所（市）を見て→距離が書かれている用紙→
別の用紙で距離から重量と送料を見るという作業をしています。
（別用紙は、↓こんな感じで、距離と重量で送料を見ます。
http://www.post.japanpost.jp/fee/simulator/kokun …）
用紙が複数に渡り、間違いもおきやすいかと思い、なんとか便利なものを作れないかと試行錯誤しています。

会社でデータだけ打ち込んできたので、明日から続きの作業が出来ればと
仮の住所や送料を入れて家で練習していますが、
まったく上手く入力できていないので、エラーばかりになってしまいます。
まったく同じ例がないので、応用が利かずマイっています。
検索なのでVLOOKUPを使うんだとうということまでは、わかってきたのですが、
最初の例えば、「北海道」を選ぶと「札幌・小樽・函館」などが選べるとこから失敗しています。
初心者のくせに中級なことをしようとしてるからなのですが・・・
少しづつエクセルを使えるようになりたいので、わかりやすいアドバイス宜しくお願い致します。

No.8 ベストアンサー 回答者： noname#79209 回答日時： 2007/02/07 04:02

#5です。

VLOOKUPでもイケますね。
=VLOOKUP(都市名,運賃表,IF(ISNA(HLOOKUP(重量-0.001,重量区分,1,TRUE)),MIN(重量区分列番号),HLOOKUP(重量-0.001,重量区分,2,TRUE)+1),FALSE)

ここで、
都市名：都市を選択するセル

重量：重量を入力するセル

運賃表：都市名も含んだ重量別料金の範囲
　　　例：
　　　　　室蘭市｜1000｜1200｜1300｜...
　　　　　小樽市｜2000｜2200｜2300｜...

重量区分：重量の範囲と列順が入力された2行の範囲
　　　例：
　　　　　5｜10｜15｜20｜20.001｜
　　　　　2｜ 3｜ 4｜ 5｜ 6｜
　　　※VLOOKUPで使用するので必ず「2」から始めてください。
　　　　5kgのときは2列目（1列目は都市名）、10kgの時は3列目の数値を引き出したいので．．．

重量区分列番号：上記の2行目部分
　　　例：
　　　　　2｜ 3｜ 4｜ 5｜ 6｜

上記のように、各範囲に名前をつけておくと便利です。

また、#1さんの参照URLのように、
都道府県を選択するセルに「都道府県」と名前をつけ
都市名が列挙されている範囲に、それぞれの属する都道府県の名前をつけておきます。
　　　例；
　　　　　小樽市　　＋
　　　　　札幌市　　＋－－これらを選択して「北海道」と名前をつける
　　　　　室蘭市　　＋

　　　　　青森市　　＋
　　　　　弘前市　　＋－－これらを選択して「青森県」と名前をつける
　　　　　つがる市　＋

そこで、都市名を入力するセルの入力規則に、
値の種類：「リスト」
元の値：「=都道府県」
と設定すれば、都道府県に「青森県」を選択すれば、都市名のセルの選択肢が、
「青森市」「弘前市」「つがる市」に限定されます。

この回答へのお礼

お礼が遅くなりすいませんでした。

まだ完成していないのですが、放置しているのも気が咎めましたもので・・・
しかし皆様のおかげで、以前より見やすいものが出来つつあります。
本当に、ありがとうございました。
色んなことを教えて頂いたおかげで、少しだけエクセルが
わかってきた気がします。

またエクセルのことをお聞きすることが、あるかと思いますので、
またアドバイス頂けると嬉しいです。

お礼日時：2007/02/16 21:44

No.7 回答者： noname#79209 回答日時： 2007/02/06 15:54

> ８，１２，１８と半端な数字だと一考を要しますね。

この場合、ちょっと工夫が必要ですね。
要は列方向のMATCH関数の代わりに列順を指定できれば良いわけですから、
重量区分を表記する範囲（ここではC2～G2とします）に、それぞれ5,10,15,20,20.001と入力し、
C2～F2の表示書式を「#"kgまで"」G2の表示書式を「#"kg超"」とし、
さらに2行目と3行目の間に1行挿入します。新たに挿入した行のC列～G列に、1,2,3,4,5を入力します。
そして、料金を計算させるセルには、
=IF(VALUE(C1)=0,"",INDEX(C4:G6,MATCH(B1,B4:B6,0),IF(ISNA(HLOOKUP(C1-0.0001,C2:F3,2,TRUE)),MIN(C3:F3),HLOOKUP(C1-0.0001,C2:F3,2,TRUE)+1)))
としておけば良いでしょう。
なお、「重量はキログラム単位で入力する」としています。
0.001を引いているのは重量区分が「～まで」なので、区分ピッタリ（5,10,15,20など）の時に下のレンジにしたいからです。

この回答へのお礼

新たな問題への解答ありがとうございます。

ベースとなる表が、ようやく完成したので
＃６さんの補足に書いてみました。
ご提示頂いた内容を元に、自分の作っているものと置き換えて
また頑張ってみます。

補足に書いていたプルダウンは、都道府県までは大丈夫だったのですが、
市の方で設定を間違ったようで、上手くいきませんでした。
たぶんコソコソと作っていたからだとは、思うのですが・・・

大昔にＰＣを買ったお店が、購入者に無料講座を開いてくれてたのに
行かなかったことを、今更ながらに後悔しています。
でも皆様が親切に教えて下さるので、大変勉強になっています。
本当に、ありがとうございます。

お礼日時：2007/02/06 23:56

No.6 回答者： sannri 回答日時： 2007/02/06 06:05

VLOOKUP関数よりINDEX関数のほうが簡単でしたね。

でもちょっと気になることが出てきました。
重量が５．５Kｇの場合は、５Kgの値を拾ってきますが、この場合１０Kgの値でなければならないのでしょう？
５．５Kgなら、重量入力時に１０Kgなどと出来るならよいのですが、８，１２，１８と半端な数字だと一考を要しますね。ここらが未解決と思います。
MATCH(D1,H1:K1,1))　の「D1」に何かの細工が必要かなと思いますが、いまは思いつきません。他の人のサポートをお願いします。

この回答への補足

こんばんは。
とうとう？家に表を転送しました(笑)

Sheet1にデータ？となる表が完成しました。
A1に「都道府県」と記入し、A2～A465まで北海道～和歌山まで入ってます。
四国や九州などいらないようなので、このようになってます。

B1に「市」と記入し、B2～B465まで函館市～市の名前を入れました。

C1から重量になりまして、C1が１０ｋｇ、D1が２０ｋｇ・・・
最後は、AZで４０００ｋｇになります。
（増え方は不同で、最初は１０ｋｇづつ途中から２０ｋｇづつ、更に５０ｋｇづつ、１００ｋｇづつになったりするので、AZまでです。）

　　　Ａ　　　　Ｂ　　　Ｃ　　　　　Ｄ　　　　　Ｅ
１　都道府県　　市　　１０ｋｇ　　２０ｋｇ　　３０ｋｇ・・・
２　北海道　　函館市　８００　　１０００　　　１２００・・・
３　北海道　　札幌市　８００　　１０００　　　１２００・・・

こんな感じです。

仕事中、こっそり皆さんのプリントアウトを見ながらやっていたら
プルダウンから失敗(ノ_-;)ハア…

Sheet2、Sheet3には、距離や運賃のデータを仮に入れてたものがあるので、
使用するシートは、Seet4に作るつもりです。

重量に関しては、切上げた重量で見るのですが、プルダウンに出来ないとなると
５５ｋｇと入れたら６０ｋｇの価格を返してくれるものがほしいです。

折角アドバイスを貰っているので、頑張ってみますが
また具体案がありましたら、よろしくお願いします。

補足日時：2007/02/06 21:10

この回答へのお礼

新たな問題にまで気づいて下さって、ありがとうございます。
自身では、きっと気づかなかったところです。
たぶん完成したーと喜んでいたところに、問題が生じたことでしょう・・・

やっと詳細（データの元）が、入力できたので
補足に書かせて頂きました。

やっとスタートラインという感じです。(;^_^A

お礼日時：2007/02/07 00:00

No.5 回答者： sannri 回答日時： 2007/02/05 17:41

まず、「Ｇ１」～に下のような表を作ります。

縦に住所、横に重量、中の表は料金です。

　｜　　Ｇ　　　　｜Ｈ　｜Ｉ　　｜Ｊ　　｜Ｋ
１｜　　　　　　　｜５　｜10　　｜15　　｜20
２｜北海道札幌｜500｜800｜1000｜1500
３｜北海道帯広｜600｜900｜1100｜1600
４｜鹿児島指宿｜700｜900｜1200｜1800

Ａ１には「リスト」で都道府県名を、Ｂ１には「ドロップダウンリスト」で市町村名を（この場合、郡は無視して＊＊町としたほうが良いでしょう）選択できるようにします。
Ｃ１には　＝Ａ１＆Ｂ１　と関数を入れます。
Ｄ１には手入力で「５」とか「１０」とか重量を入れます。
Ｅ１には　=IF（D1="","",INDEX(H2:K4,MATCH(C1,G2:G4,1),MATCH(D1,H1:K1,1))）　とすれば完成すると思います。
注意点として、Ｈ１（重量）からの数字が大文字なら、Ｄ１の数字も大文字にしなければ「マッチ」となりません。

この回答へのお礼

再度、回答をありがとうございます。

前回の回答の続きで、VLOOKUP関数ではなくINDEX関数で入れるということになるのでしょうか？
というか、ここまで書いて頂いているので、ご指示通りに入力して
作成してみます。

他の方のお礼にも書いたのですが、どうも仕事中はなかなか進められそうにないので
休みの日に少しずつ進める予定です。

また聞くことがあるかもしれないので、もうしばらく締め切らずおいておきます。（ご迷惑かもしれませんが・・・）

完成したら、またご報告します！！

お礼日時：2007/02/05 21:13

No.4 回答者： noname#79209 回答日時： 2007/02/05 12:43

#3です。

　　　　Ａ　　　　　Ｂ　　　　　Ｃ　　　　Ｄ　　　　
１｜都道府県▼｜都市▼　　｜重量▼　｜
２｜都道府県　｜都市名　　｜５Ｋｇ　｜１０ｋｇ｜．．．．
３｜北海道　　｜小樽市　　｜１０００｜１５００｜
４｜北海道　　｜札幌市　　｜１１００｜１６００｜
と言う行が１０００行続いていて、重量区分がＦ列まであったとして、
セルＤ１に
=INDEX(C3:F1000,MATCH(B1,B3:B1000,0),MATCH(C1,C2:F2))
で可能でしょう。
ただし。B列を都市名のみにしてしまうと、
東京都府中市と広島県府中市など、同名の都市もありますので
工夫が必要です。

この回答へのお礼

更にアドバイスありがとうございます。

関数の入れ方（記入例）まで書いて下さって
感謝感激です。
最初書き込んだ時は、わからんかったら学校行けって言われるのかな？
と不安だったのですが、皆さん親切に教えて下さって
本当に嬉しいです。

こりゃもう完成させなくては！と、気持ちは張り切っております。
先にも書きましたが、やはり会社では時間がとれそうもないので
また休みを使って、ゆっくり作ってみますね。

お礼日時：2007/02/05 21:07

No.3 回答者： noname#79209 回答日時： 2007/02/05 06:15

都道府県｜都市名　｜５Ｋｇ　｜１０ｋｇ　｜．．．．

北海道　｜小樽市　｜１０００｜１５００　｜
と言った表を作っておき、
都市と重量をリストから選択し、
INDEX関数で送料を取り出す方法は如何でしょう。

この回答へのお礼

アドバイスありがとうございます。

こちらの方法でも作ってみようと思います。
今朝アドバイスを見れたので、プリントアウトして持っていったのに
なんやかんやと仕事を渡されて、見出ししか作れませんでした。（泣）

これから少しずつでも進めて行きます。
INDEX関数も、もちろん初の試みですが、がんばります！

お礼日時：2007/02/05 21:04

No.2 回答者： sannri 回答日時： 2007/02/04 18:17

まず、都道府県を検索するには、Ｎｏ１の方のように「リスト」を作ります。

続いて市町村を検索するのに、下記を参考にドロップダウンリストを作ります。
http://www.relief.jp/itnote/archives/000822.php
http://oshiete1.goo.ne.jp/qa2690474.html
最後にvlookup関数で取り込みます。

この回答へのお礼

とてもわかりやすいサイトをご紹介頂き、ありがとうございます。
かなり諦めモードだったので助かりました。
おかげさまでドロップダウンリストは、何とか作れたようです。
（もちろん仮ですが）

この後、重量に導くのも同じやり方で良いのでしょうか？
例えば、札幌市に対して１ｋｇ、５ｋｇ、１０ｋｇというのを定義させて、小樽市にも同様に定義していく・・・
それとも、ここからＶＬＯＯＫＵＰを利用するのでしょうか？

ＶＬＯＯＫＵＰも本など見ながら試していたのですが、エラーばかりで
成功していません。（泣）
またご教授頂けると嬉しいです。

お礼日時：2007/02/04 23:07

No.1 回答者： maron--5 回答日時： 2007/02/04 16:12

>最初の例えば、「北海道」を選ぶと「札幌・小樽・函館」などが選べるとこから失敗しています。

◆下のURLが参考になると思います

参考URL：http://www.kenzo30.com/ex_kisopoint/onepoint_son …

この回答へのお礼

アドバイスありがとうございます。

とても参考になるサイトをご紹介頂き、ありがとうございます。
市を選択までは、なんとか出来たようです。
その後、まだ壁にぶつかっています。

北海道から札幌市を選ぶと、同じ行に書いている重量しか出てこないのですが、
ここから更に選択できるようにするには、どのようにデータを打ち込んでいればいいのでしょうか？

初歩的な質問ばかりで、すいません。

お礼日時：2007/02/04 17:01