Excelでは売上と返品、入金と出金、予定と実績の差など、正の数と負の数を同じ表で扱う場面が数多くあります。
数値の計算自体は通常どおり行えますが、マイナス記号の見せ方、赤字表示、ゼロの扱い、時間の差が負になった場合の表示には注意が必要です。
特に時刻の引き算では、結果が負の時間になるとセルに#######が出ることがあり、数値と同じ感覚で処理すると原因が分かりにくいかもしれません。
この記事で押さえるポイント
・正の数と負の数は四則演算でそのまま計算できること
・表示形式を使うとマイナス記号や赤字を見やすく設定できること
・負の時間は日付システムや数式を見直して対処すること
ここでは、実務で使いやすい数式、表示形式、負の時間表示の考え方を順番に確認していきます。
正の数と負の数を計算する基本操作
| A | B | C | D |
|---|---|---|---|
| 1 | 項目 | 増減額 | 残高 |
| 2 | 売上 | 12000 | 12000 |
| 3 | 返品 | -2500 | 9500 |
それではまず、正の数と負の数を使った基本的な計算について解説していきます。
Excelでは、正の数には通常プラス記号を付けず、負の数だけを半角のマイナス記号から入力します。
たとえばB2に12000、B3に-2500と入力すれば、返品額を減額として計算に含められます。
足し算で増減を合計する数式
増額と減額が1列に並んでいる表では、SUM関数で合計する方法が最も分かりやすい操作です。
1行目を見出しとした場合、B2からB10の増減額を合計する数式は次のとおりです。
=SUM(B2:B10)
正の数は合計を増やし、負の数は合計から自動的に差し引かれます。
つまり、足し算の式に負の数を含めるだけで、加算と減算を一度に処理できる仕組みです。
マイナス値を別途引く必要はなく、SUM関数の対象範囲にそのまま含めることが重要です。
セルを個別に指定するなら、=B2+B3+B4のように入力しても問題ありません。
ただし行が増える表では、範囲指定のSUM関数のほうが修正しやすく、入力ミスも抑えられます。
引き算とマイナス記号の考え方
ある数から別の数を引くときは、半角のハイフンを使って=B2-C2のように入力します。
たとえば売上が12000、返品額が2500と正の数で別々に入力されている場合、差引額は= B2-C2ではなく、空白を入れずに=B2-C2と入力します。
計算結果がマイナスになれば、Excelは通常、-500のように先頭へマイナス記号を付けて表示します。
一方で、C2にすでに-2500が入力されているなら、=B2+C2とするのが正しい式です。
負の数を引く式は、結果として足し算になるため、符号を二重に考えないことが大切です。
12000から-2500を引く計算は、=12000-(-2500)です。
この結果は14500となり、マイナスの金額を取り消す計算になります。
会計や在庫の表では、入力ルールを正数と負数のどちらに統一するかを最初に決めると、式が読みやすくなります。
掛け算と割り算で符号を扱う方法
掛け算と割り算では、正負の組み合わせによって結果の符号が決まります。
正の数どうし、または負の数どうしを掛けると結果は正の数です。
正の数と負の数を掛けると、結果は負の数になります。
たとえば単価が500円、返品数量が-3なら、=500*-3の結果は-1500です。
セル参照で入力する場合は、=B2*C2のようにアスタリスク記号を演算子として使いますが、本文の説明では数式欄を確認してください。
=B2*C2
数量をマイナスで記録しておけば、単価との掛け算だけで返品金額を負の値として作成できます。
割り算でも符号の規則は同じです。
ただし、分母がゼロのときは#DIV/0!エラーになるため、件数や数量が空欄またはゼロになる表ではIFERROR関数などで保護する方法も役立ちます。
【操作のポイント】正負を表す列と、計算結果を出す列を混在させず、入力用と計算用を分けると集計式を確認しやすくなります。
正の数と負の数を見やすく表示する書式設定
| A | B | C |
|---|---|---|
| 1 | 区分 | 金額 |
| 2 | 収入 | 15000 |
| 3 | 支出 | -4200 |
続いては、負の数を見やすく表示するための書式設定を確認していきます。
数値そのものを変更せず、見た目だけを赤字や括弧付きに変えられる点が表示形式の利点です。
標準の表示形式でマイナスを確認する方法
Excelの標準表示では、負の数は先頭にマイナス記号が付いた数値として表示されます。
セルに-4200と入力し、数式バーにも-4200と表示されるなら、値は正しく負の数として認識されています。
表示が意図と違うときは、セルを選択してホームタブの数値グループを確認しましょう。
文字列として入力されていると、SUM関数で合計されなかったり、左寄せになったりする場合があります。
セルの左上に緑色の三角が出ている場合は、数値が文字列として保存されている可能性があります。
その場合は警告アイコンから数値に変換するか、先頭のアポストロフィを削除して入力し直します。
全角のマイナス記号や全角数字が混じると変換が必要になることもあります。
赤字と括弧を使うユーザー定義表示形式
負の金額を目立たせたいときは、セルの書式設定から表示形式を変更します。
対象セルを選択し、右クリックしてセルの書式設定を開き、表示形式の分類でユーザー定義を選びます。
金額を3桁区切りにし、負の数だけ赤い括弧で表示する書式は次のとおりです。
#,##0;[赤](#,##0)
セミコロンで区切られた前半が正の数、後半が負の数の表示ルールです。
この設定では、-4200という実際の値を赤い(4,200)として見せられます。
表示形式を変えてもセルの内部値は-4200のままなので、SUM関数や他の数式の結果は変わりません。
円記号を付けるなら、通貨や会計の組み込み形式を選ぶ方法も便利です。
帳票のルールに応じて、マイナス記号を残すか、括弧表記にするかを決めましょう。
ゼロと空白を区別する表示ルール
収支表では、ゼロを0と見せるより、横棒や空白にしたほうが読みやすいことがあります。
ユーザー定義の表示形式は、正の数、負の数、ゼロ、文字列の順に最大4つの区分を指定できます。
#,##0;[赤]-#,##0;-
この書式では、正の数は通常の3桁区切り、負の数は赤いマイナス表示、ゼロは横棒で表示されます。
ゼロを完全に見せたくない場合は、3つ目の区分を空にする指定も可能です。
ただし、空白表示のセルには実際にはゼロが入っているため、未入力と取り違えない運用が必要です。
入力漏れとゼロを区別したい表では、空白を表示する書式よりも横棒表示が安全です。
集計表の見やすさと、データ確認のしやすさを両立させる設定が重要になります。
【操作のポイント】書式設定は数値を変えずに見た目だけを整える機能です。修正前後で数式バーの値を確認すると安心です。
負の数を条件付き書式で強調する方法
| A | B | C |
|---|---|---|
| 1 | 月 | 差額 |
| 2 | 4月 | 1800 |
| 3 | 5月 | -650 |
続いては、負の数だけを自動で目立たせる条件付き書式を確認していきます。
赤字にするだけでなく、背景色やアイコンを使えば、赤字や赤字転換の箇所をすばやく探せます。
セルの強調表示ルールを使う手順
まず、差額が入力されているB2からB10のような範囲を選択します。
ホームタブの条件付き書式からセルの強調表示ルールを開き、指定の値より小さいを選びます。
値の入力欄には0を入れ、表示する書式として淡い赤の塗りつぶしなどを選択します。
これで、0より小さい数値、つまり負の数だけに書式が適用されます。
条件付き書式はセルの値が変わるたびに判定されるため、再計算後の赤字表示も自動で更新されます。
月次の実績表や在庫差異表では、マイナスになったセルを見落としにくくなるでしょう。
ルールを増やしすぎると表が見づらくなるため、色の意味は少数に絞るのがおすすめです。
数式ルールで行全体を色付けする方法
負の差額がある行全体を色付けしたい場合は、数式を使用して書式設定するルールを利用します。
たとえばA2からD10を選択し、差額がC列にあるなら、数式欄には=C2<0と入力します。
=C2<0
列記号Cの前にはドル記号を付けず、行番号も固定しない状態で作成すると、各行のC列を基準に判定できます。
列だけを固定したい場合は、=$C2<0という数式にします。
適用先の先頭行と数式中の行番号を一致させることが、条件付き書式でずれを防ぐコツです。
たとえば適用先がA2:D10なら、判定式も2行目を基準にします。
売上、予算、実績、差額を横並びにした表では、差額のマイナスに連動して行全体を淡い色にする設定が有効です。
マイナスを判定する関数との使い分け
表示だけを変えるなら条件付き書式で十分ですが、判定結果を別セルに出す場合はIF関数を使います。
たとえばB2が負の数なら注意、そうでなければ空白と表示する数式は次のとおりです。
=IF(B2<0,”要確認”,””)
この式をC2に入力し、下方向へオートフィルすれば、負の数がある行だけに要確認と表示されます。
関数の結果は並べ替えやフィルターの条件としても使えるため、対象行を抽出したい場合に便利です。
見た目の警告には条件付き書式、処理用の判定列にはIF関数という使い分けが実務向きです。
符号を基準にしたチェック列があれば、負の値が正しい減額なのか、入力ミスなのかを確認する作業も進めやすくなります。
【操作のポイント】負の数を強調するルールは、数値が入る範囲だけに適用します。表全体に設定すると空欄まで含めて管理しにくくなることがあります。
負の時間を表示するための設定と数式
| A | B | C | D |
|---|---|---|---|
| 1 | 開始時刻 | 終了時刻 | 差 |
| 2 | 10:00 | 09:30 | -0:30 |
続いては、負の時間を扱うときの設定と数式について確認していきます。
時刻はExcel内部で1日を1とする小数として保存されるため、負の結果を通常の日付システムで表示するとエラーのように見えることがあります。
#######が表示される原因
開始時刻より終了時刻が早い状態で、=C2-B2のような時刻の引き算をすると、結果は負の時間になります。
通常の1900年日付システムでは、Excelは負の時刻を時刻形式として表示できず、セルに#######を表示する場合があります。
列幅が狭い場合にも#######は表示されますが、列を広げても消えない場合は負の時間を疑いましょう。
#######は必ずしも数式エラーではなく、負の日時を現在の表示形式で表せないという合図です。
数式バーを確認すると、セルに入力した式自体は残っていることがあります。
勤務時間、予定差、納期差などでは、時刻の前後関係によって負の結果が自然に発生します。
1904年日付システムを使う設定
負の時間を時刻として表示する方法の一つが、1904年日付システムへの変更です。
ファイルタブからオプションを開き、詳細設定を選び、次のブックを計算するときの項目にある1904年から計算するにチェックを入れます。
この設定を有効にすると、負の時刻を-0:30のように表示できる場合があります。
ただし、日付の基準が変わるため、既存のブックや他ブックとの日付計算には影響が出る可能性があります。
日付を含む重要なブックで1904年日付システムへ変更する前に、コピーを保存して結果を確認しましょう。
新規の勤務時間表など、用途を限定したブックで採用するほうが管理しやすい方法です。
TEXT関数とABS関数で負の時間を文字列表示する方法
日付システムを変更しにくい場合は、負の時間を文字列として組み立てる方法があります。
開始時刻がB2、終了時刻がC2で、差をD2に表示するなら次の数式を使えます。
=IF(C2-B2<0,”-“&TEXT(ABS(C2-B2),”[h]:mm”),TEXT(C2-B2,”[h]:mm”))
IF関数が差の正負を判定し、負の場合はマイナス記号を付けます。
ABS関数は負の値を絶対値に変え、TEXT関数が時間と分の形に整えます。
[h]:mmという表示指定は、24時間を超える時間も累計時間として表示したいときに便利です。
この式の結果は時刻ではなく文字列になるため、さらに時間計算へ使う列とは分けて管理するのが基本です。
計算用には数値の差を別セルに保持し、報告用の列だけに文字列表示の式を入れると安全です。
【操作のポイント】負の時間を見せるだけならTEXT関数の方法が便利です。負の時間を再計算に使うなら、日付システムの影響も含めて設計を確認します。
時間差と日またぎ時刻を計算する数式
| A | B | C | D |
|---|---|---|---|
| 1 | 開始 | 終了 | 経過時間 |
| 2 | 22:00 | 02:30 | 4:30 |
続いては、日付をまたぐ時刻と負の時間を区別して計算する方法を確認していきます。
終了時刻が開始時刻より小さいからといって、必ず負の時間とは限りません。
日またぎの経過時間を求めるMOD関数
夜22時から翌朝2時30分までの勤務時間は、単純な=C2-B2では負の値になります。
これは遅れではなく翌日にまたがる4時間30分なので、MOD関数で1日単位の余りを求める式が有効です。
=MOD(C2-B2,1)
1はExcelでの1日分を意味します。
差が負の場合でも1日を加えた形で処理されるため、22:00から02:30なら4:30と表示されます。
日またぎの経過時間にはMOD関数、予定より早いまたは遅い差の表示には負の時間対策を使い分けます。
結果セルの表示形式は[h]:mmまたはh:mmに設定します。
休憩時間を引く場合は、=MOD(C2-B2,1)-D2のように休憩時間セルを差し引きます。
予定との差を負の時間として出す方法
実績時間と予定時間の差を出す場合は、日またぎの経過時間と、予定との差を別の列に置くと理解しやすくなります。
たとえばD2に実績時間、E2に予定時間があるなら、差は=D2-E2で計算できます。
実績が予定より30分少なければ、内部値は-0:30です。
表示だけを整えたい場合は、前述のIF、ABS、TEXT関数を使って文字列に変換できます。
=IF(D2-E2<0,”-“&TEXT(ABS(D2-E2),”[h]:mm”),TEXT(D2-E2,”[h]:mm”))
予定との差を負の時間で表す列は、プラスが超過、マイナスが不足など、意味を見出しで明示すると誤読を防げます。
表示形式だけに頼らず、列名を時間差や超過不足のように具体的にしておくと、引き継ぎ時にも役立ちます。
時刻ではなく分数で計算する方法
勤怠や作業時間を集計するときは、時間を分に換算して計算する方法もあります。
時刻の差に1440を掛けると、1日が1440分であることから分数に変換できます。
=(C2-B2)*1440
日またぎの場合は、=MOD(C2-B2,1)*1440とします。
分数なら負の値も通常の数値として扱えるため、-30のように問題なく表示、合計、平均ができます。
負の時間を大量に集計する表では、内部計算を分数に統一し、最後に時間と分へ整形する設計も有効です。
分を時間表示に戻す場合は、60で割った値を使うか、表示専用の文字列を作成します。
【操作のポイント】終了時刻が翌日か、単なる予定との差なのかを先に判定します。同じ負の結果でも、使う数式は異なります。
正負の値で起こりやすいエラーと確認ポイント
| A | B | C |
|---|---|---|
| 1 | 確認項目 | 例 |
| 2 | 文字列の数値 | ‘-500 |
| 3 | 負の時刻 | ####### |
続いては、正負の数や時間計算で起こりやすい問題を確認していきます。
式を直す前に、セルの値、表示形式、日付と時刻の入力状態を順に見直すと原因を絞り込めます。
数値が文字列として扱われるケース
見た目が-500でも、セルに文字列として保存されていると計算に使えません。
先頭にアポストロフィが付いている、全角文字が混じっている、外部データから貼り付けたといった原因が考えられます。
セルを選んで数式バーを確認し、左寄せ表示や警告アイコンがないかを見ます。
VALUE関数を使うと、文字列の数値を数値へ変換できる場合があります。
=VALUE(B2)
合計が0になる、計算結果が変わらないときは、符号より先に数値として認識されているかを確認しましょう。
データを取り込む業務では、入力規則やテンプレートで形式をそろえることも大切です。
マイナス記号の入力が異なるケース
キーボード入力のマイナス記号には見た目が似た文字があり、全角の記号や長音記号が混じると数値として処理できないことがあります。
Excelのセルへ負の数を直接入力するときは、半角のハイフンを使い、続けて半角数字を入力します。
数式では、演算子としてのハイフンと、負の数を表すハイフンの両方が使われます。
たとえば=0-B2はB2を負の値に変換する式です。
コピー元の記号が原因で計算できない場合は、セルを編集してマイナス記号を手入力し直すと解決することがあります。
取り込みデータが多い場合は、置換機能で記号を統一する方法も検討できます。
表示形式と実際の値が一致しないケース
小数点以下を表示しない書式では、-0.4が0と見えることがあります。
また、負の数を括弧で表示する書式では、マイナス記号が見えないため、値が正か負かを見誤ることもあります。
判断に迷ったときはセルを選び、数式バーまたはステータスバーで実際の値を確認します。
時間の表示では、h:mmと[h]:mmの違いにも注意が必要です。
24時間を超える累計時間は[h]:mmで表示しないと、24時間単位で繰り返されてしまいます。
表の目的に合った表示形式を選び、重要な集計セルではテスト用の値を入れて表示を確かめましょう。
【操作のポイント】計算結果がおかしいと感じたら、入力値、数式、表示形式の順に確認します。見た目だけで値を判断しないことが基本です。
まとめ エクセルで正の数と負の数を計算・表示する方法
Excelでは、正の数と負の数を同じ列に入力しても、SUM関数や四則演算で問題なく計算できます。
負の数を入力する際は、半角のマイナス記号と半角数字を使い、数値として認識されているかを確認しましょう。
負の金額を見やすくするには、表示形式で赤字や括弧を設定したり、条件付き書式で自動強調したりする方法が役立ちます。
表示形式は見た目を変える機能であり、内部の計算値を変えない点を理解しておくと安心です。
負の時間で#######が表示される場合は、単純な時刻差なのか、日またぎの経過時間なのかを区別します。
日またぎならMOD関数を使い、予定との差を負の時間として見せるならTEXT関数とABS関数を使う方法が便利です。
1904年日付システムへの変更も選択肢ですが、既存の日付データに影響する可能性があるため、ブックの用途を確認してから実行しましょう。
正負の数、時間、表示形式の役割を分けて考えることで、集計表や勤怠表をより正確で見やすい形に整えられます。
