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

Jak wyodrębnić unikalne wartości z wielu kolumn w programie Excel?

Przypuśćmy, że masz kilka kolumn z wieloma wartościami, niektóre wartości są powtarzane w tej samej lub innej kolumnie. A teraz chcesz znaleźć wartości, które są obecne w jednej z kolumn tylko raz. Czy są jakieś szybkie sztuczki, aby wyodrębnić unikalne wartości z wielu kolumn w programie Excel?


Wyodrębnij unikalne wartości z wielu kolumn za pomocą formuły tablicowej

Oto formuła tablicowa, która również może pomóc w wyodrębnieniu unikatowych wartości z wielu kolumn.

1. Zakładając wartości w zakresie A2: C9, wprowadź następującą formułę do komórki E2:

=INDIRECT(TEXT(MIN(IF(($A$2:$C$9<>"")*(COUNTIF($E$1:E1,$A$2:$C$9)=0),ROW($2:$9)*100+COLUMN($A:$C),7^8)),"R0C00"),)&""

Uwagi: W powyższym wzorze, A2: C9 wskazuje zakres komórek, z których chcesz wyodrębnić unikalne wartości, E1: E1 to pierwsza komórka kolumny, w której chcesz umieścić wynik, $ 2: $ 9 stoi w wierszach zawiera komórki, których chcesz użyć, i $ A: $ C wskazuje, że kolumny zawierają komórki, których chcesz użyć. Zmień je na własne.

2. Następnie naciśnij Shift + Ctrl + Enter klucze razem, a następnie przeciągnij uchwyt wypełniania, aby wyodrębnić unikatowe wartości, aż pojawią się puste komórki. Zobacz zrzut ekranu:


Wyodrębnij unikalne wartości z wielu kolumn za pomocą tabeli przestawnej

Jeśli znasz tabelę przestawną, możesz łatwo wyodrębnić unikalne wartości z wielu kolumn, wykonując następujące czynności:

1. Najpierw wstaw jedną nową pustą kolumnę po lewej stronie danych, w tym przykładzie wstawię kolumnę A obok oryginalnych danych.

2. Kliknij jedną komórkę w swoich danych i naciśnij Alt + D klawiszy, a następnie naciśnij P natychmiast, aby otworzyć Kreator tabel przestawnych i wykresów przestawnychwybierz Wiele zakresów konsolidacji w kroku kreatora 1, zobacz zrzut ekranu:

3. Następnie kliknij Dalej przycisk, sprawdź Utwórz dla mnie jedno pole strony opcja w kroku kreatora 2, zobacz zrzut ekranu:

4. Kliknij dalej Dalej kliknij, aby zaznaczyć zakres danych obejmujący lewą nową kolumnę komórek, a następnie kliknij Dodaj przycisk, aby dodać zakres danych do pliku Wszystkie zakresy pole listy, zobacz zrzut ekranu:

5. Po wybraniu zakresu danych kliknij dalej Dalej, w kroku 3 kreatora wybierz, gdzie chcesz umieścić raport w formie tabeli przestawnej, jak chcesz.

6. Nareszcie kliknij koniec aby zakończyć pracę kreatora, a tabela przestawna została utworzona w bieżącym arkuszu, a następnie odznacz wszystkie pola w Wybierz pola, które chcesz dodać do raportu sekcja, patrz zrzut ekranu:

7. Następnie sprawdź pole wartość lub przeciągnij wartość do Wydziwianie label, teraz otrzymasz unikalne wartości z wielu kolumn w następujący sposób:


Wyodrębnij unikalne wartości z wielu kolumn za pomocą kodu VBA

Za pomocą następującego kodu VBA można również wyodrębnić unikalne wartości z wielu kolumn.

1. Przytrzymaj ALT + F11 klucze i otwiera plik Okno Microsoft Visual Basic for Applications.

2, Kliknij wstawka > Modułi wklej następujący kod w oknie modułu.

VBA: wyodrębnij unikalne wartości z wielu kolumn

Sub Uniquedata()
'Updateby Extendoffice
Dim rng As Range
Dim InputRng As Range, OutRng As Range
Set dt = CreateObject("Scripting.Dictionary")
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
For Each rng In InputRng
    If rng.Value <> "" Then
        dt(rng.Value) = ""
    End If
Next
OutRng.Range("A1").Resize(dt.Count) = Application.WorksheetFunction.Transpose(dt.Keys)
End Sub

3. Następnie naciśnij F5 aby uruchomić ten kod, pojawi się okno zachęty przypominające o wyborze zakresu danych, którego chcesz użyć. Zobacz zrzut ekranu:

4. A następnie kliknij OKpojawi się kolejne okno zachęty umożliwiające wybranie miejsca do umieszczenia wyniku, patrz zrzut ekranu:

5. Kliknij OK aby zamknąć to okno dialogowe, a wszystkie unikalne wartości zostały wyodrębnione jednocześnie.


Wyodrębnij unikalne wartości z jednej kolumny dzięki niesamowitej funkcji

Czasami trzeba wyodrębnić unikalne wartości z jednej kolumny, powyższe metody nie pomogą, tutaj mogę polecić przydatne narzędzie-Kutools dla programu Excel, Z siedzibą w Wyodrębnij komórki z unikalnymi wartościami (uwzględnij pierwszy duplikat) narzędzie, możesz szybko wyodrębnić unikalne wartości.

Uwaga:Aby to zastosować Wyodrębnij komórki z unikalnymi wartościami (uwzględnij pierwszy duplikat), po pierwsze, należy pobrać plik Kutools dla programu Excel, a następnie szybko i łatwo zastosuj tę funkcję.

Po zainstalowaniu Kutools dla programu Excel, zrób tak:

1. Kliknij komórkę, w której chcesz wyprowadzić wynik. (Uwagi: Nie klikaj komórki w pierwszym wierszu.)

2. Następnie kliknij Kutools > Pomocnik formuły > Pomocnik formułyzobacz zrzut ekranu:

3, w Pomocnik formuł w oknie dialogowym, wykonaj następujące operacje:

  • Wybierz Tekst opcja z Formuła Typ Lista rozwijana;
  • Następnie wybierz Wyodrębnij komórki z unikalnymi wartościami (uwzględnij pierwszy duplikat) z Wybierz fromula skrzynka na listy;
  • Po prawej Wprowadzanie argumentów wybierz listę komórek, z których chcesz wyodrębnić unikatowe wartości.

4. Następnie kliknij Ok i przeciągnij uchwyt wypełniania do komórek, które chcesz wyświetlić wszystkie unikalne wartości, aż zostaną wyświetlone puste komórki, zobacz zrzut ekranu:

Bezpłatne pobieranie Kutools dla programu Excel teraz!


Więcej artykułów względnych:

  • Policz liczbę unikalnych i odrębnych wartości z listy
  • Przypuśćmy, że masz długą listę wartości z kilkoma zduplikowanymi elementami, teraz chcesz policzyć liczbę unikalnych wartości (wartości, które pojawiają się na liście tylko raz) lub odrębnych wartości (wszystkie różne wartości na liście, oznacza to unikalne wartości + pierwsze zduplikowane wartości) w kolumnie, jak pokazano na lewym ekranie. W tym artykule opowiem o tym, jak radzić sobie z tą pracą w programie Excel.
  • Sumuj unikalne wartości na podstawie kryteriów w programie Excel
  • Na przykład mam zakres danych, który zawiera kolumny Nazwa i Kolejność, aby teraz zsumować tylko unikalne wartości w kolumnie Zamówienie na podstawie kolumny Nazwa, jak pokazano na poniższym zrzucie ekranu. Jak szybko i łatwo rozwiązać to zadanie w programie Excel?

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 (31)
Znamionowy 5 z 5 · 1 oceny
Ten komentarz został zminimalizowany przez moderatora na stronie
Is this formula complete? =INDIRECT(TEXT(MIN(IF(($A$2:$C$9"")*(COUNTIF($E$1:E1,$A$2:$C$9)=0),ROW($2:$9)*100+COLUMN($A:$C),7^8)),"R0C00"),)&"",
Ten komentarz został zminimalizowany przez moderatora na stronie
To nie zostało jeszcze naprawione :smutne:
Ten komentarz został zminimalizowany przez moderatora na stronie
co za strata czasu..... formuła NIE działa
Ten komentarz został zminimalizowany przez moderatora na stronie
Dziękuję Ci!!! Spędziłem godziny próbując to zrobić i dowiedzieć się, co stało się z Pivot Wizard (inny artykuł).
Ten komentarz został zminimalizowany przez moderatora na stronie
Używam twojego kodu VBA, ale nie chcę, aby okno się wyświetlało. Zamiast tego chcę dokładnie zdefiniować, jakiego zakresu komórek używać za każdym razem i dokładnie w jakim polu umieścić dane wyjściowe. Zakres wejściowy i wynik byłyby na dwóch różnych arkuszach. jak zaktualizować VBA, aby to zrobić? Dziękuję Ci!!
Ten komentarz został zminimalizowany przez moderatora na stronie
Hej! Czy ktoś wie, dlaczego ta formuła wydaje się prowadzić do błędu po wierszu 87? Na przykład działa idealnie, a potem w pewnym momencie zwraca mi błędy dla każdego wiersza.. co jest najgorsze! Ponieważ jestem tak blisko tego, czego tutaj potrzebuję...
Ten komentarz został zminimalizowany przez moderatora na stronie
=INDIRECT(TEXT(MIN(IF(($A$2:$C$9"")*(COUNTIF($E$1:E1,$A$2:$C$9)=0),ROW($2:$9)*100+COLUMN($A:$C),7^8)),"R0C00"),)&"" It doesn't work
Ten komentarz został zminimalizowany przez moderatora na stronie
cześć chcę wyodrębnić unikalne komórki z pierwszej kolumny, gdy porównuję ją z innymi kolumnami (mam trzy nierówne kolumny), jak mogę to zrobić?
Ten komentarz został zminimalizowany przez moderatora na stronie
cześć, mam trzy nierówne kolumny i chcę wyodrębnić unikalne komórki z pierwszej kolumny. jak mogę to zrobić?? Z góry dziękuję
Ten komentarz został zminimalizowany przez moderatora na stronie
Uwielbiam

