Triedenie údajov v Exceli je veľmi užitočný nástroj, ktorý zlepšuje vnímanie prezentovaných informácií. Skôr než prejdeme k analýze rozhrania, analyzujme poradie zoradenia aplikácie:

• Čísla idú najskôr od najmenšieho po najväčšie;
• Potom prídu špeciálne znaky ($, %, * atď.);
• Za nimi nasledujú interpunkčné znamienka;
• Potom začnú abecedy, najskôr angličtina, potom ruština. Nerozlišuje ani veľkosť písmen, t.j. veľké a malé písmená sú si navzájom rovné (A=a).

Ak chcete vykonať objednávku, prejdite na kartu „Údaje“ a vyberte časť „Zoradiť a filtrovať“. K dispozícii sú 3 ikony na triedenie:

Zoradiť vzostupne (A až Z), zostupne (Z po A) a ikona zoradiť na nastavenie podmienok pre viacero stĺpcov naraz.

Zvážme všetky body podrobnejšie.

Ak chcete zoradiť rozsah buniek v Exceli, najprv ho vyberte a potom kliknite na požadovanú ikonu. Upozorňujeme, že ako hlavička tabuľky sa použije najvrchnejšia bunka vo vybranom rozsahu, t.j. nebudú triedené a zostanú na rovnakom mieste. Ak sú vedľa zvoleného rozsahu ďalšie vyplnené bunky, program zobrazí nasledujúce okno:

Ak vyberiete prvú možnosť, riadky tabuľky si zachovajú svoju integritu. Ak je to druhé, údaje mimo rozsahu triedenia zostanú nedotknuté. Obrázky znázorňujú výsledky pred a po triedení pre dva varianty.

Počiatočný stav:

Nastavenie poradia prvého stĺpca tabuľky, keď je vybratá prvá možnosť:

Ako vidíte, riadky si zachovali svoju celistvosť a mená sa stále zhodujú s ich priezviskami.

Rovnaké akcie, ale pri výbere druhej možnosti:

Triedenie sa vykonáva aj podľa prvého stĺpca, okrem toho, že druhý stĺpec sa vzhľadom na zoradené bunky neposunul.

Okamžite vyberte požadovaný rozsah na zoradenie, aby vás Excel nevyzval na automatické rozšírenie. Stĺpec, v ktorom začnete vyberať rozsah, bude stĺpcom pre podmienky zoradenia.

Ak je potrebné nastaviť poradie vo viacerých stĺpcoch, pomôže už spomínaná ikona „Zoradiť“. Vyberte rozsah, ktorý chcete zoradiť. V tomto prípade je jedno, z ktorého stĺpca sa začne zvýrazňovať, pretože podmienky a stĺpce budú nastavené manuálne. Použijeme napríklad nasledujúcu tabuľku:

Tieto údaje konkrétne použijeme na vizualizáciu spôsobu triedenia v Exceli.

Vyberte si rozsah našej tabuľky a kliknite na ikonu "Triedenie". Zobrazí sa nasledujúce okno:

Spočiatku už pribudla prvá úroveň triedenia. Z rozbaľovacieho zoznamu vyberte požadovaný stĺpec. Vrch ("klobúk") slúži ako jeho názov.

V ďalšom rozbaľovacom zozname si môžete vybrať, podľa ktorých parametrov sa má rozsah zoradiť. Ak ste napríklad použili výplň buniek alebo zmenili farbu písma, potom je možné nastaviť poradie podľa týchto parametrov.

Nasledujúci rozbaľovací zoznam nastavuje poradie: vzostupne, zostupne atď.

Ak chcete pridať druhý a ďalšie stĺpce na triedenie, použite tlačidlo „Pridať úroveň“ a nastavte mu parametre.

V našom príklade používame trojúrovňové triedenie. Pre prvú úroveň nastavme triedenie podľa "Stĺpec1", poradie "Od Z po A". Pre druhú úroveň nastavme triedenie podľa "Stĺpec2", poradie "Vzostupne". Pre tretiu úroveň podľa "Stĺpec 3" je poradie "Zostupne". Parametre na triedenie sú všade "hodnoty".

Zvážte výsledný výsledok:

Najprv triedenie prešlo cez prvý stĺpec (prvá úroveň), pričom sa údaje zvýraznili do určitých podrozsahov (v červenom rámčeku). Ďalej sa triedi druhá úroveň, ale údaje sa triedia iba v rámci podrozsahov definovaných prvou úrovňou. Po zoradení druhej úrovne sa opäť určia podrozsahy (v modrom rámčeku). Potom nastane triedenie na tretej úrovni podľa rovnakého algoritmu.

V Exceli je teda možné nastaviť triedenie podľa značného počtu stĺpcov.

V okne triedenia môžete tiež nastaviť rôzne možnosti:

• Zaškrtnutím políčka „Moje údaje obsahujú hlavičky“ program zohľadní, že horné bunky rozsahov nie je potrebné triediť;
• Kliknutím na tlačidlo "Parametre" sa zobrazí okno, v ktorom môžete nastaviť:

1. Schopnosť rozlišovať malé a veľké písmená (veľké písmená majú vyššie sériové číslo v porovnaní s veľkými písmenami);
2. Vyberte, či chcete triediť podľa riadkov alebo podľa stĺpcov.

Neoddeliteľnou súčasťou ich analýzy je triedenie údajov. Možno budete chcieť zoradiť názvy v zozname podľa abecedy, zoradiť inventár a zoradiť ho v zostupnom poradí alebo usporiadať riadky podľa farby alebo ikony. Triedenie údajov vám pomáha rýchlo vizualizovať údaje a lepšie im porozumieť, organizovať a vyhľadávať informácie, ktoré potrebujete, a v konečnom dôsledku robiť lepšie rozhodnutia.

Údaje môžete zoradiť podľa textu (A po Z alebo Z po A), čísel (od najnižšieho po najväčšie alebo od najväčšieho po najmenšie) a dátumu a času (od najstarších po najnovšie alebo od najnovšieho po najstaršie) v jednom alebo viacerých stĺpcoch. Môžete tiež triediť podľa vlastných zoznamov, ktoré si sami vytvoríte (napríklad zoznam veľkých, stredných a malých položiek) alebo podľa formátu vrátane farby bunky, farby písma a ikon.

Poznámky:

Triedenie textových hodnôt

Poznámky: Možné problémy

Triedenie čísel

Poznámky:

Triedenie hodnôt dátumu a času

Poznámky: Možné problémy

Zoradiť podľa viacerých stĺpcov alebo riadkov

Možno budete chcieť zoradiť podľa dvoch alebo viacerých stĺpcov alebo riadkov, aby ste zoskupili údaje s rovnakými hodnotami v jednom stĺpci alebo riadku a potom zoradiť tieto skupiny s rovnakými hodnotami v inom stĺpci alebo riadku. Ak máte napríklad stĺpce „Oddelenie“ a „Zamestnanec“, môžete najskôr zoradiť podľa stĺpca „Oddelenie“ (na zoskupenie všetkých zamestnancov podľa oddelenia) a potom zoradiť podľa mena (na zoradenie mien zamestnancov v každom oddelení v abecednom poradí). . Naraz môžete triediť až podľa 64 stĺpcov.

Poznámka: Na dosiahnutie najlepších výsledkov by rozsah, ktorý sa má zoradiť, mal obsahovať hlavičky stĺpcov.

Zoradiť podľa farby bunky, farby písma alebo ikony

Ak bol rozsah buniek alebo stĺpec tabuľky naformátovaný manuálne alebo podmieneným formátovaním pomocou farby bunky alebo farby písma, môžete triediť aj podľa farby. Môžete tiež triediť podľa sady ikon vytvorených pomocou podmieneného formátovania.

