Uzyskaj wiele wartości wyszukiwania w jednej komórce (z powtórzeniem i bez)

Czy możemy wyszukać i zwrócić wiele wartości w jednej komórce w programie Excel (oddzielonych przecinkiem lub spacją)?

To pytanie wielokrotnie zadawało mi wielu moich kolegów i czytelników.

Excel ma niesamowite formuły wyszukiwania, takie jak WYSZUKAJ.PIONOWO, INDEKS/MATCH (a teraz WYSZUKAJ.X), ale żadna z nich nie oferuje sposobu na zwrócenie wielu pasujących wartości. Wszystkie te działania polegają na zidentyfikowaniu pierwszego dopasowania i zwróceniu go.

Zrobiłem więc trochę kodowania VBA, aby wymyślić niestandardową funkcję (zwaną również funkcją zdefiniowaną przez użytkownika) w programie Excel.

Aktualizacja: Po wydaniu przez program Excel tablic dynamicznych i niesamowitych funkcji, takich jak UNIQUE i TEXTJOIN, można teraz używać prostej formuły i zwróć wszystkie pasujące wartości w jednej komórce (omówione w tym samouczku).

W tym samouczku pokażę Ci, jak to zrobić (jeśli korzystasz z najnowszej wersji Excela - Microsoft 365 ze wszystkimi nowymi funkcjami), a także jak to zrobić w przypadku korzystania ze starszych wersji ( przy użyciu VBA).

Więc zacznijmy!

Wyszukaj i zwróć wiele wartości w jednej komórce (przy użyciu formuły)

Jeśli używasz programu Excel 2016 lub wcześniejszych wersji, przejdź do następnej sekcji, w której pokazuję, jak to zrobić za pomocą VBA.

Dzięki subskrypcji Microsoft 365 Twój Excel ma teraz o wiele bardziej zaawansowane funkcje i funkcje, których nie było w poprzednich wersjach (takie jak XLOOKUP, tablice dynamiczne, funkcje UNIKALNE/FILTROWANIE itp.)

Jeśli więc korzystasz z Microsoft 365 (wcześniej znanego jako Office 365), możesz użyć metod opisanych w tej sekcji, aby wyszukać i zwrócić wiele wartości w jednej komórce w programie Excel.

Jak zobaczysz, to naprawdę prosta formuła.

Poniżej mam zestaw danych, w którym mam nazwiska osób z kolumny A oraz szkolenia, które odbyli w kolumnie B.

Kliknij tutaj, aby pobrać przykładowy plik i podążaj dalej

Dla każdej osoby chcę dowiedzieć się, jakie szkolenie ukończyła. W kolumnie D mam listę unikalnych nazw (z kolumny A) i chcę szybko wyszukać i wyodrębnić wszystkie szkolenia, które wykonała każda osoba, i uzyskać je w jednym zestawie (oddzielonym przecinkiem).

Poniżej znajduje się formuła, która to zrobi:

=TEXTJOIN(", ",PRAWDA,JEŻELI(D2=$A$2:$A$20,$B$2:$B$20,""))

Po wprowadzeniu formuły w komórce E2 skopiuj ją dla wszystkich komórek, w których chcesz uzyskać wyniki.

Jak działa ta formuła?

Pozwólcie, że zdekonstruuję tę formułę i wyjaśnię, jak każda część, w jaki sposób łączy się w całość, daje nam wynik.

Test logiczny w formule JEŻELI (D2=$A$2:$A$20) sprawdza, czy komórka nazwy D2 jest taka sama jak w zakresie A2:A20.

Przechodzi przez każdą komórkę w zakresie A2:A20 i sprawdza, czy nazwa jest taka sama w komórce D2, czy nie. jeśli to ta sama nazwa, zwraca TRUE, w przeciwnym razie zwraca FALSE.

Ta część formuły da ci tablicę, jak pokazano poniżej:

{PRAWDA;FAŁSZ;FAŁSZ;PRAWDA;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ}

Ponieważ chcemy uzyskać tylko szkolenie dla Boba (wartość w komórce D2), musimy uzyskać wszystkie odpowiednie szkolenia dla komórek, które zwracają TRUE w powyższej tablicy.

