エクセルの近似値の関数で教えて下さい。

質問

B30からB38のセルを選択し
条件付き書式で以下のような数式を入れています。

=B30=LOOKUP(1,0/(ABS($B$30:$B$38-$A$1)=MIN(INDEX(ABS($B$30:$B$38-$A$1),))),$B$30:$B$38)

セルA1に数値を入力し、その数値の近似値をB30～B38の中で探し、当てはまるセルを赤く塗り潰すようにしてあります。
この時、0の近似値を探す際に、B30～B38の中で空白があるとそこを選ばれてしまい困っています。空白セルは無視して、数値の入ってる所だけを参照したい場合はどうすればいいでしょうか？

回答No.3

mt2008 · ベストアンサー

ANo.2です。
元々の近似値を求める式が空白を考慮していない（空白＝0と計算）しているせいですね。
これでどうでしょう
=AND(B30<>"",ABS(B30-A$1)=MIN(INDEX(ABS(9＾9*($B$30:$B$38="")+$B$30:$B$38-A$1),)))

お礼

できました！！！ありがとうございます。本当に悩んでいたので非常助かりました。また勉強にもなりました。
本当にありがとうございました！！

回答No.2

mt2008

取り敢えずこれでどう？
=AND(B30<>"",B30=LOOKUP(1,0/(ABS($B$30:$B$38-$A$1)=MIN(INDEX(ABS($B$30:$B$38-$A$1),))),$B$30:$B$38))

補足

早急なご回答ありがとうございます。
上記の式を入れてみたところ、確かにA1が0の時、空白セルは無視されたのですが
数値が入力されているセルに塗り潰しがされませんでした。

空白以外のセルを参照して、その中で0に一番近いセルを塗り潰しにはどのようにすればよいのでしょうか？

回答No.1

mshr1962

=AND(B30<>"",B30=LOOKUP(1,0/(ABS($B$30:$B$38-$A$1)=MIN(INDEX(ABS($B$30:$B$38-$A$1),))),$B$30:$B$38))
でいいのでは？

もしくは、条件1を
=B30=""
として、書式で文字色を設定（塗りつぶしにはしない）で
条件2を
=B30=LOOKUP(1,0/(ABS($B$30:$B$38-$A$1)=MIN(INDEX(ABS($B$30:$B$38-$A$1),))),$B$30:$B$38)
とすれば、空白の場合は条件1、空白でない場合に条件2となり、塗りつぶしの対象から逃れることができます。

補足

早急なご回答ありがとうございます。
上記の式を入れてみたところ、確かにA1が0の時、空白セルは無視されたのですが
数値が入力されているセルに塗り潰しがされませんでした。

2つとも上手くいきませんでした。
自分のやり方がいけないのかもしれませんが・・・

空白以外のセルを参照して、その中で0に一番近いセルを塗り潰しにはどのようにすればよいのでしょうか？