xin code VBA thay cho hàm countifs và sumifs

Erebus · 31/5/22

Chào các bác,
Em đang có một bài toán về đếm và tính tổng với điều kiện lớn hơn và nhỏ hơn ngày chỉ định trong chuỗi ngày.
Em có thể dùng hàm countifs và sumifs, tuy nhiên muốn nhờ các bác giúp cho 1 code VBA ạ.
Thông tin cụ thể như tệp đính kèm.
Các bác giúp em với nhé.
Em cảm ơn ạ

bebo021999 · 31/5/22

PHP:

Option Explicit
Sub dem()
Dim lr&, i&, dem1&, dem2&, dem3&, rng, arr(1 To 2, 1 To 3)
Dim cong1 As Double, cong2  As Double, cong3  As Double, ngay As Double
ngay = DateValue("2022/05/25")
With Worksheets("Sheet1")
    lr = .Cells(Rows.Count, "A").End(xlUp).Row
    rng = .Range("A2:C" & lr).Value2
    For i = 1 To lr - 1
        If rng(i, 1) < ngay Then
            dem1 = dem1 + 1: cong1 = cong1 + rng(i, 3)
            arr(1, 1) = dem1
            arr(2, 1) = cong1
        ElseIf rng(i, 1) = ngay Then
            dem2 = dem2 + 1: cong2 = cong2 + rng(i, 3)
            arr(1, 2) = dem2
            arr(2, 2) = cong2
        Else
            dem3 = dem3 + 1: cong3 = cong3 + rng(i, 3)
            arr(1, 3) = dem3
            arr(2, 3) = cong3
        End If
    Next
End With
Worksheets("Sheet2").Range("B3").Resize(2, 3).Value = arr
End Sub

Erebus · 31/5/22

bebo021999 đã viết:

PHP:

Option Explicit
Sub dem()
Dim lr&, i&, dem1&, dem2&, dem3&, rng, arr(1 To 2, 1 To 3)
Dim cong1 As Double, cong2  As Double, cong3  As Double, ngay As Double
ngay = DateValue("2022/05/25")
With Worksheets("Sheet1")
    lr = .Cells(Rows.Count, "A").End(xlUp).Row
    rng = .Range("A2:C" & lr).Value2
    For i = 1 To lr - 1
        If rng(i, 1) < ngay Then
            dem1 = dem1 + 1: cong1 = cong1 + rng(i, 3)
            arr(1, 1) = dem1
            arr(2, 1) = cong1
        ElseIf rng(i, 1) = ngay Then
            dem2 = dem2 + 1: cong2 = cong2 + rng(i, 3)
            arr(1, 2) = dem2
            arr(2, 2) = cong2
        Else
            dem3 = dem3 + 1: cong3 = cong3 + rng(i, 3)
            arr(1, 3) = dem3
            arr(2, 3) = cong3
        End If
    Next
End With
Worksheets("Sheet2").Range("B3").Resize(2, 3).Value = arr
End Sub

Em cảm ơn bác ạ

Erebus · 31/5/22

bebo021999 đã viết:

PHP:

Option Explicit
Sub dem()
Dim lr&, i&, dem1&, dem2&, dem3&, rng, arr(1 To 2, 1 To 3)
Dim cong1 As Double, cong2  As Double, cong3  As Double, ngay As Double
ngay = DateValue("2022/05/25")
With Worksheets("Sheet1")
    lr = .Cells(Rows.Count, "A").End(xlUp).Row
    rng = .Range("A2:C" & lr).Value2
    For i = 1 To lr - 1
        If rng(i, 1) < ngay Then
            dem1 = dem1 + 1: cong1 = cong1 + rng(i, 3)
            arr(1, 1) = dem1
            arr(2, 1) = cong1
        ElseIf rng(i, 1) = ngay Then
            dem2 = dem2 + 1: cong2 = cong2 + rng(i, 3)
            arr(1, 2) = dem2
            arr(2, 2) = cong2
        Else
            dem3 = dem3 + 1: cong3 = cong3 + rng(i, 3)
            arr(1, 3) = dem3
            arr(2, 3) = cong3
        End If
    Next
End With
Worksheets("Sheet2").Range("B3").Resize(2, 3).Value = arr
End Sub

Bác cho em hỏi thêm chút là nếu dùng WorksheetFunction.CountIfs/Sumifs thì có thể tính được không ạ?

bebo021999 · 31/5/22

