Hướng dẫn cho biết cách tạo danh sách thả xuống Excel tùy thuộc vào một ô khác bằng cách sử dụng các hàm mảng động mới.
Tạo một danh sách thả xuống đơn giản trong Excel thật dễ dàng. Làm một xếp tầng thả xuống luôn luôn là một vấn đề. Hướng dẫn được liên kết ở trên mô tả bốn cách tiếp cận khác nhau, mỗi cách bao gồm một số bước điên rồ, một loạt các công thức khác nhau và một số hạn chế liên quan đến các mục nhập nhiều từ, ô trống, v.v.
Đó là tin xấu. Tin tốt là những phương pháp đó được thiết kế cho các phiên bản Excel trước động. Sự ra đời của mảng động trong Excel 365 đã thay đổi mọi thứ! Với các hàm mảng động mới, việc tạo một danh sách thả xuống phụ thuộc nhiều lần chỉ mất vài phút, nếu không muốn nói là vài giây. Không thủ đoạn, không báo trước, không vô nghĩa. Chỉ những giải pháp nhanh chóng, đơn giản và dễ thực hiện.
Cách tạo danh sách thả xuống động trong Excel
Ví dụ này trình bày cách tiếp cận chung để tạo danh sách thả xuống theo tầng trong Excel bằng cách sử dụng các hàm mảng động.
Giả sử bạn có danh sách trái cây ở cột A và nhà xuất khẩu ở cột B. Một điều phức tạp nữa là tên trái cây không được nhóm lại mà nằm rải rác trên cột. Mục đích là đặt các tên trái cây duy nhất trong menu thả xuống đầu tiên và tùy thuộc vào lựa chọn của người dùng, hiển thị các nhà xuất khẩu có liên quan trong menu thả xuống thứ hai.
Để tạo danh sách thả xuống phụ thuộc động trong Excel, hãy thực hiện các bước sau:
1. Nhận các mục cho danh sách thả xuống chính
Để bắt đầu, chúng tôi sẽ trích xuất tất cả các tên trái cây khác nhau từ cột A. Điều này có thể được thực hiện bằng cách sử dụng ĐỘC NHẤT hàm ở dạng đơn giản nhất – cung cấp danh sách trái cây cho đối số đầu tiên (mảng) và bỏ qua các đối số tùy chọn còn lại vì mặc định của chúng hoạt động tốt cho chúng tôi:
=UNIQUE(A3:A15)
Công thức chuyển đến G3 và sau khi nhấn Đi vào chìa khóa, kết quả tự động tràn vào các ô tiếp theo.
2. Tạo menu thả xuống chính
Để tạo danh sách thả xuống chính của bạn, hãy định cấu hình Xác thực dữ liệu Excel quy tắc theo cách này:
-
- Chọn một ô mà bạn muốn menu thả xuống xuất hiện (trong trường hợp của chúng tôi là D3).
- Trên Dữ liệu tab, trong Công cụ dữ liệu nhóm, nhấp vào Xác nhận dữ liệu.
- bên trong Xác nhận dữ liệu hộp thoại, hãy làm như sau:
- Dưới Cho phép, lựa chọn Danh sách.
- bên trong Nguồn , nhập tham chiếu đến phạm vi tràn xuất theo công thức DUY NHẤT. Đối với điều này, hãy nhập thẻ băm ngay sau tham chiếu ô, như sau: = $ G $ 3 #Đây được gọi là tham chiếu phạm vi tràn và cú pháp này đề cập đến toàn bộ phạm vi bất kể mức độ mở rộng hoặc hợp đồng của nó.
- Nhấp chuột đồng ý để đóng hộp thoại.
Danh sách thả xuống chính của bạn đã hoàn tất!
3. Nhận các mặt hàng cho danh sách thả xuống phụ thuộc
Để nhận các mục nhập cho menu thả xuống phụ, chúng tôi sẽ lọc các giá trị trong cột B dựa trên giá trị được chọn trong menu thả xuống đầu tiên. Điều này có thể được thực hiện với sự trợ giúp của một hàm mảng động khác được gọi là BỘ LỌC:
=FILTER(B3:B15, A3:A15=D3)
Trong đó B3: B15 là dữ liệu nguồn cho danh sách thả xuống phụ thuộc của bạn, A3: A15 là dữ liệu nguồn cho danh sách thả xuống chính của bạn và D3 là ô thả xuống chính.
Để đảm bảo công thức hoạt động chính xác, bạn có thể chọn một số giá trị trong danh sách thả xuống đầu tiên và quan sát kết quả được FILTER trả về. Hoàn hảo! 🙂
4. Làm cho người phụ thuộc giảm xuống
Để tạo danh sách thả xuống thứ hai, hãy định cấu hình tiêu chí xác thực dữ liệu chính xác như bạn đã làm cho danh sách thả xuống đầu tiên ở bước 2. Nhưng lần này, hãy tham chiếu phạm vi tràn được trả về bởi hàm FILTER: = $ H $ 3 #
Đó là nó! Danh sách thả xuống phụ thuộc vào Excel của bạn đã sẵn sàng để sử dụng.
Mẹo và lưu ý:
- Để có mục mới bao gồm trong danh sách thả xuống tự động, định dạng dữ liệu nguồn của bạn dưới dạng bảng Excel. Hoặc bạn có thể bao gồm một vài ô trống trong công thức của mình như được minh họa trong ví dụ này.
- Nếu dữ liệu gốc của bạn có bất kỳ khoảng trống nào, bạn có thể lọc ra khoảng trống bằng cách sử dụng giải pháp này.
- Đến sắp xếp theo thứ tự bảng chữ cái các mục của danh sách thả xuống, hãy bọc các công thức của bạn trong hàm SORT như được giải thích trong ví dụ này.
Cách tạo nhiều danh sách thả xuống phụ thuộc trong Excel
Trong ví dụ trước, chúng tôi đã tạo một danh sách thả xuống tùy thuộc vào một ô khác. Nhưng điều gì sẽ xảy ra nếu bạn cần một hệ thống phân cấp đa cấp, tức làrd thả xuống tùy thuộc vào 2nd danh sách, hoặc thậm chí là 4thứ tự thả xuống tùy thuộc vào 3rd danh sách. Điều đó có thể không? Có, bạn có thể thiết lập bất kỳ số lượng danh sách phụ thuộc nào (tất nhiên là một con số hợp lý :).
Đối với ví dụ này, chúng tôi đã đặt các tiểu bang / tỉnh trong cột C và hiện đang tìm cách thêm trình đơn thả xuống tương ứng trong G3:
Để tạo danh sách thả xuống phụ thuộc nhiều trong Excel, đây là những gì bạn cần làm:
1. Thiết lập menu thả xuống đầu tiên
Danh sách thả xuống chính được tạo với các bước giống hệt như trong ví dụ trước (vui lòng xem bước 1 và 2 ở trên). Sự khác biệt duy nhất là tham chiếu phạm vi tràn mà bạn nhập vào Nguồn cái hộp.
Lần này, công thức DUY NHẤT nằm trong E8 và danh sách thả xuống chính sẽ nằm trong E3. Vì vậy, bạn chọn E3, nhấp vào Xác nhận dữ liệuvà cung cấp tham chiếu này: = $ E $ 8 #
2. Định cấu hình trình đơn thả xuống thứ hai
Như bạn có thể đã nhận thấy, bây giờ cột B chứa nhiều lần xuất hiện của cùng một nhà xuất. Nhưng bạn chỉ muốn những tên duy nhất trong danh sách thả xuống của mình, phải không? Để loại bỏ tất cả các lần xuất hiện trùng lặp, hãy bao hàm UNIQUE xung quanh công thức FILTER của bạn và nhập công thức đã cập nhật này vào F8:
=UNIQUE(FILTER(B3:B15, A3:A15=E3))
Trong đó B3: B15 là dữ liệu nguồn cho menu thả xuống thứ hai, A3: A15 là dữ liệu nguồn cho menu thả xuống đầu tiên và E3 là ô thả xuống đầu tiên.
Sau đó, sử dụng tham chiếu phạm vi tràn sau cho tiêu chí Xác thực dữ liệu: = $ F $ 8 #
3. Thiết lập trình đơn thả xuống thứ ba
Để thu thập các vật phẩm cho 3rd danh sách thả xuống, sử dụng Công thức FILTER với nhiều tiêu chí. Tiêu chí đầu tiên kiểm tra toàn bộ danh sách trái cây với giá trị được chọn trong 1st thả xuống (A3: A15 = E3) trong khi tiêu chí thứ hai kiểm tra danh sách các nhà xuất khẩu so với lựa chọn trong 2nd thả xuống (B3: B15 = F3). Công thức hoàn chỉnh cho G8:
=FILTER(C3:C15, (A3:A15=E3) * (B3:B15=F3))
Điều cuối cùng bạn cần làm là tạo thêm một quy tắc Xác thực dữ liệu với Nguồn tham chiếu: = $ G $ 8 #
Danh sách thả xuống nhiều phụ thuộc của bạn rất tốt để sử dụng!
Cách tạo danh sách thả xuống có thể mở rộng trong Excel
Sau khi tạo danh sách thả xuống, mối quan tâm đầu tiên của bạn có thể là điều gì sẽ xảy ra khi bạn thêm các mục mới vào dữ liệu nguồn. Danh sách thả xuống có tự động cập nhật không? Nếu dữ liệu gốc của bạn được định dạng là Bảng Excel, thì có, danh sách thả xuống động được thảo luận trong các ví dụ trước sẽ tự động mở rộng mà bạn không cần nỗ lực vì bảng Excel có thể mở rộng theo bản chất của chúng.
Nếu vì lý do nào đó, việc sử dụng bảng Excel không phải là một tùy chọn, bạn có thể làm cho danh sách thả xuống của mình có thể mở rộng theo cách này:
- Đến bao gồm dữ liệu mới tự động khi nó được thêm vào danh sách nguồn, hãy thêm một vài ô bổ sung vào các mảng được tham chiếu trong công thức của bạn.
- Đến loại trừ các ô trống, hãy cấu hình các công thức để bỏ qua các ô trống cho đến khi chúng được lấp đầy.
Hãy ghi nhớ hai điểm này, hãy tinh chỉnh các công thức trong bảng chuẩn bị dữ liệu của chúng tôi. Quy tắc Xác thực Dữ liệu không yêu cầu bất kỳ điều chỉnh nào.
Công thức cho menu thả xuống chính
Với tên trái cây trong A3: A15, chúng tôi thêm 5 ô bổ sung vào mảng để phục vụ cho các mục nhập mới có thể có. Ngoài ra, chúng tôi nhúng hàm FILTER vào DUY NHẤT để giải nén giá trị duy nhất không có khoảng trống.
Với những điều trên, công thức trong G3 có dạng như sau:
=UNIQUE(FILTER(A3:A20, A3:A20<>""))
Công thức cho trình đơn thả xuống phụ thuộc
Công thức trong G3 không cần điều chỉnh nhiều – chỉ cần mở rộng mảng với một vài ô nữa:
=FILTER(B3:B20, A3:A20=D3)
Kết quả là một danh sách thả xuống phụ thuộc hoàn toàn có thể mở rộng hoàn toàn động:
Cách sắp xếp danh sách thả xuống theo bảng chữ cái
Bạn muốn sắp xếp danh sách thả xuống của mình theo thứ tự bảng chữ cái mà không cần sử dụng dữ liệu nguồn? Excel động mới cũng có một chức năng đặc biệt cho điều này! Trong bảng chuẩn bị dữ liệu của bạn, chỉ cần bọc Hàm SORT xung quanh các công thức hiện có của bạn.
Các quy tắc xác thực dữ liệu được định cấu hình chính xác như được mô tả trong các ví dụ trước.
Sắp xếp từ A đến Z
Vì thứ tự sắp xếp tăng dần là tùy chọn mặc định, bạn chỉ có thể lồng các công thức hiện có của mình vào mảng đối số của SORT, bỏ qua tất cả các đối số khác là tùy chọn.
Cho thả xuống chính (công thức trong G3):
=SORT(UNIQUE(FILTER(A3:A20, A3:A20<>"")))
Cho trình đơn thả xuống phụ thuộc (công thức trong H3):
=SORT(FILTER(B3:B20, A3:A20=D3))
Làm xong! Cả hai danh sách thả xuống được sắp xếp theo thứ tự bảng chữ cái A đến Z.
Để sắp xếp từ Z đến A
Để sắp xếp theo thứ tự giảm dần, bạn cần đặt 3rd tranh luận (thứ tự sắp xếp) của hàm SORT thành -1.
Cho thả xuống chính (công thức trong G3):
=SORT(UNIQUE(FILTER(A3:A20, A3:A20<>"")), 1, -1)
Cho trình đơn thả xuống phụ thuộc (công thức trong H3):
=SORT(FILTER(B3:B20, A3:A20=D3), 1, -1)
Thao tác này sẽ sắp xếp cả dữ liệu trong bảng chuẩn bị và các mục trong danh sách thả xuống từ Z đến A:
Đó là cách tạo danh sách thả xuống động trong Excel với sự trợ giúp của các hàm mảng động mới. Không giống như các phương pháp truyền thống, cách tiếp cận này hoạt động hoàn hảo cho các mục nhập đơn và nhiều từ và xử lý bất kỳ ô trống nào. Cảm ơn bạn đã đọc và hy vọng sẽ gặp bạn trên blog của chúng tôi vào tuần tới!
The post Tạo danh sách thả xuống phụ thuộc động trong Excel 1 cách dễ dàng appeared first on Kế Toán MVB | Dịch Vụ kế toán tại Hà Nội | Uy tín - Chất lượng.
source https://ketoanmvb.com/tao-danh-sach-tha-xuong-phu-thuoc-dong-trong-excel.html
Không có nhận xét nào:
Đăng nhận xét