Xác thực dữ liệu định dạng có điều kiện của Google Trang tính

Trước khi biết cách đánh dấu tất cả các ô có cờ lỗi trong trình đơn thả xuống trong Google Trang tính, chúng ta phải hiểu lý do của các cờ lỗi màu đỏ

Khi bạn vi phạm quy tắc Xác thực dữ liệu trong một ô, ô đó sẽ hiển thị cờ lỗi màu đỏ. Đó là để cảnh báo người dùng rằng ô vi phạm quy tắc xác thực dữ liệu áp dụng cho ô đó

Có nhiều loại quy tắc xác thực dữ liệu khác nhau mà chúng ta có thể đặt trong một ô trong Google Trang tính

Ở đây, chúng tôi chỉ thảo luận về cách đánh dấu tất cả các ô có cờ lỗi trong trình đơn thả xuống xác thực dữ liệu trong Google Trang tính

Bạn có thể ngăn các cờ lỗi màu đỏ trong trình đơn thả xuống xác thực dữ liệu bằng cách chọn tùy chọn “Từ chối đầu vào” thay vì “Hiển thị cảnh báo” trong cài đặt xác thực dữ liệu

Bạn chưa quen với “Từ chối đầu vào” cũng như “Hiển thị cảnh báo” trong cài đặt thả xuống xác thực dữ liệu?

Trong bài đăng này, bạn sẽ nhận được quy tắc định dạng có điều kiện có thể đánh dấu trình đơn thả xuống xác thực dữ liệu trong trường hợp sau

Giả sử bạn đã đặt menu thả xuống trong ô A1 và “Trên dữ liệu không hợp lệ” được đặt thành “Hiển thị cảnh báo”

Thay vì chọn một mục hiện có trong trình đơn thả xuống, bạn đã nhập một giá trị khác

Nó có thể gây ra cờ lỗi màu đỏ trong ô A1. Quy tắc định dạng có điều kiện của tôi sẽ làm nổi bật các ô như vậy. Hãy đi đến hai ví dụ

Đánh dấu tất cả các ô có cờ lỗi trong trình đơn thả xuống

Chúng tôi có thể đặt hai loại trình đơn thả xuống xác thực dữ liệu trong Google Trang tính. Chúng là loại tiêu chí “Danh sách từ một phạm vi” và “Danh sách các mục”

Tôi đang bắt đầu với “Danh sách các mục”

Danh sách các mục và làm nổi bật các cờ lỗi màu đỏ

Trong Sheet3. A1, tôi muốn menu thả xuống có nội dung “Thấp”, “Trung bình”, “Trên trung bình” và “Cao”. Trước tiên hãy đặt nó

bước. -

1. Trong Google Trang tính của bạn, ở Trang tính 3, hãy nhấp vào ô A1 và chuyển đến menu Dữ liệu > Xác thực dữ liệu

2. Chọn “Danh sách các mặt hàng” và nhập văn bản dưới dạng các giá trị được phân tách bằng dấu phẩy như bên dưới

3. Nó sẽ thiết lập một trình đơn thả xuống với bốn văn bản trên để chọn

4. Sao chép-dán danh sách thả xuống ô A1 vào A2. A5. Vì vậy, bạn sẽ có năm danh sách thả xuống trong phạm vi A1. A5

5. Chọn “Trung bình” ở A1, “Thấp” ở A2, “Trên trung bình” ở A3 và “Thấp” ở A5

Trong A4, không chọn bất kỳ văn bản nào từ trình đơn thả xuống. Thay vào đó, hãy nhập văn bản “Extra Ordinary”

Vì văn bản “Đặc biệt thông thường” không có trong “Danh sách mặt hàng” nên nó vi phạm quy tắc xác thực dữ liệu. Điều đó gây ra cờ lỗi màu đỏ trong ô A4

Bây giờ, hãy xem cách đánh dấu các ô (ở đây là ô) bằng cờ lỗi màu đỏ trong trình đơn thả xuống xác thực dữ liệu “Danh sách các mục” trong Google Trang tính

Các bước để đánh dấu các ô có cờ lỗi trong Danh sách các mục thả xuống

1. Chọn phạm vi A1. A5

2. Chuyển đến menu ĐỊNH DẠNG Định dạng có điều kiện

3. Trong “Quy tắc định dạng”, chọn “Công thức tùy chỉnh là” và chèn công thức bên dưới vào trường đã cho

=and(
     len(A1),
     countif({"Low", "Average", "Above Average", "High"},A1)<1
)

Đó là tất cả những gì bạn muốn làm để đánh dấu các cờ lỗi màu đỏ trong phạm vi thả xuống A1. A5

Giải thích công thức

Như bạn có thể thấy, tôi đã sử dụng hàm logic AND để đánh dấu các ô có cờ lỗi trong danh sách thả xuống ở A1. A5

Đây là cú pháp của hàm đã nói trong Google Trang tính (hàm này trả về giá trị Boolean TRUE [ở đây đánh dấu] nếu tất cả các đối số được cung cấp là đúng về mặt logic)

AND(
     logical_expression1,
     [logical_expression2, …]
)

logical_expression_1 theo công thức của chúng tôi là len(A1). Nó có nghĩa là kiểm tra xem A1 có bất kỳ giá trị nào không

Biểu_thức_lôgic_2 là;

=countif({"Low", "Average", "Above Average", "High"},A1)<1

Đó là công thức COUNTIF và đây là cú pháp

COUNTIF(range, criterion)

“Phạm vi” là danh sách các mục trong Dấu ngoặc nhọn và tiêu chí là giá trị trong ô A1

Công thức trên trả về 1 nếu tiêu chí nằm trong danh sách các mục. Nếu không có, công thức sẽ trả về 0

Phần <1 buộc công thức trả về TRUE hoặc FALSE tùy thuộc vào đầu ra Countif

Nếu kết quả là <1 (nếu tiêu chí không có trong phạm vi), công thức sẽ trả về TRUE, ngược lại là FALSE

Nếu A1 có một giá trị (phần LEN) và giá trị đó không có trong danh sách các mục (phần COUNTIF), cả hai điều kiện trong AND sẽ đáp ứng TRUE. Các ô như vậy sẽ được đánh dấu trong phạm vi A1. A5

Chỉ có một ô đáp ứng tiêu chí này và đó là ô A4

Ghi chú. - Ở định dạng có điều kiện, không cần viết quy tắc riêng cho các ô từ A1 đến A5. Quy tắc A1 sẽ áp dụng cho A1. A5, nếu “Áp dụng cho phạm vi” là A1. A5 trong bảng “Quy tắc định dạng có điều kiện”

Bạn có thể đọc thêm về điều này tại đây – Tham chiếu tương đối trong Định dạng có điều kiện trong Google Trang tính

Liệt kê từ một phạm vi và làm nổi bật các cờ lỗi màu đỏ

Dưới đây là một ví dụ khác để đánh dấu các ô có cờ lỗi trong trình đơn thả xuống trong Google Trang tính

Lần này có hai trang tính (tab) được sử dụng. Sheet1 dành cho danh sách thả xuống và sheet2 dành cho danh sách thả xuống

Đây là các giá trị trong Sheet2

Trong Sheet1. B3 mình có drop-down menu sau copy xuống cho đến Sheet1. B16

Cài đặt xác thực dữ liệu

phạm vi ô. Tờ1. B3
Tiêu chuẩn. Danh sách từ một phạm vi – Sheet2. A2. A11
Trên dữ liệu không hợp lệ. Đã chọn "Hiển thị cảnh báo"

Trong Sheet1. C3, tôi có menu thả xuống sau, được sao chép xuống cho đến Sheet1. C16

Cài đặt xác thực dữ liệu

phạm vi ô. Tờ1. C3
Tiêu chuẩn. Danh sách từ một phạm vi – Sheet2. B2. B 4
Trên dữ liệu không hợp lệ. Đã chọn "Hiển thị cảnh báo"

Trong ô B8, B14 đến B17, C5, C7, C8 và C14 trong Trang tính 1 có các giá trị không có trong danh sách thả xuống tương ứng của chúng. Tôi đã nhập chúng một cách mạnh mẽ

Vì vậy, các ô này có cờ đỏ cho biết vi phạm quy tắc xác thực dữ liệu

Tại đây để đánh dấu các ô được đề cập ở trên có cờ lỗi trong trình đơn thả xuống, chúng ta có thể sử dụng quy tắc bên dưới trong định dạng có điều kiện trong Google Trang tính

Cài đặt định dạng có điều kiện

Áp dụng cho phạm vi. B3. C16
Công thức tùy chỉnh là;

=and(
     len(B3),
     countif(indirect("Sheet2!A2:B11"),B3)<1
)

Công thức này tương tự như công thức của chúng tôi được sử dụng trong ví dụ 1 (danh sách các mục) ở trên. Sự khác biệt là trong việc sử dụng COUNTIF

Trước đó, chúng tôi đã trình bày phạm vi COUNTIF (danh sách các mục) trong Dấu ngoặc nhọn. Ở đây chúng ta có danh sách trong một sheet riêng (Sheet2)

Ở đây phạm vi COUNTIF (danh sách từ một phạm vi) đề cập đến Trang tính 2

Ở định dạng có điều kiện, chúng ta không thể trực tiếp tham chiếu đến một trang tính khác. Chúng ta nên sử dụng chức năng INDIRECT cho điều đó

Vì vậy, thay vì đề cập đến Sheet2!A2:B11, tôi đã sử dụng indirect("Sheet2!A2:B11") trong COUNTIF

Có liên quan. Vai trò của hàm gián tiếp trong định dạng có điều kiện trong Google Sheets

ngoại lệ

Tôi đã đưa ra hai công thức tương tự để đánh dấu cờ lỗi màu đỏ trong menu thả xuống xác thực dữ liệu trong Google Trang tính. Nhưng các công thức trên có thể không hoạt động trong tất cả các trình đơn thả xuống xác thực dữ liệu

Đây là hai trường hợp ngoại lệ

• Xác thực dữ liệu để nhập giá trị từ danh sách theo thứ tự trong danh sách
• Giá trị khác biệt trong Danh sách thả xuống trong Google Trang tính

Vì tôi đã sử dụng một loại cài đặt cụ thể trong trình đơn thả xuống nên các quy tắc đánh dấu ở trên có thể không hoạt động trong các hướng dẫn được liên kết ở trên

Xóa các cờ báo lỗi trong Menu thả xuống

Tôi có thể xóa cờ lỗi trong trình đơn thả xuống trong Google Trang tính không?

Câu trả lời là CÓ. Bao gồm các mục không hợp lệ trong “Danh sách các mục” hoặc “Danh sách từ một phạm vi” để xóa các cờ lỗi màu đỏ. Tôi nghĩ rằng nó đi mà không cần nói

Bạn có thể áp dụng định dạng có điều kiện cho Xác thực dữ liệu không?

Bạn có thể mã màu Xác thực dữ liệu trong Google Trang tính không?

Định dạng có điều kiện có hoạt động với danh sách thả xuống không?

Định dạng có điều kiện cho các ô có Xác thực dữ liệu là gì?