Excelで作る出勤管理表／カレンダー

質問

はじめてこちらで質問させて頂きますsansian3と申します、
どうぞ宜しくお願い致します。

私は現在、150人規模の会社で総務人事を担当しており、給与計算をしています。
今までのペーパーによるやり取りに限界を感じ、Excel2003を利用して
従業員の出勤管理を行いたいと思っております。

具体的には、タイムカードを無くす事が主な目的なのですが、
私の会社は営業が中心で、正社員とパートで定休日がバラバラ、
そして年間休日制(合計92日)でカレンダーとの連動を取るのがとても難しい会社です。

休日パターンにつきましては、

Ａパターン…水曜定休・第1.3火曜隔週
Ｂパターン…水曜定休・第2.4火曜隔週
Ｃパターン…日曜定休・第1.3土曜隔週
Ｄパターン…日曜定休・第2.4土曜隔週
Ｅパターン…月曜定休・第1.3火曜隔週

...以下、Ｓまで全19パターン。

カレンダー年度は4月開始～3月終わり、
出勤タイムカードの締め日は、毎月16日開始～15日締め。

ＧＷ・夏期休暇・年末年始連続休暇有り、
毎年パターン毎の休日割り振りは、
上記の19パターンを基準として作成しています。

尚、セルに社員番号を入れると、Excelの社員マスターからVLOOKUP関数で
氏名・所属・休日パターンが出てくるようにはなっています。

このとっても問題だらけの状況下で私が作成したいExcelの出金管理なのですが…

1.Ａ４縦、列に項目(日にち・曜日・残業時間・有休等勤怠)
行に日付・曜日(4/16水・4/17木・4/18金...)

2.処理年月を入力すると、日付と曜日が自動的に変わる

3.社員番号を入れると、その社員の休日パターンの定休日(年間休日日)行が
赤く塗りつぶされる

上記の作成したExcelデータをサーバーにアップして、
従業員150人がダウンロードして各々が処理月と社員番号を打ち
印刷する流れを考えております。

以上です、私の知識不足でどうしても解決が出来ません、
ご教授宜しくお願い致します！

回答No.2

bdr · ベストアンサー

「各自印刷をして、手書をする」という運用を前提にしてアドバイスさせていただきます

> 1.Ａ４縦、列に項目(日にち・曜日・残業時間・有休等勤怠)行に日付・曜日(4/16水・4/17木・4/18金...)

列は問題なくつくれますよね
行は日付と曜日なので、
案１・・・A列に日付、B列に数式「=text(A1,"aaa")」を入れる
案２・・・A列に日付、A列のセルの書式設定-表示形式をユーザー設定で「m/d aaa」に変更する

> 2.処理年月を入力すると、日付と曜日が自動的に変わる
処理年月は、締日の年月でしょうか？開始の年月でしょうか？
仮に締日の年月を前提に
年のセルと月のセルを別にしたほうがわかりやすいと思います
開始日のセルに「=date(年のセル,月のセル-1,15)
開始日以降のセルは、「=上のセル+1」で31行作成します
29行目、30行目、31行目のセルに条件付書式を設定します
29行目：数式が=(DAY(29行目のセル)>15)・・・フォント：白、塗潰し：白
30行目：数式が=(DAY(30行目のセル)>15)・・・フォント：白、塗潰し：白
31行目：数式が=(DAY(31行目のセル)>15)・・・フォント：白、塗潰し：白

> 3.社員番号を入れると、その社員の休日パターンの定休日(年間休日日)行が赤く塗りつぶされる
別のシート（仮称：社員番号一覧）に社員番号と休日パターンの表を作成する（休日はパターンはA,bではなく、1,2の数字にしておく。つまりA->1、B->2・・・として登録しておく）
更に別のシート（仮称：カレンダー）に行は日付（1年分）、列には休日パターンの表を作成し、休日パターンごとに休日の日に1を設定しておく
社員番号を入力した際に、vlookupを使用して「社員番号一覧」から休日パターンを取得するようにする
日付の1行目～28行目まで条件付書式の設定
数式が「=(vlookup(日付のセル,カレンダーの範囲,取得した休日パターン+1,false)=1)」
を設定する
日付の29行目以降は、2.で設定した条件付書式に追加で
数式が「=(vlookup(日付のセル,カレンダーの範囲,取得した休日パターン+1,false)=1)」

おおまかにこんな感じでいいのではないでしょうか

お礼

大変詳細なご回答を頂き誠にありがとうございます、
おかげ様でExcelのデータが出来上がって参りました。

条件付き書式もアイデア次第でこんなに広がるんですね、
先程カレンダーのシートを作成し、試しに条件付き書式に
リンクさせたのですが、私のExcel2003だけなのでしょうか、
「条件付き書式は他シートの参照は出来ません」という
エラーメッセージが出てきてしまいました。

しかし同じシート内にもたくさん行列が有りますので
そちらにカレンダーを作成致しました。

ずっと悩んでおりましたが解決致しました、
本当にありがとうございました。

回答No.3

noname#79209

#1です。ゴメンナサイ。
質問1と2については、16日スタート、翌月15日締めでしたね、
そこで、A3は
=DATEVALUE(IF(VALUE($A$1)>1900,"","H")&$A$1&"/"&$B$1&"/16")
A4に
=A3+1　として、A30までコピー。
A31に
=IF(OR(A30=0,DAY(A30+1)>15),0,A30+1)として、A33までコピー。

これで、クリアされたとハズです。
ただし、和暦で入力する場合は、
A3の数式を、
=DATEVALUE(IF(A1>1900,"","H")&A1&"/"&B1&"/15")
とすれば両対応できます。（ただしH20でなく20と入力）

お礼

2回にも渡り詳細なご回答ありがとうございます、
おかげ様でExcelのデータが出来上がって参りました。

歴月締めじゃないのでとても紛らわしく、
悩んでおりましたが解決致しました、
本当にありがとうございました。

回答No.1

noname#79209

とりあえず、1.について
A1に年、B1に月を入れるとして、
A1の表示書式を「0000"年";;」、B1の表示書式を「0"月";;」
2行目は項目名の行。
A3：
　=DATE($A$1,$B$1,1)
A4～A30：　
A4に「=A3+1」として、A30までコピー
A31：
　「=IF(DAY(A30+1)<4,0,A30+1)」としてA33までコピー
A3～A33を選択し、表示書式を「ｄ"日("aaa")";;」として右寄せ。

これで、A1に年、B1に月を入れると、その年月の日付がA列に曜日つきで表示されます。
大の月、小の月、閏年に対応して、無い日付はブランクになります。
（小の月の31日、2月の29や閏年の30日以降がブランクになります）