• Author / Uploaded
• Jorge Acuña

• Categories
• Microsoft Excel
• Tabla (base de datos)
• HTML
• Archivo de computadora
• Comillas

Citation preview

Seo

Analítica web

Desarrollo Web

- Seo On Page

- Google Analytics:

- jQuery

Follow @ikhuerta

Suscribete por Rss

Martes, 31 de enero de 2012.

Crear dashboards con Excel 54

Like 58 people like this. Sign Up to see what your friends like.

Algo que me preguntan bastante ultimamente -no se por qué será - es cómo 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 detrás de todo esto, una función secreta que usamos en analítica del estilo "crear Dashboard" y que solo hace falta descubrir ese botón para disfrutar de estos cuadros llenos de gráficos y colores. Y es que más o menos, todos creemos que sabemos manejar Excel, pero son muy pocos los que han tenido que masticarlo lo suficiente como para sacar sus verdaderas posibilidades a la que es, sin duda, la mejor herramienta de la Suite Office de Microsoft. En este artículo veremos un pequeño tutorial sobre como empezar a dar tus primeros pasos en la creación de paneles de control serios a la par que atractivos con Excel. Repasaremos la utilidad real de Excel, cómo importar datos, cómo usar las fórmulas de resumen más comunes, como aplicar tablas dinámicas y veremos pequeños trucos de diseño. Al final crearemos 2 Dashboards sencillos pero que deberían bastaros para poder fabricar vuestros propios informes de negocio:

Bueno, empezamos ya con la explicación tutorial. Aunque antes de que sigas, avisarte de que este documento es bastante largo y tiene muchos pasos y detalles como para sacarle partido si solo dispones de unos minutos. Quizás prefieras guardar este link para poder abordar el tutorial cuando realmente dispongas de tiempo. ¡es un aviso! También es posible que quieras saltarte algunos capítulos si ya tienes su contenido dominado...

Tabla de Contenidos 1. Cuando usar Excel 2. Definiendo el Entorno 3. Las fuentes de datos 4. Hoja de BBDD interna 5. Hojas Resumen converted by W eb2PDFConvert.com

6. Hojas Informe 7. Las Bases de Excel 8. Fórmulas más comunes 9. Tablas dinámicas 10. Gráficos Dinámicos y Gráficos a Medida 11. Segmentos en Tablas dinámicas 12. Diseñando una página de Dashboard 13. Dashboards Estáticos

Cuando usar Excel Para este tipo de soluciones, la verdad es que, sabiendo programar yo soy más amigo de soluciones estilo Zoho Reports o desarrollo propio, pero existen ventajas muy claras para trabajar con Excel en lugar de con programación: La más obvia, no necesitas saber programar (sin duda ayuda, pero no es obligatorio) Tu trabajo queda en un único archivo que fabricas en cualquier y se mueve con este, pudiendo ser enviado y trasladado con todo su potencial. Otras personas, podrán trabajar con las fuentes de datos que se manejen ahí y realmente ir mejorando el Dashboard poco a poco con el tiempo. De la parte gráfica se encarga Excel y no pierdes el tiempo con maquetación de gráficos. En conclusión, son archivos muy útiles para manejar en el interior de un negocio, con personas involucradas en el mismo. Como contras tendríamos: Al dar acceso a tanta gente, estos archivos son inestables, si le sumamos el poco respeto que tiene la gente que no lo maneja bien por Excel, tenemos que cualquiera puede romperlos en su intento pormejorarlos Necesitas Excel para poder verlos, incluso algunos lectores de Éxcel no tienen todas sus funcionalidades. No se puede trabajar con grandisimas cantidades de datos, Excel es limitado en numero de filas y tu ordenador te limitará en la cantidad de proceso. Sin duda, y aunque siempre podemos recurrir a progamar dentro de un Excel, la capacidad de proceso de las formulas de Excel no llega ni por asomo a lo que puede hacerse con programación. Sencillamente hay cosas que con Excel "a pelo", no pueden hacerse Si te encuentras con gente desordenada, editar un Excel más o menos trabajado puede ser un autentico infierno (bueno, vale, esto también pasa con la programación) Entendido todo esto, pongamos que si que vamos a realizar un panel de control mediante esta fantástica herramienta. Genial! Pues empecemos...

Nota:Lo que sigue es una forma muy personal de trabajar; si bien cada uno es libre de organizarse de la forma que quiera. Además de lo que es el propio "cómo se hace" voy a ir dando pautas que nos ayuden a ordenar el trabajo final 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 identificar rápidamente donde entrar a cambiar las cosas en un Excel complejo. De otra forma no aprovecharemos nunca un trabajo de hace varios meses...

