エクセルで差額や誤差、目標値との差を計算すると、結果がマイナスになることがあります。
マイナス記号を除いた数値だけを表示したい場面で役立つのが、数値の絶対値を求めるABS関数です。
ABS関数は非常にシンプルな関数ですが、売上の差異分析、在庫数の比較、温度差の算出、許容差の判定など、実務で使える範囲は広いといえます。
ABS関数の基本ポイント
・マイナスの数値をプラスの数値に変換する関数です。
・プラスの数値と0は、そのままの値で返します。
・数式は =ABS(数値) の形で入力します。
この記事では、エクセルのABS関数の使い方、数式の入力方法、オートフィルでのコピー、IF関数などとの組み合わせ方を順番に解説していきます。
数値の向きではなく差の大きさだけを確認したい場合に、ABS関数を使いこなしていきましょう。
エクセルのABS関数の基本入力
| A列 | B列 |
|---|---|
| 元の数値 | ABS関数の結果 |
| -35 | 35 |
| 18 | 18 |
| 0 | 0 |
それではまず、ABS関数で絶対値を求める基本の入力方法について解説していきます。
ABS関数の書式
ABS関数の書式は、=ABS(数値)です。
=ABS(A2)
セルA2に入力された数値の絶対値を返す数式です。
数値には直接数値を指定できるほか、セル番地、別の関数、計算式を指定できます。
たとえば=ABS(-120)と入力すると、結果は120です。
=ABS(A2-B2)のように入力した場合は、A2セルとB2セルの差を計算したあと、その差の絶対値を返します。
絶対値とは、数直線上で0からどれだけ離れているかを表す値です。
したがって、-35と35は符号こそ異なりますが、どちらも0から35離れているため、絶対値は35になります。
プラスかマイナスかを判断する必要がなく、数値の大きさだけを比較したい場合に便利な考え方です。
セル参照による数式の入力
実際の表でABS関数を使う場合は、数値が入っているセルを参照する入力方法が基本です。
たとえば1行目を見出し行とし、A列のA1セルに「差額」、B列のB1セルに「絶対値」と入力した表を用意します。
A2セルに-500、A3セルに240、A4セルに-80が入力されている場合、B2セルに=ABS(A2)と入力します。
Enterキーを押すと、B2セルには500と表示されます。
ABS関数は元のセルの値を書き換える関数ではなく、別のセルに計算結果を表示する関数です。
そのため、A2セルに-500という元データが残っていても、B2セルには500という絶対値を表示できます。
元データの符号を保持したまま、比較用の数値だけを作成したいときに適した方法です。
数式を入力するときは、半角のイコール記号から入力しましょう。
全角の＝を使うと、計算式ではなく文字列として扱われることがあるため注意が必要です。
プラスとゼロの処理
ABS関数はマイナスの数値だけに使う関数ではありません。
プラスの数値を指定した場合は、その数値を変えずに返します。
たとえば=ABS(45)の結果は45です。
また、=ABS(0)の結果は0になります。
つまりABS関数は、数値の符号を取り除いて、常に0以上の値に整える関数と考えると分かりやすいでしょう。
入力値が正か負かで数式を分ける必要がないことが、ABS関数の大きな利点です。
差額がマイナスの場合もプラスの場合も、同じ=ABS(A2)という式で処理できます。
なお、セル内に文字列が入力されている場合は、ABS関数では計算できません。
数値として認識されているかを確認してから使いましょう。
【操作のポイント】ABS関数は、マイナス値だけでなくプラス値と0にも同じ数式を使えるため、差額一覧や誤差一覧を一括で整える際に便利です。
差額と誤差に使うABS関数
| A列 | B列 | C列 |
|---|---|---|
| 実績 | 目標 | 差の絶対値 |
| 980 | 1000 | 20 |
| 1080 | 1000 | 80 |
続いては、実績と目標の差額、測定値と基準値の誤差を求める使い方を確認していきます。
2つの数値の差を絶対値にする数式
実績と目標の差を確認するとき、単純に=A2-B2と計算すると、実績が目標を下回った場合はマイナスになります。
不足額と超過額を区別したいならマイナス表示にも意味があります。
一方で、目標からどの程度離れているかだけを知りたい場合は、符号が判断の邪魔になることがあります。
=ABS(A2-B2)
A2セルとB2セルの差を計算し、プラスまたはマイナスの符号を除いた差の大きさを返します。
たとえばA2セルが980、B2セルが1000なら、A2-B2の結果は-20です。
ここにABS関数を付けた=ABS(A2-B2)の結果は20になります。
A2セルが1080、B2セルが1000の場合は、通常の差も80、ABS関数の結果も80です。
目標未達と目標超過を同じ基準で比較したいときは、ABS関数で差の大きさをそろえると見やすくなります。
誤差の許容範囲を判定する方法
ABS関数は、測定値が基準値から許容範囲内にあるかを確認する場面でも活躍します。
たとえば基準値が100で、誤差が3以内なら合格とする場合を考えてみましょう。
=IF(ABS(A2-B2)<=3,”合格”,”要確認”)
A2セルの測定値とB2セルの基準値の差が3以下なら合格、それを超えたら要確認と表示する数式です。
測定値が97なら差は-3ですが、ABS関数によって3として判定されます。
測定値が103なら差は3であり、同じく合格です。
97と103は基準値の上下にありますが、基準値からの距離という観点では同じ誤差です。
このような双方向の許容差判定では、ABS関数が特に分かりやすい選択になります。
割合の差と絶対値
差額だけでなく、割合の差を比較する場合にもABS関数を使えます。
たとえば前月売上がB2セル、当月売上がA2セルに入っている場合、増減率は=(A2-B2)/B2で求められます。
増加率か減少率かを問わず変動幅だけを表示したいなら、=ABS((A2-B2)/B2)と入力します。
表示形式をパーセンテージに設定すると、売上の変動率を正の割合として確認できます。
ただし、前月売上にあたるB2セルが0の場合は、0で割ることになるためエラーになります。
割合計算では、分母となるセルが0ではないかも確認しましょう。
符号を消すと増減の方向は分からなくなるため、方向も必要な資料では通常の増減率列と絶対値の列を分けて並べるのがおすすめです。
【操作のポイント】差額の向きが必要なら通常の引き算を残し、差の大きさを比較する列にはABS関数を使うと、分析の目的を分けられます。
オートフィルによるABS関数のコピー
| A列 | B列 |
|---|---|
| 差額 | 絶対値 |
| -125 | =ABS(A2) |
| 80 | オートフィル後 |
| -42 | オートフィル後 |
続いては、入力したABS関数を複数行へ効率よくコピーするオートフィルについて確認していきます。
フィルハンドルを使う手順
データが複数行に並んでいる場合、各セルに=ABS(A2)、=ABS(A3)と手入力する必要はありません。
まずB2セルに=ABS(A2)と入力し、計算結果を表示します。
次にB2セルをクリックすると、セルの右下に小さな四角形が表示されます。
この小さな四角形がフィルハンドルです。
フィルハンドルを下方向へドラッグすると、B3セル、B4セルへ数式がコピーされます。
コピー後の数式は、B3セルでは=ABS(A3)、B4セルでは=ABS(A4)のように自動調整されます。
セル参照が行ごとに変わる仕組みを相対参照と呼びます。
同じ種類の計算を連続したデータに適用するときは、オートフィルを使うと入力ミスを減らせます。
ダブルクリックで連続データへコピー
隣の列に連続したデータがある場合は、フィルハンドルをダブルクリックする方法も便利です。
たとえばA列にA2セルからA100セルまで数値が入力されているなら、B2セルのフィルハンドルをダブルクリックします。
すると、A列のデータが続く最終行付近まで、ABS関数が自動的にコピーされます。
長い表を扱う場合、下端までドラッグし続ける必要がないため作業時間を短縮できます。
ただし、途中に空白行があると、そこでコピーが止まることがあります。
データの途中に空白が含まれる表では、コピー範囲を選択してからCtrlキーとDキーを押す方法も検討しましょう。
数式をコピーしたあと、最終行まで正しく結果が表示されているかを確認することが大切です。
絶対参照との違い
ABS関数の名前には絶対という言葉が含まれますが、セル参照の絶対参照とは別の意味です。
ABS関数は数値の絶対値を求める関数です。
一方の絶対参照は、数式をコピーしても参照先を固定するための指定方法です。
たとえば基準値がC1セルに入力されていて、A2セル以降の測定値との差を計算する場合は、=ABS(A2-$C$1)と入力します。
$C$1と指定すると、数式を下へコピーしても基準値の参照先はC1セルのままです。
=ABS(A2-$C$1)
A2セルの測定値と、固定したC1セルの基準値との差の絶対値を求める数式です。
ABS関数と$記号は役割が異なるため、混同しないようにしましょう。
数値の符号を消したい場合はABS関数、参照先を固定したい場合は$記号を使います。
【操作のポイント】最初の計算式だけを正しく作成してオートフィルすれば、大量のデータでもABS関数を効率よく適用できます。
ABS関数とIF関数の組み合わせ
| A列 | B列 | C列 |
|---|---|---|
| 測定値 | 基準値 | 判定 |
| 98 | 100 | 合格 |
続いては、ABS関数とIF関数を組み合わせて、誤差の大きさを自動判定する方法を確認していきます。
許容差の合格判定
品質管理や検品表では、基準値との差が一定範囲に収まっているかを判定することがあります。
このような表では、ABS関数とIF関数を組み合わせると、合格と不合格を自動表示できます。
=IF(ABS(A2-B2)<=2,”合格”,”不合格”)
この数式では、A2セルの測定値とB2セルの基準値の差が2以下であれば合格と表示します。
測定値が98、基準値が100なら差は-2ですが、ABS関数の結果は2です。
したがって、条件を満たして合格と表示されます。
上限だけでなく下限もある範囲判定では、ABS関数を使うと数式を短くできます。
ABS関数を使わない場合は、測定値が98以上かつ102以下かという複数条件を作る必要があります。
警告メッセージの表示
IF関数の結果には、合格や不合格以外のメッセージも設定できます。
たとえば誤差が5を超えた場合だけ確認を促したいなら、=IF(ABS(A2-B2)>5,”要確認”,””)と入力します。
誤差が5以下なら空白、5を超える場合は要確認と表示されます。
一覧表の中で注意が必要な行だけを目立たせたい場合に有効です。
さらに条件付き書式を設定すれば、要確認と表示されたセルを赤色で塗りつぶすこともできます。
数式による判定と表示形式を組み合わせると、見落としにくい管理表になります。
複数条件での判定
ABS関数の結果を、AND関数やOR関数と組み合わせることもできます。
たとえば差額が10以下で、かつ数量が0より大きい場合だけ確認済みと表示するような条件です。
数式が長くなる場合は、まずABS関数だけで誤差列を作り、その列を参照して判定式を作る方法もあります。
計算過程を列に分けると、後から数式を確認しやすくなります。
特に複数の担当者が編集する共有ファイルでは、複雑な式を1つのセルに詰め込みすぎない工夫も必要です。
数式の分かりやすさは、表を長く安全に運用するための重要な要素です。
【操作のポイント】許容範囲の上下を同じ基準で判定したいときは、IF関数の条件部分にABS関数を入れます。
ABS関数で発生するエラーと注意点
| A列 | B列 |
|---|---|
| 入力値 | =ABS(A2)の結果 |
| -25 | 25 |
| 文字列 | #VALUE! |
続いては、ABS関数で起こりやすいエラーと、計算時に知っておきたい注意点を確認していきます。
文字列によるVALUEエラー
ABS関数の引数には、数値または数値として計算できる式を指定します。
セルに文字列が入っている場合、=ABS(A2)を入力すると#VALUE!エラーが表示されることがあります。
見た目は数字でも、先頭にアポストロフィが付いていたり、文字列形式で取り込まれていたりすると計算できない場合があります。
セルの表示形式を数値に変えるだけでは直らないこともあります。
セルを編集してEnterキーを押す、VALUE関数で数値に変換する、区切り位置機能を使うなど、原因に合わせて対処しましょう。
エラーが出たときは、まず参照元セルが本当に数値として認識されているかを確認します。
空白セルを参照した場合
ABS関数が空白セルを参照した場合、通常は0として扱われます。
たとえばA2セルが空白で、B2セルに=ABS(A2)を入力すると、結果は0になります。
空白のまま表示したい表では、IF関数を使って条件を追加します。
=IF(A2=””,””,ABS(A2))
A2セルが空白なら空白を返し、値がある場合だけ絶対値を表示する数式です。
入力前の行に0が並ぶことを避けたい場合に使えます。
ただし、空白と0を区別しない集計であれば、単純なABS関数だけでも問題ありません。
表を何のために使うかによって、空白の扱いを決めるとよいでしょう。
符号情報が消える点
ABS関数を使うと、マイナスかプラスかという方向の情報は失われます。
たとえば売上差額が-100だった場合、ABS関数の結果は100です。
この結果だけを見ると、目標を100下回ったのか、100上回ったのかは判断できません。
そのため、減少と増加の判別が必要な業務では、元の差額列を残しておくことが重要です。
ABS関数の列は差の大きさを比べる補助列として使い、元の符号付き数値も併記すると分析しやすくなります。
方向と大きさを分けて確認できれば、誤差管理と業績分析のどちらにも対応しやすくなります。
【操作のポイント】ABS関数の結果だけでは増減の方向が分からないため、必要に応じて元データや通常の差額列を残しましょう。
まとめ エクセルのABS関数の使い方（数値の絶対値）
| 項目 | 確認内容 |
|---|---|
| 基本数式 | =ABS(A2) |
| 差額の絶対値 | =ABS(A2-B2) |
| 許容差の判定 | =IF(ABS(A2-B2)<=3,”合格”,”要確認”) |
エクセルのABS関数は、数値の絶対値を求めるための基本関数です。
基本となる数式は、=ABS(数値)です。
マイナスの数値はプラスの数値に変わり、プラスの数値と0はそのまま表示されます。
実績と目標の差、測定値と基準値の誤差、予算と実績の乖離など、符号ではなく差の大きさを見たい場面で活用できます。
=ABS(A2-B2)と入力すれば、2つのセルの差を常に0以上の数値として表示できます。
さらにIF関数と組み合わせることで、許容範囲内かどうかの自動判定も可能です。
オートフィルを使えば、最初のセルに入力した数式を複数行へ素早くコピーできます。
ただし、ABS関数を使うとプラスとマイナスの方向は分からなくなります。
増加か減少かも確認したい場合は、符号付きの差額列を残したうえで、ABS関数の列を比較用に追加しましょう。
ABS関数は、数値の向きではなく差の大きさを扱うための関数です。
実務の表では、誤差管理、差額比較、許容範囲の判定に役立ちます。
