01 trên 01
Excel Shading hàng / cột công thức
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 MOD và ROW , 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.
- Mở trang tính Excel — một trang tính trống sẽ làm việc cho hướng dẫn này
- Đánh dấu một dải ô trong trang tính
- Nhấp vào tab Trang chủ của ruy-băng
- Nhấp vào biểu tượng Định dạng có điều kiện để mở menu thả xuống
- Chọn tùy chọn Quy tắc mới để mở hộp thoại Quy tắc định dạng mới
- 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
- 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
- Nhấp vào nút Định dạng để mở hộp thoại Định dạng Ô
- Nhấp vào tab Điền để xem các tùy chọn màu nền
- Chọn màu để sử dụng để tô bóng các hàng thay thế của phạm vi đã chọn
- Nhấp OK hai lần để đóng hộp thoại và quay lại trang tính
- 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à:
- số 2 trong công thức xác định rằng mẫu tô bóng lặp lại mọi hàng thứ hai trong phạm vi đã chọn
- điều kiện của = 0 trong công thức xác định rằng hàng đầu tiên trong phạm vi không được tô bóng - được thực hiện bởi vì hàng này thường chứa các tiêu đề có định dạng riêng của chúng.
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) = 0Lư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.
- Để tô bóng hàng bắt đầu bằng hàng đầu tiên thay vì hàng thứ hai, ở cuối công thức, thay đổi = 0 thành = 1 ;
- Để có hàng thứ ba hoặc thứ tư được tô bóng thay vì các hàng thay thế, hãy thay đổi 2 trong công thức thành 3 hoặc 4.
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) = 2bạ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) = 0mỗ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.