hoabk đã viết:
Bác cho em hỏi thêm chút là nếu dùng WorksheetFunction.CountIfs/Sumifs thì có thể tính được không ạ?

Câu trả lời là được.
Nhưng nếu cần code VBA để dán công thức vào thì chẳng thà dùng công thức trên sheet luôn cho nhanh.

Erebus · 31/5/22

bebo021999 đã viết:
Câu trả lời là được.
Nhưng nếu cần code VBA để dán công thức vào thì chẳng thà dùng công thức trên sheet luôn cho nhanh.

vâng e hiểu, chỉ là em đang muốn học từ những cái đơn giản và dễ hiểu trước. Bác viết giúp em code bằng WorksheetFunction.CountIfs/Sumifs với ạ

batman1 · 1/6/22

hoabk đã viết:
vâng e hiểu, chỉ là em đang muốn học từ những cái đơn giản và dễ hiểu trước. Bác viết giúp em code bằng WorksheetFunction.CountIfs/Sumifs với ạ

Theo yêu cầu. Dùng SumIf, CountIf là đủ. Tôi dùng Application.SumIf, bạn có thể dùng Application.WorksheetFunction.SumIf

Mã:

Sub dem_va_tong()
Dim lastRow As Long, ketqua(1 To 2, 1 To 3), ngay As Double
    ngay = DateValue("5/25")
    With ThisWorkbook.Worksheets("Sheet1")
        lastRow = .Cells(Rows.Count, "A").End(xlUp).Row
        If lastRow < 2 Then Exit Sub
        ketqua(1, 1) = Application.CountIf(.Range("A2:A" & lastRow), "<" & ngay)
        ketqua(1, 2) = Application.CountIf(.Range("A2:A" & lastRow), "=" & ngay)
        ketqua(1, 3) = Application.CountIf(.Range("A2:A" & lastRow), ">" & ngay)
        ketqua(2, 1) = Application.SumIf(.Range("A2:A" & lastRow), "<" & ngay, .Range("C2:C" & lastRow))
        ketqua(2, 2) = Application.SumIf(.Range("A2:A" & lastRow), "=" & ngay, .Range("C2:C" & lastRow))
        ketqua(2, 3) = Application.SumIf(.Range("A2:A" & lastRow), ">" & ngay, .Range("C2:C" & lastRow))
    End With
    ThisWorkbook.Worksheets("Sheet2").Range("B3").Resize(2, 3).Value = ketqua
End Sub

Erebus · 1/6/22

batman1 đã viết:

Theo yêu cầu. Dùng SumIf, CountIf là đủ. Tôi dùng Application.SumIf, bạn có thể dùng Application.WorksheetFunction.SumIf

Mã:

Sub dem_va_tong()
Dim lastRow As Long, ketqua(1 To 2, 1 To 3), ngay As Double
    ngay = DateValue("5/25")
    With ThisWorkbook.Worksheets("Sheet1")
        lastRow = .Cells(Rows.Count, "A").End(xlUp).Row
        If lastRow < 2 Then Exit Sub
        ketqua(1, 1) = Application.CountIf(.Range("A2:A" & lastRow), "<" & ngay)
        ketqua(1, 2) = Application.CountIf(.Range("A2:A" & lastRow), "=" & ngay)
        ketqua(1, 3) = Application.CountIf(.Range("A2:A" & lastRow), ">" & ngay)
        ketqua(2, 1) = Application.SumIf(.Range("A2:A" & lastRow), "<" & ngay, .Range("C2:C" & lastRow))
        ketqua(2, 2) = Application.SumIf(.Range("A2:A" & lastRow), "=" & ngay, .Range("C2:C" & lastRow))
        ketqua(2, 3) = Application.SumIf(.Range("A2:A" & lastRow), ">" & ngay, .Range("C2:C" & lastRow))
    End With
    ThisWorkbook.Worksheets("Sheet2").Range("B3").Resize(2, 3).Value = ketqua
End Sub

Em cảm ơn bác nhiều ạ

phananhvusv · 6/6/22

Mình cũng đang có bài toán về countif, nhưng do số lượng cần làm quá lớn, hơn 1tr3 dòng. Nên khi dùng hàm countif thì excel gần như ko tính nổi. Chỉ đơn giản là tính số lượng bị trùng thôi, nhưng nếu không dùng countif thì có cách nào khác nhanh hơn ko? Thường thì tính toán nhiều nên đưa vào mảng VBA sẽ nhanh hơn, nhưng dùng countif thì không sử dụng trong mảng được.

tháº» vpt 6.6.2022.xlsm

