Nhờ mọi người sửa giúp em đoạn code ạ

Liên hệ QC
V

Vũ Tuấn Tùng

Thành viên mới
Tham gia
22/6/16
Bài viết
27
Được thích
2
Nhờ các anh chị em trên diễn đàn sửa giúp em đoạn code này để cho gọn chứ nhập tay lâu quá ạ. . . em xin cảm ơn ạ. . .
Mã: 
Sub Tinh()
With Sheet1
            .Range("B3") = Application.CountIf(.Range("H1:QQQ200"), .Range("A3")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B4") = Application.CountIf(.Range("H1:QQQ200"), .Range("A4")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B5") = Application.CountIf(.Range("H1:QQQ200"), .Range("A5")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B6") = Application.CountIf(.Range("H1:QQQ200"), .Range("A6")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B7") = Application.CountIf(.Range("H1:QQQ200"), .Range("A7")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B8") = Application.CountIf(.Range("H1:QQQ200"), .Range("A8")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B9") = Application.CountIf(.Range("H1:QQQ200"), .Range("A9")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B10") = Application.CountIf(.Range("H1:QQQ200"), .Range("A10")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B11") = Application.CountIf(.Range("H1:QQQ200"), .Range("A11")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B12") = Application.CountIf(.Range("H1:QQQ200"), .Range("A12")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B13") = Application.CountIf(.Range("H1:QQQ200"), .Range("A13")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B14") = Application.CountIf(.Range("H1:QQQ200"), .Range("A14")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B15") = Application.CountIf(.Range("H1:QQQ200"), .Range("A15")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B16") = Application.CountIf(.Range("H1:QQQ200"), .Range("A16")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B17") = Application.CountIf(.Range("H1:QQQ200"), .Range("A17")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B18") = Application.CountIf(.Range("H1:QQQ200"), .Range("A18")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B19") = Application.CountIf(.Range("H1:QQQ200"), .Range("A19")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B20") = Application.CountIf(.Range("H1:QQQ200"), .Range("A20")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B21") = Application.CountIf(.Range("H1:QQQ200"), .Range("A21")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B22") = Application.CountIf(.Range("H1:QQQ200"), .Range("A22")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B23") = Application.CountIf(.Range("H1:QQQ200"), .Range("A23")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B24") = Application.CountIf(.Range("H1:QQQ200"), .Range("A24")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B25") = Application.CountIf(.Range("H1:QQQ200"), .Range("A25")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B26") = Application.CountIf(.Range("H1:QQQ200"), .Range("A26")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B27") = Application.CountIf(.Range("H1:QQQ200"), .Range("A27")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B28") = Application.CountIf(.Range("H1:QQQ200"), .Range("A28")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B29") = Application.CountIf(.Range("H1:QQQ200"), .Range("A29")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B30") = Application.CountIf(.Range("H1:QQQ200"), .Range("A30")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B31") = Application.CountIf(.Range("H1:QQQ200"), .Range("A31")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B32") = Application.CountIf(.Range("H1:QQQ200"), .Range("A32")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B33") = Application.CountIf(.Range("H1:QQQ200"), .Range("A33")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B34") = Application.CountIf(.Range("H1:QQQ200"), .Range("A34")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B35") = Application.CountIf(.Range("H1:QQQ200"), .Range("A35")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B36") = Application.CountIf(.Range("H1:QQQ200"), .Range("A36")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B37") = Application.CountIf(.Range("H1:QQQ200"), .Range("A37")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B38") = Application.CountIf(.Range("H1:QQQ200"), .Range("A38")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B39") = Application.CountIf(.Range("H1:QQQ200"), .Range("A39")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B40") = Application.CountIf(.Range("H1:QQQ200"), .Range("A40")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B41") = Application.CountIf(.Range("H1:QQQ200"), .Range("A41")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B42") = Application.CountIf(.Range("H1:QQQ200"), .Range("A42")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B43") = Application.CountIf(.Range("H1:QQQ200"), .Range("A43")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B44") = Application.CountIf(.Range("H1:QQQ200"), .Range("A44")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B45") = Application.CountIf(.Range("H1:QQQ200"), .Range("A45")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B46") = Application.CountIf(.Range("H1:QQQ200"), .Range("A46")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B47") = Application.CountIf(.Range("H1:QQQ200"), .Range("A47")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B48") = Application.CountIf(.Range("H1:QQQ200"), .Range("A48")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B49") = Application.CountIf(.Range("H1:QQQ200"), .Range("A49")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B50") = Application.CountIf(.Range("H1:QQQ200"), .Range("A50")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B51") = Application.CountIf(.Range("H1:QQQ200"), .Range("A51")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B52") = Application.CountIf(.Range("H1:QQQ200"), .Range("A52")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B53") = Application.CountIf(.Range("H1:QQQ200"), .Range("A53")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B54") = Application.CountIf(.Range("H1:QQQ200"), .Range("A54")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B55") = Application.CountIf(.Range("H1:QQQ200"), .Range("A55")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B56") = Application.CountIf(.Range("H1:QQQ200"), .Range("A56")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B57") = Application.CountIf(.Range("H1:QQQ200"), .Range("A57")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B58") = Application.CountIf(.Range("H1:QQQ200"), .Range("A58")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B59") = Application.CountIf(.Range("H1:QQQ200"), .Range("A59")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B60") = Application.CountIf(.Range("H1:QQQ200"), .Range("A60")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B61") = Application.CountIf(.Range("H1:QQQ200"), .Range("A61")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B62") = Application.CountIf(.Range("H1:QQQ200"), .Range("A62")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B63") = Application.CountIf(.Range("H1:QQQ200"), .Range("A64")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B64") = Application.CountIf(.Range("H1:QQQ200"), .Range("A65")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B65") = Application.CountIf(.Range("H1:QQQ200"), .Range("A65")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B66") = Application.CountIf(.Range("H1:QQQ200"), .Range("A66")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B67") = Application.CountIf(.Range("H1:QQQ200"), .Range("A67")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B68") = Application.CountIf(.Range("H1:QQQ200"), .Range("A68")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B69") = Application.CountIf(.Range("H1:QQQ200"), .Range("A69")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B70") = Application.CountIf(.Range("H1:QQQ200"), .Range("A70")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B71") = Application.CountIf(.Range("H1:QQQ200"), .Range("A71")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B72") = Application.CountIf(.Range("H1:QQQ200"), .Range("A72")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B73") = Application.CountIf(.Range("H1:QQQ200"), .Range("A73")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B74") = Application.CountIf(.Range("H1:QQQ200"), .Range("A74")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B75") = Application.CountIf(.Range("H1:QQQ200"), .Range("A75")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B76") = Application.CountIf(.Range("H1:QQQ200"), .Range("A76")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B77") = Application.CountIf(.Range("H1:QQQ200"), .Range("A77")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B78") = Application.CountIf(.Range("H1:QQQ200"), .Range("A78")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B79") = Application.CountIf(.Range("H1:QQQ200"), .Range("A79")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B80") = Application.CountIf(.Range("H1:QQQ200"), .Range("A80")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B81") = Application.CountIf(.Range("H1:QQQ200"), .Range("A81")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B82") = Application.CountIf(.Range("H1:QQQ200"), .Range("A82")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B83") = Application.CountIf(.Range("H1:QQQ200"), .Range("A83")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B84") = Application.CountIf(.Range("H1:QQQ200"), .Range("A84")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B85") = Application.CountIf(.Range("H1:QQQ200"), .Range("A85")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B86") = Application.CountIf(.Range("H1:QQQ200"), .Range("A86")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B87") = Application.CountIf(.Range("H1:QQQ200"), .Range("A87")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B88") = Application.CountIf(.Range("H1:QQQ200"), .Range("A88")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B89") = Application.CountIf(.Range("H1:QQQ200"), .Range("A89")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B90") = Application.CountIf(.Range("H1:QQQ200"), .Range("A90")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B91") = Application.CountIf(.Range("H1:QQQ200"), .Range("A91")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B92") = Application.CountIf(.Range("H1:QQQ200"), .Range("A92")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B93") = Application.CountIf(.Range("H1:QQQ200"), .Range("A93")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B94") = Application.CountIf(.Range("H1:QQQ200"), .Range("A94")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B95") = Application.CountIf(.Range("H1:QQQ200"), .Range("A95")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B96") = Application.CountIf(.Range("H1:QQQ200"), .Range("A96")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B97") = Application.CountIf(.Range("H1:QQQ200"), .Range("A97")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B98") = Application.CountIf(.Range("H1:QQQ200"), .Range("A98")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B99") = Application.CountIf(.Range("H1:QQQ200"), .Range("A99")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B100") = Application.CountIf(.Range("H1:QQQ200"), .Range("A100")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B101") = Application.CountIf(.Range("H1:QQQ200"), .Range("A101")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B102") = Application.CountIf(.Range("H1:QQQ200"), .Range("A102")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B103") = Application.CountIf(.Range("H1:QQQ200"), .Range("A103")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B104") = Application.CountIf(.Range("H1:QQQ200"), .Range("A104")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B105") = Application.CountIf(.Range("H1:QQQ200"), .Range("A105")) / Application.CountA(.Range("H1:QQQ1"))
        End With
