先日は、ビジネス能力検定サーティファイのExcel®表計算処理技能認定試験1・2級の問題集の模擬問題1を解いてみました。
問題だけでも、実技練習問題1・2・3、模擬問題1・2・3・4と計7種類もあり、これは勉強になると思いました。この記事の終わりで問題集へのリンクを貼ってありますので、ご購入希望の方は是非そちらのリンクからご購入のほどよろしくお願いいたします。
さて、今回の問題で引っかかったのは、セルの書式設定のユーザー定義、DSUM関数、ゴールシークの3つでした。
セルの書式設定のユーザー定義でつまづいたのは、数字の後に「円」を付けて表示させるという問題でした。
結果としては、ユーザー定義のサンプルから問題を解くことができたのですが、スマートな回答とは思えなかったため、復習も兼ねて記事にしたいと思いました。
本日は、セルの書式設定のユーザー定義のやり方について調べていきたいと思います。
セルの書式設定のユーザー定義とは
Excelの「ユーザー定義」は、セルに入力されるデータの表示形式を細かくカスタマイズできる機能です。日付、時刻、数値、テキストなどを自分好みにフォーマットし、特定のルールで表示させることが可能です。
例えば、電話番号や郵便番号のように決まった形式で数字を表示したいときに便利です。「#,##0」や「yyyy年m月d日」といったコードを使って、見やすく整理された表を作成できます。
ユーザー定義を活用することで、データの見栄えと理解を向上させられます。
セルの書式設定のユーザー定義の種類
表示形式には、「標準」「数値」「通貨」「会計」「日付」「時刻」「パーセンテージ」「分数」「指数」「文字列」「その他」の11種類が存在します。
しかし、これらの表示形式以外の表示方法を使いたい場合もあります。そんなときに使用するのがこの「ユーザー定義」です。
「ユーザー定義」には約40種類もの方法があるとも言われ、その一部をご紹介したいと思います。
日付に関する書式
日付に関する書式を表示させたいときのルールです。
| 書式記号 | 入力データ例 | 書式適用後の表示 | 説明 |
|---|---|---|---|
| yyyy | 2025/1/6 | 2025 | 西暦4桁 |
| yy | 2025/1/6 | 25 | 西暦2桁 |
| e | 2025/1/6 | 7 | 和歴（例：令和6年） |
| ggg | 2025/1/6 | 令和 | 元号 |
| gg | 2025/1/6 | 令 | 元号の1文字目 |
| g | 2025/1/6 | R | 元号の1文字目のアルファベット |
| mmmm | 2025/1/6 | January | 「月」の英語表記 |
| mmm | 2025/1/6 | Jan | 「月」の英語表記の最初の3文字 |
| mm | 2025/1/6 | 01 | 月2桁（十の位がない場合は0を表示） |
| m | 2025/1/6 | 1 | 月 |
| dd | 2025/1/6 | 06 | 日2桁（十の位がない場合は0を表示） |
| d | 2025/1/6 | 6 | 日 |
| aaaa | 2025/1/6 | 月曜日 | 曜日3文字 |
| aaa | 2025/1/6 | 月 | 曜日1文字 |
| dddd | 2025/1/6 | Monday | 「曜日」の英語表記 |
| ddd | 2025/1/6 | Mon | 「曜日」の英語表記の最初の3文字 |
時刻に関する書式
時刻に関する書式を表示させたいときのルールです。
| 書式記号 | 入力データ例 | 書式適用後の表示 | 説明 |
|---|---|---|---|
| hh | 2:03:05 | 02 | 時間2桁（十の位がない場合は0を表示） |
| h | 2:03:05 | 2 | 時間 |
| h:mm | 2:03:05 | 2:03 | 時間＋分2桁（十の位がない場合は0を表示） |
| h:m | 2:03:05 | 2:3 | 時間＋分 |
| ss | 2:03:05 | 05 | 秒2桁（十の位がない場合は0を表示） |
| s | 2:03:05 | 5 | 秒 |
| AM/PM | 2:03:05 | AM | 午前中ならAM、午後ならPM |
| [h] | 2:03:05 | 2 | 経過時刻1時間単位（分切り捨て） |
| [mm] | 2:03:05 | 123 | 経過時刻1分単位（秒切り捨て） |
| [ss] | 2:03:05 | 7385 | 経過時刻1秒単位 |
日付に関する書式と時刻に関する書式で、「mm」と「m」がダブって出てきていますが、これは日付では「月を2桁表示」「月を1桁表示」と使われています。通常はこの「月を表示する」機能が優先されますが、「h」の「時間」や「s」の「秒」などの時刻に関する表示と併用して使う場合には「分を表示する」機能として動作します。
その他の書式
その他の書式です。
| 書式記号 | 説明 |
|---|---|
| 0 | 0の数だけの桁数を表示 （例：データ⇒123,表示形式⇒0000,表示される値⇒0123） |
| # | #の数だけの桁数を表示 ただし#の数より表示する桁数が少ない場合は、表示する桁数が優先される （例：データ⇒123,表示形式⇒####,表示される値⇒123） |
| ? | ?の数だけの桁数を表示 小数点を揃えるために用いる ただし?の数より表示する桁数が少ない場合は、スペースが挿入される |
| . | 小数点を表示 |
| , | 3桁区切りの表示 |
| % | パーセントで表示 |
| / | 分数を表示 |
| E | 指数を表示 |
| e | 指数を表示 |
| [色] | []内の色を指定して表示 （例：[赤]⇒文字の色が赤で表示される） |
| _ | このアンダースコアの次に入力した文字のスペースを表示 （例：「_)」と入力した場合「)」の文字幅のスペースが表示される） |
| ; | 表示形式の定義を分割する |
| @ | 任意の文字列を表示 （例：＠"様"⇒「山田太郎様」など） |
| * | アスタリスクの次に入力した文字を繰り返す（文字列を揃えるため使用する） （例：「* 」と入力した場合、半角スペースが繰り返し表示される） |
| #,##0 | 3桁ごとにカンマで区切る |
表計算処理技能認定試験2級の試験では、このその他の書式からよく出題されているように思います。全て暗記して試験に臨みましょう。
セルの書式設定のユーザー定義の例
| 書式記号 | 入力データ例 | 書式適用後の表示 |
|---|---|---|
| ggge”年”m”月”d”日” | 2025/1/6 | 令和7年1月6日 |
| h”時””間”mm”分”ss”秒” | 2:03:05 | 2時間03分05秒 |
| AM/PM h”:”mm | 2:03:05 | AM 2:03 |
| @”様” | 山田太郎 | 山田太郎様 |
| @”株式会社” | hogehoge | hogehoge株式会社 |
| “Mr.”@ | Brown | Mr.Brown |
| [青]¥#,##0;[赤]¥-#,##0 | 15000 | ¥15,000 |
| [青]¥#,##0;[赤]¥-#,##0 | -15000 | ¥-15,000 |
| #,##0"円" | 123456 | 123,456円 |
| 0000 | 123 | 0123 |
| 0000 | 12345 | 12345 |
| #### | 123 | 123 |
| #### | 12345 | 12345 |
| ???? | 123 | 123 |
| ???? | 12345 | 12345 |
| 0.0E+00 | 3.56E+08 | 5.6E+08 |
模擬問題1で出題された問題
ビジネス能力検定サーティファイのExcel®表計算処理技能認定試験1・2級の問題集の模擬問題1で実際に出題された問題で、私が頭を悩ませた問題がこちらです。
問題 1-2 桁区切りおよび数値の後に単位「円」を付けて、「2,500円」と表示するように、表示形式を設定する
回答
#,##0"円"
でした。無駄に難しく考えて、ユーザー定義のサンプルを参考に、
¥#,##0"円";¥-#,##0"円"
という難解な回答をしてしまいました。もっと、指示に従っただけのシンプルな回答で良かったんですね。
最後に
以上、セルの書式設定のユーザー定義のやり方について調べてみたわけですが、いかがだったでしょうか。
なにかお役に立てたならば光栄です。
ではでは、参考までに。
おまけ
サーティファイの Excel®表計算処理技能認定試験2級を受験するにあたり、これさえ買っておけば必ず受かるといっても過言ではない一冊の本をオススメ致します。
それがこちら。
ポチップ
私も購入させていただき、大変お世話になっております。
コメント