Computer >> Hướng Dẫn Máy Tính >  >> Phần Mềm >> Office

Tạo danh sách thả xuống phụ thuộc nhiều từ trong Excel – Hướng dẫn từng bước

Khi làm việc trong Microsoft Excel, đôi khi chúng ta cần tạo biểu mẫu nhập dữ liệu hoặc Excel bảng điều khiển. Khi phát triển biểu mẫu nhập dữ liệu, danh sách thả xuống là tính năng rất hữu ích trong Excel. Nó hiển thị danh sách các mục trong một ô như menu thả xuống, từ đó người dùng có thể chọn. Khi bạn có danh sách cần nhập vào một tập hợp ô thường xuyên, điều này có thể hữu ích. Trong bài viết này, chúng tôi sẽ trình bày các bước tạo danh sách thả xuống phụ thuộc Excel với nhiều từ. Cũng sẽ chỉ cho bạn cách xóa hoặc đặt lại các danh sách đó.

Bạn có thể tải xuống sổ làm việc và thực hành cùng chúng.

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

Danh sách thả xuống trong Excel là chức năng xác thực dữ liệu cho phép chọn từ danh sách các tùy chọn. Và, khi danh sách các mục thay đổi tùy thuộc vào giá trị trong ô khác, loại danh sách này được gọi là danh sách thả xuống phụ thuộc.

Các bước tạo danh sách thả xuống phụ thuộc với nhiều từ trong Excel

Đôi khi, chúng ta có thể muốn sử dụng nhiều hơn một danh sách thả xuống trong Excel để các mục có sẵn trong danh sách thả xuống thứ hai dựa trên lựa chọn của danh sách thả xuống đầu tiên. Và, đó chính là các danh sách thả xuống phụ thuộc.

Để tạo danh sách thả xuống phụ thuộc với nhiều từ trong Excel, chúng ta sẽ dùng danh sách dữ liệu sau. Chúng ta có 3 danh sách dữ liệu trong trang List; danh sách đầu tiên là Sản phẩm có hai sản phẩm - trái cây và rau củ, danh sách thứ hai là Mục trái cây có sáu loại trái cây khác nhau và Mục rau củ có năm loại rau. Bây giờ, hãy xem hướng dẫn từng bước tạo danh sách thả xuống phụ thuộc với nhiều từ trong Excel.

Tạo danh sách thả xuống phụ thuộc nhiều từ trong Excel – Hướng dẫn từng bước

Bước 1: Tạo hai trang trong sổ làm việc Excel cho danh sách thả xuống phụ thuộc với nhiều từ

Ví dụ, chúng ta đã tạo hai trang để làm việc hiệu quả hơn. Một trang cho Danh sách dữ liệu trang kia cho Nhập dữ liệu. Để tạo trang trong sổ làm việc, chỉ cần nhấp vào dấu cộng '+'.

Tạo danh sách thả xuống phụ thuộc nhiều từ trong Excel – Hướng dẫn từng bước

Và, xong! Các trang đã sẵn sàng. Bây giờ, đưa dữ liệu vào.

Tạo danh sách thả xuống phụ thuộc nhiều từ trong Excel – Hướng dẫn từng bước

Bước 2: Tạo danh sách cho menu thả xuống phụ thuộc với nhiều từ trong Excel

Trong trang Danh sách dữ liệu, chúng ta có ba cột dữ liệu. Ở cột B, ta thấy danh sách sản phẩm, và chỉ có hai danh sách sản phẩm. Một là Trái cây ở cột D, cái kia là Rau củ ở cột F. Bây giờ, để tạo danh sách cho menu thả xuống, làm theo các bước con dưới đây.

  • Trước tiên, chọn bất kỳ ô nào của cột bạn muốn tạo danh sách cho menu thả xuống.
  • Thứ hai, đi đến tab Trang chủ từ băng công cụ.
  • Thứ ba, nhấp vào Định dạng thành Bảng dưới nhóm Kiểu.

Tạo danh sách thả xuống phụ thuộc nhiều từ trong Excel – Hướng dẫn từng bước

  • Điều này sẽ mở hộp thoại Tạo Bảng.
  • Tiếp theo, chọn phạm vi $B$4:$B$6 và đánh dấu ô Bảng của tôi có tiêu đề.
  • Sau đó, nhấp nút OK.

Tạo danh sách thả xuống phụ thuộc nhiều từ trong Excel – Hướng dẫn từng bước

  • Tương tự, làm như vậy để tạo danh sách Trái cây và Rau củ. Cả ba danh sách được cấu trúc thành Bảng Excel trên trang Danh sách dữ liệu.

Tạo danh sách thả xuống phụ thuộc nhiều từ trong Excel – Hướng dẫn từng bước

  • Bây giờ, chúng ta cần tạo phạm vi đặt tên cho mỗi danh sách. Để làm vậy, các mục trong Danh sách Sản phẩm phải được chọn nhưng không phải tiêu đề bảng.
  • Tiếp theo, ở bên trái thanh công thức, nhấp vào Trường tên.
  • Sau đó, nhập tên bạn muốn đặt cho phạm vi. Vì vậy, chúng ta nhập Sản phẩm.
  • Cuối cùng, nhấn Enter.

Tạo danh sách thả xuống phụ thuộc nhiều từ trong Excel – Hướng dẫn từng bước

  • Làm theo các bước đó và làm tương tự cho Trái cây và Rau củ.

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

Bây giờ, chúng ta cần thêm danh sách thả xuống chính vào trang Nhập dữ liệu. Để làm điều này, làm theo quy trình con dưới đây.

  • Trước hết, chọn phạm vi dữ liệu B4:C6.
  • Thứ hai, đi đến tab Trang chủ trên băng công cụ.
  • Sau đó, dưới danh mục Kiểu, nhấp vào menu thả xuống Định dạng thành Bảng và chọn bất kỳ bảng định dạng nào.

Tạo danh sách thả xuống phụ thuộc nhiều từ trong Excel – Hướng dẫn từng bước

  • Điều này sẽ đưa bạn đến cửa sổ bật lên Tạo Bảng.
  • Bây giờ, chọn phạm vi trong hộp Dữ liệu cho bảng của bạn ở đâu?. Vì vậy, chúng ta chọn $B$4:$C$6.
  • Đánh dấu ô, bên cạnh Bảng của tôi có tiêu đề.
  • Nhấp OK.

Tạo danh sách thả xuống phụ thuộc nhiều từ trong Excel – Hướng dẫn từng bước

Bước 4: Thêm các danh sách thả xuống phụ thuộc lẫn nhau

Bây giờ, chúng ta cần tạo danh sách thả xuống. Hãy trình bày các bước con dưới đây.

  • Ban đầu, chọn phạm vi ô B4:B6.
  • Đi đến tab Dữ liệu từ băng công cụ.
  • Sau đó, nhấp vào Xác thực dữ liệu từ danh sách thả xuống Xác thực dữ liệu dưới danh mục Công cụ dữ liệu.

Tạo danh sách thả xuống phụ thuộc nhiều từ trong Excel – Hướng dẫn từng bước

  • Điều này sẽ mở hộp thoại Xác thực dữ liệu.
  • Tiếp theo, trong menu Cài đặt, có danh sách thả xuống, từ đó chọn Danh sách.

Tạo danh sách thả xuống phụ thuộc nhiều từ trong Excel – Hướng dẫn từng bước

  • Trong hộp Nguồn, nhập dấu bằng ('=') rồi tên danh sách là Sản phẩm. Chúng ta đặt tên này ở trang Danh sách dữ liệu.
  • Cuối cùng, nhấp OK.

Tạo danh sách thả xuống phụ thuộc nhiều từ trong Excel – Hướng dẫn từng bước

  • Bây giờ, cho danh sách thả xuống phụ thuộc, chọn ô C5 và đi đến tab Dữ liệu trên băng công cụ.
  • Tiếp theo, nhấp vào Xác thực dữ liệu từ menu thả xuống Xác thực dữ liệu.

Tạo danh sách thả xuống phụ thuộc nhiều từ trong Excel – Hướng dẫn từng bước

  • Điều này sẽ mở hộp thoại Xác thực dữ liệu.
  • Tương tự như trước, đi đến Cài đặt và chọn Danh sách từ thả xuống dưới phần Cho phép.
  • Ở Nguồn, nhập công thức.
