Jak znaleźć wszystkich naszych przedstawicieli handlowych - ale z konkretnego miasta czy regionu?

Z lekcji dowiesz się:

• Jak w grupie danych znaleźć wartości spełniające podany warunek?

• Jak stworzyć pętlę wykorzystującą zmienną "i" (Dim i as Long)?

• Co to jest ogranicznik? Jak go stworzyć?

Naszym zadaniem jest znalezienie wszystkich przedstawicieli handlowych z danego miasta, a następnie wpisanie wszystkich wyników w jedną komórkę wiesza. Dane wyjściowe znajdują się w pliku „Pobierz szablon do dalszej pracy”. Zestawienie handlowców zostało przedstawione na rysunku 1.

(Rys. 1)

W standardowej funkcji WYSZUKAJ.PIONOWO mamy do czynienia z następującym problemem – wyszuka nam ona tylko jednego przedstawiciela, pierwszego na liście, który spełni dany warunek. Zapis funkcji wyglądałby następująco:

=WYSZUKAJ.PIONOWO(D2;$A$2:$B$23;2;0)

Pierwszy argument funkcji to szukana_wartość, czyli miasto dla którego chcemy wyszukać przedstawiciela (komórka D2).

Drugi argument to tabela_tablica, czyli zakres, w którym będziemy przeszukiwać pierwszą kolumnę ($A$2:$B$23, zakres blokujemy za pomocą klawisza F4 bezwzględnie ponieważ jest stały).

Trzeci argument to nr_indeksu_kolumny, czyli kolejno licząc od lewej nr kolumny (wartość 2 na rysunku 2).

(Rys. 2)

Czwarty argument funkcji to przeszukiwany_zakres, czyli rodzaj dopasowania: dokładne bądź przybliżone (wybieramy dopasowanie dokładne, czyli wartość 0 odpowiadająca wartości logicznej FAŁSZ). Po skopiowaniu formuły na wiersze poniżej otrzymamy wyniki przedstawione na rysunku 3, czyli pierwsze wartości, które spełniły warunki.

(Rys. 3)

Wynik ten nie jest satysfakcjonujący, ponieważ jest po kilku przedstawicieli w każdym mieście. Zależy nam, aby wyniki zawierały wszystkich przedstawicieli z danego miasta.

Od Excela 2019 można używać relatywnie prostej formuły, która wykorzystując funkcję POŁĄCZ.TEKSTY zwraca właściwą listę. W pierwszej części zadania należy przetestować warunek, jaki ma spełnić wyżej wymieniona funkcja. Użyjemy w tym celu funkcji JEŻELI. Zapis warunku będzie wyglądał następująco:

=JEŻELI($A$2:$A$23=D2;$B$2:$B$23;"")

Pierwszym argumentem funkcji JEŻELI jest test_logiczny, czyli zakres wszystkich miast – cała kolumna A bez nagłówka, zablokowana bezwzględnie przy użyciu klawisza F4. W teście tym trzeba sprawdzić czy zaznaczony zakres jest równy aktualnie rozpatrywanemu miastu (komórce D2).

Drugi argument to wartość_jeżeli_prawda, czyli jeśli warunek z testu logicznego jest spełniony, to chcemy aby funkcja zwróciła nam przedstawiciela dla tego miasta (zakres z kolumny B bez nagłówka). Zakres ten blokujemy bezwzględnie za pomocą klawisza F4.

Trzeci argument funkcji to wartość_jeżeli_fałsz, czyli jeśli warunek nie zostanie spełniony, to funkcja zwrócił nam pusty ciąg tekstowy (w Excelu oznaczany "").

W trybie edycji komórki możemy podejrzeć sobie wynik funkcji JEŻELI za pomocą klawisza F9 (rys. 4).

(Rys. 4)

Otrzymujemy tablicę pustych ciągów tekstowych i wartości (imiona przedstawicieli) odpowiadających danemu miastu, czyli wszystkich przedstawicieli z miasta Warszawa.

W kolejnym kroku należy połączyć tablicę otrzymaną z funkcji JEŻELI z funkcją POŁĄCZ.TEKSTY. Cofamy podejrzenie wyniku za pomocą skrótu klawiszowego Ctrl+Z. Zapis funkcji POŁĄCZ.TEKSTY będzie wyglądał następująco:

=POŁĄCZ.TEKSTY(", ";1;JEŻELI($A$2:$A$23=D2;$B$2:$B$23;"")

Pierwszym argumentem funkcji jest ogranicznik, czyli znak którym rozdzielimy wyniki. Najczęściej używanym znakiem jest przecinek i spacja ", ".

Drugi argument funkcji to ignoruj_puste. Na tym etapie należy sobie odpowiedzieć na pytanie, czy chcemy zignorować puste wartości, czy je uwzględnić. W tym zadaniu puste wartości chcemy pominąć, czyli wybieramy wartość logiczną PRAWDA lub dla krótszego zapisu odpowiadającą jej wartość 1 (rys. 5).

(Rys. 5)

Trzecim argumentem funkcji jest tekst1, czyli tablica z wynikami z funkcji JEŻELI.

Funkcja POŁĄCZ.TEKSTY połączy tylko te wartości, które są różne od pustego ciągu tekstowego, czyli po zatwierdzeniu formuły skrótem klawiszowym Ctrl+Enter i skopiowaniu jej na wiersze poniżej, otrzymamy wyniki przedstawione na rysunku 6.

(Rys. 6)

Problem pojawia się kiedy nie mamy dostępu do funkcji POŁĄCZ.TEKSTY, a to z powodu starszej wersji arkusza. W takiej sytuacji należy samodzielnie napisać w VBA funkcję, która będzie w stanie przeprowadzić takie połączenie wyników. Zadanie to wykonamy na arkuszu WyszukajVBA w pliku do pobrania na takich samych danych jak na rysunku nr 1.

Przy użyciu skrótu klawiszowego Alt+F11, przechodzimy do edytora VBA. Po lewej stronie ekranu klikamy prawym przyciskiem myszy na nazwę arkusza i z podręcznego menu rozwijamy polecenie Insert, a następnie wybieramy polecenie Module (rys. 7).

(Rys. 7)

Za pomocą tego polecenia dodajemy nowy moduł programowania, w którym napiszemy funkcję. Funkcję tę nazwiemy WyszukajWszystkie, jej argumenty w VBA będą wyglądały następująco (rys. 8):

Function WyszukajWszystkie (Szukana as String, Zakres as Range, _

NrKolumny as Integer, Optional Ogranicznik as String) as String

Musimy określić argumenty tej funkcji, aby mogła wyszukać odpowiednich przedstawicieli. Przede wszystkim należy wyznaczyć szukaną wartość, więc wpiszemy pierwszy argument funkcji - Szukana as String (string czyli tekst).

Następnie trzeba wyznaczyć zakres (drugi argument funkcji), w którym funkcja ma szukać podanej wartości, czyli wpisujemy Zakres as Range. W VBA kończy się miejsce w linijce więc aby kontynuować zapis w linijce poniżej trzeba użyć znaków spacji i podkreślenia ( _).

Tak jak dla funkcji WYSZUKAJ.PIONOWO, w VBA potrzebujemy jeszcze trzeciego argumentu funkcji, czyli numeru kolumny, co można zapisać następująco NrKolumny as Integer. Należy tu wstawić numer kolumny, z której będzie wyciągana wartość po znalezieniu właściwego miasta.

Opcjonalnie można dołożyć czwarty argument funkcji, który będzie wyglądał następująco Optional Ogranicznik as String - czyli znak jakim zamierzamy połączyć poszczególne wyniki przedstawicieli dla danego miasta. Jednak użyjemy go dopiero po przetestowaniu działania tej funkcji.

(Rys. 8)

Argumenty funkcji są więc zadeklarowane, ale na razie funkcja nie działa. W pierwszej kolejności trzeba sprawdzić całą kolumnę A, czy jest tam szukana nazwa miasta. W tym celu stworzymy pętlę. Wykorzystujemy do tego zmienną "i" (Dim i as Long) - do stworzenia pętli For i=0 to i=Zakres.Rows.Count, co oznacza, że należy policzyć jak wysoki jest zakres. Następnie zamykamy pętlę i wpisujemy Next i.

Pętla ta dla każdego miasta z kolumny A sprawdza każda komórkę z zakresu z kolumny B. Teraz należy do funkcji napisać kolejny warunek, który będzie wyglądał następująco:

If Zakres.Cells(i,1) = Szukana Then

WyszukajWszystkie = WyszukajWszystkie & Zakres.Cells (i, NrKolumny) _

& ", "

End If

W funkcji Cells podajemy jako pierwszy argument numer wiersza, czyli „i”, następnie numer kolumny, czyli wartość 1.

Jeśli komórka z zakresu będzie równa szukanej wartości to powiększy ona tekst zwracanej wartości. Teraz trzeba określić parametry funkcji WyszukajWszystkie (argumenty funkcji przedstawione na rysunku 9).

(Rys. 9)

Funkcja będzie miała również na sam koniec znak ogranicznika, więc musimy się go pozbyć. Zapis będzie wyglądał następująco:

WyszukajWszystkie = Left ( WyszukajWszystkie, Len (WyszukajWszystkie), -2)

W funkcji Left będziemy wyciągać wartości od lewej strony do końca tekstu pomniejszone o dwa znaki.

Cały zapis kodu do sprawdzenia działania funkcji przedstawiony został na rysunku 10.

(Rys. 10)

Wracamy do arkusza w Excelu i w komórce E2 wpisujemy nazwę Wyszukaj i Excel sam podpowie nazwę funkcji z VBA, czyli WyszukajWszystkie (rys. 11).

(Rys. 11)

Zapis funkcji będzie wyglądał następująco:

=WyszukajWszystkie(D2;$A$2:$D$23;2)

Niestety Excel nie podpowiada jej składni – argumentów, czyli tworząc funkcję w VBA trzeba zapamiętać jak zapisywaliśmy wcześniej poszczególne argumenty. Pierwszym argumentem była szukana wartość, następnie zakres, po którym szukamy i numer kolumny, z której chcemy wyciągnąć wartość. Ewentualnie podajemy ogranicznik (ale na razie nie możemy go zmienić).

Tak wpisaną formułę zatwierdzamy i kopiujemy na wiersze poniżej. Otrzymamy wyniki przedstawione na rysunku 12.

(Rys. 12)

Aby dołożyć możliwość zmiany ogranicznika trzeba wrócić do edytora VBA za pomocą skrótu klawiszowego Alt+F11. Ogranicznik był opcjonalnym argumentem, więc nie musieliśmy go podawać. Teraz chcemy sprawdzić działanie ogranicznika i w zależności od tego czy jest uzupełniony w funkcji czy też nie, ustawimy go jako standardowy lub pobierzemy z argumentu, który uzupełnił użytkownik. Domyślnie jeśli nie zostanie podany ogranicznik to będzie on równy pustemu ciągowi znaków. Zapis formuły będzie wyglądał następująco:

If Ogranicznik = "" Then Ogranicznik = ", "

Jeśli ogranicznik będzie równy pustemu ciągowi znaków to - chcemy aby ogranicznik był równy znakom przecinek i spacja (czyli wtedy kiedy użytkownik nie poda tego ogranicznika).

W kolejnym etapie w miejscu ogranicznika wpisanego na stałe w funkcji WyszukajWszystkie wpisujemy nazwę zmiennej Ogranicznik (zaznaczone strzałką na rysunku 13).

(Rys. 13)

Po takich zmianach powstał kolejny problem. Nie zawsze użytkownik poda ogranicznik, który będzie miał dwa znaki, więc zmienimy następujący zapis:

WyszukajWszystkie = Left ( WyszukajWszystkie, Len (WyszukajWszystkie), -2)

Trzeba teraz policzyć długość ogranicznika wpisanego przez użytkownika za pomocą funkcji Len. Zapis funkcji będzie wyglądał następująco:

WyszukajWszystkie = Left ( WyszukajWszystkie, Len(WyszukajWszystkie), Len(Ogranicznik))

Uaktualniony zapis całego kodu VBA został przedstawiony na rysunku 14.

(Rys. 14)

W celu sprawdzenia działania nowego kodu VBA wprowadzamy nowy ogranicznik jako czwarty argument funkcji WyszukajWszystkie (średnik, spacja i średnik). Zapis funkcji w Excelu będzie wyglądał następująco:

=WyszukajWszystkie(D2;$A$2:$D$23;2;"; ;")

Po zatwierdzeniu formuły klawiszami Ctrl+Enter i skopiowaniu na wiersze poniżej otrzymamy wyniki funkcji przedstawione na rysunku 15.

(Rys. 15)

Funkcja napisana w VBA działa prawidłowo, tworzy listę przedstawicieli dla danego miasta.

Polecamy także:

• Obliczanie największych wartości. Najlepsze triki
• Formatowanie warunkowe – jak wykorzystać je w praktyce?