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

Jak vrátit více hodnot shody na základě jednoho nebo více kritérií v aplikaci Excel?

Za normálních okolností je vyhledání konkrétní hodnoty a vrácení odpovídající položky pro většinu z nás snadné pomocí funkce VLOOKUP. Ale pokusili jste se někdy vrátit více hodnot shody na základě jednoho nebo více kritérií, jak ukazuje následující snímek obrazovky? V tomto článku představím několik vzorců pro řešení tohoto složitého úkolu v aplikaci Excel.

Vraťte více shodných hodnot na základě jednoho nebo více kritérií s maticovými vzorci


Vraťte více shodných hodnot na základě jednoho nebo více kritérií s maticovými vzorci

Například chci extrahovat všechna jména, jejichž věk je 28 let a pocházejí ze Spojených států, použijte následující vzorec:

1. Zkopírujte nebo zadejte následující vzorec do prázdné buňky, kde chcete najít výsledek:

=INDEX($B$2:$B$11, SMALL(IF(COUNTIF($F$2, $C$2:$C$11)*COUNTIF($G$2, $D$2:$D$11), ROW($A$2:$D$11)-MIN(ROW($A$2:$D$11))+1), ROW(A1)), COLUMN(A1))

Poznámka: Ve výše uvedeném vzorci, B2: B11 je sloupec, ze kterého je vrácena odpovídající hodnota; F2, C2: C11 jsou první podmínka a data sloupce, která obsahuje první podmínku; G2, D2: D11 jsou druhá podmínka a data sloupce, která tuto podmínku obsahují, změňte je prosím podle potřeby.

2. Poté stiskněte tlačítko Ctrl + Shift + Enter klávesy pro získání prvního výsledku shody a poté vyberte první buňku vzorce a přetáhněte rukojeť výplně dolů do buněk, dokud se nezobrazí chybová hodnota, nyní se všechny odpovídající hodnoty vrátí, jak je znázorněno níže:

Tipy: Pokud potřebujete pouze vrátit všechny odpovídající hodnoty na základě jedné podmínky, použijte následující vzorec pole:

=IFERROR(INDEX($B$2:$B$11, SMALL(IF($F$2=$D$2:$D$11, ROW($D$2:$D$11)-ROW($D$2)+1), ROW(1:1))),"" )


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

  • Vraťte více hodnot vyhledávání v jedné buňce oddělené čárkami
  • V aplikaci Excel můžeme použít funkci VLOOKUP k vrácení první shodné hodnoty z buněk tabulky, ale někdy musíme extrahovat všechny odpovídající hodnoty a poté je oddělit konkrétním oddělovačem, například čárkou, pomlčkou atd ... do jednoho buňka jako následující snímek obrazovky. Jak bychom mohli získat a vrátit více hodnot vyhledávání v jedné buňce oddělené čárkami v aplikaci Excel?
  • Vlookup a vrácení více odpovídajících hodnot najednou v tabulce Google
  • Normální funkce Vlookup v listu Google vám pomůže najít a vrátit první odpovídající hodnotu na základě daných dat. Ale někdy možná budete muset vlookup a vrátit všechny odpovídající hodnoty, jak ukazuje následující snímek obrazovky. Máte nějaké dobré a snadné způsoby, jak vyřešit tento úkol v listu Google?
  • Vlookup a návrat více hodnot z rozevíracího seznamu
  • V aplikaci Excel, jak byste mohli vlookup a vrátit více odpovídajících hodnot z rozevíracího seznamu, což znamená, že když vyberete jednu položku z rozevíracího seznamu, zobrazí se všechny její relativní hodnoty najednou, jak ukazuje následující snímek obrazovky. V tomto článku představím řešení krok za krokem.
  • Vlookup a vrátit více hodnot vertikálně v aplikaci Excel
  • Za normálních okolností můžete použít funkci Vlookup k získání první odpovídající hodnoty, ale někdy chcete vrátit všechny odpovídající záznamy na základě konkrétního kritéria. V tomto článku budu hovořit o tom, jak vlookup a vrátit všechny odpovídající hodnoty svisle, vodorovně nebo do jedné buňky.
  • Vlookup a návrat odpovídajících dat mezi dvěma hodnotami v aplikaci Excel
  • V aplikaci Excel můžeme použít normální funkci Vlookup k získání odpovídající hodnoty na základě daných dat. Ale někdy chceme vlookup a vrátit odpovídající hodnotu mezi dvěma hodnotami, jak ukazuje následující snímek obrazovky, jak byste se mohli s touto úlohou vypořádat v aplikaci Excel?

 


  • 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 a uchovávání údajů; Rozdělit obsah buněk; Zkombinujte duplicitní řádky a součet / průměr... 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ší ...
  • Oblíbené a rychlé vkládání vzorců„Rozsahy, grafy a obrázky; Šifrovat buňky s heslem; Vytvořte seznam adresátů a posílat e-maily ...
  • 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...
  • Seskupování kontingenčních tabulek podle číslo týdne, den v týdnu a další ... Zobrazit odemčené, zamčené buňky různými barvami; Zvýrazněte buňky, které mají vzorec / název...
