Top 4 # Xem Nhiều Nhất Xóa Lọc Trong Excel Mới Nhất 3/2023 # Top Like

Chủ đề này là cổ xưa, nhưng tôi không hài lòng với bất kỳ câu trả lời nào, và cuối cùng tôi tự viết. Tôi đang chia sẻ nó ngay bây giờ:

Chúng tôi bắt đầu với:

Sub ResetWSFilters(ws as worksheet) If ws.FilterMode Then ws.ShowAllData Else End If 'This gets rid of "normal" filters - but tables will remain filtered For Each listObj In ws.ListObjects If listObj.ShowHeaders Then listObj.AutoFilter.ShowAllData listObj.Sort.SortFields.Clear End If Next listObj 'And this gets rid of table filters End Sub

Chúng ta có thể cung cấp một bảng tính cụ thể cho macro này, nó sẽ chỉ lọc ra một bảng tính đó. Hữu ích nếu bạn cần đảm bảo chỉ cần một bảng tính là rõ ràng. Tuy nhiên, tôi thường muốn làm toàn bộ sách bài tập

Sub ResetAllWBFilters(wb as workbook) Dim ws As Worksheet Dim wb As Workbook Dim listObj As ListObject For Each ws In wb.Worksheets If ws.FilterMode Then ws.ShowAllData Else End If 'This removes "normal" filters in the workbook - however, it doesn't remove table filters For Each listObj In ws.ListObjects If listObj.ShowHeaders Then listObj.AutoFilter.ShowAllData listObj.Sort.SortFields.Clear End If Next listObj Next 'And this removes table filters. You need both aspects to make it work. End Sub

Bạn có thể sử dụng điều này, ví dụ, bằng cách mở một sổ làm việc bạn cần xử lý và đặt lại các bộ lọc của chúng trước khi làm bất cứ điều gì với nó:

Sub ExampleOpen() Set TestingWorkBook = Workbooks.Open("C:Intel......") 'The .open is assuming you need to open the workbook in question - different procedure if it's already open Call ResetAllWBFilters(TestingWorkBook) End Sub

Công cụ tôi sử dụng nhiều nhất: Đặt lại tất cả các bộ lọc trong sổ làm việc mà mô-đun được lưu trữ trong:

Sub ResetFilters() Dim ws As Worksheet Dim wb As Workbook Dim listObj As ListObject Set wb = ThisWorkbook 'Set wb = ActiveWorkbook 'This is if you place the macro in your personal wb to be able to reset the filters on any wb you're currently working on. Remove the set wb = thisworkbook if that's what you need For Each ws In wb.Worksheets If ws.FilterMode Then ws.ShowAllData Else End If 'This removes "normal" filters in the workbook - however, it doesn't remove table filters For Each listObj In ws.ListObjects If listObj.ShowHeaders Then listObj.AutoFilter.ShowAllData listObj.Sort.SortFields.Clear End If Next listObj Next 'And this removes table filters. You need both aspects to make it work. End Sub

Sau khi bạn đã lọc hoặc sắp xếp dữ liệu trong một phạm vi ô hoặc cột bảng, bạn có thể áp dụng lại một bộ lọc hoặc thực hiện thao tác sắp xếp để có được kết quả Cập Nhật, hoặc bạn có thể xóa một bộ lọc để hiển thị lại tất cả dữ liệu.

Lưu ý: Khi bạn sắp xếp, không có cách nào để xóa thứ tự sắp xếp và khôi phục thứ tự đã được đặt ở vị trí trước đó. Tuy nhiên, trước khi bạn sắp xếp, bạn có thể thêm một cột để chứa các giá trị tùy ý để giữ nguyên thứ tự sắp xếp ban đầu, ví dụ: các số tăng dần. Sau đó, bạn có thể sắp xếp cột đó để khôi phục thứ tự sắp xếp ban đầu.

Bạn muốn làm gì?

Tìm hiểu thêm về việc áp dụng lại bộ lọc và sắp xếp

Áp dụng lại bộ lọc hoặc sắp xếp

Xóa một bộ lọc cho một cột

Xóa tất cả các bộ lọc trong một trang tính và hiển thị lại tất cả các hàng

