わえなび ワード&エクセル問題集

当サイトはWordとExcelの実力を鍛える最高水準の特訓問題集です。リンクを張るのは自由です。みんなで勉強しましょう!

【Excel】日付から曜日を自動で表示するのにWEEKDAY関数を使ってはいけない

Excelでカレンダーや月間予定表を作るときに、日付を曜日に変換する方法を知っていると便利です。連続で曜日を入力したり、日付の隣に曜日を表示するだけであれば関数を使う必要はありません。

ところで、Excelはなぜ日付から曜日を自動で取得することができるのでしょうか? そして、Excelで曜日を求める関数と言えばWEEKDAY関数ですが、なぜ、WEEKDAY関数は曜日ではなく曜日を表す「数値」を返すのでしょうか?

これらの疑問は「曜日=7で割ったときの余り」が理解できていればすべて説明できます。そこで、今回はExcelの曜日表示・曜日変換の仕組みを深く理解するための練習問題を出題します。

目次

1.オートフィルで連続した曜日を入力する

問題

「水」と入力してオートフィルをしなさい。また、オートフィルを利用して、「月」「水」「金」・・・と入力しなさい。

f:id:waenavi:20191113161624j:plain

 

解説

「水」と入力します。

f:id:waenavi:20191113161751j:plain

 

オートフィルをします(参考:【Excel3級レベル】オートフィルを利用した表の作成手順と入力練習)。連続して曜日を入力することができます。

f:id:waenavi:20191113163124j:plain

 

ただし、数式ではないので先頭の曜日を変えてもそれ以降は連動しません。

f:id:waenavi:20191113163151j:plain

 

「月」「水」と入力します。

f:id:waenavi:20191113163234j:plain

 

これら2つを選択してオートフィルをすると「金」になります。つまり、数値と同じように1日おきの曜日を入力することができます。

f:id:waenavi:20191113163301j:plain

 

月水金を選択したまま、Ctrlキーを押しながらオートフィルをすると単純なコピーとなり、月水金の繰り返しとなります。

f:id:waenavi:20191113163347j:plain

 

2.表示形式「aaa」

(1)数値を曜日に変換する

問題

1~14の数値を入力した。日~土の表示にしなさい。また、元に戻しなさい。

f:id:waenavi:20191113163447j:plain

 

解説

セルの書式設定の画面、表示形式(ユーザー定義)で、「aaa」にします(参考:【Excel】セルの表示形式「ユーザー定義」書式記号完全総まとめ)。

f:id:waenavi:20191113163857j:plain

 

日~土の表示になります。

f:id:waenavi:20191113163917j:plain

 

表示形式を標準にすると元の数値に戻ります。

f:id:waenavi:20191113163943j:plain

 

表示形式を「aaa」にすると、1~7の数値を日~土に変換して表示されます。8以上も同様となります。

f:id:waenavi:20191113164544j:plain

 

つまり、7で割ったときの余りが1~6のとき、それぞれ日~金となり、7の倍数の時は「土」となります。

f:id:waenavi:20191113164659j:plain

 

(2)曜日を調べてみよう

問題

セルA1に「2020/1/1」と入力して曜日を調べなさい。任意の日付(生年月日など)を入力して曜日を調べなさい。

f:id:waenavi:20191113165344j:plain

 

解説

Excelの日付は整数値(シリアル値)の連番で管理されているため、7で割ったときの余りを考えることができます(参考:【Excel】日付の「シリアル値」を本気で理解するには何を練習するべきか?)。

f:id:waenavi:20191113171230j:plain

 

1900年3月1日以降、日曜日のシリアル値を7で割ると余りは1になります。土曜日のシリアル値は必ず7の倍数になります。

f:id:waenavi:20191113171628j:plain

 

日付のシリアル値は、通常の整数値を入力した場合と同じように、シリアル値を7で割ったときの余りが1~6の時は日~金、7の倍数の時は土曜日になります。

f:id:waenavi:20191113172257j:plain

 

例えば、2020年1月1日のシリアル値は43831です。Excelで「2020/1/1」と入力するのと「43831」と入力するのは同じです。表示形式が異なるだけです。

f:id:waenavi:20191113172446j:plain

 

表示形式を「aaa」にすると「水」になります。

f:id:waenavi:20191113172524j:plain

 

Excelを使うと将来や過去の日付の曜日を簡単に求めることができます。

f:id:waenavi:20191113172747j:plain

 