Triedenie podľa vlastných zoznamov

Vlastné zoznamy môžete použiť na triedenie v poradí zadanom používateľom. Stĺpec môže napríklad obsahovať hodnoty, podľa ktorých chcete zoradiť, ako napríklad „Vysoká“, „Stredná“ a „Nízka“. Ako môžem nastaviť, aby sa triedenie zobrazovalo najskôr „Vysoké“, potom „Stredné“ a nakoniec „Nízke“? Ak ich zoradíte podľa abecedy (A až Z), navrchu sa zobrazí „Vysoká“, ale za nimi bude „Nízka“, nie „Stredná“. A pri zoradení od Z po A budú hodnoty „Priemerné“ úplne hore. V skutočnosti by sa hodnoty „Stred“ mali vždy zobrazovať v strede bez ohľadu na poradie zoradenia. Tento problém môžete vyriešiť vytvorením vlastného zoznamu.

Rozlišovať veľkosť písmen

Zoradiť zľava doprava

Normálne sa triedenie vykonáva zhora nadol, ale hodnoty je možné triediť zľava doprava.

Poznámka: Tabuľky nepodporujú možnosť triedenia zľava doprava. Najprv preveďte tabuľku na rozsah: vyberte v nej ľubovoľnú bunku a vyberte položky Práca s tabuľkami > Previesť na rozsah.

Poznámka: Pri zoraďovaní riadkov, ktoré sú súčasťou štruktúry pracovného hárka, Excel zoradí skupiny najvyššej úrovne (úroveň 1), aby sa nezmenilo poradie vŕtacích riadkov alebo stĺpcov, aj keď sú skryté.

Zoradiť podľa časti hodnoty v stĺpci

Ak chcete zoradiť časť hodnoty v stĺpci, napríklad časť kódu (789- WDG-34), priezvisko (Regina Pokrovskaya) alebo meno (Pokrovskaya Regina), najprv musíte stĺpec rozdeliť na dve alebo viac častí, aby sa hodnota, podľa ktorej chcete zoradiť, nachádzala v samostatnom stĺpci. Na rozdelenie hodnôt v bunke na časti môžete použiť textové funkcie alebo sprievodcu textom. Ďalšie informácie a príklady nájdete v téme Rozdelenie textu do rôznych buniek a Rozdelenie textu do rôznych stĺpcov pomocou funkcií.

Zoraďte menší rozsah v rámci väčšieho

Pozor: Hodnoty môžete zoradiť v rozsahu, ktorý je súčasťou iného rozsahu, ale to sa neodporúča, pretože to preruší prepojenie medzi zoradeným rozsahom a pôvodnými údajmi. Ak údaje zoradíte tak, ako je uvedené nižšie, vybratí zamestnanci budú priradení k iným oddeleniam.

Našťastie Excel vydá varovanie, ak zistí takýto pokus:

Ak ste nemali v úmysle zoradiť údaje týmto spôsobom, vyberte možnosť automaticky rozšíri vybraný rozsah, inak - triediť v rámci zadaného výberu.

Ak výsledok nie je taký, aký chcete, stlačte tlačidlo Zrušiť .

Poznámka: Týmto spôsobom nie je možné triediť hodnoty v tabuľke.

Získajte viac informácií o bežných problémoch s triedením

Ak výsledky triedenia údajov nie sú podľa očakávania, postupujte takto.

Skontrolujte, či sa hodnoty vrátené vzorcami zmenili Ak triedené údaje obsahujú jeden alebo viacero vzorcov, hodnoty vrátené týmito vzorcami sa môžu pri prepočítaní pracovného hárka zmeniť. V tomto prípade znova použite triedenie, aby ste získali aktuálne výsledky.

Pred zoradením zobraziť skryté riadky a stĺpce Zoradenie podľa stĺpcov nepresúva skryté riadky a zoradenie podľa riadkov nepresúva skryté stĺpce. Pred triedením údajov je vhodné zobraziť skryté riadky a stĺpce.

Skontrolujte aktuálne nastavenie miestneho nastavenia Poradie zoradenia závisí od zvoleného jazyka. Uistite sa, že na ovládacie panely V kapitole Regionálne možnosti alebo jazykové a regionálne normy je nastavené správne miestne nastavenie. Informácie o tom, ako zmeniť nastavenie miestneho nastavenia, nájdete v Pomocníkovi systému Microsoft Windows.

Záhlavia stĺpcov zadajte iba do jedného riadku Ak chcete použiť hlavičky na viacerých riadkoch, nastavte bunku na zalamovanie slov.

Povoliť alebo zakázať záhlavie Pri triedení podľa stĺpcov sa vo všeobecnosti odporúča zobraziť riadok hlavičky, pretože to uľahčuje čítanie údajov. V predvolenom nastavení nie je hodnota v hlavičke zahrnutá do triedenia. V niektorých prípadoch však možno budete chcieť povoliť alebo zakázať hlavičku, aby hodnota v hlavičke bola alebo nebola zahrnutá do zoradenia. Vykonajte jednu z nasledujúcich akcií.

Ak chcete vylúčiť prvý riadok údajov (hlavičku stĺpca) z triedenia, na karte Domov v skupine Úprava stlač tlačidlo Triediť a filtrovať vyberte príkaz Vlastné triedenie a začiarknite políčko.

Ak chcete zahrnúť prvý riadok údajov do zoradenia (pretože to nie je hlavička stĺpca), na karte Domov v skupine Úprava stlač tlačidlo Triediť a filtrovať vyberte príkaz Vlastné triedenie a zrušte začiarknutie Moje údaje obsahujú hlavičky.

Triedenie v Exceli je vstavaná funkcia analýzy údajov. Pomocou neho môžete zoradiť priezviská v abecednom poradí, zoradiť priemerné skóre uchádzačov vo vzostupnom alebo zostupnom poradí, nastaviť poradie riadkov v závislosti od farby alebo ikony atď. Pomocou tejto funkcie môžete tiež rýchlo dať tabuľke pohodlný vzhľad, ktorý používateľovi umožní rýchlo nájsť potrebné informácie, analyzovať ich a robiť rozhodnutia.

Video o používaní triedenia v Exceli

Čo sa dá triediť?

Excel dokáže zoradiť údaje podľa textu (v abecednom poradí alebo naopak), podľa čísel (vo vzostupnom alebo zostupnom poradí), podľa dátumu a času (od najnovšieho po najstaršie a naopak). Môžete triediť podľa jedného stĺpca alebo podľa viacerých súčasne. Môžete napríklad najskôr zoradiť všetkých zákazníkov podľa abecedy a potom ich zoradiť podľa celkovej sumy ich nákupov. Okrem toho môže Excel triediť podľa vlastných zoznamov alebo podľa formátu (farba bunky, farba textu atď.). Obvykle sa triedenie aplikuje len na stĺpce, ale túto funkciu je možné aplikovať aj na riadky.

Všetky špecifikované nastavenia pre túto možnosť sa uložia s excelovým zošitom, čo vám umožňuje pretriediť informácie pri otvorení zošita (v prípade potreby).

Zoraďte údaje v Exceli

Triedenie v Exceli možno podmienene rozdeliť na jednoduché a zložité. Zoradenie vo vzostupnom alebo zostupnom poradí sa považuje za jednoduché.

Existujú 2 hlavné typy triedenia - vzostupné a zostupné

