Note: The other languages of the website are Google-translated. Back to English

Come visualizzare per restituire più valori in una cella in Excel?

Normalmente, in Excel, quando si utilizza la funzione CERCA.VERT, se sono presenti più valori che corrispondono ai criteri, è possibile ottenere solo il primo. Ma, a volte, vuoi restituire tutti i valori corrispondenti che soddisfano i criteri in una cella come mostrato nella seguente schermata, come potresti risolverlo?

Vlookup per restituire più valori in una cella con la funzione TEXTJOIN (Excel 2019 e Office 365)

Vlookup per restituire più valori in una cella con la funzione definita dall'utente

Vlookup per restituire più valori in una cella con una funzione utile


Vlookup per restituire più valori in una cella con la funzione TEXTJOIN (Excel 2019 e Office 365)

Se hai la versione superiore di Excel come Excel 2019 e Office 365, c'è una nuova funzione: COLLEGAMENTO TESTO, con questa potente funzione, puoi visualizzare rapidamente e restituire tutti i valori corrispondenti in una cella.

Vlookup per restituire tutti i valori corrispondenti in una cella

Si prega di applicare la formula seguente in una cella vuota in cui si desidera inserire il risultato, quindi premere CTRL + MAIUSC + INVIO i tasti insieme per ottenere il primo risultato, quindi trascina il quadratino di riempimento verso il basso sulla cella in cui desideri utilizzare questa formula e otterrai tutti i valori corrispondenti come mostrato nell'immagine sottostante:

=TEXTJOIN(",",TRUE,IF($A$2:$A$11=E2,$C$2:$C$11,""))

NOTA: Nella formula sopra, A2: A11 è l'intervallo di ricerca che contiene i dati di ricerca, E2 è il valore di ricerca, C2: C11 è l'intervallo di dati da cui desideri restituire i valori corrispondenti, ","è il separatore per separare i record multipli.

Vlookup per restituire tutti i valori corrispondenti senza duplicati in una cella

Se desideri restituire tutti i valori corrispondenti in base ai dati di ricerca senza duplicati, la formula seguente potrebbe aiutarti.

Copia e incolla la seguente formula in una cella vuota, quindi premi CTRL + MAIUSC + INVIO le chiavi insieme per ottenere il primo risultato, quindi copia questa formula per riempire altre celle, e otterrai tutti i valori corrispondenti senza quelli delicati come mostrato nell'immagine sottostante:

=TEXTJOIN(",", TRUE, IF(IFERROR(MATCH($C$2:$C$11, IF(E2=$A$2:$A$11, $C$2:$C$11, ""), 0),"")=MATCH(ROW($C$2:$C$11), ROW($C$2:$C$11)), $C$2:$C$11, ""))

NOTA: Nella formula sopra, A2: A11 è l'intervallo di ricerca che contiene i dati di ricerca, E2 è il valore di ricerca, C2: C11 è l'intervallo di dati da cui desideri restituire i valori corrispondenti, ","è il separatore per separare i record multipli.

Vlookup per restituire più valori in una cella con la funzione definita dall'utente

La funzione TEXTJOIN sopra è disponibile solo per Excel 2019 e Office 365, se hai altre versioni precedenti di Excel, dovresti usare alcuni codici per completare questa attività.

Vlookup per restituire tutti i valori corrispondenti in una cella

1. Tieni premuto il ALT + F11 chiavi e apre il file Microsoft Visual Basic, Applications Edition finestra.

2. Clic inserire > Moduloe incolla il codice seguente nel file Finestra del modulo.

Codice VBA: Vlookup per restituire più valori in una cella

Function ConcatenateIf(CriteriaRange As Range, Condition As Variant, ConcatenateRange As Range, Optional Separator As String = ",") As Variant
'Updateby Extendoffice
Dim xResult As String
On Error Resume Next
If CriteriaRange.Count <> ConcatenateRange.Count Then
    ConcatenateIf = CVErr(xlErrRef)
    Exit Function
End If
For i = 1 To CriteriaRange.Count
    If CriteriaRange.Cells(i).Value = Condition Then
        xResult = xResult & Separator & ConcatenateRange.Cells(i).Value
    End If
Next i
If xResult <> "" Then
    xResult = VBA.Mid(xResult, VBA.Len(Separator) + 1)
End If
ConcatenateIf = xResult
Exit Function
End Function

