Come eseguire una ricerca VLOOKUP di numeri memorizzati come testo in Excel?
Quando si utilizza VLOOKUP in Excel, incontrare formati non corrispondenti — in particolare, quando un valore di ricerca è memorizzato come testo mentre la colonna di ricerca contiene numeri, o viceversa — può portare a errori o fallimenti nella ricerca. Questa mancata corrispondenza di formattazione è un problema comune, specialmente quando i dati provengono da fonti esterne, sono importati o quando si lavora con grandi set di dati collaborativi. Risolvere queste discrepanze è essenziale per garantire che VLOOKUP funzioni come previsto e vi aiuti a recuperare le informazioni corrette. Questa guida passo-passo introduce diverse soluzioni pratiche per affrontare queste incongruenze di formattazione, garantendo ricerche affidabili e accurate indipendentemente da come i numeri sono memorizzati nel vostro foglio di lavoro.
Questo articolo dimostrerà modi efficaci per gestire tali errori, inclusi aggiustamenti di formule, strumenti integrati di Excel e automazione VBA per l'elaborazione in blocco o automatizzata. Discuteremo anche i vantaggi e le considerazioni di ciascun metodo, aiutandovi a scegliere l'approccio più adatto al vostro scenario.
- Ricerca VLOOKUP di numeri memorizzati come testo con formule
- Correzione rapida delle mancate corrispondenze di formato con Kutools per Excel
- Macro VBA: Standardizzare i formati prima di VLOOKUP
- Altri metodi integrati di Excel: Usa 'Testo in colonne' per correggere i formati dei dati
Ricerca VLOOKUP di numeri memorizzati come testo con formule
Se i dati della vostra ricerca includono numeri memorizzati come testo in un posto e numeri effettivi in un altro, VLOOKUP potrebbe non riuscire a trovare corrispondenze a causa di questa inconsistenza di formato. Una delle soluzioni più dirette è usare formule Excel che convertono il valore di ricerca o la colonna di ricerca in un formato coerente al volo. Questo approccio funziona bene nella maggior parte delle operazioni del foglio di lavoro, è facile da applicare e mantiene i vostri dati originali invariati.
Ad esempio, se il vostro valore di ricerca è memorizzato come testo, mentre il campo corrispondente nella tabella è formattato come numero, potete utilizzare la funzione VALUE per convertire il testo in un numero all'interno della formula VLOOKUP.
Inserite la seguente formula in una cella vuota dove desiderate visualizzare il risultato:
=VLOOKUP(VALUE(G1),A2:D15,2,FALSE)Dopo aver inserito la formula, premete il tasto Invio per recuperare il valore corrispondente ai vostri criteri, come illustrato nello screenshot sottostante:
Spiegazione dei parametri e suggerimenti:
- G1: La cella contenente il valore che desiderate cercare (può essere sia testo che numero).
- A2:D15: L'intervallo della vostra tabella dati che include la colonna di ricerca e le colonne contenenti le informazioni che desiderate restituire.
- 2: Il numero della colonna (dalla colonna più a sinistra dell'intervallo della tabella) per il risultato che desiderate restituire.
Prestare attenzione a controllare gli spazi iniziali/finali nei valori di ricerca, poiché questi possono anche causare errori di ricerca. Considerate di combinare la funzione TRIM se i vostri dati potrebbero contenere spazi extra.
Se il valore di ricerca è un numero reale (formato numero), ma il campo corrispondente nella tabella è memorizzato come testo, è necessario convertire il numero in testo prima di eseguire la ricerca. La funzione TEXT è adatta per questo scenario:
=VLOOKUP(TEXT(G1,0),A2:D15,2,FALSE)Inserite questo nella vostra cella target, premete Invio e il risultato corretto sarà restituito come mostrato di seguito:
Qui, il codice di formato numero “0” all'interno della funzione TEXT assicura che il vostro numero sia convertito in un valore di testo semplice prima dell'abbinamento.
Se non siete sicuri dei possibili formati dei vostri valori di ricerca o vi aspettate che entrambi testo e numeri si verifichino nella vostra colonna di ricerca, potete nidificare entrambi gli approcci utilizzando la funzione IFERROR per gestire tutte le possibilità senza problemi:
=IFERROR(VLOOKUP(VALUE(G1),A2:D15,2,0),VLOOKUP(TEXT(G1,0),A2:D15,2,0))Inserite questa formula nella vostra cella di risultato. Proverà prima la ricerca convertendo il vostro valore in un numero; se ciò fallisce (ad esempio, se il valore non può essere forzato in un numero), proverà quindi a convertire il vostro valore in testo ed eseguirà nuovamente la ricerca. Questo è particolarmente utile in set di dati con formati misti o in file condivisi dove gli standard di inserimento dati non sono uniformi.
Dopo aver inserito una qualsiasi delle formule sopra, ricordatevi di copiare la formula nelle celle adiacenti se è necessario applicarla a più valori di ricerca — selezionate semplicemente la cella, trascinate la maniglia di riempimento verso il basso o utilizzate Ctrl+C e Ctrl+V se necessario. Per tabelle di grandi dimensioni, l'uso di queste formule aiuta a garantire abbinamenti affidabili senza alterare il vostro database originale.
Questo metodo fornisce una soluzione flessibile e universalmente applicabile per la maggior parte delle ricerche basate su fogli di lavoro. Tuttavia, per set di dati molto grandi o quando è necessario elaborare molti record automaticamente, potreste considerare l'uso di strumenti di automazione come VBA per una maggiore efficienza.
Correzione rapida delle mancate corrispondenze di formato con Kutools per Excel
Se preferite una soluzione più veloce e senza formule, Kutools per Excel offre uno strumento user-friendly chiamato Converti tra Testo e Numero. Questa funzionalità vi permette di convertire numeri memorizzati come testo in numeri reali — o viceversa — con pochi clic. È particolarmente utile quando si risolvono problemi di formato prima di eseguire ricerche come VLOOKUP o MATCH.
Dopo aver installato Kutools per Excel, procedete come segue.
- Selezionate l'intervallo che contiene i vostri dati problematici (ad esempio, numeri memorizzati come testo).
- Andate su "Kutools" > "Contenuto" > "Converti tra Testo e Numero".
- Nella finestra popup:
- Scegliete "Testo a numero" se state risolvendo errori di ricerca dovuti a numeri formattati come testo. (Oppure selezionate "Numero a testo" se i valori di ricerca sono memorizzati come testo.)
- Cliccate su "OK" per convertire immediatamente il formato dei dati.
- Scegliete "Testo a numero" se state risolvendo errori di ricerca dovuti a numeri formattati come testo.
Dopo aver convertito il testo in numeri, le celle convertite si comporteranno come veri numeri e non mostreranno più triangoli verdi che indicano incongruenze.
Questo approccio elimina la necessità di colonne ausiliarie, formule o VBA — rendendolo ideale per una pulizia rapida prima di applicare VLOOKUP.
Kutools per Excel - Potenzia Excel con oltre 300 strumenti essenziali. Goditi funzionalità AI gratuite per sempre! Ottienilo ora
Macro VBA: Standardizzare i formati prima di VLOOKUP
Per gli utenti che lavorano regolarmente con grandi set di dati, ricevono file provenienti da fonti esterne o richiedono automazione ripetuta, l'uso di una semplice macro VBA può standardizzare programmaticamente il formato dei dati sia nella colonna dei valori di ricerca che nella colonna della tabella di ricerca. In questo modo, assicuratevi che tutti i dati vengano convertiti in testo o numero prima di eseguire VLOOKUP, eliminando errori di corrispondenza dovuti a mancate corrispondenze di formato. VBA è particolarmente utile per l'elaborazione in blocco, risparmiando modifiche manuali e garantendo la coerenza dei dati attraverso l'automazione.
Vantaggi: Automazione della formattazione per grandi intervalli di dati o flussi di lavoro frequenti; minimizza il rischio di mancate corrispondenze o formattazioni inconsistenti; adatto per attività ripetitive.
Svantaggi: Non adatto per utenti con restrizioni sulle macro o per coloro che non sono familiari con l'uso delle macro VBA.
Ecco come potete utilizzare una macro per standardizzare i formati delle celle:
1. Andate sulla scheda Sviluppatore e cliccate Visual Basic per aprire l'editor VBA. Nella nuova finestra, cliccate Inserisci > Modulo, quindi copiate e incollate il seguente codice nell'area del modulo:
Sub StandardizeLookupFormats()
' Ask the user to select the lookup column and choose a target format
Dim rng As Range
Dim userChoice As Integer
Dim xTitleId As String
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set rng = Application.InputBox("Select the range to standardize (lookup or data column):", xTitleId, Type:=8)
If rng Is Nothing Then Exit Sub
userChoice = MsgBox("Convert selected data to Number? (Click Yes to convert to Number, No to convert to Text)", vbYesNoCancel, xTitleId)
If userChoice = vbYes Then
For Each cell In rng
If IsNumeric(cell.Value) Then
cell.Value = Val(cell.Value)
cell.NumberFormat = "General"
End If
Next
ElseIf userChoice = vbNo Then
For Each cell In rng
If Not IsEmpty(cell.Value) Then
cell.Value = CStr(cell.Value)
cell.NumberFormat = "@"
End If
Next
Else
Exit Sub
End If
End Sub2. Chiudete l'editor VBA. Per eseguire la macro, tornate su Excel, premete Alt+F8, selezionate StandardizeLookupFormats e cliccate Esegui.
Dettagli operativi e suggerimenti:
- Questa macro vi chiederà di selezionare la colonna (sia il vostro intervallo LOOKUP che l'intervallo TABLE) che desiderate standardizzare.
- Dopo la selezione, vi chiederà se desiderate convertire l'intervallo in numeri (cliccate Sì) o in testo (cliccate No). Selezionate lo stesso formato per entrambe le colonne di ricerca e tabella per garantire che VLOOKUP corrisponda in modo affidabile.
- Dopo aver eseguito questa macro, potrebbe essere necessario ricalcolare il foglio di lavoro (premete F9) o riapplicare le vostre formule VLOOKUP se i risultati non appaiono immediatamente.
- Se ricevete un errore che indica che le macro sono disabilitate, abilitate le macro nelle impostazioni di Excel prima di procedere.
Questa soluzione è ideale per importazioni di dati ricorrenti o quando si correggono colonne inconsistenti in grandi set di dati prima di applicare VLOOKUP o altre operazioni di ricerca.
Altri metodi integrati di Excel: Usa 'Testo in colonne' per correggere i formati dei dati
Un modo rapido per allineare i formati di numero e testo in Excel è utilizzare la funzione Testo in colonne. Questo strumento integrato viene spesso utilizzato per dividere i dati ma può anche forzare una conversione di formato senza modificare le formule, rendendolo utile quando si desidera una correzione una tantum o quando si ha a che fare con elenchi semplici.
Pro: Molto facile, non sono necessarie formule o codice, preserva la struttura dei dati originali; Contro: Adatto per correzioni una tantum, non si aggiorna automaticamente se i dati cambiano.
Per utilizzare questo metodo per convertire numeri memorizzati come testo (o viceversa) in una colonna:
- Selezionate la colonna con il formato sospetto non corrispondente (ad esempio, la vostra colonna di ricerca o la colonna referenziata da VLOOKUP).
- Nella scheda Dati, cliccate Testo in colonne.
- Nella procedura guidata, scegliete Delimitato, quindi cliccate Avanti.
- Deselezionate tutte le caselle di controllo dei delimitatori (poiché non state dividendo i dati); cliccate Avanti.
- In Formato dati colonna, selezionate Generale (per forzare Excel a riconoscere i numeri come numeri) o selezionate Testo (per convertire i numeri in testo).
- Cliccate Fine per completare il processo.
Una volta terminato, i vostri dati avranno il loro formato forzatamente allineato a numero o testo, risolvendo le mancate corrispondenze di VLOOKUP. Controllate sempre alcune celle per confermare che la conversione abbia funzionato come previsto. Se necessario, ripetete il processo per entrambe la vostra colonna di ricerca e i vostri valori di ricerca per massimizzare la coerenza.
Promemoria pratici: “Testo in colonne” modifica direttamente i dati, quindi potrebbe sovrascrivere il contenuto delle celle se avete dati esistenti immediatamente a destra. Considerate di copiare la vostra colonna in un'area vuota prima, se non siete sicuri, e salvate sempre una copia di backup del vostro file prima di utilizzare strumenti di dati in blocco.
I migliori strumenti per la produttività in Office
Potenzia le tue competenze in Excel con Kutools per Excel e sperimenta un'efficienza mai vista prima. Kutools per Excel offre oltre300 funzionalità avanzate per aumentare la produttività e farti risparmiare tempo. Clicca qui per ottenere la funzione di cui hai più bisogno...
Office Tab porta le schede su Office e rende il tuo lavoro molto più semplice
- Abilita la modifica e lettura a schede in Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Apri e crea più documenti in nuove schede della stessa finestra invece che in nuove finestre.
- Aumenta la produttività del50% e riduce centinaia di clic del mouse ogni giorno!
Tutti gli add-in Kutools. Un solo programma di installazione
La suite Kutools for Office include add-in per Excel, Word, Outlook & PowerPoint più Office Tab Pro, ideale per i team che lavorano su più app di Office.
- Suite tutto-in-uno — Add-in per Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Un solo programma di installazione, una sola licenza — configurazione in pochi minuti (pronto per MSI)
- Funzionano meglio insieme — produttività ottimizzata su tutte le app Office
- Prova completa30 giorni — nessuna registrazione, nessuna carta di credito
- Massimo risparmio — costa meno rispetto all’acquisto singolo degli add-in