Excel VBA 12 Funkcja WYSZUKAJ PIONOWO, która wyciąga wszystkie znalezione wartości

Подписаться 21 тыс.

Просмотров 33 тыс.

50% 1

Zobacz kurs wideo Mistrz Excela
www.udemy.com/mistrz-excela/?...
Darmowy Kurs Excel podstawy exceliadam.pl/darmowy-kurs
Funkcja WYSZUKAJ.PIONOWO zwraca tylko pierwszy znaleziony element i nie szuka już innych, a czasami chcesz, żeby znaleźć wszystkie elementy spełniające warunek.
Żeby to osiągnąć możesz napisać funkcję w VBA, która będzie wyszukiwała wszystkich elementów korespondujących z szukaną wartością.
Function WyszukajWszystkie(Szukana As String, Zakres As Range, NrKolumny As Integer) As String
Dim i As Integer
For i = 1 To Zakres.Rows.Count
If Zakres.Cells(i, 1) = Szukana Then
WyszukajWszystkie = WyszukajWszystkie & Zakres.Cells(i, NrKolumny) & ", "
End If
Next i
WyszukajWszystkie = Left(WyszukajWszystkie, Len(WyszukajWszystkie) - 2)
End Function
Opiera się o formuły:
Pliki do pobrania na stronie:
exceliadam.pl/youtube

Опубликовано:

9 окт 2015

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 68

@Jackpack75 6 лет назад

Dzięki, a ja myślałem że nie mogę robić swoich formuł. Teraz życie stało się łatwiejsze.

@MrXmaciek 5 лет назад

Super porada!

@ExceliAdam 5 лет назад

Dzięki 😁

@lukaszu1708 4 года назад

Hej czy jako range można wstawić tablicę?? ( utworzoną z funkcji wybierz złożonej z kilku kolumn) dzięki

@grozdek3322 7 лет назад

Adam, ogromne dzięki za mega przydatną funkcję. Mam z nią tylko jeden problem: jeżeli w tabeli źródłowej przy jednej z wyszukiwanych wartości jest pusto, to funkcja i tak wstawia nową linię. Jak ja zmodyfikować żeby je pomijała? Z góry dziękuję.

@blotted111 8 лет назад

Adam Wielki Dzieki za ten film. Bardo mi to ułatwiło prace. A czy jest mozliwosc wrzucenia wartosci duplikowanych do osobnuch komorek a nie po przecinku?

@ExceliAdam 8 лет назад

+Rafał Szantula Zobacz film ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-X-LMTSdfvHY.html, ale ogólnie wystarczyłoby wynik rozdzielić za pomocą funkcjonalności Tekst jako kolumny

@MrSerek82 4 года назад

A czy istnieje możliwość udostępnienia podobnego materiału dla funkcji WYSZUKAJ.POZIOMO. Chodzi mi o to, żeby znaleźć wszystkie wartości z danego wiersza, które będą spełniać dany warunek. Zdaje się, że ma to być proste odwrócenie zapisów kodu, ale coś mi nie chce działać. Proszę o pomoc.

@ExceliAdam 4 года назад

Poniższe przekształcenie powinno zadziałać: Function WyszukajWszystkie(Szukana As String, Zakres As Range, NrWiersza As Integer) As String Dim i As Integer For i = 1 To Zakres.Columns.Count If Zakres.Cells(1, i) = Szukana Then WyszukajWszystkie = WyszukajWszystkie & Zakres.Cells(NrWiersza, i) & ", " End If Next i WyszukajWszystkie = Left(WyszukajWszystkie, Len(WyszukajWszystkie) - 2) End Function

@bishch1073 6 лет назад

Adam, jak przerobić makro aby zwracało mi wartosci do wierszy tj Swietokrzyskie(E2), >> Maria(E2), Beata(E3), Aneta(E4) ? Bede bardzo wdzieczny za pomoc.

@ExceliAdam 6 лет назад

Dwie procedury, Wywołaj wywołuje WyszukajWszystkie2, ale działa tylko dla 1 szukanej/komórki na raz. Sub WyszukajWszystkie2(Szukana As Range, Zakres As Range, _ NrKolumny As Integer, Wstaw As Range) Dim i As Integer, j As Integer For i = 1 To Zakres.Rows.Count If Zakres.Cells(i, 1) = Szukana Then Wstaw.Offset(j, 0) = Zakres.Cells(i, NrKolumny) j = j + 1 Szukana.Offset(j, 0).Range("A1:B1").Insert Shift:=xlDown Szukana.Offset(j, 0).Value = Szukana.Value End If Next i Szukana.Offset(j, 0).Range("A1:B1").Delete Shift:=xlUp End Sub Sub Wywołaj() Call WyszukajWszystkie2(Range("D10"), Range("A2:B18"), 2, Range("E10")) End Sub