Creación de Informes y Dashboards en Excel: Definiendo el entorno. Norma: Usemos siempre 1 hoja de Excel para cada cosa. Lo primero que tenemos que entender es cual es la mejor forma de organizar un Excel. Mucha gente cuando se lanza a esta aventura intenta, por los medios que sea, realizar todo su trabajo en la misma hoja de Excel y eso resulta a la larga muy incómodo de mantener. Excel nos brinda la posibilidad de crear varias hojas en un solo archivo (o incluso ligar datos entre varios archivos, pero eso no vamos a verlo aquí) lo que significa que podemos -o más bien debemos- separar nuestros contenidos en varias hojas para facilitar el trabajo: Así que lo primero es diseñar para que vamos a usar las hojas de Excel, yo os propongo que en este ejercicio las usemos para 4 cosas distintas marcando en el nombre cada una de esas hojas cual es su función: 1.- Hojas con tablas de fuentes de datos. Marcadas con el nombre "datos.Nombre de la hoja".

converted by W eb2PDFConvert.com

2.- Hojas con referencia, equivalencias y traducciones de datos. Marcadas con el nombre "bbdd.Nombre de la hoja". En referencia a que actuan como base de datos interna del archivo 3.- Hojas con resumenes o procesos de datos. Marcadas con el nombre "resumen.Nombre del resumen". 4.- Hojas gráficas con informes creados a partir de las otras. Marcadas con el nombre "informe.Nombre del informe". Así mismo, no está de más que nos acostumbremos a usar un orden en el tipo de hojas según la importancia que tengan: por ejemplo, que las primeras hojas sean siempre informes, luego vengan los resúmenes, luego los datos y por ultimo las bases de datos. De esta forma quien mire el éxcel irá profundizando en hojas solo a medida que lo necesite. Otro tema es el nombre de las hojas en sí. No las dejemos como "hoja1", "hoja2" o tendremos que entrar en todas las hojas para saber lo que son. Usemos un nombre descriptivo. Además a ser posible evitemos nuestros espacios, así nuestras formulas serán mucho más legibles al no necesitar comillas al referenciar una hoja. Un truco sencillo es reemplazar espacios por guion bajo (_). Es posible, que para algunos tipos de informes requieras otro tipo de hojas, o que prefieras diferenciar distintos tipos de datos, resumenes e informes. Lo mismo da, simplemente intenta ser ordenado y clasificar lo máximo posible tus nombres de hojas de Excel. Sino, a la larga te volverás loco para entender que es lo que hiciste hace un año.

Creación de Informes y Dashboards en Excel: Las fuentes de datos Bien, empecemos a trabajar. Lo primero que necesitaremos para hacer un informe son los datos de los que surje este informe. Es obvio, pero no por ello menos importante. No podremos hacer ningún informe si nos faltan datos básicos de los que sacar conclusiones. Así pues necesitamos crear una Hoja de DATOS para cada fuente de datos que tenegamos. Para ello, miramos lo que nos ofrece la herramienta y vemos que Excel nos brinda, en su sección de datos, 4 tipos de importaciones distintas:

Importando cualquier fuente de datos en nuestras hojas Una vez hayamos escogido la fuente de datos -sea cual sea- Se nos solicitará a partir de que celda deseamos importarla. Lo suyo, como decíamos sería siempre crear una hoja completa para cada fuente de datos y escoger siempre la casilla A1 (por lo de ser ordenados y encontrar las cosas rápido). Una vez importados no hemos terminado. Deberemos definirle la actualización de los datos, para que Excel los extraiga el solo cada cierto tiempo. Esto es una gran ventaja que no muchos usan, nos permite no solo trabajar con los datos en el momento sino reaprovechar ese mismo Excel en futuras ocasiones: Clicamos con el botón derecho en la casilla A1 de esa hoja de datos -la casilla en la que hemos creado la importación- y en entramos en "Propiedades del Rango de Datos". Ahí declaramos como queremos que sea esta actualización. Si sabemos que los datos siempre tendrán el mismo formato, podemos establecer cada cuantos minutos queremos que se actualicen -cuidado ahí con no empezar a provocar procesos muy costosos a nuestro ordenador o al servidor del que sacamos los datos-. Si no nos fiamos de la fuente de datos y no queremos sorpresas nos interesará desmarcar la actualización en segundo plano e ir dandole cada vez que queramos a actualizar manualmente.

Importando "Desde Access"... Nos permitirá sacar los datos de una BBDD en Access. Poco útil salvo que acostumbremos a trabajar realmente con esta herramienta -que yo personalmente no os aconsejaría nunca-. Si tenemos esa suerte -o mala suerte, según se mire- la importación de datos es sencilla y creándola podremos actualizar los datos directamente desde ese archivo, lo que siempre es una ventaja. Los detalles de la importación son muy simples, creamos una consulta en Access y la importamos tal cual a Excel seleccionándola de un listado.

Importando "Desde Web"... Nos permitirá importar tablas HTML desde una URL que le indiquemos. Sin duda se trata de un puente de conexión interesante cuando tenemos que ir sacando datos de cualquier entorno web e importarlo directamente en Excel. Esta opción resulta util en 2 casos: a. Cuando encontramos publicada y actualizada una tabla en alguna página de internet, podemos importarla directamente y ir mirando sus actualizaciones para actualizar nuestros datos directamente. converted by W eb2PDFConvert.com

b. Cuando buscamos una forma rápida de importar nuestros datos internos (en nuestras propias BBDD) en el Excel, nos bastará con pedir que una URL concreta los muestre en formato de tabla HTML para importarlos y actualizarlos en nuestro Excel. Cuidado con esta forma puesto que si nuestros programadores no tienen cuidado y no ponen algo de seguridad (por Ip, por contraseñas, etc.) en la petición podríamos hacer publicos datos internos de la empresa. Para realizar esta operación se nos abrirá una especie de navegador web simplificado en el que podremos navegar hasta encontrar nuestra tabla. Entonces la seleccionaremos y le daremos a importar. Una vez importado recordemos entrar a configurar la actualización de datos.

Importando desde "Desde Texto"... Esta seguramente la opción que más veces habremos usado. No tiene mucho misterio, elegimos un archivo concreto de nuestro ordenador en un formato de texto plano (normalmente .txt o .csv) y definimos como importarlo. Aquí todo el problema suele estar en que normalmente estos archivos se delimitan por comas (",") o punto y coma (";") y por defecto Excel intentará usar el tablulador como indicación de separación... Pero bueno, con cambiarlo ya está bien. También es una buena costumbre pedir a nuestros programadores que permitan exportar "CSV para Excel" separando por tabulador en lugar de por punto y coma (acaba siendo más cómodo). En este caso debemos saber que también podremos definir la actualización automática de los datos. La mayor parte de la gente piensa que una vez han importado los datos Excel no guarda registro de como hizo esa importación, pero esto no es así. Excel guardará la ruta del archivo que hemos importado y la forma que hemos usado para interpretarlo. Así que podemos pedirle que lo vuelva a importar cada cierto tiempo o sencillamente decirle que olvide de donde ha sacado esos datos. En esta importación automática cabe destacar que si no desmarcamos la casilla "Solicitar nombre de archivo al actualizar" Excel nos volverá a abrir el navegador de archivos cada vez (lo cual es bastante molesto si nos fiamos de esos datos). Esta opción de actualización automática puede tener mucho sentido cuando cargamos archivos en carpetas en red que más gente puede ir actualizando (red de la intranet, Ftps, Dropbox, etc.).

Importando "De Otras Fuentes"... Aquí ya entramos en conexiones directas con Bases de Datos en forma de Query concreta o con sistemas de datos que tengamos. Sin duda es la mejor forma de importar datos, pero también la más compleja y por eso no podemos pararnos a explicarla. Al final cada menú es suficientemente intuitivo como para que cualquiera que sepa más o menos lo que está haciendo pueda llegar a un buen resultado. Algunas cosas muy interesantes que podemos llegar a hacer son: Conectarnos directamente con ODBC a una base de datos y lanzar consultas. Una lastima que no puedas conectarte directamente a un MySQL, pero bueno, siempre puedes añadir la conexión ODBC al MySQL que tengas instalado. Importar consultas concretas a archivos de listados (sacar solo cierta información de otros Excel, XML, BBDDD, etc.) Como siempre, podremos configurar la actualización automática en todos estos casos y siempre es recomendable revisarla tras cualquier importación.

Revisando las "Conexiones Existentes"... Este apartado nos va a permitir visualizar todas las conexiones que tenemos en nuestro documento. Al editarlas podemos ver en que hoja y casilla del excel están situadas y editar su actualización. A veces es la forma más cómoda de revisar que datos hay en un Excel.

Añadiendo datos... "¿a mano?"... Por supuesto y aunque no sea una opción del Excel siembre habrá datos que incluyamos a mano... Aunque sea para poner nombres más humanos a algunos valores que incluyamos de una fuente externa.

