Analiza wyników zawodów sportowych w LibreOffice Calc

Na przykładzie tabeli wyników zawodów sportowych zobaczymy działanie funkcji ADR.POŚR, ADRES i PODAJ.POZYCJĘ.

Dane, na których będę pracował, pobrałem ze strony Międzynarodowej Federacji Narciarskiej. Dodałem dwie kolumny, w których znajdują się wygenerowane losowo wyniki. Gotowy skoroszyt można pobrać z działu pliki.

Okno arkusza kalkulacyjnego pokazujące przykładową bazę danych. Odnośnik do niej znajduje się w artykule.

W artykule zakładam, że w konkursie skoków każdy zawodnik miał cztery podejścia. Spośród nich wybrano jedno, w którym osiągnął najlepszy wynik. Zawody wygrywa ten uczestnik, który skoczył najdalej.

Analiza gotowej bazy

Gdy analizujemy już zgromadzone dane, naszym celem jest wyodrębnienie najlepszych zawodników. Najprościej jest to zrobić przez sortowanie według najlepszego uzyskanego wyniku. Jednak aby operacja ta była możliwa, potrzebna będzie dodatkowa kolumna. Umieścimy w niej najlepszy wynik każdego zawodnika.

Tworzymy więc nową kolumnę (zatytułowaną „Najlepsza próba”) i w komórce J2 umieszczam formułę:

=MAKS($E2:$H2)

Następnie „przeciągamy” tę komórkę na pozostałe wiersze.

Teraz zaznaczamy cały zakres danych (najłatwiej jest to zrobić przechodząc do komórki A1 i wciskając Ctrl + Shift + End) i wybieramy z menu DaneSortuj. Pojawi się nowe okno, w którym z listy rozwijanej wybieramy wcześniej utworzoną kolumnę i zaznaczamy Malejąco. Poza tym należy upewnić się, że na karcie Opcje zaznaczono Zakres zawiera nagłówki kolumn.

W tym momencie mamy już wszystkie istotne informacje — wiemy, którzy skoczkowie byli najlepsi i jakie wyniki osiągnęli. Możemy dodatkowo zaznaczyć, w którym podejściu uzyskali najlepsze wyniki. W tym celu wykorzystamy formatowanie warunkowe.

Zaznaczamy wszystkie komórki zawierające uzyskane wyniki (ale bez wcześniej utworzonej kolumny) i wybieramy z menu FormatFormatowanie warunkoweFormatowanie warunkowe.... W nowym oknie klikamy Dodaj. Jako warunek wskazujemy Formuła jest, a w polu obok wprowadzamy formułę:

E1=MAKS($E1:$H1)

Wybieramy także odpowiedni styl i zatwierdzamy zmiany.

Więcej na temat formatowania warunkowego przeczytasz w poświęconym mu artykule.

Analiza zmiennej bazy

Dotychczas zakładaliśmy, że nasza baza jest już skończona. Jednak jeżeli jest ona w trakcie tworzenia (zawody jeszcze się nie skończyły), podany wyżej sposób nie sprawdzi się — konieczność sortowania wyników po każdym skoku będzie zbyt uciążliwa. Zamiast tego dobrze jest umieścić nazwisko aktualnie najlepszego zawodnika w innej części arkusza. Wykorzystamy do tego funkcje ADR.POŚR, ADRES oraz PODAJ.POZYCJĘ.

Zacznijmy od ostatniej z nich. Pozwala ona poznać umiejscowienie poszukiwanej wartości (pierwszy argument) w określonym zakresie (drugi argument). Niestety, nie potrafi pracować na zakresach dwuwymiarowych (zawierających zarówno kolumny, jak i wiersze), a przecież z takim mamy do czynienia.

Można to jednak obejść. Ponownie wykorzystamy kolumnę, w której umieścimy najlepszy wynik każdego z zawodników. Następnie zlokalizujemy w niej największą liczbę. W ten sposób poznamy numer wiersza z danymi sportowca przodującego w danym momencie. Gotowa formuła wygląda następująco:

=PODAJ.POZYCJĘ(MAKS(J1:J68);J1:J68;0)

W przykładzie tym funkcja PODAJ.POZYCJĘ przyjęła trzy argumenty. Ostatni z nich jest opcjonalny i określa, czy przeszukiwany zakres jest posortowany. Jego pominięcie lub liczba 1 oznacza uszeregowanie w kolejności rosnącej — efektem działania funkcji będzie pozycja ostatniej liczby mniejszej lub równej poszukiwanej. Liczba -1 oznacza uszeregowanie w kolejności malejącej — zwrócona zostanie pozycja ostatniej liczby większej lub równej wzorcowi. Z kolei liczba 0 oznacza brak sortowania, a funkcja zwróci pozycję liczby dokładnie takiej samej jak poszukiwana.

