Come cercare o trovare valori in un'altra cartella di lavoro?

Configurare i riferimenti di ricerca con VLOOKUP può rivelarsi complicato, soprattutto quando si modificano spesso il percorso, il nome o il foglio del file di origine. In questi casi, automatizzare la ricerca con VBA rappresenta una soluzione più efficiente: permette infatti di recuperare valori anche quando la cartella di lavoro di origine è chiusa e gestisce automaticamente la selezione dell’intervallo e l’estrazione dei dati.

Seguire questi passaggi per utilizzare VBA nelle ricerche tra cartelle di lavoro:

1. Premere contemporaneamente Alt+F11 per aprire la finestra dell’editor Microsoft Visual Basic for Applications.

2.Nell’editor VBA, fare clic su Inserisci>Modulo, quindi copiare e incollare il codice seguente nella finestra del modulo:

VBA: Cercare dati con VLOOKUP e Valore restituito da un'altra cartella di lavoro chiusa

Option Explicit

' Convert column number to column letter
Private Function GetColumn(ByVal Num As Integer) As String
    If Num <= 26 Then
        GetColumn = Chr(Num + 64)
    Else
        GetColumn = Chr((Num - 1) \ 26 + 64) & _
                    Chr((Num - 1) Mod 26 + 65)
    End If
End Function

Sub FindValue()

    Dim xAddress As String
    Dim xString As String
    Dim xFileName As Variant
    Dim xUserRange As Range
    Dim xRg As Range
    Dim xFCell As Range
    Dim xSourceSh As Worksheet
    Dim xSourceWb As Workbook
    
    On Error Resume Next
    
    ' Get current selection address
    xAddress = Application.ActiveWindow.RangeSelection.Address
    
    ' Ask user to select lookup range
    Set xUserRange = Application.InputBox( _
        Prompt:="Lookup values :", _
        Title:="Kutools for Excel", _
        Default:=xAddress, _
        Type:=8)
    
    If Err.Number <> 0 Then Exit Sub
    On Error GoTo 0
    
    ' Limit selection to used range
    Set xUserRange = Application.Intersect(xUserRange, _
                                           Application.ActiveSheet.UsedRange)
    
    ' Ask user to select source workbook
    xFileName = Application.GetOpenFilename( _
                "Excel Files (*.xlsx), *.xlsx", _
                1, _
                "Select a Workbook")
                
    If xFileName = False Then Exit Sub
    
    Application.ScreenUpdating = False
    
    ' Open source workbook
    Set xSourceWb = Workbooks.Open(xFileName)
    Set xSourceSh = xSourceWb.Worksheets.Item(1)
    
    ' Build external reference string
    xString = "='" & xSourceWb.Path & Application.PathSeparator & _
              "[" & xSourceWb.Name & "]" & _
              xSourceSh.Name & "'!$"
    
    ' Loop through user range
    For Each xRg In xUserRange
    
        ' Find matching value in source sheet
        Set xFCell = xSourceSh.Cells.Find( _
                        What:=xRg.Value, _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        MatchCase:=False)
        
        ' If found, write formula 2 columns to the right
        If Not xFCell Is Nothing Then
            xRg.Offset(0, 2).Formula = _
                xString & _
                GetColumn(xFCell.Column + 1) & _
                "$" & xFCell.Row
        End If
        
    Next xRg
    
    ' Close source workbook without saving
    xSourceWb.Close False
    
    Application.ScreenUpdating = True

End Sub

Dettagli importanti:

• Il codice restituisce il valore corrispondente in una colonna posizionata due colonne a destra rispetto all’intervallo di ricerca. Ad esempio, se si seleziona la colonna B, i risultati vengono visualizzati nella colonna D.
• Se si desidera che il risultato appaia in una colonna diversa, modificare il numero 2 in xRg.Offset(0,2).Formulacon un altro valore (ad esempio,)1 per la colonna successiva, 3 per la terza colonna a destra).
• Scegliere la cartella di lavoro e il foglio corretti quando richiesto; il codice utilizzerà sempre il primo foglio del file selezionato. Modificare il codice se il foglio di origine non è il primo.
• Salva sempre il file prima di eseguire macro sconosciute: una volta avviate, non possono essere annullate.

3. Eseguire la macro premendo il tasto F5 oppure facendo clic sul pulsante Esegui. Apparirà una finestra di dialogo intitolata «Kutools per Excel» che chiederà di selezionare l’intervallo di celle i cui valori si desidera cercare.

4. Dopo aver selezionato l’intervallo, fai clic su OK. Poco dopo apparirà un’altra finestra di dialogo che ti chiederà di scegliere la cartella di lavoro di origine (anche se è chiusa). Individua e seleziona il file appropriato, quindi fai clic su Apri per confermare.

