5.Podstawy posługiwania się programemExcel

5. Podstawy posługiwania się programem Excel

5.1. Przeznaczenie programu

Program Excel jest najpopularniejszym arkuszem kalkulacyjnym, czyli, najogólniej rzecz biorąc, programem służącym do obróbki (dużych) zestawów liczb. Przy jego pomocy można na przykład utworzyć kosztorys projektu, automatycznie przeliczyć ceny ze złotówek na dolary, sporządzić wykresy, a całość elegancko sformatować i wydrukować. Głównymi obszarami zastosowań tego programu są więc biuro i mała firma.

Podobnie jak Word, Excel posiada wiele wersji, np. Excel 97, Excel 2000 itd.

Niniejszy podręcznik opisuje nowoczesną wersję Excel 2002. Wszystkie kolejne wcielenia tego programu są do siebie dość podobne, a nowe elementy dodawane w kolejnych wersjach służą głównie ułatwieniu obsługi programu. Większość naprawdę użytecznych funkcji programu Excel 2002 dostępna była już w wersji Excel 97, więc nie należy się specjalnie martwić tym, że na rynku dostępna jest już wersja Excel 2003. Podobnie jak w przypadku edytora Word, zdecydowana większość użytkowników i tak wykorzystuje tylko niewielki ułamek podstawowych funkcji programu, dostępnych już w wersji Excel 97.

Program Excel – podobnie jak każdy z pozostałych programów pakietu MS Office – został tak zaprojektowany, by być atrakcyjnym zarówno dla użytkowników początkujących jak i bardzo zaawansowanych. Dlatego z jednej strony może być używany po prostu jak kalkulator, z drugiej zaś – jako wyrafinowane narzędzie analizy statystycznej danych, wymagające od użytkownika dobrej orientacji w wielu dziedzinach matematyki wyższej. Niniejszy kurs ogranicza się, oczywiście, do prezentacji umiejętności podstawowych i średnio zaawansowanych.

5.2. Wspólne cechy programów Word i Excel

Jednym z podstawowych powodów osiągnięcia tak wielkiej popularności przez programy pakietu MS Office (czyli Word, Excel, Access i PowerPoint) jest to, że są one do siebie bardzo podobne pod względem obsługi. Jeżeli ktoś dobrze opanuje jeden z nich, łatwo poradzi sobie z nauką każdego innego.

Powyższą tezę dobrze ilustruje przykład programów Word i Excel. W obu

dokładnie tak samo otwiera i zamyka się programy. Dokładnie tak samo zapisuje

( Ctrl+S ) i drukuje ( Ctrl+P ) pliki. Oba programy wykorzystują podobne elementy

sterowania programem: menu, paski narzędzi, paski przewijania, pasek stanu,

okienka zadań, menu kontekstowe, system pomocy kontekstowej, okna dialogowe i skróty klawiaturowe. Jeżeli jakąś czynność można wykonać w obu programach, odpowiadać jej będą te same skróty klawiaturowe, pozycje w menu, przyciski na pasku narzędziowym lub okna dialogowe (np. odszukiwanie tekstu – Ctrl+F , wstawianie zdjęcia – WstawObrazZ pliku , wytłuszczanie tekstu – przycisk , wstawianie obiektu – okno dialogowe Obiekt ). W obu programach dokładnie tak samo korzysta się z systemu pomocy i podręcznika użytkownika. Oba programy w identyczny sposób współpracują ze schowkiem Windows ( Ctrl+C , Ctrl+V , Ctrl+X ); co więcej za pośrednictwem schowka można bez najmniejszych problemów przenosić fragmenty dokumentów napisanych w jednym z tych programów do drugiego.

Tak liczne wspólne cechy obu programów nie tylko znacznie ułatwiają naukę Excela, ale także umożliwiają mi skrócenie jego opisu i skoncentrowanie się na tych właściwościach programu, które wiążą się z jego zasadniczym przeznaczeniem jako arkusza kalkulacyjnego.

5.3. Elementy okna programu

Typowy widok okna programu Excel przedstawia Rysunek 5.1. Jak widać, sprawia ono wrażenie niesłychanie przeładowanego różnymi ikonami, symbolami i okienkami. Widok ten z pewnością może być zniechęcający dla początkującego użytkownika. Na szczęście mamy już za sobą kurs obsługi edytora Word i Czytelnik zapewne rozpoznaje większość elementów wyświetlanych na ekranie.

Po pierwsze, w górnej części okna znajduje się znany już nam pasek tytułu.

W jego lewym rogu znajduje się przycisk menu systemowego ( ), obok którego wyświetlana jest nazwa programu. Natomiast po prawej stronie znajdują się także znane już nam trzy przyciski sterujące programu: ( minimalizuj ), ( maksymal- izuj ) i ( zamknij program ).

Pasek

stanu Okienko zadań „Nowy

skoroszyt”

Siatka komórek

paski narzędzi menu

główne

przyciski sterujące programu

przyciski sterujące skoroszytu pasek

formuły pasek tytułu

Rysunek 5.1. Główne elementy okna programu Excel.

Poniżej paska tytułu widzimy menu główne programu. Rzut oka pozwala nam stwierdzić, że ma ono dokładnie taki sam układ, jak w programie Word. Jedyna różnica polega na tym, że siódma pozycja menu w programie Excel nazywa się Dane , w edytorze Word – Tabela .

Pod paskiem menu rozpoznajemy dwa paski narzędziowe – standardowy oraz formatowanie . Około ¾ znajdujących się na nich przycisków znamy już z programu Word. Pozostałe omówię w dalszej części podręcznika.

Pod paskami narzędziowymi znajduje się pierwszy element specyficzny dla programu Excel – pasek formuły. Służy on do kontrolowania wpisów w komórkach arkusza oraz do szybkiego wybierania funkcji matematycznych. Jego dokładny opis zostanie przedstawiony w punkcie 5.6.1.

Po prawej stronie okna programu widzimy kolejny znajomy element – okienko zadań Nowy skoroszyt . Jedyną różnicą między nim a analogicznym oknem w programie Word jest to, że w Excelu otwiera się skoroszyty, podczas gdy Wordzie otwiera się dokumenty. Poza tym wszystkie jego funkcje w obu programach są praktycznie identyczne.

