Excel 数値に近い文字列を２つ表示させたい

質問

添付図罫線より左側がG2に上りと入力した例【上り場合】、
右側がG2に下りと入力した例【下りの場合】になります。

A列に路線名、B列に方向、C列に駅名、D列にkmが入力されています。

G1に路線名を京浜東北、G2に方向を上り、
G3にkmを1.5と入力すると、
京浜東北、上りの1.5kmはどの駅からどの駅の区間になるのかを探し、
G4に秋葉原、G5に神田と表示させたいです。

G2が下りと入力された場合は、
【下りの場合】という添付図右側のように、
神田から秋葉原と表示させたいです。

一致するkmを探して駅名を表示するには、
以下の関数かと思うのですが、

=index(c1:c25,sumproduct((a1:a25=g1)*(b1:b25=g2)*(d1:d25=g3),row(c1:c25)))

近い数値もしくは一致する数値(km)を探して、
結果(駅から駅)を表示するには、
どのようにすればいいのかご教示いただきたくお願いいたします。

回答No.7

imogasi

時間をかけて考えて文章にしたものが、いつ締め切られて、無駄になるかわからないので、テストが十分でないが、取り急ぎ上げてみます。
京浜東北線以外も同じ延長線でできると思う。
ーー
２，３日考えたが、本質問の難しさは、データによって参照する（調べる）表を
使い分けないとダメな点です。
小生が前回答で喩えたのは、男・女という（体脂肪率）の２区分でした。
しかし本質問では
１．路線
２．上がり・下り
３．駅名
の３．を除く、路線と上がり・下りの２区分で調べる表の「シート上の範囲｝を
自動的に使い分けないといけないということです。
１だけ１つでも、エクセル関数の難問と思うが、２つ以上はＷＥＢなどでも見たことがない。
多分、難問です。
ーー
エクセルシートに「表を作って」、調べないといけない（計算では答えが出ない）と思うが、 それらの表を
・別シート
・別ブック・別シート
に分けない方が複雑にならず良いと思う。
ーー
シートには
１．検索データをシートのセルに、人間が入力する範囲
Ａ列　路線名　　指定
Ｂ列　上がり・下り　指定
Ｃ列　距離数　指定

２．結果を表示する範囲
Ｄ列には　求める駅名
Ｅ列には　Ｄ列の、「次の駅」（１行下にある）

