Què són les fórmules i les funcions a Excel i com utilitzar-les?

Les dades s’utilitzen només quan realment podeu treballar-hi i Excel és una eina que proporciona una gran quantitat de comoditat tant quan heu de formular equacions pròpies o fer ús de les incorporades. En aquest article, aprendreu com podeu treballar realment amb aquestes funcions d’anuncis de Fórmules Excel.

Aquí teniu un repàs ràpid als temes que es tracten aquí:

• Què és una fórmula?
• Escriptura de fórmules Excel
• Edició d’una fórmula
• Copieu / enganxeu una fórmula
• Amaga les fórmules a Excel
• Fórmules Excel de la precedència de l’operador
• Què són les funcions a Excel?
• Funcions més importants

Què és una fórmula?

En general, una fórmula és una forma condensada de representar alguna informació en termes de símbols. A Excel, les fórmules són expressions que es poden introduir a les cel·les d’un full Excel i, en conseqüència, es mostren les seves sortides.

Les fórmules Excel poden ser dels tipus següents:

Escriptura de fórmules d'Excel:

Per escriure una fórmula en una cel·la de full d'Excel, podeu fer el següent:

• Seleccioneu la cel·la on voleu que es mostri el resultat
• Escriviu un signe “=” inicialment per fer saber a Excel que introduïu una fórmula en aquesta cel·la
• Després, podeu escriure les adreces de la cel·la o especificar els valors que voleu calcular
• Per exemple, si voleu afegir els valors de dues cel·les, podeu escriure l'adreça de la cel·la de la manera següent:

• També podeu seleccionar les cel·les la suma de les quals vulgueu calcular seleccionant totes les cel·les necessàries mentre premeu la tecla Ctrl:

Edició d'una fórmula:

En cas que vulgueu editar alguna fórmula introduïda prèviament, simplement seleccioneu la cel·la que conté la fórmula de destinació i a la barra de fórmules podeu fer els canvis desitjats. A l'exemple anterior, he calculat la suma d'A1 i A2. Ara, editaré el mateix i canviaré la fórmula per calcular el producte dels valors presents en aquestes dues cel·les:

Un cop fet això, premeu Retorn per veure la sortida desitjada.

Copiar o enganxar una fórmula:

Excel és molt útil quan heu de copiar / enganxar fórmules. Sempre que copieu una fórmula, Excel s’encarrega automàticament de les referències de cel·les necessàries en aquesta posició. Això es fa mitjançant un sistema anomenat as Adreces de cel·la relatives .

Per copiar una fórmula, seleccioneu la cel·la que conté la fórmula original i arrossegueu-la fins a la cel·la que requereixi una còpia d'aquesta fórmula de la manera següent:

Com podeu veure a la imatge, la fórmula s’escriu originalment en A3 i després l’he arrossegat al llarg de B3 i C3 per calcular la suma de B1, B2 i C1, C2 sense escriure exclusivament les adreces de les cel·les.

En cas que canviï els valors d'alguna de les cel·les, la sortida s'actualitzarà automàticament per Excel d'acord amb el fitxer Adreces de cel·la relatives , Adreces de cel·la absolutes o bé Adreces de cel·la mixta .

Amaga les fórmules a Excel:

En cas que vulgueu amagar alguna fórmula d’un full Excel, podeu fer-ho de la següent manera:

• Seleccioneu les cel·les la fórmula de la qual vulgueu amagar
• Obriu la finestra Tipus de lletra i seleccioneu el tauler de protecció
• Marqueu l'opció Oculta i feu clic a D'acord
• A continuació, a la pestanya de la cinta, seleccioneu Revisa
• Feu clic a Protegeix el full (les fórmules no funcionaran si no ho feu)
• Excel us demanarà que introduïu una contrasenya per mostrar les fórmules per al seu ús futur

Precedència de l'operador de les fórmules d'Excel:

Les fórmules Excel segueixen les regles BODMAS (Brackets Order Division Multiplication Addition Rest). Si teniu una fórmula que conté claudàtors, l'expressió entre claudàtors es resoldrà abans que qualsevol altra part de la fórmula completa. Mireu la imatge següent:

