Ak potrebujete zoznam triediť, máte k dispozícii mnoho spôsobov, z ktorých najjednoduchšie sú tlačidlá triedenia na karte alebo v ponuke dátum (Údaje – Zoradiť). Sú však situácie, kedy je potrebné triedenie zoznamu vykonať automaticky, teda vzorce. Môže to byť potrebné napríklad pri generovaní údajov pre rozbaľovací zoznam, pri výpočte údajov pre mapy atď. Ako zoradiť zoznam pomocou vzorca za behu?
Metóda 1. Číselné údaje
Ak zoznam obsahuje iba číselné informácie, triedenie sa dá jednoducho vykonať pomocou funkcií ASPOŇ (MALÁ) и LINE (RIADOK):
Funkcie ASPOŇ (MALÁ) vytiahne z poľa (stĺpec A) n-tý najmenší prvok v rade. Tie. SMALL(A:A;1) je najmenšie číslo v stĺpci, SMALL(A:A;2) je druhé najmenšie atď.
Funkcie LINE (RIADOK) vráti číslo riadku pre zadanú bunku, tj RIADOK(A1)=1, RIADOK(A2)=2 atď. V tomto prípade sa používa jednoducho ako generátor postupnosti čísel n=1,2,3… pre náš triedený zoznam. S rovnakým úspechom bolo možné vytvoriť ďalší stĺpec, ručne ho vyplniť číselnou postupnosťou 1,2,3 … a odkazovať naň namiesto funkcie ROW.
Metóda 2. Textový zoznam a bežné vzorce
Ak zoznam neobsahuje čísla, ale text, funkcia SMALL už nebude fungovať, takže musíte ísť inou, trochu dlhšou cestou.
Najprv pridajme stĺpec služby so vzorcom, kde sa poradové číslo každého mena v budúcom zoradenom zozname vypočíta pomocou funkcie COUNTIF (COUNTIF):
V anglickej verzii to bude:
=COUNTIF(A:A,»<"&A1)+COUNTIF($A$1:A1,"="&A1)
Prvý člen je funkcia na počítanie počtu buniek, ktoré sú menšie ako aktuálny. Druhým je záchranná sieť pre prípad, že sa nejaké meno vyskytne viackrát. Potom nebudú mať rovnaké, ale postupne sa zvyšujúce čísla.
Teraz musia byť prijaté čísla usporiadané postupne vo vzostupnom poradí. Na to môžete použiť funkciu ASPOŇ (MALÁ) z prvého spôsobu:
No a napokon ostáva už len vytiahnuť mená zo zoznamu podľa čísel. Ak to chcete urobiť, môžete použiť nasledujúci vzorec:
Funkcie VIAC EXPOZOVANÉ (ZÁPAS) vyhľadá v stĺpci B požadované sériové číslo (1, 2, 3 atď.) a v skutočnosti vráti číslo riadku, kde sa toto číslo nachádza. Funkcia INDEX (INDEX) vytiahne zo stĺpca A názov na tomto čísle riadku.
Metóda 3: Vzorec poľa
Táto metóda je v skutočnosti rovnaký algoritmus umiestnenia ako v metóde 2, ale implementovaný pomocou vzorca poľa. Na zjednodušenie vzorca bol rozsah buniek C1:C10 pomenovaný zoznam (vyberte bunky, stlačte Ctrl + F3 a tlačidlo vytvoriť):
Do bunky E1 skopírujte náš vzorec:
=INDEX(Zoznam; MATCH(SMALL(COUNTIF(Zoznam; "<"&Zoznam); RIADOK(1:1)); COUNTIF(Zoznam; "<"&Zoznam); 0))
Alebo v anglickej verzii:
=INDEX(Zoznam, ZHODA(SMALL(COUNTIF(Zoznam, «<"&Zoznam), RIADOK(1:1)), COUNTIF(Zoznam, "<"&Zoznam), 0))
a tlačiť Ctrl + Shift + Enterzadajte ho ako vzorec poľa. Potom je možné výsledný vzorec skopírovať po celej dĺžke zoznamu.
Ak chcete, aby vzorec nezohľadňoval pevný rozsah, ale aby sa dal upraviť pri pridávaní nových prvkov do zoznamu, budete musieť mierne zmeniť stratégiu.
Po prvé, rozsah zoznamu bude potrebné nastaviť dynamicky. Aby ste to dosiahli, musíte pri vytváraní zadať nie pevný rozsah C3:C10, ale špeciálny vzorec, ktorý bude odkazovať na všetky dostupné hodnoty bez ohľadu na ich počet. Kliknite Alt + F3 alebo otvorte kartu Vzorce – Správca mien (Vzorce — Name Manager), vytvorte nový názov a v poli odkaz (Referencia) zadajte nasledujúci vzorec (predpokladám, že rozsah údajov na triedenie začína bunkou C1):
=СМЕЩ(C1;0;0;СЧЁТЗ(C1:C1000);1)
=OFFSET(C1,0,0;1,SCHÖTZ(C1000:C1);XNUMX)
Po druhé, vyššie uvedený vzorec poľa bude potrebné rozšíriť s okrajom – s očakávaním dodatočných údajov zadaných v budúcnosti. V tomto prípade vzorec poľa začne vykazovať chybu #NUMBER v bunkách, ktoré ešte nie sú vyplnené. Ak ho chcete zachytiť, môžete použiť funkciu IFERROR, ktorý je potrebné pridať „okolo“ nášho vzorca poľa:
=IFERROR(INDEX(Zoznam; MATCH(SMALL(COUNTIF(Zoznam; "<"&Zoznam); RIADOK(1:1)); COUNTIF(Zoznam; "<"&Zoznam); 0));»»)
=IFERROR(NDEX(Zoznam, MATCH(SMALL(COUNTIF(Zoznam, «<"&Zoznam), RIADOK(1:1)), COUNTIF(Zoznam, "<"&Zoznam), 0));"")
Zachytí chybu #NUMBER a namiesto toho vypíše void (prázdne úvodzovky).
:
- Zoradiť rozsah podľa farby
- Čo sú vzorce poľa a prečo sú potrebné
- SORT triedenie a dynamické polia v novom Office 365