Przepis na

Jak powstał arkusz „zarobki.ods”?

• Autor: Mirosław Zalewski
• 17 września 2013
• Możliwość komentowania Jak powstał arkusz „zarobki.ods”? została wyłączona

Dotychczas dzięki liście pracowników fikcyjnej korporacji mogliśmy nauczyć się tworzyć tabele przestawne oraz zestawienia sum częściowych. Ale to jeszcze nie wszystko — samodzielne odtworzenie tego arkusza jest dobrym pretekstem do opanowania funkcji LOS, WYSZUKAJ.PIONOWO oraz WYBIERZ, a także LOS.ZAKR, ZAOKR.DO.WIELOKR, FRAGMENT.TEKSTU i ZNAJDŹ. Wszystkim z nich przyjrzymy się w artykule.

Tworzenie szkieletu bazy danych

Tworzenie bazy danych powinniśmy zacząć od przygotowania szkieletu, czyli wykazu informacji, jakie w ogóle chcemy uwzględnić. Wpisujemy je w pierwszym wierszu.

W swoim arkuszu przewidziałem kolumnę „imię i nazwisko”, ale jej wypełnienie wykracza poza zakres naszego ćwiczenia. Ponieważ Calc nie oferuje żadnej funkcji do generowania losowych danych osobowych, trzeba skorzystać z innych źródeł. Osobiście wykorzystałem stronę internetową fakenamegenerator.com.

Dla ułatwienia, w tym artykule pominiemy te dwa kroki i rozpoczniemy pracę od następnego etapu. Pobieramy arkusz zarobki.ods i usuwamy zakres C2:H301.

Dobieranie płci na podstawie imienia (JEŻELI, FRAGMENT.TEKSTU, ZNAJDŹ)

Wypełnianie danych możemy rozpocząć od lewej strony. W pierwszej kolejności przypiszemy płeć do każdego z pracowników.

Nasze zadanie jest ułatwione dzięki specyfice języka polskiego, w którym wszystkie imiona kobiece kończą się na samogłoskę „a” (ale żadne imię męskie nie kończy się na tę literę). Wystarczy więc spojrzeć na imię pracownika — jeżeli kończy się ono na „a”, to w kolumnie C należy wpisać „Kobieta”; w przeciwnym wypadku należy wpisać „Mężczyzna”. Czynność tę możemy wykonać automatycznie dzięki odpowiedniej formule.

Gdybym bazę danych zaprojektował inaczej i przewidział osobne kolumny na imię i nazwisko, do poznania ostatniej litery imienia moglibyśmy wykorzystać funkcję PRAWY. Ja jednak zdecydowałem obie informacje umieścić w jednej kolumnie, dlatego musimy skorzystać z funkcji FRAGMENT.TEKSTU.

Funkcja ta przyjmuje trzy argumenty. Pierwszym jest ciąg znaków, na którym ma pracować. Drugim jest liczba określająca znak, od którego ma rozpocząć działanie (od lewej strony, licząc od 1). Ostatnim jest liczba znaków, które mają zostać zwrócone.

Pierwsze dwa argumenty są oczywiste (komórka w kolumnie B, od początku). Ale skąd mamy wiedzieć, ile liter ma zwrócić funkcja, gdy imiona mają różną długość?

Możemy tutaj wykorzystać fakt, że imię od nazwiska oddziela spacji. Jeżeli więc poznamy pozycję odstępu, będziemy wiedzieć, ile znaków ma imię.

Pomoże nam w tym funkcja ZNAJDŹ. Przyjmuje ona trzy argumenty: pierwszym jest poszukiwany ciąg znaków; drugim — tekst, w którym ma nastąpić wyszukiwanie. Trzeci określa pozycję, od której szukanie ma się rozpocząć. Możemy go pominąć, dzięki czemu funkcja rozpocznie poszukiwanie od pierwszego symbolu.

Łącząc podane funkcje jesteśmy w stanie odczytać imię pracownika, które następnie możemy przekazać funkcji PRAWY w celu odczytania ostatniej litery:

=PRAWY(FRAGMENT.TEKSTU(B2;1;ZNAJDŹ(" "; B2)-1);1)

Zostańmy na chwilę przy tej formule. Przecież funkcja FRAGMENT.TEKSTU może pracować od dowolnego symbolu w podanym ciągu znaków. Czy nie lepiej więc kazać jej rozpocząć działanie od ostatniego znaku imienia (znaku przed spacją) i nakazać dostarczenie jednej litery? W ten sposób pozbędziemy się niepotrzebnego wywołania funkcji PRAWY.

=FRAGMENT.TEKSTU(B2;ZNAJDŹ(" ";B2)-1;1)

Teraz, gdy znamy już sposób na wydobycie ostatniej litery z imienia, pozostaje nam tylko sprawdzić, czy jest to „a”. Gdy porównanie to wypadnie pomyślnie, chcemy otrzymać tekst „Kobieta”; w przeciwnym wypadku interesuje nas „Mężczyzna”. Operację tę wykonuje funkcja JEŻELI:

=JEŻELI(FRAGMENT.TEKSTU(B2;ZNAJDŹ(" ";B2)-1;1)="a";"Kobieta";"Mężczyzna")

Kopiowanie formuły do wszystkich wierszy

Następnym krokiem jest skopiowanie formuły do pozostałych wierszy w kolumnie. Możemy to zrobić na trzy różne sposoby:

• Zaznaczając cały zakres (najprościej jest go wpisać w polu na lewo od paska formuły) i wybierając z menu Edycja → Wypełnienie → W dół.

  

• Łapiąc mały kwadracik w prawym dolnym rogu aktywnej komórki lewym przyciskiem myszy i „przeciągając” go do końca pożądanego zakresu.

  

• Klikając dwukrotnie lewym przyciskiem myszy wspomniany kwadracik.

Dobrym pomysłem jest również zastąpienie formuł ich wynikiem. Dzięki temu arkusz nie będzie ich wykonywał przy każdym otwarciu pliku.

W tym celu zaznaczamy całą kolumnę z danymi, kopiujemy ją (Ctrl + C) i wklejamy specjalnie (Edycja → Wklej specjalnie... lub Ctrl + Shift + V). W nowym oknie, na liście Wybór zaznaczamy wszystko oprócz Wklej wszystko oraz Formuły.

Automatyczne generowanie losowej daty (LOS.ZAKR, DATA.WARTOŚĆ)

Następna kolumna zawiera datę zatrudnienia pracownika. Osobiście zdecydowałem się losować dzień z zakresu między 1 stycznia 1995 roku a 31 grudnia 2013 roku. Możemy go otrzymać na dwa różne sposoby; ponieważ oba wykorzystują funkcje LOS.ZAKR oraz DATA.WARTOŚĆ, przyjrzyjmy im się przed przystąpieniem do pracy.

Pierwsza z nich zwraca liczbę całkowitą zawierającą się w zakresie określonym przez jej dwa argumenty (włącznie).

Druga służy do zamiany tekstu na datę. Mówiąc precyzyjniej: konwertuje tekst przedstawiający datę w formacie rozpoznawalnym przez pakiet na liczbę reprezentującą tę datę w wewnętrznych obliczeniach arkusza.

Instynktowne podejście do generowania losowych dat polega na wygenerowaniu trzech liczb (po jednej dla roku, miesiąca i dnia), połączeniu ich w jeden ciąg znaków i przekształceniu na rzeczywistą datę. Służy do tego następująca formuła:

=DATA.WARTOŚĆ(ZŁĄCZ.TEKSTY(LOS.ZAKR(1995;2013);"-";LOS.ZAKR(1;12);"-";LOS.ZAKR(1;30)))

Niestety, ma ona dwie wady. Po pierwsze, nigdy nie wygeneruje ostatniego dnia miesięcy liczących 31 dni. Po drugie, zwróci błąd w rzadkich przypadkach, gdy utworzona zostanie nieistniejąca data (taka jak 30 lutego 2000 roku).

Ograniczenia te z łatwością usuniemy, gdy zdamy sobie sprawę, że Calc daty wewnętrznie przechowuje jako liczby. Wystarczy więc wylosować liczbę z zakresu pomiędzy liczbami oznaczającymi daty graniczne:

=LOS.ZAKR(DATA.WARTOŚĆ("1995-01-01");DATA.WARTOŚĆ("2013-12-31"))

Niezależnie od tego, z której metody skorzystaliśmy, musimy jeszcze przejść do ustawień komórki (prawy przycisk myszy → Formatuj komórki...) i na karcie Liczby ustawić wyświetlanie w postaci daty.

Generowanie liczb losowych na podstawie własnego rozkładu prawdopodobieństwa (WYSZUKAJ.PIONOWO, LOS)

Przed wypełnieniem danymi następnej kolumny, musimy się na chwilę zatrzymać.

Otóż powinniśmy wiedzieć, że LibreOffice korzysta z generatora liczb losowych gwarantującego rozkład jednostajny zwróconych wartości. Oznacza to, że każda wartość (liczba w podanym zakresie) ma jednakowe prawdopodobieństwo zostania wylosowaną.

Zastanówmy się, jak wpłynie to na strukturę naszej fikcyjnej korporacji. Wyróżniłem w niej pięć działów. Zostaną one przyporządkowane do pracowników losowo, ale z identycznym prawdopodobieństwem (równym 1/5). Możemy więc przypuszczać, że w każdym dziale znajdzie się ok. 1/5 ogółu pracowników firmy. W rezultacie liczba pracowników zarządu będzie w przybliżeniu taka sama, jak liczba pracowników w dziale obsługi klienta.