Na samym dole okna programu rozpoznajemy pasek stanu. W jego lewej części program wyświetla podpowiedzi dla użytkownika (np. „ Zaznacz obszar do- celowy i naciśnij ENTER lub wybierz Wklej ”), a w części prawej może wyświetlać informacje o bieżącym stanie klawiszy Num Lock , Scroll Lock , Insert i F8 .

Główną część okna programu wypełnia obszar roboczy programu (szare tło), na którym znajduje się oddzielne okno o nazwie Zeszyt1 wypełnione rysunkiem siatki składającej się z wielu komórek. Okno to jest podstawowym miejscem naszej pracy i zwie się zeszytem lub skoroszytem. Okno skoroszytu wyglądem przypomina każde inne okno systemu Windows, gdyż posiada zarówno pasek tytułu, jak i przyciski , i . Czym różnią się te przyciski od analogicznych elementów umieszczonych na pasku tytułowym głównego okna programu? Odpowiedź jest prosta: przyciski umieszczone na małym oknie minimalizują, maksymalizują bądź zamykają wyłącznie to okno, natomiast działanie przycisków znajdujących się na oknie głównym dotyczy całego programu. Całe to zamieszanie wynika stąd, że w obszarze roboczym programu Excel można umieścić obok siebie kilka skoroszytów;

indywidualne przyciski , i znacznie ułatwiają pracę z wieloma osobnymi skoroszytami.

Okno w postaci przedstawionej na rysunku 5.1 posiada bardzo nieekonomiczną postać – dużo wygodniej (i efektywniej!) byłoby wypełnić skoroszytem cały obszar roboczy programu. W tym celu klikamy przycisk na skoroszycie. Dodatkowo możemy powiększyć obszar roboczy, wyłączając okienko zadań. W efekcie okno programu przyjmie wygląd jak na Rys. 5.2.

Proszę zwrócić uwagę na to, że na Rys. 5.2 skoroszyt wypełnia cały dostępny

obszar roboczy. Co więcej, aby maksymalnie wykorzystać dostępną powierzchnię,

okno skoroszytu pozbawione zostało paska tytułowego. Nazwa skoroszytu

(tu: Zeszyt1 ) została przeniesiona na pasek tytułowy całego programu, natomiast

przyciski sterujące skoroszytu pojawiają się tuż pod przyciskami sterującymi całego

programu. Proszę koniecznie porównać położenie tych elementów na rysunkach 5.1

i 5.2. Takie rozmieszczenie przycisków , i w dwóch rzędach jest

charakterystyczne dla wielu innych programów Windows i warto zapamiętać jego znaczenie. Analogicznie przycisk menu systemowego skoroszytu ( ) został na Rys. 5.2 przesunięty na lewy margines standardowego paska narzędziowego.

Pozostałe elementy obramowania okna skoroszytu pozostały na swoich miejscach.

Należą do nich przede wszystkim paski przewijania, zakładki arkuszy i przyciski zakładek.

Aby zrozumieć przeznaczenie zakładek arkuszy, należy uświadomić sobie, że skoroszyty programu Excel, podobnie jak prawdziwe skoroszyty, mogą składać się z wielu stron zwanych arkuszami. Każdy arkusz posiada własną, unikatową nazwę.

Skoroszyt można „otworzyć” na dowolnym z jego arkuszy. W tym celu wystarczy kliknąć zakładkę arkusza o pożądanej nazwie (Rys. 5.2 przedstawia skoroszyt z trzema arkuszami o nazwach Arkusz1 , Arkusz2 i Arkusz3 ).

Z kolei przyciski zakładek służą do rozwiązania problemów, które mogą się pojawić w skoroszytach zawierających dużą liczbę arkuszy (np. 10), gdyż w tym wypadku może zabraknąć miejsca na wyświetlenie zakładek wszystkich arkuszy.

Przyciski zakładek ułatwiają nawigację wśród dużej liczby zakładek. Mają one następujące znaczenie:

 przycisk powoduje wyświetlenie zakładki pierwszego arkusza;

 przycisk powoduje wyświetlenie zakładki poprzedniego arkusza;

 przycisk powoduje wyświetlenie zakładki następnego arkusza;

 przycisk powoduje wyświetlenie zakładki ostatniego arkusza.

5.4. Siatka arkusza kalkulacyjnego

W programie Excel, podobnie jak i w każdym innym programie tego typu, każdy arkusz obliczeniowy składa się siatki mającej postać prostokątnej tablicy. Elementy tej tablicy zwie się komórkami, a całą tablicę – arkuszem kalkulacyjnym. Arkusz składa się z co najwyżej 65 536 wierszy ponumerowanych kolejnymi liczbami naturalnymi i z co najwyżej 256 kolumn oznaczonych literami alfabetu łacińskiego.

Pierwszych 26 kolumn oznaczonych jest kolejnymi literami alfabetu A,B,…, Z,

paski przewijania

zakładki arkuszy przyci

ski zakład ek

przyciski sterujące skoroszytu

nazwa skoroszytu

przycisk menu

systemowego arkusza

uchwyt zmiany obszaru paska przewijania

uchwy ty podzia łu arkusz a

Rysunek 5.2. Wygląd okna programu po maksymalizacji okna skoroszytu i

zamknięciu okienka zadań.

natomiast następne kolumny mają oznaczenia dwuliterowe: po Z występuje AA, po niej AB, AC,…, AZ, następnie BA, BB, BC, itd. aż do kolumny IV. Mimo iż Excel wyświetla każdy arkusz jako tabelę o dokładnie 65 536 wierszach i 256 kolumnach, nie musimy wykorzystywać całej tej (ogromnej!) powierzchni; co więcej, program zapisuje w pliku wyłącznie informacje o tych komórkach, w których znajdują się jakieś wpisy, dlatego nie należy martwić się na zapas, czy aby nasze arkusze nie będą zajmować na dysku zbyt wiele miejsca.

Komórki siatki identyfikuje się poprzez tzw. adresy, składające się z literowego identyfikatora kolumny i z liczby, określającej numer wiersza. Na przykład komórka znajdująca się w lewym górnym rogu dowolnego arkusza leży w pierwszej kolumnie (o identyfikatorze „A”) i pierwszym wierszu (o numerze 1), odpowiada więc jej adres A1. Pod nią znajduje się komórka o adresie A2, a na prawo od A2 leży B2 itd. (por. Rys. 5.3).

