Excel avanzado y Power BI: cómo conectar ambos paso a paso

Cuando abres una hoja de ventas con miles de filas, las fórmulas de toda la vida se quedan cortas: BUSCARV no encuentra lo que tiene a la izquierda, el filtro se olvida al cambiar de mes y cada mes nuevo obliga a rehacer el gráfico. Excel avanzado y Power BI es el camino para salir de ahí: cruzar tablas con BUSCARX o con ÍNDICE y COINCIDIR, cuidar la entrada de datos con listas desplegables, resumir con tablas dinámicas, limpiar carpetas enteras con Power Query y, cuando el destinatario ya no abre el archivo, publicar un panel en Power BI. Al terminar el tutorial sabrás llevar el mismo libro desde una hoja con errores hasta un panel con un solo clic para Actualizar. Lo que se explica funciona en Excel para Microsoft 365, 2024, 2021 y 2019; las funciones nuevas (BUSCARX, matrices dinámicas, Power Query integrado) están en 2016, 2021, 2024 y 365 según el caso. Excel para la web y para Mac tienen menos conectores, y Power BI Desktop solo se instala en Windows.

Para qué sirve subir de nivel en Excel y Power BI

Imagina una hoja de ventas con tres columnas: fecha, país e importe. Si el directivo te pregunta el total por país, lo haces con SUMAR.SI y listo. Pero el día que añade una columna de cliente y otra de categoría, las fórmulas se duplican, el filtro deja fuera datos válidos y nadie sabe qué celda es la buena. La hoja se queda corta.

El otro caso es al revés: el directivo ya no quiere abrir el archivo. Pide ver el total por país en una pantalla grande, filtrar por mes y que el número se actualice cuando alguien suba una nueva hoja de ventas a una carpeta. Ahí Excel no llega solo; hace falta un panel.

El recorrido de este tutorial lleva de ese libro de ventas en filas a un panel con un solo clic. En cada paso el libro gana una pieza: una búsqueda que no falla, una lista desplegable que cuida los datos, una tabla dinámica que resume, una consulta que limpia archivos y un visual de barras en Power BI.

El teclado y la cinta que vas a usar en todo el tutorial

Cinco atajos aparecen una y otra vez y conviene tenerlos en la cabeza antes de empezar:

• F4 (en Mac, Cmd+T): al editar una fórmula, fija la referencia con dólares ($A$1). Pulsando varias veces va cambiando entre absoluta, mixta y relativa.
• Ctrl+Mayús+L: activa o quita los filtros automáticos de la tabla activa.
• F11: crea un gráfico nuevo en una hoja aparte, con los datos seleccionados.
• Alt+F11: abre el editor de Visual Basic, donde se escriben las macros.
• Alt+F8: muestra la lista de macros del libro para ejecutarlas.

Las pestañas de la cinta que se repiten son:

• Inicio: pegar, dar formato, ordenar y filtrar.
• Insertar: tablas, gráficos, segmentaciones e imágenes.
• Datos: ordenar, filtrar, Validación de datos, Power Query.
• Fórmulas: insertar funciones y rastrear dependencias.
• Programador: grabadora de macros, VBA y controles de formulario (viene oculta; se activa en Archivo > Opciones > Personalizar cinta de opciones).
• Vista: vistas del libro, inmovilizar paneles y la lista de macros.

BUSCARV, BUSCARX e ÍNDICE con COINCIDIR para cruzar tablas

BUSCARV (VLOOKUP) busca un valor en la primera columna de un rango y devuelve otra columna del mismo rango. Su sintaxis es =BUSCARV(valor_buscado; tabla; núm_columna; [ordenado]). El último argumento es FALSO para búsqueda exacta. Se queda corta cuando el dato que buscas no está en la primera columna o cuando la columna de resultado está a la izquierda.

BUSCARX (XLOOKUP) llega con Excel 2021 y Microsoft 365 y resuelve las dos limitaciones anteriores con =BUSCARX(valor_buscado; matriz_buscada; matriz_devuelta; [si_no_encontrado]; [modo_coincidencia]; [modo_búsqueda]). ÍNDICE (INDEX) con COINCIDIR (MATCH) hace lo mismo con dos fórmulas: =INDICE(rango_resultado; COINCIDIR(valor; rango_buscado; 0)).

