Formule in forma di matrice: indicazioni ed esempi

Formule in forma di matrice: indicazioni ed esempi

Per diventare esperti di Excel è necessario imparare a usare le formule di matrice, che consentono di eseguire calcoli impossibili da eseguire con formule di altro tipo. Questo articolo è tratto 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, un MVP di Excel.

Informazioni sulle formule di matrice

Per le formule di matrice, anziché premere INVIO è necessario premere CTRL+MAIUSC+INVIO per completare la formula.

Utilità delle formule di matrice

Le formule di Excel consentono di eseguire alcune operazioni piuttosto sofisticate, ad esempio calcolare il costo totale di un mutuo su un determinato numero di anni. È possibile usare le formule di matrice per eseguire attività complesse, ad esempio:

  • 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 nesimi valori in un intervallo di valori.

Breve introduzione alle matrici e alle formule di matrice

Una formula di matrice consente di eseguire più calcoli su uno o più elementi in una matrice. Si può considerare una matrice come una riga o una colonna di valori oppure una combinazione di righe e colonne di valori. Le formule di matrice possono inoltre restituire uno o più risultati. È possibile ad esempio creare una formula di matrice in un intervallo di celle per calcolare una colonna o una riga di subtotali oppure inserirla in un'unica cella e calcolare un singolo importo. Una formula di matrice che include più celle è denominata formula a celle multiple, mentre una formula di matrice in un'unica cella è denominata formula a cella singola.

Gli esempi riportati nella sezione seguente illustrano come creare formule di matrice a celle multiple e a cella singola.

Prova

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

Si apre una cartella di lavoro incorporata nel browser. Benché contenga dati di esempio, è necessario sapere che non è possibile creare o modificare formule di matrice in una cartella di lavoro incorporata (serve il programma Excel). Nella cartella di lavoro incorporata è possibile visualizzare le risposte e parte del testo che illustra il funzionamento della formula di matrice, ma per valutare positivamente una qualunque di tali formule, sarà necessario visualizzare la cartella di lavoro in Excel.

Creare una formula matrice a celle multiple

  1. Copiare l'intera tabella riportata di seguito e incollarla nella cella A1 in un foglio di lavoro vuoto di Excel.

    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)

  2. Per visualizzare le vendite totali di Coupé e Berline per ogni venditore, selezionare E2:E11, immettere la formula =C2:C11*D2:D11, quindi premere CTRL+MAIUSC+INVIO.

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

È possibile scaricare la cartella di lavoro facendo clic sul pulsante verde Excel nella barra nera nella parte inferiore della cartella. Quindi, aprire il file in Excel, selezionare le celle contenenti le formule di matrice e premere CTRL+MAIUSC+INVIO per fare funzionare la formula.

Se si lavora in Excel, assicurarsi che Foglio1 sia attivo, quindi selezionare le celle E2:E11. Nella cella corrente E2 premere F2 e digitare la formula =C2:C11*D2:D11. Se si preme INVIO, la formula verrà immessa solo nella cella E2 e sarà visualizzato 165000. Dopo avere digitato la formula, premere CTRL+MAIUSC+INVIO anziché solo INVIO. I risultati saranno ora visualizzati nelle celle E2:E11. Nella barra della formula la formula verrà visualizzata come {=C2:C11*D2:D11}, a indicare che si tratta di una formula di matrice, come mostrato nella tabella seguente.

Quando si preme CTRL+MAIUSC+INVIO, la formula viene racchiusa tra parentesi graffe ({ }) e in ogni cella dell'intervallo selezionato viene inserita un'istanza della formula. 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}.

Il totale nella colonna E è calcolato da una formula in forma di matrice

Creare una formula di matrice a cella singola

Nella cella F10 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 utilizza la funzione SOMMA per sommare i totali. Il risultato è un totale complessivo di 1.590.000 euro di vendite. Questo esempio dimostra quanto siano potenti le formule di questo tipo. Se ad esempio si dispone di 1.000 righe di dati, è possibile sommare tutti questi dati o una parte di essi creando una formula di matrice in un'unica cella, anziché trascinare la formula verso il basso nelle 1.000 righe .

Si noti, inoltre, che la formula a cella singola nella cella G11 è 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 G11.

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.

  • Protezione    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. Quale misura di protezione aggiuntiva, è necessario premere CTRL+MAIUSC+INVIO per confermare la modifica della 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.

Sintassi delle formule di matrice

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. La differenza fondamentale consiste nel fatto che per immettere una formula di matrice si preme CTRL+MAIUSC+INVIO. Quando si esegue questa operazione, la formula di matrice viene racchiusa tra parentesi graffe. Se invece le parentesi graffe vengono immesse manualmente, la formula verrà convertita in una stringa di testo e non funzionerà.

Le funzioni di matrice sono un modo molto efficace di costruire una formula complessa. 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).

Immettere e modificare formule di matrice

Importante    Premere CTRL+MAIUSC+INVIO quando è necessario immettere o modificare 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'intera formula, ad esempio =C2:C11*D2:D11, premere Elimina, quindi premere CTRL+MAIUSC+INVIO.

  • Non è possibile inserire celle vuote o eliminare celle da una formula di matrice a celle multiple.

Espansione di una formula di matrice

Se necessario, è possibile espandere una formula di matrice. Si tratta di una procedura semplice: assicurarsi di seguire le indicazioni precedenti.

In questo foglio di lavoro sono state aggiunte alcune righe relative alle vendite (da 12 a 17). Qui è necessario aggiornare le formule di matrice in modo che includano tali righe aggiuntive.

Assicurarsi di eseguire l'operazione nel programma desktop di Excel (dopo aver scaricato la cartella di lavoro nel computer).

Espandere una formula di matrice

  1. Copiare l'intera tabella nella cella A1 all'interno di un foglio di lavoro di Excel.

    Venditore

    Tipo
    auto

    Quantità
    vendute

    Prezzo
    unitario

    Vendite
    totali

    Udinesi

    Berlina

    5

    33000

    165000

    Coupé

    4

    37000

    148000

    Pinto

    Berlina

    6

    24000

    144000

    Coupé

    8

    21000

    168000

    Milano

    Berlina

    3

    29000

    87000

    Coupé

    1

    31000

    31000

    Scotti

    Berlina

    9

    24000

    216000

    Coupé

    5

    37000

    185000

    Romani

    Berlina

    6

    33000

    198000

    Coupé

    8

    31000

    248000

    Longo

    Berlina

    2

    27000

    Coupé

    3

    30000

    Dellucci

    Berlina

    4

    22000

    Coupé

    1

    41000

    Fanucci

    Berlina

    5

    32000

    Coupé

    3

    36000

    Totale complessivo

  2. Selezionare la cella E18, immettere la formula del totale complessivo =SOMMA(C2:C17*D2:D17) nella cella A20, quindi premere CTRL+MAIUSC+INVIO.
    Il risultato dovrebbe essere 2.131.000.

  3. Selezionare l'intervallo di celle contenente la formula di matrice corrente (E2:E11), nonché le celle vuote (E12:E17) accanto ai nuovi dati. In sostanza, selezionare l'intervallo di celle E2:E17.

  4. Premere F2 per passare alla modalità di modifica.

  5. Sulla barra della formula sostituire C11 con C17, sostituire D11 con D17, quindi premere CTRL+MAIUSC+INVIO.
    La formula nelle celle da E2 a E11 viene aggiornata e viene inserita un'istanza della formula nelle nuove celle, da E12 a E17.

  6. Digitare la formula di matrice = SOMMA(C2:C17*D2*D17) nella cella F17, in modo che faccia riferimento a celle delle righe da 2 a 17, quindi premere CTRL+MAIUSC+INVIO per immettere la formula di matrice.
    Il nuovo totale complessivo dovrebbe essere 2.131.000.

Svantaggi dell'uso delle formule di matrice

Le formule di matrice sono fantastiche, ma hanno alcuni svantaggi:

  • Può capitare anche agli utenti più esperti di dimenticare di premere CTRL+MAIUSC+INVIO. È essenziale premere questa combinazione di tasti per immettere o modificare una formula di matrice.

  • Le formule usate potrebbero risultare incomprensibili per altre persone che usano la cartella di lavoro. In pratica, le formule di matrice non sono generalmente 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.

Inizio pagina

Informazioni sulle costanti di matrice

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.

Questa è una matrice in un'unica riga: {1,2,3,4}; questa è una matrice in un'unica colonna: {1;2;3;4}. E questa è una matrice di due righe e quattro colonne: {1,2,3,4;5,6,7,8}. Nella matrice a due righe, la prima riga è 1, 2, 3, e 4, mentre la seconda riga è 5, 6, 7 e 8. Un unico 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.

Inizio pagina

Creare costanti unidimensionali e bidimensionali

Le procedure descritte di seguito illustrano il processo di creazione di costanti orizzontali, verticali e bidimensionali.

Creare una costante orizzontale

  1. Utilizzare la stessa cartella di lavoro degli esempi precedenti oppure creare una nuova cartella di lavoro.

  2. Selezionare le celle da A1 a E1.

  3. Immettere la formula seguente nella barra della formula, quindi premere CTRL+MAIUSC+INVIO:

    ={1,2,3,4,5}

    In questo caso è necessario digitare le parentesi graffe di apertura e chiusura ({ }).

    Verrà visualizzato il risultato riportato di seguito.

    Costante di matrice orizzontale nella formula

Creare una costante verticale

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

  2. Nella barra della formula immettere la formula seguente, quindi premere CTRL+MAIUSC+INVIO:

    ={1;2;3;4;5}

    Verrà visualizzato il risultato riportato di seguito.

    costante di matrice verticale nella formula in forma di matrice

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.

    Costante di matrice bidimensionale nella formula in forma di matrice

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.

    Formula in forma di matrice con costante di matrice

    Nella cella A3 viene visualizzato il valore 85.

Il funzionamento della formula è illustrato nella prossima sezione.

Sintassi delle costanti di matrice

La formula appena utilizzata contiene diverse parti.

Sintassi della formula in forma di matrice con costante di matrice

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})

