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

Cum se extrage dinamic o listă de valori unice dintr-un interval de coloane din Excel?

Pentru un interval de coloane ale cărui valori se modifică în mod regulat și trebuie întotdeauna să obțineți toate valorile unice din interval, indiferent de modul în care s-au schimbat. Cum se face o listă dinamică a valorilor unice? Acest articol vă va arăta cum să faceți față acestuia.

Extrageți dinamic o listă de valori unice dintr-un interval de coloane cu formulă
Extrageți dinamic o listă de valori unice dintr-un interval de coloane cu cod VBA


Extrageți dinamic o listă de valori unice dintr-un interval de coloane cu formulă

După cum se arată mai jos, trebuie să extrageți dinamic o listă de valori unice din intervalul B2: B9. Vă rugăm să încercați următoarea formulă matrice.

1. Selectați o celulă goală, cum ar fi D2, introduceți formula de mai jos în ea și apăsați pe Ctrl + Schimba + Intrați tastele simultan. (B2: B9 este datele coloanei pe care doriți să extrageți valorile unice, D1 este celula de mai sus unde se află formula dvs.)

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

2. Continuați să selectați celula D2, apoi trageți mânerul de umplere în jos pentru a obține toate valorile unice din intervalul specificat.

Acum sunt extrase toate valorile unice din gama de coloane B2: B9. Când valorile din acest interval s-au modificat, lista de valori unice va fi modificată dinamic imediat.

Selectați și evidențiați cu ușurință toate valorile unice dintr-un interval în Excel:

Selectați celule duplicate și unice utilitatea Kutools pentru Excel vă poate ajuta să selectați și să evidențiați cu ușurință toate valorile unice (includeți primele duplicate) sau valorile unice care apar o singură dată, precum și valorile duplicate, după cum aveți nevoie, după cum se arată în imaginea de mai jos.
Descărcați Kutools pentru Excel acum! (30- traseu liber de o zi)


Extrageți dinamic o listă de valori unice dintr-un interval de coloane cu cod VBA

De asemenea, puteți extrage dinamic o listă de valori unice dintr-un interval de coloane cu următorul cod VBA.

1. presa Alt + F11 tastele simultan pentru a deschide Microsoft Visual Basic pentru aplicații fereastră.

2. În Microsoft Visual Basic pentru aplicații fereastră, faceți clic pe Insera > Module. Apoi copiați și lipiți codul VBA de mai jos în Module fereastră.

Cod VBA: extrageți o listă de valori unice dintr-un interval

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

notițe: În cod, D2 este celula în care veți localiza lista de valori unice. Îl puteți schimba după cum aveți nevoie.

3. Reveniți la foaia de lucru, faceți clic pe Insera > Forme > Dreptunghi. Vedeți captura de ecran:

4. Desenați un dreptunghi în foaia de lucru, apoi introduceți câteva cuvinte pe care trebuie să le afișați. Apoi faceți clic dreapta pe el și selectați Atribuiți macro din meniul cu clic dreapta. În Atribuiți macro caseta de dialog, selectați CreateUniqueList în Numele macro , apoi faceți clic pe OK buton. Vedeți captura de ecran:

5. Acum faceți clic pe butonul dreptunghi, a Kutools pentru Excel apare fereastra de dialog, selectați intervalul conține valori unice pe care trebuie să le extrageți, apoi faceți clic pe OK butonul.

De acum înainte, puteți repeta pasul 5 de mai sus pentru a actualiza automat lista de valori unică.


Articole pe aceeași temă:


Cele mai bune instrumente de productivitate Office