End Sub
 

File đính kèm

  • Tinh.xlsb
    544.9 KB · Đọc: 6
Sắp xếp theo thời gian sắp xếp theo bầu chọn
Bạn thử với cặp macro này xem sao:
PHP: 
Dim WF As Object
Sub Cha()
 Dim Cls As Range
 
 Set WF = Application.WorksheetFunction
 For Each Cls In Range([B2], [B2].End(xlDown))
    Con Cls
 Next cle
End Sub
Mã: 
Sub Con(BB As Range)
    BB.Value = WF.CountIf([H1:QQQ2000], BB.Offset(, -1)) / WF.CountA([H1:QQQ1])
End Sub
 
Upvote 0
Gọn hay không gọn thì bạn đã nhập rồi. Rút ngắn chi?

Hoạ hoằn bạn muốn học code, học cách rút thì cũng cần giải thích.

(giả sử là 103 ô liên tục từ B3 đến B105)
With Sheet1
Set vung = .Range("H1:QQQ200")
mauSo = Application.CountA(.Range("H1:QQQ1"))
For Each cll In .Range("B3:B105")
cll.Value = Application.CountIf(vung, cll.Offset(, -1)) / mauSo
Next cll
Set vung = Nothing
End With
 
Upvote 0
Vũ Tuấn Tùng đã viết:
Nhờ các anh chị em trên diễn đàn sửa giúp em đoạn code này để cho gọn chứ nhập tay lâu quá ạ. . . em xin cảm ơn ạ. . .
Mã: 
Sub Tinh()
With Sheet1
            .Range("B3") = Application.CountIf(.Range("H1:QQQ200"), .Range("A3")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B4") = Application.CountIf(.Range("H1:QQQ200"), .Range("A4")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B5") = Application.CountIf(.Range("H1:QQQ200"), .Range("A5")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B6") = Application.CountIf(.Range("H1:QQQ200"), .Range("A6")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B7") = Application.CountIf(.Range("H1:QQQ200"), .Range("A7")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B8") = Application.CountIf(.Range("H1:QQQ200"), .Range("A8")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B9") = Application.CountIf(.Range("H1:QQQ200"), .Range("A9")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B10") = Application.CountIf(.Range("H1:QQQ200"), .Range("A10")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B11") = Application.CountIf(.Range("H1:QQQ200"), .Range("A11")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B12") = Application.CountIf(.Range("H1:QQQ200"), .Range("A12")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B13") = Application.CountIf(.Range("H1:QQQ200"), .Range("A13")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B14") = Application.CountIf(.Range("H1:QQQ200"), .Range("A14")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B15") = Application.CountIf(.Range("H1:QQQ200"), .Range("A15")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B16") = Application.CountIf(.Range("H1:QQQ200"), .Range("A16")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B17") = Application.CountIf(.Range("H1:QQQ200"), .Range("A17")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B18") = Application.CountIf(.Range("H1:QQQ200"), .Range("A18")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B19") = Application.CountIf(.Range("H1:QQQ200"), .Range("A19")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B20") = Application.CountIf(.Range("H1:QQQ200"), .Range("A20")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B21") = Application.CountIf(.Range("H1:QQQ200"), .Range("A21")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B22") = Application.CountIf(.Range("H1:QQQ200"), .Range("A22")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B23") = Application.CountIf(.Range("H1:QQQ200"), .Range("A23")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B24") = Application.CountIf(.Range("H1:QQQ200"), .Range("A24")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B25") = Application.CountIf(.Range("H1:QQQ200"), .Range("A25")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B26") = Application.CountIf(.Range("H1:QQQ200"), .Range("A26")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B27") = Application.CountIf(.Range("H1:QQQ200"), .Range("A27")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B28") = Application.CountIf(.Range("H1:QQQ200"), .Range("A28")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B29") = Application.CountIf(.Range("H1:QQQ200"), .Range("A29")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B30") = Application.CountIf(.Range("H1:QQQ200"), .Range("A30")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B31") = Application.CountIf(.Range("H1:QQQ200"), .Range("A31")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B32") = Application.CountIf(.Range("H1:QQQ200"), .Range("A32")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B33") = Application.CountIf(.Range("H1:QQQ200"), .Range("A33")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B34") = Application.CountIf(.Range("H1:QQQ200"), .Range("A34")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B35") = Application.CountIf(.Range("H1:QQQ200"), .Range("A35")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B36") = Application.CountIf(.Range("H1:QQQ200"), .Range("A36")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B37") = Application.CountIf(.Range("H1:QQQ200"), .Range("A37")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B38") = Application.CountIf(.Range("H1:QQQ200"), .Range("A38")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B39") = Application.CountIf(.Range("H1:QQQ200"), .Range("A39")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B40") = Application.CountIf(.Range("H1:QQQ200"), .Range("A40")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B41") = Application.CountIf(.Range("H1:QQQ200"), .Range("A41")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B42") = Application.CountIf(.Range("H1:QQQ200"), .Range("A42")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B43") = Application.CountIf(.Range("H1:QQQ200"), .Range("A43")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B44") = Application.CountIf(.Range("H1:QQQ200"), .Range("A44")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B45") = Application.CountIf(.Range("H1:QQQ200"), .Range("A45")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B46") = Application.CountIf(.Range("H1:QQQ200"), .Range("A46")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B47") = Application.CountIf(.Range("H1:QQQ200"), .Range("A47")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B48") = Application.CountIf(.Range("H1:QQQ200"), .Range("A48")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B49") = Application.CountIf(.Range("H1:QQQ200"), .Range("A49")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B50") = Application.CountIf(.Range("H1:QQQ200"), .Range("A50")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B51") = Application.CountIf(.Range("H1:QQQ200"), .Range("A51")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B52") = Application.CountIf(.Range("H1:QQQ200"), .Range("A52")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B53") = Application.CountIf(.Range("H1:QQQ200"), .Range("A53")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B54") = Application.CountIf(.Range("H1:QQQ200"), .Range("A54")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B55") = Application.CountIf(.Range("H1:QQQ200"), .Range("A55")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B56") = Application.CountIf(.Range("H1:QQQ200"), .Range("A56")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B57") = Application.CountIf(.Range("H1:QQQ200"), .Range("A57")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B58") = Application.CountIf(.Range("H1:QQQ200"), .Range("A58")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B59") = Application.CountIf(.Range("H1:QQQ200"), .Range("A59")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B60") = Application.CountIf(.Range("H1:QQQ200"), .Range("A60")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B61") = Application.CountIf(.Range("H1:QQQ200"), .Range("A61")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B62") = Application.CountIf(.Range("H1:QQQ200"), .Range("A62")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B63") = Application.CountIf(.Range("H1:QQQ200"), .Range("A64")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B64") = Application.CountIf(.Range("H1:QQQ200"), .Range("A65")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B65") = Application.CountIf(.Range("H1:QQQ200"), .Range("A65")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B66") = Application.CountIf(.Range("H1:QQQ200"), .Range("A66")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B67") = Application.CountIf(.Range("H1:QQQ200"), .Range("A67")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B68") = Application.CountIf(.Range("H1:QQQ200"), .Range("A68")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B69") = Application.CountIf(.Range("H1:QQQ200"), .Range("A69")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B70") = Application.CountIf(.Range("H1:QQQ200"), .Range("A70")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B71") = Application.CountIf(.Range("H1:QQQ200"), .Range("A71")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B72") = Application.CountIf(.Range("H1:QQQ200"), .Range("A72")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B73") = Application.CountIf(.Range("H1:QQQ200"), .Range("A73")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B74") = Application.CountIf(.Range("H1:QQQ200"), .Range("A74")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B75") = Application.CountIf(.Range("H1:QQQ200"), .Range("A75")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B76") = Application.CountIf(.Range("H1:QQQ200"), .Range("A76")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B77") = Application.CountIf(.Range("H1:QQQ200"), .Range("A77")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B78") = Application.CountIf(.Range("H1:QQQ200"), .Range("A78")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B79") = Application.CountIf(.Range("H1:QQQ200"), .Range("A79")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B80") = Application.CountIf(.Range("H1:QQQ200"), .Range("A80")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B81") = Application.CountIf(.Range("H1:QQQ200"), .Range("A81")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B82") = Application.CountIf(.Range("H1:QQQ200"), .Range("A82")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B83") = Application.CountIf(.Range("H1:QQQ200"), .Range("A83")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B84") = Application.CountIf(.Range("H1:QQQ200"), .Range("A84")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B85") = Application.CountIf(.Range("H1:QQQ200"), .Range("A85")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B86") = Application.CountIf(.Range("H1:QQQ200"), .Range("A86")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B87") = Application.CountIf(.Range("H1:QQQ200"), .Range("A87")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B88") = Application.CountIf(.Range("H1:QQQ200"), .Range("A88")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B89") = Application.CountIf(.Range("H1:QQQ200"), .Range("A89")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B90") = Application.CountIf(.Range("H1:QQQ200"), .Range("A90")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B91") = Application.CountIf(.Range("H1:QQQ200"), .Range("A91")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B92") = Application.CountIf(.Range("H1:QQQ200"), .Range("A92")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B93") = Application.CountIf(.Range("H1:QQQ200"), .Range("A93")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B94") = Application.CountIf(.Range("H1:QQQ200"), .Range("A94")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B95") = Application.CountIf(.Range("H1:QQQ200"), .Range("A95")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B96") = Application.CountIf(.Range("H1:QQQ200"), .Range("A96")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B97") = Application.CountIf(.Range("H1:QQQ200"), .Range("A97")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B98") = Application.CountIf(.Range("H1:QQQ200"), .Range("A98")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B99") = Application.CountIf(.Range("H1:QQQ200"), .Range("A99")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B100") = Application.CountIf(.Range("H1:QQQ200"), .Range("A100")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B101") = Application.CountIf(.Range("H1:QQQ200"), .Range("A101")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B102") = Application.CountIf(.Range("H1:QQQ200"), .Range("A102")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B103") = Application.CountIf(.Range("H1:QQQ200"), .Range("A103")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B104") = Application.CountIf(.Range("H1:QQQ200"), .Range("A104")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B105") = Application.CountIf(.Range("H1:QQQ200"), .Range("A105")) / Application.CountA(.Range("H1:QQQ1"))
        End With