drive.google.com

File lớn quá nên không up lên được.
2 cột đầu là dữ liệu gốc, giờ em cần đếm cột C xem có bao nhiêu lượt xuất hiện trong cả 2 cột A và B.

VetMini · 6/6/22

phananhvusv đã viết:
Mình cũng đang có bài toán về countif, nhưng do số lượng cần làm quá lớn, hơn 1tr3 dòng. ...

Hiểu chết liền.

Vả lại, nói như nước đổ lá môn. Dữ liệu khủng thì chịu khó học cái mớ Power PI.

HUONGHCKT · 6/6/22

phananhvusv đã viết:
Mình cũng đang có bài toán về countif, nhưng do số lượng cần làm quá lớn, hơn 1tr3 dòng. Nên khi dùng hàm countif thì excel gần như ko tính nổi. Chỉ đơn giản là tính số lượng bị trùng thôi, nhưng nếu không dùng countif thì có cách nào khác nhanh hơn ko? Thường thì tính toán nhiều nên đưa vào mảng VBA sẽ nhanh hơn, nhưng dùng countif thì không sử dụng trong mảng được.

tháº» vpt 6.6.2022.xlsm

drive.google.com

File lớn quá nên không up lên được.
2 cột đầu là dữ liệu gốc, giờ em cần đếm cột C xem có bao nhiêu lượt xuất hiện trong cả 2 cột A và B.

Thử chạy code rùa bò này xem sao. Không có dữ liệu nên không test được.

Mã:

Option Explicit
Sub THU()
Dim i&, j&, Lr&, R&, t&, Dic As Object, Key, Temp
Dim Arr(), Res(), Rng As Range
With Sheet1
Set Rng = .[A2].CurrentRegion
Lr = Rng.Rows.Count
Arr = Rng.Value
ReDim KQ(1 To UBound(Arr), 1 To 1)
Set Dic = CreateObject("Scripting.Dictionary")
For i = 2 To UBound(Arr)
    If Arr(i, 3) <> Empty Then
         Key = Arr(i, 3)
        If Not Dic.Exists(Key) Then t = t + 1: Dic.Add (Key), t
        Else: Exit For
    End If
Next i
For j = 1 To 2
For i = 2 To UBound(Arr)
If Arr(i, j) <> Empty Then
    Temp = Arr(i, j)
    If Dic.Exists(Temp) Then
        R = Dic.Item(Temp)
        KQ(R, 1) = KQ(R, 1) + 1
    End If
Else: Exit For
End If
Next i
Next j
.[D2].Resize(t, 1) = KQ
End With
Set Dic = Nothing
End Sub

VetMini · 7/6/22

HUONGHCKT đã viết:
Thử chạy code rùa bò này xem sao. Không có dữ liệu nên không test được.
...

Có dữ liệu thật thì nhắm code bạn chạy nổi hôn? Sheet này chứa đến trên 1,3 triệu dòng lận.

Trừ phi tôi nhầm. Thớt nói 1tr3 dòng [sic] có nghĩa là 1.000.003
.

kelacloi · 7/6/22

phananhvusv đã viết:
Mình cũng đang có bài toán về countif, nhưng do số lượng cần làm quá lớn, hơn 1tr3 dòng. Nên khi dùng hàm countif thì excel gần như ko tính nổi. Chỉ đơn giản là tính số lượng bị trùng thôi, nhưng nếu không dùng countif thì có cách nào khác nhanh hơn ko? Thường thì tính toán nhiều nên đưa vào mảng VBA sẽ nhanh hơn, nhưng dùng countif thì không sử dụng trong mảng được.

tháº» vpt 6.6.2022.xlsm

drive.google.com

File lớn quá nên không up lên được.
2 cột đầu là dữ liệu gốc, giờ em cần đếm cột C xem có bao nhiêu lượt xuất hiện trong cả 2 cột A và B.

Excel bản nào mà số dòng nhiều thế nhỉ?

Mình dùng bản 365 chỉ có 2^20 = 1,048,576 dòng.

HieuCD · 7/6/22

VetMini đã viết:
Có dữ liệu thật thì nhắm code bạn chạy nổi hôn? Sheet này chứa đến trên 1,3 triệu dòng lận.

Trừ phi tôi nhầm. Thớt nói 1tr3 dòng [sic] có nghĩa là 1.000.003
.

1.000.003 dòng đọc theo tiếng Anh rồi chuyển sang tiếng Việt sẽ là 1 triệu 3 dòng

