4.6. Praca z arkuszem kalkulacyjnym

4.6.1. Materiał nauczania

 

Arkusz kalkulacyjny jest elektroniczną wersją tabeli. Składa się z wielu rozmieszczonych obok siebie komórek. Komórka jest elementem znajdującym się na przecięciu określonego wiersza i kolumny tabeli. Każdy wiersz posiada swój numer od 1 do 65536, zaś kolumna od A do kombinacji liter IV (256 kolumn). Podstawowym zadaniem arkusza kalkulacyjnego jest wykonywanie obliczeń na pojedynczych komórkach i ich grupach. Użytkownik może dowolnie zmienić jej wygląd (krój i rozmiar czcionki, sposób wyrównywania, kolory, obramowanie, sposób wyświetlania liczb) tworzonej tabeli.

Każda komórka arkusza ma własny adres złożony z oznaczeń kolumny i wiersza, na przecięciu, których się znajduje. (np.: „A1”, „B54”, „CD43”). Jest on wykorzystywany w formułach do odwoływania się do określonych komórek.

Często zachodzi konieczność zaadresowania więcej niż jednej komórki; prostokątny obszar przylegających do siebie komórek nosi nazwę zakresu komórek. Można go wskazać poprzez podanie adresów komórek leżących w przeciwległych narożnikach obszaru oddzielonych dwukropkiem (na przykład „A1:C4”).

Do komórek arkusza można wprowadzać następujące rodzaje danych: liczbę, tekst i formułę.

Liczby umożliwiają wypełnienie arkusza własnymi danymi, które następnie można poddać dalszej analizie. Umożliwiają one przechowywanie danych zarówno w postaci całkowitej, jak i ułamkowej. Również data i czas zapamiętywane są jako pojedyncze liczby.

Tekst jest ciągiem liczb, liter lub innych znaków. Najczęściej służy do opisu innych obszarów arkusza. Może być wykorzystywany przez niektóre formuły. Liczbę, która ma być odczytana przez arkusz jako tekst należy poprzedzić znakiem „ ’ ”.

Formuły umożliwiają wykonywanie obliczeń na pojedynczych komórkach i ich grupach. Oprócz podstawowych działań (dodawanie, odejmowanie, mnożenie, dzielenie, potęgowanie) istnieje możliwość wykorzystania zaawansowanych funkcji matematycznych, logicznych, finansowych i innych.

Podstawowe informacje o projektowaniu arkusza kalkulacyjnego zostaną przekazane w oparciu o program Excel – elementu pakietu MS Office.

Na powyższym rysunku wyróżniono widok okna programu:

Występują w nim charakterystyczne dla innych programów pakietu Office, menu rozwijane, paski narzędzi, paski przewijania. Elementami nowymi są: pasek formuły (2) i struktura arkusza.

1. Karty arkuszy – umożliwiają dostęp do dowolnego arkusza w skoroszycie; karta wyróżniona wskazuje arkusz aktywny.

2. Pasek formuły – w tym miejscu widać wartość lub formułę wprowadzoną do komórki. Tu również możliwa jest ich edycja.

3. Pole nazwy komórki/zakresu lub lista funkcji – dowolnej komórce lub ich grupie (aktualnie zaznaczonej) można nadać w tym miejscu unikalną nazwę i następnie wykorzystać ją w adresowaniu. W czasie wprowadzania formuł znajduje się tu lista dostępnych funkcji.

Istotą samego programu jest konstruowanie tabel, zawierających wymienione trzy rodzaje danych dla potrzeb rozwiązywania problemów organizacyjnych, finansowych.

Do komórki aktywnej można wpisywać nowe informacje. Po wpisaniu dowolnego znaku z klawiatury pojawia się on w aktywnej komórce. Zakończenie wprowadzania następuje po wybraniu innej komórki lub po naciśnięciu klawisza ENTER. Po wpisaniu danych do komórek następuje ich klasyfikacja do jednej z trzech kategorii: liczby, formuły lub tekstu.