@magorzatalange8456 7 лет назад

WitamPanie Adamie a jak zmodyfikować tę funkcję, aby wyszukała wszystkie zakresy liczb np. 1-20;22-25;27-40 (wszystko jedno czy pośredniku czy po przecinku). Jak szukać wartości to widzę że już ktoś pytał, a ja potrzebuję znaleźć określone zakresy tych wyszukanych wartości

@krzysbajer 7 лет назад

Witam, Dziękuję za instruktaż. Mam pytanie- jak zmienić kod tak by sumował znalezione wartości liczbowe ?

@ExceliAdam 7 лет назад

Function SumujWszystkie(Szukana As String, Zakres As Range, NrKolumny As Integer) As Double Dim i As Integer For i = 1 To Zakres.Rows.Count If Zakres.Cells(i, 1) = Szukana Then SumujWszystkie = SumujWszystkie + Zakres.Cells(i, NrKolumny) End If Next i End Function

@karolzinka2384 5 лет назад

Hej jest opcja na taką funkcję w Google spredsheet?

@ExceliAdam 5 лет назад

W Google spreadsheet, masz funkcję TEXTJOIN i musisz do niej dać wyniki spełniające kryteria coś w stylu: =ArrayFormula(textjoin(", ";true;if(A1:A4=A1;B1:B4;"")))

@Be3Ahcnek 5 лет назад

Panie Adamie czy da się tak ustawić funkcję żeby zakresem były całe kolumny? Tak żeby nie trzeba było blokować zakresu tylko wstawić np. zakres A:C

@ExceliAdam 5 лет назад

Funkcja powinna coś takiego przyjąć, ale trzeba dołożyć warunek, że jak trafimy na pustą komórkę, to powinnyśmy zakończyć makro: if Zakres.Cells(i, 1) = "" Then Exit Sub Najczęściej jednak lepiej zamieniać zakresy, do których się odwołujemy na tabele.

@rzepu 8 лет назад

Adam dziękuję za ten filmik, bardzo mi się przydał. Mam pytanie, jak zmodyfikować tę funkcję, aby wyszukała wszystkie znalezione wartości z wyjątkiem wartości zduplikowanych? Załóżmy, że w Twoim przykładzie w województwie Świętokrzyskim sprzedawca Aneta występuje więcej niż jeden raz, a ja chciałbym, aby funkcja zwracała wartość "Aneta" tylko raz.

@AdamKopec84 8 лет назад

+Rafał Perz Pewnie przydałaby się kolekcja i dodawanie poszczególnych elementów do niej tylko wtedy, kiedy dany element jeszcze na niej nie był. coś w stylu Dim kolekcja As New Collection ... ilość = 0 For Each element In kolekcja if element = znaleziona then ilość = ilość + 1 Next element if ilość = 0 then kolekcja.Add znaleziona end if

@odzik88 Год назад

@@AdamKopec84 a można prosić bardziej łopatologicznie? :)

@sp3c69 6 лет назад

Witam. Dodałem tę funkcję do VBA, ale wpisując ją w komórkę jej nie wyświetla. Jakie mogą być przyczyny?

@ExceliAdam 6 лет назад

Czy na pewno wstawiłeś w module, pliku, w którym chcesz z tej funkcji skorzystać?

@cornisto9366 6 лет назад

Co zrobić jeśli w kolumnie, w której wyszukujemy dane wartości(w tym przypadku województwo) jest więcej słów, np. 3? Przykładowo, komórka B2 zawiera tekst "raz dwa trzy", komórka B3 zawiera tekst "dwa trzy cztery", a chcemy dostać wartości z kolumny C, dla których wartość z kolumny B zawiera tekst "dwa". Z góry dziękuję za pomoc.

@ExceliAdam 6 лет назад

Znaleźć w VBA odpowiednich funkcji ZNAJDŹ - InStri użyć go w odpowiednim miejscu kodu sprawdzając czy zwraca liczbę.

@oskars.5606 7 лет назад

Witam. Mam problem z korzystaniem z napisanej funkcji na kilku komputerach. Zapisalem funkcje jako dodatek i zainstalowalem na wszystkich komputerach korzystajacych z pliku w ktorym zastosowana jest funkcja (plik umieszczony w "chmurze"). Za kazdym razem gdy otwieram plik, zamiast nazwy funkcji w komorce, pojawia sie nazwa poprzedzona sciezka do dodatku z komputera gdzie ostatnio plik byl zapisany: =ALS.FOUT('......./AppData/Roaming/Microsoft/AddIns/SAll.xlam'!SAll(C2;'cel 1'!$B$2:$AL$839;5);0) zamiast =ALS.FOUT(SAll(C2;'cel 1'!$B$2:$AL$839;5);0) Jest jakies rozwiazanie aby nie zmienial formuly a korzystal z funkcji zainstalowanej lokalnie na kazdym z komputerow? Z gory dziekuje za odpowiedz

