-PR-

解決済みの質問

質問No.3904355
困ってます
困ってます
お気に入り投稿に追加する (0人が追加しました)
回答数3
閲覧数2471
Excelで作る出勤管理表/カレンダー
はじめてこちらで質問させて頂きますsansian3と申します、
どうぞ宜しくお願い致します。

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

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

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

Aパターン…水曜定休・第1.3火曜隔週
Bパターン…水曜定休・第2.4火曜隔週
Cパターン…日曜定休・第1.3土曜隔週
Dパターン…日曜定休・第2.4土曜隔週
Eパターン…月曜定休・第1.3火曜隔週

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

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

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

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


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

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

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

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

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

以上です、私の知識不足でどうしても解決が出来ません、
ご教授宜しくお願い致します!
投稿日時 - 2008-03-29 11:49:44

質問者が選んだベストアンサー

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

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

列は問題なくつくれますよね
行は日付と曜日なので、
案1・・・A列に日付、B列に数式「=text(A1,"aaa")」を入れる
案2・・・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)」

おおまかにこんな感じでいいのではないでしょうか
投稿日時 - 2008-03-29 12:58:52
この回答を支持する
(現在0人が支持しています)
お礼
大変詳細なご回答を頂き誠にありがとうございます、
おかげ様でExcelのデータが出来上がって参りました。

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

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

ずっと悩んでおりましたが解決致しました、
本当にありがとうございました。
投稿日時 - 2008-03-29 15:55:35

ベストアンサー以外の回答 (2)

回答No.3
#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と入力)
投稿日時 - 2008-03-29 14:40:43
この回答を支持する
(現在0人が支持しています)
お礼
2回にも渡り詳細なご回答ありがとうございます、
おかげ様でExcelのデータが出来上がって参りました。

歴月締めじゃないのでとても紛らわしく、
悩んでおりましたが解決致しました、
本当にありがとうございました。
投稿日時 - 2008-03-29 15:51:56
回答No.1
とりあえず、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を選択し、表示書式を「d"日("aaa")";;」として右寄せ。

これで、A1に年、B1に月を入れると、その年月の日付がA列に曜日つきで表示されます。
大の月、小の月、閏年に対応して、無い日付はブランクになります。
(小の月の31日、2月の29や閏年の30日以降がブランクになります)
投稿日時 - 2008-03-29 12:51:55
この回答を支持する
(現在0人が支持しています)
この質問は役に立ちましたか?
0人が「このQ&Aが役に立った」と投票しています
もっと聞いてみる

関連するQ&A

この他の関連するQ&Aをキーワードで探す

回答募集中

同じカテゴリの人気Q&Aランキング

-PR-

特集

背筋がゾクゾクする心霊体験を要チェック!

お城、ボート、ツリーハウス、ユニークな物件満載!

発見や気づきのある調査結果をお届け!

このQ&Aの関連キーワード

同じカテゴリの人気Q&Aランキング

-PR-

ピックアップ

-PR-