L'uccisore CERCA.VERT: XLOOKUP debutta con Excel - Suggerimenti per Excel

Lo scopo di XLOOKUP è trovare un risultato, trovarlo rapidamente e restituire la risposta al foglio di calcolo.

Joe McDaid, Project Manager di Excel

A mezzogiorno di oggi, Microsoft ha iniziato a rilasciare lentamente la funzione XLOOKUP ad alcuni addetti ai lavori di Office 365. I principali vantaggi di XLOOKUP:

  • Riesco a trovare l'ultima partita!
  • Può guardare a sinistra!
  • Il valore predefinito è una corrispondenza esatta (a differenza di CERCA.VERT che viene impostato su Vero per il 4 ° argomento)
  • Di default non supporta i caratteri jolly, ma è possibile consentire esplicitamente i caratteri jolly se li si desidera
  • Ha tutti i miglioramenti della velocità rilasciati a CERCA.VERT nel 2018
  • Non si basa più sul numero di colonna, quindi non si interromperà se qualcuno inserisce una colonna al centro della tabella di ricerca
  • Miglioramento delle prestazioni perché si specificano solo due colonne anziché l'intera tabella di ricerca
  • XLOOKUP restituisce un intervallo invece di CERCA.VERT restituendo un valore

Presentazione di XLOOKUP

La sintassi XLOOKUP è:

XLOOKUP(Lookup_Value, Lookup_Array, Results_Array, (Match_Mode), (Search_Mode))

Le scelte per Match_Mode sono:

  • 0 Corrispondenza esatta (impostazione predefinita)
  • -1 Corrispondenza esatta o successivo più piccolo
  • 1 Corrispondenza esatta o Successivo più grande
  • 2 Corrispondenza con caratteri jolly

Le scelte per Search_Mode sono

  • 1 dal primo all'ultimo (predefinito)
  • -1 dall'ultimo al primo
  • 2 ricerca binaria, dalla prima all'ultima (richiede che lookup_array sia ordinato)
  • -2 ricerca binaria, dall'ultimo al primo (richiede che lookup_array sia ordinato)

Sostituzione di un semplice CERCA.VERT

Hai una tabella di ricerca in F3: H30. La tabella di ricerca non è ordinata.

Tabella di ricerca

Vuoi trovare la descrizione dalla tabella.

Con un CERCA.VERT faresti =VLOOKUP(A2,$F$3:$H$30,3,False). L'equivalente XLOOKUP sarebbe: =XLOOKUP(A2,$F$3:$F$30,$H$3:$H$30).

In XLOOKUP, A2 è lo stesso di VLOOKUP.

F3: F30 è l'array di ricerca.

H3: H30 è l'array dei risultati.

Non c'è bisogno di False alla fine perché XLOOKUP ha come impostazione predefinita una corrispondenza esatta!

XLOOKUP Risultato semplice

Un vantaggio: se qualcuno inserisce una nuova colonna nella tabella di ricerca, il tuo vecchio CERCA.VERT restituirà il prezzo invece della descrizione. XLOOKUP sarà regolare e mantenere indicando descrizione: =XLOOKUP(A2,$F$3:$F$30,$I$3:$I$30).

XLOOKUP Inserisci colonna

Trova l'ultima partita

XLOOKUP ti consente di iniziare la ricerca nella parte inferiore del set di dati. Questo è ottimo per trovare l'ultima corrispondenza in un set di dati.

XLOOKUP Cerca dal basso

Guarda a sinistra

Come CERCA e INDICE / CORRISPONDENZA, non c'è nessun problema a guardare a sinistra del tasto con XLOOKUP.

Dove avresti usato in =INDEX($E$3:$E$30,MATCH(A2,$F$3:$F$30,0))precedenza, ora puoi usare=XLOOKUP(A2,$F$3:$F$30,$E$3:$E$30)

XLOOKUP a sinistra

Miglioramenti alla velocità di XLOOKUP

Nell'esempio precedente, CERCA.VERT deve ricalcolare se qualcosa nella tabella di ricerca cambia. Immagina se la tua tabella includesse 12 colonne. Con XLOOKUP, la formula verrà ricalcolata solo se qualcosa nella matrice di ricerca o nella matrice dei risultati cambia.

Alla fine del 2018, l'algoritmo CERCA.VERT è cambiato per ricerche lineari più veloci. XLOOKUP mantiene gli stessi miglioramenti di velocità. Ciò rende le opzioni di ricerca lineare e binaria quasi identiche. Joe McDaid afferma che non vi è alcun vantaggio significativo nell'utilizzo delle opzioni di ricerca binaria in Search_Mode.

Supporto con caratteri jolly, ma solo quando lo richiedi

Ogni CERCA.VERT supporta i caratteri jolly, rendendo difficile la ricerca di Wal * Mart. Per impostazione predefinita, XLOOKUP non utilizzerà i caratteri jolly. Se desideri il supporto dei caratteri jolly, puoi specificare 2 come Match_Mode.

Più colonne di XLOOKUP

Devi fare 12 colonne di XLOOKUP? Potresti farlo una colonna alla volta …

Più colonne di XLOOKUP

Oppure, grazie a Dynamic Arrays, restituisci tutte e 12 le colonne contemporaneamente …

Restituisci tutte e 12 le colonne contemporaneamente con le matrici dinamiche

Le ricerche approssimative non devono più essere ordinate

Se è necessario trovare il valore appena inferiore o appena maggiore del valore di ricerca, le tabelle non devono più essere ordinate.

XLOOKUP più piccolo

O per trovare il valore successivo più grande:

XLOOKUP Più grande

L'unico svantaggio: i tuoi colleghi non lo avranno (ancora)

A causa della nuova politica di Flighting, solo una piccola percentuale di Office Insider ha oggi la funzionalità XLOOKUP. Potrebbe volerci un po 'di tempo prima che la funzione sia ampiamente disponibile e anche in questo caso richiederà un abbonamento a Office 365. (Gli array dinamici sono usciti da settembre 2018 e non sono ancora stati implementati nella disponibilità generale.)

Guarda un video

Articoli interessanti...