LoginSignup
2

More than 1 year has passed since last update.

posted at

updated at

Excelの書式設定でユーザー定義を極める + Power BIのカスタム書式

この記事は、「Office 365 Advent Calendar 2021」の9日目の記事です。

※参考

通貨と会計の違い

 標準の数値の表示形式を比べてみます。

標準
image.png

数値
image.png

通貨
image.png

会計
image.png

 通貨と会計では、マイナスの表示の仕方、¥マークの位置が違います。また、標準では小数点は表示されません。小数点以下の表示を増やしてやる必要があります。また、会計では、「0」は「ー」と表示されます。セルのデータ型が変わるわけではなく、表示形式が変更になるだけです。
 分類で表示されるユーザー定義の表示を見ながら、色々と試していきます。

数値のユーザー定義

 上記の表示の仕方は、セルの書式設定、表示形式で、分類でユーザー定義を選択することで見ることができます。(※Web版のExcelでは、書式設定でユーザー定義を使用することはできません)
image.png

書式 種類
標準 G/標準
数値 0_);[赤](0)
通貨 ¥#,##0_);[赤](¥#,##0)
会計 _ ¥* #,##0_ ;_ ¥* -#,##0_ ;_ ¥* "-"_ ;_ @_

 この記号がわかれば、いろいろできそうな気がしませんか?
 ユーザー定義では、いくつかの記号を使って表示形式を定義します。

0と#

 まず、0と#です。0と#は、数値を表示するための記号です。0は、値がゼロの場合は0を表示し、#は空白となります。

ユーザー定義1
0

image.png

ユーザー定義2
#

 ゼロは表示されません。
image.png

ユーザー定義3
0␣ (0の後にスペースを入力)

 数値と右側あの罫線の間にスペースを入れます。スペースを空ける方法は、ユーザー定義の中にスペースを入力する方法と、_(アンダーバー)に続いて文字を書く方法があります。文字の幅が一定でないフォントを使う場合、アンダーバーと文字を使って幅をそろえるます。
image.png

ユーザー定義4
0"円"

 データ中に単位を表示させたい場合は、直接書き込まずに、表示形式で対応すると、数値として計算可能になります。
image.png

ゼロフィル

 ゼロを重ねることで、ゼロフィルができます。

ユーザー定義5
00

image.png

ユーザー定義6
0000

 頭がゼロで始まる可能性のある、桁数が一定の数値を入力・表示させたい場合、表示形式で指定してやれば、文字列と数値が混在する問題から解放さえれます。
image.png

小数点以下の表示

小数点以下の表示を指定できます。

ユーザー定義7
0.00

 小数点以下2桁が表示されます。
image.png

ユーザー定義8
#.00

 整数部が0の場合は、表示されません。
image.png

ユーザー定義9
0.??

 **0.##**だと、小数点以下の桁数が不ぞろいになりますが、?は、小数点の位置を揃えます。
image.png

3桁区切り

ユーザー定義10(2行目は省略形)
#,##0
#,0

image.png

ユーザー定義11(2行目は省略形)
#,###
#,#

image.png

 書式としては、千の単位でカンマがついてますが、これできちんと100万の単位でもカンマが表示されます。

千、百万の単位で表示する

 ,を最後につけることで、単位を千にすることができます。,,と2つつなげれば、百万の単位になります。

ユーザー定義12
0,

image.png

 千以下の値を、小数点で表示できます。

ユーザー定義13(2行目は省略形)
#,##0.00,
#,0.00,

image.png

色を付けて表示

 ユーザー定義で色を指定することができます。

書式記号
[黒]
[青]
[水]
[緑]
[紫]
[赤]
[白]
[黄]

 色は、番号で指定することもできます。
image.png
(※よねさんのExcelとWordの使い方

;で区切ると正、負、ゼロの条件で変更できる

 ユーザー定義を;で区切って、正、負、ゼロなどの表示形式を指定することができます。

文法
<POSITIVE>;<NEGATIVE>;<ZERO>;<TEXT>
ユーザー定義14
[黒]0.0;[赤]-0.0;[青]0.0

image.png

ユーザー定義15
[黒]#,##0.00"円";[赤]△ #,##0.00"円";"-"_円;[青]@

 _は、後に続く文字と同じ幅のスペースが入ります。@は、セルの文字列です。
image.png

 マイナスを示す△を、セルの左側に寄せます。

ユーザー定義16
[黒]#,##0.00"円";[赤]␣△*␣#,##0.00"円";"-"_円;[青]@

image.png

 ここでも␣は、スペースを表します。*マークは、次に続く文字で間を埋める役割をします。ここでは、△と数字の間をスペースで埋めています。

 この書式を使って、以下のように、参加・不参加、性別を数値で入力する表を作成することができます。

ユーザー定義17,18
"参加";"不参加";"未回答"
"男";"不明";"女"

image.png

;を使って条件式書式を設定する

文法1
[条件1]書式1;[条件2]書式2;書式3

条件1に該当する場合は書式1、条件1に該当せず条件2に該当する場合は書式2、いずれの条件にも当てはまらない場合は書式3が適用されます。条件は2つまでしか指定できません。条件が1つの場合は、以下のようになります。

文法2
[条件1]書式1;書式2

 書式3を省略した場合は、以下のように設定されます。

文法3
[条件1]書式1;G/標準

 条件式では、< ,> , =, >=,<= , <>の記号が使用できます。
 以下、使用例です。

ユーザー定義19
[>=1000]0.00,"千円";[<=-1000]-0.00,"千円";0.00_千"円"

image.png

 これを使って、先のユーザー定義18で作った性別の入力を、0=不明、1=女性、2=男性というルールで表示することもできます。

ユーザー定義20
[=2]"男";[=1]"女";"不明"

日時の書式

年の書式

image.png

月の書式

image.png

日の書式

image.png

曜日の書式

image.png

時刻の書式

image.png

文字の書式

 文字は、@を使います。

ユーザー定義21
@" 様"

image.png

分数

image.png

その他

 エクセルの表示形式で、その他に分類されているものを見てみましょう。
image.png
image.png
 漢数字について、中国簡体字と韓国のロケールにすると、以下のようになります。
image.png
 電話番号で携帯電話の場合は、カッコ内に3桁表示で、頭がゼロになるので、以下のようにします。ただし、一般電話の市外局番が2桁か3桁かの判断を入れることはできません。

ユーザー定義22
[<=99999999]####-####;[<=999999999](00) ####-####;(000) ####-####

image.png

Power BIのカスタム書式

数値

(※「Power BI Desktop でカスタム書式設定文字列を使用する」)

 Power BIで書式設定にカスタム書式を入力することができます。
image.png
 ?と[]、*の文字が使えないようで、小数点をそろえる?と色の指定や分数が使用できませんでした。
image.png

 正負による条件書式については、以下のように使用可能です。

ユーザー定義23
"▲ "#,##0"円";"▼ "#,##0"円";"-"

image.png

 ただし、[]で条件を指定した条件書式は使用できませんでした。

ユーザー定義24
[>10]"●"#,##0;#,##0

image.png

日付

 日付の書式は、Excelと異なりますので注意が必要です。和暦は、gggは使えず、gggが使えます。eeeは使えます。月のmMと大文字にする必要があります。曜日は、dddが月、火、水と表示され、ddddは、月曜日、火曜日、水曜日になります。カスタム書式に入力すると、eやMの前に\が勝手に入ったり、例の表示でMのままになったりしますが、レポートでは反映しています。うまく表示されないときは、カスタム書式を全部消して、再度入れなおすとうまくいきます。

image.png
image.png

 ただし、月のM、和暦の年号と年、時間のhが、一度はきちんと表示されても、いつの間にか表示がおかしくなってしまうことがあります。この場合も、書式を一度消して、入れなおしてやれば元に戻ります。

Power BI DAX式 FORMAT

数値

 DAX式を使って書式設定することができます。使えるものは、Power BIのカスタム書式で使えるものと同様です。
image.png
 数字以外の文字が入っていると文字列項目となりますので、左寄せで表示されます。これをビジュアルの書式設定を使って、配置を変更します。
image.png
 オプションで「新しい書式設定ウィンドウ」にチェックを入れている場合は、以下のようになります。
image.png
image.png
image.png

日付

 カスタム書式よりも使える種類が増えます。gggが使えます。曜日はdddddddで英字で表示されますし、aaaaaaaは日本語表示ができます。日本語表示を使う場合は、ロケールに***"ja-JP"***を付けておきます。
image.png

Power Queryの Number.ToTextとDate.ToText

 Power Queryでは、Number.ToTextとDate.ToTextを使って変換します。

Number.ToText([Profit],"△ #,0.##;▼ #,0.##;'-'", "ja-JP")
Date.ToText([Date],"g ee/MM/dd ddd", "ja-JP")

 結果は、この通り、和暦はうまく動きませんね。
image.png

【おまけ】Excelのデータ型

 Excelのセルで指定できるデータ型は、

データ型 Type値
数値 1
文字列 2
論理値 4
エラー値 16
配列 64
複合データ 128

 となっています。
 一方、VBAで使用できるデータ型は、

データ型 VBAでの表記 値の範囲
文字列型 String 文字列
ブール型 Boolean True または False
バイト型 Byte 8bit符号なし整数。0~255
整数型 Integer 16bit符号付き整数。-32,768~32767
長整数型 Long 32bit符号付き整数。-2,147,483,648~2,147,483,647
単精度浮動小数点型 Single 32bit符号付き単精度浮動小数点数
倍精度浮動小数点型 Double 64bit符号付き単精度浮動小数点数
通貨型 Currency 整数部15桁、小数部4桁の固定小数点型数
日付型 Date 西暦100年1月1日から西暦9999年12月31日 (日付と時刻)
オブジェクト型 Object オブジェクト参照するデータ型
バリアント型 String あらゆる種類の値を保存できる型
ユーザー定義型 String ユーザー定義の型

 などがあります。
 小数点を含む数値の計算で発生する誤差は、無限小数の問題によるものなどがあります。例えば、8.2-7.2は、1になるように見えますが、小数点以下の桁を増やしてみてみると、以下のように表示されます。

image.png

VBAで通常の計算を行うと、倍精度浮動小数点になり、以下の結果が表示されます。

倍精度浮動小数点型
Sub test1()
    x = 8.2
    y = 7.2
    MsgBox (x - y)
End Sub

image.png

 型を通貨型に指定して計算すると以下のようになります。

通貨型
Sub test2()
    x = 8.2@
    y = 7.2@
    MsgBox (x - y)
End Sub

image.png

 かように、数値の型は悩ましいものです。そんな話は、またいずれするかも、しないかも。

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
What you can do with signing up
2