Excelで連勤の氏名を抽出する

エクセルで50名程の勤怠を管理しています。
会社のルールとして14連続勤務は禁止のため日々連勤のチェックをしており、
確認日に対し連勤何日目かを任意のセルへ表示できればと考えています。
（内容はシートは添付画像をご参照ください）

尚、できれば関数が希望ですが、
VBAでもやり方あれば併せてご教示いただけますと幸いです。
宜しくお願いいたします。

（当方：WIN10 Excel2019）

No.8 回答者： goomania 回答日時： 2023/09/13 15:52

No.5,7です。

No.5の回答で前提条件に説明もれがありましたので、補足します。
前回回答の数式は「1/１」「1/2」「1/3」･･･と表示されている4行目の日付部分はセルの中身がシリアル値で、表示形式を「m/ｄ」として、「1/1」のように見せているものとして作成しています。
これが文字列だと、前回回答の数式は使用できません。

No.7 回答者： goomania 回答日時： 2023/09/12 21:29

No.5です。

前回回答はあくまでも掲出された表に基づき、連勤日数を算出するためのものです。

ご質問者は
>会社のルールとして14連続勤務は禁止のため
と仰っています。

ただ、掲出された表は「月単位」のものです。見る限り1月のデータのようですが、例えば前年の12月から本年の1月にかけて、「月越えで14連勤」になっていないかのチェックは情報がないので、チェックしようがありません。
会社のルールとしてチェックするのであれば、「月単位」のチェックでは不十分であるのは言うまでもありません。

月単位でシートを分ける場合、No.6さんのご指摘のように、前月のシートのAH列の数値を当月のシートのA列に数式で転記して、計算スタートするという方法でもよいかもしれません。そのような手当を行えば、

>できれば関数が希望ですが、･･･

というご質問者のお望みのとおり、VBAでなくても算定可能になると思います。

No.6 回答者： tatsumaru77 回答日時： 2023/09/12 20:06

No2です。

既に関数式で、回答が出ていますが、前月分を考慮していないので、前月分を考慮したマクロを回答します。

１．以下のモジュールの標準モジュールに登録します。
使用時の注意事項
①前月の連勤日数はA列に記述済みの前提です。
もし、列を変える場合は、
Const prevCol As String = "A" '前月連勤日数の列
を適切に変えてください。
②このシートのシート名は、”勤務表”としてあります。
もし修正する場合は、
Set ws = Worksheets("勤務表")
を適切に変えてください。
③出勤の記号は"○"を使用しています。（○と〇は異なります）
もし、"○"、"×"以外の文字があった場合、エラー表示して、
そのセルで止まりますので、セルの内容を確認してください。

Option Explicit
Public Sub 連勤日数算出()
Const prevCol As String = "A" '前月連勤日数の列
Dim ws As Worksheet
Dim maxrow As Long
Dim wrow As Long
Dim wcol As Long
Dim wkday As Long
Dim dd As Long
Dim mark As String
Set ws = Worksheets("勤務表")
maxrow = ws.Cells(Rows.Count, "B").End(xlUp).Row 'B列最大行
For wrow = 6 To maxrow
wkday = 0
If ws.Cells(wrow, prevCol).Value <> "" Then
wkday = ws.Cells(wrow, prevCol).Value
End If
For dd = 1 To 31
wcol = dd + 2
mark = ws.Cells(wrow, wcol).Value
If mark = "" Then Exit For
If mark = "○" Then
wkday = wkday + 1
ElseIf mark = "×" Then
wkday = 0
Else
ws.Activate
ws.Cells(wrow, wcol).Activate
MsgBox ("不正文字[" & mark & "]があります")
Exit Sub
End If
ws.Cells(wrow, "AH").Value = wkday
Next
Next
End Sub

２．上記のマクロを実行すると、AH列に連勤日数が設定されます。
当該ブックをオープンしたときに、自動的に実行したい場合は、
以下のマクロをThisWorkbookに登録します。
そうすると、オープン時に、マクロ（連勤日数算出）が実行されます。

Private Sub Workbook_Open()
Call 連勤日数算出
End Sub

不明点があれば、補足してください。

No.5 回答者： goomania 回答日時： 2023/09/12 16:50

>日々連勤のチェックをしており、確認日に対し連勤何日目か

ということは、最後の休みの(×)より後に出勤日(○)が幾つあるかを数えればよいので、添付画像で言えば、AH6セルに、

=COUNTIFS($C6:$AG6,"○",$C$4:$AG$4,">"&MAX($C$4-1,IFERROR(LOOKUP(1,0/FIND("×",$C6:$AG6),$C$4:$AG$4),0)))

という数式を入れて下方向へコピーすればよいと思います。
※○は漢数字の零ではなく、記号の○とします。

No.4 回答者： fujillin 回答日時： 2023/09/12 16:08

こんにちは

