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

※参考

通貨と会計の違い

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

標準

数値

通貨

会計

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

数値のユーザー定義

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

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

０と＃

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

0

#

　ゼロは表示されません。

0␣ (0の後にスペースを入力)

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

0"円"

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

ゼロフィル

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

00

0000

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

小数点以下の表示

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

0.00

　小数点以下２桁が表示されます。

#.00

　整数部が０の場合は、表示されません。

0.??

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

３桁区切り

#,##0
#,0

#,###
#,#

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

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

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

0,

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

#,##0.00,
#,0.00,

色を付けて表示

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

　色は、番号で指定することもできます。

（※よねさんのExcelとWordの使い方）

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

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

<POSITIVE>;<NEGATIVE>;<ZERO>;<TEXT>

[黒]0.0;[赤]-0.0;[青]0.0

[黒]#,##0.00"円";[赤]△ #,##0.00"円";"-"_円;[青]@

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

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

[黒]#,##0.00"円";[赤]␣△*␣#,##0.00"円";"-"_円;[青]@

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

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

"参加";"不参加";"未回答"
"男";"不明";"女"

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

[条件１]書式１;[条件２]書式２;書式３

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

[条件１]書式１;書式２

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

[条件１]書式１;G/標準

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

[>=1000]0.00,"千円";[<=-1000]-0.00,"千円";0.00_千"円"

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

[=2]"男";[=1]"女";"不明"

日時の書式

年の書式

月の書式

日の書式

曜日の書式

時刻の書式

文字の書式

　文字は、＠を使います。

@" 様"

分数

その他

　エクセルの表示形式で、その他に分類されているものを見てみましょう。


　漢数字について、中国簡体字と韓国のロケールにすると、以下のようになります。

　電話番号で携帯電話の場合は、カッコ内に3桁表示で、頭がゼロになるので、以下のようにします。ただし、一般電話の市外局番が2桁か3桁かの判断を入れることはできません。

[<=99999999]####-####;[<=999999999](00) ####-####;(000) ####-####

Power BIのカスタム書式

数値

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

　Power BIで書式設定にカスタム書式を入力することができます。

　？と［］、＊の文字が使えないようで、小数点をそろえる？と色の指定や分数が使用できませんでした。

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

"▲ "#,##0"円";"▼ "#,##0"円";"-"

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

[>10]"●"#,##0;#,##0

日付

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

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

Power BI DAX式 FORMAT

数値

　DAX式を使って書式設定することができます。使えるものは、Power BIのカスタム書式で使えるものと同様です。

　数字以外の文字が入っていると文字列項目となりますので、左寄せで表示されます。これをビジュアルの書式設定を使って、配置を変更します。

　オプションで「新しい書式設定ウィンドウ」にチェックを入れている場合は、以下のようになります。

日付

　カスタム書式よりも使える種類が増えます。gやggが使えます。曜日はdddとddddで英字で表示されますし、aaaとaaaaは日本語表示ができます。日本語表示を使う場合は、ロケールに***"ja-JP"***を付けておきます。

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")

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

【おまけ】Excelのデータ型

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

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

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

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

Sub test1()
    x = 8.2
    y = 7.2
    MsgBox (x - y)
End Sub

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

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

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