=BUSCARV(B2;$F$2:$H$10;3;FALSO)
=BUSCARX(B2;$F$2:$F$10;$H$2:$H$10;"No existe")
=INDICE($H$2:$H$10;COINCIDIR(B2;$F$2:$F$10;0))

Para que las tres fórmulas den el mismo resultado al copiarlas, las referencias al rango de la tabla deben ir con dólares.

D2 devuelve 1,20, D3 devuelve 0,80, D4 devuelve 4,50 y D5 devuelve 1,10.

Validación de datos y formato condicional: la hoja que se cuida

La validación de datos obliga a que en una celda solo se escriban valores permitidos. El formato condicional pinta las filas que cumplen una regla, de un vistazo ves lo que se sale de la norma.

Pasos para crear la lista desplegable de país:

1. Escribe los países válidos en F2:F5 (ES, MX, AR, CL).
2. Selecciona C2:C10.
3. Abre Datos > Herramientas de datos > Validación de datos.
4. En Permitir elige Lista y en Origen escribe =$F$2:$F$5.
5. Pulsa Aceptar. Al hacer clic en C2 aparece la flecha de lista desplegable.

Para el formato condicional, selecciona A2:D10, ve a Inicio > Estilos > Formato condicional > Nueva regla > Usar una fórmula. La fórmula =$D2>3 pinta de verde las filas cuyo precio pasa de 3.

Tablas dinámicas de un fichero externo y segmentaciones para filtrar

Una tabla dinámica resume miles de filas en segundos. Aquí vamos a crearla desde un libro de ventas cerrado en una carpeta, que es el caso real cuando el archivo es demasiado grande o se actualiza cada semana.

Pasos para crearla:

1. Pulsa en una celda vacía.
2. Abre Insertar > Tablas > Tabla dinámica > De origen de datos externo > Elegir conexión.
3. Elige el libro de ventas (.xlsx o .xlsm) y la tabla o rango.
4. Acepta. La tabla dinámica aparece en una hoja nueva.
5. Arrastra País a Filas y Precio a Valores (con Suma).

Para añadir la segmentación: haz clic en la tabla dinámica, ve a Insertar > Tablas dinámicas > Segmentación de datos y marca los campos Mes y País. Cada vez que pulses un botón de la segmentación, el panel cambia.

Power Query: limpiar y anexar archivos de una carpeta

Power Query (Obtener y transformar datos) importa, limpia y combina tablas sin tocar las fórmulas. Lo que hace con un clic se queda grabado y se repite con Actualizar.

Pasos para anexar todos los CSV de una carpeta:

1. Coloca los CSV de ventas (uno por mes) en C:\Ventas.
2. En Excel, abre Datos > Obtener y transformar datos > Obtener datos > De archivo > De carpeta.
3. Apunta a C:\Ventas y pulsa Aceptar. Power Query muestra la lista de archivos.
4. Pulsa Combinar y transformar datos. Selecciona la primera columna (la de datos) y Aceptar.
5. Quita las columnas que no sirvan, cambia el tipo de D a Fecha y el de Importe a Número decimal.
6. Pulsa Cerrar y cargar. Aparece una tabla con todos los meses juntos.

Ahora, si alguien deja un nuevo CSV en la carpeta, basta con hacer clic derecho sobre la tabla > Actualizar y el nuevo mes entra solo.

El primer panel en Power BI Desktop con datos del mismo Excel

Power BI Desktop es un programa aparte que se descarga gratis e instala solo en Windows. Toma datos, los relaciona y dibuja visuales. Aquí usaremos el mismo libro de ventas del paso anterior.

Pasos para montar el panel:

1. Abre Power BI Desktop y elige Obtener datos > Libro de Excel.
2. Carga la hoja Ventas_Unificadas y la hoja Clientes (con el país como clave).
3. En la Vista de modelo, arrastra País de Clientes sobre País de Ventas para crear la relación.
4. En la Vista de tabla, crea dos medidas rápidas: Importe total = SUM(Ventas[Importe]) y Clientes distintos = DISTINCTCOUNT(Clientes[País]).
5. En la Vista de informe, añade un visual de Barras agrupadas con País en Eje y Suma de Importe en Valor, y una Tarjeta con Suma de Importe.

