以下のような元データがあります。
↓
https://gyazo.com/0da64cc8d1d4727b97973cae0ffa6970
※システム抽出
土日含め、連続7日以上欠勤が続いている人を抽出したいのですがピボットやSUMIF等考えたのですが良い案が浮かびません。
元データから該当する人を抽出するには、どのような数式を組めば良いでしょうか?
何卒ご教授願います。
No.4
- 回答日時:
>元データから該当する人を抽出する
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が「欠勤(1日)」を意味する
といったところでしょうか。
これで、「連続欠勤」〇印でフィルターをかければ、該当者が抽出できます。
しかし、この抽出ルールだと困ったことが起きます。
添付画像➁をご覧ください。
年末年始は12/29~1/3まで6日間連続して会社の休業日、祝日、土日が続くため、山田 一郎さんは1/4に1日欠勤しただけで7日間連続欠勤したことになってしまいます。
このような事態を回避するためには、単純に「土日祝日を含んで7日連続欠勤」という抽出ルールではなく、「土日祝日を挟んで7日連続欠勤」というような抽出ルールにするなどの工夫が必要かも知れません。
こんにちわ。
この度は分かりやすくご説明頂きまして
ありがとうございます。
とてもわかりやすいご説明で、大変勉強になります。
前提条件は問題ないので、早速週明け計算式を入れて試してみたいと思います。
No.3
- 回答日時:
「元データから該当する人を抽出したい」とのことですが、抽出とは、どのレベルを期待していますか?
①マクロ一発で、別シートに抽出したい。
②関数で、とりあえず、該当者か否かの判別ができればOK。
もし②程度で良いのであれば・・・、
Countifs関数で、スタッフコードが一致していて、対象日が対象日以上、かつ、対象日+6日以下の件数をカウントします。
さらに、休日データからも同じ期間の件数をカウントします。
最終的に、上記の合計が7以上のものが対象者となるはずです。
こんにちわ。
ご提示頂きましてありがとうございます。
②の関数で判断できれば問題ありません。
countifsですね。
考え方をご教授頂きありがとうございます。
No.2
- 回答日時:
> 土日祝日除く平日欠勤された分しか載りません
そういうことならやりたいことは(不可能とは言いませんが)無理があるように思います。
例えば2021年の11/17~11/22まで出勤しなかった場合「連続7日以上欠勤が続いている人」としてカウントするのはかなり困難です。
祝祭日だけではなく、年末年始休暇とかも勘案するとなると余計です。
せめて元データの他に休日データのシートが必要かと思います。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) 複数セルデータを別シートの単一セルにコピーしたい。(詳細をご参照ください) 1 2022/12/14 15:08
- Excel(エクセル) 日付以外のデータを抽出したいのですが、 6 2023/06/27 13:32
- Excel(エクセル) Excelマクロの差分抽出のコードを教えていただきたいです。 2 2023/03/14 11:40
- Excel(エクセル) エクセルで#以降の文字を取得したい 1 2022/03/28 13:14
- Visual Basic(VBA) Sheet2の日付をキーにオートフィルターで2023年1月のデータを抽出し、Sheet3へ書き出すた 2 2023/03/06 23:57
- Excel(エクセル) Excelマクロ 差分抽出の方法が知りたいです。 2 2023/03/07 13:25
- Visual Basic(VBA) Sheet2からオートフィルターで売上日を抽出した件数をカウントし、その件数をSheet1のセルB1 2 2023/01/12 12:24
- その他(Microsoft Office) Outlookメール 連絡先の検索について 〈 ご説明 〉 Windows PC の Outlook 1 2022/09/23 14:43
- Excel(エクセル) Excel関数、何がいけないのかわかりません。 2 2023/06/11 12:14
- Excel(エクセル) excelVBAについて。 8 2022/12/11 13:47
このQ&Aを見た人はこんなQ&Aも見ています
-
性格の違いは生まれた順番で決まる?長男長女・中間子・末っ子・一人っ子の性格の傾向
同じ環境で生まれ育っても、生まれ順で性格は違うものなのだろうか。家庭教育研究家の田宮由美さんに教えてもらった。
-
エクセル関数/連続した○◎●をカウントする関数
その他(Microsoft Office)
-
エクセルで同じ文字が3連続している回数の数え方
Excel(エクセル)
-
エクセルで連続するデータの個数だけカウントする
Excel(エクセル)
-
-
4
【エクセル】シフトで○が6個以上の場合に色を自動で変更したい
Excel(エクセル)
-
5
Excelで連続するデータの個数をカウントする方法を教えてください
Excel(エクセル)
-
6
エクセル、○が連続する回数を数えるには?
その他(コンピューター・テクノロジー)
-
7
エクセル シフト表 6連続勤務はfault""
Excel(エクセル)
-
8
【EXCEL】連続データの個数を抽出する方法を御教授ください
Excel(エクセル)
-
9
Excel 同じ値が連続している行の抽出
Excel(エクセル)
-
10
Excelで連勤の氏名を抽出する
Excel(エクセル)
-
11
Excelで連続データのカウント
Excel(エクセル)
-
12
エクセルで連続した回数を数える方法を教えてください
Excel(エクセル)
-
13
エクセル2010 同じ数字や文字が連続する数をカウントするには?
Excel(エクセル)
-
14
【エクセル】シフトで○が6個以上の場合に色を自動で変更したい Part.2
Excel(エクセル)
-
15
エクセルで同じ値が連続しているセルに色をつける方法を教えてください。
Excel(エクセル)
-
16
Excel上でのデータ数字が連番であることを確認する方法を教えてくださ
その他(Microsoft Office)
-
17
エクセルで直近の連続回数を求める
Excel(エクセル)
-
18
EXCELで条件付き書式で空白セルの時は塗りつぶし無しにする方法
Excel(エクセル)
-
19
自分の部署・担当を言うとき、どういう言い方が正しいでしょうか?? 会社のときは、弊社といいますが、部
その他(ビジネス・キャリア)
関連するカテゴリからQ&Aを探す
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
ACCESS クエリの抽出条件に他の...
-
accessの日付フィールドから前...
-
ACCESSのクエリで抽出条件「ま...
-
ACCESSの時間帯の抽出について
-
PCゲームから音声ファイルを抽...
-
ACCESS 「パラメータの入力」...
-
元データから連続5日以上だった...
-
Access テーブルを分割してエク...
-
Access 2003 iif [日付フィール...
-
アクセスでの抽出方法(日付+時...
-
最大値以外の抽出方法
-
沢山の写真データから特定の人...
-
access クエリの抽出条件を入れ...
-
エクセル2010の統計作業(フィ...
-
access 文字列で範囲指定する
-
ACCESS クエリで”1”でないもの...
-
関数式教えてください。
-
アクセス・クエリの抽出条件(...
-
accessで複数フィルタをかけた...
-
エクセルで2つの条件を満たす数...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
ACCESS クエリの抽出条件に他の...
-
ACCESS 「パラメータの入力」...
-
ACCESSのクエリで抽出条件「ま...
-
ACCESSの時間帯の抽出について
-
Access テーブルを分割してエク...
-
PCゲームから音声ファイルを抽...
-
アクセス・クエリの抽出条件(...
-
accessの日付フィールドから前...
-
エクセルのマクロで検索・抽出...
-
元データから連続5日以上だった...
-
アクセスでの抽出方法(日付+時...
-
access クエリの抽出条件を入れ...
-
ACCESS クエリで”1”でないもの...
-
ACCESSのクエリで集計
-
ACCESS クエリの抽出条件を動的...
-
クエリの進行状況を知りたい。
-
最大値以外の抽出方法
-
Access 2003 iif [日付フィール...
-
Access:クエリの複数『or条件...
-
アクセスのクエリで空白データ...
おすすめ情報