Kể từ khi menu thả xuống Xác thực dữ liệu được thêm vào Excel vào năm 1997, mọi người đã cố gắng tìm ra cách để thay đổi menu thả xuống thứ hai dựa trên lựa chọn trong menu thả xuống đầu tiên.
Ví dụ: nếu bạn chọn Trái cây trong A2, menu thả xuống trong A4 sẽ cung cấp Táo, Chuối, Anh đào. Nhưng nếu bạn chọn Thảo mộc từ A2, danh sách ở A4 sẽ cung cấp Hồi, Húng quế, Quế. Đã có nhiều giải pháp trong những năm qua. Tôi đã trình bày nó ít nhất hai lần trong Podcast:
- Phương pháp cổ điển đã sử dụng rất nhiều phạm vi được đặt tên như trong tập 383.
- Một phương pháp khác đã sử dụng công thức OFFSET trong Tập 1606.
Với việc phát hành các công thức Mảng Động mới trong Xem trước Công khai, hàm FILTER mới sẽ cung cấp cho chúng ta một cách khác để thực hiện Xác thực Phụ thuộc.
Giả sử rằng đây là cơ sở dữ liệu về sản phẩm của bạn:
Sử dụng công thức =SORT(UNIQUE(B4:B23))trong D4 để có được danh sách phân loại duy nhất. Đây là một loại công thức hoàn toàn mới. Một công thức trong D4 trả về nhiều câu trả lời sẽ tràn vào nhiều ô. Để tham chiếu đến Phạm vi tràn, bạn sẽ sử dụng =D4#thay vì =D4.
Chọn một ô để giữ menu Xác thực Dữ liệu. Chọn Alt + DL để mở Xác thực dữ liệu. Thay đổi Cho phép thành "Danh sách". Chỉ định =D4#làm nguồn của danh sách. Lưu ý rằng Hashtag (#) là Spiller - có nghĩa là bạn đang đề cập đến toàn bộ Spiller Range.
Kế hoạch là ai đó sẽ chọn một phân loại từ trình đơn thả xuống đầu tiên. Sau đó, một công thức =FILTER(A4:A23,B4:B23=H3,"Choose Class First")trong E4 sẽ trả về tất cả các sản phẩm trong danh mục đó. Lưu ý rằng việc sử dụng "Chọn lớp đầu tiên" làm đối số thứ ba tùy chọn. Điều này sẽ ngăn lỗi #VALUE! lỗi xuất hiện.
Có thể có một số mục khác nhau trong danh sách tùy thuộc vào danh mục được chọn. Thiết lập Xác thực dữ liệu trỏ đến =E4#sẽ mở rộng hoặc thu hẹp theo độ dài của danh sách.
Xem video
Bản ghi video
Học Excel Từ, Podcast Tập 2248: Xác thực Phụ thuộc Sử dụng Mảng.
Chà, này. Điều này đã được giải quyết hai lần trước đây trên podcast, cách thực hiện xác thực phụ thuộc và xác thực phụ thuộc là gì là bạn có thể chọn, đầu tiên, một danh mục và sau đó, để đáp lại, menu thả xuống thứ hai sẽ thay đổi thành chỉ các mục từ danh mục đó và trước đây, điều này rất phức tạp và với các mảng động mới đã được công bố vào tháng 9 năm 2018… và các mục này đang được triển khai, vì vậy bạn phải có Office 365. Ngay bây giờ ngày 10 tháng 10, tôi đã nghe nói rằng họ thuộc khoảng 50% người dùng nội bộ Office, vì vậy họ triển khai chúng rất chậm. Có thể phải đến nửa đầu năm 2019 bạn mới nhận được những thứ này, nhưng nó sẽ cho phép chúng tôi thực hiện xác thực phụ thuộc theo cách dễ dàng hơn nhiều.
Vì vậy, tôi có hai công thức ở đây. Công thức đầu tiên là DUY NHẤT của tất cả các phân loại và tôi đã gửi công thức đó vào lệnh SORT. Vì vậy, điều đó mang lại cho tôi 1 công thức trả về 5 kết quả và công thức đó nằm trong D4. Vì vậy, ở đây, nơi tôi muốn chọn xác thực dữ liệu, tôi sẽ (DL - 1:09)… SOURCE sẽ là = D4 #. Số # đó - chúng tôi đã gọi nó là bộ đổ - hãy đảm bảo rằng nó trả về tất cả các kết quả từ D4. Vì vậy, nếu tôi thêm một danh mục mới ở đây và danh mục này tăng lên, D4 # sẽ nhận thêm số tiền đó, được chứ? (= SORT (DUY NHẤT (B4: B23)))
Vì vậy, xác thực đầu tiên đó khá đơn giản, nhưng bây giờ chúng tôi biết rằng chúng tôi đã chọn CITRUS - điều này sẽ khó khăn hơn - tôi muốn lọc danh sách trong cột A trong đó mục trong cột B bằng với mục đã chọn , ổn thỏa? Vì vậy, trước tiên chúng ta phải để họ chọn thứ gì đó và sau đó, khi tôi biết đó là CITRUS, sau đó đưa cho tôi LIME, ORANGE và TANGERINE, họ sẽ chọn thứ khác. QUẢ MỌNG. Kiểm tra điều này. Các tạp chí khoa học nói rằng một quả chuối là một quả mọng. Tôi không đồng ý với điều đó. Tôi không cảm thấy như một quả mọng nhưng đừng trách tôi. Tôi chỉ, bạn biết đấy, sử dụng Internet. BANANA, ELDERBERRY và RASPBERRY.
Bây giờ, bạn biết đấy, rắc rối với việc này là ban đầu ai đó sẽ đến đây mà không chọn bất cứ thứ gì, và, vì vậy, trong trường hợp đó, chúng tôi đã CHỌN LỚP ĐẦU TIÊN, đó là lập luận thứ ba nói rằng nếu không tìm thấy gì, được chứ? Vì vậy, bạn biết đấy, theo cách đó, nếu chúng ta bắt đầu trong tình huống này, lựa chọn sẽ là CHỌN LỚP HỌC ĐẦU TIÊN. Ý tưởng là họ chọn CLASS, VEGETABLE, cập nhật này, và sau đó các mục đó đến từ danh sách đó. XÁC NHẬN DỮ LIỆU ở đây, tất nhiên, đó là một công cụ bổ sung khác, = E4 # để làm cho nó hoạt động, được chứ? Vì vậy, điều này thật tuyệt. (= BỘ LỌC (A4: A23, B4: B23 = H3, "Chọn Loại Trước"))
Xem cuốn sách Mảng động Excel của tôi. Đây là… nó sẽ miễn phí đến cuối năm 2018. Hãy kiểm tra liên kết ở dưới đó trong phần mô tả YouTube, cách bạn có thể tải xuống, ví dụ này cùng với 29 ví dụ khác về cách sử dụng các mục này.
Vâng, kết thúc cho ngày hôm nay. Mảng động cung cấp cho chúng ta một cách khác để thực hiện xác nhận phụ thuộc. Nếu bạn chưa sử dụng Office 365 và chưa có những thứ này, hãy quay lại, tôi cho là video 1606 chỉ ra cách cũ để thực hiện việc này.
Tôi muốn cảm ơn bạn đã ghé qua. Chúng tôi sẽ gặp bạn lần sau cho một netcast khác từ.
Tải xuống tệp Excel
Để tải xuống tệp excel: depend-validation-using-array.xlsx
Để tìm hiểu thêm về Mảng động, hãy xem Mảng động Excel đi thẳng vào vấn đề.
Suy nghĩ của Excel trong ngày
Tôi đã hỏi những người bạn Excel Master của mình để được tư vấn về Excel. Hôm nay cần suy ngẫm:
"Không bao giờ xóa một tệp Excel mà không sao lưu nó trước."
Mike Alexander
