Spis treści Skorowidz Poziom główny Poziom nadrzędny Wstecz Dalej Zadania Zadania Zadania ©

Podstawy pracy z arkuszem kalkulacyjnym

Najpierw trzeba wiedzieć, jak policzyć

Przetwarzanie algorytmiczne

Więcej: poprzedni rozdział o notacji algorytmów

Typy danych

Więcej: poprzedni rozdział o typach danych

Dane w przetwarzaniu

Uzupełniające się spojrzenia na algorytm

Oprogramowanie

Pierwszy na świecie program arkusza kalkulacyjnego (w dzisiejszym rozumieniu) nosił nazwę VisiCalc (1979). Współcześnie użytkowanych jest wiele programów tego typu: Lotus 1-2-3, Corel Quattro Pro, Microsoft Excel, Sun StarCalc, OpenOffice Calc, Lotus Symphony, Google Spreadsheets, GNOME Gnumeric i inne.

Galeria ilustracji przedstawia zestaw zrzutów ekranowych kilku arkuszy kalkulacyjnych.

Struktura arkusza

Komórka

Zasady adresowania

W powszechnej konwencji adresowania, przyjętej w przeważającej większości arkuszy kalkulacyjnych, wskazanie położenia komórki odbywa się przez podanie jej współrzędnych w postaci numeru kolumny i numeru wiersza w kolumnie.

Numerację kolumn prowadzi się za pomocą etykiet jednoliterowych w porządku alfabetycznym (A, B, C, …), po ich wyczerpaniu — za pomocą etykiet dwuliterowych (AA, AB, AC, …, AZ, BA, BB, …), a w razie ich wyczerpania — także trzyliterowych.

Wiersze są numerowane liczbami całkowitymi, począwszy od 1 (1, 2, 3, …).

Na przykład adres C2 oznacza komórkę ulokowaną w drugim wierszu trzeciej kolumny.

Liczba wierszy i kolumn składających się na arkusz roboczy jest właściwością poszczególnych aplikacji i obsługiwanych przez nie formatów użytkowych. Systemy: Excel, OpenOffice Calc, Gnumeric obsługują arkusze zawierające do 256 kolumn i do 65536 wierszy (takie charakterystyki można nazwać „typowymi”). Ponadto system Excel począwszy od wersji 2007 obsługuje do 1048576 wierszy; system OpenOffice Calc począwszy od wersji 3 obsługuje do 1024 kolumn, a do wersji wersji 3.3 — do 1048576 wierszy. Arkusze systemu KOffice mogą zawierać do 32767 wierszy i 32767 kolumn. Arkusze systemu Corel Quattro Pro mogą zawierać do 1048576 wierszy i do 18276 kolumn. Arkusze GS-Calc mogą zawierać nawet do 12 milionów wierszy i do 4096 kolumn.

W jednym pliku roboczym, zwanym skoroszytem, może znajdować się wiele arkuszy. Odwołania do komórki położonej w innym arkuszu muszą mieć postać kwalifikowaną, z podaniem nazwy arkusza. Można tym sposobem odwoływać się za pomocą adresów względnych, mieszanych i bezwzględnych:

Formuły

Jedynym sposobem na umieszczenie danej w komórce jest wpisanie czegoś do tej komórki. Wpisać można albo stałą wartość, albo formułę precyzującą sposób obliczania wartości komórki. Posługiwanie się formułami stanowi zasadniczy element budowy plików roboczych arkusza kalkulacyjnego, gdyż umożliwia automatyzację obliczeń zgodnie z opisem logicznych zależności między danymi.

Formuła ustala sposób obliczenia wyrażenia, którego wartość wynikowa zostanie „nadana” komórce. Wyrażenie zapisane w formule może zawierać wartości stałe, symbole działań, funkcje arkusza i adresy innych komórek. Formuł używa się do budowy kolejnych etapów przetwarzania, począwszy od danych wejściowych aż do końcowych wyników. Dzięki temu informacja przekazana za pomocą danych wejściowych, mówiąc obrazowo, „rozprzestrzenia się” przepływając przez stadia pośrednie opisane za pomocą formuł w poszczególnych komórkach. Właściwość tę dobrze ilustruje angielski termin spreadsheet.

Poprawność wyniku obliczanego za pomocą formuły wymaga aktualizacji przeliczeń po każdej zmianie wartości komórek, których adresy umieszczono w formule. We współczesnych systemach arkuszy kalkulacyjnych na ogół dzieje się to automatycznie.

=1+2+3+4+5
=b1+2.5
=b1+$c$1
=b1+c1
=(a1^2 + a2^2 + a3^2)^0.5
=a1*b1 + a2*b2 + a3*b3
=-a2/(2*a1)

