Công thức tùy chỉnh xác thực dữ liệu

Hướng dẫn này sẽ cho bạn thấy cách tạo công thức tùy chỉnh trong Xác thực dữ liệu trong Excel và Google Trang tính

Xác thực dữ liệu - Phải bắt đầu bằng - Excel

Chúng tôi có thể viết một công thức tùy chỉnh để đảm bảo rằng dữ liệu trong một ô bắt đầu bằng văn bản nhất định.

1. Đánh dấu phạm vi cần thiết, ví dụ: B3: B8.
2. Trong Ruy-băng, hãy chọn Dữ liệu> Công cụ dữ liệu> Xác thực dữ liệu.

3. Lựa chọn Tập quán từ hộp thả xuống Cho phép, rồi nhập công thức sau:

= EXACT (LEFT (B3,4), "FRU-")

Công thức sử dụng 2 hàm CHÍNH XÁC và BÊN TRÁI để xác định xem 4 ký tự đầu tiên được nhập vào ô có giống với “FRU-” hay không

4. Nếu bạn muốn thêm thông báo đầu vào cho người dùng của mình, hãy nhấp vào Nhập tin nhắn.

5. Đảm bảo đánh dấu vào ô “Hiển thị thông báo đầu vào khi ô được chọn”Được chọn và sau đó nhập tiêu đề và thông điệp bạn yêu cầu.
6. Nếu bạn muốn thêm cảnh báo lỗi, hãy nhấp vào Cảnh báo lỗi.

7. Bạn có thể thay đổi Kiểu từ Ngừng lại cho một trong hai Cảnh báo hoặc Thông tin nếu bạn yêu cầu, rồi nhập tiêu đề và thông báo lỗi của bạn.
8. Nhấp chuột VÂNG.
9. Nếu bạn đã sử dụng Đầu vào Tùy chọn tin nhắn, bình luận sẽ xuất hiện trên màn hình thông báo cho người dùng về quy tắc.

10. Nhập “FRI-124” vào ô B3. Nếu bạn đã chọn Ngừng lại, hộp thông báo sau sẽ xuất hiện.

11. Nếu bạn đã chọn Cảnh báo, sau đó hộp thông báo này sẽ xuất hiện. Điều này cho phép bạn tiếp tục nếu bạn xác định rằng dữ liệu là chính xác.

12. Nếu bạn đã chọn Thông tin, sau đó hộp thông báo này sẽ xuất hiện.

13. Nếu bạn nhấp vào VÂNG, bạn sẽ được phép tiếp tục với dữ liệu không chính xác được nhập vào ô.
14. Nhấp chuột Hủy bỏ để thoát khỏi tin nhắn hoặc VÂNG để nhập văn bản vào ô.

Xác thực dữ liệu Chỉ cho phép viết hoa trong Excel

Chúng ta có thể viết một công thức tùy chỉnh để đảm bảo rằng dữ liệu trong một ô chỉ cho phép viết hoa nếu văn bản được nhập vào ô.

1. Đánh dấu phạm vi cần thiết, ví dụ: B3: B8.
2. Trong Ruy-băng, hãy chọn Dữ liệu> Công cụ dữ liệu> Xác thực dữ liệu.
3. Lựa chọn Tập quán từ hộp thả xuống Cho phép, rồi nhập công thức sau:

= CHÍNH XÁC (B3, LÊN (B3))

Công thức sử dụng 2 hàm CHÍNH XÁC và PHÍA TRÊN để xác định xem văn bản được nhập vào ô có ở dạng chữ hoa hay không. Các ô có hỗn hợp số và văn bản được coi là văn bản và các số sẽ bị bỏ qua trong quy tắc.

4. Nhấp chuột VÂNG.
5. Nhập "trái cây-124" vào ô B3.
6. Nếu bạn đã sử dụng tùy chọn Cảnh báo lỗi, cảnh báo tùy chỉnh và thông báo lỗi của bạn sẽ xuất hiện. Nếu bạn chưa sử dụng tùy chọn này, cảnh báo tiêu chuẩn sẽ xuất hiện.

7. Nhấp vào Hủy để thoát khỏi thông báo hoặc Thử lại để nhập lại văn bản chính xác vào ô.
8. Nhập “123456” vào ô B3.
9. Điều này sẽ được cho phép vì nó là một số chứ không phải văn bản.

Trong ví dụ tiếp theo của chúng tôi, chúng tôi sẽ đảm bảo rằng chỉ văn bản viết hoa mới có thể được nhập vào ô.

Xác thực dữ liệu Chỉ cho phép văn bản viết hoa trong Excel

Chúng tôi có thể viết một công thức tùy chỉnh trong Xác thực dữ liệu có thể được sử dụng để đảm bảo rằng dữ liệu trong một ô chỉ cho phép văn bản chữ hoa

LƯU Ý: nếu bạn nhập thông tin vào ô bắt đầu bằng văn bản nhưng chứa số, Excel sẽ xem xét văn bản thông tin.

1. Đánh dấu phạm vi cần thiết, ví dụ: B3: B8.
2. Trong Ruy-băng, hãy chọn Dữ liệu> Công cụ dữ liệu> Xác thực dữ liệu.
3. Lựa chọn Tập quán từ hộp thả xuống Cho phép, rồi nhập công thức sau:

= VÀ (CHÍNH XÁC (B3, LÊN (B3)), ISTEXT (B3))

Công thức sử dụng 4 hàm VÀ, CHÍNH XÁC, LÊN và CHỮ để xác định xem văn bản được nhập vào các ô có ở dạng chữ hoa hay không VÀ để xác định xem thông tin được nhập có thực sự là văn bản và không phải là một số thuần túy hay không.

4. Nhấp chuột VÂNG.
5. Nhập "trái cây-124" vào ô B3.
6. Nếu bạn đã sử dụng tùy chọn Cảnh báo lỗi, cảnh báo tùy chỉnh và thông báo lỗi của bạn sẽ xuất hiện. Nếu bạn chưa sử dụng tùy chọn này, cảnh báo tiêu chuẩn sẽ xuất hiện.

7. Nhấp vào Hủy để thoát khỏi thông báo hoặc Thử lại để nhập lại văn bản chính xác vào ô.
8. Nhập “123456” vào ô B3.
9. Bạn sẽ một lần nữa nhận được thông báo lỗi.
10. Nhấp vào Hủy để thoát khỏi thông báo hoặc Thử lại để nhập lại văn bản chính xác vào ô.

Xác thực dữ liệu ngăn không gian trong Excel

Chúng ta có thể viết một công thức tùy chỉnh để đảm bảo rằng không có khoảng trắng nào được nhập vào dữ liệu được nhập trong một dải ô.

1. Đánh dấu phạm vi cần thiết, ví dụ: B3: B8.
2. Trong Ruy-băng, hãy chọn Dữ liệu> Công cụ dữ liệu> Xác thực dữ liệu.
3. Lựa chọn Tập quán từ hộp thả xuống Cho phép, rồi nhập công thức sau:

= B3 = SUBSTITUTE (B3, ””, “”)

Công thức sử dụng Hàm SUBSTITUTE để kiểm tra xem khoảng trắng không tồn tại.

4. Nhấp chuột VÂNG.
5. Nhập “FRU - 124” vào ô B4.
6. Nếu bạn đã sử dụng tùy chọn Cảnh báo lỗi, cảnh báo tùy chỉnh và thông báo lỗi của bạn sẽ xuất hiện. Nếu bạn chưa sử dụng tùy chọn này, cảnh báo tiêu chuẩn sẽ xuất hiện.

7. Nhấp vào Hủy để thoát khỏi thông báo hoặc Thử lại để nhập lại văn bản chính xác vào ô.

Xác thực dữ liệu Ngăn chặn các bản sao trong Excel

Chúng tôi có thể viết một công thức tùy chỉnh để ngăn chúng tôi nhập thông tin trùng lặp vào một dải ô.

1. Đánh dấu phạm vi cần thiết, ví dụ: B3: B8.
2. Trong Ruy-băng, hãy chọn Dữ liệu> Công cụ dữ liệu> Xác thực dữ liệu.
3. Chọn Khách hàng từ hộp thả xuống Cho phép, sau đó nhập công thức sau:

= COUNTIF ($ B $ 3: $ B $ 8, B3) <2

Công thức sử dụng COUNTIF chức năng và sử dụng TUYỆT ĐỐI trong phạm vi B3: B8 để đảm bảo rằng đây là danh sách mà COUNTIF hàm sẽ xem xét khi nó kiểm tra xem có bất kỳ giá trị trùng lặp nào không.

4. Nhấp chuột VÂNG.
5. Nhập “FRU-123” vào ô D4.
6. Nếu bạn đã sử dụng tùy chọn Cảnh báo lỗi, cảnh báo tùy chỉnh và thông báo lỗi của bạn sẽ xuất hiện. Nếu bạn chưa sử dụng tùy chọn này, cảnh báo tiêu chuẩn sẽ xuất hiện.

7. Nhấp vào Hủy để thoát khỏi thông báo hoặc Thử lại để nhập lại văn bản chính xác vào ô.

Xác thực dữ liệu tồn tại trong danh sách trong Excel

Chúng tôi có thể viết một công thức tùy chỉnh đảm bảo rằng chỉ văn bản cụ thể mới được nhập vào một ô.

1. Đánh dấu phạm vi cần thiết, ví dụ: D3: D8.
2. Trong Ruy-băng, hãy chọn Dữ liệu> Công cụ dữ liệu> Xác thực dữ liệu.
3. Chọn Tùy chỉnh từ hộp thả xuống Cho phép, sau đó nhập công thức sau:

= COUNTIF ($ F $ 6: $ F $ 8, D3)> 0

Công thức sử dụng COUNTIF chức năng và sử dụng TUYỆT ĐỐI trong phạm vi F3: F8 để đảm bảo rằng đây là danh sách mà COUNTIF chức năng sẽ xem xét khi kiểm tra xem có nhập đúng văn bản hay không.

4. Nhấp chuột VÂNG.
5. Nhập “Đơn” vào ô D4.
6. Nếu bạn đã sử dụng tùy chọn Cảnh báo lỗi, cảnh báo tùy chỉnh và thông báo lỗi của bạn sẽ xuất hiện. Nếu bạn chưa sử dụng tùy chọn này, cảnh báo tiêu chuẩn sẽ xuất hiện.

7. Nhấp vào Hủy để thoát khỏi thông báo hoặc Thử lại để nhập lại văn bản chính xác vào ô.

Xác thực dữ liệu không tồn tại trong danh sách trong Excel

Chúng tôi có thể sử dụng một công thức tùy chỉnh để đảm bảo rằng văn bản cụ thể không được nhập vào một ô.

1. Đánh dấu phạm vi cần thiết, ví dụ: C3: C8.
2. Trong Ruy-băng, hãy chọn Dữ liệu> Công cụ dữ liệu> Xác thực dữ liệu.
3. Chọn Khách hàng từ hộp thả xuống Cho phép, sau đó nhập công thức sau:

= COUNTIF ($ F $ 6: $ F $ 8, C3) = 0

Công thức sử dụng COUNTIF chức năng và sử dụng TUYỆT ĐỐI trong phạm vi F3: F8 để đảm bảo rằng đây là danh sách mà COUNTIF chức năng sẽ xem xét khi kiểm tra xem có nhập đúng văn bản hay không.

4. Nhấp chuột VÂNG.
5. Nhập "Thịt bò" vào ô C4.
6. Nếu bạn đã sử dụng tùy chọn Cảnh báo lỗi, cảnh báo tùy chỉnh và thông báo lỗi của bạn sẽ xuất hiện. Nếu bạn chưa sử dụng tùy chọn này, cảnh báo tiêu chuẩn sẽ xuất hiện.

7. Nhấp vào Hủy để thoát khỏi thông báo hoặc Thử lại để nhập lại văn bản chính xác vào ô.

Xác thực dữ liệu Chỉ các số được nhập trong Excel

Chúng tôi có thể sử dụng công thức tùy chỉnh để đảm bảo rằng chỉ một số được nhập vào một ô.

1. Đánh dấu phạm vi cần thiết, ví dụ: E3: E8.
2. Trong Ruy-băng, hãy chọn Dữ liệu> Công cụ dữ liệu> Xác thực dữ liệu.
3. Chọn Khách hàng từ hộp thả xuống Cho phép, sau đó nhập công thức sau:

= ISNUMBER (F3: F8)

Công thức sử dụng hàm ISNUMBER để đảm bảo rằng một số được nhập vào các ô trong phạm vi.

4. Nhấp chuột VÂNG.
5. Gõ “nine” vào ô F4.
6. Nếu bạn đã sử dụng tùy chọn Cảnh báo lỗi, cảnh báo tùy chỉnh và thông báo lỗi của bạn sẽ xuất hiện. Nếu bạn chưa sử dụng tùy chọn này, cảnh báo tiêu chuẩn sẽ xuất hiện.

7. Nhấp vào Hủy để thoát khỏi thông báo hoặc Thử lại để nhập lại văn bản chính xác vào ô.

Xác thực dữ liệu không vượt quá giá trị trong Excel

Chúng tôi có thể sử dụng một công thức tùy chỉnh để đảm bảo rằng các giá trị được nhập vào một ô không vượt quá một giá trị được chỉ định.

1. Đánh dấu phạm vi cần thiết, ví dụ: E3: E8.
2. Trong Ruy-băng, hãy chọn Dữ liệu> Công cụ dữ liệu> Xác thực dữ liệu.
3. Chọn Khách hàng từ hộp thả xuống Cho phép, sau đó nhập công thức sau:

= E3 <= $ G $ 6

Công thức an TUYỆT ĐỐI trong phạm vi G6 để đảm bảo rằng đây là giá trị mà quy tắc kiểm tra khi dữ liệu được nhập vào E3.

4. Nhấp chuột VÂNG.
5. Nhập “9” vào ô E4.
6. Nếu bạn đã sử dụng tùy chọn Cảnh báo lỗi, cảnh báo tùy chỉnh và thông báo lỗi của bạn sẽ xuất hiện. Nếu bạn chưa sử dụng tùy chọn này, cảnh báo tiêu chuẩn sẽ xuất hiện.

7. Nhấp vào Hủy để thoát khỏi thông báo hoặc Thử lại để nhập lại văn bản chính xác vào ô.

Xác thực dữ liệu không vượt quá tổng số trong Excel

Chúng tôi có thể sử dụng công thức tùy chỉnh để đảm bảo rằng các giá trị được nhập vào một dải ô không vượt quá tổng giá trị được chỉ định cho dải ô

1. Đánh dấu phạm vi cần thiết, ví dụ: F3: F8.
2. Trong Ruy-băng, hãy chọn Dữ liệu> Công cụ dữ liệu> Xác thực dữ liệu.
3. Chọn Khách hàng từ hộp thả xuống Cho phép, sau đó nhập công thức sau:

= SUM ($ F $ 3: $ F $ 8) <= $ H $ 6

Công thức sử dụng TỔNG chức năng và sử dụng TUYỆT ĐỐI trong phạm vi F3: F8 để đảm bảo rằng đây là danh sách mà TỔNG hàm sẽ xem xét khi nó kiểm tra để thấy rằng tổng của phạm vi không lớn hơn giá trị đã nhập trong H6.

4. Nhấp chuột VÂNG.
5. Nhập “40” vào ô F4.
6. Nếu bạn đã sử dụng tùy chọn Cảnh báo lỗi, cảnh báo tùy chỉnh và thông báo lỗi của bạn sẽ xuất hiện. Nếu bạn chưa sử dụng tùy chọn này, cảnh báo tiêu chuẩn sẽ xuất hiện.

7. Nhấp vào Hủy để thoát khỏi thông báo hoặc Thử lại để nhập lại văn bản chính xác vào ô.

Xác thực dữ liệu Ngày trong tuần Chỉ trong Excel.

Chúng tôi có thể sử dụng công thức tùy chỉnh để đảm bảo rằng chỉ các ngày trong tuần được nhập khi ngày tháng được sử dụng trong Excel.

1. Đánh dấu phạm vi cần thiết, ví dụ: G3: G8.
2. Trong Ruy-băng, hãy chọn Dữ liệu> Công cụ dữ liệu> Xác thực dữ liệu.
3. Chọn Khách hàng từ hộp thả xuống Cho phép, sau đó nhập công thức sau:

= WEEKDAY (F3,2) <6

Hàm ngày trong tuần kiểm tra để đảm bảo rằng ngày có trong ngày không phải là Thứ Bảy hoặc Chủ Nhật.

4. Sửa đổi ngày trong G5 để hiển thị ngày thứ Bảy (ví dụ: 9^NS Tháng 5 năm 2022).
5. Nếu bạn đã sử dụng tùy chọn Cảnh báo lỗi, cảnh báo tùy chỉnh và thông báo lỗi của bạn sẽ xuất hiện. Nếu bạn chưa sử dụng tùy chọn này, cảnh báo tiêu chuẩn sẽ xuất hiện.

7. Nhấp vào Hủy hoặc Thử lại để nhập một ngày thay thế.

Xác thực dữ liệu Ngày trong tương lai Chỉ trong Excel

Chúng tôi có thể tạo một công thức tùy chỉnh để chỉ cho phép người dùng nhập một ngày trong tương lai.

1. Đánh dấu phạm vi cần thiết, ví dụ: G3: G8.
2. Trong Ruy-băng, hãy chọn Dữ liệu> Công cụ dữ liệu> Xác thực dữ liệu.
3. Chọn Khách hàng từ hộp thả xuống Cho phép, sau đó nhập công thức sau:

= G3> HÔM NAY ()

Công thức sử dụng HÔM NAY chức năng để kiểm tra xem ngày nhập vào ô có lớn hơn ngày hôm nay hay không.

4. Sửa đổi ngày trong G5 thành ngày hôm qua.
5. Nếu bạn đã sử dụng tùy chọn Cảnh báo lỗi, cảnh báo tùy chỉnh và thông báo lỗi của bạn sẽ xuất hiện. Nếu bạn chưa sử dụng tùy chọn này, cảnh báo tiêu chuẩn sẽ xuất hiện.

6. Nhấp vào Hủy hoặc Thử lại để nhập một ngày thay thế.

Xác thực dữ liệu phải bắt đầu bằng Google Trang tính

1. Đánh dấu phạm vi cần thiết, ví dụ: B3: B8.
2. Trong Menu, chọn Dữ liệu> Xác thực dữ liệu.

3. Phạm vi ô sẽ được điền vào.

4. Lựa chọn Công thức tùy chỉnh là từ danh sách thả xuống Tiêu chí.

5. Nhập công thức.

= EXACT (LEFT (B3,4), "FRU-")

6. Chọn một trong hai Hiển thị cảnh báo hoặc Từ chối đầu vào nếu dữ liệu không hợp lệ.

7. Bạn có thể nhập một số văn bản trợ giúp Xác thực nếu bạn yêu cầu.

8. Nhấp vào để lưu.

9. Nhấp vào B3 để xem văn bản trợ giúp Xác thực

10. Nhập FRI-123
11. Nếu bạn đã chọn Hiển thị cảnh báo, thông báo sau sẽ xuất hiện.

12. Ngoài ra, nếu bạn đã chọn Từ chối đầu vào trên dữ liệu không hợp lệ, bạn sẽ bị ngăn không cho nhập dữ liệu và thông báo sau xuất hiện trên màn hình.

Phần còn lại của các ví dụ về công thức tùy chỉnh trong Google Trang tính hoạt động theo cùng một cách.