Ombra alternatives amb format format condicional Excel

01 de 01

Fórmules de columnes d'ombrejat Excel / Fórmula de columnes

Ombrejant files alternatives amb format condicional. © Ted Francès

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.

  1. Obriu un full de càlcul d'Excel: un full de càlcul en blanc funcionarà per a aquest tutorial
  2. Ressalteu un interval de cel·les al full de càlcul
  3. Feu clic a la pestanya Inici de la cinta
  4. Feu clic a la icona de format condicional per obrir el menú desplegable
  5. Trieu l'opció Regla nova per obrir el quadre de diàleg Regla de format nou
  6. 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
  7. 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
  8. Feu clic al botó Format per obrir el quadre de diàleg Format cel·les
  9. Feu clic a la pestanya " Enllaça " per veure les opcions de color de fons
  10. Seleccioneu un color que s'utilitzarà per ombrejar les files alternatives del rang seleccionat
  11. Feu clic a D' acord dues vegades per tancar el quadre de diàleg i tornar al full de treball
  12. 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:

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) = 0

Nota: 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.

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) = 2

No 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) = 0

cada 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.