Do dowolnej komórki arkusza można dotrzeć albo poprzez wpisanie jej adresu w polu adresu komórki (jak na Rys. 5.3), albo poprzez użycie pasków przewijania, albo poprzez skróty klawiaturowe:

 klawisze , , ,  przenoszą o jedną komórkę w lewo, prawo, górę lub dół;

 Ctrl+  ( Ctrl+ , Ctrl+ , Ctrl+  ) przenosi do lewej (prawej, górnej, dolnej) krawędzi bieżącego obszaru danych;

 Home przenosi do pierwszej komórki w danym wierszu;

 Ctrl+Home przenosi do komórki A1;

 Page Down ( Page Up ) przenosi o jeden ekran w dół (w górę);

 Alt+Page Down przenosi o jeden ekran w prawo;

 Alt+Page Up przenosi o jeden ekran w lewo.

5.5. Zaznaczanie komórek

W programie Excel bardzo często zachodzi potrzeba posługiwania się całymi grupami czy zakresami komórek. Bardzo ważna jest więc umiejętność wybierania (czyli „zaznaczania”) grup komórek. Na szczęście czynność tę przeprowadza się praktycznie tak samo, jak zaznaczanie komórek tabeli w programie Word.

Aby zaznaczyć jedną komórkę, wystarczy ją kliknąć. Aby zaznaczyć kolumnę, wystarczy kliknąć jej identyfikator w nagłówku tabeli. Analogicznie, żeby zaznaczyć wiersz, wystarczy kliknąć jego numer wyświetlany przy lewej krawędzi arkusza. Z kolei aby zaznaczyć grupę komórek tworzących prostokąt, wystarczy kliknąć komórkę w jednym z jego wierzchołków i przeciągnąć mysz do wierzchołka

komórka aktywna bieżąca kolumna bieżą

cy wiers z

adres komórki aktywnej

Rysunek 5.3. Adresowanie komórek w arkuszu

kalkulacyjnym.

przeciwległego (Rys. 5.4). Można także zaznaczać kilka rozłącznych grup komórek.

W tym celu należy najpierw zaznaczyć pierwszą grupę (np. wiersz), po czym wcisnąć klawisz Ctrl i kontynuować zaznaczanie komórek. Zaznaczone komórki będą na ekranie wyróżniane niebieskim tłem. Nie dotyczy to jednej spośród zaznaczonych komórek, którą program wyróżnia białym tłem. Jest to omówiona w kolejnym punkcie komórka aktywna.

Prostokątne grupy komórek można także szybko zaznaczać klawiaturą.

Wystarczy przejść do jednego z narożników prostokąta, przycisnąć klawisz Shift i, nie puszczając go, przejść kursorem do przeciwległego narożnika. Tak więc aby zaznaczyć kolumnę liczb, ustawiamy kursor klawiatury na jej pierwszym elemencie i klikamy Ctrl+Shift+ .

Interesującym efektem ubocznym zaznaczenia wielu komórek jest to, że program wyświetla ich sumę na pasku stanu (Rys. 5.4).

5.6. Wprowadzanie danych

5.6.1. Komórka aktywna

W każdej chwili jedna komórka arkusza jest graficznie wyróżniona grubą obwódką (por. Rys. 5.3) – jest to tak zwana komórka aktywna, czyli komórka, w której mogą być wprowadzane dane. Jej adres jest dodatkowo wyświetlany w pierwszym polu na pasku formuły. Aby jeszcze bardziej ułatwić pracę z dużymi arkuszami, program podświetla na niebiesko etykietę kolumny i numer wiersza, w którym znajduje się komórka aktywna.

Aktywną można uczynić dowolną komórkę arkusza. W tym celu wystarczy:

 kliknąć ją myszką;

 lub przejść do niej, wykorzystując klawisze nawigacyjne (, , , );

 lub wpisać jej adres w pierwszym polu na pasku formuły.

Rysunek 5.4. Prostokątna grupa komórek zaznaczona przy pomocy myszy.

zaznaczone komórki

suma komórek komór

ka

aktyw

na

Po uaktywnieniu danej komórki można wpisać do niej dane wprost z klawiatury.

Zostaną one wyświetlone zarówno w komórce, jak i w polu formuły na pasku formuły (Rys. 5.5). Ma to szczególne znaczenie w przypadku długich definicji, które po prostu mogą nie zmieścić się w komórce. Aby zaakceptować wpis, wystarczy przycisnąć Enter , wybrać inną komórkę aktywną lub kliknąć przycisk na pasku formuły. Jeżeli niechcący wpis zaczniemy wprowadzać w niewłaściwej komórce, w każdej chwili możemy go anulować, przyciskając klawisz Esc lub klikając symbol na pasku formuły. Oczywiście każdy błędny wpis możemy też cofnąć kombinacją klawiszy Ctrl+Z lub Alt+BackSpace .

Aby dokonać edycji komórki posiadającej już jakąś wartość, należy kliknąć ją dwukrotnie, co wprowadza program w tryb edycji. Samej edycji można dokonać albo w polu formuły, albo bezpośrednio w komórce. Posługujemy się przy tym takimi samymi metodami, jak przy edycji tabel programu Word, a więc klawiszami

 i  (nawigacja), Delete (usuwanie znaku na prawo od kursora), BackSpace (usuwanie znaku na lewo od kursora), Home (przejście na początek wpisu) End (przejście na koniec wpisu), Ctrl+V (kopiowanie danych ze schowka Windows).

Aby usunąć wpisy z jednej lub wielu komórek na raz, wystarczy je zaznaczyć i przycisnąć Delete .

5.6.2. Podstawowe formaty danych: liczby i tekst

Excel rozróżnia dwa rodzaje danych: liczby i tekst. Do liczb zaliczają się m.in.:

 liczby całkowite (dodatnie lub ujemne), np. 2 , –45 , 12340 ;

 ułamki dziesiętne, np. 3,14 , –4,534 , 5,00 .

Jako separatora tysięcy i milionów w dużych liczb całkowitych można użyć znaku spacji. Na przykład liczbę 12340 można wpisać jako 12 340 , a 60000000 jako 60 000 000 . Zapis 123 40 jest jednak, oczywiście, błędny. Jako separatora tysięcy i milionów nie wolno używać innych znaków niż spacja (np. kropki).

