Shade Alternate Rows with Excel Định dạng có điều kiện

01 trên 01

Excel Shading hàng / cột công thức

Shading dòng thay thế với định dạng có điều kiện. © Ted Tiếng Pháp

Hầu hết thời gian, định dạng có điều kiện được sử dụng để thay đổi màu ô hoặc phông chữ để phản hồi dữ liệu được nhập vào ô như ngày quá hạn hoặc chi tiêu ngân sách quá cao và thường được thực hiện bằng các điều kiện đặt trước của Excel.

Ngoài các tùy chọn được đặt trước, tuy nhiên, bạn cũng có thể tạo quy tắc định dạng có điều kiện tùy chỉnh bằng cách sử dụng công thức Excel để kiểm tra các điều kiện do người dùng chỉ định.

Một công thức như vậy kết hợp các chức năng MODROW , có thể được sử dụng để tự động che đi các hàng dữ liệu thay thế có thể làm cho dữ liệu đọc trong các trang tính lớn, dễ dàng hơn nhiều.

Dynamic Shading

Một ưu điểm khác khi sử dụng công thức để thêm tô bóng là tô bóng là động , nghĩa là nó thay đổi nếu số hàng thay đổi.

Nếu các hàng được chèn vào hoặc bị xóa, thì điều chỉnh bóng hàng sẽ tự điều chỉnh để duy trì mẫu.

Lưu ý: Các hàng thay thế không phải là tùy chọn duy nhất với công thức này. Bằng cách thay đổi nó một chút, như được thảo luận dưới đây, công thức có thể che bất kỳ mẫu hàng nào. Nó thậm chí có thể được sử dụng để tô bóng các cột thay vì các hàng nếu bạn chọn.

Ví dụ: Công thức tô bóng

Bước đầu tiên là làm nổi bật phạm vi ô được tô bóng vì công thức chỉ ảnh hưởng đến các ô được chọn này.

  1. Mở trang tính Excel — một trang tính trống sẽ làm việc cho hướng dẫn này
  2. Đánh dấu một dải ô trong trang tính
  3. Nhấp vào tab Trang chủ của ruy-băng
  4. Nhấp vào biểu tượng Định dạng có điều kiện để mở menu thả xuống
  5. Chọn tùy chọn Quy tắc mới để mở hộp thoại Quy tắc định dạng mới
  6. Nhấp vào công thức Sử dụng công thức để xác định ô nào định dạng tùy chọn từ danh sách ở đầu hộp thoại
  7. Nhập công thức sau vào hộp bên dưới các giá trị Định dạng nơi giá trị này là tùy chọn đúng trong nửa dưới của hộp thoại = MOD (ROW (), 2) = 0
  8. Nhấp vào nút Định dạng để mở hộp thoại Định dạng Ô
  9. Nhấp vào tab Điền để xem các tùy chọn màu nền
  10. Chọn màu để sử dụng để tô bóng các hàng thay thế của phạm vi đã chọn
  11. Nhấp OK hai lần để đóng hộp thoại và quay lại trang tính
  12. Các hàng thay thế trong phạm vi được chọn sẽ được tô màu với màu nền đã chọn

Diễn giải công thức

Cách Excel đọc công thức này là:

MOD và ROW làm gì

Mẫu phụ thuộc vào chức năng MOD trong công thức. MOD nào là phân chia số hàng (được xác định bởi hàm ROW) theo số thứ hai bên trong dấu ngoặc và trả về phần dư hoặc mô đun đôi khi nó được gọi.

Tại thời điểm này, định dạng có điều kiện chiếm và so sánh mô đun với số sau dấu bằng. Nếu có một kết quả phù hợp (hoặc chính xác hơn nếu điều kiện là TRUE), hàng được tô bóng, nếu các con số ở hai bên của dấu bằng không khớp, thì điều kiện là FALSE và không có sự che bóng nào xảy ra cho hàng đó.

Ví dụ: trong hình ở trên, khi hàng cuối cùng trong phạm vi được chọn 18 được chia cho 2 bởi hàm MOD, phần còn lại là 0, vì vậy điều kiện của 0 = 0 là TRUE và hàng được tô bóng.

Mặt 17, mặt khác, khi chia cho 2 phần còn lại của 1, mà không bằng 0, do đó hàng đó là trái unshaded.

Các cột đổ bóng thay vì hàng

Như đã đề cập, các công thức được sử dụng để tô bóng các hàng thay thế cũng có thể được sửa đổi để cho phép tô bóng các cột. Thay đổi được yêu cầu là sử dụng hàm COLUMN thay vì hàm ROW trong công thức. Khi làm như vậy, công thức sẽ trông như thế này:

= MOD (COLUMN (), 2) = 0

Lưu ý: Các thay đổi đối với công thức tô bóng để thay đổi mẫu tô bóng được nêu dưới đây cũng áp dụng cho công thức tô bóng.

Thay đổi công thức, thay đổi mẫu tô bóng

Việc thay đổi mẫu tô bóng được thực hiện dễ dàng bằng cách thay đổi một trong hai số trong công thức.

Divisor không thể là Zero hoặc One

Số bên trong dấu ngoặc được gọi là số chia vì nó là số thực hiện phép chia trong hàm MOD. Nếu bạn nhớ lại trong lớp toán học chia cho số không được phép và nó cũng không được phép trong Excel. Nếu bạn cố gắng sử dụng một số không bên trong dấu ngoặc ở vị trí của 2, chẳng hạn như:

= MOD (ROW (), 0) = 2

bạn sẽ không có bóng ở tất cả trong phạm vi.

Ngoài ra, nếu bạn cố gắng sử dụng số một cho ước số để công thức trông giống như:

= MOD (ROW (), 1) = 0

mỗi hàng trong phạm vi sẽ được tô bóng. Điều này xảy ra bởi vì bất kỳ số nào được chia cho một phần còn lại bằng 0, và hãy nhớ, khi điều kiện 0 = 0 là TRUE, hàng được tô bóng.

Thay đổi toán tử, thay đổi mẫu đổ bóng

Để thực sự thay đổi mẫu, thay đổi toán tử điều kiện hoặc so sánh (dấu bằng) được sử dụng trong công thức thành dấu nhỏ hơn (<).

Bằng cách thay đổi = 0 thành <2 (nhỏ hơn 2), ví dụ, hai hàng với nhau có thể được tô bóng. Làm cho rằng <3, và bóng sẽ được thực hiện trong nhóm ba hàng.

Chỉ báo trước cho việc sử dụng toán tử nhỏ hơn là đảm bảo rằng số bên trong dấu ngoặc lớn hơn số ở cuối công thức. Nếu không, mọi hàng trong phạm vi sẽ được tô bóng.