Łączenie kilku list (np. podliczanie generalki na zawodach, sprzedaży z kilku regionów, etc.)

Organizatorzy cyklów zawodów wiedzą dobrze, że kiedy przychodzi do podsumowania całego cyklu, to trzeba posklejać listy i podsumować. I że jest to robota ciężka. Część zawodników startowała raz, część kilka razy. Listy mają elementy wspólne i nie wspólne. No i jeszcze trzeba je podsumować szybko na zakończeniu. A czasem zaktualizować wstecz, bo np. był protest związany z pierwszą edycją.

Te same problemy zdarzają się gdy trzeba podsumować np. sprzedaż produktów z kilku regionów.

Przy pracy z tym wpisem mocno pomoże Ci plik roboczy, na którym opiera się ten tekst. Możesz go znaleźć tutaj. Jeśli chcesz go edytować zrób sobie kopię (Plik —> Utwórz kopię)

Pamiętajcie, że każda z tabel może być jeszcze zmodyfikowana i nie możemy jej kopiować “na twardo”. To się naprawdę zdarza i najbardziej irytuje, gdy łączymy tabele różnymi metodami typu “Wytnij, Wklej, Sortuj” z palca.

Wiadomo, że tabele zwykle są dłuższe i bardziej skomplikowane. Często występują w odrębnych arkuszach a nawet w odrębnych plikach. Te tabele są pokazane dla uproszczenia.

Wyświetlamy wszystkich zawodników w jednej zbiorczej tabeli.

Używamy do tego funkcji UNIQUE.

Funkcja UNIQUE, która usuwa duplikaty i pokazuje wyłacznie komórki lub wiersze pojawiające się raz w zakresie, ma prostą składnię:

=UNIQUE(zakres)

Na szczęście kiedy definiujemy zakres, to możemy użyć zapisu klamrowego {} i wrzucić kilka zakresów jeden pod drugim. A więc bierzemy dane zawodników z każdej edycji, łączymy w jeden zakres przy pomocy klamry {} i dopiero wtedy usuwamy duplikaty:

=unique({A5:A17;D5:D17;A21:A33;D21:D33})

Jak widzicie – mamy tu wszystkich zawodników ze wszystkich edycji, ale każdy pojawia się tylko raz. Ponieważ użyliśmy zakresów zawierających także puste wiersze – w zestawienie wkradł się też pojedynczy pusty wiersz.

Warto brać zakresy do zestawienia “z zapasem”, jeśli dane zostaną zaktualizowane i zajmą więcej miejsca – nasz system sobie poradzi.

Dobrą praktyką jest nazywanie zakresów – robimy to zaznaczając zakres i klikając w menu Dane –> Zakresy nazwane. Wówczas nie mówimy już o zakresie A5:A17, a np. listazawodnikow1

Wyszukujemy wyniki z poszczególnych edycji przy każdym zawodniku.

(Pamiętamy ciągle, że może się to odnosić też do produktów i ich sprzedaży np. w różnych kwartałach lub regionach).

Skoro mamy już listę wszystkich zawodników, czas zebrać wyniki z poszczególnych edycji. Przygotowujemy kolumnę na każdą edycję i przy pomocy funkcji VLOOKUP pobieramy wyniki.

Składnia VLOOKUP została omówiona w osobnym wpisie.

If you don’t know VLOOKUP,

You know nothing, Jon Snow!

Ygritte

=ifna(
vlookup(G4;$A$5:$B$17;2;false)
)

“Po ludzku” Vlookup znajdujący się w komórce H4 bierze komórkę G4 i traktuje ją jako identyfikator. Następnie w pierwszej kolumnie tabeli A5:B17 szuka tego identyfikatora, ale nie wyświetla jego, tylko zawartość 2 kolumny tej tabeli. Oznaczenie FALSE mówi o tym, że poszukujemy dokładnej wartości, a nie wartości przybliżonej.

IFNA to funkcja która zapobiega wyświetlaniu się błędu N/A (nie odnaleziono), gdy formuła VLOOKUP nie znajdzie wyniku dla danego zawodnika z listy.

Formuła Vlookup jest skopiowana w dół na całą wysokość tabeli. Wyniki dla pozostałych edycji zbieramy w analogiczny sposób, tylko zmieniamy tabelę. Np. dla drugiej edycji VLOOKUP wygląda tak:

=ifna( vlookup(G4;$D$5:$E$17;2;false) )

Jeśli kolumny są bardzo długie – można sobie pomóc używając funkcji ARRAYFORMULA opisanej w osobnym wpisie.

Sumujemy punkty.

Na koniec dodajemy kolumnę sumującą punkty. To akurat zadanie proste i przyjemne.

