LoginSignup
TrendQuestionOfficial EventOfficial ColumnOpportunitiesOrganization
5
2

More than 1 year has passed since last update.

@spumoni

posted at

updated at

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

Excel,PowerBI,書式設定,ユーザー定義,カスタム書式

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

※参考

通貨と会計の違い

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

標準
image.png

数値
image.png

通貨
image.png

会計
image.png

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

数値のユーザー定義

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

書式 種類
標準 G/標準
数値 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

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

ユーザー定義8
#.00

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

ユーザー定義9
0.??

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

３桁区切り

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

image.png

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

image.png

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

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

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

ユーザー定義12
0,

image.png

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

ユーザー定義13（２行目は省略形）
#,##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
[条件１]書式１;[条件２]書式２;書式３

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

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

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

文法３
[条件１]書式１;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と異なりますので注意が必要です。和暦は、gやggは使えず、gggが使えます。eとeeは使えます。月のmはMと大文字にする必要があります。曜日は、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

日付

　カスタム書式よりも使える種類が増えます。gやggが使えます。曜日はdddとddddで英字で表示されますし、aaaとaaaaは日本語表示ができます。日本語表示を使う場合は、ロケールに***"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
Sign upLogin
5
2