Để trích xuất dữ liệu cụ thể dựa trên các giá trị cụ thể, chúng tôi có thể cần sử dụng danh sách thả xuống. Hơn nữa, chúng ta cần liên kết hai hoặc nhiều danh sách thả xuống phụ thuộc . Trong bài viết này, chúng tôi sẽ chỉ cho bạn cách trong Excel để thay đổi danh sách thả xuống dựa trên giá trị ô.
2 cách thích hợp để thay đổi danh sách thả xuống dựa trên giá trị ô trong Excel
Trong các phần bên dưới, chúng tôi sẽ nhấn mạnh 2 các cách phù hợp nhất để thay đổi danh sách thả xuống. Đầu tiên , chúng tôi sẽ áp dụng OFFSET và MATCH các chức năng trong danh sách thả xuống để thực hiện các thay đổi dựa trên giá trị ô. Ngoài ra , chúng tôi sẽ sử dụng XLOOKUP chức năng nổi bật trong Microsoft Excel 365 để làm cái tương tự. Trong hình ảnh dưới đây, chúng tôi đã cung cấp một tập dữ liệu mẫu để hoàn thành nhiệm vụ.
1. Kết hợp các hàm OFFSET và MATCH để thay đổi danh sách thả xuống dựa trên giá trị ô trong Excel
Trong tập dữ liệu sau đây của chúng tôi, chúng tôi có ba nhân viên bán hàng khác nhau với các sản phẩm đã bán của họ. Bây giờ, chúng tôi muốn tìm các sản phẩm cho một nhân viên bán hàng cụ thể. Để làm như vậy, hãy làm theo các bước bên dưới.
Bước 1:Tạo danh sách xác thực dữ liệu
- Bắt đầu vào Dữ liệu.
- Nhấp trên Xác thực dữ liệu .
Bước 2:Chọn nguồn cho Danh sách
- Từ Cho phép , hãy chọn Danh sách.
- Trong nguồn hộp, chọn dải nguồn E4:G4 cho tên của những người bán hàng.
- Nhấn Enter .
- Do đó, trình đơn thả xuống sẽ xuất hiện trong ô B5 .
Bước 3:Áp dụng chức năng OFFSET
- Nhập công thức sau cho OFFSET chức năng,
=OFFSET($E$4) - Đây, E4 là tài liệu tham khảo ô ở dạng tuyệt đối.
- Trong hàng đối số, đặt 1 là giá trị sẽ được tính là 1 hàng xuống từ ô tham chiếu E4 .
=OFFSET($E$4,1
Bước 4:Sử dụng hàm MATCH để xác định cột hàm OFFSET
- Trong cols , để chọn các cột, hãy sử dụng MATCH với công thức sau.
=OFFSET($E$4,1,MATCH($B$5 - Đây, B5 là giá trị ô được chọn trong danh sách thả xuống.
- Để chọn lookup_array đối số cho MATCH chức năng, thêm E4:G4 dưới dạng phạm vi ở dạng tuyệt đối với công thức sau.
=OFFSET($E$4,1,MATCH($B$5,$E$4:$G$4
- Nhập 0 cho Chính xác Loại so khớp. Công thức sau sẽ trả về 3 cho MATCH
MATCH($B$5,$E$4:$G$4,0)
- Viết trừ 1 ( -1 ) từ MATCH vì OFFSET hàm đếm cột đầu tiên là không ( 0 ).
MATCH($B$5,$E$4:$G$4,0)-1
Bước 5:Nhập chiều cao của các cột
- Để chọn 1 ở chiều cao đối số, nó sẽ tính rằng mỗi cột có một giá trị.
=OFFSET($E$4,1,MATCH($B$5,$E$4:$G$4,0)-1,1
Bước 6:Nhập Giá trị chiều rộng
- Đối với chiều rộng đối số, nhập 1 .
=OFFSET($E$4,1,MATCH($B$5,$E$4:$G$4,0)-1,1,1)
- Do đó, bạn sẽ thấy điều đó khi chúng tôi chọn Jacob trong B5 , nó sẽ dẫn đến Sô cô la là phần tử đầu tiên cho Jacob .
Bước 7:Đếm các phần tử của mỗi cột
- Để đếm số phần tử trong một cột, chúng tôi sẽ áp dụng COUNTA hàm trong ô C13 với công thức sau.
=COUNTA(OFFSET($E$4,1,MATCH($B$5,$E$4:$G$4,0)-1,10))
- Điều này sẽ tính phần tử / sản phẩm số cho một nhân viên bán hàng cụ thể ( Jacob ).
Bước 8:Nhập giá trị ô chiều cao đếm làm đối số chiều cao trong hàm OFFSET
- Viết công thức sau để thêm chiều cao.
=OFFSET($E$4,1,MATCH($B$5,$E$4:$G$4,0)-1,C13,1)
Bước 9:Sao chép công thức
- Nhấn Ctrl + C để sao chép công thức.
=OFFSET($E$4,1,MATCH($B$5,$E$4:$G$4,0)-1,C13,1)
Bước 10:Dán công thức
- Dán công thức vào Xác thực dữ liệu nguồn.
=OFFSET($E$4,1,MATCH($B$5,$E$4:$G$4,0)-1,C13,1)
- Cuối cùng, nhấn Enter để xem sự thay đổi.
- Do đó, các giá trị danh sách thả xuống của bạn sẽ thay đổi dựa trên một giá trị ô khác.
- Thực hiện thay đổi giá trị ô Bryan tới Juliana và lấy tên sản phẩm được bán bởi Juliana .
Đọc thêm: Cách tạo danh sách từ phạm vi trong Excel (3 phương pháp)
Bài đọc tương tự
- Cách tạo danh sách thả xuống phụ thuộc với nhiều từ trong Excel
- 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
- 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
- Tạo Bộ lọc Excel bằng Danh sách Thả xuống Dựa trên Giá trị Ô
- Cách Thêm Mục vào Danh sách Thả xuống trong Excel (5 Phương pháp)
2. Sử dụng hàm XLOOKUP để thay đổi danh sách thả xuống dựa trên giá trị ô trong Excel
Nếu bạn may mắn với Microsoft 365 , bạn có thể thực hiện chỉ với một công thức của XLOOKUP hàm số. Thực hiện theo các bước được nêu bên dưới để làm như vậy.
Bước 1:Tạo danh sách xác thực dữ liệu
- Từ Xác thực dữ liệu , hãy chọn Danh sách.
Bước 2:Nhập phạm vi nguồn
- Chọn dải nguồn E4:G4 trong hộp nguồn.
- Sau đó, nhấn Enter .
- Do đó, Xác thực dữ liệu danh sách sẽ xuất hiện.
Bước 3:Chèn hàm XLOOKUP
- Chọn B5 ô dưới dạng look_up.
=XLOOKUP(B5)
Bước 4:Chọn lookup_array
- Viết phạm vi E4:G4 dưới dạng look_array .
=XLOOKUP(B5, E4:G4)
Đọc thêm: 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ước 5:Chèn return_array
- Loại phạm vi cho lợi nhuận giá trị E5:G11 .
- Do đó, các sản phẩm sẽ trả lại theo một nhân viên bán hàng cụ thể .
- Bây giờ, hãy chọn bất kỳ tên nào từ danh sách thả xuống và lấy tên của sản phẩm.
Ghi chú. Hãy xem kỹ, điều đó trong hình ảnh trên không được hiển thị như trong phạm vi các ô trống . Đó là lý do tại sao chúng được coi là không . Để xóa số không làm theo các bước bên dưới.
Đọc thêm: Cách thêm tùy chọn trống vào danh sách thả xuống trong Excel (2 phương pháp)
Bước 6:Áp dụng hàm UNIQUE
- Nhập công thức sau được lồng bằng UNIQUE.
=UNIQUE(XLOOKUP(B5,E4:G4,E5:G11),,TRUE)
- Cuối cùng, bạn sẽ nhận được kết quả như mong muốn.
Đọc thêm: Giá trị duy nhất trong danh sách thả xuống với VBA trong Excel (Hướng dẫn đầy đủ)
Kết luận
Cuối cùng, tôi hy vọng bây giờ bạn hiểu cách cập nhật danh sách thả xuống trong Excel dựa trên giá trị ô. Tất cả các chiến lược này nên được thực hiện khi dữ liệu của bạn đang được đào tạo và thực hành. Kiểm tra sách thực hành và áp dụng những gì bạn đã học được. Chúng tôi được thúc đẩy để tiếp tục cung cấp các chương trình như thế này vì sự hỗ trợ hào phóng của bạn.
Nếu bạn có bất kỳ câu hỏi nào, xin vui lòng liên hệ với chúng tôi. Hãy chia sẻ suy nghĩ của bạn trong phần bình luận bên dưới.
Exceldemy nhân viên sẽ liên hệ lại với bạn trong thời gian sớm nhất.
Ở lại với chúng tôi và tiếp tục học hỏi.
Các bài viết liên quan
- Cách sao chép danh sách thả xuống của bộ lọc trong Excel (5 cách)
- VBA để Chọn Giá trị từ Danh sách Thả xuống trong Excel (2 Phương pháp)
- Cách tạo danh sách thả xuống có giá trị duy nhất trong Excel (4 phương pháp)
- Danh sách thả xuống của Excel không hoạt động (8 vấn đề và giải pháp)
- Danh sách thả xuống của Excel tùy thuộc vào lựa chọn
- Cách Tạo Danh sách Thả xuống trong Excel (Độc lập và Phụ thuộc)
- Tự động cập nhật danh sách thả xuống trong Excel (3 cách)