Đối với đại đa số người dùng Excel, khi từ "lọc dữ liệu" xuất hiện trong đầu họ, chỉ có bộ lọc cổ điển thông thường từ tab Dữ liệu - Bộ lọc (Dữ liệu - Bộ lọc):
Một bộ lọc như vậy là một điều quen thuộc, không còn nghi ngờ gì nữa, và đối với hầu hết các trường hợp, nó sẽ làm được. Tuy nhiên, có những tình huống khi bạn cần lọc theo một số lượng lớn các điều kiện phức tạp trong một số cột cùng một lúc. Bộ lọc thông thường ở đây không thuận tiện lắm và tôi muốn thứ gì đó mạnh mẽ hơn. Một công cụ như vậy có thể là bộ lọc nâng cao, đặc biệt là với một chút "hoàn thiện với một tập tin" (theo truyền thống).
Cơ sở
Để bắt đầu, hãy chèn một vài dòng trống phía trên bảng dữ liệu của bạn và sao chép tiêu đề bảng vào đó - đây sẽ là một phạm vi có các điều kiện (được đánh dấu bằng màu vàng cho rõ ràng):
Phải có ít nhất một dòng trống giữa các ô màu vàng và bảng gốc.
Trong các ô màu vàng, bạn cần nhập tiêu chí (điều kiện), theo đó quá trình lọc sẽ được thực hiện. Ví dụ: nếu bạn cần chọn chuối ở Moscow “Auchan” trong quý III, thì các điều kiện sẽ như sau:
Để lọc, hãy chọn bất kỳ ô nào trong phạm vi có dữ liệu nguồn, mở tab Ngày Và bấm vào Ngoài ra (Dữ liệu - Nâng cao). Trong cửa sổ mở ra, một dải ô có dữ liệu đã được nhập tự động và chúng tôi sẽ chỉ phải xác định dải điều kiện, tức là A1: I2:
Xin lưu ý rằng phạm vi điều kiện không thể được phân bổ "với một lề", tức là bạn không thể chọn thêm các dòng trống màu vàng, bởi vì một ô trống trong phạm vi điều kiện được Excel coi là không có tiêu chí và toàn bộ trống dòng như một yêu cầu hiển thị tất cả dữ liệu một cách bừa bãi.
Công tắc điện Sao chép kết quả sang vị trí khác sẽ cho phép bạn lọc danh sách không phải ở đó trên trang tính này (như với bộ lọc thông thường), nhưng để dỡ các hàng đã chọn vào một phạm vi khác, sau đó sẽ cần phải được chỉ định trong trường Đặt kết quả trong phạm vi. Trong trường hợp này, chúng tôi không sử dụng chức năng này, chúng tôi để Lọc danh sách tại chỗ và nhấp OK. Các hàng đã chọn sẽ được hiển thị trên trang tính:
Thêm Macro
"Chà, sự tiện lợi ở đây là ở đâu?" bạn hỏi và bạn sẽ đúng. Bạn không chỉ cần dùng tay nhập điều kiện vào các ô màu vàng mà còn phải mở hộp thoại, nhập phạm vi vào đó, nhấn OK. Buồn quá, tôi đồng ý! Nhưng “mọi thứ đều thay đổi khi chúng đến ©” - macro!
Làm việc với bộ lọc nâng cao có thể được tăng tốc và đơn giản hóa rất nhiều bằng cách sử dụng macro đơn giản sẽ tự động chạy bộ lọc nâng cao khi các điều kiện được nhập vào, tức là thay đổi bất kỳ ô màu vàng nào. Nhấp chuột phải vào tab của trang tính hiện tại và chọn lệnh Văn bản nguồn (Mã nguồn). Trong cửa sổ mở ra, sao chép và dán mã sau:
Private Sub Worksheet_Change (ByVal Target As Range) If Not Intersect (Target, Range ("A2: I5")) Không có gì thì Lỗi Tiếp tục lại ActiveSheet.ShowAllData Range tiếp theo ("A7"). CurrentRegion.AdvancedFilter Action: = xlFilterInPlace, CriteriaRange : = Range ("A1"). CurrentRegion End If End Sub Quy trình này sẽ tự động chạy khi bất kỳ ô nào trên trang tính hiện tại được thay đổi. Nếu địa chỉ của ô đã thay đổi rơi vào phạm vi màu vàng (A2: I5), thì macro này sẽ xóa tất cả các bộ lọc (nếu có) và áp dụng lại bộ lọc mở rộng cho bảng dữ liệu nguồn bắt đầu bằng A7, tức là mọi thứ sẽ được lọc ngay lập tức sau khi nhập điều kiện tiếp theo:
Vì vậy, mọi thứ đã tốt hơn nhiều, phải không? 🙂
Thực hiện các truy vấn phức tạp
Giờ đây, mọi thứ đang được lọc nhanh chóng, chúng ta có thể đi sâu hơn một chút vào các sắc thái và tháo rời cơ chế của các truy vấn phức tạp hơn trong bộ lọc nâng cao. Ngoài việc nhập các kết quả khớp chính xác, bạn có thể sử dụng các ký tự đại diện khác nhau (* và?) Và các dấu hiệu bất đẳng thức toán học trong một loạt các điều kiện để triển khai tìm kiếm gần đúng. Trường hợp nhân vật không quan trọng. Để rõ ràng, tôi đã tóm tắt tất cả các tùy chọn có thể có trong một bảng:
| Tiêu chuẩn | Kết quả |
| gr * hoặc gr | tất cả các ô bắt đầu bằng GrIe Grtai, Grquả mít, Granat và vv |
| = củ hành tây | chính xác tất cả các ô và chỉ với từ Cây cung, tức là kết hợp chính xác |
| * liv * hoặc * liv | ô chứa Phòng khách làm thế nào gạch dưới, tức là ОPhòng kháchviệc này, Phòng kháchep, TheoPhòng khách và vv |
| = p * v | từ bắt đầu bằng П và kết thúc bằng В ie ПThành phố điện khí hóa phía tây dãy núi Rocky đầu tiênв, Пetherв và vv |
| như | từ bắt đầu bằng А và tiếp tục chứa СIe Аxương chậuсin, Аnanaс, Asai và vv |
| = * s | những từ kết thúc bằng С |
| = ???? | tất cả các ô có văn bản gồm 4 ký tự (chữ cái hoặc số, bao gồm cả dấu cách) |
| = m ?????? n | tất cả các ô có văn bản gồm 8 ký tự bắt đầu bằng М và kết thúc bằng НIe Мandariн, Мlo âuн và vv |
| = * n ?? a | tất cả các từ kết thúc bằng А, ký tự thứ 4 từ cuối ở đâu НIe Chùm tiaнikа, Theoнozа và vv |
| > = e | tất cả các từ bắt đầu bằng Э, Ю or Я |
| <>*o* | tất cả các từ không chứa một chữ cái О |
| <>*vích | tất cả các từ ngoại trừ những từ kết thúc bằng HIV (ví dụ: lọc phụ nữ theo tên đệm) |
| = | tất cả các ô trống |
| <> | tất cả các ô không trống |
| > = 5000 | tất cả các ô có giá trị lớn hơn hoặc bằng 5000 |
| 5 hoặc = 5 | tất cả các ô có giá trị 5 |
| > = 3/18/2013 | tất cả các ô có ngày sau ngày 18 tháng 2013 năm XNUMX (bao gồm cả) |
Điểm tinh tế:
- Dấu * có nghĩa là bất kỳ số ký tự nào, và? - bất kỳ một ký tự nào.
- Logic trong xử lý truy vấn văn bản và số hơi khác nhau. Vì vậy, ví dụ, một ô điều kiện có số 5 không có nghĩa là tìm kiếm tất cả các số bắt đầu bằng năm, mà một ô điều kiện có ký tự B bằng B *, tức là sẽ tìm kiếm bất kỳ văn bản nào bắt đầu bằng ký tự B.
- Nếu truy vấn văn bản không bắt đầu bằng dấu =, thì bạn có thể nhẩm * ở cuối.
- Ngày phải được nhập theo định dạng tháng-ngày-năm của Hoa Kỳ và thông qua phân số (ngay cả khi bạn có cài đặt Excel và khu vực).
Kết nối logic VÀ-HOẶC
Các điều kiện được viết trong các ô khác nhau, nhưng trên cùng một dòng, được coi là liên kết với nhau bởi một toán tử logic И (VÀ):
Những thứ kia. lọc chuối cho tôi trong quý thứ ba, chính xác là ở Moscow và đồng thời từ Auchan.
Nếu bạn cần liên kết các điều kiện với một toán tử logic OR (HOẶC LÀ), sau đó chúng chỉ cần được nhập vào các dòng khác nhau. Ví dụ: nếu chúng ta cần tìm tất cả các đơn đặt hàng của quản lý Volina cho đào Moscow và tất cả các đơn đặt hàng cho hành tây trong quý XNUMX ở Samara, thì điều này có thể được chỉ định trong một loạt các điều kiện như sau:
Nếu bạn cần áp đặt hai hoặc nhiều điều kiện trên một cột, thì bạn có thể chỉ cần sao chép tiêu đề cột trong phạm vi tiêu chí và nhập điều kiện thứ hai, thứ ba, v.v. vào dưới đó. điều kiện. Vì vậy, ví dụ: bạn có thể chọn tất cả các giao dịch từ tháng XNUMX đến tháng XNUMX:
Nói chung, sau khi "hoàn thành với một tệp", bộ lọc nâng cao trở thành một công cụ khá tốt, ở một số nơi không tệ hơn bộ lọc tự động cổ điển.
- Bộ lọc siêu lọc trên macro
- Macro là gì, ở đâu và cách chèn mã macro trong Visual Basic
- Bảng thông minh trong Microsoft Excel