Zoraďme tabuľku pozostávajúcu z 2 stĺpcov pomocou vzorcov. Zoradiť budeme podľa jedného zo stĺpcov tabuľky (riešime 2 úlohy: zoradenie tabuľky podľa číselného a zoradenie podľa textového stĺpca). Nastavme vzorce triedenia tak, aby sa po pridaní nových údajov do zdrojovej tabuľky triedená tabuľka dynamicky menila. To vám umožní mať vždy zoradenú tabuľku bez zásahu používateľa. Urobíme aj dvojúrovňové triedenie: najprv podľa numerického, potom (pre opakujúce sa čísla) podľa textového stĺpca.

Nech existuje tabuľka pozostávajúca z 2 stĺpcov. Jeden stĺpec je text: Zoznam ovocia; a druhý je číselný Objem predaja(pozri vzorový súbor).

Problém 1 (Zoradenie tabuľky podľa stĺpca s číslami)

Riadky tabuľky musíte zoradiť podľa obsahu číselného stĺpca (podľa objemu predaja). Predpokladá sa, že používateľ neustále vypĺňa riadky tabuľky, preto je potrebné písať vzorce zohľadňujúce pridané hodnoty.

Pre prehľadnosť hodnoty v stĺpci Objem predaja zvýraznené pomocou (). Opakované hodnoty sú tiež zvýraznené žltou farbou.

Poznámka: Problém triedenia jedného stĺpca (zoznamu) bol vyriešený v článkoch a.

Riešenie1

Ak je zaručené, že číselný stĺpec neobsahuje žiadne hodnoty, problém sa dá ľahko vyriešiť:

• Zoraďte číselný stĺpec pomocou funkcie LARGE() (pozri článok);
• Funkcia VLOOKUP() alebo množstvo funkcií INDEX()+MATCH() vyberte hodnoty z textového stĺpca podľa jeho zodpovedajúcej číselnej hodnoty.

V reálnych problémoch však môže číselný stĺpec obsahovať opakovania a keďže funkcia VLOOKUP() v prípade opakovaní vždy vyberie len prvú hodnotu zhora (pozri článok), tento prístup nie je vhodný (názvy ovocie sa zobrazí nesprávne).

Preto bude treba mechanizmus triedenia implementovať inak.

INDEX(Predaj;
ROUND(REMAT(LARGEST(
---(COUNTIF(Predaj;"<"&Продажи)&","&ПОВТОР("0";3-ДЛСТР(СТРОКА(Продажи)-СТРОКА($E$6)))&СТРОКА(Продажи)-СТРОКА($E$6));
ROW()-ROW($E$6));1)*1000;0)
)

Tento vzorec triedi stĺpec Objem predaja(dynamický rozsah Predaj) v zostupnom poradí. Medzery v zdrojovej tabuľke nie sú povolené. Počet riadkov v zdrojovej tabuľke musí byť menší ako 1 000.

Pozrime sa na vzorec podrobnejšie:

• Vzorec COUNTIF(Predaj;"<"&Продажи) vráti pole (4:5:0:2:7:1:3:5). To znamená, že číslo 64 (z bunky B7 pôvodná tabuľka, t.j. prvé číslo z rozsahu Predaj) viac ako 4 hodnoty z rovnakého rozsahu; číslo 74 (z cel B8 pôvodná tabuľka, t.j. druhé číslo z rozsahu Predaj) viac ako 5 hodnôt z rovnakého rozsahu; ďalšie číslo 23 je najmenšie (nie je väčšie ako ktokoľvek iný) atď.
• Teraz premeňme vyššie uvedené pole celých čísel na pole čísel so zlomkovou časťou, kde zlomková časť bude obsahovať číslo pozície čísla v poli: (4,001:5,002:0,003:2,004:7,005:1,006:3,007:5,008 ). Toto je implementované výrazom &","&REPEAT("0";3-DĹŽKA(RAD (predaj)-RIADOK($E$6)))&ROW (predaj)-RAD (6 EUR)) Práve v tejto časti vzorca je obmedzenie na maximálne 1 000 riadkov v zdrojovej tabuľke (pozri vyššie). V prípade potreby sa dá ľahko zmeniť, ale je to zbytočné (pozri časť o rýchlosti výpočtu nižšie).
• Funkcia LARGE() triedi vyššie uvedené pole.
• Funkcia ROD() vracia zlomkovú časť čísla, čo sú čísla pozícií/1000, napríklad 0,005.
• Funkcia ROUND() sa po vynásobení číslom 1000 zaokrúhli na celé číslo a vráti číslo pozície. Teraz všetky čísla pozícií zodpovedajú číslam stĺpcov objemy predaja, zoradené v zostupnom poradí.
• Funkcia INDEX() s číslom pozície vráti číslo, ktoré mu zodpovedá.

Podobný vzorec je možné napísať na zobrazenie hodnôt v stĺpci Ovocie=INDEX(ovocie,okrúhle(...))

Vo vzorovom súbore je kvôli úvahám o rýchlosti výpočtu (pozri nižšie) rovnaký typ časti vzorca, t.j. všetko vo funkcii ROUND() je umiestnené v samostatnom stĺpci J . Preto konečné vzorce v zoradenej tabuľke vyzerajú takto: =INDEX(ovocie,J7) A =INDEX(Predaj,J7)

Tiež zmenou funkcie LARGE() na SMALL() v maticovom vzorci získame zoradenie vo vzostupnom poradí.

