Công cụ Data Validation (Kiểm tra dữ liệu) của Microsoft Excel cho phép bạn tạo một danh sách thả xuống trong ô 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 sẽ khác nhau tùy thuộc vào việc dữ liệu nguồn có phải là một phần của bảng Excel đã được định dạng hay không. Hãy cùng tìm hiểu chi tiết qua các ví dụ dưới đây để tối ưu việc sử dụng Excel của bạn.
Tạo Danh Sách Thả Xuống Từ Cột Trong Bảng Excel Đã Định Dạng
Giả sử bạn có một bảng Excel đã được định dạng với tên “Scores” chứa thông tin về tên cầu thủ, quốc gia và điểm số của họ. Nhiệm vụ của bạn là trích xuất một số dữ liệu tóm tắt. Cụ thể, tại ô H2, bạn muốn tạo một danh sách thả xuống gồm tất cả các quốc gia được liệt kê trong cột B. Sau đó, tại các ô I2 và J2, bạn sẽ hiển thị tên 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.
Bảng dữ liệu Excel đã định dạng chứa thông tin cầu thủ, quốc gia và điểm số, cùng với các ô bổ sung để trích xuất dữ liệu theo quốc gia.
Để tạo danh sách thả xuống tiện lợi này, bạn cần chọn ô nơi bạn muốn danh sách xuất hiện (trong trường hợp này là ô H2). Trên thanh công cụ Ribbon, vào tab Data (Dữ liệu), sau đó nhấp vào “Data Validation” (Kiểm tra dữ liệu) trong tùy chọn cùng tên.
Chọn ô đích và truy cập công cụ Data Validation trong tab Data của Excel để bắt đầu tạo danh sách.
Tiếp theo, trong hộp thoại Data Validation, tại tab Settings (Thiết lập), chọn “List” (Danh sách) trong trường Allow (Cho phép).
Thiết lập Data Validation để chọn kiểu danh sách thả xuống trong Excel.
Bước tiếp theo là nhập các tùy chọn mà bạn muốn hiển thị trong danh sách thả xuống vào trường Source (Nguồn). Để thực hiện điều này, bạn có thể nhập thủ công, chọn các ô liên quan trên bảng tính của mình hoặc sử dụng một công thức Excel hiệu quả.
Lý tưởng nhất, 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. Ở đây, “Scores” đại diện cho tên bảng và “Nation” đại diện cho tiêu đề cột. Tuy nhiên, không may là cách này sẽ báo lỗi, vì công cụ Data Validation chỉ nhận dạng các tham chiếu ô, công thức và các tên vùng đã được đặt tên làm nguồn dữ liệu, chứ không nhận dạng trực tiếp tiêu đề cột của bảng.
Thử nhập tên bảng và tiêu đề cột trực tiếp vào trường Source của Data Validation dẫn đến lỗi.
May mắn thay, có hai cách để khắc phục hạn chế này, giúp bạn dễ dàng tạo danh sách thả xuống từ dữ liệu trong bảng.
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 chuyển chuột đến tiêu đề cột liên quan (Nation) trong hàng tiêu đề (hàng 1) cho đến khi bạn thấy một mũi tên nhỏ màu đen hướng xuống xuất hiện. Khi mũi tên đó hiển thị, hã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 chữ cái 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 nét đứt.
Chọn phạm vi dữ liệu cụ thể trong một cột của bảng Excel làm nguồn cho điều kiện kiểm tra dữ liệu.
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 nút này để chọn một tùy chọn từ nguồn. Lưu ý thêm rằng chỉ các giá trị duy nhất được hiển thị trong danh sách – nói cách khác, Excel nhận dạng các giá trị trùng lặp và chỉ hiển thị chúng một lần để đảm bảo danh sách được gọn gàng và dễ sử dụng.
Danh sách thả xuống trong một ô Excel hiển thị các quốc gia duy nhất từ dữ liệu nguồn.
Các danh sách thả xuống của Data Validation trong Excel sẽ giữ nguyên thứ tự như 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 cần sắp xếp dữ liệu nguồn theo cột B.
Điểm tuyệt vời 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 dữ liệu Data Validation sẽ tự động điều chỉnh để bao gồm các ô dữ liệu bổ sung đó, đảm bảo tính linh hoạt và tự động cập nhật cho bảng tính của bạn.
Danh sách thả xuống trong Excel tự động mở rộng để bao gồm dữ liệu mới thêm vào bảng.
Giờ đây, bạn có thể sử dụng hàm FILTER (LỌC) trong ô I2 để liệt kê các cầu thủ của quốc gia đã chọn một cách chính xác:
=FILTER(Scores[Player],Scores[Nation]=H2)
Sử dụng hàm FILTER trong Excel để trích xuất danh sách cầu thủ từ một quốc gia cụ thể đã chọn.
Cuối cùng, hãy sử dụng hàm AVERAGEIF (TÍNH TRUNG BÌNH NẾU) trong ô J2 để hiển thị điểm số trung bình của các cầu thủ từ quốc gia này, giúp bạn có được cái nhìn tổng quan về hiệu suất:
=AVERAGEIF(Scores[Nation],H2,Scores[Total])
Ứng dụng hàm AVERAGEIF trong Excel để tính và hiển thị điểm số trung bình của các cầu thủ từ một quốc gia đã được chỉ định.
Phương Pháp 2: Đặt Tên 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. Phương pháp này không chỉ giúp bạn quản lý dữ liệu dễ dàng hơn mà còn làm cho công thức trở nên rõ ràng và dễ hiểu hơ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 này vào hộp tên (Name Box) ở góc trên bên trái của cửa sổ Excel và nhấn Enter. Để giữ 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.
Chọn dữ liệu trong cột Nation của bảng Excel và đặt tên vùng là Nation trong hộp tên.
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 tên vùng dữ liệu đã đặt vào trường Source của Data Validation trong Excel.
Tương tự như phương pháp trước, Excel nhận ra rằng dữ liệu nằm trong một bảng được định dạng, vì vậy nó sẽ tự động mở rộng vùng khi dữ liệu được thêm vào hàng tiếp theo. Điều này giúp duy trì tính chính xác của danh sách thả xuống mà không cần cập nhật thủ công.
Sử dụng các tên vùng trong Excel mang lại nhiều lợi ích đáng kể. Ví dụ, việc đặt tên 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 đã đặt tên bằng cách nhập chúng vào hộp tên hoặc chọn từ các tùy chọn khi nhấp vào mũi tên xuống. Các vùng đã đặt tên cũng có thể được sử dụng linh hoạt trong các công thức phức tạp, làm cho bảng tính của bạn trở nên mạnh mẽ và dễ quản lý hơn.
Bây giờ, tương tự 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, nhưng với cú pháp gọn gàng hơn.
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ậy, đối với hàm FILTER trong ô I2, công thức sẽ 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 Không Định Dạng
Một số loại dữ liệu – như các mảng tràn (spilled arrays) – không thể định dạng dưới dạng bảng Excel. Vì vậy, 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 một tập dữ liệu chưa được đị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ụ Ribbon, hãy chọn “List” trong trường Allow.
Biểu tượng Data Validation được chọn trong Microsoft Excel, và tùy chọn List được chọn trong trường Allow của hộp thoại.
Tiếp theo, trong trường Source, bạn sẽ sử dụng kết hợp các hàm INDIRECT và COUNTA để cho Excel biết nơi tìm các tùy chọn cho danh sách thả xuống. Việc này giúp tạo một nguồn dữ liệu động, linh hoạt với sự thay đổi của dữ liệu.
Trong trường hợp này, việc nhập:
=INDIRECT("B2:B"&COUNTA(B:B))sẽ bao gồm tất cả các giá trị trong cột B từ ô B2 đến ô B22 làm nguồn, tạo ra một danh sách động có thể tự cập nhật khi dữ liệu thay đổi.
Sử dụng kết hợp hàm INDIRECT và COUNTA trong trường Source của hộp thoại Data Validation trong Excel để tạo nguồn dữ liệu động.
Hãy cùng phân tích công thức nguồn này để hiểu cách nó hoạt động một cách chi tiết hơn:
- =INDIRECT(: Hàm này báo cho Excel biết rằng bạn muốn nguồn được xác định bằng một tham chiếu động, có nghĩa là nguồn sẽ tự điều chỉnh.
- “B2:B”: Cụ thể, tham chiếu động bắt đầu từ ô B2 và kết thúc tại một ô khác trong cột B.
- &COUNTA(B:B)): Hàm COUNTA đếm tất cả các ô không trống trong toàn bộ cột B và thêm tổng số đó vào tham chiếu. Trong trường hợp này, có 22 ô trong cột B chứa giá trị, vì vậy điều này biến “B2:B” thành “B2:B22”.
Vì vậy, nếu bạn thêm một hàng dữ liệu mới ở phía dưới, hàm COUNTA sẽ nhận diện điều này và thông báo cho tham chiếu INDIRECT trong quy tắc Data Validation rằng nguồn đã mở rộng thêm một hàng. Điều này đảm bảo danh sách thả xuống của bạn luôn được cập nhật tự động với dữ liệu mới.
Danh sách thả xuống trong ô Excel tự động mở rộng để bao gồm dữ liệu mới thêm vào bảng chưa định dạng.
Việc sử dụng công thức để xác định các giá trị cần đưa vào nguồn Data Validation—thay vì chỉ chọn toàn bộ cột—được coi là phương pháp tốt nhất. Điều này giúp ngăn chặn việc hàng tiêu đề và các hàng trống bị đưa vào danh sách thả xuống, đảm bảo danh sách của bạn luôn sạch sẽ và chính xác.
Bạn có thể kiểm tra lại điều này bằng cách chọn ô chứa danh sách thả xuống và mở lại hộp thoại Data Validation. Khi bạn chọn trường Source, mặc dù công thức không thay đổi, đường nét đứt trên bảng tính sẽ xác nhận rằng nó đã nhận diện các giá trị mới trong hàng được thêm vào.
Đường nét đứt minh họa nguồn Data Validation trong Excel cho thấy đã bao gồm hàng dữ liệu mới thêm vào.
Bây giờ danh sách thả xuống của bạn đã sẵn sàng hoạt động, bạn có thể sử dụng các hàm INDIRECT và COUNTA cùng với hàm FILTER trong ô I2 và hàm AVERAGEIF trong ô J2 để hoàn thành bảng tính. Điều này giúp tự động hóa quá trình trích xuất và phân tích dữ liệu trong các tập dữ liệu không được định dạng, mang lại hiệu quả cao.
Bảng tính Excel minh họa các công thức INDIRECT, COUNTA, FILTER và AVERAGEIF được sử dụng để xử lý dữ liệu động.
Các danh sách thả xuống được thêm thông qua công cụ Data Validation là vô cùng mạnh mẽ và linh hoạt, có thể được sử dụng trong nhiều tình huống khác nhau trong Excel. Chẳng hạn, bạn có thể sử dụng danh sách thả xuống để tạo các biểu đồ động, một cách chắc chắn để gây ấn tượng với đồng nghiệp và bạn bè.
