Przepis na

Unikatowe pozycje na liście danych

• Autor: Mirosław Zalewski
• 19 listopada 2013
• Komentarzy: 1

Przygotowanie listy unikatowych elementów występujących w zbiorze danych jest podstawą wielu czynności analitycznych. Artykuł opisuje pięć sposobów na wykonanie tego zadania — filtrowanie, tabelę przestawną, formułę macierzową, formuły wykorzystujące trzy kolumny oraz formuły wykorzystujące dwie kolumny.

Dane, na których będziemy pracować, przedstawia zrzut ekranu poniżej; w kolumnie B znajduje się efekt, do którego uzyskania będziemy dążyć. Oczywiście jest to przykład uproszczony, ale w zupełności wystarczający dla naszych celów.

Filtrowanie

Wpierw musimy zaznaczyć kolumnę z danymi. Następnie wybieramy z menu Dane → Filtr → Filtr standardowy....

Pojawi się nowe okno. Określamy w nim warunek, który zostanie spełniony dla wszystkich interesujących nas danych (np. „Niepusty”). W części Więcej opcji zaznaczamy Bez duplikatów oraz Kopiuj wyniki do..., a w polu w prawym dolnym rogu wpisujemy adres komórki, od której zopocznie się nowa lista. Na końcu klikamy OK.

Niestety, filtrowanie zupełnie nie sprawdza się, gdy nasze dane są generowane przez formułę (lista wynikowa będzie zawierała formuły, a nie efekt ich działania). Nie potrafi również automatycznie uwzględnić zmian dokonanych w liście źródłowej.

Tabela przestawna

Skorzystanie z tej metody będzie możliwe dopiero wtedy, gdy zaopatrzymy nasze dane w etykietę. W tym celu należy wypełnić zawartość komórki bezpośrednio nad pierwszą wartością. Treść etykiety nie ma większego znaczenia.

Następnie umieszczamy kursor gdziekolwiek w obrębie kolumny z danymi i wybieramy z menu Dane → Tabela przestawna → Utwórz.... Pojawi się nowe okno, w którym wybieramy Bieżące zaznaczenie i klikamy OK.

W kolejnym oknie przenosimy przycisk zawierający wpisaną etykietę na Obszar wierszy. Przy domyślnych ustawieniach, lista unikatowych wartości pojawi się w nowym arkuszu; możemy to zmienić klikając Więcej i wybierając odpowiednią pozycję w dodatkowych opcjach. Na końcu klikamy OK.

Tabela przestawna doskonale poradzi sobie z danymi wygenerowanymi na podstawie formuł, jest więc pozbawiona jednego z ograniczeń filtrowania. Jednak uwzględnianie zmian wprowadzonych do listy źródłowej nie odbywa się automatycznie — konieczne jest odświeżenie tabeli.

Formuła macierzowa

Do dowolnej komórki wprowadzamy formułę:

=INDEKS($A$2:$A$11;PODAJ.POZYCJĘ(0;LICZ.JEŻELI($C$2:C2;$A$2:$A$11);0))

W zakresie $A$2:$A$11 znajdują się dane źródłowe; C2 jest adresem komórki, do której wpisana została formuła.

Zatwierdzamy ją za pomocą kombinacji Ctrl + Shift + Enter.

Następnie kopiujemy komórkę z formułą i zaznaczamy tyle komórek pod nią, ile wartości liczy nasza kolumna źródłowa. Wreszcie wklejamy zawartość schowka (Ctrl + V).

Najbardziej podstawowym elementem formuły jest funkcja LICZ.JEŻELI. Sprawdza ona, ile komórek w podanym zakresie (pierwszy argument) spełnia określone kryteria (drugi argument). W normalnych okolicznościach kryteriów tych nie można wyrażać za pomocą zakresu, jest to jednak możliwe w trybie macierzowym. W takim przypadku funkcja wywoływana jest osobno dla każdej komórki w zakresie, a efektem działania jest lista zawierająca otrzymane wyniki.

Przekazywana jest ona jako argument do funkcji PODAJ.POZYCJĘ. Funkcja ta, jak sama nazwa wskazuje, podaje pozycję elementu określonego przy pomocy pierwszego argumentu w jednowymiarowym zakresie zdefiniowanym przy pomocy drugiego argumentu. Trzeci argument wyznacza zachowanie w sytuacji, gdy wyszukiwana jest liczba, ale nie zostanie ona znaleziona; zero oznacza zwrócenie w takim przypadku błędu.

Otrzymana wartość staje się drugim argumentem funkcji INDEKS. Ta z kolei służy do zwracania elementu tablicy dwuwymiarowej (pierwszy argument) określonego za pomocą numeru wiersza (drugi argument) i kolumny (trzeci argument).

Podsumowując: nasza funkcja przygotowuje tablicę z informacjami, ile razy każda z wartości z kolumny A pojawiła się dotychczas w kolumnie C. Następnie wyszukuje w niej zero i zwraca z kolumny A element na odpowiadającej mu pozycji. Działanie to jest powtórzone dla wszystkich komórek w kolumnie A.

Formuła wykorzystująca trzy kolumny robocze

W pierwszej kolumnie (w moim przypadku jest to E) wpisujemy formułę:

=LICZ.JEŻELI($A$2:A2;A2)

Wykorzystujemy znaną nam już funkcję LICZ.JEŻELI. W tej wersji pokazuje ona, ile razy wartość z aktualnego wiersza pojawiła się w kolumnie A od jej początku aż do bieżącej pozycji. Gdy dana wartość występuje po raz pierwszy, wynikiem jest jeden; liczba większa informuje, że element ten był już obecny w którejś z wcześniejszych komórek.

Tak przygotowana kolumna pomoże nam w opracowaniu listy unikatowych wartości. Następnym krokiem jest wyszukanie wszystkich jedynek w nowej kolumnie i odczytanie odpowiadających im elementów z kolumny A. Ponownie wykorzystamy do tego funkcję PODAJ.POZYCJĘ.

Jednak przed skonstruowaniem formuły musimy zastanowić się, na jakim zakresie powinna pracować. Z pewnością nie może to być cała kolumna E, gdyż za każdym razem wskazywałaby na jej pierwszą komórkę. Nie może to być również kolumna E od bieżącego wiersza do końca — wtedy kilka kolejnych wywołań formuły będzie wskazywać na tę samą wartość unikatową. Zamiast tego, nasza funkcja musi rozpoczynać pracę od wiersza z ostatnią znalezioną wartością.

Efekt taki możemy uzyskać dzięki funkcji PRZESUNIĘCIE. Sposób jej działania jest bardzo prosty — zwraca ona komórkę oddaloną od miejsca określonego za pomocą pierwszego argumentu o pewną liczbę wierszy (drugi argument) i kolumn (trzeci argument). Jeżeli podamy również czwarty i piąty argument, zwrócony zostanie zakres komórek.

Uzbrojeni w tę wiedzę, możemy przystąpić do wypełnienia następnej kolumny (u mnie: F). W pierwszym jej wierszu wpisujemy 1 — pierwsza wartość kolumny A zawsze jest unikatowa. W drugim i następnych wpisujemy formułę:

=PODAJ.POZYCJĘ(1;PRZESUNIĘCIE($E$2;SUMA($F$2:F2);0;1000);0)

W kolejnej kolumnie wpisujemy formułę, która odczyta zawartość kolumny A na odpowiednich pozycjach:

=INDEKS($A$2:$A$11;SUMA($F$2:F2))

Zauważmy przy tym, że efektem działania naszej pierwszej formuły jest względna (od początku przesuniętego zakresu) pozycja następnej unikatowej wartości. Jednak funkcja INDEKS oczekuje pozycji bezwzględnej (od początku całego zakresu). Funkcja SUMA wzięła się stąd, że wszystkie pozycje względne razem wzięte dają pozycję bezwzględną.

Formuła wykorzystująca dwie kolumny robocze

Uważnie analizując powyższy przykład, przekonamy się, że wcale nie musieliśmy korzystać z kolumny F. Nic nie stoi na przeszkodzie, aby pozycję ostatniej unikatowej wartości (a tym samym drugi argument funkcji PRZESUNIĘCIE) obliczyć przy pomocy PODAJ.POZYCJĘ.

Podstawowa wersja takiej formuły wygląda następująco; wprowadzamy ją do komórki J3:

=PODAJ.POZYCJĘ(1;PRZESUNIĘCIE($I$2;PODAJ.POZYCJĘ(J2;$A$2:$A$11;0);0;1000);0)

Gdzie kolumna I zawiera funkcję LICZ.JEŻELI (tak samo jak kolumna E w poprzednim przykładzie), zaś komórka J2 formułę =A2.

Formuła wyszukuje pierwszą jedynkę w kolumnie I, rozpoczynając od elementu odpowiadającemu wierszowi zawierającemu ostatnią znalezioną wartość unikatową w kolumnie A. Otrzymany wynik możemy przekazać jako drugi argument funkcji INDEKS.

Jednak efekt nie będzie zgodny z naszymi oczekiwaniami. Winny jest tutaj fakt, na który wskazywaliśmy już uwagę przy opisie techniki wykorzystującej trzy kolumny — funkcja INDEKS pracuje na zakresie bezwzględnym, zaś przygotowana przez nas formuła zwraca pozycję względną. Problem ten możemy z łatwością rozwiązać. Skoro wynikiem działania naszej formuły jest pozycja względna, wartość bezwzględną możemy otrzymać dodając pozycję elementu, od którego rozpoczęliśmy poszukiwania.

Ostateczna wersja formuły wygląda następująco:

=INDEKS($A$2:$A$11;PODAJ.POZYCJĘ(J2;$A$2:$A$11;0)+PODAJ.POZYCJĘ(1;PRZESUNIĘCIE($I$2;PODAJ.POZYCJĘ(J2;$A$2:$A$11;0);0;1000);0))