元データから連続5日以上だった人を抽出したい

以下のような元データがあります。
↓
https://gyazo.com/0da64cc8d1d4727b97973cae0ffa6970
※システム抽出

土日含め、連続7日以上欠勤が続いている人を抽出したいのですがピボットやSUMIF等考えたのですが良い案が浮かびません。
元データから該当する人を抽出するには、どのような数式を組めば良いでしょうか？

何卒ご教授願います。

No.5 ベストアンサー 回答者： goomania 回答日時： 2021/11/27 18:41

No.4です。

大変失礼しました。添付画像に誤りがあり、データに矛盾が生じたものになっています。(スタッフコードとスタッフ名がバラバラです)
また、数式も説明と異なったものになっています。
修正したものを投稿します。
No4の添付画像を差替えたものとしてご理解ください。

この回答へのお礼

おはようございます。

この度はご教授頂きましてありがとうございました。
数式で実際に試し、無事対象となる人が判定できました。

本当にありがとうございました。

お礼日時：2021/11/30 08:34

No.6 回答者： goomania 回答日時： 2021/11/30 18:44

No.4、5です。

>この度はご教授頂きましてありがとうございました。
>数式で実際に試し、無事対象となる人が判定できました。

おめでとうございます。
お役に立ててなによりです。

この回答へのお礼

こんにちわ。
大変勉強になりました。
ありがとうございました。

お礼日時：2021/12/01 12:18

No.4 回答者： goomania 回答日時： 2021/11/27 16:59

>元データから該当する人を抽出する

No.3さんも指摘されておられますが、抽出の意味をどうとらえるかで対応が変わります。
取り敢えず、ある人が「土日含め、連続7日以上欠勤が続いている」状態である該当日付に〇印をつけるという方法で該当者を明示することにしてみました。

便宜上、ご質問者が示された表を「勤務管理表」と呼びます。

勤務管理表の適当な場所に、祝日および企業独自の休業日を一覧として作成し、その範囲に「祝日」という名前をつけておくものとします。

添付画像①をご覧ください。祝日・休業日の一覧には12/28を「御用納め」として12/29～1/3までを年末年始の休業としています。また2022年の祝日を登録してあります。

なお、「勤務管理表」の日付はシリアル値(年号まで含めた値)で入力されているものとし、表示形式を「mm/dd」として「11/19」のように表示しているものとします。

ご質問者が示された表の右列に「連続欠勤」という列をつくり、そこに〇印を表示することにします。画像でい言えばI2セルに

=IF((SUMPRODUCT((F2-7+COLUMN(A:G)=$F$2:$F$8)*($D$2:$D$8=D2)*($G$2:$G$8=25))+SUMPRODUCT((WEEKDAY(F2-7+COLUMN(A:G),3)>4)*1)+SUMPRODUCT((F2-7+COLUMN(A:G)=祝日)*(WEEKDAY(祝日,3)<5)))>=7,"〇","")

という数式を記述し、下方向へコピーしています。

考え方は、No.3さんが既にお示しになっておられます。

「勤務管理表」に入力されている日付から遡って「連続した日付7日分」を予め用意し、その7日間を「勤務管理表に欠勤と入力された日」、「土曜日・日曜日」、「祝日・休業日」に該当しているかを各々調べその合計日数が7になれば、連続した7日間全ての日において出勤していない(連続欠勤)ということになります。

前提としては
(1)「勤務管理表に入力された日」には土日祝日、会社の休業日はない
(2)「勤務管理表」には欠勤以外(ex.早退や遅刻など)も記録されるが、記事コード25が「欠勤(１日)」を意味する
といったところでしょうか。

これで、「連続欠勤」〇印でフィルターをかければ、該当者が抽出できます。

しかし、この抽出ルールだと困ったことが起きます。
添付画像➁をご覧ください。
年末年始は12/29～1/3まで６日間連続して会社の休業日、祝日、土日が続くため、山田 一郎さんは1/4に1日欠勤しただけで７日間連続欠勤したことになってしまいます。

このような事態を回避するためには、単純に「土日祝日を含んで７日連続欠勤」という抽出ルールではなく、「土日祝日を挟んで７日連続欠勤」というような抽出ルールにするなどの工夫が必要かも知れません。

この回答へのお礼

こんにちわ。

この度は分かりやすくご説明頂きまして
ありがとうございます。
とてもわかりやすいご説明で、大変勉強になります。

前提条件は問題ないので、早速週明け計算式を入れて試してみたいと思います。

お礼日時：2021/11/27 17:33

No.3 回答者： ママチャリ 回答日時： 2021/11/27 11:42

「元データから該当する人を抽出したい」とのことですが、抽出とは、どのレベルを期待していますか？

①マクロ一発で、別シートに抽出したい。
②関数で、とりあえず、該当者か否かの判別ができればOK。

もし②程度で良いのであれば・・・、
Countifs関数で、スタッフコードが一致していて、対象日が対象日以上、かつ、対象日＋6日以下の件数をカウントします。
さらに、休日データからも同じ期間の件数をカウントします。
最終的に、上記の合計が7以上のものが対象者となるはずです。

この回答へのお礼

こんにちわ。
ご提示頂きましてありがとうございます。

②の関数で判断できれば問題ありません。

countifsですね。
考え方をご教授頂きありがとうございます。

お礼日時：2021/11/27 17:27

No.2 回答者： chiune 回答日時： 2021/11/26 17:05

> 土日祝日除く平日欠勤された分しか載りません

そういうことならやりたいことは（不可能とは言いませんが）無理があるように思います。
例えば2021年の11/17～11/22まで出勤しなかった場合「連続7日以上欠勤が続いている人」としてカウントするのはかなり困難です。
祝祭日だけではなく、年末年始休暇とかも勘案するとなると余計です。

せめて元データの他に休日データのシートが必要かと思います。

この回答へのお礼

ご教授頂きましてありがとうございます。
やはり難しいですか…。
別シートに休日のシートを作成することは可能です。

お礼日時：2021/11/26 17:43

No.1 回答者： chiune 回答日時： 2021/11/26 15:03

元データには土日、祝日に休んだ情報は載りますか？

この回答へのお礼

こんにちわ。
元データはあくまで、土日祝日除く平日欠勤された分しか載りません。

お礼日時：2021/11/26 16:44