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

解決済

質問者：fwod
質問日時：2014/06/06 10:44
回答数：7件

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

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

通報する

この質問への回答は締め切られました。

質問の本文を隠す

回答 (7件)

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)

- 1
- 件

通報する

この回答へのお礼

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

通報する

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

No.6

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

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

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

- 0
- 件

通報する

この回答へのお礼

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

通報する

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

No.5

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

No.4です。

投稿後気づきました。

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

- 0
- 件

通報する

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

- 1
- 件

通報する

この回答へのお礼

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

通報する

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

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

通報する

- 0
- 件

通報する

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)