以下可利用什麼Excel Formula做統計?

2008-04-11 12:47 am

Row 1 -----------Row 2 -----------Row 3
Name ----------- Age --------------Department
Peter -------------18 ----------------Accounts
Mary -------------36 ----------------HR & ADM
May --------------20 ----------------Accounts
Daisy ------------42 -----------------Sales
Kelvin -----------21 -----------------Sales
Mandy -----------25 -----------------Sales
Cindy ------------54 -----------------Management
Ronald ----------29 -----------------HR & ADM

如果我想求每個department的人的歲數分佈而可得出以下結果︰

Age Range / 人數----HR & ADM ---Accounts ----Sales ----Management
18 – 29 --------------------1 ppl---------2 ppls -----2 ppls--------0 ppl
30 – 39 --------------------1 ppl---------0 ppl ------0 ppl---------0 ppl
40 – 49 -------------------0 ppl---------0 ppl ------1ppl----------0 ppl
50 – 59 -------------------0 ppl---------0 ppl ------0 ppl---------1 ppl

Thanks!

回答 (1)

joe

2008-04-18 8:19 pm

✔ 最佳答案

下列 macro 可解決你的問題:
Dim ag(5), hr(5), ac(5), sa(5), ma(5)
Sub Macro1()
'
' Macro1 巨集表
'
'
nu = Cells(1, 1).CurrentRegion.Rows.Count

For i = 1 To 5
hr(i) = 0: ac(i) = 0: sa(i) = 0: ma(i) = 0
Next

For i = 2 To nu
c1 = Cells(i, 2)
c1 = Left(c1, 1)
c2 = Cells(i, 3)
c2 = Left(c2, 1)
If c2 = "H" Then hr(c1) = hr(c1) + 1
If c2 = "A" Then ac(c1) = ac(c1) + 1
If c2 = "S" Then sa(c1) = sa(c1) + 1
If c2 = "M" Then ma(c1) = ma(c1) + 1
Next

sc = 5

Cells(1, sc) = "Age Range": Cells(1, sc + 1) = "人數-HR & ADM"
Cells(1, sc + 2) = "Accounts": Cells(1, sc + 3) = "Sales"
Cells(1, sc + 4) = "Management"
Cells(2, sc) = "18 - 29": Cells(3, sc) = "30 - 39"
Cells(4, sc) = "40 - 49": Cells(5, sc) = "50 - 59"

j = 2
Cells(j, sc + 1) = hr(j) + hr(1): Cells(j, sc + 2) = ac(j) + ac(1)
Cells(j, sc + 3) = sa(j) + sa(1): Cells(j, sc + 4) = ma(j) + ma(1):
For j = 3 To 5
Cells(j, sc + 1) = hr(j): Cells(j, sc + 2) = ac(j): Cells(j, sc + 3) = sa(j): Cells(j, sc + 4) = ma(j):
Next

End Sub

希望幫到你.

👍 0 👎 0