Manejo avanzado de Excel: fórmulas, datos y automatización

Un manejo avanzado de Excel combina fórmulas, análisis, limpieza de datos y automatización. En este tutorialwachstum sobre un libro real de ventas, aprenderás a consultar precios con BUSCARX (XLOOKUP), resumir importes con tablas dinámicas, preparar los datos con Power Query y ordenar fechas mediante una macro.

La parte de fórmulas funciona en Excel 2021, 2024 y Microsoft 365. Power Query está integrado desde Excel 2016; las tablas dinámicas y las macros existen desde versiones anteriores. Los datos están comprobados en octubre de 2026.

Qué puedes resolver con un manejo avanzado de Excel

El libro del tutorial se llama Ventas2026. Su hoja Ventas registra seis operaciones con estas columnas: Fecha, Producto, Cantidad, Precio e Importe.

Una hoja así parece sencilla, pero cada tarea de gestión tiene una solución distinta: localizar un dato, resumir las ventas, corregir un importe o repetir una ordenación. Con estas herramientas el libro deja de ser una lista y se convierte en un informe reutilizable.

Las herramientas avanzadas y dónde están

BUSCARX admite búsqueda vertical u horizontal, coincidencia exacta o aproximada y un valor para cuando no encuentra el resultado. Power Query se abre desde la pestaña Datos. Para macros, activa primero la pestaña Programador.

Consultar y devolver datos con BUSCARX

BUSCARX (XLOOKUP) recibe el valor buscado, el rango donde se busca, el rango que se devuelve y argumentos opcionales. Su ventaja frente a BUSCARV es que el rango de resultados puede estar a la izquierda.

Para consultar el precio de un Monitor:

=BUSCARX("Monitor";B2:B7;D2:D7;"No encontrado";0)

El último argumento, 0, exige una coincidencia exacta. Si el producto no existe, devuelve el texto indicado en lugar de mostrar un error.

Resumir ventas con una tabla dinámica

Al confirmar el rango, Excel propone una hoja nueva para el resumen. Colocas Producto en Filas, Fecha en Columnas e Importe en Valores. El resultado muestra el total de cada producto y fecha sin escribir una suma manual.

Limpiar y combinar datos con Power Query

Power Query abre el editor de consultas. Desde ahí puedes quitar espacios sobrantes, borrar filas vacías y cambiar el tipo de datos de las columnas. Si otro libro aporta datos adicionales, puedes combinarlos mediante las opciones de combinación del editor.

Automatizar una tarea con el grabador de macros

En la lista de la derecha, marca Programador y pulsa Aceptar. Después, desde Programador > Grabar macro, das un nombre a la macro, ejecutas la tarea y terminas con Detener grabación. El código queda en el módulo correspondiente.

Buscar datos y resolver consultas con BUSCARX

Primero crea una hoja nueva llamada Consultas. En A2 escribe “Monitor”. En B2 calcula su precio actual y en B3 el total de unidades vendidas.

1. En la hoja Ventas, selecciona A1:E7.
2. Copia esos datos en la hoja Consultas, desde A5.
3. En A2, escribe “Monitor”.
4. En B2, pega la fórmula de BUSCARX.
5. En B3, usa SUMAR.SI (SUMIF) para sumar las cantidades del producto escrito en A2.

=SUMAR.SI(Ventas!B2:B7;A2;Ventas!C2:C7)

Buscar hacia la izquierda sin cambiar la fórmula

Añade Código antes de Producto en la hoja Ventas. Cada fila tendrá M-01, T-01, M-02, R-01, T-02 y M-03.

La búsqueda está en A2:A7 y el resultado en B2:B7. BUSCARX no obliga a que el rango devuelto quede a la derecha.

Con BUSCARX y coincidencia exacta

Para devolver “Monitor” desde el código M-01:

=BUSCARX("M-01";A2:A7;B2:B7;"Código no encontrado";0)

La coincidencia exacta exige que el código exista. Si introduces M-99, el resultado será el texto alternativo, no un error.

Con BUSCARX y coincidencia aproximada

Crea una hoja Precios con fecha y precio, ordenada de fecha más antigua a más reciente: 01/01/2026, 210,00; 08/01/2026, 215,00; 15/01/2026, 220,00.

El argumento 1 activa la coincidencia aproximada. La búsqueda devuelve el último precio válido que no supera la fecha consultada.

Reemplazar BUSCARX por BUSCARV

En Excel 2019 o 2016, la misma consulta de precio se resuelve con BUSCARV (VLOOKUP):

=BUSCARV(A2;Ventas!B2:E7;3;FALSO)

El tercer argumento indica qué columna devuelve el resultado. BUSCARV sí exige que el valor buscado esté en la primera columna del rango y que la columna de resultados esté a su derecha.

Obtener resultados exactos con BUSCARV y BUSCARX

En la hoja Consultas, estas dos fórmulas deben devolver lo mismo para “Monitor”:

=BUSCARX(A2;Ventas!B2:B7;Ventas!D2:D7;"No encontrado";0)
=BUSCARV(A2;Ventas!B2:E7;3;FALSO)

Cuándo aparece el error #N/A

Si en A2 escribes M-99 y la fórmula usa BUSCARV, aparecerá #N/A. La causa es que no existe ese código en la columna de búsqueda. Corrige el código por M-01 o amplía el rango hasta la última fila.

Cómo evitar referencias desplazadas al copiar

Si copias =BUSCARV(A2;Ventas!A2:E7;5;FALSO) una fila hacia abajo, el rango pasa a A3:E8 y la última fila de datos queda fuera.

Bloquea el rango con referencias absolutas:

=BUSCARV(A2;Ventas!$A$2:$E$7;5;FALSO)

Convertir una lista de ventas en tabla dinámica

La hoja Ventas ya contiene datos y una columna de códigos. Ahora añade Responsable con Ana, Luis y Marta para que el resumen permita comparar grupos.

1. Selecciona A1:F7 en la hoja Ventas.
2. Pulsa Insertar > Tablas > Tabla dinámica.
3. Elige una hoja nueva y acepta la ubicación propuesta.
4. Arrastra Producto al área Filas.
5. Arrastra Fecha al área Columnas.
6. Arrastra Importe al área Valores.

Resumir totales por producto y fecha

La suma de cada producto debe coincidir con sus operaciones:

Para añadir totales por responsable, mueve Responsable al área Filas, debajo de Producto. Excel anidará ambos niveles.

Filtrar y comparar grupos con segmentación de datos

1. Selecciona una celda de la tabla dinámica.
2. Pulsa Insertar > Segmentación de datos.
3. En Columnas, elige Producto.
4. Acepta el tamaño y la ubicación.

Al pulsar “Monitor”, la tabla muestra 1.300,00 y oculta los totales de Teclado y Mouse. La segmentación modifica la vista, no los datos de origen.

Limpiar datos antes de analizarlos con Power Query

Antes de confiar en los totales, hay que corregir el origen. En la hoja Ventas, la celda D6 contiene deliberadamente el texto “43,00” con formato de texto. Power Query permite localizarlo, convertirlo y devolver una tabla limpia.

1. Selecciona A1:F7 en Ventas.
2. Pulsa Datos > Obtener datos > From Table/Range.
3. En el editor, activa Transformar datos.
4. Selecciona la columna Precio.
5. Pulsa Transformar > Tipo de datos > Número decimal.
6. Cierra el editor y conserva la carga en la hoja.

Detectar valores que no son números

Abre otra vez la consulta y usa el filtro de encabezados de la columna Precio. Desmarca la opción de valores numéricos: el filtro mostrará qué registro está fuera del tipo.

Después crea una columna nueva con Agregar columna > Columna personalizada y usa:

Actualizar los resultados sin repetir la importación

Cambia el importe de la fila del Mouse de 148,00 a 156,00 en la tabla cargada y, en el origen, modifica el precio de 18,50 a 19,50.

1. En la pestaña Datos, selecciona la consulta.
2. Pulsa Actualizar todo.
3. Actualiza también la tabla dinámica.

Automatizar una tarea repetitiva con una macro

La macro ordenará las ventas por la columna Fecha. Con las fechas en orden creciente, las dos filas del Monitor quedan consecutivas.

1. En Ventas, selecciona A1:F7.
2. En Programador, pulsa Grabar macro.
3. Escribe OrdenarVentasPorFecha y pulsa Aceptar.
4. En Datos > Ordenar, elige Fecha de más antigua a más reciente.
5. Pulsa Detener grabación en Programador.

El código generado empieza así:

Sub OrdenarVentasPorFecha()
    Range("A1:F7").Select
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range("A2:A7"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range("A1:F7")
        .Header = xlYes
        .Apply
    End With
End Sub

Guardar el libro para conservar la macro

Guarda el libro con una extensión que admita VBA.

Al volver a abrir un archivo .xlsm, Excel puede mostrar la barra Habilitar contenido. Las macros están guardadas, pero permanecen desactivadas hasta que decidas habilitarlas.

Editar la macro con el Editor de Visual Basic

1. En Programador, pulsa Visual Basic o usa Alt+F11.
2. En el panel Proyectos, abre VBAProject (Ventas2026) y la carpeta Módulos.
3. Haz doble clic en el módulo con la macro grabada.
4. Cambia Range("A1:F7") por el rango real que quieras ordenar.

ActiveSheet.Sort.SortFields.Add Key:=Range("A2:A1000"), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

Al cambiar el código, vuelve a Excel con Alt+F11 y ejecuta la macro desde Programador > Macros.

Controlar los errores antes de que lleguen a la hoja

=SI(CONTARA(A2:A7)=0;"Sin datos";BUSCARV(A2;Ventas!$A$2:$E$7;5;FALSO))

Identificar el error antes de envolver la fórmula

SI.ERROR (IFERROR) oculta el motivo del fallo. Conviene aplicarlo cuando la ausencia de un dato ya está prevista; no cuando necesitas saber qué dato falla.

Para separar una celda vacía de un código inexistente:

=SI(A2="";"Falta código";BUSCARV(A2;Ventas!$A$2:$E$7;5;FALSO))

El texto solo aparece cuando A2 está vacío. Si contiene un código incorrecto, la fórmula conserva el error para que puedas corregir el origen.

Evitar que una macro sobrescriba datos importantes

Una macro grabada con WholeSheet.Sort puede mover cabeceras, totales y notas. La alternativa es ordenar solo los datos:

Sub OrdenarSoloVentas()
    Range("A2:F7").Sort Key:=Range("A2"), _
        Order:=xlAscending, Header:=xlNo
End Sub

Si el libro tuviera 100.000 filas y un total en la fila 100.002, ordenar A2:F100.000 dejaría intactos el total y la fila posterior. Por eso conviene calcular el último dato antes de definir el rango.

Qué exige cada función según tu versión de Excel

Alternativas cuando BUSCARX no está disponible

Para devolver un dato situado a la derecha, utiliza BUSCARV:

=BUSCARV(A2;Ventas!$A$2:$E$7;5;FALSO)

Para buscar hacia la izquierda, combina INDICE (INDEX) y COINCIDIR (MATCH):

=INDICE(Ventas!$B$2:$B$7;COINCIDIR(A2;Ventas!$A$2:$A$7;0))

COINCIDIR localiza la posición exacta del código y INDICE devuelve el producto en esa posición.

Qué cambia al trabajar en Excel para la web

La versión web ofrece menos funciones y herramientas de automatización. No ejecuta macros VBA. Para automatizar tareas allí se utilizan Scripts de Office, que trabajan conEssyn con el modelo de objetos disponible en el servicio.

Lo que ya sabes hacer con un manejo avanzado de Excel

• Consultar precios y códigos con BUSCARX, y usar BUSCARV o INDICE y COINCIDIR en versiones anteriores.
• Construir resúmenes por producto, fecha y responsable con tablas dinámicas y segmentaciones.
• Limpiar precios y fechas con Power Query, y actualizar resultados sin repetir la importación.
• Grabar una macro de ordenación, guardarla en .xlsm y ajustar su rango en el Editor de Visual Basic.
• Detectar #N/A y #VALOR! antes de envolver las fórmulas con SI.ERROR.

El siguiente paso es diseñar una macro para el informe mensual: crea una hoja Resumen, calcula los totales del mes y automatiza su actualización.