Přeskočit navigaci | Přeskočit na novinky
     

Jste zde: Úvodní stránka » excel » data » filtry-bez-filtru-excel
Microsoft Excel logo

Filtry bez filtrů

Videokurzy Excel

Ve spolupráci se SEDUO jsem vytvořil několik videokurzů:

Jak filtrovat bez využití filtrů, aneb využití pár funkcí a můžete mít naprogramovány vlastní filtry ;)

Reaguje na jeden zajímavý dotaz v emailu a dlouhou cestu do Prahy, tak jsem si trochu pohrál s funkcemi a napsal tento článek.

Úvodem

Microsoft Excel VBA - stahuj logo

Cílem článku je ukázat netradiční použití funkcí, které nahradí sofistikovaný filtr v Excel. Cílem je do příslušných buněk zadat hledané veličiny a pokud se v dané tabulce nacházejí, tak budou vypsány.

Pro přehlednost je článek rozdělen:

Ukázka

Jak může vypadat:

MS Excel 2010 - Úvodní obrazovka

Poznámka: Pro lepší pochopení doporučuji stáhnou přiložený excelovský sešit.

Předpokládané znalosti

Při tvorbě předpokládám, že máte mírně pokročilé znalosti Excel a umíte používat řetězení funkcí a také smíšené odkazy. Předpokládám tyto znalosti:

Pokud umíte (zvládate), můžete se vrhnout na vyřešení, článek vám bude nápomocen (případně si můžete rovnou stáhnout hotovou ukázku, včetně řešení).

Zdrojová data a vyhledávací buňky

Zdrojová tabulka bude obsahovat například ID (pořadí), Jméno, Plat, Datum, Čas

ID Jméno Plat Datum Čas
1 Pavel 15 000 4.12.2015 15:10
2 Ivo 17 000 4.12.2015 16:20
3 Ivos 20 000 7.12.2015 11:10
4 Karel 10 000 7.6.2015 17:47
5 Karel Voršálek 15 000 1.1.2014 5:01

A doplním o "tabulku" co hledám.

ID Jméno Plat Datum Čas
         

Pomocná tabulka hledání

Postup řešení. Zdrojová tabulka se doplní o pomocnou tabulku, která pomůže určit, zda daný řádek patří do hledaného rozsahu (odpovídá, hledaným hodnotám).

Microsoft Excel ukazka reseni

Nejprve pro každý sloupec zdrojové tabulky samostatně určíme, zda odpovídají hledaným kritériím. Následně spočteme počet shodných hodnot v řádku. Pokud je počet v řádku shodný s počtem hledaných hodnot zapíše se hodnota daného řádku do dalšího pomocného sloupce. Následně seřadíme čísla řádku, tak ˇAt neobsahují prázdné řádky. Následně již stačí vyplnit cílovou tabulku.

Microsoft Excel ukazka reseni

Podrobněji o použitých funkcích

Pomocí této tabulky zjistíte, zda daná položka se shoduje s hledáním (hledanou hodnotou), využijete funkce COUNTIF

=COUNTIF(A18;$A$6)

V dalším pomocném sloupci sečtete (chcete zobrazit jen řádky, které ve všech ohledech splňují hledaná kritéria).

=SUMA(G18:K18)

Ve dalším pomocném sloupci pořadí zobrazíme ID sloupců, které splňují všechna kriéria.

=KDYŽ(M18=$M$6;A18;"")

Počet vyplněných sloupců je z tabulky ve které je hledání. Jelikož někdy potřebujete hledat jen podle jména, někdy podle jména a platu, atd.

=POČET2(A6:E6)

Posledním pomocným sloupcem je sumarizace pořadí, využijete funkce SVYHLEDAT a POSUN. Lze řešit i jinak, ale já zvolil funkci POSUN, mám já rád, tak proč jí nepoužít ;) šlo by doplnit o funkci, která ošetří chybové hodnoty pokud nebude nalezeno, ale to nechám už na vás, pro další postup chybová hodnota nevadí.

=SVYHLEDAT($M$6;M18:N22;2;NEPRAVDA) =SVYHLEDAT($M$6;(POSUN($M$18:$N$22;O18;0));2;NEPRAVDA)

Takže máme ID (pořadí) řádku splňující požadovaná kritéria. Teď už zbývá jen vytvořit tabulku výsledku, na to se podíváte v další kapitole.


Výsledná tabulka

Z předchozí kapitoly máte hotové pořadová čísla, teď už není nic jednoduššího než si vypsat řádky, které splňují naše požadavky. Využijeme funkci INDEX (lze i SVYLHEDAT), ale ať se procvičíte ;)

No a aby nebylo potřeba tvořit pro každý sloupce, lze využít smíšené odkazování. Funkce je doplněna o IFERROR, aby nenalezené řádky ve výsledné tabulce byly prázdné. V této tabulce obsahují mezerník.

=IFERROR(INDEX(A$18:A$22;$O18;1);" ")

Povedlo se? Pokud ne můžete si v další kapitole stáhnout hotovou ukázku.


Microsoft Excel VBA - stahuj logo

Ke stažení

Soubor Filtry bez filtrů - Excel soubor ve formátu *.xlsx ke stažení zdarma.


Související články

Nehcete se mořit s vlastní imitací filtrů, můžete využít filtry zabudované přímo v Excel:

Závěrem

Máte inspirativní příklad různého použití funkci? Můžete zmínit v komentářích.

Článek byl aktualizován: 07.12.2015 18:08

Odměna

Ušetřil vám tento web čas, peníze? Pomohl vyřešit problém? Jste ochotni poskytnout symbolickou odměnu na další rozvoj? Vybrte si formu odměny, která vám vyhovuje.


Pavel Lasák

Pavel Lasák - autor webu

Microsoft Office (Word, Excel, PowerPoint) se věnuji od roku 2006. Své vědomosti a zkušenosti dávám k dispozici na různých školeních a konzultacích, ale také na tomto webu. K dispozici na tomto webu je mnoho návodu, tipů a triků včetně desítek různých šablon.

   Pavel Lasák LinkedIn Profil    Pavel Lasák Google+ Profil    Pavel Lasák facebook Profil    Pavel Lasák twitter Profil

Doporučte tento článek přátelům

Pokud vám článek pomohl, případně si myslíte, že může pomoci i někomu dalšímu, budu rád když jej sdílením doporučíte přátelům - děkuji:



Komentáře


Michal

Přidáno: 06.12.15 20:44

Vďaka za Vaše stránky , rád ich čítam a skúšam príklady. Tešil som sa aj na dnešné "filtre" , ale je to na mňa komplikované, preto som chcel skúsiť download nefunguje ... (stránka nenalezena...). Pls opravíte to ? Prajem všetko dobré.

Pavel Lasák

Přidáno: 06.12.15 21:34

To Michal: Děkuji, opraveno již funguje.

xlnc

Přidáno: 06.12.15 21:53

A... cože? Jo, to asi není inspirativní příspěvek, ale já mám prostě otevřenou pusu a nevím, o čem je ten článek.

Michal

Přidáno: 06.12.15 21:57

Paráda. Vy ste teda hlavička. Nech sa Vám darí (nie len v EXCEL-i).

Pavel Lasák

Přidáno: 07.12.15 08:55

To xlnc: Tohle není inspirativní článek ;) Jde o "vtípek" jak si lze hrát s funkcemi. Proto je ke stažení excelovský sešit. Zkusím ještě doplnit o další informace ať je popis pochopitelnější. Děkuji.

Peter

Přidáno: 07.12.15 22:41

Super nápad je vynikajúci ako študijný materiál.

Michal

Přidáno: 08.12.15 08:55

Mne by v práci pomohlo, ak by som mal možnosť filtrovať údaje z cca 3 (hárkov), každý má cez 800 riadkov (a pribúdajú ďalšie) s tým, že kritériom pre zoradenie by bol len jeden jediný parameter (číslo: v tvare napr. 123/2014) Ten "filter" by bol na jednom liste (hárku) a tie výsledky by sa mi v reálnom čase zobrazovali (z tých 3 iných). Vie niekto ako na to ? ... a všetkým prajem pokojný predvianočný čas.

Pavel Lasák

Přidáno: 09.12.15 13:54

To Michal: Nelze využít sloučení v kontingenčních tabulkách? http://office.lasakovi.com/excel/kontingencni-tabulka/kontingencni-tabulka-rozdelene-tabulky-dat-excel/

Michal

Přidáno: 10.12.15 11:48

Musím si tie KT viac naštudovať .... vyskúšam. Každopádne vďaka za radu.

Michal

Přidáno: 21.12.15 11:50

Tak som svoj problém filtrovania vyriešil ... , na každom hárku (liste) som pripojil rozšírený filter a na vstupnom hárku, kde sa mi majú zobrazovať filtrované dáta som umiestnil tlačítko ktoré mi za pomoci makra jednoducho skopíruje vyfiltrované dáta zo všetkých troch hárkov vedľa seba. Ide to ako má. Určite inšpirované aj Vašimi stránkami. Vďaka.

Tonda

Přidáno: 25.07.16 14:22

Já díky vám začínám milovat Excel?







Sdílejte

Pomohl Vám návod?
Sdílejte na Facebooku, G+
LinkedIn...

Nové články


Newsletter

Přihlaste se odběru novinek a získejte:
Ebook zdarma -
10 kroků ke zvládnutí (Word, Excel, PowerPoint)
Šipka Kniha 3D Více o ebooku ...

TOPlist Licence Creative Commons webarchiv rss XML

Stránky o MS Office (Excel) produktu společnosti Microsoft. Neslouží jako technická podpora.
| Email na autora: pavel.lasak@gmail.com | Copyright © : Pavel Lasák 2004 - 2017 | 2824

Tento web zatím neprošel jazykovou korekturou. Beta verze redakčního systému.