３．調べる表データの列
上り路線での
Ｆ列　駅名
Ｇ列　距離（始発駅からの）
４．下りの路線での
Ｈ列　駅名（Ｆ列の逆順序の駅名）
I列　距離（終着駅＝下りの始発駅からの）
＝＝
５．調べる表データを作成する作業
６．セル範囲に名前を付ける作業。
Ａ列＋Ｂ列＋駅名（またはＡ列＋Ｂ列＋距離）の文字列を結合した文字列を「名前」ボックスに入れたとき
正しい「調べる表データの列のセル範囲」を指定する、ように
名前定義を、質問者（ユーザー）が行う（設定する）。
京浜東北線上り駅名
京浜東北線上り距離
京浜東北線下り駅名
京浜東北線下り距離
山手線上り駅名
山手線上り距離
山手線下り駅名
山手線下り距離
以下略。
（路線ごとに４種範囲定義する）
システムを作った時に、１度だけ名前定義をするだけでよい。
上端行で名前定義やＶＢＡで名前定義等で、楽する方法はあるが、質問者は
知らないだろうから、繰り返し人手で、作業するほかないだろう。
＝＝
７．エクセル関数を作り入力する作業
その後の基本的考えは、ＩＮＤＩＲＥＣＴ関数で、調べる範囲を割り出し、
その範囲内で、ＭＡＴＣＨ関数で（距離数で）該当行を見つける事です。
次の駅は、見つかった行番号に＋１下行の駅名を採ればよい。
＝＝＝
例データ
Ａ Ｂ Ｃ Ｄ Ｅ Ｆ Ｇ Ｈ Ｉ＜ーー列位置
指定路線 上り下り 指定距離 駅名ＦＲＯＭ 駅名ＴＯ 京浜東北下り 京浜東北上り
京浜東北 上り 3 ああ いい 東京 0 ああ 0
京浜東北 下り 5.7 浜松町 品川 有楽町 3.4 いい 3.8
京浜東北 下り 4 有楽町 浜松町 浜松町 5.6 うう 4.8
京浜東北 上り 5.8 うう ええ 品川 7.3 ええ 5.9
おお 6.7
数式はＤ３に
=INDEX(INDIRECT($A3&$B3&"駅名"),MATCH(C3,INDIRECT($A3&$B3&"距離"),1))
Ｅ３の数式は
=INDEX(INDIRECT($A3&$B3&"駅名"),MATCH($C3,INDIRECT($A3&$B3&"距離"),1)+1)
結果は上記。
ーー
当方で誤りがあったら済まぬ。これ以上時間をかける気がしないので、回答として、１例として、上げます。
ーー
上記のように、この複雑なものを、質問者は、テスト的に検証し、
本番に合わせて、テーブルや式などを修正できるか？
まず無理だろう。（質問文の作成ぶりからそう思う）
回答も良いものが出ているか、今後出るかな？
会社内のエクセルが「相当」できそうな人に相談したら（使い始めた人ではだめ）。
またＶＢＡなどで、システム業者などに、依頼すべきだろう。
ーー
京浜東北の北行き、南行きの点は、ご指摘、ごもっともだがそのままにした。
こういうように、よく考えないで喩え・例えを作ると、よくわかった、詳しい人には、かえって誤解を招き理解してもらえない場合が起こるが、そのケースだと思う。適当な例えを作るのも実力のうち。

回答No.6

bunjii

>一致するkmを探して駅名を表示するには、以下の関数かと思うのですが、
距離が一致する駅名を抽出するには提示の数式で良いでしょう。

>近い数値もしくは一致する数値(km)を探して、結果(駅から駅)を表示するには、
提示の数式を変形すれば可能と思います。
私の論理では次の数式で良いという結果になりました。
上りの場合の起点から指定の距離以上遠く、起点に1番近い駅は次のようになります。
G4=INDEX(C2:C10,MATCH(LARGE((INDEX((A2:A10=G1)*(B2:B10=G2)*(D2:D10>=G3)*D2:D10,0)),SUMPRODUCT((A2:A10=G1)*(B2:B10=G2)*(D2:D10>=G3))),D2:D10))
また、もう１つの要件を満たす駅は次のようになります。
G5=INDEX(C2:C10,MATCH(MAX(INDEX((A2:A10=G1)*(B2:B10=G2)*(D2:D10<=G3)*D2:D10,0)),D2:D10))
貼付画像はExcel 2010で検証した結果です。
下りについては同様な論理なので、あなたの応用力で数式を変更してください。
尚、計算範囲を10行目までにしていますので範囲を増やす場合は10行目の前に必要行数を挿入すると数式の範囲が自動的に挿入した分だけ増えますので試してください。

回答No.5

msMike

余談めきますが、現存する通過駅名と路線名として「京浜東北線」を挙げておられますが、当該路線は東京駅を挟んで運行されるため、「上り」「下り」が適用できないために代わりに「北行」「南行」と称されていることをご存知でせうか？

回答No.4

imogasi