End Sub
Nhấp chuột vào đây để mở rộng...

Bạn thử code dưới xem nhé:
Mã: 
Sub Tinh()
Dim i as Long
On Error resume next
For i = 3 to 105
With Sheet1
            .Range("B" & i) = Application.CountIf(.Range("H1:QQQ200"), .Range("A" & i)) / Application.CountA(.Range("H1:QQQ1"))
End with
Next i
End sub
Chỗ số 3 và số 105 bạn có thể thay đổi tùy ý nhé
 
Upvote 0
SA_DQ đã viết:
Mã: 
Sub Con(BB As Range)
    BB.Value = WF.CountIf([H1:QQQ2000], BB.Offset(, -1)) / WF.CountA([H1:QQQ1])
End Sub
Nhấp chuột vào đây để mở rộng...
Mỗi lần gọi sub thì VBA lại phải tính cái range H1:QQQ2000 ( [ ] là lệnh tính - evaluate), cái range H1:QQQ1, và CountA cái range trên.
Trong khi thực tế, nếu code không thay đổi gì trong range H1:QQQ1 thì tất cả 3 cái trên chỉ cần tính 1 lần.
(nếu code có thay đổi range H1:QQQ1 thì CountA tính lại là phải thôi)

johnnylinhanh đã viết:
Bạn thử code dưới xem nhé:
Mã: 
Sub Tinh()
Dim i as Long
On Error resume next
For i = 3 to 105
With Sheet1
            .Range("B" & i) = Application.CountIf(.Range("H1:QQQ200"), .Range("A" & i)) / Application.CountA(.Range("H1:QQQ1"))
