Seřaďme tabulku sestávající ze 2 sloupců pomocí vzorců. Budeme řadit podle jednoho ze sloupců tabulky (budeme řešit 2 problémy: řazení tabulky podle číselného a řazení podle textového sloupce). Nastavíme třídicí vzorce tak, aby se po přidání nových dat do zdrojové tabulky setříděná tabulka dynamicky měnila. To vám umožní mít vždy seřazenou tabulku bez zásahu uživatele. Provedeme také dvouúrovňové řazení: nejprve číselně, poté (pro opakování čísel) podle textového sloupce.

Nechť existuje tabulka skládající se ze 2 sloupců. Jeden sloupec je text: Seznam ovoce; a druhý je číselný Objem prodeje(viz ukázkový soubor).

Problém 1 (Seřazení tabulky podle sloupce čísel)

Je třeba seřadit řádky tabulky podle obsahu číselného sloupce (podle objemu prodeje). Předpokládá se, že uživatel neustále vyplňuje řádky tabulky, proto je nutné psát vzorce zohledňující přidané hodnoty.

Pro přehlednost hodnoty ve sloupci Objem prodeje zvýrazněno pomocí (). Opakované hodnoty jsou také zvýrazněny žlutě.

Poznámka: Problém řazení jednoho sloupce (seznamu) byl vyřešen v článcích a.

Řešení1

Pokud je zaručeno, že číselný sloupec neobsahuje žádné hodnoty, problém lze snadno vyřešit:

• Seřaďte číselný sloupec pomocí funkce LARGE() (viz článek);
• Funkce VLOOKUP() nebo hromada funkcí INDEX()+MATCH() vyberte hodnoty z textového sloupce podle jeho odpovídající číselné hodnoty.

Ve skutečných problémech však může číselný sloupec obsahovat opakování, a protože funkce VLOOKUP() v případě opakování vždy vybere pouze první hodnotu shora (viz článek), není tento přístup vhodný (názvy ovoce se zobrazí nesprávně).

Proto bude muset být mechanismus třídění implementován jinak.

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

Tento vzorec třídí sloupec Objem prodeje(dynamický rozsah Odbyt) v sestupném pořadí. Mezery ve zdrojové tabulce nejsou povoleny. Počet řádků ve zdrojové tabulce musí být menší než 1000.

Podívejme se na vzorec podrobněji:

• Vzorec COUNTIF(prodej;"<"&Продажи) vrátí pole (4:5:0:2:7:1:3:5). To znamená, že číslo 64 (z buňky B7 původní tabulka, tzn. první číslo z rozsahu Odbyt) více než 4 hodnoty ze stejného rozsahu; číslo 74 (z cel B8 původní tabulka, tzn. druhé číslo z rozsahu Odbyt) více než 5 hodnot ze stejného rozsahu; další číslo 23 je nejmenší (není větší než kdokoli jiný) atd.
• Nyní převedeme výše uvedené pole celých čísel na pole čísel se zlomkovou částí, kde zlomková část bude obsahovat číslo pozice čísla v poli: (4,001:5,002:0,003:2,004:7,005:1,006:3,007:5,008 ). To je implementováno výrazem &","&REPEAT("0";3-DÉLKA(ŘÁDEK(prodej)-ŘÁDEK($E$6)))&ŘÁDEK(prodej)-ŘÁDEK(6 $E$)) Právě v této části vzorce je omezení na maximálně 1000 řádků ve zdrojové tabulce (viz výše). V případě potřeby jej lze snadno změnit, ale je to zbytečné (viz část o rychlosti výpočtu níže).
• Funkce LARGE() seřadí výše uvedené pole.
• Funkce ROD() vrací zlomkovou část čísla, což jsou čísla pozic/1000, například 0,005.
• Funkce ROUND() po vynásobení 1000 zaokrouhlí na celé číslo a vrátí číslo pozice. Nyní všechna čísla pozic odpovídají číslům sloupců objemy prodeje, seřazené v sestupném pořadí.
• Funkce INDEX() s číslem pozice vrátí číslo, které mu odpovídá.

Podobný vzorec lze zapsat pro zobrazení hodnot ve sloupci Ovoce=INDEX(Ovoce,KULATÝ(...))

V ukázkovém souboru je kvůli úvahám o rychlosti výpočtu (viz níže) stejný typ části vzorce, tzn. vše uvnitř funkce ROUND() je umístěno v samostatném sloupci J . Výsledné vzorce v seřazené tabulce tedy vypadají takto: =INDEX(ovoce,J7) A =INDEX(prodej,J7)

Také změnou funkce LARGE() na SMALL() v maticovém vzorci získáme řazení ve vzestupném pořadí.

Pro přehlednost hodnoty ve sloupci Objem prodeje zvýrazněno ( Domů/ Styly/ Podmíněné formátování/ Histogramy). Jak vidíte, řazení funguje.

