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

Jak pominąć zwracanie wielu wartości w jednej komórce w programie Excel?

Zwykle w programie Excel, gdy używasz funkcji WYSZUKAJ.PIONOWO, jeśli istnieje wiele wartości pasujących do kryteriów, możesz po prostu uzyskać pierwszą. Ale czasami chcesz zwrócić wszystkie odpowiednie wartości, które spełniają kryteria, do jednej komórki, jak pokazano na zrzucie ekranu, jak możesz to rozwiązać?

Vlookup, aby zwrócić wiele wartości do jednej komórki za pomocą funkcji TEXTJOIN (Excel 2019 i Office 365)

Vlookup, aby zwrócić wiele wartości do jednej komórki za pomocą funkcji zdefiniowanej przez użytkownika

Vlookup, aby zwrócić wiele wartości do jednej komórki z przydatną funkcją


Vlookup, aby zwrócić wiele wartości do jednej komórki za pomocą funkcji TEXTJOIN (Excel 2019 i Office 365)

Jeśli masz wyższą wersję programu Excel, taką jak Excel 2019 i Office 365, dostępna jest nowa funkcja - TEKSTJOINDzięki tej potężnej funkcji możesz szybko przeglądać i zwracać wszystkie pasujące wartości do jednej komórki.

Vlookup, aby zwrócić wszystkie pasujące wartości do jednej komórki

Zastosuj poniższą formułę do pustej komórki, w której chcesz umieścić wynik, a następnie naciśnij Ctrl + Shift + Enter klucze razem, aby uzyskać pierwszy wynik, a następnie przeciągnij uchwyt wypełniania w dół do komórki, której chcesz użyć tej formuły, a otrzymasz wszystkie odpowiednie wartości, jak pokazano na zrzucie ekranu:

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

Uwaga: W powyższym wzorze A2: A11 czy zakres wyszukiwania zawiera dane wyszukiwania, E2 jest wartością wyszukiwania, C2: C11 to zakres danych, z którego mają zostać zwrócone pasujące wartości, ",”jest separatorem oddzielającym wiele rekordów.

Vlookup, aby zwrócić wszystkie pasujące wartości bez duplikatów do jednej komórki

Jeśli chcesz zwrócić wszystkie pasujące wartości na podstawie danych wyszukiwania bez duplikatów, może Ci pomóc poniższy wzór.

Skopiuj i wklej poniższą formułę do pustej komórki, a następnie naciśnij Ctrl + Shift + Enter klucze razem, aby uzyskać pierwszy wynik, a następnie skopiuj tę formułę, aby wypełnić inne komórki, a otrzymasz wszystkie odpowiadające jej wartości bez zdeformowanych, jak pokazano na zrzucie ekranu:

=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, ""))

Uwaga: W powyższym wzorze A2: A11 czy zakres wyszukiwania zawiera dane wyszukiwania, E2 jest wartością wyszukiwania, C2: C11 to zakres danych, z którego mają zostać zwrócone pasujące wartości, ",”jest separatorem oddzielającym wiele rekordów.

Vlookup, aby zwrócić wiele wartości do jednej komórki za pomocą funkcji zdefiniowanej przez użytkownika

Powyższa funkcja TEXTJOIN jest dostępna tylko dla Excel 2019 i Office 365, jeśli masz inne niższe wersje Excela, powinieneś użyć niektórych kodów do zakończenia tego zadania.

Vlookup, aby zwrócić wszystkie pasujące wartości do jednej komórki

1. Przytrzymaj ALT + F11 klucze i otwiera plik Microsoft Visual Basic for Applications okno.

2, Kliknij wstawka > Modułi wklej następujący kod w Okno modułu.

Kod VBA: Vlookup zwraca wiele wartości do jednej komórki

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. Następnie zapisz i zamknij ten kod, wróć do arkusza i wprowadź następującą formułę: =CONCATENATEIF($A$2:$A$11, E2, $C$2:$C$11, ", ") do określonej pustej komórki, w której chcesz umieścić wynik, a następnie przeciągnij uchwyt wypełniania w dół, aby uzyskać wszystkie odpowiednie wartości w jednej komórce, którą chcesz, zobacz zrzut ekranu:

Uwaga: W powyższym wzorze A2: A11 czy zakres wyszukiwania zawiera dane wyszukiwania, E2 jest wartością wyszukiwania, C2: C11 to zakres danych, z którego mają zostać zwrócone pasujące wartości, ",”jest separatorem oddzielającym wiele rekordów.

Vlookup, aby zwrócić wszystkie pasujące wartości bez duplikatów do jednej komórki

Aby zignorować duplikaty w zwracanych pasujących wartościach, wykonaj poniższy kod.

1. Przytrzymaj Alt + F11 klawisze, aby otworzyć Microsoft Visual Basic for Applications okno.

2, Kliknij wstawka > Modułi wklej następujący kod w Okno modułu.

Kod VBA: przeglądaj i zwracaj wiele unikalnych dopasowanych wartości do jednej komórki

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. Po wstawieniu kodu kliknij Narzędzia > Referencje w otwartym Microsoft Visual Basic for Applications okno, a następnie w wyskoczył Referencje - VBAProject okno dialogowe, zaznacz Środowisko wykonawcze skryptów firmy Microsoft Microsoft opcja w Dostępne referencje pole listy, zobacz zrzuty ekranu:

4. Następnie kliknij OK aby zamknąć okno dialogowe, zapisz i zamknij okno kodu, wróć do arkusza i wprowadź następującą formułę: =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:

Uwaga: W powyższym wzorze A2: C11 to zakres danych, którego chcesz użyć, E2 jest wartością wyszukiwania, liczbą 3 to numer kolumny zawierającej zwrócone wartości.

Vlookup, aby zwrócić wiele wartości do jednej komórki z przydatną funkcją

 Jeśli posiadamy Kutools dla programu Excel, Z siedzibą w Zaawansowane wiersze łączenia możesz szybko scalać lub łączyć wiersze na podstawie tej samej wartości i wykonywać obliczenia według potrzeb.

Uwaga:Aby to zastosować Zaawansowane wiersze łączenia, po pierwsze, należy pobrać plik Kutools dla programu Excel, a następnie szybko i łatwo zastosuj tę funkcję.

Po zainstalowaniu Kutools dla programu Excelwykonaj następujące czynności:

1. Wybierz zakres danych, w którym chcesz połączyć dane z jednej kolumny na podstawie innej kolumny.

2, Kliknij Kutools > Połącz i podziel > Zaawansowane wiersze łączeniazobacz zrzut ekranu:

3. W wyskoczył Zaawansowane wiersze łączenia Okno dialogowe:

  • Kliknij nazwę kolumny kluczowej, na podstawie której chcesz połączyć, a następnie kliknij Główny klucz
  • Następnie kliknij inną kolumnę, którą chcesz połączyć z danymi na podstawie kolumny kluczowej, i kliknij Połączyć aby wybrać jeden separator do oddzielenia połączonych danych.

4. Następnie kliknij OK przycisk, a otrzymasz następujące wyniki:

Pobierz i bezpłatną wersję próbną Kutools dla programu Excel teraz!


Więcej artykułów względnych:

  • Funkcja WYSZUKAJ.PIONOWO z kilkoma podstawowymi i zaawansowanymi przykładami
  • W programie Excel funkcja WYSZUKAJ.PIONOWO jest potężną funkcją dla większości użytkowników programu Excel, która służy do wyszukiwania wartości w lewym skrajnym zakresie danych i zwracania pasującej wartości w tym samym wierszu z określonej kolumny. W tym samouczku omówiono sposób korzystania z funkcji WYSZUKAJ.PIONOWO z kilkoma podstawowymi i zaawansowanymi przykładami w programie Excel.
  • Zwróć wiele pasujących wartości na podstawie jednego lub wielu kryteriów
  • Zwykle wyszukiwanie określonej wartości i zwracanie pasującego elementu jest łatwe dla większości z nas przy użyciu funkcji WYSZUKAJ.PIONOWO. Ale czy kiedykolwiek próbowałeś zwrócić wiele pasujących wartości na podstawie co najmniej jednego kryterium? W tym artykule przedstawię kilka formuł do rozwiązania tego złożonego zadania w programie Excel.
  • Wyszukaj i zwróć wiele wartości w pionie
  • Zwykle możesz użyć funkcji Vlookup, aby uzyskać pierwszą odpowiednią wartość, ale czasami chcesz zwrócić wszystkie pasujące rekordy na podstawie określonego kryterium. W tym artykule omówię, jak przeglądać i zwracać wszystkie pasujące wartości pionowo, poziomo lub do jednej komórki.
  • Wyszukaj i zwróć wiele wartości z listy rozwijanej
  • W programie Excel, jak można pominąć i zwrócić wiele odpowiadających wartości z rozwijanej listy, co oznacza, że ​​po wybraniu jednego elementu z rozwijanej listy wszystkie jego względne wartości są wyświetlane jednocześnie. W tym artykule przedstawię rozwiązanie krok po kroku.

