一緒に読んで頂いている記事

スタディサプリというシステムを使って実際に子供の成績がアップした!?
 
小学4年生~中学3年生までを対象にしたWEB学習サービスの「スタディサプリ」を使って実際に成績が上がった人のブログです。使って良かった点と悪かった点も書かれているのでとても参考になります。
 
高速道路・一般道渋滞情報無料アプリ
 
機能充実カーナビ無料アプリ
 
ジャンプ・マガジン・サンデー無料立ち読み
 
アニメを無料で見る!

日付から曜日を表示するエクセル関数TEXTの使い方|祝日の色付けと和暦表示

text4820-29

カレンダーや日計表、スケジュール表などを作成するときに知りたいのが、日付から自動で曜日を表示したり、土日祝日は文字を色付けしたり、セルの背景を変えたりして休みが分かるようにしたいと言ったことです。曜日を手入力したり、土曜日は青文字、日曜日と祝日は赤文字に自分で設定するのはとても大変な作業です。

そこで今回は、関数を使って日付から曜日を表示し、エクセル(Excel)の機能を使って色分けをする方法をまとめました。

スポンサーリンク


TEXT関数で日付から曜日を表示する

関数の書式【文字列操作】

TEXT関数の書式は、「=TEXT(,表示形式)となっています。このTEXT関数を使うことで、数値を文字列に変更できます。数値が日付データのときは、表示形式の設定で曜日を表示することができます。

・・・表示形式を変更して文字列にする値やセルを指定します。

表示形式・・・書式記号を「”」でくくって指定します。

text4820-1

自分で使いやすいスケジュール帳をエクセル(Excel)で作ったりするときに、曜日を入れたいと思うことがあります。この曜日を手入力してしまうと来年になったときに、また曜日だけを手入力しないといけなくなりますので、関数を使って曜日を表示させるようにすると手間が省けます。

text4820-2

曜日を表示させたいセル、今回はB3に下記のTEXT関数を入力します。

=TEXT(A3,”aaa“)

・・・スケジュール帳の日付を曜日で表示するため、日付が入力されたセルを指定します。

表示形式・・・曜日を「月」や「火」で表示する「”aaa”」を入力します。

 曜日の表示形式の種類

表示形式に入力する種類によって、指定する書式の記号が変わります。曜日表示の種類は下記のとおりです。

曜日の書式記号 表示
“aaa”
“aaaa” 月曜日
“ddd” Mon
“dddd” Monday

text4820-3

曜日を表示させたいセルの範囲に関数式を貼り付けることで表示させることができます。

TEXT関数で日付を和暦表示する

text4820-4

「2016/8/1」という日付を和暦表示すると「平成28年8月1日」となります。このように、和暦表示したいときにもTEXT関数を使うことができます。

text4820-5

=TEXT(A2,“ggge年m月d日”)

・・・和暦表示するために入力されている日付のセルを指定します。

表示形式・・・日付を和暦表示にする「”ggge年m月d日”」を入力します。

text4820-6

和暦表示させたい範囲に関数式を貼り付けることで完成です。

 「H」や「平」などの和暦表示もできます

TEXT関数は、和暦表示するときに使った上記の表示形式「”ggge”」以外にも様々な表示をすることができます。これを知っておくことで、書類別に表示形式を変更するだけで対応できます。

和暦の書式記号 表示
“e” 28
“ge” H28
“gge” 平28
“ggge” 平成28

 表示形式で使用する書式記号と文字列を組み合わせる

TEXT関数の表示形式に入力することができるのは、決められた書式記号だけではなく文字列を組み合わせることもできます。和暦表示するときに「年」「月」「日」の文字を組み合わせていますが、日付と組み合わせて、色々な使い方ができます。

text4820-7

=TEXT(A2,“振込みはggge年m月d日まで”)
=TEXT(A3,“作成日:gge年m月d日”)
=TEXT(A4,“期限はge年m月d日です”)

TEXT関数の他の書式記号一覧

分類 書式記号 意味 表示形式の指定 表示
数値 # #の数だけ桁数を表示する ####.# 123.45→123.5
0 指定した0の桁数だけ0を表示する 0000.000 123.45→0123.450
? 指定した?の桁数に満たないときは空白を表示する ????.??? 123.45→123.45
日付 y 西暦の年をを表す yy
yyyy
2016/8/1→16
2016/8/1→2016
m 月を表す m
mm
mmm
mmmm
2016/8/1→7
2016/8/1→07
2016/8/1→Aug
2016/8/1→August
d 日を表す d
dd
2016/8/1→1 2016/8/1→01
時刻 h 時刻を表す h
hh
07:23:05→7
07:23:05→07
m 分を表す h:m
h:mm
07:23:05→7:5
07:23:05→7:23
s 秒を表す s
ss
07:23:05→5
07:23:05→05

