Danh sách thả xuống phụ thuộc trong Excel: Thiết lập và khắc phục sự cố

Trong hướng dẫn này, tôi sẽ hướng dẫn bạn cách tạo danh sách thả xuống phụ thuộc trong Excel, bắt đầu với thiết lập đơn giản, được hỗ trợ rộng rãi rồi chuyển sang các cách tiếp cận linh hoạt, hiện đại hơn, dễ bảo trì khi dữ liệu của bạn tăng lên.

Nếu bạn mới bắt đầu với Excel, khóa học Giới thiệu về Excel của chúng tôi bao quát các kỹ năng như điều hướng giao diện, hiểu định dạng dữ liệu và làm việc với các hàm cơ bản. Ngoài ra, tôi thấy Bảng công thức Excel có thể tải xuống là tài liệu tham khảo hữu ích vì bao gồm hầu hết các hàm thường dùng trong Excel.

Danh sách thả xuống phụ thuộc trong Excel là gì?

Trong ngữ cảnh Excel, danh sách thả xuống phụ thuộc đơn giản là danh sách thả xuống thứ hai có các tùy chọn phụ thuộc vào giá trị được chọn ở ô đầu tiên.

Ví dụ phổ biến là hệ phân cấp Quốc gia → Thành phố. Nếu bạn chọn Đức, danh sách thả xuống tiếp theo sẽ giới hạn vào các thành phố như Berlin và Munich. Chuyển quốc gia sang Canada sẽ tự động cập nhật danh sách để hiển thị Toronto và Vancouver.

Đây là lúc danh sách thả xuống tiêu chuẩn tỏ ra hạn chế. Tự thân chúng chỉ có thể hiển thị một danh sách giá trị cố định, thường dẫn đến các tùy chọn không liên quan hoặc không hợp lệ. Danh sách thả xuống phụ thuộc giải quyết vấn đề đó bằng cách liên kết các lựa chọn với nhau, giúp xác thực dữ liệu trong Excel chính xác hơn và hữu ích hơn trong các bảng tính thực tế.

Cách tạo danh sách thả xuống phụ thuộc trong Excel (Phương pháp cổ điển)

Tôi sẽ chỉ bạn cách dùng phương pháp cổ điển, là cách tiếp cận được hỗ trợ rộng rãi nhất để tạo danh sách thả xuống phụ thuộc trong Excel. Cách này hoạt động trên hầu hết các phiên bản Excel và dựa vào dữ liệu nguồn có cấu trúc và phạm vi được đặt tên thay vì các tính năng nâng cao.

Bước 1: Tạo danh sách thả xuống chính

Bắt đầu bằng cách tạo danh sách thả xuống đầu tiên theo các bước sau:

• Nhập các danh mục chính của bạn trong một cột. Ví dụ: USA, UK, Australia.
• Chọn ô nơi bạn muốn đặt danh sách thả xuống chính.
• Vào Data → Data Validation.

• Trong hộp Allow, chọn List.
• Trong hộp Source, bôi đen các danh mục chính của bạn. Nhấp OK.

Bước 2: Chuẩn bị các danh sách phụ thuộc

Tiếp theo, bạn sắp xếp dữ liệu sẽ xuất hiện trong danh sách thả xuống thứ hai. Mỗi danh sách phụ thuộc phải được đặt tên và tên đó phải khớp chính xác với tùy chọn tương ứng trong danh sách thả xuống chính.

• Liệt kê các mục con của bạn dưới các tiêu đề khớp chính xác với danh mục chính.
• Bôi đen các mục trong từng nhóm.
• Vào Name Box (góc trên bên trái, cạnh thanh công thức) và nhập tên danh mục. Nhấn Enter.

• Lặp lại cho mọi danh mục.

Bước 3: Tạo danh sách thả xuống phụ thuộc

Bây giờ liên kết danh sách thả xuống thứ hai với danh sách đầu tiên theo các bước sau:

• Chọn ô nơi danh sách thả xuống phụ thuộc sẽ xuất hiện.
• Mở Data Validation và chọn List.
• Đặt nguồn sao cho trỏ động tới phạm vi được đặt tên khớp với lựa chọn đầu tiên.
• Dùng hàm INDIRECT() để trỏ tới ô chứa danh sách thả xuống đầu tiên.

Theo logic này, nếu D2 là “Australia”, hàm INDIRECT() sẽ bảo danh sách thứ hai: “Hãy tìm phạm vi có tên Australia và hiển thị các mục đó.”

Tạo danh sách thả xuống phụ thuộc bằng mảng động (Excel hiện đại)