*補足* 日曜日の場合、表示形式が「aaa」なら「日」となりますが、「aaaa」「ddd」「dddd」なら、それぞれ「日曜日」「Sun」「Sunday」となります(以下、省略)。

 

(3)数式による連続入力

問題

セルA1に「水曜日」と入力した。セルA2以降に、数式を用いて木曜日、金曜日、土曜日・・・と表示することは可能か。

f:id:waenavi:20191113172900j:plain

 

解説

セルA1に「水曜日」と入力したとします。これに1を足してもエラーになるだけです。文字列に1を足すことはできないからです。

f:id:waenavi:20191113172949j:plain

 

そこで、セルA1に「4」と入力します。この4というのは前述の水曜日にあたる数値です。

f:id:waenavi:20191113173151j:plain

 

表示形式を「aaaa」にすると水曜日という表示になります。

f:id:waenavi:20191113173237j:plain

 

これに1を足します。4に1をたすと5になりますが、表示形式によって木曜日になります。

f:id:waenavi:20191113173341j:plain

 

これをオートフィルすると連続した曜日になります。

f:id:waenavi:20191113173416j:plain

 

先頭の数値を変えると、それ以降も自動的に曜日が変わります。

f:id:waenavi:20191113173447j:plain

f:id:waenavi:20191113173505j:plain

 

(4)後ろに曜日を付ける

問題

セルA1に2020/1/1と入力した。この日付の後に(水)をつけなさい。

f:id:waenavi:20191113173612j:plain

 

解説

表示形式を「yyyy/m/d (aaa)」にします。これで最後に曜日が付きます。

f:id:waenavi:20191113173649j:plain

 

(5)1か月間の日付と曜日を表示する

問題

セルA1に「2020/1/1」と入力した。

f:id:waenavi:20191113173821j:plain

 

セル範囲A3:B33に1日~31日の日付を入力しなさい。また、セルA1、セル範囲A3:A33、セル範囲B3:B33の表示形式をそれぞれ「yyyy年m月」「d」「aaa」にしなさい。

f:id:waenavi:20191113174149j:plain

 

解説

セルA1を参照します。

f:id:waenavi:20191113174320j:plain

 

これに1を足すと翌日の日付になります。これは日付がシリアル値(連番)で管理されているからです(参考:【Excel】完全マスター!日付の足し算と引き算を使った日数の計算問題)。

f:id:waenavi:20191113174439j:plain

 

A33までコピーします。

f:id:waenavi:20191113174505j:plain

 

さらに、左隣のセルを参照します。

f:id:waenavi:20191113174532j:plain

 

コピーします。

f:id:waenavi:20191113174548j:plain

 

セルA1の表示形式を「yyyy年m月」にします。

f:id:waenavi:20191113174628j:plain

 

A列の表示形式をユーザー定義で「d」にします。

f:id:waenavi:20191113174719j:plain

 

B列の表示形式をユーザー定義で「aaa」にします。これで、日付と曜日になります。

f:id:waenavi:20191113174804j:plain

 

いっぱんに、カレンダーを作成するときは、西暦年月日をフルで入力してから、表示形式で変換します。

f:id:waenavi:20191113175210j:plain

 

セルA1を2020/3/1に変更すると3月のカレンダーになります。

f:id:waenavi:20191113175344j:plain

f:id:waenavi:20191113175411j:plain

 

3.TEXT関数で曜日を求める

(1)別のセルに曜日を表示する

問題

セルA1とセルB1に日付を入力した。TEXT関数でセルA1の曜日を表示しなさい。また、2つの曜日を求めて「月・金」と表示しなさい。

f:id:waenavi:20191113185604j:plain

 

解説

基本的に曜日を表示するだけであれば、セルの表示形式を変更するだけで良いですが、曜日の文字列でさらに、文字列としての演算をする場合はTEXT関数を使います(参考:【Excel】TEXT関数で数値を文字列に変換するときの注意点と練習問題)。

  • TEXT(数値,"表示形式")

曜日を求めるなら「=TEXT(A1,"aaa")」となります。月曜日です。

f:id:waenavi:20191113185634j:plain

 

文字列なので&で連結することができます。

  • =TEXT(A1,"aaa")&"・"&TEXT(B1,"aaa")

f:id:waenavi:20191113185742j:plain

 

TEXT関数で取り出した文字列は、数値ではなく元の日付(シリアル値)の情報は失われているため、標準の表示形式で数値になることはありません。差し込み印刷をするときに使えます(参考:【Word】差し込み印刷のカンマ、桁数、日付時刻、パーセントの表示形式の設定と変更)。

