Code vba thay thế cho hàm COUNTIFS

dvh.hy.9902 · 5/7/15

- Em có vấn đề tổng hợp thống kê số lượng hàng hóa mà các nhân viên bán hàng được thưởng ở Sheets("Thuong_ban_hang"). Em có sử dụng công thức COUNTIFS để thống kê Tổng số mặt hàng bán được và Số lượng mặt hàng được thưởng; để tính tiền thưởng. Cụ thể em đã tổng hợp một số trường hợp.
- Em mong được mọi người giúp em tổng hợp bằng vba với ạ!. EM XIN TRÂN THÀNH CẢM ƠN!.

gtri · 26/7/15

dvh.hy.9902 đã viết:
- Bạn gtri ơi mình đã kiểm tra lại:
+ Nếu mình bỏ đi dòng này trong code của bạn thì code vẫn lọc những người có mặt hàng không có thưởng, như vậy là không được. Còn để nguyên thì ở cột tổng các mặt hàng báng được là không chính xác.
+ bạn xem lại giúp mình với!.View attachment 144450
View attachment 144451

"Tổng số mặt hàng bán được" được tính theo cột số 36 hay 37 hay là 34 sheet DATA?

Let'GâuGâu · 26/7/15

dvh.hy.9902 đã viết:
- Anh Let's Gâu Gâu sửa lại code giúp em, em nhìn code của Anh đối với bài này cũng đã quen. Mong anh sửa lại giúp để em áp dụng ngay.

code cũ chạy sai (do khai báo biến cho mảng kết quả sai, thay vì j thì khai là k)
thực ra thì trong file của bạn, tất cả các nhân viên đều tham gian bán hàng có thưởng
trong file này tôi thử xóa mục có của "nguyễn thi mơ"

gtri · 26/7/15

Sửa lại với giả định
"Người bán hàng" sheet DATA để trống sẽ bị loại ( kể cả có thưởng - AI39 sheet DATA )
"Tổng số mặt hàng bán được" tính theo cột số 36 sheet DATA
"Số lượng mặt hàng được thưởng" =0 sẽ bị loại

Mã:

Option Explicit
Public Sub Co_Khong()
    Dim DSach, Tam(), kq(), r As Long, i
    DSach = Sheet3.Range("AI6", Sheet3.Range("AK1000000").End(xlUp))
    Tam = Sheet2.Range("C3", Sheet2.Range("D1000000").End(xlUp))
    With CreateObject("scripting.dictionary")
        For r = 1 To UBound(Tam)
            .Add Tam(r, 1), Array(Tam(r, 2), 0, 0)
        Next r
        ReDim Tam(2)
        For r = 1 To UBound(DSach)
            If DSach(r, 1) <> "" And DSach(r, 2) <> "" Then   'Sửa điều kiện nhập liệu
                Tam = .Item(DSach(r, 1))
                Tam(1) = Tam(1) + 1
                If Left(DSach(r, 3), 1) = "C" Then Tam(2) = Tam(2) + 1
                .Item(DSach(r, 1)) = Tam
            End If
        Next r
        Tam = .keys
        ReDim kq(1 To .Count + 1, 1 To 4)
        For r = 0 To UBound(Tam)
            If .Item(Tam(r))(2) > 0 Then   'Sửa điều kiện truy xuất
                i = i + 1
                kq(i, 1) = Tam(r)
                kq(i, 2) = .Item(Tam(r))(0)
                kq(i, 3) = .Item(Tam(r))(1)
                kq(i, 4) = .Item(Tam(r))(2)
                kq(.Count + 1, 3) = kq(.Count + 1, 3) + .Item(Tam(r))(1)
                kq(.Count + 1, 4) = kq(.Count + 1, 4) + .Item(Tam(r))(2)
            End If
        Next r
        kq(i + 1, 1) = "Tong"
        kq(i + 1, 3) = kq(.Count + 1, 3)
        kq(i + 1, 4) = kq(.Count + 1, 4)
    End With
    Sheet1.Range("A11", "H" & Sheet1.Range("B1000000").End(xlUp).Row).Clear
    Sheet1.Range("B11").Resize(i + 1, 4) = kq
    Sheet1.Range("A11").Resize(i) = "=row()-10"
    Sheet1.Range("G11").Resize(i) = "=RC[-2]*RC[-1]"
End Sub

To Let's Gâu Gâu : Bớt vòng lặp điền trực tiếp vào mảng làm không quen, sợ bị rối nên để 3 cái vòng cho lành. Thank

