Načítanie údajov v programe Microsoft Excel

Pri práci s tabuľkami programu Excel je často potrebné ich vybrať podľa určitého kritéria alebo viacerých podmienok. V programe môžete robiť to rôznymi spôsobmi pomocou viacerých nástrojov. Poďme zistiť, ako urobiť výber v programe Excel, pomocou rôznych možností.

Vykonanie vzorky

Odber vzoriek údajov spočíva v postupe výberu výsledkov, ktoré spĺňajú špecifikované podmienky, zo všeobecného poľa a potom ich výstup na hárku v samostatnom zozname alebo v pôvodnom rozsahu.

Metóda 1: Použitie pokročilého automatického filtrovania

Najjednoduchší spôsob výberu je použitie rozšíreného automatického filtrovania. Pozrime sa, ako to urobiť, na konkrétnom príklade.

1. Vyberte oblasť na hárku, medzi ktorou je potrebné vybrať. Na karte "Domov" kliknite na tlačidlo "Zoradiť a filtrovať" . Je umiestnený v bloku nastavení "Upraviť" . V zozname, ktorý sa potom otvorí, kliknite na tlačidlo "Filter" .

   Je tu možnosť urobiť to isté iným spôsobom. Ak to chcete urobiť, po výbere oblasti na hárku prejdite na kartu "Dáta" . Klikneme na tlačidlo "Filter" , ktoré sa nachádza na pásiku v skupine "Zoradiť a filtrovať" .

2. Po tejto akcii sa v hlavičke tabuľky objavia ikony, ktoré začnú filtrovať vo forme malých obrátených trojuholníkov na pravom okraji buniek. Klikneme na túto ikonu v názve stĺpca, pre ktorý chceme vybrať. V ponuke, ktorá sa otvorí, prejdite na položku "Filtre textu" . Potom vyberte položku "Vlastný filter ..." .
3. Okno filtrovania používateľov je aktivované. V ňom môžete špecifikovať obmedzenie, podľa ktorého sa výber uskutoční. V rozbaľovacom zozname stĺpca obsahujúceho bunku číselného formátu, ktorý používame pre príklad, môžete vybrať jeden z piatich druhov podmienok:
   • sa rovná;
   • nie je rovnaká;
   • viac než;
   • väčší alebo rovný;
   • menej.

   Napíšte podmienku ako príklad, aby sme vybrali len hodnoty, o ktorých výška výnosu presahuje 10 000 rubľov. Prepnite prepínač do polohy "Viac" . V pravom poli zadajte hodnotu "10000" . Ak chcete vykonať akciu, kliknite na tlačidlo "OK" .

4. Ako vidíte, po filtrácii existovali iba linky, v ktorých výška výnosu presahuje 10 000 rubľov.
5. Ale v tom istom stĺpci môžeme pridať druhú podmienku. Za týmto účelom sa opäť vrátime do okna filtrovania používateľov. Ako vidíte, v spodnej časti je jeden ďalší prepínač stavu a jeho zodpovedajúce vstupné pole. Teraz nastavíme horný limit výberu na 15 000 rubľov. Za týmto účelom prepnite prepínač do polohy "menej" av poli vpravo zadajte hodnotu "15000" .

   Okrem toho existuje aj prepínač stavu. Má dve pozície "ja" a "OR" . Štandardne je nastavená na prvú pozíciu. To znamená, že vo vzorke budú existovať len línie, ktoré vyhovujú obom obmedzeniam. Ak je nastavená na pozíciu "OR" , zostanú hodnoty, ktoré sú vhodné pre jednu z týchto dvoch podmienok. V našom prípade musíte prepnúť prepínač do polohy "AND" , to znamená, že toto nastavenie nechajte ako predvolené. Po zadaní všetkých hodnôt kliknite na tlačidlo "OK" .

