In Microsoft 365 per Mac o Excel 2024 per Mac, combina CHOOSECOLS, TOCOL, HSTACK e FILTER per trasformare etichette ripetute di risorse grafiche e quantità in righe di inventario. La formula di esempio qui sotto mantiene l’allineamento delle coppie, le quantità pari a zero e le voci incomplete.

Questa guida è rivolta a chi usa Excel su un computer Mac e ha un foglio di lavoro in cui si ripetono una colonna degli attributi seguita dalla relativa colonna delle quantità. In questo caso, ogni attributo è l’etichetta di una risorsa grafica, come Tessera circolare. L’obiettivo è creare un inventario a due colonne e poi cercare le quantità senza separarle dalle rispettive etichette.

Quali versioni di Excel per Mac supportano questa formula?

Microsoft indica esplicitamente Excel per Microsoft 365 per Mac ed Excel 2024 per Mac per tutte e tre le funzioni di riorganizzazione: CHOOSECOLS, TOCOL e HSTACK. Per la formula principale, usa una di queste edizioni.

Excel 2021 per Mac include le matrici dinamiche, FILTER, LET e XLOOKUP, ma questo non dimostra che supporti le nuove funzioni di riorganizzazione. La documentazione delle funzionalità di Excel 2021 per Mac di Microsoft descrive le funzionalità di gestione delle matrici e di ricerca disponibili; le pagine delle tre funzioni qui sopra non indicano Excel 2021.

Controlla l’edizione e la versione nella finestra di dialogo About dell’applicazione. La guida di Microsoft sulle versioni di Office spiega che nella finestra di dialogo per Mac sono indicate sia la versione sia il tipo di licenza. Se la tua edizione non include le funzioni di riorganizzazione, usa Microsoft 365 per Mac o Excel 2024 per Mac per il metodo automatico, oppure segui il metodo manuale descritto qui sotto.

Prepara un esempio completo con dati sintetici

In un foglio di lavoro vuoto, inserisci questi dati nell’intervallo A1:F4. Lascia effettivamente vuote le due celle centrali della riga 3. Tutte le etichette e le quantità sono esemplificative.

Attributo 1 Quantità 1 Attributo 2 Quantità 2 Attributo 3 Quantità 3
Tessera circolare 4 Trama a linee 2 Motivo a punti 0
Motivo ad arco 3 Trama a griglia 5
Tessera circolare 7 Motivo a onde 6 Tessera quadrata 1

Ogni coppia di celle adiacenti rappresenta una voce dell’inventario. Il risultato desiderato segue l’ordine da sinistra a destra di ogni riga di origine, poi prosegue verso il basso. Tessera circolare compare due volte perché si tratta di voci distinte; unire le etichette ripetute sarebbe un’attività aggiuntiva.

Questo metodo presuppone che ogni etichetta sia seguita immediatamente dalla relativa quantità e che tutte le colonne selezionate facciano parte di questo schema ripetuto. Se il foglio contiene anche il nome di una raccolta o un altro campo che deve accompagnare ogni voce, questo risultato a due colonne non lo conserva. Prima di applicare la formula, stabilisci quali campi deve contenere l’inventario.

Trasforma le coppie ripetute in righe di inventario

Inserisci Risorsa in H1 e Quantità in I1. Lascia vuota l’area di output sotto queste intestazioni. Incolla la formula completa qui sotto in H2, poi premi Return. Le formule con matrici dinamiche si inseriscono una sola volta e si espandono nelle celle adiacenti, come descritto nella guida di Microsoft sulle formule con matrice.

=LET(
    source,A2:F4,
    clean,IF(source="","",source),
    labels,TOCOL(CHOOSECOLS(clean,1,3,5),0,FALSE),
    quantities,TOCOL(CHOOSECOLS(clean,2,4,6),0,FALSE),
    keep,((labels<>"")+(quantities<>""))>0,
    FILTER(HSTACK(labels,quantities),keep,"No inventory entries")
)

Il risultato previsto occupa H2:I9:

Risorsa Quantità
Tessera circolare 4
Trama a linee 2
Motivo a punti 0
Motivo ad arco 3
Trama a griglia 5
Tessera circolare 7
Motivo a onde 6
Tessera quadrata 1

