表示形式のユーザー定義ってどう使うの？日付や金額、カンマをわかりやすく表示！データ活用の基本

表示形式のユーザー定義はカスタム数値書式設定を作成して、数値をパーセンテージ、通貨、日付などの形式で表示することができるので、データーベースをより扱いやすくするための機能です。
また、ユーザー独自の細かな設定が可能であることが魅力です。
存在は知っているもののユーザー定義について詳しく知らず、実際に手を付けてみたものの難しく感じて実際に導入することができなかったというケースも少なくありません。

ユーザー定義を設定したい方、これから取り組みたい方のために、表示形式やユーザー定義の使い方の基本をご紹介します。

表示形式とは

セルに「表示形式」を設定すると、データの見た目を変更できます。
例えば、数値に3桁区切りカンマを付けて表示したり、通貨の表示形式を利用して金額を表示したり、数値を読み取りやすくできます。
表示形式を設定しても、セルに格納されている元のデータは変更されないため、数値として計算することも可能です。

設定したい範囲を選択し、《ホーム》タブを選択、 《数値》グループから設定します。

文字列と数値の違い

Excel（）で扱うデータは「文字列」と「数値」の２種類です。
人名や商品名などを入力する「文字列」と、個数や金額などを入力する「数値」の違いに気を付けるようにしましょう。

以下の図はA列が「数値」、B列が「文字列」で入力しています。
それぞれの金額を合計する場合、A列は「数値」を入力し、表示形式の機能で通貨の表示形式を利用しているため計算ができます。
しかし、B列は「文字列」で入力しているため、SUM関数を使用して計算できません。
入力しているデータは数式バーを確認することでわかります。

データ入力のルールに関しては、こちらの記事もご覧ください。
データ入力のルール！Excelの入力方法を守るだけでデータの価値が変わる

ユーザー定義とは

表示形式の中のユーザー定義は、独自に表示形式を定義することができる機能です。
数字に対して単位を付けたり、人名の後ろに「様」、社名の後ろに「株式会社」などを付けたりして、シート上の見え方を変更できます。
複数名でExcelのファイルを管理する場合には、それぞれの独自ルールで入力すると管理が難しくなるため、データ活用には社内で統一されたルールがあると便利です。

表示形式のユーザー定義の設定方法

（1）ユーザー定義を追加したいセルを選択し、以下の方法から「セルの書式設定」のダイアログボックスを開く

• 右クリックをして「セルの書式設定」を選択

• 《ホーム》タブを選択、 《数値》グループの《表示形式》をクリック

• ショートカットキー「Ctrl＋１」

（2）左側に表示される《分類》の中から1番下にある「ユーザー定義」を選択
《種類》の中には文字列や数値などの書式設定が既に設定されています。
希望通りの設定が見つからない場合には、ユーザー定義を選択し、「G/標準」と入力されているボックスに、好みの設定を入力します。

↑　記事TOP

書式記号について

ここでは書式記号について説明します。
ユーザー定義の表示形式で使う記号は半角で入力しましょう。

数値の書式記号

《種類》に入力するものの一例に「#」「0」「?」があります。
「#」は1桁の数字を示します。#の数だけ桁数が指定され、その有効桁数しか表示されず、余分な0も表示されません。
「0」は1桁の数字を示し、指定したゼロの桁数だけ常にゼロが表示され、「?」は値がゼロの時にはスペースを空けます。
また、数値の桁を区切る場合は「,（カンマ）」を使用し、少数点以下を表示するには「.（ピリオド）」を使用します。

日付と曜日の書式記号

日付の書式記号には「y」「m」「d」「g」「e」があります。
「y」は西暦の1桁、「m」は月の1桁、「d」は日の1桁、「g」は元号を表し、「e」は和暦の年を表します。

また、曜日に対する書式記号は、「aaa」「aaaa」「ddd」「dddd」などがあります。

2022年8月1日（月）を例に見てみましょう。

時刻の書式記号

時刻書式記号には、「h」「m」「s」があり、「h」が時間、「m」が分、「s」が秒です。
9時5分23秒を例に見てみましょう。

その他の書式記号

文字列に割り当てられている書式記号には「@」「!」「*」「_」があります。