dvh.hy.9902 · 26/7/15

Let'GâuGâu đã viết:
code cũ chạy sai (do khai báo biến cho mảng kết quả sai, thay vì j thì khai là k)
thực ra thì trong file của bạn, tất cả các nhân viên đều tham gian bán hàng có thưởng
trong file này tôi thử xóa mục có của "nguyễn thi mơ"

- Ở trong file dữ liệu em gửi thì tất tất cả các nhân viên đều tham gian bán hàng có thưởng, nhưng thực tế thì không phải như vậy. với danh sách còn nữa thì Có những nhân viên bán những mặt hàng không có thưởng. Như trong file anh gửi, Anh xóa đi mục có thưởng của "nguyễn thi mơ". Kết quả OK. Nhưng cũng trong file đó em thử xóa thêm trường hợp có thưởng của "Hà Thanh Hùng" ở aheet (DATA) và chạy code thì kết quả không được nư mong đợi. Anh xem giúp em với!.

dvh.hy.9902 · 26/7/15

gtri đã viết:

Sửa lại với giả định
"Người bán hàng" sheet DATA để trống sẽ bị loại ( kể cả có thưởng - AI39 sheet DATA )
"Tổng số mặt hàng bán được" tính theo cột số 36 sheet DATA
"Số lượng mặt hàng được thưởng" =0 sẽ bị loại

Mã:

Option Explicit
Public Sub Co_Khong()
    Dim DSach, Tam(), kq(), r As Long, i
    DSach = Sheet3.Range("AI6", Sheet3.Range("AK1000000").End(xlUp))
    Tam = Sheet2.Range("C3", Sheet2.Range("D1000000").End(xlUp))
    With CreateObject("scripting.dictionary")
        For r = 1 To UBound(Tam)
            .Add Tam(r, 1), Array(Tam(r, 2), 0, 0)
        Next r
        ReDim Tam(2)
        For r = 1 To UBound(DSach)
            If DSach(r, 1) <> "" And DSach(r, 2) <> "" Then   'Sửa điều kiện nhập liệu
                Tam = .Item(DSach(r, 1))
                Tam(1) = Tam(1) + 1
                If Left(DSach(r, 3), 1) = "C" Then Tam(2) = Tam(2) + 1
                .Item(DSach(r, 1)) = Tam
            End If
        Next r
        Tam = .keys
        ReDim kq(1 To .Count + 1, 1 To 4)
        For r = 0 To UBound(Tam)
            If .Item(Tam(r))(2) > 0 Then   'Sửa điều kiện truy xuất
                i = i + 1
                kq(i, 1) = Tam(r)
                kq(i, 2) = .Item(Tam(r))(0)
                kq(i, 3) = .Item(Tam(r))(1)
                kq(i, 4) = .Item(Tam(r))(2)
                kq(.Count + 1, 3) = kq(.Count + 1, 3) + .Item(Tam(r))(1)
                kq(.Count + 1, 4) = kq(.Count + 1, 4) + .Item(Tam(r))(2)
            End If
        Next r
        kq(i + 1, 1) = "Tong"
        kq(i + 1, 3) = kq(.Count + 1, 3)
        kq(i + 1, 4) = kq(.Count + 1, 4)
    End With
    Sheet1.Range("A11", "H" & Sheet1.Range("B1000000").End(xlUp).Row).Clear
    Sheet1.Range("B11").Resize(i + 1, 4) = kq
    Sheet1.Range("A11").Resize(i) = "=row()-10"
    Sheet1.Range("G11").Resize(i) = "=RC[-2]*RC[-1]"
End Sub

To Let's Gâu Gâu : Bớt vòng lặp điền trực tiếp vào mảng làm không quen, sợ bị rối nên để 3 cái vòng cho lành. Thank

- "Người bán hàng" sheet DATA để trống sẽ bị loại ( kể cả có thưởng - AI39 sheet DATA ) ----->OK
- Tỏng số hàng bán được của một nhân viên là số lần tên nhân viên đó xuất hiện ở cột 35 Sheet(DATA). Nhân viên có thể bán các mặt hàng có thưởng, không có thưởng (trong nhập liệu thì "không có thưởng" mình sẽ bỏ trống không nhập).
- "Số lượng mặt hàng được thưởng" =0 (ở sheet Thuong_ban_hang) sẽ bị loại ----->OK
- Bạn sử lại code giúp mình để mình nghiên cứu thêm cách viết của bạn nhé!. Cảm ơn bạn

gtri · 26/7/15

