Excel
Formule e funzioni
Formule
Formule
Formule in forma di matrice: indicazioni ed esempi
Si applica aExcel per Microsoft 365 Excel per Microsoft 365 per Mac Excel 2024 Excel 2024 per Mac Excel 2021 Excel 2021 per Mac Excel 2019 Excel 2016 Excel per iPad Excel per iPhone

Una formula di matrice è una formula che può eseguire più calcoli su uno o più elementi in una matrice. Una matrice può essere considerata come una riga di valori, una colonna di valori o una combinazione di righe e colonne di valori. Le formule di matrice possono restituire più risultati oppure un singolo risultato.

A partire dall'aggiornamento di settembre 2018 per microsoft 365, qualsiasi formula che può restituire più risultati li spedirà automaticamente verso il basso o attraverso le celle adiacenti. Questa modifica al comportamento è accompagnata anche da diverse nuove funzioni di matrice dinamiche. Le formule di matrice dinamiche, sia che utilizzino funzioni esistenti sia quelle di matrice dinamica, devono essere immesse in una sola cella, quindi confermate premendo INVIO. In precedenza, le formule di matrice legacy richiedono prima di tutto la selezione dell'intero intervallo di output, quindi la conferma della formula con CTRL+MAIUSC+INVIO. Vengono in genere chiamate formule CSE.

È possibile usare le formule di matrice per eseguire attività complesse, come:

  • Creare rapidamente set di dati esemplificativi.

  • Contare il numero di caratteri contenuti in un intervallo di celle.

  • Sommare solo i numeri che soddisfano determinate condizioni, ad esempio i valori più bassi in un intervallo o i numeri compresi tra un limite superiore e un limite inferiore.

  • Sommare gli ennesimi valori in un intervallo di valori.

Gli esempi riportati nella sezione seguente illustrano come creare formule di matrice a celle multiple e a cella singola. Dove possibile, sono stati inclusi esempi con alcune delle funzioni di matrice dinamiche, oltre che con le formule di matrice esistenti immesse come matrici dinamiche e legacy.

Scaricare questi esempi

Scaricare una cartella di lavoro con tutti gli esempi di formula di matrice in questo articolo..

In questo esercizio viene descritto come usare le formule di matrice a celle multiple e a cella singola per calcolare un insieme di dati sulle vendite. Nella prima procedura viene usata una formula a celle multiple per calcolare un insieme di subtotali. Nella seconda, invece, viene usata una formula a cella singola per calcolare un importo totale.

  • Creare una formula di matrice a celle multiple

    Funzione di matrice multicella nella cella H10 = F10: F19 * G10: G19 per calcolare il numero di auto vendute in base al prezzo unitario

  • Qui viene calcolato il totale delle vendite di coupé e berline per ogni venditore immettendo =F10:F19*G10:G19 nella cella H10.

    Quando si preme INVIO, vengono visualizzati i risultati fino alle celle H10:H19. Si noti che l'intervallo espanso viene evidenziato con un bordo quando si seleziona una cella all'interno di esso. Si può anche notare che le formule nelle celle H10:H19 sono in grigio. Sono solo lì per riferimento, quindi se si vuole modificare la formula, è necessario selezionare la cella H10, dove si trova la formula principale.

  • Creare una formula di matrice a celle singole

    Formula di matrice a cella singola per calcolare il totale con = SOMMA (F10: F19 * G10: G19)

    Nella cella H20 della cartella di lavoro di esempio digitare oppure copiare e incollare =SOMMA(F10:F19*G10:G19)e quindi premere INVIO.

    In questo caso, Excel moltiplica i valori nella matrice (l'intervallo di celle da F10 a G19), quindi usa la funzione SOMMA per sommare i totali. Il risultato è un totale complessivo di $ 1.590.000 di vendite.

    Questo esempio mostra quanto può essere efficace questo tipo di formula. Si supponga ad esempio di avere 1.000 righe di dati. È possibile sommare tutti i dati o parte di essi creando una formula di matrice in un'unica cella invece di trascinare la formula verso il basso nelle 1.000 righe. Si noti, inoltre, che la formula a cella singola nella cella H20 è del tutto indipendente dalla formula a celle multiple (la formula nelle celle da H10 a H19). Questo è un altro vantaggio dell'utilizzo delle formule di matrice, ovvero la flessibilità. È possibile modificare le formule nella colonna H o eliminare del tutto tale colonna, senza alcuna conseguenza sulla formula in H20. Può essere buona norma anche avere totali indipendenti come questo, perché consente di verificare l'accuratezza dei risultati.

  • Le formule di matrice dinamiche offrono anche i seguenti vantaggi:

    • Coerenza    Facendo clic su qualsiasi cella a partire dalla H10 e procedendo verso il basso, viene visualizzata la stessa formula. Questa coerenza può contribuire ad assicurare una maggiore precisione.

    • Sicurezza    Non è possibile sovrascrivere un componente di una formula di matrice a celle multiple. Ad esempio, fare clic sulla cella H11 e premere Elimina. Excel non modifica l'output della matrice. Per cambiarlo, è necessario selezionare la cella in alto a sinistra nella matrice, o la cella H10.

    • Dimensioni dei file ridotte    In molti casi è possibile utilizzare un'unica formula di matrice anziché diverse formule intermedie. L’esempio sulle vendite auto, ad esempio, utilizza una formula di matrice per calcolare i risultati nella colonna E. Se fossero state utilizzate formule standard, quale =F10*G10, F11*G11, F12*G12…, sarebbero state necessarie 11 formule separate per calcolare gli stessi risultati. Non è un problema, ma se si avessero migliaia di righe da sommare? Quindi può esservi una grande differenza.

    • Efficienza    Le funzioni di matrice possono essere un modo efficiente per creare formule complesse. La formula di matrice =SOMMA(F10:F19*G10:G19) è la stessa cosa: =SUM(F10*G10,F11*G11,F12*G12,F13*G13,F14*G14,F15*G15,F16*G16,F17*G17,F18*G18,F19*G19).

    • Espansione    Le formule di matrice dinamiche vengono automaticamente espanse nell'intervallo di output. Se i dati di origine si trovano in una tabella di Excel, le formule di matrice dinamica verranno ridimensionate automaticamente quando si aggiungono o rimuovono i dati.

    • Errore #SPILL!    Le matrici dinamiche hanno introdotto l’Errore #SPILL, che indica che l'espansione prevista è bloccata per qualche motivo. Quando si risolve il blocco, la formula viene automaticamente espansa.

Le costanti di matrice sono un componente delle formule di matrice. Vengono create immettendo un elenco di elementi, quindi racchiudendo l'elenco tra parentesi graffe ({ }), ad esempio:

={1\2\3\4\5} o ={"Gennaio"\"Febbraio"\"Marzo"}

Se gli elementi sono separati da virgole, viene creata una matrice orizzontale (una riga). Se invece sono separati da punti e virgola, viene creata una matrice verticale (una colonna). Per creare una matrice bidimensionale, è necessario delimitare gli elementi in ogni riga con le virgole e delimitare ogni riga con i punti e virgola.

Le procedure descritte di seguito illustrano il processo di creazione di costanti orizzontali, verticali e bidimensionali. Mostra alcuni esempi sull'uso della funzione SEQUENZA per generare automaticamente costanti di matrice, nonché costanti di matrice immesse manualmente.

  • Creare una costante orizzontale

    Utilizzare la stessa cartella di lavoro degli esempi precedenti oppure creare una nuova cartella di lavoro. Selezionare una cella vuota e immettere =SEQUENZA(1;5). La funzione SEQUENZA crea una matrice di 1 riga per 5 colonne uguale a ={1\2\3\4\5}. Verrà visualizzato il risultato riportato di seguito:

    Creare una costante di matrice orizzontale con =SEQUENZA(1;5) oppure ={1,2,3,4,5}

  • Creare una costante verticale

    Selezionare una cella vuota con spazio sotto di essa e immettere =SEQUENZA(5)o ={1.2.3.4.5}. Verrà visualizzato il risultato riportato di seguito:

    Creare una costante di matrice verticale con =SEQUENZA(5) oppure ={1;2;3;4;5}

  • Creare una costante bidimensionale

    Selezionare una cella vuota con spazio a destra e sotto e immettere =SEQUENZA(3;4). Verrà visualizzato il risultato riportato di seguito.

    Creare una costante di matrice di 3 righe per 4 colonne con = SEQUENZA(3,4)

    È anche possibile immettere: oppure ={1\2\3\4.5\6\7\8.9\10\11\12}, ma è bene prestare attenzione alla posizione in cui inserire il punto e virgola e le virgole.

    Come si può vedere, l'opzione SEQUENZA offre vantaggi significativi rispetto all'immissione manuale dei valori delle costanti di matrice. Consente principalmente di risparmiare tempo, ma consente anche di ridurre gli errori generati dall'immissione manuale. È anche più facile da leggere, soprattutto perché i punti e virgola possono essere difficili da distinguere dai separatori virgole.

Ecco un esempio che usa le costanti di matrice come parte di una formula più grande. Nella cartella di lavoro di esempio passare al foglio di lavoro Costante in una formula oppure creare un nuovo foglio di lavoro.

Nella cella D9 è stato immesso =SEQUENZA(1,5,3,1), ma è anche possibile immettere 3, 4, 5, 6 e 7 nelle celle A9:H9. Non c'è niente di speciale nella selezione di numeri, si è scelto qualcosa di diverso da 1-5 per la differenziazione.

Nella cella E11 immettere =SOMMA(D9:H9*SEQUENZA(1,5))o =SOMMA(D9:H9*{1\2\3\4\5}). Le formule restituiscono 85.

Usare costanti di matrice nelle formule. In questo esempio è stato usato =SOMMA(D9:H(*SEQUENZA(1,5))

La funzione SEQUENZA crea l'equivalente della costante di matrice {1\2\3\4\5}. Poiché le operazioni vengono eseguite a partire dalle espressioni racchiuse tra parentesi, i due elementi considerati successivamente sono i valori delle celle in D9:H9 e l’operatore di moltiplicazione (*). A questo punto, la formula moltiplica i valori nella matrice archiviata per i valori corrispondenti nella costante. Questa operazione corrisponde a:

=SOMMA(D9*1,E9*2,F9*3,G9*4,H9*5), oppure =SOMMA(3*1,4*2,5*3,6*4,7*5)

Infine, la funzione SOMMA aggiunge i valori e restituisce 85.

Per evitare di utilizzare la matrice archiviata e mantenere l'operazione interamente in memoria, sostituirla con un'altra costante di matrice:

=SOMMA(SEQUENZA(1,5,3,1)*SEQUENZA(1,5)), o =SOMMA({3\4\5\6\7}*{1\2\3\4\5})

Elementi utilizzabili nelle costanti matrice

  • Le costanti di matrice possono contenere numeri, testo, valori logici come VERO e FALSO e valori di errore come #N/D. I numeri possono essere interi, decimali o in formato scientifico. È necessario che il testo, se presente, sia racchiuso tra virgolette doppie ("testo”).

  • Le costanti di matrice non possono contenere altre matrici, formule o funzioni. In altre parole, possono contenere solo testo o numeri separati da virgole o punti e virgola. Excel visualizza un messaggio di avviso quando si immette una formula come {1\2\A1:D4} o {1\2\SOMMA(Q2:Z8)}. Inoltre, i valori numerici non possono contenere segni di percentuale, segni di dollaro, virgole o parentesi.

Assegnare un nome alle costanti di matrice rappresenta uno dei modi migliori per usarle. Le costanti denominate, infatti, risultano più semplici da usare e possono ridurre la complessità delle formule di matrice per gli altri utenti. Per denominare una costante di matrice e usarla in una formula, eseguire le operazioni seguenti:

Passare a Formule > Nomi definiti > Definisci nome. Nella casella Nome digitare Trimestre1. Nella casella Riferito a immettere la costante seguente, ricordandosi di digitare manualmente le parentesi graffe:

={"Gennaio"\"Febbraio"\"Marzo"}

La finestra di dialogo avrà un aspetto simile al seguente:

Aggiungere una costante di matrice denominata da Formule> Nomi definiti> Gestione nomi> Nuovo

Fare clic suOK, quindi selezionare una riga qualsiasi con tre celle vuote e immettere =Trimestre1.

Verrà visualizzato il risultato riportato di seguito:

Usare una costante di matrice denominata in una formula, come = Trimestre1, in cui Trimestre1 è stato definito come ={"Gennaio", "Febbraio", "Marzo"}

Se si vuole che i risultati si spostino verticalmente anziché orizzontalmente, è possibile usare =MATRASPOSTA(Trimestre1).

Se si vuole visualizzare un elenco di 12 mesi, utilizzabile ad esempio per la creazione di un rendiconto finanziario, è possibile basarne uno in base all'anno corrente con la funzione SEQUENZA. La cosa più interessante di questa funzione è che, anche se viene visualizzato solo il mese, esiste una data valida da usare in altri calcoli. Questi esempi sono disponibili nei fogli di lavoro Costante di matrice denominata e Set di dati di esempio rapido nella cartella di lavoro esemplificativa.

=TESTO(DATA(ANNO(OGGI()),SEQUENZA(1,12),1),"mmm")

Usare la combinazione delle funzioni TESTO, DATA, ANNO, OGGI e SEQUENZA per creare un elenco dinamico di 12 mesi

In questo modo viene utilizzata lafunzione DATA per creare una data basata sull'anno corrente, la funzione SEQUENZA crea una costante di matrice da 1 a 12 per gennaio a dicembre, quindi la funzione TESTO converte il formato di visualizzazione in "mmm" (gen, feb, mar e così via). Se si vuole visualizzare il nome completo del mese, ad esempio Gennaio, usare "mmmm".

Quando si utilizza una matrice denominata come formula di matrice, è importante immettere il segno di uguale, come =Trimestre1, non solo Trimestre1. In caso contrario, la matrice verrà interpretata come stringa di testo e la formula non funzionerà come previsto. Tenere presente, infine, che è possibile utilizzare combinazioni di testo e numeri. Dipende tutto dalla creatività che si vuole ottenere.

Gli esempi che seguono presentano alcune possibili applicazioni delle costanti di matrice nelle formule di matrice. In alcuni esempi viene utilizzata la funzione MATR.TRASPOSTA per convertire le righe in colonne e viceversa.

  • Moltiplicare i singoli elementi in una matrice

    Immettere =SEQUENZA(1,12)*2o ={1\2\3\4.5\6\7\8.9\10\11\12}*2

    È anche possibile eseguire la divisione con (/), aggiungere con (+) e sottrarre con (-).

  • Elevare al quadrato gli elementi in una matrice

    Immettere =SEQUENZA(1,12)^2o ={1\2\3\4.5\6\7\8.9\10\11\12}^2

  • Trovare la radice quadrata degli elementi quadrati in una matrice

    Immettere =RT.Q(SEQUENZA(1,12)^2)o =RT({1\2\3\4.5\6\7\8.9\10\11\12}^2)

  • Trasporre una riga unidimensionale

    Immettere =MATRASPOSTA(SEQUENZA(1;5))o =MATRASPOSTA({1\2\3\4\5})

    La funzione MATR.TRASPOSTA trasforma la costante di matrice orizzontale in una colonna.

  • Trasporre una colonna unidimensionale

    Immettere =MATRASPOSTA(SEQUENZA(5;1))o =MATRASPOSTA({1.2.3.4.5})

    La funzione MATR.TRASPOSTA trasforma la costante di matrice verticale in una riga.

  • Trasporre una costante bidimensionale

    Immettere =MATRASPOSTA(SEQUENZA(3,4))o =MATRASPOSTA({1\2\3\4.5\6\7\8.9\10\11\12})

    La funzione MATR.TRASPOSTA trasforma ogni riga in una serie di colonne.

In questa sezione sono forniti esempi di formule di matrice di base.

  • Creare una matrice da valori esistenti

    L'esempio seguente illustra come usare le formule di matrice per creare una nuova matrice da una matrice esistente.

    Inserire =SEQUENZA(3,6,10,10), o ={10\20\30\40\50\60.70\80\90\100\110\120.130\140\150\160\170\180}

    Assicurarsi di digitare { (parentesi graffa di apertura) prima del 10 e } (parentesi graffa di chiusura) dopo il 180, dal momento che si sta creando una matrice di numeri.

    Quindi, immettere =D9#o =D9:I11 in una cella vuota. Viene visualizzata una matrice di 3 x 6 celle con gli stessi valori visualizzati in D9:D11. Il segno # è chiamato Operatore di intervallo estesoed è il modo con cui Excel fa riferimento all'intero intervallo di matrice invece di doverlo digitare.

    Usare l'operatore di intervallo espanso (#) per fare riferimento a una matrice esistente

  • Creare una costante di matrice da valori esistenti

    È possibile calcolare i risultati di una formula di matrice estesa e convertirli in componenti. Selezionare la cella D9 e premere F2 per passare alla modalità di modifica. Premere quindi F9 per convertire i riferimenti di cella in valori. che poi Excel converte in una costante di matrice. Quando si preme INVIO, la formula =D9#dovrebbe ora essere ={10\20\30.40\50\60.70\80\90}.

  • Contare i caratteri in un intervallo di celle

    L'esempio seguente illustra come contare il numero di caratteri in un intervallo di celle. Questo include spazi.

    Contare il numero totale di caratteri in un intervallo e altre matrici per lavorare con le stringhe di testo

    =SOMMA(LUNGHEZZA(C9:C13))

    In questo caso, la funzione LUNGHEZZA restituisce la lunghezza di ogni stringa di testo in ogni cella dell'intervallo. Successivamente la funzione SOMMA calcola la somma di questi valori e visualizza il risultato, ovvero 66. Per ottenere il numero medio di caratteri, è possibile usare:

    =MEDIA(LUNGHEZZA(C9:C13))

  • Contenuto della cella più lunga nell’intervallo C9:C13

    =INDICE(C9:C13,CORRISPONDENZA(MAX(LUNGHEZZA(C9:C13)),LUNGHEZZA(C9:C13),0),1)

    Questa formula funziona solo quando un intervallo di dati contiene un'unica colonna di celle.

    Si osservi più attentamente la formula partendo dagli elementi più interni e procedendo verso l'esterno. La funzione LUNGHEZZA restituisce la lunghezza di ogni elemento nell'intervallo di celle D2:D6. La funzione MAX calcola il valore più grande tra questi elementi, che corrisponde alla stringa di testo più lunga, che si trova nella cella D3.

    A questo punto la situazione si complica leggermente. La funzione CONFRONTA calcola la distanza (posizione relativa) della cella contenente la stringa di testo più lunga. Per fare questo, necessita di tre argomenti: un valore di ricerca, una matrice di ricerca e un tipo di corrispondenza. La funzione CONFRONTA cerca nella matrice di ricerca il valore di ricerca specificato. In questo caso, il valore di ricerca è la stringa di testo più lunga:

    MAX(LUMGHEZZA(C9:C13)

    e la stringa risiede nella matrice:

    LUNGHEZZA(C9:C13)

    L'argomento tipo di corrispondenza in questo caso è 0. Il tipo di corrispondenza può essere un valore 1, 0 oppure -1.

    • 1 - restituisce il valore più grande che sia minore o uguale al valore di ricerca.

    • 0 - restituisce il primo valore esattamente uguale al valore di ricerca.

    • 1 - individua il valore più piccolo che sia maggiore o uguale al valore di ricerca specificato.

    • Se il tipo di corrispondenza non viene specificato, per impostazione predefinita viene utilizzato 1.

    Infine, gli argomenti della funzione INDICE possono essere una matrice e un numero di riga e di colonna all'interno di tale matrice. L'intervallo di celle C9:C13 fornisce la matrice, la funzione CONFRONTA fornisce l'indirizzo di cella e l'argomento finale (1) specifica che il valore proviene dalla prima colonna della matrice.

    Se si vuole ottenere il contenuto della stringa di testo più piccola, sostituire MAX nell'esempio precedente con MIN.

  • Trovare gli n valori più piccoli in un intervallo

    Questo esempio mostra come trovare i tre valori più piccoli in un intervallo di celle, dove è stata creata una matrice di dati di esempio nelle celle B9:B18 con: =INT(CASUALE(10,1)*100). Si noti che MATR.CASUALE è una funzione volatile, quindi si ottiene un nuovo set di numeri casuali ogni volta che Excel calcola.

    Formula di matrice di Excel per trovare il valore specifico più piccolo: =PICCOLO(B9#,SEQUENZA(D9))

    Immettere =PICCOLO(B9#;SEQUENZA(D9); =PICCOLO(B9:B18;{1\2\3})

    Questa formula usa una costante di matrice per valutare la funzione PICCOLO tre volte e restituire i tre membri più piccoli nella matrice contenuta nelle celle B9:B18, dove 3 è un valore variabile nella cella D9. Per trovare più valori, è possibile aumentare il valore nella funzione SEQUENZA o aggiungere altri argomenti alla costante. Con questa formula è anche possibile usare funzioni aggiuntive, ad esempio la funzione SOMMA o MEDIA. Ad esempio:

    =SOMMA(PICCOLO(B9#;SEQUENZA(D9))

    =MEDIA(PICCOLO(B9#;SEQUENZA(D9))

  • Trovare gli n valori più grandi in un intervallo

    Per trovare i valori più grandi in un intervallo, è possibile sostituire la funzione PICCOLO con la funzione GRANDE. Nell'esempio seguente sono utilizzate anche le funzioni RIGA e INDIRETTO.

    Immettere =GRANDE(B9#,RIF.RIGA(INDIRETTO("1:3")))o =GRANDE(B9:B18;RIGA(INDIRETTO("1:3")))

    A questo punto può essere utile fornire alcune indicazioni sulle funzioni RIF.RIGA e INDIRETTO. La funzione RIF.RIGA può essere utilizzata per creare una matrice di numeri interi consecutivi. Ad esempio, selezionare un campo vuoto e immettere:

    =RIF.RIGA(1:10)

    La formula crea una colonna di 10 numeri interi consecutivi. Per osservare un possibile problema, inserire una riga sopra l'intervallo contenente la formula di matrice, in questo caso sopra la riga 1. I riferimenti di riga vengono adeguati automaticamente e la formula genera ora numeri interi da 2 a 11. Per risolvere questo problema, aggiungere la funzione INDIRETTO alla formula:

    =RIF.RIGA(INDIRETTO("1:10"))

    La funzione INDIRETTO utilizza stringhe di testo come argomenti e per questo motivo l'intervallo 1:10 è racchiuso tra virgolette. I valori di testo non vengono adeguati automaticamente in caso di inserimento di righe o di spostamento della formula di matrice. Di conseguenza, la funzione RIF.RIGA genera sempre la matrice di numeri interi desiderata. È possibile usare SEQUENZA in modo semplice:

    =SEQUENZA(10)

    Esaminiamo la formula usata in precedenza, =GRANDE(B9#,RIGA(INDIRETTO("1:3"))) a partire dalle parentesi interne e iniziando verso l'esterno: La funzione INDIRETTO restituisce un set di valori di testo, in questo caso i valori da 1 a 3. La funzione RIGA genera a sua volta una matrice di colonne a tre celle. La funzione GRANDE usa i valori nell'intervallo di celle B9:B18 e viene valutata tre volte, una volta per ogni riferimento restituito dalla funzione RIGA. Per trovare più valori, aggiungere un maggior intervallo di celle alla funzione INDIRETTO. Infine, come per gli esempi PICCOLO, è possibile utilizzare questa formula con altre funzioni, ad esempio SOMMA e MEDIA.

  • Sommare un intervallo che contiene valori di errore

    La funzione SOMMA in Excel non funziona quando si tenta di sommare un intervallo contenente un valore di errore, ad esempio #VALORE! o #N/D. Questo esempio illustra come sommare i valori in un intervallo denominato Dati nel quale sono presenti errori:

    Usare le matrici per gestire gli errori. Ad esempio,=SOMMA(SE(VAL.ERRORE(Dati),"",Dati) sommerà l'intervallo denominato Dati anche se include errori come #VALORE! oppure #N/D.

  • =SOMMA(SE(VAL.ERRORE(Dati),"",Dati))

    La formula crea una nuova matrice contenente i valori originali esclusi gli eventuali valori di errore. A partire dalle funzioni più interne e procedendo verso l'esterno, la funzione VAL.ERRORE ricerca gli errori nell'intervallo di celle (Dati). La funzione SE restituisce un determinato valore se una condizione specificata restituisce VERO e un altro valore se tale condizione restituisce FALSO. In questo caso, restituisce stringhe vuote ("") per tutti i valori di errore che restituiscono VERO e restituisce i valori restanti dell'intervallo (Dati) perché restituiscono FALSO, in quanto non contengono valori di errore. La funzione SOMMA calcola quindi il totale per la matrice filtrata.

  • Contare il numero di valori di errore in un intervallo

    Questo esempio è analogo a quello relativo alla formula precedente, ma restituisce il numero di valori di errore in un intervallo denominato Dati anziché escluderli mediante un filtro:

    =SOMMA(SE(VAL.ERRORE(Dati),1,0))

    Questa formula crea una matrice contenente il valore 1 per le celle che contengono errori e il valore 0 per le celle che non ne contengono. È possibile semplificare la formula ottenendo lo stesso risultato rimuovendo il terzo argomento della funzione SE, ad esempio:

    =SOMMA(SE(VAL.ERRORE(Dati),1))

    Se non si specifica l'argomento, la funzione SE restituisce FALSO se una cella non contiene un valore di errore. È possibile semplificare ulteriormente la formula:

    =SOMMA(SE(VAL.ERRORE(Dati)*1))

    Questa versione funziona perché VERO*1=1 e FALSO*1=0.

In alcuni casi potrebbe essere necessario sommare valori in base a condizioni.

È possibile usare le matrici per eseguire calcoli in base a determinate condizioni. =SOMMA(SE(Vendite>0;Vendite)) sommerà tutti i valori maggiori di 0 in un intervallo denominato Vendite.

Questa formula di matrice, ad esempio, somma solo i numeri interi positivi in un intervallo denominato Vendite, che rappresenta le celle E9:E24 nell'esempio precedente:

=SOMMA(SE(Vendite>0,Vendite))

La funzione SE crea una matrice di valori positivi e falsi. La funzione SOMMA ignora i valori falsi perché 0+0=0. L'intervallo di celle utilizzato in questa formula può essere costituito da un numero indefinito di righe e celle.

È anche possibile sommare i valori che soddisfano più condizioni. Questa formula di matrice, ad esempio, calcola i valori maggiori di 0 E minori di 2500:

=SOMMA((Vendite>0)*(Vendite<=2500)*(Vendite))

Tenere presente che questa formula restituisce un errore se l'intervallo contiene una o più celle non numeriche.

È anche possibile creare formule di matrice che utilizzano una condizione di tipo OR. Ad esempio, è possibile sommare i valori maggiori di 0 O minori di 2500:

=SOMMA(SE((Vendite<0)+(Vendite>2500),Vendite))

Non è possibile utilizzare le funzioni E e O nelle formule di matrice perché restituiscono un unico risultato, VERO o FALSO, mentre le funzioni di matrice richiedono matrici di risultati. Per risolvere questo problema, è possibile utilizzare la logica descritta nella formula precedente, ovvero eseguire operazioni matematiche quali l'addizione o la moltiplicazione, su valori che soddisfano la condizione O o E.

Questo esempio illustra come rimuovere gli zeri da un intervallo quando è necessario calcolare la media dei valori nell'intervallo. Nella formula viene utilizzato un intervallo di valori denominato Vendite:

=MEDIA(SE(Vendite<>0,Vendite))

La funzione SE crea una matrice di valori che non corrispondono a 0, quindi passa tali valori alla funzione MEDIA.

Questa formula di matrice confronta i valori in due intervalli di celle denominati DatiA e DatiB e restituisce il numero di differenze riscontrate. Se il contenuto dei due intervalli è identico, la formula restituisce 0. Per utilizzare questa formula, gli intervalli di celle devono avere le stesse dimensioni. Ad esempio, se DatiA è un intervallo di 3 righe per 5 colonne, anche DatiB deve essere di 3 righe per 5 colonne:

=SOMMA(SE(DatiA=DatiB,0,1))

La formula crea una nuova matrice delle stesse dimensioni degli intervalli confrontati. La funzione SE riempie la matrice con il valore 0 e il valore 1 (0 per le mancate corrispondenze e 1 per le celle identiche), quindi la funzione SOMMA restituisce la somma dei valori nella matrice.

È possibile semplificare la formula:

=SOMMA(1*(DatiA<>DatiB))

Come la formula per il conteggio dei valori di errore in un intervallo, questa formula funziona perché VERO*1=1 e FALSO*1=0.

Questa formula di matrice restituisce il numero di riga del valore massimo in un intervallo a colonna singola denominato Dati:

=MIN(SE(Dati=MAX(Dati),RIF.RIGA(Dati),""))

La funzione SE crea una nuova matrice corrispondente all'intervallo denominato Dati. Se una cella corrispondente contiene il valore massimo dell'intervallo, la matrice conterrà il numero di riga. In caso contrario, la matrice conterrà una stringa vuota (""). La funzione MIN utilizza la nuova matrice come secondo argomento e restituisce il valore più piccolo, che corrisponde al numero di riga del valore massimo in Dati. Se l'intervallo denominato Dati contiene valori massimi identici, la formula restituirà la riga del primo di tali valori.

Se si desidera ottenere l'indirizzo di cella effettivo di un valore massimo, utilizzare la formula seguente:

=INDIRIZZO(MIN(SE(Dati=MAX(DatI),RIF.RIGA(Dati),"")),RIF.COLONNA(Dati))

Esempi simili nella cartella di lavoro di esempio sono disponibili nella cartella di lavoro Differenze tra set di dati .

Riconoscimento

Parti di quest’articolo sono tratte da una serie di articoli per utenti esperti di Excel scritti da Colin Wilcox e adattati dai capitoli 14 e 15 del libro Excel 2002 Formulas scritto da John Walkenbach, ex MVP di Excel.

Servono altre informazioni?

È sempre possibile rivolgersi a un esperto della Tech Community di Excel o ottenere supporto nelle Community.

Vedere anche

Matrici dinamiche e il comportamento di matrice espansa

Confronto tra formule di matrice CSE dinamiche e formule di matrice CSE legacy

Funzione FILTRO

Funzione MATR.CASUALE

Funzione SEQUENZA

Funzione DATI.ORDINA

Funzione DATI.ORDINA.PER

Funzione UNICI

Errori #ESPANSIONE! in Excel

Operatore di intersezione implicita: @

Panoramica delle formule

Facebook LinkedIn Posta elettronica

Serve aiuto?

Vuoi altre opzioni?

Esplorare i vantaggi dell'abbonamento e i corsi di formazione, scoprire come proteggere il dispositivo e molto altro ancora.

Vantaggi dell'abbonamento a Microsoft 365

Formazione su Microsoft 365

Microsoft Security

Centro accessibilità