Excelで、Sheetを指定してその近似値を参照させる方法

質問

Sheet1のA1には計算式（B1+C1）が入っています。
Sheet2からSheet5には、あらかじめ以下のような
数字と文字を羅列してあります。

A列 B列
10 田中
20 鈴木
30 加藤

さらにSheet1のA2には「Sheet3」のように、
参照するSheetを任意に指定して入力すると、
その指定したSheetのA列から、
Sheet1A1の数字の答えに最も近い値を探し出して、
B列の値をSheet1Ａ3に出す方法は？

意味わかりますでしょうか。もういっぱいいっぱいで
頭がパニック状態です。どうかお助けください！

回答No.2

moon_piyo · ベストアンサー

Sheet1!A1や各シートのA列の値は整数になりますか?
それでしたら下記の式でうまくいくかも

A3: =INDIRECT(A2&"!b"&MOD(SMALL(INDEX(65537*ABS(A1-INDIRECT(A2&"!a1:a99"))+ROW(INDIRECT(A2&"!a1:a99")),),1),65537))

お礼

すごいっ!!うまくいきました!!ありがとうございました。

回答No.3

DoragonFang

(1)
Sheet2～Sheet5のA列の数字が、すべて１０，２０，３０，とか１０単位であれば、ANo1の方の回答に手を加えて、
A3セルに
=VLOOKUP(ROUND(A1,-1),INDIRECT(A2&"!"&"A1:B3"),2,0)
とA1の値の１の位を四捨五入してやれば、近似値になると思います。ただし、この式では、A1の値が、Sheet2～Sheet5のA1より小さいか、A3より１０以上大きい場合はエラーになります。
そこで、式は複雑になりますが、それぞれの場合分けを考慮すると、

=VLOOKUP(IF(ROUND(A1,-1)=0,INDIRECT(A2&"!A1"),IF(MAX(INDIRECT(A2&"!A:A"))<A1,MAX(INDIRECT(A2&"!A:A")),ROUND(A1,-1))),INDIRECT(A2&"!"&"A1:B3"),2,0)

という式でいけると思います。

(2)
上記の場合のようにSheet2～Sheet5のA列が１０単位でもなく、ランダムだった場合（ただし、昇順に並んでいるのは必須です）、
補助計算にB2、B3セルを使って、以下の式で出来るのではないかと思います。

B2セルに
=IF(A1<INDIRECT(A2&"!A1"),INDIRECT(A2&"!A1"),VLOOKUP(A1,INDIRECT(A2&"!A1:B3"),1,1))

B3セルに
=IF(MAX(INDIRECT(A2&"!A:A"))<A1,9999,INDEX(INDIRECT(A2&"!A1:B3"),MATCH(B2,INDIRECT(A2&"!A1:A3"),0)+1,1))

そして、A3セルには
=INDEX(INDIRECT(A2&"!B1:B3"),MATCH(B2,INDIRECT(A2&"!A1:A3"),0)+((B2+C2)/2<A1),1)

という式を入れます。
B2セルは上記の(1)と同じで、A1セルを越えない最大値を求めています。
B3セルはそのすぐ下のセルの値を求めています。
B2とB3セルにIF関数を使っているのは、先述したSheet1!A1の値が参照SheetのA1より小さい場合と、最大値より大きい場合にエラーになるのを防ぐためです。
A3セルでは、B2セルと同じ位置の値を得ているのですが、行位置に((B2+C2)/2<A1)を加えていることで、C2の値に近い場合は１つ下（C2の値の場所）になるように計算しています。

お礼

ありがとうございました。試してみます。助かりました!!

回答No.1

papayuka

もっとも近いって所が難です。

同じ値なら

=VLOOKUP(A1,INDIRECT(A2&"!"&"A1:B3"),2,0)

未満なら

=VLOOKUP(A1,INDIRECT(A2&"!"&"a1:B3"),2)

で良いと思います。

お礼

早速ありがとうございます!!
最も近い値、ってのは、Excelではやはり
不可能なんですかね…。残念