Đề Xuất 12/2022 # Tự Động Lọc Danh Sách Không Trùng Với Advanced Filter Vba Cực Kỳ Đơn Giản / 2023 # Top 16 Like | Beiqthatgioi.com

Đề Xuất 12/2022 # Tự Động Lọc Danh Sách Không Trùng Với Advanced Filter Vba Cực Kỳ Đơn Giản / 2023 # Top 16 Like

Cập nhật nội dung chi tiết về Tự Động Lọc Danh Sách Không Trùng Với Advanced Filter Vba Cực Kỳ Đơn Giản / 2023 mới nhất trên website Beiqthatgioi.com. Hy vọng thông tin trong bài viết sẽ đáp ứng được nhu cầu ngoài mong đợi của bạn, chúng tôi sẽ làm việc thường xuyên để cập nhật nội dung mới nhằm giúp bạn nhận được thông tin nhanh chóng và chính xác nhất.

Để giúp các bạn dễ hình dung thì chúng ta cùng thực hiện yêu cầu sau:

Trong bảng dữ liệu trên, cột Tên mặt hàng có rất nhiều mặt hàng và có thể lặp lại hoặc thêm mới những mặt hàng khác. Và yêu cầu của chúng ta là:

Lọc ra 1 danh sách tên các mặt hàng không trùng (chỉ xuất hiện 1 lần trong danh sách)

Sau mỗi lần thay đổi nội dung ở cột Tên mặt hàng thì danh sách không trùng đó sẽ được cập nhật lại.

Cách thực hiện như sau:

Để có thể thực hiện thao tác record macro trước hết các bạn cần lưu ý:

File của bạn sẽ có sử dụng macro. Khi lưu file chú ý lưu ở định dạng Workbook enable macro (.xlsm)

Mở thẻ developer trong Excel để sử dụng được thao tác record macro

Trong thẻ developer chọn Record macro tại:

Action: chọn copy to another location để copy danh sách ra 1 vị trí khác

List range: vùng danh sách cần lọc trùng. Ở đây phải chọn từ tên tiêu đề (dòng thứ 1)

Criteria range: để trống

Copy to: chọn 1 vị trí cần xuất danh sách đó ra

Unique records only: đánh dấu chọn mục này để chỉ lấy những dữ liệu không trùng

Sau khi lọc ra vị trí ô G1 chúng ta được kết quả là danh sách tên các mặt hàng không trùng.

Kết thúc việc record macro, chúng ta trở lại thẻ developer và chọn Stop recording

Cùng kiểm tra câu lệnh VBA vừa record được:

Sub Macro1()

Range(“C1:C19”).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range(“G1”), Unique:=True

End Sub

2. Tự động cập nhật danh sách mỗi khi dữ liệu thay đổi

a. Thay đổi phạm vi vùng dữ liệu

Trong câu lệnh macro thì vùng Range(“C1:C19”) là vùng dữ liệu ban đầu. Chúng ta có thể xác định rõ hơn với 2 yếu tố:

Do vùng này đặt tại sheet1 nên ta có thể viết lại như sau:

Sheet1.Range(“C1:C100”)

Thay vì chỉ áp dụng từ C1 tới C19 thì chúng ta có thể mở rộng sẵn vùng này

b. Thay đổi vị trí xuất kết quả

Kết quả danh sách được trích xuất là Range(“G1”).

Chúng ta có thể thay đổi sang Sheet khác, vị trí khác. Ví dụ đặt tại Sheet2, tại ô A1

Sheet2.Range(“A1”)

Câu lệnh có thể viết lại là:

Sub Macro1()

Sheet1.Range(“C1:C100”).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheet2.Range(“A1”), Unique:=True

End Sub

c. Gắn macro vào sự kiện thay đổi trong bảng dữ liệu

Viết lệnh với sự kiện worksheet.change tại sheet1 như sau:

Như vậy là chúng ta đã hoàn thành được cách ứng dụng Advanced Filter trong VBA để có thể tự động lọc danh sách không trùng.

Tuy nhiên trong câu lệnh trên cũng chứa nhiều điểm hạn chế:

Bảng dữ liệu gốc có chứa dòng trống sẽ khiến xuất hiện dòng trống trong danh sách được lọc.

Không được thay đổi tiêu đề của cột dữ liệu lọc tại bảng dữ liệu và tại vị trí kết quả danh sách được lọc.

