Excel関数テクニック:ゼロから最も遠い数値を得る方法
Excel関数で、プラスとマイナスが混在した数値リストの中から、もっともゼロから遠い数値を得る方法について紹介します。
すべて正の数だったらMAX関数、すべて負の数ならMIN関数を使えば良いのですが、正負混在した数値群の中から見つけるにはちょっとテクニックが必要になります。
会社で少し悩んだ挙句にできたものなので忘備録として。
[ad#top-1]
まずは誤った方法を紹介
まずは誤ったやり方の紹介です。
例として、A列とB列に正負混在した数値が並べています。各列の「ゼロから最も遠い数値」を割り出すにはどうしたら良いか?
うっかり、MAX関数を使ってしまいそうですが、残念ながら誤りです。下図をご覧ください。黄色のセルにMAX関数を使った数式が入っています。
A列は良いとして、B列は-4を出したいのに3が抽出されました。一番大きい数値を求める関数なので、当然負の数より正の数の方が大きく、その中で最大値を求めているわけです。
次にMAX関数の中に絶対値を求めるABS関数を入れて、[Shift]+[Ctrl]+[Enter]キーを押すと以下のように{ }で括られた数式に変換されて絶対値の中から最大値を求めてくれます。
しかし、-4だったものが4として表示されてしまっています。絶対値だからね。
ちなみに、{ }付の数式は直接入力ができません。必ず[Shift]+[Ctrl]+[Enter]キーを押しましょう。
で、答えは…
ちょっとズルいですがIF関数を使います。これとは別のやり方はいくつかあるのですが、IF文の方が理解しやすいです。
ご覧の通り、それぞれのゼロから一番遠い数値が求まっています。
数式はこちらです。
=IF(ABS(MAX(A3:A9))>ABS(MIN(A3:A9)),MAX(A3:A9),MIN(A3:A9))
- 最大値の絶対値 > 最小値の絶対値 だったら 最大値
- 最大値の絶対値 <= 最小値の絶対値 だったら 最小値
という構文になります。
できれば、標準の関数一発で出したいところですね。
[ad#ad-1]
スポンサーリンク