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. Allo stesso tempo, sostituisce anche CERCA.ORIZZ. Come esempio, XLOOKUP può guardare alla tua sinistra, per impostazione predefinita è 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 è abilitato solo per gli utenti del programma Insider. Chiunque può unisciti al programma Insiders per accedere alle ultime funzioni di Excel non appena 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 successiva 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 ostacolo in quanto XLOOKUP ti permette di scegliere 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.
Allo stesso tempo, 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
La possibilità di scegliere 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 specificato 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.
Perché, gli utenti cercano rapidamente come nascondere questo errore perché non è né corretto né utile. E, in ogni caso, ci sono alternative 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 formula successiva 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
Anche se non è 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. Inoltre c'è 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)
Nonostante questo, 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% spento perché spendi 64 non soddisfa i criteri per alcuno sconto.
Un altro vantaggio della funzione CERCA X è che non è necessario 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 ostacolo 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 più avanti 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.
In questo post sono stati utilizzati diversi esempi 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 nel post 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 è importante evidenziare 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);
