Note: The other languages of the website are Google-translated. Back to English

Jak dynamicznie wyodrębnić listę unikatowych wartości z zakresu kolumn w programie Excel?

W przypadku zakresu kolumn, w którym wartości zmieniają się regularnie i zawsze musisz pobrać wszystkie unikalne wartości z zakresu, niezależnie od tego, jak się zmienił. Jak stworzyć dynamiczną listę unikalnych wartości? Ten artykuł pokaże Ci, jak sobie z tym poradzić.

Dynamicznie wyodrębnij listę unikatowych wartości z zakresu kolumn za pomocą formuły
Dynamicznie wyodrębnij listę unikatowych wartości z zakresu kolumn za pomocą kodu VBA


Dynamicznie wyodrębnij listę unikatowych wartości z zakresu kolumn za pomocą formuły

Jak pokazano na poniższym zrzucie ekranu, musisz dynamicznie wyodrębnić listę unikalnych wartości z zakresu B2: B9. Wypróbuj następującą formułę tablicową.

1. Wybierz pustą komórkę, taką jak D2, wprowadź do niej poniższą formułę i naciśnij Ctrl + przesunięcie + Wchodzę klucze jednocześnie. (B2: B9 to dane kolumny, które chcesz wyodrębnić unikalne wartości, D1 to powyższa komórka, w której znajduje się twoja formuła)

=IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$1:D1, $B$2:$B$9), 0)),"")

2. Kontynuuj wybieranie komórki D2, a następnie przeciągnij uchwyt wypełnienia w dół, aby uzyskać wszystkie unikalne wartości z określonego zakresu.

Teraz wszystkie unikalne wartości w zakresie kolumn B2: B9 są wyodrębniane. Gdy wartości w tym zakresie ulegną zmianie, lista unikatowych wartości zostanie natychmiast zmieniona dynamicznie.

Z łatwością wybieraj i zaznaczaj wszystkie unikalne wartości w zakresie w programie Excel:

Podróż Ruta de la Plata w liczbach Wybierz Zduplikowane i unikalne komórki użyteczność Kutools dla programu Excel może pomóc ci łatwo wybrać i wyróżnić wszystkie unikalne wartości (w tym pierwsze duplikaty) lub unikalne wartości, które pojawiają się tylko raz, a także zduplikowane wartości, jak potrzebujesz, jak pokazano na zrzucie ekranu.
Pobierz teraz Kutools dla programu Excel! (30-dzień wolny szlak)


Dynamicznie wyodrębnij listę unikatowych wartości z zakresu kolumn za pomocą kodu VBA

Możesz również wyodrębnić listę unikatowych wartości dynamicznie z zakresu kolumn za pomocą następującego kodu VBA.

1. naciśnij inny + F11 klawisze jednocześnie, aby otworzyć Microsoft Visual Basic for Applications okno.

2. w Microsoft Visual Basic for Applications okno, kliknij wstawka > Moduł. Następnie skopiuj i wklej poniższy kod VBA do pliku Moduł okno.

Kod VBA: wyodrębnij listę unikalnych wartości z zakresu

Sub CreateUniqueList()
Dim xRng As Range
Dim xLastRow As Long
Dim xLastRow2 As Long
Dim I As Integer
On Error Resume Next
Set xRng = Application.InputBox("Please select range:", "Kutools for Excel", Selection.Address, , , , , 8)
If xRng Is Nothing Then Exit Sub
On Error Resume Next
xRng.Copy Range("D2")
xLastRow = xRng.Rows.Count + 1
ActiveSheet.Range("D2:D" & xLastRow).RemoveDuplicates Columns:=1, Header:=xlNo
xLastRow2 = Cells(Rows.Count, "B").End(xlUp).Row
For I = 1 To xLastRow2
  If ActiveSheet.Range("D2:D" & xLastRow2).Cells(I).Value = "" Then
     ActiveSheet.Range("D2:D" & xLastRow2).Cells(I).Delete
  End If
Next
End Sub

