Para expandir una matriz de cantidades de recursos gráficos en una fila de la hoja de cálculo por artículo, primero enumera cada combinación de recurso y formato con una cantidad acumulada. Después, usa SEQUENCE y XLOOKUP para repetir sus etiquetas el número de veces necesario. Este recorrido usa etiquetas de ilustraciones ficticias.
La tarea es sencilla: convertir una cuadrícula de planificación compacta en una lista de artículos. Una cantidad de dos genera dos filas con el mismo recurso y formato; cero no genera ninguna. El ejemplo resuelto a continuación deja visibles los cálculos intermedios para que puedas comprobar el resultado antes de adaptarlo.
¿Qué versiones de Excel para Mac admiten este método?
Usa Excel para Microsoft 365 para Mac, Excel 2024 para Mac o Excel 2021 para Mac. La documentación de Microsoft enumera estas ediciones para SEQUENCE y XLOOKUP, y la guía de características de Excel 2021 para Mac documenta explícitamente las matrices dinámicas. Función SEQUENCE, Función XLOOKUP, Novedades de Excel 2021 para Mac.
La disponibilidad de las funciones importa cuando copias fórmulas de otros tutoriales. Por ejemplo, Microsoft incluye VSTACK en Microsoft 365 y Excel 2024 para Mac, pero no en Excel 2021. Este método evita depender de esa función. Microsoft también indica explícitamente que XLOOKUP no está disponible en Excel 2016 ni en Excel 2019, por lo que esas ediciones no pueden ejecutar las fórmulas siguientes sin cambios. Función VSTACK, Función XLOOKUP.
Configurar la matriz de cantidades de recursos gráficos
En una hoja de cálculo en blanco, introduce los siguientes datos ficticios en A1:C4. La primera columna contiene las etiquetas de los recursos, la fila de encabezado contiene los formatos y las celdas restantes contienen las cantidades.
| Recurso | Pequeño | Grande |
|---|---|---|
| Baldosa Arco | 2 | 0 |
| Baldosa Punto | 1 | 0 |
| Baldosa Ola | 2 | 1 |
En este ejemplo, todas las cantidades deben ser números enteros iguales o superiores a cero. Introduce un cero explícito cuando no se necesite ningún artículo. Resuelve los valores en blanco, las cantidades de texto, los valores negativos, las fracciones y los errores de celda antes de continuar. Estas fórmulas dan por válidos los recuentos; no los validan ni los redondean.
El resultado esperado contiene seis filas de artículos: dos baldosas Arco pequeñas, una baldosa Punto pequeña, dos baldosas Ola pequeñas y una baldosa Ola grande. Las etiquetas repetidas son intencionales, porque cada fila representa un artículo. Las fórmulas crean registros en la hoja de cálculo; no crean ni duplican archivos de ilustraciones.
Aplanar la matriz en una lista auxiliar fácil de comprobar
Deja vacía la columna D. En E1:I1, introduce los encabezados Posición, Recurso, Formato, Cantidad y Artículo final. Introduce los números del uno al seis en E2:E7, en orden. Hay seis filas auxiliares porque tres recursos multiplicados por dos formatos dan seis combinaciones.
La lista auxiliar recorre cada fila de origen de izquierda a derecha antes de bajar: Baldosa Arco pequeña, Baldosa Arco grande, luego las dos combinaciones de Baldosa Punto y, por último, las dos combinaciones de Baldosa Ola. Es esencial mantener este orden de forma coherente.
Obtener las etiquetas y la cantidad de cada combinación
Introduce esta fórmula en F2:
=INDEX($A$2:$A$4,INT((E2-1)/2)+1)
Introduce esta fórmula en G2:
=INDEX($B$1:$C$1,1,MOD(E2-1,2)+1)
Introduce esta fórmula en H2:
=INDEX($B$2:$C$4,INT((E2-1)/2)+1,MOD(E2-1,2)+1)
Copia F2:H2 hasta la fila siete. Son fórmulas normales de celda, con un resultado por celda. INDEX obtiene un valor de una fila y columna especificadas. Aquí, INT hace que la fila de origen avance cada dos posiciones, mientras que MOD hace que la columna de origen alterne entre los dos formatos. Función INDEX, Función INT, Función MOD.
Calcular el número del último artículo de cada combinación
En I2, introduce la siguiente fórmula y cópiala hasta I7:
=SUM($H$2:H2)
La referencia inicial fija y la referencia final variable crean un total acumulado. SUM suma las cantidades desde la primera fila auxiliar hasta la fila actual. Función SUM.
La lista auxiliar completa debería verse así:
| Posición | Recurso | Formato | Cantidad | Artículo final |
|---|---|---|---|---|
| 1 | Baldosa Arco | Pequeño | 2 | 2 |
| 2 | Baldosa Arco | Grande | 0 | 2 |
| 3 | Baldosa Punto | Pequeño | 1 | 3 |
| 4 | Baldosa Punto | Grande | 0 | 3 |
| 5 | Baldosa Ola | Pequeño | 2 | 5 |
| 6 | Baldosa Ola | Grande | 1 | 6 |
Interpreta Artículo final como un límite. La primera combinación ocupa los números de artículo uno y dos. La tercera combinación ocupa el número de artículo tres. La quinta ocupa los números cuatro y cinco, y la sexta ocupa el seis. Una cantidad cero no hace avanzar el límite, así que no recibe números de artículo.
Generar una fila de salida por artículo
En K1, introduce Recurso; en L1, introduce Formato. Deja vacías las celdas debajo de ambos encabezados. Coloca los resultados en celdas normales de la hoja, fuera de una tabla de Excel: Microsoft documenta que las fórmulas de matriz desbordadas no se admiten dentro de las tablas. Fórmulas de matriz dinámica y comportamiento de desbordamiento.
Introduce esta fórmula completa en K2:
=IF(SUM($H$2:$H$7)=0,"",XLOOKUP(SEQUENCE(SUM($H$2:$H$7)),$I$2:$I$7,$F$2:$F$7,,1,1))
Introduce esta fórmula completa en L2:
=IF(SUM($H$2:$H$7)=0,"",XLOOKUP(SEQUENCE(SUM($H$2:$H$7)),$I$2:$I$7,$G$2:$G$7,,1,1))
Confirma cada fórmula con Return. No copies estas dos fórmulas hacia abajo: sus resultados de matriz rellenan automáticamente las filas contiguas. Microsoft describe esta expansión automática como desbordamiento. Fórmulas de matriz dinámica y comportamiento de desbordamiento.
SEQUENCE genera los números de artículo desde uno hasta la cantidad total. Para cada número, XLOOKUP busca en los límites acumulados. Sus dos últimos argumentos especifican una coincidencia exacta o el siguiente valor mayor, con una búsqueda de primero a último. Esa dirección de búsqueda conserva el primer límite coincidente cuando los ceros generan totales repetidos. Función SEQUENCE, Función XLOOKUP.
El IF externo devuelve texto vacío cuando el total es cero. En ese caso, las dos celdas de anclaje parecen vacías y no hay registros de artículos que utilizar. Esta condición de total cero es intencional y usa IF para elegir entre dos resultados. Función IF.
Con la matriz proporcionada, K1:L7 debería contener:
| Recurso | Formato |
|---|---|
| Baldosa Arco | Pequeño |
| Baldosa Arco | Pequeño |
| Baldosa Punto | Pequeño |
| Baldosa Ola | Pequeño |
| Baldosa Ola | Pequeño |
| Baldosa Ola | Grande |
Ambas columnas de salida usan los mismos números de artículo generados y el mismo rango de límites. Solo cambia el rango de valores devueltos: la columna F proporciona las etiquetas de los recursos y la columna G proporciona los formatos. Mantén sincronizadas esas entradas de búsqueda al editar las fórmulas.
Comprobar el resultado antes de ampliar el origen
Haz tres comprobaciones con los datos de muestra:
- Compara los totales: tanto
=SUM(B2:C4)como=SUM(H2:H7)deben devolver seis. - Compara las combinaciones: el resultado debe contener las cuatro combinaciones con cantidades distintas de cero y el número de repeticiones indicado.
- Cambia
B2de dos a tres: el resultado esperado pasa a ser de siete filas, con tres entradas de Baldosa Arco pequeña. Después, restáuralo a dos.
La primera comprobación detecta cantidades omitidas o duplicadas al aplanar la matriz. La segunda detecta etiquetas desalineadas, algo que el total por sí solo no puede revelar. La tercera comprueba si los cambios pasan de la lista auxiliar a los resultados.
Añadir recursos o formatos de forma deliberada
Cambiar las cantidades dentro de B2:C4 utiliza la configuración existente. Aumentar las dimensiones de la matriz requiere cambiar las referencias y la longitud de la lista auxiliar.
Como ejemplo completo, amplía el origen a cuatro recursos y tres formatos en A1:D5. Mueve o conserva el área auxiliar en E:I, introduce las posiciones del uno al doce en E2:E13 y sustituye las fórmulas de F2:H2 por estas:
=INDEX($A$2:$A$5,INT((E2-1)/3)+1)
=INDEX($B$1:$D$1,1,MOD(E2-1,3)+1)
=INDEX($B$2:$D$5,INT((E2-1)/3)+1,MOD(E2-1,3)+1)
Copia esas fórmulas hasta la fila trece y extiende la fórmula del total acumulado de la columna I hasta la misma fila. En ambas fórmulas de salida, cambia todas las referencias de los rangos auxiliares que terminan en la fila siete para que terminen en la fila trece. El divisor y la base del resto ahora son tres porque cada recurso tiene tres formatos. Vuelve a comprobar el total del origen y las combinaciones individuales después de hacer estos cambios.
Resolver resultados incompletos o bloqueados
Si Excel muestra #SPILL!, inspecciona el área de salida prevista para detectar entradas existentes o celdas combinadas y confirma que la fórmula esté fuera de una tabla de Excel. El resultado tampoco puede desbordarse si se extiende más allá del borde de la hoja. Microsoft documenta cada una de estas condiciones. Cómo corregir un error #SPILL!.
Si hay menos filas de salida de las esperadas, inspecciona las cantidades antes de cambiar la lógica de búsqueda. Microsoft indica que SUM ignora el texto en los rangos a los que se hace referencia. Por lo tanto, una cantidad almacenada como texto puede omitirse del total. Corrige el valor de origen y repite la comprobación del total. Función SUM.
Si las etiquetas son incorrectas aunque el total sea correcto, compara la lista auxiliar con la matriz celda por celda. Mantén juntos el recurso, el formato, la cantidad y el límite acumulado de cada fila auxiliar. No reordenes por separado una columna auxiliar.
Cuando coincidan los totales y las combinaciones, usa las dos columnas de salida como lista de artículos. Conserva la matriz y los cálculos auxiliares para que los cambios posteriores en las cantidades sigan siendo rastreables hasta las filas repetidas.
Fuentes
- Novedades de Excel 2021 para Mac — Microsoft Support.
- Función SEQUENCE — Microsoft Support.
- Función XLOOKUP — Microsoft Support.
- Función VSTACK — Microsoft Support.
- Función INDEX — Microsoft Support.
- Función INT — Microsoft Support.
- Función MOD — Microsoft Support.
- Función SUM — Microsoft Support.
- Función IF — Microsoft Support.
- Fórmulas de matriz dinámica y comportamiento de desbordamiento — Microsoft Support.
- Cómo corregir un error #SPILL! — Microsoft Support.