Để giải quyết 2 vấn đề trên thì chúng ta có thể tìm hiểu thêm 1 số phương pháp sau:

Thêm câu lệnh sắp xếp dữ liệu trong danh sách sau khi lọc. Việc sắp xếp dữ liệu sẽ đẩy dòng trống xuống dưới, tránh việc có dòng trống xen kẽ trong danh sách.

Thêm câu lệnh xóa toàn bộ vùng kết quả danh sách được lọc, bao gồm cả tên tiêu đề trước mỗi lần cập nhật danh sách. Như vậy sẽ không mắc lỗi tên tiêu đề không phù hợp.

Hướng Dẫn Cách Sử Dụng Hàm Unique Tự Động Lọc Danh Sách Không Trùng Trong Excel – Tinh Tinh / 2023

CÁCH SỬ DỤNG HÀM UNIQUE ĐỂ TỰ ĐỘNG LỌC DANH SÁCH KHÔNG TRÙNG TRONG EXCEL

CẤU TRÚC CỦA HÀM UNIQUE

=UNIQUE(array,[by_col],[occurs_once])

array: mảng/vùng dữ liệu cần lọc (danh sách có dữ liệu trùng)

by_col: phương thức so sánh trùng theo cột (TRUE) hay theo hàng (FALSE). Mặc định là FALSE.

occurs_once: chỉ tính các giá trị xuất hiện 1 lần duy nhất trong danh sách (TRUE) hay các giá trị xuất hiện nhiều lần (FALSE). Mặc định là FALSE.

Ví dụ:

Trong danh sách từ A3:A11 có các mặt hàng xuất hiện nhiều lần. Nhưng thực chất chỉ bao gồm 3 mặt hàng. Khi sử dụng hàm UNIQUE cho vùng A3:A11 tại ô F3 ta thu được kết quả là mảng F3:F5 gồm 3 mặt hàngcần tìm.

NHỮNG CHÚ Ý KHI SỬ DỤNG HÀM UNIQUE

Có lẽ chúng ta sẽ gặp khó khi tìm hiểu 2 đối số còn lại trong hàm UNIQUE là by_col và occurs_once. Để làm rõ hơn nội dung này chúng ta xem ví dụ dưới đây:

LỌC TRÙNG THEO CỘT

Trong bảng kê bán hàng theo ngày, chúng ta cần lọc ra danh sách các mặt hàng bán được (không trùng) của mỗi nhân viên.

Trong trường hợp này chúng ta sử dụng hàm UNIQUE để lọc danh sách không trùng theo cột.

Công thức tại G4 sẽ tham chiếu tới vùng B4:D4, lọc trùng theo cột.

Từ G4 filldown công thức tới G8 để lấy kết quả cho các nhân viên còn lại.

LẤY RA NHỮNG GIÁ TRỊ XUẤT HIỆN CHỈ 1 LẦN TRONG DANH SÁCH

Tham số occurs_once sẽ thực hiện chức năng này khi chọn là TRUE.

Trong danh sách các mặt hàng A3:A11  chúng ta thấy có các mặt hàng: Táo, Lê, Cam, Xoài, Ổi

Nhưng chỉ có 2 mặt hàng là xuất hiện chỉ 1 lần là Xoài và Ổi. Các mặt hàng khác: Táo 3 lần, Lê 2 lần, Cam 2 lần.

Nếu không có giá trị nào xuất hiện chỉ 1 lần thì sẽ xuất hiện thông báo lỗi #CALC! có nghĩa là không tính toán được kết quả.

KẾT LUẬN

Như vậy chúng ta đã biết cách sử dụng hàm UNIQUE rồi phải không nào. Hàm này có tính ứng dụng rất cao khi kết hợp với Data Validation/ List để tạo danh sách chọn giá trị không trùng. Có thể áp dụng trong các báo cáo tổng hợp, báo cáo chi tiết rất hiệu quả.

Ngoài ra hàm này có thể kết hợp thêm với hàm SORT để vừa lọc giá trị không trùng, vừa sắp xếp danh sách các giá trị này theo ý muốn.

Nguồn: https://blog.hocexcel.online/

Danh Sách Được Lọc Với Tiêu Chí Trong Excel? / 2023

Làm thế nào để đếm dữ liệu / danh sách được lọc với tiêu chí trong Excel?

