La funzione CERCA.X è esclusiva del programma interno di Office 365. La funzione CERCA ha molte funzionalità che superano molti dei punti deboli della funzione CERCA.VERT e CERCA.ORIZZ, ma purtroppo non è disponibile per ora. Ma non preoccuparti, possiamo creare una funzione XLOOKUP che funziona esattamente come la prossima funzione XLOOKUP MS Excel. Aggiungeremo funzionalità ad esso una per una.
Codice VBA della funzione XLOOKUP
La seguente funzione di ricerca UDF risolverà molti problemi. Copialo o scarica il componente aggiuntivo xl sotto il file qui sotto.
Funzione XLOOKUP(lk As Variant, lCol As Range, rCol As Range) XLOOKUP = WorksheetFunction.Index(rCol, WorksheetFunction.Match(lk, lCol, 0)) End Function
Spiegazione:
Il codice sopra è solo INDEX-MATCH di base utilizzato in VBA. Questo semplifica molte delle cose che un nuovo utente deve affrontare. If risolve la complessità della funzione INDEX-MATCH e usa solo tre argomenti. Puoi copiarlo nel tuo file excel o scaricare il file .xlam di seguito e installarlo come componente aggiuntivo excel. Se non sai come creare e utilizzare un componente aggiuntivo, fai clic qui, ti aiuterà.
Componente aggiuntivo XLOOKUP
vediamo come funziona sul foglio di lavoro excel.
Sintassi di XLOOKUP
| =XLOOKUP(valore_ricerca, matrice_ricerca, matrice_risultato) |
valore di ricerca: Questo è il valore che vuoi cercare nel lookup_array.
ricerca_array: Si tratta di un intervallo unidimensionale in cui si desidera ricercare il valore di ricerca.
array_risultato: È anche una gamma unidimensionale. Questo è l'intervallo da cui si desidera recuperare il valore.
Vediamo questa funzione XLOOKUP in azione.
XLOOKUP Esempi:
Qui, ho una tabella di dati in Excel. Esploriamo alcune funzionalità utilizzando questa tabella di dati.
Funzionalità 1. esatto Ricerca sul lato sinistro e destro del valore di ricerca.
Come sappiamo, la funzione CERCA.VERT di Excel non può recuperare i valori a sinistra del valore di ricerca. Per questo, devi usare la complessa combinazione INDICE-MATCH. Ma non più.
Supponendo di dover recuperare tutte le informazioni disponibili nella tabella di alcuni numeri di rotolo. In tal caso, dovrai recuperare anche la regione, che si trova a sinistra della colonna del numero di rotolo.
Scrivi questa formula, I2:
| =XCERCA(H2,$B$2:$B$14,$A$2:$A$14) |
Otteniamo il risultato Nord per il numero di rotolo 112. Copia o trascina verso il basso la formula nelle celle sottostanti per riempirle con le rispettive regioni.
Come funziona?
Il meccanismo è semplice. Questa funzione cerca il valore di ricerca in lookup_array e restituisce l'indice di una prima corrispondenza esatta. Quindi usa quell'indice per recuperare il valore da array_risultato. Questa funzione funziona perfettamente con gli intervalli denominati.
Allo stesso modo usa questa formula per recuperare il valore da ogni colonna.
Funzionalità 2. Esatto Orizzontale Ricerca sopra e sotto il valore di ricerca.
XLOOKUP funziona anche come una funzione esatta di HLOOKUP. La funzione CERCA.VERT ha la stessa limitazione di CERCA.VERT. Non può recuperare il valore da sopra il valore di ricerca. Ma XLOOKUP non funziona solo come HLOOKUP, ma supera anche questa debolezza. Vediamo come.
Ipoteticamente, se vuoi confrontare due record. Il record di ricerca che hai già. Il record con cui si desidera confrontare è al di sopra di lookup_range. Usa questa formula in quel caso.
| =XCERCA.(H7,$A$9:$E$9,$A$2:$E$2) |
trascina verso il basso la formula e hai l'intero record di confronto delle righe.
Funzionalità 3. Non è necessario il numero di colonna e la corrispondenza esatta predefinita.
Quando usi la funzione CERCA.VERT, devi indicare il numero di colonna da cui vuoi recuperare i valori. Per questo, devi contare le colonne o usare alcuni trucchi, prendi l'aiuto di altre funzioni. Con questo UDF XLOOKUP, non è necessario farlo.
Se stai usando VLOOKUP solo per recuperare un valore da una colonna o per verificare se esiste un valore nella colonna, questa è la soluzione migliore secondo me.
Funzionalità 4. Sostituisce la funzione INDEX-MATCH, VLOOKUP, HLOOKUP
Per compiti semplici, la nostra funzione XLOOKUP sostituisce le funzioni sopra menzionate.
Limitazioni di XLOOKUP:
Quando si tratta di formule complesse, come VLOOKUP con Dynamic Col Index dove VLOOKUP identifica la colonna di ricerca con le intestazioni, questo XLOOKUP avrà esito negativo.
Un'altra limitazione è che se devi cercare più colonne o righe casuali dalla tabella, questa funzione sarà inutile poiché devi scrivere questa formula ancora e ancora. Questo può essere superato utilizzando intervalli denominati.
Per ora, non abbiamo aggiunto la funzionalità approssimativa, quindi ovviamente non puoi ottenere la corrispondenza approssimativa. Lo aggiungeremo troppo presto.
Se la funzione CERCA.X non riesce a trovare il valore di ricerca, restituirà l'errore #VALORE e non #N/D.
Quindi sì ragazzi, ecco come usate XLOOKUP per recuperare, cercare e convalidare i valori nelle tabelle excel. È possibile utilizzare questa funzione definita dall'utente per una ricerca senza problemi a sinistra o in alto del valore di ricerca. Se hai ancora dubbi o requisiti specifici relativi a questa funzione o EXCEL 2010/2013/2016/2019/365 o query relative a VBA, chiedilo nella sezione commenti qui sotto. Riceverai sicuramente una risposta.
Crea funzione VBA per restituire l'array | Per restituire un array dalla funzione definita dall'utente, dobbiamo dichiararlo quando chiamiamo l'UDF.
Array in Excel Formul|Scopri cosa sono gli array in Excel.
Come creare una funzione definita dall'utente tramite VBA | Scopri come creare funzioni definite dall'utente in Excel
Utilizzo di una funzione definita dall'utente (UDF) da un'altra cartella di lavoro utilizzando VBA in Microsoft Excel | Usa la funzione definita dall'utente in un'altra cartella di lavoro di Excel
Restituisce i valori di errore dalle funzioni definite dall'utente utilizzando VBA in Microsoft Excel | Scopri come restituire valori di errore da una funzione definita dall'utente
Articoli popolari:
Dividi il foglio Excel in più file in base alla colonna utilizzando VBA | Questo codice VBA divide il foglio Excel in base a valori univoci in una colonna specificata. Scarica il file di lavoro.
Disattiva i messaggi di avviso utilizzando VBA in Microsoft Excel 2016 | Per disattivare i messaggi di avviso che interrompono il codice VBA in esecuzione, utilizziamo la classe Application.
Aggiungi e salva una nuova cartella di lavoro utilizzando VBA in Microsoft Excel 2016 | Per aggiungere e salvare cartelle di lavoro utilizzando VBA, utilizziamo la classe Workbooks. Cartelle di lavoro. Aggiungi aggiunge facilmente una nuova cartella di lavoro, tuttavia…