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

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

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

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

A 回答 (3件)

Sheet1!A1や各シートのA列の値は整数になりますか?


それでしたら下記の式でうまくいくかも

A3: =INDIRECT(A2&"!b"&MOD(SMALL(INDEX(65537*ABS(A1-INDIRECT(A2&"!a1:a99"))+ROW(INDIRECT(A2&"!a1:a99")),),1),65537))
    • good
    • 0
この回答へのお礼

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

お礼日時:2005/04/15 22:25

(1)


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

=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列が10単位でもなく、ランダムだった場合(ただし、昇順に並んでいるのは必須です)、
補助計算に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の値に近い場合は1つ下(C2の値の場所)になるように計算しています。
    • good
    • 0
この回答へのお礼

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

お礼日時:2005/04/15 22:28

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



同じ値なら

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

未満なら

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

で良いと思います。
    • good
    • 0
この回答へのお礼

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

お礼日時:2005/04/13 22:45

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!


人気Q&Aランキング