エクセル関数orVBA ２の近似値を返したい

質問

A1:G1に小数点まである、数字が並んでいます。
2.18 1.85 2.33 2.23 1.88 2.11 1.93
そこでH1セルに２に最も近い数値を返す数式を作りたいです。

ネットで探した下の関数ですと、２以下のものを判別してもらえませんでした。
（上のデータですと、1.93を返してほしいのですが、2.11が返ってしまう。）
H1: =SUMPRODUCT(MIN((A1:G1>2)*A1:G1+(A1:G1<=2)*1000000)))

いい方法はありませんでしょうか。よろしくお願いいたします。

回答No.5

keithin · ベストアンサー

Ｈ１：
=INDEX(A1:G1,MATCH(MIN(INDEX(ABS(A1:G1-2),)),INDEX(ABS(A1:G1-2),),0))
と記入してふつーにEnterで入力。

お礼

ありがとうございます。
もっともシンプルで使いやすく感じたため、ベストアンサーにさせていただきます。
助かりました！

回答No.4

bunjii

提示の数式は2より大きい値で1番小さい数値を抽出しています。
次の数式で2に一番近い値を抽出できます。
=INDEX(A1:G1,1,MAX((((A1:G1)-MIN(ABS(2-A1:G1))=2)+((A1:G1)+MIN(ABS(2-A1:G1))=2))*COLUMN(A1:G1)))
この数式は計算途中で配列数の受け渡しをしていますので数式バーへ入力してCtrl+Shift+Enterの打鍵で確定してください。

お礼

ご回答ありがとうざいました。

回答No.3

f272

私なら素直に
=INDEX(A1:G1,,MATCH(MIN(ABS(A1:G1-2)),ABS(A1:G1-2),0))
と入れます。Ctrl+Shift+Enterで配列数式としてください。

お礼

ありがとうございました。

回答No.2

mt2008

こんなのでどうでしょう
H1に↓の式を入れ、Ctrl+Shift+Enterで配列数式として確定
=IFERROR(HLOOKUP(MIN(ABS(A1:G1-2))+2,A1:G1,1,FALSE),2-MIN(ABS(A1:G1-2)))

この式では、2に近い値がプラス方向とマイナス方向でそれぞれ有った時はプラス方向を優先します。
例：2.07と1.93があったら、2.07を返す。

お礼

ご回答ありがとうございました。

回答No.1

kagakusuki

H1セルに次の様な関数を入力されると良いと思います。

=IF(COUNT($A$1:$G$1),SMALL($A$1:$G$1,COUNTIF($A$1:$G$1,"<2")+(IFERROR(ABS(SMALL($A$1:$G$1,COUNTIF($A$1:$G$1,"<2"))-2),9E+307)>IFERROR(ABS(SMALL($A$1:$G$1,COUNTIF($A$1:$G$1,"<2")+1)-2),9E+307))),"")

或いは

=IF(COUNT($A$1:$G$1),LARGE($A$1:$G$1,COUNTIF($A$1:$G$1,">2")+(IFERROR(ABS(LARGE($A$1:$G$1,COUNTIF($A$1:$G$1,">2"))-2),9E+307)>IFERROR(ABS(LARGE($A$1:$G$1,COUNTIF($A$1:$G$1,">2")+1)-2),9E+307))),"")

お礼

ご回答ありがとうございました。