=INDIRECT(B5)
  • Sau đó, nhấp OK.

Tạo danh sách thả xuống phụ thuộc nhiều từ trong Excel – Hướng dẫn từng bước

Ở đây, hàm INDIRECT sẽ làm cho ô chọn mới C5, phụ thuộc vào ô B5.

  • Nếu ô B5 trống, thông báo dưới đây sẽ xuất hiện. Để tiếp tục, chọn Có.

Tạo danh sách thả xuống phụ thuộc nhiều từ trong Excel – Hướng dẫn từng bước

  • Làm tương tự cho ô C6.

Bước 5: Thử nghiệm menu danh sách thả xuống phụ thuộc trong Excel

Bây giờ, chúng ta thấy có danh sách thả xuống trong mỗi ô. Nếu chọn ô C5 và nhấp mũi tên, nó sẽ hiển thị danh sách trái cây vì ở ô B5, sản phẩm Trái cây được chọn.

Tạo danh sách thả xuống phụ thuộc nhiều từ trong Excel – Hướng dẫn từng bước

  • Nhấp vào mũi tên ô C6 và bạn sẽ thấy danh sách rau củ đó, vì ở ô B6, sản phẩm chọn là Rau củ.

Tạo danh sách thả xuống phụ thuộc nhiều từ trong Excel – Hướng dẫn từng bước

Đọc thêm: Cách chọn từ danh sách thả xuống và kéo dữ liệu từ trang tính khác trong Excel

Xóa hoặc đặt lại dữ liệu danh sách thả xuống phụ thuộc trong Excel

Khi chọn một danh sách và sau đó thay đổi danh sách thả xuống cha, danh sách thả xuống phụ thuộc không thay đổi, dẫn đến nhập sai. Để xóa hoặc đặt lại dữ liệu danh sách thả xuống phụ thuộc, chúng ta sẽ dùng VBA Excel và Định dạng có điều kiện.

1. Dùng VBA Excel để xóa danh sách thả xuống phụ thuộc với nhiều từ

VBA Excel cho phép bạn áp đặt điều hướng phức tạp hơn, thực thi nâng cao và nhiều ràng buộc hơn. Để dùng VBA xóa nhập dữ liệu thả xuống phụ thuộc, làm theo các bước dưới đây.

  • Trước tiên, đi đến tab Nhà phát triển từ băng công cụ.
  • Thứ hai, nhấp Visual Basic hoặc nhấn Alt + 11, để mở Trình soạn thảo Visual Basic.

Tạo danh sách thả xuống phụ thuộc nhiều từ trong Excel – Hướng dẫn từng bước

  • Thay vì làm vậy, bạn có thể vào trang tính và nhấp Xem mã, điều này cũng mở Trình soạn thảo Visual Basic.

Tạo danh sách thả xuống phụ thuộc nhiều từ trong Excel – Hướng dẫn từng bước

  • Bây giờ, sao chép mã và dán nó.

Mã VBA:

Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Target.Column = 4 Then If Target.Validation.Type = 3 Then Application.EnableEvents = False Target.Offset(0, 1).ClearContents End If End If exitHandler: Application.EnableEvents = True Exit Sub End Sub
  • Nhấn Ctrl + S để lưu mã trong trang tính của bạn.

Tạo danh sách thả xuống phụ thuộc nhiều từ trong Excel – Hướng dẫn từng bước

Lưu ý: Đừng thay đổi mã, chỉ sao chép và dán. Nếu bạn thay đổi, mã có thể không hoạt động đúng.

  • Bạn thấy Sản phẩm giờ là Trái cây và Mục là Chuối.
  • Bây giờ, chúng ta muốn đổi Sản phẩm thành Rau củ.

Tạo danh sách thả xuống phụ thuộc nhiều từ trong Excel – Hướng dẫn từng bước

  • Khi chúng ta chọn sản phẩm, mục đó sẽ tự động bị xóa.

Tạo danh sách thả xuống phụ thuộc nhiều từ trong Excel – Hướng dẫn từng bước