Bạn có thể nhận thấy rằng bất kể bạn đã lọc bảng của mình hay chưa, hàm COUNTIF sẽ bỏ qua việc lọc và trả về một giá trị cố định. Trong một số trường hợp, bạn được yêu cầu đếm dữ liệu đã lọc với một tiêu chí cụ thể, vậy làm thế nào để hoàn thành việc này? Trong bài viết này, tôi sẽ giới thiệu một số cách để đếm dữ liệu / danh sách đã lọc trong Excel một cách nhanh chóng.

Countif đã lọc dữ liệu với tiêu chí bằng cách thêm cột trợ giúp trong Excel

Trong bài viết này, tôi sẽ lấy bảng sau làm ví dụ. Ở đây, tôi đã lọc ra Julie và Nicole trong cột Người bán hàng.

Dữ liệu gốc:

Dữ liệu đã lọc:

Phương pháp này sẽ hướng dẫn bạn thêm một cột trợ giúp bổ sung và sau đó bạn có thể áp dụng hàm COUNTIFS để đếm dữ liệu đã lọc trong Excel. (Chú thích: Phương pháp này yêu cầu bạn phải lọc bảng gốc của mình trước khi làm theo các bước.)

1. Tìm một ô trống bên cạnh bảng đã lọc ban đầu, cho biết Ô G2, nhập = IF (B2 = “Quả lê”, 1, “”), sau đó kéo Fill Handle đến phạm vi bạn cần. (Lưu ý: Trong công thức của = IF (B2 = “Quả lê”, 1, “”), B2 là ô bạn sẽ đếm và “Quả lê” là tiêu chí bạn sẽ đếm.) Bây giờ một cột trợ giúp được thêm vào bên cạnh bảng đã lọc ban đầu. “1” cho biết đó là quả lê trong Cột B, trong khi dấu hiệu trống cho biết nó không phải quả lê trong Cột B.

2. Tìm một ô trống và nhập công thức =COUNTIFS(B2:B18,”Pear”,G2:G18,”1″)và nhấn Đi vào Chìa khóa. (Lưu ý: Trong công thức của =COUNTIFS(B2:B18,”Pear”,G2:G18,”1″), B2: B18 và G2: G18 là các phạm vi bạn sẽ đếm, và “Quả lê” và “1” là tiêu chí bạn sẽ tính theo.)

Bây giờ bạn sẽ nhận được số đếm cùng một lúc. Xin lưu ý rằng số đếm sẽ không thay đổi nếu bạn tắt tính năng lọc hoặc thay đổi tính năng lọc.

Tổng / Đếm / Trung bình các ô hiển thị chỉ trong một phạm vi cụ thể với việc bỏ qua các ô / hàng / cột ẩn hoặc được lọc

Hàm SUM / Đếm / Trung bình thông thường sẽ đếm tất cả các ô trong phạm vi được chỉ định trên các ô vật chất có bị ẩn / lọc hay không. Trong khi hàm Subtotal chỉ có thể tính tổng / đếm / trung bình với việc bỏ qua các hàng ẩn. Tuy nhiên, Kutools cho Excel TÓM TẮT/HỢP TÁC/TRUNG THỰC các hàm sẽ dễ dàng tính toán phạm vi được chỉ định mà bỏ qua bất kỳ ô, hàng hoặc cột nào bị ẩn.

Countif đã lọc dữ liệu với tiêu chí bằng các hàm Excel

Nếu bạn muốn số đếm thay đổi khi bộ lọc thay đổi, bạn có thể áp dụng các hàm SUMPRODUCT trong Excel như sau: Trong một ô trống, hãy nhập công thức =SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B18,ROW(B2:B18)-MIN(ROW(B2:B18)),,1)),ISNUMBER(SEARCH(“Pear”,B2:B18))+0)và nhấn Đi vào Chìa khóa.

Ghi chú: (1) Trong công thức trên, B2: B18 là phạm vi bạn sẽ đếm và “Quả lê” là tiêu chí bạn sẽ tính theo. (2) Giá trị trả về sẽ thay đổi khi bạn tắt tính năng lọc hoặc thay đổi lọc.

Dễ dàng chia phạm vi thành nhiều trang tính dựa trên tiêu chí trong một cột trong Excel

So với các công thức mảng phức tạp, việc lưu tất cả các bản ghi đã lọc vào một trang tính mới có thể dễ dàng hơn nhiều, sau đó áp dụng hàm Đếm để đếm phạm vi hoặc danh sách dữ liệu đã lọc.

