Giúp Lọc dữ liệu với pivot table bằng VBA (1 người xem)

[Cu Tồ]

Chào các bác anh chị trong diễn đàn!
Em có một ví dụ về lọc dữ liệu với Pivot table bằng VBA.lọc kết quả theo điều kiện và kết quả là tổng ạ.phía dưới là file đính kèm và mô tả mong các bác giúp đỡ!
trong file phần dữ liệu em có sử dụng tiếng việt có dấu cái này nếu không thích hợp có thể bỏ dấu đi được ạ

 

[Hai Lúa Miền Tây]

Chào các bác anh chị trong diễn đàn!
Em có một ví dụ về lọc dữ liệu với Pivot table bằng VBA.phí dưới là file đính kèm và mô tả mong các bác giúp đỡ!
trong file phần dữ liệu em có sử dụng tiếng việt có dấu cái này nếu không thích hợp có thể bỏ dấu đi được ạ

Bạn ghi lại macro về việc tạo Pivot Table theo hướng dẫn bên dưới sẽ ra nhé. Lưu ý nên chỉnh Tên Sheet và tiêu đề cột bằng tên không dấu cho dễ thao tác nhé.

 

[tigertiger]

Chào các bác anh chị trong diễn đàn!
Em có một ví dụ về lọc dữ liệu với Pivot table bằng VBA.phí dưới là file đính kèm và mô tả mong các bác giúp đỡ!
trong file phần dữ liệu em có sử dụng tiếng việt có dấu cái này nếu không thích hợp có thể bỏ dấu đi được ạ

File .xlsx thì sao có VBA?
cái này bạn dùng Pivot table bình thường, kết hợp với slicer thì chỉ cần 1 pivot table là đủ

 

[Cu Tồ]

Bạn ghi lại macro về việc tạo Pivot Table theo hướng dẫn bên dưới sẽ ra nhé. Lưu ý nên chỉnh Tên Sheet và tiêu đề cột bằng tên không dấu cho dễ thao tác nhé.

vâng e có ghi lại rồi nhưng phần điều kiện này e muốn lấy trực tiếp trên bảng tính thì làm như thế nào ạ
.PivotItems("điều kiện 1").Visible = False
.PivotItems("điều kiện 2").Visible = true
.PivotItems("điều kiện 3").Visible = true
.PivotItems("điều kiện 4").Visible = true
.PivotItems("điều kiện 5").Visible = true
.PivotItems("điều kiện 6").Visible =true
.PivotItems("điều kiện 7").Visible = true
.PivotItems("điều kiện8").Visible = true
.PivotItems("điều kiện 9").Visible = true
.PivotItems("điều kiện 10").Visible = true

Bài đã được tự động gộp: 18/6/20

File .xlsx thì sao có VBA?
cái này bạn dùng Pivot table bình thường, kết hợp với slicer thì chỉ cần 1 pivot table là đủ

tại vì số liệu thì sẽ thay đổi theo ngày còn lọc và điều kiện thì vẫn như thế nên e muốn làm bằng vba để đỡ thao tác bằng tay,và số liệu e chỉ cần là dạng value thôi như trong file thôi ạ

 

[tigertiger]

vâng e có ghi lại rồi nhưng phần điều kiện này e muốn lấy trực tiếp trên bảng tính thì làm như thế nào ạ
.PivotItems("điều kiện 1").Visible = False
.PivotItems("điều kiện 2").Visible = true
.PivotItems("điều kiện 3").Visible = true
.PivotItems("điều kiện 4").Visible = true
.PivotItems("điều kiện 5").Visible = true
.PivotItems("điều kiện 6").Visible =true
.PivotItems("điều kiện 7").Visible = true
.PivotItems("điều kiện8").Visible = true
.PivotItems("điều kiện 9").Visible = true
.PivotItems("điều kiện 10").Visible = true

Bài đã được tự động gộp: 18/6/20

