Ugrás a tartalomra

Hogyan lehet kibontani az egyedi értékeket az Excel kritériumai alapján?

Tegyük fel, hogy megvan a bal oldali adattartománya, amely csak az „A” oszlop egyedi kritériumai alapján szeretné felsorolni a „B” oszlop egyedi neveit, hogy az eredményt az alábbi képernyőképen láthatja. Hogyan tudná gyorsan és egyszerűen kezelni ezt a feladatot az Excel programban?

Kivonat egyedi értékeket kritériumok alapján tömbképlettel

Bontsa ki az egyedi értékeket több kritérium alapján tömbképlettel

Kivonat egyedi értékeket egy hasznos funkcióval rendelkező cellák listájából

 

Kivonat egyedi értékeket kritériumok alapján tömbképlettel

A feladat megoldásához komplex tömbképletet alkalmazhat, kérjük, tegye a következőket:

1. Írja be az alábbi képletet egy üres cellába, ahol fel szeretné sorolni a kivonási eredményt. Ebben a példában az E2 cellába teszem, majd Shift + Ctrl + Enter gombokat az első egyedi érték megszerzéséhez.

=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. Ezután húzza lefelé a kitöltő fogantyút a cellákig, amíg üres cellák nem jelennek meg, és mostantól az adott kritériumon alapuló összes egyedi érték fel lett sorolva, lásd a képernyőképet:

Jegyzet: A fenti képletben: B2: B15 az oszloptartomány tartalmazza azokat az egyedi értékeket, amelyekből kivonni szeretne, A2: A15 az oszlop tartalmazza azt a kritériumot, amelyen alapul, D2 azt a kritériumot jelöli, amely alapján az egyedi értékeket fel akarja sorolni, és E1 a beírt képlet fölötti cella.

Bontsa ki az egyedi értékeket több kritérium alapján tömbképlettel

Ha két feltétel alapján szeretné kivonni az egyedi értékeket, itt egy másik tömbképlet tehet szívességet, kérjük, tegye a következőket:

1. Írja be az alábbi képletet egy üres cellába, ahol fel szeretné sorolni az egyedi értékeket. Ebben a példában a G2 cellába teszem, majd megnyomom Shift + Ctrl + Enter gombokat az első egyedi érték megszerzéséhez.

=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. Ezután húzza lefelé a kitöltő fogantyút a cellákig, amíg üres cellák nem jelennek meg, és mostantól a két feltétel alapján felsorolt ​​összes egyedi érték fel lett sorolva, lásd a képernyőképet:

Jegyzet: A fenti képletben: C2: C15 az oszloptartomány tartalmazza azokat az egyedi értékeket, amelyekből kivonni szeretne, A2: A15 és a E2 az első olyan kritériumokkal rendelkező tartomány, amelyek alapján egyedi értékeket szeretne kinyerni, B2: B15 és a F2 a második tartomány azon feltételekkel, amelyek alapján egyedi értékeket szeretne kinyerni, és G1 a beírt képlet fölötti cella.

Kivonat egyedi értékeket egy hasznos funkcióval rendelkező cellák listájából

Néha csak ki akarja vonni az egyedi értékeket a cellák listájából, itt egy hasznos eszközt ajánlok-Kutools az Excel számára, Annak Kivonat egyedi értékű cellákat (az első másolatot is tartalmazza) segédprogrammal gyorsan kivonhatja az egyedi értékeket.

Jegyzet:Ennek alkalmazásához Kivonat egyedi értékű cellákat (az első másolatot is tartalmazza), először is le kell töltenie a Kutools az Excel számára, majd gyorsan és egyszerűen alkalmazza a funkciót.

Telepítése után Kutools az Excel számára, kérjük, tegye a következőket:

1. Kattintson egy cellára, ahol ki szeretné adni az eredményt. (Megjegyzések: Ne kattintson egy cellára az első sorban.)

2. Ezután kattintson Kutools > Formula Segítő > Formula Segítő, lásd a képernyőképet:

3. Az Képletek segítője párbeszédpanelen tegye a következőket:

  • választ szöveg lehetőség a Képlet típus legördülő lista;
  • Ezután válasszon Kivonat egyedi értékű cellákat (az első másolatot is tartalmazza) tól Válasszon egy fromulát lista mező;
  • Jobbra Érvek bevitele szakaszban válassza ki az egyedi értékeket kibontani kívánt cellák listáját.

4. Ezután kattintson Ok gombra, az első eredmény megjelenik a cellában, majd válassza ki a cellát, és húzza át a kitöltő fogantyút azokra a cellákra, amelyek az összes egyedi értéket fel akarják sorolni, amíg üres cellák nem jelennek meg, lásd a képernyőképet:

Ingyenesen töltse le a Kutools for Excel programot most!