W arkuszach, które przejęły oryginalną składnię VisiCalca, formuły wprowadza się nieco inaczej:

+1+2+3+4+5
+b1+2.5
+b1+$c$1
+b1+c1
+(a1^2 + a2^2 + a3^2)^0.5
+a1*b1 + a2*b2 + a3*b3
-a2/(2*a1)

Techniki automatyzacji pracy

Ważnym elementem pracy z arkuszem kalkulacyjnym, decydującym o uniwersalności jego zastosowań, jest możliwość automatyzacji niektórych prac. Dobrze zaprojektowany tok przetwarzania danych nie wymaga przy zmianie zestawu danych wejściowych żadnej ingerencji w zawartość komórek przechowujących dane pośrednie i wynikowe.

Koncepcja czynności powtarzalnej ma w opisie algorytmicznym odpowiednik w postaci iteracji, a w językach programowania w konstrukcji pętli. Jej zastosowanie w arkuszu kalkulacyjnym wymaga użycia tych samych formuł w wielu komórkach, odpowiadających kolejnym iteracjom. Formułę da się powielić półautomatycznie, korzystając z techniki kopiowania zawartości komórek w wierszu lub w kolumnie. Zależnie od postaci (względne, mieszane, bezwzględne), adresy komórek w formułach różnią się zachowaniem podczas kopiowania.

Podczas kopiowania zawartości komórki zawierającej formułę, adresy bezwzględne (oraz bezwzględnie adresowane składowe adresów mieszanych) są zawsze zachowywanie w niezmienionej postaci. Znaczy to, że kopia takiego adresu opisuje ten sam adres, co pierwowzór kopii — stąd zresztą nazwa.

Podczas kopiowania zawartości komórki zawierającej formułę, adresy względne (oraz względnie adresowane składowe adresów mieszanych) są modyfikowane tak, by zachowana została relacja położenia komórki, z której pobiera się daną, względem komórki zawierającej skopiowaną formułę (w adresach mieszanych uwaga ta dotyczy względnego składnika adresu). Mówiąc inaczej, zachowywane są współrzędne wektora opisującego translację od komórki z daną do komórki z formułą.

Reguły te zilustrowano w poniższej tabeli.

Zachowanie adresów komórek przy kopiowaniu formuł
Adres względny Adresy mieszane Adres bezwzględny
A1B1C1
A2B2C2
A3B3C3
$A1$A1$A1
$A2$A2$A2
$A3$A3$A3
A$1B$1C$1
A$1B$1C$1
A$1B$1C$1
$A$1$A$1$A$1
$A$1$A$1$A$1
$A$1$A$1$A$1

Techniki kopiowania

Inne techniki manipulacji

W trybie edycji pojedynczej komórki zawartość formuły jest traktowana jak zwykły tekst. Jego fragmenty można kopiować, wstawiać i usuwać wszystkimi technikami dopuszczalnymi przez edytor; nie spowoduje to zmian w adresach.

„Przeciąganie” aktywnego bloku komórek spowoduje, że zmieni on lokalizację, a zatem także adres. Jednak związki logiczne między komórkami zostaną zachowane. Odpowiednie adresy w ich komórkach potomnych zostaną automatycznie dopasowane do nowego położenia.

Operatory

W komórce da się umieścić wartość dowolnego typu. O typie wyrażenia zadanego za pomocą formuły decyduje typ argumentów i charakter wykonywanych operacji.

Następujące operatory działają na danych liczbowych (całkowitych lub zmiennopozycyjnych) i dają wyniki odpowiedniego typu liczbowego:

Operatory + i - są obliczane po operatorach * / ^. Jeżeli logika postępowania wymusza inną kolejność działań, to trzeba ją określić jawnie za pomocą nawiasów okrągłych (…).

Następujące operatory działają na danych dowolnego typu i zwracają wartości typu logicznego:

Następujący operator działa na danych typu napisowego i zwraca wartość tego samego typu:

Inne działania, nawet jeżeli w tradycyjnej notacji matematycznej bywają oznaczane za pomocą operatorów, w środowisku arkuszy kalkulacyjnych najczęściej są realizowane za pomocą funkcji. W szczególności dotyczy to budowania złożonych wyrażeń logicznych.

Funkcje wbudowane arkusza kalkulacyjnego

Spisu dostępnych funkcji oraz opisu ich użycia należy poszukiwać w dokumentacji środowiska użytkowego.

=sin(a1)
=pi()
=abs(c$1)
=sum(a1:f32)
=mproduct(a1:a3; b1:b3)
=mproduct(a1:c4; e1:e4)
=if(test; wyrażenie1; wyrażenie2)
=if(a1>0;sin(b1);cos(b2))

W arkuszach, które przejęły oryginalną składnię VisiCalca, nazwę funkcji trzeba poprzedzić symbolem adresu (@):

