如何打卡excel被保护的部分

1.excel中,如何在保护部分有公式的工作表后正常使用分类汇总功能?

全选（行与列中间单击）>；格式>；单元格>；锁定>；都不勾>；确定，再选取要保护的单元格>；格式>；单元格>；锁定>；都打勾>；确定.再工具>；保护>；保护工作表>；密码（可选）>；确定（再次输入）确定

注：如是选取有公式的，可用编辑>；定位>；条件>；公式>；确定

----------------------------

撤除我 的回答

不过，建议到下面看看，这里高手很多

2.你好,请问如何把考勤表中的异常时间显示出来

这个都是要用vba编程来实现的。

下面介绍一个实际应用案例。第一步：每月从考勤机读取打卡数据导入excel表格。

如图：第二步：维护本月工作日。如图：第三步：手动输入请假记录。

如图：第四步：自动计算打卡结果。如图：第四步代码如下：Sub 签到记录（）Sheets（"签到记录"）.ActivateRange(Cells(2, 1), Cells(100, 100)).SelectSelection.ClearContentsDim m_s1, m_s2, m_s3, m_s4 As StringDim m_arr1 '存放打卡记录Dim m_arr2 '存放日历及休日标记Dim m_arr3 '存放员工清单'Dim m_arr4(1 To 100, 1 To 31) As String '存放签到记录Dim MAXROW1, MAXROW2, MAXROW3 As IntegerDim i, j, k, l As IntegerDim m_qdsj As String '临时存放签到时间Dim m_dksj As String '临时存放打卡时间Dim m_xm, m_bm As String '存放员工姓名/部门Dim m_rq As DateDim m_rq2 As StringDim dkjl As String '存放打卡记录Dim sbsj As DateDim xbsj As DateDim bhZD 'key-姓名 item-编号Dim dkrqZD 'key-编号+日期 item-序号Dim dkrqArr(1 To 1000, 1 To 2) As Date '1-上班时间 2-下班时间Dim dkrqJs As IntegerDim xM As String, bH As IntegerDim myDate As Date, myTime As DateDim myKey As StringSet bhZD = CreateObject("scripting.dictionary")dkrqJs = 0With Sheets（"员工基本信息"）hh = 2Do While .Cells(hh, 1) <> ""xM = .Cells(hh, 1).TextbH = .Cells(hh, 5).ValuebhZD.Add xM, bHhh = hh + 1LoopEnd WithSet dkrqZD = CreateObject("scripting.dictionary")With Sheets（"打卡记录"）dkrqZD.RemoveAllhh = 2Do While .Cells(hh, 1) <> ""If hh = 13 Thenxxx = 1End IfbH = .Cells(hh, 1).ValuemyDate = .Cells(hh, 2).ValuemyTime = .Cells(hh, 3).ValuemyKey = "" & bH & ";" & Day(myDate)If Not dkrqZD.exists(myKey) ThendkrqJs = dkrqJs + 1dkrqZD.Add myKey, dkrqJsdkrqArr(dkrqJs, 1) = myTimedkrqArr(dkrqJs, 2) = myTimeElseIf myTime < dkrqArr(dkrqZD(myKey), 1) ThendkrqArr(dkrqZD(myKey), 1) = myTime '上班时间End IfIf myTime > dkrqArr(dkrqZD(myKey), 2) Then '下班时间dkrqArr(dkrqZD(myKey), 2) = myTimeEnd IfEnd Ifhh = hh + 1LoopEnd Withm_s1 = "打卡记录"m_s2 = "本月工作日"m_s3 = "签到记录"m_s4 = "员工基本信息"Sheets(m_s1).SelectMAXROW1 = Cells(Rows.Count, 1).End(xlUp).Row '找到A列最后一个有数据单元格的行号m_arr1 = Range("A2:c" & MAXROW1) '把单元格区域装入数组Sheets(m_s2).SelectMAXROW2 = Cells(Rows.Count, 1).End(xlUp).Row '日历数m_arr2 = Range("A2:c" & MAXROW2) '把单元格区域装入数组Sheets(m_s4).SelectMAXROW3 = Cells(Rows.Count, 1).End(xlUp).Row '员工清单人数m_arr3 = Range("a2:d" & MAXROW3) '把单元格区域装入数组'计算签到时间 取当天打卡最早的时间Sheets(m_s3).Select'写入表头信息：第一行：日期 第二行：上班、下班For i = 1 To MAXROW2 - 1j = (i - 1) * 2 + 3Cells(1, j) = m_arr2(i, 1)Cells(1, j + 1) = myweek(m_arr2(i, 1))Cells(2, j) = "上班"Cells(2, j + 1) = "下班"Next ij = 3For i = 1 To MAXROW3 - 1If m_arr3(i, 4) = 0 ThenGoTo 100End Ifm_xm = m_arr3(i, 1)m_bm = m_arr3(i, 2)Cells(j, 1) = m_xmCells(j, 2) = m_bmFor k = 1 To MAXROW2 - 1If m_arr2(k, 3) = "Y" Then '休息日不看打卡记录GoTo 200End Ifk2 = 3 + (k - 1) * 2m_rq = m_arr2(k, 1)m_bj = 0bH = bhZD(m_xm)myKey = "" & bH & ";" & Day(m_rq)If dkrqZD.exists(myKey) Thenm_bj = 1sbsj = dkrqArr(dkrqZD(myKey), 1)xbsj = dkrqArr(dkrqZD(myKey), 2)Elsem_bj = 0End IfIf m_bj = 0 ThenCells(j, k2) = "未打"Cells(j, k2 + 1) = "未打"ElseIf sbsj <= TimeSerial(8, 35, 0) ThenCells(j, k2) = "OK"ElseCells(j, k2) = "迟到"End IfIf xbsj >= TimeSerial(17, 30, 0) ThenCells(j, k2 + 1) = "OK"ElseCells(j, k2 + 1) = "早退"End IfEnd If200 Next kj = j + 1100 Next iSheets(m_s3).SelectEnd SubFunction myweek(a)Dim b As Integerb = Weekday(a, 2)Select Case bCase 1myweek = "一"Case 2myweek = "二"Case 3myweek = "三"Case 4myweek = "四"Case 5myweek = "五"Case 6myweek = "六"Case 7myweek = "日"End SelectEnd FunctionSub 一键更新（）请假处理外出处理出差处理调休处理End Sub。