Wstęp do arkuszy kalkulacyjnych

Witamy na kursie, dzięki któremu zdobędziesz praktyczne umiejętności wykorzystania arkuszy kalkulacyjnych w codziennej pracy w księgowości i w biurze. Ich wszechstronne możliwości pokażemy na podstawie aplikacji: Microsoft Excel oraz bezpłatnych Apache OpenOffice Calc i działających on-line w przeglądarce internetowej Arkuszy Google.
 
Ważne: wszystkie operacje (począwszy od pierwszego uruchomienia programu) opisujemy równolegle - nie ma więc znaczenia, czy wykorzystujesz na co dzień oprogramowanie płatne, czy nie.
 
W trakcie e-kursu zwracamy uwagę na szereg przydatnych funkcji, jakie znajdują się w tych aplikacjach. Pokazujemy, jak można zautomatyzować wiele żmudnych przeliczeń, które nastręczają sporo problemów i są źródłem częstych pomyłek.
 
Kurs zawiera praktyczne przykłady oraz opatrzony jest licznymi ilustracjami. Dzięki wiedzy, jaką zdobędziesz, możliwe stanie się szybkie tworzenie dokumentów i wzorów, które wykorzystasz w swojej pracy.

Arkusze kalkulacyjne to narzędzie, które w prosty i szybki sposób umożliwia wykonywanie obliczeń matematycznych, księgowych i finansowych. Oprócz funkcji tzw. zaawansowanego kalkulatora mogą służyć magazynowaniu danych w celu ich dalszej edycji, w tym: filtrowania, sortowania i tworzenia skomplikowanych zestawień danych. Arkusze pozwalają na graficzną prezentację danych w formie różnego rodzaju wykresów. Mają szerokie zastosowanie nie tylko w firmach, ale i w gospodarstwach domowych.

Najpopularniejsze to arkusze firm Microsoft, Apache oraz arkusze oferowane przez Google. Nasz kurs stanowi zbiór konkretnych przykładów zastosowania funkcji powyższych aplikacji w rozwiązywaniu praktycznych problemów.

Kurs został stworzony tak, by użytkownik w trakcie wykonywania ćwiczeń nabył umiejętność łączenia różnych funkcjonalności arkusza kalkulacyjnego w analizie danych, co w efekcie pozwoli na wydajniejszą pracę z programem w przyszłości.

Pierwsza lekcja stanowi obszerny teoretyczny wstęp do zagadnień związanych z obsługą arkuszy kalkulacyjnych. Kolejne lekcje będą poświęcone zastosowaniom arkuszy w praktyce.

Układ danych – skoroszyt i arkusze, komórki

Do napisania kursu wykorzystujemy wersję Microsoft Office Excel 2019. Jest ona obecnie najczęściej występującym arkuszem kalkulacyjnym w firmach. Inne wersje programu Excel zawierają zdecydowaną większość formuł i opcji, które zostaną zaprezentowane w trakcie kursu. Mogą jednak różnić się szatą graficzną.

Po uruchomieniu nowego arkusza w programie Microsoft Office Excel należy zwrócić uwagę na podstawowe elementy dostępne na widocznym ekranie programu.

Najistotniejsza z punktu widzenia ergonomii pracy z programem jest tzw. wstążka. Jej zadaniem jest szybkie odnajdywanie potrzebnych opcji. Zostały one odpowiednio pogrupowane i umieszczone na kartach. Każda z nich stanowi logiczny zbiór narzędzi potrzebnych dla danego typu działania, np. pisania lub wstawiania formuł.

Karty zmieniają się dynamicznie, w zależności od wykonywanej operacji, tak aby użytkownik mógł skorzystać z odpowiednich opcji bez ich zbędnego szukania, wtedy gdy są potrzebne.

W trakcie pracy na dokumencie (skoroszycie) tworzymy arkusze danych. W każdym z arkuszy podstawowym obiektem, do którego wprowadzamy dane, jest komórka. Na komórce wykonywane są operacje przetwarzania danych.

Każdą komórkę identyfikuje się za pomocą adresu. Adres składa się z numeru wiersza oraz litery oznaczającej kolumnę (co widać w: Polu nazwy). Poprawne wskazywanie komórek jest podstawą prawidłowego przetwarzania danych przy pomocy formuł. Te odwołują się bowiem do wartości umieszczonych w konkretnych komórkach arkusza przy pomocy adresów.

Dla pakietu OpenOffice stosujemy wersję Apache OpenOffice 4.1.7. Podobnie jak w przypadku opisanego powyżej Excela, inne wersje programu nie różnią się od siebie w zakresie wykorzystywanych w kursie funkcji programu.

Najnowszą dostępną wersję darmowego pakietu Apache OpenOffice można pobrać ze strony: https://www.openoffice.org/pl/download/.


Ważne!

Arkusze utworzone przy pomocy programu Microsoft Office Excel, zarówno starych wersji (pliki .xls), jak i wersji nowszych (pliki .xlsx), można obecnie bezproblemowo otwierać i edytować przy pomocy programu OpenOffice Calc.

W przypadku arkuszy utworzonych w programie Calc (przyjmijmy na potrzeby kursu taką skrótową nazwę programu) sprawa wygląda nieco inaczej. Aby wszystkie formuły zostały właściwie odczytane przez Excela, arkusz utworzony w programie Calc powinien zostać zapisany jako plik z rozszerzeniem .xls (a nie w domyślnym formacie programu Calc, czyli .ods).


Podstawowe okno programu Calc zawiera:

  1. główne (górne) menu programu,
  2. paski narzędzi – domyślnie program wyświetla paski:
    • standardowy,
    • formatowanie,
    • znajdź,
    • pasek formuły,
  3. tabelę, czyli najważniejszą część arkusza, w której podobnie jak w Excelu kolejne wiersze oznaczone są kolejnymi cyframi, a kolumny – literami,
  4. zakładki arkuszy, służące do otwierania kolejnych arkuszy w ramach jednego dokumentu (pliku),
  5. suwak do powiększania i pomniejszania rozmiarów tabeli.

Menu główne programu pozwala na uruchamianie wszystkich opcji arkusza Calc. Pierwsze w kolejności menu Plik służy do czynności, takich jak: tworzenie nowych oraz zapisywanie istniejących dokumentów, drukowanie arkuszy, zamykanie dokumentu.

Paski narzędzi, które widzimy przy pierwszym uruchomieniu programu, to paski domyślne. W każdej chwili z menu Widok możemy wybrać i włączyć (albo wyłączyć) kolejne paski narzędzi.

Następne pozycje w menu omawiamy w trakcie kursu.

Arkusze Google są trzecią – alternatywną – aplikacją do pracy z arkuszem kalkulacyjnym, działającą w trybie on-line w przeglądarce internetowej.

Na poniższym zrzucie pokazujemy podstawowe funkcje widoczne na ekranie dla użytkownika aplikacji. Ich układ jest przejrzysty, a ikony menu – czytelne. W miarę omawiania kolejnych funkcji arkuszy kalkulacyjnych zaprezentujemy możliwość ich zastosowania w tej aplikacji.

Przy arkuszach Google korzystamy z aktualnie udostępnionej do użytku (w trakcie pisania kursu) wersji.

Wprowadzanie różnych typów danych i ich formatowanie

Komórka pozwala na przechowywanie następujących typów danych:

  • tekstowe: Układ danych
  • liczbowe: Układ danych
  • data i czas: Układ danych

W zależności od typu danych, które chcemy wprowadzić do komórki, przydaje się wiedza, jak zrobić to nie tylko szybko, lecz przede wszystkim poprawnie, czyli tak, aby np. dana liczbowa nie została uznana za tekstową i na odwrót. Ma to bowiem znaczenie przy dalszym formatowaniu komórek i wykonywaniu na nich przeliczeń.

