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

Hogyan lehet dinamikusan kivonni az egyedi értékek listáját az Excel oszloptartományából?

Oszloptartomány esetén, amelynek értékei rendszeresen változnak, és mindig meg kell szereznie az összes egyedi értéket a tartománytól, függetlenül attól, hogy változott. Hogyan készítsünk dinamikus listát az egyedi értékekről? Ez a cikk megmutatja, hogyan kell kezelni.

Dinamikusan vonja ki az egyedi értékek listáját egy oszloptartományból képlettel
Dinamikusan vonja ki az egyedi értékek listáját egy oszloptartományból, VBA kóddal


Dinamikusan vonja ki az egyedi értékek listáját egy oszloptartományból képlettel

Amint az alábbi képernyőképen látható, dinamikusan ki kell vonnia az egyedi értékek listáját a B2: B9 tartományból. Kérjük, próbálja meg a következő tömbképletet.

1. Jelöljön ki egy üres cellát, például D2, írja be az alábbi képletet, és nyomja meg a Ctrl + műszak + belép gombokat egyszerre. (B2: B9 az oszlopadatok, amelyekkel ki akarja vonni az egyedi értékeket, D1 a fenti cella, ahol a képlete található)

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

2. Folyamatosan válassza a D2 cellát, majd húzza lefelé a Kitöltő fogantyút, hogy az összes egyedi értéket megkapja a megadott tartományból.

Most a B2: B9 oszloptartomány összes egyedi értékét kibontjuk. Ha ezen tartomány értékei megváltoznak, az egyedi értéklista azonnal dinamikusan megváltozik.

Könnyen kiválaszthatja és kiemelheti a tartomány összes egyedi értékét az Excelben:

A Válassza az Ismétlődő és egyedi cellák lehetőséget hasznossága Kutools az Excel számára segítségével könnyedén kiválaszthatja és kiemelheti az összes egyedi értéket (beleértve az első duplikátumokat) vagy a csak egyszer megjelenő egyedi értékeket, valamint szükség szerint megismételheti az értékeket, ahogy az alábbi képernyőképen látható.
Töltse le a Kutools for Excel alkalmazást most! (30-napos ingyenes túra)


Dinamikusan vonja ki az egyedi értékek listáját egy oszloptartományból, VBA kóddal

Az egyedi értékek listáját dinamikusan kivonhatja az oszloptartományból a következő VBA kóddal.

1. nyomja meg más + F11 gombok egyszerre a Microsoft Visual Basic for Applications ablak.

2. Ban,-ben Microsoft Visual Basic for Applications ablakban kattintson betétlap > Modulok. Ezután másolja és illessze be az alábbi VBA kódot a Modulok ablak.

VBA kód: Kivonat egy egyedi értékek listáját egy tartományból

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

Megjegyzések: A kódban D2 az a cella, ahol az egyedi értéklistát megtalálja. Szükség szerint megváltoztathatja.

3. Menjen vissza a munkalapra, kattintson betétlap > Alakzatok > téglalap. Lásd a képernyőképet:

4. Rajzoljon egy téglalapot a munkalapjára, majd írjon be néhány szót, amelyet megjelenítenie kell rajta. Ezután kattintson a jobb gombbal, és válassza ki Hozzárendelje a Makrót a jobb egérgombbal kattintva. Ban,-ben Hozzárendelje a Makrót párbeszédpanelen válassza a ikont CreateUniqueList a Makró neve jelölőnégyzetet, majd kattintson a gombra OK gomb. Lásd a képernyőképet:

5. Most kattintson a téglalap gombra, a Kutools az Excel számára megjelenik a párbeszédpanel, válassza ki azt a tartományt, amely tartalmazza a kibontandó egyedi értékeket, majd kattintson a gombra OK gombot.

Mostantól megismételheti a fenti 5. lépést az egyedi értéklista automatikus frissítéséhez.


Kapcsolódó cikkek:


A legjobb irodai termelékenységi eszközök

