Dynamiczna lista unikatowych wartości np. liczby klientów

Z lekcji dowiesz się:

• Jak szukać unikatowych (pojawiających się po raz pierwszy) np. klientów?

• Jak sprawdzać poprawność otrzymanych danych?

• Co robić, gdy formuła zaczyna zwracać błędy?

Problemem, którym się zajmiemy jest tworzenie unikatowych list, które później wykorzystane zostaną do sprawdzania poprawności danych do tworzenia list rozwijanych. Zadanie to wykonamy na przykładowych danych przedstawionych na rys. 1.

(Rys. 1)

Stworzymy formułę, która działa najszybciej w porównaniu do innych formuł dostępnych w Excelu (poza nowymi funkcjami tablicowymi, które omówimy na końcu artykułu).

W celu ułatwienia obliczeń w pierwszej kolejności wyliczymy ilość unikatowych elementów. Dane mają postać tabeli (do 9-tego wiersza), wiersz poniżej będzie potrzebny do sprawdzenia jak dynamiczne są obliczenia (rys. 1).

W pierwszej kolejności musimy policzyć unikatową ilość klientów. Należy użyć do tego testu logicznego – funkcji JEŻELI. Test będzie polegał na sprawdzeniu czy wszystkie komórki w kolumnie Klient (B), nie są puste (są różne od pustych komórek, czyli pustego ciągu tekstowego, który w Excelu oznaczamy jako dwa podwójne cudzysłowy - ""). Puste komórki (jak komórka B9) mogą stanowić problem przy dalszych obliczeniach. Dopiero przy takim założeniu chcemy wykonać niezbędne wyliczenie.

Drugi argument funkcji – wartość_jeżeli_prawda, gdzie sprawdzimy kiedy po raz pierwszy pojawił się dany klient. Nie interesują nas powtórzone osoby, tylko pierwsze pojawienie się danego klienta. Na przykład imię Magda pojawia się w trzecim i piątym wierszu, natomiast w tym przypadku liczy się tylko pierwsze wystąpienie, czyli trzeci wiersz. Użyjemy tutaj funkcji PODAJ.POZYCJĘ. Pierwszym argumentem funkcji jest szukana_wartość, która nie będzie pojedynczą wartością ale wszystkimi wartościami z kolumny Klient (rys. 2).

(Rys. 2)

Drugi argument funkcji to przeszukiwana_tab, czyli zakres, w którym chcemy sprawdzić wartości. W tym przykładzie będzie to ten sam zakres co w pierwszym argumencie, czyli kolumna Klient.

Trzeci argument to typ_porównania, którym będzie dopasowanie dokładne (wartość 0).

Zapis drugiego argumentu funkcji JEŻELI, czyli funkcja PODAJ.POZYCJĘ będzie wyglądał następująco:

PODAJ.POZYCJĘ(tSprzedaż[Klient]; tSprzedaż[Klient];0)

Za pomocą klawisza F9 można podejrzeć wyniki tej funkcji, które zostały pokazane na rys. 3.

(Rys. 3)

Na rys. 4 zostało pokazane wystąpienie klienta Magda, która pojawia się na drugiej pozycji a następnie mimo, że znowu pojawia się czwarta w kolejności, w wyniku otrzymamy ten sam numer co pierwsze pojawienie się (2).

(Rys. 4)

Ponadto w wierszu dziewiątym pojawia się błąd, ale zostanie on usunięty, kiedy zadziała test logiczny z funkcji JEŻELI. Podgląd wyników funkcji cofamy za pomoca skrótu klawiszowego Ctrl+Z.

Zapis całej formuły powinien wyglądać następująco:

=JEŻELI(tSprzedaż[Klient]<>"";PODAJ.POZYCJĘ(tSprzedaż[Klient]; tSprzedaż[Klient];0))

Kiedy ponownie podejrzymy wyniki formuły za pomocą klawisza F9, otrzymamy dane przedstawione na rys. 5.

(Rys. 5)

W wynikach pojawiają się liczby przedstawiające kolejne wystąpienia poszczególnych klientów oraz wartość logiczną FAŁSZ, z którą możemy sobie łatwo poradzić. Wychodzimy z trybu podglądu wyników formuły za pomocą skrótu klawiszowego Ctrl+Z.

Naszym celem jest sprawdzenie tylko pierwszych wystąpień danego klienta (unikatowych klientów). W tym celu użyjemy funkcji CZĘSTOŚĆ, która oblicza rozkład, w jakich przedziałach znajdują się poszczególne dane, oznacza to, że tablica z wynikami ma zostać rozdzielona równomiernie na przedziały.

Przede wszystkim chodzi o to, żeby otrzymać listę liczb po kolei, według ciągu arytmetycznego. W tym celu użyjemy funkcji WIERSZ, w której odwołamy się do całej kolumny Klient. Zapis powinien wyglądać następująco: WIERSZ(tSprzedaż[Klient]).

Kiedy podejrzymy wyniki tej funkcji za pomocą klawisza F9, zobaczymy, że funkcja zwraca przedziały (rys. 6). Z podglądu wychodzimy skrótem klawiszowym Ctrl+Z.

(Rys. 6)

Pojawia się problem, ponieważ przedziały zaczynają się od wartości 2, natomiast potrzebny jest początek od wartości 1. Należy skorygować ten ciąg. Funkcja WIERSZ patrzy na numery wierszy w arkuszu, więc od funkcji WIERSZ (WIERSZ(tSprzedaż[Klient])), musimy odjąć pozycję wiersza nagłówkowego.

Kiedy w odwołaniu klikniemy na komórkę z nagłówkiem tabeli to otrzymamy skomplikowany zapis (rys. 7).

(Rys. 7)

Najlepszym sposobem uproszeniu tego zapisu jest wpisanie ręcznie odwołania do komórki B1, którą blokujemy bezwzględnie za pomocą klawisza F4.

Zapis powinien wyglądać następująco:

WIERSZ(tSprzedaż[Klient])-WIERSZ($B$1)

Zapis całej formuły do wyznaczenia wartości unikatowej powinien wyglądać następująco:

=CZĘSTOŚĆ(JEŻELI(tSprzedaż[Klient]<>"";PODAJ.POZYCJĘ(tSprzedaż[Klient]; tSprzedaż[Klient];0)); WIERSZ(tSprzedaż[Klient])-WIERSZ($B$1))

Ustawiając kursorem na końcu formuły można podejrzeć jej wyniki za pomocą klawisza F9. Otrzymamy wtedy wyniki przedstawione na rys. 8.

(Rys. 8)

Wartość na pierwszej pozycji (Teresa) wystąpiła tylko jeden raz, wartość na drugiej pozycji (Magda) wystąpiła dwa razy, Edward wystąpił jeden raz, a Robert dwa razy. Wartości zero pojawiają się w miejscach, gdzie unikatowa wartość się powtarza (rys. 9).

(Rys. 9)

W kolejnym etapie należy zliczyć wystąpienie wszystkich liczb większych od zera, co wykonamy za pomocą funkcji JEŻELI. Pierwszym argumentem funkcji, czyli testem_logicznym będzie cała funkcja CZĘSTOŚĆ, ponieważ w Excelu jest tak, że każda wartość różna od zera jest interpretowana jako PRAWDA. Drugim argumentem funkcji (wartość_jeżeli_prawda) będzie wartość 1. Trzeci argument funkcji (wartość_jeżeli_fałsz) należy pominąć. Zapis funkcji powinien wyglądać następująco:

=JEŻELI(CZĘSTOŚĆ(JEŻELI(tSprzedaż[Klient]<>"";PODAJ.POZYCJĘ(tSprzedaż[Klient]; tSprzedaż[Klient];0)); WIERSZ(tSprzedaż[Klient])-WIERSZ($B$1));1)

Klikając w podgląd funkcji JEŻELI za pomocą klawisza F9 otrzymamy wyniki przedstawione na rys. 10.

(Rys. 10)

Otrzymaliśmy wartości 1 w miejscach, gdzie pierwszy raz wystąpiła unikatowa wartość oraz wartości logiczne FAŁSZ w miejscach, gdzie te wartości się powtarzały. Wychodzimy z podglądu wyników za pomocą skrótu klawiszowego Ctrl+Z.

Ostatnim etapem obliczenia unikatowej ilości jest zsumowanie tych wartości za pomocą funkcji SUMA. Zapis powinien wyglądać następująco:

=SUMA(JEŻELI(CZĘSTOŚĆ(JEŻELI(tSprzedaż[Klient]<>"";PODAJ.POZYCJĘ(tSprzedaż[Klient]; tSprzedaż[Klient];0)); WIERSZ(tSprzedaż[Klient])-WIERSZ($B$1));1))

Zatwierdzamy formułę skrótem klawiszowym Ctrl+Shift+Enter i otrzymujemy ilość unikatowych klientów (rys. 11).

(Rys. 11)

Aby pokazać, że wartości te są obliczane dynamicznie, należy przeciągnąć zakres tabeli na dodatkowy wiersz z klientem Ludwik. Wynik ulegnie wtedy zmianie (rys. 12).

(Rys. 12)

W kolejnym etapie tworzymy unikatową listę klientów. Aby ułatwić sobie to zadanie można skopiować część wcześniejszej formuły (całej funkcji JEŻELI), jak pokazano na rys. 13.

(Rys. 13)

Będzie to jeden z elementów jaki użyjemy do stworzenia listy unikatowych klientów. W zapisie tym musimy zmienić drugi argument funkcji (wartość_jeżeli_prawda) na pozycję tej wartości. Jak już wcześniej widzieliśmy pozycję danej wartości możemy wyznaczyć za pomocą funkcji WIERSZ. Wystarczy, że skopiujemy drugi argument funkcji CZĘSTOŚĆ (tablica_przedziały zaznaczony na rys. nr 14) i wkleimy go jako argument wartość_jeżeli_prawda dla funkcji JEŻELI.

(Rys. 14)

Zapis formuły powinien wyglądać następująco:

= JEŻELI(CZĘSTOŚĆ(JEŻELI(tSprzedaż[Klient]<>"";PODAJ.POZYCJĘ(tSprzedaż[Klient]; tSprzedaż[Klient];0)); WIERSZ(tSprzedaż[Klient])-WIERSZ($B$1));1)); WIERSZ(tSprzedaż[Klient])-WIERSZ($B$1));1)

Kiedy podejrzymy sobie wyniki funkcji JEŻELI za pomocą klawisza F9 otrzymamy dane przedstawione na rys. 15.

(Rys. 15)

Otrzymaliśmy numery pozycji, na których wystąpiły unikatowe wartości. Z obliczeń tych chcemy wyciągnąć tylko unikatowe wartości, a pominąć te które się powtarzają, czyli wartości logiczne FAŁSZ. Można to zrobić za pomocą funkcji MIN.K. Pierwszym argumentem funkcji (tablica), będzie wynik z funkcji JEŻELI. Drugi argument funkcji (k) musi się zmieniać wraz z przeciąganiem formuły na wiersze poniżej. Użyjemy do tego funkcji ILE.WIERSZY, w której musimy patrzeć na naszą formułę od jej pierwszej komórki (E5), czyli liczyć ile wierszy już wykorzystaliśmy. Zapis formuły powinien wyglądać następująco:

=MIN.K(JEŻELI(CZĘSTOŚĆ(JEŻELI(tSprzedaż[Klient]<>"";PODAJ.POZYCJĘ(tSprzedaż[Klient]; tSprzedaż[Klient];0)); WIERSZ(tSprzedaż[Klient])-WIERSZ($B$1));WIERSZ(tSprzedaż[Klient])-WIERSZ($B$1));ILE.WIERSZY($E$5:E5)

W tym zakresie pierwszą wartość blokujemy bezwzględnie za pomocą klawisza F4, a drugą pozostawiamy bez zmian, dzięki temu zakres będzie się rozrastał przy przeciąganiu formuły na wiersze poniżej.

Przy takiej postaci formuły jako wyniki otrzymalibyśmy wartości liczbowe. Natomiast chcemy otrzymać nazwy klientów, więc należy dodatkowo użyć funkcji INDEKS.

Pierwszym argumentem funkcji (tablica) jest zakres z kolumny Klient, czyli tSprzedaż[Klient].

Drugi argument funkcji to nr_wiersza, którym jest cała nasza stworzona powyżej formuła funkcji MIN.K.

Pozostałe argumenty pomijamy i zatwierdzamy formułę skrótem klawiszowym Ctrl_Shift+Enter. Formuła powinna wyglądać następująco:

=INDEKS(tSprzedaż[Klient];MIN.K(JEŻELI(CZĘSTOŚĆ(JEŻELI(tSprzedaż[Klient]<>"";PODAJ.POZYCJĘ(tSprzedaż[Klient]; tSprzedaż[Klient];0)); WIERSZ(tSprzedaż[Klient])-WIERSZ($B$1));WIERSZ(tSprzedaż[Klient])-WIERSZ($B$1));ILE.WIERSZY($E$5:E5))

Formułę tą przeciągamy na komórki poniżej. Otrzymamy wyniki przedstawione na rys. 16.

(Rys. 16)

Pojawia się tutaj problem, że w pewnym momencie funkcja zaczyna zwracać błędy. Wynika to z faktu, że za bardzo przeciągnęliśmy naszą formułę. Funkcja ILE.WIERSZY zwraca nam ilość unikatowych wartości, którą możemy podejrzeć za pomocą klawisza F9 (rys. 17).

(Rys. 17)

Należy skorygować formułę, aby po przekroczeniu ilości unikatowych wartości nie pokazywała błędu (#LICZBA), a pustą komórkę. Najczęściej używa się w tym celu funkcji JEŻELI.BŁĄD, ale w tym przypadku cały pierwszy argument tej funkcji (skomplikowana formuła) musiałaby się przeliczyć, aby Excel zrozumiał, że wyszedł nam błąd.

Jest inny test logiczny, który pozwala uniknąć tego nadprogramowego liczenia, ponieważ mamy wcześniej obliczoną ilość unikatowych wartości. Wystarczy użyć po raz kolejny funkcji JEŻELI. Sprawdzimy test logiczny jeżeli komórka, w której aktualnie się znajdujemy (otrzymana z funkcji ILE.WIERSZY($E$5:E5)) jest większa od wartości w komórce E2 zablokowanej bezwzględnie za pomocą klawisza F4, to chcemy otrzymać pustą komórkę ("") – argument wartość_jeżeli_prawda. Natomiast jeżeli test nie jest spełniony, chcemy aby Excel przeliczył tą skomplikowaną formułę funkcji INDEKS (argument wartość_jeżeli_fałsz). Zapis formuły powinien wyglądać następująco:

=JEŻELI(ILE.WIERSZY($E$5:E5)>$E$2;"";INDEKS(tSprzedaż[Klient];MIN.K(JEŻELI(CZĘSTOŚĆ(JEŻELI(tSprzedaż[Klient]<>"";PODAJ.POZYCJĘ(tSprzedaż[Klient]; tSprzedaż[Klient];0)); WIERSZ(tSprzedaż[Klient])-WIERSZ($B$1));WIERSZ(tSprzedaż[Klient])-WIERSZ($B$1));ILE.WIERSZY($E$5:E5))

Powyższą formułę zatwierdzamy skrótem klawiszowym Ctrl+Shift+Enter i kopiujemy na wiersze poniżej. Otrzymamy wyniki przedstawione na rys. 18.

(Rys. 18)

W kolejnym etapie stworzymy formułę, która będzie zwracać z unikatowej listy tylko tyle elementów, ile faktycznie jest na tej liście. Wykorzystamy funkcję INDEKS.

Nie będziemy używać funkcji PRZESUNIĘCIE, ponieważ ta funkcja przelicza się za każdym razem, kiedy coś zmieni się w danym pliku Excela.

Funkcja INDEKS ma ciekawą właściwość, mianowicie zwraca odwołanie do komórki, a nie wartość z tej komórki.

W formule użyjemy odwołania od komórki E5 (blokujemy bezwzględnie za pomocą klawisza F4), w której zaczynają się interesujące nas dane, do komórki wyznaczonej przez funkcję INDEKS. Pierwszym argumentem funkcji INDEKS jest tablica, czyli zakres danych z tabeli Unikatowa lista (razem z pustymi komórkami). Drugi argument to nr_wiersza, gdzie odwołujemy się do obliczonej wcześniej ilości unikatowych klientów (komórka E2, zablokowana bezwzględnie za pomocą klawisza F4).

Zapis formuły będzie wyglądał następująco:

=$E$5:INDEKS($E$5:$E$12;$E$2)

Kiedy podejrzymy sobie wyniki formuły za pomocą klawisza F9, otrzymamy wyniki przedstawione na rys. 19.

(Rys. 19)

Z podglądu wyników wychodzimy za pomocą skrótu klawiszowego Ctrl+Z.

Na tym etapie pracy kopiujemy formułę, którą właśnie stworzyliśmy i wybieramy polecenie Szacuj formułę (punkt 2 na rys. 20) z karty Formuły (punkt 1).

(Rys. 20)

Przy podglądaniu wyników formuły za pomocą klawisza F9 otrzymaliśmy całą tablicę danych natomiast przy użyciu tego polecenia oszacujemy zakres wyników. Otwiera się okno Szacowania formuły, gdzie za pomocą przycisku Szacuj (zaznaczony zieloną strzałką na rys. 21), oszacujemy wynik formuły.

(Rys. 21)

Skopiowaną wcześniej formułę możemy dołożyć do Menedżera nazw, za pomocą polecenia Menedżer nazw z karty Formuły (rys. 22).

(Rys. 22)

Otwiera się okno Menedżera nazw, gdzie za pomocą przycisku Nowy należy utworzyć nową nazwę (rys. 23).

(Rys. 23)

Po otwarciu się okna, w polu Nazwa należy wpisać lista, a następnie w polu Odwołuje się do trzeba wkleić skopiowaną formułę za pomocą skrótu klawiszowego Ctrl+V. Parametry nowej nazwy zatwierdzamy przyciskiem OK (rys. 24).

(Rys. 24)

Nazwa lista pojawi się na liście w oknie Menedżera nazw, który zamykamy przyciskiem Zamknij.

Następnie usuwamy wartość z komórki G2, czyli z Listy rozwijanej. Wybieramy polecenie Poprawność danych (punkt 2 na rys. 25) z karty Dane (punkt 1).

(Rys. 25)

W ten sposób otwiera się okno Sprawdzania poprawności danych, gdzie w zakładce Ustawienia (punkt 1 na rys. 26), w Kryteriach poprawności (punkt 2), w polu Dozwolone należy wybrać Listę (punkt 3). Następnie w polu Źródło (punkt 4) klikamy klawisz F3, aby Excel otworzył okno Wklejania nazwy (podpowiedział nam, jakie mamy dostępne nazwy), z których wybieramy wcześniej przygotowaną nazwę z Menedżera nazw. Tak przygotowane parametry sprawdzania poprawności danych zatwierdzamy przyciskiem OK.

(Rys. 26)

W wyniku tych obliczeń otrzymujemy listę rozwijaną poszczególnych elementów przedstawioną na rys. 27.

(Rys. 27)

Jeśli teraz przeciągniemy zakres tabeli bazowej na dodatkowy wiersz z nową nazwą klienta (inną niż te, które już mamy w danych) lub dopiszemy nowe dane, to lista automatycznie się powiększy (rys. 28).

(Rys. 28)

Powyżej stworzyliśmy unikatową listę za pomocą dostępnych do tej pory w Excelu formuł. Niedługo Microsoft zamierza wprowadzić nowe funkcje tablicowe oraz całkiem nowy sposób działań na tych formułach.

Wśród nowych funkcji tablicowych jest funkcja o nazwie UNIKATOWE (rys. 29).

(Rys. 29)

Działanie tej funkcji polega na wyznaczeniu unikatowych wartości, bez potrzeby pisania tak skomplikowanych formuł, jakie tworzyliśmy do tej pory. Pierwszym argumentem funkcji UNIKATOWE jest tablica, czyli dowolna lista z danymi (tSprzedaż[Klient]). Zapis formuły będzie wyglądał następująco:

=UNIKATOWE(tSprzedaż[Klient])

Jeśli pominiemy pozostałe argumenty, funkcja UNIKATOWE zwróci nam listę unikatowych wartości z podanego zakresu (rys. 30).

(Rys. 30)

Funkcja ta działa dynamicznie, czyli jeśli dopiszemy dane do naszego zakresu, funkcja automatycznie się przeliczy i rozszerzy - otrzymamy aktualne wyniki.

Główna formuła znajduje się w pierwszej komórce, natomiast w komórkach poniżej jest poszarzona formuła, co oznacza że jest efektem rozlania głównej formuły.

Możemy dołożyć na początek formuły funkcję SORTUJ. Zapis będzie wyglądał następująco:

=SORTUJ(UNIKATOWE(tSprzedaż[Klient]))

Funkcja ta sprawi, że otrzymamy listę unikatowych wartości posortowaną alfabetycznie (rys. 31).

(Rys. 31)

Do stworzenia Listy rozwijanej wystarczy zastosować odpowiednie odwołanie. Kiedy zaznaczymy zakres całej Unikatowej listy Excel podpowie nam nowy rodzaj oznaczenia odwołania (ze znakiem hash #). Jest to oznaczenie odwołania do formuł, które zwracają listy danych (rys. 32).

(Rys. 32)

W oknie Sprawdzania poprawności danych, ustawiamy analogiczne parametry jak na rys. 26, natomiast w polu Źródło odwołamy się do komórki E2 zawierającej początek Unikatowej listy, a następnie wpiszemy znak hash (#) -jak na rys. 33.

(Rys. 33)

Tak przygotowane parametry zatwierdzamy przyciskiem OK, po czym otrzymujemy Listę rozwijaną zawierającą wszystkie elementy z Unikatowej listy (rys. 34).

(Rys. 34)

Jeśli wprowadzone zostaną zmiany w danych bazowych, Lista rozwijana również automatycznie ulegnie zmianie.

Warto wiedzieć, że w tej sytuacji bardzo łatwo można zliczyć wszystkie elementy Unikatowej listy. Skorzystamy tutaj z funkcji ILE.WIERSZY, która zwróci nam liczbę wierszy na liście otrzymanej z funkcji UNIKATOWE. Zapis formuły będzie wyglądał następująco:

=ILE.WIERSZY(UNIKATOWE(tSprzedaż[Klient])

Otrzymamy wynik przedstawiony na rys. 35.

(Rys. 35)

Formuła ta również jest dynamiczna, czyli przelicza się po wprowadzeniu zmian w danych bazowych.

Miejmy nadzieję, że Microsoft już niedługo wprowadzi te nowe formuły tablicowe dla wszystkich użytkowników. Obecnie są one dostępne tylko w niejawnym programie testów subskrypcji 365.

Polecamy także:

• Jak stworzyć wykres wodospadowy?

• Power Query: Jak łączyć informacje z wielu plików i folderów?