Uwagi: W kodzie D2 to komórka, w której znajduje się lista unikatowych wartości. Możesz to zmienić według potrzeb.

3. Wróć do arkusza, kliknij wstawka > Kształty > Prostokąt. Zobacz zrzut ekranu:

4. Narysuj prostokąt w arkuszu, a następnie wprowadź kilka słów, które chcesz na nim wyświetlić. Następnie kliknij go prawym przyciskiem myszy i wybierz Przypisz makro z menu po kliknięciu prawym przyciskiem myszy. w Przypisz makro w oknie dialogowym wybierz opcję Utwórz unikalną listę Nazwa makra a następnie kliknij OK przycisk. Zobacz zrzut ekranu:

5. Teraz kliknij przycisk prostokąta, a Kutools dla programu Excel pojawi się okno dialogowe, wybierz zakres zawierający unikalne wartości, które chcesz wyodrębnić, a następnie kliknij OK przycisk.

Od teraz możesz powtórzyć powyższy krok 5, aby automatycznie zaktualizować listę unikatowych wartości.


Powiązane artykuły:


Najlepsze narzędzia biurowe

Kutools dla programu Excel rozwiązuje większość problemów i zwiększa produktywność o 80%

  • Ponowne użycie: Szybko włóż złożone wzory, wykresy i wszystko, czego używałeś wcześniej; Szyfruj komórki z hasłem; Utwórz listę mailingową i wysyłaj e-maile ...
  • Pasek Super Formula (łatwo edytować wiele wierszy tekstu i formuły); Układ do czytania (łatwe odczytywanie i edytowanie dużej liczby komórek); Wklej do filtrowanego zakresu...
  • Scal komórki / wiersze / kolumny bez utraty danych; Podziel zawartość komórek; Połącz zduplikowane wiersze / kolumny... Zapobiegaj zduplikowanym komórkom; Porównaj zakresy...
  • Wybierz Duplikat lub Unikalny Wydziwianie; Wybierz puste wiersze (wszystkie komórki są puste); Super Find i Fuzzy Find w wielu zeszytach ćwiczeń; Losowy wybór ...
  • Dokładna kopia Wiele komórek bez zmiany odwołania do formuły; Automatyczne tworzenie odniesień do wielu arkuszy; Wstaw punktory, Pola wyboru i nie tylko ...
  • Wyodrębnij tekst, Dodaj tekst, Usuń według pozycji, Usuń przestrzeń; Tworzenie i drukowanie podsumowań stronicowania; Konwertuj zawartość komórek i komentarze...
  • Super filtr (zapisz i zastosuj schematy filtrów do innych arkuszy); Zaawansowane sortowanie według miesiąca / tygodnia / dnia, częstotliwości i innych; Specjalny filtr pogrubieniem, kursywą ...
  • Połącz skoroszyty i arkusze robocze; Scal tabele na podstawie kluczowych kolumn; Podziel dane na wiele arkuszy; Konwersja wsadowa xls, xlsx i PDF...
  • Ponad 300 zaawansowanych funkcji. Obsługuje Office / Excel 2007-2021 i 365. Obsługuje wszystkie języki. Łatwe wdrażanie w przedsiębiorstwie lub organizacji. Pełne funkcje 30-dniowa bezpłatna wersja próbna. 60-dniowa gwarancja zwrotu pieniędzy.
karta kte 201905

Karta Office wprowadza interfejs z zakładkami do pakietu Office i znacznie ułatwia pracę

  • Włącz edycję i czytanie na kartach w programach Word, Excel, PowerPoint, Publisher, Access, Visio i Project.
  • Otwieraj i twórz wiele dokumentów w nowych kartach tego samego okna, a nie w nowych oknach.
  • Zwiększa produktywność o 50% i redukuje setki kliknięć myszką każdego dnia!
