Unikatowe wartości na rozwijanej liście Excel

W czasach gdy bramy Krakowa zapinano jeszcze na guziki i powstawały pierwsze arkusze, w których twórcy próbowali powstrzymać błędy użyszkodników użytkowników pojawiła się opcja Sprawdzania poprawności (Poprawność danych), którą zapewne bardzo dobrze znacie z Excela. Funkcjonalność ta zdecydowanie zasługuje na uwagę, niemniej jednak ma miała swoje ułomności. Jedną z nich była pewnego rodzaju trudność przekazania argumentów do listy z kontrolą w locie. A tak po ludzku?

Wyobraźmy sobie, że chcemy zbudować listę rozwijaną w komórce L1, która będzie zawierała listę unikatowych kolorów z kolumny M (ColorName) z tabeli MS Excel:

Lista powinna zasilać się zatem dynamicznie z kolumny pod spodem. Dodatkowo – jeśli w tabeli pod spodem zostanie dopisany nowy kolor (np. bergamotkowy) to jednocześnie powinien być dostępny do wyboru na liście w komórce L1. A co tam! – powinien nawet być ułożony alfabetycznie na liście kolorów.

Jak podejść do takiego tematu bez konieczności zaprzęgania woła pociągowego w postaci starego, poczciwego (i poniekąd zapomnianego przez Microsoft VBA ale niezapomnianego przez nas: Szkolenie VBA)?

Panaceum będzie tutaj skorzystanie z funkcji tablicowych nowej ery ze stajni Microsoft.

Microsoft od pewnego czasu zmienił podejście do funkcji tablicowych udostępniając zupełnie prostsze i bardziej przejrzyste, ludzkie ich wydanie (nie zobaczycie już magicznych i tajemniczych nawiasów klamrowych). Jednocześnie Excel wzbogacił się o szereg bardzo praktycznych funkcji, które udostępniają narzędziownik do naprawy/rozwiązania wielu typowych problemów.

Co zatem musimy zrobić w tym przypadku?

1. Uzyskać listę unikatowych wpisów w kolumnie M (która de facto jest kolumną oficjalnej Excelowej tabeli),
2. Posortować listę unikatowych wartości,
3. Przekazać ją jako listę opcji do narzędzia Poprawność danych.

Zajmijmy się zatem numerem 1 z naszej listy.

Najbardziej aktualnym sposobem pozyskania aktualnej listy unikatowych wartości z kolumny, który jednocześnie zapewni, że będzie to działanie dynamiczne (zmiana wartości źródła spowoduje zmianę wartości na liście) jest skorzystanie z funkcji UNIKATOWE.

Funkcja UNIKATOWE w MS Excel ma następującą składnię:

UNIKATOWE(Tablica, [by_col], [exactly_once])

Tablica – Pierwszym argumentem jest tablica/lista, którą przekazujemy Excelowi, i z której Excel będzie wyszukiwać unikatowe wartości.

By_col – argument opcjonalny – jeśli przekazujemy zakres, który ma więcej niż jedną kolumnę/wiersz – możemy wskazać czy interesuje nas uzyskanie unikatowych wierszy czy też kolumn

Exactly_once – tutaj określamy czy interesuje nas tylko wyszukanie takich unikatów, które występują dokładnie raz. Co to oznacza? Wyobraźmy sobie, że na liście kolor „blue” pojawia się 2452 razy. W takiej sytuacji nie zostanie on potraktowany jako wartość unikatowa, ponieważ występuje więcej niż raz. Tym samym nie zostanie w ogóle dopisany do listy unikatów. Jeśli dopiszemy do listy kolor „cyklamen” i będzie to jedyny wpis w całej kolumnie, innymi słowy będzie tylko jeden produkt o tym kolorze to pojawi się on na liście unikatowych kolorów. Oczywiście zniknie on, gdy nie będzie już jedynym wpisem w tej kolumnie (czyli gdy np. 2 produkty będą mieć ten kolor).

Skorzystajmy więc z wolnego miejsca w arkuszu i zbudujmy formułę, która jest w stanie odczytać unikatowe kolory z całej kolumny. W naszym przypadku funkcja będzie mieć postać:

=UNIKATOWE(DimProduct[ColorName])

gdzie DimProduct to oficjalna nazwa oficjalnej tabeli a ColorName to nazwa kolumny w tabeli.

Rezultatem działania (funkcja wprowadzona do komórki AI1) będzie lista kolorów:

Jesteśmy już zdecydowanie bliżej rozwiązania 😊 Lista jest jednak wciąż nieposortowana, dlatego czas na zatrudnienie pomocnika dla funkcji UNIKATOWE. Idealnym kandydatem na to stanowisko będzie funkcja

SORTUJ

Tak, tak – FUNKCJA. Do tej pory być może jesteś przyzwyczajony, że sortowanie odbywa się przez wciśnięcie guzika lub rozwinięcie odpowiedniej opcji w filtrze. Niemniej jednak te oldschoolowe metody mają swoje ograniczenia. Przede wszystkim – takie sortowanie nie jest dynamiczne. Być może nie raz spotkałeś się z sytuacją w Excelu, gdzie po przesortowaniu listy/tabeli zmieniłeś jakieś wartości i oczekiwałeś zmiany kolejności. Na przykład posortowałeś listę od najwyższego do najniższego wynagrodzenia, zmieniłeś najwyższe wynagrodzenie a ono wciąż zostało na górze…

Funkcja SORTUJ ma zdecydowanie więcej argumentów, niemniej jednak naszym celem nie jest tutaj jej pełne omówienie (takie omówienie robimy na szkoleniach, np. tym: Excel – poziom zaawansowany) a jedynie szybkie wykorzystanie jej działania. Dlatego obejmijmy naszą już zbudowaną formułę

=UNIKATOWE(DimProduct[ColorName]) funkcją SORTUJ do postaci:

=SORTUJ(UNIKATOWE(DimProduct[ColorName]))

Po jej wykorzystaniu naszym oczom ukaże się w Excelu następujący widok posortowanej listy unikatowych wpisów:

Czas przejść do ostatniej części naszego zadania a mianowicie wbudować naszą listę do okna sprawdzania poprawności.

W oknie Sprawdzania poprawności danych w opcji Dozwolone – Lista wprowadzamy współrzędną początku naszej listy oraz dostawiamy na koniec znak #. Wykorzystanie tego znaku gwarantuje rozwinięcie w locie formuły do pełnej listy.

Możemy już korzystać z rozwijanej listy z unikatowymi i posortowanymi wpisami w komórce:

Najważniejszy jednak tutaj jest fakt utrzymania dynamiczności. Zobaczmy co się stanie jeśli w kolumnie z kolorami pojawi się nagle „bergamotkowy”:

Widzimy, że wszystko działa wyśmienicie.

Takie i inne sztuczki, kruczki możesz poznać przychodząc na nasze szkolenia 🙂