End with
Next i
End sub
Chỗ số 3 và số 105 bạn có thể thay đổi tùy ý nhé
Nhấp chuột vào đây để mở rộng...
Tương tự như trên, mỗi lượt vòng lặp thì code phải tính lại Range("H1:QQQ200"), Range("H1:QQQ1") và hàm CountA.
 
Upvote 0
Nếu số dòng lớn (vài ngàn dòng, thay vì 105 dòng như trong file), dùng array sau đó gán xuống nhé:
PHP: 
Option Explicit
Sub Tinh()
Dim Lr&, k&, rng As Range, cell As Range, arr()
Application.ScreenUpdating = False
With Sheet1
Lr = .Cells(Rows.Count, "A").End(xlUp).Row ' dòng cuoi cot A. Gán Lr = 105 neu chi muon tinh den dong 105
ReDim arr(1 To Lr - 1, 1 To 1)
Set rng = .Range("H1").CurrentRegion
    For Each cell In .Range("A2:A" & Lr)
        k = k + 1
        arr(k, 1) = Application.CountIf(rng, cell) / rng.Columns.Count
    Next
.Range("B2").Resize(k, 1) = arr
.Range("B2").Resize(k, 1).NumberFormat = "##.00%"
End With
Application.ScreenUpdating = True
End Sub
 