Takže skôr ako začnete, musíte otvoriť Excel a vyplniť nejaké informácie. Môžete napríklad vyplniť 10 buniek číslami od 1 do 10. Teraz musíte vybrať celý stĺpec (v tomto prípade všetkých 10 buniek) a vybrať položky „Údaje - Zoradiť“ na paneli s ponukami. Otvorí sa nové okno, v ktorom musíte určiť spôsob triedenia informácií vo vzostupnom alebo zostupnom poradí. Môžete napríklad vybrať položku „zostupne“ a kliknúť na tlačidlo „OK“. Teraz čísla pôjdu od 10 do 1. Môžete znova otvoriť okno zoradenia a vybrať "vzostupne" - čísla sa budú pohybovať od 1 do 10. Tento postup je možné vykonať súčasne na 3 stĺpcoch. Aj keď je lepšie vykonať takéto triedenie.

Môžete napríklad vytvoriť tabuľku, ktorá bude uchovávať informácie o produkte v sklade. Tabuľka bude pozostávať z 3 stĺpcov: názov, farba, množstvo. Produkty musia byť napísané tak, aby ich bolo viacero rovnakej kategórie. Napríklad čierne pánske topánky (3 modely), červené pánske topánky (2 modely), biele dámske topánky (4 modely) atď. Množstvo môže byť ľubovoľné.

Ak chcete zapnúť automatický filter, musíte vybrať celý hárok a vybrať položky "Údaje - Filter - Automatický filter" na paneli s ponukami. V bunkách s názvami stĺpcov (názov, množstvo atď.) by sa mala objaviť malá ikonka, po kliknutí sa otvorí rozbaľovací zoznam. Prítomnosť takejto šípky znamená, že automatický filter je zapnutý správne. V tomto zozname môžete zoradiť údaje v zostupnom alebo vzostupnom poradí, určiť, že sa v tabuľke zobrazí iba prvých 10 položiek (v tomto príklade táto možnosť nebude fungovať) alebo že bude zobrazený konkrétny produkt (napríklad pánska obuv) . Môžete tiež vybrať položku „Stav“ a určiť napríklad, že program zobrazí všetky produkty, ktorých množstvo je menšie alebo rovné 10.

Ak je šípka automatického filtra zafarbená namodro, znamená to, že tento stĺpec už bol zoradený.

Triediace triky

Povedzme, že používateľ má tabuľku, ktorá má stĺpec s názvami mesiacov v roku. A keď to potrebujete zoradiť napríklad vzostupne, dopadne to asi takto: august, apríl, december atď. A bol by som rád, keby k triedeniu došlo v obvyklom poradí, t.j. január, február, marec atď. Dá sa to urobiť pomocou špeciálneho nastavenia na vlastnom hárku.

Ak to chcete urobiť, vyberte celú tabuľku, otvorte okno triedenia a v poli "Objednávka" vyberte položku "Vlastný zoznam". Otvorí sa nové okno, kde môžete vybrať požadovanú postupnosť mesiacov v roku. Ak takýto zoznam neexistuje (napríklad názov mesiacov v angličtine), môžete si ho vytvoriť sami výberom možnosti „Nový zoznam“.

Triedenie dát nie je vôbec zložité. V dôsledku toho však môžete získať pohodlnú tabuľku alebo prehľad na rýchle zobrazenie potrebných informácií a rozhodovanie.

Poučenie

Existuje niekoľko spôsobov, ako triediť pole. Najjednoduchšie na algoritmizáciu je „bublinové“ triedenie, ale je tiež jedným z najpomalších. Podstatou tejto metódy je postupný prechod cez dátové pole a porovnanie každej dvojice prvkov. Ak je podradený prvok menší ako predchádzajúci, pozície sa vymenia. Potom sa algoritmus spustí odznova. Príklad triediaceho kódu v jazyku C:

int hmotnosť;
intel_min=10;

pre (int i=0; ii; j--)
ak (menej(hmotnosť [j], hmotnosť ))
swap(hmotnosť [j], hmotnosť );
}

