Usa FILTER para aislar una categoría exacta de recursos gráficos y, después, XLOOKUP para devolver el nivel correspondiente al umbral de cantidad más alto que no supere tu recuento. En esta guía se crea un ejemplo completo para Excel para Mac con etiquetas ficticias y pruebas de límites.

¿Qué versiones de Excel para Mac admiten esta fórmula?

La fórmula siguiente usa LET, FILTER y XLOOKUP. La documentación de Microsoft indica que las tres están disponibles en Excel para Microsoft 365 para Mac, Excel 2024 para Mac y Excel 2021 para Mac. Microsoft también menciona estas funciones en su resumen de novedades de Excel 2021 para Mac.

Excel 2016 y Excel 2019 no pueden evaluar XLOOKUP; Microsoft documenta explícitamente esa limitación. Para esta fórmula exacta, usa una de las ediciones para Mac indicadas arriba. Abrir en una edición anterior un libro que contenga esta función no añade compatibilidad con ella. Consulta la documentación de XLOOKUP de Microsoft.

Si no sabes qué edición tienes instalada, consulta la versión y el tipo de licencia en el cuadro de diálogo Acerca de de la aplicación. Microsoft explica cómo identificar estos datos en Acerca de Office: ¿Qué versión de Office estoy usando?.

Configurar cantidades mínimas por categoría

Supongamos que llevas un recuento de recursos gráficos reutilizables y quieres asignar un nivel de inventario descriptivo a cada categoría. La tarea sigue dos reglas: la categoría debe coincidir y la cantidad debe alcanzar el mínimo de un nivel. Todas las etiquetas y cantidades de abajo son ilustrativas.

En una hoja de cálculo en blanco, introduce esta tabla en A1:C7. Los encabezados van en la fila 1 y las seis filas de datos, de la 2 a la 7. Introduce las cantidades mínimas como números y rellena todas las celdas de categoría y nivel.

Categoría Cantidad mínima Etiqueta del nivel
Mosaico de textura 5 Compacto
Mosaico de textura 20 Ampliado
Mosaico de textura 50 Extenso
Sello de forma 5 Compacto
Sello de forma 30 Ampliado
Sello de forma 80 Extenso

A continuación, introduce Categoría que comprobar en E1, Cantidad en F1 y Nivel de inventario en G1. Escribe Mosaico de textura en E2 y el número 34 en F2. Deja vacía la columna D para separar los datos de referencia de los datos de entrada.

Considera las cantidades mínimas como límites inferiores inclusivos. Para Mosaico de textura, el nivel Compacto empieza en 5, Ampliado empieza en 20 y Extenso empieza en 50. Por lo tanto, un recuento de 34 corresponde a Ampliado. Para Sello de forma, las mismas etiquetas comienzan con cantidades distintas: un recuento de 20 todavía corresponde a Compacto.

Antes de escribir la fórmula, decide si tus propios límites representan mínimos o máximos. En este ejemplo se usan mínimos en todos los casos. También se dejan fuera de todos los niveles las cantidades inferiores a 5 y se permite que el último nivel continúe por encima de la cantidad en que empieza. Esas son decisiones explícitas para este ejemplo; ajusta los datos de referencia si tus reglas previstas son distintas.

Introducir la fórmula completa por categoría y cantidad

Selecciona G2, pega la fórmula completa siguiente en la barra de fórmulas y pulsa Return. Usa exactamente la disposición de la hoja indicada arriba; no se necesitan rangos con nombre ni columnas auxiliares.

=LET(
  category,$E2,
  quantity,$F2,
  categories,$A$2:$A$7,
  minimums,$B$2:$B$7,
  labels,$C$2:$C$7,
  keep,categories=category,
  IF(
    OR(category="",NOT(ISNUMBER(quantity))),
    "Enter category and numeric quantity",
    IF(
      quantity<0,
      "Use zero or more",
      IF(
        SUM(--keep)=0,
        "Category not found",
        XLOOKUP(
          quantity,
          FILTER(minimums,keep),
          FILTER(labels,keep),
          "Below first tier",
          -1
        )
      )
    )
  )
)

Para Mosaico de textura y 34, el resultado esperado es Ampliado. La fórmula devuelve una sola etiqueta, aunque en sus cálculos intermedios usa matrices. Las fórmulas de matriz dinámica modernas se introducen de la forma habitual; Microsoft explica que basta con confirmarlas con Enter en Fórmulas de matriz dinámica y comportamiento de desbordamiento.

Cómo el filtro de categoría mantiene alineadas las matrices

La expresión categories=category crea la matriz de inclusión denominada keep. Para estos datos de entrada, selecciona las tres filas de Mosaico de textura. Ambas llamadas a FILTER usan esa misma matriz y generan los mínimos 5, 20 y 50 junto con sus etiquetas correspondientes: Compacto, Ampliado y Extenso.

Microsoft documenta que FILTER selecciona datos mediante una matriz de inclusión booleana cuyas dimensiones coinciden con las del rango de origen. Usar la misma matriz de inclusión para ambas columnas conserva la correspondencia entre ellas. Consulta la función FILTER.

La función externa LET asigna nombres fáciles de leer a los rangos de origen, los datos de entrada y la matriz de inclusión. Estos nombres solo existen dentro de esta fórmula; no es necesario crearlos por separado en el libro. Microsoft describe este comportamiento en la documentación de la función LET.

