Il nuovo XLOOKUP di Excel sostituirà VLOOKUP, fornendo un potente sostituto a una delle funzioni di Excel più popolari. Questa nuova funzionalità risolve alcune delle limitazioni di CERCA.VERT e ha funzionalità aggiuntive. Questo è quello che devi sapere.
Cos'è XLOOKUP?
La nuova funzione XLOOKUP ha correzioni per alcune delle maggiori limitazioni di VLOOKUP. Cosa c'è di più, sostituisce anche CERCA.ORIZZ. Ad esempio, XLOOKUP può guardare alla tua sinistra, il valore predefinito è una corrispondenza esatta e ti consente di specificare un intervallo di celle invece di un numero di colonna. CERCA.VERT non è così facile da usare o così versatile. Ti mostreremo come funziona il tutto.
Per il momento, XLOOKUP è disponibile solo per gli utenti del programma Insider. Chiunque può unisciti al programma Insiders per accedere alle ultime funzionalità di Excel non appena sono disponibili. Microsoft inizierà presto a distribuirlo per tutti gli utenti di Office 365.
Come utilizzare la funzione XLOOKUP
Entriamo subito in un esempio di XLOOKUP in azione. Prendi i dati di esempio qui sotto. Vogliamo restituire il dipartimento della colonna F per ogni ID nella colonna A.
Questo è un classico esempio di ricerca con corrispondenza esatta. La funzione XLOOKUP richiede solo tre informazioni.
L'immagine seguente mostra XLOOKUP con sei argomenti, ma solo i primi tre sono necessari per una corrispondenza esatta. Quindi concentriamoci su di loro:
- Valore di ricerca: Che cosa sta cercando.
- Lookup_array: Dove guardare?.
- Return_array: l'intervallo che contiene il valore da restituire.
La seguente formula funzionerà per questo esempio: =XLOOKUP(A2,$E$2:$E$8,$F$2:$F$8)
Esploriamo ora un paio di vantaggi che XLOOKUP ha su VLOOKUP qui.
Niente più numero di indice di colonna
Il famigerato terzo argomento di CERCA.VERT era quello di specificare il numero di colonna di informazioni da restituire da un array di tabelle. Questo non è più un problema perché XLOOKUP ti permette di selezionare l'intervallo da cui tornare (colonna F in questo esempio).
E non dimenticare, XLOOKUP può vedere i dati rimanenti della cella selezionata, a differenza di CERCA.VERT. Maggiori informazioni su questo di seguito.
Cosa c'è di più, non hai più il problema di una formula rotta quando vengono inserite nuove colonne. Se è successo nel tuo foglio di calcolo, l'intervallo di ritorno verrebbe regolato automaticamente.
La corrispondenza esatta è l'impostazione predefinita
Era sempre fonte di confusione quando si imparava CERCA.VERT il motivo per cui dovevi specificare una corrispondenza esatta.
fortunatamente, XLOOKUP è impostato su una corrispondenza esatta, il motivo molto più comune per utilizzare una formula di ricerca). Ciò riduce la necessità di rispondere a quel quinto argomento e garantisce un minor numero di errori da parte degli utenti nuovi alla formula..
Quindi, In sintesi, XLOOKUP fa meno domande di VLOOKUP, è più facile da usare ed è anche più durevole.
XLOOKUP può guardare a sinistra
Essere in grado di selezionare un intervallo di ricerca rende XLOOKUP più versatile di VLOOKUP. Con XLOOKUP, l'ordine delle colonne della tabella non ha importanza.
VLOOKUP è stato limitato cercando nella colonna più a sinistra di una tabella e quindi tornando da un numero specifico di colonne a destra.
Nel seguente esempio, dobbiamo cercare un id (colonna E) e restituire il nome della persona (colonna D).
La seguente formula può ottenere questo: =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8)
Cosa fare se non lo trovi
Gli utenti delle funzioni di ricerca hanno molta familiarità con il messaggio di errore # n / Salutali quando la loro funzione CERCA.VERT o CONFRONTA non riesce a trovare ciò di cui hanno bisogno. E spesso c'è una ragione logica per questo.
Perciò, gli utenti cercano rapidamente come nascondere questo errore perché non è né corretto né utile. E, Certo, ci sono modi per farlo.
XLOOKUP viene fornito con il proprio argomento “se non trovato” integrato per gestire tali errori. Vediamolo in azione con l'esempio sopra, ma con un ID errato.
La seguente formula visualizzerà il testo “ID errato” invece del messaggio di errore: =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8,"Incorrect ID")
Utilizzo di XLOOKUP per una ricerca per intervallo
Sebbene non sia così comune come la corrispondenza esatta, un uso molto efficace di una formula di ricerca è cercare un valore in intervalli. Prendi il seguente esempio. Vogliamo restituire lo sconto in base all'importo speso.
Questa volta non stiamo cercando un valore specifico. Abbiamo bisogno di sapere dove si trovano i valori della colonna B all'interno degli intervalli della colonna E. Questo determinerà lo sconto ottenuto.
XLOOKUP ha un quinto argomento opzionale (ricordare, il valore predefinito è la corrispondenza esatta) chiamato modalità di corrispondenza.
Puoi vedere che XLOOKUP ha maggiori capacità con le corrispondenze fuzzy rispetto a VLOOKUP.
C'è un'opzione per trovare la corrispondenza più vicina a meno di (-1) o più vicino maggiore di (1) il valore cercato. C'è anche un'opzione per usare i caratteri jolly (2) Che cosa? o la *. Questa impostazione non è abilitata per impostazione predefinita come con CERCA.VERT.
La formula in questo esempio restituisce il valore inferiore più vicino al valore cercato se non viene trovata una corrispondenza esatta: =XLOOKUP(B2,$E$3:$E$7,$F$3:$F$7,,-1)
tuttavia, c'è un errore nella cella C7 in cui viene restituito l'errore # n / UN (l'argomento 'se non trovato' non è stato utilizzato). Questo dovrebbe essere tornato a 0% sconto perché spendere 64 non soddisfa i criteri per eventuali sconti.
Un altro vantaggio della funzione CERCA X è che non richiede che l'intervallo di ricerca sia in ordine crescente come fa VLOOKUP..
Inserisci una nuova riga nella parte inferiore della tabella di ricerca e quindi apri la formula. Espandi l'intervallo utilizzato facendo clic e trascinando gli angoli.
La formula corregge immediatamente l'errore. Non è un problema avere il “0” nella parte inferiore della gamma.
Personalmente, ordinerebbe comunque la tabella in base alla colonna di ricerca. avere un “0” in fondo impazzirei. Ma il fatto che la formula non sia stata infranta è geniale.
XLOOKUP sovrascrive anche la funzione HLOOKUP
Come accennato, la funzione XLOOKUP è anche qui per sostituire HLOOKUP. Una funzione per sostituirne due. Eccellente!
La funzione CERCA.ORIZZ è la ricerca orizzontale, usato per cercare tra le righe.
Non è così conosciuto come suo fratello CERCA.VERT, ma è utile per esempi come quello mostrato di seguito, dove le intestazioni sono nella colonna A e i dati sono nelle righe 4 e 5.
XLOOKUP può guardare in entrambe le direzioni: colonne in basso e anche lungo le righe. Non abbiamo più bisogno di due funzioni diverse.
In questo esempio, la formula viene utilizzata per restituire il valore di vendita relativo al nome nella cella A2. Cerca lungo la linea 4 per trovare il nome e restituire il valore della riga 5: =XLOOKUP(A2,B4:E4,B5:E5)
XLOOKUP può guardare dal basso verso l'alto
Generalmente, devi cercare un elenco per trovare il primo (spesso unico) aspetto di un valore. XLOOKUP ha un sesto argomento chiamato modalità di ricerca. Questo ci permette di cambiare la ricerca per iniziare dal basso e cercare un elenco per trovare l'ultima occorrenza di un valore.
Nel seguente esempio, vorremmo trovare il livello delle scorte di ciascun prodotto nella colonna A.
La tabella di ricerca è ordinata per data e ci sono più controlli delle scorte per prodotto. Vogliamo restituire il livello delle scorte dall'ultima volta che è stato controllato (ultima occorrenza dell'ID prodotto).
Il sesto argomento della funzione CERCA X offre quattro opzioni. Siamo interessati a utilizzare l'opzione “Cerca dall'ultimo al primo”.
La formula completa è mostrata qui: =XLOOKUP(A2,$E$2:$E$9,$F$2:$F$9,,,-1)
In questa formula, il quarto e il quinto argomento sono stati ignorati. È facoltativo e volevamo il valore predefinito di una corrispondenza esatta.
Arrotondamento
La funzione XLOOKUP è la l'atteso successore alle funzioni CERCA.VERT e CERCA.VERT.
Diversi esempi sono stati utilizzati in questo articolo per dimostrare i vantaggi di XLOOKUP. Uno di questi è che XLOOKUP può essere utilizzato sui fogli, cartelle di lavoro e anche con tabelle. Gli esempi sono stati mantenuti semplici nell'articolo per aiutare la nostra comprensione..
Perché array dinamici che vengono inseriti in Excel presto, può anche restituire un intervallo di valori. Decisamente, questo è qualcosa che vale la pena esplorare ulteriormente.
I giorni di CERCA.VERT sono contati. XLOOKUP è qui e presto sarà la formula di ricerca de facto.
impostaTimeout(funzione(){
!funzione(F,B,e,v,n,T,S)
{Se(f.fbq)Restituzione;n=f.fbq=funzione(){n.callMethod?
n.callMethod.apply(n,argomenti):n.queue.push(argomenti)};
Se(!f._fbq)f._fbq = n;n.push=n;n.loaded=!0;n.version='2.0′;
n.coda=[];t=b.createElement(e);t.async=!0;
t.src=v;s=b.getElementsByTagName(e)[0];
s.parentNode.insertBefore(T,S) } (window, documento,'copione',
'https://connect.facebook.net/en_US/fbevents.js');
fbq('dentro', '335401813750447');
fbq('traccia', 'Visualizzazione della pagina');
},3000);
