|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
Sub 按年龄统计()
Application.ScreenUpdating = False '
Dim ar As Variant
Dim cr
Dim i As Long
Dim n As Long
Dim m As Long
' ReDim cr(1 To 2, 1 To 10) '
cr = Sheets("问题二").[b6].Resize(2, 10)
For Each sh In Sheets '
If InStr(sh.Name, "数据") > 0 Then '
ar = sh.Range("a1").CurrentRegion '
If sh.Name = "数据库一" Then '
nl = 7 '
hs = 2 ''
xb = 3 ''
rk = 4
Else '
nl = 9 '
hs = 4
xb = 5
rk = 6
End If '
For i = 3 To UBound(ar) '
If Len(Trim(ar(i, nl))) <> 0 Then '
If ar(i, nl) >= 50 Then
cr(1, 1) = cr(1, 1) + ar(i, hs)
cr(1, 2) = cr(1, 2) + ar(i, rk)
End If
If ar(i, nl) >= 50 And Trim(ar(i, xb)) = "女" Then
cr(1, 5) = cr(1, 5) + ar(i, hs)
cr(1, 6) = cr(1, 6) + ar(i, rk)
ElseIf ar(i, nl) >= 50 And Trim(ar(i, xb)) = "男" Then
cr(1, 9) = cr(1, 9) + ar(i, hs)
cr(1, 10) = cr(1, 10) + ar(i, rk)
End If
If ar(i, nl) < 45 Then
cr(2, 1) = cr(2, 1) + ar(i, hs)
cr(2, 2) = cr(2, 2) + ar(i, rk)
End If
If ar(i, nl) < 45 And Trim(ar(i, xb)) = "女" Then
cr(2, 5) = cr(2, 5) + ar(i, hs)
cr(2, 6) = cr(2, 6) + ar(i, rk)
ElseIf ar(i, nl) < 45 And Trim(ar(i, xb)) = "男" Then
cr(2, 9) = cr(2, 9) + ar(i, hs)
cr(2, 10) = cr(2, 10) + ar(i, rk)
End If
End If
Next i
End If
Next sh
With Sheets("问题二") '
' .Range("b6:g7") = Empty '
.[b6].Resize(2, 10) = cr '
End With '
MsgBox "ok!"
Application.ScreenUpdating = True '
End Sub
|
评分
-
1
查看全部评分
-
|