f:id:waenavi:20191113185957j:plain

 

このように、日付のシリアル値を、日本語または英語の曜日の文字列に変換するには、表示形式を用いるか、TEXT関数を使います。

f:id:waenavi:20191113190607j:plain

 

*補足* 文字列で曜日を表示するには「WEEKDAY関数が必要」などとまったく意味不明な説明をしているサイトがあるようですが間違いです。WEEKDAY関数がわざわざ数値を返している意味を理解していないのではないでしょうか??WEEKDAY関数は数値に変換するものであって文字列に変換するものではありません(後述)。

 

(2)TEXT関数の入れ子

問題

セルA1に年月日を表す8桁の数値を入力した。TEXT関数で曜日を求めなさい。

f:id:waenavi:20191113191002j:plain

 

解説

年月日を8桁で入力したものは、その年月日のシリアル値ではないため、7で割ったときの余りが一致するとは限りません。

f:id:waenavi:20191113192301j:plain

 

そこで、8桁の数値をいったん日付に変換します(参考:【Excel】TEXT関数で数値を文字列に変換するときの注意点と練習問題)。ハイフン区切りにしてから、1を掛けることによって日付にします。

  • =TEXT(A1,"0-00-00")*1

f:id:waenavi:20191113192404j:plain

 

44980は2023/2/23のシリアル値です。数値なので、表示形式を変えるだけで曜日の表示にすることが可能です。

f:id:waenavi:20191113192516j:plain

 

別解

曜日をTEXT関数で求める場合、入れ子になります。

  • =TEXT(TEXT(A1,"0-00-00")*1,"aaa")

f:id:waenavi:20191113192612j:plain

 

(3)特定の曜日だけ抽出

問題

A列にランダムに日付を入力した。このなかで土日の日付だけ抽出しなさい。

f:id:waenavi:20191113200144j:plain

 

解説

特定に曜日を抽出するのに最も簡単な方法はTEXT関数で曜日を求めることです。

  • =TEXT(A2,"aaa")

f:id:waenavi:20191113200316j:plain

 

オートフィルタを設定して、土日を選べばよいです(参考:【Excel】オートフィルタの設定と解除、条件にあう行を抽出する方法の総復習)。

f:id:waenavi:20191113200345j:plain

 

4.日本語の曜日を数値に変換する

問題

セルA1に「水」と入力した。日~土曜日を1~7の数値とするとき、セルA1に対応する数値を求めなさい。

f:id:waenavi:20191113200522j:plain

 

解説

数値を日本語の曜日にするには表示形式を変えればよいですが、逆に、日本語の曜日を数値にする関数はありません。そこで、FIND関数を使って、「日月火水木金土」から検索します(参考:【Excel】LEN関数は文字数、FIND関数は左から何文字目にあるかを数える関数である)。

f:id:waenavi:20191113201425j:plain

 

これで、日~土を1~7の数値にすることができます。

  • =FIND(A1,"日月火水木金土")

f:id:waenavi:20191113201550j:plain

 

5.土曜日が7の倍数であることを利用する

(1)土日の判定

問題

A列に連続する日付を入力した。土曜日または日曜日の場合は0、それ以外の場合は1と表示しなさい。また、土曜日または日曜日の場合は休日、それ以外は空欄にしなさい。

f:id:waenavi:20191113202118j:plain

 

解説

前述のとおり、日付を7で割ったときの余りが曜日に対応しています。

f:id:waenavi:20191113172257j:plain

 

そこで、余りを求めるMOD関数を用いて曜日の判定をする方法があります。ただし、MODは余りなので、土曜日は7ではなく0になります。原則として、土曜日=0であることを利用する場合は、MOD関数を使います。

f:id:waenavi:20191113202646j:plain

 

日付を7で割ると、その余りは0~6になります(表示形式を標準にする。以下、省略)。

  • =MOD(A1,7)

f:id:waenavi:20191113202821j:plain

 

これが2以上であれば平日です。TRUEは平日、FALSEは土日です。

  • =MOD(A1,7)>=2

f:id:waenavi:20191113203158j:plain

 

これに1を掛けたら、平日が1、土日は0になります(参考:【Excel】IF関数が使えない原因は「比較演算」の練習不足である)。

  • =(MOD(A1,7)>=2)*1

f:id:waenavi:20191113203254j:plain

 

ちなみに、この列を合計すると平日の日数となります。

f:id:waenavi:20191113203440j:plain

 