Pre prehľadnosť hodnoty v stĺpci Objem predaja zvýraznené ( Domov/ Štýly/ Podmienené formátovanie/ Histogramy). Ako vidíte, triedenie funguje.

Testovanie

Teraz do pôvodnej tabuľky pridáme nový riadok. V dynamicky triedených tabuľkách musíme získať príslušné triedenie.

1. Do cely A15 zdrojová tabuľka zadajte slovo Mrkva;
2. Do bunky B15 vstúpiť Objem predaja Mrkva = 25;
3. Po zadaní hodnôt do stĺpcov D A E automaticky sa zobrazí tabuľka zoradená v zostupnom poradí;
4. V zoradenej tabuľke sa nový riadok zobrazí ako predposledný.

Rýchlosť výpočtu vzorca

Na „priemernom“ počítači z hľadiska výkonu prepočet dvojice takých vzorce poľa, umiestnený v 100 riadkoch, je prakticky neviditeľný. Pre tabuľky s 300 riadkami trvá prepočet 2-3 sekundy, čo je nepohodlné. Alebo musíte vypnúť automatické prepočítavanie hárkov ( Vzorce/Výpočty/Možnosti výpočtu) a pravidelne stláčajte tlačidlo F9, buď upustiť od používania vzorcov poľa a nahradiť ich stĺpcami so zodpovedajúcimi vzorcami, alebo úplne opustiť dynamické triedenie v prospech štandardných prístupov (pozri nasledujúcu časť).

Alternatívne prístupy k triedeniu tabuliek

Zoraďme riadky zdrojovej tabuľky pomocou štandardného filtra (vyberte hlavičky zdrojovej tabuľky a kliknite CTRL+SHIFT+L). Z rozbaľovacieho zoznamu vyberte požadované triedenie.

Získame verziu tabuľky identickú s našou, ale pri pridávaní nových hodnôt do tabuľky budeme musieť znova použiť filter.

Môžete tiež použiť nástroj na triedenie ( Údaje/Zoradiť a Filtrovať/Zoradiť). Ak to chcete urobiť, musíte vybrať všetky hodnoty zdrojovej tabuľky, okrem hlavičky, zavolať nástroj Zoradiť, vybrať stĺpec, podľa ktorého chcete zoradiť, a možnosť zoradenia.

Získame verziu tabuľky identickú s našou, ale pri pridávaní nových hodnôt budeme musieť znova použiť filter.

Rovnako ako v predchádzajúcom probléme, predpokladajme, že v stĺpci, podľa ktorého sa triedenie vykonáva, sú opakovania (názvy ovocia sa opakujú).

Na zoradenie tabuľky budete musieť vytvoriť 2 stĺpce služby (D a E).

=COUNTIF($B$7:$B$14;"<"&$B$7:$B$14)+1

Tento vzorec je analogický s textovými hodnotami (pozícia hodnoty vzhľadom na ostatné hodnoty v zozname). Hodnota textu nižšia v abecede má vyššie „hodnotenie“. Napríklad hodnota Jablká zodpovedá maximálnemu „hodnoteniu“ 7 (vrátane opakovaní).

Do stĺpca E zadajte obvyklý vzorec:

=COUNTIF($D$6:D6;D7)+D7

Tento vzorec zohľadňuje opakovania textových hodnôt a upravuje „hodnotenie“. Teraz rôzne hodnoty jabĺk zodpovedajú rôznym „hodnotám“ - 7 a 8. To vám umožní zobraziť zoznam zoradených hodnôt. Ak to chcete urobiť, použite vzorec (stĺpec G):

=INDEX($B$7:$B$14,MATCH(ROW()-ROW($G$6),$E$7:$E$14,0))

Podobný vzorec zobrazí zodpovedajúci objem predaja (stĺpec H).

Problém 2.1 (Dvojúrovňové triedenie)

Teraz zoraďme pôvodnú tabuľku znova podľa objemu predaja. Ale teraz pre opakujúce sa hodnoty (v stĺpci A sú tri hodnoty 74), zobrazíme zodpovedajúce hodnoty v abecednom poradí.

Na tento účel použijeme výsledky problémov 1.1 a 2.

Podrobnosti vo vzorovom súbore na hárku Úloha2.

Zoznam môžete zoradiť abecedne, číselne alebo chronologicky až podľa troch polí. Pre každý stĺpec môžete určiť vzostupné a zostupné zoradenie.

Vzostupné zoradenie:

Čísla budú zoradené v poradí od najmenšieho záporného po najväčšie kladné číslo.

Hodnoty dátumu a času budú zoradené od najstaršej po najnovšiu hodnotu.

Text bude zoradený podľa abecedy. V tomto prípade budú najskôr umiestnené číselné hodnoty zadané ako text.

Pri zoraďovaní boolovských hodnôt vo vzostupnom poradí sa najskôr zobrazí hodnota FALSE a potom hodnota TRUE.

Chybové hodnoty budú zoradené v poradí, v akom sa vyskytli (z hľadiska triedenia sú všetky rovnaké).

Na konci zoradeného zoznamu sa zobrazia prázdne bunky.

Zostupné zoradenie:

V prípade zostupného zoradenia zoradí Excel záznamy v opačnom poradí, ako je opísané vyššie. Na konci zoznamu sa budú stále zobrazovať iba prázdne bunky.

Vlastné poradie triedenia:

Namiesto vzostupného a zostupného zoradenia môžete použiť aj vlastné zoradenie určené zadaným vlastným zoznamom.

Triedenie zoznamu

Ak chcete zoznam zoradiť, umiestnite ukazovateľ bunky do zoznamu a spustite príkaz Údaje – Zoradiť.

Excel automaticky vyberie zoznam a zobrazí dialógové okno Rozsah triedenia, kde môžete určiť možnosti triedenia.

Môžete nastaviť triedenie až podľa troch polí zoznamu a pre každé pole zadať iné poradie.

Excel automaticky rozpoznáva názvy polí, ak sa formát buniek obsahujúcich názvy líši od formátu buniek obsahujúcich údaje.

Dialógové okno Triediť rozsah.

Ak výber rozsahu vykonaný programom nie je úplne správny, nastavte prepínač v spodnej časti dialógového okna do požadovanej polohy (Identifikujte polia podľa „štítkov (prvý riadok rozsahu)“ alebo „označenia stĺpcov listu“).

Možnosti rozsahu a zoradenia, ktoré určíte v dialógovom okne Zoradiť, sa uložia a zobrazia sa v dialógovom okne pri ďalšom otvorení.

Môžete sa tiež rozhodnúť zoradiť stĺpce rozsahu namiesto riadkov. Ak to chcete urobiť, kliknite na tlačidlo „Možnosti“ v dialógovom okne „Rozsah zoradenia“ a v dialógovom okne „Možnosti zoradenia“ v skupine „Zoradiť“ nastavte prepínač do polohy „Stĺpce rozsahu“.

26. Filtrovanie údajov v Exceli.

Filtrovanie údajov v zozname umožňuje zobraziť len tie záznamy, ktoré spĺňajú zadané podmienky.

Automatický filter

Pri použití automatického filtra budú názvy polí doplnené o rozbaľovacie zoznamy, z ktorých si môžete vybrať požadovanú hodnotu poľa alebo nastaviť vlastné kritérium.

Vloženie automatického filtra

1. Umiestnite ukazovateľ bunky do zoznamu.

2. V podponuke Údaje – Filter vyberte príkaz „Automatický filter“. Vedľa názvov polí sa zobrazia tlačidlá so šípkami, na ktoré môžete kliknúť, aby ste otvorili zoznam.

3. Otvorte zoznam pre pole, ktorého hodnotu chcete použiť ako filter (výberové kritérium). V zozname sa zobrazia hodnoty buniek vybraného poľa.

4. Vyberte požadovanú položku zo zoznamu. Na obrazovke sa zobrazia len tie záznamy, ktoré zodpovedajú zadanému filtru.

5. V prípade potreby vyberte požadovaný prvok zo zoznamu iného poľa. Na obrazovke sa zobrazia len tie záznamy, ktoré vyhovujú všetkým zadaným podmienkam filtrovania (podmienky jednotlivých polí sa spájajú pomocou logickej operácie „AND“).

Tlačidlá na otváranie automatických filtrov pre polia, pre ktoré sú špecifikované podmienky filtrovania, sú zafarbené modrou farbou.

Ak ste pred spustením príkazu AutoFilter vybrali jeden alebo viac stĺpcov, rozbaľovacie zoznamy sa pridajú iba do zodpovedajúcich polí.

Ak chcete znova zobraziť všetky položky zoznamu na obrazovke, vykonajte príkaz „Zobraziť všetko“ z podponuky Údaje – Filter.

Kritérium filtrovania pre jednotlivé pole môžete odstrániť výberom položky „Všetko“ v zozname automatického filtra pre toto pole.

Pre deaktiváciu funkcie automatického filtrovania (odstránenie rozbaľovacích zoznamov) opäť zvoľte príkaz Údaje – Filter – Automatický filter.

Použitie vlastného automatického filtra

Pomocou vlastného automatického filtra môžete spájať jednotlivé podmienky výberu záznamov pomocou logických operátorov.

Autofilter vložte do zoznamu výberom príkazu Údaje – Filter – Automatický filter.

Otvorte zoznam automatického filtra pre požadované pole a vyberte v ňom prvok (Podmienku).

V dialógovom okne „Vlastný automatický filter“, ktoré sa otvorí (obr. 6.3.27.), zadajte prvé kritérium.

Vyberte logický operátor, ktorý kombinuje prvé a druhé kritérium.

Dialógové okno „Vlastný automatický filter“.

Vo vlastnom automatickom filtri môžete nastaviť jedno alebo dve kritériá pre jednotlivé pole. V druhom prípade ich možno kombinovať s logickým operátorom „AND“ alebo „OR“.

Nastavte druhé kritérium.

Kliknite na tlačidlo „OK“. Excel bude filtrovať záznamy na základe kritérií, ktoré určíte.

Pokročilý filter

Na nastavenie zložitých podmienok pre filtrovanie údajov zoznamu poskytuje Excel takzvaný rozšírený filter, ktorý používateľovi pomôže.

Rozsah kritérií

Kritériá je možné nastaviť na ľubovoľnom voľnom mieste v pracovnom hárku. V rozsahu kritérií môžete zadať a kombinovať dva typy kritérií:

Jednoduché kritériá: program porovná obsah polí so zadaným kritériom (podobne ako pri použití automatického filtra).

Vypočítané kritériá: V tomto prípade môžete zadať aj hodnoty vrátené vzorcom, ktoré nie sú uvedené.

