Formule in forma di matrice: indicazioni ed esempi

Formule in forma di matrice: indicazioni ed esempi

Nota: Riteniamo importante fornire il contenuto della Guida più recente non appena possibile, nella lingua dell'utente. Questa pagina è stata tradotta automaticamente e potrebbe contenere errori di grammatica o imprecisioni. L'intento è quello di rendere fruibile il contenuto. Nella parte inferiore della pagina è possibile indicare se le informazioni sono risultate utili. Questo è l'articolo in lingua inglese per riferimento.

Una formula in forma di matrice è una formula in grado di eseguire più calcoli in uno o più elementi di una matrice. Si può pensare a una matrice come a una riga o a una colonna di valori oppure a una combinazione di righe e colonne di valori. Le formule di matrice possono restituire più risultati o un singolo risultato.

A partire dall'aggiornamento di 2018 di settembre per Office 365, tutte le formule che possono restituire più risultati verranno automaticamente riversate in basso o in celle adiacenti. Questa modifica del comportamento è accompagnata anche da diverse nuove funzioni di matrice dinamica. Le formule di matrice dinamica, che usano funzioni esistenti o le funzioni di matrice dinamica, devono essere immesse solo in una singola cella, quindi confermate premendo invio. In precedenza, le formule di matrice legacy richiedono prima di tutto l'intervallo di output, quindi conferma la formula con CTRL + MAIUSC + INVIO. Si chiama comunemente formule CSE .

Puoi usare formule di matrice per eseguire attività complesse, ad esempio:

  • Creare rapidamente dataset di 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 contorno superiore e uno inferiore.

  • Sommare ogni ennesimo valore in un intervallo di valori.

Gli esempi seguenti illustrano come creare formule di matrice a celle multiple e a cella singola. Dove possibile, abbiamo incluso esempi con alcune delle funzioni di matrice dinamica, così come le formule di matrice esistenti immesse come matrici dinamiche e legacy.

Scaricare questi esempi

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

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

  • Creare una formula di matrice a celle multiple

    Funzione matrice a celle multiple nella cella H10 = F10: F19 * G10: G19 per calcolare il numero di vetture vendute per prezzo unitario

  • In questo articolo vengono calcolate le vendite totali di coupé e berline per ogni venditore inserendo = F19: F19 * G10: G19 nella cella H10.

    Quando si preme invio, verranno visualizzati i risultati riversati nelle celle H10: H19. Si noti che l'intervallo di fuoriuscita viene evidenziato con un bordo quando si seleziona una cella all'interno dell'intervallo di fuoriuscita. Si potrebbe anche notare che le formule nelle celle H10: H19 sono disabilitate. Sono disponibili solo per riferimento, quindi se si vuole modificare la formula, è necessario selezionare la cella H10, dove risiede la Formula Master.

  • Formula in forma di matrice a cella singola

    Formula in forma di matrice a cella singola per calcolare un totale complessivo con = SUM (F10: F19 * G10: G19)

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

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

    Questo esempio Mostra come può essere potente questo tipo di formula. Supponiamo ad esempio di avere 1.000 righe di dati. È possibile sommare parte o tutti i dati creando una formula in forma di matrice in una singola cella anziché trascinare la formula verso il basso attraverso le righe di 1.000. Si noti inoltre che la formula a cella singola nella cella H20 è completamente indipendente dalla formula a celle multiple (la formula nelle celle H10 tramite H19). Questo è un altro vantaggio dell'uso di formule in forma di matrice: flessibilità. È possibile modificare le altre formule nella colonna H senza influire sulla formula in H20. Può anche essere buona norma avere totali indipendenti come questo, in quanto consente di convalidare l'accuratezza dei risultati.

  • Le formule di matrice dinamica offrono inoltre questi vantaggi:

    • Coerenza    Se si fa clic su una delle celle da H10 verso il basso, viene visualizzata la stessa formula. Questa coerenza può aiutare a garantire una maggiore precisione.

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

    • Dimensioni file più piccole    È spesso possibile usare una singola formula di matrice anziché diverse formule intermedie. Ad esempio, l'esempio di vendita auto usa una formula in forma di matrice per calcolare i risultati nella colonna E. Se sono state usate formule standard come = F10 * G10, F11 * G11, F12 * G12 e così via, si sarebbero usate 11 formule diverse per calcolare gli stessi risultati. Questo non è un problema, ma se si hanno migliaia di righe in totale? Quindi può fare una grande differenza.

    • Efficienza    Le funzioni di matrice possono essere un modo efficace per creare formule complesse. La formula in forma di matrice = somma (F10: F19 * G10: G19) è uguale alla seguente: = SUM (F10 * G10, F11 * G11, F12 * G12, F13 * G13, F14 * G14, F15 * G15, F16 * G16, F17 * G17, F18 * G18, F19 * G19).

    • Riversamento Le formule di matrice dinamiche verranno automaticamente riversate nell'intervallo di output. Se i dati di origine sono in una tabella di Excel, le formule in forma di matrice dinamiche verranno ridimensionate automaticamente man mano che si aggiungono o si rimuovono dati.

    • #SPILL! errore    Le matrici dinamiche hanno introdotto la #SPILL! Error, che indica che l'intervallo di fuoriuscita previsto è bloccato per qualche motivo. Quando si risolve il blocco, la formula si riverserà automaticamente.

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 si separano gli elementi usando una virgola, si creerà una matrice orizzontale (una riga). Se si separano gli elementi utilizzando punti e virgola, è possibile creare una matrice verticale (una colonna). Per creare una matrice bidimensionale, delimitare gli elementi di ogni riga con le virgole e delimitare ogni riga con punti e virgola.