W kolumnie L wpisujemy sumę kolumn od H do K, czyli SUM(H4:K4).

Następnie kopiujemy formułę w dół.

Sortujemy według ilości zdobytych punktów.

Mamy już wyniki, ale trzeba je jeszcze posortować by wyłonić zwycięzców.

Teoretycznie można to zrobić korzystając z Menu —> Dane —> Sortuj zakres. Ale nie polecam tej metody. Za każdym razem gdy odświeżać wyniki, tą operację trzeba będzie powtarzać. A przecież zależy nam żeby kompletne wyniki były dostępne natychmiast po zakończeniu 4 edycji zawodów!

Dlatego użyjemy funkcji SORT.

Składnia:

=SORT(zakres; kolumna_sortowania; rosnąco; kolumna_sortowania2, rosnąco2)

• zakres – to tabela którą chcemy posortować (w tym wypadku G4:L43) – pamiętajmy, żeby zostawiać miejsce na nowe wiersze – kto wie ilu zawodników wskoczy w ostatniej edycji?
• kolumna sortowania – przy pomocy liczby określamy, wedle której kolumny zakresu mamy sortować (w przypadku zakresu G4:L43 wybór 1 oznacza sortowanie wg kolumny G). My wybierzemy sortowanie po sumie punktów czyli po kolumnie L (szóstej).
• rosnąco – wybieramy rodzaj sortowania – true oznacza rosnące, false – malejące. Nas interesuje malejące, bo wiersz z największą liczbą punktów chcemy mieć na szczycie.
• kolumna sortowania2, rosnąco2, etc. – w miarę potrzeby możemy wybrać drugorzędne i trzeciorzędne sortowanie.

Ustalamy końcową klasyfikację i radzimy sobie z wynikami ex equo.

Skoro mamy już posortowane wyniki, wystarczy dodać kolumnę rankingu – startując od 1, a kończąc na 30 miejscu. Ale nie jest tak prosto. Robb Stark ii Jeor Mormont – obaj mają po 880 punktów i powinni obaj mieć tą samą lokatę.

W tym krótkim zestawieniu łatwo to ręcznie wyłapać, ale na liście 500 zawodników już byłoby dużo trudniej.

Do określenia pozycji w rankingu używamy funkcji RANK (POZYCJA).

Składnia: =rank(S4;$S$4:$S$43;0)

POZYCJA(wartość; dane; [rosnąco])

• wartość – tu wpisujemy którą liczbę mamy sprawdzić ( S4 – bo w kolumnie S mamy liczbę punktów)
• dane – z jakim zestawem tą wartość porównujemy – w tym wypadku ze wszystkimi wartościami z kolumny S, czyli S4:S43 (dolarków używamy, bo formuła będzie kopiowana w dół za chwilę i nie chcemy by się nam zakres przesuwał)
• 0 oznacza, że zależy nam na rankingu od 1 wzwyż

GOTOWE!

Taką konstrukcję najlepiej przygotować już przed pierwszą edycją zawodów. Z doświadczenia (przy organizowaniu cyklu Monte Kazura) wiem, że im wcześniej się to zrobi tym lepiej.

Jak poradzić sobie gdy wyniki są w kilku plikach?

Załączone rozwiązanie łatwo jest przerobić tak by pracowało z kilkoma plikami z różnych edycji. Tak naprawdę wystarczy ściągnąć wyniki przy pomocy funkcji IMPORTRANGE.

Opisałem ją szczegółowo w osobnym wpisie.

Najlepiej jest dla każdej edycji przygotować osobny arkusz i tam zaciągnąć plik wyników.

Proponowana składnia:

=IMPORTRANGE(“adres pliku z wynikami”;”nazwa arkusza i zakres w którym są wyniki”)

W przypadku naszych biegowych zawodów – Monte Kazury, klasyfikacja jest nieco bardziej skomplikowana, bo o końcowym wyniku decydują nie tylko miejsca z poszczególnych edycji. Edycji jest pięć, a bierzemy pod uwagę tylko 3 najlepsze wyniki (miejsca). Jeśli jest sytuacja ex equo, to o końcowej pozycji decyduje suma czasów z tych trzech startów. Do tego dochodzi jeszcze podział na klasyfikację kobiet i mężczyzn. Jeśli ten tekst wzbudzi zainteresowanie, opiszę także te przypadki. Teraz zdradzę, że używam funkcji FILTER i LARGE (MAX.K).

Przy okazji gorąco pozdrawiam Jacka Morawskiego (to gość na otwierającym zdjęciu), który ostatnio mało biega, a swego czasu stanowił mocny punkt naszej firmy. Teraz prowadzi firmę trackcourse zajmującą się śledzeniem zawodników na zawodach przez GPS.