Excel 2003 PL. Ilustrowany przewodnik

Wydawnictwo Helionul. Chopina 644-100 Gliwicetel. (32)230-98-63e-mail: [email protected]

PRZYK£ADOWY ROZDZIA£PRZYK£ADOWY ROZDZIA£

IDZ DOIDZ DO

ZAMÓW DRUKOWANY KATALOGZAMÓW DRUKOWANY KATALOG

KATALOG KSI¥¯EKKATALOG KSI¥¯EK

TWÓJ KOSZYKTWÓJ KOSZYK

CENNIK I INFORMACJECENNIK I INFORMACJE

ZAMÓW INFORMACJEO NOWOŒCIACH

ZAMÓW INFORMACJEO NOWOŒCIACH

ZAMÓW CENNIKZAMÓW CENNIK

CZYTELNIACZYTELNIAFRAGMENTY KSI¥¯EK ONLINEFRAGMENTY KSI¥¯EK ONLINE

SPIS TREŒCISPIS TREŒCI

DODAJ DO KOSZYKADODAJ DO KOSZYKA

KATALOG ONLINEKATALOG ONLINE

Excel 2003 PL.Ilustrowany przewodnikAutor: Krzysztof Mas³owskiISBN: 83-7361-692-6Format: B5, stron: 216

Excel 2003 PL to kolejna wersja najpopularniejszego arkusza kalkulacyjnego — narzêdzia do przeprowadzania obliczeñ, tworzenia zestawieñ i graficznej prezentacji danych. Excel jest wykorzystywany niemal w ka¿dej firmie, a coraz czêœciej tak¿ew domach. Za jego pomoc¹ mo¿na przygotowaæ domowy bud¿et, zaplanowaæ wydatki, wykonaæ skomplikowane obliczenia i przedstawiæ ich wyniki w postaci wykresów lub tabel. Znajomoœæ Excela to obecnie jeden z podstawowych wymogów na rynku pracy — ka¿dy, kto ubiega siê o stanowisko zwi¹zane z prac¹ biurow¹, powinien znaæ jego mo¿liwoœci.

Ksi¹¿ka „Excel 2003 PL. Ilustrowany przewodnik” to idealna propozycja dla tych,którzy chc¹ poznaæ Excela. Przedstawia podstawowe zasady pracy z arkuszem kalkulacyjnym i narzêdzia, jakie ma do dyspozycji u¿ytkownik. Ka¿de zadanie przedstawione jest w postaci bogato ilustrowanego opisu, co dodatkowo u³atwia jego zrozumienie i wykonanie. Dziêki tej ksi¹¿ce nauczysz siê stosowaæ Excela w pracyi w domu, wykorzystuj¹c zarówno podstawowe, jak i bardziej zaawansowane mo¿liwoœci tej aplikacji.

• Wprowadzanie i zaznaczanie danych• Edycja arkuszy — dodawanie i usuwanie komórek, rzêdów i kolumn• Podstawowe operacje na arkuszach• Obliczenia — formu³y i funkcje• Formatowanie komórek i danych• Tworzenie wykresów• Drukowanie arkuszy• Podstawowe elementy analizy danych

Kalkulatory to ju¿ przesz³oœæ — dziœ korzysta siê z Excela

http://helion.pl/view/2547w



http://helion.pl/add/2547w/exce23




mailto:[email protected]

http://helion.pl/view/2547w/exce23.htm

5

Spis tre�ci Rozdzia³ 1. Preliminaria . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11

Poruszanie siê po arkuszu i arkuszach . . . . . . . . . . . . . . . . . . . . . 12Przechodzenie do s¹siednich komórek . . . . . . . . . . . . . . . . . 12Przechodzenie do wybranej komórki . . . . . . . . . . . . . . . . . . . 14Przechodzenie zale¿ne od otoczenia . . . . . . . . . . . . . . . . . . . 14Przechodzenie od arkusza do arkusza . . . . . . . . . . . . . . . . . . 16

Wprowadzanie danych do komórek i ich edycja . . . . . . . . . . . . . 17Wpisywanie czego� do komórki . . . . . . . . . . . . . . . . . . . . . . . 17Wpisywanie tekstu . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18Wpisywanie zwyk³ych liczb . . . . . . . . . . . . . . . . . . . . . . . . . . . 19Wpisywanie waluty . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20Wpisywanie procentów . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22Wpisywanie formu³ . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23Wpisywanie dat i czasu . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24Poprawianie zawarto�ci komórek . . . . . . . . . . . . . . . . . . . . . . 25

Zakresy . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27Typowe zakresy prostok¹tne . . . . . . . . . . . . . . . . . . . . . . . . . . 27Inne sposoby zaznaczania zakresów . . . . . . . . . . . . . . . . . . . 28Zaznaczanie zakresów niespójnych . . . . . . . . . . . . . . . . . . . . 29Zaznaczanie wierszy, kolumn i ca³ego arkusza . . . . . . . . . . . 29Czyszczenie komórek i zakresów . . . . . . . . . . . . . . . . . . . . . . 32

Rozdzia³ 2. Budowanie i edycja arkuszy . . . . . . . . . . . . . . . . . 34Wstawianie kolumn, wierszy i komórek . . . . . . . . . . . . . . . . . . . . . 35

Wstawianie wierszy . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35Wstawianie kolumn . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37Wstawianie komórek . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39

Usuwanie kolumn wierszy i komórek . . . . . . . . . . . . . . . . . . . . . . 41Usuwanie komórek . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41Usuwanie kolumn i wierszy . . . . . . . . . . . . . . . . . . . . . . . . . . 42Ukrywanie i odkrywanie kolumn i wierszy . . . . . . . . . . . . . . . . 44

Kopiowanie i przesuwanie . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47Kopiowanie przez schowek . . . . . . . . . . . . . . . . . . . . . . . . . . 47Kopiowanie przez przeci¹ganie mysz¹ . . . . . . . . . . . . . . . . . . 50Wielokrotne powielenie zakresu . . . . . . . . . . . . . . . . . . . . . . . 51

Excel 2003 PL. Ilustrowany przewodnik

6

Przesuwanie za pomoc¹ schowka . . . . . . . . . . . . . . . . . . . . . 54Przesuwanie przez przeci¹ganie mysz¹ . . . . . . . . . . . . . . . . . 56Klasyczna zamiana miejsc . . . . . . . . . . . . . . . . . . . . . . . . . . . 57Przesuwanie z odsuniêciem . . . . . . . . . . . . . . . . . . . . . . . . . . 58Kopiowanie do innych arkuszy lub dokumentów . . . . . . . . . . 59

Rozdzia³ 3. Proste operacje na arkuszach . . . . . . . . . . . . . . . . 61Kwartalna lista p³ac � nazywanie arkuszy i dzia³ania na kilku arkuszach . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62

