6. Zaawansowana praca z formułami
6.1. Praca z grupami komórek
6.1.1. Przenoszenie komórek
Przypuśćmy, że w pewnym momencie zdaliśmy sobie sprawę, że źle zaplanowaliśmy układ arkusza i musimy przesunąć część komórek w inne miejsce.
Nic prostszego! Najpierw zaznaczamy przenoszone komórki. Następnie przenosimy kursor nad brzeg zaznaczonego obszaru, czekając, aż zmieni swój wygląd z kursora zaznaczania ( ) na kursor przesuwania ( ). Wciskamy lewy klawisz myszki i – cały czas utrzymując go w tym stanie – przesuwamy wskaźnik myszy (który w tym momencie przyjmuje postać zwykłej strzałki ( )), a wraz z nim zaznaczoną grupę komórek. Podczas przenoszenia komórek tym sposobem zawarte w nich formuły nie ulegają żadnej modyfikacji (czyli wszystkie adresy traktowane są jak adresy bezwzględne).
6.1.2. Kopiowanie grup komórek
Jak wiemy, grupę komórek można skopiować poprzez schowek Windows ( Ctrl+C , Ctrl+V ). Inna metoda polega na wykorzystaniu uchwytu wypełnienia, czyli małego czarnego kwadracika wyświetlanego przy prawym dolnym rogu zaznaczonego obszaru. Przypuśćmy, że chcemy skopiować dane z komórek A1:A6 do B1:B6 (Rys. 6.1). W tym celu:
Zaznaczamy zakres kopiowanych komórek
Umieszczamy wskaźnik myszy nad uchwytem wypełnienia (wskaźnik powinien przyjąć kształt krzyżyka ( ).
Przeciągamy mysz do sąsiedniej kolumny (w prawo). Powoduje to skopiowanie danych z kolumny A do B i wyświetlenie niewielkiego przycisku menu wypełniania (Rys. 6.1). Po kliknięciu tego przycisku można wybrać jedną z
uchwyt wypełniania
przycisk menu wypełniani a
Rysunek 6.1. Wygląd arkusza bezpośrednio przed (a) i po (b) skopiowaniu grupy danych przy pomocy uchwytu wypełniania.
uchwyt wypełniania
(a) (b)
czterech opcji: kopiuj komórki , wypełnij serią , wypełnij tylko formatami oraz wypełnij bez formatowania .
6.1.3. Wypełnianie komórek
Przypuśćmy, że chcemy wypełnić pierwszą kolumnę tabeli kolejnymi liczbami porządkowymi. Jest to czynność żmudna, czasochłonna i nudna – w sam raz, by zlecić jej wykonanie maszynie. Rozpoczynamy ją od zaznaczenia pierwszej komórki zakresu (np. A1) i wpisania w niej wartości początkowej (zazwyczaj 1). Teraz łapiemy myszą za uchwyt wypełniania (por. punkt 6.1.2) i przeciągamy go do ostatniej komórki z planowanego zakresu (np. A7). Program skopiuje wartość pierwszej komórki do każdej komórki z zaznaczonego zakresu. Nie jest to wprawdzie dokładnie to, o co nam chodziło, ale jesteśmy już naprawdę blisko celu.
Teraz klikamy ikonę opcji wypełniania i wybieramy w nim pozycję Wypełnij serią (Rys. 6.2). Spowoduje to wypełnieniem całego zaznaczonego zakresu kolejnymi liczbami od 1 do 7. Dokładnie tak samo można by wypełnić kolumnę A setką lub tysiącem kolejnych liczb.
Jak łatwo się domyślić, gdybyśmy uchwyt wypełnienia przeciągnęli w kierunku poziomym, moglibyśmy wypełnić kolejnymi liczbami cały rząd arkusza. Ale Excel potrafi dużo więcej:
Jeżeli wypełnimy dwie pierwsze komórki zakresu, zaznaczymy je i przeciągniemy uchwyt wypełniania do ostatniej planowanej komórki zakresu, Excel wypełni go ciągiem arytmetycznym (tj. różnice między kolejnymi liczbami będą takie same). Na przykład, jeśli pierwsze 2 komórki będą miały wartość 2 i 4, Excel wypełni pozostałe komórki liczbami 6, 8, 10, 12,…
Zamiast liczb możemy używać nazw dni tygodnia (np. „poniedziałek”). Po przeciągnięciu uchwytu wypełniania Excel uzupełni zaznaczony zakres nazwami kolejnych dni tygodnia. Co więcej, po kliknięciu ikony menu wypełniania, program zaoferuje nam możliwość pominięcia dni świątecznych.
W pierwszej komórce zakresu można także wpisać datę. Przy pomocy opcji menu wypełniania możemy zdecydować, czy daty w kolejnych komórkach mają się różnić o dzień kalendarzowy, dzień roboczy, miesiąc czy rok.
1. Zaznacz komórkę i przeciągnij uchwyt wypełniania w dół
2. Kliknij ikonę menu
wypeniania 3. Wybierz opcję „Wypełnij serią”.
Rysunek 6.2. Trzy fazy wypełniania kolumny kolejnymi liczbami
całkowitymi.
6.2. Zakresy
6.2.1. Definiowanie zakresów w formułach
Operacje na grupach komórek to nie tylko kopiowanie, przenoszenie czy wypełnianie. W praktyce bardzo często zachodzi potrzeba użycia grup komórek w formułach. Zagadnienie to rozpatrzmy na następującym przykładzie: jak wyznaczyć najmniejszą liczbę spośród komórek o adresach leżących w prostokącie o wierzchołkach A1, E1, A4 i E4? W praktyce tego typu zagadnienia występują bardzo często – przy wyznaczaniu osoby o najniższych zarobkach, miesiąca o najmniejszej sprzedaży itp. Najprostsze rozwiązanie polega na zastosowaniu specjalnej funkcji MIN . Czy jednak musimy przekazać jej osobno adresy wszystkich komórek, wśród których ma ona znaleźć tę o najmniejszej wartości? Taka formuła
wyglądałaby dość koszmarnie:
=MIN(A1;B1;C1;D1;E1;A2;B2;C2;D2;E2;A3;B3;C3;D3;E3;A4;B4;C4;D4;E4) , a używanie funkcji MIN byłoby zupełnie niepraktyczne w przypadku naprawdę dużych tabel.
Twórcy Excela poradzili sobie z tym problemem poprzez wprowadzenia dwóch dodatkowych operatorów: dwukropka ( : ) i średnika ( ; ). Pierwszy z nich służy do definiowania prostokątnych obszarów arkusza. Na przykład zapis B1:C7 oznacza zakres obejmujący wszystkie komórki w prostokącie wyznaczonym przez komórki
B1 i C7. Formułę
=MIN(A1;B1;C1;D1;E1;A2;B2;C2;D2;E2;A3;B3;C3;D3;E3;A4;B4;C4;D4;E4) , można więc zapisać w czytelnej postaci =MIN(A1:E4) . Wprowadzono też specjalny sposób odwoływania się w formułach do wierszy i kolumn. Polega on na użyciu oznaczeń kolumn (lub wierszy) po obu stronach dwukropka. Na przykład zapis A:A oznacza całą kolumnę A, a B:D oznacza kolumny B, C i D. Analogicznie 2:2 oznacza drugi wiersz arkusza, a 1:10 odnosi się do jego pierwszych dziesięciu wierszy.
Drugi operator, średnik, służy do łączenia kilu zakresów w jeden. Na przykład definicja =MIN(1:1;A:A) nakazuje programowi odnaleźć wartość najmniejszą we wszystkich komórkach pierwszego rzędu i pierwszej kolumny. Zastosowanie tego operatora ilustruje Rys. 6.3, na którym widzimy formułę odnoszącą się do 4 prostokątnych zakresów komórek.
Istnieje jeszcze trzeci, dość rzadko używany operator, którego graficzną reprezentacją jest odstęp. Służy on do definiowania części wspólnej kilku zakresów.
A1:B
5 C3:C
7 D 9
E4:F 6
Rysunek 6.3. Przykład formuły zawierającej odwołanie
do kilku grup komórek.
Na przykład formuła =SUMA(A:B 1:2) wyznacza sumę liczb z komórek leżących jednocześnie w dwóch pierwszych kolumnach ( A:B ) i wierszach ( 1:2 ).
Należy pamiętać, że w formułach typu SUMA(A1:B5;C3:C7) do łączenia kolejnych zakresów komórek używa się średnika, mimo iż naturalniejszy wydawałby się tu przecinek. Ten jest już jednak zarezerwowany na oznaczenie separatora części ułamkowej w liczbach dziesiętnych.
6.2.2. Definiowanie zakresów metodą graficzną
W rozdziale 5.8.3 opisałem sposób definiowania adresów w formułach poprzez wskazywanie komórek w arkuszu. Dokładnie w ten sam sposób można w formułach umieszczać zakresy komórek. Prześledźmy to na prostym przykładzie: spróbujemy w ten sposób wpisać w komórce C6 formułę =MIN(A1:E4) . W tym celu:
Zaznaczamy komórkę C6.
Rozpoczynamy wpisywanie formuły, czyli wprowadzamy znak = .
Wpisujemy nazwę funkcji i otwieramy nawias. Komórka C6 powinna w tym momencie zawierać wpis =MIN( . W tym momencie poniżej komórki C6 pojawi się żółte okienko z podpowiedzią dotyczącą składni funkcji (Rys. 6.4).
Znaczenie podpowiedzi „ MIN(liczba1;[liczba2];…) ” jest następujące:
MIN to oczywiście nazwa funkcji.
liczba1 to nazwa pierwszego argumentu. Słowo „liczba” informuje, ze pierwszy argument powinien być liczbą lub datą (a nie tekstem).
Wytłuszczenie argumentu informuje, że właśnie jego wartość jest wprowadzana.
[liczba2] to nazwa drugiego argumentu. Nawiasy kwadratowe informują, że liczba2 jest argumentem opcjonalnym (nieobowiązkowym).
;… średnik przypomina, że argumenty oddziela się od siebie średnikiem.
Wielokropek informuje, że liczba dodatkowych argumentów może być dowolna.
Klikamy narożną komórkę zakresu komórek (np. A1) i – trzymając wciśnięty lewy klawisz myszki – przeciągamy wskaźnik myszy do przeciwległego narożnika (E4). Spowoduje to utworzenie „ruchomej” ramki wokół komórek A1:E4. Program wyświetli jej rozmiar w okolicy wskaźnika myszy w małym okienku podpowiedzi. Na przykład tekst „4W x 5K” (jak na Rys. 6.4) oznacza, że zaznaczony prostokąt ma 4 wiersze i 5 kolumn. Jednocześnie Excel w definiowanej komórce C6 i w polu formuły automatycznie wpisze tekst A1:E4 .
Kończymy wprowadzanie formuły, wpisując nawias i przyciskając Enter . (Jeśli
zapomnimy o nawiasie, program sam go dopisze).
6.3. Funkcje
W poprzednim punkcie zetknęliśmy się już z przykładem zastosowania funkcji do wyznaczenia najmniejszej wartości spośród zbioru liczb. Excel ma wbudowanych ponad 200 funkcji różnego typu; dzięki temu można w nim przeprowadzić niemal każde obliczenia potrzebne w zastosowaniach biurowych czy domowych.
Użycie funkcji w formule polega na wpisaniu jej nazwy wraz z listą argumentów (w nawiasach okrągłych). Niektóre funkcje wymagają podania konkretnej liczby argumentów, np. funkcja matematyczna SIN , służąca do obliczania wartości sinusa kąta, wymaga podania dokładnie jednego argumentu. Większość funkcji Excela operuje jednak na dowolnych zakresach komórek. Do takich funkcji należy m.in.
omawiana już funkcja MIN , którą można wywołać jako MIN(A1; A2; A3; A4) lub (z identycznym skutkiem) MIN(A1:A4), lub nawet MIN(A1:A3; A4) .
Funkcje w Excelu to temat niezwykle obszerny. Dlatego ograniczę się do omówienia sposobów efektywnego posługiwania się nimi na kilku przykładach.
6.3.1. Funkcje o określonej liczbie argumentów Rozpatrzmy następujący problem:
W kolumnie A dana jest pewna liczba kwot podanych z dokładnością do 1 grosza.
Zapisz w kolumnie B wartości kwot z kolumny A zaokrąglone do 1 zł.
Oto możliwe rozwiązanie tego zadania:
Zaznaczamy komórkę B1.
Klikamy przycisk ( Wstaw funkcję ) na pasku formuły. Powoduje to, że
program wejdzie w tryb definiowania formuły (w komórce B1 i polu formuły zostanie automatycznie wstawiony znak „ = ”);
na ekranie pojawi się okno dialogowe Wstawianie funkcji (Rys. 6.5).
wprowadzana
formuła ruchoma ramka definiująca zakres A1:E4
informacja o rozmiarze tabelki
informacja o
składni funkcji
Rysunek 6.4. Wprowadzanie formuły poprzez
zaznaczenie grupy komórek.
Wybieramy nazwę funkcji. Jeżeli znamy nazwę funkcji, wpisujemy ją w polu Wyszukaj funkcję . W przeciwnym wypadku w polu tym wpisujemy słowo kluczowe lub opis czynności, którą chcemy wykonać przy pomocy poszukiwanej funkcji. Ponieważ w naszym przypadku szukamy funkcji do zaokrąglania liczb, w polu tym możemy wpisać np. „zaokrąglić” lub
„zaokrąglanie”. Wpis kończymy, wciskając Enter lub klikając przycisk Prze- jdź .
W tym momencie program uaktualni listę funkcji w środkowej części okna – spośród ponad 200 możliwych funkcji wybierze tylko te, które są jakoś związane z wpisem dokonanym w polu Wyszukaj funkcję .
Przy pomocy klawiszy kursorów ( i ) przeglądamy skróconą listę funkcji, obserwując skrócone informacje o przeznaczeniu danej funkcji (pozycja 4a na Rys. 6.5).
Jeśli skrócony opis jest zbyt lakoniczny, możemy uzyskać pełną dokumentację poprzez kliknięcie podkreślonego napisu w lewym dolnym rogu okna.
Wybór funkcji kończymy kliknięciem przycisku OK . W tym momencie Excel wyświetli kolejne okno dialogowe – Argumenty funkcji (Rys. 6.6 i Rys. 6.7).
nazwa funkcji pierwszy argument drugi argument zwięzły opis
funkcji zwięzły opis argumentu
typ argumentu (białymi literami) miejsce na wynik
Rysunek 6.6. Widok okna dialogowego „Argumenty funkcji”.
pola edycji
argumentów przyciski minimalizacji okna dialogowego
1a. Tu wpisz słowo kluczowe lub opis czynności i wciśnij
Enter
1b. Ewentualnie z tej listy wybierz kategorię funkcji 2. Z tej listy
wybierz jedną funkcję 3. Odczytaj informację o argumentach
funkcji 4a. Odczytaj informację o przeznaczeniu
funkcji 4b. Klikając tu,
uzyskasz pełny opis funkcji
5a. Kliknij OK, by zatwierdzić swój
wybór 5b. Ewentualnie
Anuluj, jeśli się rozmyślisz
Rysunek 6.5. Posługiwanie się oknem dialogowym „Wstawianie
funkcji”.
Klikamy w polu edycji pierwszego argumentu i odczytujemy jego znaczenie w środkowej części okienka. Ponieważ argument Liczba ma mieć wartość A1, możemy wpisać „A1” wprost z klawiatury. Ale istnieje jeszcze jedna możliwość – zamiast wpisywać adres komórki, wystarczy kliknąć ją myszą. Spowoduje to automatyczne wprowadzenie adresu tej komórki do argumentu funkcji (Rys. 6.7). Metoda ta do złudzenia przypomina bezpieczne definiowanie formuł (por. Rozdział 5.8.2).
Klikamy pole edycji drugiego argumentu. Odczytujemy jego opis w centralnej części okna dialogowego i decydujemy się wpisać 0. Okno dialogowe powinno przybrać postać jak na Rys. 6.8 (a).
Klikamy OK . Powoduje to zamknięcie okna dialogowego Argumenty funkcji . Definicja komórki B1 jest gotowa.
Kopiujemy formułę z B1 do komórek B1:B5 metodą opisaną w punkcie 5.8.3.
Efekt naszych działań ilustruje Rys. 6.8 (b).
wartości argumentów wartość funkcji
(a)
(b)
Rysunek 6.8. (a) Okno „Argumenty funkcji” po zdefiniowaniu wszystkich argumentów funkcji ZAOKR;
(b) Ostateczny wygląd arkusza po zaokrągleniu liczb w kolumnie A.
1a. kliknij w polu edycji, by wybrać argument funkcji
3. kliknij w arkuszu, by wskazać komórkę
4. program sam wstawi adres klikniętej komórki
Rysunek 6.7. Kolejne fazy definiowania argumentu funkcji przy pomocy myszki.
1b. lub kliknij tu, jeśli okno dialogowe
„Argumenty funkcji”
zasłania zbyt duży fragment arkusza 2. odczytaj
definicję
argumentu
6.3.2. Zwijanie i rozwijanie okna dialogowego „Argumenty funkcji”
Po prawej stronie okienek argumentów w oknie dialogowym Argumenty funkcji znajduje się przycisk ( Zwiń okno dialogowe ), który służy do zmniejszenia rozmiaru okienka dialogowego i odsłaniania arkusza. Po jego kliknięciu okno redukuje się do pola edycji argumentu funkcji (Rys. 6.9). Aby wrócić do pełnego okna dialogowego, na zredukowanym okienku klikamy przycisk ( Rozwiń okno dialogowe ).
6.3.3. Funkcje typu SUMA
Oprócz funkcji wymagających podania określonej liczby argumentów liczbowych istnieją też takie, które przyjmują argumenty w postaci jednego lub kilku zakresów komórek. Do najczęściej używanych funkcji tego typu należy SUMA . Mając to na uwadze, twórcy Excela zaprojektowali wyjątkowo prosty sposób obliczania sum.
Przedstawię go, prezentując rozwiązanie następującego zadania:
Oblicz sumę liczb z komórek A1:A5 i zapisz wynik w komórce A7.
Rozpoczynamy od zaznaczenia komórki A7, w której ma pojawić się suma.
Na standardowym pasku narzędziowym klikamy symbol sumy ( ). Powoduje to wstawienie w komórce A7 formuły =SUMA(A1:A6) i otoczenie zaznaczonych komórek ruchomą ramką (Rys. 6.10). Konkretna postać argumentu sumy jest jednak tylko propozycją programu, który usiłuje domyślić się, sumę których liczb mieliśmy na myśli, klikając przycisk . Najczęściej Excel prawidłowo odczytuje nasze intencje, ale w razie czego możemy myszką zaznaczyć zupełnie inny zakres komórek, np. A1:A5 . (jak na Rys. 6.10).
Kończymy definiowanie sumy, przyciskając Enter .
W opisany tu sposób można obliczać zarówno sumy kolumn, jak i wierszy. Co więcej, klikając malutki trójkącik ( ) po prawej stronie przycisku , możemy
pole edycji argume
ntu
przycisk „rozwiń okno dialogowe”
Rysunek 6.9. Zredukowane okno dialogowe „Argumenty funkcji”.
1. kliknij przycisk
„oblicz sumę”
2. sprawdź poprawność zakresu i ewentualnie zmodyfikuj go
Rysunek 6.10. Zredukowane okno dialogowe „Argumenty funkcji”.
Rysunek 6.11. Menu przycisku obliczania sumy ().
wyświetlić specjalne menu tego przycisku (Rys. 6.11), a z niego błyskawicznie wybrać jedną z kilku innych popularnych funkcji. Znaczenie wszystkich pozycji menu jest jasne – na uwagę zasługuje jedynie Licznik . Otóż wbrew pozorom, pozycja ta nie służy do wyznacza licznika ułamka, lecz do wstawiania funkcji ILE.LICZB , która wyznacza ilość liczb w danym zakresie. Na przykład w sytuacji z Rys. 6.10 wartością ILE.LICZB(A1:C3) jest 3. Uwaga: przypominam, że Excel za liczby uważa też daty!
6.3.4. Funkcje finansowe
Oprócz wyznaczania prostych sum i iloczynów Excel potrafi też całkiem dobrze radzić sobie z problemami uważanymi za „trudne”. Rozpatrzmy następujący przykład praktyczny z dziedziny bankowości stosowanej:
Wyznacz wartość miesięcznej raty od kredytu mieszkaniowego w wysokości 100 000 zł zaciągniętego na 30 lat przy założeniu stałej (rocznej) stopy procentowej 8% i stałej wartości rat w okresie spłaty.
Rozpoczynamy od wpisania w komórkach B1, B2 i B3 kwoty kredytu, liczby lat spłaty i oprocentowania: B1=100 000 , B2=30 , B3=8% .
Przechodzimy do komórki B5 i na pasku formuły klikamy przycisk i w oknie dialogowym Wstawianie funkcji wybieramy funkcję PMT .
W kolejnym oknie dialogowym ( Argumenty funkcji ) wprowadzamy wartości kolejnych argumentów (por. Rys. 6.12):
Stopa=B3/12 (oprocentowanie roczne dzielimy przez 12, gdyż raty mają być spłacane 12 razy w roku);
Liczba_rat=B2*12 (30 lat * 12 miesięcy);
Wa=100 000 (wartość kredytu)
Pozostałych argumentów nie musimy wpisywać – Excel przyjmie, że mają wartość 0 (ale dobrze jest przeczytać opis tych argumentów, by wiedzieć, co te zera oznaczają).
Zamykamy okno Argumenty funkcji ( OK ) i z komórki B5 odczytujemy wynik:
–733,76 zł. Oznacza to, że stała miesięczna spłata 30-letniego kredytu o wartości 100 000 zł i oprocentowaniu 8% wynosi 733,76 zł. Jak łatwo obliczyć, suma wszystkich rat po 30 latach wyniesie 264 153,60 zł.
W tym momencie możemy docenić użyteczność arkusza kalkulacyjnego: dzięki użyciu ogólnych formuł odnoszących się do zawartości komórek możemy teraz bez trudu obliczyć wysokość spłaty dla dowolnego okresu kredytowania,
Rysunek 6.12. Argumenty funkcji PMT.
oprocentowania i wysokości kredytu. W tym celu wystarczy zmodyfikować wartości w komórkach B1:B3. Co więcej, wykorzystując naszą dotychczasową wiedzę moglibyśmy bez trudu skonstruować tabelę zawierającą kwoty spłat dla różnych kombinacji oprocentowania i okresu kredytowania.
6.3.5. Funkcja JEŻELI
Jedną z bardziej przydatnych funkcji Excela jest funkcja warunkowa JEŻELI . Wymaga ona podania trzech argumentów:
Test_logiczny to pewne wyrażenie o wartości PRAWDA lub FAŁSZ .
Wartość_jeśli_prawda to wartość funkcji JEŻELI , gdy wartością pierwszego argumentu jest PRAWDA .
Wartość_jeśli_fałsz to wartość funkcji JEŻELI , gdy wartością pierwszego argumentu jest FAŁSZ .
Funkcję tę wykorzystamy do rozwiązania następującego problemu:
Hurtownia zabawek udziela rabatu 1% przy zakupach na kwotę co najmniej 250 zł.
W kolumnie B tabeli wykaż wartości rabatu dla kwot zakupu podanych w kolumnie A.
Rozpoczynamy od zaznaczenia komórki B1, w której obliczymy kwotę rabatu dla kwoty wpisanej w A1.
Klikamy przycisk i w oknie dialogowym Wstawianie funkcji (Rys. 6.5) wybieramy funkcję JEŻELI . Można to osiągnąć na dwa sposoby:
wpisujemy w polu Wyszukaj funkcję słowo „jeżeli”;
lub wybieramy w polu Lub wybierz kategorię pole Logiczne ,
a następnie zaznaczamy w okienku Wybierz funkcję nazwę JEŻELI i klikamy OK .
W okienku Argumenty funkcj i dokonujemy następujących wpisów:
W polu Test_logiczny wpisujemy A1 >= 250 . Jest to warunek udzielenia rabatu.
W polu Wartość_jeśli_prawda wpisujemy wartość rabatu, czyli A1 * 1% .
W polu Wartość_jeśli_fałsz wpisujemy 0.
i klikamy OK (Rys. 6.13). W tym momencie Excel w komórce B1 wpisze formułę =JEŻELI(A1 >= 250;A1 *1%;0) (por. Rys. 6.14)
Rysunek 6.13. Okno „Argumenty funkcji” po wpisaniu wartości
argumentów funkcji JEŻELI w przykładzie z punktu 6.3.5.
Zaznaczamy komórkę B1 i kopiujemy znajdującą się w niej formułę do pozostałych komórek kolumny B (metodą opisaną w rozdziale 5.8.3).
Spowoduje to wyznaczenie wszystkich poszukiwanych kwot rabatu (Rys. 6.14).
Do porównywania wartości można stosować operatory relacyjne opisane w Tabeli 6.1.
operato
r nazwa przykład
= znak równości JEŻELI(A1=0, 1% ,
0%)
< znak mniejszości JEŻELI(A1<0, 1% , -1%)
<= znak „mniejsze lub równe” JEŻELI(A1<=0, 1% , -1%)
> znak większości JEŻELI(A1>0, 10% , 5%)
>= znak „większe lub równe” JEŻELI(A1>=0, 3% , 0%)
<> znak nierówności JEŻELI(A1<>B1, 0 , 1)
Tabela 6-1. Operatory relacyjne (porównawcze) Excela.
6.3.6. Przegląd pozostałych funkcji
Jak już wspomniałem, Excel udostępnia ponad 200 różnych funkcji i wszelka próba opisania ich wszystkich byłaby pozbawiona sensu. Tym niemniej należy mieć świadomość, jakiego rodzaju funkcjami w ogóle możemy się posługiwać i gdzie znaleźć ich opis.
Funkcje dostępne w Excelu można podzielić na:
Finansowe . Funkcje te służą do obliczania wszelkiego rodzaju odsetek, amortyzacji, lokat bankowych, wewnętrznych stóp zwrotu, wartości inwestycji itp.
Daty i czasu . Funkcje te służą do manipulowania datami i czasem. Na przykład funkcja DZIŚ wstawia w danej komórce bieżącą datę, a wynikiem funkcji TERAZ jest bieżąca data i godzina z dokładnością do minuty. Wartości pól zawierających wywołania tych funkcji są aktualizowane każdorazowo przy otwieraniu dokumentu oraz po naciśnięciu klawisza F9 .
Matematyczne. Znajdziemy tu takie funkcje, jak SIN (sinus kąta), ASIN
(funkcja odwrotna do SIN ), LOS (liczba losowa), PIERWIASTEK (pierwiastek
Rysunek 6.14. Tabela z kwotami rabatu wyznaczonymi przy pomocy
funkcji JEŻELI.
kwadratowy), SUMA (suma liczb z zakresu), ILOCZYN (iloczyn liczb z zakresu), ZAOKR (zaokrąglanie liczb), RZYM (konwersja liczby na zapis rzymski).
Statystyczne. Do tej kategorii należą funkcje obliczające wartość średnią, medianę, kowariancję, odchylenie standardowe, funkcje wyznaczające trend danych (m.in. liniowy i wykładniczy), funkcje wyznaczające punkty rozkładów statystycznych (np. normalnego), funkcje do badania niezależności rozkładów itp.
Wyszukiwania i adresu. Te funkcje mogą się przydać tylko bardzo zaawansowanym użytkownikom.
Bazy danych. Znajdziemy tu funkcje służące do pobierania informacji z baz danych, np. ilości rekordów w bazie, sumy liczb w rekordach itp.
Tekstowe. Do tej kategorii należą funkcje obliczające długość napisu, konwertujące liczby na tekst itp.
Logiczne. Znajdziemy tu następujące funkcje: FAŁSZ , PRAWDA , JEŻELI , LUB , NIE , ORAZ .
Dokładny opis wszystkich funkcji rozpoznawanych przez program znajduje się w elektronicznym podręczniku programu w rozdziale Kompendium funkcji .
6.4. Formaty liczbowe komórek
6.4.1. Definiowanie formatów liczbowych
Jak wiemy, zawartość komórek Excela dzieli się na dwie podstawowe kategorie:
tekst i liczby, przy czym Excel jako liczby traktuje nie tylko „zwykłe” liczby (np.
120 czy 11,2 ), ale i kwoty pieniężne (np. 123 zł , 10 € ), wielkości procentowe (np.
25% , 200% ) i daty (np. 2005-12-25 ). W różnych sytuacjach może zachodzić potrzeba wyświetlania tej samej liczby inaczej. Na przykład kwoty pieniężne można chcieć zaokrąglać do grosza, złotówki lub tysiąca złotych.
Format liczbowy komórki to wskazówka dla programu odnośnie pożądanego
sposobu wyświetlania (i drukowania) wartości tejże komórki. Ustala się go na karcie
Liczby okna dialogowego Formatuj komórki (Rys. 6.15). Okno to wyświetla się
kombinacją Ctrl+1 lub poprzez wybranie z menu głównego FormatKomórki .
Istnieje aż 12 różnych formatów komórek. Oto ich zwięzły opis.
Ogólne. Liczby w tym formacie nie są formatowane w żaden specjalny sposób.
Wybranie tego formatu oznacza w praktyce brak jakiegokolwiek formatowania.
Liczbowe. Podstawowy format do wyświetlania liczb. Umożliwia zdefiniowanie separatora tysięcy, określenie liczby miejsc po przecinku w wyświetlanej liczbie i specjalne potraktowanie liczb ujemnych. Patrz: format walutowe .
Walutowe. Służy do wyświetlania kwot pieniężnych. Umożliwia zdefiniowanie symbolu waluty, określenie liczby miejsc po przecinku w wyświetlanej kwocie i specjalne potraktowanie liczb ujemnych (Rys. 6.16). W zasadzie opcje są jasne – wyjaśnienia wymaga jedynie kwestia liczb ujemnych. Otóż w formacie walutowym wartości ujemne można wyświetlać „normalnie”, albo w kolorze czerwonym, albo w kolorze czerwonym bez znaku „minus”.
Księgowe. Format podobny do poprzedniego; główna różnica polega na tym, że ewentualny znak minus i symbole niektórych walut (np. dolara) są tu wyrównywane do lewej krawędzi komórki.
Data. Excel przechowuje daty jako liczby całkowite. Liczba 1 odpowiada dacie 1 stycznia 1900 r., liczba 2 – dacie 2 stycznia 1900 r. i tak dalej; na przykład data 1 maja 2005 roku przechowywana jest przez Excel jako liczba 38 463.
Oznacza to, że od 31 grudnia 1899 roku do 1 maja 2005 r. minęło 38 463 dni
1. Dane w formacie Data mają też określony typ, który określa sposób wyświetlania roku, miesiąca i dnia (na przykład ta sama data może być
wyświetlana m.in. jako
2001-01-20 lub 20 sty 2001 ). Zakres poprawnych dat mieści się pomiędzy 1 marca 1900 r. i 31 grudnia 9999 r.
1