Excelのマイナスを赤・▲で表示|反映されないときの直し方

スポンサーリンク

「マイナスは▲で、赤字は赤で」と言われてExcelの表示形式を変えたのに、思った見た目にならない。そんな状態で手が止まっていませんか。

  • ▲の前に-が付いて「-▲1,500」になる
  • 値がゼロのセルまで「▲0」と表示される
  • 書式コードを入れたのに、色が付かない

▲を手入力で済ませると、今度はSUMの合計が合わなくなります。損益表や予算実績表の数字がずれたまま回覧されると、確認のやり直しで時間を取られがち。

解決のカギは、値は数値のまま「見た目だけ」を変えるセルの表示形式です。; で区切る4つの枠のルールを押さえれば、▲も赤もカッコも1行の書式コードで作れます。

まずはコピペで使える早見表から。1500・-1500・0の3つの値を入れて、表示がこのとおりになるか確かめてみてください。

用途書式コード1500-15000
マイナスを赤(-付き)#,##0;[赤]-#,##01,500-1,500(赤)0
▲+赤#,##0;[赤]"▲"#,##01,500▲1,500(赤)0
▲だけ(色なし)#,##0;"▲"#,##01,500▲1,5000
△#,##0;"△"#,##01,500△1,5000
カッコ+赤#,##0;赤1,500(1,500)(赤)0
円単位+▲赤#,##0"円";[赤]"▲"#,##0"円"1,500円▲1,500円(赤)0円
ゼロを「-」にする#,##0;[赤]"▲"#,##0;"-"1,500▲1,500(赤)–
千円単位+▲赤#,##0,;[赤]"▲"#,##0,2▲2(赤)0

色の指定は、日本語版の書き方 [赤] で載せています。英語名の [Red] でも入力できる場合がありますが、環境によって扱いが違う可能性もあります。

千円単位の行は、1,000で割った値を四捨五入して見せる書式。そのため1500は「2」、-1500は「▲2」と表示されます。

対応バージョン: Windows のデスクトップ版 Excel(Microsoft 365 など)を基本に説明しています。Microsoft の案内では、Excel for the web ではユーザー定義の表示形式(カスタム書式)を新しく作れません。Web 版を使っている場合は、「Excel で開く」からデスクトップ版でブックを開き、書式を作ってください。ダイアログの項目名や「負の数の表示形式」の一覧の中身は、版や言語によって異なる場合があります。

まず「負の数」の既定の選択肢で済むか確かめる

マイナスを赤くするだけなら、ユーザー定義を書かずに済むこともあります。最初に、既定の選択肢で足りるかどうかを決めてしまいましょう。

30秒で決める判断フロー(既定で足りる/ユーザー定義が要る)

やりたいことを左の列から探し、右の2列で判断します。

やりたいこと既定の選択肢で足りるかユーザー定義
マイナスを赤にする「数値」の負の数の一覧に、目的の赤の形があれば選ぶだけ一覧の形で足りれば不要
マイナスを▲・△にする(赤との組み合わせを含む)版によっては一覧に出る場合もある一覧になければ必要
後ろに「円」を付ける・千円単位にする一覧にない必要
ゼロを「-」にする一覧にない必要

負の数の選択肢があるのは、分類の「数値」と「通貨」です。「会計」には負の数の選択肢がなく、桁数と通貨記号を選ぶだけになっています。

▲・△を社内ルールどおりに出したいなら、ユーザー定義で書いてしまうのが確実。一覧の中身が版で変わっても、書式コードなら同じ見た目を再現できます。

Ctrl+1 からユーザー定義を入れる最短手順

早見表のコードを貼り付ける手順です。1ステップずつ進めてください。

  1. 表示を変えたいセル範囲を選ぶ
  2. Ctrl+1 を押して、セルの書式設定を開く
  3. 「表示形式」タブを選ぶ
  4. 左の分類から「ユーザー定義」を選ぶ
  5. 「種類」の入力欄に入っている文字を消す
  6. 早見表の書式コードを貼り付ける
  7. OK を押して閉じる
  8. 空いているセルに 1500・-1500・0 を入力し、同じ書式を当てて表示を確かめる

既定の選択肢で済ませる場合は、手順4で「数値」を選びます。表示される負の数の一覧から目的の形を選び、OK を押せば完了です。

手順8の3値チェックは省かないでください。ゼロや負の数の枠の書き忘れは、3つの値を並べたときに初めて見つかります。

ユーザー定義の書き方|正;負;ゼロ;文字列の4つの枠

ユーザー定義の書式コードは、セミコロン ; で最大4つの枠に区切れます。並び順は決まっていて、次のとおりです。

正の数の書式;負の数の書式;ゼロの書式;文字列の書式