Importando datos de fuentes más complejas En ocasiones será preciso conectarse a distintas APIs para extraer los datos. Este es por ejemplo el caso de Google Analytics, del que si te dedicas al mundo web sin duda querras extraer datos concretos para tus informes y que por supuesto, vemos que Excel no maneja como posible fuente de importación de datos. Por desgracia no existe una vía 100% simple e integrada para hacer estas importaciones. Cada API está programada según han deseado sus programadores y es imposible que todo sea fácil y sencillo. Pero por suerte para nosotros, hay muchísima gente que usa Excel, lo que significa que en muchos casos podremos bajar gratuitamente o comprar alguna solución ya implementada por otros. Solo hay que usar Google e investigar un poco... converted by W eb2PDFConvert.com

para guiarnos un poco, veamos los distintos métodos por los que pueden realizarse estas importaciones más complejas:

1º) Creando una página html, que haga de puente en nuestro servidor. Al igual que haciamos para poder importar datos internos mediante "Desde Web" podemos crear un sistema de solicitudes que nos haga de puente entre lo que Excel comprende y la API. Así, podemos desarrollar la integración de la API que deseemos con los datos que necesitemos en nuestro servidor y en formato que a Excel le gusta: las tablas html. Si vamos a tirar por este camino, no olvidemos nunca añadir una capa de seguridad a lo que montemos.

2) Usando las Macros de Excel. Las macros de Excel son una via por la que Exel te permite aplicar tu propia programacion en VBA en el mismo. Sin duda es una herramienta muy util si sabes programar en ese lenguaje y estas familiarizado con su uso en Exel, pues te permite hacer practicamente cualquier cosa que podrías hacer con Visual Basic pero aplicando los resultados a las celdas, columnas y hojas de Excel en lugar de al HTML. Asi pues, una forma de importar datos de una API cualquiera es programando su integración con Macros. Podemos encontrar un ejemplo de este método aplicado a la API de Google Analytics en AutomateAnalytics. Ahi se nos permitirá acceder a este mundillo a cualquier nivel: desde decargar un informe ya totalmetne integrado con macros para partir de ahi y solo cambiar lo que te apetezca hasta descargarte cada uno de los códigos VBA para aplicarlos en tus hojas a tu manera. Otros macros interesantes que podemos descargar nos permitirán acceder a APIs tambien muy interesantes como las de SEOmoz,

3) Instalando plugins (llamados add-ins) También existen desarrollos completos que pueden comprarse/crearse/instalarse en nuestro ordenador y que dotan a Excel de nuevas funcionalidades. Existen Plugins de Excel para infinidad de cosas, que dotan transforman a Excel en herramientas más complejas o añaden nuevas vias de recuperación de datos. Estos plugins resultan mucho más comodos y rápidos 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 garantizar ni nos termina de convenir. Dentro de estos plugins encontramos a Excellent Analytics un plugin que te genera una nueva pestaña en Excel con la que identificarte en Google Analytics y lanzar con su API capturas de datos en tus páginas. Otro de los plugins interesantes, de cara a la recogida de datos es SQL Drill que nos va a permitir conectarnos a distintos tipos de bases de datos (incluidos Oracle y MySql) e importar consultas o tablas enteras (aunque debo comentar que su instalación a veces falla en W7 y excel 2010 y debe terminarse a mano). Sin duda, los plugins son una gran opción tanto para necesidades puntuales como para iniciarse en la analítica web en Excel, ya que no requieren ningún conocimiento específico.

Creación de Informes y Dashboards en Excel: La hoja de Base de datos interna Es bastante normal que los datos, tal y como se importan en la cuenta no nos acaben de valernos. Para solucionar esto mucha gente lo que hace es hacer reemplazos masivos en la hoja de datos que acaba de cargar, pero eso tiene un serio problema: no podremos actualizar nunca esos datos o tendremos que volver a repetir el trabajo de reemplazos. Para solucionar esto lo que podemos hacer es crear una Hoja de Base de Datos donde incluiremos equivalencias concretas entre datos. Un ejemplo típico sería asociar una lista de datos que tenemos solo por ID y al que le creamos en una hoja de bbdd a sus nombres más comunes asociados a estos Ids. Para ello, usaríamos dos columnas de nuestra Hoja de base de datos donde iriamos especificando en cada fila ID y Nombre. De esta forma ya tendríamos esta asociación en nuestro Excel y luego podríamos usarla en nuestras formulas para autotraducir los datos cargados.