Najlepsze narzędzia biurowe

Kutools dla programu Excel rozwiązuje większość problemów i zwiększa produktywność o 80%

  • Ponowne użycie: Szybko włóż złożone wzory, wykresy i wszystko, czego używałeś wcześniej; Szyfruj komórki z hasłem; Utwórz listę mailingową i wysyłaj e-maile ...
  • Pasek Super Formula (łatwo edytować wiele wierszy tekstu i formuły); Układ do czytania (łatwe odczytywanie i edytowanie dużej liczby komórek); Wklej do filtrowanego zakresu...
  • Scal komórki / wiersze / kolumny bez utraty danych; Podziel zawartość komórek; Połącz zduplikowane wiersze / kolumny... Zapobiegaj zduplikowanym komórkom; Porównaj zakresy...
  • Wybierz Duplikat lub Unikalny Wydziwianie; Wybierz puste wiersze (wszystkie komórki są puste); Super Find i Fuzzy Find w wielu zeszytach ćwiczeń; Losowy wybór ...
  • Dokładna kopia Wiele komórek bez zmiany odwołania do formuły; Automatyczne tworzenie odniesień do wielu arkuszy; Wstaw punktory, Pola wyboru i nie tylko ...
  • Wyodrębnij tekst, Dodaj tekst, Usuń według pozycji, Usuń przestrzeń; Tworzenie i drukowanie podsumowań stronicowania; Konwertuj zawartość komórek i komentarze...
  • Super filtr (zapisz i zastosuj schematy filtrów do innych arkuszy); Zaawansowane sortowanie według miesiąca / tygodnia / dnia, częstotliwości i innych; Specjalny filtr pogrubieniem, kursywą ...
  • Połącz skoroszyty i arkusze robocze; Scal tabele na podstawie kluczowych kolumn; Podziel dane na wiele arkuszy; Konwersja wsadowa xls, xlsx i PDF...
  • Ponad 300 zaawansowanych funkcji. Obsługuje Office / Excel 2007-2021 i 365. Obsługuje wszystkie języki. Łatwe wdrażanie w przedsiębiorstwie lub organizacji. Pełne funkcje 30-dniowa bezpłatna wersja próbna. 60-dniowa gwarancja zwrotu pieniędzy.
karta kte 201905

Karta Office wprowadza interfejs z zakładkami do pakietu Office i znacznie ułatwia pracę

  • Włącz edycję i czytanie na kartach w programach Word, Excel, PowerPoint, Publisher, Access, Visio i Project.
  • Otwieraj i twórz wiele dokumentów w nowych kartach tego samego okna, a nie w nowych oknach.
  • Zwiększa produktywność o 50% i redukuje setki kliknięć myszką każdego dnia!
officetab dół
Sortuj komentarze według
Komentarze (43)
Brak ocen. Oceń jako pierwszy!
Ten komentarz został zminimalizowany przez moderatora na stronie
Jak dostosować tę formułę, aby oddzielić każdą zwróconą wartość, ale „,”, a także zwracać tylko unikalne wartości?
Ten komentarz został zminimalizowany przez moderatora na stronie
Dzięki za kod!!

Jeśli chodzi o symbole wieloznaczne, sposobem na obejście jest użycie INSTR