Jako separatora części całkowitej i ułamkowej w liczbach dziesiętnych należy używać wyłącznie przecinka. Tak więc wpis 3,14 jest poprawny, a 3.14 jest błędny.

Ponadto w przypadku liczb bardzo dużych lub bardzo małych można wykorzystać notację naukową. Na przykład liczbę 60 000 000 można zapisać w postaci 610

, co w notacji naukowej zapisuje się jako 6E7. Analogicznie –0,0000001 to –110

7

, co w notacji naukowej przyjmuje postać –1E–7 . Zapis naukowy zawiera literę E ,

pasek formuły

1 2 3 4 5

Wpisywane dane

Rysunek 5.5. Rola paska formuły przy wpisywaniu danych. (1) pole adresu; (2) przycisk

„Anuluj wpis”; (3) Przycisk „Zatwierdź wpis”; (4) Przycisk wyboru funkcji;

(5) pole formuły.

którą czyta się „razy dziesięć do potęgi…”. Zapis 6E7 odczytuje się „6 razy dziesięć do potęgi siódmej”, a 1,5E–3 to „jeden i pół razy dziesięć do potęgi minus trzeciej”

(czyli 0,0015). Często wykładnik zapisywany jest w postaci liczby dwucyfrowej, tj.

6E07 zamiast 6E7 . Znaczenie obu zapisów jest oczywiście takie samo.

Do komórek Excela nie można wpisywać ułamków zwyczajnych, np. 2/3. Znak

„/” jest bowiem traktowany jako separator dat i wpis 2/3 może zostać przez program zinterpretowany jako „3 lutego” (por. punkt 5.6.3).

Jeżeli program nie może zinterpretować wpisu jako liczby, uważa go za tekst.

Istnieje bardzo prosty sposób na określenie, który wpis uważany jest za liczbę, a który za tekst. Liczby wyrównywane są w komórkach do prawego, a tekst – do lewego brzegu komórki. Takie automatyczne odróżnianie formatu liczbowego od tekstowego pozwala szybko wyłapać błędy popełnione przy wprowadzaniu danych.

Jednym z nich jest użycie kropki (zamiast przecinka) w ułamkach dziesiętnych.

Excel interpretuje zapis „2.12” jako dane tekstowe, a „2,12” jako liczbę.

Konsekwencje tego ilustruje Rysunek 5.6, który przedstawia kolumnę z liczbami 2,12, 2.12 i 0,08 oraz ich sumę. Ponieważ zawartość komórki B2 nie jest przez program rozpoznawana jako liczba, oblicza on sumę wszystkich liczb w komórkach B1, B2 i B3 jako B1 + B3 = 2,20, a nie B1 + B2 + B3 = 4,32. Na szczęście błąd ten możemy dostrzec od razu dzięki temu, że wpis w komórce B2 jest wyrównany do lewego brzegu komórki. Użycie kropki zamiast przecinka w ułamkach dziesiętnych jest bardzo częstym błędem, gdyż wiele innych programów jako separatora części dziesiętnej używa właśnie kropki (jest to standard w krajach anglosaskich).

Istnieje prosta możliwość wyłączenia automatycznego przypisywania liczbom formatu „liczbowy”. Wystarczy rozpocząć wpis znakiem apostrofu (‘), np. ‘2000.

Na wydruku taki wpis będzie wyglądał jak liczba 2000, jednak program będzie go traktował jak napis i pomijał w obliczeniach. Jako ciekawostkę można potraktować fakt, że przy standardowych ustawieniach programu jego moduł detekcji błędów będzie taki wpis traktował jako błąd i zasygnalizuje to poprzez wyświetlenie niewielkiego zielonego trójkącika w lewym górnym narożniku komórki.

5.6.3. Daty

Przy wpisywaniu dat wpierw należy podawać rok, następnie miesiąc, a na końcu dzień. Jako separatora można używać minusa lub znaku dzielenia. Datę 11 kwietnia 1977 r. można więc wpisać jako 1977/4/11 , 1977/04/11 , 1977-4-11 lub 1977- 04-11 . Jeżeli rok należy do bieżącego stulecia, wystarczy podać jego dwie ostatnie cyfry, np. 02/03/04 to 4 marca 2002. Przy wprowadzaniu dat z roku bieżącego można w ogóle pominąć rok, np. w roku 2005 wpis 4/20 odpowiada dacie 20

Rysunek 5.6. Przykład komórki (B2) z błędnie wpisanymi danymi

liczbowymi.

kwietnia 2005 r. Excel formatuje daty jak liczby – wyrównuje je do prawej krawędzi komórki.

Na szczęście raz wprowadzone daty można później wyświetlać zgodnie w dowolnym innym formacie, np. dzień-miesiąć-rok . Zostanie to opisane w rozdziale 6.

5.7. Elementarne formuły

5.7.1. Definiowanie formuł

Jak wspomniałem, Excel może nam służyć jako całkiem zaawansowany kalkulator.

Przypuśćmy, że chcemy obliczyć wartość iloczynu 46*38,30. Jeżeli w wybranej komórce arkusza wpiszemy po prostu 46*38,30 , program sprawdzi, że nie wpisaliśmy liczby (bo ta nie może zawierać w swym zapisie gwiazdki), więc potraktuje nasz wpis jak zwykły tekst. Jak zmusić go do wykonania obliczeń?

Odpowiedź jest prosta: musimy go poinformować, że komórka nie zawiera ani liczby, ani tekstu, lecz trzeci rodzaj danych – formułę. W tym celu całe wyrażenie poprzedzamy znakiem „ = ” (czyli zamiast 46*38,30 wpisujemy =46*38,30 ).

Znak „ = ” instruuje program, by traktował zawartość komórki jak formułę, czyli wzór matematyczny, wg którego ma być wyznaczana wartość komórki. Znak ten ma znaczenie wyłącznie podczas edycji komórki i nie pojawia się na wydruku.

