Hướng dẫn clear data validation in excel - xác thực dữ liệu rõ ràng trong excel
Chào Mike, Show
Chọn cột đó, sau đó chuyển đến dữ liệu >> Công cụ dữ liệu >> Xác thực dữ liệu Nếu một hộp tin nhắn bật lên như được hiển thị trong ảnh chụp màn hình bên dưới, nhấp vào số Sau đó, nhấp vào Xóa tất cả nút và sau đó nhấn OK.Clear All button and then hit OK.
Hướng dẫn giải thích cách thực hiện xác thực dữ liệu trong Excel: Tạo quy tắc xác thực cho các số, ngày hoặc giá trị văn bản, tạo danh sách xác thực dữ liệu, sao chép xác thực dữ liệu vào các ô khác, tìm các mục nhập không hợp lệ, sửa và xóa xác thực dữ liệu. Khi thiết lập sổ làm việc cho người dùng của bạn, bạn có thể thường muốn kiểm soát thông tin nhập vào các ô cụ thể để đảm bảo tất cả các mục dữ liệu đều chính xác và nhất quán. Trong số những thứ khác, bạn có thể muốn chỉ cho phép loại dữ liệu cụ thể như số hoặc ngày trong ô hoặc giới hạn số ở một phạm vi và văn bản nhất định ở một độ dài nhất định. Bạn thậm chí có thể muốn cung cấp một danh sách được xác định trước các mục nhập chấp nhận được để loại bỏ những sai lầm có thể xảy ra. Xác thực dữ liệu Excel cho phép bạn thực hiện tất cả những điều này trong tất cả các phiên bản của Microsoft Excel 365, 2021, 2019, 2016, 20013, 2010 và Lower. Xác thực dữ liệu trong Excel là gì?Xác thực dữ liệu Excel là một tính năng hạn chế đầu vào người dùng (xác thực) người dùng vào bảng tính. Về mặt kỹ thuật, bạn tạo một quy tắc xác thực kiểm soát loại dữ liệu nào có thể được nhập vào một ô nhất định. is a feature that restricts (validates) user input to a worksheet. Technically, you create a validation rule that controls what kind of data can be entered into a certain cell. Dưới đây chỉ là một vài ví dụ về xác thực dữ liệu của Excel có thể làm:
Chẳng hạn, bạn có thể thiết lập một quy tắc giới hạn mục nhập dữ liệu xuống số 4 chữ số trong khoảng từ 1000 đến 9999. Nếu người dùng gõ một cái gì đó khác biệt, Excel sẽ hiển thị cảnh báo lỗi giải thích những gì họ đã làm sai: Cách thực hiện xác thực dữ liệu trong ExcelĐể thêm xác thực dữ liệu trong Excel, hãy thực hiện các bước sau. 1. Mở hộp thoại Xác thực dữ liệuChọn một hoặc nhiều ô để xác thực, chuyển đến Tab Data> Nhóm công cụ dữ liệu và nhấp vào nút Xác thực dữ liệu.Data Validation button. Bạn cũng có thể mở hộp thoại Xác thực dữ liệu bằng cách nhấn Alt> D> L, với mỗi phím được nhấn riêng.Alt > D > L, with each key pressed separately. 2. Tạo quy tắc xác thực ExcelTrên tab Cài đặt, xác định các tiêu chí xác thực theo nhu cầu của bạn. Trong các tiêu chí, bạn có thể cung cấp bất kỳ điều nào sau đây:Settings tab, define the validation criteria according to your needs. In the criteria, you can supply any of the following:
Ví dụ, hãy đưa ra quy tắc hạn chế người dùng nhập toàn bộ số từ 1000 đến 9999: Với quy tắc xác thực được định cấu hình, bấm OK để đóng cửa sổ xác thực dữ liệu hoặc chuyển sang tab khác để thêm thông báo đầu vào hoặc/và cảnh báo lỗi. 3. Thêm thông báo đầu vào (tùy chọn)Nếu bạn muốn hiển thị một thông báo giải thích cho người dùng, dữ liệu nào được phép trong một ô nhất định, hãy mở tab thông báo đầu vào và thực hiện như sau:
Ngay khi người dùng chọn ô được xác thực, thông báo sau sẽ hiển thị: 4. Hiển thị cảnh báo lỗi (tùy chọn)Ngoài thông báo đầu vào, bạn có thể hiển thị một trong các cảnh báo lỗi sau khi dữ liệu không hợp lệ được nhập vào ô.
Để định cấu hình thông báo lỗi tùy chỉnh, hãy chuyển đến tab Cảnh báo lỗi và xác định các tham số sau:
Và bây giờ, nếu người dùng nhập dữ liệu không hợp lệ, Excel sẽ hiển thị cảnh báo đặc biệt giải thích lỗi (như được hiển thị ở phần đầu của hướng dẫn này). Ghi chú. Nếu bạn không nhập thông báo của riêng mình, cảnh báo dừng mặc định với văn bản sau sẽ hiển thị: Giá trị này không khớp với các hạn chế xác thực dữ liệu được xác định cho ô này. If you do not type your own message, the default Stop alert with the following text will show up: This value does not match the data validation restrictions defined for this cell. Ví dụ xác thực dữ liệu ExcelKhi thêm quy tắc xác thực dữ liệu vào Excel, bạn có thể chọn một trong các cài đặt được xác định trước hoặc chỉ định các tiêu chí tùy chỉnh dựa trên công thức xác thực của riêng bạn. Dưới đây chúng tôi sẽ thảo luận về từng tùy chọn tích hợp và tuần tới chúng tôi sẽ xem xét kỹ hơn về xác thực dữ liệu Excel với các công thức tùy chỉnh trong một hướng dẫn riêng. Như bạn đã biết, các tiêu chí xác thực được xác định trên tab Cài đặt của hộp thoại Xác thực dữ liệu (tab dữ liệu> Xác thực dữ liệu).Settings tab of the Data Validation dialog box (Data tab > Data Validation). Toàn bộ số và số thập phânĐể hạn chế mục nhập dữ liệu vào một số hoặc số thập phân, chọn mục tương ứng trong hộp cho phép. Và sau đó, chọn một trong các tiêu chí sau trong hộp dữ liệu:whole number or decimal, select the corresponding item in the Allow box. And then, choose one of the following criteria in the Data box:
Ví dụ: đây là cách bạn tạo quy tắc xác thực Excel cho phép bất kỳ số nào lớn hơn 0: Xác thực ngày và thời gian trong ExcelĐể xác thực ngày, chọn ngày trong hộp cho phép, sau đó chọn một tiêu chí thích hợp trong hộp dữ liệu. Có khá nhiều tùy chọn được xác định trước để lựa chọn: chỉ cho phép ngày giữa hai ngày, bằng, lớn hơn hoặc nhỏ hơn một ngày cụ thể và hơn thế nữa.Date in the Allow box, and then pick an appropriate criteria in the Data box. There are quite a lot of predefined options to choose from: allow only dates between two dates, equal to, greater than or less than a specific date, and more. Tương tự, để xác thực thời gian, chọn thời gian trong hộp cho phép, sau đó xác định các tiêu chí cần thiết.Time in the Allow box, and then define the required criteria. Ví dụ: để chỉ cho phép ngày giữa ngày bắt đầu trong B1 và ngày kết thúc trong B2, áp dụng quy tắc xác thực ngày Excel này: Để xác thực các mục dựa trên dữ liệu ngày nay và thời gian hiện tại, hãy tạo các công thức xác thực dữ liệu của riêng bạn như trong các ví dụ này:
Độ dài văn bảnĐể cho phép nhập dữ liệu có độ dài cụ thể, chọn độ dài văn bản trong hộp cho phép và chọn tiêu chí xác thực theo logic kinh doanh của bạn.Text length in the Allow box, and choose the validation criteria in accordance with your business logic. Ví dụ: để giới hạn đầu vào ở 10 ký tự, hãy tạo quy tắc này: Ghi chú. Tùy chọn độ dài văn bản giới hạn số lượng ký tự nhưng không phải là loại dữ liệu, có nghĩa là quy tắc trên sẽ cho phép cả văn bản và số dưới 10 ký tự hoặc 10 chữ số, tương ứng. The Text length option limits the number of characters but not the data type, meaning the above rule will allow both text and numbers under 10 characters or 10 digits, respectively. Danh sách xác thực dữ liệu Excel (thả xuống)Để thêm danh sách thả xuống các mục vào ô hoặc một nhóm ô, hãy chọn các ô đích và thực hiện như sau:
Danh sách xác thực dữ liệu Excel kết quả sẽ trông tương tự như thế này: Ghi chú. Xin cẩn thận với tùy chọn Bỏ qua trống, được chọn theo mặc định. Nếu bạn đang tạo một danh sách thả xuống dựa trên phạm vi được đặt tên có ít nhất một ô trống, chọn hộp kiểm này cho phép nhập bất kỳ giá trị nào trong ô được xác thực. Trong nhiều tình huống, điều đó cũng đúng với các công thức xác nhận: nếu một ô được tham chiếu trong công thức trống, bất kỳ giá trị nào sẽ được cho phép trong ô được xác thực. Please be careful with the Ignore blank option, which is selected by default. If you are creating a drop-down list based on a named range that has at least one blank cell, selecting this check box allows entering any value in the validated cell. In many situations, it is also true for validation formulas: if a cell referenced in the formula is blank, any value will be allowed in the validated cell. Các cách khác để tạo danh sách xác thực dữ liệu trong ExcelCung cấp các danh sách được phân tách bằng dấu phẩy trực tiếp trong hộp nguồn là cách nhanh nhất hoạt động tốt cho các thả xuống nhỏ mà khó có thể thay đổi. Trong các kịch bản khác, bạn có thể tiến hành một trong những cách sau:
Quy tắc xác thực dữ liệu tùy chỉnhNgoài các quy tắc xác thực dữ liệu Excel tích hợp được thảo luận trong hướng dẫn này, bạn có thể tạo các quy tắc tùy chỉnh với các công thức xác thực dữ liệu của riêng bạn. Đây chỉ la một vai vi dụ:custom rules with your own data validation formulas. Here are just a few examples:
Để biết thêm ví dụ, vui lòng xem các quy tắc và công thức xác thực dữ liệu tùy chỉnh. Cách chỉnh sửa xác thực dữ liệu trong ExcelĐể thay đổi quy tắc xác thực Excel, hãy thực hiện các bước sau:
Bấm OK. Danh sách xác thực dữ liệu Excel kết quả sẽ trông tương tự như thế này:Ghi chú. Xin cẩn thận với tùy chọn Bỏ qua trống, được chọn theo mặc định. Nếu bạn đang tạo một danh sách thả xuống dựa trên phạm vi được đặt tên có ít nhất một ô trống, chọn hộp kiểm này cho phép nhập bất kỳ giá trị nào trong ô được xác thực. Trong nhiều tình huống, điều đó cũng đúng với các công thức xác nhận: nếu một ô được tham chiếu trong công thức trống, bất kỳ giá trị nào sẽ được cho phép trong ô được xác thực. Các cách khác để tạo danh sách xác thực dữ liệu trong Excel
Danh sách xác thực dữ liệu Excel kết quả sẽ trông tương tự như thế này: Instead of copying data validation to other cells, you can convert your dataset to an Excel table. As you add more rows to the table, Excel will apply your validation rule to new rows automatically. Ghi chú. Xin cẩn thận với tùy chọn Bỏ qua trống, được chọn theo mặc định. Nếu bạn đang tạo một danh sách thả xuống dựa trên phạm vi được đặt tên có ít nhất một ô trống, chọn hộp kiểm này cho phép nhập bất kỳ giá trị nào trong ô được xác thực. Trong nhiều tình huống, điều đó cũng đúng với các công thức xác nhận: nếu một ô được tham chiếu trong công thức trống, bất kỳ giá trị nào sẽ được cho phép trong ô được xác thực.Các cách khác để tạo danh sách xác thực dữ liệu trong ExcelData Validation: Cung cấp các danh sách được phân tách bằng dấu phẩy trực tiếp trong hộp nguồn là cách nhanh nhất hoạt động tốt cho các thả xuống nhỏ mà khó có thể thay đổi. Trong các kịch bản khác, bạn có thể tiến hành một trong những cách sau: Danh sách xác thực dữ liệu thả xuống từ một loạt các ôDanh sách xác thực dữ liệu động từ một phạm vi được đặt tên Danh sách xác thực dữ liệu động từ bảng ExcelDanh sách thả xuống Cascading (phụ thuộc)
Tips:
Như bạn thấy, phương pháp tiêu chuẩn khá nhanh nhưng yêu cầu một vài lần nhấp chuột, không có vấn đề gì lớn như tôi quan tâm. Nhưng nếu bạn thích làm việc với bàn phím hơn một con chuột, bạn có thể thấy cách tiếp cận sau hấp dẫn. Phương pháp 2: Dán đặc biệt để xóa các quy tắc xác thực dữ liệuDe Jure, Excel Paste Special được thiết kế để dán các yếu tố cụ thể của các ô được sao chép. Thực tế, nó có thể làm nhiều việc hữu ích hơn. Trong số những người khác, nó có thể nhanh chóng xóa các quy tắc xác thực dữ liệu trong bảng tính. Đây là cách:
Mẹo xác thực dữ liệu ExcelBây giờ bạn đã biết những điều cơ bản của xác thực dữ liệu trong Excel, hãy để tôi chia sẻ một vài mẹo có thể làm cho các quy tắc của bạn hiệu quả hơn rất nhiều. Xác thực dữ liệu Excel dựa trên một ô khácThay vì gõ các giá trị trực tiếp vào các hộp tiêu chí, bạn có thể nhập chúng vào một số ô, sau đó tham khảo các ô đó. Nếu bạn quyết định thay đổi các điều kiện xác thực sau này, bạn sẽ chỉ cần nhập các số mới trên trang, mà không phải chỉnh sửa quy tắc. Để nhập tham chiếu ô, nhập nó vào hộp trước một dấu bằng hoặc nhấp vào mũi tên bên cạnh hộp, sau đó chọn ô bằng chuột. Bạn cũng có thể nhấp vào bất cứ nơi nào trong hộp, sau đó chọn ô trên trang tính.cell reference, either type it in the box preceded by an equal sign, or click the arrow next to the box, and then select the cell using the mouse. You can also click anywhere within the box, and then select the cell on the sheet. Ví dụ: để cho phép bất kỳ số nào khác ngoài số trong A1, chọn không bằng tiêu chí trong hộp dữ liệu và nhập Để tiến thêm một bước, bạn có thể nhập một công thức trong ô được tham chiếu và có Excel xác nhận đầu vào dựa trên công thức đó.formula in the referenced cell, and have Excel validate the input based on that formula. Ví dụ: để hạn chế người dùng nhập ngày sau ngày hôm nay, hãy nhập công thức Hoặc, bạn có thể nhập công thức Quy tắc xác nhận dựa trên công thứcTrong các tình huống khi không thể xác định các tiêu chí xác thực mong muốn dựa trên giá trị hoặc tham chiếu ô, bạn có thể diễn đạt nó bằng công thức. Ví dụ: để giới hạn mục nhập ở mức tối thiểu và tối đa trong danh sách các số hiện có, giả sử A1: A10, sử dụng các công thức sau:
Vui lòng chú ý rằng chúng tôi khóa phạm vi bằng cách sử dụng $ Sign (tham chiếu ô tuyệt đối) để quy tắc xác thực Excel của chúng tôi hoạt động chính xác cho tất cả các ô được chọn. Cách tìm dữ liệu không hợp lệ trên trang tínhMặc dù Microsoft Excel cho phép áp dụng xác thực dữ liệu cho các ô đã có dữ liệu trong đó, nhưng nó sẽ không thông báo cho bạn nếu một số giá trị hiện tại không đáp ứng các tiêu chí xác thực. Để tìm dữ liệu không hợp lệ đã được đưa vào bảng tính của bạn trước khi bạn thêm xác thực dữ liệu, hãy chuyển đến tab Dữ liệu và nhấp vào Xác thực dữ liệu> Circle Dữ liệu không hợp lệ.Circle Invalid Data. Điều này sẽ làm nổi bật tất cả các ô không đáp ứng các tiêu chí xác nhận: Ngay khi bạn sửa một mục nhập không hợp lệ, vòng tròn sẽ tự động biến mất. Để xóa tất cả các vòng tròn, hãy chuyển đến tab Dữ liệu và nhấp vào Xác thực dữ liệu> Xóa các vòng tròn xác thực.Clear Validation Circles. Cách bảo vệ bảng tính với xác thực dữ liệuTrong trường hợp bạn muốn bảo vệ bảng tính hoặc sổ làm việc bằng mật khẩu, trước tiên hãy định cấu hình cài đặt xác thực dữ liệu mong muốn và sau đó bảo vệ trang tính. Điều quan trọng là bạn mở khóa các ô được xác thực trước khi bảo vệ bảng tính, nếu không người dùng của bạn sẽ không thể nhập bất kỳ dữ liệu nào trong các ô đó. Để biết hướng dẫn chi tiết, vui lòng xem cách mở khóa một số ô trên một tờ được bảo vệ.unlock validated cells prior to protecting the worksheet, otherwise your users won't be able to enter any data in those cells. For the detailed guidelines, please see How to unlock certain cells on a protected sheet. Cách chia sẻ sổ làm việc với xác thực dữ liệuĐể cho phép nhiều người dùng hợp tác trên sổ làm việc, hãy chắc chắn chia sẻ sổ làm việc sau khi bạn đã thực hiện xác thực dữ liệu. Sau khi chia sẻ sổ làm việc, các quy tắc xác thực dữ liệu của bạn sẽ tiếp tục hoạt động, nhưng bạn sẽ không thể thay đổi chúng, cũng như không thêm các quy tắc mới. Xác thực dữ liệu Excel không hoạt độngNếu xác thực dữ liệu không hoạt động đúng trong bảng tính của bạn, rất có thể là vì một trong những lý do sau. Xác thực dữ liệu không hoạt động cho dữ liệu được sao chépXác thực dữ liệu trong Excel được thiết kế để cấm gõ dữ liệu không hợp lệ trực tiếp trong ô, nhưng nó không thể ngăn người dùng sao chép dữ liệu không hợp lệ. Mặc dù không có cách nào để vô hiệu hóa các phím tắt sao chép/dán (trừ khi sử dụng VBA), nhưng ít nhất bạn có thể ngăn chặn dữ liệu sao chép bằng cách kéo và thả các ô. Để thực hiện việc này, hãy chuyển đến Tệp> Tùy chọn> Nâng cao> Tùy chọn chỉnh sửa và xóa hộp kiểm Bật Fill Fill và Cell Drag-and-Drop.typing invalid data directly in a cell, but it
cannot stop users from copying invalid data. Though there is no way to disable copy/paste shortcuts (other than by using VBA), you can at least prevent copying data by dragging and dropping cells. To do this, go to File > Options > Advanced > Editing options, and clear the Enable fill handle and cell drag-and-drop check box. Xác thực dữ liệu Excel không khả dụng khi ở chế độ chỉnh sửa ôLệnh xác thực dữ liệu không có sẵn (mờ đi) nếu bạn đang nhập hoặc thay đổi dữ liệu trong một ô. Sau khi bạn chỉnh sửa xong ô, nhấn Enter hoặc ESC để thoát chế độ chỉnh sửa và sau đó thực hiện xác thực dữ liệu. Không thể áp dụng xác thực dữ liệu cho sổ làm việc được bảo vệ hoặc chia sẻMặc dù các quy tắc xác thực hiện có tiếp tục hoạt động trong sổ làm việc được bảo vệ và chia sẻ, nhưng không thể thay đổi cài đặt xác thực dữ liệu hoặc thiết lập các quy tắc mới. Để làm điều này, trước tiên, UNSHARE và/hoặc không được bảo vệ trước sổ làm việc của bạn. Công thức xác thực dữ liệu không chính xácKhi thực hiện xác thực dữ liệu dựa trên công thức trong Excel, có ba điều quan trọng cần kiểm tra:
Để biết thêm thông tin, vui lòng xem Quy tắc xác thực dữ liệu tùy chỉnh không hoạt động. Tính toán lại thủ công được bậtNếu chế độ tính toán thủ công được bật trong Excel của bạn, các công thức không được tính toán có thể ngăn dữ liệu được xác thực chính xác. Để thay đổi tùy chọn tính toán Excel trở lại tự động, hãy chuyển đến tab Công thức> Nhóm tính toán, nhấp vào nút Tùy chọn tính toán và sau đó nhấp vào Tự động.Automatic. Để biết thêm thông tin, vui lòng xem Tính toán tự động so với tính toán thủ công. Đó là cách bạn thêm và sử dụng xác thực dữ liệu trong Excel. Tôi cảm ơn bạn đã đọc và hy vọng sẽ gặp bạn trên blog của chúng tôi vào tuần tới! Bạn cũng có thể quan tâmLàm cách nào để xóa xác thực dữ liệu khỏi các tờ?Thay đổi hoặc xóa một danh sách thả xuống.. Mở một bảng tính trong Google Sheets .. Chọn ô hoặc ô bạn muốn thay đổi .. Nhấp vào dữ liệu.Xác nhận dữ liệu.. Để thay đổi các tùy chọn được liệt kê, chỉnh sửa các mục bên cạnh "Tiêu chí.". Để xóa danh sách, nhấp vào Xóa Xác thực .. Nhấp vào để lưu.. Làm cách nào để làm mới danh sách xác thực dữ liệu trong Excel?Tự động cập nhật danh sách thả xuống.. Chọn một ô bạn muốn đặt danh sách thả xuống và nhấp vào dữ liệu> Xác thực dữ liệu> Xác thực dữ liệu .. Trong hộp thoại Xác thực dữ liệu, nhấp vào tab Cài đặt và chọn Danh sách từ Danh sách cho phép, sau đó nhập = Offset ($ 2,0,0, Counta (A: A) -1) vào hộp văn bản nguồn.Xem ảnh chụp màn hình: .... Bấm OK .. |