Tìm hiểu thêm về việc áp dụng lại bộ lọc và sắp xếp

Để xác định xem một bộ lọc được áp dụng, hãy ghi lại biểu tượng trong đầu đề cột:

Khi bạn áp dụng lại một thao tác lọc hoặc sắp xếp, các kết quả khác sẽ xuất hiện vì những lý do sau:

Dữ liệu đã được thêm vào, sửa đổi hoặc xóa khỏi phạm vi ô hoặc cột bảng.

Bộ lọc là bộ lọc ngày và thời gian động, chẳng hạn như ngày hôm nay, tuần này, hoặc năm này sang ngày.

Giá trị do một công thức trả về đã thay đổi và trang tính đã được tính toán lại.

Lưu ý: Khi bạn sử dụng hộp thoại Tìm để tìm kiếm dữ liệu đã lọc, chỉ có dữ liệu được hiển thị đang tìm kiếm; dữ liệu không được hiển thị sẽ không được tìm kiếm. Để tìm kiếm tất cả dữ liệu, hãy xóa tất cả bộ lọc.

Áp dụng lại bộ lọc hoặc sắp xếp

Lưu ý: Đối với một tiêu chí bảng, lọc và sắp xếp được lưu cùng với sổ làm việc để bạn có thể áp dụng lại cả bộ lọc và sắp xếp mỗi lần bạn mở sổ làm việc đó. Tuy nhiên, đối với một phạm vi ô, chỉ các tiêu chí lọc được lưu cùng với sổ làm việc, không sắp xếp tiêu chí. Nếu muốn lưu tiêu chí sắp xếp để bạn có thể áp dụng lại thao tác sắp xếp theo định kỳ khi mở sổ làm việc, bạn nên sử dụng bảng. Điều này đặc biệt quan trọng đối với thao tác sắp xếp nhiều cột hoặc sắp xếp cần nhiều thời gian để tạo.

Để áp dụng lại một bộ lọc hoặc sắp xếp, trên tab trang đầu, trong nhóm sửa , bấm sắp xếp & bộ lọc, rồi bấm vào áp dụng lại.

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 Sub

3. 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!

Bài hướng dẫn này cho thấy cách sử dụng bộ lọc nâng cao trong Excel và cung cấp vài ví dụ vùng điều kiện nâng cao để tạo bộ lọc phân biệt dạng chữ, để tìm điểm giống và khác giữa hai cột, cách xuât hồ sơ khớp với danh sách nhỏ hơn, và nhiều hơn thế nữa.

CÀI ĐẶT VÙNG ĐIỀU KIỆN DỰA TRÊN CÔNG THỨC

Công thức bạn sử dụng cho vùng điều kiện phải biết đánh giá TRUE hay FALSE.

Vùng điều kiện phải có ít nhất 2 ô: ô công thức và ô tiêu đề.

Ô tiêu đề của điều kiện dựa trên công thức hoặc là để trống hoặc là khác với bất kỳ tiêu đề nào của bảng (dải ô danh sách).

Để công thức được đánh giá trong mỗi ô của dải ô danh sách, hãy tham chiếu ô trên cùng cùng với dữ liệu bằng cách sử dụng tham chiếu ô tương đối chẳng hạn như A1.

Để công thức được đánh giá chỉ cho một ô hay một dải ô xác định, hãy tahm chiếu ô hay dải ô đó bằng cách sử dụng tham chiếu ô tuyệt đối chẳng hạn như $A$1.

Khi tham chiếu dải ô danh sách trong công thức, hãy luôn sử dụng tham chiếu ô tuyệt đối.

Khi áp dụng nhiều điều kiện, hãy nhập tất cả điều kiện trên cùng một hàng và liên kết chúng bằng toán tử AND, hay nhập mỗi điều kiện vào một hàng khác nhau và liên kết chúng bằng toán tử OR.

VÍ DỤ VÙNG ĐIỀU KIỆN TRONG BỘ LỌC EXCEL NÂNG CAO

BỘ LỌC PHÂN BIỆT DẠNG CHỮ CHO GIÁ TRỊ DẠNG KÝ TỰ

