この記事は、「Office 365 Advent Calendar 2021」の9日目の記事です。
※参考
通貨と会計の違い
標準の数値の表示形式を比べてみます。
標準
数値
通貨
会計
通貨と会計では、マイナスの表示の仕方、￥マークの位置が違います。また、標準では小数点は表示されません。小数点以下の表示を増やしてやる必要があります。また、会計では、「０」は「ー」と表示されます。セルのデータ型が変わるわけではなく、表示形式が変更になるだけです。
分類で表示されるユーザー定義の表示を見ながら、色々と試していきます。
数値のユーザー定義
上記の表示の仕方は、セルの書式設定、表示形式で、分類でユーザー定義を選択することで見ることができます。(※Web版のExcelでは、書式設定でユーザー定義を使用することはできません）
| 書式 | 種類 |
|---|---|
| 標準 | G/標準 |
| 数値 | 0_);[赤](0) |
| 通貨 | ¥#,##0_);[赤](¥#,##0) |
| 会計 | _ ¥* #,##0_ ;_ ¥* -#,##0_ ;_ ¥* "-"_ ;_ @_ |
この記号がわかれば、いろいろできそうな気がしませんか？
ユーザー定義では、いくつかの記号を使って表示形式を定義します。
０と＃
まず、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のセルで指定できるデータ型は、
| データ型 | 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になるように見えますが、小数点以下の桁を増やしてみてみると、以下のように表示されます。
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
かように、数値の型は悩ましいものです。そんな話は、またいずれするかも、しないかも。