本帖最后由 张雄友 于 2014-12-23 19:03 编辑
简化代码 ,请帮用简化代码实现F1:I1。- Sub 执行()
- [F1:I1].ClearContents
- Call 交飞人数
- Call 上班时间小于8小时人数
- Call 达标人数
- Call 不达标人数
- End Sub
- Sub 交飞人数()
- Dim arr, i&, m&
- arr = Range("A1:D" & Cells(Rows.Count, 1).End(3).Row)
- For i = 2 To UBound(arr)
- If Len(arr(i, 3)) Then
- m = m + 1
- End If
- Next
- [F1] = "交飞人数有" & m & "人"
- End Sub
- Sub 上班时间小于8小时人数()
- Dim arr, i&, k&
- arr = Range("A1:D" & Cells(Rows.Count, 1).End(3).Row)
- For i = 2 To UBound(arr)
- If arr(i, 3) < 8 Then
- k = k + 1
- End If
- Next
- [G1] = "上班时间小于8小时有" & k & "人"
- End Sub
- Sub 达标人数() '平均时薪11以上而且上班时间大于等于8,就是达标!
- Dim arr, i&, x&
- arr = Range("A1:D" & Cells(Rows.Count, 1).End(3).Row)
- For i = 2 To UBound(arr)
- If arr(i, 3) >= 8 And arr(i, 4) >= 11 Then
- x = x + 1
- End If
- Next
- [H1] = "达标人数有" & x & "人"
- End Sub
- Sub 不达标人数() '平均时薪11以下而且上班时间大于等于8,就是不达标!
- Dim arr, i&, y&
- arr = Range("A1:D" & Cells(Rows.Count, 1).End(3).Row)
- For i = 2 To UBound(arr)
- If arr(i, 3) >= 8 And arr(i, 4) < 11 Then
- y = y + 1
- End If
- Next
- [I1] = "不达标人数有" & y & "人"
- End Sub
- '2+2+16=20
复制代码
楼上的逻辑关系有点乱,结果有误,应该是这样: - Sub 执行()
- [F1:I1].ClearContents
- Dim arr, i&, m&, K&, x&, y&
- arr = Range("A1:D" & Cells(Rows.Count, 1).End(3).Row)
- For i = 2 To UBound(arr)
- If Len(arr(i, 3)) Then
- m = m + 1
- If arr(i, 3) < 8 Then
- K = K + 1
- Else
- If arr(i, 4) >= 11 Then x = x + 1 Else y = y + 1
- End If
- End If
- Next
- [F1] = "交飞人数有" & m & "人"
- [G1] = "上班时间小于8小时有" & K & "人"
- [H1] = "达标人数有" & x & "人"
- [I1] = "不达标人数有" & y & "人"
- End Sub
复制代码
|