『検索処理について』（蘭） 初めまして！学校で検索処理の宿題が出ました。 （シート１）　　　　　　新幹線料金計算 コード 駅名 1 東京 2 品川 3 新横浜 4 名古屋 5 京都 6 新大阪 7 新神戸 8 姫路　　9　 岡山 10 福山　　11　　　　広島　　12 徳山 13 新山口　14 小倉　　15 博多 コード 車種 1 のぞみ　2　　　　ひかり （シート２） 　　　　　　　　東京　　　　　品川　　　　　新横浜　　　　　名古屋 　　　　　運賃　　特急　　運賃　　特急　　運賃　　特急　　運賃　　特急 東京　　　　−　　−　 品川　　　160　　2390　　　―　　　― 新横浜　　480　　2390　　400　　　2390　　―　　　― 名古屋　　6090　　4690　　6090　　4690　　5460　　4690　　―　　　　― 　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　（以下略） こんな感じで、別シートに分けられたものを、VLOOKUP・HLOOKUP・IFの３つを使い、 出発駅のコード、到着駅のコードを入力するだけで運賃と特急料金が出るように シート１に処理せよとのことで…。 シート３には『ひかり』のデータも入っています。シート１上でコードを入力し、 駅名を返すのは=if(c4"","",vlookup(c4,$b$9:$c$24,2,0))で出せたのですが、 これをシート２の駅名に反映させるにはどうしたら良いですか？ よろしくお願いします。 ---- セル番地などが判るように、もう少し表を直していただくと回答が出やすいかと思います。 以下のような感じで表せますでしょうか。 Sheet2 A B C D E F G H I 1 東京 品川 新横浜 名古屋 2 運賃 特急 運賃 特急 運賃 特急 運賃 特急 3 東京 − − 4 品川 160 2390 − − 5 新横浜 480 2390 400 2390 − − 6 名古屋 6090 4690 6090 4690 5460 4690 − − 7 （以下略） （川野鮎太郎） ---- 宿題という時点で、回答はしづらいですが(^^;) >VLOOKUP・HLOOKUP・IFの３つを使い この問題は難しい・・・この3つで解けるのでしょうか？ 料金表が鮎さん提示のもので、↓のように表示すると仮定すると C D E F 3 出発駅 到着駅 のぞみ 4 1 4 運賃 特急料金 5 東京 名古屋 6090 4690 IFとVLOOKUPとMATCHを使ってみたら、こんな↓数式になりました。 =IF($E$3="のぞみ",VLOOKUP($D$5,Sheet2!$A$3:$I$6,MATCH($C$5,Sheet2!$A$1:$I$1,0)+(E4<>"運賃"),0), VLOOKUP($D$5,Sheet3!$A$3:$I$6,MATCH($C$5,Sheet3!$A$1:$I$1,0)+(E4<>"運賃"),0)) 問題設定を無視すると、名前の定義とコードの数字を使ってできたりします。 Sheet2のA1:I6に「のぞみ」、Sheet3のA1:I6に「ひかり」と名前を定義して、 =INDEX(INDIRECT($E$3),$D$4+2,$C$4*2+(E4<>"運賃")) とか・・・ 　# 質問文の改行を修正しました　数式もこっそり修正(^^;)　　(Hatch) --------- すみません、セル番地が分かるように書き換えます。 （シート１） 　　　　　A　　　B　　　C　　　D　　　E　　　F　　　G　　　H　　　I　 　１　　　　　　新幹線料金表　 　２ 　３　　　　　　コード　駅名　　　　出発駅　　　　　　　　運賃　　　　　 　４　　出発駅コード　　　　　　　　到着駅　　　　　　　　特急料金 　５　　到着駅コード　　　　　　　　車種　　　　　　　　　合計額 　６　　車種コード 　７ 　８ 　９　　　　　　コード　駅名 １０　　　　　　１　　東京 　１１　　　　　　２　　品川 　１２　　　　　　３　　新横浜 　１３　　　　　　４　　名古屋 　１４　　　　　　５　　京都 　１５　　　　　　６　　新大阪 　１６　　　　　　７　　新神戸 　１７　　　　　　８　　姫路 　１８　　　　　　９　　岡山 　１９　　　　　　１０ 福山 　２０　　　　　　１１　広島 　２１　　　　　　１２　徳山 　２２　　　　　　１３　新山口 　２３　　　　　　１４　小倉 　２４　　　　　　１５　博多 　２５ 　２６　　　　　コード　車種 　２７　　　　　　１　　のぞみ 　２８　　　　　　２　　ひかり （シート２→のぞみ） 　 B 　　　　C 　　　　D　　　　　E　　　　 F 　 G 　　　H　　　　　 I 1 東京 品川 新横浜 名古屋 2 運賃 特急 運賃 特急 運賃 特急 運賃 特急 3 東京 − − 4 品川 160 2390 − − 5 新横浜 480 2390 400 2390 − − 6 名古屋 6090 4690 6090 4690 5460 4690 −− 7 （以下略→表はAF17まで） 　　　 …取りあえずこんな感じです。どうでしょうか？Ｆ３〜５、I３〜５に式を入力して、C４〜６にコードを入力すると金額が返されるようにしたいのですが。。（蘭） ---- シート２のB1セルを左上とするセル範囲の駅名がコード順に並んでいるとすると、 運賃の列番号は 駅コード*2 で出そうです。 特急利用金は 駅コード*2+1 ですね。 例えば名古屋ならコード４ですから運賃は８列目（B列から数えて）、特急料金は９列目になります。 これを使えばVLOOKUP関数だけでいけると思いますがいかがでしょう。違っていたらすみません。（純丸） ---- そうですね…純丸さんのおっしゃる通り、運賃・特急料金の列番号はそれでいけそうですが、 シート１の駅名とシート２の駅名を対応させるにはどうすれば良いのでしょうか…？ I3に入れる式として、 =IF(F5="のぞみ",VLOOKUP(C4,のぞみ!$B$1:$AF$17,C4*2,0))を考えてみたのですが、 やっぱりダメです。。（蘭） ---- 駅名は最初に蘭さんが書かれたような式で良いんじゃないですか。 =VLOOKUP(B4,$B$10:$C$24,2,FALSE) （川野鮎太郎） ---- では「運賃」のところの検索値は何になるのでしょう？ （蘭） ---- (*'ω'*)......ん?　運賃は純丸さんの方法でいけるんじゃないのかな・・・？ それとも、ずばりの答えがほしいのかな・・？ （川野鮎太郎） ---- >=IF(F5="のぞみ",VLOOKUP(C4,のぞみ!$B$1:$AF$17,C4*2,0)) =IF(F5="のぞみ",VLOOKUP(F4,のぞみ!$B$1:$AF$17,C4*2,0)) 　とか？　　(Hatch) ---- むかし、むかし、まだINDEX関数の存在を知らない頃の事じゃった。 作業列(行）を設けて 東京　　品川　　新横浜 2 4 6 東京 品川 新横浜 =VLOOKUP($F$3,Sheet2!$B$3:$AF$18,HLOOKUP($F$4,Sheet2!$B$3:$AF$4,2,FALSE)+IF($F$5="特急",1,0),FALSE) こんな感じで、作ったことを思い出してしまった。 宿題は、自分でやりましょう！という事で『ズバリ言わないわよ！』　 一応　IFとVLOOKUPとHLOOKUPだけ・・・(sin) 追記：=HLOOKUP(・・・VLOOKUP(・・・　　にしても良いかも？　お好きにどうぞ ただ、特急の処理が面倒っぽいけど 間違いを修正しました。 ---- 書き込んだ後、外に出かけたのですが途中で気がつきました。東京を検索値にしてもシート２の 東京の行には金額が入ってないので出ませんね。表の形状からは HLOOKUP関数を使いたいところですが、 特急料金の列には検索値である駅名が入っていない。蘭さんはこの事をおっしゃってるのでしょう。 となるとどうやって？ 自分は、VLOOKUP・HLOOKUP・IF の３つだけでは無理なような気がしてます。 料金が、左下側でなく右上側に入力されていれば VLOOKUP関数で出来るのでしょうが、、、。 他の関数でよければ、下記で出そうです。（純丸） 運賃　　　=OFFSET(Sheet2!$B$2,$C$5,$C$4*2-1,1,1) 特急料金　=OFFSET(Sheet2!$B$2,$C$5,$C$4*2,1,1) ---- 皆様の意見を踏まえて考えてみましたが… =IF(C4="","",VLOOKUP($F$3,のぞみ!$B$3:$AF$17,HLOOKUP($F$4,のぞみ!$B$3:$AF$17,C4+2,0)+IF($F$5="のぞみ",1,0),0))が限界です。。＃Ｎ/Ａになってしまいます…。（蘭） ---- >HLOOKUP($F$4,のぞみ!$B$3:$AF$17,C4+2,0) 範囲と行番号の指定が、違うのでは？　C1:AF17 の何行目？ ここで求めたいのは、VLOOKUPでの列番号ですよね。 >=IF(C4="","",・・・・)+IF($F$5="のぞみ",1,0) C4が空白で無い場合は、+1が確定では？　のぞみとひかりで特急料金違いましたっけ？ スミマセン、物知りませんので。。。違うのであれば、それに対応する一覧表にする必要がありますよね。 運賃：=IF(OR(C4="",C5=""),"",VLOOKUP($F$3,のぞみ!$B$4:$AF$18,HLOOKUP($F$4,のぞみ!$C$1:$AF$2,2,0),0)) 特急：=IF(OR(I3="",C6=""),"",VLOOKUP($F$3,のぞみ!$B$4:$AF$18,HLOOKUP($F$4,のぞみ!$C$1:$AF$2,2,0)+1,0)) 特急料金が違う場合は、 東京 　　　 　品川 　　　新横浜 　　　　　名古屋 2 5 8 11 運賃 のぞみ ひかり 運賃 のぞみ ひかり 運賃 のぞみ ひかり 運賃 のぞみ ひかり こんな感じの表になるでしょうから、 特急：=IF(OR(I3="",C6=""),"",VLOOKUP($F$3,のぞみ!$B$4:$AU$18,HLOOKUP($F$4,のぞみ!$C$1:$AU$2,2,0)+IF($F$5="のぞみ",1,2),0)) 範囲は、適当です。また、2行目に行挿入して、VLOOKUPに対応させる列番号を手入力していることが前提です。 一つ問題があります。ORも関数ですから、使用する関数条件が違っちゃいますねぇ！ 未入力の場合、エラー値が出てよいのならば、 特急：=VLOOKUP($F$3,のぞみ!$B$4:$AF$18,HLOOKUP($F$4,のぞみ!$C$1:$AF$2,2,0)+IF(C6="",#N/A,1),0) とする事も可能です。 (sin)　夜更かししてしまった。 ちょっと修正。 ----- 出来ました！！ =IF(C4="","",IF($F$5="のぞみ",VLOOKUP($F$4,のぞみ!$B$3:$AF$17,C4*2,0)) +IF($F$5="ひかり",VLOOKUP($F$4,ひかり!$B$3:$AF$17,C4*2,0))) で出ましたよっ（感涙）皆様ありがとうございました！！（蘭） ---- おめでとうございます。 出来たという式を見させていただいて、やっと全体のレイアウトが把握できました。 C4*2の意味も。。。一人だけオオボケしてましたね。失礼致しました。 直書き込みでは、把握できない事を痛感。(sin)