Utilitats
Utilitats
Consta dels següents apartats:
Crear noms d'etiquetes
Per donar nom a una etiqueta podem fer-ho de dues maneres diferents. Si el nom l'hem de donar només a un rang de cel·les podem seleccionar-les i posar el nom al quadre de noms.
A la fulla Control de l'aplicació al rang A2:A13 se li ha donat el nom Meses. A partir d'aquest moment cada cop que haguem de fer servir el rang A2:A13 podem escriure Meses en lloc del rang.
Quan per referir-nos a un rang ho hem de fer amb una fórmula i volem crear una etiqueta de nom caldrà anar a la pestanya Fórmulas i seleccionar Administrador de nombres. S'obre una finestra que ens permet crear, editar o eliminar els noms creats.
Podeu consultar a EXCEL TOTAL més informació sobre com crear etiquetes (noms).
Crear etiquetes de rang variable
Els rangs als quals ens hem de referir pot ser que no sempre tinguin el mateix tamany. Podrien variar segons les condicions el nombre de files o columnes. A l'aplicació trobem que el nombre de comptes que es fan servir poden variar durant l'any i que per tant el nombre de files de les fulles AnyAct o DDBB poden variar. Per tant per determinar el rang al qual donar nom hem de fer servir una fórmula de rang variable.
El rang variable el determinem amb les funcions DESREF combinada amb CONTARA. Ho podem veure al quadre de l'administrador de noms on hi veiem totes les etiquetes creades a l'aplicació i els rangs a que fan referència.
Veiem que l'etiqueta de nom RangoBBDD fa referència a la fulla BBDD des de la cel·la A1, agafant totes les files plenes de la columna A i amb 12 columnes d'amplitud.
Podeu consultar a EXCEL TOTAL més informació de com crear etiquetes (noms) amb rang variable.
Validació de dades
Per evitar que en una cel·la es pugui entrar un valor equivocat podem controlar quins s'admeten com a vàlids. A l'aplicació hem fet ús d'aquest control el qual s'activa des de la pestanya Datos, Validación de datos. Aquí podem veure com a la cel·la A3 de la fulla PyG s'ha fet ús d'aquest control per entar el nom del mes a consultar. Fixem-nos que el rang a que fem referència per la validació de les dades el designem amb el nom creat Meses en lloc del rang. (Permitir -> Lista; Origen = Meses)
A la fulla CierrreMes, cel·la B1 també hi ha el mateix control per seleccionar el mes a tancar.
Podeu consultar a EXCEL TOTAL més informació sobre validació de dades.
Consolidació
La consolidació és una utilitat que ens farà un gran servei a l'aplicació. Quan volem ajuntar dues taules que tenen dades comuns però que no tenen idèntica col·locació a files i columnes es fa molt difícil de posar-hi fórmules per obtenir els totals o bé una sola taula amb totes les dades agrupades.
Si tenim les dades comptables d'un mes i volem unir-les a les dels mesos anteriors on els comptes poden no ser els mateixos i volem crear una columna nova per cada mes com és el cas a la nostra aplicació, la solució ens la dona l'opció de Consolidar, a la qual arribem des de la pestanya Datos, Consolidar.
Veiem abans un exemple de com funciona la utilitat de consolidar.
Imaginem que hem d'obtenir el total de vendes de gener i febrer de les dues taules següents en que els articles no son els mateixos. Fer una fórmula de suma seria una feinada brutal si hi haguéssin molts articles. La suma la fem fàcilment amb l'opció Consolidar activant només com a rètols la columna de l'esquerra i situant el cursor a G1, indicant els rangs a consolidar a l'apartat referència i anar agregant tots els que convingui.
Si per consolidar usem també els rètols de la fila superior obtindrem com a resultat una taula que mostrarà dues columnes, una per cada mes i amb les dades de cada article al lloc corresponent.
A l'aplicació es fa ús d'aquesta utilitat a la fulla AñoAct, cel·la H1 per traspassar els imports del mes tancat, on consolidem el RangoConsolidacion y el RangoDescarga. Fixem-nos com també aquí en lloc de definir el rang per les seves coordenades fem ús dels noms de les etiquetes que els conformen.
A la fulla BBDD, cel·la C1 també fem una consolidació per agrupar les dades del mes seleccionat que formaran part de la Taula Dinàmica de la fulla PyG.
Podeu consultar a EXCEL TOTAL més informació sobre Consolidació.
Taules dinàmiques i camps calculats
A la fulla PyG, per construir el compte de Pèrdues i Guanys s'ha fet ús de la Taula Dinàmica (TD) que pren com a dades el RangoBBDD de la fulla BBDD.
Podem veure a la imatge com a la TD hi ha quatre columnes (K:N) que no corresponen a valors de la fulla BBDD. Aquests son camps calculats a la pròpia TD.
Per incloure un camp calculat cal anar des de Herramientas de tabla dinámica a l'opció Cálculos, Campos, Campo calculado.
A l'aplicació se n'han creat quatre, per calcular les diferències mensuals i acumulades i els percentatges de desviació.
Agrupar elements de TD i opcions de TD
Podeu veure exemples a EXCEL TOTAL de com agrupar elements a la TD.
Consta dels següents apartats:
- Crear noms d'etiquetes
- Crear etiquetes de rang variable
- Validació de dades
- Consolidació
- Taules dinàmiques (TD) i camps calculats
- Agrupar elements de TD i opcions de TD
- Formats condicionals
- Gràfics de columnes
Crear noms d'etiquetes
Per donar nom a una etiqueta podem fer-ho de dues maneres diferents. Si el nom l'hem de donar només a un rang de cel·les podem seleccionar-les i posar el nom al quadre de noms.
A la fulla Control de l'aplicació al rang A2:A13 se li ha donat el nom Meses. A partir d'aquest moment cada cop que haguem de fer servir el rang A2:A13 podem escriure Meses en lloc del rang.
Quan per referir-nos a un rang ho hem de fer amb una fórmula i volem crear una etiqueta de nom caldrà anar a la pestanya Fórmulas i seleccionar Administrador de nombres. S'obre una finestra que ens permet crear, editar o eliminar els noms creats.
Crear etiquetes de rang variable
Els rangs als quals ens hem de referir pot ser que no sempre tinguin el mateix tamany. Podrien variar segons les condicions el nombre de files o columnes. A l'aplicació trobem que el nombre de comptes que es fan servir poden variar durant l'any i que per tant el nombre de files de les fulles AnyAct o DDBB poden variar. Per tant per determinar el rang al qual donar nom hem de fer servir una fórmula de rang variable.
El rang variable el determinem amb les funcions DESREF combinada amb CONTARA. Ho podem veure al quadre de l'administrador de noms on hi veiem totes les etiquetes creades a l'aplicació i els rangs a que fan referència.
Veiem que l'etiqueta de nom RangoBBDD fa referència a la fulla BBDD des de la cel·la A1, agafant totes les files plenes de la columna A i amb 12 columnes d'amplitud.
Podeu consultar a EXCEL TOTAL més informació de com crear etiquetes (noms) amb rang variable.
Validació de dades
Per evitar que en una cel·la es pugui entrar un valor equivocat podem controlar quins s'admeten com a vàlids. A l'aplicació hem fet ús d'aquest control el qual s'activa des de la pestanya Datos, Validación de datos. Aquí podem veure com a la cel·la A3 de la fulla PyG s'ha fet ús d'aquest control per entar el nom del mes a consultar. Fixem-nos que el rang a que fem referència per la validació de les dades el designem amb el nom creat Meses en lloc del rang. (Permitir -> Lista; Origen = Meses)
A la fulla CierrreMes, cel·la B1 també hi ha el mateix control per seleccionar el mes a tancar.
Podeu consultar a EXCEL TOTAL més informació sobre validació de dades.
Consolidació
La consolidació és una utilitat que ens farà un gran servei a l'aplicació. Quan volem ajuntar dues taules que tenen dades comuns però que no tenen idèntica col·locació a files i columnes es fa molt difícil de posar-hi fórmules per obtenir els totals o bé una sola taula amb totes les dades agrupades.
Si tenim les dades comptables d'un mes i volem unir-les a les dels mesos anteriors on els comptes poden no ser els mateixos i volem crear una columna nova per cada mes com és el cas a la nostra aplicació, la solució ens la dona l'opció de Consolidar, a la qual arribem des de la pestanya Datos, Consolidar.
Veiem abans un exemple de com funciona la utilitat de consolidar.
Imaginem que hem d'obtenir el total de vendes de gener i febrer de les dues taules següents en que els articles no son els mateixos. Fer una fórmula de suma seria una feinada brutal si hi haguéssin molts articles. La suma la fem fàcilment amb l'opció Consolidar activant només com a rètols la columna de l'esquerra i situant el cursor a G1, indicant els rangs a consolidar a l'apartat referència i anar agregant tots els que convingui.
Si per consolidar usem també els rètols de la fila superior obtindrem com a resultat una taula que mostrarà dues columnes, una per cada mes i amb les dades de cada article al lloc corresponent.
A l'aplicació es fa ús d'aquesta utilitat a la fulla AñoAct, cel·la H1 per traspassar els imports del mes tancat, on consolidem el RangoConsolidacion y el RangoDescarga. Fixem-nos com també aquí en lloc de definir el rang per les seves coordenades fem ús dels noms de les etiquetes que els conformen.
A la fulla BBDD, cel·la C1 també fem una consolidació per agrupar les dades del mes seleccionat que formaran part de la Taula Dinàmica de la fulla PyG.
Podeu consultar a EXCEL TOTAL més informació sobre Consolidació.
Taules dinàmiques i camps calculats
A la fulla PyG, per construir el compte de Pèrdues i Guanys s'ha fet ús de la Taula Dinàmica (TD) que pren com a dades el RangoBBDD de la fulla BBDD.
Podem veure a la imatge com a la TD hi ha quatre columnes (K:N) que no corresponen a valors de la fulla BBDD. Aquests son camps calculats a la pròpia TD.
Per incloure un camp calculat cal anar des de Herramientas de tabla dinámica a l'opció Cálculos, Campos, Campo calculado.
A l'aplicació se n'han creat quatre, per calcular les diferències mensuals i acumulades i els percentatges de desviació.
Agrupar elements de TD i opcions de TD
Els grups generals que conformen el compte de PyG no provenen de la fulla BBDD si no que estan creats agrupant directament a la TD els grups de comptes de nivell 2. Això permet fer agrupacions segons necessitats sense haver de crear els grups al Pla de Comptes.
Per formar els grups cal seleccionar els que volem agrupar i al menú contextual selecciona Agrupar.
Per evitar que les columnes canviïn d'amplada al recalcular la TD i que els possibles valors d'error no es mostrin, a les opcions de la TD s'han activat els camps corresponents, menú que s'accedeix des del menú contextual de la TD Opciones de tabla dinámica.
Podeu veure exemples a EXCEL TOTAL de com agrupar elements a la TD.
Formats condicionals
A la fulla Ratios i a la fulla PyG s'han definit formats condicionals bàsicament per evitar mostrar dades quan la selecció del mes a consultar sigui errònia.
També a la fulla AñoAct s'ha definit un format condicional a la columna Forecast (D) per ressaltar aquelles partides en que la fórmula ha quedat substituïda per un import de forecast ajustat manualment i a la columna E per mostrar només el forecast automàtic quan ha estat substituït pel forecast manual.
A la fulla Ratios i a la fulla PyG s'han definit formats condicionals bàsicament per evitar mostrar dades quan la selecció del mes a consultar sigui errònia.
També a la fulla AñoAct s'ha definit un format condicional a la columna Forecast (D) per ressaltar aquelles partides en que la fórmula ha quedat substituïda per un import de forecast ajustat manualment i a la columna E per mostrar només el forecast automàtic quan ha estat substituït pel forecast manual.
Podeu ampliar informació a EXCEL TOTAL sobre els formats condicionals
Gràfics de columnes
A la fulla Ratios hi ha un gràfic de columnes referit a l'evolució de les dades mensuals del compte seleccionat a la cel·la O2.
Simplement cal seleccionar el rang N4:O15 i des de la pestanya del menú Insertar seleccionar Columna (tipus 2-D) i adaptar el gràfic que es formatà automàticament a les dimensions adequades per col·locar-lo al lloc on el volem mostrar.
Per tal que els valors que es mostren a l'eix vertical surtin en milers, hem de seleccionar l'eix i a Dar formato a eje (botó dret del mouse) activar la casella Millares a Unidades de visualización.
També perquè els noms dels mesos surtin sempre al peu del gràfic cal seleccionar l'eix horitzontal i a Dar formato a eje seleccionar l'opció Bajo a Etiquetas del eje.
Més informació a EXCEL TOTAL de com crear gràfics de columnes.
Gràfics de columnes
A la fulla Ratios hi ha un gràfic de columnes referit a l'evolució de les dades mensuals del compte seleccionat a la cel·la O2.
Simplement cal seleccionar el rang N4:O15 i des de la pestanya del menú Insertar seleccionar Columna (tipus 2-D) i adaptar el gràfic que es formatà automàticament a les dimensions adequades per col·locar-lo al lloc on el volem mostrar.
Per tal que els valors que es mostren a l'eix vertical surtin en milers, hem de seleccionar l'eix i a Dar formato a eje (botó dret del mouse) activar la casella Millares a Unidades de visualización.
També perquè els noms dels mesos surtin sempre al peu del gràfic cal seleccionar l'eix horitzontal i a Dar formato a eje seleccionar l'opció Bajo a Etiquetas del eje.
Més informació a EXCEL TOTAL de com crear gràfics de columnes.