Najczęściej do komórki wprowadzamy wartość liczbową i oczekujemy, że w taki sposób zostanie ona w arkuszu rozpoznana. Jeśli chcemy wpisać liczbę dziesiętną, najlepiej jest wykorzystać znak kropki na klawiaturze numerycznej (z prawej strony klawiatury). Jeżeli zastosujemy znak kropki z klawiatury alfanumerycznej (nad klawiszem spacji, obok prawego klawisza Shift), wprowadzona wartość liczbowa zostanie potraktowana jako tekst. W efekcie taka komórka ujęta w przeliczeniach funkcji, np. sumy, spowoduje wyświetlenie błędu argumentu (#ARG!) z komunikatem „błąd wartości”.

Jeśli wprowadzona wartość zostanie rozpoznana jako liczba, jest przyciągana do prawej strony komórki. Dane typu tekst są przyciągane do lewej strony komórki. Zasada ta obowiązuje wszystkie omawiane aplikacje (Excel, Calc, Google).

Układ danych

W celu rozdzielenia tysięcznych części liczby wykorzystujemy znak spacji. Pomimo, że w komórce wartość taka wyświetli się z wprowadzonym odstępem, w pasku formuły będzie poprawnie interpretowana.

Układ danych

Wprowadzając datę lub godzinę do komórki arkusza, stosujemy następujące podstawowe formaty. Dla daty:

  • dd-mm-rrrr (14-03-2019),
  • dd-mmm-rr (14-mar-19),
  • dd-mmm-rrrr (14-mar-2019),
  • dd-mmm (14-mar),
  • mmm-rr (mar-19).

Można zmieniać kolejność poszczególnych elementów formatu zapisu. Przykładowo, zamiast rrrr-mm-dd (2020-05-14) możemy zapisać dd-mm-rrrr (14-05-2020).

Data, aby została właściwie rozpoznana przez program, powinna zostać wprowadzona z jednym z dwóch separatorów: myślnikiem (-) lub prawym ukośnikiem (/).

Po wpisaniu daty do komórki, nawet przy użyciu separatorów, program zmienia format wpisanej daty na domyślny. Informację o domyślnym formacie, program pobiera z ustawień systemu operacyjnego Windows. To znaczy, że jeśli data w systemie operacyjnym Windows ustawiona jest w formacie z kropkami (14.05.2020), to po wpisaniu daty z myślnikami (2020-05-14) w komórkę w Excelu i zatwierdzeniu, zostanie wyświetlona data w formacie z kropkami (14.05.2020).

Dla godzin stosuje się następujące formaty zapisu:

  • gg:mm:ss (12:13:54),
  • gg:mm (12:13),
  • gg (12:00).

Jak widać, separatorem oddzielającym od siebie godziny, minuty i sekundy jest dwukropek (:).

Zmiana formatowania daty i czasu może odbyć się za pomocą narzędzi znajdujących się w sekcji: Liczba na karcie Narzędzia główne wstążki programu Excel:

Układ danych

Układ danych

lub poprzez opcję: Formatowanie komórek dostępną w aplikacjach Excel oraz Calc. Opcje do formatowania daty i czasu w arkuszu Google znajdują się na pasku narzędzi – zaprezentujemy je w dalszej części lekcji w rozdziale o formatowaniu komórek.

Możliwość zmiany formatu wyświetlania godziny najbardziej ograniczona jest w arkuszach Google. W pozostałych aplikacjach dostępny jest pełny zakres formatowania.

Poprawny zapis daty i czasu w komórkach umożliwia ich późniejsze wykorzystanie do obliczeń na podstawie formuł (sumy oraz różnicy). Będziemy o tym pisać w kolejnych lekcjach kursu.


Wskazówka

Podczas wprowadzania do komórek danych typu data i czas przydatne są następujące skróty:

  • Ctrl + ; - trzymając klawisz Ctrl, naciśnij znak średnika (;), a wtedy do aktywnej komórki zostanie wstawiona aktualna data w formacie: rrrr-mm-dd;
  • Ctrl + Shift + ; - trzymając klawisz Ctrl oraz Shift, naciśnij znak średnika (;), a wtedy do aktywnej komórki zostanie wstawiona aktualna godzina w formacie gg:mm.

Uwaga! Przytoczone skróty klawiaturowe nie mają zastosowania w aplikacji OpenOffice Calc.


Formatowanie komórek

W zależności od charakteru tworzonego arkusza i zastosowanych typów danych należy je poprawnie zaprezentować przy pomocy opcji do formatowania komórek. Przykładowo, tworząc zestawienie z wartością walut, należy prezentować je wraz z symbolem odpowiedniej waluty.

Trzy podstawowe sekcje, jakie wykorzystuje się do formatowania komórek, to:

  • Liczby – sekcja ta umożliwia modyfikację sposobu wyświetlania danych liczbowych oraz daty i czasu,
  • Czcionka – pozwala ustalać zmianę wyglądu zawartości komórki (rodzaj czcionki, styl <krój>, rozmiar, efekty, kolor),
  • Wyrównanie – umożliwia sterowanie danymi znajdującymi się w komórce (wyrównanie, zawijanie oraz orientacja tekstu, scalanie komórek).

Dostępność opcji do formatowania komórek różni się w zależności od aplikacji.

W Microsoft Excel trzy powyższe sekcje znajdują się na pasku narzędzi. Zostały one logicznie pogrupowane i rozmieszczone. Opcje najczęściej wykorzystywane dostępne są bezpośrednio z poziomu wstążki. Klikając na strzałkę Układ danych (w prawym dolnym rogu sekcji), przenosimy się do okna zawierającego wszystkie dostępne opcje, odpowiednie dla danej sekcji.

Okno opcji formatowania komórek wywołujemy, klikając prawym klawiszem myszy na danej komórce i wybierając z menu kontekstowego opcję: Formatuj komórki…

Pierwsza z dostępnych zakładek to: Liczby.

Układ danych

Kategorie dostępne z lewej strony pozwalają określić sposób wyświetlania w komórkach danych liczbowych. Wyboru kategorii dokonuje się intuicyjnie, zgodnie z opisami zawartymi na poszczególnych kartach. Dostępne są następujące kategorie, odmiennie nazwane w zależności od użytkowanej aplikacji:

Microsoft Excel

OpenOffice Calc

Opis

Ogólne

format domyślny; brak konkretnego formatu liczbowego

Liczbowe

Numer

pozwala określić liczbę miejsc dziesiętnych, sposób prezentowania liczb ujemnych oraz załączyć separator dla liczb tysięcznych

Walutowe

Waluta

umożliwia dodanie symbolu waluty do wartości liczbowej

Księgowe

tak jak poprzednia kategoria umożliwia dodaniesymbolu waluty; ponadto symbol waluty oraz przecinek dziesiętny zostają wyrównane w kolumnie

Data

Data

umożliwia wyświetlanie liczb w postaci daty w różnychformatach

Czas

Czas

umożliwia wyświetlanie liczb w postaci czasu w różnych formatach

Procentowe

Procent

wartość komórki zostaje pomnożona przez 100 i wyświetlonaz symbolem procentu

Ułamkowe

Ułamek

wyświetla liczy jako ułamek zwykły

Naukowe

Naukowy

wartość komórki wyświetlana jest w formaciewykładniczym (E+n)

Tekstowe

Tekst

wartość komórki traktowana jest jako tekst

Specjalne

gotowy format pozwala na prezentację numeru NIP, PESEL, telefonu i kodu pocztowego

Niestandardowe

Zdefiniowane przez użytkownika

pozwala na budowanie własnych formatów dla danych liczbowych

Wartość logiczna

typ logiczny, zwracający wartość prawda lub fałsz w zależności od zawartości komórki

Poniżej przedstawiamy pozostałe zakładki, które są wykorzystywane do prezentacji danych w komórkach i zmiany wyglądu komórek:

  • zakładka: Wyrównanie:

Układ danych

  • zakładka: Czcionka:

Układ danych

  • zakładka: Obramowanie:

Układ danych

  • zakładka: Wypełnienie:

Układ danych

  • zakładka: Ochrona:

Układ danych

Przykład formatowania komórek zamieszczamy w dalszej części lekcji.

Analogicznie sytuacja wygląda w programie OpenOffice Calc. Formatowanie komórek zostało tutaj nazwane: Atrybuty komórek. Najważniejsze opcje dostępne są na pasku narzędzi. Zostały one pogrupowane i oddzielone od siebie pionową kreską. Dostęp do wszystkich opcji formatowania możliwy jest za pośrednictwem menu głównego programu (pozycja: Format, i dalej: Komórki…) lub przy pomocy menu kontekstowego (klikając prawym klawiszem myszy na komórce) po wybraniu opcji: Formatuj komórki…

W zakładce: Liczby dostępnych jest 9 kategorii, a nie 12 jak w przypadku Microsoft Office Excel. Zostały one wcześniej porównane w tabeli (w części dotyczącej formatowania komórek w Excelu).

Po wybraniu pożądanej kategorii dostępne są oczywiście dodatkowe opcje charakterystyczne dla każdej z nich.

Wszystkie opcje formatowania zawarte są w 7 zakładkach i są zbliżone do tych z programu Excel.

Układ danych

Najmniej zaawansowany jest arkusz kalkulacyjny oferowany przez firmę Google. Opcje dostępne z menu głównego programu ograniczają się do zawartości komórki: formatu prezentowania wartości liczbowych (Liczba), kroju i rozmiaru czcionki (Czcionka, Rozmiar czcionki) oraz stylu jej wyświetlania (Pogrubienie, Kursywa, Podkreślenie, Przekreślenie).

Układ danych

Opcje te dostępne są również na pasku narzędzi. Dodatkowo znaleźć na nim można opcje do formatowania samej komórki – jej wypełnienia, obramowania, scalania.

Na poniższym zrzucie widać dokument w wersji roboczej. Brak jest w nim formatowania, niektóre dane wyświetlane są w sposób, który uniemożliwia ich odczytanie i analizę. Naszym celem jest poprawne ich zaprezentowanie oraz nadanie bardziej przyjaznego wyglądu.

Ponadto w trakcie prezentacji przykładu zostaną pokazane inne funkcje, wykorzystywane w pracy z programem.

Układ danych


Uwaga!

Zaznaczanie obszarów arkusza

Zanim przystąpimy do formatowania tabeli, omówmy podstawową umiejętność w arkuszach kalkulacyjnych, jaką jest zaznaczanie określonych obszarów arkusza. Aby tego dokonać, klikamy w dowolną komórkę i trzymając przyciśnięty lewy klawisz myszy, przeciągamy kursor na obszar, który chcemy zaznaczyć.

Przykładowo, aby zaznaczyć całą powyższą tabelę, klikamy w pole A2 i przeciągamy mysz do pola G12 – tabela podświetli się na szaro. Teraz można przystąpić do jej formatowania.

Przy większych tabelach przyda się umiejętność posługiwania się skrótami klawiszowymi. Aby przy ich pomocy zaznaczyć np. obszar od komórki A4 do G12, przechodzimy do pola A4 (klikając w nie lewym klawiszem myszy lub przemieszczając się po arkuszu przy pomocy strzałek na klawiaturze). Następnie przytrzymujemy wciśnięte klawisze Shift oraz Ctrl i wybieramy na klawiaturze strzałki: najpierw prawą, a potem dolną (lub odwrotnie: najpierw dolną, potem prawą). Excel automatycznie wykryje krawędź tabeli i zaznaczy obszar miedzy komórkami A4 i G12.

Jeżeli wciśniemy klawisze Ctrl + Shift i tylko dolną strzałkę, zaznaczona zostanie kolumna tabeli od komórki A4 do A12. Kombinacja z prawą strzałką zaznaczy pierwszy wiersz tabeli.

Identycznie wygląda to w programie Calc oraz w arkuszach Google.


W powyższym arkuszu zastosowano następujące operacje (skutki wszystkich opisanych niżej operacji przedstawia arkusz na s. 21):

Scalanie komórek

Dla komórek A2, C3 oraz E3 została zastosowana funkcja z zakładki Wyrównanie: Scal i wyśrodkuj. Dzięki temu tekst widoczny jest w całości i odpowiednio opisuje całą tabelę oraz kolumny danych.

Wypełnianie i obramowanie komórek

Odpowiednio dla uzyskania pożądanego efektu za pomocą zakładek Czcionka, Obramowanie, Wypełnienie komórkom nadano odpowiedni styl.

Część tekstu (nagłówki tabelki oraz jej tytuł) została pogrubiona (można tutaj zastosować skrót klawiaturowy Ctrl + B) oraz odpowiednio obramowana.

Szerokość kolumn

Dane w kolumnie C – czarne – nie mieszczą się, przez co pokazywane są jako ciąg znaków, np. 1E+05. Sytuację naprawia dwukrotne kliknięcie (lewym klawiszem myszy) w prawą linię nagłówka kolumny C. Excel automatycznie dopasuje szerokość kolumny. Ponadto szerokość kolumn została zmodyfikowana ręcznie poprzez przeciągnięcie odpowiednio krawędzi kolumn.

Szerokość wierszy

Pomiędzy wiersze 2 i 3 został dodany wiersz dodatkowy stanowiący odstęp. W tym celu klikamy prawym klawiszem myszy na nagłówek trzeciego wiersza i wybieramy opcję: Wstaw. Program dołożył dodatkowy wiersz, na nowo wszystkie numerując. Dla wybranych wierszy (po ich zaznaczeniu) została wybrana opcja: Wysokość wiersza…, która pozwala na dobranie pożądanej wysokości.

Dostępne są następujące metody zaznaczania wierszy lub kolumn:

  • przytrzymując lewy klawisz myszy, kolejno zaznaczamy odpowiednie wiersze/kolumny w miejscu ich nagłówków,
  • zaznaczamy wiersz/kolumnę, następnie z klawiszem Shift zaznaczamy ostatni wiersz/kolumnę – zostaną one zaznaczone wraz ze wszystkimi pomiędzy nimi,
  • wybrane wiersze/kolumny można zaznaczać lewym klawiszem myszy przy jednoczesnym trzymaniu wciśniętego klawisza Ctrl na klawiaturze.

Zawijanie tekstu

Dla komórek B5 oraz G5 została wybrana opcja: Zawijaj tekst. Dzięki temu nie było potrzeby nadmiarowego rozciągnięcia kolumny względem pozostałych danych, które się w niej znaj-dują.

Format wyświetlanych danych

Z zakładki: Liczby dla kolumny B został wybrany typ danych: Data w formacie dd-mm-rrrr.

Dla kolumny G ustalono typ danych liczbowych jako procentowe, z dwoma miejscami po przecinku. Czcionce nadano również odpowiedni rozmiar.

Wyrównanie danych w komórkach

Dane liczbowe oraz tekstowe zostały wyrównane (wyśrodkowane) w pionie oraz poziomie.

Ostatecznie uzyskano następujący efekt:

Układ danych

Zastosowane operacje mogą zostać powielone w programie OpenOffice Calc. Niektóre z funkcji, które pozwalają uzyskać zamierzony efekt, zostały inaczej nazwane. Przykładowo, opcja zawijania tekstu tutaj nosi nazwę: Podział wiersza w sekcji: Wyrównanie. Ich znalezienie nie jest jednak skomplikowane. W większości przypadków znajdują się w tych samych miejscach do jakich przyzwyczaił nas Excel.

Sformatowany arkusz w programie Calc został zaprezentowany poniżej:

Układ danych

Arkusze Google, pomimo że uboższe w opcje formatowania, również pozwalają uzyskać pożądany rezultat:

Układ danych

Formatowanie warunkowe

Jeżeli pracujemy na dużych zbiorach danych, często pojawia się potrzeba, aby wybrane z nich zaprezentować w sposób odmienny niż pozostałe. Formatowanie warunkowe pozwala ustalić dla komórek kryteria, których spełnienie spowoduje zastosowanie odpowiedniego formatowania.

Dzięki temu aplikacja samodzielnie wyróżni – zgodnie z oczekiwaniami użytkownika – dane, które w pewnych okolicznościach najbardziej go interesują. W ten sposób możemy np. wyróżnić z zestawienia kontrahentów tych, dla których upłynął termin płatności za fakturę, lub wyszczególnić trzech najbardziej wartościowych kontrahentów dla firmy (składających największe zamówienia).

W zależności od stosowanej aplikacji dostępność opcji dla formatowania warunkowego jest różna.

Arkusze kalkulacyjne firmy Microsoft oferują najbardziej rozbudowaną opcję formatowania warunkowego. Wywołać je można przy pomocy paska narzędzi (wstążki) w sekcji: Style.

Do dyspozycji mamy pięć grup reguł oraz możliwość budowania własnych. Każda z grup zawiera kilka zdefiniowanych reguł formatowania.

  • Reguły wyróżniania komórek:

Układ danych

  • Większe niż – wyróżnia komórki przekraczające określoną wartość;
  • Mniejsze niż – wyróżnia komórki których wartość jest mniejsza niż zadana;
  • Między – wyróżnia komórki, których zawartość zawiera się w określonym przedziale;
  • Równe – formatuje komórkę (-ki), których zawartość jest równa określonej wartości lub ciągowi znaków;
  • Tekst zawierający – formatuje komórki zawierające określony tekst;
  • Data występująca – wyróżni komórki, które zawierają określoną datę;
  • Duplikujące się wartości – wyróżnia komórki, które zawierają zduplikowane lub unikalne dane;
  • Reguły pierwszych/ostatnich:
  • 10 pierwszych elementów – z zadanego zakresu danych sformatowana zostanie zadana liczba komórek zawierających najwyższe wartości;
  • Pierwsze 10% – reguła działa analogicznie jak poprzednia, przy czym liczba komórek, która podlega formatowaniu określana jest procentowo;
  • 10 ostatnich elementów – z zadanego zakresu danych, sformatowana zostanie zadana liczba komórek zawierających najniższe wartości;
  • Ostatnie 10% – reguła działa tak jak poprzednia, przy czym liczba komórek podlegająca formatowaniu określana jest procentowo;
  • Powyżej średniej – reguła formatuje komórki zawierające wartości większe niż średnia wyliczona dla zaznaczonego zakresu danych;
  • Poniżej średniej – reguła formatuje komórki zawierające wartości niższe niż średnia wyliczona dla zaznaczonego zakresu danych;
  • Paski danych – reguła wstawia do każdej z komórek, w której znajduje się wartość liczbowa, kolorowy pasek; jego długość zależy od rzędu wielkości liczby w komórce – im większy, tym dłuższy pasek;
  • Skale kolorów – w ramach zaznaczonego zakresu danych komórki liczbowe są odpowiednio kolorowane dwu- lub trzykolorowym wypełnieniem (gradientem);
  • Zestawy ikon – program do komórek wstawia wybrany zestaw ikon, dobierając je odpowiednio do wielkości zawartych w nich liczb.

Excel umożliwia również budowanie własnych reguł na podstawie typów reguł, które zostały opisane powyżej. Opcja: Nowa reguła pozwala użytkownikowi skonfigurować jej działanie dokładnie tak, jak tego oczekuje. Jest to możliwe, dzięki indywidualnej konfiguracji parametrów, których wybór w zdefiniowanych typach reguł był ograniczony.

Układ danych

Utworzone reguły w skoroszycie można kontrolować przy pomocy opcji: Zarządzaj regułami. Przy rozbudowanych arkuszach funkcja ta jest bardzo pomocna i niejednokrotnie stanowi jedyny sposób na odszukanie reguł już zastosowanych w dokumencie.

Utworzone reguły można usunąć z zaznaczonego obszaru lub całego arkusza. Służy do tego opcja: Wyczyść reguły.

Dla zaznaczonego obszaru w arkuszu opcję do formatowania warunkowego wywołujemy z menu: Format Formatowanie warunkowe,

Program wyświetla okno dialogowe umożliwiające zdefiniowanie dla każdej komórki do 3 warunków, które muszą zostać spełnione, aby zaznaczona komórka otrzymała określony format.

Warunek może dotyczyć wartości liczbowej zawartej w komórce lub wyniku formuły.

Aby formatowanie zadziałało, musi zostać spełnione kryterium dla wartości komórki, która może być: równa, mniejsza niż, większa niż, mniejsza lub równa, większa lub równa, nierówna, między, nie między – względem zadanego parametru.

Po spełnieniu ustalonego warunku komórce nadany zostaje określony styl. Można wybrać spośród zdefiniowanych stylów albo utworzyć nowy, opierający się na tych samych opcjach, które wykorzystywane są do formatowania komórek.

Układ danych

Formatowanie warunkowe dla zaznaczonego obszaru komórek dostępne jest w menu głównym: FormatujFormatowanie warunkowe…

Należy wybrać, czy do zastosowania reguły potrzebny jest jeden kolor, czy skala kolorów. Aby zastosować więcej reguł należy wykorzystać opcję: Dodaj następną regułę.

Należy ustawić 4 parametry:

  • założenie do warunku, które może dotyczyć: tekstu, daty lub zawartości komórki,
  • tekst, wartość liczbowa lub zakres, które będą porównywane z kryterium,
  • wygląd tekstu (styl formatowania dla czcionki), który spełnia zadane kryterium,
  • wygląd komórki (styl formatowania dla komórki), która spełnia zadane kryterium.

Dla zaznaczonego obszaru maksymalnie można ustawić 10 warunków:

Układ danychUkład danych

W poniższym przykładzie zaprezentujemy działanie formatowania warunkowego w programie Microsoft Excel. Stosowanie funkcji formatowania warunkowego w pozostałych dwóch aplikacjach (Calc i Google) wymaga w wielu przypadkach dopasowania się do ich mniej zaawansowanych możliwości.

W zestawieniu wydruków chcemy wyróżnić żółtym tłem i czerwonym tekstem komórkę, w której zmiana procentowa w liczbie wydruków jest większa niż 20%. Ponadto czerwoną czcionką wskażemy datę spisania licznika w ostatnim miesiącu.

Zaznaczamy kolumnę z danymi zmiana procentowa (tabela znajduje się na s. 29). Wybieramy opcję: Nowa reguła, następnie odpowiedni typ i wprowadzamy warunek do reguły. Przyciskiem: Formatuj… ustalamy, w jaki sposób ma zostać sformatowana komórka spełniająca warunek:

Układ danych

W zakładce: Czcionka ustalamy kolor użytej czcionki i pogrubienie:

Układ danych

W zakładce: Wypełnienie wskazujemy kolor komórki spełniającej warunek:

Układ danych

Następnie zaznaczamy kolumnę, w której znajdują się dane dotyczące daty spisania licznika. Z gotowych reguł wyróżniania komórek wybieramy opcję: Data występująca… i określamy dla niej parametr:

Układ danych

Układ danych

W ten sposób uzyskujemy oczekiwany efekt końcowy. Zmiana wartości w arkuszu będzie na bieżąco uwzględniana i będzie wpływała na odpowiednie oznaczanie komórek zgodnie z ustalonymi kryteriami:

Układ danych

Wypełnianie seriami danych

Jedna z najprostszych w zastosowaniu i najprzydatniejszych funkcji arkuszy kalkulacyjnych to wypełnianie seriami danych. Najłatwiej z niej korzystać przy pomocy uchwytu wypełnienia (w postaci kwadracika) znajdującego się w prawym dolnym rogu komórki.

W każdym z omawianych arkuszy (Excel, Calc i Google) wygląda on bardzo podobnie:

Układ danych

Wypełnijmy teraz określony obszar przykładowymi danymi. Wprowadzamy w komórce wartość liczbową, np. 125, klikamy lewym klawiszem myszy w uchwyt wypełnienia i przeciągamy go o kilka komórek w dół. Liczba 125 zostanie powielona:

Układ danych

Teraz wypełnimy arkusz danymi, przeskakując o określoną wartość. Wpiszmy w dowolną komórkę arkusza 125, komórkę niżej – 130, zaznaczmy obie komórki i przeciągnijmy dane uchwytem wypełniania. Uzyskamy ciąg arytmetyczny: 125, 130, 135, 140, 145 itd. Arkusze na podstawie wprowadzanych danych wykrywają wartość skoku i wypełniają kolejnymi wartościami następne komórki.

Wypełnianie seriami danych działa także w przypadku wartości tekstowych, dat, godzin i innych.

Przykłady wypełniania seriami danych:

Układ danych

Po przeciągnięciu uchwytu wypełnienia przy wypełnianiu serią danych w Excelu pojawia się dodatkowa ikonka opcji autowypełniania: Układ danych

Wypełniając arkusz np. dniami miesiąca, możemy przy pomocy przycisku autowypełniania wybrać opcję: Wypełnij dniami powszednimi:

Układ danych

Zaawansowanie opcje wypełniania są dostępne w Excelu na wstążce Narzędzia główneWypełnienieSeria danych:

Układ danych

Wypełnijmy teraz przykładową liczbę 985,25 serią danych. Wypełniamy kolumnę według wzrostu liniowego o wartość kroku 54,25 do wartości maksymalnej 1250.

Układ danych

Uzyskamy następujący efekt:

Układ danych


Ćwiczenie

Wypełnij kolumnę w arkuszu serią danych, począwszy od wartości 2500 do 10 000, wartość kroku wynosi 500.


Praca z danymi: sortowanie i filtrowanie

Jedną z najważniejszych funkcji arkuszy kalkulacyjnych jest możliwość sortowania oraz filtrowania danych. Sortowania danych w Excelu dokonamy, wybierając ze wstążki: Narzędzia główne opcję: Sortuj i filtruj:

Układ danych

Tutaj mamy możliwość prostego sortowania od wartości najmniejszych do największych i odwrotnie – opcje: Sortuj od A do Z oraz: Sortuj od Z do A. W ten sposób posortujemy wartości liczbowe (od największej do najmniejszej i odwrotnie), wartości tekstowe (od A do Z i odwrotnie), wartości logiczne: prawda i fałsz. Komórki niezawierające żadnych danych zostaną umieszczone na końcu tabeli.

Mamy też tutaj opcję: Sortowanie niestandardowe, która daje nam możliwość układania danych względem wybranych kolumn, a także niestandardowych sposobów układania danych, np. sortowanie po dniach tygodnia, nazwach miesięcy np.

Układ danych

Opcję sortowania znajdziemy również na wstążce Dane:

Układ danych

Posortujmy teraz przykładową tabelę zawierającą listę pracowników wraz z datą zatrudnienia, wynagrodzeniami brutto oraz netto.

Układ danych

Spróbujmy najpierw posortować tabelę alfabetycznie – według imion pracowników. Zaznaczmy cały obszar tabeli, zaczynając od lewej strony. Klikamy w przycisk sortowania i wybieramy opcję: Sortuj od A do Z. Tabela zostanie posortowana według imion pracowników. Excel automatycznie wykryje wiersz nagłówka i odseparuje go od reszty tabeli. Jeżeli zaznaczylibyśmy tabelę, zaczynając od prawej strony, zostanie ona posortowana według wysokości wynagrodzenia w ostatniej kolumnie: Wynagrodzenie netto.

Teraz posortujemy tabelę według kolumny B: Nazwisko pracownika. Zaznaczmy cały obszar tabeli (od dowolnej strony) i przy pomocy przycisku sortowania wybierzmy opcję: Sortowanie niestandardowe. Pojawi się okno sortowania – Excel wykrywa nagłówki kolumn i wyświetla je jako listę rozwijalną. Wybierzmy z listy: Nazwisko pracownika i kliknijmy: OK.

Układ danych

Tabela została posortowana według nazwiska. Analogicznie możemy posortować tabele w innych kolumnach.

Arkusze kalkulacyjne dają nam też możliwość sortowania tabeli według wielu kryteriów jednocześnie. Przykładowo, chcemy posortować pracowników alfabetycznie według imion. Chcemy także, żeby pracownicy sortowani według imion byli dodatkowo posortowani od najwyższych do najniższych zarobków brutto. W celu zaprezentowania takiego sortowania tabela została nieco zmodyfikowana – mamy teraz w niej kilka osób o tym samym imieniu:

Układ danych

Aby dokonać sortowania wielopoziomowego zaznaczmy obszar tabeli i w oknie sortowania niestandardowego wprowadźmy pierwszy poziom sortowania: Sortuj według: Imię pracownika, według wartości, od A do Z. Następnie klikamy w przycisk Dodaj kolejny poziom i sortujmy według wynagrodzenia brutto od największych do najmniejszych. Na koniec klikamy OK:

Układ danych

Tabela została posortowana według imion oraz według wielkości wynagrodzeń brutto:

Układ danych

W ten sposób w bardziej rozbudowanych tabelach możemy wykorzystywać więcej poziomów sortowania.


Ćwiczenie

W ramach ćwiczenia przygotuj omawianą tabelę (w poprzedniej wersji – bez powielanych imion) i posortuj ją według daty zatrudnienia oraz według wynagrodzenia brutto.


Sortowanie w programie OpenOffice Calc znajduje się w menu: Dane, opcja: Sortuj oraz na standardowym pasku narzędzi jako przyciski: Układ danych

Aby posortować wyżej omawianą tabelę w programie Calc wobec pierwszej kolumny zaznaczmy obszar całej tabeli i wciśnijmy przycisk A -> Z. Analogicznie zadziała to z ostatnią kolumną. Aby posortować tabelę np. według kolumny: Wynagrodzenie brutto, zaznaczmy obszar tabeli, kliknijmy w menu: Dane, dalej: Sortuj, w polu: Sortuj według wybierzmy z listy opcję: Wynagrodzenie brutto, kliknijmy OK.

Aby posortować tabelę według jednej ze środkowych kolumn, zaznaczmy jej obszar i z głównego menu: Dane wybierzmy opcję: Sortuj. W otwartym oknie sortowania możemy zdefiniować, względem której kolumny będzie odbywać się sortowanie. W tym miejscu możemy od razu wybrać sortowanie na kolejnych poziomach (maksymalnie do 3):

Układ danych

Sortowanie w arkuszach Google realizujemy z menu: Dane. Automatyczne sortowanie względem pierwszej lub ostatniej kolumny zrealizujemy przy pomocy opcji: Sortuj arkusz A -> Z według kolumny A. Przy pomocy opcji: Sortuj zakres… posortujemy tabelę względem kolumn wewnętrznych (środkowych).

Układ danych

Aby posortować naszą tabelę z pracownikami względem daty zatrudnienia wybieramy menu Dane, dalej: Sortuj zakres…, zaznaczamy opcję Dane mają wiersz nagłówka (arkusz Google nie wykrywa automatycznie nagłówków) opcja sortuj według wybieramy Datę zatrudnienia i klikamy w przycisk Sortuj:

Układ danych

Tabela zostanie posortowania według odpowiedniej kolumny.

Arkusz Google, podobnie jak wcześniej opisane, także posiada możliwość sortowania wielopoziomowego. Zaznaczmy ponownie obszar tabeli i wybierzmy opcję: Sortuj zakres… Aby dodać kolejne poziomy sortowania, kliknijmy w przycisk: + Dodaj kolejną kolumnę na potrzeby sortowania i wybierzmy kolejne kryterium sortowania. Przykładowo, sortujemy pracowników alfabetycznie według imion, a następnie malejąco według daty zatrudnienia:

Układ danych

Filtrowanie danych pozwala na ograniczenie wyświetlanych danych do tych, które spełniają określone przez nas kryteria. W Excelu filtrowanie dostępne jest, podobnie jak wyżej opisane sortowanie, na wstążce Narzędzia główne, przycisk: Sortuj i filtruj opcja: Filtruj oraz na wstążce: Dane, opcja: Filtruj.

Układ danych

Omówmy filtrowanie na przykładzie tabeli pracowników z poprzedniego podrozdziału.

Aby założyć filtr na tabelę, zaznaczamy cały jej obszar oraz z wstążki: Dane wybierzmy opcję: Filtruj. Tabela z założonym filtrem wygląda następująco:

Układ danych

Wyfiltrujmy teraz pracowników zatrudnionych po 1 stycznia 2005 r. Klikamy w trójkąt filtru nad kolumną: Data zatrudnienia:

Następnie wybieramy opcję Filtry dat -> Po…, jest po i z kalendarza wybieramy datę: 2005-01-01.

Układ danych

Otrzymaliśmy tabelę z pracownikami zatrudnionymi w określonym przedziale czasu. Możemy teraz dodać kolejny warunek – zatrudnieni po 1 stycznia 2005 r. i przed 1 stycznia 2009 r.:

Układ danych

Uzyskamy następujący rezultat:

Układ danych

Aby zdjąć filtr, klikamy w trójkąt nad kolumną z datą zatrudnienia, a następnie w przycisk: Wyczyść.

W identyczny sposób możemy filtrować pozostałe kolumny.

Istnieje też możliwość łączenia kliku filtrów, np. zatrudnieni po 1 stycznia 2005 r. oraz zarabiający powyżej 2500 zł brutto. Łącząc filtry, należy określić zależność między nimi. Może być to zależność typu I oraz Lub. Opcja I to iloczyn warunków, np. zatrudnieni po 1 stycznia 2005 r. i zarazem zarabiający powyżej 3000 zł brutto. Opcja Lub to suma warunków: wszyscy zatrudnieni po 1 stycznia 2005 r. oraz wszyscy zarabiający powyżej 3000 zł brutto.


Ćwiczenie

Wyfiltrujcie pracowników o wynagrodzeniu brutto powyżej 3000 zł, którzy zostali zatrudnieni po 20 lutego 2006 r.


Filtrowanie pozwala także na bardziej zaawansowanie opcje. Omówmy je na przykładzie.

Chcemy z powyższej tabeli wyfiltrować wszystkie kobiety. W kolumnie: Imię wybieramy Filtr tekstuFiltr niestandardowy, opcja: Równa się. Przyjmujemy, że w języku polskim wszystkie imiona żeńskie kończą się na literę „a”, a męskie na inną niż „a”. W tym miejscu wykorzystamy symbol *, który zastępuje dowolną wartość (symbol ? zastępuje pojedynczy znak). Aby wyodrębnić imiona żeńskie, musimy wpisać: *a. Tym sposobem wyświetlimy wszystkie imiona kończące się na tę literę „a”.

Ten sam efekt osiągniemy zamiast używania symbolu gwiazdki, wybierając opcję kończy się na.

Układ danych

Układ danych

A oto rezultat naszych działań:

Układ danych

W programie Calc filtrowanie znajduje się w menu: Dane -> Filtr:

Układ danych

Aby założyć automatyczny filtr na naszą tabelę, zaznaczamy jej obszar i wybieramy menu: Dane -> Filtr -> Autofiltr. By wyfiltrować pracowników zarabiających poniżej 2000 zł netto, zaznaczamy trójkąt filtrowania nad kolumną: Wynagrodzenie netto, wybieramy opcję: Standardowe. Następnie dodajemy warunek < i wartość 2000.

Układ danych

Uzyskamy następujący efekt:

Układ danych

Filtr wyłączymy, wybierając w odpowiedniej kolumnie trójkąt filtru i opcję: Wszystkie. Podobnie jak w Excelu filtry można łączyć przy pomocy operatorów I oraz Lub.

Arkusze Google także udostępniają opcję filtrowania danych: menu górne -> Dane -> Filtr oraz ikona lejka na pasku narzędzi:

Układ danych

Aby założyć filtr na naszą tabelę, zaznaczamy jej obszar i klikamy w ikonę filtru (ww. ikona lejka). Filtrowanie w arkuszach Google nie jest tak zaawansowane, jak we wcześniej opisanych programach. Pozwala ono jednak np. na filtrowanie danych poprzez wprowadzanie początku tekstu (lub wartości liczbowej) w pole filtru. Przykładowo, odznaczamy wszystkie wiersze przyciskiem: Wyczyść wszystkie wyniki i wpisujemy początek daty: 2000, czyli osoby zatrudnione w roku 2000 – zaznaczamy tych pracowników, klikając w zaznaczenie z lewej strony każdej z dat i wybieramy OK:

Układ danych

Tym samym wyfiltrowaliśmy pracowników zatrudnionych w 2000 roku.

Podsumowanie

Mamy nadzieję, że poruszone w dzisiejszej lekcji zagadnienia były zrozumiałe, a zastosowane przykłady – czytelne. Są to podstawy korzystania z arkuszy kalkulacyjnych i ich przyswojenie otworzy każdemu drogę do bezproblemowego i praktycznego wykorzystania arkuszy w dalszej pracy.

W kolejnej lekcji omówimy funkcje w arkuszach kalkulacyjnych. Przestawimy strukturę formuł i wyjaśnimy, jak wykorzystywać operatory. Napiszemy także o zagnieżdżaniu funkcji, kolejności wykonywania operacji oraz o adresowaniu względnym i bezwzględnym.

Marcin Ciura, Paweł Kulikowski

X