Formularze
Przykłady opisane w tej lekcji dostępne są w arkuszu Excela: Formularze.xlsm tylko ich samodzielne przerobienie daje gwarancję zapamiętania tej lekcji. Plik zawiera makra ich użycie jest niezbędne w 3 przykładzie tej lekcji.
Przykłady dla wszystkich lekcji szkolenia Excel 2013: ExcelSzkolenie.pl Cwiczenia Excel 2013.zip
Ta lekcja może być obejrzana lub przeczytana poniżej.
Film wygląda najlepiej jeśli będzie odtwarzany w rozdzielczości 720p HD,
rozdzielczość można zmienić dopiero po uruchomieniu filmu klikając na ikonie
trybika 
która
pojawi się w prawym dolnym rogu poniższego ekranu. Po kilku sekundach od zmiany
obraz wyostrzy się.
Polecenia umożliwiające wstawianie formantów formularza znajdują się na karcie ‘DEWELOPER’, aby ją pokazać należy kliknąć menu PLIK a następnie wybrać ‘Opcje’.
Na karcie ‘Dostosowywanie Wstążki’ zaznaczamy opcję ‘Deweloper’.
Karta ‘DEWELOPER’ zostaje wyświetlona końcu Wstążki, będzie widoczna przy kolejnych uruchomieniach Excela.
Poleceniem które interesuje nas w tej lekcji jest ‘Wstaw’.
Po kliknięciu polecenia Wstaw, wyświetlone zostanie okno zawierające 2 grupy formantów.
Formanty formularza są łatwiejsze w użyciu, natomiast formanty ActiveX dają więcej możliwości i są używane przez osoby programujące w VBA.
Obie grupy zawierają formanty wyglądające tak samo np. ‘Przycisk opcji’, dlatego też należy zwrócić uwagę na to czy na pewno używamy formantów z pierwszej grupy – Formantów formularza.
Przykład 1.
(dane – arkusz: ‘Formularze 1’)
(przykłada rozwiązany – arkusz: ‘Formularze 2’)
Chcielibyśmy aby dla poniższej listy przedstawicieli handlowych łatwo można było zmieniać walutę sprzedaży z PLN na USD, bez konieczności powiększania tabeli.
Posłużymy się w tym celu ‘Polem wyboru’.
Klikamy ‘Wstaw’ i ‘Pole wyboru’
Kursor myszy zamienia się w mały krzyżyk.
Ustawiamy kursor w miejscu gdzie ma znaleźć się pole wyboru.
Klikamy lewy klawisz myszy i przytrzymując go przeciągamy myszką w prawo i w dół.
Po puszczeniu lewego klawisza myszy Pole wyboru’ zostanie utworzone.
Wprowadzamy tekst ‘Wartości przeliczone na USD’ w miejsce tekstu ‘Pole wyboru …’.
Jeśli okaże się to konieczne zmieniamy wielkość lub położenie pola klikając i przeciągając za jego obwódkę lub jeden z oznaczonych punktów na jej brzegach.
Klikamy prawym klawiszem w dowolnym miejscu pola wyboru i wybieramy polecenie ‘Formatuj formant…’.
Na karcie ‘Formant’ w okienku ‘Łącze komórki’ wybieramy komórkę w której ma być pokazywany efekt tego co zostało wybrane w naszym polu wyboru.
Klikamy OK.
Od momentu pierwszego użycia pola, jeżeli jest ono niezaznaczone w komórce E2 znajduje się ‘FAŁSZ’.
Gdy jest zaznaczone ‘PRAWDA’.
W komórce F2 wprowadzamy funkcję JEŻELI która w zależności od tego czy w komórce E2 jest PRAWDA czy FAŁSZ przyjmie wartość 3,15 (kurs USD) lub 1.
W komórce G4 ponownie korzystając z funkcji JEŻELI dodajemy nagłówek kolumny który w zależności od komórki E2 przyjmuje wartości: ”Sprzedaż USD” lub Sprzedaż PLN”.
W komórce G5 wprowadzamy formułę dzielącą Sprzedaż z kolumny F przez komórkę F2. Formułę tą kopiujemy do poniższych komórek.
Używając ‘malarza formatów’ formatujemy kolumnę G za wzór biorąc kolumnę F.
Po ukryciu kolumny F i zamianie koluru czcionki w komórce E2 na biały tabela wygląda tak jak na poniższym rysunku.
Po zmianie ustawienia pola wyboru w kolumnie G pokazuje się odpowiedni nagłówek a pod nim wartości w USD lub PLN zgodnie z wybraną opcją.
Przykład 2.
(Arkusz: ‘Formularze 3’, przykład rozwiązany – arkusz: ‘Formularze 4’)
Pole kombi jest
najczęściej używanym Formularzem w Excelu.
Dzięki wykorzystaniu pola kombi możemy dać użytkownikom naszych raportów możliwość filtrowania danych przekraczające możliwości oferowane przez filtry czy tabele przestawne.
Pola takie są także znacznie łatwiejsze w użytkowaniu, co jest szczególnie ważne jeśli przygotowujemy raporty dla dużej grupy (należy pamiętać, że wg anonimowej ankiety przeprowadzonej w 2009 roku określenie ‘tabela przestawna’ wzbudza strach u 91% użytkowników Excela).
Naszym celem w tym przykładzie będzie wstawienie pola kombi do komórki C3, dzięki temu polu chcielibyśmy wybierać kraj, dla którego dane będą wyświetlane w poniższych komórkach. Dane dla wszystkich krajów znajdują się w tabeli poniżej.
Wstawiamy pole kombi klikając wskazaną poniżej ikonę i przeciągając myszką z wciśniętym lewym klawiszem w miejscu gdzie ma znaleźć się pole kombi, aby określić wielkość pola.
Po wstawieniu pola możemy dopasować jego wielkość chwytając i przeciągając za dowolny z kwadratów znajdujących się na jego krawędziach.
Klikamy pole prawym klawiszem myszy i wybieramy polecenie ‘Formatuj formant...’
Wyświetlone zostanie okno ‘Formatowanie obiektu’. Wybieramy kartę ‘Format’ i wprowadzamy:
- ‘Zakres wejściowy’, – czyli to, co ma się wyświetlać w menu wyboru, w tym przypadku nazwy krajów w komórkach G19:G21, wyświetlane elementy muszą być ustawione w pionie.
- ‘Łącze komórki’ – komórka w arkuszu, w której ma się wyświetlić informacja, która z opcji została wybrana przez użytkownika, wybieramy D3, ale mogłaby to być także inna komórka.
- ‘Linie rzutu’ – ile pozycji menu ma się maksymalnie wyświetlać na raz, sugeruję pozostawić wartość 8.
Klikamy OK.
Klikamy dowolną komórkę w arkuszu, po czym w polu kombi możemy już dokonać wyboru.
W komórce D3 pojawiła się cyfra 1, ponieważ wybraliśmy pierwszą z pozycji pola kombi.
Wprowadzamy teraz w komórce C4 funkcję WYSZUKAJ.PIONOWO.
W polu ‘Nr_indeksu_kolumny’ wprowadzamy link do komórki
‘$D$3+
Adresowanie absolutne ($) jest konieczne aby można było skopiować przygotowaną formułę do poniższych komórek.
Kopiujemy formułę z komórki C4 do wszystkich miesięcy.
Pozostało już tylko ukryć ‘techniczne’ wiersze i komórki, czyli wiersze z tabelą danych, listą krajów i łączem pola kombi,.
Właściwe dane będą się wyświetlały po zmianie wybranego kraju, ale zawsze warto przetestować poprawność działania utworzonych raportów, przed rozesłaniem do użytkowników.
Korzyści z pokazanej powyżej funkcjonalności są widoczne dopiero przy znacznie bardziej skomplikowanych raportach
Przykład 3.
(Arkusz: ‘Formularze 5’ przykład rozwiązany – arkusz: ‘Formularze 6’)
Tabelę danych z poprzedniego przykładu rozszerzono o dodatkowe 3 kolumny z danymi dla tych samych krajów, ale nie w dolarach lecz w walutach lokalnych.
Dodamy więc drugie pole kombi umożliwiające wybór rodzaju waluty.
Można dodać kolejne pole kombi lub skopiować to już istniejące (wybieramy Ctrl+C, Ctrl+V) i zmienić jego łącze oraz zakres wejściowy zgodnie z poniższym rysunkiem.
W komórkę C5 wprowadzamy formułę:
=WYSZUKAJ.PIONOWO(B4;$B$20:$H$31;$D$3+1+JEŻELI($D$2=1;3;0);0)
Jedyną różnicą w konstrukcji pomiędzy tą formułą a użytą w poprzednim przykładzie jest dodanie funkcji ‘JEŻELI’, która jeśli wybrano waluty lokalne (w komórce D2 będzie wtedy 1), dodaje 3 do numeru kolumny, z której mają być pobierane dane.
Czyli jeżeli wybrane są dolary i Ukraina (jak na poniższym rysunku), dane będą czerpane z 4 kolumny tabeli z danymi ponieważ $D$3=3, a funkcja ‘JEŻELI’ przyjmie dla USD wartość 0. 3+1+0=4
Oczywiście rozszerzono także zakres funkcji wyszukaj pionowo tak aby dane w walutach lokalnych także były objęte jej obszarem.
Jeżeli zmienimy ustawienie pola kombi na zł/rur/hr (waluty lokalne) wynik funkcji ‘JEŻELI’ przyjmie wartość 3 i dane będą pobierane z kolumny będącej o 3 kolumny dalej.
Po skopiowaniu formuły do poniższych komórek i ukryciu ‘technicznych’ komórek, przykład jest zakończony.
Przykład ten jest rozwinięty o kolejne pole kombi, umożliwiające wybór kwartału, w lekcji ‘Instrukcja Select Case’.
Przykład 4.
(Arkusz: ‘Formularze 7’ rozwiązanie arkusz: ‘Formularze 8’)
W tym przykładzie zapoznamy się z metodą sumowania danych ‘od do’, gdzie dolna i górna granica przedziału wybierana jest przez użytkownika za pomocą pól kombi.
Funkcjonalność taka ułatwia przeglądanie danych po kwartałach, w okresach półrocznych oraz wg tzw YTD (ang. Year to Date) czyli od początku roku do teraz.
W poniższym arkuszu chcielibyśmy aby po wybraniu pierwszego i ostatniego miesiąca z pól kombi, które umieścimy w komórkach H2 i H3, suma sprzedaży dla wybranego okresu była wyliczana w polu H6.
Pole kombi wstawiamy zgodnie z zasadami opisanymi w przykładzie 2 tej lekcji. Jako ‘Zakres wejściowy’ wprowadzamy nazwy miesięcy z komórek B4:B15, jako ‘Łącze komórki’ podajemy ‘I2’. Ilość linii rzutu możemy zmienić na 12, aby były widoczne wszystkie miesiące bez konieczności ich przewijania.
Po przygotowaniu jednego pola kombi, zamiast przygotowywać drugie tak samo szybciej będzie skopiować to pierwsze.
Po skopiowaniu i wklejeniu drugiego pola uzyskamy 2 pola kombi, mające ten sam zakres wejściowy i to samo ‘Łącze komórki’.
Ponieważ łącze komórki jest takie samo po zmianie wybranego miesiąca w jednym z pól, drugie pole zmieni się dokładnie tak samo, właściwość ta może być użyteczna gdy w kilku arkuszach prezentowane są te same dane i w każdym arkuszu chcielibyśmy mieć możliwość zmiany sposobu ich filtrowania, które miałoby wpływ na wszystkie arkusze.
Nasz przykład jest zdecydowanie prostszy, aby uniezależnić drugie pole kombi zmienimy jego łącze na komórkę ‘I3’.
W kolumnie D umieściłem numery odpowiadające miesiącom.
W komórce E4 wprowadzamy formułę Jeżeli która w zależności od dwóch warunków połączonych funkcją ORAZ, wstawi w komórkę wartość sprzedaży danego miesiąca lub zero.
Warunki te są następujące numer miesiąca (D4) musi być większy lub równy początkowi okresu z komórki ‘I2’ oraz mniejszy lub równy końcowi tego okresu z komórki ‘I3’.
Po przeciągnięciu tej formuły do pozostałych komórek kolumny E pozostanie nam już tylko wstawienie sumy do komórki H6.
Możemy jeszcze ukryć kolumny ‘techniczne’.
Istnieją także inne sposoby rozwiązania tego problemu.
Przykład 5.
(dane – arkusz: ‘Formularze 9’)
(rozwiązanie – arkusz: ‘Formularze 10’)
W tym przykładzie skorzystamy z tej samej tabeli z danymi, na jej podstawie chcielibyśmy móc łatwo porównywać wyniki sprzedaży wybranego oddziału ze sprzedażą dla całej Polski.
Tabela jest już gotowa, wystarczy tylko dodać możliwość wyboru oddziału i formuły obliczające interesujące nas dane.
Pierwszym krokiem będzie dodanie listy oddziałów, oraz przypisanie im numerów. To gdzie znajduje się ta lista nie jest tak istotne.
Z karty ‘DEWELOPER’ wybieramy poleceni ‘Wstaw’ a później z grupy Formanty formularza ‘Pole listy’.
W sposób dokładnie taki sam jak w poprzednich przykładach umieszczamy je na arkuszu.
Umieszczamy je na lewo od tabeli z danymi. W tym momencie pole jest jeszcze puste.
Aby wypełnić je listą oddziałów, klikamy je prawym klawiszem i wybieramy polecenie ‘Formatuj formant…’.
Na karcie ‘Formant’ w okienku ‘Zakres wejściowy’ podajemy adres listy oddziałów, a w okienku ‘Łącze komórki’ adres D2.
Po wybraniu jednego z miast w komórce D2 wyświetlana jest teraz liczba mówiąca które z miast naszej listy wybrano.
W komórce F2 w której powinna się pokazywać nazwa wybranego przez nas miasta wprowadzamy funkcję WYSZUKAJ.PIONOWO, która na podstawie numeru wybranego elementu wyszuka go na liście.
Efektem działania funkcji będzie wyświetlanie nazwy miasta w komórce F2 po jego wybraniu.
W kolumnach G i H przygotujemy teraz funkcję które pozwolą nam wybierać odpowiednie dane do naszej tabeli.
Kolumnom tym nadajemy nagłówki zgodnie z poniższym rysunkiem.
W komórce G11 wprowadzamy funkcję jeżeli, która jeśli miasto w danej linii jest takie samo jak miasto wybrane (komórka F2) pokaże ilość klientów, a jeśli miasto jest inne funkcja przyjmie wartość zero.
Podobną funkcję tworzymy w komórce H11, tym razem dla sprzedaży.
Obie funkcje kopiujemy do poniższych wierszy.
Uzupełniamy tabelę odpowiednimi wzorami.
Ilość klientów oraz sprzedaż dla wybranego oddziału to sumy danych z kolumn G i H.
Aby obliczyć ilość przedstawicieli handlowych posłużymy się funkcją LICZ.JEŻELI, która zliczy ile komórek jest większych od 0 w kolumnie G, tym samym obliczy ilu jest przedstawicieli handlowych w danym oddziale.
Sprzedaż na przedstawiciela i sprzedaż na klienta to zwykłe dzielenie.
Po ukryciu tabeli z danymi uzyskujemy ostateczny wygląd i zamierzoną funkcjonalność tabeli.
Innym możliwym rozwiązaniem tego problemu jest posłużenie się funkcją SUMA.JEŻELI zamiast dodatkowych kolumn technicznych.
Przykład 6.
(dane – arkusz: ‘Formularze 11’)
(rozwiązanie – arkusz: ‘Formularze 12’)
(alternatywne rozwiązanie – arkusz ‘Formularze 13’)
W przykładzie tym nagrano proste makro przed jego przerobieniem sugeruje zapoznać się z lekcjami poświęconymi VBA.
Na podstawie tej samej tabeli z danymi chcielibyśmy utworzyć listę przedstawicieli, którzy obsługują największą ilość klientów, przy czym warunek graniczny będący ilością klientów będzie zmienny.
W tym celu posłużymy się pokrętłem.
Dodajemy je do arkusza i formatujemy.
Na karcie ‘Formant’ okna ‘Formatowanie formantu’ wprowadzamy wartość minimalną, maksymalną i Łącze komórki - $D$3.
Od tego momentu wartość komórki D3 będzie się zmieniać po kliknięciu na jedną ze strzałek pokrętła.
W kolumnie G dodajemy nagłówek „klienci-warunek” a pod nim formułę, która od ilości klientów wymaganej w warunku będzie odejmować ilość klientów obsługiwanych przez danego przedstawiciela.
Formułę tą kopiujemy do całej kolumny.
Zaznaczamy komórki G9 i H9 po czym włączamy Filtr.
Rejestrujemy nowe marko o nazwie ;pokretlo’, zapisujemy w tym skoroszycie.
Klikamy ‘OK’ w poniższym oknie.
W filtrze wybieramy opcję ‘Filtry liczb’ i ‘Mniejsze niż…’.
Po czym wprowadzamy 0 i klikamy OK. .
Zatrzymujemy nagrywanie makra.
Klikamy pokrętło prawym klawiszem i wybieramy polecenie ‘Przypisz makro…’
Przypisujemy makro ‘pokretlo’ i klikamy ‘OK.’.
Od momentu przypisania makra, będzie się ono uruchamiać przy każdym kliknięciu pokrętła.
Jeżeli przytrzymamy wciśnięty lewy klawisz myszy na jednej ze strzałek pokrętła, liczby będą zmieniały się szybciej, ale makro zostanie uruchomione dopiero gdy go puścimy.
Nagranie i uruchamiania makra jest konieczne ponieważ Filtr nie ‘odświeża’ się automatycznie po zmianie danych na liście.
Proponuje sprawdzić jak działa makro poprzez zmienianie warunku ilości klientów, wraz z jego wzrostem linie z kolejnymi przedstawicielami będą ukrywane.
Kod nagranego makra (po usunięciu opisów i pustych linii) powinien wyglądać tak jak poniżej.
Sub pokretlo()
ActiveSheet.Range("$G$9:$G$39").AutoFilter
Field:=1, Criteria1:="<0", Operator:=xlAnd
End Sub
Modyfikowanie kodu VBA czy nawet jego przeglądanie nie jest konieczne do skutecznego zakończenia tego ćwiczenia.
To ilu przedstawicieli spełnia warunek obliczymy za pomocą funkcji SUMY.CZĘŚCIOWE. Dla numeru funkcji 103 będzie ona zliczać ilość danych w nieukrytych wierszach.
(Dokładny opis funkcji SUMY.CZĘŚCIOWE znajduje się w lekcji ‘Funkcje dla Zaawansowanych’).
Procent przedstawicieli którzy spełniają warunek ilości klientów to formuła dzieląca daną z komórki D5 przez całkowitą ilość przedstawicieli.
Po ukryciu kolumny G arkusz powinien wyglądać tak jak na poniższym rysunku.
Ćwiczenie możemy na tym etapie uznać za zakończone.
Bardzo podobny efekt daje zastosowanie ‘Paska przewijania’.
Praktycznie jedyną różnicą pomiędzy ‘Paskiem przewijania’ a ‘Pokrętłem’ jest możliwość dokonywania skoków o założoną liczbę nazywaną ‘Zmianą strony’.
W poniższym przykładzie ‘Zmiana strony’ została ustalona w wysokości 10 i będzie zachodzić po kliknięciu na ‘Pasku przewijania’ poza obszarem strzałek i suwaka.
Na ‘Pasku przewijania’ można też przejść do założonej
wartości ciągnąc za suwak.
Alternatywne rozwiązanie tego
przykładu będzie więc wyglądać tak jak poniżej.
Dla wielu osób może zaskoczeniem to, że aby zmniejszyć liczbę należy nacisnąć strzałkę w górę.
Problem ten łatwo obejść wybierając jako łącze komórki formantu D2 a w D3 wpisując =130-D2.
Zawartość komórki D2 została ukryta poprzez wybranie białej czcionki.
Pozostałe Formanty formularza są rzadziej używane, a po zapoznaniu się powyżej opisanymi łatwo będzie samemu zrozumieć ich działanie.
.