Giống như Bộ lọc Tự động Excel, công cụ Bộ lọc Nâng cao cũng không phân biệt dạng chữ, có nghĩa là nó không phân biệt ký tự viết hoa và viết thường khi lọc giá trị dạng ký tự. Tuy nhiên, bạn có thể dễ dàng tìm kiếm trong khi vẫn phân biệt dạng chữ bằng cách sử dụng hàm EXACT trong điều kiện lọc nâng cao.

Ví dụ, để lọc hàng chứa Chuối, không phân biệt là CHUỐI hay chuối, hãy nhập công thức sau vào vùng điều kiện:

=EXACT(B5, “Chuối”)

Khi lọc giá trị số, thông thường bạn có thể muốn hiển thị chỉ những ô lớn hơn hay nhỏ hơn giá trị trung bình trong cột. Ví dụ:

Để lọc hàng có tổng lớn hơn giá trị trung bình, hãy sử dụng công thức sau đây trong vùng điều kiện:

Để lọc hàng có tổng nhỏ hơn giá trị trung bình, hãy sử dụng công thức sau đây:

=F5<AVERAGE($F$5:$F$50)

Xin hãy lưu ý rằng chúng ta sử dụng tham chiếu tương đối để tham chiếu ô trên cùng có dữ liệu (ô F5), và tham chiếu ô tuyệt đối để xác định toàn bộ dải ô – dải ô mà bạn muốn tính giá trị trung bình, không tính tiêu đề cột ($F$5:$F$50).

Vì thế, hãy tận dụng ví dụ này, hãy lọc hàng có tổng (cột F) HAY Doanh thu Tháng chín (cột E) lớn hơn giá trị trung bình. Về việc này, hãy thiết lập vùng điều kiện có hàm lôgic OR bằng cách nhập mỗi điều kiện vào mỗi hàng khác nhau. Kết quả là, bạn sẽ có một danh sách lớn hơn giá trị trung bình nằm ở cột F hay cột E:

Excel kế toán 1 thầy 1 trò

LỌC HÀNG TRỐNG HAY CÓ CHỨA DỮ LIỆU

Có lẽ mọi người đã biết, Bộ lọc Excel có một tính năng sẵn có để lọc các ô trống. Bằng cách chọn hay bỏ chọn (Trống) các hộp đánh dấu trong bảng chọn Bộ lọc Tự động của Excel, bạn có thể hiển thị chỉ những hàng có các ô trống hay chứa dữ liệu trong một hay nhiều cột. Vấn đề ở đây đó là bộ lọc ô trống có sẵn trong Excel chỉ chạy khi được dùng với hàm lôgic AND.

Nếu bạn muốn lọc các ô trống hay ô có chứa dữ liệu với hàm lôgic OR, hay sử dụng điều kiện cho các ô trống hay các ô có chứa dữ liệu cùng với vài điều kiện khác nữa, thì hãy cài đặt vùng điều kiện bộ lọc nâng cao cùng với một trong những công thức sau:

Lọc các ô trống:

ô_trên_cùng=””

Lọc các ô có chứa dữ liệu:

LỌC CÁC Ô TRỐNG VỚI HÀM LÔGIC OR

Để lọc các hàng có ô trống hoặc ở cột A hay B, hay ở cả hai cột, hãy lập cấu hình vùng điều kiện Bộ lọc Nâng cao theo cách này:

Trong đó, 6 là hàng trên cùng có chứa dữ liệu.

Để hiểu thêm cách Bộ lọc Nâng cao trong Excel hoạt động khi có nhiều điều kiện, hãy lọc các hàng trong bảng mẫu theo các điều kiện sau:

Tổng doanh thu (cột C) phải lớn hơn 900.

Nói cách khác, chúng ta muốn hiển thị các hàng đáp ứng các điều kiện sau:

Như bạn đã biết, trong vùng điều kiện của Bộ lọc Nâng cao Excel, điều kiện kết hợp với hàm lôgic AND nên được nhập vào cùng một hàng, và điều kiện kết hợp với hàm lôgic OR – ở các hàng khác nhau:

Điều kiện được lập với toán tử so sánh nên có tiêu đề giống 100% tiêu đề của bảng, giống như điều kiện Tổng doanh thu trong ảnh chụp màn hình trên.

