Formularze

Przykłady opisane w tej lekcji dostępne są w arkuszu Excela: Formularze.xls, tylko ich samodzielne przerobienie daje gwarancję zapamiętania tej lekcji.

Przykłady dla wszystkich lekcji szkolenia Excel 2003: ExcelSzkolenie.pl Cwiczenia Excel 2003.zip

Przykład 1.

(Arkusz: ‘Formularze 1’)

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 Formularzami. Aby pokazać Menu ‘Formularze’ klikamy prawym klawiszem myszy na pasku Menu i wybieramy ‘Formularze’.

Okno Formularze zawierające ikony które zostaną omówione w tej lekcji proponuję przenieść poniżej paska z ikonami standardowymi.

W tym przykładzie skorzystamy z ‘Pola wyboru’, klikamy tą ikonę jednokrotnie, po czym ustawiamy kursor myszy nad miejscem gdzie ma się znaleźć pole wyboru, klikamy lewy klawisz i przytrzymujemy go przesuwając mysz po ekranie, w ten sposób zaznaczamy obszar który ma zająć pole wyboru, za pierwszym razem efekt może być zaskakujący ale po kilku próbach nabywa się wprawy.

Wprowadzamy tekst ‘Wartości przeliczone na USD’ w miejsce tekstu ‘Pole wyboru …’.

Klikamy prawym klawiszem na obwódkę pola wyboru i wybieramy polecenie ‘Formatuj formant…’.

Na karcie ‘Formant’ w okienku ‘Łącze komórki’ wybieramy pole w którym ma być pokazywany efekt tego co zostało wybrane w naszym polu wyboru.

Jeżeli pole jest 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ść 2,8535 (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.

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 2’, przykład rozwiązany – arkusz: ‘Formularze 3’)

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).

Klikając prawym klawiszem na pasku Menu uzyskamy dostęp do personalizacji pasków narzędzi. Wybieramy pasek ‘Formularze’.

Z paska formularze wybieramy polecenie: ‘Pole kombi’.

Klikamy lewym przyciskiem myszy na arkuszu i trzymając przycisk wciśnięty przeciągamy myszką, aby określić wielkość pola kombi.

Klikamy pole prawym klawiszem myszy i wybieramy polecenie ‘Formatuj formant...’

Wyświetlone zostanie okno ‘Formatuj obiekt’. Wybieramy kartę ‘Format’ i wprowadzamy:

- ‘Zakres wejściowy’, – czyli to, co ma się wyświetlać w menu wyboru

- ‘Łą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.

- ‘Linie rzutu’ – ile pozycji menu ma się maksymalnie wyświetlać na raz, sugeruje pozostawić sugerowaną przez Excel wartość 8.

W polu kombi możemy już dokonać wyboru.

W komórce C4 pojawiła się cyfra 1, ponieważ wybraliśmy pierwszą z pozycji pola kombi.

Wprowadzamy teraz w komórce C5 funkcję WYSZUKAJ.PIONOWO.

W polu ‘Nr_indeksu_kolumny’ wprowadzamy link do komórki ‘$C$4+1’, ponieważ jeżeli została wybrana Polska to w komórce C4 pojawi się cyfra 1, dane dla Polski znajdują się w 2 z kolumn tabeli z danymi więc musimy dodać jeszcze 1.

Adresowanie bezwzględne ($) jest konieczne aby można było skopiować przygotowaną formułę do poniższych komórek.

Kopiujemy formułę z komórki C5 do wszystkich miesięcy.

Pozostało już tylko ukryć ‘techniczne’ wiersze (wiersz 4 i wszystkie z danymi), sformatować tabelę i raport może być rozesłany do użytkowników.

Właściwe dane będą się wyświetlały po zmianie wybranego kraju.

Rozwinięcie tego przykładu znajduje się w lekcji ‘Instrukcja Select Case’.

Korzyści z pokazanej powyżej funkcjonalności są widoczne dopiero przy znacznie bardziej skomplikowanych raportach.

Przykład 3.

(Arkusz: ‘Formularze 4’ rozwiązanie arkusz: ‘Formularze 5’)

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.

Kopiujemy istniejące pole kombi i zmieniamy jego łącze oraz zakres wejściowy zgodnie z poniższym rysunkiem.

v

Kasujemy formuły z pól C5:C16 i w komórkę C5 wprowadzamy formułę:

=WYSZUKAJ.PIONOWO(B5;$B$21:$H$32;$D$4+1+JEŻELI($D$3=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 D3 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$4=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 w lekcji ‘Instrukcja Selct Case’.

Przykład 4.

(Arkusz: ‘Formularze 6’ rozwiązanie arkusz: ‘Formularze 7’)

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 Data) 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’.

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’.

Przykład 5.

(Arkusz: ‘Formularze 8’)

W tym przykładzie 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 Menu Formaty wybieramy ‘Pole listy’.

Umieszczamy pole listy 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 komórce G11 wprowadzamy funkcję jeżeli, która jeśli miasto w danej linii jest takie samo jak miasto wybrane 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.

Ilość klientów oraz sprzedaż 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 mieście.

Sprzedaż na przedstawiciela i sprzedaż na klienta to zwykłe dzielenie.

Po ukryciu tabeli z danymi uzyskujemy ostateczny wygląd i zamierzoną funkcjonalność tabeli.

Przykład 6.

(Arkusz: ‘Formularze 9’)

W przykładzie tym nagrano proste makro przed przerobieniem tego przykładu może okazać się pomocne zapoznanie się z lekcjami poświęconymi VBA.

Na podstawie poniższej 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 Autofiltr.

Usuwamy całą kolumnę H, uzyskując autofiltr wyłącznie w kolumnie G, jest to jedyny sposób na uzyskanie autofiltra tylko dla jednej z kolumn tabeli.

Rejestrujemy nowe marko o nazwie ;pokretlo’.

Klikamy ‘OK’ w poniższym oknie.

W autofiltrze wybieramy opcję ‘Niestandardowe…’ i w oknie ‘Autofiltr niestandardowy’ warunek ‘jest mniejsze niż’ 0.

Po kliknięciu ‘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.

Nagranie i uruchamiania makra jest konieczne ponieważ Autofiltr nie updatuje się automatycznie po zmianie danych na liście.

Kod nagranego makra (po usunięciu 5 linii opisów i pustych linii) powinien wyglądać tak jak poniżej.

Sub pokretlo()

Selection.AutoFilter Field:=1, Criteria1:=”<0”, Operator:=xlAnd

End Sub

Modyfikowanie kodu VBA czy nawet jego przeglądanie jest konieczne do skutecznego zakończenia tego ćwiczenia.

To ilu przedstawicieli spełnia warunek obliczymy za pomocą funkcji SUMY.POŚREDNIE. Dla numeru funkcji 103 będzie ona zliczać ilość danych w nieukrytych wierszach.

(Dokładny opis funkcji SUMY.POŚREDNIE znajduje się w lekcji Funkcje).

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.

Ćwiczenie możemy na tym etapie uznać za zakończone.

Przykład 7.

(Arkusz: ‘Formularze 5’)

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 zaznaczony na poniższym obrazku czerwoną obwódką.

Szkolenie Excel 2003 Szkolenie Excel 2007