Excel è un ottimo strumento per la creazione di report, l'analisi, l'organizzazione e l'automazione dei dati. Le funzioni di Excel aiutano molto a lavorare sui dati. Le funzioni come COUNTIFS, SUMIFS, VLOOKUP, ecc. Sono le funzioni più potenti e utilizzate di frequente sin dall'inizio nel mondo di Excel.
Sebbene le funzioni disponibili in Excel 2016 e precedenti siano sufficienti per elaborare qualsiasi tipo di calcolo e automazione, a volte le formule diventano complicate. Ad esempio, se non trovi il valore massimo con alcune condizioni, devi utilizzare alcuni trucchi nella versione precedente di Excel del 2016. Questi tipi di cose minori ma importanti sono risolte in Excel 2019 e 365.
Ci sono più di 10 nuove funzioni in Excel 2019 e 365 che riducono lo sforzo umano e la complessità delle formule.
1. La funzione MAXIFS
In excel 2016 e versioni precedenti, se vuoi ottenere il valore massimo in un intervallo in cui una o più condizioni corrispondono, devi usare MAX con IF con alcuni trucchi. Non è molto difficile, ma per alcuni richiede tempo e crea confusione.
Excel 2019 introduce una nuova funzione denominata funzione MAXIFS. Questa funzione restituisce il valore massimo da un array quando tutte le condizioni date sono soddisfatte.
La sintassi della funzione è:
| =MAXIFS(intervallo_max,intervallo_criteri1,criteri1,intervallo_criteri2,criteri2… ) |
Max_range1: È l'intervallo numerico che contiene il valore massimo.
Criteri_range1: È l'intervallo di criteri che si desidera filtrare prima di ottenere il valore massimo.
Criteri1: Sono i criteri o il filtro che vuoi mettere su intervallo_criteri prima di ottenere il valore massimo.
Supponiamo che tu debba ottenere il massimo dei voti dalla classe 3, quindi la formula sarà
| =MAXIFS(punti,classe,3) |
Qui i segni sono l'intervallo denominato che contiene i segni e la classe è l'intervallo denominato che contiene la classe.
Leggi in dettaglio la funzione MAXIFS qui.
2. La funzione MINIFS
Come la funzione MAXIFS, la funzione MINIFS viene utilizzata per ottenere il valore minimo dall'intervallo specificato quando tutte le condizioni fornite sono soddisfatte.
La sintassi della funzione è:
| =MINIFS(intervallo_min,intervallo_criteri1,criteri1,intervallo_criteri2,criteri2… |
Min_range1: È l'intervallo numerico che contiene il valore minimo.
Criteri_range1: È l'intervallo di criteri che si desidera filtrare prima di ottenere il valore minimo.
Criteri1: Sono i criteri o il filtro che vuoi mettere su intervallo_criteri prima di ottenere il valore minimo.
Supponiamo che tu debba ottenere i voti minimi dalla classe 3, quindi la formula sarà
| =MINIFS(punti,classe,3) |
Qui "segni" è l'intervallo denominato che contiene i segni e "classe" è l'intervallo denominato che contiene la classe.
Leggi in dettaglio la funzione MAXIFS qui.
Per trovare il valore minimo nell'intervallo con le condizioni in Excel 2016 e versioni precedenti, leggi questo.
3. La funzione IFS
Poiché l'Ifs annidato occupa un posto speciale nella nostra vita lavorativa quotidiana, ci piace molto. Ma per alcuni nuovi studenti, è complesso. Gli if annidati ci consentono di controllare più condizioni e restituire un valore diverso quando viene soddisfatta una delle condizioni. Le formule diventano complesse con sempre più IF in funzione.
Excel 2019 ed Excel 365 ora utilizzano la funzione IFS. Può controllare più condizioni e restituire valori diversi per ogni condizione.
Sintassi della funzione IFS:
| =IFS (condizione1, Valore1_Se_Vero, [condizione2, Valore2_Se_Vero],… ) |
Condizione1:La prima condizione.
Valore1_Se_Vero: Valore se la prima condizione è vera.
[Condizione2]: Questo è facoltativo. La seconda condizione, se ne hai.
[Valore1_Se_Vero]: Valore se la seconda condizione è vera.
Puoi avere tutte le combinazioni di condizioni e valori che desideri. C'è un limite, ma non avrai mai bisogno di raggiungerlo.
Diciamo che devi dare i voti agli studenti in base ai voti. Per i voti superiori a 80, voto A, B superiore a 60, C superiore a 40 e F inferiore o uguale a 40.
| =SE (LA1>80, "LA", LA1>60, "LA",LA1>40, "DO",LA1<=40, "FA") |
Una spiegazione dettagliata della funzione IFS può essere trovata qui.
4. La funzione SWITCH
La funzione switch restituisce valori diversi a seconda dei risultati di un'espressione. Sembra IFS? È una specie di. In realtà questa funzione serve per sostituire un altro tipo di formule SE annidate.
A differenza della funzione IFS che restituisce valori in base a VERO, FALSO; la funzione SWITCH restituisce valori basati su VALUES restituiti dall'espressione.
| =INTERRUTTORE (espressione, valore1,risultato1, [predefinito o valore2,risultato2],… ) |
Espressione: Può essere qualsiasi espressione valida che restituisce alcuni valori. Un riferimento di cella, una formula o un valore statico.
Valore1,risultato1: Il valore e il risultato sono accoppiati. Se il valore restituito daespressione è valore1, allora verrà restituito il risultato1.
[Predefinito o valore2,risultato2]: Se vuoi restituire un valore predefinito, definiscilo qui. Altrimenti definire il valore2 e il risultato2. È facoltativo.
Ad esempio, se hai una formula che restituisce i nomi degli animali. Ora, a seconda del nome restituito dell'animale, vuoi restituire il suono caratteristico di quell'animale.
| =INTERRUTTORE (A1, "Cane","Bow Wow", "Gatto","Meow", "Parla") |
Ho spiegato in dettaglio la funzione SWITCH qui.
5. La funzione FILTRO
La funzione FILTER viene utilizzata per filtrare i dati in base ad alcuni criteri. Abbiamo utilizzato l'opzione filtro dalla scheda home in Excel. La funzione FILTRO funziona come l'opzione filtro. Restituisce solo i dati filtrati utilizzando una funzione. Questi dati filtrati possono essere utilizzati come fonte di dati per altre formule.
La sintassi della funzione FILTRO è:
| =FILTRO(array,includi,[se_vuoto]) |
Vettore: Questo è l'array che vuoi filtrare. Può essere unidimensionale o bidimensionale.
Includere:È il filtro che vuoi mettere sull'array. Come, colori = "rosso".
[se_vuoto]:Questo è facoltativo. Definire qualsiasi testo o espressione se il filtro non restituisce nulla.
La formula seguente restituisce tutti i frutti il cui colore è rosso.
| =FILTRO(frutti,colore="rosso","nessun frutto trovato") |
Qui frutta e colore sono intervalli denominati che contengono rispettivamente i nomi dei frutti e i loro colori.
Puoi leggere in dettaglio la funzione FILTRO qui.
6. La funzione SORT
In Excel 2016 e versioni precedenti, era davvero difficile ottenere un array ordinato utilizzando una formula. Questo processo è semplificato in Excel 2019 e 365.
Excel 2019 introduce la funzione ORDINA. La funzione SORT ordina l'array dato in ordine crescente o decrescente in base alla colonna/riga data.
La sintassi della funzione SORT è:
| =ORDINA(array,[sort_index],[sort_order],[by_col]) |
Vettore:È il riferimento dell'array o dell'intervallo che si desidera ordinare.
[sort_index]:Il numero di colonna nell'array bidimensionale in base al quale si desidera ordinare l'intervallo. Per impostazione predefinita, è 1.
[ordinamento]:L'ordine in base al quale si desidera ordinare l'array. Per ascendente è 1 e per discendente è -1. Per impostazione predefinita, è 1.
[by_col]:Impostalo True (1) se vuoi ordinare un array orizzontale. Per impostazione predefinita è False (0) per i dati verticali.
Diciamo se vuoi ordinare i valori nell'intervallo A2: A11 in modo ascendente. allora la formula sarà.
| =ORDINA(A2:A11) |
Ho spiegato in dettaglio la funzione SORT qui.
7. La funzione SORTBY
La funzione SORTBY è simile alla funzione SORT. L'unica differenza è che l'array di ordinamento non deve necessariamente essere parte dell'array ordinato nella funzione SORTBY.
| =ORDINA(array,sorting_array1,[ordine],… ) |
Vettore:Questo è l'array che vuoi ordinare.
Sorting_array1:Questo è l'array in base al quale vuoi ordinare l'array. La dimensione di questo array dovrebbe essere compatibile con il Vettore.
[ordine]:Opzionale. Impostalo su -1 se vuoi che l'ordine sia decrescente. Per impostazione predefinita, è crescente (1).
Supponiamo di voler ordinare l'intervallo A2:A11 per l'intervallo B2:B11, in ordine decrescente. Quindi la formula in excel 2019 o 365 sarà:
| =ORDINA(A2:A11,B2:B11,-1) |
Ho spiegato qui in dettaglio la funzione SORTBY.
8. La funzione UNICA
In Excel 2016 e versioni precedenti, abbiamo utilizzato una serie di funzioni in combinazione per ottenere tutti i valori univoci dall'elenco fornito. La formula utilizzata è piuttosto complessa e di difficile comprensione.
Excel 2019 e 365 introducono una semplice funzione UNIQUE che restituisce tutti i valori univoci da un determinato array.
La sintassi della funzione UNIQUE è:
| =UNICO(array,[per_col],[esattamente_una volta]) |
Vettore: l'array da cui si desidera estrarre valori univoci:
[by_col]: impostalo su TRUE(1) se l'array è orizzontale. Per impostazione predefinita, è FALSE per i dati verticali.
[esattamente_una volta]: impostalo su TRUE (1) se vuoi estrarre i valori che si verificano solo una volta nell'array. Per impostazione predefinita, è FALSE(0) per estrarre tutti i valori univoci.
Diciamo che voglio ottenere solo un'istanza di ciascun valore dall'intervallo A2: A11, quindi la formula sarà:
| =UNICO(A2:A11) |
Per leggere in dettaglio la funzione UNIQUE puoi cliccare qui.
9. La funzione SEQUENZA
Per ottenere una sequenza di numeri in Excel 2016 e versioni precedenti utilizziamo una combinazione di funzioni. La soluzione funziona ma è complessa.
Excel 2019 e 365 forniscono la soluzione sotto forma della funzione SEQUENZA. La funzione sequenza restituisce semplicemente la serie del numero.
La sintassi della funzione SEQUENZA è:
| =SEQUENZA(righe,[colonne],[inizio],[passo]) |
Righe:Il numero di righe a cui si desidera trasferire la sequenza.
[colonna]:Il numero di colonne a cui si desidera riversare la sequenza. I numeri riempiranno prima le colonne e poi le righe. La colonna è facoltativa. Per impostazione predefinita, è 1.
[cominciare]:Opzionale. Il numero iniziale della sequenza. Per impostazione predefinita, è 1.
[fare un passo]:Questo è il numero di incremento per il numero successivo. Per impostazione predefinita, è 1.
Il semplice esempio è ottenere una serie da 1 a 10. La formula sarà:
| =SEQUENZA(10) |
Per comprendere la funzione SEQUENZA in Excel 365 in dettaglio, leggi questo.
10. La funzione RANDARRAY
Questa è un'altra formula di matrice dinamica che restituisce una matrice di numeri casuali. È una combinazione della funzione CASUALE e CASUALE. Puoi ottenere numeri casuali frazionari o numeri interi. Puoi specificare il numero di numeri casuali che desideri. Anche righe e colonne in cui vuoi distribuire questi numeri.
La sintassi della funzione RANDARRAY è:
| =RANDARRAY([righe],[colonne],[min],[max],[intero]) |
Tutti gli argomenti in questa funzione sono opzionali. Per impostazione predefinita, funziona come funzione RAND.
[righe]:Il numero di numeri che vuoi in verticale (numero di righe che vuoi riempire).
[colonne]:Il numero di numeri che vuoi orizzontalmente (numero di colonne che vuoi riempire).
[min]:Il numero iniziale o il valore minimo dei numeri casuali.
[massimo]:L'intervallo massimo del numero.
[numero intero]:Impostalo su vero, se vuoi che i numeri casuali siano numeri interi. Per impostazione predefinita, è falso e restituisce numeri casuali frazionari.
La funzione seguente restituirà cinque numeri frazionari casuali per riga:
| =RANDARRAY(5) |
Leggi in dettaglio la funzione RANDARRAY qui.
11. La funzione CONCAT
In Excel 2016 e versioni precedenti, non è facile concatenare più di una cella o intervallo utilizzando una formula.
Su excel 2019 e 365 il problema si risolve con la funzione CONCAT. La funzione può accettare più celle, intervalli come argomenti.
La sintassi della funzione CONCAT è:
| =CONCAT(testo1,[testo2],… ) |
Testo 1 : Il testo1 può essere qualsiasi testo o intervallo che si desidera concatenare.
[testo2]: Questo è facoltativo. Questo può anche essere qualsiasi testo o intervallo.
Diciamo che se vuoi concatenare ogni cella nell'intervallo A2: A11, la formula sarà
| =CONCAT(A2:A11) |
Per esplorare in dettaglio la funzione CONCAT clicca qui.
12. La funzione TEXTJOIN
La funzione sopra concatena tutte le celle in un intervallo ma non concatena le celle con alcun delimitatore specificato. Diciamo che se stai preparando un file per il formato CSV di quanto dovrai concatenare le celle con la virgola. In tal caso le funzioni CONCATENATE e CONCAT, entrambe falliranno.
Qui la funzione TEXTJOIN fa miracoli e concatena i testi dati con il delimitatore dato.
| =TEXTJOIN(delimiter, ignore_empty_cells,text1,[text2],… ) |
Delimitatore:Questo è il delimitatore che vuoi usare come separatore tra i singoli testi. Può essere una virgola (,), un punto e virgola (;) o qualsiasi cosa, anche niente.
Ignore_empty_cells:Questa è una variabile binaria. Impostalo su TRUE se vuoi ignorare le celle vuote negli intervalli, altrimenti impostalo su FALSE per includere le celle vuote.
Testo 1:Questo è il testo a cui vuoi unirti. Possono essere singoli testi, celle o interi intervalli.
Diciamo che voglio concatenare l'intervallo A2: A11 con una virgola, ignorando le celle vuote.
| =TEXTJOIN(",",1,A2:A11) |
Per comprendere in dettaglio questa funzione, fare clic qui.
Questo articolo era solo un'introduzione alla nuova funzione di Excel 365 e 2019. Ho spiegato queste funzioni in dettaglio in articoli separati. È possibile fare clic sui collegamenti disponibili su ciascuna funzione nell'articolo per comprendere appieno la funzione. Ci sono altre funzioni come XLOOKUP che non sono ancora state rilasciate.
Se hai dei dubbi sugli argomenti di Excel o VBA, chiedi nella sezione commenti qui sotto. Dicci come possiamo migliorare. Apprezziamo il tuo suggerimento e siamo ansiosi di sentirti.
Crea funzione VBA per restituire l'array | Per restituire un array dalla funzione definita dall'utente, dobbiamo dichiararlo quando chiamiamo l'UDF.
Array in Excel Formul|Scopri cosa sono gli array in Excel.
Come creare una funzione definita dall'utente tramite VBA | Scopri come creare funzioni definite dall'utente in Excel
Utilizzo di una funzione definita dall'utente (UDF) da un'altra cartella di lavoro utilizzando VBA in Microsoft Excel | Usa la funzione definita dall'utente in un'altra cartella di lavoro di Excel
Restituisce i valori di errore dalle funzioni definite dall'utente utilizzando VBA in Microsoft Excel | Scopri come restituire valori di errore da una funzione definita dall'utente
Articoli popolari:
Dividi il foglio Excel in più file in base alla colonna utilizzando VBA | Questo codice VBA divide il foglio Excel in base a valori univoci in una colonna specificata. Scarica il file di lavoro.
Disattiva i messaggi di avviso utilizzando VBA in Microsoft Excel 2016 | Per disattivare i messaggi di avviso che interrompono il codice VBA in esecuzione, utilizziamo la classe Application.
Aggiungi e salva una nuova cartella di lavoro utilizzando VBA in Microsoft Excel 2016 | Per aggiungere e salvare cartelle di lavoro utilizzando VBA, utilizziamo la classe Workbooks. Cartelle di lavoro. Aggiungi aggiunge facilmente una nuova cartella di lavoro, tuttavia…