dvh.hy.9902 đã viết:
- "Người bán hàng" sheet DATA để trống sẽ bị loại ( kể cả có thưởng - AI39 sheet DATA ) ----->OK
- Tỏng số hàng bán được của một nhân viên là số lần tên nhân viên đó xuất hiện ở cột 35 Sheet(DATA). Nhân viên có thể bán các mặt hàng có thưởng, không có thưởng (trong nhập liệu thì "không có thưởng" mình sẽ bỏ trống không nhập).
- "Số lượng mặt hàng được thưởng" =0 (ở sheet Thuong_ban_hang) sẽ bị loại ----->OK
- Bạn sử lại code giúp mình để mình nghiên cứu thêm cách viết của bạn nhé!. Cảm ơn bạn

Sửa dòng này trong bài 43

Mã:

If DSach(r, 1) <> "" And DSach(r, 2) <> "" Then   'Sửa điều kiện nhập liệu

Thành thế này

Mã:

If DSach(r, 1) <> ""  Then   'Sửa điều kiện nhập liệu

Là đạt yêu cầu phần tô đậm.

Let'GâuGâu · 26/7/15

dvh.hy.9902 đã viết:
- Ở trong file dữ liệu em gửi thì tất tất cả các nhân viên đều tham gian bán hàng có thưởng, nhưng thực tế thì không phải như vậy. với danh sách còn nữa thì Có những nhân viên bán những mặt hàng không có thưởng. Như trong file anh gửi, Anh xóa đi mục có thưởng của "nguyễn thi mơ". Kết quả OK. Nhưng cũng trong file đó em thử xóa thêm trường hợp có thưởng của "Hà Thanh Hùng" ở aheet (DATA) và chạy code thì kết quả không được nư mong đợi. Anh xem giúp em với!.

haiz............
xài vlookup, sumifs, subtotal và sau đó chỉ cẩn filter chỉ có 30s là có kết quả, viết code chi cho nó mệt vậy...............hhihihi
kỳ này mà hỏng trúng nữa thì "em xin hàng"

dvh.hy.9902 · 26/7/15

gtri đã viết:
Sửa dòng này trong bài 43

Mã:

If DSach(r, 1) <> "" And DSach(r, 2) <> "" Then 'Sửa điều kiện nhập liệu

Thành thế này

Mã:

If DSach(r, 1) <> "" Then 'Sửa điều kiện nhập liệu

Là đạt yêu cầu phần tô đậm.

- Cảm ơn bạn nhiều!. vậy là mình có thêm một cách để tìm hiểu "lại khổ thêm -+*/

" mà còn không biết có hiểu nổi không, nhìn hoa hết cả mặt?.
- Em vẫn mong code Anh Let'GâuGâu vì code trước đó đã sử lý trong trường hợp nhân viên không có trong danh sách sheet(Nhan_vien) mà có trong nhập liệu của sheet(DATA).

dvh.hy.9902 · 26/7/15

Let'GâuGâu đã viết:
haiz............
xài vlookup, sumifs, subtotal và sau đó chỉ cẩn filter chỉ có 30s là có kết quả, viết code chi cho nó mệt vậy...............hhihihi
kỳ này mà hỏng trúng nữa thì "em xin hàng"

- Code đã lọc chuẩn rồi ạ!. Cảm ơn Anh Let'GâuGâu nhiều nha!.
- Cảm ơn Bác gtri và mọi người!. Với 2 cách viết của 2 Bác để mà hiểu được thì em phải đọc không biết bao nhiêu chủ đề của GPE này + với hỏi bao nhiêu bài nữa híc híc !$@!!

!. Để mà hiểu từng câu lệnh chứ chưa nói là để viết code.

maya09 · 20/6/19

kieu manh đã viết:
Mình chưa hiểu Application.Trim lắm bạn cho ví dụ đi

Mình nghĩ cứ đau khổ làm gì nhỉ, cứ replace tất cả các loại 2-3 khoảng trắng về 1 hết là xong

Code vba thay thế cho hàm COUNTIFS

dvh.hy.9902

Thành viên hoạt động

File đính kèm

gtri

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

Let'GâuGâu

Thành viên mới

File đính kèm

gtri

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

dvh.hy.9902

Thành viên hoạt động

dvh.hy.9902

Thành viên hoạt động

gtri

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

Let'GâuGâu

Thành viên mới

File đính kèm

dvh.hy.9902

Thành viên hoạt động

dvh.hy.9902

Thành viên hoạt động

maya09

Thành viên mới

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

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

Đếm ngược thời gian