1 Prowadzenie analizy danych w arkuszuWykład 4 Prowadzenie analizy danych w arkuszu

2 Analiza dużych grup danychExcel daje w tej dziedzinie wiele możliwości. Pozwala dane między innymi: konsolidować, sortować, filtrować, szybko obliczać sumy dla poszczególnych grup danych, wyszukiwać wartości średnie, maksymalne czy minimalne.

3 Konsolidacja danych Konsolidacja polega na łączeniu danych, posiadających takie same, powtarzające się etykiety (w tym przykładzie są to nazwiska pracowników). Przy konsolidacji danych można otrzymać różne zestawienia, w zależności od zastosowanej funkcji. Proces konsolidacji przebiega niezależnie od sposobu sortowania. Konsolidacja może dotyczyć jednej tabeli lub kilku tabel, umieszczonych w różnych arkuszach a nawet plikach. W arkuszu Zestawienie zostały zebrane dane o zarobkach pracowników z trzech miesięcy. W nowym arkuszu o nazwie Podsumowanie IV kwartał powinny znaleźć się średnie zarobki pracowników w IV kwartale. Tabela danych na podstawie, których zostanie przeprowadzona konsolidacja

4 Funkcje obsługujące arkuszePraca z wieloma arkuszami, może wymagać od użytkownika umiejętności wykonania następujących czynności: wstawienia dodatkowego arkusza, zmiany nazwy, utworzenia kopii lub przesunięcia arkusza, usunięcia zbędnego arkusza Kliknięcie prawym przyciskiem myszy zakładki któregokolwiek arkusza wyświetli menu podręczne, umożliwiające zarządzanie arkuszami. Wybór pierwszej opcji Wstaw rozwinie okno dialogowe, z którego w zależności od przeznaczenia arkusza wybieramy odpowiednią propozycję. Wybór szablonu arkusza podstawowego W efekcie pojawi się nowy arkusz; jego nazwę można zmienić wybierając z menu podręcznego funkcję Zmień nazwę Wynik konsolidacji najlepiej umieścić w nowym arkuszu. Dodawanie arkuszy, zmiana nazwy, przesuwanie arkuszy. Wybranie funkcji Przenieś lub kopiuj umożliwia zarządzanie kolejnością wyświetlania arkuszy Tworzenie kopii arkusza następuje po zaznaczeniu opcji Utwórz kopię

5 Konsolidacja Funkcję konsolidacji należy rozpocząć przy aktywnej komórce, od której ma się rozpocząć nowo utworzona tabela W arkuszu IV kwartał, z menu Dane wybieramy funkcję Konsoliduj

6 Okno dialogowe KonsolidujPo kliknięciu przycisku listy rozwijalnej, można wybrać funkcję, która ma zostać wykorzystana przy konsolidacji Przycisk zwijania palety funkcji do paska wiersza, dającego możliwość zaznaczenia odpowiedniego zakresu tablicy źródłowej w arkuszu Zestawienie

7 Określenie danych do konsolidacjiZaznaczenie zakresu danych podlegających konsolidacji Przycisk powrotu do palety funkcji Odwołanie do zakresu komórek A1:B34 z arkusza Zestawienie

8 Dodatkowe ustawienia Wyłączenie opcji Górny wiersz, Lewa kolumna wygeneruje jedynie uśrednione wartości liczbowe, bez etykiet dla wierszy i kolumn tabeli, co znacznie zmniejszy jej czytelność. Utworzenie dynamicznego łącza, automatycznie modyfikuje wartości w skonsolidowanej tabeli, w przypadku zmian w tabeli źródłowej.

9 Wynik konsolidacji Tablica wartości po konsolidacji Wybór przycisku Opcje... powoduje pojawienie się dodatkowych możliwości modyfikacji formatu tabeli Ta sama tabela po sformatowaniu np. z wykorzystaniem funkcji Autoformatowanie

10 Excel w roli bazy danychArkusz kalkulacyjny może spełniać funkcje typowe dla baz danych. Przede wszystkim pozwala gromadzić dane w postaci tabel lub formularzy podobnych do tradycyjnych, papierowych kartotek. Nazwa towaru Merida Kategoria Kawa Kraj pochodzenia Wenezuela Koszt zł/kg 15,75 Cena zł/kg 31,50 Ilość 350 Pozycja magazynowa W1 Nazwa towaru Antigua Kategoria Kawa Kraj pochodzenia Gwatemala Koszt zł/kg 15,75 Cena zł/kg 31,50 Ilość 250 Pozycja magazynowa S1 Nazwa towaru Assam Kategoria Herbata Kraj pochodzenia Indie Koszt zł/kg 14,55 Cena zł/kg 29,10 Ilość 275 Pozycja magazynowa N1 Nazwa towaru Cejlon Kategoria Herbata Kraj pochodzenia Sri Lanka Koszt zł/kg 17,55 Cena zł/kg 35,10 Ilość 300 Pozycja magazynowa N1 Jeden z rekordów bazy danych; wszystkie rekordy mają taką samą strukturę, określoną przez pola informacyjne – nagłówki kolumn tabeli, np. Nazwa towaru, Kategoria, Kraj pochodzenia itd. Tradycyjna, kartotekowa baza danych; kartoteki stanowią rekordy o określonej strukturze

11 Filtrowanie danych Podstawową zaletą elektronicznych baz jest możliwość szybkiego filtrowania danych, czyli wybierania rekordów, spełniających określone kryterium. Najprostsze zapytania można zrealizować włączając opcję Autofiltr W efekcie pojawią się w nagłówkach przyciski list rozwijalnych Autofiltra

12 Przykłady filtrowania - produkty pochodzące z IndiiKliknięcie przycisku listy rozwijalnej pokazuje możliwości wyboru kryterium filtrowania Przycisk kolumny stanowiącej kryterium filtrowania zostaje zaznaczony kolorem niebieskim W wyniku, otrzymujemy bazę zawężoną do rekordów, spełniających podane kryterium

13 Przykłady filtrowania - niebezpiecznie małe i zbyt duże zapasyWybór opcji Inne... wywoła okno dialogowe Autofiltra niestandardowego Ustawienie wartości uznanych za skrajne Przefiltrowana lista towarów

14 Przykłady filtrowania - pięć towarów o najwyższej cenieWybór opcji 10 pierwszych pozwoli w pojawiającym się oknie dialogowym ustalić parametry filtrowania Po ustawieniu liczby wyświetlanych wartości, należy zaznaczyć, czy mają to być wartości najmniejsze (dolne), czy największe (górne) Przefiltrowana tabela danych

15 Automatyczne obliczanieDane wyselekcjonowane w procesie filtrowania mogą podlegać dalszej obróbce np. poprzez podliczenia z zastosowaniem Automatycznego obliczania. Po zaznaczeniu zakresu w polu Automatycznego obliczania pojawia się wynik działania ostatnio wybranej funkcji na zaznaczonych danych Menu podręczne pozwala wybrać odpowiednią funkcję Sprawdzamy jaka jest w magazynie średnia ilość 6 najdroższych towarów Pole Automatycznego obliczania

16 Co zrobić z wynikami filtrowania?Wybór opcji (Wszystkie) pozwoli ponownie wyświetlić całą tabelę danych Przefiltrowaną bazę danych można: wydrukować, skopiować do innego arkusza, wykonać na przefiltrowanych rekordach obliczenia, lub po obejrzeniu wyników filtrowania wrócić do wyświetlania całej listy Wyłączenie Autofiltra

17 Sortowanie odbywa się według kolumny, której komórka jest aktywnaSortowanie danych - z uwzględnieniem jednego kryterium Przyciski sortowania Przycisk sortowania rosnąco i wynik jego działania Sortowanie odbywa się według kolumny, której komórka jest aktywna

18 Sortowanie danych - z uwzględnieniem jednego kryteriumPrzycisk sortowania malejąco i wynik jego działania

19 Sortowanie danych - z uwzględnieniem kilku kryteriówPo wybraniu z menu Dane funkcji Sortuj pojawia się okno dialogowe Sortowanie, w którym możliwe jest dokonanie ustawień hierarchii kryteriów sortowania oraz dodatkowych opcji sortowania. I kryterium Kategoria II kryterium Kraj pochodzenia III kryterium Koszt zł/kg

20 Sumy pośrednie Sumy pośrednie - funkcja z menu Dane pozwala analizować podsumowania wielu grup danych jednocześnie. Dane przed zastosowaniem tej funkcji muszą być posortowane. Sumy ilości towarów z kategorii

21 Zagnieżdżone sumy pośrednie Pierwszy poziom sum pośrednichLiczba poszczególnych towarów w obrębie kategorii i dalej w obrębie kraju pochodzenia – (zagnieżdżone sumy pośrednie) Przed wprowadzeniem nowych sum pośrednich należy usunąć efekt poprzedniego działania przyciskiem Usuń wszystko Określenie pierwszego poziomu sum pośrednich w obrębie kategorii

22 Zagnieżdżone sumy pośrednie Drugi poziom sum pośrednichPoziom konspektu Parametry określające drugi poziom sum pośrednich Przy określaniu drugiego poziomu, aby nie usunąć poprzednich ustawień, istotne jest wyłączenie opcji Zamień sumy pośrednie Przycisk Pokaż szczegóły Przycisk Ukryj szczegóły

23 The end