Formule di Office 365/Excel 2016 e versioni successive
Questa unità introduce tre nuove formule disponibili in Office 365/Excel 2016+ denominata XLOOKUP(), FILTER() e LET(). Un altro modo per scoprire nuove funzionalità e funzionalità nell'applicazione consiste nel partecipare al programma Microsoft Office Insiders (collegato nella sezione riferimenti alla fine di questo modulo).
CERCA.X()
XLOOKUP() è una nuova versione più potente di VLOOKUP(). È più semplice, rapida e flessibile.
XLOOKUP() offre risultati migliori di VLOOKUP() perché consente di:
Cercare colonne e righe combinano VLOOKUP() e HLOOKUP() per una ricerca più completa.
Cercare colonne nei modelli INDEX() MATCH() di sostituzione sinistra, consentendo di usare una combinazione ottimale per la ricerca.
La formula è più affidabile in quanto non si interrompe quando le colonne vengono aggiunte/eliminate.
XLOOKUP() include una sintassi con tre parametri obbligatori. La funzione esegue una corrispondenza esatta per impostazione predefinita.
XLOOKUP() ha le caratteristiche seguenti:
Restituisce un valore da una colonna specificata in base a un valore in un'altra colonna
Restituisce un valore diverso se non viene trovato alcun risultato
Cerca dall'alto o dal basso
XLOOKUP() è costituita da sei parametri, di cui tre facoltativi:
lookup_value: parametro usato per definire il valore che si vuole trovare
lookup_array: parametro array usato per specificare la colonna in cui trovare il valore
return_array: parametro array usato per definire la colonna da cui restituire il valore
if_not_found: se non viene trovata alcuna corrispondenza, restituire questo valore facoltativo
match_mode: parametro facoltativo per specificare la corrispondenza esatta, prima sopra/sotto o la ricerca con caratteri jolly
search_mode: specificare la ricerca dall'alto o dal basso con questo parametro facoltativo
Nell'esempio precedente del set di dati notare la formula XLOOKUP() a destra nella black box che mostra i risultati restituiti. I tre esempi rispondono alle domande seguenti:
Trova prodotto per ID: formula che mostra la ricerca dell'ID prodotto per prodotto = 109 in cui i risultati del prodotto vengono trovati in una colonna a destra della colonna ID prodotto.
Trova City by ZIP: la formula di esempio illustra la ricerca di City per ZIP = 21658, che vengono risultati in una colonna a sinistra della colonna ZIP.
Trova l'ultimo prodotto per City: questa formula illustra l'uso dei parametri facoltativi "Nessun risultato trovato" se non sono presenti risultati, corrispondenza esatta e -1 indica di cercare dalla parte inferiore alla parte superiore della tabella dei dati.
FILTRO()
FILTER() è una nuova funzione matrice. L'aggiunta della formula a una singola cella restituisce un subset della tabella e gli altri valori vengono vuoti nelle altre celle all'interno del risultato. FILTER() restituisce righe di dati e consente più condizioni usando la logica e/o.
FILTER() ha le caratteristiche seguenti:
Restituisce più risultati di corrispondenza per uno o più valori di ricerca
Filtra i dati senza necessità di [refresh]{.underline}
Può essere annidato all'interno di altre funzioni di Excel
I dettagli seguenti illustrano i tre parametri inclusi in FILTER():
array: parametro usato per specificare un intervallo di colonne e righe da filtrare
include: parametro usato per fornire criteri di regola di filtro
if_empty: valore del parametro facoltativo da restituire se nessuna riga soddisfa le condizioni
L'esempio precedente del set di dati mostra la formula FILTER() nella black box con i risultati restituiti. Si noti che usa una tabella anziché un intervallo. È consigliabile usare sempre una tabella, quando possibile. L'esempio precedente filtra la tabella SalesTable, dove Area = Occidentale e restituisce tutte le righe corrispondenti all'interno del risultato.
In questo esempio viene usato lo stesso set di dati, ma vengono applicati tre filtri alla tabella. La formula filtra la tabella nei criteri seguenti. Tutti i criteri devono essere soddisfatti per l'inserimento della riga.
Prodotto = Palma UM-01
Area = Occidentale
-Revenue = Maggiore di USD 1.215.00
La formula usa la funzione moltiplica perché un confronto logico genererà zero (0) per false o uno (1) per true. Se tutte le condizioni sono TRUE, 1 * 1 * 1 = 1 . Tuttavia, se qualsiasi condizione è zero (0) o false, l'intera logica è false.
Un asterisco (*) viene usato per le condizioni AND e il segno più (+) viene usato per le condizioni OR .
LET()
La funzione LET() offre una notevole flessibilità per calcoli complessi e offre un modo più semplice per il digest delle diverse parti della formula. Combina la possibilità di archiviare calcoli e valori che usano variabili con la sintassi della formula nativa di Excel.
Le variabili vengono usate per assegnare un nome a un valore o a un calcolo. Queste variabili vengono usate per richiamare la sintassi senza dover riscrivere ripetutamente la formula. È possibile definire fino a 126 variabili diverse nella funzione, ma almeno è necessario disporre dei tre componenti (variabile, valore di variabile e calcolo). È anche possibile sfruttare altre funzioni matrice, ad esempio FILTER(), all'interno della funzione LET(). L'esempio seguente si basa sull'esempio FILTER() riportato in precedenza, ma ora con le variabili assegnate.
Nello screenshot precedente i numeri da uno a quattro sono variabili e definizioni. L'ultima istruzione è il calcolo che usa le variabili.
Intervallo prodotto =intervallo di colonne del prodotto
Product = Prodotto su cui filtrare
Intervallo area = intervallo di colonne dell'area
Area = Area in cui filtrare
Filtro = filtrare la tabella in base a Prodotto e Area