Usa FILTER per restituire in Excel per Mac tutte le quantità associate a un’etichetta di elemento grafico. L’array risultante può espandersi nelle celle del foglio di lavoro, alimentare un altro calcolo o essere sommato senza perdere le voci corrispondenti ripetute.

Questa guida usa etichette e quantità sintetiche di elementi grafici. L’obiettivo è selezionare un’etichetta, recuperare tutte le quantità associate e riutilizzare ciascun numero in un calcolo. Microsoft documenta che FILTER restituisce un array in base ai criteri specificati. Funzione FILTER.

Quali versioni di Excel per Mac supportano questo metodo?

La documentazione aggiornata di Microsoft indica esplicitamente che Excel per Microsoft 365 per Mac, Excel 2024 per Mac ed Excel 2021 per Mac supportano FILTER. Usa una di queste edizioni per le formule riportate di seguito. Funzione FILTER.

Per identificare la tua installazione, apri Excel e scegli Excel → Informazioni su Excel. Microsoft documenta questo percorso di menu per Mac e spiega nella guida all’identificazione della versione che la finestra Informazioni dell’app per Mac mostra il numero di versione e il tipo di licenza. Controlla anche l’edizione, oltre al numero di build. Condividere documenti con altre versioni di Office per Mac; Informazioni su Office: quale versione di Office sto usando?.

Excel 2019 per Mac ed Excel 2016 per Mac non rientrano tra le versioni per cui è documentata la disponibilità di FILTER. Se usi una di queste edizioni, per questo specifico flusso di lavoro con matrici dinamiche puoi aprire la cartella di lavoro in una delle edizioni per Mac elencate. Le istruzioni seguenti dipendono da questo requisito; non sono una soluzione alternativa basata su formule matriciali legacy.

Impostare un semplice esempio di quantità di elementi grafici

In un foglio di lavoro vuoto, inserisci i dati seguenti nell’intervallo A1:B7. La prima riga contiene le intestazioni. Inserisci le quantità come numeri, incluso lo zero in B6.

Riga del foglio di lavoro Colonna A: elemento Colonna B: quantità
1 Elemento Quantità
2 Piastrella A 4
3 Arco B 7
4 Piastrella A 2
5 Punto C 5
6 Piastrella A 0
7 Piastrella A 4

Inserisci Elemento selezionato in D1 e Piastrella A in D2. Inserisci Quantità corrispondenti in F1. Lascia vuote le celle sotto F2 per dare spazio all’espansione del risultato.

Questo esempio include intenzionalmente due quantità pari a quattro e una quantità pari a zero per la stessa etichetta. Un risultato corretto deve conservare tutte e quattro le righe corrispondenti: 4, 2, 0 e 4. Eliminare i valori ripetuti o escludere lo zero modificherebbe il risultato richiesto.

Restituire tutte le quantità corrispondenti con FILTER

Seleziona F2, inserisci la formula seguente e premi Invio:

=FILTER(B2:B7,A2:A7=D2)

La formula usa B2:B7 come array delle quantità e A2:A7=D2 come criterio di inclusione. Questi intervalli coprono le stesse sei righe di origine, rispettando il requisito di Microsoft secondo cui l’array di inclusione deve avere un’altezza o una larghezza compatibile. Funzione FILTER.

Con i dati di esempio, il risultato occupa l’intervallo F2:F5:

Cella Risultato
F2 4
F3 2
F4 0
F5 4

Inserisci la formula solo in F2; Excel inserisce automaticamente i risultati aggiuntivi nelle celle vicine. Microsoft chiama questo comportamento espansione. Per modificare la formula in seguito, modifica la cella in alto a sinistra del risultato, che in questo caso è F2. Formule di matrice dinamica e comportamento delle matrici espanse.

Le due occorrenze del valore quattro rappresentano righe di origine corrispondenti distinte. Conservale entrambe quando il calcolo successivo deve tenere conto di ogni occorrenza dell’elemento selezionato.

