No han sido pocas las veces que me he encontrado con empresas que si bien han abandonado el papel, todavía no explotan por completo las posibilidades que les entregan las herramientas en su computador.
Precisamente esta semana me encontré con dos gerentes que analizaban los ingresos de su negocio haciendo sumas con =suma() tras aplicar filtros en una base de datos gigante de excel. ¿Te imaginas?
No hablamos de herramientas complejas como ERP o CRM, hablamos de cosas simples como word o excel. Herramientas que la mayoría de las empresas tienen en sus equipos.
De ahí el post de hoy. Quiero mostrarte como en equipo, construimos un tablero de control (dashboard) en excel a partir de la información que tenían en bases de datos.
Hay muchos suscriptores de Ingenio Empresa que son expertos en excel y puede que lo que hoy muestro en este post ya se lo sepan al derecho y al revés.
Pero si nunca has hecho un tablero de control o manejas grandes volúmenes de datos en excel o si crees que puedes analizar la información de bases de datos de forma adecuada, quédate con nosotros.
Hoy hablaremos de:
- Cómo definir la estructura de un tablero de control.
- Cómo elaborar un tablero de control.
- Cómo aplicar formato gráfico para que el tablero se vea bien.
¿Te quedas?
Qué es un tablero de control o Dashboard y para qué sirve
Tablero de control, conocido en inglés como Dashboard no es más que una pantalla donde se expone información de valor para el negocio. Este se construye a través de la manipulación, transformación y análisis de datos, de tal forma que en esa pantalla se exponga de forma muy visual, indicadores o kpi’s críticos de la organización.
El dashboard debe presentar la información de forma tal que las decisiones salten a la vista.
¿Cómo hacemos eso? Es lo que veremos a continuación.
Definiendo la estructura del tablero de control
Antes de elaborar el tablero debes revisar los datos y pensar,
- ¿Qué información se puede obtener?
- ¿Qué información me gustaría obtener?
Siempre recomiendo hacer este ejercicio en grupo, pues cada miembro puede tener unas necesidades concretas y si el dashboard va a ser para varios, pues mejor que a todos les sea útil.
Por ejemplo si estas viendo las ventas obtenidas por las diferentes líneas de galletas Noel en las ciudades de Colombia, el análisis de la estructura del dashboard puede resultar en:
- Se requiere conocer las ventas por ciudad.
- Mejor si se conoce las ventas por ciudad y por línea de galleta.
- Sería apropiado observar correlación entre ventas y ciudad.
- Evolución de las ventas a través del tiempo.
De esta forma ya partes con unos objetivos para tu dashboard.
Ajustando los datos del dashboard
Es probable que esa base de datos en excel haya sido contruida por diferentes personas en el tiempo o sea un reporte generado por un software.
Por lo tanto, en este paso lo que hacemos es ajustar la base de datos.
Te adelanto que este suele ser el paso más díficil. Tenemos que aplicar filtros y mirar lo que nos muestra excel e ir reemplazando datos.
Para esto, suelo usar las siguientes herramientas:
- Función Espacios: Elimina los espacios en en el texto. Por ejemplo si tengo Bogotá (espacios). Esto ocurre porque a veces escribimos una palabra y presionamos más de una vez la barra espaciadora para hacer el espacio entre palabras.
- Función BuscarV: Trae un valor a partir de un comando en una base de datos.
- Buscar y reemplazar: Lo activas presionando Control+B. Lo que haces es indicar la palabra que deseas reemplazar y colocar su reemplazo. Por ejemplo en buscar coloco Bogota y en reemplazar coloco Bogotá. Todas las palabras que digan Bogota serán reemplazadas por Bogotá.
- Función Extrae: Extrae caracteres de una cadena de texto. Si una palabra dice COLOMBIANOS y quiero obtener los primeros 8 caracteres para que me quede en COLOMBIA esta función me lo permite.
Esas son las que más uso. Pero hay muchas, muchas más. Por ejemplo si el software te genera las fechas en formato texto, tendrás que ingeniartelas para pasarlas a formato fecha. La buena noticia es que en internet hay mucha información para cada problema que tengas al ajustar una base de datos en excel.
¡Listo! Seguimos.
Elaborando los gráficos, tablas y tarjetas del tablero de control
El resto ya es un paseo.
Nuestro archivo de excel debe tener una hoja o pestaña donde haya:
- La base de datos ajustada
- Las tablas dinámicas con las que se contruye el tablero de control
- El tablero de control
Tener esta organización te permite realizar los ajustes necesarios cuando lo necesites. Si lo tienes todo en una hoja de excel, además de no verse muy bien, los ajustes que vayas a hacer podrían tener errores.
En este paso lo que hacemos es insertar la tabla dinámica, crear los gráficos dinámicas a partir de las tablas ya creadas y construir tarjetas.
Aplicando segmentaciones y conexiones de informe
Las segmentaciones son cuadritos que al darle click harán que nuestros gráficos y tablas cambien. Puede que apliques una segmentación de tiempo (es decir que relacione los años o meses en un cuadro) pero quieras que esa segmentación no te afecte todos los cuadros y gráficos que tienes, ahí es cuando entran las conexiones de informe, con esto lo que logras es definir los cuadros, tablas y gráficos que son accionados por las segmentaciones.
Revisar y ajustar
Por último, debemos probar nuestro dashboard. Si lo elaboramos para otra persona, que sea este quien lo pruebe. Los ajustes serán realizados según el resultado de las pruebas.
En este paso aplicamos formatos gráficos y revisamos que todo se encuentre conforme lo planificado.
Modelo en excel
Fuente de imagen: La imagen de encabezado del post fue descargada en: Freepik
Muy agradecido por el aporte, Graciasss.
Muy útil.
Hola! Muchas gracias por los videos están súper claro. La tabla de dato se puede seguir alimentando de información a diario para que me siga actualizando el tablero de control?
Hola Kate.
Si, siempre que amplíes el rango y actualices (click derecho y actualizar en gráficos) el Dashboard, reflejara los datos. También puedes automatizar esto usando rangos dinámicos. Hay bastantes tutoriales de cómo hace esto en Youtube.
Un saludo.
ojala pudieras hacer un video de actualizacion de tablas dinamicas para ir anexando la data de mes con mes
felicidades muy buenos tus videos
Hola Bernardo. Lo tendré en cuenta.
Muchas gracias por mencionarlo. Saludos.
Excelente post, práctico y muy claro.
Gracias por la trasmisión de conocimiento
Buenos días. Me alegra mucho hallar en red un documento como este. Te felicito por tu amabilidad al responder los comentarios y por la bondad al compartir tus conocimientos.
En Colombia debemos aportar a esta cultura, pero requiere tener humildad.
Muchos éxitos te deseo en tus gestiones
Atentamente:
Alonso Rodríguez Valencia
Muchas gracias Alonso. Completamente de acuerdo. Un saludo.
Estoy muy agradecido!!!
Felicitaciones gran manejo y generosidad al compartir tus conocimientos!!!
Me alegra mucho Ricardo. Un saludo.
aprendi mucho
Hola que tal!!!! no es mucho sobre el tema pero si sobre excel.
En el drive de microsoft (excel online) tengo una base pero conforme la van llenando hay alguna forma en que yo las pueda ir bloqueando? esto para evitar su edición a futuro ya que me esta ocasionando problemas el que me esten moviendo la información.
espero pueda apoyarme!! saludos 😀
Hola Ximena. La verdad en Excel online no sabría si es posible.
¿Y qué tan complejos son los datos que diligencian? Porque podrías utilizar un formulario de SharePoint. Puedes configurar el formulario para que cada diligenciamiento se refleje sobre un libro de Excel, al que solo puedas acceder tu o las personas que autorices.
Buen día Diego.
Excelente post, como comentaron anteriormente, claro y conciso.
Quería preguntarte si puedo aplicarlo en Excel 2007, ya que desconozco si posee la funcionalidad de segmentación.
Cordial saludo.
Hola Cesar.
Me alegra que te haya gustado el post. Y no, la versión de 2007 no posee segmentación de datos. Esta funcionalidad fue introducida en la versión 2010.
Un saludo.
Buenas Noches esto funciona para Office 2013 en adelante. Como hago si a la persona que le voy a enviar el Dashboard tiene una version anterior.?
Y puedo subirlos a drive en la nube o trabajarlo con las hojas de calculo de google drive?
Hola Javier.
La segmentación de datos y las tablas dinámicas que son las funcionalides principales que se usan para obtener y presentar la información, ya estaban implementadas en Office 2013. Quizá varíe un poco la parte visual pero no creo que sea un problema.
Si el archivo final lo subes a hojas de cálculo de Google no va a funcionar, principalmente por la segmentación de datos y tablas dinámicas. Si lo subes para Excel en la nube de Microsoft, debería funcionar. Aunque su contrucción es más fácil en la versión para escritorio. Una limitación importante de esta opción es que no puede superar los 50mb. De lo contrario tendrás que usar la opción de escritorio.
Aquí puedes ver una tabla comparativa de las diferencias entre las funcionalidades de la versión en la nube y escritorio de Office.
Excelente material, muchas gracias por compartir!
Diego, buenas tardes
De antemano muchas gracias por la retroalimentacion que nos brindas de estos temas.
Mi pregunta es cual es el archivo con los datos del ejercicio, no lo encontre, solo dice que al final del post, pero no lo veo.
Muchas gracias
Ya lo encontre Gracias y Saludos
Excelente tutorial, pregunto. en la segmentación de daos no puedo hacer selecciono múltiple, he intentado modificar la tabla de datos, pero sigue igual.
Que estaré haciendo mal?
Es raro Bayron. Si seguiste el tutorial al pie de la letra, no debería haber problema. ¿Tienes la misma versión de Excel?
Gracias que buena herramienta.
Muy buen tutorial, como ya han comentado, corto claro y conciso, muchas gracias!!
Buen Tutorial, pero a veces algo repetitivo sobre todo en lo que esperamos obtener.
Felicidades Diego y gracias por compartir
Buenísimo tutorial (Claro – Conciso – Corto)
Gracias por compartir tus conocimientos
Saludos desde Rosario, Argentina
Yamila
Hola Yamila. Así busco que sea.
Gracias por comentar. Saludos!
Muy bueno! Saludos desde Arg.
Hola, te consulto.
si cambia algún dato de la matriz inicial de datos cambian las tablas dinámicas, y por lo tanto los gráficos asociados a cada tabla?
He intentado hacer cambios en los datos y ni las tablas ni los gráficos cambian. No se si es un problema del excel de mi Pc, o es que es isa y no cambian en las tablas dinámicas.
Hola Cintia.
Si los datos que modificas están dentro del rango de la tabla dinámica, debería comunicarse.
Si agregaste datos por debajo del rango que toma la tabla dinámica, ahí si no se te va a actualizar. Para revisar el rango, dale click a la tabla dinámica, se te habilitará en la parte superior, «herramientas de tabla dinámica», allí dale a la pestaña analizar y busca la opción «cambiar origen de datos». Si no incluye los nuevos datos agregados, desde allí puedes cambiar el rango y la tabla debería actualizarse.
Para evitar esta actividad de actualizar el rango de datos, puedes usar desref. En Exceltotal lo explican muy bien, aquí. Si te cuesta entenderlo, en youtube también hay ejemplos del uso de desref para tener «rangos dinámicos en tabla dinámica»
Otra cosa que puedes hacer es usar power pivot y power query. A través de consultas y tablas, podrás actualizar automáticamente las tablas y tener toda la estructura del dashboard de una manera mucho más limpia, aunque esto tieme una curva de aprendizaje algo más empinada, pero te servirá mucho. En youtube encuentras tutoriales de power query y power pivot, y pronto estaremos sacando otro post de dashboard con estas herramientas de excel.
Buenas tardes.
Excelente modelo y su explicación, Muchas gracias por aportarnos tantas herramientas y conocimientos, espero en un futuro ser muy útil para otras personas que requieran de mi experiencia, como en estos momentos lo son para mi.
Gracias por tu comentario Yehison.
Pienso mucho que con internet el que no aprende es porque no quiere. Lo que puedes aprender aquí y en otros sitios lo podrás aplicar y enseñar a otros en otros entornos, solo si tu quieres y lo haces con disciplina. De ahí que internet suponga una democratización del conocimiento.
Dicho de otra forma Yehison, el futuro es hoy.
Un saludo.
Sí, pena que usen Excel (supone gastar mucho dinero en licencias de sistema operativo) en vez de LibreOffice. Pero gracias de todas formas.
Hola Guille.
Algunas funcionalidades que utilicé para crear este tablero de control no se encuentran en LibreOffice. Usualmente utilizo Ubuntu y por ende LibreOffice para las actividades que hago, y pruebo las hojas de cálculo que elaboro en este software, la mayoría funcionan. No obstante las dejo en formato .xlsx porque Excel es más utilizado y para evitar problemas por compatibilidad.
Pero este tablero de control en especial requería usar funciones como segmentación de datos de las que LibreOffice no dispone.
Saludos.
Muy buena información , esta muy interesante
Gracias por compartir
Saludos cordiales