officetab dół
Sortuj komentarze według
Komentarze (35)
Brak ocen. Oceń jako pierwszy!
Ten komentarz został zminimalizowany przez moderatora na stronie
Dziękuję Ci za tutorial. Używając metody formuły, jak zmienisz formułę, jeśli chcesz dodać kwalifikator kategorii? Powiedzmy, że w kolumnie C rozróżniasz, czy przedmiot jest owocem, czy warzywem. Jak zmienić kod, aby sortować tylko unikalne owoce i wykluczać warzywa? Próbowałem zamienić LICZ.JEŻELI na LICZ.JEŻELI, używając drugiego kryterium licznika (ZAKRES LISTY, „KATEGORIA”), ale zwraca puste. Czy muszę rozszerzyć moją tablicę i włączyć funkcję WYSZUKAJ.PIONOWO?
Ten komentarz został zminimalizowany przez moderatora na stronie
Jestem przyzwoity w programie Excel, ale naprawdę staram się owinąć głowę wokół tego, jak i dlaczego powyższa formuła działa (działa w tym, do czego jej używam, ale muszę zrozumieć, dlaczego). Czasami trochę się mylę, używając tablic, więc każde wyjaśnienie w terminach idiotów byłoby niezwykle pomocne Pozdrawiam
Ten komentarz został zminimalizowany przez moderatora na stronie
Ta formuła jest przestarzała i nie działa. Dosłownie po prostu ustawiłem ten dokładny arkusz Excela, aby sprawdzić, czy uda mi się uruchomić tę formułę, a to nie działa.
Ten komentarz został zminimalizowany przez moderatora na stronie
Hej koleś,
Z której wersji pakietu Office korzystasz?
Ten komentarz został zminimalizowany przez moderatora na stronie
{=INDEX($Q$3:$Q$263,MATCH(0,COUNTIF(V$2:V2,$Q$3:$Q$263),0))} — stwierdzono, że to działa z innej witryny...

Użyj Ctrl+Shift+Enter, aby uzyskać funkcję tablicową (nawiasy klamrowe). Przeciągnij kopiuj i wklej formuły, aż pojawi się #NA. Mój zestaw danych znajdował się w kolumnie Q, został porównany, aby sprawdzić, czy istnieje na liście unikatów w kolumnie V, która stale rozciąga się wzdłuż tej samej kolumny.
Ten komentarz został zminimalizowany przez moderatora na stronie
Dobry dzień.
Wymień wszystkie unikalne wartości kolumny Q z formułą abobv, a następnie użyj jego formuły = JEŻELI (D2 = V1, „Dopasowanie”, „Brak dopasowania”), aby porównać, czy unikaty w kolumnie Q są w porównaniu z kolumną V w tym samym wierszu .
Ten komentarz został zminimalizowany przez moderatora na stronie
Witam i dziękuję za pomoc.

Potrzebuję dokładnie tej funkcji, ale moja lista „unikalnych wartości” musi rozciągać się na kolumny, a nie na wiersze, więc rozwijanie listy w dół wierszy nie będzie dla mnie działać.

Jak mogę zmodyfikować tę formułę, aby lista „unikalnych wartości” rozszerzała się podczas przeciągania jej przez kolumny?

Zrównoważyć()?
Transponować()?
Indirect() z ciągiem odwołań bezwzględnych połączonych z odwołaniem do kolumny zamiast wiersza?


Dzięki jeszcze raz!
Ten komentarz został zminimalizowany przez moderatora na stronie
Drogi Ryanie,
Ta formuła = JEŻELI BŁĄD (INDEKS($ B$2:$B$9, MATCH(0,COUNTIF($D$2:D2,$B$2:$B$9), 0)),"") + Ctrl + Shift + Enter może pomóc rozwiązać problem.
Zobacz poniższy zrzut ekranu:
Ten komentarz został zminimalizowany przez moderatora na stronie
Ponadto, z jakiegokolwiek powodu, oryginalna formuła zapewniała:
=IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$1:D1, $B$2:$B$9), 0)),"")

zwraca ostrzeżenie „okrężne odwołanie” i nie oblicza..
Ten komentarz został zminimalizowany przez moderatora na stronie
Drogi Ryanie,
Z której wersji pakietu Office korzystasz? Formuła dobrze sprawdza się w moim pakiecie Office 2016 i 2013.
Ten komentarz został zminimalizowany przez moderatora na stronie
Miałem to już wcześniej - moja poprawka polegała na tym, że wprowadzałem formułę do komórki D1 (odpowiednik w arkuszu, którego używałem). Niezależnie od komórki, której odpowiada $D:$1, musisz wprowadzić ją w komórce poniżej - D2. Przepraszamy, jeśli to nie dlatego pojawił się błąd
Ten komentarz został zminimalizowany przez moderatora na stronie
Jakieś wskazówki dotyczące uzyskania opcji VBA do pracy z programem Excel 2016 dla systemu macOS? Podążyłem za krokami; jednak kiedy uruchamiam makro, nic się nie dzieje. Dzięki!
Ten komentarz został zminimalizowany przez moderatora na stronie
Daer Jones,
Wypróbuj poniższy kod VBA i daj mi znać, czy działa dla Ciebie. Dziękuję Ci!

Sub CreateUniqueList()
Dim xRng jako zakres
Dim xLastRow tak długo
Dim xLastRow2 tak długo
Dim I jako liczba całkowita
' Po błędzie wznowienia Dalej
Set xRng = Application.InputBox("Proszę wybrać zakres:", "Kutools for Excel", Selection.Address, , , , , 8)
Jeśli xRng jest niczym, wyjdź z Sub
On Error Resume Next
xRng.Zakres kopiowania("D2")
xLastRow = xRng.Row.Count + 1
ActiveSheet.Range("D2:D" & xLastRow).RemoveDuplicates Columns:=1, Header:=xlNo
xLastRow2 = Cells(Rows.Count, "B").End(xlUp).Row
Dla I = 1 To xLastRow2
Jeśli ActiveSheet.Range("D2:D" & xLastRow2).Cells(I).Value = "" Wtedy
ActiveSheet.Range("D2:D" & xLastRow2).Cells(I).Usuń
End If
Dalej
End Sub
Ten komentarz został zminimalizowany przez moderatora na stronie
Cześć Kryształowo,
Próbuję użyć wersji VB listy unikatowych wartości i napotykam problem.
Zakres, z którego chcę utworzyć kolumnę unikalnych wartości, to wszystkie formuły, które odwołują się do różnych kart.
Jak uzyskać wartość do przeniesienia zamiast formuły?
Ten komentarz został zminimalizowany przez moderatora na stronie
Drogi Mike,
Przekształć odwołania do formuł na bezwzględne, a następnie zastosuj skrypt VB.
Ten komentarz został zminimalizowany przez moderatora na stronie
Mam ten sam problem, z tą różnicą, że moja formuła odwołuje się do nazw kolumn i nie może przekonwertować na bezwzględną.
Jak zmienić vba, aby wkleić wartości, a nie formułę?
Ten komentarz został zminimalizowany przez moderatora na stronie
Jak dodać wiele kryteriów, na przykład gdybyś chciał dodać do listy dynamicznej tylko datę 9/12?

Próbuję „&” w formule PODAJ.POZYCJĘ, ale to nie działa.

Na przykład na podstawie twojego przykładu:
=JEŻELIBŁĄD(INDEKS($B$2:$B$9; PODAJ.POZYCJĘ(0 & B4;LICZ.JEŻELI($D$1:D1;$B$2:$B$9)&$A$2:$A$9;0)))"" )
To generuje błąd lub tworzy duplikaty.

Ewentualnie czytałem, że "+" może działać, chociaż nie mogę go uruchomić. Lub używając MAŁEGO.

Pomysły?
Ten komentarz został zminimalizowany przez moderatora na stronie
Drogi Zac,
Przepraszamy, nie mogę w tym pomóc, możesz zadać pytanie na naszym forum: https://www.extendoffice.com/forum.html aby uzyskać więcej wsparcia Excel od naszego profesjonalisty.
Ten komentarz został zminimalizowany przez moderatora na stronie
Jak dodałbyś drugą zmienną? Na przykład chcę, aby wszystkie unikalne elementy w jednej kolumnie miały podobną wartość w innej kolumnie. W swoim przykładzie wyobraź sobie trzecią kolumnę zatytułowaną „Dział”, która zawierałaby wartości takie jak produkt, mięso itp. Zdaję sobie sprawę, że to wszystko jest Produkcja, ale mam nadzieję, że rozumiesz, o co mi chodzi. Czy zmodyfikujesz formułę LICZ.JEŻELI na LICZ.JEŻELI, czy zmodyfikujesz ją w inny sposób?
Ten komentarz został zminimalizowany przez moderatora na stronie
Cześć Matt
Wypróbuj tę formułę =IF(ISNA(VLOOKUP(A2,$C$2:$C$13,1,FALSE)),"Tak","").
Załóżmy, że dwie porównywane listy to kolumna A i kolumna C, jeśli unikalne wartości pozostają tylko w kolumnie A, ale nie w kolumnie C, zostanie wyświetlone Tak w kolumnie B; podczas gdy jeśli nic nie zwrócisz w kolumnie B, oznacza to, że odpowiadająca wartość pozostaje zarówno w kolumnie A, jak i kolumnie C.
Ten komentarz został zminimalizowany przez moderatora na stronie
Dziękuję za odpowiedź.. ale gorąco, aby wyciągnąć tę unikalną wartość, jeśli wyświetli się TAK.
Ten komentarz został zminimalizowany przez moderatora na stronie
Jeśli zrobię to dla tysiąca wierszy arkusza Excela w najnowszej wersji programu Excel na komputerze Mac, nigdy nie wróci. Pierwszy wiersz działa, ale kiedy duplikuję, Excel przechodzi w tryb obliczeniowy, który nie zwraca wartości przez ponad dwie godziny.

Myślisz, jak to zrobić w przypadku dużych list (do 2 tys. wierszy), które zwrócą 50 lub 60 unikalnych wartości?

