Un Excel schieramento formula è una formula che esegue calcoli sui valori in uno o più matrici anziché in un singolo valore di dati. Nei programmi per fogli di calcolo, un array è un intervallo o una serie di valori di dati correlati che vengono normalmente memorizzati in celle adiacenti in un foglio di lavoro. Questa guida spiega come utilizzare le formule di matrice in tutte le versioni moderne di Excel.
Questo tutorial è compatibile con la maggior parte delle versioni di Excel, tra cui 2007, 2010, 2013, 2016 e Office 365.
Quali sono le formule di matrice?
Le formule di matrice sono simili alle formule regolari:
- Iniziano con un segno di uguale ( = )
- Utilizza la stessa sintassi delle formule regolari
- Usa gli stessi operatori matematici
- Segui lo stesso ordine di operazione
In Excel, le formule di matrice sono circondate da parentesi graffe { } - queste parentesi graffe non possono essere semplicemente digitate; devono essere aggiunti a una formula premendo il tasto Ctrl , Cambio , e accedere chiavi dopo aver digitato la formula in una cella o in una cella. Per questo motivo, una formula di matrice viene a volte indicata come a CSE formula in Excel.
Un'eccezione a questa regola è quando le parentesi graffe vengono utilizzate per immettere una matrice come argomento per una funzione che normalmente contiene solo un singolo valore o riferimento di cella. Ad esempio, nel tutorial qui sotto che utilizza VLOOKUP e il SCEGLIERE funzione per creare una formula di ricerca a sinistra, viene creata una matrice per SCEGLIERE la funzione di num_indice argomento digitando le parentesi attorno all'array inserito.
Passaggi per la creazione di una formula di matrice
- Inserisci la formula.
- Tenere premuto il tasto Ctrl e Cambio tasti sulla tastiera.
- Premere e rilasciare il accedere chiave per creare la formula dell'array.
- Rilasciare ilCtrl e Cambio chiavi.
Se eseguita correttamente, la formula verrà circondata da parentesi graffe e ogni cella contenente la formula conterrà un risultato diverso.
Modifica di una formula di matrice
Ogni volta che una formula di matrice viene modificata, le parentesi graffe scompaiono dalla formula dell'array. Per ottenerli, la formula dell'array deve essere inserita premendo il tasto Ctrl, Cambio, e accedere nuovamente come quando è stata creata la formula dell'array.
Tipi di formule di matrice
Esistono due tipi principali di formule di array:
- Formule di array di celle singole che eseguono calcoli multipli in una singola cella del foglio di lavoro.
- Formule di matrice a più celle che si trovano in più di una cella del foglio di lavoro.
Formule di array di celle singole
Questo tipo di formula di matrice utilizza una funzione, ad esempio SOMMA, MEDIA, o CONTARE, per combinare l'output di una formula di matrice multi-cella in un singolo valore in una singola cella. Un esempio di una formula di matrice di celle singole potrebbe essere:
{= SUM (A1: A2 * B1: B2)}
Questa formula aggiunge il prodotto di A1 * B1 e A2 * B2e quindi restituisce un singolo risultato in una singola cella nel foglio di lavoro. Un altro modo di scrivere la formula sopra sarebbe:
Formule di array multicellulari
Come suggerisce il nome, queste formule di array si trovano in più celle del foglio di lavoro e restituiscono una matrice come risposta. In altre parole, la stessa formula si trova in due o più celle e restituisce risposte diverse in ogni cella.
Ogni copia, o esempio, della formula dell'array, esegue lo stesso calcolo in ogni cella in cui si trova, ma ogni istanza della formula utilizza dati diversi nei suoi calcoli. Pertanto, ogni istanza produce risultati diversi. Un esempio di una formula a più celle cellulari sarà simile a:
{= A1: A2 * B1: B2}
Se l'esempio precedente si trova in cellule C1 e C2 in un foglio di lavoro, i risultati sarebbero i seguenti:
- I dati in A1 viene moltiplicato per i dati in B1e i risultati sono archiviati in cellula C1.
- I dati in A2 viene moltiplicato per i dati in B2e i risultati sono archiviati in cellula C2.
Utilizzo di matrici per la trasposizione di righe e colonne
Il TRASPORRE la funzione viene utilizzata per copiare i dati da una riga in una colonna o viceversa. Questa funzione è una delle poche in Excel che deve essere sempre utilizzata come formula di matrice.
Formule di matrice e funzioni di Excel
Molte delle funzioni integrate di Excel, come ad esempio SOMMA, MEDIA, e CONTARE, può anche essere usato in una formula di matrice. Ci sono anche alcune funzioni, come la TRASPORRE funzione, che deve essere sempre inserita come array in modo che funzioni correttamente. L'utilità di molte funzioni come INDICE e INCONTRO o MAX e SE può essere esteso usando insieme in una formula di matrice.
Esempi di formule di array in Excel:
01 di 08Creare una semplice formula di array di celle singole
Le formule di array di celle singole normalmente eseguono prima un calcolo a più celle e quindi utilizzano una funzione come MEDIA o SUM combinare l'output dell'array in un singolo risultato.
02 di 08Ignora i valori di errore durante la ricerca dei dati
Questa formula di matrice usa il MEDIA, SE, e ISNUMBER funzioni per trovare il valore medio per i dati esistenti ignorando i valori di errore come # DIV / 0! e #NOME?
03 di 08Conta celle di dati
Utilizzare il SOMMA eSE funziona in una formula di matrice per contare le celle di dati che soddisfano una delle diverse condizioni; questa tecnica differisce dall'usare Excel CONTA.PIÙ.SE funzione, che richiede che tutte le condizioni impostate siano soddisfatte prima che la cella venga contata.
04 di 08Trova il più grande numero positivo o negativo
Questo esempio combina il MAX funzione eSE funzione in una formula di matrice che troverà il valore massimo o massimo per un intervallo di dati quando vengono soddisfatti criteri specifici.
05 di 08Trova il più piccolo numero positivo o negativo
Simile all'esempio sopra, questo combina il MIN funzione e IF funzione in una formula di matrice per trovare il valore minimo o minimo per un intervallo di dati quando vengono soddisfatti criteri specifici.
06 di 08Trova il valore medio o mediano
Il MEDIANO la funzione in Excel trova il valore medio per un elenco di dati. Combinandolo con il SE funzione in una formula di matrice, è possibile trovare il valore medio per diversi gruppi di dati correlati.
07 di 08Crea una formula di ricerca con più criteri
Questa formula di matrice implica l'annidamento di INCONTRO e INDICE funzioni per trovare informazioni specifiche in un database.
08 di 08Crea una formula di ricerca a sinistra
Il VLOOKUP la funzione normalmente cerca solo i dati situati nelle colonne a destra, ma combinandoli con il SCEGLIERE funzione, è possibile creare una formula di ricerca a sinistra che cercherà le colonne di dati a sinistra del Valore di ricerca discussione.