L’ordine delle coppie nell’esempio, le otto voci risultanti, la conservazione dello zero e la quantità totale di 28 sono stati verificati con un piccolo calcolo deterministico in Python. La formula di Excel è stata confrontata con la sintassi documentata da Microsoft; non è stata eseguita in Excel.

In che modo la formula mantiene allineate le coppie

LET assegna nomi ai risultati intermedi, così la formula può riutilizzarli. I nomi source, clean, labels, quantities e keep esistono solo all’interno di questa formula: non devi creare intervalli denominati. Microsoft documenta questo comportamento nel riferimento a LET.

CHOOSECOLS seleziona le colonne delle etichette 1, 3 e 5 e le colonne delle quantità 2, 4 e 6. Questi numeri indicano le posizioni all’interno dell’intervallo di origine selezionato. La documentazione di Microsoft su CHOOSECOLS conferma che restituisce le colonne specificate di una matrice.

Entrambe le chiamate a TOCOL usano 0 per conservare tutte le posizioni e FALSE per esaminare le righe orizzontalmente. Poiché percorrono le posizioni corrispondenti nello stesso ordine, ogni etichetta resta allineata alla relativa quantità. Microsoft documenta entrambi gli argomenti nel riferimento a TOCOL.

HSTACK affianca le due colonne risultanti. Prima del filtro, entrambe contengono nove posizioni, quindi hanno la stessa lunghezza. Microsoft spiega che HSTACK aggiunge matrici in orizzontale e riempie con errori le matrici più corte se hanno un numero diverso di righe.

Infine, keep seleziona le posizioni che contengono un’etichetta, una quantità o entrambe. FILTER rimuove solo le coppie completamente vuote. Il suo argomento finale fornisce un messaggio se non rimangono voci, secondo la sintassi documentata da Microsoft per FILTER.

Conserva le celle vuote e gli zeri senza nascondere le voci incomplete

Una quantità vuota e uno zero numerico trasmettono informazioni diverse. Lo zero è un conteggio inserito; una cella vuota richiede ancora un’interpretazione. Gli esempi di Microsoft per TOCOL mostrano che le celle vuote non gestite possono restituire zero. Per questo il passaggio clean usa IF per restituire testo vuoto per le celle di origine vuote prima di riorganizzarle. Microsoft documenta i valori restituiti in base a una condizione nel riferimento a IF.

La formula mantiene intenzionalmente visibili le coppie incomplete. Prima di affidarti al risultato, esegui questi controlli:

Coppia di origine Comportamento nell’output Azione
Etichetta e quantità numerica Le mantiene entrambe Verifica che la quantità sia quella desiderata
Etichetta e zero Mantiene lo zero Conservalo se rappresenta un conteggio intenzionale
Entrambe le celle vuote Rimuove la coppia Non serve alcuna correzione
Etichetta con quantità vuota Mantiene l’etichetta e la quantità vuota Completa la quantità
Etichetta vuota con quantità Mantiene l’etichetta vuota e la quantità Completa l’etichetta

Evita di rimuovere separatamente le celle vuote dalle matrici delle etichette e delle quantità. Se manca un’etichetta in una posizione e manca una quantità in un’altra, rimuovere le celle vuote separatamente può spostare le matrici e creare abbinamenti errati. La scelta progettuale fondamentale di questa formula è conservare le posizioni fino a dopo l’unione.

Una cella apparentemente vuota che contiene spazi non viene considerata vuota da questa formula. Correggi queste voci nei dati di origine. Allo stesso modo, la formula conserva le quantità inserite come testo invece di convertirle automaticamente. Controlla questi valori prima di calcolare i totali.

Cerca una voce o restituisci tutte le voci corrispondenti

Inserisci Tessera circolare in K2. Per recuperare la prima quantità, inserisci questa formula in L2:

=XLOOKUP(K2,CHOOSECOLS(H2#,1),CHOOSECOLS(H2#,2),"No matching asset",0)

H2# fa riferimento all’intero risultato espanso che inizia in H2, quindi la ricerca si adatta alle variazioni delle sue dimensioni. Microsoft spiega questa notazione nella documentazione sull’operatore di intervallo espanso.

Il risultato è 4. La modalità di corrispondenza esplicita 0 richiede una corrispondenza esatta e la ricerca predefinita restituisce la prima voce corrispondente. Il riferimento di Microsoft a XLOOKUP documenta entrambi i comportamenti. Conferma inoltre che XLOOKUP non è disponibile in Excel 2016 e Excel 2019.

Se le etichette ripetute hanno significato, cercare la prima corrispondenza non basta. Per restituire tutte le voci Tessera circolare, inserisci questa formula alternativa completa in N2 e lascia spazio nelle colonne N:O:

=FILTER(H2#,CHOOSECOLS(H2#,1)=K2,"No matching asset")

Questa formula restituisce due righe: Tessera circolare con 4 e Tessera circolare con 7. FILTER restituisce le righe che soddisfano la condizione specificata, come documentato nel riferimento di Microsoft a FILTER. Scegli la formula che restituisce la prima corrispondenza se ti basta una voce corrispondente; scegli il risultato filtrato se devi esaminare ogni occorrenza.

Verifica il risultato e adatta l’intervallo di origine

Per l’esempio, verifica che siano presenti otto righe di output e che Motivo a punti abbia ancora quantità zero. Poi confronta il totale delle quantità di origine con quello delle quantità riorganizzate. Inserisci queste formule in due celle libere:

=SUM(B2:B4,D2:D4,F2:F4)

=SUM(CHOOSECOLS(H2#,2))

Entrambe dovrebbero restituire 28. La documentazione di Microsoft su SUM conferma che è possibile sommare più intervalli e spiega che i valori di testo vengono ignorati. Totali uguali, quindi, non dimostrano che tutte le quantità siano numeriche e non possono rilevare quantità abbinate alle etichette sbagliate. Controlla anche le coppie etichetta-quantità.

Per includere più righe di origine, sostituisci A2:F4 nella formula principale con l’intervallo effettivo, ad esempio A2:F100. Le righe esterne a tale riferimento non verranno incluse. Per quattro coppie ripetute, estendi l’origine a otto colonne, ad esempio A2:H100, e aggiorna le posizioni delle etichette a 1,3,5,7 e quelle delle quantità a 2,4,6,8.

Se estendi l’origine a otto colonne, sposta l’output in un’area libera, ad esempio J2, e aggiorna i riferimenti agli intervalli espansi usati nelle formule successive a J2#. Mantieni separate l’origine e l’area di output. Una posizione di colonna non valida genera un errore, come descritto nella sezione sugli errori di CHOOSECOLS.

Risolvi i problemi di output bloccato o usa il metodo manuale

Se la formula principale restituisce #SPILL!, seleziona la cella della formula e controlla l’area di output prevista. Sposta le voci esistenti fuori da quest’area e verifica che non ci siano celle unite. Inserisci la formula fuori da una tabella di Excel: Microsoft documenta che le formule con matrici espanse non possono espandersi all’interno delle tabelle o nelle celle unite nella guida alla risoluzione dei problemi di #SPILL!.

Se l’origine contiene un errore, correggi la voce di origine prima di usare l’inventario. La formula non sopprime gli errori: Microsoft osserva che un errore nella matrice di inclusione di FILTER fa sì che la funzione restituisca un errore. Racchiudere l’intera trasformazione in una soppressione degli errori potrebbe nascondere una voce da correggere.

Per un foglio di lavoro piccolo in Excel 2021 per Mac, puoi ottenere manualmente la stessa struttura a due colonne: copia A2:B4 in H2:I4, copia C2:D4 in H5:I7 e copia E2:F4 in H8:I10. Rimuovi solo le righe in cui entrambe le celle sono vuote. In questo modo le voci vengono impilate in blocchi di coppie, anziché nell’ordine per riga di origine seguito dalla formula principale, e dovrai ripetere l’operazione dopo ogni modifica ai dati di origine.

Dopo questa preparazione manuale, in Excel 2021 per Mac puoi usare la funzionalità documentata XLOOKUP con intervalli fissi:

=XLOOKUP(K2,H2:H10,I2:I10,"No matching asset",0)

Un inventario completo e utile conserva ogni coppia significativa, rende visibili le voci incomplete e usa una ricerca adatta alle etichette ripetute. Verifica queste caratteristiche prima con l’esempio, poi estendi l’intervallo di origine in base al tuo foglio di lavoro.

Fonti