Wiemy już, w którym wierszu znajdują się dane najlepszego zawodnika. Pozostaje nam więc odczytać jego nazwisko.

Najprostszy sposób opiera się na założeniu, że nazwiska zawodników znajdują się w kolumnie C. Możemy więc połączyć tę literę z poznanym wcześniej numerem wiersza, tym samym poznając adres interesującej nas komórki:

="C"&PODAJ.POZYCJĘ(MAKS(J1:J68);J1:J68;0)

Niestety, użycie operatora konkatenacji (&) spowodowało, że nasza formuła zwraca ciąg znaków. tymczasem nas interesuje zawartość komórki o podanym adresie. Musimy więc przetłumaczyć tekst na odwołanie. Służy do tego funkcja ADR.POŚR. Gotowa formuła wygląda następująco:

=ADR.POŚR("C"&PODAJ.POZYCJĘ(MAKS(J1:J68);J1:J68;0))

Warto zauważyć, że sposób ten ma jedną potencjalną wadę — przestanie działać, gdy zmienimy strukturę naszej bazy (dodamy, usuniemy lub zamienimy kolejność kolumn). Może się wtedy zdarzyć, że w kolumnie C znajdzie się jakaś inna informacja niż nazwisko skoczka.

Aby temu zapobiec, ponownie wykorzystamy PODAJ.POZYCJĘ. Tym razem jednak będziemy poszukiwali tekstu „Nazwisko” w pierwszym wierszu bazy.

Łącząc to działanie z już istniejącą funkcją, poznamy dwie wartości — numer wiersza oraz numer kolumny, w której znajduje się nazwisko najlepszego skoczka. Jednak numer kolumny na wiele nam się nie zda, gdy w arkuszu kalkulacyjnym kolumny oznaczane są za pomocą liter. Musi więc przetłumaczyć nasze liczby na adres komórki.

Służy do tego funkcja ADRES. Jej pierwszym argumentem musi być numer wiersza, a drugim numer kolumny. Trzeci, opcjonalny argument, określa zwracany typ odwołania — czy ma to być adres bezwzględny (domyślnie), względny czy też tylko jedna z części ma być identyfikowana względnie, a druga absolutnie. Czwartym argumentem może być tekst, który będzie odpowiadał za nazwę arkusza w odwołaniu.

Pewnym ograniczeniem tej funkcji jest fakt, że zwraca ona adres w postaci ciągu znaków. Jednak dla nas nie jest to żadna przeszkoda, gdyż wiemy już, jak przetłumaczyć go na odwołanie zrozumiałe dla arkusza.

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

=ADR.POŚR(ADRES(PODAJ.POZYCJĘ(MAKS(J1:J68);J1:J68;0);PODAJ.POZYCJĘ("Nazwisko";A1:J1;0);4))

Jeżeli chcesz samodzielnie przećwiczyć opisane wyżej działania, zapraszam do pobrania wykorzystanego w artykule skoroszytu. W arkuszu „gotowy” znajdują się komórki zawierające wyniki i nazwiska trzech najlepszych skoczków. Arkusz „roboczy” zawiera bazę w stanie pierwotnym.

Podobał Ci się ten artykuł? Zapisz się na listę subskrybentów i otrzymuj informacje o następnych

Komentarzy: 3

  • wfrerwe3 pisze:

    macmahon by sie przydał bo to to nie wiem do czego

  • Marek pisze:

    Ciekawe, ale co zrobić przy równych wynikach dwóch lub więcej zawodników? Podaje wtedy niewłaściwe nazwiska. Pozdrawiam

    • Rozwiązanie takiego problemu wymagałoby zupełnie innego podejścia; prawdopodobnie nie obędzie się bez większej liczby kolumn tymczasowych. Ewentualnie można sortować tabelę wg dodatkowej kolumny w kolejności malejącej, wtedy najlepsze wyniki będą od razu na widoku. Można nawet napisać makro, które będzie automatycznie sortowało tabelę po każdej zmianie.

      Podejście zaprezentowane w artykule nie jest wystarczająco ogólne, przyznaję to. Z drugiej strony, artykuł ma za zadanie zaprezentować działanie kilku funkcji arkusza kalkulacyjnego na realnym przykładzie — i myślę, że w tej roli spisuje się całkiem dobrze.

Dodaj komentarz