書式記号
書式記号とは、セルの設定 > ユーザー書式やTEXT関数で使用する記号のことです。 書式記号を使用することで位の指定やExcelでよく問題になる会員のIDなどで使用される “ 001 ” のように0から始まる数字の0が消える問題などに 対応する事ができます。
書式記号には5つの種類があります。
- 数値の位取りをする書式記号
- 数値の書式を指定する書式記号
- 文字を表示する書式記号
- 日付や時刻を表示する書式記号
- 色を指定する書式記号
ここでは１から４までを説明していきます。
数値の位取りをする書式記号
3つの種類があります。0と#、?です。整数と小数は . で区切って別々に設定する事ができます。
0
指定した桁数まで 0 で補完して表示します。
#
補完されません。そのまま表示されます。
?
指定した桁数までスペースで補完して表示します。
| 入力値 | 設定記号 | 結果 |
|---|---|---|
| 1 | 000 | 001 |
| 1 | ### | 1 |
| 1 | ??? | 1 |
| 1.1 | 000.### | 001.1 |
| 1.1 | ###.000 | 1.100 |
| 1.1 | ###.??? | 1.1 |
| 1.1 | ???.000 | 1.100 |
| 1.1 | ???.### | 1.1 |
| 1.1 | 000,??? | 000, 1 |
数値の書式を指定する書式記号と文字を表示する書式記号
数値の位取りをする書式記号と組み合わせて使用します。条件分岐などもあり複雑です。 その為、表で主なものを使用した結果のみを示します。
| 入力値 | 設定記号 | 使用例 | 結果 |
|---|---|---|---|
| 1000 | . | ###.000 | 1000.000 |
| 1 | % | ##% | 100%（パーセント表示をします。） |
| 1000 | / | ###/### | 1000/1（分数表示します。） |
| 1000 | E+ | ###E+### | 1E+3（符号付指数表示します。） |
| 1000 | E- | ###E-### | 1E3（マイナスのみ符号をつけます。） |
| 1000 | , | #,# | 1,000（3桁ごとに桁を区切ります。） |
| 1000 | ! | #!k | 1000k（半角文字1文字を後ろに挿入します。） |
| 1000 | “ | “SAMPLE”# | SAMPLE1000（文字列を挿入します。） |
| 1000 | * | *A | AA（指定文字でセルが埋め尽くされます。) |
| 1000 | _ | #_ “個” | 1000 個（空欄を指定数挿入します。） |
| 1000 | @ | “ABC”@“号店” | ABC1000号店（指定位置に値を挿入します。） |
| 1000 | [条件] | [>=999]#;“-”# | 1000（” ; “で書式を区切ります。） |
| 500 | [条件] | [>=999]#;“-”# | -500 |
| 12345678 | [DBNum1] | [DBNum1] | 千二百三十四万五千六百七十八 |
| 12345678 | [DBNum2] | [DBNum2] | 壱阡弐百参拾四萬伍阡六百七拾八 |
| 12345678 | [DBNum3] | [DBNum3] | 千２百３十４万５千６百７十８ |
因みに数式の入ったセルの書式を@にすると数式が表示されます。
日付や時刻を表示する書式記号
日付や時刻を表示する書式記号も大変多い為、表で簡単な説明をしていきます。通常、これらを組み合わせて使用していきます。 区切り記号は/ : -です。日本語を使用する際はhh”時”のように “ で囲みます。
| 入力値 | 設定記号 | 使用例 | 結果 |
|---|---|---|---|
| 2018/10/1 9:03:55 | d | 日付を1桁で表示 | 1 |
| 2018/10/1 9:03:55 | dd | 日付を2桁で表示 | 01 |
| 2018/10/1 9:03:55 | ddd | 曜日を英語短縮で表示 | Mon |
| 2018/10/1 9:03:55 | dddd | 曜日を英語で表示 | Monday |
| 2018/10/1 9:03:55 | aaa | 曜日を日本語短縮で表示 | 月 |
| 2018/10/1 9:03:55 | aaaa | 曜日を日本語で表示 | 月曜日 |
| 2018/10/1 9:03:55 | m | 月を1桁で表示 | 10 |
| 2018/10/1 9:03:55 | mm | 月を2桁で表示 | 10 |
| 2018/10/1 9:03:55 | mmm | 月を英語短縮で表示 | Oct |
| 2018/10/1 9:03:55 | mmmm | 月を英語で表示 | October |
| 2018/10/1 9:03:55 | mmmmm | 月を英語頭文字で表示 | O |
| 2018/10/1 9:03:55 | yy | 年を西暦下二桁で表示 | 18 |
| 2018/10/1 9:03:55 | yyyy | 年を西暦で表示 | 2018 |
| 2018/10/1 9:03:55 | g | 年を元号アルファベットで表示 | H |
| 2018/10/1 9:03:55 | gg | 年を元号１文字で表示 | 平 |
| 2018/10/1 9:03:55 | ggg | 年を元号で表示 | 平成 |
| 2018/10/1 9:03:55 | e | 年を和暦１桁で表示 | 30 |
| 2018/10/1 9:03:55 | ee | 年を和暦２桁で表示 | 30 |
| 2018/10/1 9:03:55 | h | 時間を１桁で表示 | 9 |
| 2018/10/1 9:03:55 | hh | 時間を２桁で表示 | 09 |
| 2018/10/1 9:03:55 | m(hh:m:ss) | 分を１桁で表示（組合せ必須） | 09:3:55 |
| 2018/10/1 9:03:55 | mm(hh:mm:ss) | 分を２桁で表示（組合せ必須） | 09:03:55 |
| 2018/10/1 9:03:55 | s | 秒を１桁で表示 | 55 |
| 2018/10/1 9:03:55 | ss | 秒を２桁で表示 | 55 |
| 2018/10/1 9:03:55 | s.0 | マイクロ秒１桁まで表示 | 55.0 |
| 2018/10/1 9:03:55 | s.00 | マイクロ秒２桁まで表示 | 55.00 |
| 2018/10/1 9:03:55 | s.000 | マイクロ秒３桁まで表示 | 55.000 |
| 2018/10/1 9:03:55 | AM/PM | 午前午後表示（時刻設定後に指定） | AM |
| 2018/10/1 9:03:55 | am/pm | 午前午後表示（時刻設定後に指定） | am |
| 2018/10/1 9:03:55 | A/P | 午前午後表示（時刻設定後に指定） | A |
| 2018/10/1 9:03:55 | a/p | 午前午後表示（時刻設定後に指定） | a |
| 7:00:00 | [h] | 経過時間を時１桁で表示 | 7 |
| 7:00:00 | [hh] | 経過時間を時２桁で表示 | 07 |
| 0:5:00 | [m] | 経過時間を分１桁で表示 | 5 |
| 0:5:00 | [mm] | 経過時間を分２桁で表示 | 05 |
| 0:0:2 | [s] | 経過時間を秒１桁で表示 | 2 |
| 0:0:2 | [ss] | 経過時間を秒２桁で表示 | 02 |
TEXT関数
TEXT関数は数値を指定された書式で文字列に変換する関数です。
= TEXT(数値 , 書式記号の組み合わせで設定)
書式設定はある意味Excelの大きな特徴の一つではないでしょうか。すでに設定されているものも多いですが、 今回紹介した書式記号を使用する事で表現が大きく広がります。