Relatívabb cikkek:

  • Számolja meg az egyedi és megkülönböztetett értékek számát egy listából
  • Tételezzük fel, hogy hosszú értéklistád van néhány ismétlődő elemmel, most meg akarod számolni az egyedi értékek számát (az értékeket, amelyek csak egyszer jelennek meg a listában) vagy a különálló értékeket (a lista összes különböző értéke, ez egyedi értékek + 1. duplikált értékek) egy oszlopban, a bal oldali képernyőkép szerint. Ez a cikk arról fog beszélni, hogyan kell kezelni ezt a munkát az Excel programban.
  • Összegezze az egyedi értékeket az Excel kritériumai alapján
  • Például van egy adattartományom, amely a Név és a Sorrend oszlopokat tartalmazza, hogy a Rendelés oszlopban csak az egyedi értékeket összegezzem a Név oszlop alapján, a következő képernyőkép szerint. Hogyan lehet ezt a feladatot gyorsan és egyszerűen megoldani az Excelben?
  • Összekapcsolja az egyedi értékeket az Excelben
  • Ha van egy hosszú listám az értékekről, amelyek néhány duplikált adattal vannak feltöltve, most csak az egyedi értékeket szeretném megkeresni, majd összefűzni őket egyetlen cellába. Hogyan tudnám gyorsan és egyszerűen kezelni ezt a problémát az Excel programban?

A legjobb irodai hatékonyságnövelő eszközök

Népszerű szolgáltatások: Ismétlődések keresése, kiemelése vagy azonosítása   |  Üres sorok törlése   |  Oszlopok vagy cellák kombinálása adatvesztés nélkül   |   Kerek Formula nélkül ...
Szuper keresés: Több kritérium VLookup    Többértékű VLookup  |   VLookup több munkalapon   |   Fuzzy Lookup ....
Speciális legördülő lista: Gyors legördülő lista létrehozása   |  Függő legördülő lista   |  Többszörösen válassza ki a legördülő listát ....
Oszlopkezelő: Adjon meg egy adott számú oszlopot  |  Oszlopok mozgatása  |  Kapcsolja be a Rejtett oszlopok láthatósági állapotát  |  Tartományok és oszlopok összehasonlítása ...
Kiemelt funkciók: Rács fókusz   |  Design nézet   |   Nagy Formula bár    Munkafüzet és lapkezelő   |  Erőforrás-könyvtár (Auto szöveg)   |  Dátumválasztó   |  Kombinálja a munkalapokat   |  Cellák titkosítása/dekódolása    E-mailek küldése listánként   |  Szuper szűrő   |   Speciális szűrő (félkövér/dőlt/áthúzott szűrés...) ...
A 15 legjobb eszközkészlet12 szöveg Eszközök (Szöveg hozzáadása, Karakterek eltávolítása,...)   |   50 + Táblázatos Típusai (Gantt-diagram,...)   |   40+ Praktikus képletek (Számolja ki az életkort a születésnap alapján,...)   |   19 beszúrás Eszközök (Helyezze be a QR-kódot, Kép beszúrása az útvonalból,...)   |   12 Átalakítás Eszközök (Számok szavakig, Valuta átváltás,...)   |   7 Egyesítés és felosztás Eszközök (Haladó kombinált sorok, Hasított sejtek,...)   |   ... és több

Töltsd fel Excel-készségeidet a Kutools for Excel segítségével, és tapasztald meg a még soha nem látott hatékonyságot. A Kutools for Excel több mint 300 speciális funkciót kínál a termelékenység fokozásához és az időmegtakarításhoz.  Kattintson ide, hogy megszerezze a leginkább szükséges funkciót...

kte lap 201905


Az Office lap füles felületet hoz 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!
Comments (40)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Thank you so much. This was very very helpful. You Rock!!
This comment was minimized by the moderator on the site
hi everyone..
i have problem..
i got blank result even i press ctrl shift enter together..
This comment was minimized by the moderator on the site
Hi all, Can some help me to get all unique values on one single cell
This comment was minimized by the moderator on the site
Hi, this worked well! Although it takes Excel sooooo long to calculate. Just dragging down 15 cells in a column takes about 15min to calculate... if not longer. Is this normal? If this becomes dynamic it will take a hell of alot of computing time.
This comment was minimized by the moderator on the site
Hello. This is really helpful, however, what If I want a formula that lists the unique values based on multiple criteria. eg. I have a data set which has the following data in a table (after each hyphen is a new column but same row):

Company A - £200 - £100
Company A - £300 - £200
Company B - £300 - £200
Company C - £600 - £200
Company B - £100 - £300
Company D - £0 - £600
Company A - £700 - £100

