Sử dụng các trường hợp cho các công thức tùy chỉnh để định dạng có điều kiện
Mục lục
I. Giới thiệu
Công thức tùy chỉnh cho định dạng có điều kiện cung cấp nhiều cách đa dạng hơn để trình bày dữ liệu và làm nổi bật các giá trị chính xác mà bạn cần. Tính linh hoạt của tính năng này được thể hiện với một số trường hợp sử dụng trong bài viết này. Để biết hướng dẫn cho các hoạt động cơ bản, hãy xem Sử dụng Định dạng Có điều kiện trong Bảng .
II. Quy tắc công thức tùy chỉnh
Đây là cách công thức tùy chỉnh hoạt động cho định dạng có điều kiện.
-
Kết quả công thức chỉ có thể là TRUE (hoặc 1) hoặc FALSE (hoặc 0).
-
Công thức được nhập trong Quy tắc sẽ áp dụng cho ô đầu tiên trong phạm vi được chỉ định (nghĩa là ô ở góc trên bên trái) và thay đổi tương ứng dựa trên ô (tham chiếu ô tương đối). Xem ví dụ sau, trong đó chúng tôi đặt các ô đáp ứng điều kiện được chỉ định để chuyển xanh lá cây:
-
Đối với công thức A1 > 3, giá trị trong mỗi ô được đánh giá dựa trên 3 và được đánh dấu xanh lá cây cho kết quả TRUE.
-
-
Đối với công thức C2 > 3, phạm vi trong hộp màu đỏ (C2: F5) được đánh giá dựa trên 3 và các ô tương ứng trong phạm vi được chỉ định (A1: D4) được đánh dấu trong xanh lá cây cho kết quả là TRUE. Ví dụ này chứng minh rằng định dạng có điều kiện không nhất thiết phải dựa trên các giá trị trong phạm vi được chỉ định.
-
Chú ý đến loại tham chiếu ô (tuyệt đối, tương đối, hỗn hợp) được sử dụng vì kết quả sẽ khác nhau. Ví dụ trên sử dụng tham chiếu tương đối: nghĩa là số hàng và cột cho mỗi ô trong công thức thay đổi theo vị trí của ô.
III. Các trường hợp sử dụng
Biểu đồ Gantt đơn giản hóa
-
Công thức
VÀ (C $2 > = $A3, C $2 < = $B3)
-
Mô tả
Bằng cách so sánh ngày tương ứng với ô (Hàng 2) với ngày bắt đầu (Cột A) và ngày kết thúc (Cột B), bạn đánh dấu một ô đáp ứng các điều kiện trong xanh lá cây.
Lưu ý rằng tham chiếu ô hỗn hợp được sử dụng trong công thức: C $2 chỉ ra rằng hàng 2 là cố định và $A3 và $B3 là các cột cố định vì đây là những ngày được đánh giá. Ví dụ: khi định dạng có điều kiện áp dụng cho ô D3, công thức là: = AND ( D $2 > = $A3, D $2 < = $B3).
Biểu đồ Gantt nâng cao
-
Công thức
-
Để bắt đầu (tô sáng màu vàng)
-
VÀ (J $5 > = $D8, J $5 < = $E8)
-
Đang tiến hành (xanh lá cây đánh dấu)
VÀ (J $5 > = $F8, J $5 < = $G8)
-
Bị trì hoãn (đánh dấu màu đỏ)
VÀ ($G8 > $E8, J $5 > $E8, J $5 < = $G8)
-
Mô tả
Bằng cách so sánh ngày tháng trong hàng 5 với thời gian bắt đầu ước tính (Cột D), thời gian kết thúc ước tính (Cột E), thời gian bắt đầu thực tế (Cột F) và thời gian kết thúc thực tế (Cột G), bạn có thể đánh dấu các ô dựa trên trạng thái của chúng.
Xác định sự mâu thuẫn
-
Công thức
$A2 < > $B2
-
Mô tả
"< >" cho biết "không bằng". Bằng cách sử dụng tham chiếu hỗn hợp, các cột A và B được cố định và công thức có thể được áp dụng xuống các hàng và các hàng không giống nhau sẽ được đánh dấu.
Đánh dấu các giá trị còn thiếu
-
Công thức
COUNTIF ($C $3: $C $10, A3) = 0
-
Mô tả
Hàm COUNTIF COUNTIF được sử dụng để tìm kiếm thông qua Danh sách B cho các tên trong Danh sách A. Nếu số lần tên xuất hiện là 0, nó sẽ được đánh dấu vì nó cho biết tên bị thiếu trong Danh sách B. Lưu ý rằng phạm vi tìm kiếm C3: C10 là cố định.
Tìm học sinh có điểm đáp ứng nhiều yêu cầu
-
Công thức
VÀ ($B2 > 70, $D2 > 80)
-
Mô tả
Hàm AND được sử dụng để tìm học sinh có điểm toán (cột B) lớn hơn 70 và điểm tiếng Anh (cột D) lớn hơn 80. Lưu ý rằng ký hiệu "$" được sử dụng trong cột B và D có nghĩa là chúng được cố định.
Đánh dấu giá trị tìm kiếm
-
Công thức
ISNUMBER (TÌM KIẾM ($E $2, B2))
-
Mô tả
Hàm TÌM KIẾM được sử dụng để tìm kiếm giá trị trong E2 trong phạm vi được chỉ định (cột B). Hàm này trả về vị trí xuất hiện đầu tiên của giá trị được tìm kiếm ở định dạng số. Vì kết quả cho các công thức định dạng có điều kiện phải là TRUE hoặc FALSE, công ISNUMBER công thức được sử dụng để xác định xem kết quả có phải là giá trị số hay không.
Hiển thị ngày có sẵn tiếp theo
-
Công thức
VÀ (B2 > HÔM NAY (), B2 < = (HÔM NAY () + 45))
-
Mô tả
Nếu bạn muốn nhanh chóng tìm thấy ngày có sẵn tiếp theo trong 1,5 tháng tới, bạn có thể sử dụng công TODAY công thức để lấy ngày hôm nay, sử dụng "(TODAY () + 45)" để tìm ngày 45 ngày kể từ bây giờ và sau đó bạn có thể sử dụng công thức AND để xác định phạm vi ngày cần được đánh dấu.
