[[20041217022632]]　『検索処理について』（蘭）　ページの最後に飛ぶ

『検索処理について』（蘭）

 初めまして！学校で検索処理の宿題が出ました。
（シート１）　　　　　　新幹線料金計算
	コード	駅名						
	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)

コメント返信：

[ 一覧(最新更新順) ]

[[20041217022632]] 『検索処理について』（蘭） ページの最後に飛ぶ

[[20041217022632]]　『検索処理について』（蘭）　ページの最後に飛ぶ