Nội dung liên quan: Cách chỉnh sửa danh sách thả xuống trong Excel (4 cách tiếp cận cơ bản)

Bài đọc tương tự:

  • Cách Trích xuất dữ liệu dựa trên lựa chọn danh sách thả xuống trong Excel
  • Thêm tùy chọn trống vào danh sách thả xuống trong Excel (2 phương pháp)
  • Cách thêm mục vào danh sách thả xuống trong Excel (5 phương pháp)
  • VBA chọn giá trị từ danh sách thả xuống trong Excel (2 phương pháp)
  • Giá trị duy nhất trong danh sách thả xuống với VBA trong Excel (Hướng dẫn hoàn chỉnh)

2. Định dạng có điều kiện để làm nổi bật danh sách không khớp trong Excel

Chúng ta có thể làm nổi bật mục nhập sai và giúp hiểu dữ liệu dễ dàng hơn. Chúng ta dùng kết hợp hàm VLOOKUP, ISERROR, INDEX và MATCH cho điều kiện. Để làm vậy, hãy làm theo quy trình dưới đây.

  • Chọn ô có danh sách thả xuống phụ thuộc. Vì vậy, chọn ô E5.
  • Tiếp theo, đi đến tab Trang chủ và chọn Quy tắc mới từ menu thả xuống Định dạng có điều kiện dưới nhóm Kiểu.

Tạo danh sách thả xuống phụ thuộc nhiều từ trong Excel – Hướng dẫn từng bước

  • Điều này sẽ mở hộp thoại Quy tắc định dạng mới.
  • Bây giờ, chọn Sử dụng công thức để xác định ô cần định dạng từ hộp chọn Chọn loại quy tắc.
  • Nhập công thức vào Chỉnh sửa mô tả quy tắc.
=ISERROR(VLOOKUP(E3,INDEX($A$2:$B$6,,MATCH(D3,$A$1:$B$1)),1,0))
  • Sau đó, nhấp Định dạng để chọn định dạng ưa thích làm nổi bật ô.

Tạo danh sách thả xuống phụ thuộc nhiều từ trong Excel – Hướng dẫn từng bước

  • Cửa sổ bật lên khác xuất hiện, là hộp thoại Định dạng ô.
  • Từ menu Điền, chọn màu để làm nổi bật ô bạn muốn định dạng.
  • Sau đó, nhấp OK.

Tạo danh sách thả xuống phụ thuộc nhiều từ trong Excel – Hướng dẫn từng bước

  • Điều này sẽ đưa bạn về hộp thoại Quy tắc định dạng mới.
  • Tiếp theo, nhấp OK trên hộp thoại này.

Tạo danh sách thả xuống phụ thuộc nhiều từ trong Excel – Hướng dẫn từng bước

  • Cuối cùng, bạn thấy nếu nhập sai dữ liệu, nó sẽ làm nổi bật ô.

Tạo danh sách thả xuống phụ thuộc nhiều từ trong Excel – Hướng dẫn từng bước

Ở đây, hàm VLOOKUP dùng trong công thức để xác định mục có trong danh sách thả xuống phụ thuộc hay không. Hàm ISERROR kiểm tra điều này và chỉ khi trả về true mới làm nổi bật ô.

Đọc thêm: Danh sách thả xuống có điều kiện trong Excel (Tạo, Sắp xếp và Sử dụng)

Kết luận

Bài viết sẽ hỗ trợ bạn tạo Danh sách thả xuống phụ thuộc với Nhiều từ trong Excel. Hy vọng điều này giúp ích cho bạn! Nếu bạn có câu hỏi, góp ý hoặc phản hồi vui lòng cho chúng tôi biết ở phần bình luận. Hoặc bạn có thể xem các bài viết khác trong blog ExcelDemy.com!

Bài viết liên quan

  • VLOOKUP với danh sách thả xuống trong Excel
  • Cách dùng câu lệnh IF tạo danh sách thả xuống trong Excel
  • Danh sách thả xuống Excel tùy thuộc vào lựa chọn
  • Cách liên kết giá trị ô với danh sách thả xuống trong Excel (5 cách)
  • Tạo bộ lọc thả xuống để trích xuất dữ liệu dựa trên lựa chọn trong Excel