Pri zadávaní jednoduchých kritérií musíte najprv zadať názvy polí v rozsahu kritérií a môžete zadať iba tie názvy polí, pre ktoré zadávate kritériá.

V riadku (riadkoch) bezprostredne pod riadkom s názvami polí zadávate kritériá. Excel pri zadávaní kritérií nerozlišuje veľké a malé písmená. Môžete pri zadávaní kritérií použiť zástupné znaky? A *.

Všetky kritériá zadané na jednom riadku sa musia vykonať súčasne (zodpovedá logickému operátoru „AND“). Ak chcete špecifikovať spojenie kritérií pomocou operátora „OR“, špecifikujte kritériá na rôznych riadkoch.

Použitie pokročilého filtra

Po vytvorení rozsahu kritérií môžete spustiť rozšírený filter a filtrovať údaje zoznamu.

1. Umiestnite ukazovateľ bunky do zoznamu. V tomto prípade Excel automaticky rozpozná rozsah zoznamu a zobrazí odkaz na rozsah v dialógovom okne.

2. Vykonajte príkaz Údaje – Filter – Rozšírený filter. Umiestnite vstupný kurzor do poľa „Rozsah podmienok“ a zvýraznite zodpovedajúci rozsah v pracovnom hárku.

3. Zatvorte dialógové okno kliknutím na tlačidlo „OK“. Na obrazovke sa teraz zobrazia záznamy, ktoré spĺňajú zadané kritériá.

Na jeden pracovný hárok môžete použiť iba jeden rozšírený filter.

Ak nechcete, aby sa v dôsledku použitia rozšíreného filtra zobrazovali duplicitné záznamy, začiarknite políčko Len jedinečné záznamy v dialógovom okne Rozšírený filter.

Ak chcete po filtrovaní nastaviť zobrazenie všetkých položiek zoznamu v pracovnom hárku, spustite príkaz Údaje – Filter – Zobraziť všetko.

Na vytvorenie pohodlných a komfortných podmienok pre používateľa pri prezeraní a analýze informácií umiestnených v tabuľkách poskytuje program MS Excel pomerne široké možnosti triedenia údajov. Triedenie záznamov vo veľkej databáze v Exceli...

Uľahčuje používateľovi nájsť informácie, ktoré ho zaujímajú.

Pri triedení všetky riadky databázy menia svoje umiestnenie v súlade s nami zvoleným algoritmom, pričom samozrejme zostávajú nezmenené.

Tri hlavné triediace algoritmy v Exceli.

1. číselné hodnoty sú zoradené podľa princípu „od najmenšej po najväčšiu“ alebo naopak.

2. Stĺpce s bunkami obsahujúcimi text hodnoty sú zoradené abecedne v smere dopredu alebo dozadu.

3. Stĺpce s bunkami obsahujúcimi termíny, sú zoradené podľa princípu „od najstarších po najnovšie“ alebo naopak.

Triedenie v Exceli 2007 a novších verziách je možné dodatočne vykonať podľa kritérií formátovania - podľa farby výplne bunky, podľa farby písma bunky, podľa sady ikon podmieneného formátovania.

Pokračujme v práci s databázou DB2 „Výroba kovových konštrukcií podľa lokality č. 2“, vytvorenou v článku „ ».

Uvažovaná tréningová databáza pozostáva iba zo 6 polí (stĺpcov) a 10 záznamov (riadkov). Skutočné databázy zvyčajne obsahujú viac ako tucet polí a tisíce záznamov! Nájsť potrebné informácie v takejto tabuľke nie je jednoduché! Práve cez prizmu takéhoto chápania sa musíme pozerať na naše následné činy.

Ďalšia práca bude štruktúrovaná nasledovne: v tomto a nasledujúcich článkoch tejto série využijeme rôzne možnosti programu PANI Excel , formulovať okamžité odpovede na rôzne otázky a úlohy, ktoré vznikajú pri praktickej činnosti vedúceho výrobného miesta. To znamená, že vyriešime skutočné výrobné problémy!

Udržiavanie databázy – zbieranie informácií – samo o sebe nič nedáva, práve naopak, nejaký čas nám to trvá! Ale výsledky analýzy týchto informácií, ktoré vám umožňujú poskytnúť presné odpovede na rôzne otázky, môžu výrazne zlepšiť spravovateľnosť databázových objektov v reálnom živote!

Vo výrobe pri správnej konštrukcii a starostlivej údržbe databáz umožňuje využitie výsledkov analýz zdvojnásobiť a niekedy aj strojnásobiť objem výroby pri použití rovnakých plôch, zariadení a pri rovnakom zložení technického a technického vybavenia.

Najjednoduchšie triedenie.

Najjednoduchšie triedenie v Exceli sa vykonáva pomocou tlačidiel „Zoradiť vzostupne“ a „Zoradiť zostupne“, ktoré sa nachádzajú na paneli nástrojov „Štandardné“. (Na obrázku nižšie sú tieto tlačidlá zakrúžkované červenou elipsou.)

Úloha č. 1:

Určte: ktorý z vyrobených výrobkov je najťažší a aká je jeho hmotnosť? Kedy bol tento produkt vyrobený?

1. Otvorte súbor v MS Excel.

2. Aktivujeme kliknutím na bunku E7 s nadpisom stĺpca „Hmotnosť 1 kusu, t“ (môžete aktivovať ktorúkoľvek bunku v stĺpci, ktorá nás zaujíma).