Można to łatwo zrobić, określając część [wartość_jeżeli_prawda] formuły JEŻELI jako zakres, w którym jest trenowane. Daje to pewność, że jeśli nazwa w komórce D2 jest zgodna z nazwą z zakresu A2:A20, formuła JEŻELI zwróci całe szkolenie, które osoba odbyła.

I gdziekolwiek tablica zwraca FALSE, podaliśmy wartość [value_if_false] jako „” (puste), więc zwraca ona spację.

Część JEŻELI formuły zwraca tablicę, jak pokazano poniżej:

{„Excel”;””;”;”PowerPoint”;””;”;”;”;”;””;”;”;”;”;”;”;”;””;””;””;””;””}

Gdzie ma imiona treningowe, które odbył Bob, i puste miejsca, gdzie nie było Boba.

Teraz wystarczy połączyć te nazwy treningu (oddzielone przecinkiem) i zwrócić je w jednej komórce.

Można to łatwo zrobić za pomocą nowej formuły TEXTJOIN (dostępnej w Excel2021-2022 i Excel w Microsoft 365)

Formuła TEXTJOIN przyjmuje trzy argumenty:

• Ogranicznik - który w naszym przykładzie jest „, ”, ponieważ chcę, aby trening był oddzielony przecinkiem i spacją
• PRAWDA - co nakazuje formule TEXTJOIN ignorowanie pustych komórek i łączenie tylko tych, które nie są puste
• Formuła Jeśli zwracająca tekst, który należy połączyć

Jeśli używasz Excela w Microsoft 365, który ma już tablice dynamiczne, możesz po prostu wpisać powyższą formułę i nacisnąć Enter. A jeśli używasz Excel2021-2022, musisz wprowadzić formułę i przytrzymać klawisz Control i Shift, a następnie nacisnąć Enter

Kliknij tutaj, aby pobrać przykładowy plik i podążaj dalej

Uzyskaj wiele wartości wyszukiwania w jednej komórce (bez powtórzeń)

Ponieważ formuła UNIQUE jest dostępna tylko w programie Excel w Microsoft 365, nie będzie można użyć tej metody w programie Excel2021-2022

Jeśli w zestawie danych występują powtórzenia, jak pokazano poniżej, musisz nieco zmienić formułę, aby uzyskać tylko listę unikalnych wartości w jednej komórce.

W powyższym zestawie danych niektóre osoby wielokrotnie przechodziły treningi. Na przykład Bob i Stan dwukrotnie przeszli szkolenie w zakresie Excela, a Betty dwukrotnie przeszła szkolenie z MS Word. Ale w naszym wyniku nie chcemy powtarzać nazwy treningu.

W tym celu możesz użyć poniższej formuły:

=TEKSTJOIN(", ",PRAWDA,UNIKALNE(JEŻELI(D2=$A$2:$A$20,$B$2:$B$20,"")))

Powyższa formuła działa w ten sam sposób, z niewielką zmianą. użyliśmy formuły JEŻELI w funkcji UNIKATOWE, aby w przypadku powtórzeń w wyniku formuły JEŻELI funkcja UNIKATOWA usunęłaby ją.

Kliknij tutaj, aby pobrać przykładowy plik

Wyszukaj i zwróć wiele wartości w jednej komórce (przy użyciu VBA)

Jeśli używasz programu Excel 2016 lub wcześniejszych wersji, nie będziesz mieć dostępu do formuły TEKSTJOIN. Najlepszym sposobem, aby następnie wyszukać i uzyskać wiele pasujących wartości w jednej komórce, jest użycie niestandardowej formuły, którą można utworzyć za pomocą VBA.

Aby uzyskać wiele wartości wyszukiwania w jednej komórce, musimy utworzyć funkcję w języku VBA (podobną do funkcji WYSZUKAJ.PIONOWO), która sprawdza każdą komórkę w kolumnie i jeśli wartość wyszukiwania zostanie znaleziona, dodaje ją do wyniku.

Oto kod VBA, który może to zrobić:

'Kod Sumit Bansal (https://trumpexcel.com) Funkcja SingleCellExtract(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer) Dim i As Long Dim Wynik jako String For i = 1 To LookupRange.Columns(1).Cells .Count If LookupRange.Cells(i, 1) = Lookupvalue Then Result = wynik & " " & LookupRange.Cells(i, ColumnNumber) & "," End If Next i SingleCellExtract = Left(Result, Len(Result) - 1) Koniec funkcji

