Přejít k hlavnímu obsahu

Jak extrahovat jedinečné hodnoty na základě kritérií v aplikaci Excel?

Předpokládejme, že máte levý rozsah dat, který chcete vypsat pouze jedinečné názvy sloupce B na základě konkrétního kritéria sloupce A, abyste získali výsledek, jak je znázorněno níže. Jak jste mohli s tímto úkolem v aplikaci Excel jednat rychle a snadno?

Extrahujte jedinečné hodnoty na základě kritérií pomocí maticového vzorce

Extrahujte jedinečné hodnoty na základě více kritérií pomocí maticového vzorce

Extrahujte jedinečné hodnoty ze seznamu buněk s užitečnou funkcí

 

Extrahujte jedinečné hodnoty na základě kritérií pomocí maticového vzorce

K vyřešení této úlohy můžete použít složitý vzorec pole, postupujte takto:

1. Zadejte následující vzorec do prázdné buňky, kde chcete vypsat výsledek extrakce, v tomto příkladu ji vložím do buňky E2 a poté stiskněte Shift + Ctrl + Enter klávesy pro získání první jedinečné hodnoty.

=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. Poté přetáhněte popisovač výplně dolů do buněk, dokud se nezobrazí prázdné buňky, a nyní jsou uvedeny všechny jedinečné hodnoty založené na konkrétním kritériu, viz screenshot:

Poznámka: Ve výše uvedeném vzorci: B2: B15 je rozsah sloupců obsahuje jedinečné hodnoty, ze kterých chcete extrahovat, A2: A15 je sloupec obsahující kritérium, na kterém jste založeni, D2 označuje kritérium, na kterém chcete vypsat jedinečné hodnoty na základě, a E1 je buňka nad zadaným vzorcem.

Extrahujte jedinečné hodnoty na základě více kritérií pomocí maticového vzorce

Pokud chcete extrahovat jedinečné hodnoty na základě dvou podmínek, zde je další vzorec pole, který vám může udělat laskavost, postupujte takto:

1. Zadejte níže uvedený vzorec do prázdné buňky, kde chcete vypsat jedinečné hodnoty, v tomto příkladu ji vložím do buňky G2 a poté stiskněte Shift + Ctrl + Enter klávesy pro získání první jedinečné hodnoty.

=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. Potom přetáhněte popisovač výplně dolů do buněk, dokud se nezobrazí prázdné buňky, a nyní jsou uvedeny všechny jedinečné hodnoty založené na konkrétních dvou podmínkách, viz screenshot:

Poznámka: Ve výše uvedeném vzorci: C2: C15 je rozsah sloupců obsahuje jedinečné hodnoty, ze kterých chcete extrahovat, A2: A15 a E2 jsou první rozsah s kritérii, na základě kterých chcete extrahovat jedinečné hodnoty, B2: B15 a F2 jsou druhou oblastí s kritérii, na základě kterých chcete extrahovat jedinečné hodnoty, a G1 je buňka nad zadaným vzorcem.

Extrahujte jedinečné hodnoty ze seznamu buněk s užitečnou funkcí

Někdy chcete pouze extrahovat jedinečné hodnoty ze seznamu buněk, zde doporučím užitečný nástroj -Kutools pro Excel, S jeho Extrahujte buňky s jedinečnými hodnotami (zahrňte první duplikát) nástroj, můžete rychle extrahovat jedinečné hodnoty.

Poznámka:Použít toto Extrahujte buňky s jedinečnými hodnotami (zahrňte první duplikát)Nejprve byste si měli stáhnout soubor Kutools pro Excela poté tuto funkci rychle a snadno aplikujte.

Po instalaci Kutools pro Excel, udělejte prosím toto:

1. Klikněte na buňku, do které chcete výsledek odeslat. (Poznámka: Neklikejte na buňku v prvním řádku.)

2. Pak klikněte na tlačítko Kutools > Pomocník vzorců > Pomocník vzorců, viz screenshot:

3. V Pomocník vzorců V dialogovém okně proveďte následující operace:

  • vybrat Text možnost z nabídky Vzorec Styl rozbalovací seznam;
  • Pak zvolte Extrahujte buňky s jedinečnými hodnotami (zahrňte první duplikát) z Vyberte si fromula seznam;
  • Vpravo Zadání argumentů V části vyberte seznam buněk, ze kterých chcete extrahovat jedinečné hodnoty.

4. Pak klikněte na tlačítko Ok Tlačítko, první výsledek se zobrazí do buňky, pak vyberte buňku a přetáhněte popisovač výplně do buněk, které chcete vypsat všechny jedinečné hodnoty, dokud se nezobrazí prázdné buňky, viz screenshot:

Stažení zdarma Kutools pro Excel nyní!

Více relativních článků:

  • Spočítejte počet jedinečných a odlišných hodnot ze seznamu
  • Předpokládejme, že máte dlouhý seznam hodnot s některými duplicitními položkami, nyní chcete spočítat počet jedinečných hodnot (hodnoty, které se v seznamu objeví pouze jednou) nebo odlišné hodnoty (všechny různé hodnoty v seznamu, to znamená jedinečné hodnoty + 1. duplicitní hodnoty) ve sloupci, jak je zobrazen snímek obrazovky vlevo. V tomto článku budu hovořit o tom, jak řešit tuto práci v aplikaci Excel.
  • Součet jedinečných hodnot na základě kritérií v aplikaci Excel
  • Například mám řadu dat, která obsahuje sloupce Název a Objednávka, nyní, abych shrnul pouze jedinečné hodnoty ve sloupci Objednávka na základě sloupce Název, jak ukazuje následující snímek obrazovky. Jak rychle a snadno vyřešit tento úkol v aplikaci Excel?
  • Zřetězení jedinečných hodnot v aplikaci Excel
  • Pokud mám dlouhý seznam hodnot, které se naplnily nějakými duplicitními daty, teď chci najít pouze jedinečné hodnoty a poté je zřetězit do jedné buňky. Jak mohu tento problém rychle a snadno vyřešit v aplikaci Excel?

Nejlepší nástroje pro produktivitu v kanceláři

🤖 Kutools AI asistent: Revoluční analýza dat založená na: Inteligentní provedení   |  Generovat kód  |  Vytvořte vlastní vzorce  |  Analyzujte data a generujte grafy  |  Vyvolejte funkce Kutools...
Populární funkce: Najít, zvýraznit nebo identifikovat duplikáty   |  Odstranit prázdné řádky   |  Kombinujte sloupce nebo buňky bez ztráty dat   |   Kolo bez vzorce ...
Super vyhledávání: Více kritérií VLookup  |   VLookup s více hodnotami  |   VLookup na více listech   |   Fuzzy vyhledávání ....
Pokročilý rozevírací seznam: Rychle vytvořte rozevírací seznam   |  Závislý rozbalovací seznam   |  Vícenásobný výběr rozevíracího seznamu ....
Správce sloupců: Přidejte konkrétní počet sloupců  |  Přesunout sloupce  |  Přepnout stav viditelnosti skrytých sloupců  |  Porovnejte rozsahy a sloupce ...
Doporučené funkce: Zaměření mřížky   |  Návrhové zobrazení   |   Velký Formula Bar   |  Správce sešitů a listů   |  Knihovna zdrojů (Automatický text)   |  Výběr data   |  Zkombinujte pracovní listy   |  Šifrovat/dešifrovat buňky   |  Odesílat e-maily podle seznamu   |  Super filtr   |   Speciální filtr (filtr tučné/kurzíva/přeškrtnuté...) ...
Top 15 sad nástrojů:  12 Text Tools (doplnit text, Odebrat znaky, ...)   |   50+ Graf Typ nemovitosti (Ganttův diagram, ...)   |   40+ Praktické Vzorce (Vypočítejte věk na základě narozenin, ...)   |   19 Vložení Tools (Vložte QR kód, Vložit obrázek z cesty, ...)   |   12 Konverze Tools (Čísla na slova, Přepočet měny, ...)   |   7 Sloučit a rozdělit Tools (Pokročilé kombinování řádků, Rozdělit buňky, ...)   |   ... a více

Rozšiřte své dovednosti Excel pomocí Kutools pro Excel a zažijte efektivitu jako nikdy předtím. Kutools for Excel nabízí více než 300 pokročilých funkcí pro zvýšení produktivity a úsporu času.  Kliknutím sem získáte funkci, kterou nejvíce potřebujete...

Popis

Office Tab přináší do Office rozhraní s kartami a usnadňuje vám práci

  • Povolte úpravy a čtení na kartách ve Wordu, Excelu, PowerPointu, Publisher, Access, Visio a Project.
  • Otevřete a vytvořte více dokumentů na nových kartách ve stejném okně, nikoli v nových oknech.
  • Zvyšuje vaši produktivitu o 50%a snižuje stovky kliknutí myší každý den!
Comments (40)
No ratings yet. Be the first to rate!
Raju Sunkasari
Raju Sunkasari
This comment was minimized by the moderator on the site
Thank you so much. This was very very helpful. You Rock!!
Reply
0
0
Ambet23
Ambet23
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..
Reply
Report
0
0
Saravanan Kumar
Saravanan Kumar
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
Reply
0
0
J Sloth
J Sloth
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.
Reply
0
0
K
K
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
Reply
0
0
skyyang
skyyang     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.
View Attachments (1 / 5)
Reply
Report
0
0
Giancarlo
Giancarlo
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
Reply
0
0
skyyang
skyyang     Giancarlo
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!
View Attachments (1 / 5)
Reply
Report
1
0
Astrid
Astrid     skyyang
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?
Reply
0
0
Konstantin
Konstantin
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.
Reply
0
0
skyyang
skyyang     Konstantin
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！
View Attachments (1 / 5)
Reply
Report
0
0
Ed
Ed
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
Reply
0
0
ewik
ewik
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?
Reply
0
0
jjer13
jjer13
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.
Reply
Report
0
0
skyyang
skyyang     jjer13
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！
View Attachments (1 / 5)
Reply
Report
0
0
jjer13
jjer13     skyyang
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?
Reply
Report
0
0
jjer13
jjer13     skyyang
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!
Reply
Report
0
0
jjer13
jjer13     skyyang
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!
Reply
Report
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations