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
-
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
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 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 bidimensionale
Selezionare una cella vuota con spazio a destra e sotto e immettere =SEQUENZA(3;4). Verrà visualizzato il risultato riportato di seguito.
È 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.
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:
Fare clic suOK, quindi selezionare una riga qualsiasi con tre celle vuote e immettere =Trimestre1.
Verrà visualizzato il risultato riportato di seguito:
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")
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.
-
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.
=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.
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:
-
=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.
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 .
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
Copiare l'intera tabella riportata di seguito e incollarla nella cella A1 in un foglio di lavoro vuoto.
Venditore |
Tipo auto |
Quantità vendute |
Prezzo unitario |
Vendite totali |
---|---|---|---|---|
Udinesi |
Berlina |
5 |
33000 |
|
Coupé |
4 |
37000 |
||
Pinto |
Berlina |
6 |
24000 |
|
Coupé |
8 |
21000 |
||
Milano |
Berlina |
3 |
29000 |
|
Coupé |
1 |
31000 |
||
Scotti |
Berlina |
9 |
24000 |
|
Coupé |
5 |
37000 |
||
Romani |
Berlina |
6 |
33000 |
|
Coupé |
8 |
31000 |
||
Formula (Totale complessivo) |
Totale complessivo |
|||
'=SOMMA(C2:C11*D2:D11) |
=SOMMA(C2:C11*D2:D11) |
-
Per visualizzare le vendite totali di Coupé e Berline per ogni venditore, selezionare le celle E2:E11, immettere la formula =C2:C11*D2:D11, quindi premere CTRL+MAIUSC+INVIO.
-
Per visualizzare il totale complessivo di tutte le vendite, selezionare la cella F11, immettere la formula =SOMMA(C2:C11*D2:D11), quindi premere CTRL+MAIUSC+INVIO.
Quando si preme CTRL+MAIUSC+INVIO, Excel racchiude la formula tra parentesi graffe ({ }) e inserisce un'istanza della formula in ogni cella dell'intervallo selezionato. Tutto questo avviene molto rapidamente e nella colonna E viene visualizzato l'importo totale delle vendite per ogni tipo di auto per ogni venditore. Se si seleziona E2, quindi E3, E4 e così via, viene visualizzata la stessa formula: {=C2:C11*D2:D11}.
-
Creare una formula di matrice a cella singola
Nella cella D13 della cartella di lavoro digitare la formula riportata di seguito, quindi premere CTRL+MAIUSC+INVIO:
=SOMMA(C2:C11*D2:D11)
In questo caso, Excel moltiplica i valori nella matrice (l'intervallo di celle da C2 a D11), 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 D13 è del tutto indipendente dalla formula a celle multiple (la formula nelle celle da E2 a E11). Questo è un altro vantaggio dell'utilizzo delle formule di matrice, ovvero la flessibilità. È possibile modificare le formule nella colonna E o eliminare del tutto tale colonna, senza alcuna conseguenza sulla formula in D13.
Le formule di matrice offrono anche i seguenti vantaggi:
-
Coerenza Facendo clic su qualsiasi cella a partire dalla E2 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, provare a fare clic sulla cella E3 e premere Elimina. È necessario selezionare l'intero intervallo di celle (da E2 a E11) e modificare la formula per l'intera matrice oppure lasciare inalterata la matrice. Come misura di sicurezza aggiunta, è necessario premere CTRL+MAIUSC+INVIO per confermare qualsiasi modifica alla formula.
-
Dimensioni dei file ridotte In molti casi è possibile utilizzare un'unica formula di matrice anziché diverse formule intermedie. La cartella di lavoro, ad esempio, utilizza una formula di matrice per calcolare i risultati nella colonna E. Se fossero state utilizzate formule standard, quale =C2*D2, C3*D3, C4*D4…, sarebbero state necessarie 11 formule separate per calcolare gli stessi risultati.
In genere, le formule di matrice usano la sintassi standard delle formule, ovvero iniziano tutte con il segno di uguale (=) e consentono di usare la maggior parte delle funzioni predefinite di Excel. Iniziano tutte con il segno di uguale e accettano qualsiasi funzione predefinita di Excel. La differenza fondamentale consiste nel fatto che per immettere una formula di matrice si preme CTRL+MAIUSC+INVIO. Se invece le parentesi graffe vengono immesse manualmente, la formula verrà convertita in una stringa di testo e non funzionerà.
Le funzioni di matrice possono essere un modo efficiente per creare formule complesse. La formula di matrice=SOMMA(C2:C11*D2:D11) equivale a questa: =SOMMA(C2*D2,C3*D3,C4*D4,C5*D5,C6*D6,C7*D7,C8*D8,C9*D9,C10*D10,C11*D11).
Importante: Premere CTRL+MAIUSC+INVIOr quando è necessario immettere una formula di matrice. Questo vale sia per le formule a cella singola che per quelle a celle multiple.
Quando si opera su formule a celle multiple, è necessario ricordare quanto segue:
-
Selezionare l'intervallo di celle che dovrà contenere i risultati prima di immettere la formula. Questa operazione è stata effettuata durante la creazione della formula di matrice a celle multiple, quando sono state selezionate le celle da E2 a E11.
-
Non è possibile modificare il contenuto di una singola cella in una formula di matrice. Per una dimostrazione, provare a selezionare la cella E3 nella cartella di lavoro e premere Elimina. Viene visualizzato un messaggio che avvisa che non è possibile modificare parte di una matrice.
-
È possibile spostare o eliminare un'intera formula di matrice, ma non una parte di essa. In altri termini, per ridurre una formula di matrice è necessario eliminare la formula esistente e ricrearla.
-
Per eliminare una formula di matrice, selezionare l'intero intervallo di formule, ad esempio E2:E11, quindi premere CANC.
-
Non è possibile inserire celle vuote o eliminare celle da una formula di matrice a celle multiple.
Se necessario, è possibile espandere una formula di matrice. Selezionare la prima cella dell'intervallo di matrice esistente e continuare fino a selezionare l'intero intervallo a cui si vuole estendere la formula. Premere F2 per modificare la formula, quindi premere CTRL+MAIUSC+INVIO per confermare la formula dopo aver modificato l'intervallo di formule. La chiave è selezionare l'intero intervallo, a partire dalla cella superiore sinistra nella matrice. La cella in alto a sinistra è quella che viene modificata.
Le formule di matrice sono fantastiche, ma hanno alcuni svantaggi:
-
Può capitare di dimenticare di premere CTRL+MAIUSC+INVIO. Può capitare anche agli utenti più esperti di Excel. È essenziale premere questa combinazione di tasti per immettere o modificare una formula di matrice.
-
Le formule potrebbero non essere comprensibili per gli altri utenti della cartella di lavoro. In pratica, le formule di matrice in genere non sono spiegate in un foglio di lavoro. Quindi se è prevista la modifica delle cartelle di lavoro da parte di altri utenti, è consigliabile evitare di usare le formule di matrice o assicurarsi che le altre persone le conoscano e sappiano eventualmente come modificarle.
-
A seconda della velocità di elaborazione e della memoria di cui dispone il computer, le formule di matrice di entità consistente possono determinare un rallentamento dei calcoli.
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}
Sappiamo già che occorre premere CTRL+MAIUSC+INVIO durante la creazione delle formule di matrice. Trattandosi di un componente delle formule di matrice, le costanti di matrice devono essere racchiuse manualmente tra parentesi graffe. Successivamente si userà CTRL+MAIUSC+INVIO per immettere la formula completa.
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 usando le virgole e delimitare ogni riga usando i punti e virgola.
Ecco una matrice in un'unica riga: {1\2\3\4}. Ecco una matrice in un'unica colonna: {1.2.3.4}. Ed ecco una matrice di due righe e quattro colonne: {1\2\3\4.5\6\7\8}. Nella matrice di due righe, la prima riga è 1, 2, 3 e 4 e la seconda riga è 5, 6, 7 e 8. Un singolo punto e virgola separa le due righe, tra 4 e 5.
Come per le formule di matrice, è possibile utilizzare le costanti di matrice con la maggior parte delle funzioni predefinite di Excel. Nelle sezioni seguenti viene illustrato come creare i diversi tipi di costante e come utilizzare queste costanti con le funzioni in Excel.
Le procedure descritte di seguito illustrano il processo di creazione di costanti orizzontali, verticali e bidimensionali.
Creare una costante orizzontale
-
In un foglio di lavoro vuoto selezionare le celle da A1 a E1.
-
Immettere la formula seguente nella barra della formula, quindi premere CTRL+MAIUSC+INVIO:
={1\2\3\4\5}
In questo caso, digitare le parentesi graffe di apertura e chiusura ({ }) ed Excel aggiunge automaticamente il secondo set.
Verrà visualizzato il risultato riportato di seguito.
Creare una costante verticale
-
Selezionare una colonna di cinque celle nella cartella di lavoro.
-
Immettere la formula seguente nella barra della formula, quindi premere CTRL+MAIUSC+INVIO:
={1.2.3.4.5}
Verrà visualizzato il risultato riportato di seguito.
Creare una costante bidimensionale
-
Nella cartella di lavoro selezionare un blocco di celle da quattro colonne in larghezza per tre righe in altezza.
-
Immettere la formula seguente nella barra della formula, quindi premere CTRL+MAIUSC+INVIO:
={1\2\3\4.5\6\7\8.9\10\11\12}
Verrà visualizzato il risultato riportato di seguito.
Utilizzare le costanti nelle formule
Di seguito è riportato un semplice esempio che utilizza costanti.
-
Nella cartella di lavoro di esempio creare un nuovo foglio di lavoro.
-
Nella cella A1 digitare 3, quindi digitare 4 in B1, 5 in C1, 6 in D1 e 7 in E1.
-
Nella cella A3 digitare la formula seguente, quindi premere CTRL+MAIUSC+INVIO:
=SOMMA(A1:E1*{1\2\3\4\5})
Si noti che la costante viene racchiusa da un'altra coppia di parentesi graffe poiché è stata immessa come formula di matrice.
Nella cella A3 viene visualizzato il valore 85.
Il funzionamento della formula è illustrato nella prossima sezione.
La formula appena utilizzata contiene diverse parti.
1. Funzione
2. Matrice archiviata
3. Operatore
4. Costante di matrice
L'ultimo elemento racchiuso tra parentesi è la costante di matrice: {1\2\3\4\5}. Tenere presente che le costanti di matrice non vengono racchiuse automaticamente tra parentesi graffe, ma vengono digitate. È inoltre importante ricordare che dopo avere aggiunto una costante a una formula di matrice è necessario premere CTRL+MAIUSC+INVIO per immettere la formula.
Poiché le operazioni vengono eseguite a partire dalle espressioni racchiuse tra parentesi, i due elementi considerati successivamente sono i valori memorizzati nella cartella di lavoro (A1:E1) e l'operatore. A questo punto, la formula moltiplica i valori nella matrice archiviata per i valori corrispondenti nella costante. Questa operazione corrisponde a:
=SOMMA(A1*1,B1*2,C1*3,D1*4,E1*5)
Infine, la funzione SOMMA calcola la somma dei valori e nella cella A3 viene visualizzato il valore 85.
Per evitare di utilizzare la matrice archiviata e mantenere l'operazione interamente in memoria, sostituire la matrice memorizzata con un'altra costante di matrice:
=SOMMA({3\4\5\6\7}*{1\2\3\4\5})
Per provare a eseguire questa operazione, copiare la funzione, selezionare una cella vuota nella cartella di lavoro, incollare la formula sulla barra della formula e premere CTRL+MAIUSC+INVIO. Il risultato sarà identico a quello ottenuto nell'esercizio precedente con la formula di matrice:
=SOMMA(A1:E1*{1\2\3\4\5})
Le costanti di matrice possono contenere numeri, testo, valori logici quali VERO e FALSO e valori di errore quali #N/D. I numeri possono essere interi, decimali o in formato scientifico. È necessario che il testo, se presente, sia racchiuso tra virgolette doppie (").
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:
-
Nel gruppo Nomi definiti della scheda Formule fare clic su Definisci nome.
Verrà visualizzata la finestra di dialogoDefinisci 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 ha ora questo aspetto:
-
Fare clic su OK e selezionare una riga di tre celle vuote.
-
Digitare la formula riportata di seguito, quindi premere CTRL+MAIUSC+INVIO.
=Trimestre1
Verrà visualizzato il risultato riportato di seguito.
Quando si utilizza una matrice denominata come formula di matrice, è importante immettere il segno di uguale. 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.
Se le costanti di matrice non funzionano, controllare se si sono verificati i seguenti problemi:
-
È possibile che alcuni elementi non siano separati dal carattere appropriato. Se si omette una virgola o un punto e virgola o se tale carattere viene inserito nel posto sbagliato, la costante di matrice potrebbe non essere creata correttamente e potrebbe essere visualizzato un messaggio di avviso.
-
È possibile che sia stato selezionato un intervallo di celle che non corrisponde al numero di elementi nella costante. Ad esempio, se si seleziona una colonna di sei celle da utilizzare con una costante di cinque celle, nella cella vuota viene visualizzato il valore di errore #N/D. Se invece si seleziona un numero di celle non sufficiente, i valori per cui non esiste una cella corrispondente vengono omessi.
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
-
Creare un nuovo foglio di lavoro e selezionare un blocco di celle vuote da quattro colonne di larghezza per tre righe di altezza.
-
Digitare la formula riportata di seguito, quindi premere CTRL+MAIUSC+INVIO.
={1\2\3\4.5\6\7\8.9\10\11\12}*2
Elevare al quadrato gli elementi in una matrice
-
Selezionare un blocco di celle vuote da quattro colonne di larghezza per tre righe di altezza.
-
Digitare la formula di matrice seguente, quindi premereCTRL+MAIUSC+INVIO.
={1\2\3\4.5\6\7\8.9\10\11\12}*{1\2\3\4.5\6\7\8.9\10\11\12}
In alternativa, immettere la seguente formula di matrice, nella quale è utilizzato l'accento circonflesso (^) come operatore:
={1\2\3\4.5\6\7\8.9\10\11\12}^2
Trasporre una riga unidimensionale
-
Selezionare una colonna di cinque celle vuote.
-
Digitare la formula riportata di seguito, quindi premere CTRL+MAIUSC+INVIO.
=MATR.TRASPOSTA({1\2\3\4\5})
La funzione MATR.TRASPOSTA trasforma la costante di matrice orizzontale in una colonna.
Trasporre una colonna unidimensionale
-
Selezionare una riga di cinque celle vuote.
-
Immettere la formula riportata di seguito, quindi premere CTRL+MAIUSC+INVIO:
=MATR.TRASPOSTA({1.2.3.4.5})
La funzione MATR.TRASPOSTA trasforma la costante di matrice verticale in una riga.
Trasporre una costante bidimensionale
-
Selezionare un blocco di celle da tre colonne di larghezza per quattro righe di altezza.
-
Immettere la costante riportata di seguito, quindi premere CTRL+MAIUSC+INVIO:
=MATR.TRASPOSTA({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 matrici e costanti di matrice a partire da valori esistenti
Nell'esempio seguente viene spiegato come utilizzare la formule di matrice per creare collegamenti tra intervalli di celle in fogli di lavoro diversi. Viene inoltre descritto come creare una costante di matrice con lo stesso insieme di valori.
Creare una matrice da valori esistenti
-
In un foglio di lavoro di Excel selezionare le celle C8:E10 e immettere questa formula:
={10\20\30.40\50\60.70\80\90}
Assicurarsi di digitare { (parentesi graffa di apertura) prima del 10 e } (parentesi graffa di chiusura) dopo il 90, dal momento che si sta creando una matrice di numeri.
-
Premere quindi CTRL+MAIUSC+INVIO per inserire questa matrice di numeri nell'intervallo di celle C8:E10 usando una formula di matrice. Nel foglio di lavoro le celle nell'intervallo da C8 a E10 dovrebbero essere simili alle seguenti:
10
20
30
40
50
60
70
80
90
-
Selezionare l'intervallo di celle da C1 a E3.
-
Immettere la formula riportata di seguito nella relativa barra, quindi premere CTRL+MAIUSC+INVIO:
=C8:E10
Verrà visualizzata una matrice 3x3 di celle nelle celle da C1 a E3 con gli stessi valori presenti nelle celle da C8 a E10.
Creare una costante di matrice da valori esistenti
-
Con le celle C1:C3 selezionate, premere F2 per passare alla modalità di modifica.
-
Premere F9 per convertire i riferimenti di cella in valori. I valori verranno convertiti automaticamente in una costante di matrice. La formula dovrebbe essere ={10\20\30.40\50\60.70\80\90}.
-
Premere CTRL+MAIUSC+INVIO per immettere la costante di matrice come formula di matrice.
Contare i caratteri in un intervallo di celle
L'esempio seguente illustra come contare il numero di caratteri, inclusi gli spazi, in un intervallo di celle.
-
Copiare questa intera tabella e incollarla nella cella A1 di un foglio di lavoro.
Dati
Questo è un
insieme di celle
messe insieme
per formare
una frase.
Totale caratteri in A2:A6
=SOMMA(LUNGHEZZA(A2:A6))
Contenuto della cella più lunga (A3)
=INDICE(A2:A6,CONFRONTA(MAX(LUNGHEZZA(A2:A6)),LUNGHEZZA(A2:A6),0),1)
-
Selezionare la cella A8 e premere CTRL+MAIUSC+INVIO per visualizzare il numero totale di caratteri nelle celle A2:A6 (66).
-
Selezionare la cella A10 e premere CTRL+MAIUSC+INVIO per visualizzare il contenuto della più lunga delle celle A2:A6 (cella A3).
La seguente formula è utilizzata nella cella A8 e conta il numero totale di caratteri (66) nelle celle da A2 a A6.
=SOMMA(LUNGHEZZA(A2:A6))
In questo caso, la funzione LUNGHEZZA restituisce la lunghezza di ogni stringa di testo in ogni cella dell'intervallo. e successivamente la funzione SOMMA calcola la somma di questi valori e visualizza il risultato, ovvero 66.
Trovare gli n valori più piccoli in un intervallo
In questo esempio viene illustrato come trovare i tre valori più piccoli in un intervallo di celle.
-
Immettere alcuni numeri casuali nelle celle A1:A11.
-
Selezionare le celle da C1 a C3. Questo insieme di celle conterrà i risultati restituiti dalla formula di matrice.
-
Immettere la formula riportata di seguito, quindi premere CTRL+MAIUSC+INVIO:
=PICCOLO(A1:A11,{1.2.3})
Questa formula utilizza una costante di matrice per valutare tre volte la funzione PICCOLO e restituire il primo (1), il secondo (2) e il terzo (3) membro più piccolo nella matrice presente nelle celle A1:A10. Per trovare altri valori, è possibile aggiungere altri argomenti alla costante. Con questa formula è anche possibile usare funzioni aggiuntive, ad esempio la funzione SOMMA o MEDIA. Ad esempio:
=SOMMA(PICCOLO(A1:A10,{1\2\3})
=MEDIA(PICCOLO(A1:A10,{1\2\3})
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 RIF.RIGA e INDIRETTO.
-
Selezionare le celle da D1 a D3.
-
Nella barra della formula immettere questa formula, quindi premere CTRL+MAIUSC+INVIO:
=GRANDE(A1:A10,RIF.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 una colonna vuota di 10 celle nella cartella di lavoro di esercitazione, immettere questa formula di matrice e premere CTRL+MAIUSC+INVIO:
=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 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 doppie. 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.
Esaminiamo la formula usata in precedenza, — =GRANDE(A5:A14,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 funzioneRIGA genera a sua volta una matrice di colonne a tre celle. La funzione GRANDE usa i valori nell'intervallo di celle A5:A14 e viene valutata tre volte, una volta per ogni riferimento restituito dalla funzione RIGA. I valori 3200, 2700 e 2000 vengono restituiti nella matrice a colonne di tre celle. Per trovare più valori, aggiungere un maggior intervallo di celle alla funzione INDIRETTO.
Come negli esempi precedenti, è possibile utilizzare questa formula con altre funzioni, ad esempio SOMMA e MEDIA.
Trovare la stringa di testo più lunga in un intervallo di celle
Tornare all'esempio di stringa di testo precedente, immettere la formula seguente in una cella vuota e premere CTRL+MAIUSC+INVIO:
=INDICE(A2:A6,CONFRONTA(MAX(LUNGHEZZA(A2:A6)),LUNGHEZZA(A2:A6),0),1)
viene visualizzato il testo "insieme di celle che".
Si osservi più attentamente la formula partendo dagli elementi più interni e procedendo verso l'esterno. La funzioneLUNGHEZZA restituisce la lunghezza di ogni elemento nell'intervallo di celle A2:A6. La funzione MAX calcola il valore più grande tra questi elementi, che corrisponde alla stringa di testo più lunga, che si trova nella cella A3.
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(LUNGHEZZA(A2:A6))
e la stringa risiede nella matrice:
LUNGHEZZA(A2:A6)
L'argomento del tipo di corrispondenza è 0. Il tipo di corrispondenza può essere costituito da un valore 1, 0 oppure -1. Se si specifica 1, la funzione CONFRONTA restituisce il valore più grande che sia minore o uguale al valore di ricerca. Se si specifica 0, la funzione CONFRONTA restituisce il primo valore esattamente uguale al valore di ricerca. Se si specifica -1, la funzione CONFRONTA 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 A2:A6 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.
In questa sezione sono forniti esempi di formule di matrice avanzate.
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 #N/D. Questo esempio illustra come sommare i valori in un intervallo denominato Dati nel quale sono presenti errori.
=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.
Sommare valori in base a condizioni
In alcuni casi potrebbe essere necessario sommare valori in base a condizioni. Questa formula di matrice, ad esempio, somma solo i numeri interi positivi in un intervallo denominato Vendite:
=SOMMA(SE(Vendite>0,Vendite))
La funzione SE crea una matrice di valori positivi e valori 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 o uguali a 5:
=SOMMA((Vendite>0)*(Vendite<=5)*(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 minori di 5 e maggiori di 15:
=SOMMA(SE((Vendite<5)+(Vendite>15),Vendite))
La funzione SE trova tutti i valori minori di 5 e maggiori di 15 e li passa alla funzione SOMMA.
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.
Calcolare una media che esclude gli zeri
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.
Contare il numero di differenze tra due intervalli di celle
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.
Individuare la posizione del valore massimo in un intervallo
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))
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