tại vì số liệu thì sẽ thay đổi theo ngày còn lọc và điều kiện thì vẫn như thế nên e muốn làm bằng vba để đỡ thao tác bằng tay,và số liệu e chỉ cần là dạng value thôi như trong file thôi ạ

Vậy thì có 2 cách
- Viết code thông qua tạo pivottable và rồi copy value ra vị trí KQ (hình như bạn đang chọn cách này)
- Hoặc viết code lọc luôn (không qua pivot table)

Còn muốn gán giá trị ô thì chỉ cần
..... =Sheet2.Range("A1").Value
là lấy được giá trị A1 ở sheet2

 

[Cu Tồ]

Vậy thì có 2 cách
- Viết code thông qua tạo pivottable và rồi copy value ra vị trí KQ (hình như bạn đang chọn cách này)
- Hoặc viết code lọc luôn (không qua pivot table)

Còn muốn gán giá trị ô thì chỉ cần
..... =Sheet2.Range("A1").Value
là lấy được giá trị A1 ở sheet2

đúng như bác nói ,e chọn cách đầu,vì trình độ của e về vba là vỡ lòng,e có sử dụng record macro nhưng bị vướng ở phần điều kiện là làm sao để thay điều kiện 1,2,3.. bằng các giá trị trong bẳng tính,.
còn cách hai như bác nói thì e thực sự không làm được nên lên đây mong các bác giúp đỡ

 

[tigertiger]

đúng như bác nói ,e chọn cách đầu,vì trình độ của e về vba là vỡ lòng,e có sử dụng record macro nhưng bị vứng ở phần điều kiện là làm sao để thay điều kiện 1,2,3.. bằng các giá trị trong bẳng tính,.
còn cách hai như bác nói thì e thực sự không làm được nên lên đây mong các bác giúp đỡ

Vậy thì phải gửi file xlsm của bạn lên đây , có code mọi người mới sửa được
Còn nói chơi xuông thế này thì chỉ có nói qua nói lại kiểu trà đá vỉa hè thôi.

 

[Cu Tồ]

Vậy thì có 2 cách
- Viết code thông qua tạo pivottable và rồi copy value ra vị trí KQ (hình như bạn đang chọn cách này)
- Hoặc viết code lọc luôn (không qua pivot table)

Còn muốn gán giá trị ô thì chỉ cần
..... =Sheet2.Range("A1").Value
là lấy được giá trị A1 ở sheet2

cách thứ hai mà bác nói thì có dễ hơn không bác?vì nó là lọc nhưng kết quả là tính tổng bác ạ

 

[tigertiger]

cách thứ hai mà bác nói thì có dễ hơn không bác?vì nó là lọc nhưng kết quả là tính tổng bác ạ

Xem trong file của bạn thì toàn là 1 mã riêng lẻ mà, sao tính tổng, túm lại là nếu đang theo đường 1 thì cứ đường 1 mà đi

 

[Cu Tồ]

Xem trong file của bạn thì toàn là 1 mã riêng lẻ mà, sao tính tổng, túm lại là nếu đang theo đường 1 thì cứ đường 1 mà đi

e cũng chưa hình dung được cách thứ hai mà bác nói nhưng cách một thì pivot table có chức năng tính tổng luôn ạ? bác cho hỏi với phần điều kiện này thì e muốn nó tương ứng với các ô sheet2 A1,2 trong bảng tính,xong paste vào đúng vùng chứa kết quả thì phần điều kiển này viết như thế nào ạ
.PivotItems("điều kiện 1").Visible = true
và chỉ khác ở phần điều kiện và vùng pase thì có thể dùng vòng lặp được không ạ?

 

[Hai Lúa Miền Tây]

e cũng chưa hình dung được cách thứ hai mà bác nói nhưng cách một thì pivot table có chức năng tính tổng luôn ạ? bác cho hỏi với phần điều kiện này thì e muốn nó tương ứng với các ô sheet2 A1,2 trong bảng tính,xong paste vào đúng vùng chứa kết quả thì phần điều kiển này viết như thế nào ạ
.PivotItems("điều kiện 1").Visible = true
và chỉ khác ở phần điều kiện và vùng pase thì có thể dùng vòng lặp được không ạ?

