Curso de Excel avanzado: BUSCARX, Power Query y tablas dinámicas

Este es un curso de Excel avanzado pensado para quien ya maneja lo básico y quiere subir de nivel. Al terminar sabrás cruzar datos entre hojas con BUSCARX, resumir miles de filas con una tabla dinámica, importar y limpiar información con Power Query, y automatizar tareas repetitivas con macros en VBA. Funciona en Microsoft 365, Excel 2024, 2021, 2019 y 2016; las funciones y herramientas más nuevas se nombran indicando desde qué versión aparecen.

Qué cubre un Excel avanzado y para qué te sirve en el trabajo

Un Excel avanzado no es Excel con más botones: es saber resolver problemas de datos reales sin copiar y pegar a mano. Imagina un informe con la hoja Ventas: 40 filas con Fecha, Vendedor, Región, Producto e Importe. El trabajo de cada mes es el mismo feo: resumir el total por region y por producto, cruzar el nombre del vendedor con su zona, importar el CSV del sistema, quitar columnas que sobran y dejar el informe listo. Hacer eso a mano lleva horas; con las herramientas avanzadas, minutos. En este curso aprenderás el kit mínimo: funciones de búsqueda y suma con condiciones, tablas dinámicas, Power Query y macros en VBA. Cada pieza resuelve un de los tres problemas típicos: cruzar datos sueltos, resumir muchos datos y automatizar lo repetitivo.

Las fórmulas que ya deberías controlar antes de pasar a macros

Antes de macros necesitas funciones que combinen datos: BUSCARV (VLOOKUP) y BUSCARX (XLOOKUP) para cruzar tablas; CONTAR.SI.CONJUNTO (COUNTIFS) y SUMAR.SI.CONJUNTO (SUMIFS) para contar y sumar con filtros; SI (IF) para tomar decisiones dentro de la fórmula; SI.ERROR (IFERROR) para tapar el feo #N/A; e INDICE (INDEX) con COINCIDIR (MATCH) como alternativa clásica a BUSCARV. Sus argumentos se separan con punto y coma. En Excel configurado con punto decimal, el separador es la coma.

Buscar y cruzar datos con BUSCARV y BUSCARX

BUSCARV busca un valor en la primera columna de un rango y devuelve el dato de otra columna del mismo ejemplo. La sintaxis es =BUSCARV(valor_buscado;tabla;indicador_columnas;FALSO). BUSCARX hace lo mismo pero sin las limitaciones de BUSCARV; está en Excel 2021, 2024 y Microsoft 365. Su sintaxis es =BUSCARX(valor_buscado;matriz_buscar;matriz_devolver;si_no_encontrado).

=BUSCARV(A2;Productos!A:B;2;FALSO)

Sumar y contar con condiciones: SUMAR.SI.CONJUNTO y CONTAR.SI.CONJUNTO

SUMAR.SI.CONJUNTO (SUMIFS) suma un rango aplicando una o varias condiciones. CONTAR.SI.CONJUNTO (COUNTIFS) cuenta cuántas filas cumplen las condiciones.

=SUMAR.SI.CONJUNTO(C2:C20;A2:A20;"Norte";B2:B20;">=1000")

Anidar SI y cubrir errores con SI.ERROR

SI (IF) mete una decisión dentro de la fórmula y SI.ERROR (IFERROR) sustituye cualquier fallo por un texto o un valor. Sirve para que no aparezcan #N/A en el informe.

=SI(A2>=1000;"Alto";SI(A2>=500;"Medio";"Bajo"))

=SI.ERROR(BUSCARV(A2;Productos!A:B;2;FALSO);"No existe")

Fijar referencias con F4: la diferencia entre $A$1, A$1 y $A1

Cuando copias una fórmula hacia abajo o hacia la derecha, las celdas se mueven. A veces quieres que se muevan (B2 pasa a B3) y a veces no (el IVA está siempre en F1). El símbolo $ fija la fila o la columna. Mientras editas la fórmula, con el cursor sobre la referencia, pulsa F4 para recorrer los cuatro tipos: A1 → $A$1 → A$1 → $A1. En Mac, Cmd+T; en portátiles puede hacer falta Fn+F4.

Cuando fijar solo la fila o solo la columna

En una tabla de meses en fila 1 y productos en columna A, la fórmula del coeficiente del mes se copia hacia abajo y a la izquierda debe quedar fija. La solución: A$1.

=B2*B$1

Que pasa si no pulsas F4 al copiar la fórmula

Sin $, la celda B1 (coeficiente de enero) se copia a C1, D1 y E1: cada celda usa su propio coeficiente y los totales se disparan. La solución: volver a editar la fórmula y pulsar F4 sobre la referencia del coeficiente.

Tablas dinámicas: resumir miles de filas en un clic

Una tabla dinámica resume una tabla de datos agrupando filas y mostrando sumas, cuentas o promedios. La ruta es Insertar > Tablas > Tabla dinámica. En el cuadro eliges el origen (tabla o rango) y dónde colocarla (hoja nueva o existente). Al crearla aparecen cuatro áreas en la parte derecha (Filtros, Columnas, Filas y Valores) y la pestaña Analizar tabla dinámica con sus opciones. Para saber más, consulta la ayuda oficial de tablas dinámicas en support.microsoft.com/es-es/excel.

Crear la primera tabla dinámica sobre una base de ventas

1. Selecciona Venas en la hoja Ventas.
2. Ve a Insertar > Tablas > Tabla dinámica.
3. Elige Nueva hoja de cálculo y pulsa Aceptar.
4. Arrastra Region a Filas.
5. Arrastra Producto a Columnas.
6. Arrastra Importe a Valores (queda como Suma de Importe).