Al termine della macro, i valori corrispondenti provenienti dalla cartella di lavoro di origine verranno inseriti nella colonna di destinazione del proprio foglio di lavoro corrente. Se alcuni valori risultano mancanti, verificare che l’intervallo dei valori di ricerca nel foglio di lavoro corrente corrisponda esattamente a quello nei dati di origine: maiuscole/minuscole e spazi iniziali o finali influiscono sulla corrispondenza esatta.

Vantaggi: Gestisce cartelle di lavoro chiuse, evita riferimenti hardcoded al percorso file nelle formule e offre massima flessibilità nella selezione dei file di origine al momento dell’uso.
Considerazioni: È necessario abilitare le macro; VBA potrebbe non funzionare su fogli protetti o con dati provenienti da file non Excel. Salvare la cartella di lavoro in formato con supporto alle macro (*.xlsm) se si prevede di utilizzare spesso questo metodo.

In caso di errori, assicurati che non vi siano errori di battitura nei nomi dei fogli o dei file, che l’intervallo selezionato sia appropriato e che i percorsi dei file siano accessibili. Per il debug, puoi eseguire il codice riga per riga utilizzando l’editor VBA.

Oltre all’approccio classico con VLOOKUP e a VBA, esistono metodi alternativi per effettuare ricerche tra cartelle di lavoro in Excel. Questi si rivelano particolarmente vantaggiosi in determinate situazioni, ad esempio quando la struttura dei dati è diversa, si preferiscono le formule alle macro o si richiede maggiore flessibilità—come nella ricerca verso sinistra o con criteri multipli.

Utilizzo delle funzioni INDICE e CONFRONTA tra cartelle di lavoro

Combinando INDICE e CONFRONTA, è possibile cercare valori in qualsiasi direzione—sinistra, destra, sopra o sotto—in un’altra cartella di lavoro. Ciò è particolarmente utile quando la colonna da cui recuperare i dati non si trova a destra della colonna di ricerca (un limite di VLOOKUP).

Scenario: Supponiamo di voler recuperare un prezzo da un’altra cartella di lavoro aperta, in cui il nome del frutto potrebbe non trovarsi nella prima colonna.

1.Nella cartella di lavoro di destinazione, selezionare la cella in cui visualizzare il risultato (ad esempio, C2) e inserire la formula seguente (sostituire cartella di lavoro, foglio e intervallo secondo necessità):

=INDEX([Price.xlsx]Sheet1!$B$1:$B$24, MATCH(B2, [Price.xlsx]Sheet1!$A$1:$A$24,0))

2. Premere Invio. Copiare quindi la formula nelle altre righe necessarie trascinando il quadratino di riempimento.

Spiegazione dei parametri:

• [Price.xlsx]Foglio1!$B$1:$B$24: L’intervallo in cui sono memorizzati i prezzi.
• B2: Il nome del frutto da cercare.
• [Price.xlsx]Foglio1!$A$1:$A$24: L’intervallo in cui cercare il valore desiderato.
• L’0 alla fine garantisce una corrispondenza esatta.

Punti di forza: Funziona per ricerche sia a sinistra che a destra e supporta layout più flessibili.
Suggerimenti: Evita di spostare o rinominare i file di origine senza aggiornare la formula.

Utilizzo di XLOOKUP per ricerche tra cartelle di lavoro (Excel 365 e versioni successive)

Se utilizzi Excel 365 o Excel 2021, la nuova funzione XLOOKUP offre ancora maggiore flessibilità: trova facilmente corrispondenze esatte, supporta ricerche a sinistra e gestisce automaticamente i valori mancanti senza generare errori.

Per utilizzarla:
Nella cella in cui si desidera il risultato, inserire:

=XLOOKUP(B2, [Price.xlsx]Sheet1!$A$1:$A$24, [Price.xlsx]Sheet1!$B$1:$B$24, "Not found")

Premere Invio e copiare la formula secondo necessità. Qui, «Non trovato» può essere sostituito con qualsiasi testo personalizzato da visualizzare quando la ricerca non dà risultati.

Vantaggi: Più flessibile di VLOOKUP e più semplice da gestire; evita molti errori comuni legati alle formule legacy. Tuttavia, XLOOKUP è disponibile solo nelle versioni più recenti di Excel.

Per esigenze complesse—come la corrispondenza con criteri multipli, la ricerca in fogli uniti o l’ottimizzazione delle prestazioni in file molto grandi—valuta di organizzare i dati di origine in tabelle strutturate o di utilizzare lo strumento Power Query di Excel per collegare i dati in modo efficiente tra più cartelle di lavoro.