Excel - Tìm hiểu về Advanced Filter

Phần 1 : Tìm hiểu về Advanced Filter 1/ Cơ sở dữ liệu cho Advanced Filter cần những gì ?Giả sử ta có một bảng dữ liệu A2 : D20- Hàng đầu tiên A1 : D1 phải là hàng tiêu đề, hàng này được phân biệt bằng một kiểu Format khác, như tô đậm chẳng hạn và các từ nên viết liền nhau, không bỏ dấuTd : cột A chứa ngày tháng, thì A1 được đặt là Ngay. Tương tự, cột B là MaKH, cột C là MaMH, cột D là SoLuong- Các hàng chứa dữ liệu phải liên tục, liền nhau- Không có hàng trống trong CSDL- Nếu kết thúc một CSDL, bạn phải chừa một hàng trống phía dưới và một cột trống bên phải của CSDL đó2/ Vùng điều kiện (Criteria Range) :Tại vùng này, bạn sẽ cho điều kiện để chỉ những hàng nào đáp ứng được điều kiện này mới hiển thị ra sau khi trích lọc. Bạn có thể sử dụng một hay nhiều điều kiện để lọcThí dụ : Vùng F1:F2 là vùng điều kiện-Tiêu đề F1 phải chính xác đúng với một trong các tiêu đề (A1 : D1), (nếu bạn muốn trích lọc cột nào, thì lấy tiêu đề của cột đó đặt làm tiêu đề cho vùng điều kiện này). Td nếu ta muốn lọc xem các khách hàng nào mua hàng với số lượng nào đó, ta sẽ đặt tại F1 tiêu đề SoLuong- Cell F2 sẽ chứa điều kiện. Các điều kiện lọc thường xảy ra là :a) = : lớn hơn hay bằng (>=500)d) > : lớn hơn (>500)e) = : bằng (=500)f) : khác hay không bằng (500)3/ Vùng trích lọc dữ liệu :Nếu bạn muốn sau khi lọc dữ liệu xong, dữ liệu mới sẽ được Copy sang một nơi khác, và bạn chỉ muốn lọc một số cột thôi, thí dụ bạn chỉ muốn lấy cột B và D là cột maKH và cột SoLuong, bạn sẽ phải đánh tiêu đề MaKH và SoLuong tại vị trí bạn muốn Copy dữ liệu. Trong trường hợp bạn lấy đủ số cột hiển thị, bạn có thể bỏ qua bước nàyBạn hãy nhớ rằng các tiêu đề cột tại vị trí mới này phải chính xác với tiêu đề của bảng dữ liệu4/ Áp dụng việc lọc dữ liệu :- Chọn tất cả các Cell của CSDL cả vùng tiêu đề- Từ Menu Data, chọn Filter / Advanced Filter- Bạn có thể chọn một trong 2 Option sau :a) Filter the list, in-place : lọc tại chỗ, chỉ những hàng nào thỏa mãn điều kiện lọc sẽ hiển thị, hàng nào không thỏa đK sẽ bị che đib) Copy to another location : Copy sang vùng khác những dữ liệu nào thỏa đK lọc- Tại List range : bạn xác lập vùng CSDL cần lọc- Tại Criteria range : bạn xác lập vùng điều kiện lọc- Copy to : Nếu bạn muốn Copy sang nơi khác, bạn hãy chọn nơi bắt đầu- Click OK để hòan tất việc lọc Phần 2 : Lọc các mẩu tin duy nhất Bạn có thể sử dụng Advanced Filter giúp cho bạn việc này. - Bạn chọn CSDL, cả vùng tiêu đề cột. - Bạn không cần xác lập vùng điều kiện như trên, còn các bước khác thì tương tự. - Nếu bạn muốn Copy sang nơi khác, bạn có thể chỉ định nơi bắt đầu DS mới- Bạn chọn dấu kiểm Unique records Only tại hộp thọai Advanced Filter và click chọn OK để kết thúc Trường hợp bạn muốn Copy sang một Worksheet khác, không cùng trên Sheet đặt CSDL . Td : Sheet đặt CSDL của bạn là Sheet1, Sheet 2 bạn sẽ đặt DS lọc. Bạn sẽ thực hiện như sau :- Chọn Sheet 2- Nếu bạn chọn tất cả các cột của CSDL thì bỏ qua bước này. Còn bạn muốn cột nào hiển thị, thì đánh tiêu đề của cột đó vào DS lọc thôi- Đứng tại Sheet2 và mở hộp thọai Advanced Filter từ Menu Data / Filter- Các bước khác tương tự như trên, chỉ duy nhất là bạn đứng ngay tại Sheet 2 chọn CSDL và vùng đK Phần 3 : Lọc điều kiện AND và OR Giả sử CSDL A1: D20 của ta có các cột như trên : Ngaythang, MaKH, MaMH, SoLuong1/ Điều kiện AND :Nếu bạn muốn lọc theo điều kiện AND, thí dụ lọc các khách hàng có tên là AA, mua mặt hàng là HH1, với số lượng >500 thì vùng điều kiện G1:I2 của bạn sẽ như sau :- Tại G1, bạn gõ MaKH, H1 là MaMH, I1 là Soluong- Tại G2, bạn gõ AA, H2, bạn gõ HH1, I2 bạn gõ >500Lưu ý : Các điều kiện AND nằm cùng với nhau trên một hàng2/ Điều kiện OR :Nếu bạn muốn lọc theo điều kiện OR, thí dụ lọc hoặc các khách hàng có tên là AA, hoặc mua mặt hàng là HH1, hoặc mua với số lượng >500 thì vùng điều kiện G1:I4 của bạn sẽ như sau :- Tại G1, bạn gõ MaKH, H1 là MaMH, I1 là Soluong- Tại G2, bạn gõ AA, H3, bạn gõ HH1, I4 bạn gõ >500Lưu ý : Các điều kiện OR không nằm cùng với nhau trên một hàng. Bạn thấy không, vùng đK lọc AND là G1:I2, trong khi vùng điều kiện OR là G1:I4 Phần 4 : Lọc với các ký tự đại diện *, ?, ~ 1/ Ký tự * :Nếu cột MaKH của bạn bao gồm các mã khách hàng khác nhau, trong đó có khách hàng : AAB, BAA, ABC, BAB bạn muốn lọc các khách hàng có chuỗi ký tự AB thì vùng điều kiện lọc bạn sẽ ghi là : *AB*Các mã KH là AAB, ABC, BAB sẽ được trích lọc ra khỏi CSDLNhư vậy, ký tự * đại diện cho một chuỗi ký tự 2/ Ký tự ? :Ký tự ? đại diện cho một ký tự duy nhất trong chuỗi, ở thí dụ trên nếu ta đặt vùng điều kiện là : BA? thì các mã KH BAA, BAB sẽ được lọc3/ Ký tự ~ :Nếu trong mã KH của bạn có ký tự *, thí dụ A*B, khi bạn đặt điều kiện lọc là A*B, Excel sẽ ngầm hiểu là bạn muốn lọc tất cả các KH có ký tự đầu là A và ký tự cuối là B, trong khi đó, bạn chỉ muốn lọc đúng mã KH là A*B mà thôi. Trong trường hợp này, bạn phải đặt vùng điều kiện là : A~*B, Excel mới giúp bạn điều mà bạn muốn Phần 5 : Một vài thí dụ về vùng điều kiện lọc Cũng CSDL A1 : D20 với 4 cột Ngaythang, MaKH, MaMH, SoLuong như trên, các bạn nhé. Các bạn cứ nhập thử dữ liệu của mình1/ Td 1 : Lọc DS các khách hàng mua hàng từ ngày 10/01/06 đến 15/01/06Vùng điều kiện ta sẽ đặt như sau : - Tại F1, gõ Ngaythang, G1 cũng gõ Ngaythang- Tại F2, gõ >=10/01/06, G2 gõ <=15/01/06Nhận xét : Đây chính là áp dụng điều kiện AND cho cùng một kiểu dữ liệu Ngaythang2/ Td 2 : Lọc DS các khách hàng có MaKH là AA mua sản phẩm HH1 và khách hàng có MaKH là BB mua sản phẩm là HH3. Ta sẽ đặt vùng điều kiện như sau :- Tại F1, gõ MaKH, G1 gõ MaHH- Tại F2 gõ AA, G2 gõ HH1- Tại F3, gõ BB và G3 gõ HH2Nhận xét : Đây là thí dụ áp dụng cho việc kết hợp lọc dữ liệu theo cả 2 điều kiện AND và OR3/ Td 3 : Lọc đúng từ cần thiết. TD nếu bạn có 2 MaKH là AB và ABAB, nếu bạn gõ vào vùng điều kiện là AB, thì DS lọc của bạn sẽ bao gồm cả 2 mã KH này. Bây giờ bạn muốn lọc chính xác chỉ KH AB thôi, bạn phải gõ vào vùng ĐK như sau :- Tại F1, gõ MaKH- Tại F2, gõ ="=AB" Lọc Advanced Filter bằng công thức --------------------------------------------------------------------------------Đây chính là phần lý thú nhất của Advanced Filter, Trước khi làm việc với các công thức tại vùng điều kiện, các bạn lưu ý rằng nếu đặt công thức tại Cell F2, thì Cell F1 là hàng trống. Vùng điều kiện của chúng ta vẫn là F1:F2, các bạn à, và CSDL của chúng ta cũng giống như các bài trênMời các bạn xem bài của Adam_tran : Trích: Trích nguyên văn:Vùng ListRange là A2:C20, CriteriaRange là F1:F2. Yêu cầu lọc các hàng ở cột A có mã là giá trị ô A1 (lúc này là SP01) và cột C có SL > giá trị ô B1 (Lúc này là 10). Tại ô F2 đặt công thức =AND(A3=A$1, C3>B$1). AdvancedFilter (AF) sẽ chạy duyệt qua tất cả các Record có trong ListRange, (trừ dòng tiêu đề) đối chiếu lần lượt với điều kiện ở vùng CriteriaRange, nhưng AF sẽ tham chiếu biểu thức ở CriteriaRange với các dòng tương ứng. Cụ thể, tại dòng thứ 1(dòng 3), Advanced sẽ kiểm tra biểu thức =AND(A3=A$1, C3>B$1) = FALSEDòng 4: =AND(A4=A$1, C4>B$1) = FALSEDòng 5: =AND(A5=A$1, C5>B$1) = FALSEDòng 6: =AND(A6=A$1, C6>B$1) = TRUETương tự như vậy thì AF chỉ trích ra các dòng 6, 8 và 13, tức các dòng có điều kiện TRUE.Chỉ cần thay đổi dấu cố định ở ô F2 thành =AND(A3=A1, C3>B1), AF sẽ cho kết quả hoàn toàn khác.Dòng 3: =AND(A3=A1, C3>B1) = FALSE Dòng 4: =AND(A4=A2, C4>B2) = FALSEDòng 5: =AND(A5=A3, C5>B3) = FALSEDòng 6: =AND(A6=A4, C6>B4) = FALSEDo đó, địa chỉ tham chiếu ở CriteriaRange là rất quan trọng, có thể nói chỉ cần thay đổi biểu thức ở CriteriaRange ta có thể trích lọc theo ý muốn của mình.Theo em dùng biểu thức điều kiện hay hơn vì CriteriaRange chỉ dùng 1 ô, nếu dùng nhiều ô thì có thể gặp rắc rối với các giá trị ngày hay các kiểu dữ liệu khác nhau. Nhưng dùng 1 ô thì phải nắm chác "cách" mà AF làm việc. Có 1 điều mà AF lẫn AutoFilter đều giống nhau ở chỗ, hình như giá trị kiểm tra của AF hay Auto đêu quy ra kiểu Text. (Sheet Thí dụ 2, em không hiểu cặn kẻ lắm). Sheet Thí dụ 2 cột A, ô A8 là 1 giá trị Text, nhưng khi lọc Cột A ngày 02/03/2006 thì giá trị này cũng được đưa vào. Ở Cột D giá trị hoàn toàn giống cột A nhưng định dạng Format của cột D là "MM" (Chỉ hiển thị tháng). Khi lọc cột D giá trị là 03 tất nhiên nó sẽ không lọc ô D8. Có thể sử dụng mẹo nhỏ này khi dùng AutoFilter để lọc từng tháng, thay vì phải vào Custom để chọn Is Greater than ... ta chỉ cần đổi Format cột ngày sang "MM", rồi lọc (rất nhanh). Ngoài ra cũng để kiểm tra xem khi nhập liệu người nhập có nhập ngày dưới dạng Text hay không. Chỉ cần đổi Format sang "yyyy" chẳng hạn, nếu đúng kiểu ngày thì giá trị đổi thành số năm, còn kiểu Text thì vẫn giữ nguyên như cũ. Trong VBA, đây là 2 thuộc tính khác biệt, đó là Cell.Text và Cell.Value Phần 6 : Các thí dụ về lọc dữ liệu bằng công thức --------------------------------------------------------------------------------Nhắc lại : Vùng Criteria F1:F2 sẽ có Cell F1 không nhất thiết phải có tiêu đề cột giống với tiêu đề cột của CSDL, hoặc có thể là vùng trốngTD1 : Tại cột D có tiêu đề SoLuong, bạn muốn lọc Top 5 mẩu tin có số lượng lớn nhất, ta sẽ đặt công thức như sau vào vùng điều kiện (Criteria)F2 : =D2>=LARGE($D$2:$D$20,5)Hàm Large sẽ cho kết quả 5 giá trị lớn nhất của dãy $D$2:$D$20. Tham chiếu tuyệt đối của dãy sẽ được sử dụng tại đây. Cell D2 đầu tiên của cột sẽ được so sánh với giá trị này. Tham chiếu tương đối sẽ được sử dụng cho D2, các bạn hãy tự tìm hiểu xem khi thay đổi các kiểu tham chiếu sẽ cho kết quả ra sao nhé (hiểu điều này khá quan trọng đối với bài thách đố đấy các bạn à). Những hàng nào có số lượng lớn hơn hoặc bằng 5 giá trị lớn nhất sẽ qua vùng lọc với giá trị là True. TD2: Giả sử ta có một CSDL gồm có 4 cột, nhưng có 2 cột C và D là cột NgayDatHang và NgayGiaoHang. Các cột còn lại tùy bạn nghĩ. Tôi muốn lọc nhữngn MaKH nào đã được giao hàng ngay (ngày đặt hàng và ngày giao hàng giống nhau) Tại vùng Criteria, tôi sẽ đặt công thức sau tại Cell F2 :=C2=D2TD3: Cùng thí dụ trên, nhưng tôi muốn lọc ngày giao và ngày nhận khác nhau, tôi sẽ đặt công thức tại F2 như sau : =C2D2Bạn cũng có thể sử dụng tiêu đề cột thay vì địa chỉ Cell tham chiếu. TD :=NgayDatHangNgayGiaoHang. Công thức trên có thể cho kết quả là #NAME? hay #VALUE! tuy vậy, Advanced Filter vẫn làm việc chính xác TD4 : Lọc các KH có số lượng mua hàng lớn hơn số lượng bình quân trong tháng. Vẫn cột D là cột SoLuongVùng điều kiện : F2 : =D2>AVERAGE($D$2:$D$20)TD5 : Lọc các mã KH mua hàng nhiều lần trong tháng. Cột B là cột MaKH. Ta đặt công thức sau vào vùng điều kiện :F2 : = COUNTIF($B$2:$B$20,B2)>1TD6 : Tìm chữ số trong một số hay một ký tự trong một chuỗi. Giả sử ta muốn tìm các mã KH tại cột B có chữ A, ta có thể đặt công thức vào vùng điều kiện như sau : (bài trên ta đặt *A*)F2 : = ISTEXT(FIND("A",B2) Tương tự, nếu ta muốn tìm những hàng có số 5 trong cột D, ta sẽ đặt công thức sau vào vùng điều kiện :F2 : = ISNUMBER(FIND("5",D2)Nếu các bạn không đặt hàm ISTEXT hay ISNUMBER, vùng điều kiện sẽ thể hiện lỗi #VALÚE, nhưng Advanced Filter vẫn cho kết quả đúngTD7 : Lọc chính xác có phân biệt chữ hoa và chữ thường. Nếu cột MaKH của bạn có các mãKH là ab, AB, aB và Ab. Khi bạn đặt công thức sau vào vùng điều kiện :F2 : =EXACT(B2,"AB") Thì sẽ lọc những mã KH AB, còn ab, Ab, aB bị bò quaTD8 : Lọc dòng trống. Giả sử cột D, SoLuong của bạn có KH không mua hàng, nhưng vẫn có tên trong CSDL. Cuối tháng, bạn muốn lọc những KH này, bạn sẽ đặt công thức sau vào vùng điều kiện :F2 : =D2=""Trên đây là một số thí dụ cho thấy vận dụng công thức đơn giản vào các điều kiện lọc của Advanced Filter. Các bạn hãy tìm hiểu thêm nhé ột vài ý kiến về sắp xếp CSDL cho Advanced Filter --------------------------------------------------------------------------------Có ý kiến cho rằng ta nên đặt vùng Criteria bên dưới CSDL, nhưng điều này thường không hợp lý. Vì sao ? CSDL của ta không biết sẽ giới hạn kích thước ở đâu, và nó luôn luôn phát triển.Do đó, nhiều lời khuyên cho thấy, CSDL của ta sẽ được thiết kế từ hàng 4 hay hàng 5 trở xuống. Giả sử ta bắt đầu hàng tiêu đề là hàng 4. Từ hàng 5 ta dùng để nhập dữ liệu. Với những CSDL lớn, nhiều cột (trường), để tiện việc lọc dữ liệu, người ta hay sắp xếp từ hàng 1 đến hàng 3, là vùng Criteria. Ngay tại A1, ta nhập công thức =A4, và kéo rê sang các cột bên cạnh. Điều này đảm bảo tên của vùng điều kiện được sao chép đúng với tên của các trường trong CSDL. Khi ta thay đổi tên của trường dữ liệu, các tên của vùng điều kiện cũng được thay đổi cập nhật theo.Từ hàng thứ 2 và hàng 3, ta dùng để đặt các điểu kiện lọc. Nếu trường hợp phải lọc các điều kiện AND (đặt trên cùng một hàng), người ta thường linh động bằng các công thức như các bài trên. Một số lưu ý trong Advanced Filter --------------------------------------------------------------------------------- Một ô trống trong vùng Criteria (vùng điều kiện), được xem như "chấp nhận bất cứ giá trị nào của cột này". Nếu bạn đưa vào một dòng trống trong vùng này, bạnsẽ nhận được một DS chưa lọc. Do đó, khi bạn xóa bớt nội dung vùng điều kiện này, không chỉ đơn giản là xóa nội dung của hàng đó, mà bạn cần giảm vùng Criteria trong hộp thọai Advanced Filter-Khi bạn định vùng đK trong hộp thọai Advanced Filter, Excel sẽ gán tên Criteria cho vùng này. Bạn có thể sử dụng cái tên đó, nếu bạn nhấn F5 và chọn Criteria từ hộp thọai Go To, bạn sẽ quay về với vùng điều kiện này-Cũng vậy, khi bạn xác định vùng Copy To trong hộp thọai Advanced Filter, Excel sẽ gán tên Extract cho vùng đó. Bạn cũng có thể dùng F5 và chọn Extract từ hộp thọai Go To-Mỗi lần bạn sử dụng Advanced Filter, Excel sẽ kiểm tra lại tòan bộ DS chứ không phải vùng đã lọc. Do vậy, bạn không cần phải sử dụng lệnh Show All khi thay đổi việc lọc-Khi bạn sử dụng công thức để tính tóan trong vùng Criteria, tiêu đề của vùng này hoặc bỏ trống, hoặc có thể chứa văn bản, nhưng nó không phải là bản sao của bất kỳ tiêu đề nào khác của DS. Nếu tiêu đề bỏ trống, bạn vẫn phải đưa chúng vào vùng Criteria khi xác định vùng này trong hộp thọai Advanced Filter- Tiêu chuẩn của văn bản : (dạng Text)a) Một từ đơn : có nghĩa là chấp nhận bất cứ giá trị nào bắt đầu bằng từ này. TD : Nhập từ M sẽ cho tên là Mary, Martha, Mina...được lọcb) Ký hiệu > và M sẽ lọc các tên bắt đầu từ M cho đến Z, trong khi nhập <M, sẽ lọc các tên bắt đầu từ A đến L Xoá vùng lọc và điều kiện lọc của AutoFilter và Advanced Filter Lê Văn Duyệt Công cụ AutoFilter và Advanced Filter là công cụ mạnh trong Excel, nó giúp cho người sử dụng lọc và lấy khối dữ liệu cần thiết._Đối với AutoFilterKhi sử dụng công cụ này nếu chú ý, bạn sẽ thấy rằng Excel nhớ vùng trước đó bạn lọc. Trong một số trường hợp thì điều này hữu ích. Nhưng nếu bạn muốn làm việc với các vùng khác thì thật là bất tiện. Excel lưu giữ vùng lọc của bạn với tên _FilterDatabase. Bạn không thể xoá tên này bằng cách thông thường là vào Insert/Name/Define. Giả sử tôi có vùng dữ liệu muốn lọc là vùng A1:E5 của Sheet1. Tôi Click và ô A2 và chọn Data/Filter/AutoFilter. Sau đó tôi chọn Insert/Name/Define. Hộp thoại Define Name xuất hiện. Nhưng bạn sẽ không thấy một tên nào cả (giả sử rằng trước đó bạn chưa đặt tên cho vùng nào cả).Tên _FilterDatabase là loại hidden. Vì vậy bạn sẽ không thấy được bằng cách này. Bây giờ giả sử tôi nhập vào ô A6 với nội dung Lê Quốc và ô B6 với nội dung Cường các ô khác tôi không nhập (bạn cũng có thể nhập nội dung vào các ô khác, ở đây do làm ví dụ tôi giản lược bớt). Tôi chọn điều kiện lọc ở cột địa chỉ là: Quận Thủ Đức. Kết quả lọc của tôi sẽ như sau: Bạn thấy rằng hàng bạn vừa nhập vào cũng bị Hide luôn. Bây giờ tôi chọn điều kiện lọc ở cột Diachi là All để tôi bỏ điều kiện lọc vừa rồi. Sau đó tôi xoá dữ liệu vừa nhập vào ở ô A6 và B6. Sau đó tôi lại lập lại điều kiện lọc ở cột Diachi là Quận Thủ Đức. Bạn sẽ thấy hàng số 6 cũng bị dấu luôn.Vâng đó là bất tiện của AutoFilter. _Đối với AdvancedFilterTương tự đối với AdvancedFilter. Tôi chọn Data/Filter/AdvancedFilter với các dữ liệu như sau:Kết quả tôi được các dữ liệu ở hàng 16 và 17 dưới đây Đến lúc này nếu bạn chọn Insert/Name/Define bạn sẽ thấy hai tên Criteria, và Extract trong hộp thoại Define NameVới công cụ AdvanceFilter ngoài hai vùng có tên trên bạn cũng được vùng có tên là _FilterDatabase. Nhưng như đã nói ở trên, tên này không thể thấy hay xoá được theo cách thông thường. Tuy nhiên, bạn có thể sử dụng đoạn mã VBA đơn giản sau để xoá và gọi hộp thoại AdvanceFilter:Sub ShowAdvancedFilterDialog() ' Xoa cac tenOn Error Resume Next With ActiveWorkbook .Names("_FilterDatabase").Delete .Names("Criteria").Delete .Names("Extract").Delete End With On Error GoTo 0 ' Goi hop thoaiApplication.Dialogs(xlDialogFilterAdvanced).Show End Sub Nếu bạn không sử dụng AdvanceFilter, thì bạn có thể sửa lại đoạn mã trên như sau:Sub DeleteNames() ' Xoa cac tenOn Error Resume Next With ActiveWorkbook .Names("_FilterDatabase").Delete End With End Sub Để sử dụng thủ tục này bạn copy thủ tục trên và đưa vào VBA module. Khi cần sử dụng bạn dùng phím nóng Alt + F8, sau đó chọn thủ tục mà bạn muốn chạy và chọn Run.Tóm lại một khi bạn không muốn Excel tự động lấy vùng lọc trước đó thì bạn sử dụng các thủ tục trên.Chúc các bạn thành công. Hy vọng bài viết trên sẽ giúp ích các bạn phần nào trong công việc.Mọi góp ý xin gởi cho tôi theo địa chỉ email sau:levanduyet@yahoo.com Lê Văn Duyệt.