Note: The other languages of the website are Google-translated. Back to English
Zaloguj Się  \/ 
x
or
x
Zarejestruj się  \/ 
x

or

Jak pominąć i zwrócić wiele wartości bez duplikatów w programie Excel? 

Czasami możesz chcieć pominąć i zwrócić wiele dopasowanych wartości jednocześnie do jednej komórki. Ale jeśli w zwracanych komórkach znajdują się powtarzające się wartości, w jaki sposób można zignorować duplikaty i zachować unikalne wartości tylko podczas zwracania wszystkich pasujących wartości, jak pokazano na zrzucie ekranu w programie Excel?

doc zwraca wiele unikalnych wartości 1

Wyszukaj i zwróć wiele pasujących wartości bez duplikatów za pomocą funkcji zdefiniowanej przez użytkownika


Wyszukaj i zwróć wiele pasujących wartości bez duplikatów za pomocą funkcji zdefiniowanej przez użytkownika

Poniższy kod VBA może pomóc w zwróceniu wielu pasujących wartości bez duplikatów, wykonaj następujące czynności:

1. Przytrzymaj Alt + F11 klawisze, aby otworzyć Microsoft Visual Basic for Applications okno.

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

Kod VBA: przeglądaj i zwracaj wiele unikalnych dopasowanych wartości:

Function MultipleLookupNoRept(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer)
    Dim xDic As New Dictionary
    Dim xRows As Long
    Dim xStr As String
    Dim i As Long
    On Error Resume Next
    xRows = LookupRange.Rows.Count
    For i = 1 To xRows
        If LookupRange.Columns(1).Cells(i).Value = Lookupvalue Then
            xDic.Add LookupRange.Columns(ColumnNumber).Cells(i).Value, ""
        End If
    Next
    xStr = ""
    MultipleLookupNoRept = xStr
    If xDic.Count > 0 Then
        For i = 0 To xDic.Count - 1
            xStr = xStr & xDic.Keys(i) & ","
        Next
        MultipleLookupNoRept = Left(xStr, Len(xStr) - 1)
    End If
End Function

3. Po wstawieniu kodu kliknij Przybory > Referencje w otwartym Microsoft Visual Basic for Applications okno, a następnie w wyskoczył Referencje - VBAProject okno dialogowe, zaznacz Środowisko wykonawcze skryptów firmy Microsoft Microsoft opcja w Dostępne referencje pole listy, zobacz zrzut ekranu:

doc zwraca wiele unikalnych wartości 2

4. Następnie kliknij OK aby zamknąć okno dialogowe, zapisz i zamknij okno kodu, wróć do arkusza i wprowadź następującą formułę: =MultipleLookupNoRept(E2,A2:C17,3) do pustej komórki, w której chcesz wypisać wynik, naciśnij wchodzić aby uzyskać poprawny wynik, jakiego potrzebujesz. Zobacz zrzut ekranu:

doc zwraca wiele unikalnych wartości 3

