- ホーム >
- 応用操作 >
- エクセル(Excel)編 >
書式のユーザー定義 ~ 自動で単位や敬称をつける・計算する ~
その使い勝手の良さから、Excelの用途は広く、表計算のみならずWordの代用として活用されている方も多いと思います。
そのため、セルに入れる値は数値だけではなく、文字列を入力するケースも多くなります。そこでよくある要望が、セルに入力した値に対して、自動的に「単位」や「敬称」を付記できないかというものです。
例えば、金額を入力したい場合の「円」や人数の「名」、名称の「様」などです。ExcelでこれらをWordのように表現しようとすると、下図ように、敬称や単位だけのセルや列をもうけるのがひとつの手です。
そもそも、なぜセルを分けなければならないかといえば、数値に文字を加えてしまうと、
セルが文字列として認識されてしまい計算できなくなる
からです。Excelで計算できなければExcelを使用する意味がありません。
したがって、このようにセルを分けて入力する方法で一応解決しますが、セルが分かれてしまうことで列数が多くなり、印刷する場合などレイアウトを整えるため、項目名称などのタイトル行のセルを結合しなければならなくなります。
そこで、数値を文字列として認識されることなく、かつ値を入力すると同時に、単位や敬称を自動的に付記させることができれば非常に便利です。その方法は、
ユーザー定義
という設定をすることで可能になります。この操作は、基本操作編の テンプレートの作成 で簡単に学習しましたが、ここではさらに深く学習したいと思います。
同項で学習のとおり、ユーザー定義の設定は、設定したいセルを選択した状態で「セルの書式設定」画面の「表示形式」タブより「ユーザー定義」を選択します。
ユーザー定義を選択すると、「種類」の欄に様々な形式のテンプレートが表示されます。この中から「@(アットマーク)」を探します。
後述しますが、「@」は文字列を表し、これに任意の文字列(単位や敬称など)を付け足すことができます。「@」を選択すると、下図のように編集できるようになります。ここでは、敬称である「様」を付記してみましょう。「@" 様"」と記述します。「""」の間は半角スペースを入力しています。
これで当該セルに書式が設定されました。適当に数値を入力し、下図のように「様」が自動的に付記されることを確認してください。
上図の場合、セルの値は「グレイテスト・エクリップス」のままで変化はありませんが、見かけ上「様」が付記されているのです。
以下の行にも同様に書式を設定したい場合は、範囲指定した状態で同様にユーザー定義を設定すると、その範囲に入力した値すべてに「様」が付記されるようになります。
ユーザー定義は、一度設定して使用すると自動的にExcel(セルの書式設定画面)に保存されるので、以降はそれを選択するだけで簡単に利用できるようになります。 (もちろん削除も可能です)
もう一つの方法として、オートフィルを使ったコピーでも書式を適用させることができます。下図のように、マウスでドラッグしてセルを任意の位置までコピーします。
コピーすると値が変化してしまいますが、右下に表示される「オートフィルオプション」のアイコンをクリックします。
すると、オプションメニューが表示されるので、「書式のみコピー」を選択します。
すると、ユーザー定義のみがコピーされて元の値にもどり、「様」が付記されます。
またこうした文字列は、値の前方に文字列を付記することも可能です。
前方に文字列を表示させる場合も同様に、例えば「「"【限定】 "@」とユーザー定義を設定します。("】"のうしろに半角スペースがあります)すると、下図のように前方に「【限定】 」が付記されます。
さらに、これらを組み合わせて複雑な定義も可能です。例えば、値「@」の前後に文字列を付記することも可能です。下図のように、「"★ "@" 新発売!"」とユーザー定義を作成してみます。
すると、値の前に「★」、後に「新発売!」の文字列が自動的に付記されるようになります。
では、次に「文字列」ではなく、本項のメインとなる「数値」に文字を付記してみましょう。
今度は金額(数値)を入力しているセルに、同様に「円」の文字をユーザー定義で付記させるように設定してみます。
これで数値に「円」の文字が付記されたでしょうか?このユーザー定義を数値のセルに適用すると下図のようになります。
この場合は、文字列「円」が付記されることもなく、数値が右揃えから左揃えになってしまいました。恐れていた「文字列」の認識となっているのです。(一応、Excelではこうした文字列様のユーザー定義の数値は計算することが可能です)
どうしてかというと、「@」に問題があるからです。
「@」は文字列を置き換える記号
だからです。つまり「@」は文字列にしか使えず、数値を扱う場合には、数値に対応した記号を用いてユーザー定義を行わなければなりません。
詳細は後述しますが、数値を置き換える記号は「#」になります。では、「@」を「#」に置き換えて、ユーザー定義「#" 円"」を作成してみましょう。
すると、正しく数値に「円」が付記されます。セルも右揃えのままで、文字列ではなく数値として認識されています。もちろん、この状態で計算も可能です。
ただし、ここである問題が発生しているのにお気づきでしょうか。それは、桁区切りのカンマが消えているということです。
これは、「#」の使い方に問題があるからです。つまり、桁区切りを指定していないからなのです。桁区切りを指定するには「#,###」の形にしなければなりません。
これを踏まえて、ユーザー定義を「#,###" 円"」と書き換えてみましょう。すると、桁区切りのカンマが正しく表示されるようになります。
ところで、「#,###」の形は、「#,#」としても自動的に「#,###」に修正されます。例えばつまり、「1,000」となる桁区切りカンマの位置を「10,00」のようにズラすことはできなくなっています。
では、この「#」についてもう少し詳しくみていきましょう。もともとExcelが作成しているユーザー定義の中には、「#,##0」という定義があります。
両者「#,###」と「#,##0」には違いがあるのです。
値が「0」のとき、「0」は「0」と表示するのに対し、「#」は何も表示しない
という違いあります。
ユーザー定義「#,###" 円"」を適用しているセルで値を「0」にすると、下図のように「円」のみが表示され「0」が表示されません。
したがって、通常は「0」のときは「0」を表示させたいので、この場合は 「#,##0" 円"」に修正しなければなりません。
これで、数値が「0」であっても「0 円と表示されるようになります。
さらに詳しくいうと、じつは「#」も「0」も数値を置き換える記号であり、「#」ではなく「0」を使うことも可能です。しかし実際には「#」が使われ、「0」は1の位に使うことがほとんどです。
なぜなら、もうおわかりのとおり、「0」を指定するとその位の値が「0」のときに「0」を表示してしまうからです。例えば、「0,000" 円"」のユーザー定義では、数値の「123」を表示すると「0,123」と表示されてしまいます。これでは、小数点と間違えてしまいます。
次に、このほかによく使われるユーザー定義を知っておきましょう。
まずは、桁数の指定表示です。
例えば、企業の決算書などにあるように千円単位、百万円単位の桁数で揃えたい場合です。このような場合にも、ユーザー定義で統一した表示にすることができます。
千円単位にしたい場合は、下3桁を非表示にすることになります。つまり、「12,345,678」という数値の場合、下3桁の「678」を四捨五入して「12,346」という値が表示されます。
使う記号は「,」カンマになります。カンマは「3桁」ずつ非表示にする記号で、3桁の倍数以外では使えません。要するに、下2桁とか5桁などを指定してを非表示にすることはできないということです。
したがって、ユーザー定義は、「#,##0," 千円"」のようになります。
少々わかりにくいですが、「0」の後ろに「,」があります。これで、下図のとおり下3桁を四捨五入して非表示にすることができます。
また、下6桁を非表示にして百万円単位にする場合は、「#,##0,," 百万円"」のように「,,」カンマ2つを加えます。すると、下6桁を四捨五入して非表示にすることができます。
ただし、このように表示を変えた場合に注意しなければならないことがあります。それは、前述したように、
表示が変わってもセルの値は変化しない
ということです。上の例のように「12,346 千円」とか「12 百万円」と表示されていても、実際の値は「12,345,678」のままだということです。
文字列「千円」とか「百万円」を付記してあればまだしも、ただ単純に桁を非表示にしている場合、「12,345,678+12,345,678」の計算が、見かけ上は「12,346+12」のように見えてしまうからです。
また、自分で設定したユーザー定義を忘れる場合もあります。他人が作成したExcelファイルを利用する場合なども、ユーザー定義を設定している可能性を頭の片隅においておきましょう。
次に、数値がプラスとマイナスの場合の表示方法です。
正の数の場合は通常通りですが、Excelのデフォルト(初期設定)の「標準」、「数値」、「通貨」のセルの表示形式では、負の値が入力されると下図のようになります。
会社の業績表などを作成する場合などでは、このような表示形式ではなく、赤字で桁区切りのカンマ、マイナス記号(もしくは▲記号など)で場合があります。
もっとも簡単な方法は、「ホーム」タブの「桁区切りスタイル」ボタンをクリックするだけで変換できます。
ただし、この方法ではマイナス記号「-」にしか変換されないので、ユーザー定義で記号を設定してみましょう。
Excelの中に登録されているユーザー定義の中から「#,##0;[赤]-#,##0」を探しましょう。これを利用して新しい定義を作成します。
この意味はもう理解できると思います。前半の「#,##0」は先述のとおりで、「;」は区切りを意味します。「[赤]」は赤字の意味、「-」はマイナスの意味で、負の数の場合は赤字でマイナス記号を付記するという意味になります。
したがって、このマイナス記号を任意の記号に変えればよいのです。ここでは「▲」にしてみましょう。定義は「#,##0;[赤]▲#,##0」になります。
すると、負の数「-10,000」を入力すると、赤字で「▲1,000」と表示されるようになります。
最後に、よく使われる記号の一覧を示します。すべて覚える必要はないと思いますが、覚えておくと便利です。特に「aaa」で定義すると、関数を使わなくても自動的に曜日を表示させることができます。
記号 | 概要 | ユーザー定義と表示形式 |
---|---|---|
@ | アットマークは、入力した文字列と置き換える。 | ユーザー定義を「@"様"」とすると、「入力した文字列」+「様」となる。 |
"" | ダブルクォーテーションは、囲んだ文字列を指定の位置に表示させる。 | ユーザー定義を「@"様"」とすると、@(入力した文字列)の後方に「様」が付記される。 |
# | ハッシュは、入力した数値を置き換える。 | ユーザー定義を「#"円"」とすると、「入力した数値」+「円」となる。ただし、数値が「0」のときは表示されず「円」のみが表示される。 桁区切りのカンマも表示されないので、桁区切りが必要な場合は「#,###」と定義する |
0 | 数字のゼロは、入力した数値を置き換える。 | ユーザー定義を「0"円"」とすると、「入力した数値」+「円」となる。数値が「0」のときは「0」が表示される。 桁区切りのカンマも表示されないので、桁区切りが必要な場合は「0,000」と定義する。ただし、「0」の桁も「0」が表示されてしまうので「#,##0」と定義するのが一般的。 |
, | 下3桁を四捨五入して非表示にする。 | 末尾に指定すると、下の3桁を四捨五入して非表示にする。例えば、千円単位の表示にしたい場合は「#,##0,」、百万円単位の表示にしたい場合は「#,##0,,」と定義する。 |
. | ピリオドは、小数点を挿入する。 | 例えば「#.##」と定義すると(カンマとの混同に注意)小数点2位まで表示される。 ただし、整数部を「#」にすると、「0」の場合に空白になってしまい「.123」のように小数点から始まってしまうので「0.##」と定義するのが一般的。 |
? | クエスチョンは、「0」の代わりにスペースを挿入する。 | 例えば「0.???」と定義すると、小数点3位まで表示されるが、小数点1位や2位で終わる値については3位までのスペースを挿入する。 主に複数の値の小数点の位置を揃えて表示するために使用する。(スペースを挿入しなければ右詰めレイアウトでは小数点の位置が揃わない) |
h | 時刻表示をするhourの「h」。 | 1~23までの時刻を表示する。「h"時"」と定義し、時刻形式(9:30など)で値を入力すると「1時」~「23時」の表示となる。分の部分は表示されない。 「9:30」の場合は「9時」と表示される。 |
hh | 2桁の時刻表示をする。 | 例えば「hh"時"」と定義すると、1時~9時までは「01時」~「09時」と表示される。 |
m(分) | 分の表示をするminuteの「m」。 | 1~59までの分を表示する。「m"分"」と定義し、時刻形式(9:30など)で値を入力すると「1分」~「59分」の表示となる。h部分は表示されない。 「9:30」の場合は「30分」と表示される。 |
mm | 2桁の分表示をする。 | hと同様に、例えば「hh"時"」と定義すると、1分~9分までは「01分」~「09分」と表示される。 |
h:m | 時刻と分を表示する。 | コロンは「""」で囲まなくても時刻表示の「:」の役割を果たす。コロンで区切らなくてもhとmが表示されるが、例えば「9:30」の場合「930」と表示される。 「h"時"m"分"」と定義すると「9時30分」と表示される。(この場合コロンは不要) |
yy | 西暦の下2桁を表示するyearの「y」。 | yは2つか4つしか指定できない。西暦の下2桁を表示する。月日の部分は表示されない。 「2018/2/17」の場合は「18」と表示される。 |
yyyy | 西暦の全桁を表示するyearの「y」。 | yは2つか4つしか指定できない。西暦の全桁を表示する。 月日の部分は表示されない。 「2018/2/17」の場合は「2018」と表示される。 |
g | 和暦の元号を表示する。 | gは1~3つ指定できる。gが1つでアルファベット表示の「M、T、S、H」、2つで漢字の「明、大、昭、平」、3つで漢字の「明治、大正、昭和、平成」が表示される。 ただし元号部分のみの表示となり、「2018/2/17」の場合、gが1つでは「H」とのみ表示される。 |
e | 和暦の年数を表示する。 | 和暦の年数部分を表示する。hやmと同様に桁数に応じて1~2つを指定できる。 ただし年数部分のみの表示となり、「2018/2/17」の場合は「30」とのみ表示される。gと組み合わせて「ge"年"」(H30年)または「gggee"年"」(平成30年)などと指定する。 |
m(月) | 年月日の月を表示するmonthの「m」。 | mは1~5つ指定できる。mが1つで「1~12」の月表示、2つで「01~12」の2桁の月表示、3つ以上は英語(Januaryなど)の表示となる。 同様に「mm"月"」などと定義する。 |
d | 年月日の日を表示するdayの「d」。 | dは1~4つ指定できる。dが1つで「1~31」の日表示、2つで「01~31」の2桁の日表示、3つ以上は英語(Mondayなど)の表示となる。 同様に「dd"日"」などと定義する。 |
yyyymmdd | 西暦の年月日を表示する。 | 左記の定義では、「2018/2/17」の場合「2018217」と表示される。上記の組み合わせと「""」を使うと幾通りもの表示が可能になる。ただし「/」で区切る場合は「yyyy/mm/dd」で「""」は不要。 |
aaa | 曜日を表示する。 | aは3つか4つ指定する。aaaで「日~土」、aaaaで「日曜日~土曜日」の表示。「m/d(aaa)」と定義すると、「9/12(水)」のように自動的に曜日が表示される。 |
このほか、Excelの中に登録されているユーザー定義の中には、かなり複雑なものもあります。ここではそうした定義の説明は割愛しますが、詳しく知りたい方はページ下の 参考サイト をご参照ください。
これでもうユーザー定義を使いこなすことができます。ユーザー定義を駆使すれば、長い文章を付記したり、数値の桁数をカットして表示させたりすることが可能になります。
更新履歴
- 2018年2月17日
- ページを公開。
- 2018年9月12日
- ユーザー定義の記号に「aaa」を追加。
参考文献・ウェブサイト
当ページの作成にあたり、以下の文献およびウェブサイトを参考にさせていただきました。
- 文献
- なし
- Excel(エクセル)の使い方-セルの書式設定-数値の表示形式
- https://www.relief.jp/docs/003352.html
- 管理人のつぶやき
- Twitterのフォローお願いします!