PR
私が使っているPCガジェット類
作業環境で実際に使っている・気になっている周辺機器などをまとめました
※ 一部のリンクは広告(アフィリエイト)を含みます
Excelのセルの書式設定 表示形式とユーザー定義の書式コード
Excelのセルの書式設定って、分類の中から選んでいるだけになっていませんか？
分類の最後にあるユーザー定義まで踏み込むと、できることが増えます
マイナスだけ赤くする・ゼロを消す・単位を付ける、このあたりは書式コードを1行書けば済みます
増えて重くなりがちな条件付き書式に頼らずに済む場面もあるんじゃないでしょうか？
ジャベ雄表示形式の有能、意外と知られていません
目次
セルの書式設定は Ctrl + 1 で開く
Ctrl + 1 は、Microsoftのキーボードショートカット一覧に「[セルの書式設定] ダイアログ ボックスを開く」として載っている公式のショートカットです
- Ctrl + 1 を押す(最短)
- セルを右クリックして[セルの書式設定]を選ぶ
- [ホーム]タブの[数値][配置][フォント]の3グループにある、右下の小さな矢印を押す
3つ目の小さな矢印は、押したグループのタブが開いた状態で出てきます
数値グループなら表示形式タブ、フォントグループならフォントタブ
開くと6つのタブが並んでいます
それぞれの持ち場は、だいたいこの表のとおり
スクロールできます
| タブ | 受け持つもの |
|---|---|
| 表示形式 | 値の見せ方(数値・日付・文字列をどう表示するか) |
| 配置 | 文字の位置・折り返し・角度・セルの結合 |
| フォント | 書体・サイズ・色・下線・文字飾り |
| 罫線 | 線の種類と色、引く場所 |
| 塗りつぶし | セルの背景色とパターン(網かけ) |
| 保護 | ロックと数式の非表示(シート保護とセットで働く) |
Windows版のExcel(Microsoft 365 / 2021 / 2024)の画面で書いています
Mac版は開くキーが ⌘ + 1 になり、Web版はそもそも画面の作りが違うので、そのあたりは読み替えてください
ダイアログを開かずに表示形式を変えるショートカット
よく使う表示形式なら、ダイアログを開かずに当てられるショートカットとかも同じページに載っています
スクロールできます
| キー | 当たる書式 |
|---|---|
| Ctrl + Shift + チルダ(~) | 標準 |
| Ctrl + Shift + ドル記号($) | 通貨(小数2桁・負数は赤字でかっこ囲み) |
| Ctrl + Shift + パーセント(%) | パーセンテージ(小数なし) |
| Ctrl + Shift + キャレット(^) | 指数 |
| Ctrl + Shift + シャープ(#) | 日付(日/月/年) |
| Ctrl + Shift + アットマーク(@) | 時刻(時と分、AM/PM) |
| Ctrl + Shift + アンパサンド(&) | 外枠罫線を付ける |
| Ctrl + Shift + アンダースコア(_) | 外枠罫線を消す |
でも、この一覧は英語配列のキーボードを前提にした書き方
日本語配列だと ^ や @ はシフトを使わない位置にあるので、Shift を足すと別の記号に化けます
反応しないキーがあったら、やっぱり Ctrl + 1 からダイアログに入ってください
おもしろいのが通貨で、一覧の説明は「小数点以下の桁数が 2 で、負数を赤字にしてかっこで囲む通貨表示形式を設定する」
ただ通貨にするだけのショートカットではなかったんです
ジャベ雄私の左手は、もう Ctrl + 1 しか覚えていません
表示形式 値は変えずに見た目だけ変える
このタブをどれだけいじっても、計算に使われる値のほうは動きません
触れるのはセルの中身ではなく、その見せ方だけ
分類は12個、上から順にこの並びです
スクロールできます
| 分類 | 何をするもの |
|---|---|
| 標準 | 数値を打ったときに当たる既定の表示形式 |
| 数値 | 小数点以下の桁数・桁区切り・負数の見せ方を選ぶ |
| 通貨 | 数値に通貨記号を付ける、負数の見せ方も選べる |
| 会計 | 同じ金額用だが、列の中で通貨記号と小数点の位置を揃える |
| 日付 | 日付として表示する(カレンダーの種類も選べる) |
| 時刻 | 時刻として表示する |
| パーセンテージ | 値を100倍して % を付ける |
| 分数 | 分数として表示する(分母の型を選ぶ) |
| 指数 | E+n の形の指数表記にする |
| 文字列 | 中身を文字として扱い、打ったとおりに表示する |
| その他 | 郵便番号・電話番号・漢数字など(日本語版の中身) |
| ユーザー定義 | 既存の書式コードを写して自分で書き換える |
通貨と会計はどこが違う？
金額を扱うたびに、この2つのどちらを選ぶか迷いませんか？
公式の説明では、通貨が「既定の通貨記号を数値に付けて表示する」もので、会計は列の中で通貨記号と小数点の位置を揃えるもの
通貨は記号が数字にくっついて動き、会計は記号が左端で縦にそろいます
帳票で桁を目で追うなら会計、単価を1つ出すだけなら通貨、という分け方でざっくり足りるはず
Microsoftは通貨の書式設定のページで、会計表示形式では負の数をかっこで表示するのが一般的なので[負の数の表示形式]ボックスは使用できない、と書いています
会計を選んだ瞬間に選択肢が減るのは、仕様どおりの動きなんです
表示形式で丸めても、値は丸まらない
ここがいちばん誤解されやすいところ
小数点以下2桁の表示にしたセルに 1.2345 と入れると、画面には 1.23 と出ます
でもMicrosoftの解説には、そのセルを計算に使う場合は小数点の右側にある4桁の完全な数字が使われる、とはっきり書かれています
表示だけ整えた金額を足し上げると、画面の数字を電卓で足した結果と合計が1円ずれることがあります
値そのものを丸めたいなら ROUND / ROUNDDOWN / ROUNDUP の出番で、表示形式は関係ありません
この差がそのまま落とし穴になるのが消費税の端数処理
税率ごとに1回だけ丸める、という決まりに合わせるには値のほうを動かすことになるので、そちらは別記事にまとめました
あわせて読みたい
エクセルで消費税の端数処理 税率ごとに1回だけ丸める
エクセルで消費税の端数処理をするとき、明細行ごとにROUNDDOWNを入れて足すと税額がずれます 国税庁のQ&Aは税率ごとに1回と書いているので、税率の列を持たせてSUMIFSで集計してから1回だけ丸める形にします 軽減税率8%が混じるときの数式も載せました
ついでに書いておくと、ブックをテキスト形式(txt / csv)で保存すると、公式の言い方で「すべての書式が削除されます」
そのとき書き出される値は画面の見た目のままで、実際の値とは違う可能性があります
ジャベ雄見えている数字と中身は別物、ここだけ覚えて帰ってください
ユーザー定義 書式コードが読めれば分類は要らない
分類の最後にある[ユーザー定義]は、上に並んだ11個を書式コードという文字列で自分で組み立てる場所です
通貨も日付もパーセンテージも、中身はぜんぶこのコードなんです
なので、ほかの分類を選んでから[ユーザー定義]に切り替えると、選んだ書式のコードが[種類]ボックスに入った状態で出てきます
それを書き換えていくのが実際の作り方
セミコロンの数で、当たる先が変わる
書式コードはセミコロンで区切った最大4セクションで、順番は決まっています
正の数 ; 負の数 ; ゼロ ; 文字列やっかいなのは、書いたセクションの数で適用先が変わるところ
4つ書かないと後ろから欠けるのではなく、割り当てそのものが組み替わります
スクロールできます
| 書いた数 | どう割り当てられるか |
|---|---|
| 1つ | すべての数値に使われる |
| 2つ | 1つ目が正の数とゼロ、2つ目が負の数 |
| 3つ | 正の数 / 負の数 / ゼロ(文字列は素のまま) |
| 4つ | 正の数 / 負の数 / ゼロ / 文字列 |
1つと2つのときの動きは公式のガイドラインに明記されています
3つのときだけは直接の説明が見当たらなかったので、上の順番の規定から読んだ形
引っかかるとしたら、2つで止めたときにゼロが正の数側へ巻き込まれるところでしょう
ゼロを消したいのに2セクションで書いていると、いつまでも 0 が消えません
飛ばしたいセクションがあるときは、中身を空にしてセミコロンだけ残します
Microsoft のヘルプも「コードが存在しないすべてのセクションにセミコロンを含める必要があります」と書いています
#,##0;[赤]"▲"#,##0;;@これで、正の数は桁区切りつき・負の数は赤い▲つき・ゼロは非表示・文字列はそのまま、という4通りが1つのセルの属性で決まります
この「中身を書かなければ出ない」性質は公式にも例があって、0.00;; と書けば負の値とゼロが表示されなくなる、と説明されています
ゼロを消す書き方は、私は見積書でよく使っています
明細の空行に 0 が並ばなくなるので、見積書に使う関数と機能をまとめた記事のほうでも同じ形のコードを当てているんです
書式記号の早見表
セクションの中に書く記号は、覚えるものが意外と少ないです
公式ドキュメントの説明を、実際に使う順に並べ替えたのがこの表
スクロールできます
| 記号 | 働き | 例 |
|---|---|---|
| 0 | 桁のプレースホルダー、桁が足りなければ 0 で埋める | 0.00 なら 8.9 が 8.90 |
| # | 同じく桁のプレースホルダー、足りない桁に 0 を出さない | #,##0 の桁区切り |
| ? | 0 の代わりに空白を置き、列の中で小数点を揃える | 0.0? で 8.9 と 88.99 が揃う |
| . | 小数点 | 0.00 |
| , | 桁区切り、プレースホルダーの後ろに置くと1,000単位に縮める | #,##0 / #,##0, |
| % | 値を100倍して % を付ける | 0% なら 0.25 が 25% |
| @ | 文字列のプレースホルダー、入力した文字がここに入る | @”様” |
| _(下線) | 次の文字ぶんの幅だけ空ける、正負の桁位置を揃えるのに使う | _) |
| * | 次の文字を列幅いっぱいまで繰り返す | 0*x なら 3 が 3xxxxxx |
| “文字列” | 引用符の中をそのまま表示する | 0.00″ 円” |
| \ | 直後の1文字をそのまま表示する(記号自体は出ない) | \# なら # の字が出る |
| E+ / E- | 指数表記にする | 0.00E+00 |
0 と # の違いだけ、最初に押さえておくと迷いません
0 は空いた桁をゼロで埋め、# は埋めない、それだけ
$ や + はそのまま書けば表示されますが、# や 0 のように書式コードの中で意味を持つ文字を字として出したいときは、二重引用符で囲むか直前にバックスラッシュを置きます
ここは日本語環境だとちょっとややこしくて、Microsoftのページも英語版が backslash・日本語版が円記号と割れています
キーボードの ¥ キーで打つ文字だと思って読むと通りますが、うまく出ないときは二重引用符で囲む書き方に逃げるのが早いと思います
色名で指定できるのは8色
公式ページに色名として載っているのは黒 / 青 / 水 / 緑 / 紫 / 赤 / 白 / 黄 の8つです
このほかに [色1] から [色56] までの番号でパレットの色を指定する書き方もありますが、フォントタブのように好きな色を選べるわけではありません
細かい色分けをしたいなら、やっぱり条件付き書式の出番
[色1]〜[色56] の色見本
見本は既定のパレットの色です
公式は「色コードをコード セクションの最初のアイテムにする必要があります」としています
角かっこで囲んで、セクションの頭に置く形
[青]#,##0;[赤]#,##0サポートページの色名一覧は、日本語と英語がまざった状態のまま載っています(同じ8色が[Black][緑][White][Blue]…という並びで出てくる)
ただ、手元の日本語版Excelでは日本語の色名で大丈夫です
コードの中に条件が書ける
あまり知られていない機能ですが、書式コードの中には条件も書けます
角かっこの中に比較演算子と値を入れる形で、公式の言い方では「条件は、比較演算子と値で構成されます」
[>100][緑]#,##0;[<=-100][黄]#,##0;[水]#,##0Microsoftのドキュメントに出てくる例を、日本語の色名に置き換えたものです
100より大きければ緑、-100以下なら黄、それ以外は水色
色はセクションの先頭に置く決まりでしたが、条件を付けるときは条件の角かっこが先に来ます
数値に使えるセクションは3つまでなので、書けるのは実質条件2つ + 残り全部という形
ただ「2つまで」と定めた記述は公式ドキュメントに見当たらなかったので、上限として覚えるのではなくセクション数の結果として理解しておくほうが安全かもしれません
日付と時刻のコード
日付と時刻は専用の記号を使います
数を増やすほど表示が長くなる、という覚え方でだいたい通るはず
スクロールできます
| コード | 出るもの |
|---|---|
| yy / yyyy | 年を2桁 / 4桁で |
| m / mm | 月、mm は1桁の月に先頭の 0 を付ける |
| mmm / mmmm | 月の英語省略形(Jan〜Dec)/ 英語のフル(January〜December) |
| d / dd | 日、dd は1桁の日に先頭の 0 を付ける |
| ddd / dddd | 曜日の英語省略形(Sun〜Sat)/ 英語のフル |
| h / hh | 時、hh は先頭に 0 を付ける |
| m / mm(h の直後) | 分 |
| s / ss | 秒 |
| AM/PM | 12時間表示にする |
| [h] / [m] / [s] | 24時間・60分・60秒を超える経過時間 |
| aaa / aaaa | 曜日も1文字 or 〇曜日で表示 / 木 o 木曜日 |
この表で引っかかるのは、m が月と分の両方に使われているところじゃないでしょうか？
公式にも、m または mm は h か hh の直後に置く必要があり、そうしないと分ではなく月が表示される、と書かれています
時刻の書式で分が月になって出てきたら、ほぼこれが原因だったりします
h を抜いて mm だけ書いた、というやらかしが多いです
32時間30分が 8:30 と表示されるとき
1週間ぶんの勤務時間を足し上げて、合計が 32:30 になるはずのセルに 8:30 と出る現象
これは h が24時間でひと回りして、余った8時間30分だけを見せているからです
角かっこで囲んだ [h]:mm にすると、32:30 とそのまま出てくれます
公式のTEXT関数のページにも、h を角かっこで囲むと24時間ごとにリセットしなくなる、と説明があります
分だけの合計なら [m]、秒なら [s]
和暦と「元年」
和暦を出す ggge という書き方は、元号の切り替えについての公式ページに実例として出てきます
そこで案内されているコードが、条件つき書式コードの見本としてよくできていたんです
[<=43585][$-ja-JP]ggge"年"m"月"d"日";[>=43831]ggge"年"m"月"d"日";ggg"元年"m"月"d"日"やっているのは日付の振り分けなんです
1つ目の [<=43585] が平成の最終日まで、2つ目の [>=43831] が2020年1月1日以降、そのどちらにも当てはまらない2019年5月〜12月だけが3つ目に落ちて「元年」と表示されます
条件2つ + それ以外の3セクション構成、$ で始まるロケール指定、”年” のような文字列リテラル
ここまでの話が1行に全部入っているので、書式コードの読み方の練習にちょうどいいと思います
ちなみに元年表示は、Excel自体が対応しています
Ctrl + 1 →[日付]→[カレンダーの種類]で和暦を選ぶと「1年を元年と表示する」というチェックが出てくるので、コードを書かずに済む道も
ジャベ雄公式に載っていない記号でも、日本語版だと普通に使われてたりします
まとめ
持ち帰ってもらいたいのは3つだけ
- Ctrl + 1 で開く表示形式タブが変えるのは見た目だけで、値も計算結果も動かない
- 書式コードは「正の数 ; 負の数 ; ゼロ ; 文字列」、飛ばすときもセミコロンは残す
- 色は8つの色名から選べて、コードの中に条件も書ける
あとは空いたセルに適当な数字を入れて、[種類]ボックスをちょっといじってみるのが早いです
ジャベ雄まずは #,##0 と ;; の2つだけ、手に馴染ませてください
最後に・・・
PythonとExcelを中心に仕事に役立つ業務ツールや自動化、スクレイピングツールの作成を受注していて、クラウドワークスでは気が付けば100件以上のお仕事を受注してきました！
会社員をやりながらの副業なので時間の捻出は相応ですが、クライアントの方々と近い立場でこちらからも提案しながら活動していますのでお悩みあれば是非ご相談ください
コメント