PDA

View Full Version : Tìm hiểu về Advanced Filter trong Excel


bichhuyen
19-01-2007, 10:17
PHẦN I: 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ấu
Td : 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ọc
Thí 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) < : nhỏ hơn (Td : <500, chỉ những số lượng nào nhỏ hơn 500 sẽ được lọc)
b) <= : nhỏ hơn hay bằng (<=500)
c) >= : 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ày
Bạ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ệu

4/ Á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 đi
b) 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 II: 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 III: 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, SoLuong

1/ Đ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õ >500
Lưu ý : Các điều kiện AND nằm cùng với nhau trên một hàng

2/ Đ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õ >500
Lư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 IV: LỌC 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 CSDL
Như 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ọc

3/ 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 V: MỘT VÀI VÍ DỤ VỀ Đ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ình

1/ Td 1 : Lọc DS các khách hàng mua hàng từ ngày 10/01/06 đến 15/01/06
Vù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/06

Nhậ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
Ngaythang

2/ 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õ HH2

Nhậ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à OR

3/ 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"

PHẦN VI: 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ê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) = FALSE
Dòng 4: =AND(A4=A$1, C4>B$1) = FALSE
Dòng 5: =AND(A5=A$1, C5>B$1) = FALSE
Dòng 6: =AND(A6=A$1, C6>B$1) = TRUE
Tươ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) = FALSE
Dòng 5: =AND(A5=A3, C5>B3) = FALSE
Dòng 6: =AND(A6=A4, C6>B4) = FALSE

Do đó, đị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


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ống

TD1 : 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=D2

TD3: 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 : =C2<>D2

Bạn cũng có thể sử dụng tiêu đề cột thay vì địa chỉ Cell tham chiếu. TD :=NgayDatHang<>NgayGiaoHang. 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 SoLuong
Vù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)>1

TD6 : 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ả đúng

TD7 : 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ò qua

TD8 : 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é

PHẦN VII: MỘT SỐ LƯU Ý

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ọc
b) Ký hiệu > và < : Nhập >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