Com podeu veure a l'exemple anterior, tinc una fórmula que consisteix en un claudàtor. Així doncs, d’acord amb les regles BODMAS, Excel trobarà primer la diferència entre A2 i B1 i després afegirà el resultat amb A1.

Què hi ha 'Funcions' a Excel?

En general, una funció defineix una fórmula que s’executa en un ordre determinat. Excel proporciona un gran nombre de fitxers funcions incorporades que es pot utilitzar per calcular el resultat de diverses fórmules.

Les fórmules a Excel es divideixen en les categories següents:

Ara, comprovem com fer ús d’algunes de les fórmules Excel més importants i d’ús més comú.

Funcions Excel més importants:

A continuació, es detallen algunes de les funcions Excel més importants, juntament amb les seves descripcions i exemples.

DATA:

Una de les funcions Data més importants i àmpliament utilitzades a Excel és la funció DATE. La seva sintaxi és la següent:

DATA (any, mes, dia)

Aquesta funció retorna un número que representa la data donada en el format de data i hora de MS Excel. La funció DATE es pot utilitzar de la següent manera:

EXEMPLE:

1. = DATA (2019,11,7)
2. = DATE (2019,11,7) -7 (retorna la data actual - set dies)

DIA:

Aquesta funció retorna el valor del dia del mes (1-31). La seva sintaxi és la següent:

DAY (número_de_seria)

Aquí, serial_number és la data el dia del qual voleu recuperar. Es pot donar de qualsevol manera, com el resultat d'alguna altra funció, subministrada per la funció DATE o una referència de cel·la.

EXEMPLE:

1. = DIA (A7)
2. = DIA (AVUI ())
3. = DIA (DATA (2019, 11,8))

MES:

Igual que la funció DIA, Excel proporciona una altra funció, és a dir, la funció MES per recuperar el mes a partir d’una data específica. La sintaxi és la següent:

MES (número_de_seria)

EXEMPLE:

1. = MES (AVUI ())
2. = MES (DATA (2019, 11,8))

Percentatge:

Com tots sabem, el percentatge és la proporció calculada com a fracció de 100. Es pot denotar de la següent manera:

Percentatge = (part / sencer) x 100

A Excel podeu calcular el percentatge de valors desitjats. Per exemple, si teniu els valors parcials i sencers presents a A1 i A2 i voleu calcular el percentatge, podeu fer-ho de la següent manera:

• Seleccioneu la cel·la on vulgueu mostrar el resultat
• Escriviu el signe '='
• A continuació, escriviu la fórmula A1 / A2 i premeu Retorn
• Al grup de números de pestanyes d'inici, seleccioneu el símbol '%'

SI:

La sentència IF és una sentència condicional que retorna True quan es compleix la condició especificada i Flase quan la condició no ho és. Excel proporciona una funció “IF” integrada que serveix per a aquest propòsit. La seva sintaxi és la següent:

IF (prova_lògica, valor_si_veritat, valor_si_false)

Aquí, prova_lògica és la condició que s'ha de comprovar

EXEMPLE:

• Introduïu els valors a comparar
• Seleccioneu la cel·la que mostrarà la sortida
• Escriviu la barra de fórmules '= IF (A1 = A2,' Sí ',' No ')' i premeu Intro

VISUALITZACIÓ:

Aquesta funció s'utilitza per buscar i obtenir algunes dades particulars d'una columna d'un full d'Excel. La 'V' de VLOOKUP significa cerca vertical. És una de les fórmules més importants i àmpliament utilitzades a Excel i, per poder utilitzar aquesta funció, cal ordenar la taula en ordre ascendent. La sintaxi d'aquesta funció és la següent:

VLOOKUP (lookup_value, table_array, col_index, num_range, lookup)

on,

valor_cerca és el valor que cal cercar

taula_array és la taula que es busca

col_index és la columna de la qual s'ha de recuperar el valor

