Jak pominąć i połączyć wiele odpowiadających wartości w programie Excel?
Jak wszyscy wiemy, plik Vlookup funkcja w programie Excel może pomóc nam w wyszukaniu wartości i zwróceniu odpowiednich danych w innej kolumnie, ale generalnie może uzyskać pierwszą wartość względną tylko wtedy, gdy istnieje wiele pasujących danych. W tym artykule omówię, jak przeglądać i łączyć wiele odpowiednich wartości tylko w jednej komórce lub na liście pionowej.
Wyszukaj i zwróć wiele pasujących wartości pionowo za pomocą formuły
Wyszukaj i połącz wiele pasujących wartości w komórce za pomocą Kutools for Excel
Wyszukaj i zwróć wiele pasujących wartości pionowo za pomocą formuły
Przypuśćmy, że mam następujący zakres danych, aby uzyskać wszystkie odpowiednie wartości na podstawie określonej wartości w pionie, jak pokazano na poniższym zrzucie ekranu, możesz zastosować formułę tablicową.
1. Wprowadź tę formułę: =IF(COUNTIF($A$1:$A$16,$D$2)>=ROWS($1:1),INDEX($B$1:$B$16,SMALL(IF($A$1:$A$16=$D$2,ROW($1:$16)),ROW(1:1))),"") do pustej komórki, w której chcesz umieścić wynik, na przykład E2, a następnie naciśnij Ctrl + Shift + Enter klucze razem, aby uzyskać względną podstawę wartości na podstawie określonego kryterium, patrz zrzut ekranu:
Note: W powyższym wzorze:
A1: A16 to zakres kolumn zawierający określoną wartość, której chcesz szukać;
D2 wskazuje konkretną wartość, którą chcesz przeoczyć;
B1: B16 to zakres kolumn, z którego mają zostać zwrócone odpowiednie dane;
$ 1: $ 16 wskazuje odwołanie do wierszy w zakresie.
2. Następnie wybierz komórkę E2 i przeciągnij uchwyt wypełniania w dół do komórek, aż uzyskasz puste komórki, a wszystkie pasujące wartości zostaną wymienione w kolumnie, jak pokazano na poniższym zrzucie ekranu:
Wyszukaj i połącz wiele pasujących wartości w komórce za pomocą funkcji zdefiniowanej przez użytkownika
Zamiast pobierać względne wartości w pionie, czasami chcesz dopasować wartości do jednej komórki i połączyć je z określonym separatorem. W takim przypadku następująca funkcja zdefiniowana przez użytkownika może wyświadczyć Ci przysługę.
1. Przytrzymaj przycisk ALT + F11 klawisze, aby otworzyć Microsoft Visual Basic for Applications okno.
2. Kliknij wstawka > Modułi wklej następujący kod w Moduł Okno.
Kod VBA: przeglądaj i łącz wiele pasujących wartości w komórce
Function CusVlookup(lookupval, lookuprange As Range, indexcol As Long)
'updateby Extendoffice
Dim x As Range
Dim result As String
result = ""
For Each x In lookuprange
If x = lookupval Then
result = result & " " & x.Offset(0, indexcol - 1)
End If
Next x
CusVlookup = result
End Function
3. Następnie zapisz i zamknij ten kod, wróć do arkusza i wprowadź następującą formułę: = cusvlookup (D2, A1: B16,2) do pustej komórki, w której chcesz umieścić wynik, i naciśnij Wchodzę klucz, wszystkie odpowiadające wartości oparte na określonych danych zostały zwrócone do jednej komórki z separatorem spacji, patrz zrzut ekranu:
Note: W powyższym wzorze: D2 wskazuje wartości komórek, które chcesz wyszukać, A1: B16 to zakres danych, z którego chcesz pobrać dane, liczba 2 to numer kolumny, z której ma zostać zwrócona pasująca wartość, możesz zmienić te odniesienia w zależności od potrzeb.
Wyszukaj i połącz wiele pasujących wartości w komórce za pomocą Kutools for Excel
Jeśli Kutools dla programu Excel, Z siedzibą w Zaawansowane wiersze łączenia możesz szybko i łatwo zakończyć tę pracę. Ta funkcja może pomóc w połączeniu wszystkich pasujących wartości z określonymi separatorami na podstawie tych samych danych w innej kolumnie.
| Kutools dla programu Excel : z ponad 300 poręcznymi dodatkami Excela, które można wypróbować bez ograniczeń w ciągu 30 dni. |
Po zainstalowaniu Kutools dla programu Excelwykonaj następujące czynności:
1. Wybierz zakres danych, dla których chcesz uzyskać odpowiednie wartości na podstawie określonych danych.
2. Następnie kliknij Kutools > Połącz i podziel > Zaawansowane wiersze łączeniazobacz zrzut ekranu:
3, w Zaawansowane wiersze łączenia w oknie dialogowym kliknij nazwę kolumny, na podstawie której chcesz połączyć, a następnie kliknij Główny klucz przycisk, zobacz zrzut ekranu:
4. Następnie kliknij nazwę innej kolumny, dla której chcesz zwrócić dopasowane wartości, i kliknij Połączyć aby wybrać jeden separator do oddzielenia połączonych wartości, zobacz zrzut ekranu:
5. A następnie kliknij Ok przycisk, wszystkie odpowiednie wartości oparte na tych samych wartościach zostały połączone razem z określonym separatorem, patrz zrzuty ekranu:
 |
 |
 |
Pobierz i bezpłatną wersję próbną Kutools dla programu Excel teraz!
Demo: przeglądaj i łącz wiele pasujących wartości w komórce za pomocą Kutools for Excel
Najlepsze narzędzia biurowe
Zwiększ swoje umiejętności Excela dzięki Kutools for Excel i doświadcz wydajności jak nigdy dotąd. Kutools dla programu Excel oferuje ponad 300 zaawansowanych funkcji zwiększających produktywność i oszczędzających czas. Kliknij tutaj, aby uzyskać funkcję, której najbardziej potrzebujesz...
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!
