Znasz potrzebę i celowość stosowania adresowania poprzez nazwę Znane ci do tej pory sposoby adresowania komórek i zakresów komórek w zupełności wystarczają do dokonywania wszystkich obliczeń, rozwiązywania

róŜnych problemów w arkuszu. Często jednak wygodniejszym i łatwiejszym sposobem identyfikacji komórek jest nadanie im przez uŜytkownika nazw identyfikacyjnych. Ze stosowania nazw płynie wiele korzyści:

• utworzone w danym arkuszu nazwy komórek lub zakresów komórek mogą być wykorzystywane w całym skoroszycie (nazwy nie mogą się w nim powtarzać),

• formuły zawierające nazwy są łatwiejsze do zapamiętania i odczytania (jeśli je logicznie nazywałeś),

• po modyfikacji arkusza istnieje moŜliwość zaktualizowania adresu w jednym miejscu, po czym wszystkie formuły, które wykorzystywały jego nazwę, zostaną automatycznie zaktualizowane,

• stosowanie nazw jest szczególnie przydatne w duŜych arkuszach. W celu zdefiniowania nazwy:

• zaznacz komórkę, zakres komórek lub zakresy komórek, • następnie z wiersza menu wybierz opcję Wstaw, Nazwa, Definiuj, podaj

nazwę i wybierz przycisk Dodaj. Nazwę moŜesz równieŜ wpisać bezpośrednio w polu nazwy po

zaznać/emu nazywanej komórki lub zakresu komórek - musisz ją zatwierdzić klawiszem En-ter. Na rysunku XII.9, korzystając z pola nazwy na pasku formuł, zakres komórek A2:C2 nazwano szkoła.

Wprowadzając nazwy, powinieneś przestrzegać następujących zasad:

• moŜesz uŜywać liter, cyfr i znaków interpunkcyjnych, • nie moŜesz stosować spacji, • moŜesz stosować wielkie i małe litery, jednak nie są one rozróŜniane,

• wybieraj nazwę logiczną, aby moŜna łatwo kojarzyć ją z zakresem lub zakresami komórek, których dotyczy.

Sprawdź, czy potrafisz ? Nadaj zakresowi komórek A1:B9 (wypełnionemu przynajmniej c/,e><i(mi>

liczbami) nazwę koszty wszystkimi znanymi ci sposobami. ? W innym arkuszu oblicz sumę komórek zakresu zdefiniowanego w óvi< lenni

poprzednim róŜnymi sposobami, takŜe odwołując się do nazwy zdefiniowani go zakresu. Zmień nazwę arkus/a, w którym występuje nazwany zakres, /.mb serwuj formuły w arkuszu, w którym obliczałeś sumę, i sformułuj wnioski.

Potrafisz wykonywać i wiązać operacje w róŜnych arkuszach i skoroszytach

Najczęściej korzystasz z jednego arkusza. Czasami jednak rozwiązujesz problem, w którym wygodniej byłoby skorzystać z kilku arkuszy lub nawet z kilku skoroszytów.

Na przykład agencja wydawnicza prowadzi sprzedaŜ podręczników szkolnych.

Agencja ma kilka oddziałów w róŜnych miastach Polski. KaŜdy oddział rozlicza się osobno. W związku z tym w danym skoroszycie dla kaŜdego oddziału moŜesz przewidzieć osobny arkusz, w którym będą prowadzone zestawienia jego obro-tów, oraz jeden arkusz zbiorczy na podsumowanie obrotów z poszczególnych od-działów. W arkuszu zbiorczym będą wykonywane operacje wykorzystujące dane z pozostałych arkuszy. Arkusz zbiorczy będzie więc z nimi powiązany.

Spróbujmy przedstawić to bardziej szczegółowo. W skoroszycie wykorzystamy 4 arkusze: jeden zbiorczy oraz trzy dla oddziałów w Katowicach, Wrocławiu i Gdańsku. W kaŜdym arkuszu dotyczącym oddziału znajduje się tabelka w postaci takiej, jak na rysunku XII.10.

W arkuszu zbiorczym tworzymy podobną tabelkę. Chcemy w niej umieścić su-my obrotów wszystkich oddziałów w poszczególnych miesiącach (rys. XII. 11, s. 284).

Jeśli chcesz obliczyć, jaką kwotę w sumie agencja uzyskała ze sprzedaŜy pod-ręczników dla szkół podstawowych na przykład w lipcu, w arkuszu zbiorczym musisz podsumować zawartości komórek B4 z arkuszy oddziałów. W tym celu:

• ustaw kursor w arkuszu zbiorczym w komórce B4,

• kliknij ikonę sumowania - pojawi się formuła =SUMA(), w nawiasie będzie

ustawiony migający kursor,

• następnie kliknij myszką nazwę arkusza pierwszego oddziału, przytrzymaj klawisz Shift, kliknij nazwę arkusza ostatniego oddziału i komórkę, której za-wartość sumujemy, a następnie naciśnij klawisz Enter.

W komórce B4 arkusza zbiorczego powinna pojawić się formuła: =SUMA(Katowice:Gdańsk!B4>.

Formuła ta oblicza sumę zawartości komórek B4 ze wszystkich arkuszy oddziałów. Teraz otrzymaną formułę wystarczy tylko skopiować do pozostałych komórek tabeli zbiorczej. Formuły arkusza zbiorczego łączą arkusze.

MoŜliwe jest równieŜ łączenie skoroszytów. W przypadku pracy z duŜymi sys-temami, skoroszyt moŜe mieć zbyt duŜy rozmiar. Ponadto przechowywanie wszystkich danych w jednym skoroszycie mogłoby być po prostu niepraktyczne i trudno byłoby na nim pracować. MoŜna w takim układzie utworzyć kilka mniejszych skoroszytów i integrować dane w skoroszycie podsumowującym. Wynika z tego wiele korzyści:

• poszczególne skoroszyty mogą być redagowane niezaleŜnie od skoroszytu podsumowującego,

• moŜna pracować w danej chwili na pojedynczych skoroszytach bez konieczności otwierania wszystkich powiązanych ze sobą,

• mniejsze skoroszyty są szybciej otwierane, przeliczane i zachowywane. Połączenia pomiędzy skoroszytami odbywają się poprzez formuły -jakiekol-wiek zmiany dokonywane w danych źródłowych są natychmiast widoczne w połączonej formule.

W przypadku połączonych skoroszytów mówi się o skoroszycie zaleŜnym i skoroszycie źródłowym. Skoroszyt zaleŜny to taki, który zawiera połączenie ze skoroszytem źródłowym, czyli zawiera formułę, w której wykorzystywane są dane ze skoroszytu źródłowego. Skoroszyt źródłowy to ten, który jest źródłem informacji, do których odwołuje się formuła w skoroszycie zaleŜnym.

W formule wykorzystującej dane ze skoroszytu źródłowego musi być uŜyty tak zwany adres zewnętrzny. W skład adresu wchodzą:

• nazwa skoroszytu ujęta w nawiasy kwadratowe,

• nazwa arkusza, w którym znajduje się dana informacja,

• wykrzyknik, • adres komórki.

Na przykład, w skoroszycie o nazwie Wynagrodzenia, w arkuszu Arkusz l, komórce H12 znajduje się ogólna kwota, jaka jest potrzebna na wynagrodzenia dla pracowników. Tę wartość chcemy wykorzystać do obliczeń w skoroszycie Koszty. Wówczas w skoroszycie Koszty w odpowiedniej komórce piszemy formułę:

=[Wynagrodzenia] Arkusz l! $H$ 12.

Pracując z połączonymi skoroszytami, musisz pamiętać o paru zasadach:

• skoroszyt źródłowy zawsze zachowuj przed skoroszytem połączonym z nim,

• jeśli zmienisz nazwę skoroszytu źródłowego w czasie, gdy skoroszyt z nim związany jest otwarty, nazwa w adresie zewnętrznym zostanie zaktualizowana,

• jeśli zmienisz nazwę skoroszytu źródłowego w czasie, gdy skoroszyt powiąza-ny z nim jest zamknięty, moŜesz zerwać połączenie; wówczas musisz w menu Edycja wybrać polecenie Łącza i wybrać przycisk Zmień źródło,

• aktualizacja zmian dokonana w skoroszycie źródłowym (przy zamkniętym skoroszycie zaleŜnym) będzie dokonana dopiero po otwarciu skoroszytu za-leŜnego.

Sprawdź, czy potrafisz ? Zaproponuj zadanie, w którym wykorzystasz przynajmniej dwa arkusze

połączone ze sobą.

? Spróbuj wykonać następujące ćwiczenie. W dzienniku szkolnym kaŜdy przedmiot jest umieszczony na osobnej kartce i zawiera oceny wszystkich uczniów w klasie. Utwórz powiązane arkusze tak, Ŝeby dla kaŜdego przed-miotu był osobny arkusz przedstawiający dane z dziennika, oraz utwórz ar-kusz zbiorczy z nazwami wszystkich przedmiotów i nazwiskami wszystkich uczniów, w którym będą zawarte oceny końcowe z kaŜdego przedmiotu z pierwszego semestru.

Operacje bazodanowe w arkuszu kalkulacyjnym Arkusz kalkulacyjny moŜesz teŜ wykorzystać do tworzenia baz danych. Bazą

danych moŜe być np. Usta wszystkich uczniów w twojej szkole, ksiąŜka telefo-niczna, wykaz ksiąŜek w szkolnej bibliotece. W arkuszu baza danych stanowi zwykle tabelę. W bazie danych operuje się pojęciami pole i rekord. W przypadku bazy skiadającej się i danych uczniów twojej szkoły polami mogą być: nazwisko, imię, data urodzenia itp. Pola będą zatem odpowiednikiem kolumn. Natomiast rekordem będzie opisany wszystkimi polami zbiór danych pojedynczego ucznia. KaŜdy wiersz w tabeli będzie więc rekordem,

W rozdziale tym poznasz moŜliwości arkusza w zakresie wykorzystania go ja-ko bazy danych. Poznasz zasady porządkowania danych, wyszukiwania po-trzebnych informacji za pomocą filtrów i filtrów zaawansowanych, poznasz spo-soby wykorzystania formularza do przeglądania danych, wprowadzania no-wych i modyfikacji istniejących danych.

Potrafisz wykorzystać arkusz jako prostą bazę danych Jeśli utworzysz dowolną tabelę w arkuszu, moŜesz ją wykorzystać jako bazę

danych. Musi ona jednak spełniać określone warunki:

• w tabeli nazwy pól musza zajmować tylko jeden wiersz,

• pod wierszem nagłówkowym nie moŜe być pustego wiersza, oddzielającego go od reszty tabeli,

• kaŜdy rekord naleŜy wpisywać w oddzielnym wierszu, II tabela moŜe się znajdować tylko w jednym arkuszu,

• naleŜy zostawić chociaŜ jedną pustą kolumnę i jeden pusty wiersz pomiędzy tabelą a innymi danymi w arkuszu,

• z prawej i lewej strony tabeli nie powinieneś przechowywać Ŝadnych danych

• wskazane jest, aby w wierszu nagłówkowym tabeli uŜyć innej czcionki i jej stylu w celu odróŜnienia tytułów kolumn od reszty danych.

JeŜeli tak utworzysz tabele dla dowolnych danych, będziesz mógł w niej sto-sować wszystkie operacje moŜliwe do zrealizowania w bazie danych.

Sprawdź, czy potrafisz ? Utwórz tabelę zawierającą dane o wszystkich uczniach z twojej klasy.

KaŜdy uczeń powinien być opisany polami: Nazwisko, Imię, Data urodzenia,

Miejsce urodzenia, Kod pocztowy, Miasto, Ulica, Średnia ocen, Znajomość jeŜyka obcego, Płeć oraz Znak zodiaku. Tabela ta posłuŜy nam jako ćwiczenio-wa baza danych do wykonywania operacji w następnych rozdziałach.

? Zmodyfikuj utworzoną bazę danych z poprzedniego ćwiczenia, dodając do niej dodatkowe pole o nazwie Liczba rodzeństwa. Pole to umieść na przedostatniej pozycji.

Potrafisz sortować tabele i znajdować dane W tabeli, którą przygotowujesz w arkuszu, bardzo łatwo moŜesz porządko-

wać, czyli sortować dane, jak równieŜ wyszukiwać określone informacje. Sorto-wanie to zwyczajne porządkowanie danych w tabeli alfabetycznie, numerycznie lub chronologicznie według wybranego pola (kolumny). Sortowanie moŜe teŜ się odbywać według kilku kolumn. Posortowana tabela jest bardziej przejrzysta.

W celu posortowania danych w tabeli zaznacz dowolną komórkę, a następnie wybierz opcję Dane, Sortuj (cała tabela zostanie automatycznie zaznaczona). Mogą wystąpić problemy, jeśli w twojej tabeli pojawi się jakiś pusty rekord. W otwartym okienku sortowania moŜesz wybrać kolumnę, według której chcesz sortować dane, i porządek sortowania (rosnący lub malejący). Jeśli twoja tabela ma być sortowana jeszcze według kolejnego pola (szczególnie poŜądane, gdy w danej kolumnie wystąpią komórki identyczne), wybierz jego nazwę w polu następnie według i postępuj dokładnie tak, jak w pierwszym wierszu. Naciśnij przycisk OK - dane w tabeli są juŜ posortowane.

Jeśli dane w tabeli chcesz posortować tylko według jednej kolumny, moŜesz skorzystać z ikon dostępnych na pasku narzędzi.

Powinieneś teŜ umieć w szybki sposób znaleźć konkretne dane tekstowe lub liczbowe w tabeli. Pamiętasz z pewnością, jak w dokumencie utworzonym w edy-torze tekstu odnajdujesz miejsce w tekście zawierające określoną informację. Po-dobnie postępujesz w arkuszu. Wykorzystujesz do tego celu opcję Edycja, Znajdź. Analogicznie do Worda, moŜesz skor?.ystać ze skrótu klawiszowego Ctrl + F.

Warto wiedzieć

W tabeli za jednym razem moŜna dokonywać sortowania maksymalnie według trzech kolumn. JeŜeli zachodzi potrzeba posortowania danych według większej liczby kolumn, naleŜy najpierw dokonać sortowania według trzech najmniej istotnych kolumn, a potem kolejno według pozostałych w porządku ich waŜności.

Sprawdź, czy potrafisz ? Posortuj dane w tabeli zawierającej dane wszystkich uczniów w twojej

klasie według nazwiska. Następnie posortuj według płci i nazwiska.

? W tej samej tabeli uporządkuj dane tak, Ŝeby uczniowie pojawiali się według średniej ocen, alfabetycznie według nazwiska i imienia, i w kolejności rosnącej daty urodzin.

Znasz i potrafisz stosować filtry z podstawowymi opcjami

Umiesz juŜ wyszukiwać zadane informacje tekstowe lub liczbowe w tabeli, ko-rzystając z polecenia Znajdź w opcji Edycja. W arkuszu moŜliwe jest tzw. filtro-wanie danych, czyli szybkie i proste wyszukanie podzbioru danych spełniają-cych określone kryteria wyszukiwania. W wyniku działania filtra w tabeli zosta-ją wyświetlone tylko wiersze spełniające warunki wyszukiwania. Pozostałe wier-sze są czasowo ukrywane.

W arkuszu występują dwa rodzaje filtrów:

• autofiltr, który słuŜy do szybkiego wyszukania informacji na podstawie prostych kryteriów,

• filtr zaawansowany, umoŜliwiający wyszukiwanie informacji przy uŜyciu zło-Ŝonych kryteriów (zajmiemy się nim w kolejnym rozdziale).

Aby w tabeli, stanowiącej bazę danych, znaleźć rekordy spełniające określo-ne, proste warunki, moŜesz zastosować autofiltr.

Na przykład, w naszej ćwiczeniowej tabeli chcesz znaleźć nazwiska tych uczniów, którzy znają język angielski. W tym celu:

• ustaw kursor w wybranej komórce tabeli, najlepiej w tej kolumnie, w której będzie się odbywać wyszukiwanie,

• wybierz z menu opcję Dane, Filtr, Autofiltr; zauwaŜysz wówczas, Ŝe w tytułach kolumn tabeli pojawią się rozwijalne strzałki,

• kliknij strzałkę w tej kolumnie, która zawiera interesujące cię kryterium (znajomość języka) - wyświetli się okienko jak na rysunku XII.18, • wybierz z listy rozwijalnej nazwę ang.

W tabeli pokaŜą się tylko nazwiska uczniów znających język angielski. Numery wierszy w tabeli oraz strzałka rozwijalna w kolumnie, która stanowiła kryterium, będą podświetlone

na niebiesko. MoŜesz, oczywiście, zmieniać kryterium wyszukiwania, wybierając inny język, wówczas zawsze w tabeli będą pokazywane te rekordy, które spełniają warunek filtra. Gdy z rozwijalnej listy wybierzesz opcję Inne, będziesz mógł zdefiniować własne kryteria dla danego pola. Stosując prosty filtr, moŜesz równieŜ określi ć kryteria dla jednego, dwóch lub więcej pól. Na przykład chcesz wiedzieć, który z uczniów ma albo brata, albo siostrę i zna język angiel-ski. Po wybraniu opcji Autofiltr kliknij rozwijalną strzałkę w kolumnie Liczba rodzeństwa i wybierz l, następnie kliknij strzałkę w kolumnie Znajomość języka i zaznaczasz ang. Wówczas w odpowiedzi uzyskasz w tabeli tylko rekordy spełniające jednocześnie oba kryteria.

Aby usunąć kryteria filtrowania z danej kolumny, wskaŜ pozycję Wszystkie. Aby ponownie wyświetlić wszystkie rekordy w tabeli, wybierz z menu opcję Da-ne, Filtr, PokaŜ wszystko. Aby usunąć strzałki autofiltra, w opcji Dane, Filtr wy-czyść pole Autofiltr.

Warto wiedzieć Jeśli jakikolwiek filtr jest czynny, to numery rekordów mają kolor

niebieski, a kolumny, względem których nastąpiło filtrowanie, oznaczone są niebieskimi strzałkami.

Sprawdź, czy potrafisz ? W tabeli zawierającej dane o wszystkich twoich kolegach z klasy znajdź

kolejno:

- uczniów, którzy są jedynakami, - uczniów, którzy mają tylko brata albo siostrę, - uczniów, którzy mają średnią ocen wyŜszą od 4.

? W tej samej tabeli znajdź uczniów, którzy znają język angielski i mają średnią ocen powyŜej 3, a następnie uczniów, którzy mają średnią ocen wyŜszą niŜ 3 i niŜszą od 4,5.

? W tej samej tabeli znajdź uczniów urodzonych w dowolnym wybranym przez ciebie miesiącu (np. w maju lub w czerwcu). Wskazówka: w tym celu wstaw dodatkowe pole o nazwie Miesiąc i skorzystaj z funkcji daty.

Xli.4.d. A Znasz i potrafisz stosować filtry i zaawansowanymi opcjami

Wspominaliśmy juŜ o drugim rodzaju filtra, mianowicie o tzw. filtrze zaawan-sowanym. Dzięki niemu moŜesz w tabeli stanowiącej bazę danych wyszukać in-formacje, które spełniają bardzo złoŜone kryteria.

Przed uŜyciem zaawansowanego filtra naleŜy najpierw przygotować zakres kryteriów. Zakres kryteriów najlepiej zdefiniować w wierszach znajdujących się nad tabelą (jeśli ich nie masz, wstaw kilka pustych wierszy). NaleŜy go przedsta-wić w postaci tabelki, w której w wierszu nagłówkowym pojawią się nazwy pól badanych w bazie {tytuły kryteriów), a w pozostałych wierszach - kryteria. Tych wierszy moŜe być wiele.

Kryterium występującym w wierszu kryteriów moŜe być:

• tekst, • wartość liczbowa, • wartość logiczna Prawda lub Fałsz, • wyraŜenie zawierające operatory porównań, • łańcuchy tekstowe typu „Nowacki”, „B*”, • formuły.

Wpisując kryteria w wierszach kryteriów, musisz stosować następujące zasady:

• jeśli mają zostać znalezione rekordy spełniające jednocześnie wszystkie kryteria, wpisujesz kryteria w jednym wierszu,

• wpisujesz kryteria w róŜnych wierszach, jeśli mają zostać znalezione rekordy spełniające kryteria z jednego lub drugiego wiersza,

• jeśli chcesz podać kilka kryteriów dla tego samego pola, musisz uŜyć tyle razy nazwy danego pola, ile kryteriów chcesz określić.

Prześledźmy działanie filtra zaawansowanego na przykładzie, który moŜna rozwiązać za pomocą autofiltra. W tabeli (rys. XII.19) chcesz znaleźć wszystkich elektryków z miasta Bytom zarabiających ponad 2100 zł. W tym celu w komórkach A1:C2 został przygotowany zakres kryteriów. W tym zakresie w wierszu tytułów muszą się znaleźć nagłówki tych kolumn z tabeli, które będziesz przeszukiwał, a więc: Zawód, Miasto i Płaca. W wierszu kryteriów zostały zamieszczone warunki - kryteria wyszukiwania. Jak widzisz, wszystkie kryteria są wpisane w jednym wierszu. Oznacza to, Ŝe muszą być one spełnione jednocześnie.

Po określeniu zakresu kryteriów moŜesz przystąpić do uŜycia filtra zaawanso-wanego. W tym celu ustaw kursor w dowolnej komórce tabeli, a następnie w opcji Dane, Filtr wybierz Filtr zaawansowany. PokaŜe się wówczas okno widoczne na rysunku XII.20 na następnej stronie.

W otwartym oknie zaawansowanego filtra:

• sprawdź, czy jest podany poprawny zakres listy (tabeli, która jest bazą da-nych - w naszym przykładzie A5:E12),

• określ zakres kryteriów - kliknij myszką w wierszu Zakres kryteriów, a następnie zaznacz myszą zakres komórek w arkuszu, gdzie znajduje się zakres kryteriów (A1:C2),

• upewnij się, czy została wybrana opcja Filtruj listę na miejscu, • kliknij przycisk OK.

W tabeli zostaną ukryte wszystkie wiersze, które nie spełniają kryteriów. Pozostaną tylko te, które spełniają warunki wyszukiwania. Jak widzisz na rysunku XII.21, tylko 2 osoby spełniły warunki wyszukiwania.

Oczywiście, informacje te mogłeś uzyskać, korzystając z filtra prostego, ła-

twiejszego do wykonania. Nie zawsze jednak postawiony problem da się zrealizować przy uŜyciu autofiltra.

Sumy pośrednie Bardzo często w arkuszu tabela spełniająca warunki bazy danych zawiera duŜą

ilość informacji. Informacje te moŜna by w jakiś sposób pogrupować według zadanych kryteriów. Na przykład, w bazie ksiąŜek w bibliotece grupą byłyby ksiąŜki jednego autora albo ksiąŜki z jednej epoki, albo ksiąŜki dotyczące tej sa-mej tematyki. Narzędzie, jakim są sumy pośrednie, pozwala na dokonywanie róŜnego rodzaju podsumowań dla określonej grupy rekordów. Musisz zatem umieć przygotować arkusz do korzystania z sum pośrednich, wiedzieć, jak su-my pośrednie działają, jak ich uŜyć i jakie dają moŜliwości obliczeń.

Wiesz, do czego słuŜą sumy pośrednie Opracowałeś juŜ wiele tabel w arkuszu. ZałóŜmy, Ŝe masz tabelę zawierającą

dane wszystkich uczniów twojej szkoły. Chciałbyś w tabeli dokonać pewnych ob-liczeń bez uŜycia formuł. MoŜesz to zrobić, uŜywając sum pośrednich. Korzy-stając z tego narzędzia, moŜesz obliczyć liczbę uczniów w kaŜdej klasie, liczbę uczniów urodzonych w konkretnym mieście itp.

Jeśli na przykład masz tabelę, która zawiera dane dotyczące stanu magazynu z samochodami opisanymi marką, ceną, rokiem produkcji, pojemnością silnika, moŜesz w szybki sposób obliczyć: !£ liczbę samochodów danej marki w magazynie, S liczbę samochodów wyprodukowanych w danym roku, B wartość samochodów danej marki, Ci wartość samochodów o danej pojemności silnika, • wartość samochodów wyprodukowanych w konkretnym roku, H średnia wartość danej marki samochodu.

W narzędziu, jakim są sumy pośrednie, oprócz obliczania sum (jak wskazuje nazwa) moŜesz korzystać z funkcji takich, jak: Licznik (liczba rekordów spełnia-jących kryterium), Średnia, Max, Min, Iloczyn, Odchylenie Standardowe i inne.

Potrafisz zorganizować arkusz dla właściwego korzystania z sum pośrednich

Tabela, w której moŜna stosować sumy pośrednie, musi być odpowiednio zorganizowana. Najlepiej, aby spełniała warunki bazy danych. KaŜda kolumna

ma więc swoją nazwę, w tabeli nie ma pustych wierszy. W takiej tabeli naleŜy tak rozmieścić dane, Ŝeby wiersze do obliczenia sum pośrednich były zgrupowane razem. Najszybszym sposobem osiągnięcia tego jest posortowanie danych we-

dług określonej kolumny.

Na przykład, jeśli chcesz wiedzieć, jaką wartość w magazynie mają samochody poszczególnych marek, posortuj dane w tabeli według pola Marka samochodu. Po uporządkowaniu danych moŜesz juŜ przystąpić do wstawiania sum po-średnich {rys. XII.25). W tym celu:

• ustaw kursor w dowolnej komórce tabeli i wybierz z menu opcję Dane, Su-my pośrednie,

Zamień bieŜące sumy pośrednie

Podział strony pomiędzy grupami Podsumowanie poniŜej danych

• w oknie Sumy pośrednie, w wierszu Dla kaŜdej zmiany w: w naszym przy kładzie wybierz odpowiednią nazwę pola {Marka samochodu),

• w wierszu UŜyj funkcji wybierz nazwę Ŝądanej funkcji (w naszym przypad-ku Suma).

• w wierszu Dodaj sumę pośrednią do: zaznacz nazwę tej kolumny, pod którą chcesz mieć wynik obliczeń (Wartość).

W wyniku działania programu otrzymasz tabelę (rys. XII.26, s. 302), w której pojawią się wyniki sumowań. Samochody tej samej marki będą zgrupowane w sąsiadujących wierszach, pod którymi pokaŜą się podsumowania częściowe (wartość wszystkich samochodów danej marki). Na końcu tabeli pojawi się wiersz podsumowujący wartość wszystkich samochodów w tabeli.

W przedstawionej tabeli moŜesz oglądać wybrane częściowe informacje dzięki temu, Ŝe po wstawieniu sum pośrednich w arkuszu tworzona jest struktura. Stosując symbole struktury, moŜesz na przykład wyświetlić tylko wiersze pod-sumowujące dla kaŜdej marki lub tylko jeden ostatni wiersz podsumowujący. Wszystko zaleŜy od tego, jakie informacje w danej chwili są ci potrzebne. Aby ukryć wiersze szczegółowe dla jednej sumowanej grupy (np. dla samochodów marki Opel), kliknij znak minus znajdujący się na poziomie wiersza podsumo-wującego te samochody. MoŜesz w ten sposób ukrywać dowolne dane szczegó-łowe. Pozostanie tylko wiersz podsumowań dla tej grupy. W celu odkrycia da-nych szczegółowych kliknij znak plus, który pojawi się w miejscu minusa.

Tabelę z sumami pośrednimi moŜesz sortować. MoŜesz równieŜ dla sum po-

średnich tworzyć wykresy.

Warto pamiętać Sumy całkowite są obliczane na podstawie danych szczegółowych

zawartych w tabeli, a nie na podstawie wierszy stanowiących sumy pośrednie.

Potrafisz wykorzystać sumy pośrednie do obliczeń w bazie danych Znasz juŜ narzędzie, jakim są sumy pośrednie, wiesz, kiedy się je stosuje i w

jaki sposób z tego narzędzia skorzystać. Wiele obliczeń i analiz niejednokrotnie łatwiej jest wykonać, korzystając z sum pośrednich niŜ z gotowych funkcji. Sumy pośrednie pozwalają na bardziej złoŜone obliczenia. Wiesz juŜ, Ŝe sumy pośrednie moŜesz wykorzystać do obliczeń m.in. funkcji: Suma, Średnia, Mas, Min, Iloczyn, Licznik.

JeŜeli masz tabelę z bazą danych, potrafisz juŜ obliczyć sumy pośrednie dla jednej kolumny danych na podstawie przykładu omawianego w poprzednim podrozdziale.

MoŜesz równieŜ w jednym wierszu sum pośrednich wyświetlić sumy pośrednie dla więcej niŜ jednej kolumny. W tym celu w trakcie tworzenia sum pośrednich

musisz w polu Dodaj sumę pośrednią do: zaznaczyć kilka kolumn i wybrać od-powiednią funkcje do obliczeń (rys. XII.27). Na przykład, bazując na przykładzie omawianym wcześniej, chcesz obliczyć liczbę samochodów danej marki znajdujących się w magazynie oraz sumaryczną wartość samochodów określonej marki.

W obrębie juŜ istniejących grup sum pośrednich moŜna wstawić mniejsze

grupy podsumowań - mówimy wtedy o zagnieŜdŜonych sumach pośrednich. Oznacza to, Ŝe jeŜeli w bazie danych (korzystamy ciągle z bazy samochodów) masz juŜ obliczone sumy pośrednie dla kaŜdej marki samochodu, moŜesz wsta-wić sumy pośrednie na przykład dla kolumny Rok produkcji. W tym celu po-sortuj najpierw bazę według pola Marka samochodu, a następnie według pola Rok produkcji. Teraz kolejno, jak poprzednio:

• wstaw w znany ci sposób sumy pośrednie, zaznaczając w polu Dla kaŜdej zmiany w: - Marka samochodu,

• w polu UŜyj funkcji wybierz Suma, • w polu Dodaj sumę pośrednią do: wybierz Wartość. Masz wstawione sumy pośrednie. Samochody tej samej rnarki zostały zgru-powane i obliczona została dla nich całkowita wartość. W drugim kroku, znowu korzystając z sum pośrednich:

• w polu Dla kaŜdej zmiany w: wybierz Rok produkcji, • w polu UŜyj funkcji zostaw - Suma, • w polu Dodaj sumę pośrednią do: - zostaw Wartość, • koniecznie wyczyść pole wyboru Zamień bieŜące sumy pośrednie.

W ramach grupy rekordów dotyczących marki samochodu otrzymasz mniejsze grupy podsumowań dla kolumny Rok produkcji tak, jak to jest widoczne na rysunku XII.28 na następnej stronie.

Korzystając z sum pośrednich, moŜna równieŜ w bazie danych dokonać ob-liczeń kilku funkcji podsumowuj ących dla tej samej kolumny. Chcesz na przykład znać sumaryczną wartość oraz średnią wartość kaŜdej marki samo-chodu. Aby uŜyć kilku funkcji sumujących, naleŜy podczas pierwszego korzy-stania z sum pośrednich w polu UŜyj funkcji zaznaczyć jedną funkcję, po czym

ponownie wybrać sumy pośrednie, wskazać inną funkcję i wyczyścić pole wybo-ru Zamień bieŜące sumy pośrednie.

Sprawdź, czy potrafisz ? W bazie danych dotyczącej uczniów twojej klasy zaproponuj konkretne

obliczenia i zrealizuj je za pomocą sum pośrednich.

? W szkolnej bibliotece na pewno jest komputer, a w nim baza wszystkich ksiąŜek w bibliotece. Jeśli takiej bazy nie ma, musisz ją utworzyć. Wykorzystując sumy pośrednie, dokonaj wszystkich podsumowań, jakie, według ciebie, są moŜliwe.

Tabele przesławne Tabele przestawne są interakcyjnymi tabelami, które umoŜliwiają bardzo

szybkie dokonywanie podsumowań duŜej liczby danych. Tabelę przestawną tworzy się dla istniejących juŜ danych, posiadających kolumny tytułowe, jak równieŜ dla istniejących baz danych lub tabel utworzonych w innych aplikacjach w celu przeglądania danych i analizowania ich w odmienny sposób. Nazwa tabela przestawna bierze się stąd, Ŝe podczas jej tworzenia nagłówki wierszy i kolumn z zakresu komórek stanowiących źródło moŜna zamieniać i przestawiać - kolumna w danych źródłowych moŜe być wierszem w tabeli przestawnej i odwrotnie.

Tabela przestawna stanowi jedną z najłatwiejszych metod prezentacji i analizy podsumowań danych, które juŜ wcześniej zostały przedstawione w arkuszu lub innych aplikacjach. W rozdziale tym dowiesz się, kiedy i w jaki sposób tworzyć tabele przestawne, a następnie –jak je wykorzystać.

Znasz istotę i celowość stosowania tabel przestawnych Tabelę przestawna moŜna utworzyć dla dowolnej tabeli w arkuszu. Zwana

jest ona często tabelą podsumowującą, poniewaŜ moŜna w niej podsumować dane z tabeli źródłowej w zupełnie innym układzie. Tabela przestawna pomaga w porównywaniu danych, analizowaniu ich przy zastosowaniu wybranego układu tabeli oraz funkcji podsumowujących.

Dzięki tabelom przestawnym na dane zawarte w arkuszu moŜna spojrzeć inaczej.

Jeśli chcesz dla istniejących danych wykonać tabelę przestawna, zaznacz dane źródłowe, a następnie wybierz z menu Dane, Raport tabeli przestawnej. Kreator tabeli przestawnej poprowadzi cię za rękę, pomagając utworzyć tabelę. • Najpierw sprawdź, czy jako źródło danych jest zaznaczone pole Lista lub baza danych Microsoft Excel i naciśnij przycisk Dalej (rys. XII.29).

Następnie Kreator pokaŜe ci adres zakresu komórek (rys. XII.30, s. 306),

gdzie znajdują się twoje dane źródłowe. Upewnij się, czy zakres jest poprawny, i znowu kliknij przycisk Dalej

W otwartym oknie naciśnij przycisk Układ i moŜesz juŜ konstruować tabelę, przeciągając odpowiednie pola do obszaru wierszy, kolumn i danych (rys. XII.31, s. 306). Naciśnij przycisk Dalej.

• Określ miejsce usytuowania tabeli przestawnej (rys. XII.32). Naciśnij przy-

cisk Zakończ. W utworzonej tabeli przestawnej moŜesz w prosty i przejrzysty sposób

dokonać analizy podsumowań danych. Jeśli po utworzeniu tabeli przestawnej dokonasz zmian w danych źródłowych, dane w tabeli przestawnej zostaną zaktualizowane po wybraniu na pasku narzędzi Tabela przesławna przycisku OdświeŜ dane.

W tabeli przestawnej moŜesz dokonywać podsumowywania za pomocą wy-branych funkcji takich, jak: Suma czy Średnia, moŜesz kontrolować sposób obliczania sum pośrednich i całkowitych. Dla danych zawartych w tabeli przestawnej moŜesz tworzyć wykresy. Gdy dokonasz zmian w tabeli przesławnej, wykres będzie automatycznie zaktualizowany.

Sprawdź, czy potrafisz ? Przeanalizuj arkusz dotyczący samochodów i podaj przynajmniej jedną

propozycję dokonania obliczeń przy uŜyciu tabeli przestawnej.

? Stosując tabelę przestawną, oblicz liczbę samochodów konkretnej marki wyprodukowanych w poszczególnych latach.

Potrafisz praktycznie zastosować tabele przesławne i korzystać z generowanych wyników

Wiesz juŜ, jak tworzy się tabele przestawne, znasz równieŜ potrzebę i celowość stosowania tych tabel. Spróbujmy zatem teraz zająć się tym problemem od strony praktycznej. ZałóŜmy, Ŝe utworzyłeś w arkuszu tabelę, która zawiera wpłaty kaŜdego ucznia, z kaŜdej klasy na komitet rodzicielski. KaŜdy uczeń dokonywał wpłat raz w miesiącu. MoŜesz zatem na podstawie tej tabeli utworzyć tabelę przestawną, w której uporządkujesz wpłaty kaŜdej klasy według miesięcy i dokonasz podsumowania wpłat kaŜdej klasy w poszczególnych miesiącach. Przedstawimy to w poniŜszym przykładzie. Niech badana tabela wygląda jak poniŜej:

Na podstawie tej tabeli moŜesz utworzyć tabelę przestawną. Po

zaznaczeniu tabeli będącej źródłem danych, wybierz z menu Dane, Raport tabeli przestawnej. W momencie tworzenia tabeli przestawnej w oknie Kreatora tabel i wykresów przesławnych, Układ przeciągnij pole Miesiąc do obszaru wiersze, pole Klasa do obszaru kolumny, natomiast pole Wplata do obszaru dane. W wyniku otrzymasz następującą tabelę przestawną:

W otrzymanej tabeli:

• moŜesz przeanalizować dane, m moŜesz się dowiedzieć, ile kaŜda klasa wpłaciła pieniędzy w poszczególnych miesiącach,

• moŜesz się dowiedzieć, ile do chwili obecnej kaŜda klasa wpłaciła pieniędzy (zaleŜy od tego, w jakim czasie dokonujesz tej analizy, czy jest to trzeci, pią-ty, czy moŜe juŜ ostatni miesiąc nauki w danym roku),

3 moŜesz równieŜ poznać sumy zbiorcze z kaŜdego miesiąca i za cały dotychczasowy okres,

• moŜesz poznać informacje szczegółowe - zaznacz w tabeli przestawnej pole, na temat którego chcesz poznać szczegółowe informacje, i z paska narzędzi Tabela przestawna wybierz Szczegóły.

W tak otrzymanej tabeli dane moŜesz sortować według dowolnego pola, moŜesz je filtrować, grupować. MoŜesz zmieniać nazwy pól, zmieniać sposób przeliczania danych, zmieniać format. Informacje podsumowujące w tabeli mogą być równieŜ podstawą do utworzenia przez ciebie wykresu.

Poznanie mechanizmu tabeli przestawnej jest zatem bardzo celowe i przydatne w analizowaniu danych.

Sprawdź, czy potrafisz ? Spróbuj wykonać tabelę, w której przedstawisz oceny końcowe z

wszystkich przedmiotów na pierwszy i drugi semestr wszystkich uczniów ze swojej klasy. Następnie wykonaj tabelę przestawna, w której będziesz mógł przeanalizować oceny średnie dla kaŜdego przedmiotu i dla kaŜdego ucznia.