agwebrunner.com

Les funcions lògiques són algunes de les més populars i útils a Excel. Poden provar valors en altres cel·les i realitzar accions dependents del resultat de la prova. Això ens ajuda a automatitzar tasques als nostres fulls de càlcul.

Com s'utilitza la funció IF

La funció IF és la principal funció lògica a Excel i, per tant, és la que s’ha d’entendre primer. Apareixerà nombroses vegades al llarg d’aquest article.

Vegem l’estructura de la funció IF i, a continuació, vegem alguns exemples del seu ús.

La funció IF accepta 3 bits d'informació:

= IF (prova_lògica, [valor_si_veritat], [valor_si_false])

• prova_lògica: Aquesta és la condició per verificar la funció.
• valor_if_veritat: L'acció a realitzar si es compleix la condició o és certa.
• value_if_false: Acció a realitzar si la condició no es compleix o és falsa.

Operadors de comparació per utilitzar amb funcions lògiques

En realitzar la prova lògica amb valors de cel·la, heu de familiaritzar-vos amb els operadors de comparació. Podeu veure un desglossament d’aquests a la taula següent.

Vegem-ne alguns exemples en acció.

Funció IF Exemple 1: valors de text

En aquest exemple, volem provar si una cel·la és igual a una frase específica. La funció IF no distingeix entre majúscules i minúscules, de manera que no té en compte les majúscules i minúscules.

La següent fórmula s'utilitza a la columna C per mostrar "No" si la columna B conté el text "Completat" i "Sí" si conté alguna cosa més.

= IF (B2 = "Completat", "No", "Sí")

Tot i que la funció IF no distingeix entre majúscules i minúscules, el text ha de coincidir exactament.

Funció IF Exemple 2: valors numèrics

La funció IF també és ideal per comparar valors numèrics.

A la fórmula següent, comprovem si la cel·la B2 conté un nombre superior o igual a 75. Si ho fa, mostrem la paraula "Passa" i, si no, la paraula "Falla".

= IF (B2> = 75, "Aprovat", "Fallat")

La funció IF és molt més que mostrar text diferent al resultat d'una prova. També el podem utilitzar per executar diferents càlculs.

En aquest exemple, volem fer un descompte del 10% si el client gasta una determinada quantitat de diners. Farem servir 3.000 GBP com a exemple.

= IF (B2> = 3000, B2 * 90%, B2)

La part B2 * 90% de la fórmula és una manera de restar un 10% del valor de la cel·la B2. Hi ha moltes maneres de fer-ho.

L’important és que pugueu utilitzar qualsevol fórmula a valor_si_veritat o bé value_if_false seccions. I executar diferents fórmules dependents dels valors d'altres cèl·lules és una habilitat molt poderosa.

Funció IF Exemple 3: Valors de data

En aquest tercer exemple, fem servir la funció IF per fer un seguiment d’una llista de dates de venciment. Volem mostrar la paraula "Vencit" si la data de la columna B és anterior. Però si la data és futura, calculeu el nombre de dies fins a la data de venciment.

La fórmula següent s’utilitza a la columna C. Comprovem si la data de venciment a la cel·la B2 és inferior a la d’avui (la funció AVUI retorna la data d’avui del rellotge de l’ordinador).