Efekt zastosowania znaku „ = ” został zilustrowany na Rys. 5.7 przedstawiającym fragment arkusza kalkulacyjnego. W komórce A1 wpisano 46*38,30 , natomiast w komórce A2 zapisano =46*38,30 . Jak widać, zawartość komórki A1 nie została przetworzona, natomiast w komórce A2 wyświetlany jest wynik mnożenia liczby 46 przez 38,30. Zwróćmy uwagę, że definicja komórki A2 pojawia się w polu formuły, natomiast w komórce arkusza wyświetlany jest wynik obliczeń. Jest to uniwersalna zasada: w arkuszu wyświetla się wyłącznie wyniki obliczeń, natomiast definicję treści dowolnej komórki można przeczytać w okienku formuły po uaktywnieniu odpowiadającej jej komórki.

Ogólnie rzecz biorąc Excel przyjmuje, że formułą jest dowolny wpis zaczynający się od znaku „ = ”, „ + ” lub „ – ”. Najczęściej jednak stosuje się znak „ = ”.

5.7.2. Operatory

Operatory to nic innego jak symbole operacji matematycznych. Excel rozpoznaje następujące operatory arytmetyczne:

 * mnożenie

 / dzielenie

form wynik uła obliczeń adres

komó rki

znak

„=”

Rysunek 5.7. Definiowanie

prostych formuł.

 + dodawanie

 – odejmowanie lub zmiana znaku liczby, czyli negacja (np. –7 , –A1 )

 % procent (np. 20%*150 wyznacza 20 procent ze 150)

 ^ potęgowanie (np. 2^4 to 2*2*2*2 = 16);

 & łączenie tekstu (np. "Ziu" & "tek" daje w wyniku tekst Ziutek ) Działania arytmetyczne wykonywane są w następującej kolejności:

 najpierw oblicza się negacje i procenty

 potem oblicza się potęgi

 następnie wykonywane są mnożenia i dzielenia

 na końcu obliczane są sumy i różnice.

Zgodnie z powyższymi regułami, 2*–3 = –6, 2+2*2 = 2+4 = 6, 10 * 50% = 5. Aby wymusić niestandardową kolejność obliczeń, należy zastosować nawiasy. Na przykład (2+2)*2 = 8, a (10*50)% = 500%. We wzorach nie można opuszczać operatora mnożenia. Na przykład odpowiednikiem znanego ze szkoły zapisu 3(x+y) jest w Excelu 3*(x+y) . Wszelkie wzory matematyczne będą bardziej czytelne, jeżeli po obu stronach operatorów + i – umieści się pojedynczy odstęp.

Warto poświęcić nieco uwagi operatorowi % . Excel interpretuje go tak, jakby znaczył on „podzielić przez 100”. Dlatego wpis 50% * 10 („oblicz 50% z 10) Excel zinterpretuje jako 50/100*10 , co daje prawidłową wartość 5. Często zachodzi potrzeba dodawania lub odejmowania procentów, co prowadzi do pewnych komplikacji. Przypuśćmy, że mamy odjąć 20 procent od liczby 120. Jeżeli działanie matematyczne odpowiadające tej operacji zapiszemy w formie 120 – 20% , Excel zinterpretuje ten zapis jako 120 – 20/100 i poda absurdalny wynik 119,8. Co gorsza, wynik przedstawi w tzw. formacie procentowym jako 11980% (sic!).

Dlatego należy zapamiętać następującą regułę: Excel dobrze sobie radzi wyłącznie z mnożeniem (i dzieleniem) przez wartości procentowe. Dodawanie lub odejmowanie procentów należy zawsze zapisywać w formie mnożenia. Na przykład rozwiązanie powyższego problemu należy zapisać w postaci 120 * (1 – 20%) . Excel zinterpretuje to wyrażenie jako 120*(1–20/100) = 120*0.8 = 96.

5.7.3. Formuły zawierające adresy komórek

Przedstawiona w poprzednim punkcie metoda wykorzystywania arkusza Excela jak kalkulatora to dopiero przedsmak tego, co stanowi o rzeczywistej sile programu.

W rzeczywistości bowiem formuły mogą zawierać nie tylko gotowe liczby, ale i odwołania do innych komórek arkusza, a nawet komórek innych arkuszy tego samego skoroszytu!

Jako przykład rozpatrzmy arkusz uwidoczniony na Rys. 5.8 (a). Wartość

komórki D3 zdefiniowano tu poprzez formułę „ =B3*(1+C3) ”. Oznacza to, że w

komórce D3 ma być wyświetlana wartość iloczynu wartości komórki B3 przez sumę

liczby 1 i wartości komórki C3. Przypuśćmy teraz, że cena netto talerza wzrosła do

10,20 zł. Aby obliczyć nową cenę brutto, wystarczy wprowadzić nową cenę netto

w komórce B3 i odczytać wynik w D3. Program automatycznie przelicza bowiem

wartości wszystkich komórek, które mogłyby w jakikolwiek sposób zależeć od

zmienionej wartości komórki B3. Efekt ten ilustruje Rys. 5.8 (b).

5.8. Efektywna praca z formułami

Mimo iż powyższy przykład może robić pewne wrażenie, pozostaje wiele wątpliwości. Po pierwsze, formuły typu B3*(1+C3) są mało czytelne. Po drugie, podczas ich definiowania dość łatwo nieświadomie użyć złego adresu komórki. Po trzecie, jak na razie nie potrafimy efektywnie korygować własnych błędów – na przykład podstawowa stawka VAT wynosi 22%, a nie 21%, jak przyjęto na Rys.

5.8. Czy korekty wymagają wszystkie wpisy w kolumnie C? Po czwarte, w typowych zastosowaniach ma się do czynienia z tabelami o setkach lub tysiącach wierszy i samo wpisywanie formuł w kolumnie D arkuszy z Rys. 5.8 może być niezwykle czasochłonne, żmudne i podatne na błędy. Tym bardziej, że formuła w komórce D4 powinna brzmieć B4*(1+C4) , w D5 należy wpisać B5*(1+C5) itd.

Czyli wszystkie formuły w komórkach kolumny D muszą się od siebie różnić, choć jednocześnie są do siebie niezwykle podobne. Nikt na świecie nie zdefiniuje 1000 takich formuł bez popadnięcia w głęboką depresję! Na szczęście twórcy programu bardzo elegancko poradzili sobie ze wszystkimi opisanymi tu problemami.

5.8.1. Nadawanie komórkom dowolnych nazw

Jak już wiemy, Excel nadaje automatycznie każdej komórce nawę składającą się z litery kolumny i numeru wiersza. Są to nazwy proste, ale nieporęczne w użyciu.