Perché XLOOKUP non recupera queste quattro righe

Microsoft descrive XLOOKUP come una funzione che restituisce l’elemento corrispondente alla prima corrispondenza trovata. Può restituire più colonne di una riga corrispondente, ma questa capacità non elenca tutte le righe che contengono la stessa etichetta. Funzione XLOOKUP.

Ad esempio, la formula seguente restituisce solo 4 dai dati di esempio:

=XLOOKUP(D2,A2:A7,B2:B7)

Questo risultato è adatto quando una sola riga corrispondente fornisce la risposta. Per questa attività, le quantità provengono da quattro righe corrispondenti distinte, quindi usa il risultato di FILTER.

Riutilizzare l’array in altri calcoli

Fai riferimento all’intero risultato con F2#. Microsoft documenta il simbolo cancelletto come operatore di intervallo espanso: si riferisce all’intero array prodotto dalla cella iniziale. In questo modo la formula successiva non è vincolata all’attuale risultato di quattro celle. Linee guida ed esempi di formule matriciali.

Inserisci le formule successive accanto al risultato, anziché subito sotto, lasciando spazio per eventuali altre quantità corrispondenti. Gli esempi seguenti usano le colonne da H a J.

Sommare tutte le quantità corrispondenti

Inserisci Quantità totale in H1, quindi inserisci questa formula in H2:

=SUM(F2#)

Il risultato illustrativo è 10: quattro più due più zero più quattro. SUM accetta intervalli e somma i relativi valori numerici. Funzione SUM.

Se ti serve solo il totale, puoi calcolarlo direttamente in un’altra cella vuota:

=SUM(FILTER(B2:B7,A2:A7=D2))

Scegli l’array visibile quando vuoi esaminare o riutilizzare le singole quantità. Scegli la formula annidata quando nel foglio di lavoro serve solo il totale finale.

Moltiplicare ogni quantità per un fattore comune

Supponiamo che l’esempio richieda tre copie di ciascuna quantità corrispondente. Inserisci Copie in I1 e il numero 3 in I2. Inserisci Quantità rettificate in J1, quindi inserisci questa formula in J2:

=F2#*$I$2

I risultati previsti in J2:J5 sono 12, 6, 0 e 12. Microsoft documenta i calcoli matriciali che moltiplicano i valori e restituiscono più risultati; la guida per Mac alla calcolatrice identifica l’asterisco come operatore di moltiplicazione. Linee guida ed esempi di formule matriciali; Usare Excel come calcolatrice in Excel per Mac.

Per ottenere un solo totale rettificato, inserisci quanto segue in H4:

=SUM(F2#*$I$2)

Il totale previsto è 30. Si tratta di calcoli illustrativi basati sui dati sintetici forniti. I calcoli sono stati verificati in modo indipendente; le formule della cartella di lavoro sono state confrontate con la documentazione Microsoft, ma non eseguite in Excel per Mac.

Decidere cosa fare quando non ci sono corrispondenze

Modifica D2 in Onda D, un’etichetta assente dai dati di esempio. La formula originale di FILTER con due argomenti restituisce #CALC! perché Excel non può restituire un array vuoto. Microsoft documenta l’argomento facoltativo del terzo valore come metodo per specificare un valore da restituire quando non ci sono risultati. Come correggere un errore #CALC!.

Scegli il valore in base alle esigenze del calcolo successivo. Il seguente schema decisionale si applica a questo foglio di lavoro:

Requisito Formula o approccio Conseguenza
Le corrispondenze mancanti devono restare visibili durante i calcoli Mantieni =FILTER(B2:B7,A2:A7=D2) Nessuna corrispondenza produce #CALC!, invitando a verificare.
Il risultato è principalmente un elenco leggibile Usa =FILTER(B2:B7,A2:A7=D2,"No matches") Se non ci sono corrispondenze, viene restituito un messaggio di testo.
Un’etichetta assente deve contribuire con zero al totale Usa =SUM(FILTER(B2:B7,A2:A7=D2,0)) Se non ci sono corrispondenze, il totale è zero.

Usare zero come valore alternativo è una regola di calcolo intenzionale. Non significa che esista una riga di origine corrispondente con quantità zero. In questo esempio, Piastrella A ha una quantità reale pari a zero; Onda D non ha righe di origine. Mantieni questa distinzione quando è importante sapere se un elemento è presente.

Anche un messaggio di testo richiede attenzione: Microsoft documenta che SUM ignora il testo nelle celle a cui si fa riferimento. Di conseguenza, sommare un risultato che contiene solo Nessuna corrispondenza può restituire zero e nascondere l’assenza di righe corrispondenti. Usa l’approccio con errore visibile se i calcoli successivi devono interrompersi quando manca un’etichetta. Funzione SUM.

Reimposta D2 su Piastrella A prima di proseguire con gli esempi numerici originali.

Aggiungere una seconda condizione solo se l’attività lo richiede

Se un’attività successiva richiede specificamente quantità positive, usa sia il criterio dell’etichetta sia quello della quantità positiva:

=FILTER(B2:B7,(A2:A7=D2)*(B2:B7>0))

Per i dati di esempio, restituisce 4, 2 e 4. Microsoft documenta la moltiplicazione tra condizioni come test AND in FILTER. Questa variante esclude intenzionalmente lo zero corrispondente, quindi mantieni la formula originale quando ti serve ogni quantità corrispondente. Funzione FILTER.

Risolvere i problemi del risultato e includere le nuove righe

Se la formula non produce le quattro quantità previste, controlla il foglio di lavoro nell’ordine seguente:

  1. Controlla lo spazio di output. Se l’intervallo di espansione è occupato, viene generato #SPILL!. Prima di riprovare, sposta i contenuti necessari fuori dall’area destinata all’espansione.
  2. Controlla la posizione della formula. Le formule di matrice espansa devono trovarsi nella griglia del foglio di lavoro, fuori da una tabella di Excel.
  3. Controlla le dipendenze della cartella di lavoro. Microsoft supporta gli array dinamici collegati tra cartelle di lavoro solo quando entrambe sono aperte; se aggiorni dopo aver chiuso la cartella di lavoro di origine, può verificarsi #REF!.

Questi limiti sono documentati in Formule di matrice dinamica e comportamento delle matrici espanse.

Quindi controlla le etichette e le quantità di origine. Confronta D2 con le celle previste per gli elementi e verifica che le quantità siano state inserite come numeri. Un totale plausibile da solo non basta come verifica, perché SUM può ignorare i valori di testo. Funzione SUM.

Le formule di esempio fanno riferimento alle righe di origine da 2 a 7. Se aggiungi dati nella riga 8, modifica entrambi gli intervalli di origine affinché la includano. Ad esempio, dopo aver inserito Piastrella A in A8 e la quantità numerica 3 in B8, sostituisci la formula in F2 con:

=FILTER(B2:B8,A2:A8=D2)

L’array previsto diventa 4, 2, 0, 4, 3 e il totale diventa 13. Il riferimento F2# segue il risultato espanso, ma non può includere una riga omessa dagli intervalli di origine iniziali.

Per un elenco che cresce spesso, Microsoft consiglia una tabella Excel con riferimenti strutturati, che si aggiornano quando si aggiungono o si rimuovono righe. Mantieni il risultato espanso fuori da quella tabella. Formule di matrice dinamica e comportamento delle matrici espanse.

Prima di affidarti al totale, controlla i singoli risultati: per le sei righe di origine iniziali, selezionando Piastrella A dovresti ottenere quattro quantità, comprese entrambe le occorrenze di quattro e lo zero. Dopo aver verificato il risultato, riutilizza F2# nei calcoli e scegli una regola esplicita per i casi senza corrispondenze, adatta allo scopo del foglio di lavoro.

Fonti