Cómo la cantidad determina el nivel

El quinto argumento de XLOOKUP, -1, solicita una coincidencia exacta con la cantidad o el siguiente valor inferior. Entre las filas filtradas de Mosaico de textura, 34 selecciona el mínimo de 20 y devuelve Ampliado. Una cantidad exacta de 50 selecciona Extenso. Microsoft define estos modos en la documentación de la función XLOOKUP.

La fórmula también distingue una categoría desconocida de una cantidad inferior al primer nivel. SUM(--keep) cuenta las filas que coinciden con la categoría. Si no hay ninguna, la fórmula devuelve Category not found antes de intentar la búsqueda filtrada. Si la categoría existe, pero todos sus mínimos superan la cantidad, la búsqueda devuelve Below first tier.

Probar los límites y la separación de categorías

Sustituye E2 y F2 por los siguientes pares, de uno en uno. Estas comprobaciones son más útiles que probar solo un valor intermedio: ponen a prueba el primer umbral, los límites exactos, las diferencias entre categorías y las categorías inexistentes.

Categoría en E2 Cantidad en F2 Resultado esperado
Mosaico de textura 0 Below first tier
Mosaico de textura 5 Compacto
Mosaico de textura 19 Compacto
Mosaico de textura 20 Ampliado
Mosaico de textura 34 Ampliado
Mosaico de textura 50 Extenso
Sello de forma 20 Compacto
Sello de forma 34 Ampliado
Sello de forma 100 Extenso
Muestra de color 34 Category not found

Las dos pruebas con una cantidad de 20 son especialmente reveladoras. Mosaico de textura debe devolver Ampliado, mientras que Sello de forma debe devolver Compacto. Si ambas devuelven Ampliado, comprueba que la búsqueda por cantidad use las filas de la categoría filtrada.

Estos diez resultados esperados se comprobaron con un pequeño cálculo en Python que implementa la regla indicada de umbrales mínimos. Se revisó la fórmula de Excel según la sintaxis documentada por Microsoft, pero no se ejecutó en Excel.

Comprueba también los mensajes de entrada: una categoría vacía o una cantidad introducida como texto debería devolver Enter category and numeric quantity; una cantidad numérica negativa debería devolver Use zero or more. Usa números enteros para los recuentos de recursos. La fórmula comprueba que las cantidades sean numéricas y no negativas, pero no exige que sean números enteros.

Diagnosticar resultados inesperados y ampliar la hoja

Sigue estas comprobaciones en orden para evitar que un problema de formato lleve a cambiar las reglas de los niveles.

  1. Comprueba el texto de la categoría. Compara el dato de entrada con las etiquetas de referencia, incluidos los espacios. Microsoft señala los caracteres inesperados y los espacios ocultos como problemas comunes de búsqueda en su guía para solucionar problemas de búsqueda.
  2. Comprueba que las celdas sean numéricas. Los datos de entrada de cantidad y los mínimos deben contener números, no texto que parezca numérico. La misma guía de Microsoft identifica los tipos de datos incoherentes como un problema de búsqueda.
  3. Comprueba la alineación de los rangos. Los rangos de categoría, mínimo y etiqueta deben abarcar las mismas filas de datos. En este ejemplo, todos empiezan en la fila 2 y terminan en la fila 7.
  4. Comprueba las reglas duplicadas. Asigna una sola etiqueta de nivel a cada combinación de categoría y cantidad mínima. Si dos filas asignan etiquetas diferentes al mismo límite, resuelve esa ambigüedad en los datos de referencia.
  5. Comprueba la convención de los límites. Un resultado que parece corresponder a un nivel demasiado alto o bajo puede indicar que los valores de referencia son máximos, mientras que esta fórmula espera mínimos.

Para inspeccionarlo visualmente, introduce la siguiente fórmula de diagnóstico en una celda sin usar, como I2. Con Mosaico de textura en E2, debería mostrar las tres filas completas de Mosaico de textura. Esta es una fórmula de diagnóstico independiente, no un reemplazo de G2.

=FILTER($A$2:$C$7,$A$2:$A$7=$E2,"Category not found")

Deja espacio para que el resultado de diagnóstico se expanda a lo ancho de tres columnas y hacia abajo tres filas. El resultado de una matriz independiente se desborda hacia las celdas contiguas y las celdas ocupadas pueden provocar #SPILL!. Microsoft explica este comportamiento en Fórmulas de matriz dinámica y comportamiento de desbordamiento.

Al añadir filas de referencia, amplía juntos los tres rangos de origen de la fórmula principal. Por ejemplo, si la última fila de datos pasa a ser la 10, cambia las definiciones a categories,$A$2:$A$10, minimums,$B$2:$B$10 y labels,$C$2:$C$10. Después, repite las pruebas de límites para la categoría añadida.

Para procesar varias filas de datos de entrada, introduce más pares de categoría y cantidad debajo de E2:F2 y copia G2 hacia abajo. Los rangos de origen permanecen fijos porque sus referencias contienen signos de dólar; los números de fila de los datos de entrada avanzan. Mantén explícitos los límites de cada categoría y usa las pruebas de categorías emparejadas para confirmar que cada etiqueta devuelta siga la regla de inventario prevista.

Fuentes