FileRun_2021_03_26_PHP_71-74.zip
安装过程
https://www.moerats.com/archives/453/

0_2021下半年专业班级排课表（0430系统导出）0512.zip
改名为.xlsm

B列为原始名单
D列为签到栏
=IF(COUNTIF($D$2:$D$302,B2)>0,COUNTIF($D$2:$D$302,B2),"")

=IF(ISNUMBER(FIND(LEFT(B2,2),"网络物联信息广告设计动画")),"计算机系",IF(ISNUMBER(FIND(LEFT(B2,2),"机电汽修数控")),"机电系",IF(ISNUMBER(FIND(LEFT(B2,2),"电商")),"电商系","经管系")))

function Ping(strip)

dim objshell,boolcode
set objshell=CreateObject("WScipt.Shell")
boolcode=objshell.Run("ping -n 1 -w 1000 " & strip , 0, True)
if boolcode = 0 then
    Ping = true
else
    Ping =false
end if

end function

Sub PingSystem()

dim strip as string
rowcount =ActiveSheet.Cells(65536,2).End(xlUp).Row
for introw = 2 to rowcount 
strip = ActiveSheet.Cells(introw,2).value
if Ping(strip) = True then
    ActiveSheet.Cells(introw,3).value="Online"
else
    ActiveSheet.Cells(introw,3).value="Offline"
end if

end sub

https://www.cnblogs.com/wzh313/articles/9739085.html

需要在A列输入以下公式，并向下复制填充：
=(G2=$M$2)+A1

在N2单元格输入以下公式，并向下复制填充：
=IFERROR(VLOOKUP(ROW(A1),A:H,6,0),"")

在O2单元格输入以下公式，并向下复制填充：
=IFERROR(VLOOKUP(ROW(A1),A:H,8,0),"")

在P2单元格输入以下公式：
=SUM(O2:O7)

Sub 合并单元格()
'功能：根据第2列（班别）是否相同，进行对第1、2、7列合并单元格

Dim m, n, t, row, rowcount As Long
rowcount = ActiveSheet.UsedRange.Rows.Count + 1
Application.ScreenUpdating = False '关闭屏幕更新
Application.DisplayAlerts = False
t = 1 '序号
m = 2
'n为行号
For n = 3 To rowcount
'根据第2列（班别）是否相同，进行合并单元格
    If Cells(n, 2).Value <> Cells(n - 1, 2).Value And m < n Then
    '合并第2列（班别）
        With Range(Cells(m, 2), Cells(n - 1, 2))
            .Merge
        End With
        '合并第7列（周总课时），并计算周总课时
        With Range(Cells(m, 7), Cells(n - 1, 7))
            .Merge
            .Value = Application.WorksheetFunction.Sum(Range(Cells(m, 6), Cells(n - 1, 6)))
        End With
        '合并第1列（序号列），并填写序号
        With Range(Cells(m, 1), Cells(n - 1, 1))
            .Merge
            .Value = t
            t = t + 1
        End With
        m = n
    End If
    If Cells(n, 2).Value = "" Then
        m = n + 1
    End If
Next n
Application.ScreenUpdating = True '恢复屏幕更新

End Sub

Sub 取消合并单元格()
'功能：取消当前工作表所有合并单元格
Dim i As Range
For Each i In ActiveSheet.UsedRange
If i.Address <> i.MergeArea.Address And i.Address = i.MergeArea.Item(1).Address Then
i.MergeArea.Select
i.MergeArea.UnMerge
' MsgBox (i.MergeArea.Address)
Selection.FillDown
End If
Next i
End Sub

ThisWorkbook.Worksheets("表名").Range("列名A" & Rows.Count).End(xlUp).Row