Chức năng danh sách lựa chọn (list) trong Excel rất quen thuộc với nhiều người. Tuy nhiên, rất ít người biết cách sử dụng Data Validation động để tạo list phụ thuộc vào một list khác.
Bài viết dưới đây sẽ hướng dẫn chi tiết cách tạo list phụ thuộc vào list khác một cách nhanh chóng.
Ở đây ta có danh sách các tỉnh cùng tên các bệnh viện thuộc tỉnh đó. Yêu cầu đặt ra tạo ra 2 danh sách mà khi ta chọn lựa tỉnh ở danh sách thứ nhất thì danh sách thứ hai sẽ cho ra các bệnh viện của tỉnh đó tương ứng.
Lưu ý, một tỉnh có nhiều các bệnh viện và danh sách này phải được sắp xếp (sorted) để đảm bảo các giá trị giống nhau (tỉnh) nằm kế nhau kèm với tên bệnh viện.
Để tạo ra danh sách cha (parent list) chuẩn là tên các tỉnh/thành phố không bị trùng thì ta cần copy toàn bộ tên tỉnh/thành phố ra 1 sheet khác và sử dụng chức năng “bỏ giá trị trùng” của Excel (Data -> Remove duplicates) , tạo ra tên các tỉnh không trùng từ nguồn dữ liệu thô.
Ở đây ta đã có một danh sách cha (parent list) gồm tên các tỉnh, sử dụng chức năng List trong Data Validation mà các bạn đã biết mà mình sẽ không nhắc lại cách làm nữa mà tập trung hướng dẫn cách liên kết hai danh sách cha-con lại với nhau.
Để thực hiện có hai cách, là dùng Range Name để gán vùng dữ liệu các bệnh viện cho tỉnh đó hoặc là dùng công thức, ở đây mình sẽ dùng cách dùng công thức còn Range Name sẽ giới thiệu ở một bài viết khác.
Công thức như sau: Ở ô E3, chọn Tab Data -> Data Validation, bảng chức năng hiện lên, mục Source , gõ công thức:
=OFFSET(‘Benh vien’!$A$2,MATCH($C$3,’Benh vien’!$A:$A,0)-2,1,COUNTIF(‘Benh vien’!$A:$A,$C$3),1)
=OFFSET(reference, rows, cols, [height], [width])
HÀM OFFSET
Hàm này để xác định một vùng linh hoạt, thay đổi tùy thuộc vào các dòng và cột được xác định.
Trong ví dụ trên, hàm OFFSET sẽ giúp bạn tự động giới hạn và cập nhật Danh sách ngắn gọn “Bệnh viện thuộc tỉnh” nếu bạn đã chọn “Tỉnh”.
+ Reference: Điểm bắt đầu của vùng dữ liệu , chúng ta bắt đầu từ ô A2 trong sheet bệnh viện
+ Rows: sử dụng hàm MATCH để tìm dòng đầu tiên của tỉnh được lựa chọn ở danh sách tỉnh (danh sách cha) . Ô C3 được lựa chọn chứa tên tỉnh cần tìm kiếm danh sách bệnh viện. Công thức này để tìm kiếm dòng đầu tiên cho ra kết quả phù hợp với tỉnh được lựa chọn. Trừ đi 2 cho 1 ô trống A1 và tiêu đề A2.
+ Cols: Ghi giá trị “1” để di chuyển một cột qua bên phải. Công thức tìm kiếm và lấy các giá trị của cột B ( tên bệnh viện) tương ứng với cột A (tên tỉnh)
+ Heights: sử dụng COUNTIF để đếm số lần xuất hiện của tên tỉnh trong danh sách nguồn . Ví dụ: tỉnh YENBAI trả về 4, nghĩa là vùng dữ liệu sẽ là cao 4 dòng,
+ Width: Vùng dữ liệu rộng 1 cột (cột B)
Bây giờ thì danh sách các bệnh viện đã phụ thuộc vào lựa chọn của bạn ở danh sách tỉnh và khiến việc lựa chọn bệnh viện dễ dàng hơn nhờ Danh sách bệnh viện đã ngắn hơn rất nhiều.
Với hướng dẫn trên bạn có thể áp dụng cho phù hợp với đặc thù công việc của mình.
<Nguồn: Coffee Excel>
Xem thêm
Khóa học thực hành Excel cho Kiểm toán
Khóa học Dashboard Reporting
Khóa học VBA
Leave us a Reply