3. Kliknite na tlačidlo „Zoradiť zostupne“ na paneli nástrojov „Štandardné“.

4. Odpoveď na otázku položenú čítame v úplne hornom riadku databázy (riadok č. 8). Najťažším výrobkom v databáze je Nosník 045 z objednávky č. 2. Nosník 045 bol vyrobený od 23. apríla do 25. apríla 2014 (pozri záznamy v riadkoch Excelu č. 8-10).

5. Databázu môžete vrátiť do formulára pred triedením v Exceli (ak je to potrebné) kliknutím na tlačidlo „Zrušiť“ na rovnakom paneli nástrojov „Štandard“. Alebo môžete použiť triedenie vzostupne na stĺpec Dátum v databáze.

Triedenie v Exceli podľa viacerých stĺpcov.

Triedenie týmto spôsobom možno vykonať postupne v dvoch alebo troch stĺpcoch.

Úloha č. 2:

Prezentujte záznamy databázy zoskupené podľa objednávok a s názvami produktov v rámci objednávok usporiadaných abecedne.

1. Kliknutím myšou aktivujeme ľubovoľnú databázovú bunku (napríklad bunku C11).

2. Kliknite na tlačidlo hlavného menu „Údaje“ a vyberte „Zoraďovanie...“.

3. V okne „Rozsah triedenia“, ktoré sa objaví, vyberte hodnoty z rozbaľovacích zoznamov, ako je znázornené na snímke obrazovky vľavo, a kliknite na „OK“.

4. Úloha č.2 splnená. Záznamy sú po prvé zoradené podľa poradových čísel a po druhé v rámci každej zákazky sú zoradené abecedne podľa názvov produktov.

Výsledky.

V tomto druhom článku zo série o ukladaní informácií a správe údajov Pozreli sme sa na základné možnosti triedenia databázových záznamov v Exceli. Tento veľmi jednoduchý mechanizmus Excelu určite pozná väčšina z vás, milí čitatelia. Dúfam, že som vám nezabral príliš veľa času banálnym príbehom o známej službe. Ale... cesta ku komplexu začína jednoduchým!

Úprimne povedané, v praxi sa zriedkakedy musíte uchýliť k triedeniu. prečo? Odpoveď na túto otázku budú v nasledujúcich článkoch série, ktoré budú hovoriť o ďalších, vrátane pokročilejších a výkonnejších mechanizmoch Excelu na rýchle a efektívne spracovanie údajov.

ja prosím úctivý autorské dielo predplatiť pre oznámenia článkov v okne umiestnenom na konci každého článku alebo v okne v hornej časti stránky!

Vážení čitatelia, píšte svoje recenzie a komentáre do komentárov.

Triedenie údajov umiestnených v oblasti riadkov a stĺpcov kontingenčnej tabuľky sa štandardne vykonáva vo vzostupnom poradí (obr. 1a) alebo pomocou vlastných triediacich zoznamov. Nie vždy to užívateľovi vyhovuje. Napríklad, keď chcete zobraziť zákazníkov s najvyšším príjmom na začiatku zoznamu (obr. 1b). Ak je vaša kontingenčná tabuľka zoradená vo vzostupnom (zostupnom) poradí, mali by ste vytvoriť pravidlo, ktoré riadi poradie zoradenia podľa poľa. Navyše toto pravidlo (pre toto pole) sa uplatní aj po pridaní nových polí do kontingenčnej tabuľky (obr. 1c).

Ryža. 1. Triedenie podľa poľa Zákazník: (a) štandardne – od A do Z; b) v poradí klesajúceho príjmu; c) triediť podľa poľa Zákazník sa pri pridávaní poľa nezmenilo Sektor

Stiahnite si poznámku vo formáte alebo formáte, príklady vo formáte

Triedenie zákazníkov v zostupnom poradí podľa príjmu

Ak chcete zoradiť riadky kontingenčnej tabuľky v zostupnom poradí podľa príjmu, vyberte ľubovoľnú bunku v stĺpci Suma podľa poľa Príjem, napríklad E4 (ale nie názov), a kliknite na ikonu JA nachádza na karte Údaje(obr. 2). Toto triedenie sa podobá štandardnému, ale ide len o povrchnú podobnosť. Keď zoradíte kontingenčnú tabuľku, Excel vytvorí pravidlo, ktoré bude fungovať aj po vykonaní ďalších zmien v kontingenčnej tabuľke.

Na príklade kontingenčnej tabuľky umiestnenej v stĺpcoch G:I (obr. 1c) môžete vidieť, čo sa stane po pridaní nového externého riadkového poľa Sektor. Kontingenčná tabuľka naďalej zoraďuje údaje v zostupnom poradí podľa príjmu v rámci každého sektora. Napríklad v sektore Výroba General Motors je na prvom mieste s príjmami 750 163 USD. V tesnom závese nasleduje Ford s príjmami 622 794 USD. Aj keď pole vymažete Zákazník z kontingenčnej tabuľky vykonajte ďalšie nastavenia a vráťte toto pole späť, ale do oblasti stĺpcov si Excel zapamätá triedenie zákazníkov v zostupnom poradí podľa príjmu.

Aby sa v súhrnnej tabuľke umiestnenej v stĺpcoch G:I (obr. 1c) zoradili aj sektory v zostupnom poradí podľa príjmov, môžete ísť jedným z troch spôsobov:

• Vyberte bunku G4, kliknite pravým tlačidlom myši a vyberte Zbaliť všetkolúka skryť všetky prvky, ktoré sa týkajú zákazníka. Keď sa na obrazovke zobrazí iba jeden sektor, vyberte bunku I4 a kliknite na ikonu YA na karte Údaje vykonať triedenie v zostupnom poradí. Tým sa vytvorí pravidlo triedenia pre pole Sektor. Znova vyberte bunku G4, kliknite pravým tlačidlom myši a vyberte Rozbaliť všetkolúka.
• Dočasne odstráňte pole Zákazník z kontingenčnej tabuľky zoraďte tabuľku v zostupnom poradí podľa príjmu (použitím metódy opísanej na obr. 2) a potom pole vráťte späť Zákazník.
• Využite schopnosti tímu Dodatočnémožnostitriedenie(Používam presne túto metódu). Vyvolanie príkazu: (a) Vyberte bunku G4, kliknite pravým tlačidlom myši a vyberte Triedenie → Dodatočnémožnostitriedenie(obr. 3) alebo (b) kliknite na ikonu trojuholníka v poli Sektor a potom vyberte Dodatočnémožnostitriedenie(obr. 4). V oboch prípadoch sa otvorí okno Triedenie(obr. 5). Nastavte prepínač do polohy zostupne a vyberte riadok Suma podľa poľa Príjem.

Ryža. 3. Zavolajte príkaz Dodatočnémožnostitriedenie kliknite pravým tlačidlom myši

Ryža. 4. Zavolajte príkaz Dodatočnémožnostitriedenie pomocou menu Triedenie a filtre poliach Sektor

Ryža. 5. Nastavenie parametrov v okne Sektor

V ľavom dolnom rohu dialógového okna Triedenie je tu tlačidlo Rozšírené... Po kliknutí na toto tlačidlo sa na obrazovke zobrazí dialógové okno . V tomto okne môžete: (a) definovať vlastný zoznam, ktorý sa použije na triedenie podľa prvého kľúča (podrobnosti pozri nižšie); b) namiesto stĺpca úhrn Vyberte iný stĺpec ako stĺpec základného triedenia.

Napríklad pre kontingenčnú tabuľku zobrazenú na obr. 6 môžete nastaviť triedenie nie podľa celkových príjmov, ale podľa príjmov z predaja jedného druhu tovaru, napr. Zariadenia(všimnite si, že zákazníci nie sú zoradení podľa stĺpca F, ale podľa stĺpca C).

Ryža. 6. Ďalšie parametre umožňujú triediť zákazníkov nie podľa celkových príjmov, ale podľa príjmov z predaja produktov Zariadenie

Ak chcete triediť takto:

1. Rozbaľte zoznam zákazníkov umiestnený v bunke A4.
2. Vyberte možnosť Ďalšie možnosti zoradenia.
3. V dialógovom okne Triedenie (zákazník) kliknite na tlačidlo Okrem toho…
4. V dialógovom okne Ďalšie možnosti triedenia (zákazník) Vyberte sekciu Poradie triedenia a nastavte prepínač Hodnoty vo zvýraznenom stĺpci.
5. Kliknite do poľa prepojenia a potom vyberte bunku C5. Upozorňujeme, že musíte kliknúť do jednej z buniek s hodnotami Zariadenie, pretože na hlavičke Zariadenie Na bunku C4 sa nedá kliknúť.
6. Ak chcete dokončiť nastavenie parametrov, dvakrát kliknite na tlačidlo OK.

Nezľaknite sa, popis tohto algoritmu krok za krokom je uvedený skôr na vzdelávacie účely. Počnúc Excelom 2013 je triedenie údajov kontingenčnej tabuľky oveľa jednoduchšie. Teraz sú tlačidlá YA a AYA na karte Údaje používať inteligentné triediace algoritmy. Pri pokuse o triedenie pomocou týchto tlačidiel sa program pokúsi predpovedať zámery používateľa na základe toho, ktorá bunka bola zvýraznená pred stlačením tlačidla triedenia (obrázok 7):

• A1, C1, D1, E1, F1, F2, A30, F30 – nedostupné
• A2:A29 – zoradí mená zákazníkov v stĺpci A podľa abecedy
• В1, В2, С2, D2, E2 – zoradí názvy tovarov podľa abecedy v riadku 2
• B30, C30, D30, E30 – usporiada v zostupnom (vzostupnom) poradí výšku príjmu v riadku 30.
• vzostupný (zostupný) predaj B3:B29 – moduly, C3:C29 – prístroje, D3:D29 – diely, E3:E29 – lieky, F3:F29 – celk.

Manuálne triedenie

Upozorňujeme, že v dialógovom okne Triedenie(pozri obr. 5) môžete ručne definovať pravidlá triedenia údajov. Ručné triedenie kontingenčnej tabuľky sa však vykonáva aj iným, dosť neobvyklým spôsobom. V zostave kontingenčnej tabuľky na obr. 8a ukazuje poradie kategórií produktov zoradených v abecednom poradí: Časť, modul, príprava A Zariadenie. Upozorňujeme, že objem predaných produktov podľa kategórie Detail, nie najväčší. A sotva sa oplatí zobrazovať túto kategóriu ako prvú. Umiestnite ukazovateľ myši do bunky E4 a zadajte slovo Detail. Stačí stlačiť kláves Zadajte Ako Excel určí, že ste sa rozhodli presunúť stĺpec Detail do posledného stĺpca tabuľky. Všetky číselné hodnoty súvisiace s touto kategóriou produktov sa presunú zo stĺpca B do stĺpca E. Hodnoty súvisiace s ostatnými kategóriami produktov sa presunú doľava. Toto správanie sa zdá nelogické a je typické len pre kontingenčné tabuľky Excelu. Bežná množina údajov Excelu sa nedá takto preusporiadať. Na obr. Obrázok 8b zobrazuje kontingenčnú tabuľku po presunutí novej hlavičky stĺpca do bunky E4.