曜日の色を自動で変える方法

日付から曜日を自動で設定するのは「TEXT関数」を使うことで簡単にできることを紹介しましたが、カレンダーを見ると土曜日は青色、日曜日と祝日は赤色に文字をしたいと思ってしまいます。

text4820-8

日付と曜日の一覧表を作ったら

・土曜日を青色
・日曜日を赤色
・祝日を赤色

で自動表示される方法を紹介します。

text4820-9

曜日別で色を変える方法としては、条件付き書式を使います。その条件付き書式の条件を「土曜日の場合は文字を青色にする」といった条件を指定してあげます。作成した表の曜日の先頭列にある関数の書式を確認します。B2セルに入力されている関数式は、

=TEXT(A2,”aaaa“)

text4820-10

曜日の色を自動で変えたい範囲を選択します。

text4820-11

「ホーム」タブを選び、「条件付き書式」を選択して、「ルールの管理」をクリックします。

text4820-12

条件付き書式ルールの管理画面が表示されますので、「新規ルール」をクリックします。

text4820-13

新しい書式ルール画面の「数式を使用して、書式設定するセルを決定」を選びます。

text4820-14

次の数式を満たす場合に値を書式設定の欄に、

=TEXT(A2,”aaaa”)=”土曜日”

と入力し、文字の色を指定するために「書式」をクリックします。

text4820-15

セルの書式設定画面が表示されたら、「フォント」タブを選び、色の下矢印「▽」をクリックして、青色を選び、「OK」を押します。

text4820-16

新しい書式ルール画面に戻り、プレビューに表示されている色が青色になっていれば文字の色付け設定は正常に行われた状態なので、「OK」をクリックします。

text4820-17

条件付き書式ルールの管理画面が表示され、ルールの欄に新規で作ったルールが追加されます。「OK」を押すと

text4820-18

土曜日の色が上記で指定した青色に変わります。

text4820-19

土曜日の文字の色付けを行なったのと同じように、日曜日の文字の色付けを赤色にする条件を設定します。

次の数式を満たす場合に値を書式設定の欄に入力するのは

=TEXT(A2,”aaaa”)=”日曜日”

と入力し、文字の色を指定します。

text4820-20

日曜日の文字列の色を赤色に設定した状態です。この「条件付き書式」を使うことで曜日別に色を自動で変更することができます。

祝日の休みの日の色を変える方法

祝日の色を変えるために必要な情報をまとめました。少し手間が掛かりますが、その手順を1つ1つ丁寧に画像を使って紹介させていただきますので、参考にしてみてください。

text4820-21

EXCEL(エクセル)の機能で祝日の日を色を変えることができませんので、祝日を自分で指定してあげる必要があります。祝日はハッピーマンデー制度により、毎年違う月日になることがあるので、祝日だけは、毎年その情報を更新する必要があります。

祝日リストを作成しましたので、後で紹介します。

新しいシートを追加して、今年の祝日リストを作成します。

text4820-22

「条件付き書式」で式を指定するときに入力値を簡単にするため、「名前の定義」を付けておきます。祝日の日付一覧のみを範囲指定して、右クリックします。メニューが表示されたら、「名前の定義」を押します。

text4820-23

新しい名前画面が表示されますので、名前欄に任意の名前を入力します。今回は「祝日リスト」と入力しました。範囲はブックでいいです。参照範囲は対象の祝日日付の範囲になっていることを確認しておきます。

text4820-24

今回は、8月11日の海の日が祝日であるかどうかを判定して、祝日リストにある場合には、その日の曜日を赤色にするために祝日の判定を行ないます。祝日判定のために「MATCH関数」と「ISNUMBER関数」を使います。

MATCH関数・・・祝日リストに含まれているかを確認する

ISNUMBER関数・・・セルの内容が数値か否かを判定する

祝日リストに含まれている日は、「TRUE」で判定されます。

=ISNUMBER(MATCH(A2,祝日リスト,0))

text4820-25

文字列の色を変えたい曜日の範囲を選択します。

text4820-26

「ホーム」タブ ⇒ 「条件付き書式」 ⇒ 「ルールの管理」 ⇒ 「新規ルール」の順に進んでいき、「数式を使用して、書式設定するセルを決定」をクリックして、「次の数式を満たす場合に値を書式設定」欄に「=ISNUMBER(MATCH(A2,祝日リスト,0))」を入力して、書式で文字列の色を赤色を選び、「OK」を押します。

text4820-27

条件付き書式ルールの管理画面で新たにルールが追加されますので、「OK」をクリックします。

text4820-28

山の日の8月11日の曜日が赤色に変更されました。条件を指定したあとであれば、祝日判定を削除してしまっても曜日の色が黒に戻ることはありません。でも、来年以降も使うことを考えれば、祝日判定の列は非表示にしておくといいです。