Nếu bạn dùng Excel 365 hoặc Excel 2021+, mảng động giúp đơn giản hóa danh sách thả xuống phụ thuộc bằng cách loại bỏ nhu cầu có nhiều phạm vi được đặt tên. Thay vì chuẩn bị các danh sách riêng cho từng tùy chọn, Excel có thể tự động tạo danh sách phụ thuộc dựa trên lựa chọn hiện tại.

Ví dụ, giả sử bạn có dữ liệu ở cột A và B trong trang tính. Hãy dùng các bước sau để tạo danh sách thả xuống phụ thuộc động:

• Tạo danh sách thả xuống chính theo các bước ở phần trước.

• Với danh sách thả xuống thứ hai ở ô kế bên, nhập =FILTER(B:B, A:A = D2) để trả về các mục khớp.

Trong trường hợp này, cú pháp của hàm FILTER() là: 

=FILTER(items, categories=main_cell)Được hỗ trợ bởi

Trong đó: 

• items: Cột chứa các giá trị cho danh sách thả xuống phụ thuộc.

• categories: Cột chứa các giá trị cho danh sách thả xuống chính

• main_cell: Ô có mục được chọn trong danh sách thả xuống chính.

Phương pháp này có các ưu điểm sau so với cách dùng phạm vi được đặt tên cổ điển:

• Cập nhật tự động: Khi bạn thêm mục mới vào bảng nguồn, mục đó xuất hiện ngay trong danh sách thả xuống, loại bỏ việc cập nhật thủ công theo từng danh mục.
• Không lỗi tên: Bạn tránh được lỗi đặt tên và sai chính tả giữa tên và lựa chọn.
• Không cần bảo trì "Name Manager": Bạn không cần tạo hàng chục tên riêng cho từng danh mục. Mối phụ thuộc hiển thị trong một công thức thay vì bị ẩn trong Name Manager.

Danh sách thả xuống phụ thuộc với Excel Tables

Việc dùng Excel Tables giúp danh sách thả xuống phụ thuộc đáng tin cậy hơn khi dữ liệu của bạn dự kiến sẽ tăng.

Giả sử chúng ta lưu dữ liệu Quốc gia → Thành phố trong một Excel Table. Lúc này, khi bạn thêm một thành phố mới dưới một quốc gia hiện có, bảng sẽ tự động mở rộng. Bất kỳ danh sách thả xuống phụ thuộc nào được xây dựng dựa trên bảng đó tiếp tục hoạt động mà không cần cập nhật phạm vi nguồn.

Tuy nhiên, bạn nên lưu ý các vấn đề sau khi kết hợp Excel Tables với xác thực dữ liệu:

• Data Validation không phải lúc nào cũng chấp nhận tham chiếu bảng trực tiếp. Luôn trỏ Data Validation tới một phạm vi tràn trợ giúp được tạo bởi FILTER().
• Các hàng trống trong bảng có thể gây ra giá trị trống trong danh sách thả xuống.
• Phạm vi tràn bị chặn sẽ ngăn danh sách cập nhật. Nếu các ô bên dưới công thức trợ giúp của bạn không trống, danh sách được lọc sẽ không thể mở rộng, và danh sách thả xuống phụ thuộc có thể xuất hiện không đầy đủ hoặc bị lỗi.

Các vấn đề thường gặp với danh sách thả xuống phụ thuộc

Dưới đây là các vấn đề thường gặp tôi đã gặp khi tạo danh sách thả xuống phụ thuộc trong Excel và cách bạn có thể tránh:

• Danh sách thả xuống hiển thị giá trị trống: Vấn đề này thường xảy ra khi phạm vi nguồn chứa các ô trống hoặc khi kết quả lọc không trả về khớp nào cho lựa chọn hiện tại. Luôn đảm bảo dữ liệu của bạn không có khoảng trống ngoài ý muốn.
• Danh sách phụ thuộc không cập nhật: Nếu danh sách thả xuống thứ hai không thay đổi sau khi cập nhật danh sách đầu tiên, có thể Excel không tính toán lại như mong đợi hoặc liên kết phụ thuộc đã bị đứt. Để khắc phục, hãy kiểm tra rằng danh sách phụ thuộc đang trỏ tới danh sách thả xuống chính.
• Sai lệch chính tả: Vì danh sách thả xuống phụ thuộc dựa vào khớp chính xác, chỉ một khác biệt nhỏ trong chính tả giữa lựa chọn chính và dữ liệu nguồn sẽ làm danh sách không hoạt động. Để khắc phục, hãy ưu tiên dùng danh sách thả xuống động.
• Khoảng trắng thừa trong dữ liệu nguồn: Khoảng trắng đầu/cuối làm hai giá trị trông giống nhau với người dùng nhưng khác nhau với Excel, khiến bộ lọc và đối chiếu tên bị lỗi. Hãy dùng TRIM() để loại bỏ khoảng trắng ẩn trong dữ liệu trước khi tạo danh sách thả xuống phụ thuộc.
• Data Validation không làm mới như mong đợi: Trong một số trường hợp, Excel lưu đệm các quy tắc xác thực cũ. Để khắc phục, hãy chọn lại ô, áp dụng lại Data Validation hoặc tính toán lại sổ làm việc.