karta kte 201905
  • 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 (25)
Zatím žádné hodnocení. Buďte první, kdo ohodnotí!
Tento komentář byl moderátorem webu minimalizován
Zkoušel jsem přesně stejný vzorec; 100% zkopírováno. Jediné, co jsem změnil, bylo spárování a vrácení dat. Když použiji tento vzorec, Excel říká: "Zadali jste příliš mnoho argumentů pro tuto funkci).=INDEX('2020 Volume Report'!$B$3:$B$100,SMALL(IF(COUNTIF($A$1,'2020 Volume) Zpráva'!$A$3:$A$100)*COUNTIF($A$3,'Zpráva o objemu 2020'!$D$3:$D$100),ROW('Zpráva o objemu 2020'!$A$3:$G$100)- MIN(ŘÁDEK('Hlášení objemu 2020'!$A$3:$G$100))+1,"0"),ŘÁDEK(A1);SLOUPEK(A1))
Tento komentář byl moderátorem webu minimalizován
Ahoj, mohl bys sem dát své údaje a chybu vzorce jako snímek obrazovky?
Tento komentář byl moderátorem webu minimalizován
Dobrý den, jak jej mohu použít pro horizontální stav.
Tento komentář byl moderátorem webu minimalizován
Jaká je "0" za +1 ve vzorci? To v tom příkladu není.
Tento komentář byl moderátorem webu minimalizován
Ahoj, zkusil jsem stejný vzorec. dostávám výsledek, ale když zadáte CSE, neposkytuje žádné vícenásobné odpovědi
Tento komentář byl moderátorem webu minimalizován

Tento komentář byl moderátorem webu minimalizován
Pokud jde o vrácení vícenásobných shodných hodnot na základě jednoho nebo více kritérií s maticovými vzorci: Proč to, že když mám data kdekoli jinde kromě začínajících v A1, nefunguje, i když aktualizuji všechny odkazy na buňky ve vzorci?
Tento komentář byl moderátorem webu minimalizován
V prvním příkladu, jaká změna vzorce by byla potřeba, aby se vrátili všichni, kterým bylo méně než 28 let?
Tento komentář byl moderátorem webu minimalizován
Dobrý den,

Zajímalo by mě, jestli je vůbec možné zadat 2. kritérium, ale ze stejného rozsahu jako 1. kritérium,

Například s použitým příkladem výše bych chtěl vyhledat jména lidí z Ameriky i Francie. Buňka F3 by tedy měla Francii, Scarlett & Andrew by se také vyplnila v seznamu ve sloupci G

Za pomoc předem děkujeme.
Tento komentář byl moderátorem webu minimalizován
Dobrý den Nick,

Rád jsem pomohl. Pokud chcete získat jména lidí z Ameriky i Francie, doporučuji vám použít náš vzorec dvakrát, abyste získali výsledek. Podívejte se prosím na snímek obrazovky, V F2 a G2 jsou hodnoty "Spojené státy americké" a "Francie". Použít vzorec =IFERROR(INDEX($B$2:$B$11, SMALL(IF($F$2=$D$2:$D$11, ŘÁDEK($D$2:$D$11)-ŘÁDEK($D$2)+1) ), ROW(1:1))),"" ), abyste získali výsledky pro Ameriku. A použijte vzorec =IFERROR(INDEX($B$2:$B$11, SMALL(IF($G$2=$D$2:$D$11, ŘÁDEK($D$2:$D$11)-ŘÁDEK($D$2)+ 1), ROW(1:1))),"" ), abyste získali výsledky pro Francii. Je to jednoduché. Zkuste to prosím.

S pozdravem,
Mandy
Tento komentář byl moderátorem webu minimalizován
Když použiji druhý vzorec a přetáhnu dolů, nic se nezobrazí. Výsledek vzorce (fx) říká, že by měl něco vracet, ale je prázdný. Jak to napravím?
Tento komentář byl moderátorem webu minimalizován
Ahoj Alysie,

