![](http://oshiete.xgoo.jp/images/v2/pc/qa/question_title.png?e8efa67)
はじめてこちらで質問させて頂きます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ランキング
-
IFとIFS関数
-
Excelを無料で使うには? パソ...
-
セルの数を求めたい
-
エクセルの数式バーのフォント...
-
Excelの表示についての質問
-
再質問です。マクロの修正箇所...
-
データチェックを行うエクセル...
-
Excelに詳しい方! B列が「日...
-
西暦や和暦の表示をyyyymmdd表...
-
Excelで50個のセルに同じ文字を...
-
Excelの数式について教えてくだ...
-
Excel VBAで全ての矢印を赤色に...
-
エクセル関数を使って
-
祝日と土曜、日曜の合計をカウ...
-
xlsxファイルを保存する際にPDF...
-
Excelについて
-
エクセルVBA、別ブックへ転記す...
-
エクセルで「ページレイアウト...
-
2列に入っているデータを1列...
-
【ExcelVBA】名前を付けて保存→...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
半角カタカナをヘボン式ローマ...
-
(マクロ)vlookupの元データを同...
-
エクセルで上位バイトのセルと...
-
exselの質問です
-
Excel 大小比較演算子による「...
-
Excel VBについての質問です。
-
エクセルの問題です。絶対値の...
-
非表示列の再表示に失敗
-
職場の人から聞かれており、こ...
-
Excel関数-文字列で自動作成さ...
-
Excelデータをコピペして、ペー...
-
ユーザー定義関数をアドイン登...
-
【マクロ】for next構文について
-
エクセルの日付を編集する
-
【マクロ】VLOOKUPにて参照元に...
-
exselで最小数で並び替える関数
-
libre 表計算ソフトの計算がう...
-
エクセルで表
-
エクセルの表で1年間の曜日を...
-
西暦和暦
おすすめ情報