@ExceliAdam 7 лет назад

Jedyny dodatek z funkcjami jaki instalowałem to spreadsheetpage.com/index.php/file/extended_date_functions_xdate/ i z nim pojawił mi się taki problem w Excelu 2016, ale jak sobie ustawiłem lokalizację pliku dodatku na zaufaną, to już mi się nie pokazywała cała ścieżka.

@wojciechstebnicki6834 7 лет назад

cześć mam pytanie czy jest jakiś ogranicznik do zakresu ponieważ jwzeli zakrea jest wiekszy od 33 tysiącach wierszy wyatepuje #arg

@wojciechstebnicki6834 7 лет назад

sry miało być jeżeli zakres jest większy od 33 tysięcy wierszy

@ExceliAdam 7 лет назад

Zmień Dim i As Integer na Dim i As Long i powinno być OK.

@wojciechstebnicki6834 7 лет назад

dzięki

@PK-vu7hs 5 лет назад

Witam mam problem. Czy jest możliwość wyświetlenia wyniku takiej formuły w formie tablicy?

@ExceliAdam 5 лет назад

Pisałem w komentarzach kod, który wypisuje wyszukane wartości po kolei w komórkach: Sub WyszukajWszystkie2(Szukana As Range, Zakres As Range, _ NrKolumny As Integer, Wstaw As Range) Dim i As Integer, j As Integer For i = 1 To Zakres.Rows.Count If Zakres.Cells(i, 1) = Szukana Then Wstaw.Offset(j, 0) = Zakres.Cells(i, NrKolumny) j = j + 1 Szukana.Offset(j, 0).Range("A1:B1").Insert Shift:=xlDown Szukana.Offset(j, 0).Value = Szukana.Value End If Next i Szukana.Offset(j, 0).Range("A1:B1").Delete Shift:=xlUp End Sub Sub Wywołaj() Call WyszukajWszystkie2(Range("D10"), Range("A2:B18"), 2, Range("E10")) End Sub

@PK-vu7hs 5 лет назад

@@ExceliAdam @@ExceliAdam dziękuję za szybką odpowiedź, ale chodziło mi o wyświetlenie tej formuły w formie formuły tablicowej czyli {1\2\3\4} bez konieczności przypisywania kolejnych wyników w kolejnych kolumnach i późniejszego tworzenia tablicy poprzez sumowanie tych wartości 😀

@zinkamangozinka8753 7 лет назад

Witam Adam jaka jest szansa by zmienić przecinek na znak Enter?

@zinkamangozinka8753 7 лет назад

po googlowałem i znalazłem:) Jak ktoś potrzebjuje to zamiast ", " proszę wpisać VbNewLine :) i działa

@ExceliAdam 7 лет назад

Dokładnie tak :D

@garage-MOlo 4 года назад

W office 2019 vbnewline nie dziala. Czym mozna to zamienic aby spacje zamienic na enter

@holistyczny 8 лет назад

Witam, Czy mógłbyś powiedzieć, co może być przyczyną tego, że ten kod daje w wyniku #ARG, wydaje mi się, że wszystko zrobiłem zgodnie z tym jak pokazałeś to na filmie: images.tinypic.pl/i/00788/feupqluezokr.jpg Z góry dziękuję za pomoc. Pozdrawiam.

@ExceliAdam 8 лет назад

+Jakub Konkel Bo podajesz funkcji 4 argumenty, a ona ma 3.

@holistyczny 8 лет назад

+Excel.i Adam No proste, wielkie dzięki! ;)

@maxikacper 7 лет назад

Właśnie mam chyba ten sam problem i utknąłem... :(

@ExceliAdam 7 лет назад

Potrzebuję więcej informacji żeby móc pomóc.

@maxikacper 7 лет назад

Chodzi o to że mam 3 kryteria według których wyszukiwana jest wartość i oczywiście funkcja wyszukaj pionowo wyszukuje mi pierwszą a ja potrzebuję znaleźć wszystkie wartości które spełniają te trzy kryteria...

@noam3d 5 лет назад

A ja poprosze o pomoc - jak będzie wyglądała funkcja która będzie SUMOWAŁA wartości z wybranej kolumny dla szukanego elementu (wszystkich wystąpień)?

@noam3d 5 лет назад

bo niestety ale zapis: WyszukajWszystkie = WyszukajWszystkie + Zakres.Cells(i, NrKolumny).Value nie sumuje mi wartości... :/

@ExceliAdam 5 лет назад

Uważam, że powinieneś użyć funkcji SUMA.JEŻELI ewentualnie SUMA.WARUNKÓW, bo produktów szukamy po kryterium, a do sumowania po kryterium służą te funkcje.

@Isabellanna 8 лет назад

Witam, czy mogę się dowiedzieć jak zmienić tą funkcję aby działała na liczbach? teraz pojawia mi się błąd #LICZBA! dziekuję Ania

@Isabellanna 8 лет назад

pokombinowałam i zmieniłam na: Function WyszukajWszystkie(Szukana As Variant, Zakres As Range, NrKolumny As Integer) As Variant Dim i As Integer For i = 1 To Zakres.Rows.Count If Zakres.Cells(i, 1) = Szukana Then WyszukajWszystkie = WyszukajWszystkie & Zakres.Cells(i, NrKolumny) & ", " End If Next i WyszukajWszystkie = Left(WyszukajWszystkie, Len(WyszukajWszystkie) - 2) End Function I działa :)

