Μετάβαση στο κύριο περιεχόμενο

Πώς να επιστρέψετε πολλές τιμές αναζήτησης σε ένα κελί διαχωρισμένο με κόμμα;

Συγγραφέας: Xiaoyang Τελευταία τροποποίηση: 2020-05-29

Στο Excel, μπορούμε να εφαρμόσουμε τη συνάρτηση VLOOKUP για να επιστρέψουμε την πρώτη αντιστοιχισμένη τιμή από κελιά πίνακα, αλλά, μερικές φορές, πρέπει να εξαγάγουμε όλες τις τιμές που ταιριάζουν και στη συνέχεια να διαχωρίζονται από ένα συγκεκριμένο οριοθέτη, όπως κόμμα, παύλα κ.λπ. ... σε ένα μόνο κελί όπως φαίνεται το ακόλουθο στιγμιότυπο οθόνης. Πώς θα μπορούσαμε να πάρουμε και να επιστρέψουμε πολλές τιμές αναζήτησης σε ένα κελί διαχωρισμένο με κόμμα στο Excel;

doc επιστρέφει πολλαπλές τιμές διαχωρισμένες με κόμμα 1

Επιστρέψτε πολλές τιμές αναζήτησης σε ένα κελί διαχωρισμένο με κόμμα με συνάρτηση καθορισμένη από το χρήστη

Επιστρέψτε πολλές τιμές αναζήτησης σε ένα κελί διαχωρισμένο με κόμμα με το Kutools για Excel


Επιστρέψτε πολλές τιμές αναζήτησης σε ένα κελί διαχωρισμένο με κόμμα με συνάρτηση καθορισμένη από το χρήστη

Κανονικά, δεν υπάρχει άμεσος τρόπος για να εξαγάγουμε και να επιστρέψουμε τις πολλαπλές τιμές που ταιριάζουν και διαχωρίζονται με κόμμα σε ένα κελί, εδώ, μπορείτε να δημιουργήσετε μια λειτουργία καθορισμένη από τον χρήστη για να λύσετε αυτήν την εργασία, κάντε τα εξής:

1. Κρατήστε πατημένο το ALT + F11 για να ανοίξετε το Microsoft Visual Basic για εφαρμογές παράθυρο.

2. Κλίκ Κύριο θέμα > Μονάδα μέτρησηςκαι επικολλήστε τον ακόλουθο κώδικα στο Μονάδα μέτρησης Παράθυρο.

Κωδικός VBA: Επιστρέψτε πολλές τιμές αναζήτησης σε ένα κελί διαχωρισμένο με κόμμα

Function SingleCellExtract(LookupValue As String, LookupRange As Range, ColumnNumber As Integer, Char As String)
'Updateby Extendoffice
    Dim I As Long
    Dim xRet As String
    For I = 1 To LookupRange.Columns(1).Cells.Count
        If LookupRange.Cells(I, 1) = LookupValue Then
            If xRet = "" Then
                xRet = LookupRange.Cells(I, ColumnNumber) & Char
            Else
                xRet = xRet & "" & LookupRange.Cells(I, ColumnNumber) & Char
            End If
        End If
    Next
    SingleCellExtract = Left(xRet, Len(xRet) - 1)
End Function

3. Στη συνέχεια, αποθηκεύστε αυτόν τον κωδικό και κλείστε το παράθυρο της ενότητας, επιστρέψτε στο φύλλο εργασίας σας και εισαγάγετε αυτόν τον τύπο: = SingleCellExtract (D2, A2: B15,2, ",") σε ένα κενό κελί που θέλετε να επιστρέψετε το αποτέλεσμα. Και μετά πατήστε εισάγετε κλειδί για να λάβετε το αποτέλεσμα, δείτε το στιγμιότυπο οθόνης:

doc επιστρέφει πολλαπλές τιμές διαχωρισμένες με κόμμα 2