Kutools pentru Excel vă rezolvă majoritatea problemelor și vă crește productivitatea cu 80%

  • reutilizarea: Introduceți rapid formule complexe, diagrame și orice ai folosit anterior; Criptați celulele cu parola; Creați o listă de corespondență și trimiteți e-mailuri ...
  • Super Formula Bar (editați cu ușurință mai multe linii de text și formulă); Layout de citire (citiți și editați cu ușurință un număr mare de celule); Lipiți la interval filtrat...
  • Merge celule / rânduri / coloane fără a pierde date; Conținut de celule divizate; Combinați rânduri / coloane duplicate... Prevenirea celulelor duplicate; Comparați gamele...
  • Selectați Duplicat sau Unic Rânduri; Selectați Rânduri goale (toate celulele sunt goale); Super Find și Fuzzy Find în multe cărți de lucru; Selectare aleatorie ...
  • Copie exactă Mai multe celule fără modificarea referinței formulelor; Creați automat referințe la foi multiple; Introduceți gloanțe, Casete de selectare și multe altele ...
  • Extrageți textul, Adăugați text, eliminați după poziție, Eliminați spațiul; Creați și imprimați subtotaluri de paginare; Convertiți conținutul dintre celule și comentarii...
  • Super Filtru (salvați și aplicați scheme de filtrare altor foi); Sortare avansată după lună / săptămână / zi, frecvență și multe altele; Filtru special cu bold, italic ...
  • Combinați cărți de lucru și foi de lucru; Merge Tables pe baza coloanelor cheie; Împărțiți datele în mai multe foi; Conversia în loturi xls, xlsx și PDF...
  • Peste 300 de funcții puternice. Suportă Office / Excel 2007-2021 și 365. Acceptă toate limbile. Implementare ușoară în întreprinderea sau organizația dvs. Funcții complete Probă gratuită de 30 de zile. Garanție de returnare a banilor de 60 de zile.
fila kte 201905

Fila Office aduce interfața cu file în Office și vă face munca mult mai ușoară

  • Activați editarea și citirea cu file în Word, Excel, PowerPoint, Publisher, Access, Visio și Project.
  • Deschideți și creați mai multe documente în filele noi ale aceleiași ferestre, mai degrabă decât în ​​ferestrele noi.
  • Vă crește productivitatea cu 50% și reduce sute de clicuri de mouse pentru dvs. în fiecare zi!
fundul officetab
Comentarii (35)
Încă nu există evaluări. Fii primul care evaluează!
Acest comentariu a fost redus la minimum de moderatorul de pe site
Mulțumesc pentru tutorial. Folosind metoda formulei, cum ați modifica formula dacă ați dori să adăugați un calificativ de categorie? Spuneți în coloana C că distingeți dacă articolul este un fruct sau o legumă. Cum ați schimba codul pentru a sorta doar fructele unice și pentru a exclude legumele? Am încercat să înlocuiesc COUNTIF cu COUNTIFS, folosind al doilea criteriu countifs din (LIST RANGE,"CATEGORY"), dar returnează necompletat. Ar trebui să-mi extind matricea și să încorporez VLOOKUP?
Acest comentariu a fost redus la minimum de moderatorul de pe site
Sunt decent la Excel, dar chiar încerc să-mi înțeleg cum și de ce funcționează formula de mai sus (funcționează pentru ce o folosesc, dar trebuie să înțeleg de ce). Sunt puțin confuz folosind matrice uneori, așa că orice explicație în termeni idioți ar fi extrem de utilă. Salutări
Acest comentariu a fost redus la minimum de moderatorul de pe site
Această formulă este depășită și nu funcționează. Literal, am configurat această foaie Excel exactă pentru a vedea dacă aș putea face ca această formulă să funcționeze și nu funcționează.
Acest comentariu a fost redus la minimum de moderatorul de pe site
Hei tipule,
Ce versiune de Office folosești?
Acest comentariu a fost redus la minimum de moderatorul de pe site
{=INDEX($Q$3:$Q$263,MATCH(0,COUNTIF(V$2:V2,$Q$3:$Q$263),0))} - am găsit că aceasta funcționează de pe alt site...

Folosiți Ctrl+Shift+Enter pentru a obține funcția matrice (acolade). Trageți copy-paste formulele până când apare #NA. Setul meu de date a fost în coloana-Q, a fost comparat pentru a vedea dacă a existat în lista unicului din coloana-V, care se întinde continuu de-a lungul aceleiași coloane.
Acest comentariu a fost redus la minimum de moderatorul de pe site
O zi buna.
Vă rugăm să enumerați toate valorile unice ale coloanei Q cu formula de mai sus și apoi să utilizați formula sa =IF(D2=V1,"Potrivire","Fără potrivire") pentru a compara dacă unicurile din coloana Q se compară cu coloana V din același rând .
Acest comentariu a fost redus la minimum de moderatorul de pe site
Bună, și mulțumesc pentru ajutor.

