Post on 01-Mar-2018
transcript
7/26/2019 Excel Dinamico
1/28
Crear dashboards con Excel
Algo que me preguntan bastante ultimamente -no se por qu ser :P- es cmo trabajar esos bonitos y
complejos Dashboards en Excel de los que algunas empresas disponen -por qu ser- El problema est en
que mucha gente piensa que hay algo de magia detrs de todo esto! una "uncin secreta que usamos en
anal#tica del estilo $crear Dashboard$ y que solo hace "alta descubrir ese botn para dis"rutar de estoscuadros llenos de gr"icos y colores
% es que ms o menos! todos creemos que sabemos manejar Excel! pero son muy pocos los que han tenido
que masticarlo lo su"iciente como para sacar sus &erdaderas posibilidades a la que es! sin duda! la mejor
herramienta de la 'uite (""ice de )icroso"t En este art#culo &eremos un peque*o tutorial sobre como
empe+ar a dar tus primeros pasos en la creacin de paneles de control serios a la par que atracti&os con
Excel
,epasaremos la utilidad real de Excel! cmo importar datos! cmo usar las "rmulas de resumen ms
comunes! como aplicar tablas dinmicas y &eremos peque*os trucos de dise*o Al "inal crearemos
Dashboards sencillos pero que deber#an bastaros para poder "abricar &uestros propios in"ormes de negocio:
.ueno! empe+amos ya con la explicacin tutorial Aunque antes de que sigas! a&isarte de que este
documento es bastante largo y tiene muchos pasos y detalles como para sacarle partido si solo dispones de
unos minutos /ui+s pre"ieras guardar este lin0 para poder abordar el tutorial cuando realmente dispongas
de tiempo 1Es un a&iso2 3ambin es posible que quieras saltarte algunos cap#tulos si ya tienes su contenido
dominado
http://blog.ikhuerta.com/crea-tus-informes-y-dashboards-de-analitica-con-excelhttp://blog.ikhuerta.com/crea-tus-informes-y-dashboards-de-analitica-con-excel7/26/2019 Excel Dinamico
2/28
Tabla de Contenidos4 5uando usar Excel
De"iniendo el Entorno
6 7as "uentes de datos
8 9oja de ..DD interna
9ojas ,esumen
; 9ojas
7/26/2019 Excel Dinamico
3/28
'i te encuentras con gente desordenada! editar un Excel ms o menos trabajado puede ser un
autentico in"ierno bueno! &ale! esto tambin pasa con la programacin
Entendido todo esto! pongamos que si que &amos a reali+ar un panel de control mediante esta "antstica
herramienta Benial2 Pues empecemos
Nota:7o que sigue es una "orma muy personal de trabajarI si bien cada uno es libre de organi+arse de la
"orma que quiera Adems de lo que es el propio $cmo se hace$ &oy a ir dando pautas que nos ayuden aordenar el trabajo "inal que hacemos Debemos entender que estas pautas concretas son lo de menos! lo
que si que es importante es que cada uno! o cada empresa! escoja un patron por el que trabajar que le
ayude a identi"icar rpidamente donde entrar a cambiar las cosas en un Excel complejo De otra "orma no
apro&echaremos nunca un trabajo de hace &arios meses
Creacin de Informes y Dashboards en Excel:Definiendo el entorno.Gorma: Jsemos siempre 4 hoja de Excel para cada cosa
7o primero que tenemos que entender es cual es la mejor "orma de organi+ar un Excel )ucha gente
cuando se lan+a a esta a&entura intenta! por los medios que sea! reali+ar todo su trabajo en la misma hoja
de Excel y eso resulta a la larga muy incmodo de mantener Excel nos brinda la posibilidad de crear &arias
hojas en un solo archi&o o incluso ligar datos entre &arios archi&os! pero eso no &amos a &erlo aqu# lo que
signi"ica que podemos -o ms bien debemos- separar nuestros contenidos en &arias hojas para "acilitar el
trabajo:
As# que lo primero es dise*ar para que &amos a usar las hojas de Excel! yo os propongo que en este
ejercicio las usemos para 8 cosas distintas marcando en el nombre cada una de esas hojas cual es su
"uncin:
4- 9ojas con tablas de "uentes de datos )arcadas con el nombre $datos.Gombre de la hoja$
- 9ojas con re"erencia! equi&alencias y traducciones de datos )arcadas con el nombre$bbdd.Gombre de la hoja$ En re"erencia a que actuan como base de datos interna del archi&o
6- 9ojas con resumenes o procesos de datos )arcadas con el nombre $resumen.Gombre del
resumen$
8- 9ojas gr"icas con in"ormes creados a partir de las otras )arcadas con el nombre
$informe.Gombre del in"orme$
As# mismo! no est de ms que nos acostumbremos a usar un orden en el tipo de hojas segFn la
importancia que tengan: por ejemplo! que las primeras hojas sean siempre in"ormes! luego &engan los
resFmenes! luego los datos y por ultimo las bases de datos De esta "orma quien mire el xcel ir
pro"undi+ando en hojas solo a medida que lo necesite(tro tema es el nombre de las hojas en s# Go las dejemos como $hoja4$! $hoja$ o tendremos que entrar
en todas las hojas para saber lo que son Jsemos un nombre descripti&o Adems a ser posible e&itemos
nuestros espacios! as# nuestras "ormulas sern mucho ms legibles al no necesitar comillas al re"erenciar
una hoja Jn truco sencillo es reempla+ar espacios por guion bajo K
Es posible! que para algunos tipos de in"ormes requieras otro tipo de hojas! o que pre"ieras di"erenciar
distintos tipos de datos! resumenes e in"ormes 7o mismo da! simplemente intenta ser ordenado y clasi"icar
7/26/2019 Excel Dinamico
4/28
lo mximo posible tus nombres de hojas de Excel 'ino! a la larga te &ol&ers loco para entender que es lo
que hiciste hace un a*o
Creacin de Informes y Dashboards en Excel:Las fuentes de datos.ien! empecemos a trabajar 7o primero que necesitaremos para hacer un in"orme son los datos de los que
surje este in"orme Es ob&io! pero no por ello menos importante Go podremos hacer ningFn in"orme si nos
"altan datos bsicos de los que sacar conclusiones As# pues necesitamos crear una 9oja de DA3(' para
cada "uente de datos que tenegamos
Para ello! miramos lo que nos o"rece la herramienta y &emos que Excel nos brinda! en su seccin de datos!
8 tipos de importaciones distintas:
Importando cualquier fuente de datos en nuestras hoas
Jna &e+ hayamos escogido la "uente de datos -sea cual sea- 'e nos solicitar a partir de que celda
deseamos importarla 7o suyo! como dec#amos ser#a siempre crear una hoja completa para cada "uente de
datos y escoger siempre la casilla A4 por lo de ser ordenados y encontrar las cosas rpido
Jna &e+ importados no hemos terminado Deberemos de"inirle la actuali+acin de los datos! para que Excel
los extraiga el solo cada cierto tiempo Esto es una gran &entaja que no muchos usan! nos permite no solo
trabajar con los datos en el momento sino reapro&echar ese mismo Excel en "uturas ocasiones:
5licamos con el botn derecho en la casilla A4 de esa hoja de datos -la casilla en la que hemos creado la
importacin- y en entramos en $Propiedades del ,ango de Datos$ Ah# declaramos como queremos que sea
esta actuali+acin 'i sabemos que los datos siempre tendrn el mismo "ormato! podemos establecer cada
cuantos minutos queremos que se actualicen -cuidado ah# con no empe+ar a pro&ocar procesos muycostosos a nuestro ordenador o al ser&idor del que sacamos los datos- 'i no nos "iamos de la "uente de
datos y no queremos sorpresas nos interesar desmarcar la actuali+acin en segundo plano e ir dandole
cada &e+ que queramos a actuali+ar manualmente
Importando !Desde "ccess!...
Gos permitir sacar los datos de una ..DD en Access Poco Ftil sal&o que acostumbremos a trabajar
realmente con esta herramienta -que yo personalmente no os aconsejar#a nunca- 'i tenemos esa suerte -o
mala suerte! segFn se mire- la importacin de datos es sencilla y crendola podremos actuali+ar los datos
directamente desde ese archi&o! lo que siempre es una &entaja
7os detalles de la importacin son muy simples! creamos una consulta en Access y la importamos tal cual aExcel seleccionndola de un listado
Importando !Desde #eb!...
Gos permitir importar tablas 93)7 desde una J,7 que le indiquemos 'in duda se trata de un puente de
conexin interesante cuando tenemos que ir sacando datos de cualquier entorno Leb e importarlo
directamente en Excel
Esta opcin resulta util en casos:
7/26/2019 Excel Dinamico
5/28
a 5uando encontramos publicada y actuali+ada una tabla en alguna pgina de internet! podemos
importarla directamente y ir mirando sus actuali+aciones para actuali+ar nuestros datos directamente
b 5uando buscamos una "orma rpida de importar nuestros datos internos en nuestras propias ..DD en
el Excel! nos bastar con pedir que una J,7 concreta los muestre en "ormato de tabla 93)7 para
importarlos y actuali+arlos en nuestro Excel 5uidado con esta "orma puesto que si nuestros programadores
no tienen cuidado y no ponen algo de seguridad por
7/26/2019 Excel Dinamico
6/28
&e'isando las !Conexiones Existentes!...
Este apartado nos &a a permitir &isuali+ar todas las conexiones que tenemos en nuestro documento Al
editarlas podemos &er en que hoja y casilla del excel estn situadas y editar su actuali+acin A &eces es la
"orma ms cmoda de re&isar que datos hay en un Excel
"(adiendo datos... !)a mano*!...
Por supuesto y aunque no sea una opcin del Excel siembre habr datos que incluyamos a mano Aunquesea para poner nombres ms humanos a algunos &alores que incluyamos de una "uente externa
Importando datos de fuentes m+s compleas
En ocasiones ser preciso conectarse a distintas AP
7/26/2019 Excel Dinamico
7/28
Estos plugins resultan mucho ms comodos y rpidos que las macros pero tienen una gran contra: todo el
que use el Excel debe tenerlos instalados para sacarle partido! algo que no siempre podemos garanti+ar ni
nos termina de con&enir
Dentro de estos plugins encontramos a Excellent Analyticsun plugin que te genera una nue&a pesta*a en
Excel con la que identi"icarte en Boogle Analytics y lan+ar con su AP< capturas de datos en tus pginas
(tro de los plugins interesantes! de cara a la recogida de datos es '/7 Drillque nos &a a permitirconectarnos a distintos tipos de bases de datos incluidos (racle y )y'ql e importar consultas o tablas
enteras aunque debo comentar que su instalacin a &eces "alla en = y excel 4 y debe terminarse a
mano
'in duda! los plugins son una gran opcin tanto para necesidades puntuales como para iniciarse en la
anal#tica Leb en Excel! ya que no requieren ningFn conocimiento espec#"ico
Creacin de Informes y Dashboards en Excel: Lahoa de 7ase de datos internaEs bastante normal que los datos! tal y como se importan en la cuenta no nos acaben de &alernos Para
solucionar esto mucha gente lo que hace es hacer reempla+os masi&os en la hoja de datos que acaba de
cargar! pero eso tiene un serio problema: no podremos actuali+ar nunca esos datos o tendremos que &ol&er
a repetir el trabajo de reempla+os
Para solucionar esto lo que podemos hacer es crear una 9oja de .ase de Datos donde incluiremos
equi&alencias concretas entre datos Jn ejemplo t#pico ser#a asociar una lista de datos que tenemos solo
por
7/26/2019 Excel Dinamico
8/28
obligasemos a &isuali+ar cada hoja de datos seguramente tardar#a mucho ms en dar respuesta a su
pregunta! as# que debemos ayudarle
Creacin de infomres y Dashboards en Excel: Lashoas Informe7os in"ormes son al "inal lo que la gente &iene a mirar a nuestro Excel por lo que deben ser claros!
concretos y lo ms automticos posibles Para listados de datos enormes ya tenemos las hojas de datos y
de resFmenes! aqui lo que buscamos son datos que $nos digan cosas$ y para generarlos tendremos que
hacer muchas &eces uso de complejas "ormulas y recursos gr"icos y de usabilidad que ayuden a &er mejor
todos los datos que incluimos y sobretodo a que destaquen aquellos en los que el usuario "inal debe "ijarse
5omo dec#amos pondremos en este tutorial un par de ejemplos sobre como crear este tipo de hojas
pasando por todas las anteriores Pero de momento! &ayamos repasando como "unciona Excel internamente
o todo este ejercicio se resumir en un corta y pega del que no entenderemos ni la mitad
Creacin de infomres y Dashboards en Excel: Las
bases de ExcelAntes de entrar en los propios in"ormes o Dashboards debemos conocer con que herramientas &amos atrabajar los datos Para eso es esencial concer algunas "unciones un poco ms a&an+adas que el $T'J)A$
o $TA4U.4$ As# que perdamos unas cuantas l#neas en &er las "unciones que ms &an a usarse para crear
estos in"ormes! de hecho! es posible que solo con estas y las bsicas ya no necesites nada ms para
crearlos
Entendiendo la seleccin de celdas y de ran/os de datos
Al de"inir celdas en Excel debemos conocer minimamente el lexico de las mismas 3enemos un asistente
que nos ayuda a crearlas! pero al &isuali+arlas tenemos que poder entender lo que dicen con solo leer el
texto de la casilla correspondiente Aqu# os dejo algunos apuntes
7as "ilas se indican como conjunto de letra y Gumero 'eguidos Por ejemplo A4 es siempre la primera
casilla de la hoja actual
'i detallamos rangos concretos lo hacemos indicando las dos celdas que delimitan ese rango Por
ejemplo A4:56 es el cuadro de 6x6 celdas del principio y A4:A6 las tres primeras celdas de la primera
columna
Podemos marcar tambien rangos de ?ilas o columnas completas indicando solo ese numero o letra en
el rango Por ejemplo A:5 son todas las celdas de las 6 primeras columnas de la hoja! 4:6 las 6 primeras
"ilas y A:A todas las celdas de la columna A
3ambin podemos de"inir datos de hojas distintas empe+ando la declaracin por el nombre de la hoja
seguido de $2$ y la declaracin de celdas Por ejemplo! 7a celda A4 de la hoja datosingresos la hoja de
datos sobre nuestros ingresos la marcariamos como datosingresos2A:4 y datosgastos2A:A se re"iere a
todas las celdas de la columna A de la hoja de gastos
% con esto tenemos claras casi todas las selecciones Gi que decir que normalmente ser ms cmo hacer
las selecciones directamente con el ratn! pero si luego no entendemos lo que hay escrito lo &amos a tener
mucho ms di"icil para detectar errores "uturos por lo que es con&eniente que practiques a reali+ar algunas
selecciones manualmente
7/26/2019 Excel Dinamico
9/28
Entendiendo las formulas
5rear una "rmula en Excel no tiene mucho misterio
7/26/2019 Excel Dinamico
10/28
pensemos tampoco es un problema puesto que con rangos numricos se interpretar cabecera y "ilas
&acias como &alor T y en los textuales o de "echas donde normalmetne buscaremos coincidencias es
di"icil que encontremos justo esa coincidencia si trabajamos con cuidado El Fnico problema real &endr al
crear tablas dinmicas! que tendremos que esconder siempre el &alor &ac#o de nuestras tablas
(tra buena costumbre a seguir es no usar casi nunca &alores concretos en las "ormulas 'iempre que
podamos re"erenciemos el resultado al contenido de una celda RPor quS pues porque ante cualquiercambio criterio o e&olucin de nuestros in"ormes se nos obliga a buscar en la "ormula para editarla cuando
hubiese sido tan "cil como editar una celda Esta celda podemos indicarla en una hoja de ..DD! como
elemento editable de nuestro Dashboard! o como columna especi"ica en nuestas hojas de datos
Por ejemplo! imaginemos que tenemos una hoja de productos datosproductos en la que hay que calcular
el
7/26/2019 Excel Dinamico
11/28
DE'5A,BA, EN5E7 DE
7/26/2019 Excel Dinamico
12/28
$datos"acturas$ Ahi generamos 8 columnas: para trabajar el
7/26/2019 Excel Dinamico
13/28
Ahora lo que haremos ser crear una nue&a hoja en el excel: resumen"acturasKanalytics que que nos debe
aportar los datos asociados de nuestras dos hojas de datos anteriores
Empe+aremos por crear las cabeceras de la nue&a hoja:
id! ?echa! )es! cliente! medio! 0eyLord! importe! i&a! 3otal! pagada
% a partir de la ?ila empe+aremos a "ormular cada celda para que recoja los datos que debe de ambas
hojas de datosA2:=!a"o#.a&"%ra#'A1
B2:=!a"o#.a&"%ra#'B1
C2:=!a"o#.a&"%ra#'52
2:=!a"o#.a&"%ra#'C2
E2:=BUSCARV(6A2;!a"o#.aal7"/'6A:6C;2;0)
F2:=BUSCARV(6A2;!a"o#.aal7"/'6A:6C;3;0)
42:=!a"o#.a&"%ra#'2
82:=!a"o#.a&"%ra#'42
52:=!a"o#.a&"%ra#'82
92:=!a"o#.a&"%ra#'F2
7a mayor parte de las "rmulas han sido tri&iales! pero hay que destacar las celdas E y ?! donde hemos
usado .J'5A,M para poder asociar ambas tablas
En ambas lo que hemos hecho ha sido seleccionar la celda A como id a buscar! les hemos indicado el
rango de columnas de la A a la 5 de la hoja datosanalytics y le hemos indicado de que columna sacar el
&alor Gotese que como los datos de la hoja datosanalytics no estaban ordenados ascendentemente por
7/26/2019 Excel Dinamico
14/28
Esta &ariacion sigue la misma lgica que 'J)A,'
7/26/2019 Excel Dinamico
15/28
Las tablas DinamicasAprender a usar las 3ablas de Dinmicas de Excel resulta esencial para hacer cualquier tipo de in"orme o
Dashboard 'u potencial para cru+ar datos es inmenso y nos permite! con solo seleccionar y arrastrar unos
cuantos datos generar matrices con in"ormacin muy interesante
7o primero que tenemos que hacer para generar tablas dinmicas es escoger nuestra "uente de datos 'i
hemos hecho las cosas de "orma ordenada la mayor parte de las &eces hablaremos de seleccionar hojas
enteras de datos o de resFmenes Aqu# lo mejor es acudir al asistente para crear las tablas dinmicas y de
ahi pasar a la &isuali+acin de $lista de campos$! una herramienta que esconde much#simas posibilidades
Mamos paso a paso y de paso! sigamos con nuestro ejemplo
Mamos a crear un nue&o in"orme! que nos de datos de calidad tanto de nuestros clientes como de los
medios por los que traemos tr"ico a nuestra Leb En el caso de los clientes mediremos su calidad
examinando cuanto han pagado de todo lo que han ido comprando! en el caso de los medios querremos &er
cuantos ingresos nos aporta cada medio por separado Adems le a*adiremos un control de ingresos por
d#a En total tres tablas dinmicas
Por lo tanto creamos una nue&a hoja en nuestro Excel: in"ormecalidadKmediosKclientes En esta hojacrearemos primero una tabla dinmica ms sencilla donde &eamos los ingresos de cada medio y que
porcentaje representan del total y otra un poco ms compleja donde &eamos cada cliente cuantas "acturas
ha pagado &s cuantas no y los ingresos reci&idos &s los pendientes
Como crear las tablas din+micas
Para crear la tabla "cilmente seleccionamos la celda a partir de la cual surgir esta Pongamos que es la
celda A4 de una nue&a hoja que como dec#amos &amos a llamar in"ormecalidadKmediosKclientes Mamos a
)enF: $
7/26/2019 Excel Dinamico
16/28
El campo $Malores$: A cada uno de esos segmentos que hemos creado a*adiendo campos a $Etiquetas de
?ila$ le iremos a*adiendo los datos que queramos &er sobre ellos en el cuadro de $Malores$ Podemos a*adir
cuantos datos queramos e incluso repetir el mismo dato &arias &eces 'in embargo lo que no debemos
ol&idar nunca es editarlos una &e+ a*adidos para que se procesen y se muestren justo como necesitamos
Ese es el problema que tiene mucha gente con las tablas dinmicas! simplemente a*aden datos al cuadro
$Malores$ pero no los editan despus de a*adirlos y por de"ecto solo les cuenta el numero de repeticionesde ese &alor! lo cual es muy limitado
=oni>ndolo en pr+ctica
'igamos con nuestro ejemplo ,ecordemos que acabamos de crear una tabla dinmica partiendo de los
datos de resumen"acturasKanalytics
7a &ista de $7ista de 5ampos$ nos o"recer para seleccionar las cabeceras que elegimos al crear la tabla
resumen: id! ?echa! )es! cliente! medio! 0eyLord! importe! i&a! 3otal! pagada
Mamos a por el primer informe! que dijimos que quer#amos que nos mostrase los ingresos que hemos
obtenido mediante los distintos medios y el porcentaje que representan estos en el total As# pues! lo que
hacemos es a*adir el campo $medio$ al cuadro de $Etiquetas de ?ila$ y a*adimos dos &eces el campo
$importe$ al cuadro $Malores$
Ahora nos toca editar los dos campos a*adidos a Malores: el primero lo pasaremos a $'uma$ y ya nos
&aldr su resultado bueno! tambin lo pasaremos a tipo $)oneda$! pero eso es ms por apariencia que por
el dato El segundo campo de
7/26/2019 Excel Dinamico
17/28
Por Fltimo! nos queda nuestra tercera tabla din+micaI la de ingresos por d#as Aqu# lo suyo hubiese sido
poder mostrar tambin estos ingresos por d#a tambin di&ididos por medios y poder crear una tabla ms
compleja! pero por desgracia la muestra de datos que tenemos solo incluye una "actura por d#a y si
hiciesemos eso quedar#an ingresos muy salteados con la mayor parte de los &alores a AFn as# mi deber
es animaros a que con otras "uentes de datos probeis a a*adir ms segmentos a &uestras tablas
5omo siempre! seleccionamos una celda y asignamos como rango todas las columnas con datos de la hojaresumen"acturasKanalytics Mamos a la &ista de 7ista de campos y ah# nada tan "cil como a*adir las
"echas como $Etiquetas de ?ila$ y el importe como Fnico $Malor$ Al a*adirlo recordemos cambiarlo de
resumen tipo $cuenta$ a $suma$ % ya tendemos la nue&a tabla Esta un poco ms larga que las
anteriores
'i hemos hecho bien todo este trabajo nuestro in"orme responder per"ectamente a las preguntas que nos
hac#amos: nos dir que medios son los que ms tr"ico nos aportan y que clientes son los que tienen
porcentajes de impago altos en comparacin con sus porcentajes de pago 'in duda! para estos datos
"icticios el tr"ico orgnico parece muy interesante y el cliente5 deber#a preocuparnos ya que apenas ha
pagado lo mismo que lo que debe
DE'5A,BA, EN5E7
Pero la &erdad es que la in"ormacin as# presentada es un poco tosca
Los /r+ficos din+micos y los /r+ficos a medidaMamos a a*adir un poco de gra"#smo a nuestro in"orme ,ecordemos que los gr"icos de Excel son muy
capaces de resumir &isualmente la in"ormacin Empecemos por dos gr"icos sencillos 'eleccionamos
nuestra tabla dinmica de "uentes y le a*adimos un gr"ico dinmico 9erramientas de tabla dinmica XX
(pciones XX Br"ico Dinmico Jn gr"ico dinmico es simplemente un gr"ico que toma como "uente de
datos directamente todo lo que haya en la tabla! lo cual ahora mismo nos &a bastante bien As# que le
asignamos un gr"ico de quesito y lo ponemos a nuestro gusto
El siguiente gr"ico &a a ser igual de sencillo ,epetimos el proceso pero ahora con la tabla de ingresos por
d#a que creamos anteriormente 5omo son muchos datos y ordenados cronolgicamnete en este caso
escogemos un gr"ico de l#neas que nos muestre las tendencias de los ingresos
.ien! hasta ahora ha sido muy "cil! pero cuando nos encontramos con el gr"ico que representa el estado
de pgo de los clientes &emos que los gr"icos no nos lo ponen "cil para resumir la in"ormacin que
deseamos Esto es porque hemos ordenado la in"ormacin de una "orma distinta a como la requieren los
gr"icos de Exel podr#amos crear otra tabla dinmica paralela pero eso puede terminar haciendo que
creemos cientos de tablas para un solo documento As# que decidimos jugar con los gr"icos creados a
medida
7os gr"icos creados a medida se reali+an insertando un gr"ico pero sin indicarle los &alores reales Por
ejemplo! situamos el cursor en una celda sin datos y &amos a )enu XX
7/26/2019 Excel Dinamico
18/28
agregar la serie y otra con los ingresos no pagados 5omo categor#as seleccionaremos tambin uno a uno
los 8 nombres de cliente de la tabla El gr"ico "inal sern por tanto 8 barras una por cliente que
mostrarn que parte del total ha sido ya pagada y que parte no
Estos gr"icos no nos aportarn ninguna "uncionalidad o dato nue&o! pero sin duda hacen ms e&identes las
conclusiones y nos ayudarn a saber en que datos "ijarnos
"(adiendo e/mentacin a las tablas din+micasPor Fltimo! en nuestro in"orme! &amos a a*adir un control de la segmentacin Estos controles &an a
permitir a quien mire nuestro cuadro poder &er solo una "ranja de datos y no todos a la &e+ En nuestro
caso queremos hacer un control para permitir a los usuarios &er todos los datos introducidos o "ijarse tan
solo en un mes sobre el que tengan dudas
A*adir la segmentacin es muy sencillo: 'eleccionamos una de nuestras tablas dinmicas y clic0amos en
)enF XX
7/26/2019 Excel Dinamico
19/28
t#tulos! leyenda! etc Pero mucha in"ormacin dentro de nuestro dashboard &a a sobrar Por lo general
eliminaremos el "ondo blanco de los gr"icos! su titular y sus bordes! ya que no nos aportarn nada
cuando me+clemos ese tr"ico con el resto de nuestro dise*o 7uego podemos querir ir ms all y borrar
ms elementos o cambiarlos de "ormato o color para cuadrarlos con nuestros intereses
6- 7as herramientas de "ormato condicional de celdas: 'ituadas en )enu XX
7/26/2019 Excel Dinamico
20/28
- Por Fltimo creamos gr"icos! hechos a medida nue&os para cada cliente: un quesito que nos compare
"acturas pagadas con no pagadas y otro que nos compare importes pagados con no pagados 5omo de
estos quesos solo nos interesa el propio gr"ico les eliminamos absolutamente todo lo que no es el gr"ico y
le damos colores tanto a las celdas de datos como a los quesos para identi"icar cada uno de esos datos que
signi"ica
Desp[s de tanto detallito acabamos con un dashboard bastante elegante en el que ir &iendo cada mes loque est sucendiendo
DE'5A,BA, EN5E7 =
.ien2 Pues ya sabemos crear Dashboards de tablas dinmicas pero por desgracia aun no hemos
terminado Gos "alta crear otro dashboard con datos ms concretos
Dashboards Est+ticos.ien! %a sabemos crear dashboards en base a tablas dinmicas pero esto no siempre es su"iciente De
echo! si os "ijis en los dashboards que &an usandose en distintas empresas nos encontramos con que no
mucha gente acaba usando tablas dinmicas para sus dashboards R% esto por quS 5on lo rpido que es
una &e+ lo dominas2 Pues sencillamente porque las tablas dinmicas no son capaces de recibir datos deotros elementos de Excel y eso nos limita mucho las posibilidades
7/26/2019 Excel Dinamico
21/28
As# que creamos dos tablas dinmicas muy simples! una que muestre el dato de medio y conteo de "acturas
y otra que muestre el dato de 0eyLord y conteo de "acturas
DE'5A,BA, EN5E7 >
El desarrollo de los datos
Ahora lo que &amos a hacer es ir creando el desglose del dato ! T.4@ e T.
- Ahora creamos la siguiente columna: la parte pagada de esa "uente en concreto Jsamos
'J)A,':F)
%a tenemos esta primera tabla esttica! solo nos queda darle un poco de color y aplicarle algun e"ectillo de
los que ya &imos en el anterior dashboard
DE'5A,BA, EN5E7 @1 Tabla de in/resos por fuente y ?ey@ord
Ahora a*adiremos el desglose por 0eyLords siguiendo exactamente la misma metodolog#a: sacaremos los
nombres de las 8 mejores 0eyLords de la tabla dinmica y empe+aremos a cru+ar las columnas de medios
con las de 0eyLords:
=SUMAR.S5.CON9UNO(r#%.a&"%ra#?aal7"/'64:64;r#%.a&"%ra#?aal7"/'6C:
6C;/or.lo-al'6C62;r#%.a&"%ra#?aal7"/'6E:6E;/or.lo-al'
6O;r#%.a&"%ra#?aal7"/'6F:6F;/or.lo-al'611)
http://blog.ikhuerta.com/tutoriales/excel/tutorial_excel_8.xlsxhttp://blog.ikhuerta.com/tutoriales/excel/tutorial_excel_9.xlsxhttp://blog.ikhuerta.com/tutoriales/excel/tutorial_excel_8.xlsxhttp://blog.ikhuerta.com/tutoriales/excel/tutorial_excel_9.xlsx7/26/2019 Excel Dinamico
22/28
Esto lo cpiamos en cada celda de la rejilla que acabamos de montar y apro&echamos para reali+ar las
columnas de suma de cada celda
=SUMA(:)
4 Ar+ficos asociados
Ahora nos "alta crear los gr"icos Para ello usaremos los totales creados:
- 5rearemos un gr"ico de queso con los totales de ingresos por "uente- Jn gr"ico de queso con los totales pagados y no pagadados
- % un gr"ico de barras para comprar los ingresos por cada 0eyoLrd
%a solo nos "alta decorar un poco todo esto
- 5reamos un e"ectillo que demuestre que nuestros datos de 0eyLords se desprenden de los totales de las
"uentes $organic$ y $other$
- )arcamos con "ormato condicional todos los resultados de 0eyLord-"uente para que resalten en color ms
oscuro los importes ms altos y llamen ms la atencin
- A*adimos el gr"ico de barras de total de 0eyLords encima del gr"ico para que al estar integrado no
haga "alta a*adir leyendas ni titulos y ahorrar espacio
- A*adimos "ormato condicional de banderas de colores a los totales de 0eyLord para destacar en &erde las
0eyLords que ms importe nos aportan
- ,emarcamos con bordes amarillos los otros dos totales que tenemos con grc"ico y les a*adimos una
"orma de llamada que muestre como cada gr"ico sale de esos totales
% ya est2 y tenemos nuestro segundo dashboard! en el que los datos ms importantes destacan por
encima del resto y en poco espacio resumimos gran cantidad de la in"ormacin importante
DE'5A,BA, EN5E7 4 ?
7/26/2019 Excel Dinamico
23/28
64 enero! 4 a las 44:8> am
7/26/2019 Excel Dinamico
24/28
)uy buen y didctico post ?elicidades por el gran trabajo
Jn saludo,esponder
H. miruludice:
64 enero! 4 a las 6:8 pm
7/26/2019 Excel Dinamico
25/28
,K. Davi% Mel#are&odice:
4 "ebrero! 4 a las =:6= pm
7o dicho
7/26/2019 Excel Dinamico
26/28
; junio! 4 a las 8:4= am
.ueno
7/26/2019 Excel Dinamico
27/28
o ikhuertadice:
44 no&iembre! 4 a las 44:; am
Go entiendo la pregunta
'implemente hay que bajarse los archi&os! disponer de Excel para abrirlos! y seguir los pasos
que se explican,esponder
1,. 'i.ar%odice:
@ diciembre! 4 a las ;:86 pm
9ola! he tratado de bajar los archi&os en excel y no se puede ?a&or si me puedes en&iar a mi correo:
]editado^\hotmailcom
Bracias,esponder
o ikhuertadice:
4 enero! 46 a las =:6 am
.uenas!
9e probado los enlaces hoy mismo y siguen "uncionando per"ectamente Prueba de nue&o por
"a&or,esponder
11. Jaume -lotetdice:
mar+o! 46 a las 4:86 am
7/26/2019 Excel Dinamico
28/28
)e alegro de que os haya gustado Waume! es algo muy sencillo para gente ya acostumbrada a
trabajar en Excel pero justo para "ormacin y gente que no ha trabajado en este sentido con
esta herramienta espero que sea un comien+o
Jn saludo,esponder
14. vi.entedice:
septiembre! 46 a las 4:6@ pm
)uchas! gracias por la dedicacin muy buen trabajo! tengo una consulta estoy siguendo los pasos
para las "ormulas! pero cuando termino de hacerlo me aparece la mayoria asiI _GQA! % A7BJG(' '