Funcions
Funcions i fórmules
A continuació es comenten les funcions utilitzades a l'aplicació.
SUMA
SUMA
La funció SUMA és prou coneguda.
=SUMA(rang)
REDONDEAR
Arrodoneix els càlculs a un nombre determinat de decimals.
=REDONDEAR(càlcul; nombre_decimals)
ABS
Retorna els càlculs en valor absolut, sense considerar el signe.
=ABS(càlculs)
ABS
Retorna els càlculs en valor absolut, sense considerar el signe.
=ABS(càlculs)
CONTARA
Compta el nombre de cel·les ocupades dins d'un rang, ja siguin de text o numèriques.
=CONTARA(rang)
La funció CONTARA serà necessària també per crear etiquetes de rang variable.
Compta el nombre de cel·les ocupades dins d'un rang, ja siguin de text o numèriques.
=CONTARA(rang)
La funció CONTARA serà necessària també per crear etiquetes de rang variable.
COINCIDIR
Ens dona la posició que ocupa un valor buscat dins d'un rang. Cal indicar el tipus de coincidència, si exacte (0); per excés (1) o per defecte(-1)
=COINCIDIR(valor_buscat; rang; coincidència)
SI
És la funció condicional, on podem definir que fer si es compleix la condició proposada i què fer en cas contrari.
=SI(condició; acció_si_es_compleix; acció_si_no_es_compleix)
SI niat
Es tracte de col·locar la funció SI de forma consecutiva. És a dir, que si la primera condició no es compleix, tindrem un altre SI on seguir establint opcions diferenciades.
=SI(primera_condició; accció_si_es_compleix;SI(segona_condició; acció_si_es_compleix; acció_si_no_es_compleix))
No convé definir mes de dos o tres SI ja que si no la fórmula es torna massa complexa i difícil d'entendre.
CONTAR.SI
Ens permet contar les cel·les ocupades que compleixen una condició. La condició s'ha d'expressar entre cometes.
=CONTAR.SI(rang; "condició")
SI.ERROR
S'utilitza freqüentment per evitar mostrar els errors que troba el sistema en realitzar alguna operació. #¡DIV0!, #N/A; etc.
Normalment, la funció s'escriu al principi de la fórmula i al final s'hi posa el que volem mostrar en lloc de l'error del sistema.
=SI.ERROR((aqui_la_fórmula);"")
En aquest cas, si el resultat de la fórmula donés un error, la cel·la es mostraria en blanc.
VERDADERO/FALSO
El sistema retorna aquests resultats si una proposició es compleix o no.
Si volem comprovar si dos valors son iguals en dues cel·les podriem escriure la fórmula
=cel·la1 =cel·la2
I el sistema ens retornaria VERDADERO o FALSO segons que fossin iguals o no.
IZQUIERDA
Ens retorna la part de text de l'esquerra que indiquem extraure.
=IZQUIERDA(cel·la; nombre_de_caràcters)
EXTRAE
Ens permet extraure una part de text a partir d'una posició inicial i un nombre determinat de caràcters.
=EXTRAE(cel·la; posició_inicial; nombre_de_caràcters)
BUSCARV
Permet fer una busca dins d'un rang, conegut el valor que volem trobar.
=BUSCARV(valor_buscat; rang; columna_on_es_troba_el valor_buscat; FALSO)
El paràmetre FALSO (també es pot posar 0) s'utilitza quan la busca es fa dins un rang en que els valors buscats no es troben ordenats, ja sigui alfabèticament o numèricament.
El paràmetre VERDADERO (també es pot posar 1 o simplement no posar res) expressa que els valors buscats estan ordenats i en aquest cas la busca resulta aproximada en lloc de exacte. És a dir, si la busca sobrepassa el valor indicat ens retornarà el valor immediat anterior, o el valor exacte si el troba idèntic al valor buscat.
BUSCARV&"*"
El símbol * es coneix com a comodí. Significa "qualsevol text". Ens permet buscar quan només disposem d'una part del text inicial que volem buscar i en el rang de busca el text és més extens.
=BUSCARV(valor_buscat&"*"; rang; num.columne; FALSO)
Per exemple: Si hem de buscar l'import del saldo d'un compte del qual només tenim el número de compte i hem de buscar el saldo dins d'un rang on el número de compte figura juntament amb la descripció del compte podríem fer ús d'aquesta facultat.
Observar l'ús de BUSCARV amb comodí, combinat amb IZQUIERDA per trobar el noms dels grups i subgrups dels comptes que figuren a la columna C i que busquem a la fulla PyG
També podem veure l'ús de BUSCARV amb comodí, combinat amb EXTRAE a la cel·la N3 de la pàgina Ratios per tal de mostrar el nom del compte al que correspon el número de compte seleccionat a la cel·la O2 i on col·loquem també SI.ERROR per cobrir el cas que no trobi la descripció. També hi apareix la condició SI per tal de que es mostri el text "Resultado" quan es deixa la cel·la O2 en blanc.
DESREF
Aquesta funció serà molt utilitzada a l'exercici. Convé entendre bé com funciona.
Ens permet trobar un valor que es troba x files i y columnes desplaçat de la cel·la d'origen. Valors positius de x i y seran desplaçaments avall i a la dreta respectivament. Valors negatius serien desplaçaments amunt i cap a l'esquerra.
=DESREF(cel·la; files; columnes)
Per exemple: =DESREF(A1;0;3) ens tornaria el valor situat a la mateixa fila que A1 (és a dir a la fila 1) i tres columnes a la dreta.
La funció DESREF té dos paràmetres opcionals: ALTO i ANCHO que es col·loquen al final de la fórmula quan és necessari. Ho farem servir per crear rangs variables al crear etiquetes i fer sumes de rangs variables. Ho veurem a la següent fórmula.
SUMA:DESREF (Suma de rang variable)
Un rang variable és un rang que tindrà una dimensió diferent segons el moment i que per tant no podem definir de la manera clàssica com seria per exemple A1:A5
La suma de rang variable s'obté de la combinació de les funcions SUMA i DESREF dins la mateixa fórmula i en aquest ens caldrà fer ús dels paràmetres addicionals de la funció DESREF.
Imaginem (com és el cas a l'exercici) que tenim els saldos mensuals dels comptes encolumnats des de la columna B a la M (12 columnes) i que a la columna A hi tenim el nom del compte. Depenent del mes seleccionat volem obtenir el saldo acumulat fins aquell mes.
Si el mes seleccionat fos Març (el mes 3) hauríem de sumar des de B1 fins a D1 però si el mes fos Juny (mes 6) caldria sumar des de B1 fins a G1. És a dir l'origen de la suma seria sempre B1 però el final del rang varia segons el mes seleccionat.
Suposem que a A1 hi posem el número del mes. Al lloc on volguéssim mostrar el resultat de la suma dels saldos mensuals escriuríem la següent fórmula per trobar la suma acumulada del compte situat a la fila 2.
=SUMA(B2:DESREF(A2;0;A1))
Veiem que el rang final de la SUMA és DESREF(A2;0;A1) on A2 és l'origen, 0 és la fila -no ens desplacem cap fila- i A1 és el nombre de columnes a considerar segons s'indiqui a A1.
D'aquesta manera només modificant el valor de A1 obtindrem l'acumulat fins el mes desitjat.
Observar l'ús de DESREF a les següents imatges on el valor del mes seleccionat, en aquest cas Febrer, mes 2, es troba indicat a la fulla Control, cel·la E2:
IMPORTARDATOSDINAMICOS
Extrau les dades d'un camp d'una taula dinàmica.
Per veure informació detallada de totes les funcions us recomano la web EXCEL TOTAL
Ens dona la posició que ocupa un valor buscat dins d'un rang. Cal indicar el tipus de coincidència, si exacte (0); per excés (1) o per defecte(-1)
=COINCIDIR(valor_buscat; rang; coincidència)
SI
És la funció condicional, on podem definir que fer si es compleix la condició proposada i què fer en cas contrari.
=SI(condició; acció_si_es_compleix; acció_si_no_es_compleix)
SI niat
Es tracte de col·locar la funció SI de forma consecutiva. És a dir, que si la primera condició no es compleix, tindrem un altre SI on seguir establint opcions diferenciades.
=SI(primera_condició; accció_si_es_compleix;SI(segona_condició; acció_si_es_compleix; acció_si_no_es_compleix))
No convé definir mes de dos o tres SI ja que si no la fórmula es torna massa complexa i difícil d'entendre.
CONTAR.SI
Ens permet contar les cel·les ocupades que compleixen una condició. La condició s'ha d'expressar entre cometes.
=CONTAR.SI(rang; "condició")
SI.ERROR
S'utilitza freqüentment per evitar mostrar els errors que troba el sistema en realitzar alguna operació. #¡DIV0!, #N/A; etc.
Normalment, la funció s'escriu al principi de la fórmula i al final s'hi posa el que volem mostrar en lloc de l'error del sistema.
=SI.ERROR((aqui_la_fórmula);"")
En aquest cas, si el resultat de la fórmula donés un error, la cel·la es mostraria en blanc.
VERDADERO/FALSO
El sistema retorna aquests resultats si una proposició es compleix o no.
Si volem comprovar si dos valors son iguals en dues cel·les podriem escriure la fórmula
=cel·la1 =cel·la2
I el sistema ens retornaria VERDADERO o FALSO segons que fossin iguals o no.
IZQUIERDA
Ens retorna la part de text de l'esquerra que indiquem extraure.
=IZQUIERDA(cel·la; nombre_de_caràcters)
EXTRAE
Ens permet extraure una part de text a partir d'una posició inicial i un nombre determinat de caràcters.
=EXTRAE(cel·la; posició_inicial; nombre_de_caràcters)
BUSCARV
Permet fer una busca dins d'un rang, conegut el valor que volem trobar.
=BUSCARV(valor_buscat; rang; columna_on_es_troba_el valor_buscat; FALSO)
El paràmetre FALSO (també es pot posar 0) s'utilitza quan la busca es fa dins un rang en que els valors buscats no es troben ordenats, ja sigui alfabèticament o numèricament.
El paràmetre VERDADERO (també es pot posar 1 o simplement no posar res) expressa que els valors buscats estan ordenats i en aquest cas la busca resulta aproximada en lloc de exacte. És a dir, si la busca sobrepassa el valor indicat ens retornarà el valor immediat anterior, o el valor exacte si el troba idèntic al valor buscat.
BUSCARV&"*"
El símbol * es coneix com a comodí. Significa "qualsevol text". Ens permet buscar quan només disposem d'una part del text inicial que volem buscar i en el rang de busca el text és més extens.
=BUSCARV(valor_buscat&"*"; rang; num.columne; FALSO)
Per exemple: Si hem de buscar l'import del saldo d'un compte del qual només tenim el número de compte i hem de buscar el saldo dins d'un rang on el número de compte figura juntament amb la descripció del compte podríem fer ús d'aquesta facultat.
Observar l'ús de BUSCARV amb comodí, combinat amb IZQUIERDA per trobar el noms dels grups i subgrups dels comptes que figuren a la columna C i que busquem a la fulla PyG
DESREF
Aquesta funció serà molt utilitzada a l'exercici. Convé entendre bé com funciona.
Ens permet trobar un valor que es troba x files i y columnes desplaçat de la cel·la d'origen. Valors positius de x i y seran desplaçaments avall i a la dreta respectivament. Valors negatius serien desplaçaments amunt i cap a l'esquerra.
=DESREF(cel·la; files; columnes)
Per exemple: =DESREF(A1;0;3) ens tornaria el valor situat a la mateixa fila que A1 (és a dir a la fila 1) i tres columnes a la dreta.
La funció DESREF té dos paràmetres opcionals: ALTO i ANCHO que es col·loquen al final de la fórmula quan és necessari. Ho farem servir per crear rangs variables al crear etiquetes i fer sumes de rangs variables. Ho veurem a la següent fórmula.
SUMA:DESREF (Suma de rang variable)
Un rang variable és un rang que tindrà una dimensió diferent segons el moment i que per tant no podem definir de la manera clàssica com seria per exemple A1:A5
La suma de rang variable s'obté de la combinació de les funcions SUMA i DESREF dins la mateixa fórmula i en aquest ens caldrà fer ús dels paràmetres addicionals de la funció DESREF.
Imaginem (com és el cas a l'exercici) que tenim els saldos mensuals dels comptes encolumnats des de la columna B a la M (12 columnes) i que a la columna A hi tenim el nom del compte. Depenent del mes seleccionat volem obtenir el saldo acumulat fins aquell mes.
Si el mes seleccionat fos Març (el mes 3) hauríem de sumar des de B1 fins a D1 però si el mes fos Juny (mes 6) caldria sumar des de B1 fins a G1. És a dir l'origen de la suma seria sempre B1 però el final del rang varia segons el mes seleccionat.
Suposem que a A1 hi posem el número del mes. Al lloc on volguéssim mostrar el resultat de la suma dels saldos mensuals escriuríem la següent fórmula per trobar la suma acumulada del compte situat a la fila 2.
=SUMA(B2:DESREF(A2;0;A1))
Veiem que el rang final de la SUMA és DESREF(A2;0;A1) on A2 és l'origen, 0 és la fila -no ens desplacem cap fila- i A1 és el nombre de columnes a considerar segons s'indiqui a A1.
D'aquesta manera només modificant el valor de A1 obtindrem l'acumulat fins el mes desitjat.
Observar l'ús de DESREF a les següents imatges on el valor del mes seleccionat, en aquest cas Febrer, mes 2, es troba indicat a la fulla Control, cel·la E2:
IMPORTARDATOSDINAMICOS
Extrau les dades d'un camp d'una taula dinàmica.
Per veure informació detallada de totes les funcions us recomano la web EXCEL TOTAL