Formule in forma di matrice: indicazioni ed esempi

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)

1. 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.

2. 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).

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

1. In un foglio di lavoro vuoto selezionare le celle da A1 a E1.

2. 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

1. Selezionare una colonna di cinque celle nella cartella di lavoro.

2. 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

1. Nella cartella di lavoro selezionare un blocco di celle da quattro colonne in larghezza per tre righe in altezza.

2. 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.

1. Nella cartella di lavoro di esempio creare un nuovo foglio di lavoro.

2. Nella cella A1 digitare 3, quindi digitare 4 in B1, 5 in C1, 6 in D1 e 7 in E1.

3. 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:

1. Nel gruppo Nomi definiti della scheda Formule fare clic su Definisci nome.
   Verrà visualizzata la finestra di dialogoDefinisci nome.

2. Nella casella Nome digitare Trimestre1.

3. 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:

   

4. Fare clic su OK e selezionare una riga di tre celle vuote.

5. 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

1. Creare un nuovo foglio di lavoro e selezionare un blocco di celle vuote da quattro colonne di larghezza per tre righe di altezza.

2. 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

1. Selezionare un blocco di celle vuote da quattro colonne di larghezza per tre righe di altezza.

2. 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

1. Selezionare una colonna di cinque celle vuote.

2. 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

1. Selezionare una riga di cinque celle vuote.

2. 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

1. Selezionare un blocco di celle da tre colonne di larghezza per quattro righe di altezza.

2. 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

1. 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.

2. 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

3. Selezionare l'intervallo di celle da C1 a E3.

4. 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

1. Con le celle C1:C3 selezionate, premere F2 per passare alla modalità di modifica. 

2. 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}.

3. 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.

1. 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)

2. Selezionare la cella A8 e premere CTRL+MAIUSC+INVIO per visualizzare il numero totale di caratteri nelle celle A2:A6 (66).

3. 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.

1. Immettere alcuni numeri casuali nelle celle A1:A11.

2. Selezionare le celle da C1 a C3. Questo insieme di celle conterrà i risultati restituiti dalla formula di matrice.

3. 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.

1. Selezionare le celle da D1 a D3.

2. 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))