添付の図がよく識別できないので、イマイチはっきりとしませんけれど・・
・日付はＣ列からAG列までで、最後の連続している〇の数を表示する
（ご質問タイトルは「氏名を抽出」とありますが、日数を算出すると解釈）
・記入は〇か×で必ず左側セルから詰めて記入される（空白があることはない）
と解釈しました。

上記の仮定で良ければ、
AH6セルに以下の式を入力し、下方にフィルコピーでいかがでしょうか。
=IFERROR(AGGREGATE(14,6,ROW(OFFSET(A$1,,,COUNTA(C6:AG6)))/(COUNTIF(OFFSET(C6,,COUNTA(C6:AG6)-ROW(OFFSET(A$1,,,COUNTA(C6:AG6))),1,ROW(OFFSET(A$1,,,COUNTA(C6:AG6)))),"<>〇")=0),1),0)

※ 「〇」の文字はいくつか異なる字体がありますので、必ず同じ字体を用いるものとし、かつ、式中の〇もそれに合わせてください。

No.3 回答者： 河内のおやじ 回答日時： 2023/09/12 15:19

Sub 勤怠管理マクロ()

Dim ws As Worksheet
Dim mainSheet As Worksheet
Dim outputSheet As Worksheet
Dim nameColumn As Range
Dim currentDate As Date
Dim consecutiveDays As Integer
Dim outputRow As Long
Dim name As String

' メインのシート、出力先のシートを設定
Set mainSheet = ThisWorkbook.Sheets("勤怠シート") ' 勤怠を記録しているシートの名前を設定してください
Set outputSheet = ThisWorkbook.Sheets("出力シート") ' 結果を出力するシートの名前を設定してください

' 出力シートを初期化
outputSheet.Cells.Clear

' 名前が記載された列を設定（1行目の日付から50人分の名前が並んでいると仮定）
Set nameColumn = mainSheet.Range("A1:AX1") ' 列の範囲を調整

' 日付が記載された列を設定（2行目から最終行までループ）
For Each ws In ThisWorkbook.Sheets
If ws.Name <> outputSheet.Name And ws.Name <> mainSheet.Name Then
For Each cell In nameColumn
consecutiveDays = 0
name = cell.Value
For Each dateCell In cell.Offset(1, 0).Resize(mainSheet.UsedRange.Rows.Count - 1, 1)
If dateCell.Value = "○" Then
consecutiveDays = consecutiveDays + 1
If consecutiveDays >= 14 Then
' 14連続出勤の名前を出力シートに書き込む
outputRow = outputSheet.Cells(outputSheet.Rows.Count, 1).End(xlUp).Row + 1
outputSheet.Cells(outputRow, 1).Value = name
Exit For
End If
Else
consecutiveDays = 0
End If
Next dateCell
Next cell
End If
Next ws
End Sub

この回答へのお礼

ご回答ありがとうございます！
マクロ詳細ありがとうございます！
一旦頂いた内容にて検証してみます！

お礼日時：2023/09/12 15:58

No.2 回答者： tatsumaru77 回答日時： 2023/09/12 15:07

補足要求です。

画像がよく見えないので確認の意味もあります。
１．出勤を示す文字（記号）は何でしょうか。

２．休みを示す文字（記号）は何でしょうか。

３．連勤何日目かを表示するのは、AH列でしょうか。
連勤４日の場合は、「4日」と表示すれば良いのでしょうか。（末尾に日を付加する）
　　又、赤字で表示したいということでしょうか。

４．カレンダーは、毎月、１日～月末日までであってますか。
　　又、前月の連勤日は、無視して良いのでしょうか。（１日に出勤した場合は、１日の連勤日は１日になる）

この回答へのお礼

ご回答ありがとうございます！
ご指摘につきまして、
１）出勤は”○”になります。
２）休みは”×”になります。
３）添付のサンプル表においてはAH列で結構です。
　　連勤4日の場合は黒字の「4日」で構いません。
４）カレンダーは毎月1～末日です。
　　前月分は可能であれば別途計算用セルか何かを作り手入力できれば結構です。
以上でよろしいでしょうか？
他何かあればご指示ください。
宜しくおねがいします。

お礼日時：2023/09/12 15:57

No.1 回答者： Zimmerman 回答日時： 2023/09/12 14:45

関数自体はcountifで割と簡単に実現できると思いますが、

肝心なのは「確認日」をどのように指定するかというところかと思います。

例えば確認日の列（にあるセル）を選択しておいて「確認ボタン」を押すとか。
でもこれだと知っている人しか使えないインターフェイスになります。

色々やり方はありますので、その辺りをハッキリさせないと
答えづらいかと思います。

この回答へのお礼

ご回答ありがとうございます！
ご指摘いただいた内容についてですが、
理想はエクセルファイルを開いた都度を想定していました。
今日開いたら本日の日付に対しAさんが何連勤というのがわかればよろしいです。

お礼日時：2023/09/12 15:53