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

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 typ 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ší kancelářské nástroje produktivity

Kutools pro Excel řeší většinu vašich problémů a zvyšuje vaši produktivitu o 80%

  • Opakované použití: Rychle vložte složité vzorce, grafy a cokoli, co jste dříve používali; Šifrovat buňky s heslem; Vytvořte seznam adresátů a posílat e-maily ...
  • Super Formula Bar (snadno upravit více řádků textu a vzorce); Rozložení pro čtení (snadno číst a upravovat velké množství buněk); Vložit do filtrovaného rozsahu...
  • Sloučit buňky / řádky / sloupce bez ztráty dat; Rozdělit obsah buněk; Zkombinujte duplicitní řádky / sloupce... Zabraňte duplicitním buňkám; Porovnat rozsahy...
  • Vyberte možnost Duplikovat nebo Jedinečný Řádky; Vyberte prázdné řádky (všechny buňky jsou prázdné); Super hledání a fuzzy hledání v mnoha sešitech; Náhodný výběr ...
  • Přesná kopie Více buněk beze změny odkazu na vzorec; Automaticky vytvářet reference do více listů; Vložte odrážky, Zaškrtávací políčka a další ...
  • Extrahujte text, Přidat text, Odebrat podle pozice, Odebrat mezeru; Vytváření a tisk mezisoučtů stránkování; Převod mezi obsahem buněk a komentáři...
  • Super filtr (uložit a použít schémata filtrů na jiné listy); Rozšířené řazení podle měsíce / týdne / dne, frekvence a dalších; Speciální filtr tučnou kurzívou ...
  • Kombinujte sešity a pracovní listy; Sloučit tabulky na základě klíčových sloupců; Rozdělte data do více listů; Dávkový převod xls, xlsx a PDF...
  • Více než 300 výkonných funkcí. Podporuje Office / Excel 2007-2021 a 365. Podporuje všechny jazyky. Snadné nasazení ve vašem podniku nebo organizaci. Plné funkce 30denní bezplatná zkušební verze. 60denní záruka vrácení peněz.