Ryža. 8. Manuálne triedenie: (a) kategórie produktov sú zoradené podľa abecedy, (b) kategória Diel je na poslednom mieste

Nadšenci myši môžu jednoducho pretiahnuť požadované nadpisy stĺpcov (alebo jednotlivé riadky). Kliknite do oblasti hlavičky stĺpca a podržte ukazovateľ myši nad hranicou vybratého rozsahu buniek, kým sa nezmení na šípku so štyrmi hrotmi. Začnite ťahať bunku na vybrané miesto; Zobrazí sa ukazovateľ vo forme hrubej čiary a pätiek. Hneď ako uvoľníte tlačidlo myši, číselné hodnoty sa okamžite presunú do nového stĺpca. Upozorňujeme, že pri použití manuálneho triedenia sa produkty pridané do zdroja údajov pridajú na koniec zoznamu. Je to spôsobené tým, že Excel nevie, kam presne pridať nový región.

Triedenie údajov podľa vlastných zoznamov

Ďalším riešením problému prispôsobenia poradia, v ktorom sú polia prezentované, je vytvorenie vlastných zoznamov. Pomocou takéhoto zoznamu sa budú triediť kontingenčné tabuľky vytvorené v budúcnosti. Excel predvolene obsahuje štyri vlastné zoznamy: dni v týždni, mesiace v roku a skrátené názvy dní v týždni a mesiacov v roku. Program zoraďuje názvy dní v týždni v prirodzenom poradí, počnúc Po a končiac Slnkom (a nie podľa abecedy).

Ak chcete vytvoriť vlastný zoznam triedenia, postupujte takto:

1. V oblasti pracovného hárka bez údajov zadajte názvy kategórií produktov v poradí, ktoré zodpovedá vlastnému zoznamu, ktorý vytvárate. Do každej bunky zadajte jeden názov a názvy umiestnite do jedného stĺpca (obr. 9).
2. Vyberte výsledný zoznam názvov kategórií produktov (bunky A10:A13).
3. Vyberte kartu s nástrojmi Súbor a v spodnej časti navigačnej lišty zobrazenej v okne vľavo kliknite na tlačidlo možnosti otvoríte dialógové okno Možnosti programu Excel.
4. vyberte kategóriu Okrem toho, prejdite do sekcie Sú bežné a kliknite na tlačidlo Upraviť zoznamy.
5. V dialógovom okne zoznamy v poli sa zobrazí adresa rozsahu obsahujúceho vopred zvolený zoznam titulov Import zoznamu z buniek(obr. 10). Kliknite na tlačidlo Importovať na vygenerovanie nového zoznamu kategórií produktov na základe zadaných údajov. Do spodnej časti oblasti sa pridá nový zoznam zoznamy.
6. Kliknutím na tlačidlo OK zatvorte dialógové okno zoznamy. Opätovným kliknutím na tlačidlo OK zatvorte dialógové okno Možnosti programu Excel.

Ryža. 10. Okno zoznamy

Novovytvorený zoznam sa uloží v nastaveniach programu a bude dostupný v budúcich reláciách programu Excel. Teraz sa všetky kontingenčné tabuľky vytvorené v budúcnosti automaticky zoradia podľa produktového poľa v súlade s poradím uvedeným v zozname. Na obr. Obrázok 11 zobrazuje novú kontingenčnú tabuľku (ktorá bola vytvorená na základe po pridaní vlastného zoznamu produktov) zoradenú podľa vytvoreného zoznamu.

Ak chcete zoradiť predtým vytvorené kontingenčné tabuľky podľa nového vlastného zoznamu, postupujte takto:

1. Rozbaľte zoznam polí Produkt a vyberte možnosť Ďalšie možnosti triedenia.
2. V dialógovom okne Triedenie (produkt) tlačidlo výberu vzostupne (od A po Z) podľa poľa a z rozbaľovacieho zoznamu vyberte Produkt.
3. Kliknite na tlačidlo Okrem toho…
4. V dialógovom okne Ďalšie možnosti zoradenia (produkt) zrušte začiarknutie políčka Automatické triedenie.
5. Rozšírte zoznam Zoradiť podľa prvého kľúča a vyberte zoznam, ktorý obsahuje názvy kategórií produktov (obr. 12).
6. Dvakrát kliknite na tlačidlo OK.

Poznámka bola napísaná na základe knihy Billa Jelena, Michael Alexander. . Kapitola 4.

Triedenie údajov v Exceli je usporiadanie tabuľkových údajov v požadovanom poradí, napríklad vzostupne (od najmenšieho po najväčšie) alebo zostupne (od najväčšieho po najmenšie). Zoradené sú číselné a textové hodnoty, hodnoty dátumu a času a formáty. Triedenie údajov je možné podľa stĺpcov aj riadkov. Pred zoradením sa musia zobraziť skryté riadky a stĺpce.