Testování

Nyní do původní tabulky přidáme nový řádek. V dynamicky řazených tabulkách musíme získat příslušné řazení.

1. Do buňky A15 zdrojová tabulka zadejte slovo Mrkev;
2. Do buňky B15 vstoupit Objem prodeje Mrkev = 25;
3. Po zadání hodnot do sloupců D A E automaticky se zobrazí tabulka seřazená sestupně;
4. V setříděné tabulce se nový řádek zobrazí jako předposlední.

Rychlost výpočtu vzorce

Na „průměrném“ počítači z hlediska výkonu přepočet na pár takových maticové vzorce, umístěn ve 100 řádcích, je prakticky neviditelný. U tabulek s 300 řádky trvá přepočet 2-3 sekundy, což je nepohodlné. Nebo musíte zakázat automatické přepočítávání listu ( Vzorce/Výpočty/Možnosti výpočtu) a periodicky tiskněte tlačítko F9, buď upustit od používání maticových vzorců a nahradit je sloupci s odpovídajícími vzorci, nebo zcela opustit dynamické třídění ve prospěch standardních přístupů (viz další část).

Alternativní přístupy k řazení tabulek

Seřaďme řádky zdrojové tabulky pomocí standardního filtru (vybereme záhlaví zdrojové tabulky a klikneme CTRL+SHIFT+L). Z rozevíracího seznamu vyberte požadované řazení.

Získáme verzi tabulky shodnou s naší, ale při přidávání nových hodnot do tabulky budeme muset znovu použít filtr.

Můžete také použít nástroj pro řazení ( Data/Sort and Filter/Sort). Chcete-li to provést, musíte vybrat všechny hodnoty zdrojové tabulky, kromě záhlaví, zavolat nástroj pro řazení, vybrat sloupec, podle kterého chcete seřadit, a možnost řazení.

Získáme verzi tabulky shodnou s naší, ale při přidávání nových hodnot také budeme muset znovu použít filtr.

Stejně jako v předchozím problému předpokládejme, že ve sloupci, podle kterého se třídění provádí, jsou opakování (názvy Ovoce se opakují).

Pro seřazení tabulky budete muset vytvořit 2 sloupce služeb (D a E).

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

Tento vzorec je analogický textovým hodnotám (umístění hodnoty vzhledem k ostatním hodnotám v seznamu). Textová hodnota nižší v abecedě má vyšší „hodnocení“. Například hodnota Jablka odpovídá maximálnímu „hodnocení“ 7 (včetně opakování).

Do sloupce E zadejte obvyklý vzorec:

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

Tento vzorec zohledňuje opakování textových hodnot a upravuje „hodnocení“. Nyní různé hodnoty jablek odpovídají různým „hodnostem“ - 7 a 8. To vám umožní zobrazit seznam seřazených hodnot. K tomu použijte vzorec (sloupec G):

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

Podobný vzorec zobrazí odpovídající objem prodeje (sloupec H).

Problém 2.1 (Dvouúrovňové řazení)

Nyní seřaďme původní tabulku znovu podle objemu prodeje. Ale nyní pro opakování hodnot (ve sloupci A jsou tři hodnoty 74), zobrazíme odpovídající hodnoty v abecedním pořadí.

K tomu použijeme výsledky Problému 1.1 a Problému 2.

Podrobnosti v ukázkovém souboru na listu Úloha2.

Seznam můžete seřadit abecedně, číselně nebo chronologicky až podle tří polí. Pro každý sloupec můžete určit vzestupné a sestupné řazení.

Vzestupně řazení:

Čísla budou seřazeny v pořadí od nejmenšího záporného po největší kladné číslo.

Hodnoty data a času budou seřazeny od nejstarší po nejnovější.

Text bude řazen abecedně. V tomto případě budou nejprve umístěny číselné hodnoty zadané jako text.

Při řazení booleovských hodnot ve vzestupném pořadí se nejprve zobrazí hodnota FALSE a poté hodnota TRUE.

Chybové hodnoty budou seřazeny v pořadí, ve kterém byly zjištěny (z hlediska řazení jsou všechny stejné).

Na konci seřazeného seznamu se objeví prázdné buňky.

Sestupné pořadí řazení:

V případě sestupného řazení seřadí Excel záznamy v opačném pořadí popsaném výše. Na konci seznamu budou stále zobrazeny pouze prázdné buňky.

Vlastní pořadí řazení:

Namísto vzestupného a sestupného řazení můžete také použít vlastní řazení určené zadaným vlastním seznamem.

Řazení seznamu

Chcete-li seznam seřadit, umístěte ukazatel na buňku do seznamu a spusťte příkaz Data – Seřadit.

Excel automaticky vybere seznam a zobrazí dialogové okno Rozsah řazení, kde můžete určit možnosti řazení.

