Chào các bạn. Đã có rất nhiều những bài viết của các anh Chị hướng dẫn về Advance Filter... rất chi tiết và cụ thể như bài viết của handung107. Tôi cũng xin trình bày lại vấn đề này cũng mong là sẽ giúp ích được cho những người mơi tìm hiểu về lọc và rút trích dữ liệu trong Excel.
Phần 1:
1. Hướng dẫn tạo vùng tiêu chuẩn (Criteria Range)
Giả sử chúng ta có bảng cơ sơ dữ liệu như sau:
Khái niệm về vùng tiêu chuẩn
· Vùng tiêu chuẩn:
- Các thao tác, các hàm tính toán trên Excel thường thoả mãn các điều kiện quy định trong một vùng gọi là vùng tiêu chuẩn (Ciriteria Range)
- Vùng này bao gồm một dòng đầu tiên chứa một hay nhiều tiêu đề của vùng cơ sở dữ liệu, các dòng còn lại chứa các điều kiện.
Ví dụ: Vùng tiêu chuẩn với điều kiện:
· Các dạng vùng tiêu chuẩn:
- Tiêu chuẩn số, văn bản.
Ô điều kiện có thể là số, văn bản, có thể kết hợp các phép toán >,<,>=,<=,<>
Ví dụ 1: Vùng tiêu chuẩn với điều kiện:
@Chú ý: Có thể dùng các ký tự * hay ? trong các chuỗi ký tự của điều kiện
Ví dụ 2: Vùng tiêu chuẩn với điều kiện
Tên bắt đầu bằng N ( Nam, Nga, Ngân)
Chức vụ bắt đầu với bất kỳ ký tự nào sau đó là chữ P (TP,PP…)
- Tiêu chuẩn Công thức:
Ô điều kiện là một công thức. Nếu sử dụng tiêu chuẩn này phải lưu ý 2 điều kiện sau:
+ Ô tiêu đề của vùng tiêu chuẩn phải là một tieu đề khác với tất cả các tiêu đề của vùng dữ liệu hoặc là tiê đề trống.
+ Trong công thức của ô điều kiện, phải dùng địa chỉ ô trong mẩu tin đầu tiên ( Dòng thứ 2 trong vùng dữ liệu)
+ Giá trị trong ô điều kiện sẽ là True hoặc False ( Đúng hoặc Sai)
Ví dụ:
Tiêu chuẩn ký tự đầu của các tên khác “H”
Công thức trong ô điều kiện là: Left(C2,1)<>”H”
- Phối hợp nhiều điều kiện trong cùng một tiêu chuẩn:
Trong vùng tiêu chuẩn có thể kết hợp nhiều điều kiện với nhau:
a. Kết hợp Và: Các điều kiện đặt cùng dòng
Ví dụ: Vùng tiêu chuẩn thể hiện 2 điều kiện: có số con bằng 2 và chức vụ là NV1
b. Kết hợp Hoặc: Các điều kiện đặt khác dòng:
Ví dụ 1: Vùng tiêu chuẩn thể hiện 2 điều kiện: Có số con bằng 2 hoặc chức vụ bằng NV1.
Ví dụ 2: Vùng tiêu chuẩn thể hiện 2 điều kiên: số con bằng 2 hoặc số con bằng 3
Ví dụ 3: Vùng tiêu chuẩn thể hiện cả 2 điều kiện có tính chất “Và” và “Hoặc”: Là nhân viên có chức vụ là NV1 và số con bằng 2 hoặc có chức vụ là NV2 và có số con bằng 3.
· Tạo vùng tiêu chuẩn:
-Vùng tiêu chuẩn phải ở ngoài phạm vi của vùng cơ sở dữ liệu
- Dòng đầu tiên chứa các tiêu đề cột cần đặt điều kiện.
- Thông thường, nếu khôngdùng tiêu chuẩn công thức, nên sao chép từ vùng dữ liệu ban đầu đến vùng dữ liệu tiêu chuẩn.
- Những dòng phía dưới tiêu đề cột sẽ chứa các điều kiện
Phần 2: Lọc và rút trích dữ liệu
Kết xuất: (lọc dữ liệu)
- Kết xuất các mẩu tin (dòng) thoả mãn điều kiện đã quy định đến một vùng khác.
- Để thực hiện thao tác này phải tổ chức ra các vùng sau:
· Vùng Database: Vùng chứa dữ liệu sẽ thao tác
· Vùng Criteria: Vùng tiêu chuẩn chứa điều kiện lọc
· Vùng Extract: là vùng dữ liệu chứa các mẫu tin của vùng Database thoả mãn điều kiện của vùng tiêu chuẩn
Ví dụ:
Vùng A1:I8 là vùng Database
Vùng B12:B13, hoặc D12
13 .. là vùng tiêu chuẩn.
Vùng B15: ... là vùng kết xuất
Thực hiện:
1. Chọn một ô bất kỳ trong vùng Database
2. Thực hiện lệnh Data \Filter \ Advanced Filter
3. Trong Action chọn Copy to another Localtion (Để sao chép các mẫu tin cần lọc sang một vùng khác).
4. Trong List Range nhập địa chỉ của vùng Database
5. Trong Criteria nhập địa chỉ của vùng Criteria range ( Vùng tiêu chuẩn)
6. Đặt con trỏ vào mục Copy to và chọn một ô là vị trí đầu tiên của vùng sẽ chứa dữ liệu được kết xuất ra.
7. Nếu chọn Unique record only, dữ liẹu lọc ra nếu có những dòng trùng nhau sẽ chỉ thể hiện một dòng.
8. Chọn Ok, các mẫu tin thoả mãn điều kiện trong một vùng Criteria sẽ được trình bày trong vùng bắt đầu tại ô đã chọn trong vùng Copy to.
@ Chú ý: Nếu trong Action chọn Filter the list, in place sau khi thực hiện vùng Database chỉ xuất hiện những vùng thoả mãn điều kiện trong vùng Criteria. Từ trình đơn chọn Data \ Filter \ Show All để hiển thị lại các dòng trong vùng Database
Phần 1:
1. Hướng dẫn tạo vùng tiêu chuẩn (Criteria Range)
Giả sử chúng ta có bảng cơ sơ dữ liệu như sau:
Khái niệm về vùng tiêu chuẩn
· Vùng tiêu chuẩn:
- Các thao tác, các hàm tính toán trên Excel thường thoả mãn các điều kiện quy định trong một vùng gọi là vùng tiêu chuẩn (Ciriteria Range)
- Vùng này bao gồm một dòng đầu tiên chứa một hay nhiều tiêu đề của vùng cơ sở dữ liệu, các dòng còn lại chứa các điều kiện.
Ví dụ: Vùng tiêu chuẩn với điều kiện:
· Các dạng vùng tiêu chuẩn:
- Tiêu chuẩn số, văn bản.
Ô điều kiện có thể là số, văn bản, có thể kết hợp các phép toán >,<,>=,<=,<>
Ví dụ 1: Vùng tiêu chuẩn với điều kiện:
@Chú ý: Có thể dùng các ký tự * hay ? trong các chuỗi ký tự của điều kiện
Ví dụ 2: Vùng tiêu chuẩn với điều kiện
Tên bắt đầu bằng N ( Nam, Nga, Ngân)
Chức vụ bắt đầu với bất kỳ ký tự nào sau đó là chữ P (TP,PP…)
- Tiêu chuẩn Công thức:
Ô điều kiện là một công thức. Nếu sử dụng tiêu chuẩn này phải lưu ý 2 điều kiện sau:
+ Ô tiêu đề của vùng tiêu chuẩn phải là một tieu đề khác với tất cả các tiêu đề của vùng dữ liệu hoặc là tiê đề trống.
+ Trong công thức của ô điều kiện, phải dùng địa chỉ ô trong mẩu tin đầu tiên ( Dòng thứ 2 trong vùng dữ liệu)
+ Giá trị trong ô điều kiện sẽ là True hoặc False ( Đúng hoặc Sai)
Ví dụ:
Tiêu chuẩn ký tự đầu của các tên khác “H”
Công thức trong ô điều kiện là: Left(C2,1)<>”H”
- Phối hợp nhiều điều kiện trong cùng một tiêu chuẩn:
Trong vùng tiêu chuẩn có thể kết hợp nhiều điều kiện với nhau:
a. Kết hợp Và: Các điều kiện đặt cùng dòng
Ví dụ: Vùng tiêu chuẩn thể hiện 2 điều kiện: có số con bằng 2 và chức vụ là NV1
b. Kết hợp Hoặc: Các điều kiện đặt khác dòng:
Ví dụ 1: Vùng tiêu chuẩn thể hiện 2 điều kiện: Có số con bằng 2 hoặc chức vụ bằng NV1.
Ví dụ 2: Vùng tiêu chuẩn thể hiện 2 điều kiên: số con bằng 2 hoặc số con bằng 3
Ví dụ 3: Vùng tiêu chuẩn thể hiện cả 2 điều kiện có tính chất “Và” và “Hoặc”: Là nhân viên có chức vụ là NV1 và số con bằng 2 hoặc có chức vụ là NV2 và có số con bằng 3.
· Tạo vùng tiêu chuẩn:
-Vùng tiêu chuẩn phải ở ngoài phạm vi của vùng cơ sở dữ liệu
- Dòng đầu tiên chứa các tiêu đề cột cần đặt điều kiện.
- Thông thường, nếu khôngdùng tiêu chuẩn công thức, nên sao chép từ vùng dữ liệu ban đầu đến vùng dữ liệu tiêu chuẩn.
- Những dòng phía dưới tiêu đề cột sẽ chứa các điều kiện
Phần 2: Lọc và rút trích dữ liệu
Kết xuất: (lọc dữ liệu)
- Kết xuất các mẩu tin (dòng) thoả mãn điều kiện đã quy định đến một vùng khác.
- Để thực hiện thao tác này phải tổ chức ra các vùng sau:
· Vùng Database: Vùng chứa dữ liệu sẽ thao tác
· Vùng Criteria: Vùng tiêu chuẩn chứa điều kiện lọc
· Vùng Extract: là vùng dữ liệu chứa các mẫu tin của vùng Database thoả mãn điều kiện của vùng tiêu chuẩn
Ví dụ:
Vùng A1:I8 là vùng Database
Vùng B12:B13, hoặc D12
13 .. là vùng tiêu chuẩn.Vùng B15: ... là vùng kết xuất
Thực hiện:
1. Chọn một ô bất kỳ trong vùng Database
2. Thực hiện lệnh Data \Filter \ Advanced Filter
3. Trong Action chọn Copy to another Localtion (Để sao chép các mẫu tin cần lọc sang một vùng khác).
4. Trong List Range nhập địa chỉ của vùng Database
5. Trong Criteria nhập địa chỉ của vùng Criteria range ( Vùng tiêu chuẩn)
6. Đặt con trỏ vào mục Copy to và chọn một ô là vị trí đầu tiên của vùng sẽ chứa dữ liệu được kết xuất ra.
7. Nếu chọn Unique record only, dữ liẹu lọc ra nếu có những dòng trùng nhau sẽ chỉ thể hiện một dòng.
8. Chọn Ok, các mẫu tin thoả mãn điều kiện trong một vùng Criteria sẽ được trình bày trong vùng bắt đầu tại ô đã chọn trong vùng Copy to.
@ Chú ý: Nếu trong Action chọn Filter the list, in place sau khi thực hiện vùng Database chỉ xuất hiện những vùng thoả mãn điều kiện trong vùng Criteria. Từ trình đơn chọn Data \ Filter \ Show All để hiển thị lại các dòng trong vùng Database
Lần chỉnh sửa cuối:
