1 dr inż. Piotr Muryjas Wyższa Szkoła Przedsiębiorczości i AdministracjiNarzędzia informatyki - arkusze kalkulacyjne IV (zaawansowane techniki obliczeniowe) dr inż. Piotr Muryjas Wyższa Szkoła Przedsiębiorczości i Administracji
2 Konsolidacja danych w arkuszachMożliwość prezentowania danych z różnych arkuszy w dowolnych przekrojach informacyjnych Grupowanie danych (kryterium grupowania) Dane skonsolidowane są rezultatem działań wykonywanych na grupach komórek (funkcje grupujące np. Min, Max, Licznik, Średnia, Suma) Techniki konsolidacji: tabele przestawne według odwołań 3W wg kategorii wg położenia ©Piotr Muryjas
3 Tabele przestawne - charakterystykaTabela przestawna - zbiór kolumn i wierszy, zawierający podsumowania i analizy otrzymane z wielu różnych tabel lub bloków komórek Oparta jest na danych zgromadzonych w arkuszu lub innej bazie danych (np. MS Access, SQL Server, dBase, ...) Posiada budowę tabeli krzyżowej (kolumny i wiersze opisane przez nagłówki, na ich przecięciu wartości funkcji grupujących) Możliwość elastycznego kształtowania struktury tabeli krzyżowej (przestawianie wierszy i kolumn) Analizy na dowolnym poziomie szczegółowości (wprowadzanie nowych kolumn i wierszy, dowolne funkcje grupujące na różnych poziomach) ©Piotr Muryjas
4 Tabele przestawne - przykład (1)Tabela przestawna z jednym poziomem grupowania ©Piotr Muryjas
5 Tabele przestawne - przykład (2)©Piotr Muryjas
6 Grupowanie (konsolidowanie) danychKryterium grupowania - pole bazy danych, którego wartość jest taka sama w danej grupie Rodzaje operacji na danych zgrupowanych (funkcje podsumowujące): sumowanie zliczanie wartość średnia w grupie danych wartość minimalna w grupie danych wartość maksymalna w grupie danych Operacje mogą być wykonywane na dowolnym polu bazy danych (nie musi to być pole stanowiące kryterium grupowania) ©Piotr Muryjas
7 Tabele przestawne - struktura tabelPole strony - pole BD będące nadrzędnym kryterium grupowania Element pola strony - wartość przyjmowana przez pole strony Pole danych - pole BD, na którym będą wykonywane operacje grupowe Pola wierszowe - pola BD, których wartości będą prezentowane w kolejnych wierszach Elementy pól wierszowych - wartości przyjmowane przez pola wierszowe Pola kolumnowe - pola BD, których wartości będą prezentowane w kolejnych kolumnach Obszar danych - wartości otrzymane w wyniku wykonania operacji grupowych na polu danych ©Piotr Muryjas
8 Struktura tabeli przestawnej - przykładPole strony Element pola strony Pole danych Pole kolumnowe Obszar danych Elementy pola kolumnowego Elementy pola wierszowego Pole wierszowe ©Piotr Muryjas
9 Etapy tworzenia tabel przestawnych1. Określenie rodzaju źródła danych (arkusz, zewnętrzne źródło danych, tabela przestawna). 2. Wskazanie danych. 3. Definicja struktury tabeli (wskazanie pól strony, pól wierszy i kolumn oraz pola danych) 4. Określenie rodzaju operacji wykonywanych na danych skonsolidowanych 5. Wskazanie miejsca umieszczenia tabeli przestawnej. ©Piotr Muryjas
10 Formatowanie tabel przestawnychFormatowanie tabel obejmuje: formatowanie etykiet (pole strony i danych, nagłówki wierszy i kolumn) formatowanie wartości (elementy pól wierszowych, kolumnowych oraz obszaru danych) Obszar danych nie dziedziczy formatu danych źródłowych (konieczne powtórne formatowanie) Format wyświetlanych danych może ulec zmianie po zmianie struktury tabeli ©Piotr Muryjas
11 Modyfikacja tabel przestawnychModyfikacja struktury (technika drag-and-drop w arkuszu lub wykorzystanie kreatora) Zmiana sposobu wyświetlania danych Grupowanie (rozgrupowanie) danych Określanie zakresu szczegółowości wyświetlanych danych Podsumowania - wyświetlanie, rodzaje Definiowanie pól i elementów obliczeniowych Zmiany w tabeli przestawnej nie wpływają na dane źródłowe !!! ©Piotr Muryjas
12 Modyfikacja struktury tabelObejmuje: dodawanie nowych pól ze źródła danych usuwanie pól tabeli przestawnej przestawianie kolejności pól w tabeli Skutki: zmiana sposobu prezentacji danych (inny układ wyświetlania) zmiana sposobu interpretacji danych dla danych zagregowanych (zmiana ilości kryteriów grupowania oraz ich kolejności) ©Piotr Muryjas
13 Modyfikacja struktury - przykładTabela przed zmianą struktury Tabela po zmianie struktury ©Piotr Muryjas
14 Modyfikacja sposobu wyświetlaniaZmiana formatu wyświetlania danych Zmiana nazwy pól (wierszowych, kolumnowych) Sortowanie danych: wg wartości elementów pól wg wartości z obszaru danych Inne zmiany sposobu wyświetlania: rezultatów funkcji podsumowujących (np. jako % z kolumny, wiersza) w przypadku błędów dla pustych komórek ©Piotr Muryjas
15 Grupowanie danych Wykonywane na strukturze tabeli przestawnej (elementy pól wierszowych lub kolumnowych oraz dane liczbowe) Grupowanie elementów pól wierszowych (kolumnowych): prowadzi do powstania dodatkowych kategorii (podgrup) umożliwia definiowanie dodatkowych podsumowań przy pomocy funkcji wbudowanych Grupowanie danych liczbowych w obszarze danych: możliwość definiowania podsumowań dla ograniczonego zakresu danych (początek i koniec zakresu grupowania) możliwość określania liczności grupy np. każda grupa liczy dokładnie po 3 elementy Możliwe wykonanie operacji komplementarnej - Rozgrupowanie ©Piotr Muryjas
16 Grupowanie danych - przykładyGrupowanie elementów pól Grupowanie elementów pola wierszowego ©Piotr Muryjas
17 Grupowanie danych - przykłady c.d.Grupowanie danych liczbowych Grupowanie danych liczbowych ©Piotr Muryjas
18 Zakres szczegółowości danychSzczegółowość danych - różne poziomy reprezentacji danych Zmiana zakresu szczegółowości: ukrycie/wyświetlanie danych szczegółowych zwijanie/rozwijanie danych szczegółowych dla kolejnych kryteriów grupowania (analiza wielowymiarowa) Szczegóły mogą dotyczyć: pojedynczych pól kolumnowych lub wierszowych wszystkich pól kolumnowych lub wierszowych wartości wyświetlanych w obszarze danych Analiza wielowymiarowa problemu: powiązana z ilością kryteriów grupowania pozwala analizować dane na różnych stopniach uogólnienia (różnych wymiarach) np. sprzedaż - region - towar, sprzedaż - region ©Piotr Muryjas
19 Szczegółowość danych - przykładDane ogólne Dane szczegółowe ©Piotr Muryjas
20 Podsumowania w tabeli przestawnejZastosowanie: sum całkowitych (podsumowania dla całej tabeli) sum częściowych (podsumowania dla podgrup) Zmiana rodzaju podsumowania w grupach danych: definicja innej operacji standardowej (funkcji podsumowującej) wprowadzenie dodatkowej operacji standardowej definicja obliczeń niestandardowych wprowadzenie dodatkowych obliczeń niestandardowych ©Piotr Muryjas
21 Rodzaje podsumowań Sumy całkowite: Sumy częściowe:obliczane dla wszystkich kolumn lub wierszy wyznaczane przez funkcje podsumowujące użyte w obszarze danych możliwe użycie tylko jednej sumy całkowitej (jednej dla kolumn, jednej dla wierszy) Sumy częściowe: możliwe użycie wielu sum częściowych wyznaczane przez dowolne funkcje podsumowujące ©Piotr Muryjas
22 Podsumowania - przykładSuma cząstkowa Suma cząstkowa ©Piotr Muryjas
23 Podsumowania niestandardoweWykonywane w polach danych tabeli (wykorzystujące wbudowane funkcje): różnica względem % z % wiersza % kolumny % sumy zbiorczej Dowolne formuły definiowane przez użytkownika, wykorzystujące dane z tabeli przestawnej: pola obliczeniowe elementy obliczeniowe ©Piotr Muryjas
24 Podsumowania niestandardowe - c.d.% wiersza % kolumny ©Piotr Muryjas
25 Wykresy i tabele przestawneNa wykresie można prezentować tylko dane otrzymane w wyniku konsolidowania Tabela przestawna będąca źródłem danych dla wykresu nie może zawierać sum cząstkowych Wykresy oparte na tabelach przestawnych zmieniają się wraz ze zmianami struktury tabeli przestawnej (dodawanie/ usuwanie danych szczegółowych, zmiana pól wierszowych i/lub kolumnowych) Wykresy mogą być generowane oddzielnie dla każdej wartości pola strony ©Piotr Muryjas
26 Wnioski Tabele przestawne to narzędzie umożliwiające prowadzenie analiz danych w oparciu o podsumowania wykonywane na grupach danych. Elastyczna zmiana struktury tabel przestawnych oraz rozmaitość wbudowanych funkcji podsumowujących pozwala kształtować różne przekroje informacyjne w zależności od potrzeb użytkownika. ©Piotr Muryjas