A Kutools for Excel megoldja a legtöbb problémát, és 80% -kal növeli a termelékenységet

  • újrafelhasználás: Gyorsan helyezze be összetett képletek, diagramok és bármi, amit korábban használt; Cellák titkosítása jelszóval; Levelezőlista létrehozása és e-maileket küldeni ...
  • Szuper Formula Bár (könnyedén szerkeszthet több szöveget és képletet); Olvasás elrendezés (könnyen olvasható és szerkeszthető nagyszámú cella); Beillesztés a Szűrt tartományba...
  • Cellák / sorok / oszlopok egyesítése az adatok elvesztése nélkül; Osztott cellák tartalma; Kombinálja a duplikált sorokat / oszlopokat... megakadályozza az ismétlődő cellákat; Hasonlítsa össze a tartományokat...
  • Válassza a Másolat vagy az Egyedi lehetőséget Sorok; Válassza az Üres sorok lehetőséget (az összes cella üres); Super Find és Fuzzy Find sok munkafüzetben; Véletlenszerű kiválasztás ...
  • Pontos másolás Több cella a képletreferencia megváltoztatása nélkül; Automatikus referenciák létrehozása több lapra; Helyezze be a golyókat, Jelölőnégyzetek és még sok más ...
  • Kivonat szöveg, Szöveg hozzáadása, Eltávolítás pozíció szerint, Hely eltávolítása; Hozz létre és nyomtasson személyhívó részösszegeket; Konvertálás a cellatartalom és a megjegyzések között...
  • Szuper szűrő (mentse el és alkalmazza a szűrősémákat más lapokra); Haladó rendezés hónap / hét / nap, gyakoriság és egyebek szerint; Speciális szűrő félkövér, dőlt betűvel ...
  • Kombinálja a munkafüzeteket és a munkalapokat; Táblázatok egyesítése kulcsoszlopok alapján; Az adatok felosztása több lapra; Kötegelt konvertálás xls, xlsx és PDF...
  • Több mint 300 hatékony funkció. Támogatja az Office / Excel 2007-2021 és 365 verziókat. Minden nyelvet támogat. Könnyű üzembe helyezés vállalatában vagy szervezetében. Teljes funkciók 30 napos ingyenes próbaverzió. 60 napos pénzvisszafizetési garancia.
kte lap 201905

Az Office fül a füles felületet hozza az Office-ba, és sokkal könnyebbé teszi a munkáját

  • Füles szerkesztés és olvasás engedélyezése Wordben, Excelben és PowerPointban, Publisher, Access, Visio és Project.
  • Több dokumentum megnyitása és létrehozása ugyanazon ablak új lapjain, mint új ablakokban.
  • 50% -kal növeli a termelékenységet, és naponta több száz kattintással csökkenti az egér kattintását!
officetab alja
A megjegyzések rendezése szerint
Hozzászólások (35)
Még nincs értékelés. Legyen Ön az első, aki értékel!
A weboldal moderátora ezt a megjegyzést minimalizálta
Köszönöm a bemutatót. A képletmódszer használatával hogyan módosítaná a képletet, ha kategóriaminősítőt szeretne hozzáadni? Tegyük fel, hogy a C oszlopban megkülönböztetjük, hogy a tétel gyümölcs vagy zöldség. Hogyan változtatná meg a kódot úgy, hogy csak az egyedi gyümölcsöket rendezze, és kizárja a zöldségeket? Megpróbáltam a COUNTIF-et COUNTIFS-re cserélni, a (LIST RANGE"CATEGORY") második countifs-feltételét használva, de üresen tér vissza. Ki kell bővítenem a tömbömet, és be kell építenem a VLOOKUP-ot?
A weboldal moderátora ezt a megjegyzést minimalizálta
Rendesen értek az excelhez, de tényleg azon töprengek, hogyan és miért működik a fenti képlet (amire használom, de meg kell értenem, hogy miért). Néha kissé összezavarodok a tömbök használatakor, így bármilyen idióta kifejezéssel történő magyarázat rendkívül hasznos lenne. Üdvözlettel
A weboldal moderátora ezt a megjegyzést minimalizálta
Ez a képlet elavult és nem működik. Szó szerint csak beállítottam ezt az excel lapot, hogy megnézzem, sikerül-e működésre bírnom ezt a képletet, de nem.
A weboldal moderátora ezt a megjegyzést minimalizálta
Üdv haver,
Melyik Office verziót használod?
A weboldal moderátora ezt a megjegyzést minimalizálta
{=INDEX($Q$3:$Q$263,MATCH(0,COUNTIF(V$2:V2,$Q$3:$Q$263),0))} – azt találta, hogy ez egy másik webhelyről működik...