Kutools cho Excel’s Dữ liệu phân tách tiện ích có thể giúp người dùng Excel dễ dàng chia một phạm vi thành nhiều trang tính dựa trên tiêu chí trong một cột của phạm vi ban đầu.

Các công cụ năng suất văn phòng tốt nhất

Kutools cho Excel giải quyết hầu hết các vấn đề của bạn và tăng 80% năng suất của bạn

Tái sử dụng:

Chèn nhanh

công thức phức tạp, biểu đồ

 và bất cứ thứ gì bạn đã sử dụng trước đây;

Mã hóa ô

với mật khẩu;

Tạo danh sách gửi thư

và gửi email …

Super Formula Bar

(dễ dàng chỉnh sửa nhiều dòng văn bản và công thức);

Bố cục đọc

(dễ dàng đọc và chỉnh sửa số lượng ô lớn);

Dán vào Dải ô đã Lọc

hữu ích. Cảm ơn !

Hợp nhất các ô / hàng / cột

mà không làm mất dữ liệu; Nội dung phân chia ô;

Kết hợp các hàng / cột trùng lặp

… Ngăn chặn các ô trùng lặp;

So sánh các dãy

hữu ích. Cảm ơn !

Chọn trùng lặp hoặc duy nhất

Hàng;

Chọn hàng trống

(tất cả các ô đều trống);

Tìm siêu và Tìm mờ

trong Nhiều Sổ làm việc; Chọn ngẫu nhiên …

Bản sao chính xác

Nhiều ô mà không thay đổi tham chiếu công thức;

Tự động tạo tài liệu tham khảo

sang Nhiều Trang tính;

Chèn Bullets

, Hộp kiểm và hơn thế nữa …

Trích xuất văn bản

, Thêm Văn bản, Xóa theo Vị trí,

Xóa không gian

; Tạo và In Tổng số phân trang;

Chuyển đổi giữa nội dung ô và nhận xét

hữu ích. Cảm ơn !

Siêu lọc

(lưu và áp dụng các lược đồ lọc cho các trang tính khác);

Sắp xếp nâng cao

theo tháng / tuần / ngày, tần suất và hơn thế nữa;

Bộ lọc đặc biệt

bằng cách in đậm, in nghiêng …

Kết hợp Workbook và WorkSheets

; Hợp nhất các bảng dựa trên các cột chính;

Chia dữ liệu thành nhiều trang tính

;

Chuyển đổi hàng loạt xls, xlsx và PDF

hữu ích. Cảm ơn !

Hơn 300 tính năng mạnh mẽ

. Hỗ trợ Office / Excel 2007-2019 và 365. Hỗ trợ tất cả các ngôn ngữ. Dễ dàng triển khai trong doanh nghiệp hoặc tổ chức của bạn. Đầy đủ các tính năng dùng thử miễn phí 30 ngày. Đảm bảo hoàn tiền trong 60 ngày.

Tab Office mang lại giao diện Tab cho Office và giúp công việc của bạn trở nên dễ dàng hơn nhiều

Cho phép chỉnh sửa và đọc theo thẻ trong Word, Excel, PowerPoint

, Publisher, Access, Visio và Project.

Mở và tạo nhiều tài liệu trong các tab mới của cùng một cửa sổ, thay vì trong các cửa sổ mới.

Tăng 50% năng suất của bạn và giảm hàng trăm cú nhấp chuột cho bạn mỗi ngày!

Cách Sử Dụng Lọc Dữ Liệu – Advanced Filter Trong Microsoft Excel / 2023

Ngoài công cụ lọc dữ liệu là Filter thì Excel còn hỗ trợ công cụ lọc nâng cao là Advanced Filter. So với cách lọc dữ liệu bằng Filter thì Advanced Filter có nhiều ưu điểm hơn. Advanced Filter giúp bạn lọc dữ liệu theo các tiêu chí cài đặt sẵn, điều chỉnh tiêu chí ngay tại các Cell.

Vì vậy, thành thạo công cụ Advanced Filter thì nó sẽ hỗ trợ bạn rất nhiều trong việc sử dụng Excel để quản lý dữ liệu.

Cách sử dụng lọc dữ liệu – Advanced Filter trong Microsoft Excel

1

Cách tạo Advanced Filter

2