早見表の #,##0;[赤]"▲"#,##0;"-" なら、1つ目が正の数、2つ目が負の数、3つ目がゼロの枠。文字列の枠は書いていない状態です。

枠のルールで押さえたいのは次の4つ。

  • 書いた枠の数によって、省略した値がどの枠を使うかが変わる
  • 途中の枠を省略して後ろを書くときは、省略した枠の分も ; を書く
  • 枠に何も書かないと、その種類の値は表示されない(0.00;; なら負の数とゼロが消える)
  • 文字列の枠は必ず最後に置き、@ を入れないと入力した文字が表示されない

枠が1〜4つのとき、省略された値はどの枠を使うか(1枚の表)

同じ1500・-1500・0に、文字の「未定」を加えた4つの値で並べました。

枠の数書式コードの例1500-15000未定
1つ#,##01,500-1,5000未定
2つ#,##0;"▲"#,##01,500▲1,5000未定
3つ#,##0;"▲"#,##0;"-"1,500▲1,500–未定
4つ#,##0;"▲"#,##0;"-";"※"@1,500▲1,500–※未定

枠が1つなら、正・負・ゼロのすべてにその書式が使われます。2つのときは、1つ目の枠が正の数とゼロ、2つ目が負の数の担当です。

文字列の枠を書かない1〜3つの行では、「未定」には書式がかからず、入力したままの表示になります。4つ目の枠を "※" だけにして @ を省くと、入力した「未定」は表示されません。文字列の枠を書くときは @ を忘れないようにしましょう。

3値で表示を確かめると、もう1つ大事な違いが見えてきます。枠が1つの #,##0 では、-1500 に「-」が付いて「-1,500」と表示されます。

一方、負の数の枠を自分で書いた2つ以上の行では、-1500 の表示に「-」が付いていません。表示されるのは、負の枠に書いた記号と数字だけです。

つまり #,##0;#,##0 のように負の枠に記号を何も書かないと、-1500 が「1,500」と見えてしまいます。プラスと見分けが付かなくなるので、負の枠には ▲・△・-・カッコのどれかを必ず入れておきましょう。

▲+赤・△・カッコ+赤・円単位・千円単位・ゼロを「-」にする組み立て方

早見表のコードは、どれも次の部品の組み合わせです。

部品書き方置く場所
色[赤]枠の先頭
記号"▲" "△" ( )数字の前後
数字と桁区切り#,##0枠の中心
単位の文字"円"数字の後ろ
千円単位#,##0,(末尾のカンマ)数字の部分

色は、角かっこで囲んだ色名を枠の先頭に置くのがルールです。使える色は黒・緑・白・青・マゼンタ・黄・シアン・赤の8種類あります。

▲・△は、引用符なしで表示できる記号の一覧に入っていません。半角の " で囲んで "▲" と書くのが基本です。1文字だけなら、前に を付けて ▲ と書く方法もあります。

- や ( ) は、引用符なしで表示できる記号の一覧に入っています。カッコ+赤の 赤 で " を付けていないのは、このためです。

部品を足していく順番で書くと、次のとおり。

#,##0;"▲"#,##0
#,##0;[赤]"▲"#,##0
#,##0;[赤]"▲"#,##0;"-"

1行目で▲を付け、2行目で負の枠の先頭に色を足し、3行目でゼロの枠を加えています。△にしたいときは、"▲" を "△" に置き換えるだけ。

前年比のようなパーセントも、数字の部分を差し替えれば同じ形で書けます。

0.0%;[赤]"▲"0.0%;"-"

この書式だと、0.15 は「15.0%」、-0.15 は赤の「▲15.0%」、0 は「-」と表示されます。

ユーザー定義の書式の使い道は、マイナスの表示だけではありません。電話番号の先頭の0を残す方法は、Excelで先頭の0が消える原因と解決法で解説しています。24時間を超える時間の合計なら、Excelで時間の合計が24時間を超えると0時に戻る問題の解決法が参考になります。

マイナスの▲・赤が反映されないときの症状別の直し方

書式コードを入れたのに見た目がおかしいときは、症状から原因を絞り込めます。どの症状でも、最後は1500・-1500・0の3値で確かめ直してください。

「-▲1,500」とマイナスが二重になる

