Přejít k hlavnímu obsahu

Jak zobrazit seznam všech shodných instancí hodnoty v aplikaci Excel?

Jak je ukázáno na levém snímku obrazovky, musíte v tabulce najít a uvést všechny instance shody hodnoty „Linda“. Jak toho dosáhnout? Vyzkoušejte metody uvedené v tomto článku.

Seznam všech shodných instancí hodnoty s maticovým vzorcem
Pomocí aplikace Kutools pro Excel můžete snadno uvést pouze první shodnou instanci hodnoty

Další výukové programy pro VLOOKUP ...


Seznam všech shodných instancí hodnoty s maticovým vzorcem

S následujícím vzorcem pole můžete snadno vypsat všechny instance shody hodnoty v určité tabulce v aplikaci Excel. Postupujte prosím následovně.

1. Vyberte prázdnou buňku pro výstup první shodné instance, zadejte do ní následující vzorec a stiskněte Ctrl + směna + vstoupit současně. 

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

Poznámka: Ve vzorci je B2: B11 rozsah, ve kterém se shodné instance nacházejí. A2: A11 je rozsah, který obsahuje určitou hodnotu, na které vypsáte všechny instance. A D2 obsahuje určitou hodnotu.

2. Pokračujte ve výběru výsledné buňky, potom přetáhněte rukojeť výplně dolů a získejte další shodné instance.


Pomocí aplikace Kutools pro Excel můžete snadno uvést pouze první shodnou instanci hodnoty

Můžete snadno najít a vypsat první shodnou instanci hodnoty pomocí Vyhledejte hodnotu v seznamu funkce Kutools pro Excel aniž by si pamatoval vzorce. Postupujte prosím následovně.

Před aplikací Kutools pro Excel, Prosím nejprve si jej stáhněte a nainstalujte.

1. Vyberte prázdnou buňku, do které umístíte první shodnou instanci, a klikněte Kutools > Pomocník vzorců > Pomocník vzorců

2. V Pomocník vzorců dialogové okno, musíte:

2.1 Najít a vybrat Vyhledejte hodnotu v seznamu možnost v Vyberte vzorec krabice;
Tipy: Můžete zkontrolovat Filtr zadejte klíčové slovo do textového pole a rychle filtrujte vzorec, který potřebujete.
2.2 V Tabulka_pole v poli vyberte rozsah tabulky, který obsahuje sloupec konkrétní hodnoty i sloupec shodných instancí;
2.3 V Vzhled_hodnota V poli vyberte buňku s konkrétní hodnotou, na které se zobrazí první instance;
2.4 V Sloupec vyberte sloupec obsahující shodnou instanci. Nebo do něj jednoduše zadejte číslo sloupce;
Tipy: Číslo sloupce je založeno na vybraném počtu sloupců, pokud vyberete čtyři sloupce a toto je 3. sloupec, musíte do pole zadat číslo 3 Sloupec box.
2.5 klikněte na OK

Poté je uvedena první shodná instance dané hodnoty, jak je uvedeno níže.

  Pokud chcete mít bezplatnou (30denní) zkušební verzi tohoto nástroje, kliknutím jej stáhněte, a poté přejděte k použití operace podle výše uvedených kroků.


související články

Hodnoty Vlookup na více pracovních listech
Funkci vlookup můžete použít k vrácení odpovídajících hodnot v tabulce listu. Jak však můžete postupovat, pokud potřebujete zobrazit hodnotu vlookup na více pracovních listech? Tento článek obsahuje podrobné kroky, které vám pomohou problém snadno vyřešit.

Vlookup a návrat shodných hodnot ve více sloupcích
Za normálních okolností může použití funkce Vlookup vrátit pouze uzavřenou hodnotu z jednoho sloupce. Někdy možná budete muset na základě kritérií extrahovat shodné hodnoty z více sloupců. Zde je řešení pro vás.

Vlookup vrátí více hodnot v jedné buňce
Normálně při použití funkce VLOOKUP, pokud existuje více hodnot, které odpovídají kritériím, můžete získat pouze výsledek první. Pokud chcete vrátit všechny shodné výsledky a zobrazit je všechny v jedné buňce, jak toho dosáhnout?

Vlookup a vrátit celý řádek odpovídající hodnoty
Za normálních okolností lze pomocí funkce vlookup vrátit pouze výsledek z určitého sloupce ve stejném řádku. Tento článek vám ukáže, jak vrátit celou řadu dat na základě konkrétních kritérií.

