【EXCEL | セルの書式設定 | 表示形式】独自の表示形式を作る!

スポンサーリンク

単位や日付の表示方法などをカスタマイズする

「セルの書式設定」の「表示形式」で数字や日付の表示方法をいろんなかたちに変更できることはご存知の通りだと思います。でも中には、思ったようなものがなかったり、一般的ではない設定が必要なときも出てきますね。

そんな時に、便利な機能をご紹介します。

セルの書式設定 > 表示形式 > ユーザー定義

書式設定の表示形式の選択項目の中に、「ユーザー定義」というものがあります。これは、ユーザーが独自に表示形式を設定するための項目です。「書式記号」というものを使って設定することで、様々な形の表示形式を実現出来ます。

セルの書式設定の場所

こんな表を用意しました。

毎日の筋トレ回数を記録した表です。

まずは、A4セル~A10 セルの日付の表示形式を設定していきたいと思います。

表示形式を変更するためには、まずは表示形式を変更したいセルを選択し、「ホーム」タブの「数値」グループのなかの右下のボタンを押すか、右クリックで「セルの書式設定」のメニューをクリックします。

*ホーム → 数値 → 右下ボタン

*右クリック → セルの書式設定

「セルの書式設定」の画面、「表示形式」タブ

図で選択されている、「ユーザー定義」を使います。

右側に「サンプル」と「種類」のボックスがあると思います。種類の下にある入力可能なボックスに「書式記号」を使って形式を指定します。指定した形式は「サンプル」のボックスで確認することが出来ます。

ちなみに、大体普通使うであろう形式は、「種類」ボックスの下に選択肢が用意されているので、これで事足りる場合は、選ぶだけでOKです。

試しに、別の形式を選んでみましょう。「yyyy"年"m"月"」を選びます。

サンプルに「2020年1月」と表示されました。これを決定すると次のようになります。

サンプルと同じ形で表示されましたね。(これじゃ意味ないですが。。。)

日付の書式記号

それでは、独自の表示形式を作っていきましょう。先程から出てきている、「書式記号」というものを使います。日付の書式記号は次のように決まっています。

記号 説明
yyyy、yy 西暦の表示(yyyy:4桁 yy:2桁)
e 和暦の年表示
ggg、 gg、g 和暦の元号(ggg:令和 gg:令 g:R)
mmmm、mmm 月の英語表記(mmmm:January mmm:Jan)
mm、m 月表記(mm:2桁/01 m:1桁/1)
dd、d 日表示(dd:2桁/01 d:1桁/1)
aaaa、aaa 曜日表記(aaaa:月曜日 aaa:月)
dddd、ddd 曜日の英語表記(dddd:Monday ddd:Mon)

これを組み合わせて、表示形式を作っていきます。

それでは、次ののような日付の表示形式を作りたいと思います。

  • 2桁西暦表記 単位「年」
  • 2桁月表記 単位「月」
  • 2桁日付表記 単位「日」
  • 英語曜日表記 短縮形 ()でくくる

この条件で作ると次のようになります。

yyyy"年"mm"月"dd"日"(ddd)

これを表示形式の「種類」ボックスの中に入れて下さい。

サンプルに正しく表示されたら、OKで確定します。

指示通りにきちんと表示されました。これで完了です。

単位は、「”」で囲うんですが、「/」と「()」は囲わないです。そして、「””」忘れてても自動的にやってくれるのであんまり記にしなくてもOKでです。

こんなんでもちゃんと出ます。

数値の書式記号

次に数値の書式記号についてみていきます。数値の書式記号は次のように決まっています。

記号 説明
0 位を表す「0」の数よりも数値の桁数が少ない場合、数値に「0」を補う
# 位の表す「#」の数よりも数値の桁数が少ない場合「0」を補わずに数値をそのまま表示
? 位を表す「?」よりも数値の桁が少ない場合はスペースを補う
. 少数点
, 3桁区切りの記号、位を表す書式記号のに含めると千単位で,(カンマ)がつく
% %表示にする
/ 分数
E、e 指数

次のサンプルで試していきます。

「セルの書式設定」から「表示形式」を設定します。「ユーザー定義」まで進んで下さい。

