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

この記事は、「[Office 365 Advent Calendar 2021](https://adventar.org/calendars/6679)」の9日目の記事です。

※参考 

https://support.microsoft.com/ja-jp/topic/excel-%E3%81%AE%E3%82%BB%E3%83%AB%E3%81%AE%E8%A1%A8%E7%A4%BA%E5%BD%A2%E5%BC%8F%E3%81%A7-%E3%83%A6%E3%83%BC%E3%82%B6%E3%83%BC%E5%AE%9A%E7%BE%A9-%E3%81%AB%E4%BD%BF%E7%94%A8%E3%81%A7%E3%81%8D%E3%82%8B%E6%9B%B8%E5%BC%8F%E8%A8%98%E5%8F%B7%E3%81%AB%E3%81%A4%E3%81%84%E3%81%A6-979767bb-6635-c0f5-5e7f-32c69715ccfa

# 通貨と会計の違い
　標準の数値の表示形式を比べてみます。

標準
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/67a2a962-52b3-96f0-00f2-07ab25fb0542.png)

数値
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/d86b9c86-f3b7-72d7-a41b-38f15cf89be7.png)

通貨
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/066acbb9-76cf-ccf5-b9ce-ce144f490e5a.png)

会計
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/ee93bd18-b00d-a27f-8477-facfa8a4ba2d.png)

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

# 数値のユーザー定義
　上記の表示の仕方は、セルの書式設定、表示形式で、分類でユーザー定義を選択することで見ることができます。(※Web版のExcelでは、書式設定でユーザー定義を使用することはできません）
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/e16b7718-3d84-dc84-d644-30553a351d7f.png)

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

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

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


```:ユーザー定義1
0
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/d1c621cb-5479-4ad6-6c78-7e5c37caafd4.png)

```:ユーザー定義2
#
```
　ゼロは表示されません。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/34940ab3-bfe8-352f-1b1b-4993cb5ff4d2.png)

```:ユーザー定義3
0␣ (0の後にスペースを入力)
```
　数値と右側あの罫線の間にスペースを入れます。スペースを空ける方法は、ユーザー定義の中にスペースを入力する方法と、_(アンダーバー）に続いて文字を書く方法があります。文字の幅が一定でないフォントを使う場合、アンダーバーと文字を使って幅をそろえるます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/9b28e5db-2765-34c4-41b2-c387a1aac628.png)

```:ユーザー定義4
0"円"
```

　データ中に単位を表示させたい場合は、直接書き込まずに、表示形式で対応すると、数値として計算可能になります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/dc376adb-e520-8601-0c51-fe895b97815e.png)

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

```:ユーザー定義5
00
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/852df118-28fd-836d-f203-3b95697d15b9.png)

```:ユーザー定義6
0000
```

　頭がゼロで始まる可能性のある、桁数が一定の数値を入力・表示させたい場合、表示形式で指定してやれば、文字列と数値が混在する問題から解放さえれます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/729105ad-e8b7-97fc-67ea-031e57fa78f7.png)

## 小数点以下の表示

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

```:ユーザー定義7
0.00
```

　小数点以下２桁が表示されます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/c99ac3d7-0669-a68d-c695-a0c66bd21760.png)

```:ユーザー定義8
#.00
```
　整数部が０の場合は、表示されません。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/04b37937-92a5-0421-2580-89b09a7ca66b.png)


```:ユーザー定義9
0.??
```
　**0.##**だと、小数点以下の桁数が不ぞろいになりますが、？は、小数点の位置を揃えます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/d6fe855c-2406-82e4-f7ae-8644c4addc37.png)

## ３桁区切り

```:ユーザー定義10（２行目は省略形）
#,##0
#,0
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/512c19e5-54c6-b6a1-851c-172a4fd07f71.png)

```:ユーザー定義11（２行目は省略形）
#,###
#,#
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/b5e33b32-1925-8509-4930-e7cea0035333.png)

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

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

```:ユーザー定義12
0,
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/84c57208-1a3e-742b-cc4c-df11ac76b6f9.png)

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

```:ユーザー定義13（２行目は省略形）
#,##0.00,
#,0.00,
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/4ee52c10-7060-19d1-0038-0f244c255d06.png)

## 色を付けて表示
　ユーザー定義で色を指定することができます。

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