Wyodrębnij unikalne wartości z wielu kolumn za pomocą tabeli przestawnej
Ten komentarz został zminimalizowany przez moderatora na stronie
Czy możesz przesłać poprawną formułę ... funkcja VBA działa dobrze.
Tylko do mojego projektu używam raczej poprawnej formuły.


Podziękowania
Ten komentarz został zminimalizowany przez moderatora na stronie
czy ktoś wie, na wyjściu, jak to zrobić w kilka wierszy, a nie w jeden wiersz? (obecnie jeden wynik wiersza jest osiągany przez worksheetfunction.transpose, ale to, co chcę osiągnąć (jako wynik), to to, że po wybraniu dla 3 kolumn zwracany wynik to również 3 kolumny, zamiast jednej
Ten komentarz został zminimalizowany przez moderatora na stronie
Ta formuła tablicowa jest PRAWIDŁOWA. Dane w kolumnach od A do C, pierwsza formuła wynikowa w komórce D2... Ta formuła różni się od innych formuł tablicowych, które później polegają na kopiowaniu formuły w dół i Ctrl+Shift+Enter wszystkich formuł. Jednak tę formułę tablicową należy wykonać Ctrl+Shift+Enter w pierwszej komórce i skopiować w dół.
Ten komentarz został zminimalizowany przez moderatora na stronie
Muchas gracias po makro!!! ja fue muy util
Ten komentarz został zminimalizowany przez moderatora na stronie
dostosowałem się do mojego arkusza, ale zwracam tylko pierwszą wartość w zdefiniowanej tablicy ... czego mi brakuje?
Ten komentarz został zminimalizowany przez moderatora na stronie
Witaj, Cody,
Powyższa formuła działa dobrze w moim arkuszu, czy możesz podać tutaj zrzut ekranu swojego problemu z danymi?
Dziękuję Ci!
Ten komentarz został zminimalizowany przez moderatora na stronie
Jeśli chodzi o wersję formuły, czy mógłbyś bardziej szczegółowo wyjaśnić, co robi ta część? *100+COLUMN($A:$C),7^8)),"R0C00") W szczególności, jakie są * 100, 7 ^ 8, „R0C000” czyn? Rozumiem wszystko inne, ale nie wiem, do czego to służy.
Ten komentarz został zminimalizowany przez moderatora na stronie
Trochę za późno na moją odpowiedź tutaj, ale...
ROW($2:$9)*100 — to pomnożenie numeru wiersza *100, więc jeśli jest w wierszu 5, teraz liczba wynosi 500
COLUMN($A:$C) - to jest dodawane do numeru wiersza*100, więc jeśli jest to wiersz 5 kolumna 2, to liczba wynosi 502.
7^8)), - to (chyba) ma mieć maksymalną wartość dla wcześniejszej instrukcji min.
„R0C00”) – formatuje tekst na podstawie numeru. W przykładzie mieliśmy 502, więc daje to R5C02 (wiersz 5, kolumna 02).

Jeśli masz dużo kolumn, ale niewiele wierszy, możesz zmienić to na WIERSZ(2 USD: 9 USD)*1000+KOLUMNA($A:$C),7^8)),"R0C000")
Ten komentarz został zminimalizowany przez moderatora na stronie
Dzięki za kod. Używam kodu VBA tej strony. Czy istnieje sposób na dodanie kodu sortującego po wyodrębnieniu unikalnych wartości, aby posortował go automatycznie?
Ten komentarz został zminimalizowany przez moderatora na stronie
czy możemy stworzyć funkcję uniqdata zamiast makra?
Ten komentarz został zminimalizowany przez moderatora na stronie
Cześć, İlhan, Jeśli lubisz funkcję zdefiniowaną przez użytkownika, aby utworzyć formułę rozwiązania tego problemu, poniższy kod może ci pomóc: Po wstawieniu kodu wybierz listę komórek, w których chcesz umieścić wyniki. Następnie wpisz tę formułę:=Unikalne(A1:C4)  na pasku formuły.Naciśnij Ctrl + Shift + Enter klucze razem. 