Můžete nastavit řazení až podle tří polí seznamu a pro každé pole zadat jiné pořadí řazení.

Excel automaticky rozpozná názvy polí, pokud se formát buněk obsahujících názvy liší od formátu buněk obsahujících data.

Dialogové okno Seřadit rozsah.

Pokud není výběr rozsahu provedený programem zcela správný, nastavte přepínač ve spodní části dialogového okna do požadované polohy (Identifikujte pole podle „popisů (první řádek rozsahu)“ nebo „označení sloupců listu“).

Možnosti rozsahu a řazení, které určíte v dialogovém okně Seřadit, se uloží a zobrazí se v dialogovém okně při příštím otevření.

Můžete se také rozhodnout seřadit sloupce rozsahu místo řádků. Chcete-li to provést, klikněte na tlačítko „Možnosti“ v dialogovém okně „Rozsah řazení“ a v dialogovém okně „Možnosti řazení“ ve skupině „Řadit“ nastavte přepínač do polohy „Sloupce rozsahu“.

26. Filtrování dat v Excelu.

Filtrování dat v seznamu umožňuje zobrazit pouze ty záznamy, které splňují zadané podmínky.

Autofiltr

Při použití automatického filtru budou názvy polí doplněny o rozevírací seznamy, ze kterých můžete vybrat požadovanou hodnotu pole nebo nastavit vlastní kritérium.

Vložení automatického filtru

1. Umístěte ukazatel buňky do seznamu.

2. V podnabídce Data – Filtr vyberte příkaz „Automatický filtr“. Vedle názvů polí se zobrazí tlačítka se šipkami, na která můžete kliknutím otevřít seznam.

3. Otevřete seznam pro pole, jehož hodnotu chcete použít jako filtr (výběrové kritérium). V seznamu se zobrazí hodnoty buněk vybraného pole.

4. Vyberte požadovanou položku ze seznamu. Na obrazovce se zobrazí pouze záznamy, které odpovídají zadanému filtru.

5. V případě potřeby vyberte požadovaný prvek ze seznamu jiného pole. Na obrazovce se zobrazí pouze ty záznamy, které vyhovují všem zadaným podmínkám filtrování (podmínky jednotlivých polí se spojí pomocí logické operace „AND“).

Tlačítka pro otevření automatických filtrů pro pole, pro která jsou specifikovány podmínky filtrování, jsou zbarvena modře.

Pokud jste před spuštěním příkazu Automatický filtr vybrali jeden nebo více sloupců, rozbalovací seznamy budou přidány pouze do odpovídajících polí.

Chcete-li znovu zobrazit všechny položky seznamu na obrazovce, proveďte příkaz „Zobrazit vše“ z podnabídky Data – Filtr.

Filtrovací kritérium pro jednotlivá pole můžete odstranit výběrem položky „Vše“ v seznamu automatického filtru pro toto pole.

Pro deaktivaci funkce autofiltru (odstranění rozbalovacích seznamů) znovu zvolte příkaz Data – Filtr – Autofiltr.

Použití vlastního automatického filtru

Pomocí vlastního automatického filtru můžete propojit jednotlivé podmínky výběru záznamů pomocí logických operátorů.

Autofiltr vložte do seznamu výběrem příkazu Data – Filtr – Autofiltr.

Otevřete seznam automatického filtru pro požadované pole a vyberte v něm prvek (Podmínku).

V dialogovém okně „Vlastní automatický filtr“, které se otevře (obr. 6.3.27.), zadejte první kritérium.

Vyberte logický operátor, který kombinuje první a druhé kritérium.

Dialogové okno „Vlastní automatický filtr“.

Ve vlastním automatickém filtru můžete nastavit jedno nebo dvě kritéria pro jednotlivá pole. V druhém případě je lze kombinovat s logickým operátorem „AND“ nebo „OR“.

Nastavte druhé kritérium.

Klikněte na tlačítko „OK“. Excel bude filtrovat záznamy na základě kritérií, která zadáte.

Pokročilý filtr

Pro nastavení složitých podmínek pro filtrování dat seznamu poskytuje Excel tzv. pokročilý filtr, který má uživateli pomoci.

Rozsah kritérií

Kritéria lze nastavit na libovolné volné místo na listu. V rozsahu kritérií můžete zadat a kombinovat dva typy kritérií:

Jednoduchá kritéria: program porovná obsah polí se zadaným kritériem (podobně jako při použití automatického filtru).

Vypočítaná kritéria: V tomto případě můžete také zadat hodnoty vrácené vzorcem, které nejsou uvedeny.

Při zadávání jednoduchých kritérií musíte nejprve zadat názvy polí v rozsahu kritérií a můžete zadat pouze názvy polí, pro která zadáváte kritéria.

