1. はじめに
1-1. ユーザー定義の表示形式（以下、「ユーザ表示形式」と略）とは
[右クリック]→[セルの書式設定]→[表示形式]→[分類：ユーザー定義]の事です。
「数値」や「通貨」など定番の表示形式以外にユーザーが独自の表示形式を作ることができます。ユーザ表示形式は書式記号を組み合わせて作ります。
例えば「n人」や「n皿」など単位を自由に表示したり、ある値以下の値を[赤]色で表示したりできます。
1-2. 表示形式を使用するメリットは
- セルの入力値を計算に使用できます
セルに直接「n皿」と入力するとExcelでは文字列として扱われるため計算に使用できません。表示形式を使用した場合はセルの入力値は「n」で、それを「n皿」と表示しているだけなので計算式に使用できます。 - 1つのセルに単位も表示できます
単位を表示したいだけなら「値を入力するセル」と「単位を表示するセル」の二つを並べて用意すれば実現できます。これなら計算にも使用できます。
ただ、この辺は好みですがセルを二つで実現するとその分、幅をとってしまったり、検索にヒットしてしまったり、セルの移動にキー操作が余計に必要になったりします。 - 結構複雑なことができる
ある一定の値以下は色文字にしたり、0値を表示しなかったり’-’(ハイフン)などの代替文字で表示したり、正数と負数で表示形式を変えたり、結構複雑なことができます。これを計算式で実現しようとすると「入力するセル」と「表示するセル」の二つが必要になったりして色々と面倒です。
2. ユーザ表示形式の作成の仕方
[右クリック]→[セルの書式設定]→[表示形式]→[分類：ユーザー定義]の「種類」欄に書式記号や文字列を入力して作成します。
何もないところからいきなり作成するのはハードルが高いので、定型の表示形式（数値〜その他）から似たものを一旦選択して、その後「分類：ユーザー定義」を選択するとその表示形式が「種類」欄に表示されます。これを適宜修正して自分に欲しい表示形式を作成すると楽チンです。
以降で使用できる書式記号と、正数/負数/ゼロのそれぞれに表示形式を指定する方法、条件式を含む複雑な表示形式を説明します。
3. 使用できる書式記号
3-1. 標準の書式記号
デフォルトの書式記号です。入力された数値をそのままの書式で表示します。
3-2. 数値の書式記号
'#' １桁の数字を示します。# の数だけ桁数が指定され、その有効桁数しか表示されません。余分な0も表示されません。 '0' １桁の数字を示すが、指定したゼロの桁数だけ常にゼロが表示されます。 '?' 固定幅フォントで数値の小数点を揃えるために、整数部と小数部の余分なゼロがスペースで表示されます。
? は桁数の異なる複数の小数を揃えて表示する場合に使います。
整数の位に指定した書式記号の桁数よりも、入力した整数の位の桁数が多い場合は、すべての整数の位が表示されます。
小数の位に指定した書式記号の桁数よりも、入力した小数の位の桁数が多い場合は、書式記号で設定した桁数に合わせて小数の位の桁数が四捨五入されます。
3-3. 桁区切り記号の書式記号
数値の桁を区切るには、カンマ (,)を使用します。カンマ (,)は1000 単位の区切り記号として表示します。
#,##0.00; 000,000.00; //やろうと思えば0埋めもできる。（1000.23 → 001,000.23）
3-4. 日付の書式記号
＜年＞ yy 西暦の下 2 桁を表示します。 yyyy 西暦を 4 桁で表示します。 e 年を年号を元に表示します。 ee 年を年号を元に 2 桁の数値で表示します。 g 元号をアルファベットの頭文字 (M、T、S、H) で表示します。 gg 元号を漢字の頭文字 (明、大、昭、平) で表示します。 ggg 元号を漢字 (明治、大正、昭和、平成) で表示します。 ＜月＞ m 月を表示します。 mm 1 桁の月には 0 をつけて 2 桁で表示します。 mmm 英語の月の頭文字 3 文字 (Jan～Dec) を表示します。 mmmm 英語の月 (January～December) を表示します。 mmmmm 英語の月の頭文字 (J～D) で表示します。 ＜日＞ d 日にちを表示します。 dd 1 桁の日にちには 0 をつけて 2 桁で表示します。 ＜曜日＞ ddd 英語の曜日の頭文字から 3 文字 (Sun～Sat) を表示します。 dddd 英語の曜日 (Sunday～Saturday) を表示します。 aaa 漢字で曜日の頭文字 (日～土) を表示します。 aaaa 漢字で曜日 (日曜日～土曜日) を表示します。
3-5. 時間および時刻の書式記号
時間の書式は、24時間表示を基準にしています。 日付を表示する書式記号は以下の記号を使用します。
＜時間/分/秒＞ h 時刻 (0～23) を表示します。 hh 時刻 (00～23) を表示します。 m 分 (0～59) を表示します。 mm 分 (00～59) を表示します。 s 秒 (0～59) を表示します。 ss 秒 (00～59) を表示します。 ＜時間の経過＞ [h]:mm 24時間を超える時間の合計を表示します。 [mm]:ss 60分を超える分の合計を表示します。 [ss] 60秒を超える秒の合計を表示します。分を表す m、mm は、h、hhの後、またはs、ssの前に入力されていないと、分を表す書式記号として認識されません。これ以外の位置または単独で入力すると、分ではなく月の形式で表示されます。
3-6. 色の書式記号
色の名前 (8色) を指定して、文字色を指定することができます。 色の指定を行うには、以下の8色を角かっこ ([]) で囲んで、書式部分の先頭に入力します。
[黒] 文字色を黒色で表示します。 [青] 文字色を青色で表示します。 [水] 文字色を水色で表示します。 [緑] 文字色を緑色で表示します。 [紫] 文字色を紫色で表示します。 [赤] 文字色を赤色で表示します。 [白] 文字色を白色で表示します。 [黄] 文字色を黄色で表示します。
4. 表示形式の書式
正の数の書式;負の数の書式;ゼロの書式;文字列の書式ユーザー定義の表示形式は、一つの表示形式で 4つの書式を作成することができます。それぞれの書式は、「;」セミコロンで区切ります。左から 3つまでの数値の書式と、文字列用に 4つ目の書式を持つことができます。
2つのセクションだけを指定した場合、最初のセクションは正の数とゼロの表示形式になり、2番目のセクションは負の数の表示形式になります。
1つのセクションを指定した場合は、その表示形式がすべての数値に適用されます。
各セクションに書式記号がないと該当する数値は表示されません。例えば…
#,##0.00;-#,##0.00;;とすると、ゼロを表示しないようにできます。
5. 条件付きの書式
角括弧[ ]で、等号や不等号などの比較演算子を使用して条件を設定することができます。等号や不等号などの比較演算子で使用できる記号は以下のとおりです。
< ,> , =, >=,<= , <>
例えば10以上の数値は文字色が青色になるユーザ表示形式は以下のようになります。
[青][>=10]G/標準 [青][>=10]#,##0 ← 3桁区切りのカンマをつけた
6. その他の書式
6-1. 単位などの文字列を書式設定する
文字列の書式部分は常に表示形式の末尾に指定します。文字列の書式部分に@記号がない場合、入力した文字列は表示されません。
文字列を入力し、特定の文字列も同時に表示させる場合、表示する特定の文字列をダブル クォーテーション (") で囲み、入力する文字列を表示する部分に @ 記号を入力します。
"特定の文字列"@
| 設定された書式 | 値 | 表示 |
|---|---|---|
| “評価”@ | A | 評価A |
数値を入力し、特定の文字列も同時に表示させる場合、数値の書式の後に特定の文字列を二重引用符 ("") で囲みます。
[数値書式]"文字列"
| 設定された書式 | 値 | 表示 |
|---|---|---|
| #,##0"人" | 1000 | 1,000人 |
6-2. プラス値/マイナス値で書式文字が違う場合にスペースで幅を合わせる
特定の文字の文字幅分のスペースを空けるなど表示形式内でスペースを入力する場合は、Spaceキーによってスペース(空白) を入力する方法と、下線 (_) を入力する方法とがあります。
また、 特定の文字の文字幅分のスペースを空けるには、表示形式で下線 (_) に続けてその特定の文字を入力します。
負の数値を入力すると括弧がつく書式を設定している場合、括弧の幅の分だけ文字間隔が空くためこの書式を設定して、正の数を負の数と揃えて表示します。
_ , _特定文字列
| 設定された書式 | 値 | 表示 |
|---|---|---|
| #,##0_);(#,##0) | 100 | 100 |
| 同上 | -100 | (100) |
6-3. 記号を繰り返して埋め文字する
表示形式で指定した文字をセル幅に達するまで繰り返し表示する場合、アスタリスク (*) の後ろに記号などの文字列を入力すると、表示されているセル幅に達するまでその記号を繰り返し表示します。
*記号などの文字列
| 設定された書式 | 値 | 表示 |
|---|---|---|
| 0*! | 100 | 100!!!!!!!!!! (セル幅いっぱいに’!‘を表示) |