Jedným z optimálnych algoritmov na triedenie poľa vo vzostupnom poradí je poradie vkladania. Podstatou algoritmu je vytvoriť danú postupnosť medzi skupinou prvkov (vo vzostupnom poradí). Špeciálna obsluha v slučke kontroluje poradie poľa. Na zoradenie vo vzostupnom poradí je špecifikovaná nasledujúca podmienka. Ak je nasledujúci prvok menší ako predchádzajúci, odstráni sa zo svojho miesta a umiestni sa na to, ktoré zodpovedá jeho hodnote. Príklad kódu programu na triedenie vo vzostupnom poradí v jazyku C:

int Kol = 40;
intmass, k;

for (int i = 1, j = 0; i(
k = hmotnosť [i]; // pomocná premenná na uloženie prvku poľa
j = i - 1;
zatiaľ čo(k(
hmotnosť = hmotnosť [j];
j--;
if (jmass = k;
}
}

Ďalšou bežne používanou metódou triedenia je triediaci algoritmus vyhľadávaním minimálneho alebo maximálneho prvku v poli. Pri vzostupnom radení sa v čase prvého prechodu poľom nájde jeho prvok s najmenšou hodnotou a umiestni sa na začiatok poľa. Na jeho mieste je nainštalovaný prvok, ktorý predtým obsadil prvú pozíciu. Po ďalšom zvážení poľa je vyplnené miesto vylúčené. Vyhľadá sa ďalší minimálny prvok, umiestni sa na druhé miesto a tak ďalej, až kým sa nezoradí celé pole. Kód na triedenie nájdením minimálneho prvku zoznamu:

intmass, bb;
int Kol = 30, min, poz;

for (int i = 0; i(
min = hmotnosť[i];
poz = i;
for (int j=0; j (
if (hmotnosť [j] (
min = hmotnosť [j];
pos = j;
}
bb = hmotnosť[i];
hmotnosť[i] = hmotnosť;
hmotnosť = bb;
}
}

Spôsob, akým sú prvky poľa usporiadané, závisí od nástrojov, ktoré máte k dispozícii. Nasleduje niekoľko možností na objednávanie jednorozmerných polí pomocou najbežnejšieho programovacieho jazyka na strane servera, PHP. Pri používaní tohto jazyka nie je potrebné vytvárať funkcie na iteráciu prvkov poľa, ich porovnávanie a priraďovanie nových hodnôt - to všetko robia vstavané funkcie.

Poučenie

Ak potrebujete zoradiť údaje v poli vo vzostupnom poradí, použite funkciu sort(). Napríklad: $values ​​​​= array(58, 15, 2,41, 26, 30);
sort($values);V dôsledku aplikácie sa umiestnenie údajov v poli zmení - bude to takto: (2.41, 15, 26, 30, 58). Ak sa do volania funkcie pridá príznak SORT_STRING, funkcia bude považovať údaje poľa za reťazce a zoradí ich podľa . Keďže znak reťazcovej premennej "2.41" sa nachádza ďalej v abecede ako prvý znak reťazcovej premennej "15", po použití funkcie sort($values, SORT_STRING) sa premenné zoradia inak: (15, 2,41, 26, 30, 58).

Ak potrebujete zoradiť pole v zostupnom poradí jeho hodnôt, použite funkciu rsort(). Táto funkcia sa líši od funkcie opísanej v prvom kroku iba v poradí triedenia.

Funkciu asort() použite, keď chcete zoradiť vzostupne hodnoty pomenovaného (asociatívneho) poľa bez toho, aby ste zmenili pôvodné mapovania medzi indexom a hodnotou každého prvku poľa. Napríklad: $values ​​​​= array("jeden" => 58, "dva" => 15, "tri" => 2,41, "štyri" => 26, "päť" => 30);
asort($values); Výsledkom bude, že poradie prvkov poľa bude: ("tri" => 2,41, "dva" => 15, "štyri" => 26, "päť" => 30, "jeden " => 58). Inak sa táto funkcia nelíši od funkcie triedenia opísanej v prvom kroku. Ak chcete podobne zoradiť prvky v zostupnom poradí, použite funkciu arsort().

Zoraďte podľa vzorcov tabuľku pozostávajúcu z 2 stĺpcov. Budeme triediť podľa jedného zo stĺpcov tabuľky (riešime 2 úlohy: triedenie tabuľky podľa číselného a triedenie podľa textového stĺpca). Nastavme vzorce triedenia tak, aby sa po pridaní nových údajov do zdrojovej tabuľky dynamicky menila triedená tabuľka. 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 číselného znaku, 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 - text: Zoznam ovocia; a druhý je číselný Objem predaja(pozri vzorový súbor).

Úloha 1 (Zoradiť tabuľku podľa číselného stĺpca)

Riadky tabuľky je potrebné 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ť, veľkosť hodnôt v stĺpci Objem predaja zvýraznené pomocou (). Duplicitné hodnoty sú tiež zvýraznené žltou farbou.

Poznámka: Úloha triedenia samostatného stĺpca (zoznamu) je rieš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 plodov sa budú zobrazovať nesprávne ).

Preto bude treba mechanizmus triedenia implementovať inak.

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

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

Poďme analyzovať 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ý stôl, t.j. prvé číslo v rozsahu Predaj) viac ako 4 hodnoty z rovnakého rozsahu; číslo 74 (z cel B8 pôvodný stôl, t.j. druhé číslo v rozsahu Predaj) viac ako 5 hodnôt z rovnakého rozsahu; ďalšie číslo 23 je najmenšie (nie je to nikto 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 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-DLSTR(RAD(predaj)-RAD ($E$6)))&ROW(predaj)-RAW($E$6)) 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 MOD() 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() podľa čísla pozície vráti zodpovedajúce číslo.

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

Vo vzorovom súbore je vzhľadom na rýchlosť výpočtov (pozri nižšie) rovnaký typ časti vzorca, t.j. všetko vo funkcii ROUND() sa presunie do samostatného stĺpca J . Preto konečné vzorce v zoradenej tabuľke vyzerajú takto: =INDEX(ovocie,J7) a =INDEX(Predaj,J7)

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

Pre prehľadnosť, veľkosť hodnôt v stĺpci Objem predaja zvýraznené ( Domov/ Štýly/ Podmienené formátovanie/ Pruhové grafy). 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 v stĺpcoch D a E tabuľka sa automaticky zobrazí zoradená v zostupnom poradí;
4. V zoradenej tabuľke sa nový riadok zobrazí predposledný.

Rýchlosť výpočtu vzorca

Na výkonovo "priemernom" počítači prepočet na dvojicu takých vzorce poľa, nachádza sa v 100 riadkoch, prakticky nie je badateľný. Pri tabuľkách s 300 riadkami trvá prepočet 2-3 sekundy, čo spôsobuje nepríjemnosti. 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, alebo opustiť používanie 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 tabuľky

Zoraďte riadky zdrojovej tabuľky pomocou štandardného filtra (vyberte nadpisy 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 na triedenie, vybrať stĺpec, podľa ktorého chcete triediť, a možnosť triedenia.

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

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

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ý pre textové hodnoty (pozícia hodnoty vzhľadom na ostatné hodnoty v zozname). Textová hodnota, ktorá je abecedne nižšia, má vyššie „hodnotenie“. Napríklad hodnota jabĺk zodpovedá maximálnemu „hodnoteniu“ 7 (pri zohľadnení opakovaní).

Do stĺpca E zadajte obvyklý vzorec:

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

Tento vzorec zohľadňuje opakovanie 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 opakované hodnoty (v stĺpci A 74 sú tri hodnoty), zobrazíme zodpovedajúce hodnoty v abecednom poradí.

Na tento účel používame výsledky úloh 1.1 a 2.

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