LOOKUP関数について

質問

電車の料金表があったとします。
　　A　　　B　　C　　D
1　　　　横浜　渋谷　新宿
2　大阪　100　　120　150
3　梅田　120　　200　250
4　京橋　150　　300　400

始発を列にして所持金180円だとするとどこまで、
行くことが出来るか？という問題です。
A9＝梅田
A10＝180
とすると

＝LOOKUP(A10,OFFSET(B1,MATCH(A9,A2:A4),0,1,3),B1:D1）
というところまでは分かったのですが、もしここで
駅が増えてしまった場合（始発駅でも終着駅でも）にどのように数式を変化させたらいいのか分かりません。

どなたか、この問題を解いてくださ～い。
よろしくお願いします。

回答No.2

imogasi · ベストアンサー

（１）使われている関数は３つです。Match、Offset,Lookupです。
（２）内側からMATCH(A9,A2:A4)はA2からA4の表の中でA9と等しい駅名文字を探せ見つかれば（あれば）表の上から何番目かを返す（答える）。A9は梅田でA2からA4の表の第２番目なので、２を返す。
（３）始発駅が増えれば、A5以下に増やし、A2:A8等に変える。
問い合わせ駅と運賃をA9とA10に置いているのは拡張に邪魔である。
（４）次にOffsetであるが、OFFSET(B1,2,0,1,3)となるが、運賃表の
左上隅が基準にとっているので、B1の１００円である。ここを基準にして
A9とMatchした梅田は２行下と言うことで＋２、その列を始点にするので
０（B列）、そしてB3より、行数１で列数３、すなわちB3:D3を出したことになる。ここで３は列数が増えると（横浜、渋谷、新宿がさらに右に増えると）４，５，６・・と増やさなければならない。
（５）最後にLOOKUPであるが、LOOKUP(A10,B３：D3,B1:D1)について.
A10の１８０について、B3:D3のテーブルを探す。一致するものがないが、検査値（１８０）以下の値の最大値を検査結果と
するので１２０円の列で第１行目である。B1:D1の表の第１番
目の横浜が表示される。
（６）ここで表が増えるとB3:J3のように増え、B1:D1もB1:J1のように増える。

回答No.3

nishi6

質問にある算式を書き換えてみます。
　質問の式：＝LOOKUP(A10,OFFSET(B1,MATCH(A9,A2:A4),0,1,3),B1:D1）

(1)始発駅、終着駅が増えても変更の必要のない算式－１

　　=LOOKUP(A10,OFFSET(B1,MATCH(A9,A:A,0)-1,0,1,255),B1:IV1)

　このようになりますが、入力するセルがA9、A10なので、始発駅をA8までしか増やせません。

(2)始発駅、終着駅が増えても変更の必要のない算式－２
　料金表と入力位置を変えてみます。
　料金表はＢ列から作成。始発駅の入力＝A2、料金＝A3とします。A4で計算します。

　　=LOOKUP(A3,OFFSET(C1,MATCH(A2,B:B,0)-1,0,1,254),C1:IV1)

　終着駅は254駅入力できます。始発駅は日本中の駅全て？（何駅あるんでしょう）
　MATCHは列自体を指定できることを利用し、LOOKUPの列指定は最右端のIV列まで指定しています。
　ある意味、調べる範囲を特定していないことになります。
　料金表の下、右側には料金表以外の要素が入力されていないとしています。

(3)始発駅、終着駅が増えても変更の必要のない算式－３（検索する範囲を決める方法）
　始発駅、終着駅数が増減してもいいように検索する範囲に範囲名を付けます。前提は(2)を使います。
　(2)とは違い、MATCHとLOOKUPで対象にする範囲を明確に決めています。

　挿入→名前→定義で
　　範囲名：始発駅　の参照範囲に　=OFFSET($B$2,0,0,MATCH("",$B:$B,-1)-1,1)
　　　入力後、Sheet1!$B$2 のようになるかもしれませんが、かまいません。
　　範囲名：終着駅　の参照範囲に　=OFFSET($C$1,0,0,1,MATCH("",$1:$1,-1)-2)
　とします。これは、MATCHを使って入力された最終セルを探して、OFFSETで範囲指定しています。

　A4セルに
　　=LOOKUP(A3,OFFSET(C1,MATCH(A2,始発駅,0),0,1,COUNTA(終着駅)),終着駅)

　　　MATCH(A2,始発駅,0)
　　　　　で入力した始発駅がある行を探します。
　　　OFFSET(C1,MATCH(A2,始発駅,0),0,1,COUNTA(終着駅))
　　　　　で入力した始発駅の料金表を指定
　　　LOOKUP(A3,・・・,終着駅)
　　　　　で入力した料金で買える最大の料金を求め、それに対応する終着駅を求めます。

(2)がいちばん簡単ですが、(3)は算式の意味をつかまえやすいかもしれません。

ご参考に。

お礼

ワー！！すごいです！ありがとうございます！
この３つの方法をそれぞれ試してみましたが、どれも完璧です。
本当にありがとうございました。
また、機会がありましたらよろしくお願いします。

回答No.1

Fat01ton

電車の料金表が増えた場合どうすれば良いかという質問でしょうか？

＝LOOKUP(A10,OFFSET(B1,MATCH(A9,A2:A4),0,1,3),B1:D1）

の式で
始発駅が1つ増えたら　A2:A4→A2:A5
　＝LOOKUP(A10,OFFSET(B1,MATCH(A9,A2:A5),0,1,3),B1:D1）
到着駅が1つ増えたら　B1:D1→B1:E1　0,1,3→0,1,4
　＝LOOKUP(A10,OFFSET(B1,MATCH(A9,A2:A4),0,1,4),B1:E1）

で良いのでは勘違いの回答でしたらすいません。

補足

範囲を変えれば簡単に済むのですが、私の場合は
駅名、料金を追加すれば自動的に範囲も変わるという
式に変えたいのです。
そんなことは、無理でしょうか？