- ベストアンサー
エクセルで運賃検索表を作りたいのですが・・・
運賃表を作ろうと勉強しながらなのですが、全く上手くいかないのでご教授願います。 作りたいものは、https://takuhai.yahoo.co.jp/ypack/yp201 こちらの「都道府県からの検索」のようなもので 発送元は同じなので、発送元の部分は都道府県を入力でき 発送先になっている所で「市」が選べ、 重量を入れれば金額が出てくる、というものが作りたいのです。 現在、発送先の住所（市）を見て→距離が書かれている用紙→ 別の用紙で距離から重量と送料を見るという作業をしています。 （別用紙は、↓こんな感じで、距離と重量で送料を見ます。 http://www.post.japanpost.jp/fee/simulator/kokunai/index.html） 用紙が複数に渡り、間違いもおきやすいかと思い、なんとか便利なものを作れないかと試行錯誤しています。 会社でデータだけ打ち込んできたので、明日から続きの作業が出来ればと 仮の住所や送料を入れて家で練習していますが、 まったく上手く入力できていないので、エラーばかりになってしまいます。 まったく同じ例がないので、応用が利かずマイっています。 検索なのでVLOOKUPを使うんだとうということまでは、わかってきたのですが、 最初の例えば、「北海道」を選ぶと「札幌・小樽・函館」などが選べるとこから失敗しています。 初心者のくせに中級なことをしようとしてるからなのですが・・・ 少しづつエクセルを使えるようになりたいので、わかりやすいアドバイス宜しくお願い致します。
- みんなの回答 （8）
- 専門家の回答
質問者が選んだベストアンサー
- ベストアンサー
#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のように、 都道府県を選択するセルに「都道府県」と名前をつけ 都市名が列挙されている範囲に、それぞれの属する都道府県の名前をつけておきます。 例； 小樽市 ＋ 札幌市 ＋－－これらを選択して「北海道」と名前をつける 室蘭市 ＋ 青森市 ＋ 弘前市 ＋－－これらを選択して「青森県」と名前をつける つがる市 ＋ そこで、都市名を入力するセルの入力規則に、 値の種類：「リスト」 元の値：「=都道府県」 と設定すれば、都道府県に「青森県」を選択すれば、都市名のセルの選択肢が、 「青森市」「弘前市」「つがる市」に限定されます。
その他の回答 (7)
> ８，１２，１８と半端な数字だと一考を要しますね。 この場合、ちょっと工夫が必要ですね。 要は列方向の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など）の時に下のレンジにしたいからです。
お礼
新たな問題への解答ありがとうございます。 ベースとなる表が、ようやく完成したので ＃６さんの補足に書いてみました。 ご提示頂いた内容を元に、自分の作っているものと置き換えて また頑張ってみます。 補足に書いていたプルダウンは、都道府県までは大丈夫だったのですが、 市の方で設定を間違ったようで、上手くいきませんでした。 たぶんコソコソと作っていたからだとは、思うのですが・・・ 大昔にＰＣを買ったお店が、購入者に無料講座を開いてくれてたのに 行かなかったことを、今更ながらに後悔しています。 でも皆様が親切に教えて下さるので、大変勉強になっています。 本当に、ありがとうございます。
VLOOKUP関数よりINDEX関数のほうが簡単でしたね。 でもちょっと気になることが出てきました。 重量が５．５Kｇの場合は、５Kgの値を拾ってきますが、この場合１０Kgの値でなければならないのでしょう？ ５．５Kgなら、重量入力時に１０Kgなどと出来るならよいのですが、８，１２，１８と半端な数字だと一考を要しますね。ここらが未解決と思います。 MATCH(D1,H1:K1,1)) の「D1」に何かの細工が必要かなと思いますが、いまは思いつきません。他の人のサポートをお願いします。
お礼
新たな問題にまで気づいて下さって、ありがとうございます。 自身では、きっと気づかなかったところです。 たぶん完成したーと喜んでいたところに、問題が生じたことでしょう・・・ やっと詳細（データの元）が、入力できたので 補足に書かせて頂きました。 やっとスタートラインという感じです。(;^_^A
補足
こんばんは。 とうとう？家に表を転送しました(笑) Sheet1にデータ？となる表が完成しました。 A1に「都道府県」と記入し、A2～A465まで北海道～和歌山まで入ってます。 四国や九州などいらないようなので、このようになってます。 B1に「市」と記入し、B2～B465まで函館市～市の名前を入れました。 C1から重量になりまして、C1が１０ｋｇ、D1が２０ｋｇ・・・ 最後は、AZで４０００ｋｇになります。 （増え方は不同で、最初は１０ｋｇづつ途中から２０ｋｇづつ、更に５０ｋｇづつ、１００ｋｇづつになったりするので、AZまでです。） Ａ Ｂ Ｃ Ｄ Ｅ １ 都道府県 市 １０ｋｇ ２０ｋｇ ３０ｋｇ・・・ ２ 北海道 函館市 ８００ １０００ １２００・・・ ３ 北海道 札幌市 ８００ １０００ １２００・・・ こんな感じです。 仕事中、こっそり皆さんのプリントアウトを見ながらやっていたら プルダウンから失敗(ノ_-;)ハア… Sheet2、Sheet3には、距離や運賃のデータを仮に入れてたものがあるので、 使用するシートは、Seet4に作るつもりです。 重量に関しては、切上げた重量で見るのですが、プルダウンに出来ないとなると ５５ｋｇと入れたら６０ｋｇの価格を返してくれるものがほしいです。 折角アドバイスを貰っているので、頑張ってみますが また具体案がありましたら、よろしくお願いします。
まず、「Ｇ１」～に下のような表を作ります。 縦に住所、横に重量、中の表は料金です。 ｜ Ｇ ｜Ｈ ｜Ｉ ｜Ｊ ｜Ｋ １｜ ｜５ ｜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関数で入れるということになるのでしょうか？ というか、ここまで書いて頂いているので、ご指示通りに入力して 作成してみます。 他の方のお礼にも書いたのですが、どうも仕事中はなかなか進められそうにないので 休みの日に少しずつ進める予定です。 また聞くことがあるかもしれないので、もうしばらく締め切らずおいておきます。（ご迷惑かもしれませんが・・・） 完成したら、またご報告します！！
#3です。 Ａ Ｂ Ｃ Ｄ １｜都道府県▼｜都市▼ ｜重量▼ ｜ ２｜都道府県 ｜都市名 ｜５Ｋｇ ｜１０ｋｇ｜．．．． ３｜北海道 ｜小樽市 ｜１０００｜１５００｜ ４｜北海道 ｜札幌市 ｜１１００｜１６００｜ と言う行が１０００行続いていて、重量区分がＦ列まであったとして、 セルＤ１に =INDEX(C3:F1000,MATCH(B1,B3:B1000,0),MATCH(C1,C2:F2)) で可能でしょう。 ただし。B列を都市名のみにしてしまうと、 東京都府中市と広島県府中市など、同名の都市もありますので 工夫が必要です。
お礼
更にアドバイスありがとうございます。 関数の入れ方（記入例）まで書いて下さって 感謝感激です。 最初書き込んだ時は、わからんかったら学校行けって言われるのかな？ と不安だったのですが、皆さん親切に教えて下さって 本当に嬉しいです。 こりゃもう完成させなくては！と、気持ちは張り切っております。 先にも書きましたが、やはり会社では時間がとれそうもないので また休みを使って、ゆっくり作ってみますね。
都道府県｜都市名 ｜５Ｋｇ ｜１０ｋｇ ｜．．．． 北海道 ｜小樽市 ｜１０００｜１５００ ｜ と言った表を作っておき、 都市と重量をリストから選択し、 INDEX関数で送料を取り出す方法は如何でしょう。
お礼
アドバイスありがとうございます。 こちらの方法でも作ってみようと思います。 今朝アドバイスを見れたので、プリントアウトして持っていったのに なんやかんやと仕事を渡されて、見出ししか作れませんでした。（泣） これから少しずつでも進めて行きます。 INDEX関数も、もちろん初の試みですが、がんばります！
まず、都道府県を検索するには、Ｎｏ１の方のように「リスト」を作ります。 続いて市町村を検索するのに、下記を参考にドロップダウンリストを作ります。 http://www.relief.jp/itnote/archives/000822.php http://oshiete1.goo.ne.jp/qa2690474.html 最後にvlookup関数で取り込みます。
お礼
とてもわかりやすいサイトをご紹介頂き、ありがとうございます。 かなり諦めモードだったので助かりました。 おかげさまでドロップダウンリストは、何とか作れたようです。 （もちろん仮ですが） この後、重量に導くのも同じやり方で良いのでしょうか？ 例えば、札幌市に対して１ｋｇ、５ｋｇ、１０ｋｇというのを定義させて、小樽市にも同様に定義していく・・・ それとも、ここからＶＬＯＯＫＵＰを利用するのでしょうか？ ＶＬＯＯＫＵＰも本など見ながら試していたのですが、エラーばかりで 成功していません。（泣） またご教授頂けると嬉しいです。
- maron--5
- ベストアンサー率36% (321/877)
>最初の例えば、「北海道」を選ぶと「札幌・小樽・函館」などが選べるとこから失敗しています。 ◆下のURLが参考になると思います
お礼
アドバイスありがとうございます。 とても参考になるサイトをご紹介頂き、ありがとうございます。 市を選択までは、なんとか出来たようです。 その後、まだ壁にぶつかっています。 北海道から札幌市を選ぶと、同じ行に書いている重量しか出てこないのですが、 ここから更に選択できるようにするには、どのようにデータを打ち込んでいればいいのでしょうか？ 初歩的な質問ばかりで、すいません。
お礼
お礼が遅くなりすいませんでした。 まだ完成していないのですが、放置しているのも気が咎めましたもので・・・ しかし皆様のおかげで、以前より見やすいものが出来つつあります。 本当に、ありがとうございました。 色んなことを教えて頂いたおかげで、少しだけエクセルが わかってきた気がします。 またエクセルのことをお聞きすることが、あるかと思いますので、 またアドバイス頂けると嬉しいです。