Uwagi: W powyższym wzorze, E2 to kryteria, które chcesz pominąć, A2: C17 to zakres danych, którego chcesz użyć, liczba 3 to numer kolumny zawierającej zwrócone wartości.


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-2019 i 365. Obsługuje wszystkie języki. Łatwe wdrażanie w przedsiębiorstwie lub organizacji. Pełne funkcje 30-dniowy bezpłatny okres próbny. 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ęć myszą każdego dnia!
officetab dół
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Drew · 1 years ago
    This is great! How would I adapt this to not add null values to the dictionary? I've tried adding the bold below, but the final string is still returning with ,"", instances.


    xRows = LookupRange.Rows.Count
    For i = 1 To xRows
    If LookupRange.Columns(1).Cells(i).Value = Lookupvalue And Not IsEmpty(LookupRange.Columns(1).Cells(i).Value) Then
    xDic.Add LookupRange.Columns(ColumnNumber).Cells(i).Value, ""
    End If
    Next

    Thanks,
  • To post as a guest, your comment is unpublished.
    Jame · 1 years ago
    Hello , I did as u told and it great but it still havent solve one of my problem , what happen when u unique value in each month ? =MultipleLookupNoRept(E2,A2:C17,3) , i try to E2&1 for January but it not working
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hi, Jame,
      Could you give your problem as a screenshot here, so that i can understand your requires?
  • To post as a guest, your comment is unpublished.
    srikanth · 2 years ago
    hi,
    while the time of lot value multivlooks my worksheet got hang.is there any other ways to multivlookupwithoutrepeation????

    and also i used on new desktop also its getting hang only...

    my data value is around 10,000 rows
  • To post as a guest, your comment is unpublished.
    rasike001 · 2 years ago
    Hi

    I wanted to create a list in a table from this instead of all results in one cell. So I have used a formula similar below (what you have suggested)

    =LOOKUP(2, 1/((COUNTIF($E$1:E1, $B$2:$B$12)=0)*($D$2=$A$2:$A$12)), $B$2:$B$12)

    However, this is taking a long time to process from a large set of data.
    Is there any alternative method to process this faster?
    Thanks again
    Rasike
  • To post as a guest, your comment is unpublished.
    Imre · 2 years ago
    xStr = xStr & xDic.Keys(I) & "," to be this: xStr = xStr & xDic.Keys(I) & ", "

    Is there a way to replace "," with in-cell ALT+ENTER, so that the results will be in the same cell but on different lines? Do I need to introduce additional VBA module for that and combine them?

    Also, this code is quite slow when looping over huge tables. Anyone knows any faster solutions?
    • To post as a guest, your comment is unpublished.
      skyyang · 2 years ago
      Hi, Imre,
      To separate the result values by Alt + Enter keys, please apply the following User Defined Function:

      Function MultipleLookupNoRept(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer)
      Dim xDic As New Dictionary
      Dim xRows As Long
      Dim xStr As String
      Dim i As Long
      On Error Resume Next
      xRows = LookupRange.Rows.Count
      For i = 1 To xRows
      If LookupRange.Columns(1).Cells(i).Value = Lookupvalue Then
      xDic.Add LookupRange.Columns(ColumnNumber).Cells(i).Value, ""
      End If
      Next
      xStr = ""
      MultipleLookupNoRept = xStr
      If xDic.Count > 0 Then
      For i = 0 To xDic.Count - 1
      xStr = xStr & xDic.Keys(i) & Chr(10) + Chr(13)
      Next
      MultipleLookupNoRept = Left(xStr, Len(xStr) - 1)
      End If
      Debug.Print xStr
      End Function

      And then do with the above steps in this article, at last, after entering the formula, you should click Wrap Text under the Home tab.
  • To post as a guest, your comment is unpublished.
    Demetre · 3 years ago
    Is there a way to add a space in between the multiple values retrieved in the results without introducing a comma at the end of the list? For example your result above would show as: "Emily, James, Daisy, Gary" instead of like this: "Emily,James,Daisy,Gary"

    I tried to edit this portion of the VBA code: xStr = xStr & xDic.Keys(I) & "," to be this: xStr = xStr & xDic.Keys(I) & ", "

    That did add the space in between the values, but it also added a comma after the last value. "Emily, James, Daisy, Gary,"

    Is there a way to make it work with the space but without the extra comma after the last value?
    • To post as a guest, your comment is unpublished.
      skyyang · 3 years ago
      Hello, Demetre,
      Use the space to separate the values, you just need to change the vba code:
      from xStr = xStr & xDic.Keys(i) & "," to be this: xStr = xStr & xDic.Keys(i) & " "

      Please try it.
  • To post as a guest, your comment is unpublished.
    Tom · 3 years ago
    what if I wanted to create a list in a table from this instead of all results in one cell?
    • To post as a guest, your comment is unpublished.
      skyyang · 3 years ago
      Hello, Tom,
      If you want to extract the unique values in a list of cells instead of one cell, the following formula may help you:

      =LOOKUP(2, 1/((COUNTIF($E$1:E1, $B$2:$B$12)=0)*($D$2=$A$2:$A$12)), $B$2:$B$12)

      Please try it.
      • To post as a guest, your comment is unpublished.
        clifford · 1 months ago
        Hi skyyang what if you want the result as a column?
      • To post as a guest, your comment is unpublished.
        rasike001 · 2 years ago
        Hi Skyyang,

        Thank you very much for this formula.
        This works for me. However, it is taking a long time to process from a large set of data.
        Can we modify this formula to work this bit faster?
        Thanks again
        Rasike