Microsoft Excel là công cụ bảng tính mạnh mẽ, không chỉ hỗ trợ các phép tính phức tạp mà còn cung cấp nhiều tính năng giúp quản lý và hiển thị dữ liệu một cách hiệu quả. Trong đó, công cụ Data Validation (Xác thực dữ liệu) cho phép bạn tạo danh sách thả xuống (drop-down list) trong một ô dựa trên dữ liệu hiện có trong một cột. Tuy nhiên, cách thức hoạt động của tính năng này có sự khác biệt đáng kể tùy thuộc vào việc dữ liệu nguồn của bạn có phải là một phần của bảng Excel đã được định dạng hay không.
Bài viết này từ trithuccongnghe.net sẽ đi sâu vào hướng dẫn chi tiết các phương pháp để tạo danh sách thả xuống động trong Excel, đảm bảo rằng danh sách của bạn sẽ tự động cập nhật khi dữ liệu nguồn thay đổi, giúp tăng cường tính linh hoạt và chuyên nghiệp cho các bảng tính của bạn. Chúng ta sẽ cùng tìm hiểu các ví dụ cụ thể để bạn có thể áp dụng một cách dễ dàng và hiệu quả.
Tạo Danh Sách Thả Xuống Từ Cột Trong Bảng Excel Đã Định Dạng
Hãy hình dung bạn có một bảng Excel đã được định dạng, được đặt tên là “Scores”, chứa thông tin về tên cầu thủ, quốc gia và điểm số. Mục tiêu của bạn là trích xuất một số dữ liệu tóm tắt. Cụ thể, trong ô H2, bạn muốn tạo một danh sách thả xuống của tất cả các quốc gia được liệt kê trong cột B. Sau đó, trong các ô I2 và J2, sẽ hiển thị tên các cầu thủ và điểm số trung bình tương ứng với quốc gia được chọn từ danh sách thả xuống.
Bảng Excel đã định dạng với dữ liệu cầu thủ, quốc gia và điểm số
Để tạo danh sách thả xuống, bạn hãy chọn ô mà bạn muốn đặt danh sách đó (trong ví dụ này là ô H2). Sau đó, trên thanh công cụ ribbon, chọn tab Data, và trong nhóm Data Tools, nhấp vào nút Data Validation.
Chọn ô đích và nút Data Validation trên thanh công cụ Excel
Trong hộp thoại Data Validation xuất hiện, tại tab Settings, trong trường Allow, chọn tùy chọn List.
Chọn tùy chọn "List" trong hộp thoại Data Validation của Excel
Bước tiếp theo là nhập các tùy chọn mà bạn muốn xuất hiện trong danh sách thả xuống vào trường Source. Để làm điều này, bạn có thể nhập thủ công các tùy chọn, chọn các ô liên quan trực tiếp trên bảng tính của mình, hoặc sử dụng một công thức.
Thông thường, bạn có thể nghĩ đến việc nhập:
=Scores[Nation]vào trường Source, giống như khi bạn tham chiếu cột đó trong một công thức thông thường, trong đó “Scores” là tên bảng và “Nation” là tiêu đề cột. Tuy nhiên, đáng tiếc là cách này sẽ trả về một thông báo lỗi, bởi vì công cụ Data Validation chỉ nhận dạng các tham chiếu ô, công thức và các vùng được đặt tên (Named Ranges) làm nguồn dữ liệu, chứ không phải các tiêu đề cột của bảng.
Lỗi khi tham chiếu trực tiếp tên bảng và tiêu đề cột trong Data Validation Source
May mắn thay, có hai cách để khắc phục vấn đề này.
Phương Pháp 1: Tham Chiếu Trực Tiếp Các Ô Trong Bảng
Đầu tiên, hãy kích hoạt trường Source trong hộp thoại Data Validation để con trỏ nhấp nháy. Sau đó, di chuột qua tiêu đề cột liên quan ở hàng đầu tiên (trong ví dụ là “Nation” ở cột B) cho đến khi bạn thấy một mũi tên đen nhỏ hướng xuống. Khi thấy, nhấp một lần để chọn tất cả các ô dữ liệu trong cột đó.
Hãy cẩn thận không chọn toàn bộ cột bằng cách nhấp vào mũi tên xuất hiện khi bạn di chuột qua ký tự tham chiếu cột (trong trường hợp này là “B”), vì điều này sẽ chọn toàn bộ cột, bao gồm cả tiêu đề ở hàng 1 và các ô bên dưới bảng của bạn. Bạn đã chọn đúng phạm vi nếu chỉ các ô trong cột nằm trong bảng được bao quanh bởi một đường chấm chấm.
Chọn một cột dữ liệu trong bảng Excel làm nguồn cho Data Validation
Bây giờ, sau khi nhấp OK, khi bạn chọn ô H2, một nút thả xuống sẽ xuất hiện. Bạn có thể nhấp vào đó để chọn một tùy chọn từ nguồn. Hãy lưu ý rằng chỉ các giá trị duy nhất được hiển thị trong danh sách — nghĩa là, Excel nhận diện các giá trị trùng lặp và chỉ hiển thị chúng một lần.
Danh sách thả xuống hiển thị các quốc gia duy nhất từ nguồn dữ liệu trong Excel
Các danh sách thả xuống của Data Validation trong Excel sẽ giữ nguyên thứ tự của dữ liệu nguồn. Trong ví dụ này, để sắp xếp các quốc gia theo thứ tự bảng chữ cái, bạn sẽ cần sắp xếp dữ liệu nguồn theo cột B.
Điểm ưu việt của việc sử dụng phương pháp này là nếu bạn thêm các hàng mới vào bảng Excel đã định dạng, nguồn Data Validation sẽ tự động điều chỉnh để bao gồm các ô dữ liệu bổ sung đó. Điều này giúp danh sách thả xuống của bạn luôn được cập nhật mà không cần thao tác thủ công.
Danh sách thả xuống tự động mở rộng khi dữ liệu mới được thêm vào bảng Excel đã định dạng
Bây giờ, bạn có thể sử dụng hàm FILTER trong ô I2 để liệt kê các cầu thủ của quốc gia đã chọn:
=FILTER(Scores[Player],Scores[Nation]=H2)
Sử dụng hàm FILTER để trích xuất danh sách cầu thủ theo quốc gia đã chọn trong Excel
Cuối cùng, sử dụng hàm AVERAGEIF trong ô J2 để hiển thị điểm số trung bình của các cầu thủ từ quốc gia này:
=AVERAGEIF(Scores[Nation],H2,Scores[Total])
Tính điểm trung bình cầu thủ theo quốc gia bằng hàm AVERAGEIF trong Excel
Phương Pháp 2: Đặt Tên Cho Vùng Dữ Liệu (Named Range)
Cách thứ hai để tạo danh sách thả xuống từ một cột trong bảng đã định dạng là đặt tên cho vùng dữ liệu nguồn.
Trước khi mở hộp thoại Data Validation, hãy chọn tất cả các ô trong bảng chứa các giá trị bạn muốn đưa vào danh sách thả xuống. Sau đó, nhập một tên cho vùng dữ chọn vào ô tên (Name Box) ở góc trên bên trái cửa sổ Excel và nhấn Enter. Để giữ cho mọi thứ đơn giản (và dễ nhớ!), tên bạn nhập cho vùng đã chọn nên giống với tiêu đề cột.
Đặt tên cho vùng dữ liệu cột "Nation" trong bảng Excel
Sau đó, trong trường Source của hộp thoại Data Validation, nhập dấu bằng (=), theo sau là tên bạn vừa gán cho vùng, và nhấn Enter. Trong ví dụ này, bạn cần nhập:
=Nation
Nhập Named Range vào trường Source của Data Validation trong Excel
Tương tự như phương pháp trước, Excel nhận diện rằng dữ liệu nằm trong một bảng đã định dạng, vì vậy nó sẽ tự động mở rộng phạm vi nếu dữ liệu được thêm vào hàng tiếp theo.
Sử dụng các vùng được đặt tên (Named Ranges) trong Excel mang lại nhiều lợi ích. Ví dụ, việc đặt tên các vùng giúp bảng tính của bạn dễ tiếp cận hơn đối với những người sử dụng trình đọc màn hình. Ngoài ra, bạn có thể nhanh chóng điều hướng đến các vùng được đặt tên bằng cách nhập tên vào ô tên hoặc chọn chúng từ các tùy chọn khi bạn nhấp vào mũi tên thả xuống, và các vùng được đặt tên cũng có thể được sử dụng trong các công thức.
Bây giờ, giống như phương pháp 1, bạn có thể sử dụng các hàm FILTER và AVERAGEIF để hoàn thành việc trích xuất dữ liệu. Tuy nhiên, lần này, các công thức có thể đơn giản hơn, vì bạn có thể tham chiếu vùng bạn đã đặt tên là “Nation” mà không cần tham chiếu bảng chứa vùng đó.
Vì vậy, đối với hàm FILTER trong ô I2, công thức là:
=FILTER(Scores[Player],Nation=H2)và đối với hàm AVERAGEIF trong ô J2, công thức là:
=AVERAGEIF(Nation,H2,Scores[Total])Tạo Danh Sách Thả Xuống Từ Cột Trong Tập Dữ Liệu Chưa Định Dạng
Một số loại dữ liệu—như mảng tràn (spilled arrays)—không thể định dạng thành bảng Excel. Do đó, có những lúc bạn cần tìm cách tạo danh sách thả xuống từ một cột trong tập dữ liệu chưa định dạng.
Sau khi chọn ô sẽ chứa danh sách thả xuống và nhấp vào “Data Validation” trong tab Data trên thanh công cụ, hãy chọn “List” trong trường Allow.
Chọn Data Validation và tùy chọn "List" để tạo danh sách thả xuống từ dữ liệu chưa định dạng
Tiếp theo, trong trường Source