Arkusz kalkulacyjny (spreadsheet) to program komputerowy służący do przechowywania dużej ilości danych liczbowych. Umożliwia szybkie (automatyczne) wykonywanie rachunków, uaktualnianie i porządkowanie danych, a także przedstawianie ich w postaci graficznej. Arkusz kalkulacyjny jest doskonałym narzędziem do sporządzania zestawień finansowych i statystycznych oraz różnego rodzaju kalkulacji (np. list płac). Ponadto umożliwia tworzenie modeli i dokonywanie symulacji. W zaawansowanych arkuszach kalkulacyjnych dostępne są języki makropoleceń i języki programowania, uelastyczniające przetwarzanie danych. Pierwowzorem arkuszy kalkulacyjnych był arkusz o nazwie VisiCalc zaprojektowany (przez Dana Bricklina i Boba Frankstona)w 1978 r. dla komputerów Apple II. Odniósł on błyskawiczny sukces oraz spowodował popyt na komputery marki Apple. Była to pierwsza aplikacja, która zyskała miano killer application, a więc oprogramowania, dla którego klienci kupowali cały komputer.
Przykłady arkuszy kalkulacyjnych: Microsoft Excel(z pakietu MS Office);Quattro Pro (firmy Corel) z pakietu WordPerfect Office; Lotus 1-2-3(firmy Lotus Development Corporation) z pakietu Lotus SmartSuite;OpenOffice.org Calc(firmy Sun Microsystems) z pakietu OpenOffice.org. Struktura arkusza kalkulacyjnego
Arkusz kalkulacyjny posiada strukturę tabeli złożonej z kolumn i wierszy. Kolumny oznaczone są literami alfabetu, zaś wiersze kolejnymi liczbami całkowitymi. Na przecięciu każdej kolumny i wiersza znajduje się pojedyncza komórka. Jest ona jednoznacznie identyfikowana dzięki swojemu adresowi. Komórka arkusza - elementarna jednostka struktury arkusza. Każda komórka ma swój adres (unikalny) Adres komórki - składa się z nazwy kolumny i nazwy wiersza (bez spacji) na przecięciu których, komórka się znajduje, np. D5. Zakres komórek - jest to pewien obszar arkusza wyznaczony przez adres pierwszej i ostatniej komórki wchodzącej w jego skład. Zakres komórek zapisujemy rozdzielając adresy komórek znakiem dwukropka (:) np. A1:D7 (lub w niektórych arkuszach dwiema kropkami np. A1..D7)
Do komórek arkusza można wprowadzać wartości stałe (liczby, tekst) a także formuły. Arkusz kalkulacyjny daje możliwość generowania wzorów matematycznych (formuł), w których występują różne działania arytmetyczne.
Wprowadzanie formuły rozpoczyna się od wpisania specjalnego znaku, wskazującego, że to co za nim stoi jest wzorem matematycznym a nie zwykłym tekstem. Zazwyczaj będzie to znak = (równości), choć w niektórych arkuszach może nim być znak + a czasem @.
Formuły obliczeniowe umożliwiają zapisanie w arkuszu działań, które mogą być wykonywane na danych.
Operatory arytmetyczne stosowane w formułach
+ operator dodawania
- operator odejmowania
* operator mnożenia
/ operator dzielenia
^ operator potęgowania
Arkusz kalkulacyjny daje możliwości wykorzystania wbudowanych funkcji obliczeniowych, które automatyzują i przyspieszają wiele operacji na danych zgromadzonych w arkuszu. Funkcje - narzędzie obliczeniowe służące do automatycznego przeprowadzania operacji na argumentach notacja: nazwa_funkcji (argumenty)
Przykłady podstawowych funkcji (z arkusza Microsoft Excel)
=SUMA(argumenty)- funkcja obliczającą sumę podanych argumentów. Mogą być one podawane jako zakres lub (i) rozdzielane średnikami; np.=SUMA(A1:A8) – zsumuje wszystkie liczby znajdujące się w podanym zakresie
=SUMA(A1;A2;8;B2:B5) – doda liczby z komórek A1,A2 następnie liczbę 8 i wartości liczbowe z zakresu B2:B5
=MIN(argumenty) - funkcja zwracająca najmniejszą wartość z podanych argumentów. Mogą być one podawane jako zakres lub (i) rozdzielane średnikami; np. =MIN(A1:A8)
=MAX (argumenty) - funkcja zwracająca największą wartość z podanych argumentów. Mogą być one podawane jako zakres lub (i) rozdzielane średnikami; np. =MAX(A1:A8)
=ŚREDNIA(argumenty)
=AVERAGE(argumenty) - funkcja zwracająca wartość średnią arytmetyczną podanych argumentów. Mogą być one podawane, jako zakres lub (i) rozdzielane średnikami; np. =ŚREDNIA(A1:A8)
=JEŻELI (warunek logiczny; operacja jeżeli prawda; operacja jeżeli fałsz)
=IF(logical test; value if true; value if false) - funkcja sprawdza podany warunek logiczny i wykonuje jedną operację (zwraca wartość) jeżeli warunek jest spełniony (prawda) lub inną jeżeli warunek nie jest spełniony (fałsz) np. =JEŻELI(B2<>0;A2/B2;”niewykonalne”)
W funkcjach warunkowych wykorzystywane są operatory porównań, za pomocą których można przeprowadzać porównania dwóch wartości. Gdy dwie wartości są porównywane za ich pomocą, to wynik jest wartością logiczną — albo PRAWDA (True), albo FAŁSZ (False.
Operatory porównań stosowane w formułach
= równe
> większe niż
< mniejsze niż
>= większe lub równe
<= mniejsze lub równe
<>różne od
Jeżeli chcemy użyć w formule ciągu tekstowego należy umieścić go w cudzysłowie („tekst”). Zapis typu " " oznacza tzw. pusty łańcuch znaków. Przy jego pomocy możemy sprawdzić czy dana komórka zawiera jakąś daną lub wstawić do niej pusty łańcuch znaków.
Operatory tekstowe stosowane w formułach
& łącznik dowolnych tekstów
W arkuszach kalkulacyjnych można wyróżnić trzy typy adresowań komórek: względny, bezwzględny i mieszany.
Sposób adresowania komórek ma bezpośredni wpływ na kopiowanie formuł z jednej komórki do innych. Przy kopiowaniu formuł zawierających zmienne w postaci adresów, brana jest pod uwagę zmiana położenia kopii komórek w stosunku do ich źródeł. Jeżeli np. kopia w stosunku do źródła zmieniła swoje położenie o dwie kolumny i dwa wiersze, to adresy w komórkach skopiowanych zmienią się według takiego samego klucza. Jeżeli chcemy aby adres komórki (lub jego część) nie został zaktualizowany (zmieniony) podczas kopiowania formuły stosujemy adresowanie bezwzględne (lub mieszane). Adresowanie względne - w adresie względnym nie występują inne znaki niż nazwa kolumny i wiersza na przecięciu których, komórka się znajduje. Podczas kopiowania formuł zawierających adresy względne zostają one automatycznie aktualizowane w zależności od komórki, do jakiej są skopiowane (np. G3) Adresowanie bezwzględne - w adresie bezwzględnym zarówno przed nazwa kolumny jak i wiersza umieszcza się znak $. Adresy bezwzględne pozostają niezmienione w wyniku kopiowania (np. $G$3). Adresowanie mieszane - jest kombinacją dwóch poprzednich sposobów adresowania. W zapisie adresu komórki tylko nazwa kolumny lub tylko nazwa wiersza poprzedzona jest znakiem $ (np. $G3 lub G$3). Taki zapis zapewnia niezmienność nazwy kolumny lub wiersza podczas kopiowania formuły zawierającej adresy mieszane.
Aby wyświetlić wszystkie formuły znajdujące się w komórkach wystarczy wcisnąć kombinację klawiszy CTRL+`(słaby akcent, nad klawiszem Tab)
Aby szybko zaznaczyć wszystkie komórki zawierające formuły należy wybrać: karta Narzędzia główne → grupa Edytowanie → przycisk polecenia Znajdź i zaznacz → polecenie Formuły.
W celu jednoczesnego manipulowania arkuszami na raz np. formatowania, wpisywania danych, budowania formuł stosuje się grupowanie arkuszy.
Niektóre błędy (kody błędów) zwracane przez arkusz kalkulacyjny podczas używania formuł.
#ARG! (#VALUE!) - pojawia się, gdy użyjemy niewłaściwego typu argumentu
#DZIEL/0!(#DIV/0!) - pojawia się, gdy w formule występuje próba dzielenia przez zero.
#NAZWA?(#NAME?) - nazwa użyta w formule jest nieprawidłowa lub nie można jej znaleźć
### - liczba nie mieści się w komórce
Liczbę ujemną poprzedza się znakiem -{minus} lub zapisuje w nawiasie
Do tworzonego arkusza można wprowadzać dane z klawiatury, kopiować poprzez schowek, ale również można importować dane zewnętrzne (np. z pliku programu Access, pliku tekstowego, czy też strony WWW).
Karta Dane → grupa Dane zewnętrzne
Zaimportowane w ten sposób dane są połączone ze swoim źródłem, tzn., że ich zmiana w miejscu źródłowym może być (automatycznie lub ręcznie) odzwierciedlona w arkuszu zawierającym zaimportowane dane (dane zostaną zaktualizowane). Formatowanie warunkowe - to automatyczne nadawanie przez arkusz kalkulacyjny odpowiedniego wyglądu komórkom i ich zawartości (krój czcionki, obramowanie, desenie itp.), jeśli spełniony jest określony warunek.
W celu ustawienia formatowania warunkowego dla komórki (lub zakresu) należy:
Zaznaczyć obszar (komórkę lub zakres komórek), dla którego chcemy zastosować formatowanie warunkowe
Wybrać narzędzie do formatowania warunkowego
(Format) → polecenie Formatowanie warunkowe...(Conditional Formatting...); karta Narzędzia główne → grupa Style → przycisk polecenia Formatowanie warunkowe)
Ustawić jeden lub więcej warunków (kryteriów) formatowania dla Wartości komórki (Cell Value) lub Formuły (Formula).
Określić sposób sformatowania komórki gdy ustawiony wcześniej warunek będzie spełniony.
Zatwierdzić OK
Arkusz kalkulacyjny udostępnia narzędzia, które pozwalają na kontrolę poprawności wprowadzanych danych. Za pomocą funkcji sprawdzania poprawności danych można kontrolować typ danych lub wartości wprowadzane przez użytkowników w komórce. Można na przykład ograniczyć wprowadzanie danych do określonego zakresu dat, ograniczyć wybory przy użyciu listy lub zagwarantować, że są wprowadzane tylko całkowite liczby dodatnie.
Aby z tego skorzystać należy:
Zaznaczyć obszar (komórkę lub zakres komórek), dla którego mamy zamiar zastosować kontrolę poprawności wprowadzanych danych
Wybrać narzędzie do sprawdzania poprawności danych (menu Dane (Data) → polecenie Sprawdzanie poprawności...(Validation...); karta Dane → grupa Narzędzia danych → Przycisk polecenia Poprawność danych)
Ustawić Kryteria poprawności (Validation criteria) w zakładce Ustawienia(Settings)
Ustawić Komunikat wejściowy (Input Message) - jeżeli jest konieczny
Ustawić Komunikaty o błędach (Error Alert) - tytuł i treść komunikatu oraz Styl (Style): Zatrzymaj (Stop) - umożliwia wpisanie wartości poprawnej, wybranie jej z listy lub zaniechanie wpisywania; Ostrzeżenie (Warning) - umożliwia zaakceptowanie błędnej wartości, poprawienie jej lub anulowanie wpisu; Informacje (Information) - umożliwia zaakceptowanie wpisu lub anulowanie
Zatwierdzić OK
Sprawdzanie poprawności danych można skonfigurować, aby uniemożliwić użytkownikom wprowadzanie nieprawidłowych danych. Można też zezwalać użytkownikom na w prowadzanie nieprawidłowych danych, ale ustawić ostrzeżenia dla nich, gdy próbują wpisać je w komórce. Ponadto można udostępnić komunikaty definiujące dane wejściowe oczekiwane dla komórki oraz instrukcje ułatwiające użytkownikom poprawianie ewentualnych błędów.
Sprawdzanie poprawności danych to nieoceniona funkcja w sytuacji, gdy twórca skoroszytu chce udostępnić skoroszyt innym osobom i zagwarantować, że wprowadzane dane będą dokładne i spójne.
Ćwiczenie
Przygotuj projekt. Lista płac dla 10 pracowników – wprowadź wszystkie formuły przeliczające i je zablokuj. Użytkownik może tylko wpisywać w polach z punku 2. 3. 5. 7. 9.
Usuń ochronę z komórek, do których użytkownik może wpisywać dane i włącz ochronę całego arkusza bez hasła.
1.L.p;
2.Nazwisko i imię;
3.Płaca zasadnicza w zł;
4.Stawka za godzinę w zł (1/175 płacy zasadniczej);
5.Ilość nadgodzin
6.Za nadgodziny w zł;
7.Premia w % (od płacy zasadniczej);
8.Premia w zł;
9.Dodatek w zł;
10.Płaca brutto w zł;
11.Ubezpieczenia w zł (od płacy brutto 34%);
12.Do opodatkowania (Płaca brutto – ubezpieczenia)
13.Podatek (stały 18%)
14.Do wypłaty