Excelで関数を使い、自動で曜日を入力されるようにする（勤務表の作り方、その2）

前回のExcelで関数を使い、自動で日付を入力されるようにする（勤務表の作り方、その１）では、関数を使い、基準となる年月日を入力するだけで、日付が自動で入力される方法を書きましたが、これと同じように、関数を使い、年月日を入力するだけで、曜日が自動で入力されるようにする方法があります。（前回の続きです）

日付の横に曜日が入力されていると思いますが、これも関数を使い、左上の年月日を基準として自動で曜日が入力されています。

例えば、A1のセルを　「2016/1/1」　から　「2016/2/1」　に入力し直すと、29日までの曜日までが入力されます。
29日以降がある月は、自動で曜日を入力し、29日以降が無い月は、自動で曜日を消すようにするには、前回のExcelで関数を使い、自動で日付を入力されるようにする（勤務表の作り方、その１）で書いた方法で、29日以降がある月は、自動で日付を入力し、29日以降が無い月は、自動で日付を消すようにする必要があります。



方法その１
日付の横のセルに、上のセルから順に、下のように入力します。


=CHOOSE(WEEKDAY(A1),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+1),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+2),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+3),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+4),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+5),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+6),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+7),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+8),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+9),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+10),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+11),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+12),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+13),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+14),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+15),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+16),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+17),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+18),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+19),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+20),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+21),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+22),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+23),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+24),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+25),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+26),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+27),"日","月","火","水","木","金","土")
=IF(A32="","",CHOOSE(WEEKDAY(A1+28),"日","月","火","水","木","金","土"))
=IF(A33="","",CHOOSE(WEEKDAY(A1+29),"日","月","火","水","木","金","土"))
=IF(A34="","",CHOOSE(WEEKDAY(A1+30),"日","月","火","水","木","金","土"))


注意事項
29日～31日の曜日の自動入力は、基準となる年月日と、日付が入ったセルを参照しています。
また、日付が入っていなければ、曜日は入力せずに空白になります。

その為、29日～31日の曜日は、日付の入っていないセルを指定していると、曜日が表示されずに空白になってしまいます。

例えば、29日がA34に入力されている場合は、曜日の入力は、
=IF(A34="","",CHOOSE(WEEKDAY(A1+28),"日","月","火","水","木","金","土"))
となり、A32の所がA34になります。

また、基準となる年月日がA1以外に入力されている場合は、間違った曜日が入力されるので、注意が必要です。

例えば、基準となる年月日が、B1に入力されている場合は、
=CHOOSE(WEEKDAY(B1),"日","月","火","水","木","金","土")
や
=IF(A32="","",CHOOSE(WEEKDAY(B1+28),"日","月","火","水","木","金","土"))
となり、A1の所が、B1になります。



方法その２
日付の横のセルに、上のセルから順に、下のように入力します。


=CHOOSE(WEEKDAY(A1),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+1),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+2),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+3),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+4),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+5),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+6),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+1),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+2),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+3),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+4),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+5),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+6),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+1),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+2),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+3),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+4),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+5),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+6),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+1),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+2),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+3),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+4),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+5),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+6),"日","月","火","水","木","金","土")
=IF(A32="","",CHOOSE(WEEKDAY(A1),"日","月","火","水","木","金","土"))
=IF(A33="","",CHOOSE(WEEKDAY(A1+1),"日","月","火","水","木","金","土"))
=IF(A34="","",CHOOSE(WEEKDAY(A1+2),"日","月","火","水","木","金","土"))

注意事項としては、方法その１と同じです。



方法その３
29日～31日の曜日が日付に連動して、入力されたり、消えたりしなくても良い場合は、29日～31日も下のようにIF関数を使わずに入力します。


=CHOOSE(WEEKDAY(A1),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+1),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+2),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+3),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+4),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+5),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+6),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+7),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+8),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+9),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+10),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+11),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+12),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+13),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+14),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+15),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+16),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+17),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+18),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+19),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+20),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+21),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+22),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+23),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+24),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+25),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+26),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+27),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+28),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+29),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+30),"日","月","火","水","木","金","土")
このように入力するか、


=CHOOSE(WEEKDAY(A1),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+1),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+2),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+3),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+4),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+5),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+6),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+1),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+2),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+3),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+4),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+5),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+6),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+1),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+2),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+3),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+4),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+5),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+6),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+1),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+2),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+3),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+4),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+5),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+6),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+1),"日","月","火","水","木","金","土")
=CHOOSE(WEEKDAY(A1+2),"日","月","火","水","木","金","土")
このように、入力するかになります。



関連記事
Excelで関数を使い、自動で日付を入力されるようにする（勤務表の作り方、その１）

Excelで関数を使い、自動で定時の出社や退社時間を入力されるようにする（勤務表の作り方、その３）

Excelで関数を使い、自動で労働時間を入力されるようにする（勤務表の作り方、その４）

Excelで関数を使い、自動で労働時間を合計されるようにする（勤務表の作り方、その５）

Excelで関数を使い、自動で残業時間が計算されるようにする（勤務表の作り方、その６）

Excelで関数を使い、自動で祝日が入力されるようにする（勤務表の作り方、その７）

Excelで関数を使い、祝日の出退社時間が自動で空白になるようにする（勤務表の作り方、その８）