Arkusze kalkulacyjne

Solver

Solver to narzędzie pozwalające zoptymalizować (zmaksymalizować lub zminimalizować) pewną wartość poprzez zmianę konkretnych komórek. Solver może ponadto brać pod uwagę pewne dodatkowe ogranicznenia. Najprostszym przykładem jest tzw. zagadnienie diety: mojąc tablicę wartości odżywczych składników i ich cenę, skomponować możliwie najtańszy posiłek dostarczający organizmowi wszystkiego co potrzeba. Oczywiście powyższe sformułowanie jest tylko przykładowe, w praktyce często spotyka się problemy sprowadzające się do rozwiązania podobnego zagadnienia. Optymalizacja nie jest zagadnieniem latwym. O ile wszystkie ograniczenia są liniowe (i zmiennych nie ma zbyt wiele), możemy skorzystać z tzw. algorytmu sympleksowego zaimplemetowanego w arkuszach kalkulacyjnych. Jeżeli optymalizowana wartość lub ograniczenia nie są liniowe, często trzeba będzie skorzystać z innych metod, nie zawsze dostępnych z poziomu arkusza kalkulacyjnego.

Aby włączyć solver w MS Excel: https://support.office.com/en-us/article/Load-the-Solver-Add-in-612926fc-d53b-46b4-872c-e24772f078ca.

Zadanie 1. (Zagadnienie produkcji) Jednoosobowe PPH Poduszkowiec produkuje artystyczne poduszki w dwóch wielkościach. Wyprodukowanie małej poduszki wymaga 0.5 m2 tkaniny, 200 g wypełnienia i 15 minut pracy. Wyprodukowanie dużej poduszki wymaga 0.85 m2 tkaniny, 500 g wypełnienia i 20 minut pracy. Zysk na małej poduszce to 30 zł, na dużej 50 złotych. Dostawca jest w stanie dostarczyć 20 m2 tkaniny dziennie, 10 kg wypełnienia. Ile poduszek każdego rodzaju należy produkować, by osiągnąc najwyższy możliwy zysk, zakładając, że poduszki produkujemy tylko przez 8h dziennie?

Tabela przestawna

Wyobraźmy sobie duży zbiór danych, które chcemy poddać jakiejś analizie. Dla przykładu mogłyby to być dane o wszystkich zakupach na Allegro w ciągu ostatniego miesiąca czy odpowiedzi respondentów spisu powszechnego. Każdy element zbioru danych może być opisany ogromną ilością informacji, ale z reguły w danym momencie interesuje nasz stosunkowo niewielki zbiór informacji, np. kiedy jest największy ruch na serwerach i czy wygląda, że przed nadchodzącymi świętami Allegro musi kupić/wynając dodatkowe serwery czy jak wygląda struktura ludności w każdym województwie.

Stosunkowo prostym narzędziem służącym do tego zadania jest tabela przestawna (ang. pivot table). Tabela pozwala pogrupować dane na podstawie jakiejś cechy lub cech (np. województwo i wiek) i zagregować jakieś inne wartości (np. wyliczyć średni dochód w zależności od województwa i wieku).

Tabela przestawna nie ma zbyt dużych możliwości, ale jest łatwo dostępna w arkuszach kalkulacyjnych i w praktyce często wystarczy do szybkiej analizy danych. W przypadku bardziej skomplikowanych zagadnień zachodzi potrzeba użycia bardziej wyspecjalizowanego narzędzia: analitycznych baz danych i zapytań MDX. Te i podobne metody obróbki danych są częścia tzw. Business Intelligence.

Zadanie 2. W pliku airports_and_runways.xls (plik skompresowany algorytmem XZ) znajdują się tabele lotnisk i pasów startowych z całego świata. Odpowiedz na poniższe pytania:

Zadanie 3. W pliku data.csv (plik skompresowany algorytmem XZ) znajdują się odpowiedzi 30000 respondentów amerykańskiego spisu powszechnego. W pliku dataSpec.txt znajduje się opis znaczenia poszególnych pól wraz z opisem przyjętych parametrów. Stwórz 3 tabele przestawne pokazujące (najlepiej interesujące) zależności w tych danych. Odczytanie tabeli przestawnej nie powinno wymagać znajomości pliku dataSpec.txt – przydatna będzie funkcja WYSZUKAJ.PIONOWO (ang. VLOOKUP). Staraj się jak najmniej zmieniać dane żródłowe. Przy pracy nad zadaniem pomocny może być plik data.short.csv zawierający pierwsze tysiąc wierszy dużego pliku data.csv.

Zadanie 4. (Opcjonalne, dodatkowe 4 punkty) Znajdź interesujący, publicznie dostępny duży zbiór rzeczywistych danych w formie tabelarycznej, najlepiej aby był to zbiór w miarę aktualny. UWAGA: 4 punkty za zbiór danych zostaną podzielone po równo między wszystkich, którzy dany zbiór znajdą.

Przy przesyłaniu rozwiązań, proszę usunąć z plików tabele danych, bez tego nie zmieścimy się w limicie 2MB.