数値の表示形式の設定も、日付と同じで「種類」のボックスに「書式記号」を入力していきます。

まずは「種類」に「0」を入れてみましょう。

サンプルに「1235」と表示されました。なぜこうなったかというと、「0」は、

位を表す「0」の数よりも数値の桁数が少ない場合、数値に「0」を補う

でしたね。「0」の数が桁数を表しています。今、「0」が一個なので、1桁です。つまり1桁よりも桁数が少ない場合、頭に「0」を付けてその桁数を表します。そんな事無いので、その件については何も起こっていません。

少数点以下が四捨五入されていますが、それは小数点以下の指示がされていないので、数字が丸められえています。

例えば、「0」を5つ並べてみましょう。

「0」が5つなので、5桁表示ですね。サンプルは、整数部分が4桁なので、1桁足りません。それを「0」を表示して表しています。

少数点以下の部分を設定するには、書式記号の「.」を入れます。そのままですね。

現在の書式記号に続けて、「.000000」とドットと「0」を6個入れてみましょう。

整数部分は、先程と同じままですね。少数部分が「0」を6個付けているので6桁です。もともとは、「.56789」と少数部分は5桁なので、一桁「0」で補って「.567890」と表示されます。

では、次に「0」を「#」に変えてみましょう。

「#」は、桁数が足りないときも「0」で補いません。ですので補われずにそのまま、「1234.56789」と表示されました。

次に、「#」を「?」に変えてみましょう。

ちょっと分かりやすいように、整数部分の「?」を10個入れました。変化が分かりにくいかもしれませんが、サンプルの1234の左側がスペースで空いています。ちょっと右になってるのが分かりますね。このように「?」は、「0」ではなく、「空白(スペース)」で補います。

次は、「,」を使ってみましょう。「,」は、

3桁区切りの記号、位を表す書式記号のに含めると千単位で,(カンマ)がつく

「位を表す書式記号のに含める」なので、入ってたらどこでもいいです。「位を表す書式記号」は「0,#,?」です。この記号のに「,」を含めると、「千単位」で「,」を付けてくれます。

※普通は分かりやすいように「###,###」とかみたいに3桁毎に付けた方が良いかなと思います。1個つけるだけで効きます。

整数部分が「1,234」とカンマ区切りになりましたね。整数部分の書式記号に付けないと聞かないので注意して下さい。

このようにユーザー設定で様々な表示形式を設定することが出来ます。

独自の単位を作る

この機能を使うことで次のような、独自の単位を作って表示させることも可能です。

合計列を作成して、SUM関数で計算しています。単位に「回パワーUP」を設定しています。

このように、表示形式で単位を設定することによって、そのセルを計算セルとしても使えるので、表の作成のアイデアの幅を広げることも出来ます。

おまけ

分数サンプル

数値 書式記号 表示内容
5.25 # ???/??? 5 1/4

【「”」で囲わなくていいいやつ】

書式記号 表示内容
$ ドル記号
+ 正符号
- 負符号
/ スラッシュ
( 左かっこ
) 右かっこ
: コロン
! 感嘆符
^ 曲折アクセント (キャレット)
& アンパサンド
' アポストロフィ
~ チルダ
{ 左中かっこ
} 右中かっこ
< 小なり記号
> 大なり記号
= 等号
 

スペース文字

【時刻の書式記号】

記号 説明
hh、h 時の表示桁数(hh:2桁 h:1桁)
mm、m 分の表示桁数(mm:2桁 m:1桁)
ss、s 秒の表示桁数(ss:2桁 s:1桁)
AM/PM 午前はAM、午後はPMで表示する
[h]、[mm]、[s] 経過時間の表示

【その他の書式記号】

記号 説明
@ 文字列
[DBNum1] 漢数字(二十四万五千二百三十一みたいなやつ)
[DBNum2] 漢数字(弐拾参萬みたいなやつ)
[DBNum3] 全角数字と漢数字の単位ミックス(2十4万5千8百十6みたいな)
[色] 色。([黒][赤][青][緑][黃][紫][水][白]の8種類)

まとめ

このように、書式記号を色々と組み合わせることによって、表示形式を独自に作ることが出来ます!