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