- 帖子
- 522
- 主題
- 36
- 精華
- 1
- 積分
- 603
- 點名
- 0
- 作業系統
- win xp sp3
- 軟體版本
- Office 2003
- 閱讀權限
- 50
- 性別
- 男
- 註冊時間
- 2012-12-13
- 最後登錄
- 2021-7-11
|
4#
發表於 2014-1-19 15:33
| 只看該作者
用VBA, VBA code 如下:
Sub 統計放假名單()
Dim x, y As Object
Dim i, j, k As Integer
Dim str1 As String
Dim Colindx(5) As Integer
Set x = Sheets("放假名單")
Set y = Sheets("統計表")
y.Range("D3:X72").ClearContents
'列數(i+2)代表日期
For i = 1 To 31
'Colindx(k)代表"統計表"的欄數計數器
For k = 1 To 5
Colindx(k) = 3
Next
'欄數(j-2)代表員工數
For j = 3 To 18
str1 = x.Cells(i + 2, j)
Select Case str1
Case Is = "▽"
Colindx(1) = Colindx(1) + 1
y.Cells(i * 5 - 2, Colindx(1)) = x.Cells(2, j)
Case Is = "○"
Colindx(2) = Colindx(2) + 1
y.Cells(i * 5 - 1, Colindx(2)) = x.Cells(2, j)
Case Is = "◎"
Colindx(3) = Colindx(3) + 1
y.Cells(i * 5, Colindx(3)) = x.Cells(2, j)
Case Is = "□"
Colindx(4) = Colindx(4) + 1
y.Cells(i * 5 + 1, Colindx(4)) = x.Cells(2, j)
Case Is = "☆"
Colindx(5) = Colindx(5) + 1
y.Cells(i * 5 + 2, Colindx(5)) = x.Cells(2, j)
End Select
Next
Next
End Sub
放假名單如下:
統計表執行結果如下:
 |
|
發表於 2014-1-18 23:43
| 