Cambiar el cálculo: de Suma a Cuenta y a Promedio

Haz clic en la celda Suma de Importe dentro del área Valores y elige Configuración de campo de valor. Ahí puedes cambiar entre Suma, Cuenta y Promedio.

Filtrar con segmentaciones y escalas de tiempo

Una segmentación son botones que filtran la tabla dinámica como si fueran desplegantes, y una escala de tiempo es lo mismo pero para fechas. Ve a Analizar tabla dinámica > Filtrar > Insertar segmentación de datos, selecciona Vendedor. Para fechas, repite y elige Fecha.

Por qué la tabla dinámica no muestra datos nuevos

Si la base es un rango normal y le añades filas, la tabla dinámica no las coge. La solución: convierte la base en Tabla (Insertar > Tablas > Tabla) y usa su nombre como origen de la tabla dinámica. Así, al crecer la Tabla, el origen dinámico se incluye y la tabla dinámica se actualiza con un clic en Analizar tabla dinámica > Datos > Actualizar todo.

Power Query: traer, limpiar y combinar datos de verdad

Power Query es la herramienta de Excel para importar datos de fuera, limpiarlos y dejarlos listos sin tocar la hoja. Está integrado desde Excel 2016 (en 2010 y 2013 como complemento). Sirve para CSV, libros de Excel, bases de datos, páginas web y carpetas enteras. Al importar, se abre el editor de Power Query, que muestra los pasos aplicados (uno por línea) y permite deshacerlos.

Importar un CSV y quitar columnas inútiles

1. Ve a Datos > Obtener y transformar datos > De un archivo > De texto/CSV.
2. Elige Ventas.csv y pulsa Importar.
3. En el editor, selecciona la columna Observaciones, clic derecho y Quitar columna.
4. Haz clic en el tipo de la columna Importe (suele decir ABC) y elige Número decimal.
5. Pulsa Inicio > Cerrar y cargar > Cerrar y cargar en… > Tabla > Hoja existente.

Combinar dos tablas en una sola consulta

1. Importa Clientes (A1:C100) y Pedidos (A1:D500) como dos consultas.
2. En Power Query, ve a Inicio > Combinar > Combinar consultas.
3. Elige Clientes como primera tabla y selecciona la columna Clientes. La segunda tabla es Pedidos y la columna Pedidos. Tipo de combinación: Left outer.
4. Pulda Aceptar, expande la columna resultante con el icono de flecha y cierra y carga en una hoja nueva como Solo conexión.

Automatizar tareas con macros y VBA

Una macro es una grabación de clics y teclas que Excel puede repetir. VBA es el lenguaje en que se guardan esas grabaciones. La pestaña Programador viene oculta: se activa en Archivo > Opciones > Personalizar cinta de opciones. El editor se abre con Alt+F11. Para grabar: Programador > Código > Grabar macro.

Grabar la primera macro: dar formato a un informe

1. Activa la pestaña Programador (Archivo > Opciones > Personalizar cinta de opciones).
2. Selecciona A1:D20 en la hoja Ventas.
3. Programador > Código > Grabar macro. Nombre: FormatoInforme. Aceptar.
4. Aplica Inicio > Fuente > Negrita, Inicio > Fuente > Color de relleno (uno cualquiera) y Inicio > Fuente > Bordes > Todos los bordes.
5. Programador > Código > Detener grabación.
6. Ejecuta con Programador > Código > Macros > Ejecutar.

El código que Excel escribió por ti

Pulsa Alt+F11 para abrir el editor. En Módulo1 verás la macro generada. Las líneas clave son Selection.Font.Bold = True (negrita) y el bloque With Selection.Interior (color de relleno).

Sub FormatoInforme()
    Selection.Font.Bold = True
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
    End With
    With Selection.Borders
        .LineStyle = xlContinuous
    End With
End Sub

Crear una función propia con un cuadro de entrada

En Módulo1, escribe esta otra macro que pregunta un nombre y lo saluda.

Sub Saludo()
    Dim nombre As String
    nombre = InputBox("Como te llamas?")
    MsgBox "Hola, " & nombre
End Sub

Como te llamas?
Hola, Carlos

Los errores más frecuentes del Excel avanzado y cómo arreglarlos

Por qué BUSCARV devuelve #N/A si el dato está ahí

Porque la columna de búsqueda no es la primera del rango, o porque el dato tiene espacios al principio o al final. La solución es asegurar que la clave esté en la primera columna y usar RECORTAR (TRIM) para limpiar.

=BUSCARV(RECORTAR(A2);Productos!A:B;2;FALSO)

Qué hacer cuando F4 no fija los dólares

Porque la tecla Fn del portátil está activa, porque otro programa captura F4 o porque no estás editando la fórmula. Pulsa F2 para entrar en edición, coloca el cursor sobre la referencia y prueba Fn+F4; si sigue sin funcionar, prueba Ctrl+F4 si lo tienes.

Lo que ya sabes hacer con un Excel avanzado y el siguiente paso

• Cruzar datos entre hojas con BUSCARV y BUSCARX.
• Resumir miles de filas con tablas dinámicas y segmentaciones.
• Importar y limpiar datos con Power Query desde CSV, web o carpetas.
• Grabar macros que ahorran trabajo repetido.
• Leer y entender código VBA sencillo.

El siguiente nivel natural es un Power BI o tablas dinámicas avanzadas basadas en el modelo de datos de Excel.

Dudas habituales sobre un curso de Excel avanzado