Am nevoie exact de această funcționalitate, dar lista mea de „valori unice” trebuie să se extindă pe coloane în loc de rânduri, astfel încât lista extinsă în jos pe rânduri nu va funcționa pentru mine.

Cum pot modifica această formulă pentru a face lista de „valori unice” să se extindă pe măsură ce o trag de-a lungul coloanelor?

Decalaj()?
Transpune()?
Indirect() cu un șir de referințe absolute concatenate cu o referință la coloană în loc de rând?


Multumesc din nou!
Acest comentariu a fost redus la minimum de moderatorul de pe site
Dragă Ryan,
Această formulă =IFEROARE(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$2:D2, $B$2:$B$9), 0)),"") + Ctrl + Shift + Enter poate te ajuta sa rezolvi problema.
Vezi mai jos captura de ecran:
Acest comentariu a fost redus la minimum de moderatorul de pe site
De asemenea, indiferent de motiv, formula originală prevedea:
=IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$1:D1, $B$2:$B$9), 0)),"")

returnează un avertisment de „referință circulară” și nu va calcula..
Acest comentariu a fost redus la minimum de moderatorul de pe site
Dragă Ryan,
Ce versiune de Office folosești? Formula funcționează bine în Office 2016 și 2013.
Acest comentariu a fost redus la minimum de moderatorul de pe site
Mi s-a întâmplat asta înainte - soluția mea a fost că introduceam formula în celula D1 (echivalentul în foaia de lucru pe care o foloseam). Indiferent de celula căreia îi corespunde $D:$1, trebuie să o introduceți în celula de mai jos - D2. Scuze dacă nu din acest motiv ați primit eroarea
Acest comentariu a fost redus la minimum de moderatorul de pe site
Sfaturi despre cum să funcționeze opțiunea VBA cu Excel 2016 pentru macOS? Am urmat pașii; cu toate acestea, când rulez macro-ul, nu se întâmplă absolut nimic. Mulțumiri!
Acest comentariu a fost redus la minimum de moderatorul de pe site
Daer Jones,
Vă rugăm să încercați codul VBA de mai jos și să-mi spuneți dacă funcționează pentru dvs. Mulțumesc!

Sub CreateUniqueList()
Dim xRng As Range
Dim xLastRow As Long
Dim xLastRow2 As Long
Dim I ca întreg
' La eroare Reluați următoarea
Set xRng = Application.InputBox(„Vă rugăm să selectați intervalul:”, „Kutools pentru Excel”, Selection.Address, , , , , 8)
Dacă xRng este Nimic, Ieșiți din Sub
La data de eroare CV următoare
xRng.Copy Range ("D2")
xLastRow = xRng.Rows.Count + 1
ActiveSheet.Range(„D2:D” și xLastRow).RemoveDuplicates Columns:=1, Header:=xlNo
xLastRow2 = Cells(Rows.Count, "B").End(xlUp).Row
Pentru I = 1 To xLastRow2
Dacă ActiveSheet.Range("D2:D" & xLastRow2).Cells(I).Value = "" Atunci
ActiveSheet.Range(„D2:D” și xLastRow2).Cells(I).Delete
Final, dacă
Pagina Următoare →
End Sub
Acest comentariu a fost redus la minimum de moderatorul de pe site
Salut Crystal,
Încerc să folosesc versiunea VB a listei de valori unice și întâmpin o problemă.
Intervalul din care vreau să creez o coloană de valori unică este toate formulele care se referă la diferite file.
Cum se transferă valoarea în locul formulei?
Acest comentariu a fost redus la minimum de moderatorul de pe site
Dragă Mike,
Vă rugăm să convertiți referințele formulei în absolute și apoi să aplicați scriptul VB.
Acest comentariu a fost redus la minimum de moderatorul de pe site
Am aceeași problemă, cu excepția faptului că formula mea se referă la nume de coloane și nu poate converti în absolut.
Cum schimb vba pentru a lipi valorile și nu formula?
Acest comentariu a fost redus la minimum de moderatorul de pe site
Cum ați adăuga mai multe criterii, cum ar fi dacă ați dori să adăugați doar la lista dinamică dacă data era doar 9/12?

