Unidad 4

Presentación y Análisis de Datos en Excel y Power BI

Gestión de la Información y Gobernanza de Datos

Especialización en Gobierno y Gestión Pública Territorial — EAFIT

2026-01-01

Agenda de hoy

  1. Buenas prácticas para el manejo de datos
  2. Excel: fórmulas esenciales
  3. Excel: gráficos para análisis estadístico
  4. Introducción a Power BI
  5. Diseño de dashboards
  6. Más allá de Power BI: herramientas alternativas
  7. IA aplicada al análisis de datos

Parte 1: Buenas Prácticas para el Manejo de Datos

El pecado original: datos sucios

El 80% del tiempo de un analista se gasta en limpiar datos, no en analizarlos.

Causas comunes de datos sucios:

  • Celdas combinadas
  • Colores usados como información
  • Múltiples tablas en una misma hoja
  • Encabezados inconsistentes
  • Datos mixtos en una columna (números + texto)
  • Filas en blanco intercaladas

Las 5 reglas de oro

  1. Una fila = una observación
  2. Una columna = una variable
  3. Una hoja = un conjunto de datos
  4. Nunca combinar celdas (rompe fórmulas y filtros)
  5. Nunca usar colores como dato (usa columnas para categorías)

Datos sucios vs. datos limpios

❌ Datos sucios (no analizables)

Municipio | 2020 | 2021 | 2022
Medellín  | 45%  | —    | 48%
TOTAL     | 133  | 140  | 145

Problemas: años como columnas · total mezclado · valores faltantes sin marcar

✅ Datos limpios (listos para Power BI)

municipio  | anio | ipm
Medellín   | 2020 | 45.0
Medellín   | 2021 | NULL
Medellín   | 2022 | 48.0

Una fila = una observación · una columna = una variable

Nombres de archivos y hojas

Convenciones recomendadas

  • datos_pobreza_rionegro_2024.xlsx (descriptivo, con fecha)
  • NO: Datos (1).xlsx, Copy of Copia de datos.xlsx
  • Nombres de hojas: datos_brutos, datos_limpios, analisis, graficos
  • NO: Hoja1, Hoja2, Hoja3

Control de versiones básico

Si no usas Git (y está bien), al menos:

  1. Guarda el archivo original sin modificar (carpeta originales/)
  2. Trabaja sobre una copia (carpeta trabajo/)
  3. Guarda versiones con fecha: analisis_v2_20260315.xlsx
  4. Documenta qué hiciste en una hoja llamada log o notas

Parte 2: Excel — Fórmulas Esenciales

Las fórmulas que todo gestor público necesita

Fórmula Para qué sirve Ejemplo territorial
=BUSCARV() / XLOOKUP() Cruzar datos de dos tablas Unir datos IPM con inversión por municipio
=SUMAR.SI() Sumar con condición Total ingresos de municipios cat. 6
=CONTAR.SI() Contar con condición ¿Cuántos municipios superan 40% de IPM?
=PROMEDIO.SI() Promedio con condición IPM promedio por departamento
Tabla dinámica Resumir grandes conjuntos SGP por región y año
Formato condicional Visualizar rangos Semáforo de cobertura escolar

BUSCARV / XLOOKUP — La fórmula más útil

Problema: Tienes datos de población en una tabla y datos de educación en otra. Quieres cruzarlos por municipio.

=BUSCARV(A2, Tabla_Poblacion, 3, FALSO)
  • A2: El municipio que buscas
  • Tabla_Poblacion: Dónde buscar
  • 3: La columna que quieres traer
  • FALSO: Buscar coincidencia exacta

XLOOKUP (Excel 365)

=XLOOKUP(A2, Poblacion[Municipio], Poblacion[Habitantes]) — Más intuitivo, busca en cualquier dirección.

SUMAR.SI y CONTAR.SI — Análisis por categoría

¿Cuántos municipios de Antioquia tienen NBI mayor a 30%?

=CONTAR.SI.CONJUNTO(B:B, "Antioquia", C:C, ">30")

¿Cuál es el ingreso total de los municipios de categoría 6?

=SUMAR.SI(D:D, 6, E:E)

Tablas dinámicas — El superpoder de Excel

Una tabla dinámica resume miles de filas en segundos.

Ejemplo: Tienes datos de 1,102 municipios con: departamento, categoría, población, ingresos, NBI.