Teksty wyrównywane są domyślnie do lewej krawędzi komórki, liczby do prawej, natomiast w przypadku formuły zamiast jej treści w komórce pojawia się wyliczona z niej wartość. Edycję formuły rozpoczynamy od znaku „=”.

Istnieje możliwość modyfikowania rozmiaru komórek i dostosowania ich do potrzeb projektowanego arkusza.

Oprócz zawartości każdej komórce przypisany jest określony format. Określa on wygląd komórki. Jego modyfikacja polega na zmianie kroju, rozmiaru i atrybutów czcionki, koloru wypełnienia, sposobu wyrównywania tekstu, sposobu wyświetlania liczb. Do formatowania komórek służy opcja „Format/Komórki”.

Komórki lub grupę komórek można, korzystając z poleceń edycyjnych, przenosić (Edycja/Wytnij, Edycja/Wklej) lub kopiować (Edycja/Kopiuj, Edycja/Wklej). Do wymienionych operacji można wykorzystać pasek narzędzi lub odpowiednie skróty klawiszowe. Obszar przenoszony lub kopiowany zaznaczony jest animowaną linią przerywaną aż do momentu zakończenia operacji lub anulowania jej klawiszem ESC.

Komórki można również przenosić i kopiować za pomocą myszy. Należy zaznaczyć żądany zakres komórek, „schwycić” go za otaczającą go grubą ramkę, a następnie przeciągnąć myszą zaznaczenie w miejsce docelowe. Spowoduje to przeniesienie komórek w nowe miejsce. Aby komórki skopiować należy dodatkowo w czasie tej operacji przytrzymać wciśnięty klawisz Ctrl.

Jeśli jako dane źródłowe zaznaczymy pojedynczą komórkę, a jako obszar docelowy większą grupę komórek, wówczas komórka źródłowa zostanie powielona odpowiednią ilość razy tak, aby wypełnić zaznaczenie.

Formuły są to wyrażenia matematyczne umożliwiające prowadzenie obliczeń w arkuszu kalkulacyjnym. Wprowadzanie formuły rozpoczyna się od znaku równości „=”. Jest to sygnał dla programu, że jest to wyrażenie do obliczenia a nie zwykły tekst. Argumentami dla operacji wykonywanych w formułach są zarówno stałe wartości wprowadzone przez użytkownika, jak i wartości innych komórek arkusza, do których odwołuje się poprzez adres komórki.

Projektując zapis formuły można korzystać z czterech podstawowych działań arytmetycznych tj. dodawania, odejmowania, mnożenia i dzielenia oraz potęgowania, a także zbioru funkcji oferowanych przez program. Możliwe jest stosowanie nawiasów okrągłych w celu określenia kolejności wykonywania działań

.

Ogółem dostępnych jest kilkaset funkcji. Zostały one podzielone na następujące kategorie:

  • finansowe,
  • daty i czasu,
  • matematyczne,
  • statystyczne,
  • wyszukiwania i adresu,
  • bazy danych,
  • tekstowe,
  • logiczne,
  • informacyjne,
  • użytkownika.

Program zawiera pełny opis każdej funkcji wraz z listą argumentów i przykładami ich użycia.

Formuły można powielać kopiując i przenosząc komórki.

Projektując arkusz należy pamiętać o możliwości kopiowania komórek zawierających formuły. Procesowi temu towarzyszy modyfikacja zapisanych w formułach adresów komórek. Adresy komórek zawierające jedynie numer kolumny i wiersza są adresami względnymi i w trakcie kopiowania ulegają modyfikacji. Określający je numer kolumny i wiersza zmienia się w zależności od miejsca docelowego kopiowanej komórki.

