TỔNG HỢP CÁCH SỬ DỤNG DATA VALIDATION TRONG EXCEL TẠO LIST NHẬP NHANH
Data Validation trong Excel giúp cố định dữ liệu trong ô tính, giúp các thao tác thống kê tại sheet đơn giản và dễ dàng hơn. Tuy nhiên, không phải ai cũng biết cách dùng tính năng này. Nếu bạn chưa biết cách sử dụng đúng, hãy tham khảo ngay hướng dẫn sau đây!
Data Validation là gì?
Data Validation là một tính năng trong Excel được tạo ra nhằm mục đích cố định dữ liệu của các ô đã được chọn trong trang tính.
Bạn đang xem: Cách sử dụng data validation trong excel
Theo đó, người dùng chỉ có thể điền các dữ liệu được thiết lập trong ô đó. Điều này giúp hạn chế sai sót và tránh nhầm lẫn trên các phiếu điền thông tin, kiểm kê,… Dễ dàng nhận thấy, cách này giúp việc thống kê dữ liệu trong ô đơn giản và dễ dàng hơn.
Các tính năng của Data Validation bao gồm: tạo danh sách dạng sổ xuống, thiết lập thông báo kiểu dữ liệu cho ô được chọn, báo lỗi nhập sai dữ liệu
Hướng dẫn cách sử dụng Data Validation trong Excel
Bước 1: Chọn ô tính muốn tạo Data Validation. Trên thanh công cụ bạn chọn Data > Chọn Data Validation tại Data Tools.
Bước 2: Tại Settings, chọn dữ liệu cho phép nhập tại Allow và điều kiện dữ liệu trong Data. Allow gồm có:
Any Value: Không có điều kiện.Whole number: Số nguyên.Decimal: Số thực (có dấu phẩy).List: Danh sách.Date: Ngày tháng năm.Time: Thời gian.Text Length: Độ dài chuỗi.Custom: Tùy chọn.Các kiểu dữ liệu trong mục Allow
between: Giá trị nằm ở giữa khoảng nhỏ nhất (Minimum) và lớn nhất (Maximum).
not between: Giá trị nằm ở ngoài khoảng nhỏ nhất (Minimum) và lớn nhất (Maximum).
equal to: Bằng giá trị được nhập vào.
not equal to: Không bằng giá trị được nhập vào.
greater than: Lớn hơn giá trị được nhập vào.
less than: Nhỏ hơn giá trị được nhập vào.
greater than or equal to: Lớn hơn hoặc bằng giá trị được nhập vào.
less than or equal to: Nhỏ hơn hoặc bằng giá trị được nhập vào.
Bước 3: Cài đặt thông báo kiểu dữ liệu phải nhập chọn Input Message, chọn Show input message when cell is selected > Title (Tiêu đề) và Input message (Nội dung tin nhắn).
Bước 4: Cài đặ cảnh báo nhập sai kiểu dữ liệu, chọn Error Alert, chọn Show error alert after invalid data is entered > Style (Kiểu thông báo: Stop, Warning và Information) > Title (Tiêu đề cho cảnh báo) và Error message (Nội dung của lỗi).
Bước 5: Chọn OK sau khi đã cài đặt xong.
Hướng dẫn cách bỏ định dạng Data Validation
Để bỏ định dạng này, bạn chỉ cần thực hiện các thao tác sau đây:
Bước 1: Chọn ô cần bỏ định dạng. Chọn Data > Data Validation.
Bước 2: Chọn Clear All.
Như bạn nhận thấy, các thao tác thực hiện Data Validation trong Excel khá đơn giản và dễ thực hiện. Chỉ cần thực hiện theo các thao tác này, bạn chỉ tốn chút thời gian là đã hoàn thành. Bạn hãy ghi nhớ cách thực hiện và đảm bảo công việc của mình tốt nhất nhé!
Data Validation trong Excel là một tính năng tuyệt vời mà bạn cần biết. Dưới đây là chi tiết cách sử dụng Data Validation Excel.
Nhắc tới phần mềm bảng tính, chắc chắn hầu hết mọi người đều nghĩ ngay tới Microsoft Excel. Dù hiện đã có nhiều lựa chọn thay thế hữu ích không kém như Google Sheets, WPS Office Spreadsheets, Zoho Sheets…, Microsoft Excel vẫn là lựa chọn được ưu tiên hàng đầu ở các doanh nghiệp, công ty, tập đoàn lớn. Tại sao vậy? Bởi Microsoft Excel cung cấp rất nhiều tính năng tuyệt vời, dễ dùng mà các chương trình khác không có.
Bạn đang xem: Học Excel – Bài 10: Cách sử dụng Data Validation trong Excel
Ở Microsoft Excel, bạn sẽ tìm thấy rất nhiều hàm tính tổng, ngày tháng, lượng giác, logic… Tất cả giúp cho công việc xử lý thông tin, nhất là bảng dữ liệu lớn trở nên dễ dàng, chính xác và nhanh chóng hơn bao giờ hết. Một trong số những tính năng hỗ trợ xử lý dữ liệu thường được dùng nhất ở Excel là Data Validation.
Xem thêm: Hướng dẫn cách làm mục lục tự đông word 2010, 2016
Đúng như tên gọi, Data validation trong Excel giúp bạn cố định dữ liệu trong ô nào đó. Điều này giảm thiểu nhầm lẫn thông tin trong các tờ phiếu, đơn giá, kiểm kê… Dưới đây là cách dùng Data Validation trong Excel.
Nội dung chính
Cách sử dụng Data Validation trong Excel trên mac
OSOffice 2013 trở lên
Office 2011Cách sử dụng Data Validation trong Excel trên Web
Sử dụng Data Validation hữu ích khi nào?
Xác thực dữ liệu trong Excel là vô giá khi bạn muốn chia sẻ một workbook với người khác và bạn muốn nhập dữ liệu chính xác, nhất quán. Ngoài ra, Data Validation Excel còn hữu ích trong những trường hợp sau:Hạn chế mục nhập cho dữ liệu được xác định trước trong danh sách. Ví dụ, giới hạn lựa chọn bộ phận của người dùng với phòng kế toán, hành chính, nhân sự…Hạn chế số lượng nằm ngoài một phạm vi cụ thể. Ví dụ, bạn có thể chỉ định tỷ lệ phần trăm đầu vào tối đa cho mức tăng thành tích hàng năm của nhân viên, giả sử là 3% hoặc chỉ cho phép một số nguyên từ 1 đến 100.Hạn chế ngày nằm ngoài khung thời gian nhất định. Ví dụ, trong đơn xin nghỉ của nhân viên, bạn có thể thiết lập không cho phép chọn ngày nghỉ trước ngày hiện tại.Hạn chế thời gian nằm ngoài khung giờ cụ thể. Ví dụ, bạn có thể lên lịch hợp từ 8 giờ tới 17 giờ.Hạn chế số lượng ký tự văn bản. Ví dụ, bạn có thể giới hạn text trong một ô chỉ 10 ký tự trở xuống.Cách sử dụng Data Validation trong Excel trên Windows
1. Chọn ô bạn muốn tạo quy tắc.
2. Chọn Data >Data Validation.
Thêm các kiểu xác thực dữ liệu khác
Bảng sau liệt kê các loại xác thực dữ liệu khác và cách thêm nó vào worksheet.
| Nhiệm vụ | Các bước thực hiện |
| Hạn chế mục nhập dữ liệu ở số nguyên nằm trong giới hạn | Làm theo các bước 1-2 kể trên.Từ list Allow, chọn Whole number.Trong box Data, chọn loại giới hạn mong muốn. Ví dụ, thiết lập giới hạn trên & dưới, chọn between.Nhập giá trị tối thiểu, tối đa hoặc cụ thể cho phép. Bạn cũng có thể nhập công thức trả về giá trị số. Ví dụ: Bạn đang xác thực dữ liệu ở ô F1. Đặt giới hạn khấu trừ tối thiểu bằng 2 lần số con trong ô đó, chọn greater than or equal to trong box Data > nhập công thức: =2*F1 vào box Minimum. |
| Hạn chế mục nhập dữ liệu ở phân số trong giới hạn | Làm theo bước 1-2 kể trên.Trong box Allow, chọn Decimal.Trong box Data, chọn loại giới hạn bạn muốn.Nhập giá trị tối thiểu, tối đa hoặc cụ thể cho phép. Bạn có thể nhập công thức trả về giá trị số. Ví dụ: Đặt giới hạn tối đa cho tiền thưởng và hoa hồng là 6% lương của nhân viên bán hàng trong ô E1, chọn less than or equal to trong Data > nhập công thức =E1*6% vào box Maximum. Lưu ý: Để cho phép người dùng nhập phần trăm, chọn Decimal trong Allow, chọn loại giới hạn bạn muốn ở box Data, nhập giá trị tối thiểu, tối đa hoặc cụ thể dưới dạng số thập phân (ví dụ: .2), rồi hiện ô xác thực dữ liệu ở dạng phần trăm bằng cách chọn ô đó, rồi click Percent Style trong nhóm Number ở tab Home. |
| Hạn chế mục nhập dữ liệu ở ngày trong phạm vi ngày | Làm theo các bước 1-2 ở trên.Trong box Allow, chọn Date.Trong box Data, chọn loại hạn chế bạn muốn. Ví dụ, cho phép hiển thị các ngày sau một ngày cụ thể, chọn greater than.Nhập ngày bắt đầu, kết thúc cho phép. Bạn cũng có thể nhập công thức trả về ngày. Ví dụ: Đặt khung thời gian giữa ngày hôm nay và 3 ngày sau đó, chọn between trong Data, nhập =TODAY() vào box Start day, nhập =TODAY()+3 vào End date. |
| Hạn chế mục nhập dữ liệu ở thời gian trong một khung giờ | Làm theo các bước 1-2 ở trên.Trong Allow, chọn Time.Trong Data, chọn loại giới hạn. Ví dụ, cho phép thời gian trước giờ cụ thể trong ngày, chọn less than.Nhập thời gian bắt đầu, kết thúc hoặc cụ thể cho phép. Nếu muốn nhập giờ cụ thể, dùng định dạng thời gian hh:mm. Ví dụ, thiết lập ô E2 ở thời gian bắt đầu (8:00 AM) và ô F2 với thời gian kết thúc (5:00 PM). Bạn muốn giới hạn thời gian họp giữa khung giờ này, chọn between trong box Data, nhập E2 ở box Start time, nhập =F2 ở box End time. |
| Hạn chế mục nhập dữ liệu văn bản ở độ dài lựa chọn | Làm theo các bước 1-2 kể trên.Trong box Allow, chọn Text Length.Trong box Data, chọn loại giới hạn. Ví dụ, cho phép giới hạn tới số lượng ký tự nào đó, chọn less than or equal to.Trong trường hợp muốn hạn chế mục nhập tới 25 ký tự, chọn less than or equal to trong Data, rồi nhập 25 vào box Maximum. |
| Tính toán giới hạn được phép dựa trên nội dung của ô khác. | Làm theo bước 1-2 ở trên.Trong Allow, chọn loại dữ liệu bạn muốn.Trong box Data, chọn loại giới hạn bạn muốn.Trong các ô dưới box Data, click ô bạn muốn dùng để chọn dữ liệu cho phép. Ví dụ, chỉ cho phép các mục dành cho tài khoản nếu kết quả không vượt quá ngân sách trong ô E1, chọn Allow >Whole number, Data, less than or equal to, và Maximum >= =E1. |
Ví dụ các công thức trong xác thực dữ liệu ở Excel
Lưu ý:
Những ví dụ này dùng tùy chọn Custom tại nơi bạn viết công thức để đặt điều kiện. Bạn không cần lo lắng về bất cứ dữ liệu hiện trong box Data bởi nó đã bị Custom vô hiệu hóa.Ảnh chụp màn hình này ở Excel 2016 nhưng chức năng của nó tương tự Excel Online.| Đảm bảo | Công thức |
| Ô chứa ID sản phẩm (C2) luôn bắt đầu bằng tiền tố chuẩn “ID-” và dài tối thiểu 10 ký tự | =AND(LEFT(C2,3)=”ID-“,LEN(C2)>9)   Lưu ý: Bạn phải nhập công thức xác thực dữ liệu cho ô A2 trước, rồi sao chép A2 sang A3:A10 để đối số thứ 2 tới COUNTIF sẽ khớp với ô hiện tại. Cụ thể, phần A2)= sẽ thay đổi thành A3)=1, A4)=1…
|