3. Quindi salva e chiudi questo codice, torna al foglio di lavoro e inserisci questa formula: =CONCATENATEIF($A$2:$A$11, E2, $C$2:$C$11, ", ") in una cella vuota specifica in cui desideri posizionare il risultato, quindi trascina verso il basso la maniglia di riempimento per ottenere tutti i valori corrispondenti in una cella che desideri, vedi screenshot:

NOTA: Nella formula sopra, A2: A11 è l'intervallo di ricerca che contiene i dati di ricerca, E2 è il valore di ricerca, C2: C11 è l'intervallo di dati da cui desideri restituire i valori corrispondenti, ","è il separatore per separare i record multipli.

Vlookup per restituire tutti i valori corrispondenti senza duplicati in una cella

Per ignorare i duplicati nei valori corrispondenti restituiti, eseguire il codice seguente.

1. Tieni premuto il Alt + F11 chiavi per aprire il Microsoft Visual Basic, Applications Edition finestra.

2. Clic inserire > Moduloe incolla il codice seguente nel file Finestra del modulo.

Codice VBA: Vlookup e restituisce più valori corrispondenti univoci in una cella

Function MultipleLookupNoRept(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer)
'Updateby Extendoffice
    Dim xDic As New Dictionary
    Dim xRows As Long
    Dim xStr As String
    Dim i As Long
    On Error Resume Next
    xRows = LookupRange.Rows.Count
    For i = 1 To xRows
        If LookupRange.Columns(1).Cells(i).Value = Lookupvalue Then
            xDic.Add LookupRange.Columns(ColumnNumber).Cells(i).Value, ""
        End If
    Next
    xStr = ""
    MultipleLookupNoRept = xStr
    If xDic.Count > 0 Then
        For i = 0 To xDic.Count - 1
            xStr = xStr & xDic.Keys(i) & ","
        Next
        MultipleLookupNoRept = Left(xStr, Len(xStr) - 1)
    End If
End Function

3. Dopo aver inserito il codice, fare clic su Strumenti > Riferimenti in aperto Microsoft Visual Basic, Applications Edition finestra, e poi, nel saltato fuori Riferimenti - VBAProject finestra di dialogo, controllare Runtime di script Microsoft opzione nel Riferimenti disponibili casella di riepilogo, vedere screenshot:

4. Quindi fare clic OK per chiudere la finestra di dialogo, salvare e chiudere la finestra del codice, tornare al foglio di lavoro e immettere questa formula: =MultipleLookupNoRept(E2,$A$2:$C$11,3) into a blank cell where you want to output the result, and then drag the fill hanlde down to get all matching values, see screenshot:

NOTA: Nella formula sopra, A2: C11 è l'intervallo di dati che desideri utilizzare, E2 è il valore di ricerca, il numero 3 è il numero di colonna che contiene i valori restituiti.

Vlookup per restituire più valori in una cella con una funzione utile

 Se hai il nostro file Kutools for Excel, Con la sua Combina righe avanzate funzione, puoi unire o combinare rapidamente le righe in base allo stesso valore ed eseguire alcuni calcoli di cui hai bisogno.

NOTA:Per applicare questo Combina righe avanzate, in primo luogo, dovresti scaricare il file Kutools for Excele quindi applica la funzione in modo rapido e semplice.

Dopo l'installazione Kutools for Excel, si prega di fare come segue:

1. Seleziona l'intervallo di dati in cui desideri combinare i dati di una colonna in base a un'altra colonna.

2. Clic Kutools > Unisci e dividi > Combina righe avanzate, vedi screenshot:

3. Nel saltò fuori Combina righe avanzate la finestra di dialogo:

  • Fare clic sul nome della colonna chiave da combinare in base a, quindi fare clic su Chiave primaria
  • Quindi fare clic su un'altra colonna di cui si desidera combinare i dati in base alla colonna chiave e fare clic Combinare per scegliere un separatore per separare i dati combinati.

4. Quindi fare clic su OK pulsante e otterrai i seguenti risultati:

Scarica e prova gratuitamente Kutools per Excel ora!