= SI (B2<>

Què són les fórmules Nested IF?

És possible que abans hagueu sentit a parlar del terme IF imbricats. Això significa que podem escriure una funció IF dins d’una altra funció IF. És possible que vulguem fer-ho si tenim més de dues accions a realitzar.

Una funció IF és capaç de realitzar dues accions (la valor_si_veritat i value_if_false ). Però si incrustem (o anidem) una altra funció IF al fitxer value_if_false a la secció, podem realitzar una altra acció.

Agafeu aquest exemple on volem que aparegui la paraula "Excel·lent" si el valor de la cel·la B2 és superior o igual a 90, es mostra "Bo" si el valor és superior o igual a 75 i es mostra "Mala" si hi ha alguna cosa més .

= IF (B2> = 90, "Excel·lent", IF (B2> = 75, "Bo", "Mala"))

Ara hem ampliat la nostra fórmula més enllà del que només pot fer una funció IF. I podeu niar més funcions IF si cal.

Fixeu-vos en els dos claudàtors al final de la fórmula: un per a cada funció IF.

Hi ha fórmules alternatives que poden ser més netes que aquest enfocament IF imbricat. Una alternativa molt útil és la funció SWITCH a Excel.

Les funcions lògiques AND i OR

Les funcions AND i OR s’utilitzen quan es vol fer més d’una comparació a la fórmula. La funció IF només pot gestionar una condició o una comparació.

Prenem un exemple en què descomptem un valor un 10% en funció de l'import que un client gasta i quants anys són clients.

Per si soles, les funcions AND i OR retornaran el valor de TRUE o FALSE.

La funció AND només retorna TRUE si es compleixen totes les condicions i, en cas contrari, retorna FALS. La funció OR retorna TRUE si es compleixen una o totes les condicions i retorna FALSE només si no es compleixen les condicions.

Aquestes funcions poden provar fins a 255 condicions, de manera que certament no es limiten a només dues condicions com es demostra aquí.

A continuació es mostra l’estructura de les funcions AND i OR. S’escriuen igual. Només cal que substituïu el nom I per OR. La seva lògica és diferent.

= AND (lògic1, [lògic2] ...)

Vegem un exemple d’ambdós que avaluen dues condicions.

I exemple de funció

La funció AND s'utilitza a continuació per comprovar si el client gasta almenys 3.000 lliures esterlines i ha estat client durant almenys tres anys.

= AND (B2> = 3000, C2> = 3)

Podeu veure que retorna FALS per a Matt i Terry perquè, tot i que tots dos compleixen un dels criteris, han de complir amb la funció AND.

O Exemple de funció

La funció OR s'utilitza a continuació per comprovar si el client gasta almenys 3.000 lliures esterlines o ha estat client durant almenys tres anys.

= OR (B2> = 3000, C2> = 3)

En aquest exemple, la fórmula retorna TRUE per a Matt i Terry. Només Julie i Gillian fallen ambdues condicions i retornen el valor de FALS.

Utilitzant AND i OR amb la funció IF

Com que les funcions AND i OR retornen el valor de TRUE o FALSE quan s’utilitzen soles, és rar utilitzar-les per si soles.

En lloc d’això, normalment els utilitzeu amb la funció IF o dins d’una funció Excel, com ara el format condicional o la validació de dades, per dur a terme alguna acció retrospectiva si la fórmula s’avalua com a TRUE.

A la fórmula següent, la funció AND es troba a la prova lògica de la funció IF. Si la funció AND torna CERT, es descompta el 10% de l'import de la columna B; en cas contrari, no s’ofereix cap descompte i el valor de la columna B es repeteix a la columna D.

= IF (AND (B2> = 3000, C2> = 3), B2 * 90%, B2)

La funció XOR

A més de la funció OR, també hi ha una funció OR exclusiva. Això s’anomena funció XOR. La funció XOR es va introduir amb la versió d'Excel 2013.

Aquesta funció pot suposar un cert esforç per comprendre-la, de manera que es mostra un exemple pràctic.

L’estructura de la funció XOR és la mateixa que la funció OR.

= XOR (lògic1, [lògic2] ...)

En avaluar només dues condicions, torna la funció XOR:

• TRUE si qualsevol de les condicions es valora com a TRUE.
• FALS si ambdues condicions són TRUE o cap de les dues condicions és TRUE.

Això difereix de la funció OR perquè això retornaria TRUE si ambdues condicions fossin TRUE.

Aquesta funció es fa una mica més confusa quan s’afegeixen més condicions. A continuació, torna la funció XOR:

• CERT si un estrany el nombre de condicions torna TRUE.
• FALS si un fins i tot nombre de condicions donen lloc a TRUE, o si tot les condicions són FALSES.

Vegem un exemple senzill de la funció XOR.

En aquest exemple, les vendes es divideixen en dues meitats de l'any. Si un venedor ven 3.000 lliures esterlines o més a les dues meitats, se li assigna el patró d'or. Això s’aconsegueix amb una funció AND amb IF com abans de l’article.

Però si venen 3.000 lliures esterlines o més a la meitat, volem assignar-los la condició de plata. Si no venen 3.000 lliures esterlines o més en ambdues coses, res.

La funció XOR és perfecta per a aquesta lògica. La fórmula següent s'introdueix a la columna E i mostra la funció XOR amb IF per mostrar "Sí" o "No" només si es compleix qualsevol condició.

= IF (XOR (B2> = 3000, C2> = 3000), "Sí", "No")

La funció NO

La funció lògica final que es comentarà en aquest article és la funció NO, i hem deixat la més simple per a la fi. Tot i que de vegades pot ser difícil veure els usos del món real de la funció al principi.

La funció NOT inverteix el valor del seu argument. Per tant, si el valor lògic és CERT, torna FALS. I si el valor lògic és FALS, tornarà CERT.

Això serà més fàcil d’explicar amb alguns exemples.

L’estructura de la funció NOT és;

= NO (lògic)

NOT Funció Exemple 1

En aquest exemple, imaginem que tenim una oficina central a Londres i després molts altres llocs regionals. Volem mostrar la paraula "Sí" si el lloc és qualsevol cosa excepte Londres i "No" si es tracta de Londres.

La funció NOT s'ha aniat a la prova lògica de la funció IF següent per invertir el resultat TRUE.

= SI (NOT (B2 = "Londres"), "Sí", "No")

Això també es pot aconseguir utilitzant l'operador lògic NO de. A continuació es mostra un exemple.

= IF (B2 "Londres", "Sí", "No")

NO Funció Exemple 2

La funció NO és útil quan es treballa amb funcions d'informació a Excel. Aquests són un grup de funcions a Excel que comproven alguna cosa i retornen TRUE si la comprovació és un èxit i FALS si no ho és.

Per exemple, la funció ISTEXT comprovarà si una cel·la conté text i retornarà TRUE si ho fa i FALSE si no. La funció NOT és útil perquè pot invertir el resultat d’aquestes funcions.

A l'exemple següent, volem pagar a un venedor el 5% de l'import que venen. Però si no van vendre res, la paraula "Cap" és a la cel·la i això produirà un error a la fórmula.

La funció ISTEXT s’utilitza per comprovar la presència de text. Això retorna TRUE si hi ha text, de manera que la funció NOT la inverteix a FALS. I l’IF realitza el seu càlcul.

= SI (NO (ISTEXT (B2)), B2 * 5%, 0)

Dominar les funcions lògiques us proporcionarà un gran avantatge com a usuari d’Excel. És molt útil poder provar i comparar valors a les cel·les i realitzar diferents accions basades en aquests resultats.

Aquest article ha tractat sobre les millors funcions lògiques que s’utilitzen actualment. Les versions recents d'Excel han vist la introducció de més funcions a aquesta biblioteca, com ara la funció XOR esmentada en aquest article. Mantenir-se al dia amb aquestes noves incorporacions us mantindrà per davant de la multitud.