Bạn đang xem bài viết Danh Sách Được Lọc Với Tiêu Chí Trong Excel? được cập nhật mới nhất trên website Hoisinhvienqnam.edu.vn. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung 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.
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!
Tự Động Lọc Danh Sách Không Trùng Với Advanced Filter Vba Cực Kỳ Đơn Giản
Để 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
CÁCH SỬ DỤNG HÀM UNIQUE ĐỂ TỰ ĐỘNG LỌC DANH SÁCH KHÔNG TRÙNG TRONG EXCEL
=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.
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.
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ả.
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/
Làm Cách Nào Để Lọc Danh Sách Và Xóa Các Hàng Ẩn Hoặc Hiện Còn Lại Trong Excel?
Làm cách nào để lọc danh sách và xóa các hàng ẩn hoặc hiện còn lại trong Excel?
Đối với danh sách đã lọc, bạn có thể cần xóa các hàng ẩn hoặc hiện để chỉ giữ lại dữ liệu hữu ích. Trong bài viết này, chúng tôi sẽ chỉ cho bạn các phương pháp xóa các hàng ẩn hoặc hiện còn lại của danh sách đã lọc trong Excel.
Xóa các hàng ẩn trong trang tính đang hoạt động với mã VBA Xóa các hàng hiển thị của danh sách đã lọc bằng cách chọn tất cả các ô hiển thịDễ dàng xóa các hàng ẩn hoặc hiện của danh sách đã lọc với Kutools cho Excel
Xóa các hàng ẩn trong trang tính đang hoạt động với mã VBA
Phần này sẽ hiển thị cho bạn mã VBA để xóa các hàng ẩn trong trang tính đang hoạt động. Hãy làm như sau.
1. Kích hoạt trang tính bạn cần để xóa các hàng ẩn, nhấn Khác + F11 các phím đồng thời để mở Microsoft Visual Basic cho các ứng dụng cửa sổ.
Mã VBA: Xóa các hàng ẩn
Sub RemoveHiddenRows() Dim xRow As Range Dim xRg As Range Dim xRows As Range On Error Resume Next Set xRows = Intersect(ActiveSheet.Range("A:A").EntireRow, ActiveSheet.UsedRange) If xRows Is Nothing Then Exit Sub For Each xRow In xRows.Columns(1).Cells If xRow.EntireRow.Hidden Then If xRg Is Nothing Then Set xRg = xRow Else Set xRg = Union(xRg, xRow) End If End If Next If Not xRg Is Nothing Then MsgBox xRg.Count & " hidden rows have been deleted", , "Kutools for Excel" xRg.EntireRow.Delete Else MsgBox "No hidden rows found", , "Kutools for Excel" End If End Sub3. Nhấn nút F5 phím để chạy mã. Nếu có các hàng ẩn trong trang tính hiện hoạt, sau khi chạy mã, một hộp thoại sẽ bật lên để cho bạn biết có bao nhiêu hàng ẩn đã bị xóa. Nhấn vào OK để xóa các hàng ẩn. Xem ảnh chụp màn hình:
Nếu không, bạn sẽ nhận được hộp thoại sau sau khi chạy mã.
Chú thích: mã VBA trên không chỉ có thể xóa các hàng ẩn của danh sách đã lọc mà còn xóa các hàng ẩn mà bạn đã ẩn theo cách thủ công trước đó.
Xóa các hàng hiển thị của danh sách đã lọc với tính năng chọn tất cả các ô hiển thị
Để xóa các hàng hiển thị của danh sách đã lọc, vui lòng thực hiện như sau.
1. Chọn tất cả các hàng đã lọc ra và nhấn F5 chìa khóa để mở Go To hộp thoại, sau đó nhấp vào Đặc biệt cái nút. Xem ảnh chụp màn hình:
2. bên trong Đi đặc biệt hộp thoại, kiểm tra Chỉ các ô hiển thị rồi bấm vào OK .
3. Bây giờ tất cả các hàng hiển thị đã được chọn, nhấp chuột phải vào lựa chọn, sau đó nhấp vào Xóa Hàng.
Cho đến bây giờ, tất cả các hàng hiển thị đều bị xóa khỏi danh sách đã lọc.
Dễ dàng xóa các hàng ẩn hoặc hiện của danh sách đã lọc với Kutools cho Excel
Hai phương pháp trên có thể không phải là giải pháp mong muốn của nhiều người dùng Excel, ở đây chúng tôi xin giới thiệu với bạn một công cụ tiện dụng. Với Xóa hàng & cột ẩn (hiển thị) tiện ích của Kutools cho Excel, bạn có thể dễ dàng xóa các hàng ẩn trong phạm vi / trang tính đã chọn, trang tính hiện hoạt hoặc tất cả các trang tính trong Excel.
Trước khi áp dụng Kutools cho Excel, Xin vui lòng tải xuống và cài đặt nó trước tiên.
2. bên trong Xóa hàng & cột ẩn (hiển thị) hộp thoại, giữ Trong phạm vi đã chọn được lựa chọn trong Nhìn vào danh sách thả xuống (bạn có thể chọn các tùy chọn khác khi cần), hãy kiểm tra Hàng tùy chọn trong Xóa loại và trong Loại chi tiết phần, kiểm tra Hàng có thể nhìn thấy or Hàng ẩn tùy chọn như bạn cần. Và cuối cùng nhấp vào OK .
3. Sau đó, một hộp thoại bật lên cho bạn biết có bao nhiêu hàng đã bị xóa, vui lòng nhấp vào OK .
Nếu bạn muốn dùng thử miễn phí (30 ngày) tiện ích này, vui lòng nhấp để tải xuống, và sau đó đi đến áp dụng hoạt động theo các bước trên.
Xóa các hàng ẩn hoặc hiển thị của danh sách đã lọc bằng Kutools cho Excel
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 …
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.
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ập nhật thông tin chi tiết về Danh Sách Được Lọc Với Tiêu Chí Trong Excel? trên website Hoisinhvienqnam.edu.vn. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất. Chúc bạn một ngày tốt lành!