TRUE/FALSEを別の文字列に変換するには、IF関数を使います。

  • =IF(MOD(A1,7)>=2,"","休日")
  • =IF(MOD(A1,7)<=1,"休日","")

f:id:waenavi:20191113203530j:plain

 

(2)2つの日付の曜日一致判定

問題

セルA1に入力した日付とセルB1に入力した日付の曜日が一致すればTRUE、異なる場合はFALSEと表示しなさい。また、曜日が一致した場合に「〇」と表示しなさい。

f:id:waenavi:20191113204219j:plain

 

解説

曜日が一致するとき、7で割ったときの余りが一致します。7で割ったときの余りが一致する場合、引き算をすると余りが消えて7の倍数になります。

  • 7a+x-(7b+x) = 7a-7b = 7(a-b)

したがって、2つの曜日が一致するとき、7で割ったときの余りが0になります。一致すればTRUE、不一致ならFALSEです。

  • =MOD(A1-B1,7)=0

f:id:waenavi:20191113204425j:plain

 

TRUE/FALSEを別の文字列に変換するには、IF関数を使います。

  • =IF(MOD(A1-B1,7),"","〇")

f:id:waenavi:20191113204605j:plain

f:id:waenavi:20191113204609j:plain

 

(3)CEILING関数、FLOOR関数

土曜日が7の倍数であることから、日付(シリアル値)を、CEILING関数を用いて7の倍数で切り上げたり、FLOOR関数を用いて7の倍数で切り捨てると土曜日になります。これを利用して、特定の曜日の日付を求めることができます。詳しくは別の記事で解説しています。

 

6.WEEKDAY関数で曜日を判定する

(1)曜日の判定

問題

A列に入力した日付が木曜日であればTRUE、それ以外はFALSEとしなさい。

f:id:waenavi:20191113211656j:plain

 

解説

WEEKDAY関数は引数が2つありますが、第2引数を省略したときは、日~土曜日を1~7の数値で返します。

  • WEEKDAY(シリアル値, [種類])

MOD関数との違いは土曜日が0か7かというだけであって、他は変わりません。7で割ったときの余りとほぼ同じと言えます。

f:id:waenavi:20191113211827j:plain

 

単に日付から曜日を求めるだけであれば、前述のように表示形式またはTEXT関数を使いますが、曜日(文字列)ではなく数値を返すということは、数値でなければならない状況のときにWEEKDAY関数を使います。

  • 四則演算(足し算・引き算・掛け算・割り算)で使用する場合
  • 条件式で利用する場合
  • 数値を引数とする関数を使う場合

木曜日であれば、WEEKDAY=3です。TRUEであれば木曜日です。

  • =WEEKDAY(A1)=3

f:id:waenavi:20191113212146j:plain

 

各曜日の条件式は次の通りです。

  • 日曜日・・・WEEKDAY(日付)=1
  • 月曜日・・・WEEKDAY(日付)=2
  • 火曜日・・・WEEKDAY(日付)=3
  • 水曜日・・・WEEKDAY(日付)=4
  • 木曜日・・・WEEKDAY(日付)=5
  • 金曜日・・・WEEKDAY(日付)=6
  • 土曜日・・・WEEKDAY(日付)=7

 

(2)複数の曜日の判定

問題

A列に入力した日付が火曜または水曜の場合に*印をつけなさい。

f:id:waenavi:20191113212336j:plain

 

解説

火曜または水曜ということは、WEEKDAYが3または4ということです。OR関数を使います。

  • =IF(OR(WEEKDAY(A1)=3,WEEKDAY(A1)=4),"*","")

f:id:waenavi:20191113212444j:plain

 

(3)他の関数と併用して判定する

問題

A列に入力した日付が月・水・金のいずれかであれば「会議」、それ以外は空白としなさい。

f:id:waenavi:20191113212532j:plain

 

解説

WEEKDAYが偶数であれば月水金です。

f:id:waenavi:20191113212633j:plain

 

偶数かどうかを判定する関数はISEVENです(奇数はISODD)。

  • =IF(ISEVEN(WEEKDAY(A2)),"会議","")

f:id:waenavi:20191113212931j:plain

 

ISEVEN関数やISODD関数のほか、MOD、INT、PRODUCTなど、引数が数値でなければならない関数はたくさんあります。このような関数の引数として曜日を使うときは必ずWEEKDAY関数を使います。

 

(4)第2引数を指定する

問題

A列に入力した日付が火曜または水曜の場合に*印をつけなさい。

f:id:waenavi:20191113212336j:plain

 

