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

Cum se extrag valori unice pe baza criteriilor din Excel?

Presupunând că aveți intervalul de date din stânga pe care doriți să îl listați doar cu numele unic al coloanei B pe baza unui criteriu specific al coloanei A pentru a obține rezultatul așa cum este prezentat mai jos. Cum ați putea face față acestei sarcini în Excel rapid și ușor?

Extrageți valori unice pe baza criteriilor cu formula matricei

Extrageți valori unice pe baza mai multor criterii cu formula matricei

Extrageți valori unice dintr-o listă de celule cu o caracteristică utilă

 

Extrageți valori unice pe baza criteriilor cu formula matricei

Pentru a rezolva această sarcină, puteți aplica o formulă de matrice complexă, procedați după cum urmează:

1. Introduceți formula de mai jos într-o celulă goală unde doriți să listați rezultatul extragerii, în acest exemplu, îl voi pune în celula E2, apoi apăsați Shift + Ctrl + Enter chei pentru a obține prima valoare unică.

=IFERROR(INDEX($B$2:$B$15, MATCH(0, IF($D$2=$A$2:$A$15, COUNTIF($E$1:$E1, $B$2:$B$15), ""), 0)),"")

2. Apoi, glisați mânerul de umplere în jos până la celule până când sunt afișate celulele goale și acum au fost listate toate valorile unice bazate pe criteriul specific, a se vedea captura de ecran:

Notă: În formula de mai sus: B2: B15 este gama de coloane conține valorile unice din care doriți să extrageți, A2: A15 este coloana conține criteriul pe care te-ai bazat, D2 indică criteriul pe care doriți să enumerați valorile unice pe baza și E1 este celula de deasupra formulei introduse.

Extrageți valori unice pe baza mai multor criterii cu formula matricei

Dacă doriți să extrageți valorile unice pe baza a două condiții, iată o altă formulă matrice care vă poate face o favoare, vă rugăm să faceți acest lucru:

1. Introduceți formula de mai jos într-o celulă goală în care doriți să enumerați valorile unice, în acest exemplu, o voi pune în celula G2, apoi apăsați Shift + Ctrl + Enter chei pentru a obține prima valoare unică.

=IFERROR(INDEX($C$2:$C$15,MATCH(0,COUNTIF(G1:$G$1,$C$2:$C$15)+IF($A$2:$A$15<>$E$2,1,0)+IF($B$2:$B$15<>$F$2,1,0),0)),"")

2. Apoi, glisați mânerul de umplere în jos până la celule până când sunt afișate celulele goale și acum au fost listate toate valorile unice bazate pe cele două condiții specifice, a se vedea captura de ecran:

Notă: În formula de mai sus: C2: C15 este gama de coloane conține valorile unice din care doriți să extrageți, A2: A15 și E2 sunt prima gamă cu criteriile pe care doriți să extrageți valori unice bazate pe, B2: B15 și F2 sunt al doilea interval cu criteriile pe care doriți să extrageți valori unice pe baza și G1 este celula de deasupra formulei introduse.

Extrageți valori unice dintr-o listă de celule cu o caracteristică utilă

Uneori, doriți doar să extrageți valorile unice dintr-o listă de celule, aici, vă voi recomanda un instrument util-Kutools pentru Excel, Cu său Extrageți celule cu valori unice (includeți primul duplicat) utilitar, puteți extrage rapid valorile unice.

Notă:Pentru a aplica acest lucru Extrageți celule cu valori unice (includeți primul duplicat), în primul rând, ar trebui să descărcați fișierul Kutools pentru Excel, apoi aplicați caracteristica rapid și ușor.

După instalare Kutools pentru Excel, vă rugăm să faceți acest lucru:

1. Faceți clic pe o celulă în care doriți să afișați rezultatul. (notițe: Nu faceți clic pe o celulă din primul rând.)

2. Apoi apasa Kutools > Formula Helper > Formula Helper, vezi captura de ecran:

3. În Ajutor pentru formule caseta de dialog, vă rugăm să efectuați următoarele operații:

  • Selectați Text opțiune de la Formulă Tip lista verticală;
  • Atunci alege Extrageți celule cu valori unice (includeți primul duplicat) de la Alegeți o fromula casetă listă;
  • In dreapta Argumente de intrare secțiune, selectați o listă de celule pe care doriți să extrageți valori unice.