karta kte 201905

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!
officetab dno
Komentáře (40)
Zatím žádné hodnocení. Buďte první, kdo ohodnotí!
Tento komentář byl moderátorem webu minimalizován
Ahoj, děkuji za tento návod, funguje perfektně. Snažím se to upravit tak, aby fungovalo s podmínkou OR, ale zdá se, že to nefunguje - je to možné? např. =INDEX($B$2:$B$17, MATCH(0, IF(OR($D$2=$A$2:$A$17,$D$2=$B$2:$B$17), COUNTIF($E$1) :$E1, $B$2:$B$17), ""), 0))
Tento komentář byl moderátorem webu minimalizován
Děkuji za tento návod! Snažím se také upravit vzorec, jako výše uvedený komentátor, ale s podmínkou AND, takže splňuje další podmíněná kritéria (např. v tomto příkladu bych rád viděl pouze věci nad určitou prahovou hodnotou). Můžete prosím poradit? Děkuji!
Tento komentář byl moderátorem webu minimalizován
Hej, jeden způsob, jak to udělat: Nahraďte vzorec if sumproduct((condition1=rng1)+(condition2=rng2))*countif(... Fungovalo to pro mě. Hodně štěstí! Nahrazením + za * můžete udělejte to podmínkou NEBO, ale dobře se postarejte o závorky!
Tento komentář byl moderátorem webu minimalizován
Děkuji, to je skvělé!
Tento komentář byl moderátorem webu minimalizován
Díky za to, zkusil jsem to a zdá se, že občas funguje dobře. Problém, který se stále opakuje, je, že někdy se vrátí pouze první shodná hodnota a poté se duplikuje, když tažením dolů vrátím všechny odpovídající hodnoty. Jak tomu mohu zabránit? Nějaké návrhy?
Tento komentář byl moderátorem webu minimalizován
Funguje to opravdu dobře, ale kdykoli je hodnota, kterou vkládá, duplikována, umístí hodnotu pouze jednou. Pokud například váš seznam obsahoval dvě Lucie, přivede k novému stolu pouze jednu Lucii. Dá se to nějak opravit?
Tento komentář byl moderátorem webu minimalizován
Při použití tohoto vzorce neustále opakuje první hodnotu, jak to zastavíte a poskytnete seznam hodnot, který se rovná součinu v D2?
Tento komentář byl moderátorem webu minimalizován
Ahoj, chcete-li zastavit opakování první hodnoty při tažení dolů, musíte COUNTIF buňku NAD buňkou, do které vkládáte vzorec. Např. pokud je vzorec v E2, musíte zadat countif($E$1:$E1...
Tento komentář byl moderátorem webu minimalizován
Ahoj Ryane. Vzorce fungují skvěle, nicméně při tažení dolů se první hodnota neustále opakuje. Ujistil jsem se, že COUNTIF odkazuje na buňku NAD buňkou se vzorcem, ale stále opakuje první hodnotu při tažení dolů? (např. pokud je maticový vzorec v C2, pak COUNTIF ukazuje na buňku $C$1:$C$1)
Tento komentář byl moderátorem webu minimalizován
Ahoj Ryane. Vzorce fungují skvěle, nicméně při tažení dolů se první hodnota neustále opakuje. Ujistil jsem se, že COUNTIF odkazuje na buňku NAD buňkou se vzorcem, ale stále opakuje první hodnotu při tažení dolů? (např. pokud je maticový vzorec v C2, pak COUNTIF ukazuje na buňku $C$1:$C$1)
Tento komentář byl moderátorem webu minimalizován
Pravděpodobně to nefunguje, protože jste zamkli buňky - Zkuste nahradit $C$1:$C$1 za $C$1:$C1
Tento komentář byl moderátorem webu minimalizován
to bylo super užitečné, ale stále dostávám dvojnásobky všech jmen, jako je tento:
Doe, Jane
Doe, Jane
Hoovere, Tome
Hoovere, Tome

Jak to mohu zastavit?
Tento komentář byl moderátorem webu minimalizován
Dobrý den, dostávám chybu "#N/A" ve funkci "Match function", můžete mi prosím poradit?
Tento komentář byl moderátorem webu minimalizován
=IF(AL2="AP","AP",IF(AK2="AD","AD",IF(Z2>500000,"Ano","Ne"))) Chci, aby byly splněny "všechny podmínky" říci ano...excel odráží chybu v tomto vzorci..pls poradit
Tento komentář byl moderátorem webu minimalizován
ve skutečnosti chci, aby buňka odrážela "ANO", pokud (AL2="AP" a AK2="AD" a Z2>500000)
Tento komentář byl moderátorem webu minimalizován
Zobrazuje se mi chyba #N/A ve funkci Match s tímto vzorcem. Můžete mi prosím pomoci?
Tento komentář byl moderátorem webu minimalizován
Dobrý den, dostávám chybu "#N/A" ve funkci "Match function", můžete mi prosím poradit?
Tento komentář byl moderátorem webu minimalizován
Pokud se zobrazí chyba #N/A, přejděte ke svému vzorci a místo Enter použijte Control + Shift + Enter.
Tento komentář byl moderátorem webu minimalizován
Dostávám 0 místo očekávaných výsledků, vzorec funguje skvěle pro data ve stejném listu, máte nějaké řešení pro data v jiném listu?

toto je můj vzorec

=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)
Tento komentář byl moderátorem webu minimalizován
Ahoj Gone,
Po vložení vzorce byste měli současně stisknout klávesy Ctrl + Shift + Enter, nejen klávesu Enter.
Prosím, zkuste to, děkuji!
Tento komentář byl moderátorem webu minimalizován
Ahoj Gone, doufám, že se máš dobře. Zajímalo by mě, zda můžete tento problém vyřešit. Dostávám stejnou chybu, když vzorec pochází z jiného listu. Budu rád, když se o řešení podělíte, pokud ho máte.
Tento komentář byl moderátorem webu minimalizován
Thank You!
Tento komentář byl moderátorem webu minimalizován
Jak dosáhnu toho, aby tento vzorec vrátil každý z duplikátů místo jednoho z každého jména? Například ve výše uvedeném příkladu, jak docílím toho, aby sloupec výsledků (B:B) vrátil Lucy, Ruby, Anny, Jose, Lucy, Anny, Tom? Používám to jako rozpočtový nástroj, který získává konkrétní souhrny účtů z hlavní knihy. Některé částky a popisy transakcí jsou však duplikáty v hlavní knize. Jakmile je stažena první z duplicitních hodnot, nebudou staženy žádné další z nich.
Tento komentář byl moderátorem webu minimalizován
Ahoj, Joe,
Chcete-li extrahovat všechny odpovídající hodnoty na základě konkrétních kritérií buňky, může vám pomoci následující maticový vzorec, viz snímek obrazovky:
=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))

Po vložení vzorce stiskněte společně klávesy Shift + Ctrl + Enter, abyste získali správný výsledek, a poté přetáhněte úchyt výplně dolů, abyste získali všechny hodnoty.
Doufám, že vám to pomůže, děkuji!
Tento komentář byl moderátorem webu minimalizován
Zatím je vše dobré. Jsem schopen duplikovat výsledky v testovacím listu, provést změny v poli a poté opravit vzorec, aby zohlednil změny, které jsem provedl. Plánuji to dnes přesunout do hlavního listu a podívat se, jak to funguje. Děkuji za pomoc!
Tento komentář byl moderátorem webu minimalizován
Dobře, tak to funguje v hlavním sešitu. Existuje jedna výjimka, kterou jsem nebyl schopen určit příčinu: Pokud pole (v mém případě hlavní kniha, která začíná na řádku 3) nezačíná v řádku 1, vrácené hodnoty jsou nesprávné. Co způsobuje tento problém a který výraz ve vzorci jej řeší? Ještě jednou děkujeme za vaši pomoc!
Tento komentář byl moderátorem webu minimalizován
Poslední otázka: Pokud chci, aby sloupec výsledků vrátil všechny hodnoty, které nejsou spojeny s KTE nebo KTO (takže D:D by byl Tom, Nocol, Lily, Angelina, Genna), jak bych to udělal?
Tento komentář byl moderátorem webu minimalizován
Pro mě vzorec nefunguje. Stisknu ctrl shift enter a stále se zobrazuje chyba N/A. Rád bych dodal, že jsem připravil přesně stejná data jako v tutoriálu. Jaký je důvod, proč to nefunguje?
Tento komentář byl moderátorem webu minimalizován
To se mi osvědčilo se specifickou hodnotou vyhledávání. Pokud bych však chtěl k vyhledání dílčích hodnot použít zástupný znak, jak bych to udělal? Například, pokud bych chtěl vyhledat všechna jména spojená s KT?

Tuto funkci používám k vyhledání buněk, které obsahují více textu. Pokud by například každý produkt měl ve stejné buňce také podprodukt, ale hledal jsem pouze názvy spojené s podproduktem „elf“.

KTE - elf
KTE- míč
KTE - klavír
KTO - skřítek
KTO- míč
KTO - klavír
Tento komentář byl moderátorem webu minimalizován
Existuje způsob, jak to zajistit, aby to fungovalo a zároveň POVOLENO pro duplicitní hodnoty? Například chci, aby byly ve výsledcích uvedeny všechny výskyty Lucy.
Tento komentář byl moderátorem webu minimalizován
Dobrý den, Konstantine,
Chcete-li extrahovat všechny odpovídající hodnoty včetně duplikátů na základě konkrétních kritérií buňky, může vám pomoci následující maticový vzorec, viz snímek obrazovky:
=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))

Po vložení vzorce stiskněte společně klávesy Shift + Ctrl + Enter, abyste získali správný výsledek, a poté přetáhněte úchyt výplně dolů, abyste získali všechny hodnoty.
Doufám, že vám to pomůže, děkuji!
Zatím zde nejsou žádné komentáře
Načíst další
Zanechat své připomínky
Odesílání jako host
×
Ohodnoťte tento příspěvek:
0   Postavy
Doporučená umístění

Sociální sítě

Copyright © 2009 - www.extendoffice.com. | Všechna práva vyhrazena. Poháněno ExtendOffice. | |. | Sitemap
Microsoft a logo Office jsou ochranné známky nebo registrované ochranné známky společnosti Microsoft Corporation ve Spojených státech a / nebo jiných zemích.
Chráněno Sectigo SSL