3MIF

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.

IDPracownikUsługaLiczba godz.Stawka za godz. (zł)Części (zł)Cena usługi (zł)Status
1001KowalskiDiagnostyka1800150Zakończone
1002NowakWymiana SSD290250480Zakończone
1003WiśniewskiInstalacja systemu28530280Zakończone
1004KowalskiCzyszczenie PC1,58020220Zakończone
1005NowakModernizacja RAM190180300W trakcie
1006WiśniewskiDiagnostyka2850150Zakończone
1007KowalskiWymiana zasilacza1,580210390Zakończone
1008NowakInstalacja systemu2,59040320Anulowane
1009WiśniewskiCzyszczenie PC18515220Zakończone
1010KowalskiModernizacja RAM1,580200340W trakcie
1011NowakDiagnostyka1900150Zakończone
1012WiśniewskiWymiana SSD285270500Zakończone
1013KowalskiInstalacja systemu28035290Zakończone
1014NowakCzyszczenie PC1,59025240Zakończone
1015WiśniewskiWymiana zasilacza285230410W 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:

  1. Oblicz koszt robocizny jako iloczyn liczby godzin i stawki godzinowej.
  2. Oblicz koszt całkowity jako sumę kosztu robocizny i kosztu części.
  3. Oblicz zysk jako różnicę między ceną usługi a kosztem całkowitym.
  4. Oblicz marżę procentową jako stosunek zysku do ceny usługi.
  5. 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”.
  6. 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:

  1. Posortuj usługi od najbardziej do najmniej rentownej.
  2. Wskaż usługę generującą największy zysk.
  3. Wskaż usługę o najniższej średniej marży.
  4. Zastosuj formatowanie warunkowe, aby wyróżnić trzy najbardziej rentowne usługi.
  5. 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:

  1. Zastosuj listę rozwijaną dla kolumny Status, zawierającą wartości: Zakończone, W trakcie, Anulowane.
  2. Zablokuj możliwość wpisania ujemnej liczby godzin, ujemnej ceny usługi i ujemnego kosztu części.
  3. Wyróżnij czerwonym kolorem zlecenia anulowane.
  4. Wyróżnij żółtym kolorem zlecenia, które są w trakcie realizacji.
  5. Zabezpiecz komórki zawierające formuły przed przypadkową edycją.
  6. 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ę.

ProduktKategoriaSprzedawcaLiczba sztukCena nettoKoszt zakupu
Laptop LenovoKomputeryNowak532002500
Monitor 27″MonitoryKowalski81100750
Mysz gamingowaAkcesoriaNowak2015080
KlawiaturaAkcesoriaWiśniewski15220130
Laptop HPKomputeryKowalski438002900
Drukarka laserowaDrukarkiWiśniewski6750520
Monitor 24″MonitoryNowak10850600
Dysk SSD 1 TBPamięciKowalski12350220
Laptop DellKomputeryWiśniewski345003500
Mysz biurowaAkcesoriaNowak257035
Drukarka kolorowaDrukarkiKowalski41200850
Dysk SSD 500 GBPamięciWiśniewski10220140
Monitor 27″MonitoryNowak51100750
Laptop LenovoKomputeryKowalski632002500
KlawiaturaAkcesoriaWiśniewski12220130

Część 2. Obliczenia w arkuszu

Dodaj do tabeli cztery nowe kolumny:

KolumnaNazwaSposób obliczenia
GPrzychódLiczba sztuk × cena netto
HKoszt całkowityLiczba sztuk × koszt zakupu
IZyskPrzychód − koszt całkowity
JMarża procentowaZysk / przychód × 100%

Wykonaj obliczenia za pomocą formuł. Zastosuj odpowiednie formaty walutowe i procentowe.

Część 3. Funkcje Excela

Wykonaj poniższe polecenia:

  1. Oblicz łączny przychód ze wszystkich transakcji, korzystając z funkcji SUMA.
  2. Oblicz średni zysk z jednej pozycji sprzedaży, korzystając z funkcji ŚREDNIA.
  3. Znajdź największy i najmniejszy zysk za pomocą funkcji MAX i MIN.
  4. Policz liczbę pozycji sprzedaży, w których liczba sztuk przekracza 10, za pomocą LICZ.JEŻELI.
  5. Oblicz przychód wygenerowany przez sprzedawcę Nowak, korzystając z SUMA.JEŻELI.
  6. Za pomocą JEŻELI dodaj 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

  1. Zmień zakres danych w tabelę Excela za pomocą Ctrl + T.
  2. 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.
  3. Posortuj dane malejąco według zysku.
  4. Włącz filtrowanie i wyświetl wyłącznie produkty z kategorii Komputery.
  5. 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:

  1. Który sprzedawca uzyskał największy przychód?
  2. Który produkt przyniósł największy zysk w pojedynczej pozycji sprzedaży?
  3. Która kategoria odpowiada za największą część przychodu?
  4. Czy produkt o najwyższej cenie sprzedaży ma również najwyższą marżę?
  5. 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

ElementKoszt
Autokar4 800 zł
Nocleg – osoba/noc120 zł
Śniadanie – osoba25 zł
Obiad – osoba40 zł
Kolacja – osoba30 zł
Muzeum25 zł/os.
Park nauki35 zł/os.
Centrum technologii40 zł/os.
Rejs45 zł/os.
Gra miejska20 zł/os.

Polecenia

1. Obliczenia matematyczne

  1. Oblicz koszt zakwaterowania całej grupy na 2 noce.
  2. Oblicz koszt wyżywienia jednej osoby przez 3 dni, zakładając:
    • 3 śniadania,
    • 3 obiady,
    • 2 kolacje.
  3. Oblicz koszt wyżywienia całej grupy.
  4. Oblicz koszt każdej atrakcji dla całej grupy.
  5. 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ą:

WariantAtrakcjeTransportNoclegiWyżywienieAtrakcjeRazemKoszt/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ńMatematykaInformatykaAngielskiFrekwencja %
178928196
265748891
392967998
454617283
587859497
671797689
743586476
896918799
968729193
1081888395
1159637085
1274817892
1389949098
1462698588
1577847394
1648556779
17949792100
1869768090
1983898696
2057667184

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:

  1. oblicz wskaźnik dla każdego ucznia,
  2. znajdź ucznia z najwyższym wskaźnikiem,
  3. utwórz ranking uczniów,
  4. przedstaw wyniki na wykresie,
  5. wyjaśnij, dlaczego uczeń z najwyższą średnią nie musi mieć najwyższego wskaźnika.

Możliwość komentowania została wyłączona.