Sumy częściowe3tab.doc

Sumy częściowe (pośrednie)

Narzędzie Sumy cześciowe pozwala na tworzenie wierszy podsumowujących dla danych grup listy. Za jego pomocą można prowadzić różne wyliczenia, na przykład: średnie wartości z okreslonych grup wierszy, liczba elementów w grupie.

Sposób posługiwania się przedstawię na przykładzie poniższej tabeli.

Średnia cena towaru w każdej kategorii

Pierwszym krokiem musi być przesortowanie tabeli według kolumny Nazwa kategorii, ponieważ dla niej tworzymy podsumowanie.

Następnie należy upewnić się, czy komórka aktywna znajduje się w zakresie komórek należących do tabeli.

Kolejnym krokiem będzie wybranie polecenia z menu Dane | Sumy częściowe.

W wyniku program stworzy konspekt, który można zwijać i rozwijać za pomocą oznaczonych przycisków, a także za pomocą przycisków Plus i Minus widocznych po lewej stronie arkusza.

Poziom 1 konspektu pokazuje średnią cenę dotyczącą danych w całej tabli.
Poziom 2 przedstawia tylko wiersze podsumowań dla poszczególnych grup towarów.
Poziom 3 wyświetla całą tabelę z wierszami podsumować utowrzonymi poniżej każdej grupy towarów.

Aby przywrócić poprzedni wygląd tabeli należy z okna Sum częściowych kliknąć przycisk Usuń wszystko.

Liczba towarów w każdej kategorii

1. Przesortuj tabelę według kolumny Nazwa kategorii.

2. Wybierz ustawienia w oknie Sumy częściowe tak jak na poniższym zrzucie.

Zastosowana funkcja Licznik może wykonać dodać sumę pośrednią do dowolnej kolumny. Ja wybrałam kolumnę Nazwa produktu, choć identyczny wynik uzyskałabym wskazując kolumnę Cena jednostkowa czy Rabat.

Stan magazynowy towarów w każdej kategorii

Podobnie jak poprzednio najpierw sortujemy tabelę według kolumny Nazwa kategorii.

Następnie wprowadzamy następujące ustawienia...

i w wyniku otrzymujemy następujące zestawienie (konspekt poziom 2).

Wyświetlanie dwóch sum częściowych na jednym konspekcie.

W tym przykładzie wykonam podsumowanie najniższej oraz najwyższej ceny jednostkowej produktu w każdej kategorii.

Tak jak poprzednio tabele sortujemy według kolumny Nazwa kategorii i wybieramy poniższe ustawienia w oknie Sumy częściowe:

Następnie ponownie otwieramy okno Sum częściowych, zmieniamy funkcję na maksimum i odznaczamy opcję Zamień bieżące sumy pośrednie.

Opcja ta pozwoli na dodanie nowego podsumowania do porzedniego.

Formatowanie konspektu

Konspekt jaki program tworzy w wyniku działania Sum częściowych może być mało czytelny zwłaszcza na najniższym poziomie konspektu.

Do zwiększenia czytelności warto zastosować polecenie Format | Autoformatowanie. Po wybraniu jednego z dostępnych formatów konspekt będzie prezentował się o wiele lepiej.

Tabele przestawne

Tabela przestawna to narzędzie służące do tworzenia dynamicznych podsumowań list utworzonych w Excelu lub pobranych z zewnętrznych baz danych. Raporty tabeli przestawnej pozwalają na opracowywanie tabelarycznych zestawień i podsumowań rekordów poprzez łączenie wartości pochodzących z różnych pól. Dane umieszczone w tabeli przestawnej można rozmieszczać na różne sposoby a także stosować różne funkcje do analizy zbioru. Zbudowaną tabelę można dowolnie modyfikować, zmieniając jej układ tak, aby prezentowała dane w innym aspekcie.

Sposób tworzenia tabeli przestawnej pokażę na przykładzie listy krajów z podstawowymi informacjami o nich.

Tworzenie raportu tabeli przestawnej z listy danych

Zaznacz dowolną komórkę należącą do listy, na podstawie której tworzysz tabelę przestawną.

Wybierz polecenie Dane | Raport tabeli przestawnej i wykresu przestawnego…

Na ekranie pojawi się okno dialogowe Kreator tabel przestawnych i wykresów przestawnych.

Wybierz opcję Lista lub baza danych programu Microsoft Office Excel oraz
opcję Tabela przestawna.

Kliknij przycisk Dalej.

Na ekranie pojawi się okno drugiego kroku kreatora.

Sprawdź, czy w polu Zakres wprowadzone zostało odwołanie do odpowiedniego zakresu komórek określających położenie listy. Jeśli zakres jest niewłaściwy kliknij przycisk Zwiń dialog i wybierz odpowiedni obszar bezpośrednio w arkuszu.

