Le funzioni di base di Excel sono adatte per calcoli semplici, ma diventano rapidamente complesse quando si tratta di analisi di dati complesse. Si finisce per avere formule annidate difficili da leggere, molteplici colonne di supporto che ingombrano il foglio di calcolo e formule che possono interrompersi quando i dati cambiano. È qui che entrano in gioco le formule matriciali in Excel.
Le formule di matrice consentono di eseguire calcoli su interi intervalli di dati in un'unica formula. Pertanto, puoi Esegui ricerche rapidissime, filtra e ordina con un'unica potente espressione, invece di scrivere formule separate per ogni riga o colonna. Non è una novità per Excel, ma alcune persone restano fedeli ai vecchi metodi di lavoro quando queste funzioni possono rendere il loro lavoro più semplice ed efficiente.
Link veloci
5. XLOOKUP
Supera ogni volta le prestazioni di CERCA.VERT.
CERCA.X è la funzione di ricerca che avrebbe dovuto esistere fin dall'inizio. A differenza di CERCA.VERT, che obbliga a contare le colonne e cerca solo a destra, CERCA.X funziona in qualsiasi direzione e utilizza riferimenti di colonna effettivi. La sintassi è la seguente:
=XLOOKUP(valore_cercato; matrice_cercata; matrice_restituita; [se_non_trovato]; [modalità_corrispondenza]; [modalità_ricerca])
Ecco cosa significa ogni parametro:
- valore di ricerca: Il valore specifico che stai cercando. Potrebbe essere un codice articolo, un codice prodotto o qualsiasi identificatore presente nel tuo set di dati.
- ricerca_array: L'intervallo in cui Excel effettua le ricerche valore di ricerca Il tuo. Di solito si tratta di una singola colonna o riga contenente i tuoi criteri di ricerca.
- matrice_di_ritorno: L'intervallo che contiene i valori che si desidera recuperare. Può trattarsi di una singola colonna, di più colonne o persino di un'intera sezione di tabella.
- if_not_found (facoltativo): Testo o valore personalizzato da visualizzare quando non viene trovata alcuna corrispondenza. Elimina i fastidiosi errori #N/D e consente di visualizzare al loro posto "Non trovato" o "Controlla codice articolo".
- match_mode (facoltativo): Controlla il tipo di corrispondenza. Usa 0 per la corrispondenza esatta (predefinita), -1 per la corrispondenza esatta successiva o più piccola, 1 per la corrispondenza esatta successiva o più grande e 2 per la corrispondenza con caratteri jolly.
- search_mode (facoltativo): Specifica la direzione della ricerca. Utilizzare 1 per una ricerca dal primo all'ultimo (predefinita), -1 per una ricerca dall'ultimo al primo e 2 per una ricerca binaria su dati ordinati.
Prendiamo come esempio un foglio di calcolo per l'inventario meccanico. La seguente formula cerca il codice componente "BRG-002" in un intervallo di ID componente e restituisce i dati corrispondenti. Se il componente non è presente, visualizza "Componente non trovato" anziché un errore.
=XLOOKUP("BRG-002", A:A, A:H, "Parte non trovata")
CERCA.X consente di estrarre dati da colonne diverse senza i complessi calcoli di colonna presenti in CERCA.VERT, rendendolo uno degli strumenti più importanti Funzioni di Excel per trovare rapidamente i dati.
4. MATR.SOMMA.PRODOTTO
Stazione di generazione di energia per calcoli condizionali
SUMPRODUCT non solo somma numeri, ma moltiplica anche matrici e somma i risultati. Questo la rende utile per calcoli condizionali complessi che richiedono più colonne ausiliarie.
Ha la seguente formula:
=SOMMA.PRODOTTO(matrice1; [matrice2]; [matrice3]; ...)
qui, matrice1 È il primo intervallo di valori da moltiplicare, solitamente la colonna di dati principale, come quantità o costi. matrice2 Si tratta di un secondo intervallo facoltativo per la moltiplicazione, che spesso contiene criteri o logica condizionale mediante operatori di confronto.
Diventano più utili quando utilizziamo operatori logici all'interno di array. Ad esempio, quando digitiamo condizioni come (supplier="Siemens"), Excel converte i risultati TRUE/FALSE in 1/0, consentendo i calcoli.
Ad esempio, la seguente formula calcola il valore totale delle scorte per i componenti forniti solo da Siemens. La formula moltiplica le quantità per i costi unitari, ma solo per le righe in cui il fornitore soddisfa i criteri.
=SUMPRODUCT(D2:D100*H2:H100*(G2:G100="Siemens"))
Allo stesso modo, la seguente formula calcola il costo totale di una scorta di cuscinetti in buone condizioni:
=SUMPRODUCT((C2:C100="Bearings")*(D2:D100>=15)*H2:H100)
Si applicano due condizioni contemporaneamente: la categoria deve essere "Cuscinetti" e i livelli di inventario devono essere pari o superiori a 15 unità, il che ci aiuta a identificare le categorie di cuscinetti che hanno una copertura di inventario sufficiente.
A differenza delle tradizionali funzioni SOMMA con più criteri, SUMPRODUCT non richiede complesse strutture annidate perché gestisce più condizioni in un'unica formula leggibile. Funzioni SOMMA in Excel, Come SOMMA.SE e SOMMA.PIÙ.SE, sono ottime per semplici sommatorie condizionali, ma la funzione SOMMA.PRODOTTO eccelle quando è necessario moltiplicare valori prima di sommarli o gestire operazioni logiche più complesse.
3. FILTRO
Semplifica l'estrazione dinamica dei dati
FILTER estrae righe dal set di dati in base alle condizioni specificate. A differenza del filtraggio manuale, questa funzione genera risultati dinamici che si aggiornano automaticamente quando i dati di origine cambiano. La sintassi di FILTER è la seguente:
=FILTER(array, includi, [se_vuoto])
Ecco cosa controlla ogni input:
- matrice (intervallo): L'intera gamma di dati che desideri filtrare. Include tutte le colonne che desideri includere nei risultati, non solo la colonna dei criteri.
- includono: Condizione logica che specifica quali righe restituire: utilizza operatori di confronto per creare array TRUE/FALSE per ogni riga.
- if_empty (facoltativo): Visualizza un messaggio personalizzato quando nessuna riga soddisfa i criteri specificati. Previene gli errori #CALC! e visualizza un testo significativo come "Nessun risultato corrispondente trovato".
La funzione valuta la condizione per ogni riga dell'intervallo. Quando la condizione restituisce TRUE, l'intera riga viene visualizzata nei risultati filtrati. Ecco un esempio tratto da un foglio di calcolo per l'inventario meccanico:
=FILTER(A2:H101, (C2:C101="Bearings")*(G2:G101="Timken"))
Questa formula estrae tutte le righe in cui la risorsa è "Timken" e la categoria è "Cuscinetti". L'asterisco (*) crea una condizione AND moltiplicando tra loro gli array logici.
Quando aggiungi nuovi dati al tuo intervallo di origine, Utilizzo della funzione FILTRO in Excel È più sensato dell'ordinamento manuale e delle tabelle temporanee perché i risultati filtrati vengono aggiornati automaticamente. Questo lo rende utile per creare dashboard e report in tempo reale.
2. UNICO
Estrarre valori univoci senza duplicati
UNIQUE estrae valori univoci dall'intervallo di dati ed evita automaticamente i duplicati. Questa funzione è importante se si desidera creare elenchi a discesa, analizzare categorie di dati e creare report di riepilogo. La formula è:
=UNIQUE(array, [per_colonna], [esattamente_una_volta])
Ecco come funziona ogni input:
- matrice (intervallo): L'intervallo che contiene i dati da cui si desidera rimuovere i duplicati: può essere una singola colonna, più colonne o un'intera sezione della tabella.
- by_col (facoltativo): FALSE confronta le righe per determinarne l'univocità (impostazione predefinita), mentre TRUE confronta le colonne. Tuttavia, la maggior parte degli scenari utilizza il confronto di riga predefinito.
- exactly_once (facoltativo): FALSE restituisce tutti i valori univoci, compresi quelli che compaiono più volte (impostazione predefinita), mentre TRUE restituisce solo i valori che compaiono esattamente una volta nel set di dati.
La funzione UNIQUE valuta ogni riga o valore nell'array e restituisce solo la prima occorrenza di ciascun elemento univoco. L'ordine corrisponde alla sequenza di dati originale. Ecco un esempio:
=UNICO(G2:G22)
Questa formula estrae tutti i nomi univoci dei fornitori dalla colonna G "Fornitore" e crea un elenco pulito e duplicato. La utilizzo per creare elenchi a discesa dei fornitori o report di riepilogo.
Puoi anche utilizzarlo sull'intera tabella, come mostrato di seguito:
=UNICO(A2:F100)
Restituisce combinazioni univoche in tutte le colonne (da A a F), visualizzando record di inventario distinti. Se due articoli hanno valori identici in ciascuna colonna, solo uno apparirà nei risultati.
Quando si lavora con set di dati di grandi dimensioni, UNIQUE elimina il noioso processo di rimozione manuale dei duplicati. I risultati dinamici vengono aggiornati man mano che arrivano nuovi dati e, poiché UNIQUE crea matrici di spillover, questo approccio elimina la necessità di ridimensionare le tabelle, ridimensionandole automaticamente per contenere tutti i valori univoci. Lo utilizzo per mantenere elenchi di riferimento puliti e creare intervalli di convalida dei dati affidabili.
1. ORDINA e ORDINA PER
Organizza i tuoi dati senza compromettere l'originale
Le funzioni SORT e SORTBY organizzano i dati in modo dinamico mantenendo intatta la sorgente. SORT gestisce l'ordinamento di base in base alla posizione delle colonne, mentre SORTBY ordina in base ai valori presenti in colonne diverse, offrendo maggiore flessibilità per ordinamenti complessi.
SORT utilizza questa struttura:
=ORDINA(array, [indice_ordinamento], [ordinamento_ordinato], [per_colonna])
Ecco cosa controlla ciascun parametro:
- Vettore: L'intervallo di dati che si desidera ordinare, che include tutte le colonne che devono apparire nei risultati ordinati.
- sort_index (facoltativo): Numero di colonna all'interno dell'array in base al quale ordinare. Utilizzare 1 per la prima colonna, 2 per la seconda colonna e così via (il valore predefinito è 1).
- sort_order (facoltativo): Utilizzare 1 per l'ordine crescente (predefinito) e -1 per l'ordine decrescente.
- by_col (facoltativo): FALSE per ordinare per righe (predefinito), TRUE per ordinare per colonne: la maggior parte degli scenari utilizza l'ordinamento per righe.
La funzione ORDINA.PER assume la seguente forma:
=ORDINA PER(matrice, per_matrice1, [ordina_ordine1], [per_matrice2], [ordina_ordine2], ...)
Le sue transazioni includono:
- Vettore: L'intervallo di dati da ordinare: simile alla funzione ORDINA, contiene tutte le colonne che si desidera includere nei risultati.
- by_array1: L'intervallo contenente i valori che determinano l'ordinamento può essere qualsiasi colonna, anche esterna all'intervallo della matrice principale.
- sort_order1 (facoltativo): 1 per ordine crescente (predefinito), -1 per ordine decrescente.
- by_array2, sort_order2 (facoltativo): Criteri di ordinamento aggiuntivi per l'ordinamento multilivello.
Prendendo come esempio un foglio di calcolo per l'inventario meccanico, queste funzioni gestiscono scenari di ordinamento reali:
=ORDINA(A2:H22; 4; -1)
Questa formula ordina l'intero inventario in base ai livelli di scorta in ordine decrescente, con gli articoli con la scorta più alta mostrati per primi. La formula ordina in base alla colonna 4 (livelli di scorta) mantenendo tutte le relazioni tra le righe.
Sto utilizzando la funzione SORTBY. Invece di ORDINA, puoi utilizzarlo per ottenere un maggiore controllo sui criteri di ordinamento e sui livelli di ordinamento multipli. Ad esempio, la seguente formula ordina prima alfabeticamente per categoria, poi per livelli di inventario dal più alto al più basso all'interno di ciascuna categoria.
=ORDINA PER(A2:H22; C2:C22; 1; D2:D22; -1)
Fogli di calcolo organizzati, risultati più intelligenti
Le formule di matrice eliminano l'ingombro delle colonne di supporto e delle funzioni nidificate che rendono i fogli di calcolo difficili da gestire. Si ottengono formule singole che gestiscono più operazioni, rendendo le cartelle di lavoro più pulite e professionali.
Un vantaggio notevole sono le funzioni dinamiche, i cui risultati vengono aggiornati automaticamente quando i dati di origine cambiano. Questo elimina gli aggiornamenti manuali o le stringhe di formule interrotte, rendendo i fogli di calcolo più affidabili per l'analisi continua.
La libreria di funzioni array di Excel continua ad espandersi oltre questi strumenti di base. Quando devo combinare dati provenienti da più fonti, utilizzo le funzioni VSTACK e HSTACK per combinare intervalli. Insieme, queste funzioni creano potenti flussi di lavoro di elaborazione dati che sarebbero impossibili da ottenere con le formule tradizionali.