Creación de informes y Dashboards en Excel: Las Hojas Resumen Estas hojas deberiamos tratarlas exactamente igual que las de datos solo que sabiendo que sus datos serán en realidad formulas que precalculen datos en base a otras hojas de datos que hemos ido importado. Por eso las llamamos resumen. No aportan datos nuevos, solo resumen los importantes en una sola hoja. Estas tablas no siempre son necesarias, muchas veces podemos evitarlas y hacer los calculos directamente en los informes así que marcate dos únicos motivos para crearlas: 1) No te ves capaz de poder crear el informe sin este apollo intermedio que te procese los datos. Normalmente no significará que no pueda hacerse, pero uno debe ser consciente de sus propias limitaciones y no todo el mundo conoce todas las funciones de Excel ni sabe anidar varias en una sola celda. Además, para Dashboards complejos muchas veces es más rápido crear este resumen que ir teniendo en cuenta varias hojas para nuestras fórmulas. converted by W eb2PDFConvert.com

2) Visualizarlas vaya a tener algún sentido para los usuarios. Esto ya es más comun. Nosotros en nuestro informe podemos mostrar un gráfico, por ejemplo de evolución de ingresos por tipo producto. Pero sabemos que alguien al observarlo puede preguntarse... ¿y este tipo de producto por qué tiene este resultado? Así que le ofrecemos una hoja resumen que le ayude a ver el desglose de cada venta. Si le obligasemos a visualizar cada hoja de datos seguramente tardaría mucho más en dar respuesta a su pregunta, así que debemos ayudarle.

Creación de infomres y Dashboards en Excel: Las hojas Informe Los informes son al final lo que la gente viene a mirar a nuestro Excel por lo que deben ser claros, concretos y lo más automáticos posibles. Para listados de datos enormes ya tenemos las hojas de datos y de resúmenes, aqui lo que buscamos son datos que "nos digan cosas" y para generarlos tendremos que hacer muchas veces uso de complejas formulas y recursos gráficos y de usabilidad que ayuden a ver mejor todos los datos que incluimos y sobretodo a que destaquen aquellos en los que el usuario final debe fijarse. Como 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, vayamos repasando como funciona Excel internamente o todo este ejercicio se resumirá en un corta y pega del que no entenderemos ni la mitad...

Creación de infomres y Dashboards en Excel: Las bases de Excel Antes de entrar en los propios informes o Dashboards debemos conocer con que herramientas vamos a trabajar los datos. Para eso es esencial concer algunas funciones un poco más avanzadas que el "=SUMA()" o "=A1+B1". Así que perdamos unas cuantas líneas en ver las funciones que más van a usarse para crear estos informes, de hecho, es posible que solo con estas y las básicas ya no necesites nada más para crearlos.

Entendiendo la selección de celdas y de rangos de datos Al definir celdas en Excel debemos conocer minimamente el lexico de las mismas. Tenemos un asistente que nos ayuda a crearlas, pero al visualizarlas tenemos que poder entender lo que dicen con solo leer el texto de la casilla correspondiente. Aquí os dejo algunos apuntes. Las filas se indican como conjunto de letra y Numero Seguidos. Por ejemplo A1 es siempre la primera casilla de la hoja actual. Si detallamos rangos concretos lo hacemos indicando las dos celdas que delimitan ese rango. Por ejemplo A1:C3 es el cuadro de 3x3 celdas del principio y A1:A3 las tres primeras celdas de la primera columna. Podemos marcar tambien rangos de Filas o columnas completas indicando solo ese numero o letra en el rango. Por ejemplo A:C son todas las celdas de las 3 primeras columnas de la hoja, 1:3 las 3 primeras filas y A:A todas las celdas de la columna A. También podemos definir datos de hojas distintas empezando la declaración por el nombre de la hoja seguido de "!" y la declaración de celdas. Por ejemplo, La celda A1 de la hoja datos.ingresos (la hoja de datos sobre nuestros ingresos) la marcariamos como datos.ingresos!A:1 y datos.gastos!A:A se refiere a todas las celdas de la columna A de la hoja de gastos. Y con esto tenemos claras casi todas las selecciones. Ni que decir que normalmente será más cómo hacer las selecciones directamente con el ratón, pero si luego no entendemos lo que hay escrito lo vamos a tener mucho más dificil para detectar errores futuros por lo que es conveniente que practiques a realizar algunas selecciones manualmente.

Entendiendo las formulas Crear una fórmula en Excel no tiene mucho misterio. Incluso tenemos un asistente (que lanzamos con el símbolo "fx") que nos facilitará saber que hacemos en cada momento. Pero, como antes, debemos entender lo que queda escrito al final en la celda o perderemos mucha agilidad detectando cambios y tendremos que reformular hojas enteras cuando se produzca un error. El Debug de Excel normalmente no pasa de indicar que hay un error con un valor y es nuestra labor detectar por qué. Así pues una formula siempre tiene este aspecto: =NOMBREFORMULA( Valor1 ; Valor2 ; Valor3 ; ...)

