この記事で書きましたように、火事になりかねない状況を放置などありましたので、【ロイヤルバス】、【エイブル】ご利用時は、ご注意下さい。

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

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



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



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



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


=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になります。



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


=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),"日","月","火","水","木","金","土"))

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



方法その3
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で関数を使い、自動で日付を入力されるようにする(勤務表の作り方、その1)

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

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

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

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

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

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

スマホなどのモバイルなどでも見られている方へ

このブログは、Windows10で23インチ画面のデスクトップパソコン(自作)を使用し、作成・確認をしています。
また、今の所、パソコンでこのブログを見られている方の方が多いようなので、パソコンで見る事を想定してブログの作成をしています。

その為、スマホなどを使って見ると見辛いかな?と思う表示になってしまう記事が一部あります。
見辛くなってしまい、すみません。<(_ _)>

この記事へのコメント