Σημείωση: Στον παραπάνω τύπο:

D2: υποδεικνύει τις τιμές κελιών που θέλετε να αναζητήσετε.

Α2: Β15: είναι το εύρος δεδομένων που θέλετε να ανακτήσετε τα δεδομένα.

2: ο αριθμός 2 είναι ο αριθμός στήλης που πρέπει να επιστραφεί η αντίστοιχη τιμή.

,: το κόμμα είναι το διαχωριστικό που θέλετε να διαχωρίσετε τις πολλαπλές τιμές.

Μπορείτε να τα αλλάξετε ανάλογα με τις ανάγκες σας.


Επιστρέψτε πολλές τιμές αναζήτησης σε ένα κελί διαχωρισμένο με κόμμα με το Kutools για Excel

Εάν έχετε Kutools για Excel, αυτή η εργασία δεν θα είναι πλέον πρόβλημα. ο Σύνθετες σειρές συνδυασμού βοηθητικό πρόγραμμα μπορεί να σας βοηθήσει να συνδυάσετε όλες τις σχετικές τιμές με βάση μια στήλη.

Kutools για Excel : με περισσότερα από 300 εύχρηστα πρόσθετα Excel, δωρεάν δοκιμή χωρίς περιορισμό σε 30 ημέρες

Μετά την εγκατάσταση Kutools για Excel, κάντε τα εξής:

1. Επιλέξτε το εύρος δεδομένων που θέλετε να συνδυάσετε όλες τις αντίστοιχες τιμές με βάση μια στήλη.

2. Κλίκ Kutools > Συγχώνευση & διαχωρισμός > Σύνθετες σειρές συνδυασμού, δείτε το στιγμιότυπο οθόνης:

3. Στην Συνδυάστε σειρές με βάση τη στήλη πλαίσιο διαλόγου, κάντε κλικ στο όνομα της στήλης με βάση το οποίο θέλετε να συνδυάσετε και, στη συνέχεια, κάντε κλικ στο Πρωτεύων κλειδί κουμπί, δείτε το στιγμιότυπο οθόνης:

doc επιστρέφει πολλαπλές τιμές διαχωρισμένες με κόμμα 4

4. Στη συνέχεια, κάντε κλικ σε άλλο όνομα στήλης που θέλετε να συνδυάσετε τις αντίστοιχες τιμές τους και κάντε κλικ στο Συνδυασμός για να επιλέξετε ένα διαχωριστικό για να διαχωρίσετε τις συνδυασμένες τιμές, δείτε το στιγμιότυπο οθόνης:

doc επιστρέφει πολλαπλές τιμές διαχωρισμένες με κόμμα 5

5. Στη συνέχεια κάντε κλικ στο κουμπί OK κουμπί, όλα τα αντίστοιχα κελιά με την ίδια τιμή έχουν συνδυαστεί σε ένα κελί που διαχωρίζονται με το κόμμα, δείτε στιγμιότυπα οθόνης:

doc επιστρέφει πολλαπλές τιμές διαχωρισμένες με κόμμα 6 2 doc επιστρέφει πολλαπλές τιμές διαχωρισμένες με κόμμα 7

Κάντε κλικ για να μάθετε περισσότερες λεπτομέρειες σχετικά με αυτό το βοηθητικό πρόγραμμα Advanced Combine Rows…

Κατεβάστε και δωρεάν δοκιμή Kutools για Excel τώρα!


Επίδειξη: Επιστροφή πολλαπλών τιμών αναζήτησης σε ένα κελί διαχωρισμένο με κόμμα με Kutools για Excel

Kutools για Excel: με περισσότερα από 300 εύχρηστα πρόσθετα του Excel, δωρεάν δοκιμή χωρίς περιορισμό σε 30 ημέρες. Λήψη και δωρεάν δοκιμή τώρα!

Τα καλύτερα εργαλεία παραγωγικότητας γραφείου

