Arkusz kalkulacyjny pozwala na bezpośrednie zapisywanie na ekranie monitora wzorów, wg których wykonywane są obliczenia na bieżąco wyświetlając na tym samym ekranie wyniki otrzymane dla konkretnych danych metodą wpisaną w arkusz.
Zmiana wartości jednej z danych powoduje automatyczne przeliczenia wszystkich zależnych od niej wielkości. Pliki arkusza to zeszyty podzielone na pojedyncze arkusze. Zbudowane są z komórek, których kolumny oznaczone są literami alfabetu (od A do IV) i rzędów, ponumerowanych kolejno od 1 wzwyż. Tworzą one współrzędne, za pomocą których określa się adres komórki.
Komórka może zawierać wartość liczbową, wyrażenie arytmetyczne, tekst, formuły i funkcje. Punktem odniesienia na ekranie jest kursor znajdujący się w komórce, której wartość można w danej chwili zmieniać. Komórka ta nazywa się komórką bieżącą i jest zaznaczona grubszą ramką. Jej zawartość wyświetlona jest na pasku formuły.
Formuła to zestaw działań i adresów komórek, które zawierają wartości liczbowe, zwracające wynik wykonywanego obliczenia. Treść formuły poprzedza znak =. Formuły są używane do wykonywania obliczeń.
NP.: =(D2/A1)*100
Arkusz wyposażony jest w ponad 200 rozmaitych funkcji (matematycznych, statystycznych, finansowych, itd.). Wchodzą one również w skład formuł.
NP.: =SUMA(F1:F90)
Przycisk SUMA na pasku narzędziowym to najczęściej używana funkcja sumująca wartości w określonym obszarze – autosumowanie.
Jeśli wartość liczbowa nie mieści się w komórce, program zamienia ją na ####. Należy wówczas dopasować szerokość kolumny lub zmniejszyć czcionkę.
Liczby w arkuszu mogą mieć określony format:
ogólny, walutowy, procentowy, daty i czasu, księgowy, ułamkowy lub niestandardowy.
Wielkie liczby wyświetlane są w postaci wykładniczej (naukowej).
NP.: 123 456 789 = 1,23E+08
W arkuszu kalkulacyjnym wykonuje się operacje blokowe tak jak w edytorze tekstów. Początek bloku oznacza adres komórki znajdującej się w lewym górnym rogu zaznaczonego obszaru, a koniec to adres komórki w prawym dolnym rogu tego obszaru (A1:G9)
Razem z zawartością komórek jest przenoszony (kopiowany) ich format oraz formuły. Wraz z tą operacją zmieniają się adresy niektórych komórek (tzw. adresy względne). Wprowadzenie znaku $(adres bezwzględny) zabezpieczy przed niepożądanymi zmianami.
NP.: =$A$2*B1
WYBRANE FUNKCJE ARKUSZA KALKULACYJNEGO EXCEL
FUNKCJE MATEMATYCZNE:
Za pomocą funkcji tego typu możemy wykonywać proste, jak i złożone obliczenia matematyczne.
Do kategorii tej należą także funkcje, które umożliwiają zaokrąglanie liczb oraz przeprowadzanie działań na funkcjach trygonometrycznych. Poniżej zaprezentowane są przykłady:
SUMA() – umożliwia sumowanie argumentów z podanego zakresu
np. SUMA(A3:A20) Można ją także wywołać korzystając z paska narzędzi Standardowy i przycisku sigma. Liczba argumentów w nawiasach nie może przekraczać trzydziestu. Poszczególne argumenty oddzielamy od siebie za pomocą operatorów(;:), np. SUMA (A3:A20; B3:B50).
SUMA.JEŻELI() – wykonuje opcję sumowania komórek w podanym zakresie, gdy komórki spełniają podane kryteria. Jej składnia jest następująca:
SUMA.JEŻELI (Zakres;Kryteria;Suma:Zakres)
Na przykład formuła: =SUMA.JEŻELI (A1:A5;”>1000″;B1:B5) oznacza, że tylko te komórki z zakresu B1:B5 będą sumowane, dla których odpowiedniki z zakresu A1:A5 będą większe od l000.
ILOCZYN() – umożliwia mnożenie argumentów ze wskazanych zakresów np. ILOCZYN(A3:A7). Może ona posiadać do trzydziestu argumentów, podobnie jak funkcja SUMA().
MODUŁ.LICZBY()-zwraca wartość bezwzględną z podanej liczby, np. gdy w komórce A5 wpisaliśmy liczbę -345, to formuła: = MODUŁ. LICZBY(A5) zwróci liczbę 345.
PIERWIASTEK() – zwraca pierwiastek kwadratowy z argumentu liczbowego (dodatniego), np. gdy w komórce A5 znajduje się liczba 256, to formuła: =PIERWIASTEK(A5) oblicza wartość 16.
LICZBA.CAŁK() – zwraca część całkowitą podanej liczby, np. gdy w komórce A5 znajduje się liczba 234,56 to formuła: =LICZBA.CAŁK(A5) oblicza wartość 234.
ZAOKR() – umożliwia zaokrąglanie liczby w podanej komórce do wskazanej liczby miejsc po przecinku, np. gdy w komórce A5 znajduje się liczba 234,4567 to formuła: = ZAOKR(A5;2) zwróci liczbę 234,46.
ZAOKR.DO.CAŁK() – funkcja eliminuje wszystkie cyfry znajdujące się po przecinku liczby dziesiętnej, zaokrąglając do najbliższej całkowitej, np. gdy w komórce A5 znajduje się liczba 234,56 to formuła: =ZAOKR.DO.CAŁK(A5) zwróci wartość 235.
ZNAK.LICZBY() – zwraca wartość l gdy liczba jest dodatnia, O gdy jest zerem i -l gdy jest liczbą ujemną, np. gdy w komórce A5 znajduje się liczba 10, to formuła =ZNAK.LICZBY(A5) zwraca wartość l.
POTĘGA() – podaje wartość liczby podniesionej do danej potęgi, np. gdy w komórce A5 znajduje się liczba 2 i chcemy ją podnieść do potęgi trzeciej, to formuła: =POTĘGA(A5;3) zwraca wartość 8.
STOPNIE() – przekształca wartość kąta w radianach na wartość kąta w stopniach, np. formuła: =STOPNIE(3,141593) oblicza wartość 180°.
RADIANY() – przekształca wartość kąta w stopniach na wartość kąta w radianach, np. formuła: =RADIANY( 180) oblicza wartość 3,141593 radiana.
PI() zwraca wartość stałej ? tzn. 3,141593.
SIN() – funkcja zwraca wartość sinusa kąta podanego w radianach, np. SIN(PI) zwraca wartość 0. Jeżeli wartość kąta podajemy w stopniach to należy jego wartość przy pomocy funkcji RADIANY() przekształcić na radiany, np. formuła: SIN(RADIANY(30)) oblicza wartość 0,5.
COS() – funkcja zwraca wartość cosinusa kąta podanego w radianach, np.
COS (PI()) zwraca wartość l. Jeżeli wartość kąta podajemy w stopniach, to należy jego wartość przy pomocy funkcji RADIANY(), przekształcić na radiany, np. formuła: =COS(RADIANY(60)) zwraca wartość 0,5.
TAN() – funkcja zwraca wartość tangensa kąta w radianach, np. TAN(0,785) zwraca wartość 0,99920. Jeżeli wartość kąta podajemy w stopniach, to należy jego wartość, przy pomocy funkcji RADIANY(), przekształcić na radiany, np. formuła: =TAN (RADIANY(45)) oblicza wartość l.
Przykład 1 – Funkcja PIERWIASTEK
1. W skoroszycie Przykłady funkcji matematycznych, w arkuszu Arkusz1 wpisz do komórki A7 formułę: =PIERWIASTEK(A8).
2. W komórce A8 wpisz liczbę 16.
3. W komórce A7 pojawiła się wartość 4.
4. Wpisz do komórki A8 liczbę -16.
W komórce A7 pojawi się wartość #LICZBA! W ten sposób Excel informuje o błędnym argumencie funkcji, gdyż nie istnieje pierwiastek z liczby ujemnej.
5. Sprawdź działanie funkcji, wpisując do komórki A8 inne liczby dodatnie.
6. Zapisz zmiany w skoroszycie.
Ćwiczenie 1
W komórce C7 wpisz formułę: =PIERWIASTEK (MODUŁ.LICZBY (C8)). W komórce C8 wpisz liczbę -16. Dlaczego tym razem Excel nie wyświetlił informacji o błędzie?
FUNKCJE LOGICZNE:
Funkcje logiczne umożliwiają sprawdzanie jednego lub wielu warunków i w zależności od wyniku sprawdzenia zwracają jedną z dwóch wartości: wartość logiczną PRAWDA (l) lub wartość logiczną Fałsz(0). Każdą z wartości można opisać dowolnym działaniem, które Excel podejmie, gdy wartość ta wystąpi.
Oto przykłady funkcji:
JEŻELI(Warunek;Wartość_Gdy_Prawda;Wartość_Gdy_Fałsz) – funkcja sprawdzająca określony Warunek. Jeżeli jest on prawdziwy, to wykonywana jest sekcja Wartość_Gdy_Prawda, natomiast gdy jest on fałszywy, to wykonywana jest sekcja Wartość_Gdy_Fałsz.
Na przykład, formuła typu: =Jeżeli(A1=5;C1/A1;D1*A1) działa w ten sposób, że jeżeli warunek Al=5 jest prawdziwy, to w komórce, w której wpisaliśmy formułę obliczany będzie iloraz C1/A1. Natomiast w przypadku, gdy warunek jest fałszywy, to w komórce tej będzie obliczany iloczyn D1*A1. Warunek oraz poszczególne sekcje oddzielamy od siebie średnikami.
ORAZ() – funkcja jest często łączona z funkcją JEŻELI, gdy konieczne jest podjęcie decyzji uzależnionej od jednoczesnego spełnienia wszystkich warunków. Składnia funkcji jest następująca: ORAZ(Warunekl;Warunek2;Warunek3;…Warunek30).
Np. formuła wpisana do komórki B10: =JEŻELI(ORAZ(Al=0;A2=0);”NIE”;”TAK”), działa w ten sposób, że tylko wtedy, gdy komórka A1=0 i A2=0, w komórce B10 pojawi się napis NIE, w przeciwnym wypadku pojawi się napis TAK.
LUB() – funkcja jest często łączona z funkcją JEŻELI, gdy podjęcie decyzji uzależnione jest od spełnienia przynajmniej jednego z warunków. Składnia funkcji jest następująca: =LUB(Warunekl;Warunek2;Warunek3; …Warunek30). Na przykład, wpisana do komórki B10 formuła typu: =JEŻELI (LUB(A1=0;A2=0); “NIE”; “TAK”), wyświetla w tej komórce tekst NIE, gdy przynajmniej jedna z komórek będzie zawierała wartość 0. W przeciwnym wypadku, w komórce pojawi się tekst TAK.
NIE() – funkcja powoduje negację warunku i jest zazwyczaj używana w połączeniu z innymi funkcjami np. =JEŻELI (NIE(A5=2; “Zdał”; “Nie zdał”) ).
PRAWDA() i FAŁSZ() – funkcje zwracaj ą wartość logiczną PRAWDA lub FAŁSZ. Są to funkcje bezargumentowe i używane są zazwyczaj w połączeniu z innymi funkcjami, np. =JEŻELI (SUMA(Al:A5) >100;PRAWDA; FAŁSZ).
Na poniższym przykładzie pokazany zostanie sposób wykorzystania funkcji JEŻELI() do sprawdzenia, czy do określonej komórki arkusza została wpisana niewłaściwa wartość liczbowa. W zależności od wyniku sprawdzenia, zostanie wykonane działanie lub wyświetlony tekst komunikatu.
Przykład 2 – Zastosowanie funkcji JEŻELI
l. Otwórz nowy skoroszyt w programie Excel. Zapisz skoroszyt po nazwą Przykłady funkcji logicznych. Jako arkusz roboczy wybierz Arkuszl.
2. Wpisz do podanych poniżej komórek następujące wartości:
Al: 10, A2: 0
3. Do komórki A3 wpisz następująca formułę: =Jeżeli(A2=0;”Niemożliwe jest dzielenie przez zero (Al/A2).
W komórce A3 pojawi się komentarz “Niemożliwe jest dzielenie przez zero”, ponieważ A2=0 – warunek jest prawdziwy.
4. Wpisz do komórki A2 liczbę 5. W komórce A3 pojawi się wartość 2, ponieważ A2=5 to warunek jest fałszywy.
5. Zapisz zmiany w skoroszycie.
Jak można było zauważyć, za pomocą funkcji JEŻELI() dla każdego wyniku warunku można wyświetlać w komórkach komentarze tekstowe (tekst komentarza powinien być ujęty w cudzysłów) lub wykonywać działania.
Ćwiczenie
Utwórz formułę, która oblicza pierwiastek kwadratowy z podanej liczby. Za pomocą funkcji JEŻELI() wyświetl tekst komentarza o błędnej danej, gdy liczba pierwiastkowana jest mniejsza od zera.
W poniższym przykładzie utworzona zostanie formuła sprawdzająca, czy w dwóch wybranych komórkach wpisane są dane. W formule wykorzystane zostało połączenie dwóch funkcji: JEŻELI() i ORAZ().
Przykład 3 – Zastosowanie funkcji ORAZ
1. W skoroszycie z przykładu powyżej, w arkuszu Arkusz1 wpisz do komórki A5 wartość liczbową 10.
2. W komórce A7 wpisz formułę: =JEŻELI (ORAZ (A5<>” “;A6<>” “) ;”Każda komórka z zakresu AA zawiera dane”; “Przynajmniej jedna z komórek w podanym zakresie jest pusta”). Pojawi się tekst: “Przynajmniej jedna z komórek w podanym zakresie jest pusta”.
3. Wpisz do komórki A6 liczbę 100. Pojawi się tekst: “Każda komórka z zakresu AA zawiera dane”.
4. Zapisz zmiany w skoroszycie.
FUNKCJE TEKSTOWE
Zapis typu ” ” oznacza tzw. pusty łańcuch znaków. Przy jego pomocy możemy sprawdzać, czy dana komórka zawiera jakaś daną. Za pomocą zapisu typu “” możemy także wstawiać do danej komórki pusty łańcuch znaków. Wpisana do komórki B20 formuła typu: =JEŻELI(B24>0;SUMA(C1:C3) ;” “), działa w ten sposób, że gdy warunek jest spełniony, to obliczana jest suma z podanego zakresu i wynik sumy jest wyświetlany w komórce B20, w przeciwnym wypadku komórka wyświetla pusty ciąg znaków – czyli nic.
Za pomocą funkcji tekstowych można sterować ciągami tekstowymi w formułach. Na przykład można zmienić wielkość liter albo ustalić długość ciągu tekstowego. Przykłady funkcji:
DL() – funkcja zwraca długość łańcucha znaków w podanym tekście w postaci liczby całkowitej np. formuła: =DL(“NAZWISKO”) zwraca wartość 8.
PORÓWNAJ() – funkcja umożliwia porównanie dwóch tekstów, przy uwzględnieniu wielkości liter. Jeżeli porównywane teksty są jednakowe to zwraca wartość logiczną PRAWDA, w przeciwnym przypadku zwraca wartość FAŁSZ. Składnia funkcji jest następująca: =PORÓWNAJ(Tekstl;Tekst2).
POWT() – funkcja pozwala wypełnić daną komórkę określoną liczbę razy podanym znakiem ujętym w cudzysłów. Składnia funkcji jest następująca: POWT(“Znak”;IleRazy).
LITERY.MAŁE() i LITERY.WIELKIE() – funkcje pozwalają dokonać konwersji tekstów w podanej komórce na teksty pisane małymi bądź dużymi literami. Składnia tych funkcji jest następująca: LITERY.MAŁE(Tekst) i LITERY.WIELKIE(Tekst).
Z.WIELKIEJ.LITERY() – funkcja zamienia zawsze pierwszą literę wpisanego tekstu na dużą. Składnia funkcji jest następująca: =Z.WIELKIEJ.LITERY(Tekst).
FUNKCJE INFORMACYJNE
Funkcje tego typu są przeznaczone do ustalania typu danych przechowywanych w komórce. Najpopularniejszymi z tej grupy są funkcje typu CZY. Przykłady funkcji:
CZY.BŁ();CZY.BŁĄD;CZY.BRAK – funkcje umożliwiają wykrycie w arkuszu błędów. Mają one następującą postać: CZY.BŁ (Wartość), CZY.BŁĄD(Wartość), CZY.BRAK(Wartość). Argumentem tych funkcji może być wartość, wyrażenie, tekst, adres lub zakres komórek Funkcja CZY.BŁ() może być zastosowana do wykrywania wszystkich kodów błędu w programie Microsoft Excel (z wyjątkiem kodu #N/D). Funkcja CZY.BŁĄD() wykrywa wszystkie kody błędu, natomiast funkcja CZY.BRAK() wykrywa tylko kody #N/D. W tabeli poniżej podano najczęściej występujące błędy w arkuszu kalkulacyjnym Excel.
Kody wybranych błędów i ich opis w programie Excel:
KOD BŁĘDU
ZNACZENIE
#DZIEL/0!
Próba dzielenia przez zero.
# N/D!
W komórce argumentu brak danych.
#NAZWA!
Nazwa użyta w formule jest nieprawidłowa, lub nie można jej znaleźć.
#ZERO!
Wpisane zakresy komórek nie mają wspólnych komórek.
#LICZBA!
Wpisany został błędny argument, na którym funkcja nie potrafi działać.
#ARG!
Zakres danych został niewłaściwie użyty w formule.
#ADR!
Formuła zawiera adres nieistniejącej komórki.
ZWARTOŚĆ!
Formuła używa niewłaściwego argumentu.
Ćwiczenie
1. Do komórki o adresie A4 wpisz następującą formułę: =JEŻELI(CZY.BŁĄD(A3);”Niewłaściwe dane w komórkach lub w komórce A2 wpisano zero”;” “).
Ponieważ dane są poprawne, funkcja CZY.BŁĄD() zwróciła wartość Fałsz – pusty ciąg znaków.
2. Wpisz do komórki A2 zamiast wartości 2 liczbę 0.
Ponieważ jedna z danych jest niewłaściwa, funkcja CZY.BŁĄD() zwróciła wartość Prawda – tekst “Niewłaściwe dane w komórkach lub w komórce A2 wpisano zero”.
3. Wpisz do komórki A2 zamiast wartości O dowolny tekst, np. zero. Ponieważ i w tym przypadku jedna z danych jest niepoprawna, funkcja CZY.BŁĄD() zwróciła wartość Prawda – tekst “Niewłaściwe dane w komórkach lub w komórce A2 wpisano zero”.
4. Zapisz zmiany w skoroszycie.
FUNKCJE DATY I CZASU
Za pomocą tych funkcji można analizować dane typu data i czas oraz tworzyć formuły. Można także dokonywać na nich operacji arytmetycznych. Oto przykłady funkcji:
TERAZ() – funkcja bezargumentowa, która zwraca aktualną datę i godzinę.
DZIŚ() – funkcja bezargumentowa, która zwraca aktualną datę.
DATA() – funkcja pozwala na wpisanie daty w komórce według następującej składni: =DATA (Rok;Miesiąc;Dzień), co umożliwia dokonywanie obliczeń na datach np. dodawanie dni.
CZAS() – funkcja pozwala na wpisanie czasu w komórce według następującej składni: =CZAS(Godzina;Minuta; Sekunda), co umożliwia dokonywanie obliczeń, np. dodawanie minut.
FUNKCJE STATYSTYCZNE
Są to funkcje przeznaczone do wykonywania analizy statystycznej na zakresach danych w arkuszach. Przykłady funkcji:
ILE.NIEPUSTYCH() – funkcja zwraca liczbę niepustych komórek z podanego zakresu w postaci liczby całkowitej. Składnia funkcji: ILE.NIEPUSTYCH (ZAKRES).
MAX() – funkcja zwraca maksymalną wartość z pośród podanych argumentów. Składnia funkcji: =MAX(Argumentl;Argument2;…Argument30).
MIN() – funkcja zwraca minimalną wartość spośród podanych argumentów. Składnia funkcji: =MIN(Argumentl;Argument2;…Argument30).
ŚREDNIA() – funkcja zwraca wartość średniej arytmetycznej podanych argumentów. Składnia funkcji: =ŚREDNIA(Argumentl;Argument2;…Argument30).
UWAGA
Argumenty funkcji wpisuje się zawsze w nawiasach ( ).
W przypadku funkcji bezargumentowych zostają puste nawiasy. Np.: PI().
Nazw funkcji, nawiasów i argumentów nie rozdziela się spacjami. Zakres komórek, to także argument funkcji.
Zadanie 2