▲の前に「-」が付くのは、次の2つのどちらかです。

  • ▲を枠1つだけで書いた(例: "▲"#,##0)
  • 負の枠に「-」を残したまま▲を足した(例: #,##0;[赤]-"▲"#,##0)

枠1つの "▲"#,##0 を3値で確かめると、症状がよく分かります。1500 は「▲1,500」、-1500 は「-▲1,500」、0 は「▲0」です。枠が1つだと、正もゼロも同じ書式を使ううえに、負の数には「-」が付くためです。

直し方は、正の枠と負の枠に分けて、負の枠から「-」を消すこと。

#,##0;[赤]"▲"#,##0

この形にすれば、-1500 は赤の「▲1,500」になり、1500 とゼロからは▲が消えます。

-0.4 が「▲0」と表示される(ROUND で値を丸めるか小数桁を出す)

#,##0;[赤]"▲"#,##0;"-" を当てたセルで、-0.4 が「▲0」と出ることがあります。ゼロの枠に「-」を書いたのに、なぜでしょうか?

表示形式は見た目だけを変える設定で、セルの値そのものは変わりません。小数を出さない書式で -0.4 が「0」に丸めて見えても、値は -0.4 のまま。値が負なので、負の数の枠の書式で表示されるというわけです。

千円単位の表でも同じことが起きます。#,##0,;[赤]"▲"#,##0, の列に -400 が入ると、同じ理由で「▲0」と表示されることがあります。

直し方は、見せたい形に合わせた2択。

  • 値そのものを丸める: ROUND 関数で計算結果を丸めれば、値が本当に 0 になり、ゼロの枠が使われる
  • 小数の桁を出す: #,##0.0;[赤]"▲"#,##0.0;"-" のように桁を増やし、「▲0.4」と見せる

値を丸める場合は、元の計算式を ROUND で包みます。B2 から C2 を引いた差額を円単位で丸めるなら、次の形です。

=ROUND(B2-C2,0)

千円単位の表なら、桁数の引数を -3 にして千円未満を丸めます。端数処理の関数の選び方は、Excel端数処理の関数選び|消費税・時間・金額のシーン別早見表にまとめています。

ゼロまで▲や赤になる/ゼロを「-」にしたい

ゼロのセルに▲や赤が付くなら、▲を正の枠に書いているか、枠1つで書いているかのどちらか。枠を2つに分ければ、ゼロは1つ目の正の枠を使うので、▲も赤も付きません。

ゼロを「-」や空白にしたいときは、3つ目の枠を書き足します。

  • ゼロを「-」にする: #,##0;[赤]"▲"#,##0;"-"
  • ゼロを空白にする: #,##0;[赤]"▲"#,##0;

空白にする書き方は、3つ目の枠を空にして末尾の ; だけ残す形。枠に何も書かないとその種類の値は表示されない、というルールを利用しています。

色が付かない([赤] の位置・色名・条件付き書式やフォント色の上書き)

コードに [赤] を書いたのに色が変わらないときは、次の順に確かめます。

  1. [赤] が枠の先頭にあるか。"▲"[赤]#,##0 のように記号の後ろに置いていたら、[赤]"▲"#,##0 に並べ替える
  2. [赤] を書いた枠が、色を付けたい値の枠か。負の数を赤くするなら、2つ目の枠の先頭に書く
  3. 色名の書き方。[赤] で受け付けられないときは、英語名の [Red] を試す
  4. 条件付き書式。ホームタブの「条件付き書式」から「ルールの管理」を開き、フォントの色を指定するルールがないか見る

条件付き書式やセルのフォント色も設定しているなら、注意しましょう。どの色が表示されるかは、設定の組み合わせで変わる可能性があります。表示形式の色だけで管理したいなら、同じ範囲に重ねて色を指定していないか点検しておくと安心です。

▲・△が付かない、「”」が入る・消える(半角の”で囲む)

▲・△が表示されないときは、まず " が半角かどうかを見てください。日本語入力のまま打つと全角の " になりやすく、書式の区切りとして扱われない可能性があります。

引用符なしで ▲#,##0 と入力すると、Excel が自動で " や を補った形で保存し直す場合があります。「入れていない " が勝手に入った」と見えるのは、このケースです。

いったん「種類」の入力欄を空にして、半角の " で囲んだ "▲" を入れ直すのが確実。入れ直したら、3値で表示を確かめましょう。

書式を変えても見た目が変わらない(値が文字列のまま)

正しいコードを入れても1500が1,500にならず、何も変わらない。この場合は、セルの値が数値ではなく文字列として入っている可能性があります。

表示形式の数値の書式が効くのは、中身が数値のセルだけ。CSVから貼り付けた値や、先頭にアポストロフィを付けた入力は、見た目が数字でも中身が文字列のことがあります。文字列を数値に戻す方法は、Excelの文字列数値を一括変換する4つの方法で詳しく解説しています。

ダブルクリックしてEnterを押すと反映される症状については、Excelの表示形式が変わらない原因でも扱っています。

▲を手入力したセルが合計されない

▲を数字の前に直接入力したセルは、Excel にとって文字列です。SUM は文字列を合計に含めないため、合計が合わなくなります。

予算実績表の差額の列で確かめてみましょう。B3とB5に「▲300」「▲200」を手入力した例です。

セル入力した内容中身の種類
B21200数値
B3▲300文字列
B4500数値
B5▲200文字列
=SUM(B2:B5)

この式の結果は 1,700 です。数値の 1,200 と 500 だけが足され、本来の合計 1,200(1,200-300+500-200)と500ずれています。

正しい入力は、▲を付けずに -300 や -200 と入れること。そのうえで、早見表の▲の書式を当てれば、見た目は「▲300」のまま合計も 1,200 になります。

すでに手入力の▲が並んでいる列は、隣の列に次の式を入れると数値に変換できます。

=IF(ISNUMBER(B2),B2,-SUBSTITUTE(B2,"▲",""))

B2 が数値ならそのまま返し、文字列なら▲を消してマイナスを付ける仕組み。変換後の列をコピーし、元の列に値として貼り付けます。最後に▲の書式を当てれば完了。

この式には2つの注意点があります。

  • 空白セルがあると #VALUE! になる。空白が混じる列は、先に空白でないセルだけを対象にするか、IF(B2="","",…) で包む
  • ▲が付いていない文字列の数字(例: 文字列の「300」)は、符号が逆の -300 になる。変換前に、▲付きの文字列だけが並んでいるか確かめる

表示形式で足りないとき|TEXT関数・条件付き書式に切り替える

表示形式は「セルの値を数値のまま、見た目だけ変える」機能です。できることの範囲を超えたら、別の機能に切り替えたほうが早く片付きます。

文字列にしたい・他の文字と結合したい → TEXT関数

「前年比 ▲15.0%」のように、文章の中に▲付きの数字を入れたいときは TEXT 関数の出番です。表示形式と同じ書式コードを使えます。

="前年比 "&TEXT(B2,"0.0%;""▲""0.0%")

数式の中で " を書くときは、"" と2つ重ねるのがポイント。B2 が -0.15 なら、結果は「前年比 ▲15.0%」になります。

ただし、TEXT 関数の結果は文字列です。合計や比較に使う列は表示形式、文章や見出しに埋め込むときは TEXT 関数、と使い分けましょう。書式コードの詳しい書き方はExcelのTEXT関数の使い方で解説しています。

行全体・セルの背景を塗りたい → 条件付き書式

表示形式で変えられるのは、セルの文字の見た目だけ。赤字の行を丸ごと塗る、背景をピンクにするといった表現は、条件付き書式の担当になります。

「差額がマイナスの行だけ背景を塗る」なら、条件付き書式で数式を使ったルールを作ります。設定の手順はExcelの条件付き書式完全ガイドを参考にしてください。

文字の▲と赤は表示形式、行や背景の塗り分けは条件付き書式。役割を分けておくと、色が付かない症状で迷ったときも原因を追いやすくなります。

よくある質問とまとめ

Q. 「;;;」で中身が消えるのも同じルール?

同じルールです。;;; は、正・負・ゼロの3つの枠と文字列の枠をすべて空にした書式。枠に何も書かないとその種類の値は表示されないため、どの値も画面から消えます。

数式バーには値が残っているので、データ自体は消えていません。見えないセルの探し方と直し方は、Excelでセルに入力した文字が表示されない原因と直し方で解説しています。

Q. マイナスの時間も▲で表示できる?

時間の引き算でマイナスになると、セルが「####」になる場合があります。これは表示形式の▲とは別の問題で、この記事では扱っていません。

時間のマイナスについては、Excelで日付・時間の引き算がおかしい原因と直し方にまとめています。

まとめ(3値で確認する手順の再掲)

Excelでマイナスを赤や▲で表示するときは、値は数値のまま、表示形式で見た目だけを変えるのが基本です。

  • 赤くするだけなら、まず「数値」の負の数の一覧に目的の形があるか見る
  • ▲・△・円単位・ゼロの「-」は、ユーザー定義で 正;負;ゼロ の枠に分けて書く
  • 負の枠には記号を必ず入れ、色は [赤] を枠の先頭に置く
  • ▲や△は半角の " で囲む

設定したら、最後にもう一度3値で確かめましょう。

  1. 空いている3つのセルに 1500・-1500・0 を入力する
  2. 設定したい書式を3つのセルに当てる
  3. 冒頭の早見表と見比べる

-1500 に「-」が二重に付く、0 に▲が付くといったずれがあれば、症状別の直し方の該当する見出しに戻ってください。3つの値が早見表どおりに並べば、損益表や予算実績表の数字も同じ見た目で表示されます。

タイトルとURLをコピーしました