Încerc „&” în formula MATCH, dar nu funcționează.

De exemplu, pe baza exemplului dvs.:
=IFEROARE(INDEX($B$2:$B$9, MATCH(0 & B4,COUNTIF($D$1:D1, $B$2:$B$9)) & $A$2:$A$9, 0)),"" )
Acest lucru aruncă o eroare sau creează duplicate.

Alternativ, am citit că „+” ar putea funcționa, deși nu pot să funcționeze. Sau folosind SMALL.

Idei?
Acest comentariu a fost redus la minimum de moderatorul de pe site
Dragă Zac,
Îmi pare rău că nu vă pot ajuta cu asta, puteți posta întrebarea dvs. pe forumul nostru: https://www.extendoffice.com/forum.html pentru a obține mai multe suporturi Excel de la profesioniștii noștri.
Acest comentariu a fost redus la minimum de moderatorul de pe site
Cum ați adăuga o a doua variabilă? De exemplu, vreau toate elementele unice dintr-o coloană care au, de asemenea, o valoare similară într-o altă coloană. În exemplul dvs., imaginați-vă o a treia coloană intitulată „Departament” care ar avea valori precum produs, carne etc. Îmi dau seama că toate sunt Produse, dar sper să înțelegeți punctul meu de vedere. Ați modifica formula CountIF într-un COUNTIFS sau o modificați în alt mod?
Acest comentariu a fost redus la minimum de moderatorul de pe site
Buna Matt
Vă rugăm să încercați această formulă =IF(ISNA(VLOOKUP(A2,$C$2:$C$13,1,FALSE)),„Da”,”).
Presupunând că cele două liste comparate sunt coloana A și coloana C, dacă valorile unice rămân doar în coloana A, dar nu în coloana C, se va afișa Da în coloana B; în timp ce dacă nu returnează nimic în coloana B, înseamnă că valoarea corespunzătoare rămâne atât în ​​coloana A, cât și în coloana C.
Acest comentariu a fost redus la minimum de moderatorul de pe site
Mulțumesc pentru răspuns.. dar fierbinte să scot acea valoare unică dacă este afișată DA.. ați putea vă rog să-mi sfătuiți formula pentru a extrage valoarea unică în altă coloană.
Acest comentariu a fost redus la minimum de moderatorul de pe site
Dacă fac asta pentru o foaie Excel de o mie de rânduri pe cea mai recentă versiune de Excel pe un Mac, nu se întoarce niciodată. Primul rând funcționează, dar când dublez, Excel intră într-un mod de calcul care nu a returnat valori de peste două ore.

Aveți vreo idee despre cum să faceți acest lucru pentru liste mari (până la 2k de rânduri) care vor returna 50 sau 60 de valori unice?

Am batut joc de asta în aplicația „Numere” și funcționează perfect acolo, luând doar câteva minute pentru a calcula. Doar că durează atât de mult în Excel încât mă întreb dacă se va finaliza vreodată. Plănuiesc să-l las să „funcționeze” peste noapte pentru a vedea ce se va întâmpla.
Acest comentariu a fost redus la minimum de moderatorul de pe site
Verificați opțiunile de calcul. Trebuie setat pe automat. Fișier > Opțiuni > Formule > Opțiuni de calcul > Calcul registru de lucru (Selectare automată)
Acest comentariu a fost redus la minimum de moderatorul de pe site
Încerc să trag formula în jos dincolo de datele mele reale, astfel încât să pot introduce seturi de date de diferite dimensiuni și să nu fiu nevoit să ajustez nimic. Cu toate acestea, ultimul rând după ce datele mele reale se termină returnează întotdeauna un „0”. Folosesc valorile unice pentru altceva într-o coloană adiacentă, iar 0 face ca ultima valoare să se repete (când șterg 0, valoarea nu se mai repetă). Aveți idee cum să remediați asta? De asemenea, folosesc Office 365 Business
Acest comentariu a fost redus la minimum de moderatorul de pe site
Bună, mulțumesc pentru ajutor.
Acum, cum pot avea valorile mele sortate și alfabetic? (Nu vreau să folosesc filtrul de pe tabelul meu principal)
Ar trebui să folosesc COUNTIFS în loc de COUNTIF?
Te rog ajuta-ma
Acest comentariu a fost redus la minimum de moderatorul de pe site
Salut Alexis,
Ne pare rău, nu pot ajuta să sortăm valoarea extrasă alfabetic în același timp cu formula. Multumesc pentru comentariu.
Acest comentariu a fost redus la minimum de moderatorul de pe site
Folosesc acea =IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$1:D1, $B$2:$B$9), 0)),"") care este grozavă pentru o coloană, dar datele mele sunt răspândite pe o serie de coloane și rânduri. Pot edita formula pentru a include întreaga zonă? Datele mele trăiesc de la AC4 la AR60...
Acest comentariu a fost redus la minimum de moderatorul de pe site
Încerc codul VBA și formula. Codul VBA funcționează foarte bine, dar nu pot păstra un fișier cu macro. Dar problema este că nu pot face formula să funcționeze. A avut cineva vreo idee? Mulțumesc
Acest comentariu a fost redus la minimum de moderatorul de pe site
Bună Charlotte,
Multumesc pentru comentariu. Puteți păstra fișierul cu macro pentru utilizare ulterioară salvând registrul de lucru ca registru de lucru Excel activat pentru macrocomandă.
Pentru problema formulei, vă rugăm să furnizați o captură de ecran a datelor dvs.? Multumesc pentru comentariu.
Acest comentariu a fost redus la minimum de moderatorul de pe site
mulțumesc mult
Acest comentariu a fost redus la minimum de moderatorul de pe site
cum să faceți ca codul vba să funcționeze pentru un interval în care a fost folosită o altă formulă? Pe coloana BI aveți o formulă, referindu-se la coloanele D și E.
Dacă folosesc aplicați codul la coloana L (să zicem), (evident, modificând corect celulele din cod) macro-ul returnează formula aplicată coloanelor M și N... Funcționează, atunci, dar nu așa cum vreau! Cum se păstrează valorile în coloana B? Mulțumesc
Acest comentariu a fost redus la minimum de moderatorul de pe site
Ik heb gemerkt dat de 'formule'-methode erg traag werkt bij grote datasets. Een goed alternatief este 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'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 este mai complex.
Acest comentariu a fost redus la minimum de moderatorul de pe site
Aș dori să pot face exact același lucru, cu excepția utilizării a două intervale de coloane separate (B2:B9), precum și (D2:D9), este posibil acest lucru?
Acest comentariu a fost redus la minimum de moderatorul de pe site
Bună Anthony,
Puteți plasa rezultatele în aceeași coloană cu datele originale. Cum ar fi coloana B în acest caz.
Dar trebuie să faceți referire la celula de sus a celulei rezultat în formulă, după cum urmează.
=IFEROARE(INDEX($B$2:$B$9, MATCH(0,COUNTIF($B$11:B11, $B$2:$B$9), 0)),"") + Ctrl + Shift + Enter
Acest comentariu a fost redus la minimum de moderatorul de pe site
con este procedura de filtru se face de forma foarte 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ște un criteriu de filtrare în acest caz în fila d56, cu același titlu din lista a remover duplicados și la d57 la dejo en blanco
3. una vez executado se muestran los datos în fila destino, care în mi-casual era la d59

Interval("A59:A239"). Acțiune de filtrare avansată:=xlFilterCopy, CriteriaRange:=Interval _
("D56:D57"), CopyToRange:=Range ("D59"), Unique:=True
Nu există comentarii postate aici încă
Lăsa comentarii
Postare ca invitat
×
Evaluează această postare:
0   Caractere
Locații sugerate

Urmărește-ne

Copyright © 2009 - www.extendoffice.com. | Toate drepturile rezervate. Cu sprijinul ExtendOffice. | Harta site-ului
Microsoft și sigla Office sunt mărci comerciale sau mărci comerciale înregistrate ale Microsoft Corporation în Statele Unite și / sau în alte țări.
Protejat de Sectigo SSL