Data Analysis with Excel: Filter

Bạn có thể filter dữ liệu trên excel theo những tiêu chí nhất định mà bạn muốn. Trong bài viết này chúng ta sẽ cùng nhau tìm hiểu tất tần tật về tính năng filter của Excel nhé!

Trong bài viết này chúng ta sẽ tìm hiểu tất tần tật chủ đề Filter trong Excel nhé!

Note: Bạn có thể kéo xuống dưới cùng để download tài liệu của bài viết này.

Filter

1. Click vào ô bất kỳ bên trong một data set

2. Ở tab Data, trong group Sort & Filter, hãy click vào Filter

Bạn sẽ thấy icon mũi tên nằm ở trên đầu của các cột xuất hiện.

3. Click vào mũi tên nằm ở cột Country.

4. Click vào Select All để xoá hết các dấu check, tiếp theo bạn click vào ô USA để filter các giá trị USA.

5. Click OK

Kết quả sẽ chỉ hiện thị các sales ở USA

6. Click vào mũi tên ở cột Quarter

7. Click vào ô Qtr 4

8. Click OK

Kết quả sẽ chỉ hiện thị các sales ở USA trong quý 4

9. Để xoá filter, ở tab Data, trong group Sort & Filter, click Clear. Để xoá các filter và các icon mũi tên, click vào Filter.

Có một cách khác nhanh hơn để filter dữ liệu trên Excel

10. Chọn vào ô USA

11. Chuột phải, và sau đó click Filter, Filter by Selected Cell's Value.

Kết quả chỉ hiển thị các sales ở USA

Number and Text Filter

Trong phần này chúng ta sẽ áp dụng tính năng number filter và text filter của Excel để hiển thị các record với các tiêu chí nhất định mà chúng ta mong muốn.

Number Filter

Để áp dụng number filter, bạn hãy thực thi các bước sau đây.

1. Click vào mũi tên ở cột Sales

2. Click Number Filters (Tuỳ chọn này có sẵn bởi vì cột sales có chứa các dữ liệu số), tiếp theo bạn chọn Greater Than từ danh sách.

3. Nhập 10,000 và click OK

Kết quả là bản records sẽ chỉ hiển thị những nơi có sales lớn hơn $10,000.

Bạn có thể filter những giá trị bằng, ít hơn, những giá trị nằm giữa hai giá trị, top giá trị hoặc những giá trị trên trung bình,...

Text Filter

Để thực hiện text filter, bạn hãy thực thi những bước sau

4. Click vào mũi tên ở cột Last Name

5. Click Text Filter (Tuỳ chọn này có sẵn bởi vì cột Last Name có chứ dữ liệu chữ), tiếp theo chọn Equals từ danh sách.

6. Nhập ?m* và click OK

Dấu hỏi (?) đại diện cho một ký tự bất kỳ. Dấu hoa thị (*) đại diện cho bất kỳ ký tự nào, nó không giới hạn số lượng ký tự, có thể là 0 hoặc nhiều hơn.

Kết quả, records chỉ hiển thị các giá trị có ký tự thứ 2 là ký tự m ở cột Last Name

Bạn cũng có thể hiển thị các record bắt đầu với ký tự cụ thể, kết thúc với một ký tự cụ thể, chứa hoặc không chứa một ký tự cụ thể,...Bạn có thể thoải mái mày mò khám phá tiếp nhé!

Date Filter

Trong phần này chúng ta sẽ áp dụng date filter để hiển thị các tiêu chí nhất định trong Excel nhé!

1. Click vào icon mũi tên ở ô Date

2. Click vào dấu + bên cạnh 2015 rồi tích vào ô January

3. Click OK

Kết quả sẽ hiển thị các sales vào tháng 1 năm 2015

4. Click vào mũi tên ở cột Date

5. Click vào Select All để chọn tất cả các hộp

6. Click vào Date Filters (Tuỳ chọn này có sẵn bởi vì cột Date chữa loại dữ liệu ngày tháng), tiếp theo hãy chọn Last Month từ danh sách

Kết quả chỉ hiển thị các sales của tháng trước.

Các date filter sẽ phụ thuộc vào ngày hiện tại

Advanced Filter

Khi sử dụng Advanced Filter, bạn cần nhập các tiêu chí trên worksheet.

Bạn phải tạo một phạm vi tiêu chí (đường viền màu xanh trong hình bên dưới chỉ để minh họa) phí trên data set của bạn.

Phạm vi tiêu chí này cần sử dụng cùng một tiêu đề với cột của data set. Hãy chắc chắn rằng có ít nhất một hàng trống giữa phạm vi tiêu chí và data set của bạn.

Tiêu chí And

Để hiển thị các sales có giá trị USA và (and) Qtr 4, bạn hãy thực thi các bước sau.

1. Nhập các tiêu chí nhất định như hình bên dưới trên worksheet

2. Click vào ô bất kỳ trong data set

3. Ở tab Data, trong group Sort & Filter, hãy click vào Advanced

4. Click vào hộp Criteria range sau đó chọn phạm vị A1:D2

5. Click OK

Kết quả

