ベストアンサー

Excelにて出勤日数を自動で振り分け

2015/01/10 03:11

こんばんわ。　初めて質問しますよろしくお願いいたします。別シートに記録されている従業員の出勤日（スクリーンショットの下の票）の数値を自動的に引き出して上の票の週別出勤の状態を色で表示させたいのです。（色分けできるならベターですが従業員全員単色でもOK）感覚的には下のように色付けを、　従業員の出勤日を入力した時点で発生させたくおもってます。最近時間管理等を行う様になりなれないエクセルを打ち込みを行って関数（？）を覚え始めたばかりなのでいろいろ試してはみてるのですがピンとくるものがなく悩んでます。変な質問ですがどうか教えてもらえないでしょうか？

fwkatsu
お礼率100% (1/1)

Excel（エクセル）
回答数1
ありがとう数2

みんなの回答 （1）
専門家の回答

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

ベストアンサー

puddingeater
ベストアンサー率77% (28/36)

2015/01/11 03:43 回答No.1

色分けはしんどそうですね。単色ならすぐできそうです。次のような方針で。 [step 1] 週別出勤表の各週の開始日付・終了日付を別の行に用意する。 [step 2] 週別出勤表の条件に合うものが出勤日表に何行あるかカウントし、週別出勤表に表示させる。 [step 3] 週別出勤表のセルの値が0であれば無色or白色、1以上であれば緑色に着色する（文字色も）。質問の画像を基本にして書きます。どのセルにおいても、日付を入れるセルは必ず表示形式の分類が「日付」になるようにしてください（書式設定）。 [step 1] 週の開始・終了日付の行が欲しいのですが、5～6行目が開いているので説明ではここを使います。 (1) セルB5に最初の週の開始日付(1/4)を記入。 (2) セルC5（2週間目の開始日付）に　　=B6+1 　　と入力。このセルをコピーし、D5～I5（右端の列まで）にペースト。 (3) セルB6（最初の週の終了日付）に　　=B5+6 　　と入力。このセルをコピーし、C6～I6（右端の列まで）にペースト。 (4) セルB1に　　=TEXT(B5,"m/d")&"-"&TEXT(B6,"m/d") 　　と入力。このセルをコピーし、C1～I1（右端の列まで）にペースト。　　1行目の週は直接記入するのでなく、開始・終了日付の行から組み立てるように　　した方がよいかと思います。 [step 2] (1) セルB2に　　=SUMPRODUCT(($A$8:$A$12=$A2)*($D$8:$D$12>=B$5)*($B$8:$B$12<=B$6)) 　　と入力します。　　見かけがややこしいですが、次のような構造になっています。　　=SUMPRODUCT((条件1)*(条件2)*(条件3)) 　　条件1： $A$8:$A$12=$A2 → 出勤日表の従業員名が週別出勤表と同じ。　　条件2： $D$8:$D$12>=B$5 → 出勤日表の「～まで」の日付が週の開始日付以降。　　条件3： $B$8:$B$12<=B$6 → 出勤日表の「～から」の日付が週の終了日付以前。　　関数内の条件をつないでいる * は「なお且つ」の意味になります。　　SUMPRODUCT()は本来セル範囲同士を掛け算して足し合わせるという関数ですが、　　今回のように複合条件を与えた場合は、複合条件に合うものの数が返ります。　　参考URLを付けてます。 (2) セルB2をコピーし、B2：I4の範囲にペースト。 [step 3] セル範囲B2：I4に次のような条件付き書式を設定します。　条件付き書式1：セルの値が0なら、そのセルの文字色と背景を白色にする。　条件付き書式2：セルの値が1以上なら、そのセルの文字色と背景を緑色（または好きな色）にする。実際にやってみた画像を添付しています。途中結果の確認のため文字色は黒になっています。ところで、現状では出勤日の日付を空欄にしてたり 2/99 みたいなありえない日付を記入しても、それなりに結果が出てしまいますので気を付けてください。こういうのはセルに入力規則を設定してチェックすべきだと思ったので、関数では何もしていません。