Tôi không dùng Pivot mà là dùng ADO, bạn chỉnh sửa tên sheet 'Số Liệu' thành 'SoLieu' và Sheet 'Phân Tích' thành 'PhanTich' rồi chèn code sau vào chạy thử nhé:

Sub LocDL_HLMT()
    With CreateObject("ADODB.Connection")
        .Open ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0;HDR=No""")
        Sheet2.Range("B29").CopyFromRecordset .Execute("Select F1,Sum(F3) from [SoLieu$] where F2 In (Select F1 from [PhanTich$] where F1 is not null) group by F1")
        Sheet2.Range("F29").CopyFromRecordset .Execute("Select F1,Sum(F4) from [SoLieu$] where F2 In (Select F5 from [PhanTich$] where F5 is not null) group by F1")
        Sheet2.Range("J29").CopyFromRecordset .Execute("Select F1,Sum(F3) from [SoLieu$] where F2 In (Select F9 from [PhanTich$] where F9 is not null) group by F1")
        Sheet2.Range("N29").CopyFromRecordset .Execute("Select F1,Sum(F3) from [SoLieu$] where F2 In (Select F13 from [PhanTich$] where F13 is not null) group by F1")
    End With
End Sub

 

[Cu Tồ]

Tôi không dùng Pivot mà là dùng ADO, bạn chỉnh sửa tên sheet 'Số Liệu' thành 'SoLieu' và Sheet 'Phân Tích' thành 'PhanTich' rồi chèn code sau vào chạy thử nhé:

Sub LocDL_HLMT()
    With CreateObject("ADODB.Connection")
        .Open ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0;HDR=No""")
        Sheet2.Range("B29").CopyFromRecordset .Execute("Select F1,Sum(F3) from [SoLieu$] where F2 In (Select F1 from [PhanTich$] where F1 is not null) group by F1")
        Sheet2.Range("F29").CopyFromRecordset .Execute("Select F1,Sum(F4) from [SoLieu$] where F2 In (Select F5 from [PhanTich$] where F5 is not null) group by F1")
        Sheet2.Range("J29").CopyFromRecordset .Execute("Select F1,Sum(F3) from [SoLieu$] where F2 In (Select F9 from [PhanTich$] where F9 is not null) group by F1")
        Sheet2.Range("N29").CopyFromRecordset .Execute("Select F1,Sum(F3) from [SoLieu$] where F2 In (Select F13 from [PhanTich$] where F13 is not null) group by F1")
    End With
End Sub

bị lỗi rồi bác ạ

 

[Hai Lúa Miền Tây]

View attachment 239488View attachment 239489

bị lỗi rồi bác ạ

Thử lưu file lại rồi chạy code lại nhé bạn

 

[tigertiger]

e cũng chưa hình dung được cách thứ hai mà bác nói nhưng cách một thì pivot table có chức năng tính tổng luôn ạ? bác cho hỏi với phần điều kiện này thì e muốn nó tương ứng với các ô sheet2 A1,2 trong bảng tính,xong paste vào đúng vùng chứa kết quả thì phần điều kiển này viết như thế nào ạ
.PivotItems("điều kiện 1").Visible = true
và chỉ khác ở phần điều kiện và vùng pase thì có thể dùng vòng lặp được không ạ?

Chú ý tôi đã viết trên

Vậy thì phải gửi file xlsm của bạn lên đây , có code mọi người mới sửa được
Còn nói chơi xuông thế này thì chỉ có nói qua nói lại kiểu trà đá vỉa hè thôi.

Và chọn cách 2 thì học theo HLMT bài 11 cũng là 1 phương án nhé

 

[Cu Tồ]

FIle đã lưu nhưng vẫn không chạy được bác ạ

 

[Cu Tồ]

Thử lưu file lại rồi chạy code lại nhé bạn

code chỉ chạy trên office 16 bác ạ. office 10 với kingsoft thì không chạy bác ạ

Bài đã được tự động gộp: 18/6/20

Chú ý tôi đã viết trên


Và chọn cách 2 thì học theo HLMT bài 11 cũng là 1 phương án nhé

em đang làm file ví dụ để học bác ạ,e dùng record macro nhưng không được nên lên đây hỏi các bác chứ không có file sẵn áp dụng bác ạ

 

[Hai Lúa Miền Tây]

code chỉ chạy trên office 16 bác ạ. office 10 với kingsoft thì không chạy bác ạ

Bài đã được tự động gộp: 18/6/20

em đang làm file ví dụ để học bác ạ,e dùng record macro nhưng không được nên lên đây hỏi các bác chứ không có file sẵn áp dụng bác ạ

Code của tôi không chạy được với WPS Office, vậy tôi không thể hỗ trợ bạn được rồi.

 

[tigertiger]

em đang làm file ví dụ để học bác ạ,e dùng record macro nhưng không được nên lên đây hỏi các bác chứ không có file sẵn áp dụng bác ạ

Vậy bạn tự search (tìm kiếm) vậy sẽ vỡ và học được nhiều vấn đề. Thông cảm tôi thường làm việc thật thực tế, kiểu phải có và trông thấy bột mới gột lên hồ.

 

[Cu Tồ]

vâng cảm ơn hai bác ạ

 

[Cu Tồ]

Vậy bạn tự search (tìm kiếm) vậy sẽ vỡ và học được nhiều vấn đề. Thông cảm tôi thường làm việc thật thực tế, kiểu phải có và trông thấy bột mới gột lên hồ.

Vâng cũng không phải là em chỉ làm chơi để lên hỏi mà em chưa xây dựng được bản thật,vì em đang tìm hiểu phần này nếu xong thì e mới bắt đầu làm hoàn chỉnh,vì theo hướng bản mà e xây dựng thì sẽ lọc dữ liệu ra như thế

 

[Hai Lúa Miền Tây]

Vâng cũng không phải là em chỉ làm chơi để lên hỏi mà em chưa xây dựng được bản thật,vì em đang tìm hiểu phần này nếu xong thì e mới bắt đầu làm hoàn chỉnh,vì theo hướng bản mà e xây dựng thì sẽ lọc dữ liệu ra như thế

Nếu muốn được hỗ trợ nhanh thì nên dùng MS Office thay vì dùng WPS Office.

 

[Cu Tồ]

Nếu muốn được hỗ trợ nhanh thì nên dùng MS Office thay vì dùng WPS Office.

e có dùng ms office,nhưng code hôm qua bác viết không hiểu sao office 2010 của e lại báo lỗi không chạy,hình như chỉ chạy từ bản 2016 trở lên thì phải

 

[Hai Lúa Miền Tây]

e có dùng ms office,nhưng code hôm qua bác viết không hiểu sao office 2010 của e lại báo lỗi không chạy,hình như chỉ chạy từ bản 2016 trở lên thì phải

Trên Excel 2010 báo lỗi như thế nào bạn? Vì code trên có thể dùng được từ phiên bản 2007.

 

[Cu Tồ]

Trên Excel 2010 báo lỗi như thế nào bạn? Vì code trên có thể dùng được từ phiên bản 2007.

lỗi như trên bác ạ,hay do bản của e cài thiếu cái gì ạ?bác cho e hỏi thêm là nếu dùng phương án này thì với dữ liệu với tầm 10k dòng thì code chạy ổn không bác?

 

[Hai Lúa Miền Tây]

View attachment 239515
lỗi như trên bác ạ,hay do bản của e cài thiếu cái gì ạ?bác cho e hỏi thêm là nếu dùng phương án này thì với dữ liệu với tầm 10k dòng thì code chạy ổn không bác?