@ExceliAdam 7 лет назад

Gratuluję.

@reakcjonistapl562 7 лет назад

Witam, bardzo ciekawy film. Ale dokładnie przepisałem funkcję i pojawia mi się błąd #NAZWA Nie wiem co zrobić. Co prawda wartością szukaną jest u mnie liczba, natomiast formuła ma zwracać słowa, ale zmieniłem nawet String na Integer ale to nic nie pomogło cały czas ten sam błąd.

@ExceliAdam 7 лет назад

błąd #NAZWA to pojawia się najczęściej wtedy, kiedy Excel nie rozpoznaje nazwy funkcji. Prześlij plik na adam (at) exceliadam.pl to zerknę

@reakcjonistapl562 7 лет назад

Właśnie wysłałem oraz maila wysłałem do Pana też. Dziękuję za odpowiedź ;)

@kamilkurzynski4586 7 лет назад

Adam Potrzebuje jeszcze pomocy. Potrzebuje z wyniku formuły usunąć duplikujące się wartości :) Z góry dzięki

@ExceliAdam 7 лет назад

Tak umiem ;) Function WyszukajWszystkie(Szukana As String, Zakres As Range, NrKolumny As Integer) As String Dim i As Integer, pozycja As Integer For i = 1 To Zakres.Rows.Count If Zakres.Cells(i, 1) = Szukana Then On Error Resume Next pozycja = WorksheetFunction.Find(Zakres.Cells(i, NrKolumny), WyszukajWszystkie) If Err > 0 Then WyszukajWszystkie = WyszukajWszystkie & Zakres.Cells(i, NrKolumny) & ", " End If On Error GoTo 0 End If Next i WyszukajWszystkie = Left(WyszukajWszystkie, Len(WyszukajWszystkie) - 2) End Function

@kamilkurzynski4586 7 лет назад

Dziękuje bardzo. Działa :)

@dreykd6750 Год назад

@@ExceliAdam Panie Adamie wszystko super działa i było by idealnie gdyby zwracało wartości w wierszach. jak by wyglądał wtedy ten kod? Byłbym mega wdzięczny za pomoc. Pozdrawiam.

@ExceliAdam Год назад

@@dreykd6750 Jeśli masz Excela tablicowego to może być taki mało optymalny kod: Function WyszukajWszystkieArray(Szukana As String, Zakres As Range, _ NrKolumny As Integer) Dim i As Integer, WyszukajWszystkie As String For i = 1 To Zakres.Rows.Count If Zakres.Cells(i, 1) = Szukana Then WyszukajWszystkie = WyszukajWszystkie & Zakres.Cells(i, NrKolumny) & ", " End If Next i WyszukajWszystkie = Left(WyszukajWszystkie, Len(WyszukajWszystkie) - 2) WyszukajWszystkieArray = Split(WyszukajWszystkie, ", ") End Function

@maciejnejman5126 5 лет назад

Ukłony za dzielenie się wiedzą i tłumaczenie. Przykład jest skonstruowany tak, że wyszukiwane wartości nie mają duplikatów. Jeśli dla danego województwa pewne imiona by się powtarzały, to wówczas w komórce zostaną powtórzone imiona, które dla danego województwa występują więcej niż jeden raz. Fajnie byłoby takie duplikaty usunąć i pokazywać tylko unikalne imiona dla poszczególnych województw. Uwaga jest jeszcze taka, że można by wspomnieć dla niezaznajomionych z VBA, że kod taki trzeba umieszczać w module (w wersji anglojęzycznej: menu w edytorze VBA Insert ---> Module). Kiedy wstawiałem do "ThisWorkbook" nie chciało działać (przynajmniej w moim wypadku) i dopiero na necie znalazłem jakiś materiał o pisaniu funkcji w VBA gdzie było napisane żeby wstawiać w module. Dzięki raz jeszcze za materiał.