Come utilizzare una formula di matrice in Excel

Le formule di matrice sono una parte cruciale del kit di strumenti che rende Excel versatile. Tuttavia, queste espressioni possono essere scoraggianti per i principianti.

Sebbene possano sembrare complicati a prima vista, le loro basi sono facili da comprendere. L’apprendimento degli array è utile in quanto queste formule semplificano la gestione di dati Excel complessi.

Che cos’è una formula di matrice in Excel?

Un array è qualsiasi raccolta di celle in una colonna, riga o combinazione riunite in un gruppo.

In Microsoft Excel, il termine formula di matrice si riferisce a una famiglia di formule che eseguono una o più operazioni su un intervallo di celle di questo tipo, tutte in una volta, anziché una cella alla volta.

Le versioni precedenti di Excel richiedevano agli utenti di premere Ctrl + Maiusc + Invio per creare una funzione di matrice, risultando nelle funzioni CSE (Ctrl, Maiusc, Escape), sebbene questo non sia più il caso di Excel 365.

Un esempio potrebbe essere una funzione per ottenere la somma di tutte le vendite superiori a $ 100 in un dato giorno. O per determinare la lunghezza (in cifre) di cinque diversi numeri memorizzati in cinque celle.

Le formule di matrice possono anche essere il modo perfetto per estrarre dati di esempio da set di dati di grandi dimensioni. Possono utilizzare argomenti che includono un intervallo di celle da una singola colonna, un intervallo di celle da una singola riga o celle che si estendono su più righe e colonne.

Inoltre, le istruzioni condizionali possono essere applicate quando si estraggono i dati in una formula di matrice, ad esempio solo estraendo i numeri sopra o sotto un valore specifico o estraendo ogni n-esimo valore.

Questo controllo granulare ti consente di assicurarti di estrarre un sottoinsieme esatto dei tuoi dati, filtrando i valori di cella indesiderati o prelevando un campione casuale di elementi a scopo di test.

Come definire e creare un array in Excel

Le formule di matrice sono, al loro livello più elementare, facili da creare. Per un semplice esempio, possiamo considerare una fattura per un ordine di parti di un cliente con le seguenti informazioni sulla riga: il prodotto WeU (Stock Keeping Unit), la quantità acquistata, il peso cumulativo dei prodotti acquistati e il prezzo per unità acquistata.

Per completare la fattura, devono essere aggiunte tre colonne alla fine della tabella inferiore, una per il totale parziale delle parti, una per il costo della spedizione e la terza per il prezzo totale di ciascuna riga.

Puoi utilizzare una semplice formula per calcolare ogni voce in modo indipendente. Ma se la fattura riguardava molte voci, potrebbe rapidamente diventare molto più complicata. Quindi, invece, il “Subtotale” può essere calcolato con un’unica formula inserita nella cella E4:

=B4:B8 * D4:D8

Entrambi i numeri che stiamo moltiplicando sono intervalli di celle piuttosto che singole celle. In ogni riga, la formula estrae i valori di cella appropriati dagli array e inserisce il risultato nella cella corretta senza ulteriori input da parte dell’utente.

Se assumiamo una tariffa di spedizione forfettaria di $ 1,50 per libbra, possiamo calcolare la nostra colonna di spedizione con una formula simile inserita nella cella F4:

=C4:C8 * B11

Questa volta abbiamo utilizzato solo un array sul lato sinistro dell’operatore di moltiplicazione. I risultati vengono ancora immessi automaticamente, ma ciascuno viene moltiplicato per un valore statico questa volta.

Infine, possiamo utilizzare lo stesso tipo di formula che abbiamo utilizzato per il totale parziale per ottenere un totale aggiungendo matrici di totali parziali e costi di spedizione nella cella G4:

=E4:E8 + F4:F8

Ancora una volta, una semplice formula di matrice ha trasformato quelle che altrimenti sarebbero state cinque formule in una.

In futuro, l’aggiunta dei calcoli delle imposte a questo foglio di lavoro richiederebbe la modifica di una sola formula invece di ogni elemento pubblicitario.

Gestione dei condizionali negli array di Excel

L’esempio precedente utilizza formule aritmetiche di base tra matrici per produrre i risultati, ma la semplice matematica non funzionerà più per situazioni più complesse.

Si supponga, ad esempio, che la società che ha creato la fattura nell’esempio precedente sia passata a un altro vettore merci. In tal caso, la spedizione standard potrebbe diminuire di prezzo, ma potrebbe essere applicata una tariffa per oggetti che superano un certo peso.

Per le nuove tariffe di spedizione, la spedizione standard sarà di $ 1,00 per libbra e la spedizione di peso superiore sarà di $ 1,75 per libbra . La spedizione di peso elevato si applica a qualsiasi cosa superiore a sette libbre .

Anziché tornare al calcolo di ciascuna tariffa di spedizione riga per riga, una formula di matrice può includere un’istruzione condizionale IF per determinare il costo di spedizione appropriato per ogni riga:

=IF(C4:C8 < 7, C4:C8*B11, C4:C8*B12)

Con una rapida modifica a una funzione in una cella e l’aggiunta di una nuova tariffa di spedizione nella nostra tabella delle tariffe, ora possiamo vedere che non solo l’intera colonna di spedizione viene calcolata in base al peso, ma anche i totali raccolgono automaticamente la nuova prezzi.

La fattura è quindi a prova di futuro, garantendo che qualsiasi modifica futura al calcolo dei costi richieda la modifica di una sola funzione.

La combinazione di condizionali multipli o diversi può permetterci di eseguire varie azioni. Questa logica può essere utilizzata in diversi modi sullo stesso array di dati. Ad esempio,

• È possibile aggiungere una funzione IF aggiuntiva per ignorare la spedizione se il totale parziale supera un determinato importo.
• Si potrebbe aggiungere una tabella delle imposte per tassare diverse categorie di prodotti con aliquote diverse.

Non importa quanto sia complessa la fattura, la modifica di una singola cella sarà sufficiente per ricalcolare qualsiasi parte della fattura.

Esempio: calcolo della media di numeri ennesimi

Oltre a semplificare il calcolo e l’aggiornamento di grandi quantità di dati, le formule di matrice possono estrarre una sezione di un set di dati a scopo di test.

Come mostra l’esempio seguente, estrarre una porzione di dati da un set di dati di grandi dimensioni a scopo di convalida è facile utilizzando le formule di matrice:

Nell’esempio precedente, la cella D20 utilizza una semplice funzione per calcolare la media di ogni n-esimo risultato dal sensore 1. In questo caso, possiamo definire n-esimo utilizzando il valore nella cella D19, dandoci il controllo sulla dimensione del campione che determina la media:

=AVERAGE(IF(MOD(ROW(B2:B16)-2,D19)=0,B2:B16,""))

Ciò consente di segmentare rapidamente e facilmente un set di dati di grandi dimensioni in sottoinsiemi.

Padroneggiare le formule di matrice in Excel è essenziale

Questi semplici esempi sono un buon punto di partenza per cogliere la logica dietro le formule di matrice. Pensaci bene in modo da poter eseguire rapidamente più operazioni oggi e in futuro modificando solo alcuni riferimenti e funzioni di cella. Le formule di matrice sono un modo leggero ed efficiente per suddividere e suddividere i dati. Padroneggiali all’inizio del tuo percorso di apprendimento di Excel in modo da poter eseguire calcoli statistici avanzati con sicurezza.