Đánh giá lequocthai.com:
Bạn đã tạo một bảng Excel chỉn chu, nhưng khi cố dùng tiêu đề bảng làm danh sách thả xuống thì mọi thứ lại không hoạt động. Tính năng Xác thực Dữ liệu (Data Validation) của Excel vốn không tương thích tốt với tham chiếu bảng, nhưng có một giải pháp thông minh. Hãy ngừng việc nhập thủ công danh sách cố định và chuyển sang phương pháp động đồng bộ này.
Giả sử bạn có một bảng Excel đã định dạng tên là T_Inventory. Ở khu vực tra cứu, bạn muốn hai danh sách thả xuống: một ở ô H2 để chọn sản phẩm và một ở ô I1 để chọn thuộc tính (như Chi phí hay Nhà cung cấp). Khi cả hai được chọn, ô I2 sẽ trả về kết quả chính xác.
Một cách khác là tham chiếu trực tiếp đến phạm vi cố định như $A$1:$E$1 trong Xác thực Dữ liệu. Nhược điểm là nếu bạn thêm cột mới vào bảng, phạm vi tham chiếu này sẽ không tự mở rộng.
Tóm lại, bạn cần một danh sách thả xuống tự động cập nhật khi bảng mở rộng.
Tại sao bảng gây khó cho Xác thực Dữ liệu
Bảng Excel sử dụng tham chiếu có cấu trúc (ví dụ: T_Inventory[#Headers]), rất tiện vì chúng tự điều chỉnh khi dữ liệu thay đổi. Vấn đề là Xác thực Dữ liệu chỉ chấp nhận một phạm vi ô cụ thể hoặc một tên đã xác định, và nó không hiểu trực tiếp cú pháp tham chiếu có cấu trúc trong ngoặc vuông của bảng.
Để vượt qua hạn chế này, chúng ta sẽ dùng một phạm vi được đặt tên (Named Range) làm cầu nối giữa bảng và công cụ Xác thực Dữ liệu.
Giải pháp: Thiết lập công cụ tra cứu động
Xây dựng công cụ tra cứu này gồm 3 bước. Bước 2 chính là mẹo then chốt – một cầu nối thông minh giúp danh sách thả xuống của bạn thực sự động và bền vững.
Bước 1: Danh sách thả xuống Sản phẩm (Ô H2)
Xác thực Dữ liệu “không ưa” tham chiếu có cấu trúc nhưng lại “hợp tác” rất tốt với phạm vi được đặt tên. Đây là cách sạch nhất để đưa danh sách sản phẩm vào khu vực tra cứu.
Đầu tiên, chọn toàn bộ dữ liệu trong cột Product (Sản phẩm).
Di chuột lên viền trên của ô tiêu đề cột Product cho đến khi con trỏ thành mũi tên đen hướng xuống, rồi nhấp một lần. Thao tác này chọn toàn bộ dữ liệu trong cột mà không bao gồm tiêu đề.
Sau đó, tại hộp Tên (Name Box), nhập một tên dễ nhớ (ví dụ: ProductList) và nhấn Enter.
Vì phạm vi được đặt tên này là một phần của bảng, nó sẽ tự động mở rộng khi bạn thêm hàng mới.
Bây giờ, chọn ô H2, vào tab Data (Dữ liệu) và nhấp Data Validation (Xác thực Dữ liệu).
Trong hộp thoại, tại mục Allow (Cho phép), chọn List (Danh sách). Tại mục Source (Nguồn), nhập dấu = theo sau là tên bạn vừa tạo (=ProductList) và nhấp OK.
Giờ đây, khi chọn ô H2, bạn sẽ thấy danh sách thả xuống các sản phẩm.
Bước 2: Danh sách thả xuống Tiêu đề (Ô I1) – Mẹo then chốt
Để vượt qua việc Xác thực Dữ liệu không chấp nhận trực tiếp tiêu đề bảng, chúng ta sẽ tạo một phạm vi được đặt tên động. Đầu tiên, vào tab Formulas (Công thức), nhấp Name Manager (Trình quản lý Tên), rồi chọn New (Mới).
Trong trường Name (Tên), nhập HeaderList. Xóa mọi thứ trong trường Refers to (Tham chiếu tới), sau đó đặt con trỏ vào trường trống này và chọn tất cả các tiêu đề trong bảng của bạn. Hãy đảm bảo trường này hiển thị tham chiếu có cấu trúc đến tiêu đề bảng, ví dụ:
=T_Inventory[#Headers]
Nhấp OK rồi Close để đóng hộp thoại.
Bây giờ, chọn ô I1 (nơi hiển thị danh sách thuộc tính). Mở lại Data Validation, chọn List và tại Source, nhập =HeaderList.
Nhấp OK, bạn sẽ thấy danh sách thả xuống trong ô I1 chứa tất cả tiêu đề bảng.
Bước 3: Công thức trung tâm (Ô I2)
Khi đã chọn sản phẩm ở H2 và thuộc tính ở I1, chúng ta cần một công thức để kết nối chúng. Sử dụng kết hợp INDEX và MATCH:
=INDEX(T_Inventory, MATCH(H2, T_Inventory[Product], 0), MATCH(I1, T_Inventory[#Headers], 0))
Đảm bảo tên cột trong công thức ([Product]) khớp chính xác với tên cột trong bảng của bạn.
Công thức này hoạt động như sau:
- INDEX(T_Inventory…): Tham chiếu toàn bộ bảng dữ liệu.
- MATCH(H2…): Tìm số hàng tương ứng với sản phẩm được chọn.
- MATCH(I1…): Tìm số cột tương ứng với thuộc tính được chọn.
Hãy thử nghiệm: chọn một sản phẩm và thuộc tính khác, ô I2 sẽ cập nhật kết quả. Tuy nhiên, bạn sẽ thấy khi chọn “Cost”, kết quả hiển thị dưới dạng số thường thay vì định dạng tiền tệ.
Điều này xảy ra vì ô I2 phải hiển thị nhiều loại dữ liệu khác nhau từ các cột. Nhưng đừng lo, có cách khắc phục đơn giản.
Khắc phục vấn đề định dạng
Để kết quả trong ô I2 hiển thị đúng định dạng tiền tệ khi chọn “Cost”, chúng ta dùng Định dạng Có điều kiện (Conditional Formatting).
Chọn ô I2, vào tab Home (Trang chủ), chọn Conditional Formatting (Định dạng Có điều kiện) > New Rule (Quy tắc Mới).
Chọn Use a formula to determine which cells to format (Sử dụng công thức để xác định ô cần định dạng). Trong trường công thức, nhập:
=$I$1="Cost"
Nhấp Format (Định dạng), trong tab Number (Số), chọn Accounting (Kế toán) hoặc Currency (Tiền tệ) và chọn ký hiệu tiền phù hợp. Nhấp OK hai lần để đóng hộp thoại.
Bây giờ, khi ô I1 chứa “Cost”, kết quả trong ô I2 sẽ tự động chuyển sang định dạng tiền tệ.
Bạn có thể áp dụng phương pháp tương tự cho các định dạng khác. Ví dụ, với cột ngày tháng, dùng công thức =$I$1="Date" và chọn định dạng Date. Các cột văn bản hoặc số thông thường không cần định dạng đặc biệt.
Kiểm tra cuối cùng: Thêm cột mới
Sức mạnh thực sự của thiết lập này nằm ở khả năng thích ứng với thay đổi. Hãy thêm một cột mới, ví dụ Restocked, vào ô F1 (ngay bên phải bảng). Mở rộng danh sách thả xuống ở ô I1, bạn sẽ thấy tiêu đề cột mới đã xuất hiện ngay lập tức.
Tags: Excel dropdown, Data Validation, Dynamic Named Range