Bạn giúp tôi vào Tools\References xem coi có những cái sau không nhé.



À mà cùng 1 máy của bạn có cả 2 phiên bản 2010 và 2016? Hay là trên các máy khác nhau?

 

[Cu Tồ]

Bạn giúp tôi vào Tools\References xem coi có những cái sau không nhé.

View attachment 239518

À mà cùng 1 máy của bạn có cả 2 phiên bản 2010 và 2016? Hay là trên các máy khác nhau?

có này bác ơi,cùng 1 máy bác ạ, wps và office 2010 báo lỗi,còn 2016 thì chạy phà phà bác ạ

 

[Hai Lúa Miền Tây]

View attachment 239519
có này bác ơi,cùng 1 máy bác ạ, wps và office 2010 báo lỗi,còn 2016 thì chạy phà phà bác ạ

Vậy giúp tôi chọn cái 2.6 hoặc 1 trong số đó và thử chạy code sau nhé.

Sub LocDL_HLMT()
    Dim Cn As New ADODB.Connection
    With Cn
        .Open ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0;HDR=No""")
        Sheet2.Range("B29").CopyFromRecordset .Execute("Select F1,Sum(F3) from [SoLieu$] where F2 In (Select F1 from [PhanTich$] where F1 is not null) group by F1")
        Sheet2.Range("F29").CopyFromRecordset .Execute("Select F1,Sum(F4) from [SoLieu$] where F2 In (Select F5 from [PhanTich$] where F5 is not null) group by F1")
        Sheet2.Range("J29").CopyFromRecordset .Execute("Select F1,Sum(F3) from [SoLieu$] where F2 In (Select F9 from [PhanTich$] where F9 is not null) group by F1")
        Sheet2.Range("N29").CopyFromRecordset .Execute("Select F1,Sum(F3) from [SoLieu$] where F2 In (Select F13 from [PhanTich$] where F13 is not null) group by F1")
    End With
End Sub

 

[Cu Tồ]

Vậy giúp tôi chọn cái 2.6 hoặc 1 trong số đó và thử chạy code sau nhé.

Sub LocDL_HLMT()
    Dim Cn As New ADODB.Connection
    With Cn
        .Open ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0;HDR=No""")
        Sheet2.Range("B29").CopyFromRecordset .Execute("Select F1,Sum(F3) from [SoLieu$] where F2 In (Select F1 from [PhanTich$] where F1 is not null) group by F1")
        Sheet2.Range("F29").CopyFromRecordset .Execute("Select F1,Sum(F4) from [SoLieu$] where F2 In (Select F5 from [PhanTich$] where F5 is not null) group by F1")
        Sheet2.Range("J29").CopyFromRecordset .Execute("Select F1,Sum(F3) from [SoLieu$] where F2 In (Select F9 from [PhanTich$] where F9 is not null) group by F1")
        Sheet2.Range("N29").CopyFromRecordset .Execute("Select F1,Sum(F3) from [SoLieu$] where F2 In (Select F13 from [PhanTich$] where F13 is not null) group by F1")
    End With
End Sub

e đã thử rồi bác ạ,chọn 2.6 và sau chọn lại 6.1 chạy code thì excel tự đóng luôn bác ạ?cảm ơn bác giúp đỡ,phương thức thứ hai này em cũng chưa quen lắm nên hơi mơ hồ,để e đi tìm hiểu thêm phần pivot table xem như thế nào à,chữ như thế này tốn công bác lắm

 

[Cu Tồ]

Em có dùng record macro và chỉnh sửa một ít được như file ở dưới,mong hai bác giúp e làm sao cho code ngắn gọn hơn được không ạ? và có thế đưa điều kiện lọc là lấy ở bảng tính để khi thêm mục lọc và điều kiện lọc thì thuận tiện hơn,và các mục lóc điều như nhau chỉ khác ở phần điều kiện lọc và vùng paste dữ liệu thì có thể dùng vòng lặp được không ạ?