Řádek (řádky) bezprostředně pod řádkem s názvy polí je místo, kde zadáváte kritéria. Excel nerozlišuje velká a malá písmena při zadávání kritérií. Můžete při zadávání kritérií použít zástupné znaky? A *.

Všechna kritéria zadaná na jednom řádku musí být provedena současně (odpovídá logickému operátoru „AND“). Chcete-li určit spojení kritérií pomocí operátoru „OR“, zadejte kritéria na různých řádcích.

Použití pokročilého filtru

Jakmile vytvoříte rozsah kritérií, můžete spustit rozšířený filtr a filtrovat data seznamu.

1. Umístěte ukazatel buňky do seznamu. V tomto případě Excel automaticky rozpozná rozsah seznamu a v dialogovém okně zobrazí odkaz na rozsah.

2. Proveďte příkaz Data – Filtr – Rozšířený filtr. Umístěte vstupní kurzor do pole „Rozsah podmínek“ a zvýrazněte odpovídající rozsah v listu.

3. Zavřete dialogové okno kliknutím na tlačítko „OK“. Na obrazovce se nyní zobrazí záznamy, které splňují zadaná kritéria.

Na jeden list můžete použít pouze jeden rozšířený filtr.

Pokud nechcete, aby se v důsledku použití rozšířeného filtru zobrazovaly duplicitní záznamy, zaškrtněte políčko Pouze jedinečné záznamy v dialogovém okně Rozšířený filtr.

Chcete-li po filtrování nastavit zobrazení všech položek seznamu v listu, spusťte příkaz Data – Filtr – Zobrazit vše.

Pro vytvoření pohodlných a komfortních podmínek pro uživatele při prohlížení a analýze informací umístěných v tabulkách poskytuje program MS Excel poměrně široké možnosti třídění dat. Třídění záznamů ve velké databázi v Excelu...

Poněkud usnadňuje uživateli vyhledání informací, které ho zajímají.

Při řazení mění všechny řádky databáze své umístění v souladu s námi zvoleným algoritmem, přičemž zůstávají samozřejmě beze změny.

Tři hlavní třídicí algoritmy v Excelu.

1. číselné hodnoty jsou seřazeny podle principu „od nejmenšího po největší“ nebo naopak.

2. Sloupce s buňkami obsahujícími text hodnoty jsou seřazeny abecedně vpřed nebo vzad.

3. Sloupce s buňkami obsahujícími Termíny, jsou seřazeny podle principu „od nejstaršího po nejnovější“ nebo naopak.

Řazení v Excelu 2007 a novějších verzích lze navíc provádět podle kritérií formátování - podle barvy výplně buňky, podle barvy písma buňky, pomocí sady ikon podmíněného formátování.

Pokračujme v práci s databází DB2 „Výroba kovových konstrukcí podle lokality č. 2“, vytvořenou v článku „ ».

Uvažovaná školicí databáze se skládá pouze ze 6 polí (sloupců) a 10 záznamů (řádků). Skutečné databáze obvykle obsahují více než tucet polí a tisíce záznamů! Najít potřebné informace v takové tabulce není jednoduché! Právě prizmatem takového porozumění se musíme dívat na naše následné činy.

Další práce bude strukturována následovně: v tomto a následujících článcích této série využijeme různé možnosti programu SLEČNA Vynikat , formulovat okamžité odpovědi na různé otázky a úkoly, které vyvstávají v praktické činnosti vedoucího výrobního místa. To znamená, že vyřešíme skutečné výrobní problémy!

Udržování databáze – shromažďování informací – samo o sobě nic nedává, naopak nám to nějakou dobu trvá! Ale výsledky analýzy těchto informací, které vám umožní dát přesné odpovědi na různé otázky, mohou výrazně zlepšit ovladatelnost databázových objektů v reálném životě!

Ve výrobě při správné konstrukci a pečlivé údržbě databází umožňuje využití výsledků analýz zdvojnásobit a někdy až ztrojnásobit objem výroby při použití stejných ploch, zařízení a při stejném složení technického a technického vybavení.

Nejjednodušší řazení.

Nejjednodušší řazení v Excelu se provádí pomocí tlačítek „Seřadit vzestupně“ a „Seřadit sestupně“ umístěných na panelu nástrojů „Standardní“. (Na obrázku níže jsou tato tlačítka zakroužkována červenou elipsou.)

Úkol 1:

Určete: který z vyrobených výrobků je nejtěžší a jakou má hmotnost? Kdy byl tento produkt vyroben?

1. Otevřete soubor v MS Excel.

2. Aktivujeme kliknutím na buňku E7 s nadpisem sloupce „Hmotnost 1 kusu, t“ (můžete aktivovat kteroukoli buňku ve sloupci, která nás zajímá).

3. Klikněte na tlačítko „Seřadit sestupně“ na panelu nástrojů „Standardní“.

