Pri práci s tabuľkami Excel je často potrebné vykonať výber na konkrétnom kritériu alebo v niekoľkých podmienkach. Program to môže urobiť rôznymi spôsobmi pomocou série nástrojov. Pozrime sa, ako urobiť vzorku v exterele pomocou rôznych možností.
Vzorkovanie
Dátová vzorka spočíva v výberovom konaní z celkového počtu výsledkov, ktoré spĺňajú stanovené podmienky, po ktorom nasleduje výstup na hárku samostatného zoznamu alebo v rozsahu zdrojov.Metóda 1: Aplikujte rozšírenú automatickú polohu
Najjednoduchší spôsob, ako urobiť výber, je použitie rozšíreného autoFiltra. Zvážte, ako to urobiť v konkrétnom príklade.
- Vyberte oblasť na hárku, medzi ktorými chcete vytvoriť vzorku. Na karte Domov kliknite na tlačidlo "Zoradiť a filter". Je umiestnený v bloku úprav. V zozname, ktorý sa otvorí po tomto zozname, kliknite na tlačidlo "Filter".
Je tu príležitosť a inak. Ak to chcete urobiť, po výbere oblasti na hárku sa presunieme na kartu "Údaje". Kliknite na tlačidlo "Filter", ktorý je umiestnený na pásku v triede a filtrovej skupine.
- Po tejto akcii v hlavičke tabuľky sa zobrazia piktogramy, ktoré spustia filtrovanie vo forme okrajov malých trojuholníkov v pravom okraji buniek. Kliknite na túto ikonu v názve tohto stĺpca, podľa ktorého chceme urobiť vzorku. V ponuke Menu prejdite cez položku "Text Filtre". Potom vyberte polohu "Prispôsobiteľný filter ...".
- Okno pre filtrovanie používateľa je aktivované. V ňom môžete nastaviť limit, na ktorom sa vyberie výber. V rozbaľovacom zozname pre stĺpec numerickej formátovej bunky, ktorú používame napríklad, môžete si vybrať jeden z piatich typov podmienok:
- rovná;
- nerovná sa;
- viac;
- viac alebo rovnaké;
- menšie.
Poďme nastaviť podmienku ako príklad tak, aby ste vybrali iba hodnoty, pre ktoré výška príjmov presahuje 10 000 rubľov. Vytvoríme prepínač na polohu "viac". V pravom poli zapadá do hodnoty "10 000". Ak chcete vykonať akciu, kliknite na tlačidlo "OK".
- Ako vidíme, po filtrácii zostali len linky, v ktorých výška príjmov presahuje 10 000 rubľov.
- Ale v tom istom stĺpci môžeme pridať druhú podmienku. Ak to chcete urobiť, vráťte sa na vlastné filtračné okno. Ako môžeme vidieť, v jeho spodnej časti je ďalší spínací stav a zodpovedajúce pole pre vstup. Poďme teraz nainštalovať hornú výberovú hranicu 15 000 rubľov. Ak to chcete urobiť, nastavte prepínač na polohu "Menej" av poli na pravej strane je hodnota "15000".
Okrem toho stále existuje prechod podmienok. Má dve ustanovenia "a" a "alebo". V predvolenom nastavení je nainštalovaný v prvej polohe. To znamená, že vo vzorke zostanú len čiary, ktoré spĺňajú oba obmedzenia. Ak je nastavená na polohu "alebo", zostanú hodnoty, ktoré sú vhodné pre ktorékoľvek z týchto dvoch podmienok. V našom prípade musíte nastaviť prepínač do polohy "a", to znamená, že toto predvolené nastavenie ponechajte toto predvolené nastavenie. Po zadaní všetkých hodnôt kliknite na tlačidlo OK.
- Teraz zostali len čiary zostali v tabuľke, v ktorom výška príjmov nie je nižšia ako 10 000 rubľov, ale nepresahuje 15 000 rubľov.
- Podobne môžete konfigurovať filtre v iných stĺpcoch. Je možné si zachovať filtrovanie a na predchádzajúce podmienky, ktoré boli nastavené v stĺpcoch. Pozrime sa tak, ako sa výber dostane pomocou filtra pre formát dátumov. Kliknite na ikonu filtrácie v príslušnom stĺpci. Dôsledne kliknutím na zoznam "Filter podľa dátumu" a filtra.
- Opätovné spustí okno Custom AutoFilt Run. Vykonajte výber výsledkov v tabuľke od 4 do 6. mája 2016 vrátane. Vo výbere, ako môžete vidieť, ešte viac možností ako pre číselný formát. Vyberte polohu "po alebo rovnosti". V poli na pravej strane nastavte hodnotu "04.05.2016". V spodnom bloku sme nastavili prepínač na polohu "Až do rovnakej". V pravom poli zadajte hodnotu "06.05.2016". Spínač kompatibility stavu dovolenku v predvolenej polohe - "A". Ak chcete použiť filtrovanie v akcii, stlačte tlačidlo "OK".
- Ako vidíte, náš zoznam odmietol ešte viac. Teraz sú v ňom ponechané len linky, v ktorých sa výška príjmov líši od 10 000 do 15 000 rubľov na obdobie od 04.05 do 06.05.2016 vrátane.
- Môžeme obnoviť filtrovanie v jednom zo stĺpcov. Urobme to za hodnoty príjmov. Kliknite na ikonu AutoFilter v príslušnom stĺpci. V rozbaľovacom zozname kliknite na položku "Odstrániť filter".
- Ako vidíte, po týchto akciách bude vzorka zakázaná podľa množstva výnosov zakázaná, ale bude vykonávať len výber podľa dátumu (od 04/05/2016 do 06.05.2016).
- Táto tabuľka má iný stĺpec - "Názov". Obsahuje údaje vo formáte textu. Pozrime sa, ako vytvoriť vzorku pomocou filtrovania cez tieto hodnoty.
Kliknite na ikonu filtra v názve stĺpca. Dôsledne prejdite na mená zoznamu "Textové filtre" a "Prispôsobiteľný filter ...".
- Otvorí sa okno User AutoFilter. Urobme vzorku menami "zemiakov" a "mäso". V prvom bloku sa podmienky podmienky nastavené na polohu "rovnakú". V poli vpravo od toho zapadá do slova "zemiaky". Prepínač spodnej jednotky bude tiež umiestniť "rovnakú" polohu. V poli oproti tomu, urobím rekord - "mäso". A teraz vykonávame to, čo predtým neboli: Nastavte prepínač kompatibility do polohy "alebo". Teraz sa na obrazovke zobrazí riadok obsahujúci ktorúkoľvek zo zadaných podmienok. Kliknite na tlačidlo "OK".
- Ako vidíte, existujú obmedzenia v deň v novej vzorke (od 04.05.2016 do 06.05.2016) a podľa mena (zemiaky a mäso). Neexistujú žiadne obmedzenia výšky príjmov.
- Filter môžete úplne odstrániť rovnakými metódami, ktoré boli použité na jeho inštaláciu. A nezáleží na tom, aká bola použitá metóda. Na resetovanie filtrovania, zatiaľ čo v karte "Údaje" kliknite na tlačidlo "Filter", ktorý sa nachádza v skupine "Sort and Filter".
Druhá možnosť zahŕňa prechod na kartu "Domov". Vykonávame kliknutím na pásku na tlačidle "Zoradiť a filter" v jednotke na úpravu. V aktivovanom zozname kliknite na tlačidlo "Filter".
Pri použití niektorého z dvoch vyššie uvedených spôsobov sa filtrovanie odstráni a výsledky vzorky sa čistia. To znamená, že tabuľka sa zobrazí celé množstvo údajov, ktoré má.
Lekcia: Funkcia automatického filtra v programe Excel
Metóda 2: Aplikácia vzorec poľa
Urobiť výber môže tiež použiť komplexný vzorec poľa. Na rozdiel od predchádzajúcej verzie táto metóda poskytuje výstup výsledku v samostatnej tabuľke.
- Na tom istom hárku vytvárame prázdny stôl s rovnakými názvami stĺpcov v hlavičke ako zdroj.
- Prideľovať všetky prázdne bunky v prvom stĺpci novej tabuľky. Nainštalovať kurzor vo vzorci reťazci. Len tu bude zadaný vzorec, ktorý produkuje vzorku na základe stanovených kritérií. Vyberieme linku, výška príjmov, v ktorom presahuje 15.000 rubľov. V našom konkrétnom prípade sa predstavil vzorec bude vyzerať takto:
= Index (A2: A29, najmenší (if (15.000
Samozrejme, že v každom jednotlivom prípade, je adresa buniek a rozsahy bude vaša. Na tomto príklade môžete porovnať vzorec so súradnicami na ilustráciu a prispôsobiť ho pre vaše potreby.
- Pretože sa jedná o vzorec poľa, aby ju aplikovať v praxi, je potrebné stlačiť tlačidlo ENTER, ale Ctrl + SHIFT + ENTER kombinácie klávesov. Robíme to.
- Má upozorniť na druhý stĺpec s dátami a inštaláciu kurzor vo vzorci reťazec, sme zaviedli nasledujúce výraz:
= Index (B2: B29; najmenších (if (15.000
Kliknite na CTRL + SHIFT + ENTER kombinácie klávesov.
- Podobne, v kolóne s príjmami, zadať vzorec nasledujúci obsah:
= Index (C2: C29; najmenších (if (15.000
Opäť platí, že typ CTRL + SHIFT + ENTER kombinácie klávesov.
Vo všetkých troch prípadoch je len prvá hodnoty súradníc sa mení, a zvyšok vzorca je úplne identická.
- Ako vidíme, tabuľka je naplnený dátami, ale jeho vzhľad nie je úplne atraktívny, okrem toho, že hodnoty dáta sú vyplnené v ňom nesprávne. Je potrebné opraviť tieto nedostatky. Nesprávnosť dáta súvisí s tým, že formát buniek zodpovedajúci stĺpec je bežné, a musíme nastaviť formát dátumu. Zdôrazňujeme celý stĺpec, vrátane buniek s chybami, a kliknite na zvýraznenie pravým tlačidlom myši. V zozname, ktorý sa objaví, prejsť "Formát bunky ...".
- V formátovania okne, ktoré sa otvorí, prejdite na kartu "number". V "číselným formátom" bloku priradiť hodnota "Dátum". Na pravej strane okna, môžete zvoliť požadovaný typ zobrazenia dátumu. Po vystavení nastavení kliknite na tlačidlo "OK".
- Teraz je termín je správne zobrazený. Ale ako vidíme, celá spodná časť tabuľky je vyplnená s bunkami, ktoré obsahujú chybnú hodnotu "# číslo!". V podstate sa jedná o tieto bunky sú dáta zo vzorky, ktoré nemali dosť. Bolo by to viac atraktívne, ak by boli zobrazené vôbec prázdna. Pre tieto účely používame podmienené formátovanie. Zdôrazňujeme všetky bunky tabuľky, s výnimkou hlavičky. Kým v karte Domov kliknite na tlačidlo "Podmienené formátovanie", ktorý je v "Štýly" blok náradia. V zozname, ktorý sa objaví, vyberte "... vytvoriť pravidlo" položku.
- V okne, ktoré sa otvorí, vyberte typ pravidla "formátu iba bunky, ktoré obsahujú". V prvom poli pod nápisom "Formátovanie iba buniek, pre ktoré nasledujúca podmienka," vyberte "Chyba". Ďalej kliknite na tlačidlo "Formát ...".
- V spustení formátovania prejdite na kartu "Font" a vyberte White vo vhodnom poli. Po týchto akciách kliknite na tlačidlo "OK".
- Na tlačidle s presne rovnakým názvom kliknite na tlačidlo po návrate na vytvorenie okna Vytvorenie správania.
Teraz máme hotovú vzorku na zadanom limite v samostatnom riadne zariadenej tabuľke.
Lekcia: Podmienené formátovanie v Excel
Metóda 3: Odber vzoriek na niekoľko podmienok pomocou vzorca
Rovnako ako pri používaní filtra, pomocou vzorca môžete vybrať niekoľko podmienok. Užívajte napríklad rovnakú zdrojovú tabuľku, ako aj prázdny stôl, kde budú výsledky vychádzať, s už vykonanými číselnými a podmienenými formátovaním. Vykonáme prvé obmedzenie dolnej hranice výberu príjmov z 15 000 rubľov a druhá podmienka hornej hranice 20 000 rubľov.
- Zadajte samostatný stĺpec, hraničné podmienky pre odber vzoriek.
- Rovnako ako v predchádzajúcom spôsobe, striedavo prideľujte prázdne stĺpce novej tabuľky a zadajte v nich príslušné tri vzorce. V prvom stĺpci zavádzame nasledujúci výraz:
= Index (A2: A29; najmenší (ak ((((($ D $ 2 = C2: C29); LINE (C2: C29); "); String (C2: C29) -strkok ($ C $ 1)) - riadok ($ C $ 1))
V nasledujúcich stĺpcoch vyhovujú presne rovnakým vzorcom, len zmenou súradníc ihneď po mene operátora, index na zodpovedajúce stĺpce, ktoré potrebujeme, analogicky s predchádzajúcou metódou.
Zakaždým, keď po zadaní nezabudnite získať kláves CTRL + SHIFT + ENTER ENTER CHCEUNTY.
- Výhodou tohto spôsobu pred predchádzajúcim je, že ak chceme zmeniť hranice vzorky, nebude nutné zmeniť samotný tuhý vzorec, ktorý sama o sebe je pomerne problematický. Stačí v stĺpci podmienok na hárku zmeniť hraničné čísla na tých, ktoré potrebuje užívateľ. Výsledky výberu sa okamžite automaticky zmenia.
Metóda 4: Náhodný odber vzoriek
V exile, s pomocou špeciálneho vzorca, je tiež možné použiť náhodný výber. V niektorých prípadoch sa vyžaduje pri práci s veľkým množstvom údajov, keď potrebujete prezentovať spoločný obrázok bez komplexnej analýzy všetkých údajov poľa.
- Vľavo od tabuľky Preskočte jeden stĺpec. V bunke nasledujúceho kolóny, ktorý sa nachádza oproti prvej bunke s údajmi tabuľky, zadajte vzorca:
= Lepidlo ()
Táto funkcia zobrazuje náhodné číslo. Ak chcete aktivovať, kliknite na tlačidlo ENTER.
- Aby ste vytvorili celý stĺpec náhodných čísel, nastavte kurzor do pravého dolného rohu bunky, ktorý už obsahuje vzorca. Zobrazí sa výplňový marker. Tlačím ho s ľavým tlačidlom myši rovnobežne s tabuľkou s údajmi až do konca.
- Teraz máme rad buniek naplnených náhodnými číslami. Ale obsahuje vzorec Calc. Musíme pracovať s čistou hodnotou. Ak to chcete urobiť, skopírujte do prázdneho stĺpca vpravo. Vyberte rozsah buniek s náhodnými číslami. Na karte "HOME", kliknite na ikonu "Kopírovať" na páse.
- Zvýrazňujeme prázdny stĺpec a kliknite pravým tlačidlom myši, volajte na kontextové menu. V paneli s nástrojmi "Vložiť parametre" vyberte klauzulu "Hodnota", znázornená ako piktogramy s číslami.
- Po tom, zatiaľ čo v karte "Domov" kliknite na už známe ikonu "Zoradiť a filter". V rozbaľovacom zozname zastavíte výber na položke "Vlastné triedenie".
- Okno Nastavenia triedenia je aktivované. Nezabudnite nainštalovať kliešte oproti parametrovi "Moje údaje obsahujú titulky", ak je k dispozícii uzáver, ale žiadne kliešte. V poli "Zoradiť podľa", zadajte názov tohto stĺpca, v ktorom sú obsiahnuté kopírované hodnoty náhodných čísel. V poli "Triediť" nechajte predvolené nastavenia. V poli "Objednať" si môžete vybrať parameter ako "vzostupne" a "zostupne". Pre náhodnú vzorku táto hodnota nemá. Po vyrábaní nastavení kliknite na tlačidlo "OK".
- Po tom, všetky hodnoty tabuľky sú postavené vo vzostupnom poradí alebo znížení náhodných čísel. Môžete si vziať ľubovoľný počet prvých riadkov z tabuľky (5, 10, 12, 15 atď.) A môžu byť považované za výsledok náhodnej vzorky.
Lekcia: Triedenie a filtrovanie údajov do programu Excel
Ako môžete vidieť, vzorka v tabuľke Excel môže byť vyrobená, a to ako pomocou autofiltra a aplikácie špeciálnych vzorcov. V prvom prípade sa výsledok zobrazí v zdrojovej tabuľke av oddelenej oblasti. Je možné vyrábať výber, jeden stav aj niekoľko. Okrem toho môžete vykonať náhodnú vzorku pomocou funkcie lepidla.