Wybierz przycisk Dalej.

Na ekranie pojawi się okno trzeciego kroku kreatora.

Aby umieścić raport w nowym arkuszu kalkulacyjnym, wybierz pole opcji Nowy arkusz. Możesz także skorzystać z opcji Istniejący arkusz i wybrać odpowiednią komórkę, jeśli chcesz, by raport pojawił się w bieżącym arkuszu.

Wybierz przycisk Układ.

Na ekranie pojawi się okno Kreator tabel i wykresów przestawnych – układ.

Budowanie tabeli przestawnej przypomina nieco składanie tabeli z części. Tymi częściami są poszczególne kolumny z listy. Różny układ tabeli, a co za tym idzie różne aspekty analizy danych jest możliwy dzięki umieszczaniu kolumn w tzw. polach tabeli przestawnej.
Pole wierszy - pole, któremu przypisano orientację wiersza w raporcie tabeli przestawnej.
Pole kolumny - pole, któremu w raporcie tabeli przestawnej przypisano orientację kolumny.
Raport tabeli przestawnej może mieć wiele pól kolumn, podobnie jak może mieć wiele pól wierszy.
Pole strony - pole, które jest przypisane do orientacji strony lub filtru. Gdy klikniesz inny element w polu strony, to cały raport tabeli przestawnej zmieni swój wygląd w ten sposób, że wyświetlane będą wyłącznie podsumowane dane powiązane z tym elementem.
Obszar danych - część raportu tabeli przestawnej zawierająca dane podsumowania.

Ja przeanalizę tabelę z danymi statystycznymi dotyczącymi ludności, powierzchni oraz kontynentów pod kątem średniej liczby ludności na każdym kontynencie.
Przeciągam pola Ludność i Kontynent na pola tabeli tak jak na ilustracji.
I wybieram przycisk OK.

Na ekranie ponownie pojawi się okno trzeciego kroku kreatora.

Kliknij przycisk Zakończ.

Excel utworzy zdefiniowaną tabelę przestawną i umieści ją w określonym miejscu. Excel domyślnie zastosował w tabeli przestawnej funkcję SUMA.

W przypadku, kiedy użytkownik nie skorzysta z przycisku Układ otrzyma na arkuszu schemat pustej tabeli przestawnej, na którą należy przeciągnąć pola wierszy, kolumny, danych i strony.

Zmiana typu obliczeń pola danych

Aby zmienić funkcję podsumowującą kliknij prawym przyciskiem myszy pole zaznaczone na rysunku.

Wybierz opcję Ustawienia pola.

1. Zaznacz funkcję Średnia.

2. W pole tekstowe Nazwa wprowadź bardziej czytelną nazwę.

3.Wybierz przycisk Liczby, aby ustawić niezbędne formatowanie liczb.

W oknie Formatowanie komórek zaznacz kategorię Liczbowe i ustal ilość miejsc dziesiętnych. Kliknij OK.

Program zastosuje wprowadzone ustawienia do tabeli przestawnej.

Filtrowanie pola.

Tabela przestawna umożliwia filtrowanie pól dzięki czemu można przeglądać tylko dane spełniające określone kryteria. Po utworzeniu tabeli lub po dodaniu pola wyświetlane są wszystkie elementy. Pola można jednak przefiltrować tak, aby widoczna była jedynie średnia liczba ludności dla Afryki, Azji i Europy.

Kliknij zaznaczony na czerwono przycisk strzałki widoczny z prawej strony pola, które chcesz przefiltrować.
Na ekranie pojawi się okienko listy wszystkich elementów.
Usuń zaznaczenie z pól, które chcesz ukryć w raporcie.
Kliknij przycisk OK.

Zmiana układu tabeli przestawnej

Tabele przestawne pozwalają na szybkie dodawanie, przemieszczanie i usuwanie pól z raportu.
Aby zmienić układ tabeli należy przesunąć pola do odpowiednich miejsc. Można zamienić miejscami pola wierszy i pola kolumn, a także dodać dodatkowe pola.

Dodawanie nowych pól do tabeli umożliwia okno Lista pól tabeli przestawnej. W tym oknie są widoczne wszystkie dostępne pola tabeli, przy czym pola używane obecnie w raporcie są wyróżnione pogrubieniem.

Na przykład do tabeli z danymi o średniej liczbie ludności dodam pole powierzchnia w km2.
Przeciągane pole należy umieścić w obszarze danych - środkowej części tabeli.

Excel po dodaniu nowego pola do tabeli przestawnej wyświetli taki oto układ:

Tabela wymaga korekty: zamiany funkcji Licznik na Średnia oraz przeformułowania nagłówka Średnia liczba ludności na kontynentach.