Typy údajov na triedenie a poradie triedenia

Zoraďte číselné hodnoty v Exceli

Vzostupné triedenie číselných hodnôt je usporiadanie hodnôt, v ktorom sú hodnoty usporiadané od najmenšej po najväčšiu (od minima po maximum).

V súlade s tým je triedenie číselných hodnôt v zostupnom poradí usporiadaním hodnôt od najväčšej po najmenšiu (od maxima po minimum).

Triedenie textových hodnôt v Exceli

"Zoraďovanie od A do Z" - triedenie údajov vo vzostupnom poradí;

"Zoradiť od Z po A" - zoradenie údajov v zostupnom poradí.

Na abecedné zoradenie textových hodnôt sa tieto hodnoty navzájom porovnávajú. Ako viete, pri ukladaní textu počítač používa schému, v ktorej má každý znak svoje vlastné jedinečné číslo, nazývané kód znaku. Práve tieto kódy sa porovnávajú, aby sa určilo, ktorá textová hodnota je väčšia a ktorá menšia.

Textové hodnoty môžu obsahovať abecedné, číselné a špeciálne znaky. V tomto prípade je možné čísla uložiť v číselnom aj textovom formáte. Čísla uložené v číselnom formáte sú menšie ako čísla uložené v textovom formáte. Pre správne triedenie textových hodnôt musia byť všetky údaje uložené v textovom formáte. Okrem toho, keď sa do buniek vkladajú textové údaje z iných aplikácií, údaje môžu obsahovať úvodné medzery. Pred začatím triedenia musíte zo zoraďovaných údajov odstrániť úvodné medzery (alebo iné netlačiteľné znaky), inak sa triedenie nevykoná správne.

Textové údaje môžete triediť tak, aby sa rozlišovali malé a veľké písmená. Ak to chcete urobiť, musíte v možnostiach triedenia zaškrtnúť políčko „Zvážiť prípad“.

Veľké písmená majú zvyčajne nižšie čísla ako malé písmená.

Zoraďte hodnoty dátumu a času

„Zoradiť od najstarších po najnovšie“ slúži na zoradenie hodnôt dátumu a času od najstaršej hodnoty po najnovšiu.

„Zoradiť od nových po staré“ slúži na zoradenie hodnôt dátumu a času od najnovšej hodnoty po najskoršiu.

Formáty triedenia

Microsoft Excel 2007 a novší poskytuje triedenie podľa formátovania. Táto metóda triedenia sa používa, keď je rozsah buniek formátovaný pomocou farby výplne bunky, farby písma alebo sady ikon. Farby výplne a písma v Exceli majú svoje vlastné kódy a práve tieto kódy sa používajú pri triedení formátov.

Zoradiť podľa vlastného zoznamu

Tabuľkové údaje možno triediť podľa vlastných zoznamov, ako je zoznam mesiacov, zoznam dní v týždni, zoznam štrukturálnych divízií podniku, zoznam personálnych čísiel zamestnancov atď. Excel má schopnosť vytvárať vlastné zoznamy na triedenie údajov. Pri tomto poradí triedenia sa údaje, ktoré sa majú triediť, a hodnoty zoznamu musia zhodovať.

Možnosti triedenia

Zoradiť podľa stĺpca

V akejkoľvek verzii Excelu je predvolene nastavené triedenie podľa stĺpca, to znamená, že hodnoty buniek vybratého stĺpca sú usporiadané v požadovanom poradí a riadky rozsahu sú zamenené v závislosti od polohy bunky. v triedenom stĺpci.Ak chcete zoradiť tabuľku podľa stĺpca, stačí nastaviť značku výberu do ľubovoľnej bunky tabuľky a kliknúť na jednu z ikon symbolizujúcich zoradenie od minima po maximum alebo od maxima po minimum. Tabuľka bude zoradená podľa stĺpca, ktorý obsahuje značku výberu.

Zoradiť podľa reťazca

V nastaveniach programu môžete zmeniť triedenie podľa stĺpca na triedenie podľa riadkov. Ak je nastavené triedenie podľa riadka, hodnoty buniek vo vybranom riadku sú usporiadané v určenom poradí a stĺpce sú vymenené v závislosti od pozícií buniek v zoradenom riadku.

Viacúrovňové triedenie

Ak teda zoradíte podľa stĺpca, riadky sa vymenia, ak sa údaje zoradia podľa riadka, vymenia sa stĺpce.

Excel umožňuje triediť údaje nielen podľa jedného stĺpca alebo jedného riadka, ale aj vytvorením iného počtu úrovní triedenia. Napríklad v Exceli 2007 je ich 64. Úrovne triedenia je možné pridávať, mazať, kopírovať a zamieňať.

Okrem iného môžete pri triedení buď brať do úvahy veľkosť písmen alebo ich ignorovať.

Doplnok na triedenie údajov v Exceli

Program Microsoft Excel poskytuje používateľom pomerne veľkú sadu štandardných nástrojov na triedenie hodnôt rôznych typov, existujú však úlohy, ktorých riešenie pomocou štandardných nástrojov je buď nepohodlné alebo časovo náročné, napríklad triedenie každého riadka/každého stĺpca v spôsob, že triedenie sa vykonáva iba v rámci riadka/stĺpca a neovplyvňuje susedné bunky.