Donde: NOMBREFORMULA es la formula que estamos usando. Y cada valor es los valores que le incluimos que en cada formula, por su definición significan algo distinto. Estos valores pueden ser realmente que incluimos como número o texto entre comillas (") o una seleccion del celdas del tipo de las que hemos comentado antes.

converted by W eb2PDFConvert.com

De esta forma... =SUMA(1000;A1:C1)

Será igual a la suma de 1000+ el valor de la celda A1 + el valor de la celda B1 + el valor de la celda C1. Sencillo, ¿verdad? Aparte de las propias fórmulas el hecho de estar dentro de una formulación nos permite usar símbolos matemáticos para unir distintas partes de nuestra declaración. Así que la formula anterior podríamos haberla expresado también de esta forma: =1000+A1+B1+C1

o como... =1000+SUMA(A1:C1)

Incluso podemos ir uniendo distintas formulas de esta forma... =(SUMA(A1:C1)*0,25)+(SUMA(B2:C2)*0,50)

Esta formula nos daría directamente un cuarto del valor la suma de las celdas A1,B1 y C1 sumado a la mitad del valor de las celdas B2 y C2.

Algunas consideraciones en las selecciones para las formulas Cuando creamos formulas en Excel normalmente se nos permiten definir rangos de datos o matrices completas. Esto tiene cierto peligro con datos importados puesto que podemos caer en el error de marcar con el ratón una zona de los datos y que estos al volver a importarse aumenten de número de filas dejando nuevos datos sin procesarse. Por lo tanto, siempre que sea posible haremos lo siguiente: Para matrices completas seleccionaremos Todas las columnas afectadas directamente. Por ejemplo una selección referente a una fuente de datos con 12 parametros sería: datos.ingresos!A:L Para marcar rangos, también siempre que sea posible lo haremos con columnas completas. Por ejemplo, para seleccionar el precio (4a culumna de la hoja de ingresos) de todos los productos, lanzariamos datos.ingresos!D:D incluyendo la cabecera en la selección en lugar de datos.ingresos:D2:D45 ya que de otra forma si alguna vez tenemos más de 45 ingresos no nos los contabilizaría. Trabajando de esta forma nos evitamos muchos problemas pero también generamos alguno nuevo al tener en cuenta las cabeceras y las filas vacias como parte de los rangos seleccionados. Esto a poco que lo pensemos tampoco es un problema puesto que con rangos numéricos se interpretará cabecera y filas vacias como valor = 0 y en los textuales o de fechas donde normalmetne buscaremos coincidencias es dificil que encontremos justo esa coincidencia si trabajamos con cuidado. El único problema real vendrá al crear tablas dinámicas, que tendremos que esconder siempre el valor vacío de nuestras tablas. Otra buena costumbre a seguir es no usar casi nunca valores concretos en las formulas. Siempre que podamos referenciemos el resultado al contenido de una celda. ¿Por qué? pues porque ante cualquier cambio criterio o evolución de nuestros informes se nos obliga a buscar en la formula para editarla cuando hubiese sido tan fácil como editar una celda. Esta celda podemos indicarla en una hoja de BBDD, como elemento editable de nuestro Dashboard, o como columna especifica en nuestas hojas de datos. Por ejemplo, imaginemos que tenemos una hoja de productos (datos.productos) en la que hay que calcular el IVA de un producto. Podríamos hacer lo siguietne: =datos.productos!A1*(18/100)

Pero cualquier día nos arriegamos a tener ingresos a otro porcentaje de IVA... con lo correcto sería crear en la celda contigua el porcentaje de IVA a aplicar y cambiar el calculo: =datos.productos!A1*(datos.productos!B1/100)

Como norma: ni un solo dato que no sea una referencia a una celda. A la larga saldreis ganando.

El copia/corta y pega y las selecciones fijas Por ultimo, otra cosa que tenemos que entender es que cuando en excel hacemos un corta y y pega (o un copia y pega) se entienden

converted by W eb2PDFConvert.com

por defecto todas las declaraciones como en referencia a celda en la que estamos... Así si yo en la celda C1 indico que su resultado es =A1+B1 al copiarla en la casilla C2 automaticamente la formula de esa casilla será =A2+B2 puesto que lo que se copia es una formula que hace referencia a las casillas a la izquierda de la actual y no las casillas fijas. Esto es ideal cuando estamos haciendo formulaciones consecutivas de nuestros datos y nos ahorra mucho trabajo, pero es un poco engorroso cuando hacemos formulas que usan un valor fijo de una tabla que no debe moverse. Para evitar esto podemos incluir el símbolo dolar ($) delante de cada elemento la selección y esto indicará al sistema que esa seleccion es fija y no relativa a la casilla en la que nos encontremos:

Asi si yo en la celda C1 indico que su resultado es =$A$1+B1 al copiarla en la casilla C2 la formual de esa casilla será =$A$1+B2 ya que $A$1 no se ha movido con la copia.

Creación de Informes y Dashboards en Excel: Las Fórmulas más comunes Que empiece el tutorial! Ve abriendo tu Excel Despues de tanta explicación de las bases de Excel y de buenas costumbres al crearlos, quizás ha llegado el momento de entrar en materia y empezar a trabajar sobre un ejemplo que nos deje las cosas más claras, ¿no? A partir de ahora y a modo de ilustración práctica, a medida que vayamos hablando de nuevos conceptos iremos explicando como poco a poco va tomando forma un Excel de informes sobre ciertos datos Ficticios. Partiremos de un archivo Excel de ejemplo con una única hoja: una hoja de datos de 42 facturas emitidas a clientes entre el 1 de enero y el 11 de febrero de 2010. DESCARGAR EXCEL DE INICIO

Fórmulas BUSCARV() y BUSCARH() Estas dos formulas son iguales, salvo que BUSCARV se aplica en filas y BUSCARH se aplica en columnas. Lo más normal es usar BUSCARV, ya que los datos suelen presentarse por columnas y por lo tanto nos suele interesar examinar filas. Esta formula lo que hace es buscar la primera aparición de un elemento en una columna para darte como resultado el elemnto de otra columna. Su formulación es la siguiente: =BUSCARV( valor que buscamos; matriz deonde se busca; Columna que realmente queremos ; Ordenado)

Donde: El valor que buscamos es la coincidencia que tiene que haber para que la formula busque un dato. la matriz debe ser una selección de varias filas y columnas en la que la primera columna es SIEMPRE en la que buscamos la coincidencia. Columna que realmente queremos representa cuantas columnas debemos movernos en la matriz para sacar el dato que queremos. Ordenado es un valor que le indica a Excel si la fuente que se está mirando está ordenada de forma ascendente o no. Por defecto está indicado como "Verdadero" (1) y podemos pasarlo a "Falso" (0) indicandolo en la fórmula. Este valor, indicado a "Verdadero" (1) es útil solo cuando necesitamos que BUSCARV() siempre devuelva algo, en ese caso aun no encontrándolo coincidencia devolverá la más cercana posible al valor indicado. Si trabajos con valores no ordenados o no deseamos que siempre haya resultado debemos indicarlo siempre a "Falso" (0). La verdad es que esta, a pesar de ser una de las fórmulas que más vamos a usar, es un poco tosca e incomoda... eso de que siempre se elija la columna 1 de la matriz y contar columnas hasta el valor que realmente queremos es bastante engorroso. Pero es lo que se nos da, y vamos a tener que funcionar con estas reglas... Esta formula resulta ideal para cruzar datos entre dos fuentes distintas, montando sobre la marcha tablas de resumen o traducciones de datos con datos de distintas hojas. Empecemos a poner en práctica el uso de BUSCARV() con el Excel de Ejemplo que mencionabamos antes. Para empezar si observamos nuestros datos de ejemplo veremos 2 cosas que no acaban de cuadrar. Por un lado se nos da un importe bruto y se nos indica el tipo de iva a aplicar pero no se nos calcula ni el valor del IVA ni el total Neto que hemos cobrado a los clientes por esa factura. Por otro lado tenemos la fecha, pero sabemos que la facturación en nuestra empresa la cuentan por meses así que deseamos saber el mes de cada factura, y lo queremos saber con su nombre no con un número que nadie entiende... converted by W eb2PDFConvert.com

Seguramente nos pueden hacer falta estos esos datos cuando avancemos en la elaboración de nuestro dashboard así que es mejor que mejoremos nuestra fuente de datos con columnas extra que calculen esos datos. Esas columnas no se sobreescribirán al actualizar datos, pues no forman parte de la importación, además si en la configuración de la importación de datos marcasemos la casilla para respetar las formulas de la hoja, si se cargasen más datos de los actuales Excel añadiría formulas a los nuevos datos cargados... Bien, para hacer este trabajo requeriremos de una hoja extra: nuestra primera hoja de Base de datos. Así que creamos una nueva hoja y la llamamos "bbdd.facturas" ya que es el apoyo de base de datos para "datos.facturas". Ahi generamos 4 columnas: 2 para trabajar el IVA y una para trabajar los meses del año (hay otras formas de hacer esta traducción pero para el ejemplo usaremos la hoja de bbdd. Así pues nuestra nueva hoja contendrá estas equivalencias: Tipos de iva (columnas A y B): - normal: 18 - reducido: 8 Numero de Mes (columnas C y D) - 1: Enero - 2: Febrero - 3: Marzo - 4: Abril - 5: Mayo - 6: Junio - 7: Julio - 8: Agosto - 9: Septiembre - 10: Octubre - 11: Noviembre - 12: Diciembre Ahora solo nos falta unir esta traducción a los datos que tenemos den datos.facturas. Añadimos tres nuevas columnas (preferiblemente de otro color para saber que son datos calculados) a nuestros datos: iva, Total pagado y Mes. Ahí usamos las siguientes formulas. Iva: =D2*(BUSCARV(E2;bbdd.facturas!A:B;2)/100)

Donde multiplicamos el valor del importe, por la traducción del tipo de iva y dividimos entre 100. Total Pagado: =D2+G2

Sí, esta muy facilita... Mes: =BUSCARV(MES(B2);bbdd.facturas!D:E;2)

Sacamos con la función MES() el numero de més de la fecha y aplicamos la traducción con la Base de datos. Ahora solo nos hace falta rellenar el resto de filas con estas fórmulas. Puedes validar el trabajo realizado descargando el Excel tal cual deberías tenerlo ahora mismo: DESCARGAR EXCEL 1 Esto ha sido fácil, compliquemoslo un poco más. Vamos a crear nuestra primera tabla de resumen. Para ello hemos supuesto que teniamos una nueva fuente de datos: Google Analytics en la cual tenemos asociados a cada id de factura un par de datos de analítica web. DESCARGAR EXCEL 2

converted by W eb2PDFConvert.com

Ahora lo que haremos será crear una nueva hoja en el excel: resumen.facturas_analytics que que nos debe aportar los datos asociados de nuestras dos hojas de datos anteriores. Empezaremos por crear las cabeceras de la nueva hoja: id, Fecha, Mes, cliente, medio, keyword, importe, iva, Total, pagada Y a partir de la Fila 2 empezaremos a formular cada celda para que recoja los datos que debe de ambas hojas de datos... A2: =datos.facturas!A1 B2: =datos.facturas!B1 C2: =datos.facturas!I2 D2: =datos.facturas!C2 E2: =BUSCARV($A2;datos.analytics!$A:$C;2;0) F2: =BUSCARV($A2;datos.analytics!$A:$C;3;0) G2: =datos.facturas!D2 H2: =datos.facturas!G2 I2: =datos.facturas!H2 J2: =datos.facturas!F2

La mayor parte de las fórmulas han sido triviales, pero hay que destacar las celdas E2 y F2, donde hemos usado BUSCARV() para poder asociar ambas tablas. En ambas lo que hemos hecho ha sido seleccionar la celda A2 como id a buscar, les hemos indicado el rango de columnas de la A a la C de la hoja datos.analytics y le hemos indicado de que columna sacar el valor. Notese que como los datos de la hoja datos.analytics no estaban ordenados ascendentemente por ID hemos tenido que marcar el cuarto parámetro (ordenado) de BUSCARV() a 0 ("Falso"). De otra forma los resultados habrían sido muy extraños... Una vez tenemos esta fila lista, tan solo tenemos que rellenar el resto de filas hasta tener una hoja ordenadita con todos los datos asociados a la vez a los IDs Estos dos ejemplos han sido bastante sencillos, pero tu mismo puedes ver el potencial de este tipo de hojas resumen una vez mas mezclando datos con distintos criterios. Al final, el potencial es cercano al de muchas sentencias SQL solo que mucho menos directo. DESCARGAR EXCEL 3

SUMAR.SI() y CONTAR.SI() Ahroa vamos a tratar las funciones de .SI(), donde la más interesante va a ser SUMAR.SI(), una función muy útil para hacer nuestros cálculos globales en el Dashboard o en tablas de resumen. Esta función nos permite seleccionar un rango de datos (normalmente una columna de una hoja de datos o de resumen) y que solo sume los datos si en otro rango especificado el dato coincide con nuestors intereses. La formula se aplica de la siguiente forma: =SUMAR.SI( rango al que se aplica el criterio ; criterio para saber si sumar el dato ; rango que se suma )

De esta forma elegimos de nuestras tablas de datos, en relación a una busqueda que nos interesa que elementos sumamos y que elementos no. Los criterios pueden ser booleanos ">=1","