La nuova funzione XLOOKUP sarà disponibile in Office 365 a partire da novembre 2019. Joe McDaid del team di Excel ha progettato XLOOKUP per unificare le persone che utilizzano CERCA.VERT e le persone che utilizzano INDICE / CONFRONTA. Questa sezione discuterà i 12 vantaggi di XLOOKUP:
- La corrispondenza esatta è l'impostazione predefinita.
- Il terzo argomento basato su numeri interi di CERCA.VERT è ora un riferimento appropriato.
- IFNA è integrato per gestire i valori mancanti.
- XLOOKUP non ha problemi ad andare a sinistra.
- Trova la corrispondenza successiva più piccola o successiva più grande senza ordinare la tabella.
- XLOOKUP può fare HLOOKUP.
- Trova l'ultima corrispondenza effettuando una ricerca dal basso.
- I caratteri jolly sono "disattivati" per impostazione predefinita, ma puoi riattivarli.
- Restituisci tutti i 12 mesi in un'unica formula.
- Può restituire un riferimento di cella se XLOOKUP è accanto a due punti come XLOOKUP (); XLOOKUP ()
- Può fare una corrispondenza a due vie come può fare INDEX (, MATCH, MATCH).
- Può sommare tutte le ricerche in un'unica formula come potrebbe fare LOOKUP.
Ecco la sintassi: = XLOOKUP (Lookup_Value, Lookup_Array, Results_Array, (if_not_found), (match_mode), (search_mode)).
XLOOKUP Vantaggio 1: corrispondenza esatta per impostazione predefinita
Il 99% delle mie formule CERCA.VERT termina con, FALSO o 0 per indicare una corrispondenza esatta. Se utilizzi sempre la versione con corrispondenza esatta di CERCA.VERT, puoi iniziare a lasciare la modalità match_mode disattivata dalla funzione XLOOKUP.
Nella figura seguente, stai cercando W25-6 dalla cella A4. Vuoi cercare quell'articolo in L8: L35. Quando viene trovato, vuoi il prezzo corrispondente dalla colonna N. Non è necessario specificare False come match_mode perché XLOOKUP ha come impostazione predefinita una corrispondenza esatta.
XLOOKUP Vantaggio 2: Results_Array è un riferimento invece di un numero intero
Pensa alla formula CERCA.VERT che useresti prima di XLOOKUP. Il terzo argomento sarebbe stato un 3 per indicare che si desidera restituire la terza colonna. C'era sempre il pericolo che un collega incapace inserisse (o cancellasse) una colonna nella tua tabella. Con una colonna in più nella tabella, il CERCA.VERT che aveva restituito un prezzo inizierebbe a restituire una descrizione. Poiché XLOOKUP puntava a un riferimento di cella, la formula si riscrive per continuare a puntare al prezzo che si trova ora nella colonna O.
XLOOKUP Vantaggio 3: IFNA è integrato come argomento opzionale
Il temuto errore # N / D viene restituito quando il valore di ricerca non viene trovato nella tabella. In passato, per sostituire # N / A con qualcos'altro, dovresti usare SE.ERRORE o IFNA avvolto attorno a CERCA.VERT.
Grazie a un suggerimento di Rico sul mio canale YouTube, il team di Excel ha incorporato un quarto argomento opzionale per if_not_found. Se vuoi sostituire quegli errori # N / D con zero, aggiungi semplicemente 0 come quarto argomento. Oppure puoi usare del testo, come "Valore non trovato".
XLOOKUP Vantaggio 4: Nessun problema guardando a sinistra del campo chiave
CERCA.VERT non può guardare a sinistra del campo chiave senza ricorrere a CERCA.VERT (A4, SCEGLI ((1,2), G7: G34, F7: F34), 2, Falso). Con XLOOKUP, non c'è alcun problema ad avere il Results_array a sinistra del Lookup_array.
XLOOKUP Vantaggio 5: corrispondenza successiva più piccola o successiva più grande senza ordinamento
CERCA.VERT aveva un'opzione per cercare la corrispondenza esatta o solo un valore inferiore. È possibile lasciare il quarto argomento fuori da CERCA.VERT o modificare False in True. Affinché ciò funzionasse, la tabella di ricerca doveva essere ordinata in sequenza crescente.
Ma CERCA.VERT non era in grado di restituire la corrispondenza esatta o l'elemento successivo più grande. Per questo, dovevi passare all'uso di MATCH con -1 come match_mode e dovevi stare attento che la tabella di ricerca fosse ordinata in modo discendente.
Il quinto argomento facoltativo di XLOOKUP match_mode può cercare solo la corrispondenza esatta, uguale o appena inferiore, uguale o solo maggiore. Nota che i valori in XLOOKUP hanno più senso che in MATCH:
- -1 trova il valore uguale o appena inferiore
- 0 trova una corrispondenza esatta
- 1 trova il valore uguale o appena maggiore.
Ma la parte più sorprendente: la tabella di ricerca non deve essere ordinata e qualsiasi match_mode funzionerà.
Di seguito, un match_mode di -1 trova l'elemento successivo più piccolo.
Qui, un match_mode di 1, trova il veicolo necessario a seconda del numero di persone nel gruppo. Tieni presente che la tabella di ricerca non è ordinata per passeggeri e il nome del veicolo si trova a sinistra della chiave.
La tabella dice:
- L'autobus può contenere 64 persone
- L'auto può ospitare 4 persone
- Motorcyle contiene 1 persona
- Tour Van può ospitare 12 persone
- Van può ospitare 6 persone.
Come bonus, i dati sono ordinati per Veicolo (nella vecchia soluzione, utilizzando MATCH, la tabella avrebbe dovuto essere ordinata decrescente per Capacità. Inoltre: il Veicolo è a sinistra della Capacità.
XLOOKUP Vantaggio 6: XLOOKUP lateralmente sostituisce HLOOKUP
Lookup_array e results_array possono essere orizzontali con XLOOKUP, rendendo semplice la sostituzione di HLOOKUP.
XLOOKUP Vantaggio 7: ricerca dal basso per l'ultima partita
Ho un vecchio video su YouTube che risponde a una domanda di un allevamento di cavalli britannico. Avevano una flotta di veicoli. Ogni volta che un veicolo entrava per il carburante o il servizio, registravano veicolo, data e chilometraggio in un foglio di calcolo. Volevano trovare l'ultimo chilometraggio noto per ogni veicolo. Mentre l'era Excel-2017 MAXIFS potrebbe risolvere questo problema oggi, la soluzione molti anni fa era una formula arcana che utilizzava CERCA e prevedeva la divisione per zero.
Oggi, il sesto argomento opzionale di XLOOKUP ti consente di specificare che la ricerca deve iniziare dal fondo del set di dati.
Nota
Sebbene questo sia un grande miglioramento, ti consente solo di trovare la prima o l'ultima corrispondenza. Alcune persone speravano che questo ti avrebbe permesso di trovare la seconda o la terza corrispondenza, ma questa non è l'intenzione dell'argomento search_mode.
Attenzione
La figura sopra mostra che ci sono modalità di ricerca che utilizzano la vecchia ricerca binaria. Joe McDaid sconsiglia di utilizzarli. Innanzitutto, l'algoritmo di ricerca migliorato del 2018 è abbastanza veloce da non offrire alcun vantaggio significativo in termini di velocità. In secondo luogo, corri il rischio che un collega incapace ordina la tabella di ricerca e introduce risposte sbagliate.
XLOOKUP Vantaggio 8: I caratteri jolly sono "disattivati" per impostazione predefinita
La maggior parte delle persone non si rendeva conto che CERCA.VERT tratta l'asterisco, il punto interrogativo e la tilde come caratteri jolly come descritto in "# 51 Usa un carattere jolly in CERCA.VERT" a pagina 143. Con XLOOKUP, i caratteri jolly sono disattivati per impostazione predefinita. Se vuoi che XLOOKUP tratti questi caratteri come un carattere jolly, usa 2 come Match_Mode.
Vantaggio XLOOKUP 9: restituisci tutti i 12 mesi in un'unica formula!
Questo è davvero un vantaggio di Dynamic Arrays, ma è il mio motivo preferito per amare XLOOKUP. Quando devi restituire tutti i 12 mesi in una ricerca, una singola formula inserita in B6 con un return_array rettangolare restituirà più risultati. Questi risultati si riverseranno nelle celle adiacenti.
Nella figura seguente, una singola formula inserita in B7 restituisce tutte le 12 risposte mostrate in B7: M7.
XLOOKUP Vantaggio 10: può restituire un riferimento cellulare se adiacente al colon
Questo è complesso ma bellissimo. In passato, c'erano sette funzioni che passavano dalla restituzione di un valore di cella alla restituzione di un riferimento di cella se la funzione toccava i due punti. Per un esempio, vedere Usare A2: INDEX () come OFFSET non volatile. XLOOKUP è l'ottava funzione che offre questo comportamento, unendo SCEGLI, SE, IFS, INDICE, INDIRETTO, OFFSET e COMMUTA.
Considera la figura seguente. Qualcuno seleziona Cherry in E4 e Fig in E5. Vuoi una formula che sommerà tutto da B6 a B9.
Nella figura sopra, puoi vedere che un XLOOKUP di E4 restituirà il 15 dalla cella B6. Un XLOOKUP di E5 restituirà il 30 da B9. Tuttavia, se prendi le due funzioni XLOOKUP dalle celle D9 e D10 e le metti insieme con i due punti intermedi, il comportamento di XLOOKUP cambia. Invece di restituire 15, il primo XLOOKUP restituisce l'indirizzo di cella B6!
Per dimostrarlo, ho selezionato D7 e uso Formule, Valuta formula. Dopo aver premuto Evaluate due volte, la parte successiva da calcolare è XLOOKUP ("Cherry", A4: A29, B4: B29), come mostrato qui.
Premi di nuovo Valuta e sorprendentemente, la formula XLOOKUP restituisce $ B $ 6 invece dei 15 memorizzati in B6. Ciò accade perché immediatamente dopo questa formula XLOOKUP sono presenti i due punti.
Premere Valuta altre due volte e la formula provvisoria sarà = SOMMA (B6: B9).
Questo è un comportamento straordinario che la maggior parte delle persone non conosce. L'MVP di Excel Charles Williams mi dice che può essere attivato con uno qualsiasi di questi tre operatori accanto a XLOOKUP:
- Colon
- Spazio (operatore di intersezione)
- Virgola (operatore dell'Unione)
XLOOKUP Vantaggio 11: Corrispondenza bidirezionale come INDEX (, MATCH, MATCH)
Per tutti i miei amici di CERCA.VERT, le persone INDICE / CORRISPONDENZA stavano aspettando di vedere se XLOOKUP è in grado di gestire una corrispondenza a due vie. La grande notizia: può farcela. La cattiva notizia: la metodologia è leggermente diversa da quella che i fan di INDICE / PARTITA si aspetterebbero. Potrebbe essere un po 'sopra le loro teste. Ma sono sicuro che possono avvicinarsi a questo metodo.
Per una corrispondenza a due vie, si desidera trovare quale riga contiene il numero di conto A621 mostrato in J3. Quindi, XLOOKUP inizia abbastanza facilmente: = XLOOKUP (J3, A5: A15. Ma poi devi fornire un result_array. Puoi usare lo stesso trucco di XLOOKUP Benefit 9: Return All 12 months in a Single Formula above, but then usalo per restituire un vettore verticale. Un XLOOKUP interno cerca il mese J4 nelle intestazioni del mese in B4: G4. Il return_array è specificato come B5: G15. Il risultato è che XLOOKUP interno restituisce un array come quello mostrato in I10 : I20 di seguito. Poiché A621 si trova nella quinta cella di lookup_array e 104 si trova nella quinta cella di results_array, si ottiene la risposta corretta dalla formula. Di seguito, J6 mostra il vecchio modo. J7 restituisce il nuovo modo.
XLOOKUP Vantaggio 12: somma tutti i valori di ricerca in un'unica formula
L'antica funzione CERCA offriva due strani trucchi. Innanzitutto, se stai cercando di calcolare l'importo totale della spesa bonus da accumulare, potresti chiedere a CERCA di cercare tutti i valori in un'unica formula. Nell'immagine seguente, CERCA (C4: C14 sta eseguendo 11 ricerche. Ma la funzione RICERCA non offriva una corrispondenza esatta e richiedeva l'ordinamento della tabella di ricerca.
Con XLOOKUP, puoi specificare un intervallo come lookup_value e XLOOKUP restituirà tutte le risposte. Il vantaggio è che XLOOKUP può fare corrispondenze esatte.
Suggerimento bonus: che ne dici di una RICERCA distorta?
L'MVP di Excel Mike Girvin mostra spesso un trucco della funzione CERCA in cui Lookup_Vector è verticale e Result_Vector è orizzontale. XLOOKUP non supporterà nativamente questo trucco. Ma, se imbroglia un po 'e avvolgi il result_array nella funzione TRANSPOSE, puoi gestire una ricerca contorta.
