DATA VALIDATION ĐỘNG – TẠO DANH SÁCH PHỤ THUỘC VÀO DANH SÁCH KHÁC

Home / Excel / DATA VALIDATION ĐỘNG – TẠO DANH SÁCH PHỤ THUỘC VÀO DANH SÁCH KHÁC

Như các bạn đã biết ,chức năng danh sách lựa chọn (List) sẵn có trong Excel đã quá quen thuộc với mọi người nên hôm nay Coffee Excel xin giới thiệu thêm thủ thuật Data Validation động để có thể tạo ra  một danh sách thứ hai phụ thuộc vào lựa chọn tạo ra bởi danh sách thứ nhất.

Data Validation độ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.

Data Validation động

Để 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ô.

Data Validation động

Ở đâ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.

Data Validation động

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])

Giải thích: sử dụng hàm OFFSET để tạo ra  một vùng tham khảo linh hoạt, nghĩa là danh sách sẽ tự động cập nhật nếu bạn bổ sung thêm dữ liệu vào bảng ban đầu.

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)

Data Validation động

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 rồi !

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ỗi người !

Tác giả: Danh Nguyễn from Coffee Excel

Quà tặng từ Coffee Excel: Excel for CEO
BIỂU ĐỒ TRONG EXCEL VÀ TOP THÁC TÁC AI CŨNG CẦN BIẾT (PHẦN 1)

Professionals in Finance, Accounting, Auditing. A financial, accounting, auditing Excel lovers.