Wyśmiewałem to w aplikacji „Liczby” i działa idealnie, a obliczenie zajmuje tylko kilka minut. W Excelu trwa to tak długo, że zastanawiam się, czy kiedykolwiek się zakończy. Planuję pozwolić mu "biegnąć" przez noc, aby zobaczyć, co się stanie.
Ten komentarz został zminimalizowany przez moderatora na stronie
Sprawdź opcje obliczania. Musi być ustawiony na automatyczny. Plik > Opcje > Formuły > Opcje obliczania > Obliczanie skoroszytu (wybór automatyczny)
Ten komentarz został zminimalizowany przez moderatora na stronie
Próbuję przeciągnąć formułę poza moje rzeczywiste dane, aby móc wprowadzać zestawy danych o różnych rozmiarach i nie muszę niczego dostosowywać. Jednak ostatni wiersz po zakończeniu moich rzeczywistych danych zawsze zwraca „0”. Używam unikalnych wartości dla czegoś innego w sąsiedniej kolumnie, a 0 powoduje powtórzenie ostatniej wartości (po usunięciu 0 wartość nie jest już powtarzana). Jakiś pomysł jak to naprawić? Również używam Office 365 Business
Ten komentarz został zminimalizowany przez moderatora na stronie
Witam, dziękuję za pomoc.
Jak mogę teraz posortować moje wartości alfabetycznie? (Nie chcę używać filtra na moim stole głównym)
Czy powinienem używać funkcji LICZ.JEŻELI zamiast LICZ.JEŻELI?
Proszę o pomoc
Ten komentarz został zminimalizowany przez moderatora na stronie
Cześć Alexis,
Niestety nie mogę posortować wyodrębnionej wartości alfabetycznie w tym samym czasie z formułą. Dziękuję za Twój komentarz.
Ten komentarz został zminimalizowany przez moderatora na stronie
Używam tej formuły =IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$1:D1,$B$2:$B$9),0)),""), która jest świetna dla jednej kolumny, ale moje dane są rozłożone na szereg kolumn i wierszy. Czy mogę edytować formułę, aby obejmowała cały obszar? Moje dane żyją od AC4 do AR60...
Ten komentarz został zminimalizowany przez moderatora na stronie
Próbuję kodu VBA i formuły. Kod VBA działa bardzo dobrze, ale nie mogę zachować pliku z makrami. Ale problem polega na tym, że nie mogę sprawić, by formuła działała. Czy ktoś miał pomysł? Dziękuję Ci
Ten komentarz został zminimalizowany przez moderatora na stronie
Cześć Charlotte,
Dziękuję za Twój komentarz. Możesz zachować plik z makrem do wykorzystania w przyszłości, zapisując skoroszyt jako skoroszyt programu Excel z włączoną obsługą makr.
W przypadku problemu z formułą, czy możesz przesłać zrzut ekranu swoich danych? Dziękuję za Twój komentarz.
Ten komentarz został zminimalizowany przez moderatora na stronie
Dziękuję bardzo
Ten komentarz został zminimalizowany przez moderatora na stronie
jak sprawić, by kod vba działał dla zakresu, w którym użyto innej formuły? w kolumnie BI masz formułę odwołującą się do kolumn D i E.
Jeśli zastosuję kod do kolumny L (powiedzmy), (oczywiście odpowiednio modyfikując komórki w kodzie) makro zwróci formułę zastosowaną do kolumn M i N... To działa, ale nie tak, jak chcę! Jak zachować wartości w kolumnie B?dzięki
Ten komentarz został zminimalizowany przez moderatora na stronie
Ik heb gemerkt dat de 'formule'-methode erg traag werkt bij grote datasets. Een goed alternatief is het gebruik van een draaitabel. Kies dan alleen de rijlabels, je krijgt dan een lijstje spotkał unieke waarden. Het kan zijn dat je wat extra's krijgt „(leeg)” bijvoorbeeld. Je kunt deze er vervolgens uitfilteren. Helaas kun je maar op 1 criterium filteren. Ook daar zijn wel weer oplossingen voor, maar dat jest wat complexer.
Ten komentarz został zminimalizowany przez moderatora na stronie
Chciałbym móc zrobić dokładnie to samo, z wyjątkiem używania dwóch oddzielnych zakresów kolumn (B2: B9) oraz (D2: D9) czy jest to możliwe?
Ten komentarz został zminimalizowany przez moderatora na stronie
Hi Anthony
Możesz umieścić wyniki w tej samej kolumnie, co oryginalne dane. Na przykład kolumna B w tym przypadku.
Ale musisz odwołać się do górnej komórki komórki wynikowej w formule w następujący sposób.
=JEŻELIBŁĄD(INDEKS($B$2:$B$9; PODAJ.POZYCJĘ(0;LICZ.JEŻELI($B$11:B11;$B$2:$B$9);0)));"") + Ctrl + Shift + Enter
Ten komentarz został zminimalizowany przez moderatora na stronie
con este procedimiento de filtro se hace de forma muy rapida

1.EN ESTE EJEMPLO los datos a remover los duplicados estan en la col A de la fila 59 a la 239
2. se zdefiniuj un criterio de filtrado en este caso en la fila d56 el mismo titulo de la lista a remover duplicados y la d57 la dejo en blanco
3. una vez ejecutado se muestran los datos en la fila destino, que en mi caso fue la d59

Zakres("A59:A239").Zaawansowana akcja filtra:=xlFilterCopy, CriteriaRange:=Range _
("D56:D57"), CopyToRange:=Range("D59"), Unique:=True
Nie ma tu jeszcze żadnych komentarzy
Zostaw swój komentarz
Publikowanie jako gość
×
Oceń ten post:
0   Postacie
Sugerowane lokalizacje

Bądż na bieżąco

Prawa autorskie © 2009 - www.extendoffice.com. | Wszelkie prawa zastrzeżone. Zasilany przez ExtendOffice, | Mapa strony
Microsoft i logo Office są znakami towarowymi lub zastrzeżonymi znakami towarowymi Microsoft Corporation w Stanach Zjednoczonych i / lub innych krajach.
Chronione przez Sectigo SSL