@sin(a1)
@pi()
@abs(c$1)
@if(a1>0;@sin(b1);@cos(b2))

W przeciwieństwie do wyrażeń arytmetycznych, nazwy funkcji arkusza kalkulacyjnego są na ogół dopasowane do narodowych wersji interface'u.

=sin(a1)
=pi()
=moduł.liczby(c$1)
=suma(a1:f32)
=macierz.iloczyn(a1:a3; b1:b3)
=macierz.iloczyn(a1:c4; e1:e4)
=jeżeli(test; wyrażenie1; wyrażenie2)

W ten sposób zamiast powszechnie przyjętych w świecie i w językach programowania nazw, takich jak np. if dla funkcji warunkowej, abs dla wartości bezwzględnej czy też sqrt dla pierwiastka, mamy do czynienia z takimi dziwolągami, jak jeżeli (albo gdy), moduł.liczby czy też pierwiastek. Można się temu faktowi dziwić, jednak dyskutować z nim trudno. Na szczęście wewnętrzny sposób przechowywania danych w plikach roboczych arkuszy jest niezależny od wersji narodowej.

Rozszerzanie biblioteki funkcji

Współczesne systemy arkuszy kalkulacyjnych są rozbudowanymi pakietami użytkowymi. Oprócz wykonywania przekształceń danych za pomocą formuł umożliwiają także prowadzenie prac programistycznych (zazwyczaj w jakimś języku interpretowanym) oraz dołączanie bibliotek rozszerzających.

Jedną z istotnych dla użytkownika możliwości jest rozbudowa biblioteki funkcji arkusza. Przeprowadza się ją odmiennie w każdym programie użytkowym. W systemach Excel i OpenOffice Calc użytkownik ma możliwość używania w formułach funkcji, których deklaracje (w dialekcie języka Basic) zostały umieszczone w modułach programistycznych arkusza. W innych systemach podobną funkcję może pełnić inny język programowania.

Pewne wskazówki odnośnie przygotowywania takich funkcji zamieściliśmy w osobnym podrozdziale dokumentacji.

Składnia funkcji tablicowych

Wynikiem klasycznie rozumianej funkcji jest wartość typu prostego, którą da się przechowywać w pojedynczej komórce. Możliwe jest także obliczanie wartości wyrażeń, których argumenty i wyniki są wektorami lub macierzami. Wartości wynikowe takich wyrażeń będą reprezentowane przez jedno- lub dwuwymiarowe bloki przylegających do siebie komórek.

Wyrażenia, których wyniki są tablicami

Formuły, których wyniki są traktowane jak tablice, są dostępne w większości współczesnych arkuszy kalkulacyjnych. Zatwierdzanie zawartości takiej formuły przebiega w sposób odmienny, niż zwykłej formuły; zazwyczaj za pomocą klawiszy Ctrl+Shift+Enter.

Wyrażenia, których argumenty są tablicami

Współczesne środowiska (w tym m.in. Microsoft Excel, Corel Quattro Pro, OpenOffice Calc, Sun StarCalc, Gnumeric) dysponują także rozszerzoną składnią formuł, wzbogaconą o notację wektorową.

W rozszerzonej składni typu macierzowego inne jest znaczenie operatorów arytmetycznych i funkcji. Operatory i funkcje, których argumenty są skalarami, stosowane są wtedy osobno do wszystkich składowych argumentu wektorowego. Na przykład formuła macierzowa =(a1:a10)^2 obliczy 10-elementowy wektor, którego elementy będą kwadratami elementów pobranych z obszaru a1:a10. Jeżeli w komórkach a1:b20 przechowujemy współrzędne 20 punktów, to długość łączącej je linii łamanej będzie można obliczyć za pomocą jednej zwartej formuły =sum((a1:b19-a2:b20)^2)^0.5 .

Ten sam efekt da się oczywiście uzyskać za pomocą osobnego programowania kolejnych operacji skalarnych i zapamiętywania ich wyników w komórkach jako danych pośrednich. Redukcja przestrzeni niezbędnej do przechowywania wyników pośrednich jest więc silnym argumentem za korzystaniem z rozszerzonej składni. Wobec faktu, że brak dostępu do wyników pośrednich uniemożliwia kontrolę poszczególnych etapów przetwarzania, obliczenia korzystające z rozszerzonej składni należy projektować i testować szczególnie starannie.

Przykłady przetwarzania

Ciągi liczbowe

Tablica zmienności funkcji jednej zmiennej

Funkcje dwóch zmiennych

Operacje macierzowe

Operacje bazodanowe

(patrz także następny dokument)

Podstawy prezentacji graficznej danych

Charakter przedstawianych danych