4. Odpověď na otázku položenou čteme v úplně horním řádku databáze (řádek č. 8). Nejtěžším výrobkem v databázi je Nosník 045 ze zakázky č. 2. Nosník 045 byl vyroben od 23. 4. do 25. 4. 2014 (viz záznamy v Excelu řádky č. 8-10).

5. Databázi můžete vrátit do formuláře před tříděním v Excelu (v případě potřeby) kliknutím na tlačítko „Storno“ na stejném panelu nástrojů „Standard“. Nebo můžete použít Seřadit vzestupně na sloupec Datum v databázi.

Řazení v Excelu podle více sloupců.

Řazení tímto způsobem lze provádět postupně ve dvou nebo třech sloupcích.

Úkol č. 2:

Prezentujte záznamy databáze seskupené podle objednávek a s názvy produktů v rámci objednávek uspořádaných abecedně.

1. Libovolnou buňku databáze aktivujeme kliknutím myši (například buňku C11).

2. Klikněte na tlačítko hlavní nabídky „Data“ a vyberte „Řazení...“.

3. V okně „Rozsah řazení“, které se objeví, vyberte hodnoty z rozevíracích seznamů, jak je znázorněno na snímku obrazovky vlevo, a klikněte na „OK“.

4. Úkol č. 2 splněn. Záznamy jsou za prvé seřazeny podle objednacích čísel a za druhé v rámci každé objednávky jsou seřazeny abecedně podle názvů produktů.

Výsledek.

V tomto druhém článku seriálu o ukládání informací a správě dat Podívali jsme se na základní možnosti řazení databázových záznamů v Excelu. Tento velmi jednoduchý mechanismus Excelu jistě většina z vás, milí čtenáři, zná. Doufám, že jsem vám nezabral příliš mnoho času banálním příběhem o známé službě. Ale... cesta ke komplexu začíná tím jednoduchým!

Abych byl upřímný, v praxi se málokdy musíte uchýlit k třídění. Proč? Odpověď na tuto otázku budou v následujících článcích seriálu, který bude hovořit o dalších, včetně pokročilejších a výkonnějších mechanismů Excelu pro rychlé a efektivní zpracování dat.

prosím uctivý autorské dílo předplatit pro oznámení článků v okně umístěném na konci každého článku nebo v okně v horní části stránky!

Vážení čtenáři, pište své recenze a komentáře do komentářů.

Řazení dat umístěných v oblasti řádků a sloupců kontingenční tabulky se standardně provádí ve vzestupném pořadí (obr. 1a) nebo pomocí vlastních třídicích seznamů. Ne vždy to uživateli vyhovuje. Například, když chcete zobrazit zákazníky s nejvyššími příjmy v horní části seznamu (obr. 1b). Pokud je vaše kontingenční tabulka seřazeny ve vzestupném (sestupném) pořadí, měli byste vytvořit pravidlo, které řídí řazení podle pole. Navíc toto pravidlo (pro toto pole) bude aplikováno i po přidání nových polí do kontingenční tabulky (obr. 1c).

Rýže. 1. Řazení podle oboru Zákazník: (a) standardně – od A do Z; b) v pořadí klesajících příjmů; c) řazení podle polí Zákazník se při přidávání pole nezměnilo Sektor

Stáhněte si poznámku ve formátu nebo formátu, příklady ve formátu

Řazení zákazníků v sestupném pořadí podle příjmů

Chcete-li seřadit řádky kontingenční tabulky v sestupném pořadí podle příjmů, vyberte libovolnou buňku ve sloupci Částka podle pole Příjem, například E4 (ale ne název), a klikněte na ikonu JA umístěný na kartě Data(obr. 2). Toto řazení se podobá standardnímu, ale jedná se pouze o povrchní podobnost. Při řazení kontingenční tabulky Excel vytvoří pravidlo, které bude fungovat i po provedení dalších změn v kontingenční tabulce.

Na příkladu kontingenční tabulky umístěné ve sloupcích G:I (obr. 1c) můžete vidět, co se stane po přidání nového externího řádkového pole Sektor. Kontingenční tabulka pokračuje v řazení dat v sestupném pořadí podle příjmů v rámci každého sektoru. Například v sektoru Výroba General Motors je na prvním místě s příjmem 750 163 USD. Ford těsně následuje s příjmy 622 794 $. I když pole smažete Zákazník z kontingenční tabulky proveďte další nastavení a vraťte toto pole zpět, ale do oblasti sloupců si Excel zapamatuje řazení zákazníků v sestupném pořadí podle příjmů.

Aby v souhrnné tabulce umístěné ve sloupcích G:I (obr. 1c) byly sektory také seřazeny v sestupném pořadí podle příjmů, můžete jít jedním ze tří způsobů:

• Vyberte buňku G4, klikněte pravým tlačítkem a vyberte Sbalit všepole skrýt všechny prvky, které se týkají zákazníka. Jakmile se na obrazovce zobrazí pouze jeden sektor, vyberte buňku I4 a klikněte na ikonu YA na kartě Data provést řazení v sestupném pořadí. Tím vytvoříte třídicí pravidlo pro pole Sektor. Znovu vyberte buňku G4, klikněte pravým tlačítkem a vyberte Rozšířit všepole.
• Pole dočasně odeberte Zákazník z kontingenční tabulky seřaďte tabulku v sestupném pořadí podle příjmu (pomocí metody popsané na obr. 2) a poté pole znovu vraťte Zákazník.
• Využijte schopnosti týmu Dalšímožnostitřídění(Používám přesně tuto metodu). Vyvolání příkazu: (a) Vyberte buňku G4, klikněte pravým tlačítkem a vyberte Řazení → Dalšímožnostitřídění(obr. 3) nebo (b) klikněte na ikonu trojúhelníku v poli Sektor a poté vyberte Dalšímožnostitřídění(obr. 4). V obou případech se otevře okno Řazení(obr. 5). Nastavte přepínač do polohy klesající a vyberte řádek Částka podle pole Příjem.

Rýže. 3. Vyvolejte příkaz Dalšímožnostitřídění klikněte pravým tlačítkem myši

Rýže. 4. Vyvolejte příkaz Dalšímožnostitřídění pomocí nabídky Třídění a filtry pole Sektor

Rýže. 5. Nastavení parametrů v okně Sektor

V levém dolním rohu dialogového okna Řazení je zde tlačítko Upřesnit... Po kliknutí na toto tlačítko se na obrazovce objeví dialogové okno . V tomto okně můžete: (a) definovat vlastní seznam, který bude použit pro třídění podle prvního klíče (viz podrobnosti níže); b) místo sloupce celkový součet Vyberte jiný sloupec jako sloupec základního řazení.

Například pro kontingenční tabulku zobrazenou na Obr. 6 lze nastavit řazení nikoliv podle celkových příjmů, ale podle příjmů z prodeje jednoho druhu zboží, např. Zařízení(všimněte si, že zákazníci nejsou řazeni podle sloupce F, ale podle sloupce C).

Rýže. 6. Doplňkové parametry umožňují seřadit zákazníky nikoli podle celkových příjmů, ale podle příjmů z prodeje produktů přístroj

Chcete-li seřadit takto:

1. Rozbalte seznam zákazníků umístěný v buňce A4.
2. Vyberte možnost Další možnosti řazení.
3. V dialogovém okně Řazení (zákazník) klikněte na tlačítko dodatečně…
4. V dialogovém okně Další možnosti řazení (zákazník) Vyberte sekci Pořadí řazení a nastavte spínač Hodnoty ve zvýrazněném sloupci.
5. Klikněte do pole odkazu a vyberte buňku C5. Všimněte si, že musíte kliknout do jedné z buněk hodnot přístroj, protože na hlavičce přístroj Na buňku C4 nelze kliknout.
6. Nastavení parametrů dokončíte dvojitým kliknutím na OK.

Nelekejte se, popis tohoto algoritmu krok za krokem je uveden spíše pro vzdělávací účely. Počínaje Excelem 2013 je řazení dat kontingenční tabulky mnohem jednodušší. Nyní jsou tlačítka YA a AYA na kartě Data používat inteligentní třídicí algoritmy. Při pokusu o řazení pomocí těchto tlačítek se program pokusí předpovědět záměry uživatele na základě toho, která buňka byla zvýrazněna před stisknutím tlačítka řazení (obrázek 7):

• A1, C1, D1, E1, F1, F2, A30, F30 – není k dispozici
• A2:A29 – seřadí jména zákazníků ve sloupci A podle abecedy
• В1, В2, С2, D2, E2 – seřadí názvy zboží podle abecedy na řádku 2
• B30, C30, D30, E30 – seřadí sestupně (vzestupně) výši příjmu v řádku 30
• vzestupný (sestupný) prodej B3:B29 – moduly, C3:C29 – přístroje, D3:D29 – díly, E3:E29 – léky, F3:F29 – celk.

Ruční řazení

Všimněte si prosím, že v dialogovém okně Řazení(viz obr. 5) můžete ručně definovat pravidla pro třídění dat. Ruční řazení kontingenční tabulky se ale provádí také jiným, poněkud neobvyklým způsobem. V sestavě kontingenční tabulky na Obr. 8a ukazuje pořadí kategorií produktů seřazených v abecedním pořadí: Část, modul, příprava A přístroj. Vezměte prosím na vědomí, že objem prodaných produktů podle kategorie Detail, ne největší. A sotva se vyplatí zobrazovat tuto kategorii jako první. Umístěte ukazatel myši do buňky E4 a zadejte slovo Detail. Stačí stisknout klávesu Vstupte Jak Excel určí, že jste se rozhodli přesunout sloupec Detail do posledního sloupce tabulky. Všechny číselné hodnoty související s touto kategorií produktů se přesunou ze sloupce B do sloupce E. Hodnoty související s ostatními kategoriemi produktů se přesunou doleva. Toto chování se zdá nelogické a je typické pouze pro kontingenční tabulky Excel. Běžný soubor dat aplikace Excel nelze tímto způsobem přeskupit. Na Obr. Obrázek 8b ukazuje kontingenční tabulku po přesunutí nového záhlaví sloupce do buňky E4.

