Projekt: ANALIZATOR WYDAJNOŚCI I RENTOWNOŚCI SERWISU KOMPUTEROWEGO
Excel / LibreOffice Calc
Poziom zaawansowany
Czas: 3–4 godziny
1. Cel zadania
Twoim zadaniem jest stworzenie rozbudowanego arkusza kalkulacyjnego do analizy pracy serwisu komputerowego. Arkusz ma automatycznie obliczać przychody, koszty, zyski, premie pracowników oraz wskazywać najbardziej rentowne usługi.
Projekt powinien wykorzystywać zaawansowane formuły, funkcje warunkowe, wyszukiwanie danych, formatowanie warunkowe, tabele przestawne i wykresy.
2. Dane wejściowe
Utwórz arkusz o nazwie Zlecenia i wprowadź poniższe dane.
| ID | Pracownik | Usługa | Liczba godz. | Stawka za godz. (zł) | Części (zł) | Cena usługi (zł) | Status |
|---|---|---|---|---|---|---|---|
| 1001 | Kowalski | Diagnostyka | 1 | 80 | 0 | 150 | Zakończone |
| 1002 | Nowak | Wymiana SSD | 2 | 90 | 250 | 480 | Zakończone |
| 1003 | Wiśniewski | Instalacja systemu | 2 | 85 | 30 | 280 | Zakończone |
| 1004 | Kowalski | Czyszczenie PC | 1,5 | 80 | 20 | 220 | Zakończone |
| 1005 | Nowak | Modernizacja RAM | 1 | 90 | 180 | 300 | W trakcie |
| 1006 | Wiśniewski | Diagnostyka | 2 | 85 | 0 | 150 | Zakończone |
| 1007 | Kowalski | Wymiana zasilacza | 1,5 | 80 | 210 | 390 | Zakończone |
| 1008 | Nowak | Instalacja systemu | 2,5 | 90 | 40 | 320 | Anulowane |
| 1009 | Wiśniewski | Czyszczenie PC | 1 | 85 | 15 | 220 | Zakończone |
| 1010 | Kowalski | Modernizacja RAM | 1,5 | 80 | 200 | 340 | W trakcie |
| 1011 | Nowak | Diagnostyka | 1 | 90 | 0 | 150 | Zakończone |
| 1012 | Wiśniewski | Wymiana SSD | 2 | 85 | 270 | 500 | Zakończone |
| 1013 | Kowalski | Instalacja systemu | 2 | 80 | 35 | 290 | Zakończone |
| 1014 | Nowak | Czyszczenie PC | 1,5 | 90 | 25 | 240 | Zakończone |
| 1015 | Wiśniewski | Wymiana zasilacza | 2 | 85 | 230 | 410 | W trakcie |
Wprowadź nagłówki w wierszu 1, a dane w wierszach 2–16.
3. Część I – obliczenia finansowe
W arkuszu Zlecenia dodaj kolejne kolumny:
- I – koszt robocizny,
- J – koszt całkowity,
- K – zysk na zleceniu,
- L – marża procentowa,
- M – ocena rentowności,
- N – premia pracownika.
Wykonaj poniższe zadania:
- Oblicz koszt robocizny jako iloczyn liczby godzin i stawki godzinowej.
- Oblicz koszt całkowity jako sumę kosztu robocizny i kosztu części.
- Oblicz zysk jako różnicę między ceną usługi a kosztem całkowitym.
- Oblicz marżę procentową jako stosunek zysku do ceny usługi.
- Przypisz ocenę rentowności:
- marża co najmniej 40% – „Wysoka”,
- marża od 20% do poniżej 40% – „Średnia”,
- marża poniżej 20% – „Niska”.
- Przyznaj premię w wysokości 10% zysku, jeśli zlecenie jest zakończone i zysk wynosi co najmniej 100 zł. W pozostałych przypadkach premia wynosi 0 zł.
Wymaganie: wykorzystaj funkcje JEŻELI, ORAZ oraz podstawowe działania matematyczne. Formuły wpisz w pierwszym wierszu danych i skopiuj do końca tabeli.
4. Część II – funkcje zaawansowane
Utwórz arkusz Raport i wykonaj następujące obliczenia.
Zadanie 1. Podsumowanie finansowe
Oblicz:
- łączny przychód z zakończonych zleceń,
- łączny koszt zakończonych zleceń,
- łączny zysk,
- średnią marżę,
- liczbę zakończonych zleceń.
Zadanie 2. Analiza pracowników
Przygotuj tabelę pokazującą dla każdego pracownika:
- liczbę zakończonych zleceń,
- sumę wypracowanego zysku,
- sumę przyznanych premii,
- średnią wartość zlecenia.
Użyj funkcji LICZ.WARUNKI, SUMA.WARUNKÓW i ŚREDNIA.WARUNKÓW.
Zadanie 3. Wyszukiwanie danych
Przygotuj pole, w którym po wpisaniu ID zlecenia automatycznie wyświetlą się pracownik, usługa, status i zysk.
Wykorzystaj WYSZUKAJ.X albo połączenie INDEKS i PODAJ.POZYCJĘ.
Zadanie 4. Filtrowanie
Utwórz dodatkową tabelę wyświetlającą wyłącznie zakończone zlecenia, których zysk przekracza 100 zł.
Wykorzystaj funkcję FILTRUJ, jeżeli jest dostępna w używanej wersji arkusza. W przeciwnym razie użyj filtra zaawansowanego.
5. Część III – analiza usług
Utwórz arkusz Usługi i przygotuj zestawienie dla każdego rodzaju usługi.
Oblicz:
- liczbę wszystkich zleceń,
- liczbę zakończonych zleceń,
- sumę przychodów z zakończonych zleceń,
- sumę zysków,
- średnią marżę,
- udział danej usługi w całkowitym zysku serwisu.
Następnie:
- Posortuj usługi od najbardziej do najmniej rentownej.
- Wskaż usługę generującą największy zysk.
- Wskaż usługę o najniższej średniej marży.
- Zastosuj formatowanie warunkowe, aby wyróżnić trzy najbardziej rentowne usługi.
- Przygotuj tabelę przestawną pokazującą zysk według pracownika i usługi.
6. Część IV – panel menedżerski
Utwórz arkusz Dashboard, który będzie stanowił czytelny panel zarządzania serwisem.
Łączny przychód
Formuła
Tylko zakończone zlecenia
Łączny zysk
Formuła
Po odjęciu kosztów
Zakończone zlecenia
Formuła
Liczba zleceń
Średnia marża
Formuła
Wartość procentowa
Dodaj co najmniej trzy wykresy:
- wykres kolumnowy – zysk według pracowników,
- wykres kołowy – udział usług w zysku,
- wykres słupkowy – przychody według rodzaju usługi.
Panel powinien być czytelny, estetyczny i aktualizować się automatycznie po zmianie danych w arkuszu Zlecenia.
7. Część V – kontrola jakości danych
Wykonaj dodatkowe zadania:
- Zastosuj listę rozwijaną dla kolumny
Status, zawierającą wartości:Zakończone,W trakcie,Anulowane. - Zablokuj możliwość wpisania ujemnej liczby godzin, ujemnej ceny usługi i ujemnego kosztu części.
- Wyróżnij czerwonym kolorem zlecenia anulowane.
- Wyróżnij żółtym kolorem zlecenia, które są w trakcie realizacji.
- Zabezpiecz komórki zawierające formuły przed przypadkową edycją.
- Dodaj komunikat ostrzegawczy, jeśli cena usługi jest niższa niż koszt całkowity.
8. Część VI – Zadanie dla uczniów, którzy ukończą podstawową część projektu.
- A. Symulacja podwyżki: oblicz, jak zmieni się zysk, jeśli ceny wszystkich usług wzrosną o 8%, a koszty pozostaną bez zmian.
- B. Ranking pracowników: utwórz automatyczny ranking według łącznego zysku z zakończonych zleceń.
- C. Analiza progu rentowności: wyznacz minimalną cenę usługi zapewniającą marżę 30%, przy niezmienionych kosztach.
- D. Analiza scenariuszy: porównaj zysk przy wzroście kosztów części o 5%, 10% i 15%.
- E. Automatyczny raport: przygotuj komunikat, który wskazuje najbardziej rentowną usługę i pracownika z największym zyskiem.
Projekt: Budżet i analiza wyników firmy komputerowej
Cel zadania
Przygotuj arkusz kalkulacyjny do analizy sprzedaży firmy komputerowej. Wykorzystaj formuły, funkcje warunkowe, sortowanie, filtrowanie, formatowanie warunkowe oraz wykresy.
Część 1. Wprowadź dane
Utwórz arkusz Sprzedaż i wpisz poniższą tabelę.
| Produkt | Kategoria | Sprzedawca | Liczba sztuk | Cena netto | Koszt zakupu |
|---|---|---|---|---|---|
| Laptop Lenovo | Komputery | Nowak | 5 | 3200 | 2500 |
| Monitor 27″ | Monitory | Kowalski | 8 | 1100 | 750 |
| Mysz gamingowa | Akcesoria | Nowak | 20 | 150 | 80 |
| Klawiatura | Akcesoria | Wiśniewski | 15 | 220 | 130 |
| Laptop HP | Komputery | Kowalski | 4 | 3800 | 2900 |
| Drukarka laserowa | Drukarki | Wiśniewski | 6 | 750 | 520 |
| Monitor 24″ | Monitory | Nowak | 10 | 850 | 600 |
| Dysk SSD 1 TB | Pamięci | Kowalski | 12 | 350 | 220 |
| Laptop Dell | Komputery | Wiśniewski | 3 | 4500 | 3500 |
| Mysz biurowa | Akcesoria | Nowak | 25 | 70 | 35 |
| Drukarka kolorowa | Drukarki | Kowalski | 4 | 1200 | 850 |
| Dysk SSD 500 GB | Pamięci | Wiśniewski | 10 | 220 | 140 |
| Monitor 27″ | Monitory | Nowak | 5 | 1100 | 750 |
| Laptop Lenovo | Komputery | Kowalski | 6 | 3200 | 2500 |
| Klawiatura | Akcesoria | Wiśniewski | 12 | 220 | 130 |
Część 2. Obliczenia w arkuszu
Dodaj do tabeli cztery nowe kolumny:
| Kolumna | Nazwa | Sposób obliczenia |
|---|---|---|
| G | Przychód | Liczba sztuk × cena netto |
| H | Koszt całkowity | Liczba sztuk × koszt zakupu |
| I | Zysk | Przychód − koszt całkowity |
| J | Marża procentowa | Zysk / przychód × 100% |
Wykonaj obliczenia za pomocą formuł. Zastosuj odpowiednie formaty walutowe i procentowe.
Część 3. Funkcje Excela
Wykonaj poniższe polecenia:
- Oblicz łączny przychód ze wszystkich transakcji, korzystając z funkcji
SUMA. - Oblicz średni zysk z jednej pozycji sprzedaży, korzystając z funkcji
ŚREDNIA. - Znajdź największy i najmniejszy zysk za pomocą funkcji
MAXiMIN. - Policz liczbę pozycji sprzedaży, w których liczba sztuk przekracza 10, za pomocą
LICZ.JEŻELI. - Oblicz przychód wygenerowany przez sprzedawcę Nowak, korzystając z
SUMA.JEŻELI. - Za pomocą
JEŻELIdodaj kolumnęOcena sprzedaży:- zysk co najmniej 5000 zł – „Wysoki”;
- zysk co najmniej 2000 zł – „Średni”;
- zysk poniżej 2000 zł – „Niski”.
Część 4. Formatowanie i analiza danych
- Zmień zakres danych w tabelę Excela za pomocą
Ctrl + T. - Zastosuj formatowanie warunkowe:
- zysk powyżej 5000 zł – zielone tło;
- zysk od 2000 do 5000 zł – żółte tło;
- zysk poniżej 2000 zł – czerwone tło.
- Posortuj dane malejąco według zysku.
- Włącz filtrowanie i wyświetl wyłącznie produkty z kategorii
Komputery. - Wyświetl transakcje, w których marża wynosi co najmniej 25%.
Część 5. Wykresy i raport
Utwórz nowy arkusz o nazwie Raport.
- Przygotuj zestawienie przychodu każdego sprzedawcy.
- Wstaw wykres kolumnowy przedstawiający przychód według sprzedawców.
- Wstaw wykres kołowy pokazujący udział poszczególnych kategorii w całkowitym przychodzie.
- Dodaj nagłówek raportu, legendę i etykiety danych.
Część 6. Pytania podsumowujące
Odpowiedz na pytania, wykorzystując przygotowany arkusz:
- Który sprzedawca uzyskał największy przychód?
- Który produkt przyniósł największy zysk w pojedynczej pozycji sprzedaży?
- Która kategoria odpowiada za największą część przychodu?
- Czy produkt o najwyższej cenie sprzedaży ma również najwyższą marżę?
- Który sprzedawca uzyskał najwyższy łączny zysk?
Zadanie projektowe: „Optymalizacja budżetu wycieczki klasowej”
Klasa planuje 3-dniową wycieczkę dla 28 uczniów i 2 opiekunów. Dysponuje budżetem 24 000 zł.
Należy zaplanować wycieczkę tak, aby:
- zmieścić się w budżecie,
- zapewnić transport,
- zakwaterowanie,
- wyżywienie,
- co najmniej 3 atrakcje,
- pozostawić minimum 500 zł rezerwy.
Dane
| Element | Koszt |
|---|---|
| Autokar | 4 800 zł |
| Nocleg – osoba/noc | 120 zł |
| Śniadanie – osoba | 25 zł |
| Obiad – osoba | 40 zł |
| Kolacja – osoba | 30 zł |
| Muzeum | 25 zł/os. |
| Park nauki | 35 zł/os. |
| Centrum technologii | 40 zł/os. |
| Rejs | 45 zł/os. |
| Gra miejska | 20 zł/os. |
Polecenia
1. Obliczenia matematyczne
- Oblicz koszt zakwaterowania całej grupy na 2 noce.
- Oblicz koszt wyżywienia jednej osoby przez 3 dni, zakładając:
- 3 śniadania,
- 3 obiady,
- 2 kolacje.
- Oblicz koszt wyżywienia całej grupy.
- Oblicz koszt każdej atrakcji dla całej grupy.
- Ustal, ile pieniędzy pozostanie po opłaceniu transportu, noclegów i wyżywienia.
2. Analiza wariantów
Przygotuj co najmniej 4 różne warianty wycieczki, wybierając po 3 atrakcje.
Dla każdego wariantu oblicz:
- koszt atrakcji,
- koszt całej wycieczki,
- koszt przypadający na jednego uczestnika,
- pozostałą rezerwę.
3. Excel
W arkuszu kalkulacyjnym utwórz tabelę zawierającą:
| Wariant | Atrakcje | Transport | Noclegi | Wyżywienie | Atrakcje | Razem | Koszt/os. | Rezerwa |
|---|
Zastosuj:
- formuły,
- adresowanie względne i bezwzględne,
- funkcję
SUMA, - formatowanie warunkowe,
- sortowanie wariantów według kosztu.
4. Zadanie algorytmiczne
Zapisz w postaci listy kroków algorytm, który dla dowolnej liczby uczestników:
oblicza całkowity koszt wycieczki i sprawdza, czy wybrany wariant mieści się w określonym budżecie.
Algorytm powinien zawierać co najmniej jeden warunek:
JEŻELI koszt całkowity ≤ budżet, wyświetl „WYCIECZKA MOŻLIWA”, w przeciwnym przypadku „PRZEKROCZONO BUDŻET”.
5. Zadanie dla grupy chętnych – poziom rozszerzony
Załóż, że budżet zmniejszono do 20 000 zł.
Należy znaleźć taki zestaw 3 różnych atrakcji, aby:
- koszt był jak najmniejszy,
- rezerwa wynosiła co najmniej 500 zł,
- każda z trzech atrakcji była innego rodzaju.
Następnie odpowiedz:
Czy zmniejszenie budżetu o 4 000 zł wymusza zmianę planu wycieczki? Uzasadnij odpowiedź obliczeniami.
🖥️ Zadanie: „ANALIZA DANYCH – MŁODY ANALITYK”
Czas: 2 × 45 minut
Poziom: klasa 3 liceum, profil matematyczno-informatyczny
Program: Excel / LibreOffice Calc
Sytuacja:
Otrzymujesz dane dotyczące wyników 20 uczniów w trzech przedmiotach: matematyce, informatyce i języku angielskim. Twoim zadaniem jest przeanalizowanie danych i wyciągnięcie wniosków.
| Uczeń | Matematyka | Informatyka | Angielski | Frekwencja % |
|---|---|---|---|---|
| 1 | 78 | 92 | 81 | 96 |
| 2 | 65 | 74 | 88 | 91 |
| 3 | 92 | 96 | 79 | 98 |
| 4 | 54 | 61 | 72 | 83 |
| 5 | 87 | 85 | 94 | 97 |
| 6 | 71 | 79 | 76 | 89 |
| 7 | 43 | 58 | 64 | 76 |
| 8 | 96 | 91 | 87 | 99 |
| 9 | 68 | 72 | 91 | 93 |
| 10 | 81 | 88 | 83 | 95 |
| 11 | 59 | 63 | 70 | 85 |
| 12 | 74 | 81 | 78 | 92 |
| 13 | 89 | 94 | 90 | 98 |
| 14 | 62 | 69 | 85 | 88 |
| 15 | 77 | 84 | 73 | 94 |
| 16 | 48 | 55 | 67 | 79 |
| 17 | 94 | 97 | 92 | 100 |
| 18 | 69 | 76 | 80 | 90 |
| 19 | 83 | 89 | 86 | 96 |
| 20 | 57 | 66 | 71 | 84 |
Polecenia
1. Przygotuj arkusz kalkulacyjny i wprowadź powyższe dane.
2. Dodaj kolumnę „Średnia” i oblicz średnią z trzech przedmiotów dla każdego ucznia.
3. Dodaj kolumnę „Ocena” według zasad:
- średnia ≥ 90 → 6
- 80–89,99 → 5
- 70–79,99 → 4
- 60–69,99 → 3
- 50–59,99 → 2
- poniżej 50 → 1
Użyj formuły, a nie wpisuj ocen ręcznie.
4. Oblicz:
- średnią klasy z matematyki,
- średnią klasy z informatyki,
- średnią klasy z angielskiego,
- średnią frekwencję,
- najwyższy wynik z każdego przedmiotu,
- najniższy wynik z każdego przedmiotu.
5. Wykorzystaj funkcje MAX, MIN, ŚREDNIA oraz JEŻELI.
6. Posortuj uczniów od najwyższej do najniższej średniej.
7. Zastosuj formatowanie warunkowe:
- średnia ≥ 90 – wyróżnienie,
- średnia < 60 – ostrzeżenie.
8. Utwórz wykres przedstawiający średnie wyniki klasy z trzech przedmiotów.
Sprawdź, czy można zauważyć zależność pomiędzy frekwencją a średnią ocen.
Podziel uczniów na dwie grupy:
- frekwencja ≥ 90%,
- frekwencja < 90%.
Oblicz średnią ocen dla obu grup.
Napisz 3–4 zdania wniosku, odpowiadając na pytanie:
Czy w przygotowanym zbiorze danych wyższa frekwencja wiąże się z wyższą średnią wyników?
Utwórz dodatkową kolumnę „Wskaźnik ucznia”, którego wartość będzie liczona według wzoru:
Wskaźnik = 0,4 × Matematyka + 0,4 × Informatyka + 0,2 × Angielski
Następnie:
- oblicz wskaźnik dla każdego ucznia,
- znajdź ucznia z najwyższym wskaźnikiem,
- utwórz ranking uczniów,
- przedstaw wyniki na wykresie,
- wyjaśnij, dlaczego uczeń z najwyższą średnią nie musi mieć najwyższego wskaźnika.