Điều kiện dựa trên công thức nên có hoặc là ô tiêu đề trống hoặc là tiêu đề không giống với bất kỳ tiêu đề nào của bảng, giống như điều kiện Có chứa dữ liệu trong ảnh chụp màn hình trên.

CÁCH XUẤT N HỒ SƠ ĐẦU/CUỐI

Ví dụ, để lọc 3 tổng doanh thu đầu, hãy tạo vùng điều kiện cùng với công thức này:

Để xuất 3 tổng doanh thu cuối, hãy sử dụng công thức này:

Trong đó, F5 là ô trên cùng có chứa dữ liệu trong cột Tổng doanh thu (không tính tiêu đề của cột).

LỌC ĐIỂM GIỐNG VÀ ĐIỂM KHÁC GIỮA HAI CỘT

Một trong những bài viết trước của chúng ta đã giải thích những cách khác nhau nhằm so sánh hai cột trong Excel rồi tìm điểm giống và điểm khác giữa chúng. Bên cạnh các công thức Excel, quy luật định dạng có điều kiện và công cụ Duplicate Remover đã được nói đến trong bài hướng dẫn trên, bạn cũng có thể sử dụng Bộ lọc Nâng cao Excel để xuất các hàng có các giá trị giống hay khác nhau trong hai hay nhiều cột. Để làm điều này, hãy nhập một trong những công thức đơn giản sau đây vào vùng điều kiện:

Lọc điểm giống (sự trùng lặp) giữa hai cột:

=B5=C5

Lọc điểm khác (giá trị độc nhất) giữa hai cột:

Trong đó, B5 và C5 là các ô trên cùng có chứa dữ liệu thuộc hai cột mà bạn muốn so sánh.

LỌC CÁC HÀNG DỰA TRÊN CÁC MỤC GIỐNG NHAU TRONG DANH SÁCH

LỌC CÁC HÀNG CÓ CHỨA DANH MỤC THUỘC DANH SÁCH

Ví dụ, để lọc các hàng nằm trong danh sách, nhưng chỉ các vùng miền Bắc thôi, hãy nhập hai điều kiện này vào cùng một hàng để chúng có thể hoạt động khi dùng hàm lôgic AND:

LỌC HÀNG KHÔNG CHỨA DANH MỤC THUỘC DANH SÁCH

Vùng miền: ="=Miền Bắc"

Nếu danh sách cần so sánh nằm ở trang tính khác, hãy chắc rằng bạn thêm tên trang tính vào công thức, ví dụ =COUNTIF(Sheet2!$A$2:$A$7,B10).

Nếu bạn muốn xuất kết quả sang một trang tính khác, hãy sử dụng Bộ lọc Nâng cao ở trang tính mà bạn muốn xuất kết quả.

LỌC CÁC NGÀY CUỐI TUẦN VÀ TRONG TUẦN

Nãy giờ, ví dụ về vùng điều kiện Bộ lọc Nâng cao phần lớn xử lý giá trị số và giá trị ký tự. Bây giờ, là lúc cho bạn thấy cách xử lý ngày tháng năm.

Như bạn đã biết, Microsoft Excel cung cấp hàm WEEKDAY đặc biệt – hàm này trả về ngày trong tuần dựa trên ngày tháng năm cho sẵn. Và đây là hàm mà chúng ta sẽ sử dụng trong vùng điều kiện Bộ lọc Nâng cao Excel.

CÁCH LỌC NGÀY CUỐI TUẦN TRONG EXCEL

Hãy nhớ rằng, trong thuật ngữ WEEKDAY, 1 là Chủ nhật và 7 là Thứ bảy, công thức lọc ngày cuối tuần có dạng như sau:

OR(WEEKDAY(ngày tháng năm)=7, WEEKDAY(ngày tháng năm)=1)

Trong ví dụ này, chúng ta lọc ngày tháng năm trong cột B bắt đầu từ hàng 5, vì thế công thức lọc ngày cuối tuần có dạng như sau:

=OR(WEEKDAY(B5)=7, WEEKDAY(B5)=1)

Để lọc các ngày trong tuần, hãy thay đổi công thức trên đễ nó không trả về kết quả 1 (Chủ nhật) và 7 (Thứ bảy):

Đối với bảng mẫu của chúng ta, công thức sau có ích rất nhiều: