Las funciones básicas de Excel funcionan bien para cálculos sencillos, pero se complican rápidamente al analizar datos complejos. El resultado son fórmulas anidadas difíciles de leer, múltiples columnas auxiliares que saturan la hoja de cálculo y fórmulas que pueden fallar al cambiar los datos. Aquí es donde entran en juego las fórmulas matriciales de Excel.
Las fórmulas matriciales le permiten realizar cálculos en rangos completos de datos en una sola fórmula. Por lo tanto, puedes Realizar búsquedas ultrarrápidas, filtre y ordene con una expresión potente, en lugar de escribir fórmulas separadas para cada fila o columna. No es algo nuevo en Excel, pero algunas personas se aferran a las viejas formas de hacer las cosas cuando estas funciones pueden hacer que su trabajo sea más simple y eficiente.
Enlaces rápidos
5. BUSCARX
Supera a BUSCARV en todo momento.
BUSCARX es la función de búsqueda que debería haber existido desde el principio. A diferencia de BUSCARV, que obliga a contar columnas y solo busca a la derecha, BUSCARX funciona en cualquier dirección y utiliza referencias de columna reales. Tiene la siguiente sintaxis:
= XLOOKUP (lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Esto es lo que significa cada parámetro:
- valor de búsqueda: El valor específico que busca. Puede ser un número de pieza, un código de producto o cualquier identificador de su conjunto de datos.
- matriz_buscada: El rango en el que Excel busca valor de búsqueda Tuyo. Suele ser una sola columna o fila que contiene tus criterios de búsqueda.
- matriz de retorno: El rango que contiene los valores que desea recuperar. Puede ser una sola columna, varias columnas o incluso una sección completa de la tabla.
- if_not_found (opcional): Texto o valor personalizado para mostrar cuando no se encuentra ninguna coincidencia. Elimina los molestos errores #N/A y permite mostrar "No encontrado" o "Verificar número de pieza".
- match_mode (opcional): Controla el tipo de coincidencia. Use 0 para coincidencia exacta (predeterminado), -1 para la siguiente coincidencia exacta o menor, 1 para la siguiente coincidencia exacta o mayor, y 2 para coincidencia con comodín.
- modo_búsqueda (opcional): Especifica la dirección de búsqueda. Use 1 para una búsqueda del primero al último (predeterminado), -1 para una búsqueda del último al primero y 2 para una búsqueda binaria en datos ordenados.
Tomemos como ejemplo una hoja de cálculo de inventario mecánico. La siguiente fórmula busca el número de pieza "BRG-002" dentro de un rango de ID de pieza y devuelve los datos correspondientes. Si la pieza no está presente, se muestra "Pieza no encontrada" en lugar de un error.
=BUSCARXL("BRG-002", A:A, A:H, "Pieza no encontrada")
XLOOKUP le permite extraer datos de diferentes columnas sin los engorrosos cálculos de columnas que se encuentran en VLOOKUP, lo que lo convierte en una de las funciones más importantes. Funciones de Excel para encontrar datos rápidamente.
4. SUMPRODUCT
Central generadora de energía para cálculos condicionales
SUMAPRODUCTO no solo suma números, sino que también multiplica matrices y suma los resultados. Esto lo hace útil para cálculos condicionales complejos que requieren varias columnas auxiliares.
Tiene la siguiente fórmula:
=SUMAPRODUCTO(matriz1, [matriz2], [matriz3], ...)
aquí, array1 Es el primer rango de valores a multiplicar, generalmente la columna de datos principal, como cantidades o costos. array2 Es un segundo rango opcional para la multiplicación, que a menudo contiene criterios o lógica condicional utilizando operadores de comparación.
Se vuelven más útiles cuando usamos operadores lógicos dentro de matrices. Por ejemplo, al escribir condiciones como (proveedor="Siemens"), Excel convierte los resultados VERDADERO/FALSO a 1/0, lo que permite realizar cálculos.
Por ejemplo, la siguiente fórmula calcula el valor total del inventario de las piezas suministradas únicamente por Siemens. La fórmula multiplica las cantidades por los costes unitarios, pero solo para las filas donde el proveedor cumple los criterios.
=SUMPRODUCT(D2:D100*H2:H100*(G2:G100="Siemens"))
De manera similar, la siguiente fórmula determina el costo total de un stock de rodamientos en buen estado:
=SUMPRODUCT((C2:C100="Bearings")*(D2:D100>=15)*H2:H100)
Se aplican dos condiciones simultáneamente: la categoría debe ser “Rodamientos” y los niveles de inventario deben ser de 15 unidades o más, lo que nos ayuda a identificar categorías de rodamientos que tienen suficiente cobertura de inventario.
A diferencia de las funciones SUMA tradicionales con múltiples criterios, SUMPRODUCTO no requiere estructuras anidadas complejas porque maneja múltiples condiciones en una única fórmula legible. Funciones SUMA en Excel, Al igual que SUMAR.SI y SUMAR.CONJUNTO, son excelentes para sumas condicionales simples, pero la función SUMAPRODUCTO sobresale cuando necesita multiplicar valores antes de sumar o manejar operaciones lógicas más complejas.
3. FILTRAR
Simplifica la extracción dinámica de datos
FILTER extrae filas de su conjunto de datos según las condiciones que especifique. A diferencia del filtrado manual, esta función genera resultados dinámicos que se actualizan automáticamente cuando cambian los datos de origen. La sintaxis de FILTER es la siguiente:
=FILTRO(matriz, incluir, [si_está_vacío])
Esto es lo que controla cada entrada:
- matriz (rango): El rango completo de datos que desea filtrar. Esto incluye todas las columnas que desea en sus resultados, no solo la columna de criterios.
- incluir: Condición lógica que especifica qué filas devolver: utiliza operadores de comparación para crear matrices VERDADERO/FALSO para cada fila.
- if_empty (opcional): Muestra un mensaje personalizado cuando ninguna fila cumple con tus criterios. Evita errores #CALC! y muestra texto con significado como "No se encontraron resultados coincidentes".
La función evalúa la condición con cada fila del rango. Cuando la condición devuelve VERDADERO, esa fila completa aparece en los resultados filtrados. A continuación, se muestra un ejemplo de una hoja de cálculo de inventario mecánico:
=FILTER(A2:H101, (C2:C101="Bearings")*(G2:G101="Timken"))
Esta fórmula extrae todas las filas donde el recurso es "Timken" y la categoría es "Rodamientos". El asterisco (*) crea una condición AND al multiplicar las matrices lógicas.
Cuando agrega nuevos datos a su rango de origen, Uso de la función FILTRO en Excel Es más práctico que la ordenación manual y las tablas temporales, ya que los resultados filtrados se actualizan automáticamente. Esto lo hace útil para crear paneles e informes en tiempo real.
2. UNIQUE
Extraer valores únicos sin duplicados
UNIQUE extrae valores únicos de su rango de datos y evita automáticamente los duplicados. Esta función es importante si desea crear listas desplegables, analizar categorías de datos y generar informes resumidos. La fórmula es:
=ÚNICO(matriz, [por_columna], [exactamente_una vez])
Así es como funciona cada entrada:
- matriz (rango): El rango que contiene los datos de los que desea eliminar duplicados: puede ser una sola columna, varias columnas o una sección completa de la tabla.
- por_col (opcional): FALSO compara filas para determinar su unicidad (predeterminado), mientras que VERDADERO compara columnas. Sin embargo, la mayoría de los casos utilizan la comparación de filas predeterminada.
- exactamente_una vez (opcional): FALSO devuelve todos los valores únicos, incluidos aquellos que aparecen varias veces (predeterminado), y VERDADERO devuelve solo los valores que aparecen exactamente una vez en el conjunto de datos.
La función UNIQUE evalúa cada fila o valor de la matriz y devuelve solo la primera ocurrencia de cada elemento único. El orden coincide con la secuencia de datos original. A continuación, se muestra un ejemplo:
=ÚNICO(G2:G22)
Esta fórmula extrae todos los nombres de proveedores únicos de la columna G y crea una lista limpia y duplicada. La utilizo para crear listas desplegables de proveedores o informes resumidos.
También puedes usarlo en toda la tabla, como se muestra a continuación:
=ÚNICO(A2:F100)
Devuelve combinaciones únicas en todas las columnas (A a F), mostrando registros de inventario distintos. Si dos piezas tienen valores idénticos en cada columna, solo una aparecerá en los resultados.
Al trabajar con grandes conjuntos de datos, UNIQUE elimina el tedioso proceso de eliminar manualmente los duplicados. Los resultados dinámicos se actualizan con la llegada de nuevos datos, y dado que UNIQUE crea matrices de derrame, este enfoque elimina la molestia de redimensionar las tablas al escalarlas automáticamente para acomodar todos los valores únicos. Lo utilizo para mantener listas de referencias limpias y crear rangos de validación de datos fiables.
1. ORDENAR y ORDENAR POR
Organiza tus datos sin comprometer el original
Las funciones SORT y SORTBY organizan los datos dinámicamente, conservando el origen. SORT gestiona la ordenación básica por posición de columna, mientras que SORTBY ordena según los valores de diferentes columnas, lo que ofrece mayor flexibilidad para ordenaciones complejas.
SORT utiliza esta estructura:
= CLASIFICAR (matriz, [índice_clasificación], [orden_clasificación], [por_columna])
Esto es lo que controla cada parámetro:
- formación: El rango de datos que desea ordenar: incluye todas las columnas que deben aparecer en los resultados ordenados.
- sort_index (opcional): El número de columna de la matriz por el que se ordenará. Use 1 para la primera columna, 2 para la segunda, y así sucesivamente (el valor predeterminado es 1).
- ordenar_orden (opcional): Utilice 1 para orden ascendente (predeterminado) y -1 para orden descendente.
- por_col (opcional): FALSO para ordenar por filas (predeterminado), VERDADERO para ordenar por columnas (la mayoría de los escenarios utilizan la ordenación por filas).
La función ORDENAR POR toma la siguiente forma:
= ORDENAR POR (matriz, por_orden1, [ordenar_orden1], [por_matriz2], [ordenar_orden2], ...)
Sus transacciones incluyen:
- formación: El rango de datos a ordenar: similar a la función ORDENAR, contiene todas las columnas que desea en los resultados.
- por_array1: El rango que contiene los valores que determinan el orden de clasificación puede ser cualquier columna, incluso fuera del rango de la matriz principal.
- sort_order1 (opcional): 1 para orden ascendente (predeterminado), -1 para orden descendente.
- por_array2, sort_order2 (opcional): Criterios de clasificación adicionales para la clasificación multinivel.
Si tomamos como ejemplo una hoja de cálculo de inventario mecánico, estas funciones manejan escenarios de clasificación del mundo real:
=ORDENAR(A2:H22, 4, -1)
Esto ordena todo el inventario por niveles de existencias en orden descendente, mostrando primero los artículos con mayor stock. La fórmula ordena por la columna 4 (niveles de existencias), conservando las relaciones entre filas.
Estoy usando la función ORDENAR POR. En lugar de ORDENAR, puede usarlo para controlar mejor los criterios de ordenación y los múltiples niveles de ordenación. Por ejemplo, la siguiente fórmula ordena primero alfabéticamente por categoría y luego por niveles de inventario, de mayor a menor, dentro de cada categoría.
=ORDENAR(A2:H22, C2:C22, 1, D2:D22, -1)
Hojas de cálculo organizadas, resultados más inteligentes
Las fórmulas matriciales eliminan la acumulación de columnas auxiliares y funciones anidadas que dificultan el mantenimiento de las hojas de cálculo. Obtienes fórmulas individuales que gestionan múltiples operaciones, lo que hace que los libros de trabajo sean más ordenados y profesionales.
Una ventaja notable son las funciones dinámicas, que permiten que los resultados se actualicen automáticamente al cambiar los datos de origen. Esto elimina las actualizaciones manuales o las cadenas de fórmulas incompletas, lo que aumenta la fiabilidad de las hojas de cálculo para el análisis continuo.
La biblioteca de funciones de matriz de Excel continúa expandiéndose más allá de estas herramientas básicas. Cuando necesito combinar datos de varias fuentes, utilizo las funciones VSTACK y HSTACK para combinar rangos. Juntas, estas funciones crean potentes flujos de trabajo de procesamiento de datos que serían imposibles con fórmulas tradicionales.