Rád jsem pomohl. Zkusil jsem druhý vzorec v článku a přetáhl vzorec dolů, zbytek výsledků se vrátil. Myslím, že váš problém může mít dva důvody. Nejprve možná zapomenete zadat vzorec stisknutím kláves Ctrl + Shift + Enter. Za druhé, výsledek shody je pouze jeden, takže žádné další výsledky nejsou vráceny. Prosím o kontrolu.

S pozdravem,
Mandy
Tento komentář byl moderátorem webu minimalizován
Ahoj,
Zkoušel jsem použít vzorec a buď vygeneruje hodnotu 0 nebo přiložený obrázek
Tento komentář byl moderátorem webu minimalizován
Ahoj Milku
Váš snímek obrazovky ukázal software WPS verze MAC, takže si nejsem jistý, zda je náš vzorec k dispozici.
Nahrál jsem sem soubor Excel, můžete zkusit, zda to ve vašem prostředí dokáže správně vypočítat.
Děkuji!
Tento komentář byl moderátorem webu minimalizován
Ahoj,
co by bylo potřeba k rozšíření prvního vzorce v následujícím případě:
Některá ID jsou prázdná (např. buňka A5 je prázdná) a chtěl bych další podmínku vypisující řádky pouze v případě, že ID nejsou prázdná. (Výstup by tedy měl být James a Abdul.
Díky!
Tento komentář byl moderátorem webu minimalizován
Ahoj Jo,
Chcete-li vyřešit svůj problém, použijte níže uvedený vzorec:
=INDEX($B$2:$B$11, SMALL(IF(COUNTIF($F$2, $C$2:$C$11)*COUNTIF($G$2, $D$2:$D$11)*($A$2:$A$11<>0), ROW($A$2:$D$11)-MIN(ROW($A$2:$D$11))+1), ROW(A1)), COLUMN(A1))

Prosím o odpověď, doufám, že vám to pomůže!
Tento komentář byl moderátorem webu minimalizován
Dobrý den,

pokud do buňky H1 napíšu "Jméno" a chtěl bych to propojit se vzorcem, jak by to fungovalo?
Pak bych mohl napsat "ID" do buňky H1 a automaticky by dostal: AA1004; DD1009; PP1023 (pro první vzorec)

Děkuji předem!
Tento komentář byl moderátorem webu minimalizován
Dobrý den, Marie
Promiňte, nechápu smysl vašeho prvního problému, mohl byste svůj problém vysvětlit jasněji a podrobněji? Nebo sem můžete vložit snímek obrazovky, který popíše váš problém.
Pokud jde o druhou otázku, stačí změnit odkaz na buňku takto:
=INDEX($A$2:$A$11, SMALL(IF(COUNTIF($F$2, $C$2:$C$11)*COUNTIF($G$2, $D$2:$D$11), ROW($A$2:$D$11)-MIN(ROW($A$2:$D$11))+1), ROW(A1)), COLUMN(A1))

Nezapomeňte stisknout Ctrl + Shift + Enter klíče dohromady.
Zkuste to prosím, doufám, že vám to pomůže!
Tento komentář byl moderátorem webu minimalizován
Ahoj, děkuji za vzorec. Fungovalo to pro "pevné" hodnoty / text jako kritéria. Nicméně jedním z kritérií, které se snažím použít, je podmínka (hodnoty <>0 ), ale nefunguje popsaný vzorec. Nevíte, prosím, co bych měl změnit, abych upravil vzorec, abych mohl mít podmínku jako jedno z kritérií?

Nejlepší,

John
Tento komentář byl moderátorem webu minimalizován
Ahoj, Marcusi
Chcete-li vyřešit svůj problém, přečtěte si tento článek:
https://www.extendoffice.com/documents/excel/6393-excel-vlookup-function.html#b3-2
Existuje několik podrobných vysvětlení tohoto úkolu. Stačí si změnit kritéria na své vlastní.
Děkuji!
Tento komentář byl moderátorem webu minimalizován
Dobrý den,

Nejprve děkujeme za sdílení!

Můžete prosím poskytnout řešení níže uvedeného případu:

Mám 3 sloupce (A: obsahující referenční informace, B: obsahující informace k prohledávání, C: výsledek hledání)

Adresa URL obrázku je uvedena níže

https://ibb.co/VHCd09K

Sloupec A-------------------------Sloupec B------------Sloupec C
Název souboru-------------------------Název---------------- Název souboru, Název dokumentu, Název prvku, název
Změněný prvek-----------------Prvek--------------Změněný prvek, název prvku, ID prvku
Umístění sloupce
Název dokumentu
Název prvku
Název produktu
Kategorie
Záruka
Sklon
ID prvku

Potřebuji vyhledat ve sloupci A jakoukoli částečnou shodu s buňkou B2 (Název) nebo B3 (Prvek) a získat výsledek v jedné buňce,

Děkuji, Behzade
Tento komentář byl moderátorem webu minimalizován
Ahoj, Behzade
Možná vám může pomoci níže uvedená uživatelsky definovaná funkce.
Public Function ConcatPartLookUp(rngInput As Range, rngSource As Range, Optional strDelimiter As String, Optional blCaseSensitive)
Dim rng As Range
If strDelimiter = "" Then strDelimiter = ","
If IsMissing(blCaseSensitive) Then
    blCaseSensitive = False
Else
    blCaseSensitive = True
End If
For Each rng In rngSource
    If blCaseSensitive Then
        If InStr(1, rng.Value, rngInput.Value, vbBinaryCompare) > 0 Then ConcatPartLookUp = ConcatPartLookUp & strDelimiter & rng.Value
    Else
        If InStr(1, rng.Value, rngInput.Value, vbTextCompare) > 0 Then ConcatPartLookUp = ConcatPartLookUp & strDelimiter & rng.Value
    End If
Next
If Len(ConcatPartLookUp) > 0 Then ConcatPartLookUp = Mid(ConcatPartLookUp, 2, Len(ConcatPartLookUp))
End Function


Po zkopírování a vložení tohoto kódu a poté použijte tento vzorec:=ConcatPartLookUp(B2,$A$2:$A$8) abyste získali výsledek, který potřebujete.
Zkuste to prosím, doufám, že vám to pomůže!
Tento komentář byl moderátorem webu minimalizován
Dobrý den,

Děkujeme za zveřejnění těchto příkladů.
Snažím se to implementovat do svého vlastního listu, ale nedaří se mi to (možná proto, že používám evropskou verzi excelu)?

Chci získat data dnů, kdy jsem měl směny nebo kdy jsem pro klienta odpracoval „nějaké“ (>0) hodiny.

Takže v I3 je název a v J3 měsíc. K3 a L3 jsou směny (1 je odpracována) a hodiny (nevím, jak to nastavit, mělo by být více než nula)

Mé očekávané výsledky jsou:
Směny: I7 a I8
hodiny: J7

Takže jsem v říjnu 0-2-3 pracoval více než 10 hodin pro „osobu 2022“
měl směny pro osobu 2 na '10-10-2022' a 28-10-2022

Když přidám '=INDEX($B$2:$B$11, SMALL(IF(COUNTIF($F$2, $C$2:$C$11)*COUNTIF($G$2, $D$2:$D$11), ŘÁDEK ($A$2:$D$11)-MIN(ROW($A$2:$D$11))+1), ŘÁDEK(A1)), COLUMN(A1))' v mém listu Excel, neumožňuje čárka mezi různými částmi vzorce.
Takže je musím změnit na ';'.
Ale když to zkouším, vždy to říká: '#JMÉNO?'

Může mi s tím tedy někdo pomoci?

Se srdečným pozdravem,

Bas
[img]https://drive.google.com/file/d/1iIPQKuj_PNhqWyWlwJ4IQTqGNEd6B9Hw/view?usp=share_link[/img]
Tento komentář byl moderátorem webu minimalizován
Ahoj, pokud existují duplicitní hodnoty (např. dva adamy), jak zajistím, že vrátí pouze 1 adam a ne 2?
Tento komentář byl moderátorem webu minimalizován
Dobrý den, Bobby,
Chcete-li extrahovat pouze jedinečné odpovídající hodnoty, měli byste použít níže uvedený vzorec:
Po vložení vzorce stiskněte Ctrl + Shift + Enter dohromady, abyste dosáhli správného výsledku.
=IFERROR(INDEX($B$2:$B$5, MATCH(0; COUNTIF(H1:$H$1, $B$2:$B$5)+IF($D$2:$D$5<>$G$2, 1) , 0)+IF($C$2:$C$5<>$F$2; 1; 0); 0)), "")

Zkuste to prosím, doufám, že vám to pomůže!
Zatím zde nejsou žádné komentáře
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