Dla przykładu, jeśli w komórkach A1 i B1 umieści się jakieś liczby a w komórce C1 formułę „=A1+B1” to po przekopiowaniu komórki C1 do komórki C2 postać formuły będzie następująca „=A2+B2”. Choć taka właściwość arkusza wydaje się bardzo przydatna, to jednak czasem zachodzi potrzeba odwołania się do konkretnej komórki bez względu na to, jak i ile razy zostanie powielona formuła. W celu zablokowania zmian w adresie komórki podczas kopiowania należy adres tej komórki wzbogacić o znak „$” umieszczony przed numerem wiersza lub numerem kolumny (adresowanie mieszane) lub w obu miejscach naraz (adresowanie bezwzględne) w zależności od specyfiki projektowanego arkusza.

Wykresy

Bardzo istotną częścią programu Microsoft Excel są wykresy. Umożliwiają one przedstawienie w sposób graficzny i na wiele różnych sposobów danych umieszczonych w arkuszu

Tworzenie wykresu rozpoczyna się od wybrania z arkusza serii danych. Seria danych jest to pojedynczy zakres komórek mieszczący się w jednym wierszu lub kolumnie, z którego wartości są graficznie interpretowane na wykresie. Podczas tworzenia wykresu można skorzystać z kreatora. Uruchomienie kreatora następuje po wydaniu z menu „Wstaw” polecenia „Wykres...”. W trakcie tworzenia wykresu należy określić takie parametry jak: typ wykresu, zakres komórek, z których pobierane są dane, parametry dotyczące wyglądu wykresu oraz miejsce umieszczenia wykresu.

Arkusz jako tabelaryczna baza danych

Odpowiednio zaprojektowany arkusz kalkulacyjny o jednolitej strukturze, w którym poszczególne kolumny określają jednolity typ danych, a wiersze stanowią uporządkowany opis określonego elementu można potraktować jako bazę danych o tabelarycznej strukturze. Wiersze takiej tabeli stanowią rekordy bazy, zaś kolumny – pola. Excel posiada wbudowane funkcje umożliwiające wykonywanie podstawowych operacji na tak stworzonej bazie. Do tych operacji należy: sortowanie filtrowanie danych, tworzenie formularza dla potrzeb wprowadzania danych, sumowanie list, konstruowanie tabel przestawnych. Wszystkie wymienione funkcje są dostępne w menu Dane.

Aby dokonać sortowania danych w tabeli należy wywołać funkcje „Dane/Sortowanie” oraz dokonać wyboru do 3 pól, według których dane zostaną posortowane. Należy również pamiętać o określeniu sposobu sortowania: malejąco lub rosnąco. Funkcja filtrowania (Dane/Filtr) pozwala na wyświetlanie wybranych rekordów bazy według określonego kryterium. Funkcja ta pozwala wykorzystać autofiltr lub filtr zaawansowany. Funkcja „sumy częściowe” umożliwia wykonywanie operacji na danych w tabeli uporządkowanych według określonych kryteriów. W tym celu można wykorzystać takie funkcje, jak: suma, licznik, średnia, maksimum, minimum, licznik.

Konstruując bazę w arkuszu kalkulacyjnym, należy pamiętać o możliwości wprowadzania do niej danych poprzez formularz wywoływany poprzez menu Dane/Formularz. Program Microsoft Excel może posłużyć do budowy prostej bazy danych.

Zagadnienie projektowania i budowy baz danych zostało dokładniej omówione w pakiecie do modułu 312[01].Z2.01.

TWORZENIE BAZY DANYCH W EXCELU - MINI PRZEWODNIK [zobacz]

Tabele przestawne

Program Excel jest wyposażony w kreator tabel przestawnych, umożliwiających konstruowanie tabel podsumowujących dane zawarte w arkuszu pełniącym rolę bazy. Informacje zawarte w tabeli przestawnej mogą być wzbogacone o interpretację graficzną w postaci wykresu. Kreator tabel przestawnych wywołuje się w menu „Dane/Raport” tabeli przestawnej i wykresu przestawnego.

Makro

Wykonując często powtarzające się czynności podczas konstruowania arkusza kalkulacyjnego można je zautomatyzować za pomocą makro. Makro jest zestawem poleceń i funkcji zapisanych za pomocą języka Visual Basic, uruchamianych podczas wykonywania określonego zadania. Projektując makra należy pamiętać, że istnieje możliwość ich automatycznego rejestrowania. Zarejestrowane makro można przyporządkować do zaprojektowanej ikony paska narzędzi lub skrótu klawiszowego w celu łatwego jego wywoływania. Każde makro można edytować i wykorzystując język Visual Basicmodyfikować jego zawartość.

 

FUNKCJE TEKSTOWE W MS EXCEL [zobacz]

 

4.6.2. Pytania sprawdzające

Odpowiadając na pytania, sprawdzisz, czy jesteś przygotowany do wykonania ćwiczeń.

1. Jakiego typu dane można wprowadzać do komórek arkusza?

2. Co oznacza i jakie ma znaczenie dla projektowanego arkusza kalkulacyjnego adresowanie względne, bezwzględne i mieszane?

3. W jaki sposób konstruuje się formuły?

4. Dokonaj analizy efektów związanych z kopiowaniem komórek arkusza lub ich bloków.

5. W jaki sposób tworzy się wykresy?

6. Wymień operacje, jakie można wykonać na tabeli pełniącej rolę bazy danych?

7. W jaki sposób można zarejestrować makro? Podaj przykład.

 

4.6.3. Ćwiczenia

Ćwiczenie 1

Wykonaj arkusz kalkulacyjny według następującej specyfikacji. Zaprojektuj tabelę z miesięcznym zestawieniem sprzedaży komputerów. Powinna ona zawierać następujące nagłówki: lp., data sprzedaży, imię, nazwisko sprzedawcy. W kolejnej tabeli arkusz powinien obliczać premię miesięczną pracowników w zależności od liczby sprzedanych komputerów. Jeżeli pracownik sprzedał więcej niż 5 komputerów otrzyma 10% swojej miesięcznej pensji. Jeżeli zaś liczba sprzedanych komputerów przekroczyła 10 premia wyniesie 20% zasadniczego wynagrodzenia. Posortuj tabelę według wielkości otrzymanego wynagrodzenia.

Sposób wykonania ćwiczenia

Aby wykonać ćwiczenie powinieneś:

1) wykonać tabelę sprzedaży komputerów dla 20 dni roboczych w miesiącu, zakładając, że w sprzedaży uczestniczyło 5 sprzedawców,

2) wykonać drugą tabelę dla 5 sprzedawców z pierwszej tabeli,

3) wykorzystać odpowiednie funkcje do zliczania liczby sprzedanych komputerów przez poszczególnych pracowników,

4) przyjąć zasadnicze wynagrodzenie dla wszystkich pracowników,

5) wykorzystując funkcje warunkowe określić wartości premii dla poszczególnych pracowników,

6) posortować tabelę według wielkości otrzymanego wynagrodzenia,

7) obliczyć kwotę potrzebną na wynagrodzenia pracowników.

 

Ćwiczenie 2

Zaprojektuj i wykonaj następujący arkusz kalkulacyjny. Zaprojektuj formularz do wprowadzania danych do faktury sprzedaży komputerów. Zaprojektuj fakturę, pamiętając o stawce VAT.

Sposób wykonania ćwiczenia

Aby wykonać ćwiczenie powinieneś:

1) wykonać arkusz w postaci faktury, wykorzystując odpowiednie funkcje do zliczenia wartości netto i brutto,

2) zaprojektować formularz do wprowadzania danych do faktury.

 

Ćwiczenie 3

Dokonaj analizy swoich ocen w układzie semestralnym dla 10 przedmiotów.

Sposób wykonania ćwiczenia

Aby wykonać ćwiczenie powinieneś:

1) wykonać tabelę według załączonego wzoru,

2) obliczyć średnią ocen wykorzystując odpowiednie funkcje,

3) korzystając z funkcji warunkowych wystawić oceny końcowe zgodnie z następującymi kryteriami:

4) wystawić oceny w formie słownej.

 

Ćwiczenie 4

Dokonaj ilościowej analizy wyposażenia komputerowego Twojej szkoły.

Sposób wykonania ćwiczenia

Aby wykonać ćwiczenie powinieneś:

1) dokonać inwentaryzacji sprzętu komputerowego w poszczególnych pracowniach szkolnych, umieścić dane w tabeli pokazanej na rysunku, wypełniając ją następującymi danymi rodzaj sprzętu, nazwa pracowni, rok zakupu, wartość,

2) wykonać raport tabeli przestawnej w celu pokazania wyposażenia i jego wartości w poszczególnych pracowniach.

Ćwiczenie 5

Zaprezentuj konfigurację kilku stanowisk komputerowych wykorzystując pole combi

Sposób wykonania ćwiczenia

Aby wykonać ćwiczenie powinieneś:

1) przygotować konfigurację sprzętową stanowisk komputerowych przeznaczonych do różnych celów (zestawienie powinno być wykonane zgodnie z załączonym wzorem),

2) zaprojektować tabelę do zaprezentowania jednego zestawienia wybranego z pola combi.

 

Ćwiczenie 6

Przedstaw na wykresach przebieg funkcji w podanym zakresie.

Sposób wykonania ćwiczenia

Aby wykonać ćwiczenie powinieneś:

1) wykonać dla kolejnych funkcji tabele wartości funkcji dla poszczególnych argumentów,

2) samodzielnie dobrać liczbę punktów (przedziałów) dla podanych zakresów argumentów funkcji,

3) dobrać i wykonać wykresy wymienionych funkcji.

 

Ćwiczenie 7

Na podstawie własnych badań przedstaw graficznie popularność wśród twoich kolegów, wytypowanych przez Ciebie piosenkarzy.

Sposób wykonania ćwiczenia

Aby wykonać ćwiczenie powinieneś:

1) zaprojektować ankietę badawczą,

2) przedstawić ankietę w arkuszu kalkulacyjnym, wykorzystując możliwość wyboru poszczególnych odpowiedzi za pomocą pola combi,

3) dokonać analizy wyników,

4) przedstawić wyniki na wykresie.

 

Ćwiczenie 8

Zautomatyzuj sortowanie tabeli podającej wyniki zawodów sportowych skoku wzwyż.

Sposob wykonania ćwiczenia

Aby wykonać ćwiczenie powinieneś:

1) zaprojektować tabelę i formularz do wpisywania wynikow zawodow,

2) zarejestrować makro sortujące tabelę w sposob malejący według wyniku skoku,

3) utworzyć ikonę wykonującą makropolecenie.

 

Ćwiczenie 9

Zaprojektuj konfigurację komputera swoich marzeń i oblicz jego cenę.

Sposob wykonania ćwiczenia

Aby wykonać ćwiczenie powinieneś:

1) dla każdego komponentu komputera przygotować tabelę, podając typ elementu i jego cenę,

2) sporządzić tabelę przedstawiającą konfigurację komputera według powyższego wzoru,

3) do wyboru poszczegolnych elementow zastosować w tabeli pola combi.

 

Dodatkowe 10 ćwiczeń z excel'a

plik xlsx (Office 2007) - [pobierz]

 

4.6.4. Sprawdzian postępów

Czy potrafisz:

1) zastosować arkusz kalkulacyjny do określonych potrzeb?

2) zaprojektować dowolne formuły uwzględniając konieczność stosowania rożnych typow adresowania komorek?

3) zaprojektować makro?

4) dokonać interpretacji graficznej danych w arkuszu kalkulacyjnym?

5) wykorzystać arkusz jako bazę danych?

 

ZALICZENIE:

I. Praca praktyczna - zadania testowe:

Zadanie nr.1 - Baza danych z excel'u - [pobierz]

Zadanie nr.2 - Funkcje warunkowe - [pobierz]

Zadanie nr.3 - Analiza bazy danych - [pobierz]

Zadanie nr.4 - Baza danych z excel'u - [pobierz]

Zadanie nr.5 - Funkcje tekstowe - [pobierz]

II. Zadanie typu 'Próba pracy' [...]