4. Apoi apasa Ok butonul, primul rezultat este afișat în celulă, apoi selectați celula și trageți mânerul de umplere peste celulele pe care doriți să le afișați toate valorile unice până când sunt afișate celulele goale, vedeți captura de ecran:

Descărcați gratuit Kutools pentru Excel acum!


Mai multe articole relative:

  • Numărați numărul de valori unice și distincte dintr-o listă
  • Presupunând că aveți o listă lungă de valori cu unele elemente duplicate, acum doriți să numărați numărul de valori unice (valorile care apar în listă o singură dată) sau valorile distincte (toate valorile diferite din listă, înseamnă unic valori + primele valori duplicate) într-o coloană așa cum este afișat ecranul din stânga. În acest articol, voi vorbi despre cum să rezolvați această sarcină în Excel.
  • Suma valorilor unice pe baza criteriilor din Excel
  • De exemplu, am o serie de date care conține coloane Nume și comandă, acum, pentru a însuma doar valorile unice în coloana Comandă pe baza coloanei Nume, după cum se arată în următoarea captură de ecran. Cum să rezolvați această sarcină rapid și ușor în Excel?
  • Concatenează valori unice în Excel
  • Dacă am o listă lungă de valori care sunt populate cu unele date duplicate, acum, vreau să găsesc doar valorile unice și apoi să le concatenez într-o singură celulă. Cum aș putea rezolva această problemă rapid și ușor în Excel?

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 (40)
Încă nu există evaluări. Fii primul care evaluează!
Acest comentariu a fost redus la minimum de moderatorul de pe site
Bună, mulțumesc pentru acest tutorial, funcționează perfect. Încerc să-l modific pentru a funcționa cu o condiție SAU, dar nu pare să funcționeze - este posibil? de ex. =INDEX($B$2:$B$17, MATCH(0, DACĂ(SAU($D$2=$A$2:$A$17, $D$2=$B$2:$B$17), COUNTIF($E$1), :$E1, $B$2:$B$17), ""), 0))
Acest comentariu a fost redus la minimum de moderatorul de pe site
Mulțumesc pentru acest tutorial! Încerc, de asemenea, să modific formula, ca și comentatorul de mai sus, dar cu o condiție AND, astfel încât să îndeplinească un alt criteriu condiționat (de exemplu, pentru acest exemplu, aș dori să văd doar lucrurile peste un anumit prag). Puteti va rog sa sfatuiti? Mulțumesc!
Acest comentariu a fost redus la minimum de moderatorul de pe site
Hei, o modalitate de a face acest lucru: Înlocuiți formula if cu sumproduct((condition1=rng1)+(condition2=rng2))*countif(... A funcționat pentru mine. Mult succes! Înlocuind + cu un *, puteți faceți din aceasta o condiție OR, dar aveți grijă de brackets!
Acest comentariu a fost redus la minimum de moderatorul de pe site
Mulțumesc, este grozav!
Acest comentariu a fost redus la minimum de moderatorul de pe site
Mulțumesc pentru asta, am încercat asta și se pare că funcționează bine intermitent. Problema care se repetă este că uneori doar prima valoare potrivită va reveni și este apoi duplicată atunci când trag în jos pentru a returna toate valorile potrivite. Cum pot preveni acest lucru? Orice sugestii?
Acest comentariu a fost redus la minimum de moderatorul de pe site
Acest lucru funcționează foarte bine, dar ori de câte ori valoarea pe care o pune este duplicată, plasează valoarea o singură dată. De exemplu, dacă lista ta conține două Lucy, aceasta aduce doar o singură Lucy la noua masă. Există vreo modalitate de a remedia asta?
Acest comentariu a fost redus la minimum de moderatorul de pe site
Când utilizați această formulă, se repetă în permanență prima valoare, cum faceți acea oprire și furnizați lista de valori care este egală cu produsul din D2?
Acest comentariu a fost redus la minimum de moderatorul de pe site
Bună, pentru a opri repetarea primei valori în timp ce trageți în jos, trebuie să COUNTIF celula DE DEAPS celula în care introduceți formula. De exemplu, dacă formula merge în E2, trebuie să tastați countif($E$1:$E1...
Acest comentariu a fost redus la minimum de moderatorul de pe site
Bună Ryan. Formulele funcționează excelent, totuși, când trageți în jos, prima valoare se repetă în continuare. M-am asigurat că COUNTIF face referire la celula DE DEASPRU celula cu formula, dar totuși repetă prima valoare când trageți în jos? (de exemplu, dacă formula matricei este în C2, atunci COUNTIF indică către celula $C$1:$C$1)
Acest comentariu a fost redus la minimum de moderatorul de pe site
Bună Ryan. Formulele funcționează excelent, totuși, când trageți în jos, prima valoare se repetă în continuare. M-am asigurat că COUNTIF face referire la celula DE DEASPRU celula cu formula, dar totuși repetă prima valoare când trageți în jos? (de exemplu, dacă formula matricei este în C2, atunci COUNTIF indică către celula $C$1:$C$1)
Acest comentariu a fost redus la minimum de moderatorul de pe site
Probabil că nu funcționează pentru că ați blocat celulele - Încercați să înlocuiți $C$1:$C$1 cu $C$1:$C1
Acest comentariu a fost redus la minimum de moderatorul de pe site
a fost foarte util, dar continui să primesc duble ale tuturor numelor ca acesta:
Doe, Jane
Doe, Jane
Hoover, Tom
Hoover, Tom

Cum pot opri asta?
Acest comentariu a fost redus la minimum de moderatorul de pe site
Bună ziua, primesc eroarea „#N/A” la „Funcția de potrivire”, vă rog să ne ghidați?
Acest comentariu a fost redus la minimum de moderatorul de pe site
=IF(AL2="AP","AP",IF(AK2="AD","AD",IF(Z2>500000,"Da","Nu"))) Vreau ca "toate condițiile" să fie îndeplinite pentru a spune da... Excel reflectând eroarea în această formulă.. Vă rugăm să sfătuiți
Acest comentariu a fost redus la minimum de moderatorul de pe site
de fapt, vreau ca celula să reflecte „DA” dacă (AL2="AP" și AK2="AD" și Z2>500000)
Acest comentariu a fost redus la minimum de moderatorul de pe site
Primesc o eroare #N/A la funcția de potrivire cu această formulă. Vă rugăm să ne ajutați?
Acest comentariu a fost redus la minimum de moderatorul de pe site
Bună ziua, primesc eroarea „#N/A” la „Funcția de potrivire”, vă rog să ne ghidați?
Acest comentariu a fost redus la minimum de moderatorul de pe site
Dacă primiți eroarea #N/A, accesați formula și utilizați Control + Shift + Enter în loc de Enter.
Acest comentariu a fost redus la minimum de moderatorul de pe site
Primesc 0 în loc de rezultatele așteptate, formula se descurcă grozav pentru datele din aceeași foaie, aveți vreo soluție pentru datele din altă foaie?

aceasta este formula mea

=IFERROR(INDEX('Switching Data'!$B$7:$B$204,MATCH(0,IF($A$2='Switching Data'!$A$7:$A$204,COUNTIF($A$4:A4,'Switching Data'!$B$7:$B$204),""),0)),0)
Acest comentariu a fost redus la minimum de moderatorul de pe site
Bună, Gon,
După introducerea formulei, ar trebui să apăsați simultan tastele Ctrl + Shift + Enter, nu doar tasta Enter.
Vă rugăm să încercați, vă mulțumesc!
Acest comentariu a fost redus la minimum de moderatorul de pe site
Bună Gon, sper că ești bine. Mă întreb dacă poți să rezolvi această problemă. Primesc aceeași eroare când formula provine dintr-o foaie diferită. Voi aprecia să împărtășesc soluția dacă ați primit-o.
Acest comentariu a fost redus la minimum de moderatorul de pe site
Vă mulţumim!
Acest comentariu a fost redus la minimum de moderatorul de pe site
Cum aș face ca această formulă să returneze fiecare dintre duplicate în loc de unul din fiecare dintre nume? De exemplu, în exemplul de mai sus, cum aș obține ca coloana cu rezultate (B:B) să returneze Lucy, Ruby, Anny, Jose, Lucy, Anny, Tom? Folosesc acest lucru ca instrument bugetar care trage la anumite rezumate ale conturilor dintr-un registru general. Cu toate acestea, mai multe dintre sumele și descrierile tranzacțiilor sunt duplicate în registrul general. Odată ce prima dintre valorile duplicate este extrasă, nu mai sunt extrase dintre ele.
Acest comentariu a fost redus la minimum de moderatorul de pe site
Bună, Joe,
Pentru a extrage toate valorile corespunzătoare pe baza unui anumit criteriu de celulă, următoarea formulă de matrice vă poate ajuta, vedeți captura de ecran:
=IF(ISERROR(INDEX($A$1:$B$17,SMALL(IF($A$1:$A$17=$D$2,ROW($A$1:$A$17)),ROW(1:1)),2)),"",
INDEX($A$1:$B$17,SMALL(IF($A$1:$A$17=$D$2,ROW($A$1:$A$17)),ROW(1:1)),2))

După introducerea formulei, apăsați simultan tastele Shift + Ctrl + Enter pentru a obține rezultatul corect, apoi trageți mânerul de umplere în jos pentru a obține toate valorile.
Sper că acest lucru vă poate ajuta, mulțumesc!
Acest comentariu a fost redus la minimum de moderatorul de pe site
Până acum, bine. Pot să dublez rezultatele în foaia de test, să fac modificări ale matricei și apoi să corectez formula pentru a ține cont de modificările pe care le-am făcut. Plănuiesc să mut asta în foaia principală astăzi și să văd cum funcționează. Multumesc pentru ajutor!
Acest comentariu a fost redus la minimum de moderatorul de pe site
Ok, așa că funcționează în registrul de lucru principal. Există o excepție pentru care nu am putut determina cauza: dacă matricea (în cazul meu, registrul general pe care îl aveam începând cu rândul 3) nu începe în rândul 1, valorile returnate sunt incorecte. Ce cauzează această problemă și ce termen din formulă o rezolvă? Vă mulțumesc încă o dată pentru ajutorul dumneavoastră!
Acest comentariu a fost redus la minimum de moderatorul de pe site
Ultima întrebare: Dacă vreau ca coloana cu rezultate să returneze toate valorile care nu sunt asociate cu KTE sau KTO (deci, D:D ar fi Tom, Nocol, Lily, Angelina, Genna), cum aș face asta?
Acest comentariu a fost redus la minimum de moderatorul de pe site
Pentru mine formula nu merge. Apăs pe ctrl shift enter și încă primesc o eroare N/A. Aș dori să adaug că am pregătit exact aceleași date ca în tutorial. Care este motivul pentru care nu funcționează?
Acest comentariu a fost redus la minimum de moderatorul de pe site
Acest lucru a funcționat grozav pentru mine, cu o anumită valoare de căutare. Cu toate acestea, dacă aș vrea să folosesc un wildcard pentru a căuta valori parțiale, cum aș face asta? De exemplu, dacă aș vrea să caut toate numele asociate cu KT?

Folosesc această funcție pentru a căuta celule care conțin mai multe text. De exemplu, dacă fiecare produs avea și un subprodus în aceeași celulă, dar eu căutam doar nume asociate cu subprodusul „elf”.

KTE - elf
KTE- minge
KTE - pian
KTO - elf
KTO- minge
KTO - pian
Acest comentariu a fost redus la minimum de moderatorul de pe site
Există vreo modalitate de a face acest lucru în timp ce PERMITĂ valori duplicate? De exemplu, vreau ca toate cazurile de Lucy să fie listate în rezultate.
Acest comentariu a fost redus la minimum de moderatorul de pe site
Salut, Constantin,
Pentru a extrage toate valorile corespunzătoare, inclusiv duplicatele pe baza unui anumit criteriu de celulă, următoarea formulă de matrice vă poate ajuta, vedeți captura de ecran:
=IF(ISERROR(INDEX($A$1:$B$17,SMALL(IF($A$1:$A$17=$D$2,ROW($A$1:$A$17)),ROW(1:1)),2)),"",
INDEX($A$1:$B$17,SMALL(IF($A$1:$A$17=$D$2,ROW($A$1:$A$17)),ROW(1:1)),2))

După introducerea formulei, apăsați simultan tastele Shift + Ctrl + Enter pentru a obține rezultatul corect, apoi trageți mânerul de umplere în jos pentru a obține toate valorile.
Sper că acest lucru vă poate ajuta, mulțumesc!
Nu există comentarii postate aici încă
Încărcați mai
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