Danh sách thả xuống Excel với siêu liên kết

Thêm danh sách thả xuống vào trang tính Excel của bạn là một cách thực sự tuyệt vời và thuận tiện để cho phép nhập dữ liệu vào trang tính Excel của bạn trong khi vẫn duy trì dữ liệu nhất quán có chất lượng tốt và giảm dung lượng chiếm dụng bởi danh sách. Nếu bạn muốn xem lại cách tạo danh sách thả xuống để xác thực dữ liệu, hãy xem bài đăng trên blog của tôi tại đây. Vì vậy, nếu bạn giống tôi, bạn có thể lưu trữ rất nhiều siêu liên kết trong Excel không?. Chúng có thể dành cho các báo cáo, trang web, nguồn dữ liệu… bạn hiểu ý. Tôi có tất cả các trang web của mình trong một trang tính thường mở mọi lúc

Vì vậy, tôi sẽ chỉ cho bạn cách sử dụng cùng một danh sách xác thực thả xuống để lưu trữ các siêu liên kết Excel và giảm dung lượng chiếm dụng trên trang tính của bạn. [Không gian bất động sản có giá trị cao trên một trang tính, bạn có đồng ý không?] Chúng tôi cũng sẽ chèn một ô có thể nhấp để đưa bạn đến ngay đích siêu liên kết

Nó rất đơn giản để làm. Đây là ví dụ của tôi. Tôi có một phạm vi được đặt tên là My_Links. Chúng được lưu trữ trên Sheet2 bắt đầu từ ô A1

Chúng tôi sẽ tạo một danh sách thả xuống động từ nó

Thực hiện theo các bước dưới đây để sử dụng phương pháp

bước

• Trước hết, hãy chọn một ô mà bạn muốn tạo danh sách [i. e. D5] và chuyển đến tab Dữ liệu-> nhấp vào Xác thực dữ liệu

• Sau đó, hộp thoại Xác thực dữ liệu sẽ xuất hiện
• Sau đó, chọn “Danh sách” làm tiêu chí xác nhận
• Trong trường nguồn, hãy áp dụng hàm OFFSET cùng với hàm COUNTIF. Chèn các giá trị và công thức cuối cùng là

=OFFSET[$B$4,0,0,COUNTIF[$B$4:$B$100,””]]

Ở đâu,

• Tham chiếu là $B$5
• Hàng và Cột là 0
• [chiều cao] là COUNTIF[$B$5. $B$100,””]

• Sau đó, nhấp vào OK để nhận danh sách thả xuống

Vì vậy, chúng tôi đã có danh sách thả xuống của chúng tôi

Bây giờ hãy kiểm tra xem danh sách này có được liên kết với một giá trị ô hay không. Xóa một số dữ liệu khỏi danh sách dữ liệu của bạn

Sau đó mở danh sách thả xuống và thấy rằng danh sách đã tự động cập nhật

Tương tự, nếu chúng ta thêm một số giá trị vào “Mục”, danh sách sẽ tự động thêm các giá trị này. Vì vậy, các giá trị ô được liên kết với danh sách

2. Danh sách thả xuống phụ thuộc để liên kết giá trị ô

Danh sách thả xuống phụ thuộc cũng có thể thiết lập liên kết đến các giá trị ô của chúng và tự động cập nhật. Hãy thảo luận về điều này

Xem xét ví dụ này trong đó các cột "Phần cứng" và "Phần mềm" được cung cấp cùng với một số dữ liệu. Chúng ta cần lập một danh sách phụ thuộc từ đó

bước

• Đầu tiên, chọn ô E5 và chuyển đến tab Dữ liệu -> chọn Xác thực dữ liệu từ nhóm Công cụ dữ liệu

• Trong hộp thoại xác thực dữ liệu, chọn Danh sách làm tiêu chí xác thực. Trong trường nguồn, gán giá trị =$B$4. $C$4 khi chọn “Phần cứng” và “Phần mềm”

• Bây giờ trong cửa sổ mới, bấm OK để tạo danh sách

Chúng tôi có danh sách thả xuống cho các cột. Để hoàn thành tác vụ, hãy chọn cột “Phần cứng” và “Phần mềm”, chuyển đến “Công thức” và trong “Trình quản lý tên”, nhấp vào “Tạo từ lựa chọn”

Công thức → Trình quản lý tên → Tạo từ lựa chọn

• Một cửa sổ mới hiện ra. Kiểm tra “Hàng trên cùng” và nhấp vào OK

• Và hai tên cụ thể được tạo ra. Bấm vào “Đóng”
• Bây giờ nhiệm vụ cuối cùng của chúng ta là đây. Chọn ô F4 và đi đến “Xác thực dữ liệu” và chọn “Danh sách”

• Trong ô “Source”, áp dụng hàm INDIRECT

=INDIRECT[E4]

Điều này có nghĩa là khi bạn chọn “Phần cứng” trong danh sách thả xuống [E3], điều này đề cập đến phạm vi được đặt tên là “Phần cứng” [thông qua chức năng GIÁN TIẾP] và do đó liệt kê tất cả các mục trong danh mục đó

• Và bấm OK. Danh sách thả xuống được tạo và các ô được liên kết với danh sách

• Để chứng minh rằng các giá trị ô được liên kết với danh sách, bạn có thể cộng hoặc trừ bất kỳ giá trị nào khỏi danh sách nguồn và có thể kiểm tra xem danh sách thả xuống có tự động cập nhật không

Đọc thêm. Cách tạo danh sách thả xuống phụ thuộc động trong Excel

3. Sử dụng hàm VLOOKUP để liên kết một ô

Chúng ta có thể sử dụng hàm VLOOKUP để liên kết ô với danh sách thả xuống

Trong ví dụ sau, danh sách “Tên mặt hàng” và “Giá” của chúng được đưa ra

Chúng tôi sẽ tạo một danh sách thả xuống và liên kết các giá trị ô với danh sách bằng hàm VLOOKUP

bước

• Đầu tiên, tạo danh sách thả xuống trong ô “E5” giống như Phương pháp 1 bằng cách sử dụng dữ liệu nguồn làm ô của cột “Tên mặt hàng”

• Bây giờ danh sách được thực hiện. Tại đây, hãy áp dụng hàm Vlookup trong ô “F5”. công thức là

=VLOOKUP[E4,B4:C23,2,FALSE]

Ở đâu,

• Lookup_Value là E5 [Danh sách thả xuống]
• Table_Array là B5. C22
• Col_Index_Num là 2
• Chúng tôi muốn giá trị chính xác [FALSE]

• Tiếp theo, nhấn ENTER. Hàm VLOOKUP đã liên kết các giá trị ô với danh sách

Bạn có thể thay đổi giá trị danh sách và xem liệu các giá trị ô có được liên kết hay không

4. Sử dụng hàm SUMIF để liên kết một ô

Hàm SUMIF cũng có thể liên kết các giá trị ô với danh sách

Chúng tôi sẽ sử dụng ví dụ trước. Bây giờ trong ô “F5” hãy áp dụng hàm SUMIF

Sau khi chèn giá trị, công thức là

=SUMIF[B5:B22,E5,C5:C22]

Ở đâu,

• Phạm vi là B4. B22
• Tiêu chí là E5
• [Dải_tổng] là C4. C23

Đọc thêm. Cách tạo danh sách thả xuống dựa trên công thức trong Excel

5. Danh sách thả xuống siêu liên kết để liên kết một giá trị ô

Trong phương pháp này, chúng tôi sẽ tạo một danh sách thả xuống siêu liên kết. hãy học

Ở đây, chúng tôi có hai trang tính và đặt tên cho chúng là “Phần cứng” và “Phần mềm”. Một trong số họ có dữ liệu cho “Phần cứng”

Và cái còn lại là viết tắt của dữ liệu của “Phần mềm”

bước

• Đầu tiên, tạo một trang tính khác, nơi chúng tôi sẽ tạo danh sách siêu liên kết. Tạo một cột "Danh mục" nơi bạn sẽ chèn tên trang tính mà bạn muốn liên kết với danh sách. Trong ô “E4”, tạo danh sách thả xuống “Phần cứng” và “Phần mềm”

• Chọn ô danh sách thả xuống, đi đến “Công thức” và nhấp vào “Trình quản lý tên”

• Trong cửa sổ mới xuất hiện, nhấp vào “Mới” để đặt tên mới cho ô này

• Đặt tên cho ô “E4” này là “Worksheet_Link” và nhấp vào “OK”

• Chọn ô “E4” và nhấn “CTRL+K” để tạo liên kết trong ô này. Trong cửa sổ mới, chọn “Place in the Document”, nhấp vào “Worksheet_Link” và nhấn “OK”

• Bây giờ, chúng tôi sẽ chỉnh sửa liên kết của ô này

• Chọn ô danh sách thả xuống, chuyển đến Công thức, nhấp vào Trình quản lý tên. Chọn Worksheet_Link và nhấp vào Chỉnh sửa

• Trong trường Refers to, chèn công thức sau,

=INDIRECT[ADDRESS[4,5,,,E4]]

Ở đâu,

• [ref_text] là ĐỊA CHỈ[4,5,,,E4]

Bây giờ hàm ADDRESS là

=ADDRESS[4,5,,,E4]

Ở đâu,

• Row_num là 4 cho trang tính được liên kết
• Col_Num là 5 cho trang tính được liên kết
• [abs_num], [a1] bị bỏ qua
• [sheet_text] là E4

• Nhấp vào OK. Bây giờ các trang tính được liên kết với danh sách thả xuống. Bây giờ, giả sử chúng ta muốn chuyển đến trang "Phần cứng". Chọn “Phần cứng” từ danh sách và nhấp vào nó

Và chúng tôi đang ở trong bảng tính phần cứng. Đó là cách chúng tôi tạo danh sách thả xuống siêu liên kết

Đọc thêm. Cách tạo danh sách thả xuống từ một trang tính khác trong Excel

Những điều cần ghi nhớ

⏩ Trong khi tạo danh sách thả xuống động, hãy đảm bảo rằng các tham chiếu ô là tuyệt đối [chẳng hạn như $B$4] và không tương đối [chẳng hạn như B2 hoặc B$2 hoặc $B2]

⏩ Trong danh sách thả xuống siêu liên kết, [abs_num], [a1] bị bỏ qua. Đặt [,,] để làm điều đó

⏩ Để tránh sai sót, nhớ check “Ignore Blank” và “In-cell Dropdown”

Phần kết luận

Vì vậy, chúng ta đã thảo luận năm phương pháp khác nhau để liên kết một giá trị ô với danh sách thả xuống của Excel. Danh sách thả xuống được liên kết với các giá trị ô giúp công việc của chúng tôi trở nên dễ dàng và thoải mái hơn rất nhiều. Nếu bạn có bất kỳ sự nhầm lẫn hoặc suy nghĩ nào về bài viết này, vui lòng cho chúng tôi biết trong phần bình luận