HUONGHCKT · 7/6/22

VetMini đã viết:
Có dữ liệu thật thì nhắm code bạn chạy nổi hôn? Sheet này chứa đến trên 1,3 triệu dòng lận.

Trừ phi tôi nhầm. Thớt nói 1tr3 dòng [sic] có nghĩa là 1.000.003
.

Cảm ơn Anh đã xem bài. Tôi đã nói là code rùa bò mà. Code đưa nên chủ yếu để hy vọng có được sự góp ý về thuật toán của các thành viên có kiến thức uyen thâm (tích lũy kiến thức cho bản thân - hoặc cho bạn đọc nào quan tâm) mà thôi, chứ cũng không có ý gì khác.
Bài này nếu đổi lại đề là "Thống kê số lần xuất hiện của các số ( nằm trong 1 cột) với các số nằm trong nhiều chục, trăm, nghìn cột và mỗi cột chứa nhiều nghìn dòng (chục, trăm nghìn dòng< 1.048.576 dòng) " thì code trên có lẽ cũng vẫn test được, code vẫn chạy nhưng sẽ phải rất lâu mới cho ra kết quả.

VetMini · 7/6/22

HUONGHCKT đã viết:
Cảm ơn Anh đã xem bài. Tôi đã nói là code rùa bò mà. Code đưa nên chủ yếu để hy vọng có được sự góp ý về thuật toán của các thành viên có kiến thức uyen thâm (tích lũy kiến thức cho bản thân - hoặc cho bạn đọc nào quan tâm) mà thôi, chứ cũng không có ý gì khác.
Bài này nếu đổi lại đề là "Thống kê số lần xuất hiện của các số ( nằm trong 1 cột) với các số nằm trong nhiều chục, trăm, nghìn cột và mỗi cột chứa nhiều nghìn dòng (chục, trăm nghìn dòng< 1.048.576 dòng) " thì code trên có lẽ cũng vẫn test được, code vẫn chạy nhưng sẽ phải rất lâu mới cho ra kết quả.

Đùa bạn chơi cho vui thôi.
Ý mình chỉ nói về cái thói quen phóng đại của mấy người hỏi bài.

truongvu317 · 7/6/22

VetMini đã viết:
học cái mớ Power PI

Khi mới biết power query thì sẽ làm được không bác?

VetMini · 7/6/22

truongvu317 đã viết:
Khi mới biết power query thì sẽ làm được không bác?

Hỏi như vậy là không làm được gì cả.
Đầu tiên hết là phải biết:
- Power Query chỉ là một cách lọc dữ liệu và đưa vào Data Model.
- Nếu dữ liệu chứa trong Data Model thì nó sẽ làm được nhiều việc. Hầu hết các truy vấn trong Data Model được làm qua cỗ máy SQL Server Express cho nên nó rất hiệu quả, và có khả năng "cầm cương" được dữ liệu khủng. Triệu dòng là chuyện bình thường với nó.
- Dữ liệu chứa trong Data Model có thể được xào nấu bằng các hàm DAX. Các hàm này rất mạnh.

Google Sheets sỡ dĩ được bà con ca tụng là nhờ sanh sau đẻ muộn. Không phải hỗ trợ các phiên bản cũ cho nên tự nó đưa thẳng dữ liệu vào cái tương tự như Data Model (gú-gồ giữ kín, không hé răng cho biết nó là cái gì)

truongvu317 · 7/6/22

VetMini đã viết:
Data Model.

cái này cháu chưa có dịp tiếp cận, Power queery nó chạy quay cu đơ, chắc phải lên đời data model thui bác.

VetMini · 8/6/22

truongvu317 đã viết:
cái này cháu chưa có dịp tiếp cận, Power queery nó chạy quay cu đơ, chắc phải lên đời data model thui bác.

Chịu khó đi học Data Model đi rồi sẽ biết câu trên sai bét.

xin code VBA thay cho hàm countifs và sumifs

Thành viên mới

File đính kèm

Thành viên gạo cội

File đính kèm

Thành viên mới

Thành viên mới

Thành viên gạo cội

Thành viên mới

Thành viên gạo cội

Thành viên mới

Thành viên chính thức

Ăn cùng góc phố

Zalo 0986997214

Ăn cùng góc phố

Thành viên thường trực

Chuyên gia GPE

Zalo 0986997214

Ăn cùng góc phố

Thành viên tích cực

Ăn cùng góc phố

Thành viên tích cực

Ăn cùng góc phố