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 tutti i valori corrispondenti in una cella
- Vlookup per restituire tutti i valori corrispondenti senza duplicati in una cella
Vlookup per restituire più valori in una cella con la funzione definita dall'utente
- Vlookup per restituire tutti i valori corrispondenti in una cella
- Vlookup per restituire tutti i valori corrispondenti senza duplicati in una cella
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:
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:
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 > Modulie 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:
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 > Modulie 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:
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.
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
Potenzia le tue competenze di Excel con Kutools per Excel e sperimenta l'efficienza come mai prima d'ora. Kutools per Excel offre oltre 300 funzionalità avanzate per aumentare la produttività e risparmiare tempo. Fai clic qui per ottenere la funzionalità di cui hai più bisogno...
Office Tab porta l'interfaccia a schede in Office e semplifica notevolmente il tuo 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!