Zpětný výhled nebo v opačném pořadí
Obecně funkce VLOOKUP prohledává hodnoty zleva doprava v tabulce polí a vyžaduje, aby vyhledávací hodnota zůstala na levé straně cílové hodnoty. Někdy však možná znáte cílovou hodnotu a chcete zjistit vyhledávací hodnotu obráceně. Proto je třeba v aplikaci Excel zpětně vyhledávat. V tomto článku existuje několik způsobů, jak tento problém snadno vyřešit!

Další výukové programy pro VLOOKUP ...

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

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...

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!
Comments (15)
Rated 5 out of 5 · 1 ratings
This comment was minimized by the moderator on the site
Is there a way to make it totla up the values it find and just put it in the first box?
This comment was minimized by the moderator on the site
Hi Bramdon,

To total up all matched values, the following formula can help. Or you can take a look at this tutorial: vlookup and sum matches in rows or columns in Excel
=SUMPRODUCT((A2:A7=A10)*B2:F7)
https://www.extendoffice.com/images/stories/doc-excel/doc-vlookup-and-sum/doc-vlookup-sum-sumif4.png
This comment was minimized by the moderator on the site
Is there a way to stop this formula returning duplicate values

Thanks
Steven
This comment was minimized by the moderator on the site
Hi Steven Waddleton,
The following two formulas can do you a favor.
Array formula in E2:
=INDEX($B$2:$B$11,MATCH(0,COUNTIF($E$1:E1,$B$2:$B$11)+($A$2:$A$11<>$D$2),0)) + Ctrl + Shift + Enter
Formula in E2:
=LOOKUP(2, 1/((COUNTIF($F$1:F1,$B$2:$B$11)=0)*($D$2=$A$2:$A$11)), $B$2:$B$11)
In the formulas, E1 is the cell above the result cell.
https://www.extendoffice.com/images/stories/comments/comment-picture-zxm/instance-no-duplicates.png
This comment was minimized by the moderator on the site
WOW! Worked perfectly, thanks a ton! Awesome formula.
Rated 5 out of 5
This comment was minimized by the moderator on the site
Nice formula. In testing it I get #NUMBER! when I clear cells A9:B11. How to change the formula to prevent getting this error? I wonder, because I want to use this formula for a list of events on a certain date and on date 1 there are 40 events, on day 2 it are 10 events etc. I could create an indexed list for perhaps 100 rows, but it looks very strange when for date 2 the first 10 rows show events and the other 90 rows show #NUMBER!. It would be okay for me if it gave "-" as a result instead of #NUMBER!
This comment was minimized by the moderator on the site
Fantastic. Thank you so much. Very helpful for my work.
This comment was minimized by the moderator on the site
Amazing. The formula worked for me exactly the way I was looking for. Hats off. Thanks a ton
This comment was minimized by the moderator on the site
Question, I have a sheet laid out opposite of this where "Linda" would be the column header I would like to flag on and the value "90" or 89" I would like to return. Would I change the row function to a column function?
This comment was minimized by the moderator on the site
How would I use this formula but instead of filling down I could fill across but the formula would continue moving down as I fill across (ie. the formula changes from ROW(1:1) to ROW(2:2)
This comment was minimized by the moderator on the site
Hi Luke,The below array formula can do you a favor. Note: After entering the formula, please press the Ctrl + Shift + Enter key to apply it. And then drag its Fill Handle right across the cells you need. =INDEX($B$2:$B$11, SMALL(IF($D$2=$A$2:$A$11, ROW($A$2:$A$11)-ROW($A$2)+1), COLUMN(A1)))
This comment was minimized by the moderator on the site
A very useful and educational formula. I am matching against a set numeric value and it works a treat. How can I use >= rather than just = (i.e. IF($D$2>=$A$2:$A$11) to perform the match so it is looking for values greater than a set value (in $D$2). I assume as it doesn't work as one of the functions in the formula is matching against specific text rather than working with numbers?
This comment was minimized by the moderator on the site
This was super useful, thanks! I'm trying to take this one step further and be able to return all match instances of a certain value while having to search through more than a single-column array. To work through this using your example, I added a second column of test scores and modified your formula to look up a given test score and return the names that match that score. I got this to work with INDEX(SMALL()) and can pull all of the names from both test columns. I've also managed to return only names with that score on Test 2 using INDEX(MATCH(INDEX(MATCH))), however this can only find the first instance in the array. What I'm really trying to do is a combination of these: return all of the names with the given array, while narrowing the search to a specific column within the array. Do you have any tips for this?
This comment was minimized by the moderator on the site
Good day,
Would you please provide a screenshot of your spreadsheet showing what you are exactly trying to do? Thank you for your comment.
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