🤖 Kutools AI Aide: Επανάσταση στην ανάλυση δεδομένων με βάση: Ευφυής Εκτέλεση   |  Δημιουργία κώδικα  |  Δημιουργία προσαρμοσμένων τύπων  |  Αναλύστε δεδομένα και δημιουργήστε γραφήματα  |  Επίκληση Λειτουργιών Kutools...
Δημοφιλή χαρακτηριστικά: Εύρεση, επισήμανση ή αναγνώριση διπλότυπων   |  Διαγραφή κενών γραμμών   |  Συνδυάστε στήλες ή κελιά χωρίς απώλεια δεδομένων   |   Γύρος χωρίς φόρμουλα ...
Σούπερ Αναζήτηση: VLookup πολλαπλών κριτηρίων    VLookup πολλαπλών τιμών  |   VLookup σε πολλά φύλλα   |   Ασαφής αναζήτηση ....
Σύνθετη αναπτυσσόμενη λίστα: Γρήγορη δημιουργία αναπτυσσόμενης λίστας   |  Εξαρτημένη αναπτυσσόμενη λίστα   |  Πολλαπλή αναπτυσσόμενη λίστα ....
Διαχειριστής στήλης: Προσθέστε έναν συγκεκριμένο αριθμό στηλών  |  Μετακίνηση στηλών  |  Εναλλαγή κατάστασης ορατότητας κρυφών στηλών  |  Συγκρίνετε εύρη και στήλες ...
Επιλεγμένα Χαρακτηριστικά: Εστίαση πλέγματος   |  Προβολή σχεδίου   |   Μεγάλη Formula Bar    Διαχείριση βιβλίου εργασίας & φύλλου   |  Βιβλιοθήκη πόρων (Αυτόματο κείμενο)   |  Επιλογή ημερομηνίας   |  Συνδυάστε φύλλα εργασίας   |  Κρυπτογράφηση/Αποκρυπτογράφηση κελιών    Αποστολή email ανά λίστα   |  Σούπερ φίλτρο   |   Ειδικό φίλτρο (φίλτρο με έντονη γραφή/πλάγια γραφή/διαγραφή...) ...
Κορυφαία 15 σύνολα εργαλείων12 Κείμενο Εργαλεία (Προσθήκη κειμένου, Κατάργηση χαρακτήρων, ...)   |   50 + Διάγραμμα Τύποι (Gantt διάγραμμα, ...)   |   40+ Πρακτικό ΜΑΘΗΜΑΤΙΚΟΙ τυποι (Υπολογίστε την ηλικία με βάση τα γενέθλια, ...)   |   19 Εισαγωγή Εργαλεία (Εισαγωγή κωδικού QR, Εισαγωγή εικόνας από το μονοπάτι, ...)   |   12 Μετατροπή Εργαλεία (Αριθμοί σε λέξεις, Μετατροπή Συναλλάγματος, ...)   |   7 Συγχώνευση & διαχωρισμός Εργαλεία (Σύνθετες σειρές συνδυασμού, Διαίρεση κελιών, ...)   |   ... κι αλλα

Αυξήστε τις δεξιότητές σας στο Excel με τα Kutools για Excel και απολαύστε την αποτελεσματικότητα όπως ποτέ πριν. Το Kutools για Excel προσφέρει πάνω από 300 προηγμένες δυνατότητες για την ενίσχυση της παραγωγικότητας και την εξοικονόμηση χρόνου.  Κάντε κλικ εδώ για να αποκτήσετε τη δυνατότητα που χρειάζεστε περισσότερο...

Περιγραφή


Το Office Tab φέρνει τη διεπαφή με καρτέλες στο Office και κάνει την εργασία σας πολύ πιο εύκολη

  • Ενεργοποίηση επεξεργασίας και ανάγνωσης καρτελών σε Word, Excel, PowerPoint, Publisher, Access, Visio και Project.
  • Ανοίξτε και δημιουργήστε πολλά έγγραφα σε νέες καρτέλες του ίδιου παραθύρου και όχι σε νέα παράθυρα.
  • Αυξάνει την παραγωγικότητά σας κατά 50% και μειώνει εκατοντάδες κλικ του ποντικιού για εσάς κάθε μέρα!
