Per espandere una matrice delle quantità di risorse grafiche in una riga del foglio di lavoro per ogni elemento, elenca prima ogni combinazione risorsa-formato con una quantità cumulativa. Poi usa SEQUENCE e XLOOKUP per ripetere le etichette il numero di volte richiesto. Questa guida usa etichette di opere d’arte sintetiche.

L’obiettivo è semplice: trasformare una griglia di pianificazione compatta in un elenco di elementi. Una quantità pari a due produce due righe con la stessa risorsa e lo stesso formato; una quantità pari a zero non ne produce nessuna. L’esempio svolto qui sotto lascia visibili i calcoli intermedi, così puoi verificare il risultato prima di adattarlo.

Quali versioni di Excel per Mac supportano questo metodo?

Usa Excel per Microsoft 365 per Mac, Excel 2024 per Mac o Excel 2021 per Mac. La documentazione Microsoft indica queste edizioni sia per SEQUENCE sia per XLOOKUP e la guida alle funzionalità di Excel 2021 per Mac documenta esplicitamente le matrici dinamiche. Funzione SEQUENCE, Funzione XLOOKUP, Novità di Excel 2021 per Mac.

La disponibilità delle funzioni è importante quando copi formule da altre guide. Per esempio, Microsoft indica VSTACK per Microsoft 365 ed Excel 2024 su Mac, ma non per Excel 2021. Il metodo qui descritto evita questa dipendenza. Microsoft afferma inoltre esplicitamente che XLOOKUP non è disponibile in Excel 2016 ed Excel 2019, quindi queste edizioni non possono eseguire le formule riportate qui sotto senza modifiche. Funzione VSTACK, Funzione XLOOKUP.

Configurare la matrice delle quantità delle risorse grafiche

In un foglio di lavoro vuoto, inserisci i seguenti dati sintetici nell’intervallo A1:C4. La prima colonna contiene le etichette delle risorse, la riga di intestazione contiene i formati e le celle rimanenti contengono le quantità.

Risorsa Piccolo Grande
Piastrella ad arco 2 0
Piastrella a punti 1 0
Piastrella a onde 2 1

In questo esempio, ogni quantità deve essere un numero intero pari o superiore a zero. Inserisci esplicitamente zero quando non serve alcun elemento. Prima di procedere, correggi le celle vuote, le quantità testuali, i valori negativi, le frazioni e gli errori nelle celle. Queste formule presuppongono conteggi validi; non li convalidano né li arrotondano.

Il risultato previsto contiene sei righe di elementi: due Piastrelle ad arco piccole, una Piastrella a punti piccola, due Piastrelle a onde piccole e una Piastrella a onde grande. Le etichette ripetute sono intenzionali, perché ogni riga rappresenta un elemento. Le formule creano record nel foglio di lavoro; non creano né duplicano file di opere d’arte.

Appiattire la matrice in un elenco ausiliario verificabile

Lascia vuota la colonna D. Inserisci le intestazioni Posizione, Risorsa, Formato, Quantità e Ultimo elemento in E1:I1. Inserisci i numeri da uno a sei, in ordine, nell’intervallo E2:E7. Servono sei righe ausiliarie perché tre risorse moltiplicate per due formati danno sei combinazioni.

L’elenco ausiliario procede da sinistra a destra in ogni riga di origine prima di passare alla riga successiva: Piastrella ad arco piccola, Piastrella ad arco grande, poi le due combinazioni della Piastrella a punti, seguite dalle due combinazioni della Piastrella a onde. È essenziale mantenere questo ordine coerente.

Recuperare le etichette e la quantità di ciascuna combinazione

Inserisci questa formula in F2:

=INDEX($A$2:$A$4,INT((E2-1)/2)+1)

Inserisci questa formula in G2:

=INDEX($B$1:$C$1,1,MOD(E2-1,2)+1)

Inserisci questa formula in H2:

=INDEX($B$2:$C$4,INT((E2-1)/2)+1,MOD(E2-1,2)+1)

Copia F2:H2 fino alla riga sette. Sono normali formule di cella, con un risultato per cella. INDEX recupera un valore in una riga e colonna specificate. Qui, INT fa avanzare la riga di origine ogni due posizioni, mentre MOD alterna la colonna di origine tra i due formati. Funzione INDEX, Funzione INT, Funzione MOD.

Calcolare il numero dell’ultimo elemento di ciascuna combinazione

In I2, inserisci la formula seguente e copiala fino a I7:

=SUM($H$2:H2)

Il riferimento iniziale fisso e quello finale progressivo creano un totale progressivo. SUM somma le quantità dalla prima riga ausiliaria a quella corrente. Funzione SUM.

L’elenco ausiliario completato dovrebbe apparire così:

Posizione Risorsa Formato Quantità Ultimo elemento
1 Piastrella ad arco Piccolo 2 2
2 Piastrella ad arco Grande 0 2
3 Piastrella a punti Piccolo 1 3
4 Piastrella a punti Grande 0 3
5 Piastrella a onde Piccolo 2 5
6 Piastrella a onde Grande 1 6