I want a new data table in a new tab which groups the duplicate values without using an array formula. currently I'm grouping using a pivot table and pasting to my new data table. It's a long process but array formulas make my spreadsheet really slow.

Company A - £1200 - £400
Company B - £400 - £500
Company C - £600 - £200
Company D - £0 - £600

Thanks,
K
This comment was minimized by the moderator on the site
Hello, K,
For solving your problem, I can recommend our useful tool- Kutools for Excel, with its Advanced Combine Rows feature, you can deal with this job quickly. Firstly, you should copy and paste your data into a new worksheet, and then apply htis feature as below screenhsot shown.
You can know more about this feature from: https://www.extendoffice.com/product/kutools-for-excel/excel-combine-duplicate-rows.html
Please download Kutools for Excel and install it, then apply this feature. Full feature free trial 30-day, please try.
This comment was minimized by the moderator on the site
Hi! the formula works really well. I would like to add another criterion, i mean, get the unique answers but using two criteria
This comment was minimized by the moderator on the site
Hi, Giancarlo,
to extract unique values based on multiple criteria, any of the below formula can help you: (after pasting the formula, please press Ctrl + Shift + Enter keys together.)
=IFERROR(INDEX($C$2:$C$11, MATCH(0, COUNTIF(G1:$G$1, $C$2:$C$11)+IF($A$2:$A$11<>$E$2, 1, 0)+IF($B$2:$B$11<>$F$2, 1, 0), 0)), "")
=INDEX($C$2:$C$11, MATCH(0, IF(($A$2:$A$11=$E$2)*($B$2:$B$11=$F$2), COUNTIF($G$1:$G1, $C$2:$C$11), ""), 0))
Please try, hope it can help you!
This comment was minimized by the moderator on the site
Hi. I am using the two conditions formula =IFERROR(INDEX($C$2:$C$11, MATCH(0, COUNTIF(G1:$G$1, $C$2:$C$11)+IF($A$2:$A$11<>$E$2, 1, 0)+IF($B$2:$B$11<>$F$2, 1, 0), 0)), "") to extract a unique list and it works great, but I am struggle to add the SMALL function to get the list sorted as well in ascending order. Are you able to help?
This comment was minimized by the moderator on the site
Is there a way to make this work while ALLOWING for duplicate values? For instance, I want all instances of Lucy to be listed in the results.
This comment was minimized by the moderator on the site
Hello, Konstantin,
To extract all corresponding values including the duplicates based on a specific cell criteria, the following array formula can help you, see screenshot:
=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))

After inserting the formula, please press Shift + Ctrl + Enter keys together to get the correct result, and then drag the fill handle down to get all values.
Hope this can help you, thank you!
This comment was minimized by the moderator on the site
This has worked great for me with a specific lookup value. However, if I wanted to use a wildcard to look up partial values, how would I do that? For example, if I wanted to lookup all the names associated with KT?

I am using this function to look up cells that contain multiple text. For example if each product also had a sub-product within the same cell but I was only looking for names associated with the sub-product "elf".

KTE - elf
KTE- ball
KTE - piano
KTO - elf
KTO- ball
KTO - piano
This comment was minimized by the moderator on the site
For me the formula does not work. I press ctrl shift enter and i still get an error N/A. I would like to add that i prpared exaclty the same data as in tutorial. What is the reason it does not work?
This comment was minimized by the moderator on the site
How would I get this formula to return each of the duplicates instead of one of each of the names? For instance, in the example above, how would I get the results column (B:B) to return Lucy, Ruby, Anny, Jose, Lucy, Anny, Tom? I'm using this as a budget tool pulling to specific account summaries from a general ledger. However, several of the amounts and transaction descriptions are duplicates in the general ledger. Once the first of the duplicated values is pulled, no more of them get pulled.
This comment was minimized by the moderator on the site
Hi, Joe,
To extract all corresponding values based on a specific cell criteria, the following array formula can help you, see screenshot:
=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))

After inserting the formula, please press Shift + Ctrl + Enter keys together to get the correct result, and then drag the fill handle down to get all values.
Hope this can help you, thank you!
This comment was minimized by the moderator on the site
Last Question: If I want the results column to return all values not associated with KTE or KTO (so, D:D would be Tom, Nocol, Lily, Angelina, Genna), how would I do that?
This comment was minimized by the moderator on the site
Ok, so it works in the master workbook. There is one exception that I haven't been able to determine the cause of: If the array (in my case, the general ledger that I had beginning in row 3) does not begin in Row 1, the returned values are incorrect. What causes this problem, and which term in the formula fixes it? Thanks again for your help with this!
This comment was minimized by the moderator on the site
So far so good. I'm able to duplicate the results in the test sheet, make changes to the array, and then correct the formula to account for the changes I've made. I plan to move this into the master sheet today and see how it works. Thanks for the help!
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations