Come usare VLOOKUP con numeri memorizzati come testo in Excel?
Quando si utilizza VLOOKUP in Excel, le incongruenze di formato — ad esempio quando il valore da cercare è memorizzato come testo mentre la colonna di ricerca contiene numeri, o viceversa — possono causare errori o il mancato reperimento dei dati. Questa discrepanza è un problema comune, soprattutto con dati provenienti da fonti esterne, importati o gestiti in set ampi e collaborativi. Risolvere tali incongruenze è fondamentale per garantire che VLOOKUP funzioni correttamente e recuperi le informazioni esatte. Questa guida passo passo offre soluzioni pratiche ed efficaci per affrontare queste differenze di formato, assicurando ricerche precise e affidabili, indipendentemente dal modo in cui i numeri sono memorizzati nella cartella di lavoro.
Questo articolo illustra metodi efficaci per gestire tali errori, tra cui aggiustamenti tramite formule, strumenti integrati di Excel e automazione VBA per elaborazioni批量 o automatizzate. Analizziamo inoltre i vantaggi e le considerazioni legati a ciascun approccio, aiutandoti a individuare la soluzione più adatta al tuo scenario.
- Cerca numeri memorizzati come testo con formule
- Risolvi rapidamente le incongruenze di formato con Kutools per Excel
- Macro VBA: uniforma i formati prima di VLOOKUP
- Altri metodi integrati di Excel: usa «Testo in colonne» per correggere i formati dei dati
Cerca numeri memorizzati come testo con formule
Se i tuoi dati di ricerca includono numeri memorizzati come testo in una posizione e numeri reali in un’altra, VLOOKUP potrebbe non rilevare corrispondenze a causa di questa discrepanza di formato. Una delle soluzioni più immediate consiste nell’utilizzare formule di Excel che convertono al volo il valore da cercare o la colonna di ricerca in un formato coerente. Questo approccio si rivela efficace nella maggior parte delle operazioni sui fogli di lavoro, è semplice da applicare e lascia intatti i dati originali.
Ad esempio, se il valore che devi cercare è memorizzato come testo mentre il campo corrispondente nella tabella è formattato come numero, puoi usare la funzione VALORE per convertire il testo in numero direttamente nella formula VLOOKUP.
Inserisci la seguente formula in una cella vuota in cui desideri visualizzare il risultato:
=VLOOKUP(VALUE(G1),A2:D15,2,FALSE)Dopo aver inserito la formula, premi il tasto Invioper recuperare il valore corrispondente ai tuoi criteri, come illustrato nell’immagine seguente:
Spiegazione dei parametri e suggerimenti:
- G1: la cella che contiene il valore che vuoi cercare (può essere testo o numero).
- A2:D15: l’intervallo della tua tabella dati che include sia la colonna di ricerca sia quelle con le informazioni da restituire.
- 2: il numero della colonna (contando dalla colonna più a sinistra dell’intervallo della tabella) che contiene il risultato desiderato.
Presta attenzione agli spazi iniziali o finali presenti nell’Intervallo di valore di ricerca, poiché possono causare errori nei risultati. Se i tuoi dati potrebbero contenere spazi superflui, valuta di abbinare la funzione STRINGA.ESTRAI per gestirli efficacemente.
Se il valore da cercare è un numero reale (in formato numerico), ma il campo corrispondente nella tabella è memorizzato come testo, devi convertire il valore in testo prima di eseguire la ricerca. La funzione TESTO è perfetta per questo scenario:
=VLOOKUP(TEXT(G1,0),A2:D15,2,FALSE)Inseriscila nella cella di destinazione, premi Invioe il risultato corretto verrà restituito come mostrato di seguito:
In questo caso, il codice di formato numero «0» all’interno della funzione TESTO garantisce che il tuo numero venga convertito in un valore Testo normale prima della corrispondenza.
Se non sei certo del formato dei tuoi Intervallo di valore di ricerca o ti aspetti che entrambi i Dividi per testo e numero possano comparire nella colonna di ricerca, puoi annidare entrambi gli approcci utilizzando la funzione SE.ERROREper gestire tutte le possibilità in modo trasparente:
=IFERROR(VLOOKUP(VALUE(G1),A2:D15,2,0),VLOOKUP(TEXT(G1,0),A2:D15,2,0))Inserisci questa formula nella cella del risultato. Proverà innanzitutto a eseguire la ricerca convertendo il tuo valore in numero; se ciò fallisce (ad esempio, se il valore non può essere convertito in numero), proverà quindi a convertirlo in testo ed eseguire nuovamente la ricerca. Questo approccio è particolarmente utile con set di dati con formati misti o in file condivisi in cui gli standard di immissione dati non sono uniformi.
Dopo aver inserito una qualsiasi delle formule sopra indicate, ricordati di copiarla nelle celle adiacenti se devi applicarla a più intervalli. Per farlo, seleziona semplicemente la cella e trascina il quadratino di riempimento verso il basso oppure usa Ctrl+C e Ctrl+V secondo necessità. Con tabelle di grandi dimensioni, queste formule garantiscono corrispondenze affidabili senza modificare il database originale.
Questo metodo offre una soluzione flessibile e universalmente applicabile alla maggior parte delle ricerche basate su fogli di lavoro. Tuttavia, per set di dati molto grandi o quando è necessario elaborare automaticamente numerosi record, potresti valutare l’uso di strumenti di automazione come VBA per ottenere maggiore efficienza.
Risolvi rapidamente le incongruenze di formato con Kutools per Excel
Se preferisci una soluzione più rapida e senza formule, Kutools per Excel offre uno strumento intuitivo chiamato Conversione tra testo e valore. Questa funzionalità consente di convertire numeri memorizzati come testo in numeri veri — o viceversa — con pochi clic. È particolarmente utile per risolvere problemi di formato prima di eseguire ricerche come VLOOKUP o CONFRONTA.
Dopo aver installato Kutools per Excel, procedi come segue.
- Seleziona l’intervallo contenente i tuoi dati problematici, ad esempio numeri memorizzati come testo.
- Vai su «Kutools» > «Contenuto» > «Conversione tra testo e valore».
- Nella finestra di dialogo visualizzata:
- Scegli «Da testo a valore» se devi correggere errori di ricerca causati da numeri formattati come testo.(Oppure seleziona «Da valore a testo» se i Intervallo di valore di ricerca sono memorizzati come testo.)
- Fai clic su «OK» per convertire immediatamente il formato dei dati.
- Scegli «Da testo a valore» se devi correggere errori di ricerca causati da numeri formattati come testo.
Dopo la conversione da testo a valore, le celle convertite si comporteranno come numeri veri e non mostreranno più l’indicatore a triangolo verde per incongruenza.
Questo approccio elimina la necessità di colonne ausiliarie, formule o VBA, risultando ideale per una pulizia rapida prima di applicare VLOOKUP.
Kutools per Excel– Potenzia Excel con oltre 300 strumenti essenziali, rendendo il tuo lavoro più veloce e semplice, e sfrutta le funzionalità basate sull’IA per un’elaborazione dati più intelligente e una maggiore produttività.Scaricalo ora
Macro VBA: uniforma i formati prima di VLOOKUP
Per gli utenti che lavorano regolarmente con set di dati di grandi dimensioni, ricevono file provenienti da fonti esterne o richiedono automazione ripetuta, l’uso di una semplice macro VBA può uniformare programmaticamente il formato dei dati sia nella colonna del valore da cercare che in quella della tabella. In questo modo, assicuri che tutti i dati siano convertiti in testo o numero prima di eseguire VLOOKUP, eliminando errori di corrispondenza dovuti a incongruenze di formato. VBA è particolarmente utile per Elaborazione in Massa, evita regolazioni manuali e garantisce coerenza dei dati tramite automazione.
Vantaggi: Automatizza la formattazione per ampi intervalli o flussi di lavoro ricorrenti; riduce il rischio di formati mancanti o incongruenti; ideale per compiti ripetitivi.
Svantaggi: Non adatto a utenti con restrizioni sull’uso delle macro o poco familiari con le macro VBA.
Ecco come puoi utilizzare una macro per uniformare Formato celle:
1. Passare alla scheda Sviluppatore e fare clic su Visual Basic per aprire l'editor VBA. Nella nuova finestra, fare clic su Inserisci > Modulo, quindi copiare e incollare il codice seguente 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. Chiudere l'editor VBA. Per eseguire la macro, tornare a Excel, premere Alt+F8, selezionare StandardizeLookupFormats e fare clic su Esegui.
Dettagli operativi e suggerimenti:
- Questa macro ti chiederà di selezionare la colonna (sia l’intervallo di ricerca che quello della tabella) che desideri uniformare.
- Dopo la selezione, ti verrà chiesto se convertire l’intervallo in numeri (fai clic su Sì) o in testo (fai clic su No). Seleziona lo stesso formato sia per la colonna di ricerca che per quella della tabella per garantire che VLOOKUP trovi corrispondenze in modo affidabile.
- Dopo aver eseguito questa macro, potrebbe essere necessario ricalcolare il foglio di lavoro (premi)F9) o riapplicare le formule VLOOKUP se i risultati non compaiono immediatamente.
- Se ricevi un errore relativo alle macro disabilitate, abilitala nelle impostazioni di Excel prima di procedere.
Questa soluzione è ideale per importazioni dati ricorrenti o per ripulire colonne inconsistenti in set di dati di grandi dimensioni 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 numeri e formattare il testo in Excel consiste nell’utilizzare la funzione Testo in colonne. Questo strumento integrato viene spesso usato per suddividere i dati, ma può anche forzare una conversione di formato senza modificare le formule, risultando ideale per correzioni immediate o quando si lavora con elenchi semplici.
Vantaggi: Estremamente semplice, non richiede formule né codice e preserva la struttura originale dei dati;Svantaggi: Ideale per correzioni occasionali, ma non si aggiorna automaticamente se i dati cambiano.
Per utilizzare questo metodo al fine di convertire numeri memorizzati come testo (o viceversa) in una colonna:
- Seleziona la colonna con il formato sospettato di essere incongruente (ad esempio, la tua colonna di ricerca o quella a cui fa riferimento VLOOKUP).
- Nella scheda Dati, fai clic su Testo in colonne.
- Nella procedura guidata, seleziona Delimitati, quindi fai clic su Avanti.
- Deseleziona tutte le caselle di controllo relative ai delimitatori (poiché non stai suddividendo i dati), quindi fai clic su Avanti.
- In Formato dati colonna, seleziona Generale (per forzare Excel a riconoscere i numeri come tali) oppure seleziona Testo (per convertire da valore a testo).
- Fai clic su Fine per completare il processo.
Al termine, i dati avranno il formato uniformato forzatamente a numero o testo, risolvendo le incongruenze di VLOOKUP. Controllare sempre alcune celle per verificare che la conversione abbia funzionato come previsto. Se necessario, ripetere la procedura sia per la colonna di ricerca che per Intervallo di valore di ricerca per garantire la massima coerenza.
Promemoria pratici: «Testo in colonne» modifica direttamente i dati e potrebbe sovrascrivere il contenuto delle celle immediatamente a destra. In caso di dubbi, copia preventivamente la colonna in un'area vuota e salva sempre un backup del file prima di utilizzare strumenti per modifiche massive.
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 oltre 300 funzionalità avanzate per aumentare la produttività e Risparmia tempo.Clicca qui per ottenere la funzionalità di cui hai più bisogno...
Office Tab Porta l'interfaccia a schede in Office e rende il tuo lavoro molto più semplice
- Abilita la modifica e la lettura a schede in Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Apri e crea più documenti in nuove schede all’interno della stessa finestra, invece che in finestre separate.
- Aumenta la tua produttività del 50 % e risparmia centinaia di clic del mouse ogni giorno!
Tutti i componenti aggiuntivi di Kutools in un unico programma di installazione.
Kutools for Office è la suite che include componenti aggiuntivi per Excel, Word, Outlook e PowerPoint, oltre a Office Tab Pro: la soluzione ideale per i team che lavorano su diverse app di Office.
- Suite completa— componenti aggiuntivi per Excel, Word, Outlook e PowerPoint + Office Tab Pro
- Un unico programma di installazione, una sola licenza— configurazione in pochi minuti (pronto per MSI)
- Funziona meglio insieme— produttività ottimizzata tra le app di Office
- Prova gratuita di 30 giorni con tutte le funzionalità— nessuna registrazione, nessuna carta di credito
- Miglior rapporto qualità-prezzo— risparmia rispetto all’acquisto dei singoli componenti aggiuntivi