VietNetwork.Vn

Phương pháp bạn sử dụng phụ thuộc vào cách dữ liệu của bạn được định dạng.

Công cụ Data Validation của Microsoft Excel cho phép bạn thêm danh sách thả xuống vào 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 công cụ này phụ 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 xem một số ví dụ.


1. Sử dụng một cột trong bảng Excel được định dạng

Hãy tưởng tượng bạn có bảng Excel được định dạng này có tên là "Điểm" chứa tên người chơi, quốc gia và điểm số, và bạn cần trích xuất một số dữ liệu tóm tắt. Cụ thể, trong ô H2, bạn muốn tạo danh sách thả xuống của tất cả các quốc gia được liệt kê trong cột B và trong các ô I2 và J2, hiển thị tên người chơi và điểm trung bình tương ứng theo quốc gia đã chọn.


Để tạo danh sách thả xuống, hãy chọn ô mà bạn muốn (trong trường hợp này là ô I2) và trong tab Dữ liệu trên ruy-băng, nhấp vào "Xác thực dữ liệu" trong tùy chọn thả xuống có cùng tên.


Sau đó, trong trường Cho phép của tab Cài đặt, chọn "Danh sách".
Tùy chọn Danh sách trong trường Cho phép của hộp thoại Xác thực dữ liệu của Excel được chọn.


Bước tiếp theo bao gồm nhập các tùy chọn bạn muốn xuất hiện trong danh sách thả xuống vào trường Nguồn. Để thực hiện việc này, bạn có thể nhập các tùy chọn theo cách thủ công, chọn các ô có liên quan trên bảng tính hoặc sử dụng công thức.

Lý tưởng nhất là bạn có thể nhập:

=Scores[Nation]
vào trường Nguồn, chính xác như khi bạn tham chiếu cột đó trong công thức, trong đó "Điểm" biểu thị tên bảng và "Quốc gia" biểu thị tiêu đề cột. Tuy nhiên, thật không may, điều này trả về thông báo lỗi vì công cụ xác thực dữ liệu chỉ nhận dạng các tham chiếu ô, công thức và phạm vi được đặt tên là nguồn dữ liệu, không phải tiêu đề cột bảng.


May mắn thay, có hai cách để giải quyết vấn đề này.

Phương pháp 1: Tham chiếu các ô trong Bảng

Đầu tiên, kích hoạt trường "Nguồn" trong hộp thoại Xác thực dữ liệu sao cho con trỏ nhấp nháy và di chuột qua tiêu đề cột có liên quan ở hàng 1 cho đến khi bạn thấy một mũi tên nhỏ màu đen hướng xuống. Khi bạn thấy, 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ì thao tác này sẽ chọn toàn bộ cột, bao gồm 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 trong bảng được bao quanh bởi đường chấm chấm.


Bây giờ, sau khi nhấp vào "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 tùy chọn từ nguồn. Lưu ý, chỉ những giá trị duy nhất mới đượ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.


Danh sách thả xuống xác thực dữ liệu trong Excel áp dụng cùng thứ tự như 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 tuyệt vời khi sử dụng phương pháp này là nếu bạn thêm nhiều hàng vào bảng Excel đã định dạng, nguồn xác thực dữ liệu sẽ tự động điều chỉnh để bao gồm ô hoặc các ô bổ sung.


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)

Cuối cùng, sử dụng hàm AVERAGEIF trong ô J2 để hiển thị điểm trung bình của người chơi từ quốc gia này:

=AVERAGEIF(Scores[Nation],H2,Scores[Total])

Phương pháp 2: Đặt tên cho phạm vi

Cách thứ hai để tạo danh sách thả xuống từ một cột trong bảng đã định dạng liên quan đến việc đặt tên cho phạm vi nguồn.

Trước khi khởi chạy hộp thoại Xác thực dữ liệu, hãy chọn tất cả các ô trong bảng có chứa các giá trị bạn muốn đưa vào danh sách thả xuống, nhập tên cho phạm vi vào hộp tên ở góc trên cùng bên trái của cửa sổ Excel và nhấn Enter.