Használja a Ctrl+Shift+Enter billentyűkombinációt a tömbfüggvény (kapcsos zárójelek) eléréséhez. Húzza a másolás-beillesztés képleteket addig, amíg a #NA meg nem jelenik. Az adatkészletem a Q oszlopban volt, összehasonlították, hogy létezik-e az egyedi listában az V. oszlopban, amely folyamatosan ugyanazon az oszlopon húzódik.
A weboldal moderátora ezt a megjegyzést minimalizálta
Jó nap.
Kérjük, sorolja fel a Q oszlop összes egyedi értékét az abobv képlettel, majd használja az =IF(D2=V1"Egyezés","Nincs egyezés") képletét annak összehasonlításához, hogy a Q oszlopban lévő egyedi értékeket az ugyanabban a sorban lévő V oszlophoz viszonyítja-e. .
A weboldal moderátora ezt a megjegyzést minimalizálta
Sziasztok és köszönöm a segítséget.

Pontosan erre a funkcióra van szükségem, de az „egyedi értékek” listámnak sorok helyett oszlopokra kell kiterjednie, így a sorokkal lefelé bővülő lista nem fog működni.

Hogyan módosíthatom ezt a képletet annak érdekében, hogy az "egyedi értékek" listája kibővüljön, amikor áthúzom az oszlopokon?

Offset()?
Transpone()?
Indirect() abszolút hivatkozásokból álló sztringgel, amely sor helyett az oszlopra való hivatkozással van összefűzve?


Köszönöm mégegyszer!
A weboldal moderátora ezt a megjegyzést minimalizálta
Kedves Ryan!
Ez a képlet =IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$2:D2, $B$2:$B$9), 0)),"") + Ctrl + Shift + Enter segít a probléma megoldásában.
Lásd az alábbi képernyőképet:
A weboldal moderátora ezt a megjegyzést minimalizálta
Ezenkívül, bármilyen okból, az eredeti képlet a következőket tartalmazza:
=IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$1:D1, $B$2:$B$9), 0)),"")

"körkörös referencia" figyelmeztetést ad vissza, és nem számol.
A weboldal moderátora ezt a megjegyzést minimalizálta
Kedves Ryan!
Melyik Office verziót használod? A képlet jól működik az Office 2016-ban és 2013-ban.
A weboldal moderátora ezt a megjegyzést minimalizálta
Korábban is előfordult már ilyen – a javításom az volt, hogy a képletet a D1 cellába írtam be (ez megegyezik az általam használt munkalapon). Amelyik cellának felel meg a $D:$1, azt az alábbi cellába kell beírnia - D2. Elnézést, ha nem ezért kaptad a hibát
A weboldal moderátora ezt a megjegyzést minimalizálta
Van valami tippje a VBA opciónak az Excel 2016 for macOS-hez való működéséhez? Követtem a lépéseket; viszont amikor futtatom a makrót, egyáltalán nem történik semmi. Kösz!
A weboldal moderátora ezt a megjegyzést minimalizálta
Daer Jones,
Kérjük, próbálja ki az alábbi VBA-kódot, és tudassa velem, hogy működik-e az Ön számára. Köszönöm!

Sub CreateUniqueList()
Dim xRng As Range
Dim xLastRow As Long
Dim xLastRow2 As Long
Dim I As Integer
' Hiba esetén Folytatás a következővel
Set xRng = Application.InputBox("Kérjük, válasszon tartományt:", "Kutools for Excel", Selection.Address, , , , , 8)
Ha az xRng semmi, akkor lépjen ki a Subból
On Error Resume Next
xRng.Copy Range("D2")
xLastRow = xRng.Rows.Count + 1
ActiveSheet.Range("D2:D" & xLastRow).RemoveDuplicates Columns:=1, Header:=xlNo
xLastRow2 = Cellák(Sorok.Szám, "B").End(xlUp).Sor
Ha I = 1 - xLastRow2
Ha ActiveSheet.Range("D2:D" & xLastRow2).Cells(I).Value = "" Akkor
ActiveSheet.Range("D2:D" & xLastRow2).Cells(I).Törlés
Ha véget
Következő
End Sub
A weboldal moderátora ezt a megjegyzést minimalizálta
Szia Crystal,
Megpróbálom az egyedi értéklista VB verzióját használni, és problémába ütközök.
Az a tartomány, amelyből egyedi értékoszlopot szeretnék létrehozni, az összes képlet, amely különböző lapokra hivatkozik.
Hogyan lehet elérni, hogy a képlet helyett átvigyük az értéket?
A weboldal moderátora ezt a megjegyzést minimalizálta
Kedves Mike!
Kérjük, konvertálja a képlethivatkozásokat abszolútra, majd alkalmazza a VB-szkriptet.
A weboldal moderátora ezt a megjegyzést minimalizálta
Ugyanez a problémám, kivéve, hogy a képletem oszlopnevekre hivatkozik, és nem konvertálható abszolúttá.
Hogyan módosíthatom a vba-t, hogy az értékeket illessze be, és ne a képletet?
A weboldal moderátora ezt a megjegyzést minimalizálta
Hogyan adna hozzá több feltételt, például ha csak akkor szeretne hozzáadni a dinamikus listához, ha a dátum csak szeptember 9-e?