Hướng dẫn thực hiện lọc dữ liệu bằng  Advanced Filter

👉 Lưu ý:được sử dụng khi bạn đã lọc dữ liệu. Nút này cho phép bạn xóa kết quả lọc để đưa bảng dữ liệu về ban đầu.

Sau khi chọn Advanced, thì bạn sẽ được của sổ Advanced Filter. Trong cửa sổ này bao gồm các trường sau:

Filter the list, in-place: tức là kết quả lọc sẽ Fill ngay tại bảng tính, tương tự với lọc dữ liệu theo kiểu Filter.

Copy to another location: kết quả lọc sẻ được copy tới địa chỉ khác theo chỉ định trong trường (5).

Criteria range: vùng tiêu chí lọc. Lưu ý: bao gồm cả Tên cột và tên cột phải giống với tên cột của bảng cần lọc dữ liệu. Excel sẽ so sánh theo Tên cột để lọc giá trị.

Copy to: chỉ định vị trí sẽ hiển thị kết quả lọc nếu bạn chọn Copy to another location (2).

Unique records only: nếu kết quả lọc có nhiều hàng giống nhau thì chỉ lấy 1 hàng.

Ví dụ Thực hiện lọc với 1 hàng tiêu chí  ( phép AND)

Chọn Filter the list, in-place.

List range: chọn vùng lọc dữ liệu là: $A$9:$I$37

Criteria range: chọn điều kiện lọc: $C$3:$I$4. Trong Advanced Filter các bạn có thể liệt kê tất cả các Tên cột của bảng dữ liệu cần lọc, những tiêu chí không muốn lọc thì để ô giá trị ngay bên dưới rỗng. Excel chỉ lấy các tiêu chí lọc khi có giá trị khác rỗng để tìm kiếm.

Kết quả sẽ được Tên thiết bị là laptop AND Hãng là dell AND Tình trạng là Đang SD.

Trong Excel, các tiêu chí trong 1 hàng là phép AND (Và), tức là tc_1 AND tc_2 AND tc_3…Excel không lấy các kết quả nếu không thỏa hết các tiêu chí.

Ví dụ thực hiện lọc với 2 hàng tiêu chí (phép OR)

Chọn Filter the list, in-place.

List range: chọn vùng lọc dữ liệu là: $A$9:$I$37

Criteria range: chọn tiêu chí lọc: $C$3:$I$

(Data là tên sheets).

👉 Kết quả sẽ được như sau: Tất cả các hàng thỏa điều kiện ( Tên thiết bị là laptop AND Hãng là dell AND Tình trạng là Đang SD) OR (Tên thiết bị là pc AND Tình trạng là Đang SD).

⇒ Như vậy, lọc giữa các hàng với nhau là phép OR (hợp), tức là tc_h1 OR tc_h2 OR ….

3

Lọc với các tiêu chí chứa dấu so sánh.

Mặc định là so sánh bằng, và bạn có thể thêm vào các dấu so sánh sau:

<: nhỏ hơn

<=: nhỏ hơn hoặc bằng.

💡 Lưu ý: sử dụng điều kiện so sánh khi cột được định dàng là số, ngày tháng. Kết quả trả về không chính xác nếu sử dụng ở cột định dạng Text.

4

Lọc dữ liệu qua tiêu chí xử lý trung gian

Ví dụ Thêm một tiêu chí có tên là Tình trạng = [True/False]

Đầu tiên, mình dùng hàm “=OR(I10=”Kho”)” để trả về kết quả TRUE hoặc FALSE.

Thực hiện lọc Advanced Filter như các ví dụ trên với tiêu chí Tình trạng = [True/False]

👉 Kết quả sẽ được tất cả các hàng có giá trị là Kho trong cột Tình trạng. Vậy kết quả lọc này có nghĩa là khi lọc các giá trị của cột Tình trạng sẽ so sánh với hàm “=OR(I10=”Kho”)” nếu là TRUE thì sẽ được lọc đưa ra bảng dữ liệu lọc.

Bạn đang đọc nội dung bài viết Tự Động Lọc Danh Sách Không Trùng Với Advanced Filter Vba Cực Kỳ Đơn Giản / 2023 trên website Beiqthatgioi.com. Hy vọng một phần nào đó những thông tin mà chúng tôi đã cung cấp là rất hữu ích với bạn. Nếu nội dung bài viết hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất. Chúc bạn một ngày tốt lành!