Bao giờ bạn tự hỏi làm thế nào để thiết lập danh sách thả xuống phụ thuộc trên mỗi hàng trong Excel? Tôi đã đề cập đến điều này trong vài bài viết và thậm chí đã tạo một danh sách phát đặc biệt về nó. Một câu hỏi phổ biến mà tôi thường nhận được là làm thế nào để tạo nhiều danh sách xác nhận dữ liệu phụ thuộc trên cùng một bảng tính. Điều này có thể phức tạp, nhưng tôi sẽ hướng dẫn bạn một cách sử dụng chỉ một công thức để hoàn thành công việc.
Thiết Lập Danh Sách Thả Xuống Cho Phòng Ban
Bắt đầu bằng cách tạo một danh sách thả xuống cho cột “Division”. Việc này khá đơn giản. Điều hành tới tab “Dữ Liệu”, nhấp vào “Xác Nhận Dữ Liệu”, chọn “Danh Sách” làm nguồn, và chọn các tùy chọn về phòng ban. Bây giờ, bạn có thể dễ dàng chọn phòng ban cho từng hàng.
Tạo Danh Sách Thả Xuống Phụ Thuộc Cho Ứng Dụng
Đây là phần khó khăn – tạo một danh sách thả xuống phụ thuộc cho cột “Ứng Dụng” dựa trên phòng ban đã chọn. Trong ví dụ này, các phòng ban được liệt kê ở trên cùng, và các ứng dụng tương ứng được liệt kê ngay bên cạnh chúng. Thông thường, bộ dữ liệu này sẽ ở trên một tab riêng hoặc ẩn đi.
Sử Dụng Công Thức Offset
Để đạt được điều này, tôi sẽ sử dụng hàm OFFSET. Trước khi áp dụng nó, tôi sẽ viết công thức để đảm bảo nó hoạt động đúng. Hàm OFFSET cần một điểm tham chiếu, số hàng cần di chuyển xuống, số cột cần di chuyển sang phải, chiều cao và chiều rộng.
Thiết Lập Công Thức OFFSET
- Bắt đầu với điểm tham chiếu (tiêu đề phòng ban).
- Di chuyển xuống một hàng (do các ứng dụng bắt đầu dưới điểm tham chiếu).
- Xác định cột dựa trên phòng ban đã chọn bằng cách sử dụng hàm MATCH.
- Điều chỉnh vị trí cột bằng cách trừ đi 1 từ kết quả của MATCH (do OFFSET bắt đầu từ 0).
- Đặt chiều cao cố định, ban đầu là 15 (hoặc một số dựa trên giá trị tối đa kỳ vọng của bạn).
Xử Lý Kích Thước Danh Sách Động
Để tránh các ô trống trong danh sách thả xuống, chúng ta cần điều chỉnh chiều cao một cách động dựa trên số ứng dụng cho mỗi phòng ban. Sử dụng cùng một công thức OFFSET để đếm giá trị văn bản trong phạm vi ứng dụng cho phòng ban đã chọn.
Hoàn Thiện Công Thức Động
- Bắt đầu với điểm tham chiếu (tiêu đề phòng ban).
- Di chuyển xuống một hàng.
- Sử dụng hàm MATCH để xác định cột dựa trên phòng ban đã chọn.
- Điều chỉnh vị trí cột bằng cách trừ đi 1 từ kết quả của MATCH.
- Sử dụng công thức COUNTA để định động chiều cao (số ứng dụng) cho danh sách thả xuống.
- Đặt chiều rộng cố định là 1.
Bây giờ bạn đã có một giải pháp linh hoạt và hiệu quả cho việc tạo nhiều danh sách thả xuống phụ thuộc trên mỗi hàng trong Excel. Copy và dán công thức xác nhận dữ liệu, và bạn đã hoàn tất!
Nguồn :https://www.youtube.com/watch?v=7mo4COng7Sg
Biên Tập: thuthuatviet.org