Con una tabla dinámica puedes ver en segundos:

  • Promedio de NBI por departamento
  • Total de ingresos por categoría municipal
  • Número de municipios por rango de población

Cómo crear

  1. Seleccionar tus datos
  2. Insertar → Tabla dinámica
  3. Arrastrar campos a filas, columnas, valores
  4. Cambiar función de resumen (suma, promedio, conteo)

Formato condicional — Hacer que los datos “hablen”

Resaltar automáticamente valores que cumplan ciertas condiciones.

Ejemplos útiles:

  • Municipios con NBI > 40%: rojo
  • Municipios que mejoraron su tasa de deserción: verde
  • Los 10 municipios con mayor desempleo: barras de datos

Menú: Inicio → Formato condicional → Reglas de resaltado / Barras de datos / Escalas de color

Parte 3: Excel — Gráficos

¿Qué gráfico uso para qué?

Tipo Usa cuando… Ejemplo
Barras Comparas categorías NBI por departamento
Línea Muestras evolución temporal Tasa de desempleo 2015-2024
Dispersión Exploras relación entre 2 variables Ingreso vs. educación por municipio
Histograma Muestras distribución Distribución de población municipal
Mapa Datos geográficos Pobreza por municipio
Dona/torta Composición (máx 5 categorías) Distribución del presupuesto

Gráfico de barras — Paso a paso

  1. Selecciona tus datos (ej: Departamento + Valor)
  2. Insertar → Gráfico de barras
  3. Ordenar de mayor a menor (no alfabéticamente)
  4. Agregar título descriptivo
  5. Etiquetar eje Y con unidad de medida
  6. Eliminar elementos innecesarios (leyenda si hay 1 serie, gridlines excesivos)

Regla: Un gráfico de barras siempre se ordena por valor, a menos que el orden natural importe (categorías municipales 1-6).

Serie temporal — Errores comunes

Mal hecho

  • Eje Y empieza en 95% (exagera variaciones)
  • Sin línea de referencia
  • Sin título ni fuente

Bien hecho

  • Eje Y empieza en 0% (o se advierte el corte)
  • Línea de referencia: promedio nacional
  • Título: “Cobertura educativa neta, Municipio X, 2015-2024”
  • Fuente: “DANE/TerriData”

Parte 4: Introducción a Power BI

¿Qué es Power BI?

Herramienta de Microsoft para crear dashboards interactivos a partir de datos.

¿Por qué Power BI y no solo Excel?

Excel Power BI
Análisis estático Dashboards interactivos
Límite de filas (~1M) Maneja millones de registros
Gráficos individuales Visualizaciones conectadas
Se comparte como archivo Se comparte como link/app
Todos lo tienen Gratuito (Desktop)

El flujo de trabajo en Power BI

flowchart LR
    A["📥 Fuentes\nTerriData · DANE"] -->|Descargar| B["📊 Excel\nLimpiar"]
    B -->|Importar| C["⚡ Power Query\nTransformar"]
    C -->|Modelar| D["🔗 Relaciones\nentre tablas"]
    D -->|Visualizar| E["📱 Dashboard\nDecisión pública"]
    style A fill:#1B6B4E,color:#fff,stroke:#1B6B4E
    style B fill:#0047BB,color:#fff,stroke:#0047BB
    style C fill:#0047BB,color:#fff,stroke:#0047BB
    style D fill:#0047BB,color:#fff,stroke:#0047BB
    style E fill:#F7941D,color:#fff,stroke:#F7941D

Paso 1: Importar datos

  • Desde Excel: Inicio → Obtener datos → Excel
  • Desde CSV: Inicio → Obtener datos → Texto/CSV
  • Desde web: Inicio → Obtener datos → Web

Power Query: El editor de transformación de datos. Aquí puedes:

  • Eliminar columnas innecesarias
  • Filtrar filas
  • Cambiar tipos de datos
  • Combinar tablas (Merge)

Paso 2: Modelo de datos

Conectar tablas entre sí mediante campos comunes.

Ejemplo:

  • Tabla 1: municipios (código, nombre, departamento, categoría)
  • Tabla 2: educacion (código_municipio, año, cobertura, saber11)
  • Tabla 3: finanzas (código_municipio, año, ingresos, gastos)

Relación: municipios.codigo ↔︎ educacion.codigo_municipio ↔︎ finanzas.codigo_municipio

Paso 3: Visualizaciones clave