6. Teraz v tabuľke zostali iba riadky, ktorých výška výnosu nie je nižšia ako 10 000 rubľov, ale nepresahuje 15 000 rubľov.
7. Podobne môžete nakonfigurovať filtre v iných stĺpcoch. V tomto prípade je tiež možné uložiť aj filtrovanie za predchádzajúcich podmienok, ktoré boli nastavené v stĺpcoch. Pozrime sa teda, ako je filter vytvorený pre bunky vo formáte dátumu. Kliknite na ikonu filtra v príslušnom stĺpci. Dôsledne kliknite na položky v zozname "Filter podľa dátumu" a "Vlastný filter" .
8. Obnoví sa okno Vlastné automatické filtrovanie. Výber výsledkov vykonáme v tabuľke od 4. do 6. mája 2016 vrátane. V prepínači stavu, ako vidíte, sú ešte viac možností ako v číselnom formáte. Vyberte pozíciu "Po alebo Rovno . " V poli vpravo nastavte hodnotu na "04/05/2016" . V spodnom bloku nastavte prepínač do polohy "Pred alebo Rovná" . V pravom poli zadáme hodnotu "06.05.2016" . Prepínač kompatibility podmienok zostáva v predvolenej pozícii - "AND" . Ak chcete použiť filtrovanie v akcii, kliknite na tlačidlo "OK" .
9. Ako môžete vidieť, náš zoznam sa ďalej znižuje. Teraz ponecháva len linky, v ktorých sa výška príjmov pohybuje od 10 000 do 15 000 rubľov za obdobie od 04.05 do 06.05.2016 vrátane.
10. Filtrovanie môžeme obnoviť v jednom zo stĺpcov. Urobme to pre čísla príjmov. Kliknite na ikonu automatického filtrovania v príslušnom stĺpci. V rozbaľovacom zozname kliknite na položku "Odstrániť filter" .
11. Ako vidíte, po týchto akciách bude vzorka o výšku výnosu zakázaná a bude k dispozícii iba výber podľa dátumov (od 04/05/2016 do 06/05/2016).
12. V tejto tabuľke je ešte jeden stĺpec - "Name" . Obsahuje údaje v textovom formáte. Pozrime sa, ako vygenerovať vzorku pomocou filtrovania týchto hodnôt.

    Kliknite na ikonu filtra v názve stĺpca. Prejdeme cez mená zoznamu "Textové filtre" a "Vlastný filter ..." .

13. Opäť sa otvorí okno Vlastné automatické filtrovanie. Urobme si výber na menách "Zemiaky" a "Mäso" . V prvom bloku nastavte prepínač stavu do polohy "Rovná" . V poli, napravo od nej, zadáme slovo "Zemiaky" . Spínač spodného bloku sa tiež umiestni do polohy "Rovná" . Na opačnom poli urobíme poznámku - "Mäso" . Potom vykonáme to, čo sme predtým neurobili: nastavte prepínač kompatibility stavu na pozíciu "OR" . Teraz sa zobrazí riadok, ktorý obsahuje niektorú z uvedených podmienok. Klikneme na tlačidlo "OK" .
14. Ako vidíte, v novej vzorke sú obmedzené dátum (od 04/05/2016 do 06/05/2016) a podľa názvu (zemiaky a mäso). Výška príjmu nie je obmedzená.
15. Úplne odstrániť filter môžu byť rovnaké metódy, ktoré boli použité na inštaláciu. A nezáleží na tom, ktorá metóda bola použitá. Ak chcete obnoviť filtrovanie, na karte "Data" kliknite na tlačidlo "Filter" , ktoré sa nachádza v skupine "Zoradiť a filtrovať" .

    Druhá možnosť zahŕňa prepnutie na kartu Domov . Klikneme na pásik na karte "Zoradiť a triediť" v bloku "Upraviť" . V aktivovanom zozname kliknite na tlačidlo "Filter" .

Ak použijete niektorú z vyššie uvedených dvoch metód, filtrovanie sa odstráni a výsledky výberu sa vymažú. To znamená, že v tabuľke sa zobrazí celý rad dát, ktoré má.

Lekcia: Funkcia automatického filtrovania v programe Excel

Metóda 2: Použiť vzorec poľa

Môžete tiež urobiť výber použitím komplexného vzorca pre pole. Na rozdiel od predchádzajúcej verzie táto metóda poskytuje výstup výsledku v samostatnej tabuľke.

1. Na rovnakom hárku vytvoríme prázdnu tabuľku s rovnakými názvami stĺpcov v hlavičke ako zdroj.
2. Vyberte všetky prázdne bunky prvého stĺpca novej tabuľky. Nastavte kurzor na lište vzorcov. Práve tu sa zadá vzorec a urobí sa vzorka podľa určených kritérií. Vyberieme linky, ktorých výška príjmov presahuje 15 000 rubľov. V našom konkrétnom príklade vstupný vzorec bude vyzerať takto:

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

   Samozrejme, v každom prípade bude adresa buniek a rozsahov rovnaká. V tomto príklade môžete podľa vzoru odpovedať vzorec so súradnicami na obrázku a prispôsobiť ho vašim potrebám.

3. Keďže ide o vzorec poľa, aby ste ho mohli použiť v akcii, musíte stlačiť kláves Enter a kombináciu klávesov Ctrl + Shift + Enter . Robíme to.
4. Vyberte druhý stĺpec s dátumami a umiestnite kurzor na riadok vzorca a zadajte nasledujúci výraz:

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

   Stlačte kombináciu klávesov Ctrl + Shift + Enter .

5. Podobne v stĺpci s príjmami zadávame nasledujúci vzorec:

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

   Znova zadajte klávesy Ctrl + Shift + Enter .

   Vo všetkých troch prípadoch sa mení iba prvá hodnota súradníc a inak sú vzorce úplne totožné.

6. Ako môžete vidieť, tabuľka je naplnená údajmi, ale jej vzhľad nie je príliš atraktívny, navyše, hodnoty dátumu sú vyplnené nesprávne. Tieto nedostatky je potrebné opraviť. Nesprávny dátum je spôsobený skutočnosťou, že formát buniek príslušného stĺpca je všeobecný a musíme nastaviť formát dátumu. Vyberte celý stĺpec vrátane buniek s chybami a kliknite pravým tlačidlom myši na výber. V zobrazenom zozname prejdite na položku "Formát bunky ..." .
7. V otvorenom okne formátu otvorte kartu "Číslo" . V bloku "Číselné formáty" vyberte hodnotu "Dátum" . V pravej časti okna môžete vybrať požadovaný typ zobrazenia dátumu. Po nastavení nastavenia kliknite na tlačidlo "OK" .
8. Teraz sa dátum zobrazuje správne. Ale ako vidíte, celá spodná časť tabuľky je naplnená bunkami, ktoré obsahujú chybnú hodnotu "# NUMBER!" . V skutočnosti sú to bunky, údaje zo vzorky, pre ktoré nestačilo. Bolo by atraktívnejšie, keby boli zobrazené vôbec prázdne. Na tieto účely používame podmienené formátovanie. Vyberte všetky bunky v tabuľke okrem čiapky. Na karte "Domov" kliknite na tlačidlo "Podmienečné formátovanie" , ktoré sa nachádza v poli "Štýly" . V zobrazenom zozname vyberte položku "Vytvoriť pravidlo ..." .
9. V okne, ktoré sa otvorí, vyberte typ pravidla "Formátovať iba bunky, ktoré obsahujú" . V prvom poli pod textom "Formátovať iba bunky, pre ktoré je splnená táto podmienka", vyberte položku "Chyby" . Potom kliknite na tlačidlo "Formát ..." .
10. V okne formátovania, ktoré sa otvorí, prejdite na kartu "Písmo" a vyberte biele farby v príslušnom poli. Po týchto akciách kliknite na tlačidlo "OK" .
11. Na tlačidle s presným rovnakým názvom kliknite po návrate do okna Vytvoriť podmienky.

Teraz máme pripravenú vzorku pre špecifikované obmedzenie v samostatnej riadne navrhovanej tabuľke.

Lekcia: Podmienené formátovanie v programe Excel

Metóda 3: Viac podmienok pomocou vzorca

Rovnako ako pri použití filtra, môžete použiť vzorec na výber vzorky v niekoľkých podmienkach. Napríklad, vezmite rovnakú zdrojovú tabuľku, rovnako ako prázdnu tabuľku, kde sa výsledky zobrazia, s už vykonaným číselným a podmieneným formátovaním. Zaviedli sme prvé obmedzenie nižšieho výberového limitu pre príjmy 15 000 rubľov a druhou podmienkou je horná hranica 20 000 rubľov.

1. Do samostatného stĺpca zadáme hraničné podmienky pre vzorku.
2. Rovnako ako v predchádzajúcej metóde vyberiete jeden po druhom prázdne stĺpce novej tabuľky a vložíte do nich zodpovedajúce tri vzorce. V prvom stĺpci zadáme nasledujúci výraz:

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

   V nasledujúcich stĺpcoch zadávame presne tie isté vzorce, pričom meniť súradnice bezprostredne za názvom operátora INDEX na stĺpcoch zodpovedajúcich stĺpcoch, ktoré potrebujeme, analogicky s predchádzajúcou metódou.

   Zakaždým po zadaní nezabudnite zadať klávesy Ctrl + Shift + Enter .

3. Výhoda tejto metódy oproti predchádzajúcej je, že ak chceme zmeniť hranice vzorky, nemusíme zmeniť samotný vzorec poľa, čo je samo osebe problematické. V stĺpci podmienok na hárku stačí zmeniť čísla hraníc na tie, ktoré používateľ potrebuje. Výsledky výberu sa potom automaticky zmení.

Metóda 4: Náhodné odoberanie vzoriek

V programe Excel pomocou špeciálneho vzorca môže RAND použiť aj náhodný výber. Je potrebné, aby ste ju v niektorých prípadoch vyrobili pri práci s veľkým množstvom údajov, keď potrebujete predstaviť všeobecný obraz bez komplexnej analýzy všetkých dát poľa.

1. Vľavo od tabuľky prejdeme za jeden stĺpec. V bunke ďalšieho stĺpca, ktorý je oproti prvej bunke s údajmi v tabuľke, zadáme vzorec:

   =СЛЧИС()

   Táto funkcia zobrazuje náhodné číslo. Ak ju chcete aktivovať, stlačte tlačidlo ENTER .

2. Ak chcete vytvoriť celý stĺpec náhodných čísel, nastavte kurzor do pravého dolného rohu bunky, ktorá už obsahuje vzorec. Zobrazí sa popisovač plnenia. Potiahnite ho ľavým tlačidlom myši stlačením paralelne s tabuľkou údajov na jej koniec.
3. Teraz máme rad buniek naplnených náhodnými číslami. Obsahuje však vzorec RAND . Musíme tiež pracovať s čistými hodnotami. Ak to chcete urobiť, skopírujte do prázdneho stĺpca vpravo. Vyberieme rozsah buniek s náhodnými číslami. Po umiestnení na karte "Domov" kliknite na ikonu "Kopírovať" na karte.
4. Vyberte prázdny stĺpec a kliknite pravým tlačidlom myši na kontextové menu. V skupine nástrojov Insertion parameters zvoľte položku "Values" , ktorá je reprezentovaná ako piktogram s číslami.
5. Potom na záložke "Domov" kliknite na známu ikonu "Sort and Filter" . V rozbaľovacom zozname zastavte výber položky "Vlastné triedenie" .
6. Triediace okno je aktivované. Nezabudnite začiarknuť políčko vedľa možnosti "Moje dáta obsahuje hlavičky" , ak je záhlavie a nie je začiarknuté. V poli "Triediť podľa" zadajte názov stĺpca, v ktorom sú obsiahnuté skopírované hodnoty náhodných čísiel. V poli "Zoradiť" ponechávame predvolené nastavenia. V poli "Objednávka" môžete vybrať možnosť "Vzostupne" alebo "Zostupne" . Pri náhodnom odbere vzoriek to nezáleží. Po vykonaní nastavení kliknite na tlačidlo "OK" .
7. Potom sú všetky hodnoty tabuľky usporiadané v poradí nárastu alebo znižovania náhodných čísel. Z tabuľky (5, 10, 12, 15 atď.) Môžete brať ľubovoľný počet prvých riadkov a môžu sa považovať za výsledok náhodného výberu vzorky.

Lekcia: Triedenie a filtrovanie údajov v programe Excel

Ako môžete vidieť, výber v tabuľke programu Excel môžete vykonať buď pomocou automatického filtra, alebo pomocou špeciálnych vzorcov. V prvom prípade sa výsledok zobrazí v zdrojovej tabuľke av druhej - v samostatnej oblasti. Je možné vybrať buď jednu podmienku alebo niekoľko. Navyše môžete náhodne vybrať pomocou funkcie RANDOM .

• ← Pomocou interpolácie v programe Microsoft Excel
• Použitie funkcie tabuľky v programe Microsoft Excel →