Function Uniques(ByVal inputRange As Range)
Dim inputArray As Variant
Dim myColl As New Collection
Dim xVal As Variant
Dim outArray() As Variant
On Error Resume Next
With inputRange
inputArray = Application.Intersect(.Cells, .Parent.UsedRange).Value
End With
On Error GoTo 0
On Error Resume Next
For Each xVal In inputArray
myColl.*** Item:=xVal, Key:=(CStr(xVal) & TypeName(xVal))
Next xVal
myColl.Remove "String"
On Error GoTo 0
ReDim outArray(1 To Application.Max(myColl.Count, Application.Caller.Cells.Count))
For xVal = 1 To UBound(outArray)
outArray(xVal) = vbNullString
Next xVal
For xVal = 1 To Application.Min(myColl.Count, Application.Caller.Cells.Count)
outArray(xVal) = myColl(xVal)
Next xVal
If Application.Caller.Columns.Count = 1 Then
Uniques = Application.Transpose(outArray)
Else
Uniques = outArray
End If
End Function
Ten komentarz został zminimalizowany przez moderatora na stronie
Czy żartować?
Ten komentarz został zminimalizowany przez moderatora na stronie
Formuła tablicowa na górze działa świetnie, gdy jest używana z danymi w tym samym arkuszu, jednak gdy próbuję użyć jej do odwoływania się do tych samych dokładnych danych z innego arkusza, formuła nic nie zwraca. Nie wiem dlaczego. Czy istnieje ograniczenie funkcji tablicowych, które uniemożliwia odwoływanie się do zakresów w innym arkuszu?

Dzięki za wszelkie wglądy, których możesz udzielić.
Ten komentarz został zminimalizowany przez moderatora na stronie
Witaj Erin,

Miło, że mogłem pomóc. Funkcja ADR.POŚR w tej formule jest bardziej skomplikowana w przypadku odwoływania się do danych w innych arkuszach. Nie zaleca się używania tej funkcji podczas odwoływania się do zakresów w różnych arkuszach.

Na przykład: Teraz dane znajdują się w Arkuszu1, chcę odwołać się do zawartości komórki C2 w Arkuszu1 w Arkuszu2. Najpierw w dowolnych dwóch komórkach w Arkuszu2, takich jak D1 i D2, wprowadź odpowiednio Arkusz1 i C2. W tym momencie wprowadź formułę w pustej komórce Arkusza2:
=ADR.POŚR("'"&D1&"'!"&D2), to może zostać zwrócona zawartość komórki C2 w Arkuszu1.

Jak widać, sprawia to, że sprawy stają się bardziej złożone. Mam nadzieję, że moje wyjaśnienie może pomóc. Miłego dnia.

Pozdrawiamy,
Mandy
Ten komentarz został zminimalizowany przez moderatora na stronie
Dzień dobry Panu! VBA zdziałało cuda, bardzo za to dziękuję! Zastanawiałem się, czy jeśli zmienię oryginalne dane, mogę automatycznie odświeżyć kolumnę z unikalnymi wartościami?
Znamionowy 5 z 5
Ten komentarz został zminimalizowany przez moderatora na stronie
Witaj Ioannisie,

Miło, że mogłem pomóc. Po zmianie oryginalnych danych VBA nie może automatycznie odświeżyć wyniku. Najprostszym sposobem, jaki przychodzi mi do głowy, jest naciśnięcie Ctrl + Alt + F9, aby odświeżyć wszystkie wyniki w arkuszach roboczych we wszystkich otwartych skoroszytach. Miłego dnia.

Pozdrawiamy,
Mandy
Ten komentarz został zminimalizowany przez moderatora na stronie
Dziękuję za ten wspaniały artykuł.

Dla osób korzystających z formuła tablicowa w programie Excel w języku innym niż angielski należy zwrócić szczególną uwagę na ciąg formatu tekstu: w twoim przykładzie: "R0C00".
Dla języka niemieckiego oznaczałoby to „Z0S00”. Jednak „S” jest znakiem specjalnym odnoszącym się do sekund do formatowania czasu. Ten znak musi zostać zmieniony, dlatego poprawny ciąg formatu dla niemieckiego programu Excel to „Z0\S00”.

Mam nadzieję, że to komuś pomoże w przyszłości :-)
Nie ma tu jeszcze żadnych komentarzy
Pokaż więcej
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