range_lookup (opcional) retorna TRUE durant aprox. partit i FALS per a un partit exacte

EXEMPLE:

Com podeu veure a la imatge, el valor que he especificat és 2 i l’interval de la taula es troba entre A1 i D4. Vull obtenir el nom de l’empleat, per tant he donat el valor de la columna com a 2 i, com que vull que coincideixi exactament, he utilitzat False per a la cerca d’interval.

Impost sobre la Renda:

Suposem que voleu calcular l’impost sobre la renda d’una persona amb un salari total de 300 dòlars. Haureu de calcular l’impost sobre la renda de la següent manera:

• Enumereu el salari total, les deduccions salarials, la renda imposable i el percentatge de l'impost sobre la renda
• Especifiqueu els valors de salari total, deduccions salarials
• A continuació, calculeu la renda imposable trobant la diferència entre el salari total i les deduccions salarials
• Finalment, calculeu l’import fiscal

SUMA:

La funció SUM a Excel calcula el resultat afegint tots els valors especificats a Excel. La sintaxi d'aquesta funció és la següent:

SUMA (número1, número2, ...)

Hi afegeix tots els números que s’especifiquen com a paràmetre.

EXEMPLE:

En cas que vulgueu calcular la suma de la despesa que heu gastat en la compra de verdures, enumereu tots els preus i, a continuació, utilitzeu la fórmula SUM de la següent manera:

Interès compost:

Per calcular l'interès compost, podeu fer ús d'una de les fórmules d'Excel anomenades FV. Aquesta funció retornarà el valor futur d’una inversió sobre la base d’un tipus d’interès i pagaments constants periòdics. La sintaxi d'aquesta funció és la següent:

FV (taxa, nper, pmt, pv, tipus)

Per calcular la taxa, haureu de dividir la tarifa anual pel nombre de períodes, és a dir, la taxa / períodes anuals. El nombre de períodes o nper es calcula multiplicant el termini (nombre d’anys) amb els períodes, és a dir, períodes de termini *. pmt significa pagament periòdic i pot incloure qualsevol valor, inclòs zero.

Penseu en el següent exemple:

A l'exemple anterior, he calculat l'interès compost per 500 dòlars a una taxa del 10% durant 5 anys i suposant que el valor del pagament periòdic és 0. Tingueu en compte que he utilitzat -B1, és a dir, se m'han tret 500 dòlars.

Mitjana:

La mitjana, com tots sabem, representa el valor mitjà d’un nombre de valors. A Excel, la mitjana es pot calcular fàcilment mitjançant la funció integrada anomenada 'MITJANA'. La sintaxi d'aquesta funció és la següent:

MITJÀ (número1, número2, ...)

EXEMPLE:

En cas que vulgueu calcular la nota mitjana obtinguda pels estudiants en tots els exàmens, podeu crear una taula i, a continuació, utilitzar la fórmula MITJANA per calcular la nota mitjana obtinguda per cada estudiant.

En l'exemple anterior, he calculat la nota mitjana de dos estudiants en dos exàmens. En cas que tingueu més de dos valors la mitjana dels quals s’ha de determinar, només heu d’especificar l’interval de cel·les en què estan presents els valors. Per exemple:

COMPTE:

La funció de recompte a Excel comptarà el nombre de cel·les que contenen números en un interval determinat. La sintaxi d'aquesta funció és la següent:

diferència entre javascript i jquery

COUNT (valor1, valor2, ...)

EXEMPLE:

En cas que vulgui calcular el nombre de cel·les que contenen números de la taula que he creat a l’exemple anterior, simplement hauré de seleccionar la cel·la on vull mostrar el resultat i fer ús de la funció COUNT de la següent manera:

RODÓ:

Per arrodonir els valors a alguns decimals específics, podeu fer servir la funció RODONAR. Aquesta funció retornarà un número arrodonint-lo al nombre especificat de decimals. La sintaxi d'aquesta funció és la següent:

RONDA (nombre, num_digits)

EXEMPLE:

Grau de troballa:

Per trobar notes, haureu de fer servir instruccions IF imbricades a Excel. Per exemple, a l'exemple Mitjà, havia calculat la nota mitjana obtinguda pels estudiants a les proves. Ara, per trobar les qualificacions assolides per aquests estudiants, hauré de crear una funció IF imbricada de la següent manera:

Com podeu veure, les notes mitjanes són presents a la columna G. Per calcular la qualificació, he utilitzat una fórmula IF imbricada. El codi és el següent:

= IF (G19> 90, 'A', (IF (G19> 75, 'B', IF (G19> 60, 'C', IF (G19> 40, 'D', 'F')))))

Després de fer-ho, només haureu de copiar la fórmula a totes les cel·les on vulgueu mostrar les qualificacions.

CLASSIFICACIÓ:

En cas que vulgueu determinar el rànquing assolit pels estudiants d'una classe, podeu fer servir una de les fórmules Excel integrades, és a dir, RANK. Aquesta funció retornarà el rang d'un rang especificat comparant un rang determinat en ordre ascendent o descendent. La sintaxi d'aquesta funció és la següent:

RANK (ref, número, comanda)

EXEMPLE:

Com podeu veure, a l'exemple anterior, he calculat el Rang dels estudiants mitjançant la funció Rang. Aquí, el primer paràmetre és la mitjana de notes assolides per cada estudiant i la matriu és la mitjana assolida per la resta d’alumnes de la classe. No he especificat cap ordre, per tant, la sortida es determinarà en ordre descendent. Per als rangs d'ordre ascendents, haureu d'especificar qualsevol valor diferent de zero.

COUNTIF:

Per tal de comptar les cel·les en funció d'algunes condicions determinades, podeu utilitzar una de les fórmules integrades d'Excel anomenades 'COUNTIF'. Aquesta funció retornarà el nombre de cel·les que compleixen alguna condició en un interval determinat. La sintaxi d'aquesta funció és la següent:

COUNTIF (interval, criteris)

EXEMPLE:

Com podeu veure, a l'exemple anterior, he trobat el nombre de cel·les amb valors superiors a 80. També podeu donar algun valor de text al paràmetre de criteri.

ÍNDEX:

La funció INDEX retorna un valor o referència de cel·la en una posició particular en un interval especificat. La sintaxi d'aquesta funció és la següent:

INDEX (matriu, fila_num, columna_num) o bé

INDEX (referència,fila_num, columna_num, àrea_num)

La funció índex funciona de la següent manera per a Matriu formulari:

• Si es proporciona el número de fila i el número de columna, retorna el valor que hi ha a la cel·la d'intersecció
• Si el valor de la fila s'estableix a zero, retornarà els valors presents a tota la columna de l'interval especificat
• Si el valor de la columna s'estableix a zero, retornarà els valors presents a tota la fila de l'interval especificat

La funció índex funciona de la següent manera per a Referència formulari:

• Retorna la referència de la cel·la on es creuen la fila i els valors de la columna
• El número_àrea indicarà quin rang s'ha d'utilitzar en cas que s'hagin subministrat diversos intervals

EXEMPLE:

Com podeu veure, a l'exemple anterior, he utilitzat la funció INDEX per determinar el valor present a la 2a fila i 4a columna per a l'interval de cel·les entre A18 i G20.

De la mateixa manera, també podeu utilitzar la funció INDEX especificant diverses referències de la manera següent:

Això ens porta al final d’aquest article sobre Fórmules i funcions d’Excel. Espero que ho tingueu clar amb tot el que us ha estat compartit. Assegureu-vos de practicar el màxim possible i de recuperar la vostra experiència.

Tens alguna pregunta? Esmenteu-lo a la secció de comentaris d’aquest bloc “Fórmules i funcions d’Excel” i us respondrem el més aviat possible.

Per obtenir coneixements en profunditat sobre qualsevol tecnologia de tendència juntament amb les seves diverses aplicacions, podeu inscriure-us a la publicació en directe amb assistència les 24 hores del dia, els 7 dies de la setmana i accés permanent