在10份考勤表中，如何在Excel中找到其中至少7份的名字？

Question

我在excel中有10张考勤单，里面只有现职人员的名字。我想知道至少有7张纸上有哪些名字。我该怎么做？

我知道这是保持出勤率的糟糕方法，而不是我自己想出来的，我只是想挽救它。提前感谢

Answer 1

好吧，这应该能行。我把它组合在一起，这样就有可能提高效率:)

不知道你的结构是什么，所以我建立了基于最简单的工作表，其中有一个标题行和学生的名字显示时，他们出席。没有出席的学生名单上没有。这可能不完全匹配，但代码应该很容易调整。如果需要的话我可以帮忙。最后，您将得到一个名为"arrGood“的数组，该数组包含学生，它将弹出一个消息框，告诉您谁会出席会议，如果没有人参加。

Option Explicit
Sub attendance()

'Set Variables
Dim sht As Integer, rw As Integer, pos As Integer, i As Integer, j As Integer, goodAttendance As Integer
Dim arrClass() As Variant, arrTransfer() As Variant, arrGood() As Variant
Dim strName As String, strStudents As String
Dim inArray As Boolean

goodAttendance = 2 'change this if you want to check a different number

'Initialize empty arrays
ReDim arrClass(0, 1)
ReDim arrGood(0)

For sht = 1 To ThisWorkbook.Worksheets.Count 'loop through worksheets
    For rw = 2 To ThisWorkbook.Worksheets(sht).Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row 'loop through rows starting with 2 (assuming there's a header row)
        inArray = False
        strName = ThisWorkbook.Sheets(sht).Cells(rw, 1).Value 'change this cell if searching for student names outside the first column
        'This section only triggers on the first run and adds the first student with attendance of 1
        If arrClass(0, 0) = "" Then
            arrClass(0, 0) = strName
            arrClass(0, 1) = 1
            inArray = True
        Else
            For pos = 0 To UBound(arrClass()) 'loop through array
                'check if student is already in array. Add 1 to their attendance if so
                If arrClass(pos, 0) = strName Then
                    inArray = True
                    arrClass(pos, 1) = arrClass(pos, 1) + 1
                End If
            Next pos
        End If
        If inArray = False Then 'new student not in array
            'Workaround to redim multidimensional array
            arrTransfer = arrClass
            ReDim arrClass(0 To UBound(arrClass) + 1, 1)
            For i = 0 To UBound(arrTransfer)
                For j = 0 To 1
                    arrClass(i, j) = arrTransfer(i, j)
                Next j
            Next i
            'Add new student to array and set their attendance to 1
            arrClass(UBound(arrClass), 0) = strName
            arrClass(UBound(arrClass), 1) = 1
        End If
    Next rw
Next sht

'Check for students with good attendance
For i = 0 To UBound(arrClass)
    If arrClass(i, 1) >= goodAttendance Then
        'Add first student
        If arrGood(0) = "" Then
            arrGood(0) = arrClass(i, 0)
        'Add subsequent students
        Else
            ReDim Preserve arrGood(0 To UBound(arrGood) + 1)
            arrGood(UBound(arrGood)) = arrClass(i, 0)
        End If
        strStudents = strStudents + arrClass(i, 0) + ", "
    End If
Next i

'Display students meeting criteria
If Len(strStudents) > 0 Then
    strStudents = Left(strStudents, Len(strStudents) - 2)
    MsgBox ("Students with more than " & goodAttendance & " attendances: " & strStudents)
Else
    MsgBox ("No students had at least " & goodAttendance & " attendances.")
End If

End Sub