Ok, więc na początku mojej analizy danych miałem potrzebę automatycznego pobierania unikalnych pozycji w programie Excel z listy, zamiast usuwania duplikatów za każdym razem. I wymyśliłem, jak to zrobić. Po tym mój pulpit nawigacyjny w Excelu był jeszcze bardziej dynamiczny niż wcześniej. Zobaczmy więc, jak możemy to zrobić…
Aby wyodrębnić listę unikalnych wartości z listy, użyjemy INDEX, MATCH i COUNTIF. Użyję również IFERROR, aby uzyskać czyste wyniki, jest to opcjonalne.
I tak, będzie to formuła tablicowa… Więc zróbmy to…
Ogólna formuła do wyodrębniania unikalnych wartości w Excelu
{=INDEKS(lista_odn., PODAJ.POZYCJĘ(0,LICZ.JEŻELI(rozszerzenie_zakresu_wyjściowego;lista_odn.),0))}
Lista ref.: Lista, z której chcesz wydobyć unikalne wartości
Expanding_output_range: Teraz jest to bardzo ważne. Jest to zakres, w którym chcesz zobaczyć swoją wyodrębnioną listę. Ten zakres musi mieć odrębny nagłówek, który nie jest częścią listy, a formuła będzie znajdować się pod nagłówkiem (jeśli nagłówek jest w E1, to Formuła będzie w E2).
Teraz rozwinięcie oznacza, że po przeciągnięciu formuły powinna ona rozszerzyć się poza zakres wyjściowy. Aby to zrobić, musisz podać odniesienie do nagłówka jako $E$1:E1 (Mój nagłówek jest w E1). Kiedy przeciągnę go w dół, rozszerzy się. W E2 będzie $E$1:E1. Na E3 będzie $E$1:E2. W E2 będzie $E$1:E3 i tak dalej.
Zobaczmy teraz przykład. To wyjaśni.
Przykład wyodrębniania unikalnych wartości w Excelu
Więc tutaj mam tę listę klientów w kolumnie A w zakresie A2:A16. Teraz w kolumnie E chcę uzyskać unikalne wartości tylko od klientów. Teraz ten zakres A2: A16 może również wzrosnąć, więc chcę, aby moja formuła pobierała każdą nową nazwę klienta z listy, gdy lista się zwiększa.
Ok, teraz, aby pobrać unikalne wartości z kolumny A, napisz tę formułę w Komórka E2, i uderzył CTRL+SHIFT+ENTER aby była formułą tablicową.
{=INDEKS($A$2:A16,MATCH(0,LICZ.JEŻELI($E$1:E1,$A$2:A16),0))}
A2:A16: Spodziewam się, że ta lista będzie się rozszerzać i może zawierać nowe unikalne wartości, które chciałbym wydobyć. Dlatego zostawiłem to otwarte od dołu, nie bezwzględnie odwołując się do A16. Pozwoli to rozwinąć się za każdym razem, gdy skopiujesz formułę poniżej.
Wiemy więc, jak działa funkcja INDEKS i PODAJ.POZYCJĘ. Główna część to:
LICZ.JEŻELI($E$1:E1,$A$2:A16): Ta formuła zwróci tablicę złożoną z jedynek i zer. Ilekroć wartość w zakresie $E$1:E1 znajduje się na liście kryteriów $A$2:A16, wartość jest konwertowana na 1 na swojej pozycji w zakresie $A2:A16.
Teraz korzystając z funkcji PODAJ.POZYCJĘ szukamy wartości 0. Dopasowanie zwróci pozycję pierwszego 0 znalezionego w tablicy zwróconej przez funkcję LICZ.JEŻELI. Następnie INDEKS zajrzy do A2:A16 aby zwrócić wartość znalezioną w indeksie zwróconym przez funkcję PODAJ.POZYCJĘ.
Może trochę trudno to ogarnąć, ale działa. 0 na końcu listy oznacza, że nie ma więcej unikalnych wartości. Jeśli na końcu nie widzisz 0, skopiuj formułę w poniższych komórkach.
Teraz, aby uniknąć #Nie dotyczy w można użyć funkcji IFERROR programu Excel.
{=JEŻELIBŁĄD(INDEKS($A$2:A16;MATCH(0;LICZ.JEŻELI($E$1:$E1,$A$2:A16);0)),"")}
Tak, możesz użyć tej formuły, aby uzyskać unikalne wartości z listy. W programie Excel 2019 z subskrypcją Office 365 firma Microsoft oferuje funkcję o nazwie UNIQUE. Po prostu przyjmuje zakres jako argument i zwraca tablicę unikalnych wartości. Nie jest dostępny w jednorazowym zakupie programu Microsoft Excel 2016.
Pobieranie pliku:
Formuła Excel do wyodrębniania unikalnych wartości z listyFormuła Excel do wyodrębniania unikalnych wartości z listy
Jak liczyć unikalne wartości w Excelu?
Popularne artykuły:
50 skrótów Excela, które zwiększą Twoją produktywność
Jak korzystać z funkcji WYSZUKAJ.PIONOWO w programie Excel
Jak korzystać z funkcji LICZ.JEŻELI w programie Excel
Jak korzystać z funkcji SUMA.JEŻELI w programie Excel