Elementi utilizzabili nelle costanti

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, ovvero possono contenere solo testo o numeri separati da virgole o punti e virgola. Quando si immette una formula come {1,2,A1:D4} o {1,2,SOMMA(Q2:Z8)}, viene visualizzato un messaggio di avviso. Inoltre, i valori numerici non possono contenere segni di percentuale, segni di dollaro, virgole o parentesi.

Denominazione delle costanti di matrice

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 dialogo Definisci 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:

    finestra di dialogo modifica nome con formula

  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.

    matrice denominata immessa come formula

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.

Risoluzione dei problemi relativi alle costanti di matrice

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.

Utilizzo delle costanti di matrice

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 premere CTRL+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.

Inizio pagina

Applicazione pratica di formule di matrice di base

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.
    La formula di matrice dovrebbe ancora essere = C8:E10.

  2. Premere F9 per convertire i riferimenti di cella in valori. I valori verranno convertiti automaticamente in una costante di matrice. La formula dovrebbe ora essere ={10,20,30;40,50,60;70,80,90}, proprio come per le celle C8:E10.

  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.

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

  3. Selezionare la cella A12 e premere CTRL+MAIUSC+INVIO per visualizzare il contenuto della più lunga delle celle A2:A6 (cella A3).

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)

La seguente formula è utilizzata nella cella A9 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 (66) nella cella contenente la formula, ovvero A9.

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. Selezionare le celle da A16 a A18.
    Questo insieme di celle conterrà i risultati restituiti dalla formula di matrice.

  2. Nella barra della formula immettere la formula seguente, quindi premere CTRL+MAIUSC+INVIO:

    =PICCOLO(A5:A14,{1;2;3})

Nelle celle da A16 a A18 verranno visualizzati rispettivamente i valori 400, 475 e 500.

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 e un numero equivalente di celle per i risultati all'intervallo A12:A14. È inoltre possibile utilizzare altre funzioni con questa formula, quali SOMMA o MEDIA, come nell'esempio seguente:

=SOMMA(PICCOLO(A 5 :A1 4 ,{1;2;3}))

=MEDIA(PICCOLO(A 5 :A1 4 ,{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 A1 a A3.

  2. Nella barra della formula immettere questa formula, quindi premere CTRL+MAIUSC+INVIO:

    =GRANDE(A5:A14,RIF.RIGA(INDIRETTO("1:3")))

Nelle celle da A1 a A3 verranno visualizzati rispettivamente i valori 3200, 2700 e 2000.

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 utilizzata per l'esercitazione, immettere questa formula di matrice nelle celle A5:A14, quindi 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.

A questo punto è opportuno esaminare la formula utilizzata in precedenza, ovvero GRANDE(A5:A14,RIF.RIGA(INDIRETTO("1:3"))), iniziando dalle parentesi interne e procedendo verso l'esterno. La funzione INDIRETTO restituisce un insieme di valori di testo, in questo caso i valori da 1 a 3. La funzione RIF.RIGA genera a sua volta una matrice a colonna di tre celle. La funzione GRANDE usa i valori nell'intervallo di celle A5:A14 e viene valutata tre volte, una per ogni riferimento restituito dalla funzione RIF.RIGA. Nella matrice a colonna di tre celle vengono restituiti i valori 3200, 2700 e 2000. Se si desidera individuare altri valori, è possibile aggiungere un intervallo di celle più ampio alla funzione INDIRETTO.

Infine, è possibile utilizzare questa formula con altre funzioni, ad esempio SOMMA e MEDIA.

Trovare la stringa di testo più lunga in un intervallo di celle

Questa formula funziona solo quando un intervallo di dati contiene un'unica colonna di celle. In Foglio3 immettere la formula seguente nella cella A16 e premere CTRL+MAIUSC+INVIO:

=INDICE(A6:A9,CONFRONTA(MAX(LUNGHEZZA(A6:A9)),LUNGHEZZA(A6:A9),0),1)

Nella cella A16 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 funzione LUNGHEZZA restituisce la lunghezza di ogni elemento nell'intervallo di celle A6:A9. La funzione MAX calcola il valore più grande tra tali elementi, che corrisponde alla stringa di testo più lunga, che si trova nella cella A7.

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( A6 : A9 ))

e la stringa risiede nella matrice:

LUNGHEZZA( A6:A9 )

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 A6:A9 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.

Inizio pagina

Applicazione pratica di formule di matrice avanzate

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 OR o AND.

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 della stessa dimensione 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))

Inizio pagina

Servono altre informazioni?

È sempre possibile rivolgersi a un esperto nella Tech Community di Excel, ottenere supporto nella community Microsoft o suggerire una nuova caratteristica o un miglioramento in Excel UserVoice.

Vedere anche

Panoramica delle formule

Amplia le tue competenze
Esplora i corsi di formazione
Ottieni in anticipo le nuove caratteristiche
Partecipa al programma Office Insider

Queste informazioni sono risultate utili?

Grazie per i tuoi commenti e suggerimenti

Grazie per il tuo feedback! Potrebbe essere utile metterti in contatto con uno dei nostri operatori del supporto di Office.

×