はじめてこちらで質問させて頂きます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人がダウンロードして各々が処理月と社員番号を打ち
印刷する流れを考えております。
以上です、私の知識不足でどうしても解決が出来ません、
ご教授宜しくお願い致します!
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)」
おおまかにこんな感じでいいのではないでしょうか
大変詳細なご回答を頂き誠にありがとうございます、
おかげ様でExcelのデータが出来上がって参りました。
条件付き書式もアイデア次第でこんなに広がるんですね、
先程カレンダーのシートを作成し、試しに条件付き書式に
リンクさせたのですが、私のExcel2003だけなのでしょうか、
「条件付き書式は他シートの参照は出来ません」という
エラーメッセージが出てきてしまいました。
しかし同じシート内にもたくさん行列が有りますので
そちらにカレンダーを作成致しました。
ずっと悩んでおりましたが解決致しました、
本当にありがとうございました。
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と入力)
2回にも渡り詳細なご回答ありがとうございます、
おかげ様でExcelのデータが出来上がって参りました。
歴月締めじゃないのでとても紛らわしく、
悩んでおりましたが解決致しました、
本当にありがとうございました。
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日以降がブランクになります)
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- 人事・法務・広報 会社の行事などで休日労働させる時、振替休日が休日労働した日の前週or翌週以降、月をまたいだ場合の手当 3 2023/05/21 23:37
- 転職 転職活動中で2社内定があり迷っています。皆様ならAとBどちらの方が良いと思いますか?理由もお願いしま 1 2023/02/06 12:20
- 中途・キャリア 契約社員で在職中ですが、正社員目指して転職活動中です。 20代半ばの男です。 2社から内定を頂きまし 5 2022/04/29 13:39
- その他(Microsoft Office) 従業員増減対応で当番種類の増減対応な当番表 21 2022/07/19 07:30
- 労働相談 有給計算について 回答お願いします。 建設業経営です。休日は日曜のみ 従業員の有給計算について 雇い 2 2023/03/26 06:12
- 会社・職場 転職についてどっちが良いと思いますか? 私は新卒社会人一年目。女です。 現在働いている会社が凄く嫌で 3 2023/01/20 21:31
- 労働相談 飲食店勤務のスタッフの勤怠管理について 新規事業で飲食店を始めたのですが、深夜営業もあり勤怠管理が難 2 2023/04/23 15:56
- アルバイト・パート 結果として1週間無断欠勤をしてしまいました。 2 2022/10/09 08:30
- 正社員 21歳男です。接客業の休日について質問します。 接客業で副店長(正社員)で勤務してます。お店はアルバ 1 2022/09/24 01:56
- その他(悩み相談・人生相談) フリーターなのですが労働契約では週5勤務で 会社都合で月曜日が定休日になり 火曜日、日曜日が元からの 2 2023/01/04 06:25
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
時間によってファイル名が変わ...
-
Excelで、決まった行を繰り返し...
-
【マクロ】ファイル名の変更に...
-
WPS OFFICEでの縦書きについて
-
エクセルで 例えば 伊藤と名前...
-
Aというブックの1というシート...
-
【マクロ】毎回、ファイル名が...
-
エクセルの順位別一覧表の自動...
-
Excel2013のF6キー操作について
-
エクセルの条件付き書式につい...
-
エクセルで「-0.0」と表示さ...
-
エクセル 価格表から単価を呼び...
-
シートAで横に並んだ項目→シー...
-
【Microsoft Office Excel Comp...
-
西暦や和暦の表示をyyyymmdd表...
-
Excelが固まってしまった。
-
Excel元に戻す方法を教えてくだ...
-
文字列になっている時間をVBAで...
-
【関数】スペースがいくつ入っ...
-
Excelのセルを飛ばして入力する
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel 2019 のピボットテーブル...
-
[関数得意な方]教えて下さい・...
-
Excelにてある膨大なデータを管...
-
[関数について]わかる方教えて...
-
Excel初心者です。 詳しい方、...
-
excelの不要な行の削除ができな...
-
エクセル関数に詳しい方教えて...
-
INDIRECTを使わず excelで複数...
-
[オートフィルタ]で抽出された...
-
エクセルの神よ、ご回答を! エ...
-
エクセル関数に詳しい方、教え...
-
各ページの1番上の表示について
-
Excelで写真のような表を作った...
-
エクセルで不等号記号(≠)が上に...
-
数学 Tan(θ)-1/Cos(θ)について...
-
Excel 2019 は、SPILL機能があ...
-
Excelで全角を半角にしたいので...
-
条件付き書式を教えてください
-
Excel フィルターを掛けた状態...
-
[オートフィルタ]の適用範囲の...
おすすめ情報