Do nauki po wolnym weekendzie – będą kartkówki co tydzień !!
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.
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”.




odpowiedzi
arkusz RAPORT
Utwórz arkusz Raport.
Zadanie 1. Podsumowanie finansowe
Przyjmij następujący układ komórek:
| Komórka | Opis | Formuła |
| B2 | Łączny przychód | =SUMA.WARUNKÓW(Zlecenia!G2:G16;Zlecenia!H2:H16;"Zakończone") |
| B3 | Łączny koszt | =SUMA.WARUNKÓW(Zlecenia!J2:J16;Zlecenia!H2:H16;"Zakończone") |
| B4 | Łączny zysk | =SUMA.WARUNKÓW(Zlecenia!K2:K16;Zlecenia!H2:H16;"Zakończone") |
| B5 | Średnia marża | =ŚREDNIA.WARUNKÓW(Zlecenia!L2:L16;Zlecenia!H2:H16;"Zakończone") |
| B6 | Liczba zakończonych zleceń | =LICZ.JEŻELI(Zlecenia!H2:H16;"Zakończone") |
Prawidłowe wyniki
Łączny przychód
3 070 zł
Łączny koszt
2 335 zł
Łączny zysk
735 zł
Średnia marża
26,97%
Zakończone zlecenia
11
Uwaga: średnia marża 26,97% jest średnią arytmetyczną marż poszczególnych zakończonych zleceń. Marża liczona od łącznego przychodu i zysku wynosi 23,94%.
Zadanie 2. Analiza pracowników
W arkuszu Raport przygotuj tabelę:
| Pracownik | Liczba zleceń | Łączny zysk | Premie | Średnia wartość zlecenia |
| Kowalski | 4 | 305 zł | 0 zł | 262,50 zł |
| Nowak | 3 | 190 zł | 0 zł | 290 zł |
| Wiśniewski | 4 | 240 zł | 12 zł | 287,50 zł |
Zakładamy, że nazwiska znajdują się w komórkach A10:A12.
Formuły dla pierwszego pracownika (wiersz 10):
Liczba zakończonych zleceń:
=LICZ.WARUNKI(Zlecenia!B$2:B$16;A10;Zlecenia!H$2:H$16;"Zakończone")Łączny zysk:
=SUMA.WARUNKÓW(Zlecenia!K$2:K$16;Zlecenia!B$2:B$16;A10;Zlecenia!H$2:H$16;"Zakończone")Łączne premie:
=SUMA.WARUNKÓW(Zlecenia!N$2:N$16;Zlecenia!B$2:B$16;A10)Średnia wartość zlecenia:
=ŚREDNIA.WARUNKÓW(Zlecenia!G$2:G$16;Zlecenia!B$2:B$16;A10;Zlecenia!H$2:H$16;"Zakończone")Przeciągnij formuły do wiersza 12.
Zadanie 3. Wyszukiwanie danych
Przygotuj pole wyszukiwania:
- B2 – wpisywany numer ID,
- B3 – pracownik,
- B4 – usługa,
- B5 – status,
- B6 – zysk.
Przykładowo wpisz w B2 numer 1009.
Formuła dla pracownika:
=WYSZUKAJ.X(B2;Zlecenia!A2:A16;Zlecenia!B2:B16;"Brak danych")Formuła dla usługi:
=WYSZUKAJ.X(B2;Zlecenia!A2:A16;Zlecenia!C2:C16;"Brak danych")Formuła dla statusu:
=WYSZUKAJ.X(B2;Zlecenia!A2:A16;Zlecenia!H2:H16;"Brak danych")Formuła dla zysku:
=WYSZUKAJ.X(B2;Zlecenia!A2:A16;Zlecenia!K2:K16;"Brak danych")Przykładowy wynik wyszukiwania ID 1009
| Pole | Wynik |
| Pracownik | Wiśniewski |
| Usługa | Czyszczenie PC |
| Status | Zakończone |
| Zysk | 120 zł |
ecenia!H$2:H$16;”Zakończone”)
Zysk:
=SUMA.WARUNKÓW(Zlecenia!K$2:K$16;Zlecenia!C$2:C$16;A2;Zlecenia!H$2:H$16;”Zakończone”)
Średnia marża:
=ŚREDNIA.WARUNKÓW(Zlecenia!L$2:L$16;Zlecenia!C$2:C$16;A2;Zlecenia!H$2:H$16;”Zakończone”)
Udział w zysku:
=E2/SUMA($E$2:$E$6)
Skopiuj formuły do wiersza 6.
Wnioski z analizy
- Najbardziej zyskowną usługą jest czyszczenie PC – 280 zł zysku.
- Najniższą średnią marżę ma wymiana SSD – 11,21%.
- Czyszczenie PC odpowiada za 38,10% całego zysku serwisu.
- Instalacja systemu zajmuje drugie miejsce pod względem wygenerowanego zysku – 175 zł.
Tabela przestawna
Utwórz tabelę przestawną na podstawie danych z arkusza Zlecenia.
Ustaw pola:
- Wiersze: Pracownik.
- Kolumny: Usługa.
- Wartości: Zysk – suma.
- Filtr: Status – wybierz Zakończone.
W ten sposób otrzymasz analizę zysków poszczególnych pracowników z podziałem na rodzaje usług.
5. Część IV – panel menedżerski DASHBOARD
Arkusz Dashboard powinien prezentować najważniejsze informacje w postaci dużych wartości liczbowych i wykresów.
Wskaźniki KPI
Wstaw następujące formuły:
| Wskaźnik | Formuła |
| Przychód | =Raport!B2 |
| Koszty | =Raport!B3 |
| Zysk | =Raport!B4 |
| Średnia marża | =Raport!B5 |
| Liczba zleceń | =Raport!B6 |
Wykres 1 – zysk według pracowników
Wykorzystaj dane z tabeli pracowników w arkuszu Raport.
Zysk według pracowników
Zysk z zakończonych zleceń
0 zł85 zł170 zł255 zł340 złKowalskiNowakWiśniewski
Wykres 2 – udział usług w zysku
Udział usług w zysku
Procentowy udział poszczególnych usług w zysku serwisu
Czyszczenie PC
Diagnostyka
Instalacja systemu
Wymiana SSD
Wymiana zasilacza
Wykres 3 – przychody według usług
Przychody według usług
Przychody wyłącznie z zakończonych zleceń
Diagnostyka
Przychód450 zł
0 zł300 zł600 zł900 zł1,2 tys. złDiagnostykaWymiana SSDInstalacja systemuCzyszczenie PCWymiana zasilacza
