Porządkowanie danych za pomocą makr i formuł w Excelu - ebook
Porządkowanie danych za pomocą makr i formuł w Excelu - ebook
Warunkiem efektywnej pracy z Excelem jest uporządkowanie danych i doprowadzenie ich do postaci, w której nadają się do właściwej obróbki. Taka konieczność zdarza się bardzo często przy importowaniu danych z zewnętrznych źródeł, ale także, gdy przychodzi analizować tabele zaprojektowane przez współpracowników. W wielu przypadkach porządkowanie danych czy przebudowa tabel wiąże się ze żmudnymi i czasochłonnymi operacjami. Niniejsza książka prezentuje szereg rozwiązań, jak np. makra, dzięki którym można sobie znacznie uprościć i skrócić te niewdzięczne zadania.
Kategoria: | Poradniki |
Zabezpieczenie: |
Watermark
|
ISBN: | 978-83-269-3307-3 |
Rozmiar pliku: | 7,7 MB |
FRAGMENT KSIĄŻKI
Przetwarzanie danych w zestawieniach wiąże się niekiedy z bardzo częstym filtrowaniem. Niestety, każdorazowe definiowanie kryteriów grupowania danych staje się w pewnym momencie uciążliwe. Dlatego sprawdźmy, jak te czynności zautomatyzować za pomocą odpowiednich makr. Dzięki nim grupowanie danych będziemy mogli uruchamiać jednym kliknięciem myszy. Z hasła dowiemy się m.in.:
Zastosujmy makra, a częste filtrowanie tabeli będzie wygodniejsze.
JAK DZIAŁA FILTROWANIE ARKUSZA ZA POMOCĄ MAKR?
Przyjmijmy, że zarządzamy flotą samochodów w firmie i w arkuszu znajduje się obszerna tabela z danymi po- jazdów. Na rysunku 1 przedstawiony jest widok arkusza po modyfikacji.
Rys. 1. Arkusz z danymi samochodów w firmie
W górnej części arkusza znajdują się komórki i przyci- ski służące do filtrowania tabeli.
1. Jeśli chcemy, aby pokazywane były tylko samochody dostawcze, to naciskamy przycisk Pokaż dostawcze.
2. Jeżeli chcemy odkryć wszystkie wiersze, to naciska- my przycisk Pokaż wszystkie.
3. Aby pokazywane były tylko samochody, za które od- powiedzialny jest Leszek Pelc, to klikamy komórkę E3 i z listy rozwijanej wybieramy tego pracownika.
4. Jeśli chcemy, aby w tabeli pokazywane były tylko samochody, które będą przechodziły przegląd w naj- bliższym miesiącu, to naciskamy przycisk Najbliż- sze przeglądy.
Rys. 2. Tabela po naciśnięciu przycisku Pokaż dostawcze
Rys. 3. Samochody, za które odpowiedzialny jest Leszek Pelc
Po wybraniu pracownika pokazane będą wszyst- kie samochody, za które on odpowiada niezależnie od ustawionych wcześniej parametrów filtrowania. Poprzednie ustawienia filtrowania będą usunięte. W ten sam sposób działają inne przyciski, dzięki któ- rym ustawiane są odpowiednie kryteria filtrowania.
Rys. 4. Tabela pokazująca tylko te samochody, których przegląd mija w najbliższym miesiącu
Obok tabeli z danymi samochodów znajduje się tabela z wymienionymi elementami wyposażenia dodatkowego.
5. Jeśli w tabeli mają być pokazywane tylko samochody, które posiadają immobiliser (IMM) i autoalarm (AL), to zaznaczamy komórki zawierające oznacze- nia tych elementów wyposażenia. W tym celu klika- my komórkę K3, a następnie, trzymając wciśnięty klawisz Ctrl, zaznaczamy komórkę O3.
Rys. 5. Komórki z symbolami wyposażenia dodatkowego
Rys. 6. Filtrowanie tabeli według wyposażenia dodatkowego
SAMODZIELNE PRZYGOTOWANIE ARKUSZA
W tym rozdziale dowiemy się, w jaki sposób wykonać arkusz opisany w pierwszej części tekstu. Informacje na temat samochodów wpisane są do tabeli przedstawionej na rysunku 7.
Aby przygotować arkusz:
1. Zaznaczamy 5 pierwszych wierszy arkusza i wy- bieramy polecenie menu Wstaw/Wiersze (w Excelu 2007/2010: na karcie Narzędzie główne, w grupie
Rys. 7. Dane samochodów przed modyfikacją arkusza
poleceń Komórki wybieramy Wstaw/Wstaw wiersze arkusza), aby wstawić 5 pustych wierszy na samej górze arkusza.
2. Wybieramy polecenie menu Widok/Paski narzędzi/ Formularze, aby wyświetlić pasek narzędzi Formu- larze (w Excelu 2007/2010: przechodzimy do karty Deweloper).
Wstawienie formantów
-
Na pasku narzędzi Formularze wybieramy pole- cenie Przycisk (w Excelu 2007/2010: na karcie Deweloper, w grupie poleceń Formanty wskazuje- my Wstaw i w sekcji Formanty formularza klikamy Przycisk). Następnie we wstawionych wierszach w kolumnach A oraz B zaznaczamy miejsce, gdzie chcemy wstawić pierwszy przycisk.
- Pojawi się okno Przypisywanie makra. Naciskamy przycisk Anuluj, gdyż makro będzie później przypi- sywane do przycisku.
Rys. 8. Przycisk po wstawieniu do arkusza
1. W podobny sposób wstawiamy drugi przycisk w ko- lumnach C i D oraz trzeci w kolumnach F, G i H.
2. Klikamy pierwszy przycisk prawym przyciskiem myszy i z menu podręcznego wybieramy polecenie Edytuj tekst. Tekst na przycisku będzie gotowy do edy- cji. Zaznaczamy go i wpisujemy: Pokaż dostawcze.
3. W podobny sposób zmieniamy teksty pozostałych dwóch przycisków. Środkowy opatrzmy opisem: Po- każ wszystkie, a ostatni: Najbliższe przeglądy.
Rys. 9. Arkusz po wstawieniu przycisków i zmianie tekstów
Lista rozwijana
W następnych krokach sprawdzimy, jak utworzyć listę rozwijaną w komórce E3. Najpierw musimy utworzyć spis wszystkich osób odpowiedzialnych za samochody.
4. Zaznaczamy wszystkie komórki w kolumnie E, począwszy od komórki E7 aż do końca tabeli.
Jeśli w tej kolumnie tabeli nie ma pustych ko- mórek, to po zaznaczeniu komórki E7 możemy przytrzymać klawisz Shift, nacisnąć klawisz End, a następnie wybrać klawisz Strzałka w dół. Zostaną zaznaczone odpowiednie komórki. W podobny spo- sób możemy zaznaczać niepuste komórki występu- jące powyżej zaznaczonej komórki lub po jej prawej i lewej stronie. W każdym z tych przypadków trzeba użyć innego klawisza strzałki, odpowiedniego do kierunku zaznaczania komórek.
3. Wybieramy polecenie Dane/Filtr/Filtr zaawanso- wany (w Excelu 2007/2010: na karcie Deweloper, w grupie poleceń Sortowanie i filtrowanie klikamy Zaawansowane). W oknie filtra zaawansowanego wybieramy opcję Kopiuj w inne miejsce. Następnie klikamy w polu Kopiuj do i zaznaczamy komórkę L7. Później wskazujemy opcję Tylko unikatowe re- kordy i naciskamy przycisk OK.
4. W zakresie rozpoczynającym się od komórki L7 zostaną wpisani pracownicy odpowiedzialni za sa- mochody. Posortujmy tę listę alfabetycznie. Zazna- czamy komórki i wybieramy polecenie menu Dane/ Sortuj, w oknie sortowania wskazujemy opcję Nie ma wiersza nagłówka i zatwierdzamy OK (w Excelu
Rys. 10. Ustawienia filtra zaawansowanego
2007/2010: na karcie Narzędzia główne, w grupie poleceń Edycja wybieramy Sortuj i filtruj/Sortuj od A do Z).
- W komórce E2 wpisujemy tekst: Wybieramy osobę, a później formatujemy komórki E2 i E3 zgodnie z rysunkiem 11.
Rys. 11. Arkusz po sformatowaniu komórek E2 i E3
Rys. 12. Ustawienia listy rozwijanej w komórce
1. Zaznaczamy komórkę E3 i wybieramy polecenie menu Dane/Sprawdzanie poprawności (w Excelu 2007/2010: na karcie Dane, w grupie poleceń Narzędzia danych wskazujemy Poprawność danych). W oknie spraw- dzania poprawności na karcie Ustawienia, w polu Do- zwolone wybieramy Lista. W polu Źródło określamy zakres komórek w kolumnie L, w których znajduje się lista osób i naciskamy OK.
2. Podświetlamy komórki w kolumnie L zawiera- jące imiona i nazwiska osób odpowiedzialnych, a następnie wskazujemy polecenie menu Edycja/ Wyczyść/Formaty (w Excelu 2007/2010: na karcie Narzędzia główne, w grupie poleceń Edycja wy- bieramy Wyczyść/Wyczyść formaty), aby usunąć formatowanie komórek.
3. Ukryjmy teraz te imiona i nazwiska. Wybiera- my polecenie menu Format/Komórki (w Excelu 2007/2010: na karcie Narzędzia główne, w grupie poleceń Komórki wskazujemy Format/Formatuj ko- mórki). Przechodzimy do zakładki Liczby i wybiera- my kategorię Niestandardowe. W polu Typ wpisuje- my 3 średniki (;;;), a następnie naciskamy przycisk OK, aby zmienić format liczbowy komórki.
Rys. 13. Ustawienia formatu liczbowego komórki ukrywającego zawartość komórki
Imiona i nazwiska osób odpowiedzialnych nie będą widoczne w arkuszu. Zawartość takiej komórki będzie można odczytać na pasku formuły.
Rys. 14. Odczyt ukrytej zawartości komórki na pasku formuły
1. Na koniec w zakresie rozpoczynającym się od ko- mórki I3 wypisujemy wszystkie kody wyposaże- nia samochodów. Zaznaczamy komórki 2. wiersza widniejące nad kodami wyposażenia i scalmy je za pomocą polecenia Scal i wyśrodkuj, które odszu- kamy na pasku narzędzi Formatowanie (w Excelu 2007/2010: na karcie Narzędzia główne, w grupie poleceń Wyrównanie wybieramy Scal i wyśrodkuj). Do scalonej komórki wpisujemy tekst: Wybieramy wyposażenie dodatkowe: i formatujemy komórki zgodnie z rysunkiem 15.
Rys. 15. Komórki zawierające kody wyposażenia dodatkowego
PRZYGOTOWANIE MAKR
Arkusz jest przygotowany na dodanie kodu Visual Ba- sic i powiązanie go z wstawionymi przyciskami.
Aby to zrobić:
1. Otwieramy Edytor Visual Basic za pomocą pole- cenia menu Narzędzia/Makro/Edytor Visual Basic (w Excelu 2007/2010: klikamy kartę Deweloper i w grupie poleceń Kod naciskamy Visual Basic). Możemy także skorzystać z wygodnego skrótu kla- wiaturowego lewy Alt + F11.
2. W oknie Edytora wybieramy polecenie menu Insert/ Module, aby wstawić moduł standardowy.
3. Na środku pojawi się okno kodu tego modułu. U góry wpisujemy kod procedury, która będzie usuwać kryteria filtrowania i pokazywać wszystkie ukryte wiersze (rys. 16).
W tej procedurze sprawdzane jest, czy arkusz jest aktu- alnie w trybie filtrowania lub inaczej mówiąc: czy au- tofiltr został uaktywniony. Jeśli tak, to za pomocą me- tody ShowAllData wszystkie aktualnie wprowadzone kryteria filtrowania są usuwane i ukryte wiersze stają się widoczne.
Rys. 16. Kod usuwający zadane kryteria filtrowania
1. Poniżej w oknie modułu dodajemy procedurę Dostawcze, której kod przedstawiony jest na ry- sunku 17.
Rys. 17. Procedura pokazująca tylko dostawcze samochody
Na początku wywołujemy wcześniejszą procedurę (rys. 16), która usuwa kryteria filtrowania. Następnie za pomocą me- tody Autofilter pokazywane są w 3. kolumnie tylko te ko- mórki, w których wpisany jest tekst: dostawczy. Pozostałe wiersze są ukrywane.
1. Pod procedurą Dostawcze wpisujemy kolejną. Kod przedstawiony jest na rysunku 18.
Procedura ta, podobnie jak poprzednia, najpierw wywo- łuje procedurę odkrywającą wszystkie ukryte wiersze. Następnie wyświetla tylko te wiersze, w których ko- mórki z kolumny G zawierają datę wcześniejszą od ak- tualnej o 31 dni lub mniej. Wiersze niespełniające tego warunku pozostają ukryte.
Argumentem właściwości Range może być dowol- na komórka filtrowanej tabeli. Jeśli chcemy filtrować według innej kolumny, to zamiast liczby 3 po nazwie argumentu Field wpisujemy inny numer kolumny. Pamiętajmy, że ich numeracja nie odnosi się do całe- go arkusza, ale tabeli podlegającej filtrowaniu. Jeżeli przed kolumną Lp. znajdowałaby się pusta kolumna, a zatem przeznaczenie pojazdu byłoby określone w ko- lumnie D, to wciąż przy filtrowaniu miałaby numer 3, chociaż w arkuszu byłaby na 4. miejscu.
Jeśli chcemy pokazać tylko samochody osobowe, a nie dostawcze, to po nazwie argumentu Criteria1 wpisujemy w cudzysłowie kryterium filtrowania: osobowe.
Rys. 18. Kod procedury Przeglądy
1. W okienku Project – VBA Project klikamy dwukrot- nie moduł arkusza
Darmowy fragment