Możesz zamienić [ If rng = pValue Then ] na [ InStr(1, rng.Value, pValue) Then ], a jeśli nie chcesz, aby wielkość liter była rozróżniana, użyj [ InStr(1, rng.Value, pValue, vbTextPorównaj) Następnie ]
Ten komentarz został zminimalizowany przez moderatora na stronie
Dzięki za powyższy kod VBA. Czy możesz mi powiedzieć jak wpisać wyniki do nowej linii w komórce czyli jak Alt-Enter 300 400 1000 1300
Ten komentarz został zminimalizowany przez moderatora na stronie
Dziękujemy za udostępnienie powyższego kodu. Używam tego od kilku miesięcy, ale dziś wydaje się, że nie działa. Otrzymuję puste komórki zamiast zwykłego błędu, gdy są dane do zwrócenia. jakieś pomysły?
Ten komentarz został zminimalizowany przez moderatora na stronie
Świetna praca.. Mam dokładnie to, czego chcę !!! Kocham to !!
Ten komentarz został zminimalizowany przez moderatora na stronie
Cześć, jestem naprawdę pod wrażeniem pracy i tak łatwo ją stworzyć, aby korzystać z tej funkcji. jednak potrzebuję dalszego wsparcia. Mój ? jest to, jak mogę wybrać liczbę z komórki z wieloma komórkami w mojej tablicy podglądu. tzn. jeśli komórka A1 = 100, A2 = 350, A3 = 69, C1 = 100; 1222; 12133 C2 = 69; 222 D1 = Jabłko D2 = banan Więc jak mogę wybrać 100 z mojej kolumny tablicy tabeli C, aby uzyskać korespondenta D1 = jabłko Proszę zauważyć, że mam 7-cyfrowe liczby w mojej wartości wyszukiwania i tablicy tabeli, które są oddzielone znakiem ";". Byłbym naprawdę wdzięczny, gdybyś mógł to rozwiązać i pomóc mi zaoszczędzić dużo czasu.
Ten komentarz został zminimalizowany przez moderatora na stronie
Dziękuję za kod VBA. Dostałem dokładnie to, czego chcę! Zmodyfikowałem tylko kod "rng.Offset(0, pIndex - 1)" na "rng.Offset(0, pIndex - 2)". Tak samo MYVLOOKUP może wyszukiwać od prawej do lewej.
Ten komentarz został zminimalizowany przez moderatora na stronie
To jest dokładnie to, czego szukałem i nie myślałem tylko o stworzeniu własnego UDF. Jednak nie będzie działać dokładnie tak, jak WYSZUKAJ.PIONOWO. Jeśli szukany ciąg nie znajduje się tylko w pierwszej kolumnie, może to dać dane poza pierwotnym przekazanym zakresem. Imię Numer Inna nazwa Kolumna poza zakresem minęła Jay 1 Jay 1 Jay 2 Jay 2 Chris 3 Chris 3 Jorge 4 Jorge 4 Jay 5 Jay 5 Jorge 6 Jorge 6 Jeśli w powyższej tabeli były komórki A1:D7 jeśli zdałeś tylko A1:C7 Funkcja „MYVLOOKUP” zwraca 1 1 2 2 5 5, kiedy można by oczekiwać, że zwróci 1 2 5. Poniższe zmiany rozwiązują problem: Funkcja MYVLOOKUP(pValue As String, pWorkRng As Range, pIndex As Long) 'Aktualizuj 20150310 'Zaktualizowano 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
Ten komentarz został zminimalizowany przez moderatora na stronie
Działa to świetnie, ale potrzebuję pomocy z poleceniem usunięcia duplikatów z wyników. Poważnie, świetna robota.
Ten komentarz został zminimalizowany przez moderatora na stronie
Działa to świetnie, ale nadal potrzebuję pomocy z funkcją polecenia, aby usunąć duplikaty z wyników.
Ten komentarz został zminimalizowany przez moderatora na stronie
Powiadamiaj mnie o dalszych komentarzach
Ten komentarz został zminimalizowany przez moderatora na stronie
Nic nie zwracaj! po zastosowaniu MYLOOKUP nie dającego wyniku, ale puste.
Ten komentarz został zminimalizowany przez moderatora na stronie
Cześć, działa dobrze. To, co chciałbym zrobić, to zaadaptować kod, aby oddzielić wyniki wartości za pomocą "///" lub dowolnego innego znacznika (z powodów technicznych nie chcę tylko pojedynczego separatora znaków). Zauważyłem również, że ta formuła nie działa z dziką kartą. Wiem, że proszę o zbyt wiele, ale to nie działa, ponieważ funkcja vlookup może działać, gdy wyszukuję = myvlookup ("*"&E6&"*",$A$2:$C$15,2), co zrobiłby/może zrobić. Jakaś pomoc?
Ten komentarz został zminimalizowany przez moderatora na stronie
Heads-up. Wymyśliłem, jak uzyskać dowolny separator w tym wyjściu. Jego szczątkowe. Ale rozgryzłem to. xResult = xResult & "///" & rng.Offset(0, pIndex - 1) Ostatnią i najbardziej pożądaną rzeczą jest umożliwienie pracy z symbolami wieloznacznymi w kryteriach wyszukiwania. Jeszcze raz dziękuję za to piękne i genialne rozwiązanie. Niezwykle pomocny. Teraz chcę tylko uruchomić makro i zainstalować je na stałe w moim programie Excel, bez względu na to, co robię, abym mógł go używać, kiedy potrzebuję. I dzikie karty! Dziękuję bardzo. Jedyne, co pozostało do zrobienia, to symbole wieloznaczne.
Ten komentarz został zminimalizowany przez moderatora na stronie
Aby uzyskać unikalny rekord, możesz użyć poniższego: (zmodyfikowane przez odwołanie innego kodu użytkownika) 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 Następnie sTemp = sTemp & sDelimiter & varSection End If Next varSection MYVLOOKUP = Mid(sTemp, Len(sDelimiter) + 1) End Function
Ten komentarz został zminimalizowany przez moderatora na stronie
Działało to idealnie, ale zajęło mi trochę czasu, aby funkcja działała poprawnie w moim arkuszu kalkulacyjnym z 20 zakładkami i 50 tys. wierszy. Teraz WIELKIE pytanie brzmi, jak wziąć ten rozdzielany ciąg, a następnie użyć każdego wpisu jako indeksu/dopasowania (nie powiązanego z indeksem/dopasowaniem, ale wydaje się to szybsze) w innym zestawie danych, zwracając wartość SUM wszystkich zwrotów do jednej komórki . Mój scenariusz jest taki, że mam pojedyncze zamówienie, które ma wiele faktur. Twoja funkcja MYVLOOKUP działa znakomicie, zgłaszając wszystkie faktury w jednej komórce. To, co chcę teraz zrobić, to wziąć każdy połączony zwrot z raportowaną komórką, przejrzeć tę tablicę i zsumować kwoty płatności każdej faktury z powrotem do komórki z formułą. Doceniam wszelką pomoc, jaką możesz zaoferować i dziękuję za funkcję MYVLOOKUP!
Ten komentarz został zminimalizowany przez moderatora na stronie
Bez względu na to, co robię, zawsze otrzymuję #value! zwrócone zamiast wyniku. vlookup działa dobrze, więc dane działają. Śledziłem już proces włączania makr. Połączyłem nawet wszystko w jeden arkusz. Jakieś pomysły??
Ten komentarz został zminimalizowany przez moderatora na stronie
Świetne makro, przydatne. Ale musisz wiedzieć, czy można go zmodyfikować, aby sprawdzić 2 kryteria i czy ktoś znalazł i tak, aby działały na nim symbole wieloznaczne. Jakaś pomoc?
Ten komentarz został zminimalizowany przez moderatora na stronie
Czy istnieje sposób na zmianę wyniku tak, aby zamiast 1000 1000 -1000 wyświetlał na przykład 1,000/1,000/(1,000) ?
Ten komentarz został zminimalizowany przez moderatora na stronie
Świetna funkcja, jednak przebijanie się przez 100,000 XNUMX rekordów dowodzi trochę dla mojego biednego laptopa, będzie musiało działać przez noc!
Ten komentarz został zminimalizowany przez moderatora na stronie
To niesamowite, dziękuję!
Nie ma tu jeszcze żadnych komentarzy
Pokaż więcej
Zostaw swój komentarz
Publikowanie jako gość
×
Oceń ten post:
0   Postacie
Sugerowane lokalizacje

Bądż na bieżąco

Prawa autorskie © 2009 - www.extendoffice.com. | Wszelkie prawa zastrzeżone. Zasilany przez ExtendOffice, | Mapa strony
Microsoft i logo Office są znakami towarowymi lub zastrzeżonymi znakami towarowymi Microsoft Corporation w Stanach Zjednoczonych i / lub innych krajach.
Chronione przez Sectigo SSL