Nội dung chính
Nếu bạn đang tìm cách tạo một Drop down List có thể mở rộng, có thể kéo hoặc copy qua nhiều hàng, bạn đã đến đúng trang và sẽ có giải pháp sau vài phút.
Sự ra đời của mảng động đã làm cho cuộc sống của chúng ta với tư cách là người dùng Excel dễ dàng hơn rất nhiều. Trong số nhiều thứ khác, chúng đơn giản hóa đáng kể việc tạo Drop down List động . Hướng dẫn được liên kết ở trên cho biết cách thiết lập nhanh nhiều menu Drop down với các mảng động và làm cho nó có thể mở rộng để tự động bao gồm các mục nhập mới.
Tôi cảm thấy rất tự hào về công việc Ketoanmvb đã làm cho đến khi Ketoanmvb nhận được một vài nhận xét như sau: “Điều đó hoạt động tốt cho một hàng, nhưng làm thế nào để điều này hoạt động cho toàn bộ cột?” Một câu hỏi công bằng. Thật vậy, danh sách chọn trong Excel thường được sử dụng nhất trong các trang tính có hàng trăm hàng để thuận tiện cho việc nhập dữ liệu. Vì vậy, Dịch vụ kế toán tại Hà Nội – ketoanmvb đã xem xét lại toàn bộ cách tiếp cận, tìm ra giải pháp cho nhiều ô và tôi rất vui được chia sẻ kết quả nỗ lực của Ketoanmvb với bạn!
Tạo Drop Down List từ nguồn dữ liệu
Để bắt đầu, hãy lấy một số dữ liệu nguồn để làm việc. Trong bảng chính của Ketoanmvb ở bên trái, chúng tôi muốn có hai danh sách chọn trong mỗi hàng, để việc chọn một Phòng trong danh sách đầu tiên chỉ hiển thị Người quản lý cho bộ phận đã chọn đó trong danh sách thứ hai. Nếu bạn thay đổi lựa chọn trong danh sách chính, tên trong Drop down List phụ thuộc sẽ cập nhật tương ứng.
Bước 1. Cấu trúc dữ liệu nguồn
Dữ liệu nguồn cho Drop down List thường đến từ các nguồn khác nhau và được tổ chức khác nhau. Vì vậy, bước đầu tiên của chúng tôi là cấu trúc dữ liệu gốc theo nhu cầu của chúng tôi. Đối với điều này, chúng tôi sẽ tạo một số loại bảng chuẩn bị sẽ liệt kê tất cả các tên bộ phận khác nhau trong hàng tiêu đề và dưới mỗi nợ. tên sẽ có danh sách nhân viên làm việc trong bộ phận cụ thể đó. Để tự động hóa công việc và tránh sai sót của con người, chúng tôi sẽ sử dụng các công thức sau.
Để lấy các phòng ban , hãy nhập công thức này vào H2.
=TRANSPOSE(SORT(UNIQUE(E3:E15)))
Ở đây, hàm UNIQUE trích xuất tất cả các phòng ban khác nhau từ E3: E15. Hàm SORT sắp xếp các kết quả theo thứ tự bảng chữ cái để các mục trong danh sách chính của bạn sẽ được sắp xếp từ A đến Z (nếu bạn không muốn điều đó, bạn có thể xóa SORT khỏi công thức và tên sẽ xuất hiện trong thứ tự như trong bảng nguồn của bạn). Cuối cùng, TRANSPOSE thay đổi hướng đầu ra từ dọc sang ngang.
Xin lưu ý rằng công thức chỉ cần được nhập vào một ô và kết quả tự động tràn sang các ô lân cận (tính năng này được gọi là phạm vi tràn ).
Bằng cách này, chúng tôi đã có các mục cho Drop down List chính của chúng tôi:
Để kéo tên người quản lý , công thức trong H3 là:
=SORT(FILTER($F$3:$F$15, $E$3:$E$15=H$2))
Ở đây, Ketoanmvb sử dụng chức năng FILTER để lọc các nhân viên thuộc một bộ phận cụ thể ($ E $ 3: $ E $ 15 = H $ 2). Hàm SORT sắp xếp các tên theo thứ tự bảng chữ cái (nếu bạn muốn giữ nguyên thứ tự hiện có, hãy xóa nó khỏi công thức).
Giống như công thức trước, công thức này cũng động, và toàn bộ cơn thịnh nộ tràn được lấp đầy với các kết quả cùng một lúc. Tất cả những gì bạn cần làm là kéo công thức sang bên phải để có danh sách người quản lý cho từng khoản nợ.
Và điều này cung cấp cho chúng tôi các mục cho Drop down List phụ thuộc:
Mẹo và lưu ý:
- Trong ví dụ này, chúng tôi có tất cả dữ liệu trên cùng một trang tính để giúp bạn theo dõi dễ dàng hơn. Trong bảng tính thực của bạn, bạn nên đặt một bảng chuẩn bị trên một trang tính riêng biệt để đảm bảo có đủ ô trống ở bên dưới và bên phải để chứa tất cả dữ liệu. Như đã đề cập, các công thức mảng động chỉ được nhập vào một ô và bạn không thể biết kết quả sẽ tràn vào bao nhiêu ô.
- Nếu mục tiêu của bạn là tạo Drop down List có thể mở rộng , thì hãy sử dụng một phương pháp hơi khác cho bảng chuẩn bị, được thảo luận trong ví dụ này .
Bước 2. Tạo Drop down List chính
Với dữ liệu nguồn được sắp xếp hợp lý, hãy tạo Drop down List đầu tiên theo cách thông thường với sự trợ giúp của Data Validation trong Excel :
- Chọn ô trên cùng mà bạn muốn Drop down List chính xuất hiện (trong trường hợp của chúng tôi là B3).
- Trên tab Data , trong nhóm Data Tools , hãy bấm Data Validation .
- Trên tab Cài đặt của hộp thoại Data Validation , hãy định cấu hình quy tắc:
- Trong Allow , chọn List .
- Trong hộp Source , nhập tham chiếu phạm vi tràn trỏ đến tên các phòng ban trong bảng chuẩn bị: = $ H $ 2 #Cú pháp này (địa chỉ ô theo sau là thẻ băm) đề cập đến toàn bộ phạm vi tràn bất kể nó thực sự chứa bao nhiêu ô.
- Bấm OK để đóng hộp thoại.
Drop down List chính cho ô đầu tiên được thực hiện:
Bước 3. Tạo Drop down List phụ thuộc
Về mặt kỹ thuật, việc thiết lập danh sách chọn liên tiếp có vẻ khá dễ dàng – bạn chỉ cần tạo thêm một quy tắc xác thực dữ liệu cho một cột khác. Tuy nhiên, có một phần khó khăn – công thức cho hộp Source . Lần này, bạn không thể sử dụng tham chiếu phạm vi tràn thông thường vì Drop down List thứ hai cần tính đến lựa chọn trong danh sách thả xuống đầu tiên. Để giải quyết nhu cầu này, chúng tôi sẽ đề cập gián tiếp đến phạm vi tràn có liên quan với công thức sau:
INDIRECT(CHAR(CODE(“col_letter“) + MATCH(dropdown_cell, dropdown_spill_range, 0)) & “row_num#”)
Ở đâu:
- col_letter – là chữ cái của cột đứng trước cột ngoài cùng bên trái của bảng chuẩn bị. Trong ví dụ này, bảng chuẩn bị bắt đầu bằng H2. Cột đứng trước là G, vì vậy chúng tôi sử dụng CODE (“G”).
- dropdown_cell – là địa chỉ của ô trên cùng chứa Drop down List chính, trong trường hợp của chúng tôi là B3. Hãy đảm bảo sử dụng tham chiếu ô tương đối không có dấu $ để công thức điều chỉnh chính xác cho từng hàng nơi bạn sẽ Copy menu Drop-down của mình.
- dropdown_spill_range – tham chiếu đến phạm vi tràn trong bảng chuẩn bị có chứa các mục cho Drop down List chính. Trong ví dụ của chúng tôi, đó là các phòng ban được trả về bởi công thức mảng động trong H2, vì vậy chúng tôi sử dụng tham chiếu phạm vi tràn này: $ H $ 2 #
- row_num – số lượng hàng trong bảng chuẩn bị chứa công thức trả về các mục cho menu Drop-down phụ thuộc. Công thức của chúng ta nằm ở hàng 3 (H3: K3), vì vậy nó là “3 #”. Thay vì mã hóa cứng số hàng, bạn có thể sử dụng hàm ROW (), nhưng chúng tôi sẽ không làm phức tạp mọi thứ hơn chúng đã có 🙂
Đặt tất cả các phần trên lại với nhau, chúng tôi nhận được công thức sau cho quy tắc xác thực thả xuống phụ thuộc:
=INDIRECT(CHAR(CODE("G") + MATCH(B3, $H$2#, 0))&"3#")
Bây giờ, bạn chỉ cần chọn các tế bào trên cùng để xem Drop down List thứ yếu (C3), đi đến dữ liệu tab> Data Validation , và cấu hình các quy tắc như thế này:
Đó là nó! Drop down List phụ thuộc rất tốt để đi:
Công thức này hoạt động như thế nào:
Từ kinh nghiệm của tôi, nhiều khách truy cập blog của chúng tôi háo hức không chỉ biết cách áp dụng công thức này hay công thức kia mà còn hiểu logic nội tại của nó. Đối với những người dùng tò mò và chu đáo như vậy, tôi sẽ cung cấp bảng phân tích chi tiết của công thức:
=INDIRECT(CHAR(CODE("G") + MATCH(B3, $H$2#, 0))&"3#")
Ở cấp độ cao, chúng tôi sử dụng hàm INDIRECT để “động” tham chiếu đến phạm vi tràn của các nhà quản lý tương ứng với bộ phận được chọn trong menu Drop down chính.
Giả sử bạn đã chọn Lập kế hoạch từ menu Drop down trong B3. Các nhân viên của Kế hoạch nợ. được liệt kê trong cột J bắt đầu bằng J3. Điều đó có nghĩa là, bằng cách nào đó chúng ta cần cung cấp tham chiếu J3 # cho INDIRECT:
=INDIRECT("J3#")
Và đây là cách chúng tôi làm điều đó:
Để lấy ký tự cột, 3 hàm sau được sử dụng cùng nhau:
CHAR(CODE("G") + MATCH(B3, $H$2#, 0))
Hàm MATCH tìm kiếm giá trị B3 trong danh sách các phòng ban bắt đầu bằng H2 và trả về vị trí tương đối của nó, đó là 3 (Phân tích, Thiết kế, Lập kế hoạch,…).
Hàm CODE trả về mã số cho ký tự của cột trước cột đầu tiên của bảng chuẩn bị, bạn có thể coi là cột 0. Trong trường hợp của chúng tôi, cột trước đó là G và CODE (“G”) bằng 71. Thêm số được MATCH trả về vào nó, và bạn sẽ nhận được 74 (71 + 3 = 74).
Bây giờ, chúng ta cần chuyển đổi số mã ở trên trở lại ký tự cột và chúng ta cam kết tác vụ này với hàm CHAR:
CHAR(74)
Ký tự tương ứng với mã ASCII 74 là “J”, chính xác là những gì chúng tôi đang tìm kiếm!
Tại thời điểm này, công thức phức tạp của chúng tôi rút gọn thành một công thức dễ hiểu:
INDIRECT("J"&"3#")
“J” được nối với “3” tạo ra địa chỉ của ô J3, mà chúng tôi thêm một thẻ băm để làm cho nó trở thành tham chiếu phạm vi tràn, để hàm INDIRECT trả về toàn bộ một mảng thay vì một giá trị ô đơn lẻ. Làm xong!
Bước 4. Copy Drop down List trên nhiều hàng
Đây là phần dễ nhất. Để danh sách chọn xuất hiện trong nhiều ô, bạn có thể chỉ cần copy chúng giống như bất kỳ nội dung ô nào khác bằng cách sử dụng tính năng copy / paste hoặc bằng cách kéo qua các ô liền kề. Đối với thực hành, chúng tôi sẽ thử cả hai kỹ thuật.
Để copy Dropdown List chính , hãy chọn ô chứa nó (B3) và kéo chốt điền (một hình vuông nhỏ ở góc dưới bên phải của ô) qua nhiều ô nếu cần:
Để Copy Drop down List phụ thuộc , hãy thực hiện Copy / Paste thông thường:
- Chọn ô có menu thả xuống phụ (C3) và nhấn Ctrl + C để Copy nó.
- Chọn tất cả các ô khác mà bạn muốn danh sách phụ thuộc xuất hiện (C4: C12) và nhấn Ctrl + V để Paste nội dung đã Copy.
Giờ đây, cả hai danh sách đều xuất hiện ở mọi hàng cho phép bạn chọn một bộ phận và người quản lý cho mỗi dự án.
Hãy nhớ rằng các phương pháp trên Copy tất cả nội dung của một ô bao gồm Xác thực Dữ liệu và lựa chọn hiện tại. Vì vậy, chúng tốt nhất nên được sử dụng khi chưa có mục nào được chọn trong danh sách.
Nếu bạn đã chọn các mục, thì bạn chỉ có thể Copy quy tắc Xác thực dữ liệu bằng cách sử dụng tính năng Dán Đặc biệt . Đây là cách thực hiện:
- Chọn ô thả xuống và nhấn Ctrl + C hoặc nhấp vào Copy trên ruy-băng.
- Chọn các ô đích.
- Bấm chuột phải vào phạm vi đã chọn, sau đó bấm Dán Đặc biệt… .
- Trong cửa sổ hộp thoại Dán Đặc biệt , chọn Xác thực và bấm OK .
Kết quả: Drop down List phụ thuộc nhiều hàng
Mặc dù giải pháp mảng động của chúng tôi không phải là thiết lập một cú nhấp chuột, nhưng nó nhanh hơn vẻ bề ngoài và chắc chắn nhanh hơn nhiều so với cách tiếp cận kiểu cũ với các dải được đặt tên. Dù sao thì kết quả cũng đáng để nỗ lực, đồng ý chứ?
Đây là – trình đơn thả xuống phụ thuộc cho nhiều hàng trong đó việc chọn một mục từ danh sách đầu tiên xác định những mục nào sẽ xuất hiện trong Drop down List phụ.
Cách làm cho nhiều Drop down List có thể mở rộng
Nếu nhiều dữ liệu hơn có thể được thêm vào bảng nguồn của bạn trong tương lai và bạn muốn tự động đưa các mục nhập mới vào Drop down List, thì bạn sẽ phải sử dụng các phiên bản công thức phức tạp hơn một chút cho bảng chuẩn bị. Có 2 cách tiếp cận có thể có ở đây, một phạm vi thông thường và một bảng Excel chính thức, mỗi cách có những lưu ý riêng.
Phương pháp tiếp cận 1. Tổ chức dữ liệu nguồn trong một bảng
Một trong những ưu điểm chính của bảng Excel là tự động mở rộng để chứa dữ liệu mới và chúng tôi sẽ tận dụng khả năng này.
Điều đầu tiên bạn làm là chuyển đổi dữ liệu nguồn thành một bảng . Cách nhanh nhất là chọn dải E2: F15 và nhấn Ctrl + T. Để thuận tiện, chúng tôi đặt tên bảng là Source_data .
Tiếp theo, xây dựng các công thức cho bảng chuẩn bị bằng cách sử dụng các tham chiếu có cấu trúc . Nếu bạn không quen với cú pháp này, nó không phải là một vấn đề! Chỉ cần bắt đầu nhập công thức vào một ô, chọn phạm vi bắt buộc trong bảng của bạn và Excel sẽ tự động tạo tham chiếu có cấu trúc thích hợp cho bạn dựa trên tên cột.
Để chiết xuất các cục , công thức của H2 là:
=TRANSPOSE(SORT(UNIQUE(Source_data[Dept.])))
Để lấy tên người quản lý , hãy nhập công thức dưới đây vào H3 và kéo công thức đó sang bên phải qua một vài ô hơn so với các phòng ban hiện tại (giả sử, đến P3):
=IFERROR(SORT(FILTER(Source_data[[Manager]:[Manager]], (Source_data[[Dept.]:[Dept.]]=H$2))),"")
Hãy chú ý rằng các tham chiếu có cấu trúc tuyệt đối nên được sử dụng cho các cột Phòng và Người quản lý để các tham chiếu sẽ không thay đổi khi công thức được Copy theo chiều ngang. Mặc định trong bảng Excel là tham chiếu cột tương đối như bảng [cột] . Để làm cho tham chiếu là tuyệt đối, bạn cần lặp lại tên cột như sau: table [[column]: [column]] .
Hàm IFERROR được sử dụng để ngăn lỗi khi công thức được Copy sang các cột phụ ở bên phải.
Nếu tất cả được thực hiện chính xác, bảng chuẩn bị sẽ tự động mở rộng để kết hợp các bản ghi mới:
Phương pháp tiếp cận 2. Sử dụng một phạm vi nhưng cải tiến công thức
Nếu tham chiếu bảng trông quá phức tạp đối với bạn, bạn cũng có thể làm với một phạm vi bình thường. Trong trường hợp này, cần thực hiện một số cải tiến đối với các công thức:
- Bao gồm một số hàng trống trong phạm vi được cung cấp cho cả hai công thức.
- Lọc ra các ô trống để chúng không lọt vào danh sách chọn của bạn.
Để chiết xuất tên các bộ phận , công thức của H2 là:
=TRANSPOSE(SORT(UNIQUE(FILTER(E3:E30, E3:E30<>""))))
Xin lưu ý rằng chúng tôi sử dụng phạm vi E3: E30, mặc dù hiện tại có ít bản ghi hơn trong bảng nguồn của chúng tôi.
Để lấy tên người quản lý , hãy nhập công thức dưới đây vào H3 và kéo công thức đó qua một vài cột hơn so với các mục hiện có trong hàng tiêu đề:
=IFERROR(SORT(FILTER($F$3:$F$30, ($E$3:$E$30=H$2)*($E$3:$E$30<>""))),"")
Trong trường hợp này, chúng tôi khóa các tham chiếu phạm vi bằng dấu $ để ngăn chúng thay đổi trong khi Copy công thức.
Như với giải pháp trước đó, các bản ghi mới xuất hiện trong bảng chuẩn bị sau khi chúng được thêm vào bảng nguồn.
Kết quả: Drop down List có thể mở rộng nhiều
Bất kỳ cách tiếp cận nào bạn đã chọn, hãy định cấu hình và Copy hai quy tắc Xác thực dữ liệu như được giải thích trong Bước 2 – 4 ở trên và các mục mới được thêm vào sẽ được hiển thị trong Drop down List mà bạn không cần phải nỗ lực thêm!
Đó là mục đích của tôi để tạo một Drop down List nhiều trong Excel. Hy vọng rằng, giải pháp này cũng sẽ hữu ích cho bạn. Dù sao, tôi cảm ơn bạn đã đọc và hy vọng sẽ gặp bạn trên blog của Dịch vụ kế toán tại Hà Nội – Ketoanmvb vào tuần tới!