Czyż nie lepiej byłoby używać nazw typu rabat , VAT , oprocentowanie ? Otóż jest to jak najbardziej możliwe! Aby nadać jakiejś komórce specjalną nazwę, np. VAT , należy ją zaznaczyć, po czym wybrać z menu polecenie WstawNazwaDefiniuj , co powoduje wyświetlenie okna dialogowego Definiuj nazwy (Rys. 5.9). Nową nazwę komórki wpisujemy w górnym polu tego okna. W okienku na dole możemy skontrolować (lub nawet zmodyfikować) pełny adres komórki, której przypisujemy

Rysunek 5.8. (a) Przykład formuły zawierającej odwołania do innych komórek arkusza.

(b) Ten sam arkusz po zmodyfikowaniu wartości komórki B3.

(a) (b)

Rysunek 5.9. Okno dialogowe „Definiuj nazwy”

służy do zarządzania niestandardowymi nazwami komórek.

wpisz nową nazwę lista nazw komórek

adres komórki

zaakceptuj zmiany i zamknij okno

zamknij okno

zaakceptuj definicję nazwy

usuń

nazwę

nową nazwę. Adres ten składa się z nazwy arkusza, na której znajduje się komórka, znaku wykrzyknika ( ! ), znaku $ , literowej etykiety kolumny, znaku $ i numeru wiersza, np. Arkusz1!$C$2 ; zapis ten zostanie wyjaśniony w dalszej części podręcznika. W dużym okienku pośrodku okna dialogowego znajduje się wykaz wszystkich zdefiniowanych dotychczas nazw. Posługując się nim, można uniknąć wielokrotnego definiowania tej samej nazwy w skoroszycie. Aby przypisać komórce nową nazwę, klikamy przycisk OK . Przycisk Dodaj ma podobne działanie, ale nie powoduje zamknięcia okna dialogowego. Aby usunąć nazwę, zaznaczamy ją na liście i klikamy Usuń .

Po pewnym czasie mamy prawo zapomnieć, do jakiej komórki odnosi się dana nazwa. Informację tę można jednak łatwo uzyskać za pośrednictwem pola adresu na pasku formuły (Rys. 5.10). Wystarczy w nim bowiem kliknąć symbol strzałki ( ) i z

listy, która się wyświetli, wybrać określoną nazwę. Excel automatycznie wybierze komórkę o tej nazwie (i w ten sposób jednocześnie wskaże nam jej położenie).

Nazwy komórek muszą zaczynać się literą i mogą składać się z liter, cyfr, kropek i znaków podkreślenia ( Shift+Minus ); w szczególności nie mogą zawierać odstępów. Nazwy nie mogą mieć postaci standardowych nazw Excela, np. A1 , BC132 . Excel nie odróżnia małych liter od dużych, dlatego vat , VAT i VaT to dla niego te same adresy.

Powyższe informacje możemy wykorzystać do poprawienia arkuszy z Rys. 5.8:

 Najpierw zaznaczamy komórkę C3 i wprowadzamy z klawiatury Ctrl+Shift+ , co powoduje zaznaczenie wszystkich wpisów „21%”.

 Następnie usuwamy te wpisy, przyciskając klawisz Delete . Powoduje to przejściowe kłopoty, gdyż brak wpisu w komórce jest przez Excel traktowany (w formułach matematycznych) jak wpis o wartości 0. Dlatego przez chwilę arkusz zawiera wartości obliczone dla zerowej stawki VAT.

 Przechodzimy do C2 i w opisany powyżej sposób nadajemy jej nazwę VAT .

 Komórce C2 nadajemy wartość 22%. Od tej pory stawka VAT wynosi 22%.

 Przechodzimy do D3 i dokonujemy edycji zapisanej w niej formuły: zamiast

=B3*(1+C3) wpisujemy =B3*(1+VAT) . Ilustruje to Rys. 5.11.

Rysunek 5.11. Przykład formuły wykorzystującej nazwaną komórkę arkusza.

komórka o nazwie VAT

formuła wykorzystująca komórkę VAT

Rysunek 5.10. Odszukiwanie komórek o nazwach zdefiniowanych przez użytkownika.

tu kliknij i stąd wybierz

nazwę

ramka wskaże

położenie komórki

5.8.2. Bezpieczne definiowanie formuł

Nadszedł czas, by zmierzyć się z drugim problemem: jak zredukować prawdopodobieństwo wpisania w formule błędnego adresu? Przecież one wszystkie są do siebie tak bardzo podobne!