Upvote 1
Vũ Tuấn Tùng đã viết:
Nhờ các anh chị em trên diễn đàn sửa giúp em đoạn code này để cho gọn chứ nhập tay lâu quá ạ. . . em xin cảm ơn ạ. . .
Mã: 
Sub Tinh()
With Sheet1
            .Range("B3") = Application.CountIf(.Range("H1:QQQ200"), .Range("A3")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B4") = Application.CountIf(.Range("H1:QQQ200"), .Range("A4")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B5") = Application.CountIf(.Range("H1:QQQ200"), .Range("A5")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B6") = Application.CountIf(.Range("H1:QQQ200"), .Range("A6")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B7") = Application.CountIf(.Range("H1:QQQ200"), .Range("A7")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B8") = Application.CountIf(.Range("H1:QQQ200"), .Range("A8")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B9") = Application.CountIf(.Range("H1:QQQ200"), .Range("A9")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B10") = Application.CountIf(.Range("H1:QQQ200"), .Range("A10")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B11") = Application.CountIf(.Range("H1:QQQ200"), .Range("A11")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B12") = Application.CountIf(.Range("H1:QQQ200"), .Range("A12")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B13") = Application.CountIf(.Range("H1:QQQ200"), .Range("A13")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B14") = Application.CountIf(.Range("H1:QQQ200"), .Range("A14")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B15") = Application.CountIf(.Range("H1:QQQ200"), .Range("A15")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B16") = Application.CountIf(.Range("H1:QQQ200"), .Range("A16")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B17") = Application.CountIf(.Range("H1:QQQ200"), .Range("A17")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B18") = Application.CountIf(.Range("H1:QQQ200"), .Range("A18")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B19") = Application.CountIf(.Range("H1:QQQ200"), .Range("A19")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B20") = Application.CountIf(.Range("H1:QQQ200"), .Range("A20")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B21") = Application.CountIf(.Range("H1:QQQ200"), .Range("A21")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B22") = Application.CountIf(.Range("H1:QQQ200"), .Range("A22")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B23") = Application.CountIf(.Range("H1:QQQ200"), .Range("A23")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B24") = Application.CountIf(.Range("H1:QQQ200"), .Range("A24")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B25") = Application.CountIf(.Range("H1:QQQ200"), .Range("A25")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B26") = Application.CountIf(.Range("H1:QQQ200"), .Range("A26")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B27") = Application.CountIf(.Range("H1:QQQ200"), .Range("A27")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B28") = Application.CountIf(.Range("H1:QQQ200"), .Range("A28")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B29") = Application.CountIf(.Range("H1:QQQ200"), .Range("A29")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B30") = Application.CountIf(.Range("H1:QQQ200"), .Range("A30")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B31") = Application.CountIf(.Range("H1:QQQ200"), .Range("A31")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B32") = Application.CountIf(.Range("H1:QQQ200"), .Range("A32")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B33") = Application.CountIf(.Range("H1:QQQ200"), .Range("A33")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B34") = Application.CountIf(.Range("H1:QQQ200"), .Range("A34")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B35") = Application.CountIf(.Range("H1:QQQ200"), .Range("A35")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B36") = Application.CountIf(.Range("H1:QQQ200"), .Range("A36")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B37") = Application.CountIf(.Range("H1:QQQ200"), .Range("A37")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B38") = Application.CountIf(.Range("H1:QQQ200"), .Range("A38")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B39") = Application.CountIf(.Range("H1:QQQ200"), .Range("A39")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B40") = Application.CountIf(.Range("H1:QQQ200"), .Range("A40")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B41") = Application.CountIf(.Range("H1:QQQ200"), .Range("A41")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B42") = Application.CountIf(.Range("H1:QQQ200"), .Range("A42")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B43") = Application.CountIf(.Range("H1:QQQ200"), .Range("A43")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B44") = Application.CountIf(.Range("H1:QQQ200"), .Range("A44")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B45") = Application.CountIf(.Range("H1:QQQ200"), .Range("A45")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B46") = Application.CountIf(.Range("H1:QQQ200"), .Range("A46")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B47") = Application.CountIf(.Range("H1:QQQ200"), .Range("A47")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B48") = Application.CountIf(.Range("H1:QQQ200"), .Range("A48")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B49") = Application.CountIf(.Range("H1:QQQ200"), .Range("A49")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B50") = Application.CountIf(.Range("H1:QQQ200"), .Range("A50")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B51") = Application.CountIf(.Range("H1:QQQ200"), .Range("A51")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B52") = Application.CountIf(.Range("H1:QQQ200"), .Range("A52")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B53") = Application.CountIf(.Range("H1:QQQ200"), .Range("A53")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B54") = Application.CountIf(.Range("H1:QQQ200"), .Range("A54")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B55") = Application.CountIf(.Range("H1:QQQ200"), .Range("A55")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B56") = Application.CountIf(.Range("H1:QQQ200"), .Range("A56")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B57") = Application.CountIf(.Range("H1:QQQ200"), .Range("A57")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B58") = Application.CountIf(.Range("H1:QQQ200"), .Range("A58")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B59") = Application.CountIf(.Range("H1:QQQ200"), .Range("A59")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B60") = Application.CountIf(.Range("H1:QQQ200"), .Range("A60")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B61") = Application.CountIf(.Range("H1:QQQ200"), .Range("A61")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B62") = Application.CountIf(.Range("H1:QQQ200"), .Range("A62")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B63") = Application.CountIf(.Range("H1:QQQ200"), .Range("A64")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B64") = Application.CountIf(.Range("H1:QQQ200"), .Range("A65")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B65") = Application.CountIf(.Range("H1:QQQ200"), .Range("A65")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B66") = Application.CountIf(.Range("H1:QQQ200"), .Range("A66")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B67") = Application.CountIf(.Range("H1:QQQ200"), .Range("A67")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B68") = Application.CountIf(.Range("H1:QQQ200"), .Range("A68")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B69") = Application.CountIf(.Range("H1:QQQ200"), .Range("A69")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B70") = Application.CountIf(.Range("H1:QQQ200"), .Range("A70")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B71") = Application.CountIf(.Range("H1:QQQ200"), .Range("A71")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B72") = Application.CountIf(.Range("H1:QQQ200"), .Range("A72")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B73") = Application.CountIf(.Range("H1:QQQ200"), .Range("A73")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B74") = Application.CountIf(.Range("H1:QQQ200"), .Range("A74")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B75") = Application.CountIf(.Range("H1:QQQ200"), .Range("A75")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B76") = Application.CountIf(.Range("H1:QQQ200"), .Range("A76")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B77") = Application.CountIf(.Range("H1:QQQ200"), .Range("A77")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B78") = Application.CountIf(.Range("H1:QQQ200"), .Range("A78")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B79") = Application.CountIf(.Range("H1:QQQ200"), .Range("A79")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B80") = Application.CountIf(.Range("H1:QQQ200"), .Range("A80")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B81") = Application.CountIf(.Range("H1:QQQ200"), .Range("A81")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B82") = Application.CountIf(.Range("H1:QQQ200"), .Range("A82")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B83") = Application.CountIf(.Range("H1:QQQ200"), .Range("A83")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B84") = Application.CountIf(.Range("H1:QQQ200"), .Range("A84")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B85") = Application.CountIf(.Range("H1:QQQ200"), .Range("A85")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B86") = Application.CountIf(.Range("H1:QQQ200"), .Range("A86")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B87") = Application.CountIf(.Range("H1:QQQ200"), .Range("A87")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B88") = Application.CountIf(.Range("H1:QQQ200"), .Range("A88")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B89") = Application.CountIf(.Range("H1:QQQ200"), .Range("A89")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B90") = Application.CountIf(.Range("H1:QQQ200"), .Range("A90")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B91") = Application.CountIf(.Range("H1:QQQ200"), .Range("A91")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B92") = Application.CountIf(.Range("H1:QQQ200"), .Range("A92")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B93") = Application.CountIf(.Range("H1:QQQ200"), .Range("A93")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B94") = Application.CountIf(.Range("H1:QQQ200"), .Range("A94")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B95") = Application.CountIf(.Range("H1:QQQ200"), .Range("A95")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B96") = Application.CountIf(.Range("H1:QQQ200"), .Range("A96")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B97") = Application.CountIf(.Range("H1:QQQ200"), .Range("A97")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B98") = Application.CountIf(.Range("H1:QQQ200"), .Range("A98")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B99") = Application.CountIf(.Range("H1:QQQ200"), .Range("A99")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B100") = Application.CountIf(.Range("H1:QQQ200"), .Range("A100")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B101") = Application.CountIf(.Range("H1:QQQ200"), .Range("A101")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B102") = Application.CountIf(.Range("H1:QQQ200"), .Range("A102")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B103") = Application.CountIf(.Range("H1:QQQ200"), .Range("A103")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B104") = Application.CountIf(.Range("H1:QQQ200"), .Range("A104")) / Application.CountA(.Range("H1:QQQ1"))
            .Range("B105") = Application.CountIf(.Range("H1:QQQ200"), .Range("A105")) / Application.CountA(.Range("H1:QQQ1"))
        End With
End Sub
Nhấp chuột vào đây để mở rộng...
Góp vui. máy tôi code chạy chậm, không biết máy khác thì sao?
Mã: 
Option Explicit

Sub TinhLai()
Dim i&, Lr&, T&, R&
Dim Arr(), KQ(), Rng As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
With Sheet1
Set Rng = .Range("H1").CurrentRegion
T = Rng.Columns.Count
Lr = .Cells(Rows.Count, 1).End(xlUp).Row
Arr = .Range("A2:A" & Lr).Value
ReDim KQ(1 To UBound(Arr), 1 To 1)

For i = 1 To UBound(Arr)
    KQ(i, 1) = Application.CountIf(Rng, Arr(i, 1)) / T
Next i
.[C2].Resize(UBound(Arr), 1) = KQ
End With
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
 
Upvote 1
SA_DQ đã viết:
Bạn thử với cặp macro này xem sao:
PHP: 
Dim WF As Object
Sub Cha()
 Dim Cls As Range
 
 Set WF = Application.WorksheetFunction
 For Each Cls In Range([B2], [B2].End(xlDown))
    Con Cls
 Next cle
End Sub
Mã: 
Sub Con(BB As Range)
    BB.Value = WF.CountIf([H1:QQQ2000], BB.Offset(, -1)) / WF.CountA([H1:QQQ1])
End Sub
Nhấp chuột vào đây để mở rộng...
Em xin cảm ơn ạ
Bài đã được tự động gộp:

HUONGHCKT đã viết:
Góp vui. máy tôi code chạy chậm, không biết máy khác thì sao?
Mã: 
Option Explicit

Sub TinhLai()
Dim i&, Lr&, T&, R&
Dim Arr(), KQ(), Rng As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
With Sheet1
Set Rng = .Range("H1").CurrentRegion
T = Rng.Columns.Count
Lr = .Cells(Rows.Count, 1).End(xlUp).Row
Arr = .Range("A2:A" & Lr).Value
ReDim KQ(1 To UBound(Arr), 1 To 1)

For i = 1 To UBound(Arr)
    KQ(i, 1) = Application.CountIf(Rng, Arr(i, 1)) / T
Next i
.[C2].Resize(UBound(Arr), 1) = KQ
End With
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
Nhấp chuột vào đây để mở rộng...
em cảm ơn ạ
 
Upvote 0
VetMini đã viết:
Mỗi lần gọi sub thì VBA lại phải tính cái range H1:QQQ2000 ( [ ] là lệnh tính - evaluate), cái range H1:QQQ1, và CountA cái range trên.
Trong khi thực tế, nếu code không thay đổi gì trong range H1:QQQ1 thì tất cả 3 cái trên chỉ cần tính 1 lần.
(nếu code có thay đổi range H1:QQQ1 thì CountA tính lại là phải thôi)
Nhấp chuột vào đây để mở rộng...
Ờ hén, Có thể tính số liệu WF.CountA([H1:QQQ1]) ngay trong chương trình 'Cha' & chỉ 1 lần
Xin cảm ơn bác nhiều!
 
Upvote 0
Dùng Application.CountIf nhiều lần làm code ì ạch
Mã: 
Sub ABC()
  Dim sArr(), res(), C&, i&, iMax&, j&, sRow&, sCol&
  With Sheet1
    C = Application.CountA(.Range("H1:QQQ1"))
    iMax = (.Range("A999999").End(xlUp).Value)
    ReDim res(0 To iMax, 1 To 1)
    sArr = .Range("H1").CurrentRegion.Value
    sRow = UBound(sArr): sCol = UBound(sArr, 2)
    For i = 1 To sRow
      For j = 1 To sCol
        If sArr(i, j) <> Empty Then
          res(sArr(i, j), 1) = res(sArr(i, j), 1) + 1
        End If
      Next j
    Next i
    For i = 0 To iMax
      res(i, 1) = res(i, 1) / C
    Next i
    .Range("C2").Resize(iMax + 1) = res
  End With
End Sub
Có thể rút gọn bằng cách bỏ vòng For i = 0 To iMax khi thay lệnh
C = Application.CountA(.Range("H1:QQQ1"))
res(sArr(i, j), 1) = res(sArr(i, j), 1) + 1
bằng
C = 1/Application.CountA(.Range("H1:QQQ1"))
res(sArr(i, j), 1) = res(sArr(i, j), 1) + C
Nhưng kết quả sẽ có sai số rất nhỏ
 
Upvote 1
HieuCD đã viết:
Dùng Application.CountIf nhiều lần làm code ì ạch
Mã: 
Sub ABC()
  Dim sArr(), res(), C&, i&, iMax&, j&, sRow&, sCol&
  With Sheet1
    C = Application.CountA(.Range("H1:QQQ1"))
    iMax = (.Range("A999999").End(xlUp).Value)
    ReDim res(0 To iMax, 1 To 1)
    sArr = .Range("H1").CurrentRegion.Value
    sRow = UBound(sArr): sCol = UBound(sArr, 2)
    For i = 1 To sRow
      For j = 1 To sCol
        If sArr(i, j) <> Empty Then
          res(sArr(i, j), 1) = res(sArr(i, j), 1) + 1
        End If
      Next j
    Next i
    For i = 0 To iMax
      res(i, 1) = res(i, 1) / C
    Next i
    .Range("C2").Resize(iMax + 1) = res
  End With
End Sub
Có thể rút gọn bằng cách bỏ vòng For i = 0 To iMax khi thay lệnh
C = Application.CountA(.Range("H1:QQQ1"))
res(sArr(i, j), 1) = res(sArr(i, j), 1) + 1
bằng
C = 1/Application.CountA(.Range("H1:QQQ1"))
res(sArr(i, j), 1) = res(sArr(i, j), 1) + C
Nhưng kết quả sẽ có sai số rất nhỏ
Nhấp chuột vào đây để mở rộng...
Kiểu này giống như người ta hỏi đi đường bộ dùng phương tiện nào nhanh nhất thì bạn lại chỉ đi Airbus. Hahaa
 
Upvote 0
Bạn phải đăng nhập hoặc đăng ký để trả lời bài viết tại đây.
Web KT

Bài viết mới nhất

ongke0711
  • Question
Tải hóa đơn điện tử (https://hoadondientu.gdt.gov.vn/) Excel Vba
    • Thích
    • Yêu thích
8 9 10
Trả lời
193
Đọc
10K
qtm1987
Q
OverAC
Gặp gỡ giao lưu tại Hà Nội, chiều ngày 10/11/2025. cùng Ptm0412, OverAC và các anh chị thân thiết.
    • Thích
    • Yêu thích
Trả lời
4
Đọc
109
viethung78
V
Hai Lúa Miền Tây
Nhập liệu, truy vấn dữ liệu qua Internet = VB.Net
    • Thích
2
Trả lời
26
Đọc
10K
phamduyhoang2907
P
P
Connection string cho SQL Express 2005
Trả lời
7
Đọc
7K
phamduyhoang2907
P
T
Xin trợ giúp code về dò tìm dữ liệu
Trả lời
4
Đọc
166
CHAOQUAY
C
HeSanbi
  • Question
Lỗi VBA: Cài đặt thông số ForceVBALoadFromSource trong Registry để VBA biên dịch không bị lỗi
Trả lời
2
Đọc
83
HeSanbi
HeSanbi
Quyen182
  • Question
Xin giúp code VBA để chèn ảnh có sẵn trong Excel vào phần comment
Trả lời
0
Đọc
57
Quyen182
Quyen182
B
  • Question
Xin hướng dẫn tách 1 vùng dữ liệu sang thành nhiều sheet
Trả lời
2
Đọc
64
Hoàng Tuấn 868
H
Toanke063
  • Question
Xin hỏi code vba chuyển 1 file pdf bất kì thành ảnh
Trả lời
7
Đọc
149
cantl
C
HeSanbi
  • Đã giải quyết
ZaloExcel v2.91: Gửi tin nhắn, hình ảnh, bảng, biểu đồ, tập tin, tạo nhóm, thu thập tin nhắn
    • Thích
    • Cảm ơn
    • Yêu thích
2 3
Trả lời
55
Đọc
13K
HeSanbi
HeSanbi
Bebooby
Tách mã sản phẩm trong chuỗi thông tin
Trả lời
2
Đọc
60
NT Ngoc Yen
N
thlpro
Truy Vấn Công thức từ Sheet này Qua Sheet Khác
Trả lời
8
Đọc
170
thlpro
thlpro
N
  • Question
VBA - copy các sheets unhide và dạng giá trị theo vùng xác định
Trả lời
3
Đọc
108
huhumalu
H
S
Lý do công thức bị mờ tại ô Function
    • Cười
Trả lời
2
Đọc
73
sakurasumo
S
GPE-Trợ Lý
Đăng ký tham dự hội thảo tại Hà Nội: Power Query - Công cụ quản lý dữ liệu
    • Thích
    • Cảm ơn
    • Yêu thích
Trả lời
5
Đọc
655
GPE-Trợ Lý
GPE-Trợ Lý
N
  • Question
Làm ơn viết giùm mình macro làm tròn giá trị ạ
    • Cười
Trả lời
14
Đọc
139
ptm0412
ptm0412
CNKyNguyen
Giảm dung lượng của file excel
    • Cảm ơn
    • Cười
    • Thích
Trả lời
2
Đọc
247
CNKyNguyen
CNKyNguyen
hunganh.nguyen
Xin giúp code tách dòng tổng thành những dòng chi tiết với giá trị là 1
Trả lời
9
Đọc
420
vostro 1500
vostro 1500
P
Sự cố về hàm SUMIF
Trả lời
8
Đọc
239
yeudoi
yeudoi
OverAC
Thông báo nội dung xấu và bài viết vi phạm nội quy.
19 20 21
Trả lời
416
Đọc
41K
vanle33
vanle33
Xem thêm…

Thành viên có số lượng bài viết cao nhất tháng

Thành viên có điểm tương tác cao nhất tháng

Back
Top Bottom