エクセル ゼロに近い数値

質問

-1 , -3 , +5 の中からゼロに近い数値(-1)を選ぶ方法を教えて下さい。

回答No.8

tresbien · ベストアンサー

No.7 の補足です
入力セルの選択を間違えて指示しました。ごめんなさい。B1:B10を選択してください。
A1からA10に適当なデータを入力してから、B1:B10を選択し、No.7に書いた式をコピーして貼り付け、確定するとき指示通りにして、B1:B10のセルに一度に
{=IF(ABS(A1:A10)=MIN(ABS(A1:A10)),A1:A10,"")}
が入力されれば完成です。
これで確かめる事が出来ますね。

お礼

ありがとうございました。
成功しました！
こんな方法がエクセルにあったなんて、新しい発見です。
何度もご親切に回答していただき、感謝いたします。

回答No.7

tresbien

No.3 No5 の者です

=IF(ABS(A1:A10)=MIN(ABS(A1:A10)),A1:A10,"")

を入力する時のやり方が間違っていませんか。
式を入力する前に　A1:A10　を選択しておかねばなりません。そして　上の式を入力して確定するときに、Ctrl キーと Shift キーを押しながら Enter キーを押します。これで10個のセルに同時に上の式を入れられます。（見た目には｛ ｝でくくられた式が全てに入力されています）つまり引数にも配列、数式入力にも配列を使用した事になります。
少しセル範囲が多くてもShift キーを使ったり、引数のセル入力時も番地を覚えていてキーで入力するなどすれば、式の形がシンプルなので分かりやすいと思いますが。
No6 のかたのVBAも良いと思います。
ただ、-0.5と0.5の様なデータが最小値になった場合も考えなければならないのでしたら、更に付け足す必要がありますね。

お礼

何度もすみません。
式は、どのセルに入れればよろしいのでしょうか？
A1:A10を選択したとき、式はA1に入ってしまいます。
きっと、私の勘違いだと思いますが、あと、ちょっとでなので、是非、ご教示をお願いいたします。

回答No.6

BLUEPIXY

ユーザー定義関数を作ってみました。
使い方
=nearZeroMin(A1:A10)

Function nearZeroMin(r As Range)
Dim x As Range, absMin, ret
ret = Application.WorksheetFunction.min(r) '仮の最小値
absMin = Abs(ret) '仮の最小値
For Each x In r
If absMin > Abs(x) Then
absMin = Abs(x)
ret = x
End If
Next
nearZeroMin = ret
End Function

お礼

ありがとうございます。
やはり、VBAは必須ということですね
もっと、勉強します。

回答No.5

tresbien

N0.3の者です。
朝起きて見直したら前回のはさらに改良できました。
もっと多いデータにも対応できるように全てを配列引数にしました。

=IF(ABS(A1:A10)=MIN(ABS(A1:A10)),A1:A10,"")

データがA列に10個ある場合にしてあります。
これなら式の入力も簡単で、しかも前にも言いましたがいろいろな場合に対応します。
いろいろなデータを入力して確かめてください。

お礼

ありがとうございます。
A1が一番小さいときは、表示されました。
A2以外が小さいときは、””という式になるのでしょうか？

回答No.4

hirumin

No.2で回答したものです。
すみません、ちょっと間違っていました。
後ろから2番目の「,1」を「,0」にして下さい。
↓修正後
=INDIRECT(ADDRESS(MATCH(MIN(ABS(A1),ABS(A2),ABS(A3)),ABS(A1:A3),0),1))
MATCH関数の「照合の型」の指定ミスでした。詳しくはヘルプをご覧下さい。

お礼

ありがとうございました。数値を入れ替えてもOKでした。いろんな関数があるんですね
勉強になります。

回答No.3

tresbien

No.2の方の様に配列数式を使うのであれば、
A列の隣B列の３つのセル(B1:B3)を選択しておいて、次のように入力します。
=IF(ABS(A1:A3)=MIN(ABS(A1),ABS(A2),ABS(A3)),A1:A3,"")
確定時にCtrl キーと Shift キーを押しながら Enter キーを押すのを忘れないでください。
B列のどこかに小さい値が現れます。
これの良くないところは、同じ値があった場合同じものが２つ表示される事であり、又良いところは、最小値が-1と1のような場合にも対応できる事です。
データが沢山あるのでしたら、VBAの方がすっきりしますかね。

お礼

ありがとうございます。
データが膨大にありますので、VBAも勉強したいと思います。

回答No.2

hirumin

配列数式を使います。
仮に、
[A1]=-1
[A2]=-3
[A3]=+5
であるならば、任意のセルに
=INDIRECT(ADDRESS(MATCH(MIN(ABS(A1),ABS(A2),ABS(A3)),ABS(A1:A3),1),1))
この式を入れ、Ctrl キーと Shift キーを押しながら Enter キーを押します。
セルの数式は{}で囲まれます。

この数式の流れとしては・・・
(1)ABSで、各々の絶対値を取ります
　　　ABS(-1)=1,ABS(-3)=3,ABS(+5)=5
(2)MINで、その中の最小の値を取ります。
　　　MIN(1,3,5)=1
(3)MATCHで、A1:A3の中で(2)の値のあるセルの位置（何番目か）を取ります。
　　　MATCH(1,ABS(A1:A3))=1（絶対値1と合致するのはA1:A3の"1"番目）
(4)ADDRESSで、A列の[(3)の値]行目のセルアドレスに変換します。
　　　ADDRESS(1,1)=$A$1 前の「1」は(3)で求めたもの。後の「1」は「A列」を表します。
(5)INDIRECTで、(4)のセルアドレスにある値を拾います。
　　　INDIRECT($A$1)=-1

参考になりますでしょうか。

お礼

ありがとうございます。例題はうまくいきました。
[A1]=+5
[A2]=-3
[A3]=-1 と数値を入れ替えた場合には、エラーになりますが、範囲指定をする場合、式のどこを変えれば良いのでしょうか？

回答No.1

ymmasayan

一発では無理でしょう。
列を１列増やして=ABS(　)を求めてからMIN関数で探すしかないでしょう。

お礼

ありがとうございます。
しかし、マイナス符号を表示させたいのです・・・
また、良い方法がありましたら宜しくお願いします。