Gdzie umieścić ten kod?

1. Otwórz skoroszyt i kliknij Alt + F11 (otworzy się okno edytora VBA).
2. W tym oknie edytora VBA po lewej stronie znajduje się eksplorator projektów (w którym znajdują się wszystkie skoroszyty i arkusze). Kliknij prawym przyciskiem myszy dowolny obiekt w skoroszycie, w którym ma działać ten kod, i przejdź do Wstaw -> Moduł.
3. W oknie modułu (które pojawi się po prawej stronie) skopiuj i wklej powyższy kod.
4. Teraz wszystko gotowe. Przejdź do dowolnej komórki w skoroszycie i wpisz =Wyciąg z pojedynczej komórki i podłącz wymagane argumenty wejściowe (tj. LookupValue, LookupRange, ColumnNumber).

Jak działa ta formuła?

Ta funkcja działa podobnie do funkcji WYSZUKAJ.PIONOWO.

Jako dane wejściowe przyjmuje 3 argumenty:

1. Wartość wyszukiwania - Ciąg, który musimy wyszukać w szeregu komórek.
2. Zakres wyszukiwania - Tablica komórek, z których musimy pobrać dane (w tym przypadku $B3:$C18).
3. NumerKolumny - Jest to numer kolumny tabeli/tablicy, z której ma zostać zwrócona pasująca wartość (w tym przypadku 2).

Gdy używasz tej formuły, sprawdza ona każdą komórkę w skrajnej lewej kolumnie w zakresie wyszukiwania a gdy znajdzie dopasowanie, dodaje do wyniku w komórce, w której użyto formuły.

Pamiętać: Zapisz skoroszyt jako skoroszyt z obsługą makr (xlsm lub xls), aby ponownie użyć tej formuły. Ponadto ta funkcja byłaby dostępna tylko w tym skoroszycie, a nie we wszystkich skoroszytach.

Kliknij tutaj, aby pobrać przykładowy plik

Dowiedz się, jak zautomatyzować nudne, powtarzalne zadania za pomocą VBA w programie Excel. Dołącz Kurs Excela VBA

Uzyskaj wiele wartości wyszukiwania w jednej komórce (bez powtórzeń)

Istnieje możliwość, że możesz mieć powtórzenia w danych.

Jeśli użyjesz kodu użytego powyżej, da ci on również powtórzenia w wyniku.

Jeśli chcesz uzyskać wynik, w którym nie ma powtórzeń, musisz nieco zmodyfikować kod.

Oto kod VBA, który daje wiele wartości wyszukiwania w jednej komórce bez żadnych powtórzeń.

'Kod Sumit Bansal (https://trumpexcel.com) Funkcja MultipleLookupNoRept(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer) Dim i As Long Dim Wynik jako String For i = 1 To LookupRange.Columns(1).Cells .Count If LookupRange.Cells(i, 1) = Lookupvalue Then For J = 1 To i - 1 If LookupRange.Cells(J, 1) = Lookupvalue Then If LookupRange.Cells(J, ColumnNumber) = LookupRange.Cells(i, ColumnNumber) Następnie GoTo Pomiń End If End If Next J Wynik = Wynik & " " & LookupRange.Cells(i, ColumnNumber) & "," Skip: End If Next i MultipleLookupNoRept = Left(Result, Len(Result) - 1) End Funkcjonować

Po umieszczeniu tego kodu w edytorze VB (jak pokazano powyżej w samouczku), będziesz mógł używać Multiple LookupNoRept funkcjonować.

Oto migawka wyniku, który uzyskasz dzięki temu Multiple LookupNoRept funkcjonować.

Kliknij tutaj, aby pobrać przykładowy plik

W tym samouczku omówiłem, jak używać formuł i VBA w programie Excel do znajdowania i zwracania wielu wartości wyszukiwania w jednej komórce w programie Excel.

Chociaż można to łatwo zrobić za pomocą prostej formuły, jeśli korzystasz z programu Excel w subskrypcji Microsoft 365, jeśli korzystasz z wcześniejszych wersji i nie masz dostępu do funkcji takich jak TEXTJOIN, nadal możesz to zrobić za pomocą VBA, tworząc własna funkcja niestandardowa.