Articoli più relativi:

  • Funzione CERCA.VERT con alcuni esempi di base e avanzati
  • In Excel, la funzione CERCA.VERT è una funzione potente per la maggior parte degli utenti di Excel, che viene utilizzata per cercare un valore nella parte più a sinistra dell'intervallo di dati e restituire un valore corrispondente nella stessa riga da una colonna specificata. Questo tutorial parla di come utilizzare la funzione CERCA.VERT con alcuni esempi di base e avanzati in Excel.
  • Restituisce più valori corrispondenti in base a uno o più criteri
  • Normalmente, cercare un valore specifico e restituire l'elemento corrispondente è facile per la maggior parte di noi utilizzando la funzione CERCA.VERT. Ma hai mai provato a restituire più valori corrispondenti in base a uno o più criteri? In questo articolo, introdurrò alcune formule per risolvere questo compito complesso in Excel.
  • Vlookup e restituisce più valori verticalmente
  • Normalmente, puoi usare la funzione Vlookup per ottenere il primo valore corrispondente, ma, a volte, vuoi restituire tutti i record corrispondenti in base a un criterio specifico. In questo articolo, parlerò di come visualizzare e restituire tutti i valori corrispondenti verticalmente, orizzontalmente o in una singola cella.
  • Vlookup e restituisce più valori dall'elenco a discesa
  • In Excel, come potresti visualizzare e restituire più valori corrispondenti da un elenco a discesa, il che significa che quando scegli un elemento dall'elenco a discesa, tutti i suoi valori relativi vengono visualizzati contemporaneamente. In questo articolo, introdurrò la soluzione passo dopo passo.

I migliori strumenti per la produttività in ufficio

Kutools per Excel risolve la maggior parte dei tuoi problemi e aumenta la tua produttività dell'80%

  • Riutilizzo: Inserisci rapidamente formule complesse, grafici e tutto ciò che hai usato prima; Crittografa celle con password; Crea mailing list e invia email ...
  • Bar Super Formula (modifica facilmente più righe di testo e formula); Layout di lettura (leggi e modifica facilmente un gran numero di celle); Incolla su intervallo filtrato...
  • Unisci celle / righe / colonne senza perdere dati; Contenuto delle celle divise; Combina righe / colonne duplicate... Impedisci celle duplicate; Confronta intervalli...
  • Seleziona Duplica o Unico Righe; Seleziona Righe vuote (tutte le celle sono vuote); Super Find e Fuzzy Find in molte cartelle di lavoro; Selezione casuale ...
  • Copia esatta Più celle senza modificare il riferimento della formula; Riferimenti di creazione automatica a più fogli; Inserisci punti elenco, Caselle di controllo e altro ...
  • Estrai testo, Aggiungi testo, Rimuovi per posizione, Rimuovi spazio; Creare e stampare totali parziali di paging; Converti contenuto e commenti tra celle...
  • Super filtro (salva e applica schemi di filtri ad altri fogli); Ordinamento avanzato per mese / settimana / giorno, frequenza e altro; Filtro speciale in grassetto, corsivo ...
  • Combina cartelle di lavoro e fogli di lavoro; Unisci tabelle in base a colonne chiave; Suddividi i dati in più fogli; Conversione in batch xls, xlsx e PDF...
  • Più di 300 potenti funzionalità. Supporta Office/Excel 2007-2021 e 365. Supporta tutte le lingue. Facile implementazione nella tua azienda o organizzazione. Funzionalità complete Prova gratuita di 30 giorni. Garanzia di rimborso di 60 giorni.
scheda kte 201905

Scheda Office porta l'interfaccia a schede a Office e semplifica notevolmente il lavoro

  • 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 della stessa finestra, piuttosto che in nuove finestre.
  • Aumenta la produttività del 50% e riduce ogni giorno centinaia di clic del mouse!
fondo officetab
Commenti (43)
Ancora nessuna valutazione. Puoi essere il primo a votare!
Questo commento è stato fatto dal moderatore sul sito
Come regolerei questa formula per separare ogni valore restituito ma ", " e restituire solo valori univoci?
Questo commento è stato fatto dal moderatore sul sito
Grazie per il codice!!

Per quanto riguarda i caratteri jolly, un modo per aggirare è l'utilizzo di INSTR

