Sử dụng Bộ lọc nâng cao trong Excel để giải quyết vấn đề của Mort. Mặc dù các bộ lọc thông thường đã trở nên mạnh mẽ hơn, nhưng vẫn có lúc Bộ lọc nâng cao có thể thực hiện một số thủ thuật mà những bộ lọc khác không làm được.
Xem video
- Bộ lọc Nâng cao "nâng cao" hơn bộ lọc thông thường vì:
- 1) Nó có thể sao chép sang một phạm vi mới
- 2) Bạn có thể xây dựng các tiêu chí phức tạp hơn như Trường 1 = A hoặc Trường 2 = A
- 3) Nó nhanh
- Mort đang cố gắng xử lý 100K hàng trong VBA bằng cách lặp qua các bản ghi hoặc sử dụng một mảng
- Sẽ luôn nhanh hơn khi sử dụng các tính năng Excel tích hợp sẵn hơn là viết mã của riêng bạn.
- Bạn cần Phạm vi đầu vào, sau đó là Phạm vi tiêu chí và / hoặc Phạm vi đầu ra
- Đối với phạm vi đầu vào: một hàng tiêu đề phía trên dữ liệu
- Thêm một hàng tạm thời cho các tiêu đề
- Đối với phạm vi đầu ra: một hàng tiêu đề cho các cột bạn muốn trích xuất
- Đối với phạm vi tiêu chí: tiêu đề ở hàng 1, giá trị bắt đầu từ hàng 2
- Sự phức tạp: Các phiên bản Excel cũ hơn sẽ không cho phép phạm vi đầu ra nằm trên một trang tính khác
- Nếu bạn đang viết một macro có thể chạy vào năm 2003, hãy sử dụng một phạm vi đã đặt tên cho phạm vi đầu vào để phá vỡ
Bản ghi video
Học Excel từ Podcast, Tập 2060: Bộ lọc nâng cao của Excel
Chào mừng bạn trở lại netcast, tôi là Bill Jelen. Câu hỏi hôm nay do Mort gửi đến. Mort, anh ấy có 100.000 hàng dữ liệu và anh ấy quan tâm đến các Cột A, B và D trong đó Cột C khớp với một năm cụ thể nào đó. Vì vậy, anh ấy muốn một người nhập một năm và sau đó nhận được các Cột A, B và D. Và Mort có một số VBA nơi anh ấy sử dụng các mảng để thực hiện việc này và tôi đã nói, “Chờ một chút, bạn biết đấy, bộ lọc nâng cao sẽ làm điều này tốt hơn rất nhiều. ” Được rồi, và bây giờ chỉ để xem lại, tôi đã quay lại, xem lại các video của mình. Tôi đã không đề cập đến bộ lọc nâng cao trong một thời gian dài, vì vậy chúng ta nên nói về điều này.
Bộ lọc nâng cao yêu cầu một phạm vi đầu vào và sau đó ít nhất một trong những điều này: phạm vi tiêu chí hoặc phạm vi đầu ra. Mặc dù hôm nay chúng ta sẽ sử dụng cả hai. Được rồi, vì vậy phạm vi đầu vào là dữ liệu của bạn và bạn phải có tiêu đề phía trên dữ liệu. Vì vậy, Mort không có tiêu đề và vì vậy tôi sẽ tạm thời chèn một hàng lên đây và chỉ làm như Trường 1. Mort biết dữ liệu của mình là gì và vì vậy anh ta có thể đưa các tiêu đề thực lên đó. Và chúng tôi không sử dụng bất kỳ thứ gì được gọi là dữ liệu này trong các Cột từ E đến O, vì vậy tôi không cần phải thêm các tiêu đề vào đó, được chứ? Vì vậy, bây giờ, A1 đến D, 100000 trở thành phạm vi đầu vào của tôi. Và sau đó là phạm vi đầu ra và phạm vi tiêu chí - Chà, phạm vi đầu ra chỉ là danh sách các tiêu đề mà bạn muốn. Vì vậy, tôi sẽ đặt phạm vi đầu ra ở đây và chúng tôi không cần Trường 3 nên tôi 'Tôi chỉ cần tháo nó sang một bên. Vì vậy, bây giờ, phạm vi này ngay tại đây, A1 đến C1 trở thành phạm vi đầu ra của tôi cho Excel biết tôi muốn trường nào từ phạm vi đầu vào. Và, chúng có thể theo một thứ tự khác nếu bạn muốn sắp xếp lại các thứ, như nếu tôi muốn Trường 4 trước rồi đến Trường 1 rồi đến Trường 2. Và một lần nữa, đây sẽ là các tiêu đề thực như số hóa đơn. Tôi chỉ không biết dữ liệu của Mort trông như thế nào.
Và sau đó, phạm vi tiêu chí là một tiêu đề và giá trị bạn muốn. Vì vậy, giả sử tôi đã cố gắng đạt được bất cứ điều gì trong năm 2014. Đây trở thành phạm vi tiêu chí như thế. Được rồi, chỉ một lời cảnh báo ở đây. Tôi đang sử dụng Excel 2016 và có thể thực hiện bộ lọc nâng cao giữa hai trang tính trong Excel 2016 nhưng nếu bạn quay lại và tôi không nhớ đường quay lại là gì, có thể là năm 2003, tôi không chắc. Trước đây, bạn không thể thực hiện bộ lọc nâng cao từ trang tính này sang trang tính khác, vì vậy bạn phải vào đây và đặt tên cho phạm vi đầu vào của mình. Bạn sẽ phải tạo một cái tên ở đây. MyName hoặc cái gì đó tương tự, được chứ? Và đó sẽ là cách mà bạn có thể thực hiện điều này, được thôi. Không nhất thiết phải có trong Excel 2016 nhưng một lần nữa, tôi 'Tôi không chắc liệu Mort có chạy điều này trong các phiên bản cũ hơn của dữ liệu hay không.
Được rồi, quay lại đây tại Dữ liệu, chúng ta chuyển đến Bộ lọc nâng cao. Và chúng tôi sẽ Sao chép sang một vị trí khác cho phép phạm vi đầu ra của chúng tôi ở đó. Được rồi, vậy phạm vi danh sách, dữ liệu ở đâu? Bởi vì tôi đang ở trong Excel 2016, tôi sẽ đi đến Dữ liệu, thay vì sử dụng phạm vi tên - Vì vậy, đó là phạm vi đầu vào của tôi. Phạm vi tiêu chí là những ô ngay đó và sau đó, nơi chúng ta sẽ - xuất ra, nó sẽ chỉ có ba ô này ở đó. Và sau đó chúng tôi nhấp vào OK. Được rồi, và BAM! Nhanh, nhanh là thế. Và nếu chúng ta muốn một năm khác thì sao? Nếu chúng tôi muốn một năm khác, chúng tôi sẽ xóa kết quả, đưa vào năm 2015 và sau đó thực hiện lại bộ lọc nâng cao, Sao chép sang vị trí khác, nhấp vào OK và có tất cả các bản ghi năm 2015. Nhanh như chớp.
Được rồi, mặc dù tôi là người yêu thích bộ lọc nâng cao trong Excel thông thường, nhưng tôi là một người rất thích bộ lọc nâng cao trong VBA, được thôi, vì VBA tạo bộ lọc nâng cao thực sự rất đơn giản. Được rồi, vì vậy chúng ta sẽ viết một số mã ở đây cho Mort, giả sử rằng dữ liệu của Mort không có tiêu đề và chúng ta sẽ phải tạm thời thêm các tiêu đề, được chứ? Vì vậy, tôi sẽ chuyển sang VBA, Alt + F11 và chúng tôi sẽ chạy điều này từ trang tính có dữ liệu. Vì vậy: Dim WS As Worksheet, Đặt WS = ActiveSheet. Và sau đó, chèn Hàng 1 và chỉ cần thêm một số tiêu đề: A, B, Năm và D. Tìm ra bao nhiêu hàng dữ liệu chúng ta có ngày hôm nay và sau đó bắt đầu từ ô A1 đi ra 4 cột xuống hàng cuối cùng, đặt tên là là phạm vi đầu vào. Được rồi, đây thực sự là mã của Mort ở đây, nơi anh ấy yêu cầu InputBox,nhận được năm họ muốn và sau đó anh ấy hỏi họ muốn đặt tên cho trang tính mới là năm nào hoặc tên gì. Vì vậy, nó sẽ thực sự chèn một trang tính trên Fly và sau đó I- Kích thước một trang tính mới, WSN, làm ActiveSheet. Vì vậy, tôi biết rằng WS là trang tính gốc, WSN là trang tính mới vừa được thêm vào. Trên trang tính mới, hãy đặt phạm vi tiêu chí để bên dưới Cột E có tiêu đề phù hợp với tiêu đề này ở đây và sau đó, bất kỳ câu trả lời nào họ đưa ra cho chúng ta sẽ chuyển trong E2. Phạm vi đầu ra sẽ là ba tiêu đề khác của tôi: A, B và D. Và một lần nữa, nếu bạn hoặc Mort thay đổi những tiêu đề này thành tiêu đề thực, điều này có lẽ tốt hơn nên làm so với A, B, D và bạn cũng thay đổi những tiêu đề này thành tiêu đề thực, được chứ? Vì vậy, tất cả những điều này chỉ là một chút công việc trước ở đây. Một dòng mã tuyệt vời này sẽ thực hiện toàn bộ bộ lọc nâng cao. Vì thế,từ InputRange, chúng tôi tạo AdvancedFilter, chúng tôi sẽ sao chép. Đó là bộ lọc lựa chọn của chúng tôi tại chỗ hoặc sao chép. CriteriaRange là E1 đến E2, CopyToRange là A đến C. Các giá trị duy nhất -Không, chúng tôi muốn tất cả các giá trị. Được rồi, một dòng mã ở đó thực hiện tất cả điều kỳ diệu là lặp qua tất cả các bản ghi hoặc thay thế việc lặp qua tất cả các bản ghi hoặc thực hiện các mảng. Và sau đó chúng ta đã hoàn tất, chúng ta sẽ xóa phạm vi tiêu chí và sau đó xóa Hàng 1 trở lại trang tính ban đầu.Và sau đó chúng tôi đã hoàn tất, chúng tôi sẽ xóa phạm vi tiêu chí và sau đó xóa Hàng 1 trở lại trang tính ban đầu.Và sau đó chúng tôi đã hoàn tất, chúng tôi sẽ xóa phạm vi tiêu chí và sau đó xóa Hàng 1 trở lại trang tính ban đầu.
Được rồi, chúng ta hãy chuyển lại dữ liệu của chúng tôi ở đây. Chúng tôi sẽ giúp bạn dễ dàng chạy điều này, vì vậy: Chèn, một Hình dạng và gọi Bộ lọc này, Trang chủ, Trung tâm, Trung tâm, Lớn hơn, Lớn hơn, Lớn hơn, nhấp chuột phải, Gán Macro và gán nó cho MacroForMort. Được rồi, vậy chúng ta bắt đầu. Chúng tôi sẽ làm một bài kiểm tra. Xem chúng ta đang ở trên bảng dữ liệu, nhấp vào Bộ lọc, chúng ta muốn năm nào? Chúng tôi muốn 2015. Tôi muốn gọi nó là gì? Tôi muốn gọi nó là 2015, được rồi. Và BAM! Vậy là xong. Đó là cách nhanh, đó là nhanh như thế này.
Bây giờ, vì dữ liệu ban đầu của Mort không có tiêu đề, có lẽ dữ liệu này không nên có tiêu đề. Vì vậy, hãy nhấn Alt + F11, ngay tại đây chúng tôi muốn xóa phạm vi tiêu chí. Chúng tôi cũng sẽ Hàng (1) .Xóa. Được rồi, bây giờ lần sau khi chúng ta làm việc này, nó sẽ loại bỏ những tiêu đề đó. Và hãy chỉ - Thay vì chạy nhanh toàn bộ, chúng ta hãy xem xét ở đây với năm 2014. Vì vậy, tôi sẽ chọn một ô trên Dữ liệu, Alt + F11 và tôi muốn chạy ngay đến điểm mà chúng tôi thực hiện bộ lọc nâng cao. Vì vậy, chúng ta có thể xem xét toàn bộ macro đang làm gì ở đây. Vì vậy, chúng tôi sẽ nhấp vào Chạy và tôi muốn có được năm 2014. 2014, được rồi. Và vì vậy, nhấn F8, chúng ta sắp thực hiện bộ lọc nâng cao. Chúng ta có thể quay lại Excel tại đây và xem điều gì đã xảy ra.
Điều đầu tiên đã xảy ra- Bây giờ, điều đầu tiên đã xảy ra là chúng tôi đã thêm một hàng tạm thời mới với các tiêu đề. Đã chèn trang tính này, xây dựng phạm vi tiêu chí với tiêu đề và năm họ nhập vào, chọn các trường mà chúng tôi muốn thực hiện và sau đó quay lại VBA, tôi sẽ chạy dòng mã tiếp theo, đó là F8 thực hiện bộ lọc nâng cao ngay tại đó . Nó cực kỳ nhanh và bạn sẽ thấy rằng điều đó thực sự đã mang lại cho chúng tôi tất cả các kỷ lục. Từ đó, nó chỉ là một chút dọn dẹp, xóa cái này, xóa cái này. Tôi sẽ quay lại dữ liệu và xóa Hàng 1 và chúng ta sẽ sẵn sàng. Vì vậy, tôi sẽ chỉ để phần còn lại của nó chạy, loại bỏ điểm ngắt đó, được không? Vì vậy, có VBA. Đối với tôi, tôi nghĩ đây là con đường nhanh nhất, nhanh nhất để đi.
Alright, episode recap: The advanced filter is more advanced than the regular filter because it can copy to a new range. And now, I didn't show it in this video but you can build complex criteria where Field 1 = A or Field 2 = A. The regular auto filter can't do that and it's fast. Mort is trying to process 100,000 rows in VBA by using an array or by looping, but it'll always be faster to use Excel building features than writing your own code. You need to define an input range, criteria range, output range. You always need an input range in at least one of these, although today I'm using both. For the input range, single row of headings above the data. So we're going to add a temporary row of headings. For the output range, the same headings that you want to extract, alright. So, you know, if it was A, B, Year and D, we’ll just put A, B and D as the output range. For the criteria range, headings in Row 1. So this is the field I want to build a criteria on and this is the value I'm looking for. Complications: Older versions of Excel will not allow the output range to be on another sheet, so, potentially your code will run back then. You want to use a named range for the input range because from this sheet, you know, the named range, even though it's on another sheet, the sheet believes the name branches on the current sheet. So that would allow the advanced filter to work.
Được rồi, bạn đã có nó. Tôi muốn cảm ơn Mort vì đã gửi câu hỏi đó. Tôi muốn cảm ơn bạn đã ghé qua. Chúng tôi sẽ gặp bạn lần sau cho một netcast khác từ.
Tải tập tin
Tải xuống tệp mẫu tại đây: Podcast2060.xlsm
