Số lượng các mục có sẵn để lọc là có hạn. Excel không thể lọc các cột có số mục vượt quá 999 (không phải số hàng).

Để lọc khi có hơn 999 mục, hãy sử dụng bộ lọc nâng cao.

Để tạo bộ lọc nâng cao, chúng tôi sẽ sử dụng các hàm “OFFSET” và “COUNTA” trong Microsoft Excel.

COUNTA: Nó trả về số lượng ô chứa giá trị.

Cú pháp của hàm “COUNTA”: = COUNTA (value1, value2, value3 …….)

Ví dụ: _ _Trong phạm vi A1: A5, các ô A2, A3 và A5 chứa các giá trị và các ô A1 và A4 để trống. Chọn ô A6 và viết công thức- _ = COUNTA (A1: A5) _ _ hàm sẽ trả về 3_

img1

OFFSET: Nó trả về một tham chiếu đến một phạm vi được bù đắp một số hàng và cột từ một phạm vi hoặc ô khác.

Cú pháp của hàm OFFSET: = OFFSET (tham chiếu, hàng, cột, chiều cao, chiều rộng) _ Tham khảo: -_ Đây là ô hoặc dải ô mà bạn muốn bù trừ.

_ Các hàng và Cột để di chuyển: _ – Số hàng bạn muốn di chuyển từ điểm bắt đầu và cả hai hàng này có thể là số dương, số âm hoặc số không.

_Height and Width _ * : – Đây là kích thước của phạm vi bạn muốn trả về. Đây là trường tùy chọn . Hãy lấy một ví dụ để hiểu về hàm Offset trong Excel.

Chúng tôi có dữ liệu trong phạm vi A1: D10. Cột A chứa Mã sản phẩm, Cột B chứa Số lượng, cột C chứa giá thành mỗi sản phẩm và cột D chứa Tổng giá thành. Chúng ta cần trả về giá trị của ô C5 trong ô E2.

img2

Để có được kết quả mong muốn, chúng ta cần làm theo các bước được đề cập dưới đây.

  • Chọn ô E2 và viết công thức.

OFFSET (A1,4,2,1,1) * và nhấn Enter trên bàn phím.

  • Hàm sẽ trả về giá trị của ô C5 .

img3

img4

Trong ví dụ này, chúng ta cần lấy giá trị từ ô C5 đến E2. Ô tham chiếu của chúng tôi là ô đầu tiên trong phạm vi A1 và C5 là 4 hàng bên dưới và 2 cột bên phải từ A1. Do đó, công thức là = OFFSET (A1,4,2,1,1) hoặc = OFFSET (A1,4,2) (vì 1,1 là tùy chọn).

Bây giờ, hãy lấy một ví dụ để truy xuất giá trị cuối cùng trong danh sách động.

Chúng tôi có tên quốc gia trong một phạm vi. Bây giờ, nếu chúng tôi thêm nhiều quốc gia hơn vào danh sách này, nó sẽ tự động có sẵn trong danh sách thả xuống.

img5

Để chuẩn bị bộ lọc nâng cao, hãy làm theo các bước dưới đây: – * Chọn ô B2.

  • Chuyển đến tab Dữ liệu, chọn Xác thực Dữ liệu từ nhóm Công cụ dữ liệu.

img6

  • Hộp thoại “Xác thực Dữ liệu” sẽ xuất hiện. Trong tab “Cài đặt”, chọn “Tùy chỉnh” từ danh sách Cho phép thả xuống.

img7

  • Hộp công thức sẽ được kích hoạt.

  • Viết công thức vào ô này.

* _ = OFFSET (A: A, 1,0, COUNTA (A: A) -1,1) ._

  • Nhấp vào OK.

img8

  • Ở giai đoạn này, ô cập nhật cuối cùng là A11.

img9

  • Để kiểm tra xem Xác thực Dữ liệu có hoạt động bình thường hay không, hãy thêm tên thành phố vào ô A12.

img10

Ngay sau khi bạn thêm một mục nhập trong A12, nó sẽ được thêm vào danh sách thả xuống.

Đây là cách chúng ta có thể thêm nhiều mục nhập hơn 999 mục trong Microsoft Excel.