年度が変わったときの修正方法

2016年から来年の2017年に変わったら使えなくなってしまうの?と思うかも知れませんが、そんなことはありません。一度作ってしまえば、更新は簡単です。

1.置き換え機能を使って年を置き換える(2016⇒2017)

2.祝日リストを置き換える

この2つの処理をすることで、簡単に更新することができます。

2016年~2021年までの祝日リスト

2016年 2017年
祝日名 日付 祝日名 日付
元日 2016/1/1 元日 2017/1/1
成人の日 2016/1/11 振替休日 2017/1/2
建国記念の日 2016/2/11 成人の日 2017/1/9
春分の日 2016/3/20 建国記念の日 2017/2/11
振替休日 2016/3/21 春分の日 2017/3/20
昭和の日 2016/4/29 昭和の日 2017/4/29
憲法記念日 2016/5/3 憲法記念日 2017/5/3
みどりの日 2016/5/4 みどりの日 2017/5/4
子供の日 2016/5/5 子供の日 2017/5/5
海の日 2016/7/18 海の日 2017/7/17
山の日 2016/8/11 山の日 2017/8/11
敬老の日 2016/9/19 敬老の日 2017/9/18
秋分の日 2016/9/22 秋分の日 2017/9/23
体育の日 2016/10/10 体育の日 2017/10/9
文化の日 2016/11/3 文化の日 2017/11/3
勤労感謝の日 2016/11/23 勤労感謝の日 2017/11/23
天皇誕生日 2016/12/23 天皇誕生日 2017/12/23
2018年 2019年
祝日名 日付 祝日名 日付
元日 2018/1/1 元日 2019/1/1
成人の日 2018/1/8 成人の日 2019/1/14
建国記念の日 2018/2/11 建国記念の日 2019/2/11
振替休日 2018/2/12 春分の日 2019/3/21
春分の日 2018/3/21 昭和の日 2019/4/29
昭和の日 2018/4/29 憲法記念日 2019/5/3
振替休日 2018/4/30 みどりの日 2019/5/4
憲法記念日 2018/5/3 こどもの日 2019/5/5
みどりの日 2018/5/4 振替休日 2019/5/6
子供の日 2018/5/5 海の日 2019/7/15
海の日 2018/7/16 山の日 2019/8/11
山の日 2018/8/11 振替休日 2019/8/12
敬老の日 2018/9/17 敬老の日 2019/9/16
秋分の日 2018/9/23 秋分の日 2019/9/23
振替休日 2018/9/24 体育の日 2019/10/14
体育の日 2018/10/8 文化の日 2019/11/3
文化の日 2018/11/3 振替休日 2019/11/4
勤労感謝の日 2018/11/23 勤労感謝の日 2019/11/23
天皇誕生日 2018/12/23 天皇誕生日 2019/12/23
振替休日 2018/12/24
2020年 2021年
祝日名 日付 祝日名 日付
元日 1月1日 元日 1月1日
成人の日 1月13日 成人の日 1月11日
建国記念の日 2月11日 建国記念の日 2月11日
春分の日 3月20日 春分の日 3月20日
昭和の日 4月29日 昭和の日 4月29日
憲法記念日 5月3日 憲法記念日 5月3日
みどりの日 5月4日 みどりの日 5月4日
こどもの日 5月5日 こどもの日 5月5日
海の日 7月20日 海の日 7月19日
山の日 8月11日 山の日 8月11日
敬老の日 9月21日 敬老の日 9月20日
秋分の日 9月22日 秋分の日 9月23日
体育の日 10月12日 体育の日 10月11日
文化の日 11月3日 文化の日 11月3日
勤労感謝の日 11月23日 勤労感謝の日 11月23日
天皇誕生日 12月23日 天皇誕生日 12月23日

編集後記

日付から曜日を自動で表示し、さらに曜日の文字、土曜日を青色、日曜日と祝日を赤色にする方法をまとめました。エクセルを使っていると、曜日を設定することが多く、管理するためにも色をすぐに変えられると作業効率の短縮になります。

特に年間スケジュール表を作ったりするときに、毎月の曜日を手入力するのは、とても面倒です。紹介したTEXT関数を使って簡単に表示することができ、年度が変わっても、その年度にあった曜日を表示してくれますので、一度設定してしまえば、来年になっても修正は簡単です。

さらに、休みの日を把握するためには祝日の日も赤い文字色にしておくことが必要です。

もっと言えば、六曜も表示できるようにするといいのですが、エクセルの機能や関数で六曜を表示することができません。そのため、他の方法を使って表示させないといけませんので、その方法は別でまとめたいと思います。

スポンサーリンク

コメントを残す