　色は、番号で指定することもできます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/f50e4895-2ee0-4b30-b72f-30d108805dbe.png)
（※[よねさんのExcelとWordの使い方](http://www4.synapse.ne.jp/yone/excel2010/excel2010_hyoujistyle2.html)）

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

```:文法
<POSITIVE>;<NEGATIVE>;<ZERO>;<TEXT>
```

```:ユーザー定義14
[黒]0.0;[赤]-0.0;[青]0.0
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/c6edc7f8-2ee7-5c2a-7d16-f4aae61b3d5e.png)

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

　_は、後に続く文字と同じ幅のスペースが入ります。@は、セルの文字列です。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/491cf389-4d13-4820-327a-758a1c4f996b.png)

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

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/db3531c2-5554-a881-dde8-f781e1464140.png)

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

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

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/74f8af28-f260-081d-2d18-3ba90182901a.png)


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

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

```:文法2
[条件１]書式１;書式２
```

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

```:文法３
[条件１]書式１;G/標準
```

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

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/42c4340d-b168-3660-ab97-cda5f1bc88da.png)

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

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


# 日時の書式

## 年の書式
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/b30c2561-cb38-51a0-86a0-c5890e1c5747.png)

## 月の書式
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/baf6904c-9f00-2406-6271-e4a9eecdf659.png)

## 日の書式
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/9a469e9c-c055-5083-78cc-9f69a0b632e9.png)

## 曜日の書式
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/69d4d764-05fd-85e4-5d65-2fcba5cf5825.png)

## 時刻の書式
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/0e2e394e-579c-345c-a2d4-dea8e04f4254.png)

# 文字の書式
　文字は、＠を使います。

```:ユーザー定義21
@" 様"
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/9e1aad56-d023-486b-ebda-546ad7ab793d.png)

# 分数
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/7f657113-0595-8c73-a630-870f35c125cc.png)

# その他
　エクセルの表示形式で、その他に分類されているものを見てみましょう。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/e1e6ecd7-10c9-90ff-4063-242332c9b2a6.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/eb85debd-fd70-c3d8-a48b-4a8a50002fda.png)
　漢数字について、中国簡体字と韓国のロケールにすると、以下のようになります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/310326e4-57fb-5e9b-ad89-93da08770c03.png)
　電話番号で携帯電話の場合は、カッコ内に3桁表示で、頭がゼロになるので、以下のようにします。ただし、一般電話の市外局番が2桁か3桁かの判断を入れることはできません。

```:ユーザー定義22
[<=99999999]####-####;[<=999999999](00) ####-####;(000) ####-####
````
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/d0d32012-e6ab-e0a5-34e5-5eb14b06b72c.png)


# Power BIのカスタム書式
## 数値
（※「[Power BI Desktop でカスタム書式設定文字列を使用する](https://docs.microsoft.com/ja-jp/power-bi/create-reports/desktop-custom-format-strings)」）

　Power BIで書式設定にカスタム書式を入力することができます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/3abd16c4-bd95-7af8-aca8-30a6d3fe82b5.png)
　**？と［］、＊の文字が使えないようで、小数点をそろえる？と色の指定や分数が使用できませんでした。**
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/314dbb23-be72-3056-dedd-933ce83022b9.png)

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

```:ユーザー定義23
"▲ "#,##0"円";"▼ "#,##0"円";"-"
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/b6abc3be-96a6-d599-ebd6-eccabeb3c4f2.png)

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

```:ユーザー定義24
[>10]"●"#,##0;#,##0
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/a90bde89-0b5d-d273-3acb-2de0760e53ed.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/4b5aa08b-814d-77a9-2421-4122c9995cde.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/eb4d6548-a9a8-87bf-1730-3db39d6863b1.png)

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

# Power BI DAX式 FORMAT
## 数値
　DAX式を使って書式設定することができます。使えるものは、Power BIのカスタム書式で使えるものと同様です。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/3ee840b4-f520-6f77-d0af-f4f97bf4acfe.png)
　数字以外の文字が入っていると文字列項目となりますので、左寄せで表示されます。これをビジュアルの書式設定を使って、配置を変更します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/29bf2a47-ef61-31a3-9906-d152811971a3.png)
　オプションで「新しい書式設定ウィンドウ」にチェックを入れている場合は、以下のようになります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/b36f04ba-8d9b-84e2-2250-f48944dac300.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/9aeef3b7-22d3-9f09-838f-5e29bd4a680d.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/04d7eb8b-74c3-dc77-b068-372621904e99.png)

## 日付
　カスタム書式よりも使える種類が増えます。***g***や***gg***が使えます。曜日は***ddd***と***dddd***で英字で表示されますし、***aaa***と***aaaa***は日本語表示ができます。日本語表示を使う場合は、ロケールに***"ja-JP"***を付けておきます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/a087012d-8134-1c94-b3d6-36496e95eb7f.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](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/c710598b-4e9c-ecd0-2748-6b443e7baaf2.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](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/77564187-b08e-f7e4-d47c-322f53c5d29d.png)

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

```:倍精度浮動小数点型
Sub test1()
    x = 8.2
    y = 7.2
    MsgBox (x - y)
End Sub
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/560b8550-9ca7-65b6-3fae-c861ba51a4fd.png)

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

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/a4a339d4-07c2-6b0a-0f6b-c2e9d6a1a907.png)

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