Excel oferuje bardzo wygodne rozwiązanie: zamiast wpisywać adresy komórek, możemy wskazywać myszką komórki, a Excel sam odczyta i umieści w formule ich adresy. Prześledźmy to na prostym przykładzie: spróbujemy jeszcze raz wpisać w komórce D3 formułę =B3*(1+VAT) . Oczywiście najpierw całkowicie usuwamy jej zawartość (zaznaczenie + klawisz Delete ) po czym:

 Zaznaczamy komórkę D3.

 Wprowadzamy z klawiatury znak „ = ”. Znak ten pojawi się w komórce i w okienku formuły. Ponadto na pasku stanu (w lewym narożniku) pojawi się komunikat Edycja – w ten sposób Excel informuje nas, że weszliśmy w tryb edycji.

 Klikamy komórkę B2. Program wyświetli wokół niej „ruchomą” ramkę i wpisze jej adres w polu formuły. „Ruchomość” ramki jest sygnałem, że nasz wybór nie jest jeszcze ostateczny. W każdej chwili możemy się rozmyślić i wybrać inną komórkę – kliknięciem myszy lub klawiszami nawigacyjnymi na klawiaturze.

 Z klawiatury wprowadzamy znak mnożenia (*). W tym momencie wokół pola D3 znika ruchoma ramka, pojawia się zaś kolorowe obramowanie. W ten sposób program sygnalizuje, że pole objęte kolorową ramką jest wykorzystywane w formule wyświetlanej na pasku formuły.

 Wpisujemy dalszą część wzoru: otwieramy nawias i wpisujemy „ 1 + ”. W tym momencie formuła powinna przyjąć postać „ =B3*(1 + ”.

 Klikamy komórkę C2 (której wcześniej nadaliśmy nazwę VAT ). Powoduje to wyświetlenie wokół niej znanej już nam „ruchomej” ramki.

 Zamykamy nawias. W tym momencie wokół pola C2 znika ruchoma ramka, pojawia się zaś kolorowe obramowanie. Stan arkusza obliczeniowego na tym etapie ilustruje Rys. 5.12.

 Sprawdzamy poprawność formuły i zatwierdzamy wpis klawiszem Enter . Warto zwrócić uwagę na to, że Excel otacza specjalnymi różnokolorowymi ramkami wszystkie komórki występujące w formule. Adresy komórek w formule również są wyświetlane różnymi kolorami, przy czym kolor adresu odpowiada kolorowi ramki. Znacznie ułatwia to orientację w formule – wystarczy jeden rzut oka, by sprawdzić, jakie komórki są przez nią używane. Ramki są dwojakiego rodzaju – niektóre są proste, inne mają w narożnikach kwadraciki. Proste ramki otaczają komórki z niestandardowymi nazwami (np. VAT ). Ramki z kwadracikami otaczają „zwyczajne” komórki arkusza i sygnalizują możliwość edycji formuły Rysunek 5.12. Przykładowy wygląd arkusza w trakcie edycji formuły w

komórce.

poprzez przesuwanie lub rozciąganie ramki myszką. Tak, to nie żarty – formuły Excela można modyfikować przy pomocy samej myszki!

5.8.3. Kopiowanie formuł

Pozostał nam jeszcze jeden problem do rozwiązania: czy można zdefiniować podobne, ale nie identyczne formuły w kolumnie składającej się z, powiedzmy, 1000 liczb? Odpowiedź jest pozytywna, a metodę tworzenia takich definicji ponownie przedstawię na przykładzie gotowego arkusza.

W arkuszu z Rys. 5.12 komórka D3 powinna zawierać formułę =B3*(1+VAT) , komórkę D4 należy zdefiniować jako =B4*(1+VAT) , itd. aż do komórki D1002, która powinna mieć definicję =B1002*(1+VAT) . W poprzednim punkcie zdefiniowaliśmy już stosowną formułę dla komórki D3. Teraz wystarczy ją

„inteligentnie” skopiować do pozostałych 999 komórek. W tym celu:

 Zaznaczamy komórkę D3 (np. kliknięciem).

 Z klawiatury wprowadzamy kombinację Ctrl+C . W tym momencie wokół komórki D3 pojawi się „ruchoma ramka”, która ma nam przypominać, że to właśnie stąd będziemy kopiować formułę. W tym momencie na pasku stanu pojawi się komunikat „ Zaznacz obszar docelowy i naciśnij Enter lub wybierz Wklej ”.

 Zaznaczamy zakres komórek, do których ma być skopiowana formuła. Można to zrobić myszką lub kombinacją klawiaturową Ctrl+Shift+ .

 Zgodnie z instrukcją wyświetlaną na pasku stanu, naciskamy klawisz Enter . I voilà! Wszystkie 1000 komórek ma wpisaną poprawną wartość formuły!

Zwróćmy uwagę, że do kopiowania formuły użyliśmy kombinacji Ctrl+C służącej w innych programach systemu Windows do kopiowania różnych obiektów (np. tekstu, zdjęć, plików) do schowka Windows. W programie Excel kombinacja Ctrl+C służy przede wszystkim do inteligentnego kopiowania formuł. Formułę wklejamy do zaznaczonej grupy komórek albo kombinacją Ctrl-V , albo po prostu poprzez przyciśniecie klawisza Enter .

Inteligentne kopiowanie formuły polega na tym, że Excel automatycznie przenumerowuje użyte w niej adresy. Przypuśćmy, że program ma skopiować formułę =B3*(1+VAT) z komórki D3 do D103. Program zauważa, że aby przejść z komórki D3 do D103 należy zwiększyć licznik wierszy tabeli o 100 i pozostać w tej samej kolumnie. Dlatego podczas kopiowania formuły zwiększy wszystkie numery w występujących w niej adresach o 100, natomiast oznaczenia literowe pozostawi bez zmian. Program nie zmieni nazw komórek zdefiniowanych przez użytkownika ani wartości liczbowych. W efekcie w komórce D103 pojawi się formuła

=B103*(1+VAT) . Gdybyśmy tę samą formułę kopiowali z D3 do E5, program dodałby w nazwie każdego standardowego adresu 2 (bo 5–3=2) do numeru rzędu i zamienił każdą literę adresu na następną w alfabecie (bo E następuje bezpośrednio po D). Dlatego w E5 pojawiłaby się formuła =C5*(1+VAT) .

5.8.4. Adresy względne i bezwzględne

Istnieje możliwość zablokowania modyfikowania adresów podczas kopiowania

formuł. Służy do tego specjalny zapis polegający na tym, że w adresie numer

wiersza lub literę kolumny (lub jedno i drugie) poprzedza się znakiem dolara. Oto przykłady takich adresów: $B3 , $B$3 , B$3 . Jeżeli na przykład w komórce D3 zamiast formuły =B3*(1+VAT) wpiszemy =B$3*(1+VAT) , to po skopiowaniu jej do komórki E4 przyjmie ona postać =C$3*(1+VAT) . Litera kolumny została zmodyfikowana, ale numer rzędu, dzięki zablokowaniu znakiem $ , pozostał taki sam.

Adresy niezawierające znaków $ nazywamy adresami względnymi. Adresy zawierające dwa znaki $ (przed nazwą kolumny i numerem wiersza) zwane są adresami bezwzględnymi. Adresy, w których występuje jeden znak $ to adresy mieszane. To, czy w formule występuje adres względny, mieszany czy bezwzględny, ma znaczenie wyłącznie przy jej kopiowaniu, nie ma zaś żadnego wpływu na jej wartość.

Znaczenie adresów względnych, bezwzględnych i mieszanych w formułach ilustruje Tabela 5.1, w której przedstawiono efekt kopiowania formuł zawierających adresy różnego typu z komórki D1 do E2 (czyli o jedną komórkę w prawo i jeden wiersz w dół).

Typ adresu Oryginalna formuła w D1 Formuła skopiowana do E2

względny ^{=A1 + 1 =B2 + 1}

mieszany ^{=$A1 + 1 =$A2 + 1}

mieszany ^{=A$1 + 1 =B$1 + 1}

bezwzględny ^{=$A$1 + 1 =$A$1 + 1}

Tabela 5.1. Efekt kopiowania formuł zawierających adresy względne, mieszane i bezwzględne.

5.9. Ćwiczenia

1. Otwórz program Excel. Poeksperymentuj z przyciskami i odnoszącymi się do skoroszytu. Następnie sprawdź działanie analogicznych przycisków na pasku tytułowym programu.

2. Wprowadź następującą tabelę:

Imię i nazwisko Data urodzenia Wynagrodzenie

Jacek Goździk 1964/11/15 2450zł

Agata Goździk 1968/12/06 3000zł

Zwróć uwagę, że część wpisów Excel automatycznie przekształci, np. wpis 1964/11/15 zostanie zamieniony na 1964-11-25 , a 3000zł na 3 000 zł . Jeżeli część wpisów nie mieści się w komórkach, rozszerz odpowiednie kolumny myszką (jak w tabelach programu Word).

3. Spójrz na wyrównanie danych w tabeli z punktu 6. Które dane są przez program traktowane jak liczby, a które jak teksty?

4. Wpisz (w dowolnych komórkach) liczby 3,14 (z przecinkiem dziesiętnym) oraz

3.14 (z kropką zamiast przecinka). Na podstawie wyrównania tych danych

w komórce oceń, która komórka faktycznie zawiera liczbę.

5. W różnych komórkach wpisz datę w następujących formatach: 3 czerwiec 2005 , 2005/06/03 , 05-6-3 , 05-06-03 , czerwiec 03 , 3 VI 2005 . Na podstawie wyrównania danych w komórkach określ, które komórki zawierają wpisy rozpoznawane przez program jako daty.

6. Popraw daty nieprawidłowo wprowadzone w poprzednim punkcie.

7. W kolejnych komórkach wpisz poniższe działania matematyczne poprzedzone znakiem równości

=2 + 3 =(2+2)*2 =2,5 * 2,5

=123,30 * 11%

=2+2/2 =2/3 =2^3 =14000 * 5,65%

8. Oblicz pierwiastek kwadratowy z 2 przy pomocy formuły =pierwiastek(2) . 9. Sprawdź poprawność formuł z punktów 2 i 3 (ustawiając kursor klawiatury

w kolejnych zapisanych komórkach i odczytując formułę na pasku formuły).

10. Zaznacz cały arkusz i usuń wszystkie dane ( Delete ).

11. Przejdź do arkusza Zeszyt2 i poczynając od komórki A1 wprowadź następującą tabelę (bez rysowania krawędzi):

kwota pożyczki odsetki suma 1 000 zł

2 000 zł 3 000 zł 4 000 zł 5 000 zł

─ W komórce B2 wprowadź z klawiatury formułę =A2*8,3% (i wprowadź Enter ).

─ Zaznacz B2 i przyciśnij Ctrl+C .

─ Zaznacz komórki B2:B6 i kliknij Enter .

─ Odczytaj wartości i formuły w kolejnych komórkach B2 , B3 ,…, B6 .

─ W analogiczny sposób oblicz wartości w kolumnie 3 jako sumy kwot z odpowiednich komórek w kolumnach 1 i 2 .

12. Upewnij się, że potrafisz zaznaczyć (kolejno):

─ komórkę A2 ;

─ kolumnę A ;

─ wiersze 2 i 3 ;

─ prostokąt z danymi (np. przejdź do A1 , przyciśnij kolejno Ctrl+Shift+

oraz Ctrl+Shift+) .

13. Usuń zawartość komórek B2:C6 ( Delete ) w powyższej tabeli i powtórz kroki z punktu 11, tym razem używając myszy do wprowadzenia adresu komórki A2 . 14. Zamień kwoty z pierwszej kolumnie tabeli z punktu 11 na 10 razy większe (np.

zamiast 1 000 zł wpisz 10 000zł itd.). Jaki ma to wpływ na pozostałe liczby w tabeli?

15. W komórce E1 wpisz słowo oprocentowanie , a w komórce E2 wpisz 8,3% . Następnie:

─ Usuń zawartość komórek B2:C6 .

─ W komórce B2 wprowadź formułę = A2 * E2

─ Skopiuj formułę z B2 do komórek B3:B6 .

─ Zastanów się, dlaczego w komórkach B3:B6 program wyświetla 0? W tym celu dokonaj inspekcji formuł w komórkach B3:B6 .

─ Popraw formułę w B2 na = A2 * $E$2

─ Ponownie skopiuj formułę z B2 do komórek B3:B6 . Czy teraz wyniki są satysfakcjonujące?

─ Wprowadź odpowiednią formułę do C2 i skopiuj ją do C3:C6 . 16. Ponownie usuń zawartość komórek B2:C6 .

─ Nadaj komórce E2 nazwę oprocentowanie ( WstawNazwaDefiniuj ).

─ W komórce B2 wprowadź formułę = A2 * oprocentowanie (do wprowadzenia adresów A2 i oprocentowanie posłuż się myszką).

─ Uzupełnij resztę tabeli jak w poprzednich zadaniach.

17. Jeszcze jedna wariacja na ten sam temat: w tabeli otrzymanej w poprzednim punkcie w komórce B2 wprowadź (bezsensowną) formułę =A2 * E1 .

─ Popraw tę formułę na = A2 * oprocentowanie używając wyłącznie myszki.

18.  Jeszcze jedna wariacja na ten sam temat, ale z wykorzystaniem dwóch arkuszy:

─ Usuń dane z komórek B2:C6 .

─ Przejdź do zakładki Arkusz3 .

─ Komórce A1 przypisz nazwę stopa ( WstawàNazwaàDefiniuj ).

─ Komórce A1 przypisz wartość 5% .

─ Wróć do arkusza Arkusz2 i w komórce B2 wpisz formułę = A2*stopa .

─ W znany już sposób uzupełnij wartości pozostałych komórek tabeli.

─ Kilka razy przejdź do arkusza Arkusz3 i zmień wartość komórki stopa (np.

na 100% lub 0% , lub 0,01 ), po czym wróć do arkusza Arkusz2 i zaobserwuj zmiany w wartościach tabeli.

19.  Utwórz tabliczkę mnożenia (wskazówka: zastosuj adresowanie mieszane!).