Technologie informacyjne. Laboratorium N5.
Obliczenia z wykorzystaniem arkusza kalkulacyjnego
Cel ćwiczeń: projektowanie prostych arkuszy obliczeniowych z wykorzystaniem funkcji arkusza;
formuły obliczeniowe wykorzystujące funkcje logiczne; przenoszenie danych między arkuszami;
formatowanie danych.
Program zajęć
1. Zapoznać się z możliwościami arkusza kalkulacyjnego w zakresie:
1.1. Tworzenia nowego skoroszytu zawierającego kilka arkuszy.
1.2. Formatowania komórek (dobór właściwego formatu dla różnych danych:
walutowe, procentowe, czasu, ogólne).
1.3. Wstawiania, usuwania wierszy i kolumn w arkuszu.
1.4. Wykonywania obliczeń z wykorzystaniem stałych oraz formuł utworzonych w oparciu o funkcje arkusza (matematyczne, statystyczne, wyszukiwania i adresu, logiczne).
1.5. Projektowania i weryfikacji prawdziwości formuł logicznych.
1.6. Przenoszenia danych między arkuszami.
1.7. Wykorzystanie funkcji sumowania danych w wierszach i kolumnach.
2. Przykładowe zadania
Zad. 2.1. Utworzyć arkusz umożliwiający obliczenie kwot w poszczególnych komórkach faktury pokazanej na rysunku 1.
• Kolejne pola w fakturze: | Lp. | Nazwa towaru | Ilość | J.m. | Cena j. brutto (zł) |
| Cena j. netto (zł) | Wartość netto (zł) | Stawka (%) | VAT (zł) | Wartość brutto (zł) | Rabat (%) | Do zapłaty (zł) |. Przy czym Stawka oznacza VAT w %, Wartość brutto - całkowitą kwotę do zapłaty za dane towary, Rabat – wartość rabatu w procentach, Do zapłaty – kwotę brutto do zapłaty zmniejszoną o wartość rabatu.
• Uzupełnić fakturę własnymi danymi dotyczącymi: nazw towarów, ich ilości, ceny jednostkowej netto, stawki procentowej VAT, a następnie obliczyć wartości pozostałych pól:
- cena j. brutto = cena j. netto * stawka % VAT;
- wartość netto = cena j. netto * ilość;
- VAT = wartość netto * stawka % VAT;
- wartość brutto = wartość netto + VAT;
- rabat w procentach zależny od wartości brutto zakupu;
- kwotę do zapłaty zależną od ustalonego progu rabatowego.
• W kwocie do zapłaty uwzględnić odpowiedni rabat. Przyjąć następujące progi rabatowe: jeśli wartość brutto jest mniejsza niż 100zł rabat wynosi 0%; jeśli wartość brutto należy do przedziału [100zł, 500zł) rabat wynosi 5%; jeśli wartość brutto należy do przedziału [500zł, 1000zł) rabat wynosi 10%; jeśli wartość brutto należy do przedziału [1000zł, 2000zł) rabat wynosi 15%; jeśli wartość brutto jest większa, lub równa 2000zł, rabat wynosi 20%.
• Do obliczenia rabatu, w zależności od ustalonych progów rabatowych, wykorzystać funkcje logiczne: JEŻELI(), ORAZ().
• Obliczyć kwotę łączną do zapłaty (RAZEM:) oraz łączny rabat (RABAT:).
Wykorzystać funkcję sumowania (SUMA).
Rysunek 1. Wydruk arkusza z fakturą
Zad. 2.2. Sprawdzanie poprawności wyrażeń logicznych. Wyznaczyć wartości wyrażeń logicznych podanych w tabeli stosując metodę zerojedynkową (0-1). Wykorzystać funkcje logiczne: NIE(), LUB(), ORAZ(), JEŻELI(). Określić, czy prawdziwe są prawa De Morgana:
Prawo 1: ~(p ∧ q) ≡ (~p ∨ ~q ), Prawo 2: ~(p ∨ q) ≡ (~p ∧ ~q ).
Prawo 1 de Morgana
| p | q | ~p | ~q | p ∧ q |~(p ∧ q)| ~p ∨ ~q | Prawo 1 |
| 0 | 0 |
| 1 | 0 |
| 0 | 1 |
| 1 | 1 |
Prawo 2 de Morgana
| p | q | ~p | ~q | p ∨ q |~(p ∨ q)| ~p ∧ ~q | Prawo 2 |
| 0 | 0 |
| 1 | 0 |
| 0 | 1 |
| 1 | 1 |
Zad. 2.3. Analiza sprzedaży w firmie. Przenoszenie danych między arkuszami.
• Do Arkusza 1 wprowadzić dowolne dane określające wartości sprzedaży różnych produktów w Oddziale X pewnej Firmy. Postać tabeli:
SprzedaŜ w Oddziale X
| 2002r. | 2003r. | 2004r. | 2005r. | 2006r. | Produkt A |
Produkt B | Produkt C | Produkt D | Produkt E |
• Do Arkusza 2 wprowadzić analogiczne dane dla Oddziału Y (Sprzedaż w Oddziale Y).
SprzedaŜ w Oddziale Y
| 2002r. | 2003r. | 2004r. | 2005r. | 2006r. | Produkt A |
Produkt B | Produkt C | Produkt D | Produkt E |
• W Arkuszu 3 utworzyć dwie tabele ilustrujące łączne wyniki firmy dotyczące obu oddziałów:
Zestawienie sprzedaży każdego produktu w firmie w każdym roku.
Zestawienie łącznej sprzedaży Oddziałów X i Y oraz całej firmy w każdym roku.
SprzedaŜ produktów w Firmie
| 2002r. | 2003r. | 2004r. | 2005r. | 2006r. | Produkt A |
Produkt B | Produkt C | Produkt D | Produkt E |
SprzedaŜ całkowita w Firmie
| 2002r. | 2003r. | 2004r. | 2005r. | 2006r. | Oddział X |
Oddział Y | RAZEM |