指定範囲から任意の近似値を探したい

画像のようなものを作ろうと思っています。
ですが、vlookupやindex・match関数では私の力ではうまい具合にはいきませんでした。

どのように数式を入れるとよろしいのでしょうか？

No.1 回答者： kybo 回答日時： 2014/06/06 11:15

以下の様に入力し、CtrlとShiftキーを押しながらEnterキーで確定

=MIN(IF(D3:D15>G8,D3:D15,""))


又は、D2、G7にそれぞれ同じ見出し（例えば、「数値」とか）を入れ、
G8は「>1180」といれ、以下の式でもできます。


=DMIN(D2:D15,1,G7:G8)

この回答へのお礼

ありがとうございます、こんな単純な式でよかったのですね。期待していた値になってくれました。

お礼日時：2014/06/06 12:48

No.2 回答者： MackyNo1 回答日時： 2014/06/06 11:28

近似値を表示するG12セルには以下の式を入力します。

=MIN(INDEX((B3:E15<G8)*100000+(B3:E15-G8),))+G8

セル番地を表示する目的がよくわかりませんが（おそらくもっと合理的な処理があると思いますが）、どうしてもセル番地を表示したいならG14セルには以下の式を入力します。

=ADDRESS(SUMPRODUCT((B3:E15=G12)*ROW(B3:E15)),SUMPRODUCT((B3:E15=G12)*COLUMN(B3:E15)),4)

この回答への補足

上の方の値、残念なことに配列の値が見つからないとvalueエラーがでてしまいました。

下のものは期待通りの値がでました、ありがとうございます。

補足日時：2014/06/06 12:52

No.3 回答者： bunjii 回答日時： 2014/06/06 11:34

質問の内容でVLOOKUP関数は一致する値またはより小さい値を検出することができます。

INDEX関数とMATCH関数の組み合わせでは同様な結果になりますがMATCH関数の返り値に1を加算すれば目的に合う結果になるでしょう。
他の方法としてはSUMPURODUCT関数とMIN関数を使って行番号を検出してINDEX関数に渡せば目的の値が得られます。

=INDEX(D3:D15,MATCH(G8,D3:D15)+1,1)
=INDEX(D1:D15,SUMPRODUCT(MIN((D3:D15>G8)*ROW(D3:D15)+(D3:D15<=G8)*1000)),1)

この回答への補足

回答なされた関数は予めD列にあることが分かっているものではありませんか？
今回は画像のものですと、B3:E15のものが対象です。

上は入力値を変えてみるとエラーがでて、
下は循環依存エラーがでてしまいました。

補足日時：2014/06/06 11:46

No.4 ベストアンサー 回答者： tom04 回答日時： 2014/06/06 11:48

こんにちは！

G12セルに
=MIN(IF(B3:E15>G8,B3:E15))
これは配列数式になりますので、Ctrl＋Shift＋Enterで確定！

G14セルに
=ADDRESS(SUMPRODUCT((B3:E15=G12)*ROW(B3:B15)),SUMPRODUCT((B3:E15=G12)*COLUMN(B3:E3)),4)

という数式を入れてみてください。m（_ _）m

この回答へのお礼

ありがとうございます、両方の式で私の期待していた値を出すことができました。
今回は、こちらをベストアンサーとさせてください。

お礼日時：2014/06/06 12:55

No.5 回答者： tom04 回答日時： 2014/06/06 11:49

No.4です。

投稿後気づきました。

No.1さん・No.2さんの回答とほぼダブっています。
どうも失礼しました。m（_ _）m

No.6 回答者： mt2008 回答日時： 2014/06/06 12:23

近似値が複数あった場合の事を考えると、セル番地を表示するよりは条件付き書式で表のセル色を変えた方が良くないですか。

別な用途の為にセル番地を表示しようとしているのでしたらごめんなさい。

この回答へのお礼

ありがとうございます、これ単体で使うのではなく
これを利用して別のものを作ろうとしています。
ですが、たしかに色分けして見易くしたほうが確認も容易ですね、取り入れます。
お気遣いありがとうございます

お礼日時：2014/06/06 13:02

No.7 回答者： bunjii 回答日時： 2014/06/06 12:31

回答No.3です。

検索範囲を勘違いしていたようです。
検索範囲をB3～E15とする場合は次の式を使ってください。

=SUMPRODUCT(MIN((B3:E15>G8)*B3:E15+(B3:E15<=G8)*1000000))

また、検出された値のセルのアドレスを算出する数式を忘れていました。

=ADDRESS(SUMPRODUCT((B3:E15=G12)*ROW(B3:E15)),SUMPRODUCT((B3:E15=G12)*COLUMN(B3:E15)),4)

この回答へのお礼

ありがとうございます、目当ての値を出すことができました。

お礼日時：2014/06/06 13:00