01 de 01
Fórmules de columnes d'ombrejat Excel / Fórmula de columnes
La major part del temps, el format condicional s'utilitza per canviar els colors de les cel·les o fonts com a resposta a les dades ingressades en una cel·la com ara una data vençuda o una despesa pressupostària massa alta, i normalment es fa amb les condicions predefinides d'Excel.
A més de les opcions prefixades, però, també és possible crear regles de format condicional personalitzades utilitzant fórmules d'Excel per provar condicions específiques de l'usuari.
Una de les fórmules que combina les funcions MOD i ROW es pot utilitzar per omplir automàticament files alternatives de dades que poden fer que les dades de lectura en fulls de treball siguin molt més fàcils.
Ombrejat dinàmic
Un altre avantatge per utilitzar la fórmula per afegir l'ombrejat de files és que l'ombrejat és dinàmic, el que significa que canvia si el nombre de files canvia.
Si s'insereixen o s'eliminen files, l'ombrejat de fila s'ajusta per mantenir el patró.
Nota: Les files alternatives no són l'única opció amb aquesta fórmula. En canviar-la lleugerament, com es descriu a continuació, la fórmula pot ombrejar qualsevol patró de files. Fins i tot es pot utilitzar per ombreir columnes en comptes de files si així ho voleu.
Exemple: Fórmula de llums d'ombrejat
El primer pas és ressaltar l'abast de les cel·les a ombrejar perquè la fórmula només afecta aquestes cel·les seleccionades.
- Obriu un full de càlcul d'Excel: un full de càlcul en blanc funcionarà per a aquest tutorial
- Ressalteu un interval de cel·les al full de càlcul
- Feu clic a la pestanya Inici de la cinta
- Feu clic a la icona de format condicional per obrir el menú desplegable
- Trieu l'opció Regla nova per obrir el quadre de diàleg Regla de format nou
- Feu clic a Utilitza una fórmula per determinar quines cel·les formateu l' opció de la llista a la part superior del quadre de diàleg
- Introduïu la fórmula següent al quadre que hi ha a sota dels valors de format on aquest valor és l' opció veritable a la meitat inferior del quadre de diàleg = MOD (ROW (), 2) = 0
- Feu clic al botó Format per obrir el quadre de diàleg Format cel·les
- Feu clic a la pestanya " Enllaça " per veure les opcions de color de fons
- Seleccioneu un color que s'utilitzarà per ombrejar les files alternatives del rang seleccionat
- Feu clic a D' acord dues vegades per tancar el quadre de diàleg i tornar al full de treball
- Les files alternatives en el rang seleccionat ara s'han d'ombrejar amb el color de farciment de fons seleccionat
Interpretació de la Fórmula
Com es llegeix aquesta fórmula per Excel és:
- el número 2 de la fórmula determina que el patró d'ombrejat repeteix cada segona fila de l'interval seleccionat
- la condició de = 0 a la fórmula determina que la primera fila del rang no està ombrejada, cosa que es fa perquè aquesta fila sovint conté encapçalaments que tenen el seu propi format.
Què fan MOD i ROW?
El patró depèn de la funció MOD a la fórmula. El que MOD fa és dividir el número de fila (determinat per la funció ROW) pel segon número dins dels claudàtors i retorna la resta o el mòdul a vegades anomenat.
En aquest punt, es fa càrrec del format condicional i es compara el mòdul amb el número després del signe igual. Si hi ha una coincidència (o més correctament si la condició és TRUE), la fila està ombrejada, si els nombres a banda i banda del signe igual no coincideixen, la condició és FALS i no hi ha ombrejat per a aquesta fila.
Per exemple, a la imatge de dalt, quan la última fila del rang seleccionat 18 es divideix per 2 amb la funció MOD, la resta és 0, de manera que la condició de 0 = 0 és TRUE i la fila està ombrejada.
La fila 17, d'altra banda, quan es divideix per 2, deixa una resta d'1, que no és igual a 0, de manera que la fila queda sense encobrir.
Columnes ombrejades en lloc de files
Com es va esmentar, les fórmules utilitzades per ombreir fileres alternatives es poden modificar per permetre també columnes d'ombreig. El canvi requerit és utilitzar la funció COLUMNA en comptes de la funció ROW a la fórmula. En fer-ho, la fórmula seria així:
= MOD (COLUMN (), 2) = 0Nota: els canvis a la fórmula de les línies d'ombrejat per alterar el patró d'ombrejat que es descriuen a continuació també s'apliquen a la fórmula de columnes ombrejades.
Canvieu la fórmula, canvieu el patró d'ombrejat
Canviar el patró d'ombreig es fa fàcilment canviant qualsevol dels dos números de la fórmula.
- Perquè l'ombreig de la fila comenci per la primera fila en lloc del segon, al final de la fórmula, canvieu = 0 a = 1 ;
- Per tenir ombra cada tercera o quarta fila en comptes de files alternatives, canvieu el 2 a la fórmula a un 3 o un 4.
El divisor no pot ser zero ni un
El número dins dels claudàtors s'anomena divisor ja que és el número que fa la divisió en la funció MOD. Si es recorda de nou en la classe de matemàtiques que es divideix per zero, no estava permès i no està permès a Excel tampoc. Si intenteu utilitzar un zero dins dels claudàtors en lloc del 2, com ara:
= MOD (ROW (), 0) = 2No tindreu cap tipus d'ombreig en el rang.
Alternativament, si intenteu utilitzar el número u per al divisor, la fórmula és la següent:
= MOD (ROW (), 1) = 0cada fila de la gamma serà ombrejada. Això passa perquè qualsevol nombre dividit per un deixa una resta de zero, i recordeu, quan la condició de 0 = 0 és CERT, la fila es ombreja.
Canvieu l'operador, canvieu el patró d'ombrejat
Per canviar realment el patró, canvieu l' operador de condicional o de comparació (el signe d'igual) que s'utilitza a la fórmula a menys del signe (<).
Si canvieu = 0 a <2 (menys de 2), per exemple, es poden ombrejar dues files junts. Feu que <3, i l'ombrejat es farà en grups de tres files.
L'única excepció per utilitzar l'operador menys que per assegurar-se que el número que hi ha entre els claudàtors és més gran que el número al final de la fórmula. Si no, totes les files del rang estaran ombrejades.