Comments (19)
Rated 5 out of 5 · 2 ratings
This comment was minimized by the moderator on the site
Como faço para ajustar a função do "MultipleValues" para ignorar células vazias?

Pois no meu caso tenho algumas células em branco e quando faço o merge, fico com algumas vírgulas repetidas.

Ex.: (001,002,,,,003).

Obrigado!
This comment was minimized by the moderator on the site
Hello, Bruno,
To ignore the blank cells when combine, pleae apply the following code:

Function SingleCellExtract(LookupValue As String, LookupRange As Range, ColumnNumber As Integer, Char As String) As String
    Dim I As Long
    Dim xRet As String
    For I = 1 To LookupRange.Columns(1).Cells.Count
        If LookupRange.Cells(I, 1) = LookupValue Then
            ' Check if the cell in the specified column is not empty before appending
            If Not IsEmpty(LookupRange.Cells(I, ColumnNumber)) Then
                If xRet = "" Then
                    xRet = LookupRange.Cells(I, ColumnNumber) & Char
                Else
                    xRet = xRet & LookupRange.Cells(I, ColumnNumber) & Char
                End If
            End If
        End If
    Next
    If Len(xRet) > 0 Then
        SingleCellExtract = Left(xRet, Len(xRet) - 1)
    Else
        SingleCellExtract = ""
    End If
End Function


Please have a try, hope it can help you!
This comment was minimized by the moderator on the site
Hello skyyang,

Yes, it works!

Thank you!
This comment was minimized by the moderator on the site
No soy programador pero necesito ayuda para que la función de abajo en lugar de devolverme esto

S-01-08-0017->Micro Post 1R, Black, Stainless Steel -End,->4;S-01-08-0057->Micro Post 2R, Black, Stainless Steel -End,->2

me devuelva los valores en líneas diferentes.
S-01-08-0017->Micro Post 1R, Black, Stainless Steel -End,->4
S-01-08-0057->Micro Post 2R, Black, Stainless Steel -End,->2

La función es:
Function SingleCellExtract(LookupValue As String, LookupRange As Range, ColumnNumber As Integer, Char As String)
'Updateby Extendoffice
Dim I As Long
Dim xRet As String
For I = 1 To LookupRange.Columns(1).Cells.Count
If LookupRange.Cells(I, 1) = LookupValue Then
If xRet = "" Then
xRet = LookupRange.Cells(I, ColumnNumber) & Char
Else
xRet = xRet & "" & LookupRange.Cells(I, ColumnNumber) & Char
End If
End If
Next
SingleCellExtract = Left(xRet, Len(xRet) - 1)
End Function
This comment was minimized by the moderator on the site
Hello, Yery,
Did you mean to split a cell into multiple rows based on the semicolon character?
If so, the following VBA code can help you:
Sub SplitAll()
    Dim xRg As Range
    Dim xRg1 As Range
    Dim xCell As Range
    Dim I As Long
    Dim xAddress As String
    Dim xUpdate As Boolean
    Dim xRet As Variant
    On Error Resume Next
    xAddress = Application.ActiveWindow.RangeSelection.Address
    Set xRg = Application.InputBox("Please select a range", "Kutools for Excel", xAddress, , , , , 8)
    Set xRg = Application.Intersect(xRg, xRg.Worksheet.UsedRange)
    If xRg Is Nothing Then Exit Sub
        If xRg.Columns.Count > 1 Then
            MsgBox "You can't select multiple columns", , "Kutools for Excel"
            Exit Sub
            End If
            Set xRg1 = Application.InputBox("Split to (single cell):", "Kutools for Excel", , , , , , 8)
            Set xRg1 = xRg1.Range("A1")
            If xRg1 Is Nothing Then Exit Sub
                xUpdate = Application.ScreenUpdating
                Application.ScreenUpdating = False
                For Each xCell In xRg
                    xRet = Split(xCell.Value, ";")
                    xRg1.Worksheet.Range(xRg1.Offset(I, 0), xRg1.Offset(I + UBound(xRet, 1), 0)) = Application.WorksheetFunction.Transpose(xRet)
                    I = I + UBound(xRet, 1) + 1
                Next
                Application.ScreenUpdating = xUpdate
            End Sub