Rýže. 8. Ruční řazení: (a) kategorie produktů jsou seřazeny podle abecedy, (b) kategorie dílů je umístěna jako poslední

Nadšenci myší mohou jednoduše přetáhnout požadovaná záhlaví sloupců (nebo jednotlivé řádky). Klikněte do oblasti záhlaví sloupce a podržte ukazatel myši nad okrajem vybrané oblasti buněk, dokud se nezmění na čtyřhlavou šipku. Začněte přetahovat buňku na vybrané místo; Zobrazí se ukazatel ve formě tlusté čáry a patek. Jakmile uvolníte tlačítko myši, číselné hodnoty se okamžitě přesunou do nového sloupce. Upozorňujeme, že při použití ručního třídění jsou produkty přidané do zdroje dat přidány na konec seznamu. To je způsobeno tím, že Excel neví, kam přesně přidat novou oblast.

Třídění dat podle vlastních seznamů

Dalším řešením problému přizpůsobení pořadí, ve kterém jsou pole prezentována, je vytvoření vlastních seznamů. Pomocí takového seznamu se budou třídit kontingenční tabulky vytvořené v budoucnu. Excel standardně obsahuje čtyři vlastní seznamy: pro dny v týdnu, měsíce v roce a zkrácené názvy dnů v týdnu a měsíců v roce. Program seřadí názvy dnů v týdnu v přirozeném sledu, počínaje Po a konče Ne (a ne abecedně).

Chcete-li vytvořit svůj vlastní seznam řazení, postupujte takto:

1. V oblasti listu bez dat zadejte názvy kategorií produktů v pořadí, které odpovídá vlastnímu seznamu, který vytváříte. Do každé buňky zadejte jeden název a jména umístěte do jednoho sloupce (obr. 9).
2. Vyberte výsledný seznam názvů kategorií produktů (buňky A10:A13).
3. Vyberte kartu pásu karet Soubor a ve spodní části navigační lišty zobrazené v okně vlevo klikněte na tlačítko Možnosti otevřete dialogové okno Možnosti aplikace Excel.
4. Vyberte kategorii dodatečně, přejděte do sekce Jsou běžné a klikněte na tlačítko Upravit seznamy.
5. V dialogovém okně Seznamy v poli se zobrazí adresa rozsahu obsahujícího předvolený seznam titulů Import seznamu z buněk(obr. 10). Klepněte na tlačítko Import pro vygenerování nového seznamu kategorií produktů na základě zadaných údajů. Do spodní části oblasti se přidá nový seznam Seznamy.
6. Klepnutím na tlačítko OK zavřete dialogové okno Seznamy. Opětovným kliknutím na OK dialogové okno zavřete Možnosti aplikace Excel.

Rýže. 10. Okno Seznamy

Nově vytvořený seznam se uloží do nastavení programu a bude dostupný v budoucích relacích Excelu. Nyní se všechny kontingenční tabulky vytvořené v budoucnu automaticky seřadí podle produktového pole v souladu s pořadím uvedeným v seznamu. Na Obr. Obrázek 11 ukazuje novou kontingenční tabulku (která byla vytvořena na základě po přidání vlastního seznamu produktů) seřazenou podle vytvořeného seznamu.

Chcete-li seřadit dříve vytvořené kontingenční tabulky podle nového vlastního seznamu, postupujte takto:

1. Rozbalte seznam polí Produkt a vyberte možnost Další možnosti řazení.
2. V dialogovém okně Řazení (produkt) tlačítko výběru vzestupně (od A do Z) po polích a z rozevíracího seznamu vyberte Produkt.
3. Klepněte na tlačítko dodatečně…
4. V dialogovém okně Další možnosti řazení (produkt) zrušte zaškrtnutí políčka Automatické řazení.
5. Rozbalte seznam Seřadit podle prvního klíče a vyberte seznam obsahující názvy kategorií produktů (obr. 12).
6. Poklepejte na OK.

Poznámka byla napsána na základě knihy Billa Jelena, Michael Alexander. . Kapitola 4.

Řazení dat v Excelu je uspořádání tabulkových dat v požadovaném pořadí, například vzestupně (od nejmenšího po největší) nebo sestupně (od největšího po nejmenší). Jsou seřazeny číselné a textové hodnoty, hodnoty data a času a formáty. Třídění dat je možné jak po sloupcích, tak po řádcích. Skryté řádky a sloupce musí být zobrazeny před řazením.