Chúng ta có thể làm được điều trên dễ hơn bằng cách filter theo cách thông thường. Advanced Filter sinh ra phù hợp cho việc filter các tiêu chí Or hơn.

Tiêu chí Or

Để hiển thị các sales trong quý tư (Qtr 4) ở USA hoặc (Or) trong quý một (Qtr 1) ở UK, hãy thực thi các bước sau.

6. Nhập các tiêu chí như hình bên dưới trong worksheet.

7. Ở tab Data, trong group Sort & Filter, hãy click vào Advanced, chọn phạm vị A1:D3 trong Criteria range.

8. Click OK

Kết quả

Tiêu chí theo công thức

Để hiển thị sales trong quý tư (Qtr 4) ở USA lớn hơn $10,000 hoặc (Or) trong quý một (Qtr 1) ở UK, hãy thực thi các bước sau.

9. Nhập tiêu chí và công thức như hình bên dưới trên worksheet

10. Ở tab Data, trong group Sort & Filter, hãy click vào Advanced, chọn phạm vị A1:E3 trong Criteria range.

11. Click OK

Kết quả

Công thức luôn luôn phải ở trong một cột mới và sẽ luôn trả về TRUE hoặc FALSE

Chúng ta cần tạo một tham chiếu liên quan đến ô đâu tiên trong cột của data set.

Ở ví dụ trên chúng ta filter giá trị sale nên tham chiếu là ô đầu tiên của cột Sales chính là B6.

Remove Duplicates

Trong phần này, chúng ta sẽ cùng tìm hiểu cách xoá các giá trị bị trùng lặp trong Excel.

1. Click vào ô bất kỳ trong data set

2. Ở tab Data, trong group Data Tool, click Remove Duplicates.

Hộp thoại Remove Duplicates sẽ xuất hiện

3. Tick tất cả các hộp rồi click OK

Kết quả là Excel sẽ xoá tất cả các hàng giống nhau (viền xanh) ngoại trừ hàng đầu tiên (Màu vàng)

Để xoá các hàng bị trùng lặp của các cột nhất định thì bạn hãy làm theo các bước sau.

4. Ví dụ, chúng ta sẽ xoá các hàng bị trùng ở cột Last Name và Country

5. Tick vào ô Last Name và Country rồi click OK.

Kết quả là Excel sẽ xoá tất cả các hàng bị trùng lặp ở cột Last Name và Country (Viền xanh) ngoại trừ hàng đầu tiên mà nó tìm thấy (Màu vàng).

Chúng ta hãy xem một tính năng Excel thú vị hơn để loại bỏ các giá trị trùng lặp. Bạn có thể sử dụng Advanced Filter để trích xuất các hàng duy nhất (hoặc giá trị duy nhất trong một cột).

6. Ở tab Data, trong group Sort & Filter, hãy click vào Advanced

Hộp thoại Advanced Filter xuất hiện

7. Click Copy to another location

8. Click vào ô List Range và chọn phạm vị A1:A17 (Bạn có thể xem hình bên dưới)

9. Click vào ô Copy to và chọn ô F1

10. Tick vào Unique records only

11. Click OK

Kết quả là Excel xoá hết tất cả các giá trị trùng lặp ở cột Last Name và hiển thị kết quả ở cột F.

Ở bước 8, thay vì chọn phạm vi A1:A17, bạn có thể chọn phạm vị A1:D17 để trích xuất những hàng duy nhất.

12. Cuối cùng, bạn có thể sử dụng Conditional Formatting trong Excel để highlight những giá trị bị trùng lặp.

13. Hoặc bạn cũng có thể sử dụng conditional formatting để highlight các hàng bị trùng lặp.

Outlining Data

Outlining data sẽ giúp cho dữ liệu của chúng ta sẽ dễ nhìn hơn rất nhiều. Trong ví dụ này, chúng ta sẽ gom nhóm và tính tổng các hàng có dữ liệu liên quan với nhau.

1. Đầu tiên, bạn hãy sort dữ liệu theo cột Company nhé. Bạn có thể xem lại bài viết Sort nếu có lỡ quên kiến thức.

2. Ở tab data, trong group Outline, click Subtotal.

3. Chọn cột Company để sử dụng cho việc outline worksheet của chúng ta nhé!

4. Sử dụng hàm Count

5. Tick vào ô Company

6. Click OK

Kết quả sẽ như thế này:

7. Để thu gọn các nhóm, bạn có thể click vào dấu - nằm bên trái màn hình nhé.

Bạn cũng có thể sử dụng các con số phía trên bên trái để thu gọn hoặc mở rộng các nhóm theo cấp độ.

Ví dụ, click số 2 để chỉ hiển thị subtotals, click số 1 để chỉ hiển thị Grand Count, click số 3 để xem tất cả mọi thứ.

Để thu gọn một nhóm các cột, hãy thực thi các bước sau.

8. Ví dụ, chọn cột A và B

9. Ở tab Data, trong group Outline, click Group

10. Click vào dấu - phía trên cột C (nó sẽ chuyển thành dấu cộng)

Kết quả sẽ như thế này:

11. Để xoá Outline, click vào ô bất kỳ trong data set.

12. Ở tab data, trong group Outline, click Subtotal, Remove All.

Subtotal

Sử dụng hàm SUBTOTAL trong Excel thay thế các hàm SUM, COUNT, MAX, etc để bỏ qua các hàng bị ẩn bởi filter hoặc bị ẩn bởi thủ công.

Các hàng bị ẩn bởi Filter

1. Ví dụ, hàm SUM dưới đây tính tổng doanh số

2. Áp dụng một filter

3. Hàm SUM vẫn tính tổng của các hàng đã bị ẩn

4. Hàm SUBTOTAL bỏ qua các hàng bị ẩn bởi filter và cho ra kết quả đúng hơn.

109 là tham số của hàm SUM nếu bạn sử dụng hàm SUBTOTAL để thay thế

5. Thật khó để nhớ tham số nào của hàm nào. Tuy nhiên, tính năng AutoComplete trong Excel sẽ giúp bạn làm điều này.

Các hàng bị ẩn thủ công

1. Ví dụ, hàm SUM bên dưới tính tổng của phạm vi các ô (A1:A3)

2. Ẩn dòng thứ 2 bằng cách click chuột phải vào hàng thứ 2 rồi click Hide

3. Hàm SUM vẫn tính hàng đã bị ẩn

4. Hàm SUBTOTAL bỏ qua các hàng bị ẩn thủ công và cho ra kết quả đúng hơn.

Unique Values

Để tình những giá trị duy nhất trong Excel, bạn hãy sử dụng Advanced Filter. Bạn có thể trích xuất các giá trị duy nhất hoặc filter các giá trị duy nhất đó. Nếu bạn sử dụng Excel 365 hoặc Excel 2021 thì có thể sử dùng hàm UNIQUE cũng rất thú vị.

Trích xuất Unique Values

Khi sử dụng Advanced Filter trong Excel, bạn nên lưu ý luôn cần phải có nhãn tiêu chí nằm đầu tiên ở mỗi cột của dữ liệu.

1. Click vào một ô bất kỳ trong phạm vi danh sách.

2. Ở tab Data, trong group Sort & Filter, hãy click vào Advanced

Hộp thoại Advanced Filter sẽ xuất hiện

3. Click Copy to another location (Xem hình bên dưới)

4. Click vào ô Copy to và chọn ô C1

5. Tick vào ô Unique records only

6. Click OK

Kết quả:

Filter cho Unique Values

Để filter cho các giá trị duy nhất trong Excel thì rất là dễ.

1. Click vào một ô trong phạm vi của danh sách

2. Ở tab Data, trong group Sort & Filter, hãy click vào Advanced

3. Click Filter the list, in-place

4. Tick vào ô Unique records only

5. Click OK

Kết quả:

Hàm UNIQUE

Nếu bạn có Excel 365 hoặc Excel 2021, thật đơn giản để trích xuất các giá trị duy nhất với hàm UNIQUE

1. Hàm UNIQUE bên dưới trích xuất các giá trị duy nhất của danh sách. Hàm dưới đây không có tham số.

2. Hàm UNIQUE dưới đây trích xuất các giá trị chỉ xuất hiện một lần.

Hàm này có 2 tham số. Tham số 0 để hàm UNIQUE trích xuất giá trị theo mảng dọc. Tham số 1 để trích xuất giá trị chỉ xuất hiện một lần.

Hàm FILTER

Sử dụng hàm FILTER trong Excel 365/2021 để trích xuất các records theo các tiêu chí nhất định. Hàm FILTER sẽ giúp bạn linh hoạt hơn rất nhiều.

1. Hàm FILTER đơn giản dưới đây có hai tham số và trích xuất tất cả các giá trị USA.

2. Hàm FILTER dưới đây trích xuất tất cả giá trị UK theo tiêu chí đã nhập trước tại ô B17.

3. Hàm FILTER dưới đây có một tham số thứ ba. Tham số này bạn có thể nhập một lời nhắn để hiển thị nếu không có giá trị nào được tìm thấy (thay vì xuất hiện dòng chữ #CALC! error)

4. Hàm FILTER dưới đây sử dụng toán tử * để trích xuất tất cả records có sales lớn hơn $10,000 và (and) cột Country bằng với "USA".

5. Hàm FILTER dưới đây sử dụng toán tử + để trích xuất tất cả record có Last Name bằng với "Smith" hoặc (or) Last Name bằng với "Brown"

6. Thêm hàm SORT để sort records theo cột đầu tiên.

Tài liệu của series

Để chuẩn bị cho việc thực hành, bạn tải tài liệu của series bằng cách điền vào form bên dưới nhé!

Chúc bạn có trải nghiệm học tập vui vẻ tại 200Lab!

Nếu bạn có định hướng trở thành Data Analyst chuyên nghiệp thì bạn có thể tham khảo bộ khóa học toàn diện chuyên nghiệp với combo 4 khóa học trong 1 bootcamp của 200Lab tại đây.

Bài viết tiếp theo cùng series:

• Data Analyst with Excel: Conditional Formatting