Please have a try, hope it can help you!
This comment was minimized by the moderator on the site
Hi,

I want exact validation code for multiple values separated by comma and space for each value.

Example:
Lucy, Tom, Nicol, Akash, Apple

Please replay if you have any suggestions.
Rated 4.5 out of 5
This comment was minimized by the moderator on the site
Hello, Manikanta
To separate the multiple values by a comma and space, you just need to add a space behind the comma, change the formula as this: =SingleCellExtract(D2,A2:B15,2,", ").
Please try, hope it can help you!
This comment was minimized by the moderator on the site
Hi Skyyang,

Thanks for your replay!

I tried already same way but in the cell value last one extra comma (,) coming below is the example.

Lucy, Tom, Nicol, Akash, Apple,

This will not work for Json file, so I want values separated by comma and space like below.

Lucy, Tom, Nicol, Akash, Apple

Thank you!
This comment was minimized by the moderator on the site
Hello, Manikanta
In this case, you can apply the below User Defined Function:

Function MultipleValues(work_range As Range, criteria As Variant, merge_range As Range, Optional Separator As String = ",") As Variant
Dim outcome As String
On Error Resume Next
If work_range.Count <> merge_range.Count Then
MultipleValues = CVErr(xlErrRef)
Exit Function
End If
For i = 1 To work_range.Count
If work_range.Cells(i).Value = criteria Then
outcome = outcome & Separator & merge_range.Cells(i).Value
End If
Next i
If outcome <> "" Then
outcome = VBA.Mid(outcome, VBA.Len(Separator) + 1)
End If
MultipleValues = outcome
Exit Function
End Function


After pasting the code, please use this formula: =MultipleValues($A$2:$A$15,D2,$B$2:$B$15,", ")

Please try, hope this can help you!
If you still have any other problem, please comment here.
https://www.extendoffice.com/images/stories/comments/comment-skyyang/doc-return-multiple-matching-1.png
This comment was minimized by the moderator on the site
Hi Skyyang,

This is working now, Thank you for your quick response.

It's very use full to me once again Thank you for your help.

Regards,
Manikanta.
Rated 5 out of 5
This comment was minimized by the moderator on the site
Good Morning,

the VBA code worked perfectly with my worksheet, pretty clear and simple, however, I was trying to find a way to tell excel to only return the unique values. Would that be possible using this same code?
Rated 5 out of 5
This comment was minimized by the moderator on the site
When 2 Criteria Match then Return Multiple Lookup Values In One Comma Separated Cell
A2=B2 Then Result From Range by "SingleCellExtract" - Please.......
This comment was minimized by the moderator on the site
Error pops up if increase the array size
This comment was minimized by the moderator on the site
VB command breaks when the range is longer than 154 rows (ie :B154)....
This comment was minimized by the moderator on the site
thank you, firstly i managed to get this to work without the slow down in performance. I'm using values rather that text so my question is i want to bring back all those with less than say 19 points in a list. Can the single cell Extract work for that or does it have to be a specific value?
This comment was minimized by the moderator on the site
This simply does not work. I was unable to get it to work in my own application, so I copy/pasted the vba and the formula and it returned an error every time
This comment was minimized by the moderator on the site
This works but slows down my excel majorly! Any tips to help speed?
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations