Zadanie z makr arkusza kalkulacyjnego
Pliki do zadania:
Podczas wykonywania zadań, nie wolno modyfikować dwóch arkuszy z surowymi danymi, czyli arkuszy Klient
oraz Zakupy
.
Część 0 – Zapoznaj się z makrem
Zdecyduj, czy będziesz pracował w języku Python czy VBA. Jeśli używasz VBA:
- Upewnij się, że w opcjach arkusza masz włączoną chorągiewkę
Deweloper
; - Pobierz plik
Dane_vba.xlsm
; - Otwórz
Dane_vba.xlsm
, wyraź zgodę na uruchamianie makr i w chorągiewceDeweloper
uruchom makroclear_data
; - Przeanalizuj kod makra w VBA.
Jeśli używasz Pythona (informacje o makrach w Pythonie będą na wykładzie 21 czerwca, ale analiza kodu makra może wystarczyć do zrobienia zadania, ponieważ Pythona używaliśmy już wcześniej):
- Uruchom
Anaconda Command Prompt
(lub BASH'a i aktywuj środowiskobase
Anacondy) i wywołaj komendęxlwings addin install
. Doda to nową chorągiewkę do Excela –xlwings
; - Pobierz pliki
Dane_vba.xlsm
orazDane_vba.py
i umieść je w tym samym katalogu; - Otwórz plik
Dane_vba.xlsm
i w chorągiewcexlwings
kliknij przycisk „play”, aby uruchomić makro zapisane w funkcjimain
w pliku Pythona o tej samej nazwie co arkusz; - Otwórz plik
Dane_vba.py
i przeanalizuj kod makra w Pythonie.
Część 1 – Dane klient
- Zmodyfikuj kod makra tak, aby wyznaczał dodatkowo wiek każdego klienta w latach w dniu 13 czerwca 2024 roku. Wiek zapisz w kolumnie o nazwie
Wiek
.
Część 2 – Dane zakupy
- Za pomocą makra przygotuj dane z arkusza
Zakupy
tak, aby miały odpowiedni typ i zapisz je do arkuszaZakupyC
. Możesz wzorować się na istniejącym makrze, ale stwórz nową procedurę.
Część 3 – Analiza zakupów
Za pomocą makra, tabel przestawnych lub formuł przygotuj arkusz o nazwie Statystyki zakupów
, w którym będą dane dla klientów, którzy wykonali choć jeden zakup. Umieść w nim:
- numer klienta,
- datę pierwszych zakupów,
- datę ostatnich zakupów,
- liczbę zakupów,
- sumę kupionych produktów oraz
- sumę wydanych pieniędzy.
Część 4 – Połączenie danych
Za pomocą makra, tabel przestawnych lub formuł przygotuj osobny arkusz o nazwie Statystyki klientów
, zawierający dane w następującej postaci:
- imię,
- nazwisko,
- wiek klienta,
- wiek klienta w postaci skategoryzowanej, na przykład na przedziały 20-30, 30-40, 40-50, 50-60, 60-70,
- data pierwszego zakupu,
- data ostatniego zakupu,
- liczbę wykonanych zakupów,
- liczba wszystkich zakupionych produktów przez klienta,
- sumę wydatków klienta w sklepie.
Zadbaj o to, aby w odpowiednich miejscach były braki danych lub zera, jeśli któryś z klientów nie wykonał ani jednego zakupu.
Część 5 – Przygotuj raport
Za pomocą makr, tabel przestawnych lub formuł przygotuj raport w formie tabel i wykresów. Wszystkie wyniki umieść w jednym arkuszu o nazwie Wyniki
. Raport powinien zawierać:
- informacje o sklepie (ilu klientów jest w sklepie, jaki jest całkowity obrót, kiedy dokonano ostatniej sprzedaży),
- imiona i nazwiska najlepszych klientów (kto kupił najwięcej produktów, kto zostawił najwięcej gotówki w sklepie, kto najwięcej razy kupował, kto najdawniej zrobił pierwszy zakup),
- wykres rozrzutu zależności sumy wydatków od wieku klienta,
- wykres słupkowy przedstawiający liczbę klientów w różnych kategoriach wiekowych,
- wykres kołowy przedstawiający procent klientów, którzy zrobili choć jedne zakupy.