Trong bài viết Data Validation Trong Excel, hoctin.vn đã giới thiệu về cách tạo menu xổ xuống cơ bản trong Excel.
Bài viết này sẽ hướng dẫn các bạn cách sử dụng Data Validation nâng cao để tạo list có điều kiện là bội số của 1 số hoặc list được nhập tăng dần trong Excel bằng kết hợp các hàm MOD, OFFSET.
Để tạo ứng Data Validation nâng cao vào việc xác thực dữ liệu có điều kiện Custom, cùng ôn lại cách tạo Data Validation (đã được mình nêu ở bài viết trước) bằng cách chọn vùng dữ liệu sẽ nhập và chọn chức năng trên tab Data:
Các ví dụ dưới đây về cách viết điều kiện để dữ liệu là bội số của 1 số (ví dụ số 4) và điều kiện để dữ liệu nhập trong dòng dưới không được nhỏ hơn dữ liệu cùng cột nằm ở dòng trước đó.
1, Ví Dụ Data Validation Nâng Cao: Điều Kiện Số Được Nhập Phải Là Bội Số Của 1 Số
Bạn cần viết hàm =MOD(A4,4)=0 để ô A4 chia hết cho 4. Các ô sau A5, A6,… sẽ nhảy công thức tương ứng.
2, Ví Dụ Data Validation Nâng Cao: Điều Kiện Để Nhập Ngày Tháng Không Nhỏ Hơn Dữ Liệu Đã Nhập Trước Đó
Bạn cần nhập công thức sau vào vùng ô cần nhập dữ liệu:
Lưu ý: ô A4 là ô đầu tiên nhập ngày tháng năm không cần cho vào vùng Data Validation vì không có điều kiện cụ thể nào với ô đầu tiên (điều kiện chỉ bắt đầu từ ô thứ 2 là ô sau không được lớn hơn ô trước đó).
Chắc hẳn các bạn thấy lạ tại sao hoctin.vn không đặt start date là A4 luôn thay vì phải sử dụng cấu trúc hàm:
=OFFSET(reference, rows, cols, height, width)
Nguyên nhân vì nếu chỉ đặt Start date là 1 ô ngay trên ô cần nhập thì khi bạn nhập dữ liệu ngày tháng tiếp theo cách đó vài ô trống, chức năng Data Validation của bạn sẽ không có tác dụng gì cho việc ràng buộc dữ liệu có điều kiện.
Việc áp dụng công thức kết hợp hàm OFFSET giúp Excel báo lỗi khi bạn nhập cách dòng như sau:
Giải thích ý nghĩa hàm OFFSET($A$4,0,0,ROW(A5)-4,1):
- Reference là $A$$ được cố định là điểm làm mốc.
- Rows = 0, Cols = 0: Số hàng/cột di chuyển từ điểm làm gốc ($A$4).
-
Height = ROW(A5)-4; Width =1: Chiều cao, tính bằng số hàng và chiều rộng, tính bằng số cột, mà bạn muốn tham chiếu trả về.
Height được viết là công thức hàm trong khi Width cố định bằng 1 vì chiều cao sẽ thay đổi tương ứng, ví dụ tại ô A6 thì phạm vi trả về là ROW(A6)-4= 2 dòng và 1 cột bắt đầu từ A4 >> là $A$4:A5; tại ô A8 thì phạm vi là 4 dòng và 1 cột >> là $A$4:A7.
Hàm OFFSET($A$4,0,0,1,1) trả về một phạm vi tham chiếu tính từ ô $A$4 (vị trí bắt đầu nhập ngày đầu tiên) đến ô liền kề trước ô đặt công thức.
Hoctin.vn thêm hàm MAX kết hợp hàm OFFSET để thứ tự ô cần nhập phải được sắp xếp tăng dần. Khi đó, việc nhập dữ liệu sẽ đảm bảo được xác thực như yêu cầu:
Trên đây là 1 số ví dụ Data Validation Nâng Cao Trong Excel.
Hi vọng bài viết của hoctin.vn đã mang lại cho các bạn các thông tin hữu ích về các cách xác thực dữ liệu đầu vào trong Excel với Data Validation.
Ngoài ra, để ứng dụng hiệu quả Excel, bạn cần phải sử dụng tốt các hàm, các công cụ khác của Excel, hãy cùng đón đọc series bài viết ứng dụng các hàm Excel:
Sử Dụng Pivot Table Để Phân Tích Dữ Liệu
Cách Xóa Dòng Trống Trong Excel