Data Validation Nâng Cao Tạo List Có Điều Kiện: Chỉ Được Nhập Dữ Liệu Tăng Dần/ Giảm Dần

Data Validation Nâng Cao Tạo List Có Điều Kiện Là Bội Số Của Một Số Hoặc Tăng Dần Theo Thứ Tự

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:

Để sử dụng chức năng Data Validation, bạn cần chọn các ô cần xác thực dữ liệu, vào tab Data trên thanh Ribbon >> Chọn Data Validation 2 lần để mở hộp thoại

 

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.

Sử dụng data validation nâng cao kết hợp hàm MOD Để Điều Kiện Số Được Nhập Phải Là Bội Số Của 1 Số

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 đó).

Điều Kiện DATA VALIDATION Để Nhập Ngày Tháng Không Nhỏ Hơn Dữ Liệu Đã Nhập 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:

Excel báo lỗi khi nhập dữ liệu chưa được xác thực theo Data Validation

 

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:

nhập dữ liệu sẽ đảm bảo được xác thực như yêu cầu của Data Validation

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

Pivot Table Nâng Cao

Cách Xóa Dòng Trống Trong Excel

Leave a Comment