BREAK dungbv7.

Bài này cảm hứng bất chợt nên em viết Tiếng Việt nhé các bác :)) có thời gian sẽ dịch qua Tiếng Anh sau ạ :))

1. Hiểu yêu cầu nghiệp vụ

Một trong những công việc đầu tiên mình tiếp xúc khi đi làm là xử lý dữ liệu. Mình vẫn nhớ cảm giác sướng run người vầng trán ướt mồ hôi mỗi khi mầy mò được một cái gì đó mới trong excel :))

Hôm nay tình cờ có một người hỏi mình về đoạn tạo Data Validation trên file template excel mình đã làm từ hồi 2018, mình nghĩ rằng có lẽ nên viết ra để cho những người chưa biết có thể nắm được dễ dàng hơn.

Data Validation sử dụng để làm gì?

Đúng như tên gọi, tính năng Data Validation trong excel dùng để kiểm soát dữ liệu được nhập trên các worksheet và hiển thị thông báo lỗi (nếu muốn).

Ví dụ mình có một bảng dữ liệu nhân viên và mình muốn cột Mã nhân viên sẽ chỉ nhận dữ liệu dạng số thôi, mình có thể sử dụng Data Validation để hiển thị thông báo như sau mỗi khi người dùng nhập sai:

Tuy nhiên ứng dụng phổ biến nhất của Data Validation lại là được dùng như một công cụ gợi ý nhập liệu, tác dụng là để người dùng có thể biết được danh mục các giá trị cho phép nhập vào ở một ô nhất định:

Dynamic dependent validation tức là sao?

Không vòng vo nữa, nội dung chính của bài viết hôm nay là hướng dẫn cách tạo ra cơ chế năng động khi tương tác với nhiều cột dữ liệu mà bộ danh mục của chúng có sự liên kết với nhau.

Ví dụ rất nhiều template excel của BHXH có chứa các 3 cột Tỉnh/TP, Quận/Huyện và Xã/Phường. Bài toán đặt ra là phải thiết kế được file excel sao cho khi người dùng chọn Tỉnh Phú Thọ thì ở ô Quận/Huyện chỉ hiển thị ra danh mục các Quận/Huyện của Phú Thọ mà thôi, tương tự áp dụng đối với cột Phường/Xã:

2. Hiểu cấu hình phần mềm

Trước tiên ta cùng thiết kế Data Validation cho một cell để nhập tên Tỉnh/TP

a. Version lớp mầm

Liệt kê tên các Tỉnh/TP vào ô Source:

Kết quả đã nhìn thấy dropdown list khi điền cell Tỉnh/TP làm việc:

b. Version lớp chồi

Thay thế danh sách liệt kê thủ công ở trên bằng địa chỉ các cell chứa giá trị Tỉnh/TP:

Ưu điểm là khi cần chỉnh sửa bộ danh mục thì thao tác dễ dàng hơn, khi cần thêm một hạng mục mới vào danh sách thì có thể thêm vào giữa danh mục cũ:

Tự động danh mục data validation được cập nhật:

Lưu ý: Thêm hạng mục mới vào cuối danh sách thì không tự động nha

c. Version lớp lá

Bôi đen danh sách các Tỉnh/TP rồi đặt tên cho range này, ví dụ _city

Điền _city vào mục Source của Data Validation

Ưu điểm giống lớp mầm, được cái ô Source nhìn gắn gọn VIP pro hơn

d. Version tiểu học

Bôi đen toàn bộ các cột trong bộ danh mục Tỉnh/TP sau đó đặt tên table, ví dụ city

Điền =INDIRECT("city[Name]") vào mục Source của Data Validation

Ưu điểm là 1 lần đặt tên table có thể tạo Data Validation cho bất kỳ cột nào trong table đó, đồng thời khi phát sinh hạng mục mới có thể thêm vào cuối danh sách cũ thoải mái:

Cơ chế table sẽ tự động mở rộng khi có dữ liệu mới được nhập vào đuôi table, name range thì không. Chính vì thế nên ta mới có sự khác biệt này.

e. Version trung học cơ sở

Đến với cấp độ sơ trung chúng ta sẽ làm quen với dependent list, tức là xử lý Data Validation cho cell Quận/Huyện sao cho tương tác được với cell Tỉnh/TP.

Trước hết để thuận tiện ta tạo ra một cột Mã Tỉnh/TP và đặt công thức cho nó lấy giá trị từ table đã tạo bên trên ra:

Lưu ý: Ở đây sử dụng XLOOKUP bởi vì trong table danh mục thì cột ID nằm về bên trái của cột Name, nếu bạn không quen XLOOKUP thì chịu khó đổi cột ID qua bên phải và dùng VLOOKUP như bình thường 😅

Tiếp theo chúng ta tạo công thức Data Validation cho cell Quận/Huyện như sau: =INDIRECT("_"&E2)

Khi bấm Enter hệ thống sẽ báo lỗi, bởi vì lúc này name range _10 chưa tồn tại, tạm thời cứ bấm Ok