Thực hành tốt nhất cho danh sách thả xuống phụ thuộc trong Excel

Để đảm bảo danh sách thả xuống phụ thuộc của bạn luôn đáng tin cậy, có thể mở rộng và dễ bảo trì, tôi khuyến nghị danh sách kiểm tra dưới đây:

• Giữ nhãn nhất quán: Danh sách thả xuống phụ thuộc dựa vào khớp chính xác. Chuẩn hóa cách đặt tên, kiểu chữ và khoảng trắng trên dữ liệu nguồn để tránh lỗi âm thầm.

• Tránh cố định phạm vi: Đừng trỏ Data Validation của bạn tới các ô cố định như $A$1:$A$10 vì phạm vi cố định sẽ hỏng ngay khi dữ liệu tăng. Bất cứ khi nào có thể, hãy dùng mảng động hoặc Excel Tables để các giá trị mới được tự động nhận diện.

• Ưu tiên dùng bảng hoặc công thức động khi có thể: Bảng và mảng động (FILTER()) giảm bảo trì thủ công và giúp sổ làm việc của bạn dễ cập nhật theo thời gian, đặc biệt với tệp chia sẻ.

• Kiểm thử các trường hợp biên sớm: Kiểm tra điều gì xảy ra khi danh sách thả xuống chính để trống, thay đổi sau khi đã chọn, hoặc trỏ tới một danh mục không có giá trị khớp. Cách tiếp cận này đảm bảo dữ liệu của bạn hoạt động như mong đợi, không có bất ngờ.

Khi nào không nên dùng danh sách thả xuống phụ thuộc

Mặc dù danh sách thả xuống phụ thuộc rất hữu ích, chúng không phải lúc nào cũng là công cụ phù hợp. Dưới đây là vài trường hợp bạn nên tránh dùng:

• Khi phụ thuộc trở nên quá phức tạp: Nếu lựa chọn phụ thuộc vào nhiều điều kiện hoặc nhiều tầng, logic có thể nhanh chóng trở nên khó quản lý và dễ lỗi trong Excel.
• Khi biểu mẫu hoặc cơ sở dữ liệu là công cụ tốt hơn: Với nhập liệu có cấu trúc, nhiều người dùng hoặc quy trình cần xác thực chặt, các biểu mẫu chuyên dụng hoặc hệ thống có cơ sở dữ liệu sẽ cung cấp khả năng kiểm soát và độ tin cậy tốt hơn.
• Cân nhắc hiệu năng với danh sách rất lớn: Tập dữ liệu lớn có thể làm chậm việc tính toán lại và khiến danh sách thả xuống phản hồi kém, đặc biệt khi dùng công thức động trên hàng nghìn dòng.

Kết luận

Danh sách thả xuống phụ thuộc giúp bạn giữ cho dữ liệu chính xác và hữu dụng bằng cách áp đặt mối quan hệ logic giữa các lựa chọn thay vì dựa vào kiểm tra thủ công. Dù bạn ưa phương pháp dùng phạm vi đặt tên cổ điển hay mảng động hiện đại, Excel cung cấp nhiều cách để xây dựng chúng, vì vậy bạn có thể chọn cách phù hợp với phiên bản và quy trình làm việc của mình. Tôi khuyên bạn bắt đầu với phương pháp đơn giản nhất, rồi chuyển sang các giải pháp động dựa trên bảng khi dữ liệu và mô hình trở nên phức tạp hơn.

Giờ bạn đã sẵn sàng khám phá sâu hơn, tôi khuyến nghị tham gia khóa học Phân tích dữ liệu trong Excel để nâng cao kỹ năng Excel của bạn. Khóa học này sẽ giúp bạn thành thạo phân tích nâng cao và thúc đẩy sự nghiệp. Tôi cũng khuyến nghị khóa Power Query trung cấp trong Excel để học về chuyển đổi dữ liệu và sử dụng ngôn ngữ M để tạo hàm động.