Nadanie nazwy skoroszytowi . . . . . . . . . . . . . . . . . . . . . . . . 62Nadawanie nazw arkuszom . . . . . . . . . . . . . . . . . . . . . . . . . . 63Jednoczesne wype³nianie komórek w trzech arkuszach . . . . . 64Wstawianie wiersza od razu do dwóch arkuszy . . . . . . . . . . . 65

Rozliczanie delegacji � kopiowanie, dodawanie i usuwanie arkuszy . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66

Przygotowanie arkusza wzorcowego . . . . . . . . . . . . . . . . . . . 66Kopiowanie zawarto�ci arkusza . . . . . . . . . . . . . . . . . . . . . . . 66Usuwanie niepotrzebnego arkusza . . . . . . . . . . . . . . . . . . . . 67Kopiowanie arkusza . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68Kopiowanie lub przesuwanie arkusza do innego skoroszytu . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70Dodawanie nowego arkusza . . . . . . . . . . . . . . . . . . . . . . . . . 71Zmiana domy�lnej liczby arkuszy w skoroszycie . . . . . . . . . . . 72

Ogl¹danie arkuszy . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74Blokowanie okienek . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74Podzia³ okna arkusza na dwa niezale¿nie przewijane panele . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76Ukrywanie arkuszy . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77

Rozdzia³ 4. Obliczenia . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79Operatory u¿ywane w formu³ach i kolejno�æ ich dzia³ania . . . . . . 80Adresy wzglêdne, mieszane, bezwzglêdne i trójwymiarowe . . . . . 81

Zmiana adresów wzglêdnych przy kopiowaniu . . . . . . . . . . . 81Adresy mieszane i bezwzglêdne . . . . . . . . . . . . . . . . . . . . . . . 83Adresy trójwymiarowe . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84

Nazwy . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88Definiowanie nazw . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88Stosowanie nazw . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89

Spis tre�ci

7

Tworzenie nazw . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91Nazwy zakresów z adresowaniem wzglêdnym . . . . . . . . . . . 92

Funkcje . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94Wpisywanie funkcji do formu³y na przyk³adzie funkcji JE¯ELI() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94Sumy po�rednie . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96Zagnie¿d¿anie funkcji, czyli funkcja w funkcji . . . . . . . . . . . . . 98Funkcje podrêczne . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100Przyk³ad korzystania z systemu pomocy Excela . . . . . . . . . . 103

Jak to siê liczy lub dlaczego siê nie liczy? . . . . . . . . . . . . . . . . . . 106Dzielenie przez zero . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106Dzielenie przez tekst . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107Inspekcja formu³ . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108Szacowanie formu³ . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109Dwa popularne b³êdy: #NAZWA i b³¹d adresowania cyklicznego . . . . . . . . . . . . . . . . . . . . . . . . . . . 110Zmiana trybu wykonywania obliczeñ . . . . . . . . . . . . . . . . . . 111

Rozdzia³ 5. Formatowanie . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113Ozdobniki . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115

Zmiana kroju pisma (czcionki) i proste wyrównanie (wy�rodkowanie) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115Wyrównanie . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 117Scalanie komórek . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119Wy�wietlanie i drukowanie linii siatki . . . . . . . . . . . . . . . . . . 121Kre�lenie ró¿nych krawêdzi (obramowañ) w ró¿nych kolorach . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 122Kolory t³a i tekstu . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 124Ochrona komórek . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 126Malarz formatów . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 128Autoformatowanie . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129

Formatowanie zmieniaj¹ce znaczenie informacji . . . . . . . . . . . . 133Format tekstowy . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 133Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 135Czas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 138


8

Formatowanie warunkowe i niestandardowe . . . . . . . . . . . . . . . 140Formatowanie warunkowe . . . . . . . . . . . . . . . . . . . . . . . . . . 140Formatowanie warunkowe za pomoc¹ formatu niestandardowego . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 141Sumowanie wiêcej ni¿ 24 godzin . . . . . . . . . . . . . . . . . . . . . 143

Rozdzia³ 6. Graficzna prezentacja danych � wykresy . . . . . . 144Podstawowe informacje o wykresach i szybkie ich tworzenie . . 146

Szybkie tworzenie wykresu w oddzielnym arkuszu . . . . . . . . 146Szybkie tworzenie wykresu osadzonego

w arkuszu bie¿¹cym . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 147Podstawowe informacje o wykresie

i jego modyfikowaniu . . . . . . . . . . . . . . . . . . . . . . . . . . . . 149Sprzê¿enie danych z wykresem . . . . . . . . . . . . . . . . . . . . . . 150

Wykres domy�lny . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 152Zmiana typu wykresu domy�lnego . . . . . . . . . . . . . . . . . . . . 152Dodanie przycisku Wykres domy�lny . . . . . . . . . . . . . . . . . . 153Jak Excel domy�lnie wybiera serie i kategorie? . . . . . . . . . . . 154

Manipulowanie danymi i wykresami . . . . . . . . . . . . . . . . . . . . . . 155Prosta zmiana ci¹g³ego zakresu danych

� dodawanie i usuwanie serii i kategorii . . . . . . . . . . . . . 156Dodawanie serii lub kategorii

przez przeci¹ganie mysz¹ zakresu danych . . . . . . . . . . . 156Kopiowanie serii i kategorii do arkusza wykresu . . . . . . . . . . 157Usuwanie serii danych . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 159Zamiana wykresu osadzonego w arkuszu na arkusz

wykresu i odwrotnie . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 162Konsolidowanie danych na wykresie . . . . . . . . . . . . . . . . . . 163

Kreator wykresów . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 166Czasami szybkie tworzenie wykresów

nie prowadzi do celu . . . . . . . . . . . . . . . . . . . . . . . . . . . . 166Kreator wykresów � krok 1. z 4 . . . . . . . . . . . . . . . . . . . . . . 167Kreator wykresów � krok 2. z 4 . . . . . . . . . . . . . . . . . . . . . . 168Kreator wykresów � krok 3. z 4 . . . . . . . . . . . . . . . . . . . . . . 170Kreator wykresów � krok 4. z 4 . . . . . . . . . . . . . . . . . . . . . . 171Poprawianie wykresów za pomoc¹

Kreatora wykresów . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172

Spis tre�ci

9

Formatowanie wykresów . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 174Menu Format . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 174U¿ycie menu podrêcznego . . . . . . . . . . . . . . . . . . . . . . . . . . 175Formatowanie przez dwukrotne klikniêcie mysz¹ . . . . . . . . . 176

Ró¿ne typy wykresów . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 177Tworzenie wykresu ko³owego . . . . . . . . . . . . . . . . . . . . . . . . 177Porównanie serii danych na dwóch wykresach ko³owych . . . . 179Inne mo¿liwo�ci wykresów ko³owych . . . . . . . . . . . . . . . . . . 179Wykres pier�cieniowy . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 180Wykresy liniowy i XY (punktowy) . . . . . . . . . . . . . . . . . . . . . . 181Wykres radarowy . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 182Wykres b¹belkowy . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 182

Na zakoñczenie rozdzia³u o wykresach . . . . . . . . . . . . . . . . . . . 185

Rozdzia³ 7. Drukowanie . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 187Podgl¹d . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 188

Podgl¹d podzia³u stron . . . . . . . . . . . . . . . . . . . . . . . . . . . . 188Podgl¹d wydruku . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 191

Drukowanie . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 193Drukowanie jednym klikniêciem . . . . . . . . . . . . . . . . . . . . . . 193Drukowanie sterowane . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 194

Ustawienia strony . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 196Karty Strona i Marginesy . . . . . . . . . . . . . . . . . . . . . . . . . . . . 196Karta Nag³ówek/Stopka . . . . . . . . . . . . . . . . . . . . . . . . . . . . 197Karta Arkusz . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 198Karta Wykres . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 199

Rozdzia³ 8. Elementy analizy danych . . . . . . . . . . . . . . . . . . . 200Przyk³ad importowania danych i przystosowania ich do analizy . . . 201

Przyk³adowe importowanie danych z Eurostatu . . . . . . . . . . 201Przystosowanie �ci¹gniêtego arkusza do analizy danych . . . . . 203

Analizowanie danych . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 204Sortowanie . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 204Filtrowanie . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 205Sumy czê�ciowe i grupowanie rekordów . . . . . . . . . . . . . . . 207Tabele przestawne . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 209

Skorowidz . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 213

Bez możliwości wykonywania ob-liczeń Excel byłby tylko wielką

tabelą, lepszą od zwykłej, papierowejtylko pod tym względem, że łatwow niej coś „wytrzeć” i wpisać od no-wa. Obliczenia są wykonywane przezformuły wpisane do komórek,a w formułach można wykorzysty-wać wiele przydatnych funkcji,w których solidną bibliotekę zaopa-trzyli nas twórcy programu.

A więc obliczenia to formuły i funk-cje. Oto garść podstawowych infor-macji na ten temat. Stanowią one za-ledwie wierzchołek góry lodowej, aleod czegoś trzeba zacząć, aby spraw-nie posługiwać się formułami, nale-ży poznać:

l operatory i pierwszeństwo wyko-nywania działań,

l adresy względne, mieszane i bez-względne,

l funkcje podstawowe oraz specy-ficzne dla dziedziny, jaką się zaj-mujemy, np. inżynierskie, ekono-miczne itd.1

l śledzenie etapów obliczeń wyko-nywanych przez formuły, czyli in-spekcję formuł.

Tym właśnie zajmiemy się w tymrozdziale.Zacznijmy od przypo-mnienia, że:

l formuły rozpoczynają się od zna-ku =. Gdy jest on pierwszym zna-kiem wpisanym do komórki, ciągpozostałych będzie traktowany ja-ko treść formuły,

l formuły mogą, ale nie muszą, za-wierać funkcje.

Rozdzia³ 4.

Obliczenia

79

1 Ca³kiem dobry opis funkcji mo¿na znale�æ w dodatku A w przet³umaczonej przezemnie ksi¹¿ce Kathy Ivens i Conrada Carlberga Excel 2002 PL. Ksiêga ekspertaWydawnictwo Helion 2002. Przydatne informacje na temat ka¿dej funkcji mo¿na równie¿znale�æ w systemie pomocy Excela.

może nie jest powszechnie znany, alełatwo zrozumieć jego proste działaniepolegające na łączeniu tekstów, co po-kazuje poniższy rysunek.


80

Operatory u¿ywane w formu³ach i kolejno�æ ich dzia³ania

Operator jest symbolem określającym operację. Operatory arytmetyczne i lo-giczne oraz kolejność ich działania znamy ze szkoły. Operator konkatenacji być

+ dodawanie 3

� odejmowanie 3

* mno¿enie 2

/ dzielenie 2

^ potêgowanie 1

& konkatenacja, czyli ³¹czenie tekstów 4

= porównanie logiczne � równy 5

> porównanie logiczne � wiêkszy ni¿ 5

< porównanie logiczne � mniejszy ni¿ 5

>= porównanie logiczne � wiêkszy lub równy 5

<= porównanie logiczne � mniejszy lub równy 5

<> porównanie logiczne � ró¿ny od 5

Operator2 Dzia³anie Priorytet

2 Oprócz wymienionych w tabeli istniej¹ jeszcze operatory (�) oraz (%). Z powodu takiegosamego wygl¹du symbol pierwszy jest mylony z operatorem odejmowania, a drugiz formatem procentowym. Omówienie obu wykracza poza zakres tej ksi¹¿ki.

Pobierz zawartość z komórki położonej po-wyżej to polecenie (odwołanie) względ-ne. Jego wynik zależy od tego, do jakiejkomórki zostanie wpisane.

Pobierz zawartość z komórki leżącej naskrzyżowaniu piątego wiersza i kolumny Bto polecenie (odwołanie) bezwzględ-ne, niezależne od komórki, do którejzostanie wpisane. Można także po-bierać dane z innych arkuszy, czyliz trzeciego wymiaru.

Do tworzenia odwołań względnychsłużą adresy względne, a do tworze-nia odwołań bezwzględnych – adre-sy bezwzględne.

Zmiana adresów wzglêdnych przy kopiowaniu

Dość często wartości zapisane w ko-lumnie przeliczamy według określo-nego wzoru: złotówki na dolary, do-lary na euro, ceny netto, czyli bez po-datku VAT, na ceny brutto itd.

Powiedzmy, że ceny netto towarówzłożonych w magazynie musimyprzeliczyć na ceny brutto. Możemyto zrobić w sposób pokazany na ry-sunkach poniżej.

l Po opisaniu produktów w kolum-nie A, cen netto w kolumnie B orazwspółczynnika podatku w kolum-nie C – w komórce D2 wpisujemyformułę =B2*(1+C2) obliczającącenę brutto czajnika stalowego.

Rozdzia³ 4. Obliczenia

81

Adresy wzglêdne, mieszane, bezwzglêdne i trójwymiarowe

l Jak widać, formuła ta po skopiowa-niu do komórki D3 zmienia sięw formułę =B3*(1+C3) (pierwszyrysunek od góry).

l Sposób kopiowania nie ma tu zna-czenia.

l Można się przekonać, że ta samaformuła skopiowana do komórkiD4 ma postać =B4*(1+C4). Łatwo

zgadnąć, jak wygląda w komór-kach D5, D6 iD7 (drugi rysunek odgóry).

l Adresy komórek zapisywane w po-staci B2, C2, D2 itd. (litera kolum-ny i numer wiersza) są adresamiwzględnymi, co widać na rysun-kach poniżej.

