Usa FILTER para devolver todas las cantidades asociadas a una etiqueta de recurso gráfico en Excel para Mac. La matriz resultante puede expandirse a las celdas de la hoja de cálculo, alimentar otro cálculo o sumarse sin perder las entradas coincidentes repetidas.
Este tutorial usa etiquetas y cantidades sintéticas de recursos gráficos. La tarea consiste en seleccionar una etiqueta, recuperar todas sus cantidades y reutilizar esos números individuales en un cálculo. Microsoft documenta que FILTER devuelve una matriz según criterios especificados. Función FILTER.
¿Qué versiones de Excel para Mac admiten este método?
La documentación actual de Microsoft indica explícitamente que Excel para Microsoft 365 para Mac, Excel 2024 para Mac y Excel 2021 para Mac admiten FILTER. Usa una de esas ediciones para las fórmulas siguientes. Función FILTER.
Para identificar tu instalación, abre Excel y elige Excel → Acerca de Excel. Microsoft documenta esta ruta de menú de Mac, y su guía para identificar versiones explica que el cuadro de diálogo «Acerca de» de la aplicación para Mac muestra el número de versión y el tipo de licencia. Comprueba la edición además del número de compilación. Compartir documentos con otras versiones de Office para Mac; Acerca de Office: ¿Qué versión de Office estoy usando?.
Excel 2019 para Mac y Excel 2016 para Mac quedan fuera de la disponibilidad documentada de FILTER. Si usas cualquiera de esas ediciones, la opción admitida para este flujo de trabajo específico con matrices dinámicas es abrir el libro en una de las ediciones de Mac indicadas. Las instrucciones siguientes dependen de ese requisito previo; no son una alternativa con fórmulas matriciales heredadas.
Configurar un pequeño ejemplo de cantidades de activos
En una hoja de cálculo en blanco, introduce los siguientes datos en A1:B7. La primera fila contiene encabezados. Introduce las cantidades como números, incluido el cero en B6.
| Fila de la hoja | Columna A: Activo | Columna B: Cantidad |
|---|---|---|
| 1 | Activo | Cantidad |
| 2 | Mosaico A | 4 |
| 3 | Arco B | 7 |
| 4 | Mosaico A | 2 |
| 5 | Punto C | 5 |
| 6 | Mosaico A | 0 |
| 7 | Mosaico A | 4 |
Introduce Activo seleccionado en D1 y Mosaico A en D2. Introduce Cantidades coincidentes en F1. Deja vacías las celdas debajo de F2 para que haya espacio para expandir el resultado.
Este ejemplo incluye deliberadamente dos cantidades de cuatro y una cantidad de cero para la misma etiqueta. Un resultado correcto debe conservar las cuatro filas coincidentes: 4, 2, 0 y 4. Eliminar valores repetidos o excluir el cero cambiaría el resultado solicitado.
Devolver todas las cantidades coincidentes con FILTER
Selecciona F2, introduce la siguiente fórmula y pulsa Return:
=FILTER(B2:B7,A2:A7=D2)
La fórmula usa B2:B7 como matriz de cantidades y A2:A7=D2 como criterio de inclusión. Estos rangos cubren las mismas seis filas de origen, lo que satisface el requisito de Microsoft de que la matriz de inclusión tenga una altura o anchura compatible. Función FILTER.
Con los datos de ejemplo, el resultado ocupa F2:F5:
| Celda | Resultado |
|---|---|
| F2 | 4 |
| F3 | 2 |
| F4 | 0 |
| F5 | 4 |
Introduce la fórmula solo en F2; Excel coloca automáticamente los resultados adicionales en las celdas contiguas. Microsoft denomina a este comportamiento expansión. Para cambiar la fórmula más adelante, edita la celda superior izquierda del resultado, que en este caso es F2. Fórmulas de matriz dinámica y comportamiento de expansión de matrices.
Las dos apariciones del cuatro representan filas de origen coincidentes distintas. Consérvalas ambas cuando el siguiente cálculo deba tener en cuenta cada aparición del activo seleccionado.
Por qué XLOOKUP no recupera estas cuatro filas
Microsoft describe XLOOKUP como una función que devuelve el elemento correspondiente a la primera coincidencia que encuentra. Puede devolver varias columnas de una fila coincidente, pero esa capacidad no enumera todas las filas que contienen la misma etiqueta. Función XLOOKUP.
Por ejemplo, la siguiente fórmula devuelve solo 4 del ejemplo:
=XLOOKUP(D2,A2:A7,B2:B7)
Ese resultado es adecuado cuando una fila coincidente proporciona la respuesta. Para esta tarea, cuatro filas coincidentes distintas proporcionan las cantidades, así que usa el resultado de FILTER.
Reutilizar la matriz en otros cálculos
Haz referencia al resultado completo con F2#. Microsoft documenta el signo de almohadilla como el operador de rango expandido: hace referencia a toda la matriz generada a partir de la celda inicial. Así, la fórmula posterior no queda limitada al resultado actual de cuatro celdas. Directrices y ejemplos de fórmulas matriciales.
Coloca las fórmulas posteriores junto al resultado, no inmediatamente debajo, y deja espacio para más cantidades coincidentes. Los ejemplos siguientes usan las columnas H a J.
Sumar todas las cantidades coincidentes
Introduce Cantidad total en H1 y luego introduce esta fórmula en H2:
=SUM(F2#)
El resultado ilustrativo es 10: cuatro más dos más cero más cuatro. SUM acepta rangos y suma sus valores numéricos. Función SUM.
Si solo necesitas el total, también puedes calcularlo directamente en otra celda vacía:
=SUM(FILTER(B2:B7,A2:A7=D2))
Elige la matriz visible si quieres revisar o reutilizar las cantidades individuales. Elige la fórmula anidada si la hoja de cálculo solo necesita el total final.
Multiplicar cada cantidad por un factor común
Supongamos que el ejemplo requiere tres copias de cada cantidad coincidente. Introduce Copias en I1 y el número 3 en I2. Introduce Cantidades ajustadas en J1 y luego introduce esta fórmula en J2:
=F2#*$I$2
Los resultados esperados en J2:J5 son 12, 6, 0 y 12. Microsoft documenta cálculos matriciales que multiplican valores y devuelven varios resultados, y su guía de calculadora para Mac identifica el asterisco como el operador de multiplicación. Directrices y ejemplos de fórmulas matriciales; Usar Excel como calculadora en Excel para Mac.
Para obtener un único total ajustado, introduce lo siguiente en H4:
=SUM(F2#*$I$2)
El total esperado es 30. Estos son cálculos ilustrativos con los datos sintéticos proporcionados. La aritmética se comprobó de forma independiente; las fórmulas del libro se contrastaron con la documentación de Microsoft, pero no se ejecutaron en Excel para Mac.
Decidir qué debe ocurrir si no hay coincidencias
Cambia D2 por Ola D, una etiqueta ausente del ejemplo. La fórmula original de dos argumentos FILTER devuelve #CALC! porque Excel no puede devolver una matriz vacía. Microsoft documenta el tercer argumento opcional como la forma de indicar un valor cuando no hay resultados. Cómo corregir un error #CALC!.
Elige ese valor según lo que necesite el siguiente cálculo. La siguiente guía de decisión se aplica a esta hoja de cálculo:
| Requisito | Fórmula o método | Consecuencia |
|---|---|---|
| Las coincidencias ausentes deben seguir siendo visibles durante los cálculos | Conserva =FILTER(B2:B7,A2:A7=D2) |
Si no hay coincidencias, se produce #CALC! y se solicita una revisión. |
| El resultado es principalmente una lista legible | Usa =FILTER(B2:B7,A2:A7=D2,"No matches") |
Si no hay coincidencias, se devuelve un mensaje de texto. |
| Una etiqueta ausente debe aportar cero al total | Usa =SUM(FILTER(B2:B7,A2:A7=D2,0)) |
Si no hay coincidencias, el total es cero. |
Usar cero como valor alternativo es una regla de cálculo deliberada. No significa que exista una fila coincidente de origen con cantidad cero. En este ejemplo, Mosaico A tiene una cantidad real de cero; Ola D no tiene filas de origen. Conserva esa distinción siempre que importe la presencia de un activo.
Un mensaje de texto también requiere cuidado: Microsoft documenta que SUM ignora el texto de las celdas referenciadas. Por lo tanto, sumar un resultado que solo contiene Sin coincidencias puede dar cero y ocultar la ausencia de filas coincidentes. Usa el enfoque con error visible cuando los cálculos posteriores deban detenerse si falta una etiqueta. Función SUM.
Restablece D2 a Mosaico A antes de continuar con los ejemplos numéricos originales.
Añadir una segunda condición solo cuando la tarea lo requiera
Si una tarea posterior pide específicamente cantidades positivas, usa tanto la prueba de etiqueta como una prueba de cantidad positiva:
=FILTER(B2:B7,(A2:A7=D2)*(B2:B7>0))
Para el ejemplo, esto devuelve 4, 2 y 4. Microsoft documenta que la multiplicación entre condiciones funciona como una prueba AND en FILTER. Esta variante excluye intencionalmente el cero coincidente, así que conserva la fórmula original cuando necesites todas las cantidades coincidentes. Función FILTER.
Solucionar problemas del resultado e incluir las filas nuevas
Si la fórmula no produce las cuatro cantidades esperadas, comprueba la hoja de cálculo en este orden:
- Revisa el espacio de salida. Si el rango de resultados está bloqueado, se produce
#SPILL!. Mueve el contenido necesario fuera del área prevista para la expansión antes de volver a intentarlo. - Comprueba dónde está la fórmula. Las fórmulas de matriz expandida deben estar en la cuadrícula de la hoja de cálculo, fuera de una tabla de Excel.
- Comprueba las dependencias del libro. Microsoft admite matrices dinámicas vinculadas entre libros solo mientras ambos estén abiertos; al actualizar después de cerrar el archivo de origen, puede aparecer
#REF!.
Estos límites se documentan en Fórmulas de matriz dinámica y comportamiento de expansión de matrices.
A continuación, revisa las etiquetas y cantidades de origen. Compara D2 con las celdas de activos previstas y comprueba que las cantidades se hayan introducido como números. Un total plausible no basta para verificarlo, porque SUM puede ignorar los valores de texto. Función SUM.
Las fórmulas del ejemplo hacen referencia a las filas de origen 2 a 7. Si añades datos en la fila 8, edita ambos rangos de origen para incluirlos. Por ejemplo, después de introducir Mosaico A en A8 y la cantidad numérica 3 en B8, sustituye la fórmula en F2 por:
=FILTER(B2:B8,A2:A8=D2)
La matriz esperada pasa a ser 4, 2, 0, 4, 3, y el total pasa a ser 13. La referencia F2# sigue el resultado ampliado, pero no puede hacer que los rangos de origen originales incluyan una fila omitida.
Para una lista que crece con frecuencia, Microsoft recomienda usar una tabla de Excel con referencias estructuradas, que se ajustan al añadir o eliminar filas de la tabla. Mantén el resultado expandido fuera de esa tabla. Fórmulas de matriz dinámica y comportamiento de expansión de matrices.
Antes de confiar en el total, comprueba los resultados individuales: para las seis filas de origen originales, al seleccionar Mosaico A deben aparecer cuatro cantidades, incluidos los dos cuatros y el cero. Cuando eso coincida, reutiliza F2# en los cálculos y elige una regla explícita para los casos sin coincidencias que se ajuste al propósito de la hoja de cálculo.
Fuentes
- Función FILTER — Microsoft Support.
- Compartir documentos con otras versiones de Office para Mac — Microsoft Support.
- Acerca de Office: ¿Qué versión de Office estoy usando? — Microsoft Support.
- Fórmulas de matriz dinámica y comportamiento de expansión de matrices — Microsoft Support.
- Función XLOOKUP — Microsoft Support.
- Directrices y ejemplos de fórmulas matriciales — Microsoft Support.
- Función SUM — Microsoft Support.
- Usar Excel como calculadora en Excel para Mac — Microsoft Support.
- Cómo corregir un error #CALC! — Microsoft Support.