Načítání dat v aplikaci Microsoft Excel

Při práci s tabulkami aplikace Excel je často nutné je vybrat podle určitého kritéria nebo několika podmínek. V programu můžete toto provést různými způsoby pomocí několika nástrojů. Zjistěte, jak provést výběr v aplikaci Excel pomocí různých možností.

Ukázka provedení

Odběr vzorků spočívá v postupu výběru výsledků z obecných polí, které splňují zadané podmínky, a následné výstupy na listu v samostatném seznamu nebo v původním rozsahu.

Metoda 1: Použití pokročilého automatického filtrování

Nejjednodušším způsobem výběru je použití rozšířeného automatického filtrování. Zvažme, jak to udělat na konkrétním příkladu.

1. Vyberte oblast na listu, mezi kterou je třeba vybrat. Na kartě Domů klikněte na tlačítko Seřadit a filtrovat . Je umístěn v bloku nastavení "Upravit" . V seznamu, který se poté otevře, klikněte na tlačítko Filtr .

   Existuje příležitost udělat to samé jiným způsobem. Chcete-li to provést, po výběru oblasti na listu přejděte na kartu "Data" . Klepneme na tlačítko "Filtr" , které je umístěno na pásu ve skupině "Seřadit a filtrovat" .

2. Po této akci se v hlavičce tabulky objeví ikony, které zahájí filtrování ve formě malých obrácených trojúhelníků na pravém okraji buněk. Klikneme na tuto ikonu v názvu sloupce, který chceme vybrat. V nabídce, která se otevře, přejděte na položku "Filtry textu" . Poté vyberte položku "Vlastní filtr ..." .
3. Okno pro filtrování uživatelů je aktivováno. V něm můžete zadat omezení, podle kterého bude výběr proveden. V rozevíracím seznamu pro sloupec obsahující buňku číselného formátu, který používáme pro příklad, můžete zvolit jeden z pěti druhů podmínek:
   • se rovná;
   • nerovná;
   • více;
   • větší nebo roven;
   • méně.

   Nastavíme stav jako příklad tak, abychom vybrali pouze hodnoty, jejichž výše přesahuje 10 000 rublů. Nastavte přepínač na pozici "Více" . Do pravého pole zadejte hodnotu "10000" . Chcete-li akci provést, klikněte na tlačítko "OK" .

4. Jak vidíte, po filtraci existovaly pouze linky, jejichž výše výnosů přesahuje 10 000 rublů.
5. Ale ve stejném sloupci můžeme přidat druhou podmínku. Za tímto účelem se opět vrátíme do okna filtrování uživatelů. Jak vidíte, ve spodní části je jeden spínač stavu a jeho odpovídající vstupní pole. Nyní nastavíme horní limit výběru na 15 000 rublů. Chcete-li to provést, přepněte přepínač do polohy "Méně" av poli vpravo zadáme hodnotu "15000" .

   Kromě toho je k dispozici také přepínač stavu. Má dvě pozice "já" a "OR" . Ve výchozím nastavení je nastavena na první pozici. To znamená, že ve vzorku budou pouze linky, které splňují obě omezení. Je-li nastavena na pozici "OR" , zůstanou hodnoty, které jsou vhodné pro jednu ze dvou podmínek. V našem případě je třeba nastavit přepínač do polohy "AND" , to znamená, že toto nastavení zůstane výchozí. Po zadání všech hodnot klikněte na tlačítko "OK" .

6. Nyní se v tabulce ponechaly pouze řádky, jejichž výše výnosu není menší než 10 000 rublů, ale nepřesahuje 15 000 rublů.
7. Stejně tak můžete nakonfigurovat filtry v dalších sloupcích. V tomto případě je také možné uložit filtrování za předchozích podmínek, které byly nastaveny ve sloupcích. Takže, podívejme se, jak je filtr vytvořen pro buňky ve formátu dat. Klikněte na ikonu filtru v příslušném sloupci. Důsledně klikněte na položky v seznamu "Filtrovat podle data" a "Vlastní filtr" .
8. Okno Custom AutoFilter je znovu spuštěno. Výběru výsledků provedeme v tabulce od 4. do 6. května 2016 včetně. V přepínači stavu, jak vidíte, je ještě více možností než v číselném formátu. Zvolte pozici "Po nebo Equal . " V poli vpravo nastavte hodnotu na "04/05/2016" . V dolním bloku nastavte přepínač do polohy "Před nebo rovnou" . V pravém poli zadejte hodnotu "06.05.2016" . Přepínač kompatibility podmínek zůstává ve výchozí poloze - "AND" . Chcete-li použít filtrování v akci, klikněte na tlačítko "OK" .
9. Jak můžete vidět, náš seznam byl dále snížen. Nyní se ponechávají pouze řádky, ve kterých se výše výnosů pohybuje od 10 000 do 15 000 rublů za období od 04.05 do 06.05.2016 včetně.
10. Filtrování můžeme obnovit v jednom ze sloupců. Udělejte to pro údaje o příjmech. Klikněte na ikonu automatického filtrování v příslušném sloupci. V rozevíracím seznamu klepněte na položku "Odstranit filtr" .
11. Jak je vidět, po těchto akcích bude vzorek podle výtěžku zakázán a bude k dispozici pouze výběr podle dat (od 04/05/2016 do 06/05/2016).
12. V této tabulce je ještě jeden sloupec - "Jméno" . Obsahuje data v textovém formátu. Podívejme se, jak vygenerovat vzorek filtrováním těchto hodnot.

    Klikněte na ikonu filtru v názvu sloupce. Projdeme jména seznamu "Filtry textu" a "Vlastní filtr ..." .

13. Opět se otevře okno Vlastní automatický filtr. Udělejme si výběr na jménech "Brambory" a "Maso" . V prvním bloku nastavte přepínač stavu do polohy "Rovná" . V poli, napravo od ní, zadáváme slovo "brambor" . Spínač spodního bloku je také umístěn v poloze "Equal" . Na poli naproti tomu učiníme poznámku - "Maso" . A pak provedeme to, co jsme předtím neudělali: nastavte přepínač kompatibility stavu do polohy "OR" . Nyní se zobrazí řádek obsahující některé z uvedených podmínek. Klepněte na tlačítko "OK" .
14. Jak vidíte, v novém vzorku existují omezení na datum (od 04/05/2016 do 06/05/2016) a podle názvu (brambory a maso). Výše výnosu není omezena.
15. Úplně odstranit filtr mohou být stejné metody, které byly použity k jeho instalaci. A nezáleží na tom, která metoda byla použita. Chcete-li obnovit filtrování, klikněte na kartě "Data" na tlačítko "Filtr" , které se nachází ve skupině "Seřadit a filtrovat" .

    Druhá možnost zahrnuje přepnutí na kartu Domovská stránka . Klepněte na pásu karet "Třídit a filtr" v bloku "Upravit" . V aktivovaném seznamu klikněte na tlačítko Filtr .

Pokud použijete některou z výše uvedených metod, bude filtrování odstraněno a výsledky výběru budou vymazány. To znamená, že tabulka zobrazí celou řadu dat, která má.

Lekce: Funkce automatického filtrování v aplikaci Excel

Metoda 2: Použijte vzorec pole

Můžete také provést výběr použitím komplexního vzorce pole. Na rozdíl od předchozí verze tato metoda poskytuje výstup výsledku v samostatné tabulce.

1. Na stejném listu vytvoříme prázdnou tabulku se stejnými názvy sloupců v záhlaví jako zdroj.
2. Vyberte všechny prázdné buňky prvního sloupce nové tabulky. Nastavte kurzor na liště vzorců. Právě zde bude zadán vzorec, který provede vzorek podle zadaných kritérií. Vybíráme linky, jejichž výnos přesahuje 15 000 rublů. V našem konkrétním příkladu bude vstupní vzorec vypadat takto:

   =ИНДЕКС(A2:A29;НАИМЕНЬШИЙ(ЕСЛИ(15000<=C2:C29;СТРОКА(C2:C29);"");СТРОКА()-СТРОКА($C$1))-СТРОКА($C$1))

   Samozřejmě, v každém případě bude adresa buněk a rozsahů stejná. V tomto příkladu můţete na obrázku odpovídat vzorec se souřadnicemi a přizpůsobit je vašim potřebám.

3. Vzhledem k tomu, že se jedná o vzorec pole, aby bylo možné jej použít v akci, musíte stisknout klávesu Enter a kombinaci kláves Ctrl + Shift + Enter . Děláme to.
4. Vyberte druhý sloupec s daty a vložte kurzor do řádku vzorce a zadejte následující výraz:

   =ИНДЕКС(B2:B29;НАИМЕНЬШИЙ(ЕСЛИ(15000<=C2:C29;СТРОКА(C2:C29);"");СТРОКА()-СТРОКА($C$1))-СТРОКА($C$1))

   Stiskněte kombinaci kláves Ctrl + Shift + Enter .

5. Podobně ve sloupci s výnosy uvedeme následující vzorec:

   =ИНДЕКС(C2:C29;НАИМЕНЬШИЙ(ЕСЛИ(15000<=C2:C29;СТРОКА(C2:C29);"");СТРОКА()-СТРОКА($C$1))-СТРОКА($C$1))

   Zadejte znovu Ctrl + Shift + Enter .

   Ve všech třech případech se změní pouze první hodnota souřadnic a jinak jsou vzorce zcela totožné.

6. Jak je vidět, tabulka je vyplněna daty, ale její vzhled není příliš atraktivní, navíc data data jsou vyplněna nesprávně. Tyto nedostatky je nutné odstranit. Nesprávné datum je způsobeno tím, že formát buněk odpovídajícího sloupce je obecný a je třeba nastavit formát data. Vyberte celý sloupec, včetně buněk s chybami, a klikněte pravým tlačítkem myši na výběr. V zobrazeném seznamu přejděte na položku "Formát buňky ..." .
7. V otevřeném okně formátu otevřete kartu "Číslo" . V bloku "Číselné formáty" vyberte hodnotu "Datum" . V pravé části okna můžete vybrat požadovaný typ zobrazení data. Po nastavení nastavení klepněte na tlačítko "OK" .
8. Nyní je datum zobrazeno správně. Ale jak vidíte, celá spodní část tabulky je vyplněna buňkami, které obsahují chybu "# NUMBER!" . Ve skutečnosti jsou to buňky, data ze vzorku, pro které nebylo dost. Bylo by přitažlivější, kdyby byly zobrazeny zcela prázdné. Pro tyto účely použijeme podmíněné formátování. Vyberte všechny buňky v tabulce s výjimkou čepice. Na kartě "Domov" klikněte na tlačítko "Podmíněné formátování" , které se nachází v poli nástrojů "Styly" . V zobrazeném seznamu vyberte položku "Vytvořit pravidlo ..." .
9. V okně, které se otevře, vyberte typ pravidla "Formátovat pouze buňky, které obsahují" . V prvním poli pod textem "Formátovat pouze buňky, pro které je splněna následující podmínka", vyberte položku "Chyby" . Potom klikněte na tlačítko "Formát ..." .
10. V okně formátování, které se otevře, přejděte na kartu "Písmo" a v příslušném poli vyberte bílou barvu. Po těchto akcích klikněte na tlačítko "OK" .
11. Na tlačítko s přesně stejným názvem klikněte na tlačítko po návratu do okna Vytvořit podmínku.

Nyní máme připravený vzorek pro zadané omezení v samostatné řádně navržené tabulce.

Lekce: Podmíněné formátování v aplikaci Excel

Metoda 3: Vícenásobné podmínky pomocí vzorce

Stejně jako při použití filtru, můžete použít vzorec pro vzorek za několika podmínek. Vezměte například stejnou zdrojovou tabulku, stejně jako prázdnou tabulku, kde budou výsledky zobrazeny, s již provedeným číselným a podmíněným formátováním. Stanovili jsme první omezení nižšího výběrového limitu pro tržby 15 000 rublů a druhou podmínkou je horní hranice 20 000 rublů.

1. Do samostatného sloupce zadáme hraniční podmínky vzorku.
2. Stejně jako v předchozí metodě vybíráte jeden po druhém prázdné sloupce nové tabulky a vložíte do nich odpovídající tři vzorce. V prvním sloupci zadáme následující výraz:

   =ИНДЕКС(A2:A29;НАИМЕНЬШИЙ(ЕСЛИ(($D$2=C2:C29);СТРОКА(C2:C29);"");СТРОКА(C2:C29)-СТРОКА($C$1))-СТРОКА($C$1))

   V následujících sloupcích zadáváme přesně ty isté vzorce, ale pouze změny souřadnic bezprostředně za názvem operátoru INDEX na sloupcích odpovídajících sloupcům, které potřebujeme, analogicky s předchozí metodou.

   Pokaždé po vstupu nezapomeňte zadat Ctrl + Shift + Enter .

3. Výhodou této metody oproti předchozí je, že pokud chceme změnit hranice vzorků, nemusíte měnit samotný vzorec pole, což je samo o sobě poměrně problematické. Stačí, když ve sloupci podmínek na listu změníte hraniční čísla na ty, které uživatel potřebuje. Výsledky výběru se pak automaticky změní.

Metoda 4: náhodný výběr vzorků

V aplikaci Excel pomocí speciálního vzorce může RAND použít také náhodný výběr. V některých případech je nutné, aby v některých případech pracoval s velkým množstvím dat, kdy potřebujete představit obecný obrázek bez komplexní analýzy všech dat pole.

1. Vlevo od tabulky předáme jeden sloupec. V buňce dalšího sloupce, který je oproti první buňce s údaji tabulky, zadáme vzorec:

   =СЛЧИС()

   Tato funkce zobrazuje náhodné číslo. Chcete-li jej aktivovat, stiskněte tlačítko ENTER .

2. Chcete-li vytvořit celý sloupec náhodných čísel, nastavte kurzor do pravého dolního rohu buňky, který již obsahuje vzorec. Zobrazí se popisovač výplně. Táhněte dolů levým tlačítkem myši, který je stisknut v paralelní tabulce s daty.
3. Nyní máme řadu buněk naplněných náhodnými čísly. Ale obsahuje vzorec RAND . Musíme také pracovat s čistými hodnotami. Chcete-li to provést, zkopírujte do prázdného sloupce vpravo. Vybíráme rozsah buněk s náhodnými čísly. Po kliknutí na kartu "Domů" klikněte na ikonu "Kopírovat" na pásu karet.
4. Vyberte prázdný sloupec a klikněte pravým tlačítkem myši na místní kontextovou nabídku. Ve skupině nástrojů "Vložení parametrů" vyberte položku "Hodnoty" , která je reprezentována jako piktogram s čísly.
5. Poté na kartě "Domov" klikněte na známou ikonu "Seřadit a filtrovat" . V rozevíracím seznamu vyberte možnost "Vlastní řazení" .
6. Třídící okno je aktivováno. Nezapomeňte zaškrtnout políčko vedle položky Moje data obsahuje záhlaví , pokud existuje záhlaví a není zaškrtnuto. Do pole "Třídit podle" zadejte název sloupce, ve kterém jsou obsaženy zkopírované hodnoty náhodných čísel. V poli "Seřadit" ponecháme výchozí nastavení. V poli "Objednat" můžete vybrat možnost "Vzestupně" nebo "Sestupně" . Při náhodném odběru vzorků to na tom nezáleží. Po provedení nastavení klikněte na tlačítko "OK" .
7. Poté jsou všechny hodnoty tabulky uspořádány podle pořadí nárůstu nebo snižování náhodných čísel. Z tabulky (5, 10, 12, 15 atd.) Můžete vzít libovolný počet prvních řádků a lze je považovat za výsledek náhodného odběru vzorků.

Lekce: Třídění a filtrování dat v aplikaci Excel

Jak můžete vidět, výběr v tabulce aplikace Excel lze provést buď pomocí automatického filtru nebo pomocí speciálních vzorců. V prvním případě bude výsledek zobrazen ve zdrojové tabulce av druhém - v samostatné oblasti. Je možné zvolit buď jednu podmínku nebo několik. Navíc můžete náhodně vybrat pomocí funkce RANDOM .

• ← Pomocí interpolace v aplikaci Microsoft Excel
• Použití funkce tabulky v aplikaci Microsoft Excel →