Przed dokonaniem poprawek należy kliknąć na wysokości nagłówka zaznaczonego strzałką. Kilknięcie spowoduje zaznaczenie wszystkich nagłówków tej kategorii. Następnie należy ponownie kliknąć w tym samym miejscu ale prawym przyciskiem myszy i wybrać opcję Ustawienia pola.

W polu Nazwa można skorygować zbyt długi nagłówek.

Przed zmianą funkcji Licznik, należy analogicznie zaznaczyć pola nagłówków Powierzchniaw m2 i wybrać funkcję Średnia w oknie Ustawieniach pola.

Poniżej zamieszczam przykłady tabel przestawnych analizujących bazę krajów pod względem różnych aspektów.

Liczba krajów na kontynencie posługujących się wybranym językiem.

Gęstość zaludnienia na każdym kontynencie.

Liczba języków urzędowych na każdym kontynencie.

W następnym tutorialu opiszę dodatkowe obliczenia w tabeli przestawnej.

Pola obliczeniowe w tabelach przestawnych

W poprzednim tutorialu Excela opisałam sposób tworzenia raportu tabeli przestawnej.
Dzisiaj postaram się opisać metodę tworzenia dodatkowych pól obliczeniowych w tabeli przestawnej.

Do tego zadania posłużę się tabelą, której fragment widać poniżej.

Korzystając z polecenia Dane|Raport tabeli przestawnej i wykresu przestawnego
tworzę układ tabeli tak jak na poniższym zrzucie.

Po utworzeniu raportu na arkuszu pojawia się tabela przestawna.

Wszystkie towary zostały zgrupowane według kategorii, do której należą. Przy każdym towarze jest wyświetlona
łączna ilość towaru oraz cena jednostkowa.

Ja natomiast uzupełnię tabelę o dwa pola obliczeniowe. Pierwsze obliczy łączną wartość każdego towaru,
drugie wyliczy wartość towaru pomniejszoną o przysługujący rabat.

Ustawiam komórkę aktywną w obszarze tabeli przestawnej i na pasku narzędziowym Tabela przestawna
wybieram polecenia: Tabela przestawna | Formuły | Pole obliczeniowe...

W oknie Wstawianie pola obliczeniowego należy wpisać nazwę pola. Ja podałam nazwę: Łączna wartość.
Następnie korzystając z dostępnych nazw pól, należy ułożyć formułę. Nazwy pól do formuły należy wprowadzać kliknięciami.

Jak widać Excel uzupełnił w tabeli podaną nazwę pola o nazwę funkcji. Można to oczywiście skorygować.
W tym celi należy kliknąć na wysokości wiersza Suma z Łączna wartość prawym przyciskiem myszy...

... i z menu kontekstowego wybrać polecenie Ustawienia pola.

Następnie w polu Nazwa należy skorygować nazwę pola na Łączna wartość produktów i dostosować format liczb
poprzez wybranie przycisku Liczby i zaznaczenie odpowiedniego formatu liczbowego.

W analogiczny sposób można wstawić do tabeli przestawnej kolejne pole obliczeniowe Wartość promocyjna.
A następnie dostosować jego tytuł oraz format liczb.

W tabeli korekty wymaga jeszcze pole Suma z Ilość.
Ja zmieniłam jego nazwę na Stan magazynowy i napisałam format liczbowy, który podane wartości będzie
wyświetlał jako sztuki.

I jeszcze drobna korekta tytułu pola Cena jednostkowa na Cena detaliczna...

I prawie gotowe.

Aby uatrakcyjnić wygląd tabeli przestawnej można zastosować jeden z gotowych formatów
z polecenia Format | Autoformatowanie. Ja wybrałam wzór o nazwie Raport 2.

Poniżej końcowy rezultat.

W przypadku gdyby zmieniły się dane źródłowe w podstawowej tabeli można aktualizować dane w tabeli przestawnej
poprzez przycisk Odśwież dane

sumy i tabele 2

Sumy pośrednie

Sumy pośrednie służą do analizowania danych etapami np.: podsumowanie wydatków po każdym kwartale, miesiącu....itp.

Jak  z tego korzystać

***Posortuj listę według pola, dla którego chcesz obliczyć sumy pośrednie

-gdy chcemy podsumować po każdym miesiącu to sortujemy miesiące

-gdy chcemy podsumować wydatki każdej firmy to musimy pogrupować firmy

1.      Zaznacz dowolną komórkę tabeli

2.      Dane/sumy częściowe

3. W oknie dialogowym sumy pośrednie wybierz z listy dla każdej zmiany nazwę pola dla którego będą liczone sumy pośrednie (miesiąc, firma...)
4. Z listy rozwijalnej użyj  funkcji wybierz funkcję, której należy użyć do obliczenia sum

5.       Na liście dodaj sumę pośrednią do: zaznacz pole do którego ma by dodana suma częściowa.(przeważnie będzie to kwota)

...