PDA

View Full Version : Hướng dẫn tạo vùng tiêu chuẩn và lọc dữ liệu



duongdv
29-06-08, 02:43 PM
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:
http://i283.photobucket.com/albums/kk310/duongdv/Bangcosodulieumau3.gif

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:
http://i283.photobucket.com/albums/kk310/duongdv/Tieuchuan1.gif

· 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:
http://i283.photobucket.com/albums/kk310/duongdv/Tieuchuan2.gif

@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)
http://i283.photobucket.com/albums/kk310/duongdv/Tieuchuan3.gif
Chức vụ bắt đầu với bất kỳ ký tự nào sau đó là chữ P (TP,PP…)
http://i283.photobucket.com/albums/kk310/duongdv/Tieuchuan4-1.gif

- 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”
http://i283.photobucket.com/albums/kk310/duongdv/Tieuchuan5.gif
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
http://i283.photobucket.com/albums/kk310/duongdv/Tieuchuan6.gif

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.
http://i283.photobucket.com/albums/kk310/duongdv/Tieuchuan7.gif
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

http://i283.photobucket.com/albums/kk310/duongdv/Tieuchuan8.gif

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.
http://i283.photobucket.com/albums/kk310/duongdv/Tieuchuan9.gif
· 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:D13 .. 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

hlovns
23-10-09, 06:53 PM
Advanced Filter có thể kết xuất dữ liệu ra sheet khác được không ạ? Em lọc và kết xuất dữ liệu trên cùng một sheet thì được nhưng nếu chọn Vùng Extract nằm ở sheet khác thì lại không được. Mong các anh chỉ giáo!

ngngocnguyen
27-10-09, 12:18 PM
Gửi các bạn chỉ giúp dùm mình xem tại sao trong bảng tính của mình lệnh advanced filter bị mờ,cả lệnh pivot table cũng mờ nữa, vậy làm sao chọn? Mình sai chổ nào, chỉ mình với! Cám ơn mọi người nhiều lắm!

ptlong04x1
06-11-09, 08:59 PM
Advanced Filter có thể kết xuất dữ liệu ra sheet khác được không ạ? Em lọc và kết xuất dữ liệu trên cùng một sheet thì được nhưng nếu chọn Vùng Extract nằm ở sheet khác thì lại không được. Mong các anh chỉ giáo!


Không làm bằng tay được nhưng nếu Advanced Filter bằng VBA thì lại được. Xem file.

jun_hd
12-11-09, 12:12 PM
Bác ơi file của bác đặt pass à e không mở đựơc, với sao e dùng Advanced Filter e quét vùng database sao thì cái extract nó ra y xì cái database chứ hem thấy nó lọc gì cả hix e làm sai chỗ nào thế.
Quên, cám ơn các bác nhiều

jun_hd
12-11-09, 05:27 PM
E lọc được dữ liệu rồi và em lưu Extract ở một sheep khác cụ thể là e để database ở sheep 1 và extract ở sheep 2, các bác có cách nào để khi e thêm dữ liệu vào database thì bên extract nó cũng tự động cập nhật dữ liệu lọc nếu dữ liệu đó đủ ĐK - e cám ơn các bác nhìu^^.

w_tathha
25-01-13, 02:55 PM
Bạn cho xin 1 file excel làm ví dụ đi.

muabui_kt08
03-03-13, 12:53 PM
bác ơi làm sao để xuất qua sheet khác thể? em mở file của bác lên xem mà chẳng biết làm sao cả?huhu

haonlh
04-03-13, 04:10 PM
Advanced Filter có thể kết xuất dữ liệu ra sheet khác được không ạ? Em lọc và kết xuất dữ liệu trên cùng một sheet thì được nhưng nếu chọn Vùng Extract nằm ở sheet khác thì lại không được. Mong các anh chỉ giáo!

Tôi làm trên 2003 như sau
Chọn Sheet chứa kết quả, chọn lệnh lọc nâng cao, chọn Sheet có dữ liệu (chắc chỗ này bạn quen rồi).
Chọn Extract trên Sheet chứa kết quả.
Chúc thành công

haonlh
16-04-13, 09:38 AM
Bài 4

Không làm bằng tay được nhưng nếu Advanced Filter bằng VBA thì lại được. Xem file.

Đoạn mã của anh rất ngắn làm tôi thích.
Sub Af()
Range("a1.a6").AdvancedFilter _
xlFilterCopy, [a8.a9], Sheets(2).[a1]
End Sub

Đọc lại mã của anh, thấy dấu chấm trong địa chỉ vùng. Có phải dấu này thay cho dấu : ???

Thêm nữa, nếu trên Sheet1, bảng có 2 cột thì nó chỉ chép cột A thôi. Tôi đã thay a1.a6 bằng a1:b6
cũng không được. Khắc phục?

hungpecc1
16-04-13, 09:49 AM
Bài 4


Đoạn mã của anh rất ngắn làm tôi thích.
Sub Af()
Range("a1.a6").AdvancedFilter _
xlFilterCopy, [a8.a9], Sheets(2).[a1]
End Sub

Đọc lại mã của anh, thấy dấu chấm trong địa chỉ vùng. Có phải dấu này thay cho dấu : ???

Thêm nữa, nếu trên Sheet1, bảng có 2 cột thì nó chỉ chép cột A thôi. Tôi đã thay a1.a6 bằng a1:b6
cũng không được. Khắc phục?
Tôi xin khẳng định 1 câu với bài #4 : " Nếu làm bằng Code VBA được <=====> thì làm bằng tay cũng được ",
Cụ thể: nếu bạn muốn copy vào sheet nào , thì tại sheet đó bạn vào Advanced Filter --> soure bên sheet khác

3 cách viết sau sẽ cho kết quả giống nhau :
Range("A8.A9") = Range("A8:A9") = Range("A8","A9")

haonlh
17-04-13, 08:00 AM
Bài 4
Thêm nữa, nếu trên Sheet1, bảng có 2 cột thì nó chỉ chép cột A thôi. Tôi đã thay a1.a6 bằng a1:b6
cũng không được. Khắc phục?

Qua mấy lần thử, tôi thấy nếu vùng kết quả không có số liệu thì nội dung lọc có đủ các cột.
Do đó, nên có lệnh xoá vùng sẽ chép kết quả lọc đến.

Sub Af()
Sheets(2).[a1].CurrentRegion.ClearContents
Range("a1.b6").AdvancedFilter _
xlFilterCopy, [a8.a9], Sheets(2).[a1]
End Sub