Công việc tiếp theo là tạo name range _01 cho Hà Nội, _02 cho Hà Giang, _04 cho Cao Bằng,...

Excel có hỗ trợ việc tạo name range tự động, nhưng trước tiên bạn phải đưa được table lookup về dạng như sau:

Ở đây ta bôi đen toàn bộ phần dữ liệu từ mã Tỉnh/TP cho đến hết danh sách tên Quận/Huyện rồi bấm Create from Selection để tạo name range tự động

Sau khi tạo xong bạn có thể bấm vào nút Name Manager để kiểm tra danh sách các name range đã tạo, bạn có thể để ý các name range được tạo tự động đều có tiền tố _ ở đầu

Lúc này khi bấm chọn cell Quận/Huyện bạn đã nhìn thấy danh sách các giá trị có thể chọn

f. Version trung học phổ thông

Nhược điểm của version sơ trung:

• Phải biết cách xoay dọc bảng mapping Tỉnh/TP và Quận/Huyện, không phải ai cũng biết làm
• Tạo ra quá nhiều name range, 63 name range cho 63 Tỉnh/TP và nếu tiếp tục làm cho Quận/Huyện thì tổng cộng sẽ có 768 name range tất cả

Để khắc phục nhược điểm đó, chúng ta đến với cấp độ cao trung 😉

Ý tưởng ở đây là chúng ta vẫn sẽ sử dụng công thức INDIRECT trong phần Source của Data Validation, tuy nhiên phải làm thế nào đó mà không còn phụ thuộc vào name range nữa

Để ý kỹ một chút ta sẽ thấy nếu ta sort table danh mục và thêm vào 1 cột số thứ tự thì sẽ có thể dùng INDIRECT như sau:

Danh mục các Quận/Huyện của Cần Thơ sẽ nằm ở cột B, từ dòng số 663 đến 671, tương ứng số thứ tự từ 662 đến 670, tương tự đối với Hậu Giang.

Vậy hiện giờ ta có 2 nhiệm vụ

• Một là làm sao để tìm ra 2 con số xác định dòng bắt đầu và dòng kết thúc của khối danh mục cần lấy ra
• Hai là làm sao để đưa công thức INDIRECT này vào phần Source của Data Validation mà đảm bảo vẫn chạy ok cho tất cả các dòng trong báo cáo

Đầu tiên ta xử lý vấn đề con số

Nếu phiên bản excel của bạn nhỏ hơn 365 thì có thể dùng bộ 2 công thức sau đây, VLOOKUP để tìm ra dòng đầu tiên xuất hiện Tỉnh Lào Cai, COUNTIF để đếm số lần xuất hiện. Kết quả tìm ra với Tỉnh Lào Cai sẽ cần show ra trong Data Validation các giá trị từ dòng số 67 đến 75:

Nếu bạn dùng excel 365 thì có thể dùng hàm FILTER, trông gọn nhẹ hơn khá nhiều:

Tiếp theo là công thức Data Validation

Để đưa công thức vào Data Validation và dùng chung cho tất cả các dòng trong báo cáo, ta cần loại bỏ hết những tham chiếu chính xác đi, ví dụ như trong công thức tìm max min thì cần loại bỏ cụm D2

Giải pháp là biến báo cáo danh sách nhân viên của ta thành một table

Khi đó công thức max min sẽ biến thành như sau, cụm D2 đã được thay thế thành employee[@[Tỉnh/TP làm việc]], đây là cách mà table tham chiếu đến giá trị trong cột Tỉnh/TP làm việc của dòng hiện tại

Để nâng cao tính thẩm mỹ, chúng ta sẽ đưa 2 công thức max min này vào 2 name range.

Công thức INDIRECT cuối cùng để đưa vào phần Source của Data Validation như sau:

=INDIRECT("District!B"&dis_min&":B"&dis_max)

Kết quả khi chọn Quận/Huyện cho dòng Tỉnh Lào Cai:

Khi chọn Quận/Huyện cho dòng Tỉnh Tuyên Quang:

Thành công mỹ mãn 😍

Cuối cùng ta làm tương tự với cột Phường/Xã.

Lưu ý: Công thức max min của Phường/Xã sẽ hơi khác do cần look up theo 2 điều kiện, ơn giời hàm FILTER thần thánh vẫn hỗ trợ xử lý đa điều kiện

Cuối cùng số lượng name range chúng ta phải tạo mới chỉ vỏn vẹn 4 cái, quá ư là ngon bổ rẻ 🤣

Nếu như sau này cần update bộ danh mục thì ta chỉ cần làm việc đơn giản là paste thêm các dòng mới vào đuôi table lookup và sort lại là xong.

Ok vậy là đủ cho hôm nay rồi. Hẹn các bạn vào một hôm khác tôi sẽ viết tiếp về Data Validation nhưng ở phía nâng cao trải nghiệm người dùng, ví dụ cho phép search được ở danh mục Tỉnh/TP đang lựa chọn.

Cám ơn vì đã đọc đến đây!

May the best solution be with you