解説

さきほどと同じ問題ですが、WEEKDAY関数の第2引数を使うと数式が短くなります。WEEKDAY関数の第2引数を指定すると曜日によって返る値が変わります。

  • 1または省略・・・1 (日曜) ~ 7 (土曜)
  • 2・・・1 (月曜)~ 7 (日曜)
  • 3・・・0 (月曜)~ 6 (日曜)
  • 11・・・1 (月曜)~ 7 (日曜)
  • 12・・・1 (火曜)~ 7 (月曜)
  • 13・・・1 (水曜)~ 7 (火曜)
  • 14・・・1 (木曜)~ 7 (水曜)
  • 15・・・1 (金曜)~ 7 (木曜)
  • 16・・・1 (土曜)~ 7 (金曜)
  • 17・・・1 (日曜)~ 7 (土曜)

このなかで、火・水が右端または左端に寄っているものを探します。12か14が使えます。

f:id:waenavi:20191113213503j:plain

 

第2引数を12とするとWEEKDAYが2以下であれば火または水です。

  • =IF(WEEKDAY(A1,12)<=2,"*","")

f:id:waenavi:20191113213639j:plain

 

第2引数を14とするとWEEKDAYが6以上であれば火または水です。

  • =IF(WEEKDAY(A1,14)>=6,"*","")

f:id:waenavi:20191113213743j:plain

 

7.WEEKDAY関数で曜日を別の文字列に変換する

(1)MID関数を使う方法

問題

A列に入力した日付が月曜・木曜の場合は「A」、火曜・金曜の場合は「B」、水曜・土曜の場合は「C」、日曜の場合は「S」と表示しなさい。

f:id:waenavi:20191113214816j:plain

 

解説

MID関数は、1から始まる整数値を他の1文字に変換するときに使います(参考:【Excel】LEFT関数、RIGHT関数、MID関数で文字列を抽出する方法の基本例題)。WEEKDAY関数の戻り値は1~7なので、MID関数の開始位置(第2引数)として使えます。

  • =MID("日月火水木金土",WEEKDAY,1)

f:id:waenavi:20191113214529j:plain

 

日曜日から順に文字を連結して(SABCABC)、MIDで1文字抽出します。

  • =MID("SABCABC",WEEKDAY(A1),1)

f:id:waenavi:20191113214623j:plain

f:id:waenavi:20191113214746j:plain

 

(2)CHOOSE関数を使う方法

問題

月曜と木曜を「塾」、水曜を「英会話」、日曜を「スイミング」にして、それ以外を空欄にしなさい。

f:id:waenavi:20191113214850j:plain

 

解説

MID関数は1文字だけ抽出するときに使いますが、2文字以上の場合はCHOOSE関数を使います。

  • =CHOOSE(WEEKDAY,日,月,火,水,木,金,土)

第1引数にWEEKDAYを入れて、第2引数以降に日曜日から順に並べます。

  • =CHOOSE(WEEKDAY(A1),"","塾","","英会話","塾","","スイミング")

f:id:waenavi:20191113215116j:plain

 

8.曜日の色分け、条件付き書式

WEEKDAY関数を用いて、曜日で塗りつぶし等の書式設定をすることができます。これについてはこちらの記事をご覧ください。

 

9.補足:曜日がずれる件

Excelは、1900年2月28日と1900年3月1日の間に、実際には存在しない1900年2月29日が存在するバグがあります。このため、1900年1~2月の日付と曜日が1日ずれています。

f:id:waenavi:20191113215647j:plain

 

この点について、曜日のずれを直すために、Excelオプションの詳細設定で「1904年から計算する」にチェックを入れることを推奨するサイトがありますが、絶対にやめてください。チェックが入っている場合は必ず外してください。

f:id:waenavi:20191113215859j:plain

 

1900年1月1日(シリアル値が1)は本当は月曜日なのですが、曜日がずれたことによってExcel上では日曜日として扱われ、シリアル値1~7が日曜~土曜に対応しています。わざとかどうかは分かりませんが、WEEKDAY関数をはじめとする曜日の計算は、曜日を1日ずらしたことによって可能となっているのです。アメリカのカレンダーが日曜始まりであることとも一致します。

1900年1~2月の曜日は正しく判定できませんが、1900年3月以降に関しては全く問題ありません。

f:id:waenavi:20191113220427j:plain

 


解説は以上です。


 


Copyright(C)2018-2019 waenavi, All rights reserved. [www.waenavi.com 定礎 平成三十年八月]