l Formuła =A1 wpisane do komór-ki B1 oznacza w istocie: pobierz


82

l W komórkach jest wy-�wietlana obliczona warto�æ.Formu³ê zapisan¹ w komórcebie¿¹cej mo¿na zobaczyæ napasku edycji.

l Naciskanie kombinacji klawiszyCtrl+` (s³aby akcent nad klawi-szem Tab) powoduje naprze-mienne wy�wietlanie w komór-kach formu³ i warto�ci.

Wskazówka

wartość z komórki położonej polewej stronie. Po skopiowaniu dokomórki C2 zamieni się w formu-łę =B2, gdyż właśnie komórka B2leży na lewo od komórki C2.

l Ta sama formuła skopiowana dokomórki A3 pokaże błąd adresu,bo przecież na lewo od A3 nie mażadnej komórki (górny rysunek).

l W pokazanym poprzednio przy-kładzie, w którym ceny netto byłyprzeliczane na ceny brutto, adresywzględne odpowiednio zmieniałysię podczas kopiowania formuływ dół kolumny.

Adresy mieszane i bezwzglêdne

Jeżeli przelicznik zmienia się często,nie sposób wielokrotnie wpisywaćgo w wielu komórkach.

Każda zmiana wartości wymagałabywielu poprawek, a każda poprawkagrozi popełnieniem błędu.

Kurs walutowy zmienia się z dnia nadzień, więc arkusz trzeba tak przygo-tować, aby zmieniającą się wartośćwpisywać tylko do jednej komórki,skąd będzie pobierana przez wszyst-kie potrzebujące formuły.

Te formuły należy w odpowiednisposób napisać, aby nie psuły sięprzy kopiowaniu i przesuwaniu.

Zobaczmy, jak to zrobić.

l Wstawienie w adresie znaku dola-ra ($) powoduje zablokowaniewiersza lub kolumny. W adresieB1 nic nie jest zablokowane,w $B1 jest zablokowana kolum-na, w B$1 – wiersz, a w $B$1 –i wiersz, i kolumna.

l B1 to adres względny, $B1 i B$1to adresy mieszane (w pierwszymbezwzględna kolumna i względnywiersza, a w drugim odwrotnie),a $B$1 to adres bezwzględny.


83

l Zablokowana (bezwzględna) częśćadresu nie zmienia się podczas ko-piowania i przesuwania.

l Formuła =B4/B$1 po skopiowa-niu z C4 do C5 została zamienio-na na =B5/B$1.

l Kopiowanie w dół zmienia tylkowiersze, więc wystarczyło zabloko-wanie numeru wiersza. Moż-

liwe było również użycie adresubezwzględnego komórki B1 i za-pisanie w C4 kopiowanej formuływ postaci =B4/$B$1.

Adresy trójwymiarowe

Arkusz jest płaski, dwuwymiarowy,ale arkuszy w skoroszycie może byćwiele (od 1 do 255). Mamy więc ko-lumny, wiersze i arkusze – razem trzywymiary.

Jeżeli nie chcemy, aby pewne danebyły od razu widoczne, dobrze jestprzechowywać je w oddzielnym ar-kuszu. Np. prowadząc negocjacjehandlowe, zwykle nie chcemy poka-zywać cen zakupu towarów, ani wła-snej marży, a jedynie ceny detaliczne.


84

l Warto pamiêtaæ, ¿e w formu³ach adresy mo¿na pisaæ ma³ymi lubwielkimi literami. Formu³y =b4/b$1, =b4/B$1, =B4/b$1 Excel potraktujejednakowo i zamieni na =B4/B$1.

l Znaki $ blokuj¹ce w adresach wiersze lub kolumny mo¿na wpisywaærêcznie, lecz wygodniej jest u¿ywaæ klawisza F4.

l Podczas pisania lub poprawiania formu³y, gdy punkt wstawiania pozo-staje w obrêbie adresu komórki, kolejne przyciskanie klawisza F4 powo-duje zmiany wzglêdno�ci adresu pokazane na rysunkach poni¿ej.

Na skróty

Adresy w formu³ach najwy-godniej pisaæ ma³ymi literami,w miarê potrzeby blokuj¹c wierszelub kolumny przez jedno- lub kilka-krotne naciskanie klawisza F4, aleje¿eli kto� ma inne przyzwyczaje-nia, niczemu to nie szkodzi.

Wskazówka

Zobaczmy, jak to zrobić.

l Przygotowujemy dwa arkusze: Taj-ne i Cennik. W pierwszym umieści-my ceny zakupu i marżę, a w dru-gim ceny sprzedaży.

l Do komórki B4 wpisujemy for-mułę obliczającą cenę sprzedaży:=Tajne!B4*(1+Tajne!B$1).

l W adresach Tajne!B4 i Taj-ne!B$1, nazwa arkusza poprze-


85

dza adres komórki i jest od niegooddzielona wykrzyknikiem. Takadresujemy komórki w arkuszachinnych niż bieżący.

l Zwróć uwagę, że w adresie Taj-ne!B$1 numer wiersza jest bez-względny, aby nie zmieniał sięprzy kopiowaniu formuły w dółkolumny.

Technikę pisania formuł przećwiczy-my na formule z komórki B4 z ostat-nio omówionego przykładu.

l Przechodzimy do komórki B4, pi-szemy znak = i klikamy zakładkęarkusza Tajne (rysunek poniżej).

l Gdy Excel wyświetli arkusz Tajne(zgrupowany z arkuszem Cennik –obie zakładki są podświetlone),klikamy komórkę B4. Jej adres zo-staje wpisany do formuły.

l Klikamy na pasku edycji i dopisu-jemy *(1+ po czym ponownie kli-kamy komórkę B4.

l Po kliknięciu na pasku edycji naci-skamy dwukrotnie klawisz F4, aby

zamienić adres względny Tajne!B1na mieszany Tajne!B$1, dopisuje-my zamykający nawias i naciskamyEnter (rysunek na stronie 85).

l Excel wraca do wyświetlania arku-sza Cennik i wprowadza formułę dojego komórki B4.


86

Zapisane w oddzielnym arkuszu informacje niejawnemo¿na zabezpieczyæ przed wzro-kiem ciekawskich przez ukryciearkusza (patrz w poprzednimrozdziale podrozdzia³ �Ukrywaniearkuszy�).

Wskazówka

Wszystkie formu³y mo¿na wpi-sywaæ rêcznie, ale lepiej tego nierobiæ, gdy¿:l stosowanie innej techniki jest

szybsze,l wpisuj¹c formu³y rêcznie, ³atwiej

siê pomyliæ.

Wskazówka


87

Opisana procedura mo¿e siê na pocz¹tku wydawaæ skomplikowana,ale po nabraniu wprawy jej stosowanie nie sprawia k³opotów, a pozwalaunikn¹æ wielu b³êdów powodowanych przez rêczne wpisywanie adresów.

Wskazówka

Nazwy są przez początkujących rzad-ko używane, a szkoda, gdyż bardzozwiększają przejrzystość arkusza. Po-winno się je stosować, gdy przygotowa-nego przez nas arkusza mają używaćinne osoby, a także wtedy, gdy arkuszma być używany przez długi czas. Jeże-li po wielu miesiącach lub latach bę-dziemy musieli w nim coś zmienić lubpoprawić, istnienie zrozumiałychnazw bardzo nam pomoże.

Definiowanie nazw

Mamy do rozwiązania dokładnie ta-ki sam problem jak w podrozdziale„Adresy mieszane i bezwzględne” –ceny w złotówkach trzeba przeliczyćna euro według kursu zapisanegow jednej komórce. Tym razem, abyarkusz był czytelniejszy, w formuleużyjemy nazwy.

l Po przejściu do komórki B2wyda-jemy polecenie Wstaw/Nazwa/Definiuj. Ponieważ Excel zapropo-

nuje rozsądną nazwę EUR (pobra-ną z sąsiedniej komórki), pozosta-nie jedynie zatwierdzić ten wybórprzez kolejne kliknięcia przyci-sków Dodaj i OK.

l Gdy podczas wprowadzania for-muły przeliczeniowej do komórkiC5 klikniesz nazwaną komórkęB2, do wzoru zamiast adresu, jakpoprzednio, zostanie wpisana jejnazwa EUR.

l Po skopiowaniu formuły z ko-mórki C5 w dół kolumny nazwaEUR, będąca odwołaniem do B2,nie zmienia się i przeliczenia są


88

Nazwy

prawidłowe. Jest tak, gdyż nazwanadana w sposób standardowyjest odwołaniem bezwzględnymna poziomie arkusza, co oznacza,że użyta w formule spowoduje za-

wsze pobranie wartości z tej samejkomórki tego samego arkusza.W tym wypadku jest to komórkaB2 z arkusza Przeliczenie.

Stosowanie nazw

Jak wspomniałem na początku roz-działu, w większości arkuszy przygo-towywanych nieprofesjonalnie na-zwy nie są stosowane. Aby je zastoso-wać, nie musimy poprawiać po koleiwszystkich formuł.

l W arkuszu pokazanym poniżejformuły zostały zapisane bez uży-cia nazwy.

l Aby zastosować nazwę, trzeba za-cząć od nazwania właściwej komór-


89

Po wydaniu polecenia Wstaw/Nazwa/Definiuj, Excel proponuje nazwê po-bran¹ z jednej z komórek z najbli¿-

szego otoczenia komórki nazy-wanej. Je¿eli propozycja nam nie od-powiada, mo¿emy wpisaæ inn¹ na-

Uwaga

ki (patrz podrozdział „Definiowa-nie nazw”). Jeżeli bieżąca komórkajest nazwana, jej nazwa jest wyświe-tlana w lewej części paska edycji.

l Po zaznaczeniu zakresu, w któ-rym ma nastąpić zamiana, wydajpolecenie Wstaw/Nazwa/Zastosuji (po wybraniu nazwy jeśli jest ichkilka) kliknij przycisk OK. Zamia-na została dokonana.


90

l Kopiowanie nazwanychkomórek nie powoduje zmianyprzypisania nazw.

l Po przesuniêciu nazwanej ko-mórki nazwa jest przypisana dokomórki przesuniêtej.

Wskazówka

Tworzenie nazw

Jeżeli mamy kilka kont bankowychprowadzonych w różnych walutach,warto sporządzić arkusz pozwalają-cy na ciągłe śledzenie, ile wynosi su-maryczna wartość posiadanej gotów-ki. Trzeba go zrobić tak, aby po wpi-saniu bieżących kursów walut dowyznaczonych komórek wartośćwszystkich kont była automatycznieprzeliczana na złotówki.

l W przykładowym arkuszu za-znacz zakres B1:D2 obejmującynazwy walut i wydaj polecenie

Wstaw/Nazwa/Utwórz, po czymw oknie dialogowym Tworzenienazw kliknij przycisk OK, zatwier-dzając pobieranie nazw z komó-rek górnego wiersza.

l Po wpisaniu formuł przeliczenio-wych, pokazanych poniżej w ko-mórkach zakresu D4:D9, pozosta-nie jedynie podsumowanie wszyst-kich pieniędzy w komórce D10.


91

l Je¿eli przed wydaniem po-lecenia Wstaw/Nazwa/Zastosujnie zaznaczysz zakresu, zamia-na zostanie dokonana w ca³ymarkuszu.

l Je¿eli w oknie dialogowym Sto-sowanie nazw wy³¹czysz opcjêIgnoruj wzglêdne/bezwzglêdne,nazwa zostanie wstawiona je-dynie w miejsce adresów bez-wzglêdnych. Wzglêdne i mie-szane pozostan¹ niezmienione.

Uwaga

Nazwy zakresów z adresowaniem wzglêdnym

Choć domyślnie Excel przypisujenazwy do zakresów bezwzględnych,możliwe jest również nazwanie za-kresu adresowanego względnie. Ma-

jąc w kolumnie B spisane dochodymiesięczne, chcemy w kolumnie Cpodliczyć dochody kwartalne.

l Po przejściu do C4 i wydaniu pole-cenia Wstaw/Nazwa/Definiuj w ok-nie dialogowym Definiowanie nazwwpisz nazwę Kwartał i po kliknię-ciu w polu Odwołuje się do zaznaczmyszą zakres B2:B4.

l Gdy Excel zaproponuje adresowa-nie bezwzględne =Arkusz1!$B$2:$B$4, zamień je na względne=Arkusz1!B2:B4 i kliknij OK.

l Do C4 wpisz formułę =suma(Kwartał) i skopiuj ją doC7, C10 i C13.


92


93

l Formu³ê =suma(kwarta³) Excel zamieni automatycznie na pisan¹wielkimi literami =SUMA(KWARTAŁ).

l W ka¿dej z komórek C4, C7, C10 i C13 formu³a sumuj¹ca ma tak¹ sam¹postaæ =SUMA(KWARTAŁ), ale za ka¿dym razem sumowanie obejmuje licz-by zapisane w innych komórkach, odpowiadaj¹cych dochodom w miesi¹-cach innego kwarta³u. Oznacza to, ¿e nazwa Kwarta³ jest przypisana dozakresu adresowanego wzglêdnie.

l Informacje o przypisywaniu nazw do sta³ych i formu³, nazywaniu zakresówdynamicznych oraz definiowaniu nazw na poziomie arkusza i skoroszytuumie�ci³em w ksi¹¿kach Excel 2002/XP/PL. Æwiczenia zaawansowane i Excel2003 PL. Æwiczenia zaawansowane oraz Excel 2003 PL. 161 praktycznychporad. Tutaj pomijam te sprawy jako zbyt zaawansowane.

Wskazówka

Pisanie formuł od podstaw jest możli-we jedynie w przypadku prostych ob-liczeń. Dopiero stosowanie gotowychfunkcji czyni z Excela narzędzie przy-datne w wielu dziedzinach, np. w fi-nansach, statystyce, obliczeniach ma-tematycznych i inżynierskich.

Mówiąc w skrócie, funkcje to predefi-niowane formuły. Ktoś je dla nas przy-gotował i korzystając z nich, nie musi-my „ponownie odkrywać Ameryki”.

Wpisywanie funkcji do formu³y na przyk³adziefunkcji JE¯ELI()

Jeżeli prowadzimy sklep, wartosprawdzać na bieżąco, czy posiadaneprzez nas zapasy poszczególnych to-warów nie spadają poniżej wy-

znaczonego minimum. Dzięki temumożemy na czas uzupełniać braki.

l Aby do komórki D2 wprowadzićformułę sprawdzającą, po wpisa-niu znaku =, kliknij przyciskWstaw funkcję.

l W oknie dialogowym Wstawianiefunkcji wybierz kategorię Logicznei funkcję JEŻELI. Naciśnij OK.


94

Funkcje

l Formu³ê do komórki D2mo¿na by³o wpisaæ rêcznie bezkorzystania z przycisku Wstawfunkcjê i wy�wietlanego przezniego okna dialogowego.

l Wpisuj¹c formu³y, trzeba pamiê-taæ, ¿e argumenty s¹ ujête w na-wiasy i oddzielone �rednikami(;). Ka¿da funkcja ma inne argu-menty. Je¿eli nie znamy ich licz-by i znaczenia, najlepiej korzy-staæ z przycisku Wstaw funkcjêi mechanizmu pomocy Excela.

Wskazówka

l W oknie dialogowym Argumentyfunkcji wypełnij pola jak na rysun-ku powyżej i naciśnij OK.

l Po skopiowaniu formuły z ko-mórki D2 do zakresu D3:D6 ar-kusz jest gotowy.


95

W polach okna dialogowego Argumenty funkcji adresy komórek mo¿-na wpisywaæ rêcznie lub wprowadzaæ przez wybieranie mysz¹ w arkuszu.

Wskazówka

l Jak widaæ z opisu na doleokna dialogowego Wstawianiefunkcji, funkcja JEŻELI ma trzyargumenty: JEŻELI(test_lo-giczny; wartość_jeże-li_prawda; wartość_jeże-li_fałsz). Ich nazwy okre�laj¹znaczenie, a dzia³anie praktycz-ne zosta³o pokazane w dalszejczê�ci przyk³adu.

l Nale¿y pamiêtaæ o oddzielaniuargumentów �rednikami (wwersji angielskiej przecinkami)

Wskazówka

Sumy po�rednie

Sumowanie pośrednie jest czynno-ścią bardzo często wykonywaną przysporządzaniu różnego rodzaju spi-sów i zestawień. Bez stosowania wła-ściwej funkcji przygotowanie odpo-wiedniego arkusza i korzystaniez niego jest uciążliwe.

l Jeżeli wypłaty w działach i wypłatęogólną podsumujemy za pomocązwykłej funkcji SUMA, powstaniebłąd – otrzymamy wartość dwu-krotnie większą od rzeczywistej(dodane zostaną składniki sum po-średnich i same sumy).

l Jak widać na rysunkach powyżeji poniżej, właściwy wynik daje ręcz-ne sumowanie za pomocą formuły=C6+C10+C15 lub =SUMA(C6;C10;C15).Jednak stosowanie ta-kich formuł jest niewygodne i przy-sparza wielu kłopotów podczasprzeróbek arkusza (np. gdy zwięk-sza się lub zmniejsza liczba osóbw poszczególnych działach).


96


97

Aby problem rozwi¹zaæ, nale¿y u¿yæ funkcji SUMY.POŚREDNIE.l Jak widaæ, wszystkie wyniki sumowania s¹ prawid³owe.

l Formu³a =SUMY.POŚREDNIE(9;C2:C15) w komórce C16 obejmuje sumowa-niem ca³y zakres C2:C15, ale funkcja SUMY.POŚREDNIE pomija w sumowaniuinne SUMY.POŚREDNIE unika siê podwójnego sumowania.

Wskazówka

l Po przejściu do komórki C6i kliknięciu przycisku na pa-sku edycji wybierz w oknie dialo-

gowym Wstawianie funkcji kategorięMatematyczne i funkcję SUMY.PO-ŚREDNIE.

l W oknie dialogowym Argumentyfunkcji podaj wartości: Funk-cja_nr: 9 i Adres 1: C2:C5.

Zagnie¿d¿anie funkcji, czyli funkcja w funkcji

Zagnieżdżanie to stosowaniejednej funkcji wewnątrz dru-giej.

Funkcja zagnieżdżona jest argumen-tem innej funkcji. Zagnieżdżaniefunkcji jest bardzo wygodne i poży-

teczne.

W firmie sprzedającej te-lewizory pracownicyotrzymują premie kwar-talne w wysokości 500 zł,jeżeli ich średnia sprze-daż miesięczna przekra-


98

W omawianym przyk³adzie parametrowi Funkcja_nr zosta³a nadanawarto�æ 9. Dziêki temu funkcja SUMY.POŚREDNIE wykonywa³a zwyk³e su-mowanie (z pominiêciem innych sum po�rednich). Gdyby temu parametro-wi zosta³a nadana inna warto�æ, funkcja SUMY.POŚREDNIE mog³aby obli-czaæ np. iloczyn.Znaczenie ró¿nych warto�ci parametru Funkcja_nr wed³ug systemu pomocyExcela 2003.

Wskazówka

cza 80 sztuk. Trzeba przygotować ar-kusz, w którym wartość premii bę-dzie wyliczana automatycznie.

l Po przejściu do E2 kliknij przy-cisk i wybierz w oknie dialo-gowym Wstawianie funkcji funkcjęJEŻELI, po czym rozwiń listęfunkcji, które mogą być w tejfunkcji zagnieżdżone.

l Z listy wybierz funkcję ŚREDNIA.

l W tym wypadku Excel automa-tycznie rozpozna zakres oblicza-nia średniej. Gdyby tak się nie sta-ło, trzeba adres zakresu ręczniepoprawić.

l Potem najwygodniej bezpośred-nio na pasku edycji wpisać resztęwarunku i pozostałe dwa argu-menty funkcji JEŻELI oraz koń-cowy nawias i zatwierdzić wszyst-ko kliknięciem OK.


99

l Po skopiowaniu zawartości E2w dół kolumny arkusz jest gotowy.

Funkcje podrêczne

Najczęściej używaną funkcją jest SU-MA, dlatego wpisywanie jej do formułyzostało ułatwione przez umieszczeniena standardowym pasku narzędziprzycisku Autosumowanie . Tegoprzycisku używaliśmy już kilkakrot-nie. Obok znajduje się przycisk rozwi-jania listy funkcji podręcznych .


100

W funkcji zagnie¿d¿onejmo¿na zagnie�dziæ nastêpn¹funkcjê i tak do sze�ciu poziomów.

Wskazówka

Ułatwia on stosowanie innych częstoużywanych funkcji, ale – jak się prze-konamy – ten element Excela jestniedopracowany, przynajmniej w tejwersji pakietu Office 2003 PL, z któ-rej korzystam.

Jeżeli chcemy zebrać jakieś dane, np.o wszystkich pracownikach firmy,musimy sprawdzać, czy kogoś niepominęliśmy. Gdy lista jest tak krót-ka jak pokazana poniżej, wystarczyjeden rzut oka, lecz jeśli osób jest kil-kanaście tysięcy, sprawdzanie, czy ca-

ła kolumna już została wypełniona,może być żmudne. Trzeba więcwprowadzić kontrolę automatyczną.

l Po przejściu do komórki poniżejkolumny danych (w tym wypad-ku D9) rozwijamy listę funkcjipodręcznych i wybieramy z niejpolecenie Licznik.

l Ponieważ kolumna danych niejest pełna, Excel źle rozpoznajezakres jako D6:D8. Poprawiamygo na D2:D8, dopisując ponadtoa8– na początku formuły.

l Po naciśnięciu Enter formuła zo-stanie wprowadzona do komórki.Zauważ, że a8– zostało poprawio-ne na A8–. Formuła prawidłowopodaje liczbę niewypełnionychkomórek w zakresie D2:D8.


101


102

Poniewa¿ funkcja ILE.LICZB zlicza liczby (odró¿nia je nie tylko odpustej komórki, lecz tak¿e od tekstu), mo¿e s³u¿yæ do sprawdzania, czyw jakie� komórce nie pope³niono b³êdu i nie wpisano tekstu zamiast liczby,np. 12.0 zamiast 12,0, co w przypadku wyrównania zawarto�ci do prawejstaje siê trudne do wykrycia.

Wskazówka

l W ostatnim przyk³adzie zwróæ uwagê na nietypow¹ kolejno�æ wpro-wadzania formu³y =A8-ILE.LICZB(D2:D8). Najpierw przez wybranie po-lecenia Licznik z menu Autosumowania zosta³a wprowadzona funkcjaILE.LICZB, a potem dopisany pocz¹tkowy fragment formu³y a8–.

Uwaga

Przyk³ad korzystania z systemu pomocy Excela

Opis funkcji archaicznej już wersjiExcela 4.0 liczył 745 stron. Nie wiado-mo ile stron byłoby teraz, gdyż zanie-

chano drukowania takich instrukcji,a opisy funkcji umieszczono w syste-mie pomocy Excela. Nikt nie jestw stanie spamiętać tak wielu infor-macji i nikomu nie jest to potrzebne.Jest nieco funkcji powszechnie uży-


103

l Taka kolejno�æ nie ma logicznego uzasadnienia. Z powodu b³êdu w pro-gramie, je¿eli najpierw napiszemy pocz¹tek formu³y =a8–, a potem spró-bujemy wprowadziæ funkcjê, wybieraj¹c polecenie Licznik z menu Auto-sumowanie, pojawi siê komunikat o b³êdzie. Byæ mo¿e z czasem zosta-nie to poprawione.

l Gdyby�my po wpisaniu pocz¹tku formu³y =a8– dopisali resztê rêcznie al-bo wprowadzili funkcjê ILE.LICZB(D2:D8) w sposób standardowy przezklikniêcie przycisku , dzia³anie skoñczy³oby siê pomy�lnie bez komu-nikatu o b³êdzie.

l Wszystkie programy sprawiaj¹ niespodzianki � Excel te¿.

wanych, takich jak SUMA, ŚREDNIA,MAX itp., lecz poza nimi jednym sąpotrzebne takie, a innym owakie.

Dlatego warto wiedzieć, w jaki spo-sób szukać informacji o funkcjach.Samo odczytywanie nazw nie zawszewystarcza. Zaraz się o tym przekona-my.Czym się różni funkcja ŚREDNIAod funkcji ŚREDNIA.A, co jest skró-tem od średnia arytmetyczna?

l Na rysunkach poniżej widać śred-nią ocenę trzyosobowej grupy obli-czoną za pomocą funkcji ŚREDNIAi ŚREDNIA.A. W drugim przypad-ku wynik jest błędny, gdyż brak oce-ny nie powinien obniżać średniej.

l Aby poznać przyczynę tego błę-du, przejdź do komórki E1,w której wartość średniej jest błęd-nie wyliczona, i na pasku edycji


104

kliknij przycisk Wstaw funkcję. GdyExcel wyświetli okno dialogoweargumentów funkcji wpisanej dobieżącej komórki, kliknij łącze Po-moc dotycząca tej funkcji.

l Dwa zakreślone akapity wyjaśniająprzyczynę innej wartości średniejwyliczonej za pomocą funkcjiŚREDNIA.A. Wyliczona wartość to(3+5+0)/3. Gdyby została użytafunkcja ŚREDNIA, byłaby obliczo-na wartość (3+5)/2.


105

l Je¿eli nie znasz dok³adnieargumentów i sposobu dzia³ania funkcji, przed jejwstawieniem do formu³y lepiejprzeczytaj jej opis w systemiepomocy Excela. Nie zachowu-j¹c tej ostro¿no�ci, mo¿naotrzymaæ bardzo dziwne wyniki.

l Wiem, ¿e to truizm, ale rady oczywiste s¹ zwykle lekce-wa¿one.

Wskazówka

Błędy w obliczeniach, ich przyczynyi dociekanie, w jaki sposób oblicze-nia są wykonywane, to temat rzeka,więc powiemy tylko o kilku najczę-ściej spotykanych problemach.

Dzielenie przez zero

Firma prowadząca kursy żeglarskieprzygotowała trzy kursy i chce oce-nić koszty wstępne przypadające najednego uczestnika.

Na kursy zgłaszają się różni ludziei niektórych nie można przyjąćchoćby ze względów zdrowotnych.

Należy policzyć koszt przypadającyna jedną przyjętą osobę.

l Jak widać na rysunku poniżej,użycie prostej formuły dzielącejkoszt przez różnicę między licz-bą kandydatów a liczbą przyję-tych działa dobrze pod warun-kiem, że nie wszyscy zostaną od-rzuceni.

l W takim przypadku otrzymuje-my błąd dzielenia przez zero#DZIEL/0!.

l Rozwiązaniem jest użycie w for-mule funkcji JEŻELI.


106

Jak to siê liczy lub dlaczego siê nie liczy?

Dzielenie przez tekst

Komunikat informujący o dzieleniuprzez zero dokładnie objaśnia przy-czynę błędu, ale nie zawsze jest takdobrze. Najczęściej dowiadujemy sięo istnieniu błędu, ale sami musimydociec jego przyczyny.

l W przykładzie obok czas ruchujest liczony według prostego wzo-ru dzielenia drogi przez prędkość.

l W komórce B4 zamiast przecinkadziesiętnego została napisanakropka. Jak już wiemy, ciąg zna-ków 4.5 zostanie uznany za teksti spowoduje błąd w komórce C4.

l Komunikat #ARG oznacza użyciebłędnego argumentu, lecz użytkow-nik musi sam stwierdzić, który ar-gument jest błędny i dlaczego.


107

l Wszelkie b³êdy obliczeñ, wiêc równie¿ i ten, mo¿na ukryæ, stosuj¹cz³o¿enie funkcji JEŻELI i CZY.BŁĄD, co zosta³o pokazane na rysunkuponi¿ej. Zagadnienie maskowania b³êdów w ten sposób nieco wykraczapoza zakres tej ksi¹¿ki, wiêc zosta³o tu tylko wspomniane.

Wskazówka

Inspekcja formu³ Inspekcja formuł to nazwa paska narzę-dziowego zawierającego zestaw bar-dzo przydatnych przycisków.

l Aby wyświetlić pasek Inspekcja for-muł, należy wydać polecenie Widok/Paski narzędzi/Inspekcja formuł.

l Jeżeli bieżąca komórka zawiera for-mułę, kliknięcie przycisku Śledź po-przedniki spowoduje wyświetleniestrzałek wskazujących komórki,z których formuła pobiera dane.

l Strzałki usuwamy, klikając przy-cisk Usuń strzałki poprzedników lubprzycisk Usuń wszystkie strzałki.

l Odwrotnie działa przycisk Śledźzależności – pokazuje on, w jakichkomórkach znajdują się formułypobierające dane z komórki bieżą-cej. Te strzałki są kasowane przezkliknięcie przycisku Usuń strzałkizależności bądź przycisku Usuńwszystkie strzałki.


108

l Je¿eli formu³a jest skompli-kowana, do jej zanalizowaniawarto u¿yæ paska narzêdziowe-go Inspekcja formu³ (patrz na-stêpny podrozdzia³).

Wskazówka

Szacowanie formu³

Jest to jedno z zagadnień inspekcjiformuł, lecz na tyle ważne, że posta-nowiłem je wyróżnić, poświęcającmu oddzielny podrozdział. Posłuży-my się tym samym co poprzednioprostym przykładem.

l Szacowanie formuły zaczynamyod przejścia do właściwej komór-ki i kliknięcia przycisku Szacujformułę.

l Poniższe rysunki pokazują kolejnekroki szacowania następujące w wy-niku klikania przycisku Szacuj.


109

l Pasek narzêdziowy Inspekcja formu³ dok³adnie omówi³emw ksi¹¿kach Excel 2002/XP Pl. Æwiczenia zaawansowane i Excel2003 PL. Æwiczenia zaawansowane).

l Zosta³y tam opisane wszystkie przyciski tego paska narzêdziowego i po-dane przyk³ady ich u¿ycia.

Wskazówka

l Należy pamiętać, że w następ-nym kroku będzie obliczony ele-ment, który jest podkreślony,a tekst jest wyróżniany przez uję-cie w cudzysłów.

Dwa popularne b³êdy:#NAZWA i b³¹d adesowania cyklicznego

Błędy te można popełnić nawet przywpisywaniu najprostszej formuły,więc warto je poznać.

l Najczęstszą przyczyną błędu#NAZWA jest użycie w formulebłędnej nazwy funkcji (jak na ry-sunku poniżej) lub błędnej nazwyzakresu (np. wcześniej nie zdefi-niowanej, jak na rysunku drugimi trzecim u góry następnej strony).


110

l Błąd odwołania cyklicznego po-wstaje, gdy formuła zapisana w ja-kiejś komórce odwołuje się bezpo-średnio lub pośrednio do tej ko-mórki.

l Na rysunkach poniżej formuła=SUMA(A1:A4) jest zapisana w A4i odwołuje się do A4.

l Kliknięcie OK w oknie komunikatuo błędzie powoduje wyświetlenie

paska narzędziowego Odwołanie cy-kliczne, który pomaga zdiagnozowaćbłąd (przyciski są znane z paska In-spekcja formuł – patrz strona 106).

Zmiana trybu wykonywania obliczeñ

Excel oblicza formuły natychmiastpo ich wpisaniu do arkusza. Pozmianie wartości w komórce, od któ-rej formuła jest zależna, od razu zo-staje wyświetlony nowy wynik.

Obowiązują tu następujące zasady:

l po zmianie formuły lub zmianiedanych w komórkach Excel na-tychmiast oblicza wszystkie for-muły, które są zależne od dokona-nych zmian.

l Formuły są wyliczane w natural-nej kolejności, według zależności.Po zmianie wartości w komórceC2 najpierw zostanie wyliczona


111

suma w komórce E2, a potem su-ma sum w komórce E5.

To natychmiastowe automatycznewyliczanie jest na co dzień bardzowygodne, ale jeżeli pracujemy z arku-szem zawierającym wiele tysięcy po-wiązanych ze sobą formuł, staje sięmęczące i opóźnia działanie.

l Po wydaniu polecenia Narzę-dzia/Opcje można w oknie Opcje nakarcie Przeliczanie zmienić oblicza-nie Automatyczne na Ręczne.

Po włączeniu przeliczania ręcznegoExcel przestaje wykonywać obliczeniazaraz po zmianie formuł lub danych.

l Obliczenia są wykonywane dopie-ro po naciśnięciu przycisków Ob-licz teraz (F9) lub Oblicz arkusz w po-kazanym oknie dialogowym.

l Sięganie do okna dialogowego jestniewygodne, więc w praktyce ko-rzysta się z dwóch skrótów klawi-szowych:

l F9 – powoduje obliczenie formuł wewszystkich otwartych skoroszytach.

l Shift+F9 – powoduje obliczanie for-muł tylko w aktywnym skoroszycie.


112

Zmiana trybu przeliczaniaformu³ dotyczy nie tylko bie¿¹ce-go arkusza lub skoroszytu, leczwszystkich arkuszy wszystkichotwartych skoroszytów.

Uwaga

Excel 2003 PL. Ilustrowany przewodnik

Technology

Transcript of Excel 2003 PL. Ilustrowany przewodnik