Typy dat k třídění a pořadí řazení

Seřadit číselné hodnoty v Excelu

Vzestupné řazení číselných hodnot je uspořádání hodnot, ve kterém jsou hodnoty uspořádány od nejmenší po největší (od minima po maximum).

V souladu s tím je řazení číselných hodnot v sestupném pořadí uspořádáním hodnot od největší k nejmenší (od maxima k minimu).

Řazení textových hodnot v Excelu

"Řazení od A do Z" - řazení dat vzestupně;

"Sort from Z to A" - řazení dat v sestupném pořadí.

Pro abecední řazení textových hodnot se tyto hodnoty vzájemně porovnávají. Jak víte, při ukládání textu počítač používá schéma, ve kterém má každý znak své vlastní jedinečné číslo, které se nazývá kód znaku. Právě tyto kódy se porovnávají, aby se určilo, která textová hodnota je větší a která menší.

Textové hodnoty mohou obsahovat abecední, číselné a speciální znaky. V tomto případě lze čísla ukládat v číselném i textovém formátu. Čísla uložená v číselném formátu jsou menší než čísla uložená v textovém formátu. Pro správné řazení textových hodnot musí být všechna data uložena v textovém formátu. Navíc při vložení textových dat z jiných aplikací do buněk mohou data obsahovat úvodní mezery. Než začnete třídit, musíte z tříděných dat odstranit úvodní mezery (nebo jiné netisknutelné znaky), jinak nebude řazení provedeno správně.

Textová data můžete třídit tak, aby se rozlišovala malá a velká písmena. Chcete-li to provést, musíte v možnostech řazení zaškrtnout políčko „Zvažovat případ“.

Velká písmena mají obvykle nižší čísla než malá písmena.

Seřadit hodnoty data a času

"Řadit od nejstarších po nejnovější" je seřadit hodnoty data a času od nejstarší hodnoty po nejnovější.

„Řadit od nového k starému“ slouží k řazení hodnot data a času od nejnovější hodnoty po nejstarší.

Formáty řazení

Microsoft Excel 2007 a novější poskytuje řazení podle formátování. Tato metoda řazení se používá, když je oblast buněk formátována pomocí barvy výplně buňky, barvy písma nebo sady ikon. Barvy výplně a písma v Excelu mají své vlastní kódy a právě tyto kódy se používají při řazení formátů.

Seřadit podle vlastního seznamu

Tabulková data lze třídit podle vlastních seznamů, jako je seznam měsíců, seznam dnů v týdnu, seznam strukturních divizí podniku, seznam personálních čísel zaměstnanců a tak dále. Excel má schopnost vytvářet vlastní seznamy třídění dat. S tímto pořadím řazení se musí třídit data a hodnoty seznamu.

Možnosti řazení

Seřadit podle sloupce

V jakékoli verzi Excelu je ve výchozím nastavení nastaveno řazení podle sloupce, to znamená, že hodnoty buněk vybraného sloupce jsou uspořádány v požadovaném pořadí a řádky rozsahu jsou zaměněny v závislosti na poloze buňky. v seřazeném sloupci.Chcete-li seřadit tabulku podle sloupce, stačí nastavit značku výběru do libovolné buňky tabulky a kliknout na jednu z ikon symbolizujících řazení od minima k maximu nebo od maxima k minimu. Tabulka bude seřazena podle sloupce, který obsahuje značku výběru.

Seřadit podle řetězce

V nastavení programu lze změnit řazení podle sloupce na řazení podle řádku. Pokud je nastaveno řazení podle řádku, pak se hodnoty buněk vybraného řádku uspořádají v určeném pořadí a sloupce se zamění v závislosti na pozicích buněk v seřazeném řádku.

Víceúrovňové řazení

Pokud tedy třídíte podle sloupce, řádky jsou prohozeny, pokud jsou data řazena podle řádku, jsou prohozeny sloupce.

Excel umožňuje řadit data nejen podle jednoho sloupce nebo jednoho řádku, ale vytvořením různého počtu úrovní řazení. Například v Excelu 2007 je jich 64. Úrovně řazení lze přidávat, mazat, kopírovat a zaměňovat.

Mimo jiné můžete při třídění buď zohledňovat malá a velká písmena, nebo je ignorovat.

Doplněk pro řazení dat v Excelu

Microsoft Excel poskytuje uživatelům poměrně velkou sadu standardních nástrojů pro řazení hodnot různých typů, ale existují úkoly, které je buď nepohodlné nebo časově náročné na řešení pomocí standardních nástrojů, například řazení každého řádku/každého sloupce v způsob, kdy se řazení provádí pouze v rámci řádku/sloupce a neovlivňuje sousední buňky.