Le procedure seguenti consentono di creare una procedura per la creazione di costanti orizzontali, verticali e bidimensionali. Verranno illustrati gli esempi che usano la funzione Sequence per generare automaticamente costanti di matrice, nonché le costanti immesse manualmente.

  • Creare una costante orizzontale

    Usare la cartella di lavoro degli esempi precedenti oppure creare una nuova cartella di lavoro. Selezionare una cella vuota e immettere = sequenza (1,5). La funzione Sequence crea una matrice di 1 riga per 5 colonne uguale a = {1, 2, 3, 4, 5}. Viene visualizzato il risultato seguente:

    Creare una costante di matrice orizzontale con = SEQUENCE (1,5) o = {1, 2, 3, 4, 5}

  • Creare una costante verticale

    Selezionare una cella vuota con spazio sottostante e immettere = sequenza (5)o = {1; 2; 3; 4; 5}. Viene visualizzato il risultato seguente:

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

  • Creare una costante bidimensionale

    Selezionare una cella vuota con spazio a destra e sotto di essa, quindi immettere = sequenza (3, 4). Viene visualizzato il risultato seguente:

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

    È anche possibile immettere: o = {1, 2, 3, 4; 5, 6, 7, 8; 9, 10, 11, 12}, ma è preferibile prestare attenzione a dove inserire punti e virgola.

    Come puoi vedere, l'opzione sequenza offre vantaggi significativi rispetto all'immissione manuale dei valori costanti della matrice. In primo luogo, consente di risparmiare tempo, ma può anche essere utile per ridurre gli errori dall'immissione manuale. È anche più facile da leggere, in particolare perché i punti e virgola possono essere difficili da distinguere dai separatori di comma.

Ecco un esempio che usa costanti di matrice come parte di una formula più grande. Nella cartella di lavoro di esempio fare clic sulla costante in un foglio o crearne uno nuovo.

Nella cella D9 è stato immesso = Sequence (1, 5, 3, 1), ma è anche possibile immettere 3, 4, 5, 6 e 7 nelle celle A9: h9. Non c'è niente di speciale in quella particolare selezione di numeri, ma abbiamo appena scelto qualcosa di diverso da 1-5 per la differenziazione.

Nella cella E11 immettere = somma (D9: h9 * sequenza (1; 5))oppure = Sum (D9: h9 * {1, 2, 3, 4, 5}). Le formule restituiscono 85.

Usare le costanti di matrice nelle formule. In questo esempio abbiamo usato = SUM (D9: H (* SEQUENCE (1; 5))

La funzione SEQUENCE genera l'equivalente della costante di matrice {1, 2, 3, 4, 5}. Poiché Excel esegue le operazioni sulle espressioni racchiuse tra parentesi prima, i due elementi successivi che entrano in gioco sono i valori di cella in D9: H9 e l'operatore di moltiplicazione (*). A questo punto, la formula moltiplica i valori nella matrice archiviata per i valori corrispondenti della costante. È l'equivalente di:

= Somma (D9 * 1, E9 * 2, F9 * 3, G9 * 4, H9 * 5)o = somma (3 * 1,4 * 2,5 * 3,5 * 3, 6 * 4, 7 * 5)

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

Per evitare di usare la matrice archiviata e conservare l'operazione completamente in memoria, è possibile sostituirla con un'altra costante di matrice:

= Sum (sequenza (1, 5, 3, 1) * sequenza (1; 5))o = somma ({3, 4, 5, 6, 7} * {1; 2; 3; 4; 5})

Elementi che è possibile usare nelle costanti di matrice

  • Le costanti di matrice possono contenere numeri, testo, valori logici (ad esempio TRUE e FALSE) e valori di errore come #N/A. È possibile usare i numeri in formati interi, decimali e scientifici. Se si include il testo, è necessario racchiuderlo tra virgolette ("testo").

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

Uno dei modi migliori per usare le costanti di matrice consiste nel nominarli. Le costanti deNominate possono essere molto più facili da usare e possono nascondere parte della complessità delle formule di matrice dagli altri. Per assegnare un nome a una costante di matrice e usarla in una formula, eseguire le operazioni seguenti:

Passa a formule > nomi definiti > Definisci nome. Nella casella nome digitare Trimestre1. Nella casella fa riferimento a immettere la costante seguente (ricordarsi di digitare le parentesi graffe manualmente):

={"Gennaio","Febbraio","Marzo"}

La finestra di dialogo dovrebbe ora essere simile alla seguente:

Aggiungere una costante di matrice denominata dalle formule > nomi definiti > Name Manager > New

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

Viene visualizzato il risultato seguente:

Usa una costante di matrice denominata in una formula, ad esempio = Trimestre1, dove Trimestre1 è stato definito come = {"gennaio", "febbraio", "marzo"}

Se si vuole che i risultati vengano riversati verticalmente anziché orizzontalmente, è possibile usare = Transpose (Trimestre1) .

Se si vuole visualizzare un elenco di 12 mesi, come si può usare quando si crea un rendiconto finanziario, è possibile basare uno sconto sull'anno corrente con la funzione sequenza. L'aspetto interessante di questa funzione è che, anche se viene visualizzato solo il mese, esiste una data valida che può essere usata in altri calcoli. Questi esempi sono disponibili nei fogli di lavoro costanti e di esempio di set di dati della matrice denominati nella cartella.

= TESTO (data (anno (oggi)), sequenza (1; 12), 1), "mmm")

Usare una combinazione delle funzioni testo, data, anno, oggi e sequenza per creare un elenco dinamico di 12 mesi

Viene usata la funzione data per creare una data in base all'anno corrente, Sequence 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, è necessario usare "MMMM".

Quando si usa una costante denominata come formula in forma di matrice, ricordarsi di immettere il segno di uguale, come in = Trimestre1, non solo Trimestre1. In caso contrario, Excel interpreta la matrice come stringa di testo e la formula non funziona come previsto. Infine, tieni presente che puoi usare combinazioni di funzioni, testo e numeri. Tutto dipende da come si vuole ottenere la creatività.