#2です。
下記を思いついたので、追加。
次の駅名も求めるためならＶＬＯＯＫＵＰよりも、
ＭＡＴＣＨ関数の第３引数が１の方が、次の行をつかむのには、よさそうだ。
例データ　Ａ１：Ｅ７
Ａ，Ｂ列は元データ駅名とそこまでの距離
ｃ列は（問題の）距離
Ｄ列はＦＲＯＭ駅の名、Ｅ列はＴｏ駅の名
東京 　0　 1 東京 神田
神田 　1.3 2 御徒町 ああ
御徒町 2 3.4 ああ いい
ああ 　3 　　4.1 いい うう
いい 　3.5 4.5 いい うう
うう　4.6 　　5.6 うう ええ
ええ　6.1
Ｄ列の関数　=INDEX($A$1:$A$7,MATCH(C1,$B$1:$B$7,1))
Ｅ列の関数　=INDEX($A$1:$A$7,MATCH(C1,$B$1:$B$7,1)+1)
次の行ということで＋１している。
この例がもし正しいなら、質問の例を作るのも簡単で、画像なんかいらないだろう。

回答No.3

msMike

同じ「●●線」に、「D」駅を基点とする上下線と「え」駅を基点とする上下線があるのは何故でせう？

回答No.2

imogasi

前の取り消した質問の場合も、わかりにくい質問で、変な例を挙げる人だなと思ったが、本件もよくわからない。
本当のニーズを換骨脱胎して（やっていることや職場を隠したいために）、別のケースの例に置き換えたのかな、と思うが、それが適当でなく、読者にわかりにくくするのかなと思う。
エクセルの質問でも、適当な例を挙げられるのは相当勉強や経験が要ると思う。
ーー
下記は見当はずれなら、済まない。
しかし下記は使えれば有用な関数利用分野だとおもうので、我慢して付き合って。
エクセル関数の、ＶＬＯＯＫＵＰ関数のＴＲＵＥ型を使うべき問題ではないかと思うが、質問者の挙げる例が悪くて、よくわからない。鉄道路線の始発駅からの距離を問題にするような例は、人生でも職場でも起こらないだろう。
あるいは交通費の精算などで、運賃を算出したいのか？
ーー
全く勝手な例を挙げておく。
これと「一脈を通じる問題ではないか」考えてほしい。この（問題の要点のパターン化）判定能力もエクセルの経験がいる（一番大切な能力だ）とは思うが。
ーーー
数値のどの範囲区分に属するかを、調べて、別列の文字列を引いてくる問題。
一例
　体脂率の判定の問題
男性 女性
0 低い 　　0 低い
10 標準 　　20 標準
20 やや高い 30 やや高い
25 高い 　　35 高い
上記の男性と女性のカテゴリの別は、質問の場合は鉄道の路線（京浜東北、山手など）に相当するのかなと思う。もっと多数あっても良い。
基準表のデータは、数値データは、少ないものから大きいものの順に表をつくっておくこと。
例データ
Ａ，Ｂ列に　Ｃ列に関数結果が出る
カテゴリ データ 検索結果（望む結果）
男性 23 やや高い
女性 14 低い
男性 12 標準
女性 31 やや高い
男性 21 やや高い
女性 30 やや高い
男性 15 標準
女性 29 標準
男性 25 高い
男性 29 高い
女性 40 高い
とデータがあるとして
Ｃ２の式は
=VLOOKUP(B2,INDIRECT(A2),2,TRUE)
と入れて下方向に式を複写。
このＩＮＤＩＲＥＣＴ関数を使うところは、勉強してないと意味が分からないと思うし、考えても、思いつくのは少数者だろう。出会って、真似するしかない。
ＶＬＯＯＫＵＰ関数でデータで参照する表部分を切り替える問題です
https://kokodane.com/kan34.htm
複数の参照表を自由に切り替えて「表引き」をする技

回答No.1

msMike

京浜東北、上りで 1.5km が、どうして 秋葉原～神田 になるのか、理路整然と説明されたし

補足

説明不足で申し訳ありません。

東京を起点に0.1ずつ印があるとして、
秋葉原には2.0、神田には1.3の印があります。
上り電車に乗って1.5の印が見えたのは、
上りの何駅から何駅の間ですか？
と聞かれた時の回答がG4の秋葉原、G5の神田という事になります。

よろしくお願い申し上げます。