Visualización Uso en gestión pública
Tarjeta (KPI) Indicador principal: “Tasa de pobreza: 32%”
Gráfico de barras Comparar municipios, sectores
Línea Tendencias temporales
Mapa Distribución geográfica
Segmentador Filtrar por año, departamento, categoría
Tabla Detalle numérico

Paso 4: Diseño del dashboard

Principios de diseño

  1. Una historia por página: ¿Qué pregunta responde este dashboard?
  2. Jerarquía visual: KPIs arriba, detalle abajo
  3. Máximo 6-8 visualizaciones por página
  4. Colores con significado: Rojo = alerta, Verde = bien, Azul = neutro
  5. Filtros siempre visibles: Año, territorio, categoría

Ejemplo: Dashboard de gestión territorial

Fila superior:

  • KPI: Población
  • KPI: NBI
  • KPI: Presupuesto per cápita

Fila media:

  • Mapa de indicadores
  • Barras: Top 5 problemas

Fila inferior:

  • Línea: Tendencia 5 años
  • Tabla: Detalle municipal

Segmentadores:

  • Año
  • Departamento
  • Categoría municipal

Interactividad:

Al hacer clic en un municipio del mapa, todos los demás gráficos se filtran.

Estructura del dashboard del proyecto final

Estructura del dashboard del proyecto final

Página 1 — Resumen ejecutivo - 🔢 Tarjeta KPI: indicador principal - 📊 Barras: comparación con municipios similares - 🗺️ Mapa: distribución territorial

Página 2 — Análisis detallado - 📈 Serie temporal: tendencia 2015–2023 - 🔍 Tabla: indicadores por dimensión - 🎛️ Segmentador: filtrar por año o categoría

Principios de un buen dashboard

  1. Una historia clara: ¿qué quiero que el usuario entienda?
  2. Máximo 5 visualizaciones por página
  3. Siempre incluir filtros (año, municipio)
  4. Fuente visible en cada visualización
  5. Colores consistentes: un color = un municipio

Parte 6: Más Allá de Power BI

El ecosistema de visualización en 2026

Power BI no es la única opción. Conocer alternativas les da flexibilidad según el contexto:

Herramienta Gratis Dashboard Instalación Ideal para
Power BI Desktop sí Sí Windows Dashboards complejos
Looker Studio Sí Sí No (web) Equipos Google
Tableau Public Sí (público) Sí Sí Viz sofisticadas
Flourish Freemium Parcial No (web) Storytelling
Datawrapper Freemium No No (web) Gráficos limpios

Google Looker Studio

Antes “Google Data Studio”. Gratuito, sin límites.

Ventajas:

  • Funciona 100% en el navegador — no requiere instalar nada
  • Colaboración en tiempo real (como Google Docs)
  • +1,000 conectores de datos
  • Publicar dashboards en la web con un clic

Para municipios: Si su equipo ya usa Google Workspace, es la opción natural. Gratis y colaborativo.

URL: lookerstudio.google.com

Flourish y Datawrapper

Flourish

  • 50+ plantillas interactivas
  • Mapas animados, scrollytelling
  • Ideal para la Actividad 2 (infografía)
  • flourish.studio

Datawrapper

  • Gráficos extremadamente limpios
  • Usado por NYT, La Silla Vacía
  • Ideal para informes ejecutivos
  • datawrapper.de

Consejo: Power BI para análisis profundo. Datawrapper para comunicar resultados al alcalde.

Parte 7: IA y Análisis de Datos

IA como acelerador — no como sustituto

La IA generativa cambia cómo interactuamos con los datos:

  • Microsoft Copilot en Excel: “Calcula la tasa de crecimiento anual de la población”
  • Copilot en Power BI: “Muestra la evolución del NBI por año en un gráfico de líneas”
  • ChatGPT / Claude: Suba su CSV y pida “identifica las 3 tendencias más relevantes”

Regla de oro

La IA propone, usted valida. Nunca incluya en un informe algo que no pueda explicar.

Herramientas de IA para gestores públicos

Herramienta Qué hace Acceso
Copilot (Excel) Fórmulas, tablas dinámicas, gráficos por instrucción Excel 365
Copilot (Power BI) Visualizaciones y narrativas con lenguaje natural Power BI Pro
ChatGPT Analiza CSV, genera código, redacta interpretaciones Gratuito (básico)
Claude Analiza datos, genera gráficos, explica resultados Gratuito (básico)
Julius AI Visualización automática desde datos Freemium

Política del curso: Uso de IA permitido y fomentado. Con transparencia, validación y comprensión.

Cómo escribir un resumen ejecutivo

El resumen ejecutivo NO es una introducción

Es un documento autónomo. Un alcalde lo lee en 3 minutos y sabe qué decidir.

Estructura en 3 párrafos:

Párrafo Qué dice Extensión
El problema Qué analizaron y por qué importa 3–4 oraciones
Los hallazgos 2–3 hallazgos clave en lenguaje no técnico 4–5 oraciones
La recomendación Una acción concreta, con responsable y plazo 3–4 oraciones

Recomendación mala vs. buena

❌ Recomendación vaga

“Se sugiere mejorar la educación en el municipio y fortalecer los procesos de gestión para reducir la deserción escolar en el mediano plazo.”

Problemas: - Sin responsable claro - Sin plazo - Sin evidencia que la respalde - No dice qué hacer exactamente

✅ Recomendación accionable

“Se recomienda a la Secretaría de Educación implementar un programa de atención temprana a estudiantes con ausentismo en grados 6° y 7°, priorizando las 3 instituciones rurales con mayor deserción. Inicio: primer trimestre 2026. Seguimiento mensual con SIMAT.”

Tiene: - Responsable ✓ - Acción específica ✓ - Focalización ✓ - Plazo y métrica ✓

Checklist antes de entregar el proyecto

Excel ✅ - [ ] Una fila por observación/año - [ ] Encabezados sin tildes ni espacios - [ ] Sin celdas combinadas - [ ] Valores faltantes como vacío (no como 0) - [ ] Al menos 3 gráficos con título y fuente

Power BI ✅ - [ ] Mínimo 2 páginas en el dashboard - [ ] Al menos 1 segmentador interactivo - [ ] Títulos descriptivos en cada visual - [ ] Paleta de colores coherente

Informe ✅ - [ ] Resumen ejecutivo (máx 1 página) - [ ] Análisis referencia datos con estadísticas - [ ] Recomendación específica y accionable - [ ] Formato APA 7.ª ed. - [ ] Gráficos o capturas del dashboard

Criterio más importante

¿Podría un alcalde leer tu resumen ejecutivo en 3 minutos y saber exactamente qué decisión tomar?

Parte 8: El Proyecto Final

Actividad 4: “Dashboard de gestión de información para mi territorio”

Entregable:

  1. Excel con datos limpios y análisis
  2. Dashboard en Power BI
  3. Informe ejecutivo (3 págs máx)

Trabajo grupal: Equipos de 2-3 personas

¿Qué esperamos?

Un producto que un alcalde o secretario de despacho pueda usar para tomar decisiones.

No es:

  • Un ejercicio académico
  • Un reporte de datos sin análisis
  • Una copia de TerriData

Sí es:

  • Un dashboard con análisis propio
  • Datos cruzados de múltiples fuentes
  • Una recomendación de política fundamentada en datos

Cronograma del proyecto final

Momento Tarea Entregable parcial
Semana 3 Conformar grupos, elegir municipio y tema Registro en plataforma
Semana 4 (antes presencial) Descargar y limpiar datos en Excel Excel con datos
Sesión presencial Construir dashboard en Power BI Dashboard funcional
Post-presencial Escribir informe ejecutivo Informe + archivos
Sesión de cierre Presentar dashboard Presentación 10 min

Resumen de la Unidad 4

Ideas clave

  • Datos limpios son la base de todo análisis
  • Las 5 reglas de oro evitan el 80% de los problemas
  • BUSCARV, SUMAR.SI, tablas dinámicas son las fórmulas esenciales
  • Power BI transforma datos en dashboards interactivos
  • Existen alternativas gratuitas (Looker Studio, Flourish, Datawrapper) según el contexto
  • La IA acelera el análisis, pero usted valida los resultados
  • Un buen dashboard cuenta una historia y responde una pregunta
  • El proyecto final integra todo el curso: PPBE + estadística + fuentes + herramientas

Referencias

  • Lora, E. & Prada, S. Técnicas de Medición Económica. 6a ed. ICESI.
  • Microsoft Learn. Primeros pasos con Power BI Desktop.
  • Knaflic, C. N. (2015). Storytelling with Data. Wiley. [Complementaria]