Oczywiście żadna prawdziwa korporacja nie posiada takiej „zastojowej” struktury. Co więcej, późniejsza analiza tak przygotowanej bazy danych będzie dość nudna.

Dlatego my przygotujemy dane w taki sposób, aby odpowiadały one arbitralnie określonemu rozkładowi. Wykorzystamy do tego funkcje LOS oraz WYSZUKAJ.PIONOWO.

Pierwsza z nich zwraca liczbę z zakresu 0‒1, włącznie.

Druga jest bardziej skomplikowana i przyjmuje aż cztery argumenty. Pierwszym jest poszukiwana wartość. Gdzie poszukiwana? W pierwszej kolumnie zakresu zdefiniowanego przy pomocy drugiego argumentu. Gdy wartość ta zostanie znaleziona, funkcja odczytuje zawartość komórki w tym samym wierszu, ale z kolumny określonej przy pomocy trzeciego argumentu. Jeżeli czwarty argument został podany i wynosi 1, informuje funkcję, że pierwsza kolumna zakresu zawiera liczby uszeregowane w kolejności rosnącej. Dzięki temu gdy poszukiwana wartość nie zostanie znaleziona, odczytana zostanie zawartość komórki z ostatniego wiersza zawierającego liczbę mniejszą od szukanej.

Zaczniemy od kolumny zawierającej dział, ponieważ — aby dane były jeszcze ciekawsze — w każdym dziale chcemy mieć odmienne rozkłady rodzaju umowy, stanowisk oraz zarobków.

Przede wszystkim, gdzieś w nieużywanej części arkusza, jeden pod drugim, wypisujemy działy firmy (Obsługa klienta, Badania i rozwój, Marketing, Księgowość oraz Zarząd). Następnie określamy, jaką część pracowników ma zatrudniać każdy z działów (mówiąc bardziej precyzyjnie: prawdopodobieństwo wylosowania każdego z nich). Możemy to zrobić na prawo od nazw działów.

Kolejnym krokiem jest przekształcenie naszych liczb na zapis zrozumiały dla funkcji WYSZUKAJ.PIONOWO. Jak pamiętamy, zakłada ona, że liczby są uszeregowane w kolejności rosnącej — oznacza to, że musimy sumować prawdopodobieństwa dla kolejnych działów. Obok każdego z nich podajemy dolną granicę przedziału prawdopodobieństwa (jeżeli poszukiwana liczba nie zostanie znaleziona, funkcja odczyta wartość z wcześniejszego wiersza).

Teraz już tylko pozostaje nam wprowadzić formułę:

=WYSZUKAJ.PIONOWO(LOS();$K$2:$L$6;2)

Zwróćmy przy tym uwagę, że zakres został określony za pomocą adresowania absolutnego. Gdybyśmy skorzystali z domyślnego adresowania względnego, już w siódmym wierszu otrzymalibyśmy błąd.

Wybór rozkładu prawdopodobieństwa w zależności od innej zmiennej (WYBIERZ)

Po przyporządkowaniu pracowników do działów możemy przejść do kolumny ze stanowiskami. Logika działania jest tutaj identyczna jak w poprzednim przypadku — z boku arkusza wypisujemy dostępne stanowiska, a na lewo od nich warunki wylosowania w formacie zrozumiałym dla funkcji WYSZUKAJ.PIONOWO. Operację powtarzamy dla pozostałych działów, ponieważ naszym celem jest utworzenie innej struktury stanowisk w każdym z nich (np. w zarządzie będą pracowali wyłącznie koordynatorzy).

Do przypisania pracownikom stanowisk wykorzystamy lekko zmodyfikowaną wersję wcześniejszej formuły. Musimy jedynie sprawdzić, w którym dziale pracuje dana osoba, i w zależności od tego wywołać funkcję na innym zakresie. Możemy to osiągnąć przy pomocy szeregu funkcji JEŻELI:

=JEŻELI(G2="Obsługa klienta";WYSZUKAJ.PIONOWO(LOS();$K$13:$L$16;2);JEŻELI(G2="Badania i rozwój";WYSZUKAJ.PIONOWO(LOS();$M$13:$N$16;2);JEŻELI(G2="Marketing";WYSZUKAJ.PIONOWO(LOS();$O$13:$P$16;2);JEŻELI(G2="Księgowość";WYSZUKAJ.PIONOWO(LOS();$Q$13:$R$15;2);JEŻELI(G2="Zarząd";WYSZUKAJ.PIONOWO(LOS();$S$13:$T$13;2))))))

Formuła ta działa (zwraca poprawne wyniki), ale jest długa i skomplikowana, a przez to trudna w modyfikacji. Na szczęście możemy ją uprościć. Wykorzystamy do tego funkcję WYBIERZ.

Ogólna zasada jej działania jest banalnie prosta. Wykonuje ona działanie określone w pierwszym argumencie i — w zależności od wyniku — zwraca n-ty argument. Jeżeli wynik wyniesie 1, to zwróci drugi argument; jeżeli 2 — trzeci argument. I tak dalej.

Jako kolejne argumenty umieścimy zakresy ze stanowiskami. Musimy jedynie zapewnić, że nazwa działu zwróci odpowiednią liczbę. W jaki sposób? Oczywiście wykorzystując WYSZUKAJ.PIONOWO.

Na prawo od nazw działów (tam gdzie wcześniej określaliśmy prawdopodobieństwo wylosowania) wpisujemy liczby od 1 do 5.

Ostatecznie przygotowana przez nas formuła wygląda następująco:

=WYSZUKAJ.PIONOWO(LOS();WYBIERZ(WYSZUKAJ.PIONOWO(G2;$L$2:$M$6;2;0);$K$13:$L$16;$M$13:$N$16;$O$13:$P$16;$Q$13:$R$15;$S$13:$T$13);2)

Najbardziej wewnętrzna funkcja WYSZUKAJ.PIONOWO zwraca liczbę od 1 do 5. Ta z kolei jest przekazywana funkcji WYBIERZ, która odczytuje jeden z podanych zakresów. W jego obrębie będzie działała zewnętrzna funkcja WYSZUKAJ.PIONOWO, która losowo wybiera stanowisko.

Rodzaje umów dla pracowników generujemy analogicznie jak stanowiska.

Generowanie losowej liczby w podanym zakresie (LOS.ZAKR)

Wypełniliśmy już wszystkie kolumny oprócz płacy. W jej przypadku postąpimy odrobinę inaczej niż ze stanowiskami i rodzajami umów.

Tym razem poszukiwana wartość nie będzie zależała wyłącznie od działu, ale również od zajmowanego stanowiska. Ostatecznie możemy się spodziewać, że zarobki koordynatora grupy sprzedażowej będą wyższe niż pracownika przyjętego na okres próbny; główna księgowa pewnie może liczyć na wyższą stawkę niż wspomniany koordynator.

Ogólna formuła pozostanie bez zmian — najpierw za pomocą WYBIERZ znajdziemy zakres z danymi dla danego działu, a następnie wykorzystamy WYSZUKAJ.PIONOWO aby znaleźć stanowisko zajmowane przez pracownika.

Żeby nasza analiza była ciekawsza, dodatkowo zróżnicujemy płace na każdym stanowisku w każdym dziale.

Tak więc w wolnej części arkusza umieszczamy, jedno pod drugim, stanowiska. Na prawo od nich wpisujemy dolną granicę zarobków; w kolejnej kolumnie po prawej stronie wpisujemy górną granicę.

Korzystając z przedstawionej wcześniej formuły, liczby te przekażemy jako argumenty funkcji LOS.ZAKR.

Właściwie na tym nasza praca się kończy. Jednak podana funkcja zwraca dowolną liczbę całkowitą w podanym zakresie, co nie wygląda specjalnie estetycznie w naszym kontekście. Dlatego ja wynik jej działania zdecydowałem się dodatkowo objąć funkcją ZAOKR.DO.WIELOKR.

Funkcja ta przyjmuje dwa argumenty. Zwraca liczbę podaną jako pierwszy z nich zaokrągloną do wielokrotności liczby podanej jako drugi argument.

Gotowa formuła wygląda następująco:

=ZAOKR.DO.WIELOKR(LOS.ZAKR(WYSZUKAJ.PIONOWO(F2;WYBIERZ(WYSZUKAJ.PIONOWO(G2;$L$2:$M$6;2;0);$L$29:$N$32;$O$29:$Q$32;$R$29:$T$32;$U$29:$W$31;$X$29:$Z$29);2;0);WYSZUKAJ.PIONOWO(F2;WYBIERZ(WYSZUKAJ.PIONOWO(G2;$L$2:$M$6;2;0);$L$29:$N$32;$O$29:$Q$32;$R$29:$T$32;$U$29:$W$31;$X$29:$Z$29);3;0));10)

Na zakończenie pracy powinniśmy jeszcze wkleić wszystkie dane specjalnie, o ile nie zrobiliśmy tego wcześniej. Dzięki temu będziemy mogli usunąć dane tymczasowe oraz rozpowszechnić nasz arkusz — wszyscy jego użytkownicy będą pracować na tym samym zestawie danych.

Ponadto możemy sprawdzić, np. przy pomocy autofiltru, czy nasz arkusz nie zawiera błędów logicznych. Dla przykładu, prawdopodobieństwo wylosowania koordynatora w dziale księgowości jest na tyle małe , że nawet przy trzystu pracownikach może się on w ogóle nie pojawić.