dane o charakterze nominalnym
wolno przyrównywać i rozróżniać obserwacje, ale ma podstaw do ich uszeregowania; da się jedynie stwierdzić, że dwie wartości są takie same albo różne. Przykłady: zapach, narodowość, płeć.
dane o charakterze porządkowym
wolno porównywać i porządkować obserwacje; da się sprawdzić, że jedna wartość jest większa od innej, ale ma podstaw do stwierdzenia, o ile jest większa. Przykłady: twardość, oceny szkolne.
dane liczbowe (numeryczne)
na skali przedziałowej
punkt zerowy, o ile istnieje, ma charakter czysto umowny; wiemy o ile jedna wartość jest większa od innej, ale nie wiemy ile razy jest większa. Przykłady: data, temperatura w skali Celsjusza.
na skali ilorazowej
istnieje bezwzględny punkt zerowy; wolno wykonywać działania arytmetyczne w celu sprawdzenia ile razy jedna wartość jest większa od drugiej. Przykłady: gęstość, stężenie, wiek, temperatura w skali bezwzględnej.

Sposoby prezentacji danych na wykresach

skala nominalna
przedstawia etykiety z opisem, kolejność jest nieistotna.
skala porządkowa
przedstawia kolejność w ciągu danych.
skala numeryczna — przedziałowa
przedstawia wartości liczbowe odłożone na osi liczbowej z ustaloną jednostką.
skala numeryczna — ilorazowa
przedstawia wartości liczbowe odłożone na osi, przy czym można stosować podziałkę nieliniową (np. logarytmiczną).

Typy wykresów dostępne w środowisku roboczym

wykres „słupkowy”
zmienna niezależna ma charakter nominalny;
wykres „liniowy”
zmienna niezależna ma charakter porządkowy;
wykres „punktowy”
zmienna niezależna ma charakter numeryczny.

Wykres jako grafika wektorowa

aktualizacja wykresu
następuje (zazwyczaj) automatycznie po modyfikacji danych
elementy wykresu
są obiektami, których właściwości da się interaktywnie modyfikować
adresy danych przedstawianych na wykresie
są jedną z właściwości; istnieje możliwość korekty adresów i zarządzania seriami danych
eksport wykresu
do pliku w formacie grafiki wektorowej; patrz np. rozwiązanie opcji eksportu w arkuszu Gnumeric
przez osadzenie go w innym miejscu lub w innym dokumencie; użycie schowka systemowego

Import i eksport

Wykrywanie i eliminacja błędów

Uwagi dotyczące rodzajów błędów, ich przyczyn i sposobów ich usuwania odnoszą się w równej mierze do programów źródłowych i do plików arkusza kalkulacyjnego. W tym drugim przypadku nie ma potrzeby korzystania z osobnych debuggerów, gdyż dane pośrednie albo są dostępne w komórkach, albo mogą być tam umieszczone w wyniku prostych modyfikacji formuł.

Na przykład formuła mająca obliczać iloraz sum

=(a1 + a2) / (b1 + b2)

zapisana w postaci

=(a1 + a2) / b1 + b2)

jest niepoprawna składniowo (brak jednego nawiasu), co zostanie najprawdopodobniej wykryte przy próbie jej zatwierdzenia. Natomiast formuła

=a1 + a2 / b1 + b2

użyta do tego samego obliczenia zawiera poważny błąd logiczny, polegający na innej kolejności działań, niż wynika to z założonego celu obliczeń. Jednak błąd ten nie zostanie automatycznie dostrzeżony, gdyż formuła ta poprawnie opisuje pewne wyrażenie arytmetyczne. Do znalezienia tego błędu niezbędna jest krytyczna analiza zawartości formuł, w czym może pomóc krytyczna analiza otrzymywanych wyników.

Podpowiedzi sugerujące modyfikację błędu składniowego przy zatwierdzaniu formuły radzimy traktować z rezerwą, gdyż nie biorą one pod uwagę rzeczy najważniejszej, jaką jest zamiar użytkownika.

Środowiska arkuszy kalkulacyjnych dostarczają także narzędzi pomocnych przy sprawdzaniu poprawności logicznej formuł. Są to m.in.:

  1. wizualizacja argumentów formuły (np. za pomocą barw),
  2. dopasowanie nawiasów,
  3. wizualizacja kaskady zależności komórek w arkuszu (np. za pomocą strzałek),
  4. wizualizacja struktury pojedynczej formuły w formularzu asystenta tworzenia formuł (kreatora).

Pytania kontrolne

© Copyright 2002–2008, 2010 by Jan Jełowicki, Katedra Matematyki Uniwersytetu Przyrodniczego we Wrocławiu
janj@aqua.up.wroc.pl
http://karnet.up.wroc.pl/~jasj