È possibile sostituire [ If rng = pValue Then ] con [ InStr(1, rng.Value, pValue) Then ] e se non si desidera che faccia distinzione tra maiuscole e minuscole, utilizzare [ InStr(1, rng.Value, pValue, vbTextCompare) Quindi ]
Questo commento è stato fatto dal moderatore sul sito
Grazie per il codice VBA sopra. Puoi dirmi come fare in modo che i risultati entrino in una nuova riga nella cella, ad esempio come Alt-Invio 300 400 1000 1300
Questo commento è stato fatto dal moderatore sul sito
Grazie per aver condiviso il codice sopra. Lo uso da diversi mesi ma oggi non sembra funzionare. Ricevo celle vuote invece del solito errore quando ci sono dati da restituire. qualche idea?
Questo commento è stato fatto dal moderatore sul sito
Lavoro fantastico.. Ho esattamente quello che voglio!!! Lo adoro !!
Questo commento è stato fatto dal moderatore sul sito
Ciao, sono davvero impressionato dal lavoro ed è così facile crearne uno per utilizzare questa funzione. tuttavia ho bisogno di ulteriore supporto. Il mio ? è così che posso selezionare un numero da una cella con più celle nel mio array vlookup. cioè se la cella A1 = 100, A2 = 350, A3 = 69 C1 = 100; 1222; 12133 C2 = 69; 222 D1 = Apple D2 = banana Quindi, come posso selezionare 100 dalla colonna dell'array della mia tabella C per derivare il corrispondente D1 = mela Si noti che ho numeri a 7 cifre nel mio valore di ricerca e nell'array della tabella che è separato da un ";". Apprezzerei davvero se riuscissi a risolvere questo problema e aiutarmi a risparmiare molto tempo.
Questo commento è stato fatto dal moderatore sul sito
Grazie per il codice VBA. Ho esattamente quello che voglio! Ho modificato solo il codice " rng.Offset(0, pIndex - 1) " in " rng.Offset(0, pIndex - 2) " . Quindi MYVLOOKUP è in grado di cercare da destra a sinistra.
Questo commento è stato fatto dal moderatore sul sito
Questo è esattamente quello che stavo cercando e non ho pensato di creare solo il mio UDF. Tuttavia non funzionerà esattamente come VLOOKUP. Se la stringa che stai cercando non è solo nella prima colonna, potrebbe fornirti dati al di fuori dell'intervallo originale passato. Nome Numero Altro nome Colonna non nell'intervallo superato Jay 1 Jay 1 Jay 2 Jay 2 Chris 3 Chris 3 Jorge 4 Jorge 4 Jay 5 Jay 5 Jorge 6 Jorge 6 Se la tabella sopra fosse celle A1:D7 se hai superato solo A1:C7 il tuo La funzione "MYVLOOKUP" restituisce 1 1 2 2 5 5 quando ti aspetteresti che restituisca 1 2 5. Le modifiche seguenti risolvono il problema: Function MYVLOOKUP(pValue As String, pWorkRng As Range, pIndex As Long) 'Update 20150310 'Updated 6 /9/16 Jay Coltrain 'Dim rng As Range Dim xResult As String xResult = "" Dim Rows As Long, i As Long Rows = pWorkRng.Rows.Count For i = 1 To Rows If pWorkRng.Cells(i, 1). Value = pValue Then xResult = xResult & " " & pWorkRng.Cells(i, 1).Offset(0, pIndex - 1) End If Next i 'For Each rng In pWorkRng ' If rng = pValue Then ' xResult = xResult & " " & rng.Offset(0, pIndex - 1) ' End If 'Next MYVLOOKUP = xResult End Function
Questo commento è stato fatto dal moderatore sul sito
Funziona alla grande, ma ho bisogno di aiuto con il comando per rimuovere i duplicati dai risultati. Seriamente però, ottimo lavoro.
Questo commento è stato fatto dal moderatore sul sito
Funziona alla grande, ma ho ancora bisogno di aiuto con la funzione di comando per rimuovere i duplicati dai risultati.
Questo commento è stato fatto dal moderatore sul sito
Notificami i commenti successivi
Questo commento è stato fatto dal moderatore sul sito
Non restituire niente! dopo aver applicato MYLOOKUP non ha dato alcun risultato ma vuoto.
Questo commento è stato fatto dal moderatore sul sito
Ciao, funziona bene. Quello che vorrei fare è adattare il codice per separare i risultati dei valori con "///" o qualsiasi altro marcatore (per motivi tecnici, non voglio solo un separatore di caratteri singoli). Inoltre, ho notato che questa formula non funziona con un carattere jolly. So che sto chiedendo troppo, ma non come vlookup può funzionare quando cerco =myvlookup("*"&E6&"*",$A$2:$C$15,2) che farebbe/potrebbe fare. Qualche assistenza?
Questo commento è stato fatto dal moderatore sul sito
Dritta. Ho capito come ottenere qualsiasi separatore in quell'output. È rudimentale. Ma l'ho capito. xResult = xResult & "///" & rng.Offset(0, pIndex - 1) L'ultima e più desiderata cosa è consentirgli di funzionare con i caratteri jolly nei criteri di ricerca. Grazie ancora per questa bellissima e brillante soluzione. Estremamente utile. Ora voglio solo far eseguire la macro e essere installata nel mio excel in modo permanente, indipendentemente da quello che sto facendo, quindi posso usarla quando ne ho bisogno. E i caratteri jolly! Grazie mille. I caratteri jolly sono tutto ciò che resta da fare.
Questo commento è stato fatto dal moderatore sul sito
Per ottenere un record univoco, puoi utilizzare di seguito: (modificato facendo riferimento a un altro codice utente) Function MYVLOOKUP(pValue As String, pWorkRng As Range, pIndex As Long) 'Update 20150310 'Updated 6/9/16 Jay Coltrain 'Dim rng As Range Dim xResult As String xResult = "" Dim Rows As Long, i As Long Rows = pWorkRng.Rows.Count For i = 1 To Rows If pWorkRng.Cells(i, 1).Value = pValue Then xResult = xResult & "," & pWorkRng.Cells(i, 1).Offset(0, pIndex - 1) End If Next i Dim varSection As Variant Dim sTemp As String Dim sDelimiter As String sDelimiter = "," For Each varSection In Split(xResult, sDelimiter) If InStr(1, sDelimiter & sTemp & sDelimiter, sDelimiter & varSection & sDelimiter, vbTextCompare) = 0 Then sTemp = sTemp & sDelimiter & varSection End If Next varSection MYVLOOKUP = Mid(sTemp, Len(sDelimiter) + 1) End Function
Questo commento è stato fatto dal moderatore sul sito
Questo ha funzionato perfettamente, ma mi ci è voluto del tempo per far funzionare correttamente la funzione all'interno del mio foglio di calcolo con 20 schede e oltre 50 righe. Ora la GRANDE domanda è come prendere quella stringa delimitata e quindi utilizzare ogni voce come valore di ricerca Indice/Corrispondenza (non sposato con Indice/Corrispondenza, ma sembra più veloce) in un altro set di dati, restituendo il valore SUM di tutti i ritorni in una cella . Il mio scenario è che ho un singolo ordine con più fatture. La tua funzione MYVLOOKUP funziona in modo eccellente per riportare tutte le fatture in una cella. Quello che voglio fare ora è prendere ogni ritorno concatenato con la cella segnalata, scorrere quella matrice e sommare gli importi di pagamento di ciascuna fattura nella cella della formula. Apprezzo tutto l'aiuto che puoi offrire su questo e grazie per la funzione MYVLOOKUP!
Questo commento è stato fatto dal moderatore sul sito
Non importa quello che faccio, ottengo sempre #valore! restituito invece di un risultato. vlookup funziona bene, quindi i dati funzionano. Ho già seguito il processo di abilitazione delle macro. Ho anche combinato tutto in un unico foglio. Qualche idea??
Questo commento è stato fatto dal moderatore sul sito
Ottima macro, utile. Ma è necessario sapere se può essere modificato per verificare 2 criteri e qualcuno è stato trovato comunque a far funzionare i caratteri jolly su di esso. Qualsiasi aiuto?
Questo commento è stato fatto dal moderatore sul sito
C'è un modo per modificare il risultato in modo che invece di mostrare 1000 1000 -1000 mostrerebbe ad esempio 1,000/1,000/(1,000) ?
Questo commento è stato fatto dal moderatore sul sito
Ottima funzione, tuttavia il blocco di 100,000 record si rivela un po' eccessivo per il mio povero laptop, sarà necessario lasciarlo funzionare durante la notte!
Questo commento è stato fatto dal moderatore sul sito
È fantastico, grazie!
Non ci sono ancora commenti pubblicati qui
Carica Altre
Lasciate i vostri commenti
Pubblicazione come ospite
×
Valuta questo post:
0   Personaggi
Posizioni suggerite

Seguici

Copyright © 2009 - www.extendoffice.com. | Tutti i diritti riservati. Offerto da ExtendOffice, | Mappa del sito
Microsoft e il logo Office sono marchi o marchi registrati di Microsoft Corporation negli Stati Uniti e / o in altri paesi.
Protetto da Sectigo SSL