＠（アットマーク）

「＠」は、任意の文字列を表す書式記号です。
ユーザー定義の表示形式で文字列を使用する際は「"（ダブルクォーテーション）」でかこみます。
「@"株式会社"」とした場合に社名を入力すると「〇〇株式会社」という表示になります。

!（エクスクラメーションマーク）

「!」は、1文字に限定して表示させる書式記号です。
「@!様」の形で、人名の後ろに「様」を自動的に付与する際に使用されることが多いです。

*（アスタリスク）

「*」は、後ろに付けた文字列を繰り返してセルを埋めることを示す書式記号です。
「＠*/」と入力した場合には「××///////////」のようにセルの幅を埋めるように文字列が繰り返されます。

_（アンダーバー）

「_」は、後ろに付けた1文字の幅だけスペースを空ける書式記号です。
「_」の後ろに半角文字を入れると半角分の幅、全角文字の場合には全角分の幅のスペースが空きます。
「#,##0_」とした場合、「1,234 」のように後ろに半角のスペースが空きます。

覚えておくと便利なユーザー定義

セミコロンを使用すると、条件に応じて書式設定を使用することができます。
「正の数の書式;負の数の書式;ゼロの書式;文字列の書式」となります。

マイナスの数値に赤色の▲を設定

一般的にマイナスの数値に▲を設定する場合には、書式設定として「数値」の欄にある「▲1234」を選びますが、赤色にはなりません。
マイナスの数値に赤色の▲を設定したい場合、ユーザー定義で「0;[赤]"▲"0」を設定しましょう。

ユーザー定義として設定することのメリットとしては、正の数や文字列などのマイナスの数値以外にも条件を割り当てることができ、複雑な条件の設定が可能なことです。

何も表示しない設定

あまり頻繁に登場するケースではありませんが、どのような値が入っていたとしても何も表示しない状態とする場合には、「;;;」というユーザー定義を設定します。
「;;;」と設定することで、正の数、負の数、0、文字列のどれに対しても、何も表示しません。

↑　記事TOP

ユーザー定義で発生する問題と解決方法

ユーザー定義はExcelに慣れていない初心者だけでなく、普段から使いなれている人でもなかなか解決できない問題が発生するものです。
Excelは、決められたルールや条件などのプログラムを素直に実行しますので、もしユーザー定義が思うように機能しない場合には、どこかに設定の誤りがあります。

文字列になっていないか

数値や日付、時刻などに対してユーザー定義を設定したいのにも関わらず、正しく機能しないときは「数値」としてデータを入力しているか確認しましょう。
表示形式を使わずにセル内に直接単位を入れてしまったり、頭に「’（シングルクォーテーション)」がついたりしていないでしょうか。
冒頭でも説明した通り「文字列」と「数値」の違いに気を付けるようにしましょう。

スペースが挿入されていないか

Excelに限らずスペースについては目で見て確認することが難しいため、数値や文字列の前後に挿入されていても気付かない場合があります。
しかし、ユーザー定義を行うルールによっては、スペースが挿入されていることで上手く機能しないケースが多くありますので注意が必要です。
特に、数値についてはスペースの影響を強く受けますので、ユーザー定義が機能しない場合にはしっかりとチェックしましょう。

ユーザー定義を使ってExcel（エクセル）を便利に活用

ユーザー定義は、Excelの初心者と上級者を分けるテクニックのひとつで、上手く使いこなすことによって自由度の高い表示ができます。
また、1つのセルに入力した内容をさまざまな形式で表示することが可能になり、これまで個別に入力していたセルを1箇所の入力だけに簡略化できます。
Excelファイルを複数のスタッフで管理している場合には、それぞれのやり方が混在するようなファイルでは、社内ルールをユーザー定義として設定しておくことで管理が容易になります。

自社の業務で使用するExcelファイルについてユーザー定義の設定ができる人材がいない場合や、Excelファイルを整える十分な時間が確保できない場合には、Excelの使用方法に習熟した「EXCEL女子」の活用を検討してください。
さまざまな機能を使いこなしながら、集計業務やレポート作成などのデータ活用にも対応したExcel作成をお手伝いさせていただきますので、まずはお気軽にご相談ください。