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

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)

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

記事の内容以外の事で私に伝えたい事などありましたら、こちらの記事のコメント欄をご利用下さい。

ウェブリブログのリニューアル後、文字数制限が何字なのか不明です。
文字数制限に引っかかるようでしたら、何回かに分けてコメントして下さい。

また、詐欺サイトと思うアドレスなどをコメント欄に書き込まれる方がいらっしゃるので、コメントの公開は私の承認制に設定しております。
コメントの公開まで、しばらくお時間を頂く事になってしまいますが、ご了承下さい。
コメント時にエラーが出てコメントができない場合

私が設定している制限事項に該当しない書き込みのハズなのに、エラーが出てコメントが書き込めない場合があります。

制限事項に該当しない書き込みでもエラーが出る条件がイマイチわかっていませんが、、、何度か試していると、 【お名前の所に@など特殊文字を使用する】 や 【認証コードを1番最初に入力する】 をするとエラーが出ることが多いです。

一度エーラーが出ると、最初から入力し直しても、しつこくエラーが出続ける場合が多いので、一旦ページをブラウザ(Google ChromeやInternet Explorerなど)の×をクリックして該当のタブを閉じて、ページを開き直してから書き込む方がエラーが解消しやすいです。
(コメントはアプリの中にあるメモ帳などに一旦コピーペーストしておくと書き直ししなくて済みます)

お名前、メールアドレス、ホームページアドレスの3つは未入力可に設定してあるので、コメントと認証コードだけ入力すればコメントは書き込めます。
(試した中では、これが一番エラーが出にくいです)
お名前
メールアドレス
ホームページアドレス
コメント[必須入力]
認証コード:[必須入力]

※画像の中の文字を半角で入力してください。

※ブログオーナーが承認したコメントのみ表示されます。