Por qué falla: #N/A, la lista que no crece y la consulta que no actualiza

BUSCARV devuelve #N/A aunque el dato está

Si en B2 escribes "Lapiz" con tilde y la tabla tiene "Lápiz", BUSCARV no lo encuentra. La fórmula distingue mayúsculas, tildes y espacios. Solución: limpia los datos con la función ESPACIOS y, si hace falta, reemplaza tildes antes de buscar. Otra causa típica es que el dato está a la izquierda de la columna de búsqueda; ahí BUSCARV no llega y toca usar BUSCARX o ÍNDICE y COINCIDIR.

La lista desplegable no incluye la categoría nueva

En la validación de datos se usó Origen =$F$2:$F$5. Cuando en F6 escribes "PT", el desplegable no lo muestra. La causa es que el rango es fijo. Hay dos arreglos: ampliar el rango a =$F$2:$F$100, o convertir la lista en una tabla con Ctrl+T y escribir el Origen como =Tabla1[País]. La tabla crece sola y la lista también.

La consulta apunta al archivo viejo tras renombrarlo

Power Query guarda la ruta del archivo dentro de la consulta. Si cambias Ventas_2025.xlsx por Ventas_2026.xlsx, Actualizar falla. Arreglo: abre Datos > Consultas y conexiones, haz doble clic en la consulta, cambia la ruta en el paso Origen y aplica.

La segmentación no filtra la tabla dinámica

Si la segmentación está en otra hoja, asegúrate de que el informe tiene conexión a la tabla dinámica: clic derecho en la segmentación > Conexiones del informe y marca la tabla. Si sigue sin filtrar, el origen de datos no es el mismo.

Power BI no encuentra la relación entre tablas

Las dos tablas deben tener una columna con valores únicos en la dimensión (Clientes) y repetidos en el hecho (Ventas). Si País aparece como "ES" en una y "España" en otra, la relación queda inactiva. Hay que unificar los valores antes de relacionar, con Reemplazar valores en Power Query.

Lo que no hace Excel (ni Power BI) y cómo sustituirlo

Hay cosas que esta cadena de herramientas no cubre y conviene saber de antemano:

• Excel para la web ejecuta Power Query, pero con menos conectores y sin la grabadora de M. Para flujos complejos, mejor el escritorio.
• Power BI Desktop solo se instala en Windows. En Mac hay un Power BI para Mac limitado a la visualización de informes publicados en el servicio.
• Las macros VBA no funcionan en Excel para la web ni en Power BI. Si necesitas automatizar en la web, usa Scripts de Office (TypeScript).
• Power Pivot (modelo de datos, DAX) requiere una versión que lo traiga: Excel 2016 ProPlus, 2019, 2021, 2024 y Microsoft 365. En 2013 viene como complemento.
• BUSCARX no existe en Excel 2019 ni en 2016: ahí se usa BUSCARV o ÍNDICE con COINCIDIR.
• Power Query puede manejar tablas mucho más grandes que una hoja (millones de filas) pero no ejecuta cálculos celda a celda como SUMA; para eso están las fórmulas.

Lo que ya sabes hacer y el siguiente paso

• BUSCARX (o ÍNDICE y COINCIDIR) para cruzar tablas sin que el orden de las columnas importe.
• Validación de datos para que la entrada de País no admita valores basura.
• Power Query para limpiar y anexar todos los CSV de una carpeta en una sola consulta que se actualiza con un clic.
• Tabla dinámica con segmentaciones para resumir por país y mes.
• Power BI Desktop para publicar el mismo modelo como un panel con un visual de barras y una tarjeta de total.

Siguiente paso: en Power BI, crea una medida con CALCULATE(SUM(Ventas[Importe]); Ventas[País] = "ES") para obtener el total filtrado por un país concreto y añadirlo al panel. Esa medida es la base de cualquier informe que compare periodos o regiones.

Dudas al subir de Excel a Power BI