Để đơn giản (và dễ nhớ!), tên bạn nhập cho phạm vi đã chọn phải giống với tiêu đề cột.


Sau đó, trong trường Nguồn của hộp thoại Xác thực dữ liệu, nhập dấu bằng (=), theo sau là tên bạn vừa gán cho phạm vi và nhấn Enter. Trong ví dụ này, bạn cần nhập:

=Nation

Tương tự như phương pháp trước, Excel nhận dạng dữ liệu nằm trong bảng được định dạng, do đó 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 phạm vi được đặt tên trong Excel có nhiều lợi ích. Ví dụ, việc đặt tên phạm vi giúp sổ làm việc của bạn dễ truy cập 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 phạm vi được đặt tên bằng cách nhập chúng vào hộp 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 xuống và phạm vi đượ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 tất 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 đến phạm vi bạn đặt tên là "Quốc gia" mà không cần tham chiếu đến bảng chứa phạm vi đó.

Vì vậy, đối với FILTER trong ô I2, thì:

=FILTER(Scores[Player],Nation=H2)
và đối với AVERAGEIF trong ô J2, thì:

=AVERAGEIF(Nation,H2,Scores[Total])
2. Sử dụng một cột trong một tập dữ liệu chưa định dạng

Một số loại dữ liệu nhất định, chẳng hạn như mảng bị tràn, không thể định dạng dưới dạng bảng Excel, do đó, đôi khi bạn có thể 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 ô chứa danh sách thả xuống và nhấp vào "Xác thực dữ liệu" trong tab Dữ liệu trên ruy-băng, hãy chọn "Danh sách" trong trường Cho phép.


Tiếp theo, trong trường Nguồn, hãy 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.

Trong trường hợp này, hãy nhập:

=INDIRECT("B2:B"&COUNTA(B:B))
bao gồm tất cả các giá trị trong cột B từ ô B2 đến ô B22 làm nguồn.


Chúng ta hãy phân tích công thức nguồn này để xem nó hoạt động như thế nào.

    =INDIRECT(: Điều này cho Excel biết rằng bạn muốn nguồn được xác định bằng cách sử dụng tham chiếu động.
    "B2:B": Cụ thể hơn, tham chiếu động bắt đầu tại ô B2 và kết thúc tại một ô khác trong cột B.
    &COUNTA(B:B)): Đếm tất cả các ô trong cột B không trống và thêm tổng vào tham chiếu. Trong trường hợp này, 22 ô trong cột B chứa giá trị, do đó, hàm này sẽ chuyển B2:B thành B2:B22.

Vì vậy, nếu bạn thêm một hàng dữ liệu khác ở phía dưới, hàm COUNTA sẽ chọn hàng này và thông báo cho tham chiếu INDIRECT trong quy tắc xác thực dữ liệu rằng nguồn đã mở rộng xuống phía dưới thêm một hàng.


Việc sử dụng công thức để xác định giá trị nào sẽ đưa vào nguồn xác thực dữ liệu—thay vì chỉ chọn toàn bộ cột—được coi là phương pháp hay nhất vì nó ngăn không cho hàng tiêu đề và hàng trống được đưa vào danh sách thả xuống.

Bạn có thể kiểm tra lại điều này bằng cách chọn ô lưu trữ danh sách thả xuống và mở lại hộp thoại Xác thực dữ liệu. Khi bạn chọn trường Nguồn, mặc dù công thức không thay đổi, đường chấm trên bảng tính xác nhận rằng nó chọn các giá trị mới trong hàng đã thêm.


Bây giờ danh sách thả xuống đã sẵn sàng, hãy 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 thiện bảng tính.


Danh sách thả xuống được thêm thông qua công cụ Xác thực dữ liệu cực kỳ mạnh mẽ và linh hoạt, có thể được sử dụng trong nhiều tình huống trong Excel. Ví dụ, bạn có thể sử dụng danh sách thả xuống để làm cho biểu đồ thông thường trở nên động —một cách chắc chắn để gây ấn tượng với bạn bè và đồng nghiệp của bạn.