Megpróbálom az "&"-t a MATCH képletben, de nem működik.

Például a te példád alapján:
=IFERROR(INDEX($B$2:$B$9, MATCH(0 & B4,COUNTIF($D$1:D1, $B$2:$B$9) & $A$2:$A$9, 0)),"" )
Ez hibát okoz, vagy ismétlődéseket hoz létre.

Alternatív megoldásként azt olvastam, hogy a "+" működhet, bár nem tudom működésre bírni. Vagy a SMALL használatával.

Ötletek?
A weboldal moderátora ezt a megjegyzést minimalizálta
Kedves Zac!
Sajnos ebben nem tudok segíteni, kérdését felteheti fórumunkba: https://www.extendoffice.com/forum.html hogy további Excel-támogatást kaphasson szakemberünktől.
A weboldal moderátora ezt a megjegyzést minimalizálta
Hogyan adnál hozzá egy második változót? Például azt szeretném, hogy az összes egyedi elem egy oszlopban legyen, amelyek egy másik oszlopban is hasonló értékkel rendelkeznek. Példájában képzeljen el egy „Osztály” nevű 3. oszlopot, amelyben olyan értékek szerepelnek, mint a termék, hús stb. Tisztában vagyok vele, hogy ezek mind Termelés, de remélhetőleg érted, amit mondok. Módosítaná a CountIF képletet COUNTIFS-re, vagy más módon módosítaná?
A weboldal moderátora ezt a megjegyzést minimalizálta
Szia Matt
Kérjük, próbálja ki ezt a képletet: =IF(ISNA(VLOOKUP(A2,$C$2:$C$13,1,FALSE)),"Igen","").
Tételezzük fel, hogy a két összehasonlított lista az A oszlop és a C oszlop, ha az egyedi értékek csak az A oszlopban maradnak, de a C oszlopban nem, akkor a B oszlopban az Igen felirat jelenik meg; míg ha nem ad vissza semmit a B oszlopban, az azt jelenti, hogy a megfelelő érték az A és a C oszlopban is marad.
A weboldal moderátora ezt a megjegyzést minimalizálta
Köszönöm a választ.. de forró kihúzni ezt az egyedi értéket, ha IGEN jelenik meg.. Tudna tanácsot adni nekem a képletben, hogy az egyedi értéket más oszlopokba húzzam.
A weboldal moderátora ezt a megjegyzést minimalizálta
Ha ezt csinálom ezer soros excel munkalapon az Excel legújabb verzióján Macen, soha nem tér vissza. Az első sor működik, de amikor lemásolom, az excel számítási módba lép, amely már több mint két órája nem ad vissza értékeket.

Van valami ötleted, hogyan kell ezt megtenni nagy listáknál (legfeljebb 2k sor), amelyek 50 vagy 60 egyedi értéket adnak vissza?

Ezt kigúnyoltam a "Számok" alkalmazásban, és ott tökéletesen működik, mindössze pár percet vesz igénybe a kiszámítása. Csak olyan sokáig tart az Excelben, hogy kíváncsi vagyok, be fog-e készülni valaha. Azt tervezem, hogy egyik napról a másikra hagyom "futni", hogy meglássam, mi fog történni.
A weboldal moderátora ezt a megjegyzést minimalizálta
Ellenőrizze a számítási beállításokat. Automatikusra kell állítani. Fájl > Beállítások > Képletek > Számítási beállítások > Munkafüzet számítása (automatikus kijelölés)
A weboldal moderátora ezt a megjegyzést minimalizálta
Megpróbálom lejjebb húzni a képletet a tényleges adataim mögé, hogy különböző méretű adatkészleteket vigyek be, és ne kelljen semmit módosítanom. A tényleges adataim vége utáni utolsó sor azonban mindig „0”-t ad vissza. Az egyedi értékeket valami máshoz használom egy szomszédos oszlopban, és a 0 az utolsó érték ismétlődését okozza (amikor törlöm a 0-t, az érték már nem ismétlődik). Valami ötlet, hogyan lehetne ezt orvosolni? Én is az Office 365 Business-t használom
A weboldal moderátora ezt a megjegyzést minimalizálta
Szia! Köszönjük a segítséget.
Most hogyan tudom az értékeimet ábécé szerint rendezni? (Nem akarom használni a szűrőt a főtáblámon)
COUNTIFS-t használjam a COUNTIF helyett?
Kérem, segítsen
A weboldal moderátora ezt a megjegyzést minimalizálta
Szia Alexis,
Sajnos a kivont értéket nem lehet egyszerre ábécé sorrendbe rendezni a képlettel. Köszönjük észrevételét.
A weboldal moderátora ezt a megjegyzést minimalizálta
Ezt az =IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$1:D1, $B$2:$B$9), 0)),"") képletet használom, ami nagyszerű egy oszlopra, de az adataim több oszlop és sor között oszlanak el. Szerkeszthetem a képletet úgy, hogy az a teljes területet tartalmazza? Az adataim AC4-től AR60-ig élnek...
A weboldal moderátora ezt a megjegyzést minimalizálta
Kipróbálom a VBA kódot és a képletet. A VBA kód nagyon jól működik, de nem tudok fájlt tartani makróval. De a probléma az, hogy nem tudom működőképessé tenni a képletet. Volt valakinek ötlete? Köszönöm
A weboldal moderátora ezt a megjegyzést minimalizálta
Szia Charlotte,
Köszönjük észrevételét. A makrót tartalmazó fájlt későbbi használatra is megőrizheti, ha a munkafüzetet Excel-makró-kompatibilis munkafüzetként menti.
A képletprobléma kapcsán kérem, adjon képernyőképet az adatairól? Köszönjük észrevételét.
A weboldal moderátora ezt a megjegyzést minimalizálta
köszönöm szépen
A weboldal moderátora ezt a megjegyzést minimalizálta
hogyan lehet a vba kódot működni egy olyan tartományban, ahol más képletet használtak? a BI oszlopban van egy képlet, amely a D és E oszlopokra hivatkozik.
Ha alkalmazom a kódot az L oszlopra (mondjuk), (nyilván a kód celláit megfelelően módosítva) a makró az M és N oszlopokra alkalmazott képletet adja vissza... Akkor működik, de nem úgy, ahogy én szeretném! Hogyan lehet megtartani az értékeket a B oszlopban? Köszönjük
A weboldal moderátora ezt a megjegyzést minimalizálta
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 met unieke waarden. Het kan zijn dat je wat extra 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 is wat összetettebb.
A weboldal moderátora ezt a megjegyzést minimalizálta
Pontosan ugyanezt szeretném megtenni, kivéve két külön oszloptartomány (B2:B9) és (D2:D9) használatával, lehetséges ez?
A weboldal moderátora ezt a megjegyzést minimalizálta
Hi Anthony,
Az eredményeket az eredeti adatokkal azonos oszlopba helyezheti. Ilyen például ebben az esetben a B oszlop.
De hivatkoznia kell a képlet eredmény cellájának felső cellájára az alábbiak szerint.
=IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($B$11:B11, $B$2:$B$9), 0)),"") + Ctrl + Shift + Enter
A weboldal moderátora ezt a megjegyzést minimalizálta
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 define un criterio de filtrado en este caso en la fila d56 el mismo titulo de la list 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

Tartomány("A59:A239").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Tartomány _
("D56:D57"), CopyToRange:=Tartomány("D59"), Unique:=True
Még senki sem írt megjegyzést
Hagyja meg észrevételeit
Feladás vendégként
×
Értékeld ezt a bejegyzést:
0   Karakterek
Javasolt helyek

Kövess minket

Copyright © 2009 - www.extendoffice.com. | Minden jog fenntartva. Powered by ExtendOffice. | Oldaltérkép
A Microsoft és az Office logó a Microsoft Corporation védjegyei vagy bejegyzett védjegyei az Egyesült Államokban és / vagy más országokban.
Sectigo SSL védi