Usuwanie duplikatów za pomocą UNIQUE

Funkcja UNIQUE zwraca listę bez duplikatów, pozostawiając tylko różne wartości z zakresu. Rozlewa wynik, dzięki czemu jedna formuła tworzy przejrzystą, zawsze aktualną listę.

Jest to znacznie wygodniejsze niż użycie starszego polecenia menu Remove Duplicates, które tworzyło statyczną kopię wymagającą ponownego wykonania po każdej zmianie danych.

Funkcja UNIQUE przyjmuje jeden wymagany argument i dwa opcjonalne:

=UNIQUE(array, [by_col], [exactly_once])

• array to zakres, z którego mają zostać usunięte duplikaty.
• Ustawienie by_col na TRUE powoduje porównywanie kolumn zamiast wierszy (rzadko potrzebne).
• Ustawienie exactly_once na TRUE zwraca tylko wartości występujące dokładnie jeden raz.

W większości przypadków wystarczy podać tablicę.

Załóżmy, że zakres A2:A12 zawiera powtarzające się nazwy regionów: East, West, East, North, West, East. Aby uzyskać każdy region tylko raz:

=UNIQUE(A2:A12)

Formuła rozlewa trzy wartości: East, West i North, w kolejności ich pierwszego wystąpienia. Po dodaniu nowego regionu do źródła lista automatycznie się powiększy.

Funkcja UNIQUE może usuwać duplikaty całych wierszy. Jeśli zakres A2:B12 zawiera region i produkt, wiersz jest duplikatem tylko wtedy, gdy obie kolumny są takie same.

=UNIQUE(A2:B12)

Ta formuła zwraca każdą różną kombinację regionu i produktu jako małą, rozlaną tabelę z dwiema kolumnami. Doskonale nadaje się do tworzenia etykiet wierszy raportu podsumowującego.

Ustaw trzeci argument na TRUE, aby pozostawić tylko wartości występujące dokładnie jeden raz i usunąć wszystkie, które powtarzają się choćby raz.

=UNIQUE(A2:A12,,TRUE)

Zwróć uwagę na dwa przecinki: pomijamy argument by_col i podajemy tylko exactly_once. Jeśli East występuje trzy razy, zostanie całkowicie usunięty, natomiast region występujący jeden raz zostanie zachowany.

Aby zliczyć różne wartości, umieść UNIQUE wewnątrz COUNTA lub ROWS. Aby sprawdzić, ile różnych regionów istnieje:

=ROWS(UNIQUE(A2:A12))

Funkcja ROWS zlicza wiersze rozlanej listy. Po pojawieniu się nowych regionów liczba automatycznie się aktualizuje. To prosty sposób na sprawdzenie, ile różnych elementów znajduje się w danych.

Funkcja UNIQUE zachowuje kolejność pierwszego wystąpienia. Aby wyświetlić listę alfabetycznie, umieść ją wewnątrz SORT:

=SORT(UNIQUE(A2:A12))

Teraz różne regiony zostaną wyświetlone w kolejności: East, North, West. To połączenie jest jednym z najczęściej stosowanych wzorców tworzenia przejrzystych etykiet raportów.

UNIQUE doskonale współpracuje z FILTER. Aby uzyskać listę różnych produktów sprzedanych wyłącznie w regionie East:

=UNIQUE(FILTER(B2:B12, A2:A12="East"))

Najpierw FILTER ogranicza dane do wierszy z regionu East, a następnie UNIQUE usuwa powtarzające się produkty. Zagnieżdżanie w ten sposób funkcji obsługujących rozlewanie pozwala uzyskiwać złożone wyniki za pomocą jednej formuły.

Klasyczny mini-raport łączy funkcję UNIQUE używaną do tworzenia etykiet z funkcją SUMIF służącą do obliczania sum. Umieść różne regiony w kolumnie E za pomocą formuły =UNIQUE(A2:A12), a następnie obok rozlanej listy użyj:

=SUMIF(A2:A12, E2#, C2:C12)

Odwołanie do rozlanego zakresu E2# przekazuje do SUMIF każdy różny region, zwracając jedną sumę dla każdego z nich. Całe podsumowanie aktualizuje się wraz z rozrostem danych.

Funkcja UNIQUE działa zarówno w Excel 365, jak i w Arkuszach Google, choć występują niewielkie różnice. W Arkuszach Google starsza wersja UNIQUE porównywała całe wiersze i nie miała argumentu exactly_once, dlatego w razie rozbieżności wyników należy sprawdzić sposób działania.

Arkusze oferują również UNIQUE wewnątrz QUERY oraz funkcję SORTN do zaawansowanych zastosowań. W przypadku codziennych list różnych wartości podstawowa formuła =UNIQUE(range) działa na obu platformach tak samo.

Lista różnych wartości doskonale nadaje się jako źródło listy rozwijanej w sprawdzaniu poprawności danych. Umieść formułę =UNIQUE(A2:A12) w obszarze pomocniczym, a następnie wskaż zakres rozlany jako źródło listy, używając operatora #.

Jeśli lista zaczyna się w komórce H1, ustaw źródło sprawdzania poprawności na =H1#. Gdy w danych pojawią się nowe regiony, opcje listy rozwijanej automatycznie się powiększą i nie będą wymagać ręcznych aktualizacji.

Sprawdź swoją znajomość funkcji UNIQUE.

Nauczyli się Państwo wyodrębniać różne wartości za pomocą funkcji UNIQUE:

• Składnia to =UNIQUE(array, [by_col], [exactly_once]).
• Funkcja rozlewa różne wartości w kolejności ich pierwszego wystąpienia.
• Może usuwać duplikaty całych wierszy złożonych z wielu kolumn.
• Ustaw exactly_once na TRUE, aby uzyskać wartości występujące tylko raz.
• Połącz ją z SORT, FILTER, ROWS i SUMIF, aby tworzyć przejrzyste raporty na żywo.

W następnym kroku nauczą się Państwo diagnozować i naprawiać błąd SPILL.