Interpreta Ultimo elemento come un limite. La prima combinazione occupa i numeri di elemento uno e due. La terza combinazione occupa il numero di elemento tre. La quinta occupa quattro e cinque, e la sesta occupa sei. Una quantità pari a zero non sposta in avanti il limite, quindi non le viene assegnato alcun numero di elemento.

Generare una riga di output per ogni elemento

In K1, inserisci Risorsa; in L1, inserisci Formato. Lascia vuote le celle sotto entrambe le intestazioni. Inserisci l’output in normali celle del foglio di lavoro, fuori da una tabella di Excel: Microsoft documenta che le formule di matrice con espansione non sono supportate all’interno delle tabelle. Formule di matrice dinamica e comportamento delle matrici espanse.

Inserisci questa formula completa in 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))

Inserisci questa formula completa in 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))

Conferma ciascuna formula premendo Return. Non copiare queste due formule verso il basso: i risultati delle matrici riempiono automaticamente le righe adiacenti. Microsoft descrive questa espansione automatica come «spill». Formule di matrice dinamica e comportamento delle matrici espanse.

SEQUENCE genera i numeri di elemento da uno fino alla quantità totale. Per ciascun numero, XLOOKUP cerca i limiti cumulativi. Gli ultimi due argomenti specificano una corrispondenza esatta o il valore successivo più grande, con ricerca dal primo all’ultimo. Questa direzione di ricerca conserva il primo limite corrispondente quando gli zeri producono totali ripetuti. Funzione SEQUENCE, Funzione XLOOKUP.

La IF esterna restituisce testo vuoto quando il totale è zero. In tal caso, le due celle di ancoraggio sembrano vuote e non ci sono record di elementi da utilizzare. Si tratta della condizione intenzionale di totale zero, che usa IF per scegliere tra due risultati. Funzione IF.

Per la matrice fornita, K1:L7 dovrebbe contenere:

Risorsa Formato
Piastrella ad arco Piccolo
Piastrella ad arco Piccolo
Piastrella a punti Piccolo
Piastrella a onde Piccolo
Piastrella a onde Piccolo
Piastrella a onde Grande

Entrambe le colonne di output usano gli stessi numeri di elemento generati e lo stesso intervallo di limiti. Cambia solo l’intervallo dei valori restituiti: la colonna F fornisce le etichette delle risorse e la colonna G fornisce i formati. Mantieni sincronizzati questi input di ricerca quando modifichi le formule.

Verificare il risultato prima di ampliare i dati di origine

Con i dati di esempio, esegui tre verifiche:

  1. Confronta i totali: =SUM(B2:C4) e =SUM(H2:H7) devono restituire entrambi sei.
  2. Confronta le combinazioni: l’output deve contenere le quattro combinazioni non nulle, ripetute il numero di volte indicato.
  3. Modifica B2 da due a tre: l’output previsto diventa di sette righe, con tre voci Piastrella ad arco piccola. Poi ripristina il valore a due.

La prima verifica rileva quantità omesse o duplicate durante l’appiattimento. La seconda rileva etichette non allineate, cosa che il solo totale non può rivelare. La terza verifica che le modifiche si propaghino dall’elenco ausiliario all’output.

Aggiungere altre risorse o formati con attenzione

Le modifiche alle quantità nell’intervallo B2:C4 usano la configurazione esistente. Per aumentare le dimensioni della matrice, è necessario modificare i riferimenti e la lunghezza dell’elenco ausiliario.

Per un esempio completo, amplia l’origine a quattro risorse e tre formati in A1:D5. Sposta o mantieni l’area ausiliaria in E:I, inserisci le posizioni da uno a dodici nell’intervallo E2:E13 e sostituisci le formule in F2:H2 con:

=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 quelle formule fino alla riga tredici ed estendi la formula del totale progressivo nella colonna I fino alla stessa riga. In entrambe le formule di output, sostituisci con la riga tredici ogni riferimento all’intervallo ausiliario che termina alla riga sette. Il divisore e la base del resto ora sono tre, perché ogni risorsa ha tre formati. Dopo queste modifiche, ricontrolla il totale di origine e le singole combinazioni.

Risolvere un output incompleto o bloccato

Se Excel visualizza #SPILL!, controlla nell’area di output prevista la presenza di voci esistenti o celle unite e verifica che la formula sia fuori da una tabella di Excel. Anche un risultato che oltrepassa il bordo del foglio di lavoro non può espandersi. Microsoft documenta ciascuna di queste condizioni. Come correggere un errore #SPILL!.

Se l’output ha troppe poche righe, controlla le quantità prima di modificare la logica di ricerca. Microsoft specifica che SUM ignora il testo negli intervalli a cui si fa riferimento. Una quantità memorizzata come testo può quindi essere esclusa dal totale. Correggi il valore di origine e ripeti il controllo del totale. Funzione SUM.

Se le etichette sono errate nonostante il totale corretto, confronta l’elenco ausiliario con la matrice cella per cella. Mantieni insieme, in ogni riga ausiliaria, la risorsa, il formato, la quantità e il limite cumulativo. Non riordinare singolarmente una colonna ausiliaria.

Quando i totali e le combinazioni corrispondono, usa le due colonne di output come elenco di elementi. Conserva la matrice e i calcoli ausiliari, così le successive modifiche alle quantità rimangono riconducibili alle righe ripetute.

Fonti