Gli esempi seguenti illustrano alcuni dei modi in cui è possibile inserire costanti di matrice da usare nelle formule in forma di matrice. Alcuni esempi usano la funzione Trasponi per convertire le righe in colonne e viceversa.

  • Multipli di ogni elemento in una matrice

    Enter = Sequence (1, 12) * 2o = {1, 2, 3, 4; 5, 6, 7, 8; 9, 10, 11, 12} * 2

    È anche possibile dividere con (/), aggiungere con (+) e sottrarre con (-).

  • Elevare al quadrato gli elementi in una matrice

    Enter = Sequence (1, 12) ^ 2o = {1, 2, 3, 4; 5, 6, 7, 8; 9, 10, 11, 12} ^ 2

  • Trovare la radice quadrata di elementi al quadrato in una matrice

    Enter =Sqrt(sequenza (1; 12) ^ 2)o = sqrt ({1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12} ^ 2)

  • Trasporre una riga unidimensionale

    Invio = Trasponi (sequenza (1; 5))o = Trasponi ({1; 2; 3; 4; 5})

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

  • Trasporre una colonna unidimensionale

    Invio = Trasponi (sequenza (5; 1))o = Trasponi ({1; 2; 3; 4; 5})

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

  • Trasporre una costante bidimensionale

    Invio = Trasponi (sequenza (3,4))o = Trasponi ({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 formule di matrice per creare una nuova matrice da una matrice esistente.

    Enter = Sequence (3, 6, 10, 10)o = {10, 20, 30, 40, 50, 60; 70, 80, 90100110120; 130140150160170180}

    Assicurarsi di digitare {(parentesi graffa di apertura) prima di digitare 10 e} (parentesi graffa di chiusura) dopo aver digitato 180, perché si sta creando una matrice di numeri.

    Immettere quindi = D9 #o = D9: i11 in una cella vuota. Viene visualizzata una matrice di celle 3 x 6 con gli stessi valori visualizzati in D9: D11. Il segno # viene chiamato operatore di intervallo rovesciatoed è il modo Excel's di fare riferimento all'intero intervallo di matrici invece di digitarlo.

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

  • Creare una costante di matrice da valori esistenti

    Puoi prendere i risultati di una formula in forma di matrice rovesciata e convertirla nelle parti del componente. Selezionare la cella D9, quindi premere F2 per passare alla modalità di modifica. Premere quindi F9 per convertire i riferimenti di cella in valori, che Excel converte quindi in una costante di matrice. Quando si preme invio, la formula = D9 # deve essere ora = {10, 20, 30; 40; 50; 60; 70; 80; 90}.

  • Contare i caratteri in un intervallo di celle

    Nell'esempio seguente viene illustrato come contare il numero di caratteri in un intervallo di celle. Include spazi.

    Contare il numero totale di caratteri in un intervallo e altre matrici per l'uso delle stringhe di testo

    = SUM (LEN (C9: C13))

    In questo caso, la funzione Len restituisce la lunghezza di ogni stringa di testo in ognuna delle celle dell'intervallo. La funzione somma aggiunge questi valori insieme e visualizza il risultato (66). Se si vuole ottenere un numero medio di caratteri, è possibile usare:

    = MEDIA (LEN (C9: C13))

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

    = INDEX (C9: C13, MATCH (MAX (LEN (C9: C13)), LEN (C9: C13), 0), 1)

    Questa formula funziona solo quando un intervallo di dati contiene una singola colonna di celle.

    Esaminiamo più da vicino la formula, a partire dagli elementi interni e lavorando verso l'esterno. La funzione Len 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.

    Ecco dove le cose si complicano un po'. La funzione Confronta calcola l'offset (la posizione relativa) della cella che contiene la stringa di testo più lunga. A tale scopo, richiede tre argomenti: un valore di ricerca, una matrice di ricerca e un tipo di corrispondenza. La funzione Confronta cerca la matrice di ricerca per il valore di ricerca specificato. In questo caso, il valore di ricerca è la stringa di testo più lunga:

    MAX (lunghezza (C9: C13)

    e la stringa risiede nella matrice:

    LEN (C9: C13)

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

    • 1-restituisce il valore più grande minore o uguale alla ricerca Val

    • 0: restituisce il primo valore esattamente uguale al valore di ricerca

    • -1-restituisce il valore più piccolo maggiore o uguale al valore di ricerca specificato

    • Se si omette un tipo di corrispondenza, Excel presuppone 1.

    Infine, la funzione index accetta questi argomenti: una matrice e un numero di riga e di colonna all'interno della matrice. L'intervallo di celle C9: C13 fornisce la matrice, la funzione MATCH fornisce l'indirizzo della 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, è necessario 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, in cui è stata creata una matrice di dati di esempio nelle celle B9: B18has con: = int (RANDARRAY(10, 1) * 100). Tieni presente che RANDARRAY è una funzione volatile, quindi otterrai un nuovo set di numeri casuali ogni volta che viene calcolato in Excel.

    Formula in forma di matrice di Excel per trovare il valore più piccolo nth: = SMALL (B9 #, SEQUENCE (D9))

    Invio = piccolo (B9 #, sequenza (D9), = piccolo (B9: B18, {1; 2; 3})

    Questa formula usa una costante di matrice per valutare la funzione piccola tre volte e restituisce i 3 membri più piccoli nella matrice contenuta nelle celle B9: B18, dove 3 è un valore variabile nella cella D9. Per trovare altri valori, è possibile aumentare il valore nella funzione SEQUENCE o aggiungere altri argomenti alla costante. È anche possibile usare funzioni aggiuntive con questa formula, ad esempio somma o media. Per esempio:

    = SUM (SMALL (B9 #, SEQUENCE (D9))

    = MEDIA (piccola (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 piccola con la funzione grande. L'esempio seguente usa inoltre la riga e le funzioni indirette .

    Invio = grande (B9 #, riga (indiretto ("1:3"))) o = grande (B9: B18, riga (indiretto ("1:3") )))

    A questo punto, potrebbe essere utile conoscere un po' la riga e le funzioni inDIRETTE. Puoi usare la funzione ROW per creare una matrice di numeri interi consecutivi. Ad esempio, seleziona un vuoto e immetti:

    = RIGA (1:10)

    La formula crea una colonna di 10 numeri interi consecutivi. Per vedere un potenziale problema, inserire una riga sopra l'intervallo che contiene la formula in forma di matrice (ovvero, sopra la riga 1). Excel regola i riferimenti di riga e la formula genera ora numeri interi da 2 a 11. Per risolvere il problema, aggiungere la funzione inDIRETTO alla formula:

    = RIGA (inDIRETTO ("1:10"))

    La funzione inDIRETTO usa stringhe di testo come argomenti (motivo per cui l'intervallo 1:10 è racchiuso tra virgolette). Excel non modifica i valori di testo quando si inseriscono righe o in caso contrario spostare la formula in forma di matrice. Di conseguenza, la funzione ROW genera sempre la matrice di numeri interi desiderati. È possibile usare la sequenza in modo semplice:

    = SEQUENZA (10)

    Esaminiamo la formula usata in precedenza, = LARGE (B9 #, ROW (inDIRETTO ("1:3"))), a partire dalle parentesi interne e lavorando verso l'esterno: la funzione inDIRETTO restituisce un set di valori di testo, in questo caso i valori da 1 a 3. La funzione ROW a sua volta genera una matrice di colonne con tre celle. La funzione grande usa i valori nell'intervallo di celle B9: B18 e viene valutato tre volte, una per ogni riferimento restituito dalla funzione ROW. Se si vogliono trovare altri valori, è possibile aggiungere un intervallo di celle maggiore alla funzione inDIRETTA. Infine, come per gli esempi di piccole dimensioni, è possibile usare 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 che contiene un valore di errore, ad esempio #VALUE! o #N/A. Questo esempio Mostra come sommare i valori in un intervallo denominato dati che contengono errori:

    Usare le matrici per gestire gli errori. Ad esempio, = somma (se (errore (dati), "", dati) somma l'intervallo denominato dati, anche se include errori, come #VALUE! o #NA!.

  • =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 è simile alla formula precedente, ma restituisce il numero di valori di errore in un intervallo denominato dati anziché filtrarli:

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

Potrebbe essere necessario sommare i valori in base alle condizioni.

Puoi usare le matrici per calcolare in base a determinate condizioni. = SUM (IF (Sales>0, Sales)) somma tutti i valori maggiori di 0 in un intervallo denominato Sales.

Ad esempio, questa formula di matrice somma solo gli interi positivi in un intervallo denominato Sales, 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 essenzialmente i valori false perché 0 + 0 = 0. L'intervallo di celle usato in questa formula può essere costituito da qualsiasi numero di righe e colonne.

È anche possibile sommare valori che soddisfano più di una condizione. Questa formula di matrice, ad esempio, calcola valori maggiori di 0 e minori di 2500:

= SUM ((Sales>0) * (Sales<2500) * (Sales))

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

Puoi anche creare formule in forma di matrice che usano un tipo o una condizione. Ad esempio, è possibile sommare valori maggiori di 0 o minori di 2500:

= SUM (IF ((Sales>0) + (Sales<2500), Sales))

Non è possibile usare le funzioni AND e OR nelle formule di matrice direttamente perché tali funzioni restituiscono un singolo risultato, vero o falso, e le funzioni di matrice richiedono matrici di risultati. Per risolvere il problema, è possibile usare la logica illustrata nella formula precedente. In altre parole, è possibile eseguire operazioni matematiche, ad esempio l'aggiunta o la moltiplicazione sui valori che soddisfano la condizione OR 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 di due intervalli di celle denominati dati e DatiB e restituisce il numero di differenze tra i due. Se il contenuto dei due intervalli è identico, la formula restituirà 0. Per usare questa formula, gli intervalli di celle devono avere le stesse dimensioni e la stessa dimensione. Ad esempio, se i dati sono un intervallo di 3 righe per 5 colonne, DatiB deve essere anche 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.

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

Sono disponibili esempi simili nella cartella di lavoro di esempio sulle differenze tra i DataSet .

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 seguente e incollarla nella cella A1 in un foglio di lavoro vuoto.

Vendite Persona

Macchina Digitare

Numero In vendita

Unità Prezzo

Totale Vendite

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: D11e 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)e quindi premere CTRL + MAIUSC + INVIO.

Quando si preme CTRL + MAIUSC + INVIO, in Excel la formula viene racchiusa tra parentesi graffe ({}) e viene inserita un'istanza della formula in ogni cella dell'intervallo selezionato. Questo accade molto rapidamente, quindi ciò che vedi nella colonna E è l'importo totale delle vendite per ogni tipo di vettura per ogni venditore. Se si seleziona E2, quindi selezionare 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 matrice a cella singola

Nella cella D13 della cartella di lavoro digitare la formula seguente, 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) e quindi usa la funzione Sumper sommare i totali. Il risultato è un totale complessivo di $1.590.000 nelle vendite. Questo esempio Mostra come può essere potente questo tipo di formula. Supponiamo ad esempio di avere 1.000 righe di dati. È possibile sommare parte o tutti i dati creando una formula in forma di matrice in una singola cella anziché trascinare la formula verso il basso attraverso le righe di 1.000.

Si noti inoltre che la formula a cella singola nella cella D13 è completamente indipendente dalla formula a celle multiple (la formula nelle celle da E2 a E11). Questo è un altro vantaggio dell'uso di formule in forma di matrice: flessibilità. È possibile modificare le formule nella colonna E o eliminare completamente la colonna, senza influire 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 in forma di matrice a celle multiple. Ad esempio, fare clic su cella E3 e premere Canc. È necessario selezionare l'intero intervallo di celle (da E2 a E11) e modificare la formula per l'intera matrice oppure uscire dalla matrice così com'è. Come misura di sicurezza aggiunta, è necessario premere CTRL + MAIUSC + INVIO per confermare le modifiche apportate 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 generale, le formule di matrice usano la sintassi delle formule standard. Tutte iniziano con un segno di uguale (=) ed è possibile usare la maggior parte delle funzioni predefinite di Excel nelle formule di matrice. La differenza fondamentale è che quando si usa una formula in forma di matrice, premere CTRL + MAIUSC + INVIO per immettere la formula. Quando si esegue questa operazione, Excel racchiude la formula in forma di matrice con parentesi graffe, se si digitano manualmente le parentesi graffe, la formula verrà convertita in una stringa di testo e non funzionerà.

Le funzioni di matrice possono essere un modo efficace per creare formule complesse. La formula in forma di matrice = somma (C2: C11 * D2: D11) è uguale alla seguente: = Sum (C2 * D2, C3 * D3, C4 * D4, C5 * D5, C6 * D6, C7 * D7, C8 * D8, C9 * D9, C10 * D10, C11 * D11).

Importante: Premere CTRL + MAIUSC + INVIO ogni volta che è necessario immettere una formula in forma di matrice. Questa operazione si applica sia alle formule a cella singola che 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 in forma 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 in forma di matrice a celle multiple.

Talvolta potrebbe essere necessario espandere una formula in forma di matrice. Selezionare la prima cella nell'intervallo di matrici esistente e continuare finché non si seleziona l'intero intervallo in cui si vuole estendere la formula. Premere F2 per modificare la formula, quindi premere CTRL + MAIUSC + INVIO per confermare la formula dopo aver regolato l'intervallo di formule. La chiave consiste nel selezionare l'intero intervallo, a partire dalla cella superiore sinistra della matrice. La cella superiore sinistra è quella che viene modificata.

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

  • Si può occasionalmente dimenticare di premere CTRL + MAIUSC + INVIO. Può succedere anche agli utenti di Excel più esperti. Ricordarsi di premere questa combinazione di tasti ogni volta che si immette o si modifica una formula in forma di matrice.

  • Gli altri utenti della cartella di lavoro potrebbero non comprendere le formule. In pratica, le formule di matrice non sono in genere spiegate in un foglio di lavoro. Pertanto, se gli altri utenti devono modificare le cartelle di lavoro, è necessario evitare le formule di matrice o assicurarsi che queste persone conoscano le formule di matrice e capiscano come modificarle, se necessario.

  • A seconda della velocità di elaborazione e della memoria di cui dispone il computer, le formule in forma 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}

A questo punto, è necessario premere CTRL + MAIUSC + INVIO quando si creano formule in forma di matrice. Poiché le costanti di matrice sono un componente delle formule in forma di matrice, le costanti vengono racchiuse tra parentesi graffe manualmente per digitarle. Si usa quindi CTRL + MAIUSC + INVIO per immettere l'intera formula.

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 una singola riga: {1, 2, 3, 4}. Ecco una matrice in una singola colonna: {1; 2; 3; 4}. 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 in forma 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. Nella barra della formula immettere la formula seguente, quindi premere CTRL + MAIUSC + INVIO:

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

    In questo caso, è necessario Digitare le parentesi graffe di apertura e di chiusura ({}) ed Excel aggiungerà il secondo set per l'utente.

    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. Nella barra della formula immettere la formula seguente, 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.

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 Excel non racchiude le costanti di matrice con le parentesi graffe; in realtà le digiti. Ricorda anche che dopo aver aggiunto una costante a una formula in forma di matrice, premi 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 questa operazione, copiare la funzione, selezionare una cella vuota nella cartella di lavoro, incollare la formula nella barra della formula e quindi premere CTRL + MAIUSC + INVIO. Vedrai lo stesso risultato dell'esercizio precedente che ha usato la formula in forma 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, 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.

Uno dei modi migliori per usare le costanti di matrice consiste nel nominarli. Le costanti deNominate possono essere molto più facili da usare e possono nascondere parte della complessità delle formule di matrice dagli altri. Per assegnare un nome a 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 seguente, 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.

Se le costanti di matrice non funzionano, controllare se si sono verificati i seguenti problemi:

  • Alcuni elementi potrebbero non essere separati con il carattere appropriato. Se si omette una virgola o un punto e virgola oppure se ne viene inserita una nella posizione sbagliata, la costante di matrice potrebbe non essere creata correttamente oppure 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 seguenti illustrano alcuni dei modi in cui è possibile inserire costanti di matrice da usare nelle formule in forma di matrice. Alcuni esempi usano la funzione Trasponi 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 seguente, 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 seguente, 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 seguente, 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 seguente e 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 CTRL + MAIUSC + INVIO, che immette questa matrice di numeri nell'intervallo di celle C8: E10 tramite una formula in forma di matrice. Nel foglio di lavoro, da C8 a E10 dovrebbe essere simile al seguente:

    10

    20

    30

    40

    50

    60

    70

    80

    90

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

  4. Immettere la formula seguente nella barra della formula e quindi premere CTRL + MAIUSC + INVIO:

    =C8:E10

    UNA matrice di celle 3x3 viene visualizzata nelle celle da C1 a E3 con gli stessi valori visualizzati in C8 tramite 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. Excel converte i valori in una costante di matrice. La formula deve essere ora = {10, 20, 30; 40, 50; 60; 70; 80; 90}.

  3. Premere CTRL + MAIUSC + INVIO per immettere la costante di matrice come formula in forma 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 quindi premere CTRL + MAIUSC + INVIO per visualizzare il numero totale di caratteri nelle celle a2: A6 (66).

  3. Selezionare la cella A10, quindi premere CTRL + MAIUSC + INVIO per visualizzare il contenuto della più lunga delle celle a2: A6 (cella a3).

La formula seguente viene usata nella cella A8 per conteggiare il numero totale di caratteri (66) nelle celle da A2 a A6.

=SOMMA(LUNGHEZZA(A2:A6))

In questo caso, la funzione Len restituisce la lunghezza di ogni stringa di testo in ognuna delle celle dell'intervallo. La funzione somma aggiunge questi valori insieme e visualizza il risultato (66).

Trovare i 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 set di celle conterrà i risultati restituiti dalla formula in forma di matrice.

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

    = PICCOLO (A1: A11, {1; 2; 3})

Questa formula usa una costante di matrice per valutare la funzione piccola tre volte e restituire il più piccolo (1), il secondo più piccolo (2) e il terzo più piccolo (3) membri nella matrice contenuta nelle celle a1: A10 per trovare altri valori, si aggiungono altri argomenti al costante. È anche possibile usare funzioni aggiuntive con questa formula, ad esempio somma o media. Per esempio:

= SUM (SMALL (A1: A10, {1; 2; 3})

= MEDIA (piccola (A1: A10, {1; 2; 3})

Trovare i valori n 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, potrebbe essere utile conoscere un po' la riga e le funzioni indirette . Puoi usare la funzione Row per creare una matrice di numeri interi consecutivi. Ad esempio, selezionare una colonna vuota di 10 celle nella cartella di lavoro esercitazione, immettere la formula in forma di matrice e 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.

Esaminiamo la formula usata in precedenza, = grande (A5: A14, riga (indiretto ("1:3") )), a partire dalle parentesi interne e lavorando verso l'esterno: la funzione indiretto restituisce un set di valori di testo, in questo caso i valori da 1 a 3. La funzione Row a sua volta genera una matrice colonnare a tre celle. La funzione grande usa i valori nell'intervallo di celle A5: A14 e viene valutato tre volte, una per ogni riferimento restituito dalla funzione Row . I valori 3200, 2700 e 2000 vengono restituiti alla matrice a colonne a tre celle. Se si vogliono trovare altri valori, è possibile aggiungere un intervallo di celle maggiore alla funzione indiretta.

Come per gli esempi precedenti, è possibile usare 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, quindi premere CTRL + MAIUSC + INVIO:

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

Il testo "gruppo di celle che" viene visualizzato.

Esaminiamo più da vicino la formula, a partire dagli elementi interni e lavorando verso l'esterno. La funzione Len 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 (LEN (A2: A6))

e la stringa risiede nella matrice:

LEN (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, la funzione index accetta questi argomenti: una matrice e un numero di riga e di colonna all'interno della matrice. L'intervallo di celle a2: a6 fornisce la matrice, la funzione match fornisce l'indirizzo della 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 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 in forma 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))

Riconoscimento

Le parti di questo articolo sono basate su una serie di colonne di Excel Power User scritte da Colin Wilcox e adattate dai capitoli 14 e 15 delle formule di Excel 2002, un libro scritto da John Walkenbach, un ex MVP di Excel.

Servono altre informazioni?

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

Vedere anche

Matrici dinamiche e il comportamento di matrice espansa

Formule di matrice dinamiche e formule di matrice CSE legacy

Funzione FILTRO

Funzione MATR.CASUALE

Funzione SEQUENZA

Funzione SINGOLA

Funzione SORT

Funzione SORTBY

Funzione UNIQUE

Errori #SPILL! in Excel

Panoramica delle formule

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

Queste informazioni sono risultate utili?

Grazie per il feedback!

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

×