Pokud potřebujete seznam seřadit, máte k dispozici mnoho způsobů, z nichž nejjednodušší jsou tlačítka řazení na kartě nebo v nabídce Data (Data – Seřadit). Jsou však situace, kdy je potřeba řazení seznamu provést automaticky, tedy vzorce. To může být vyžadováno například při generování dat pro rozevírací seznam, při výpočtu dat pro grafy atd. Jak seřadit seznam pomocí vzorce za běhu?
Metoda 1. Číselná data
Pokud seznam obsahuje pouze číselné informace, lze je snadno třídit pomocí funkcí NEJMÉNĚ (MALÝ) и LINE (ŘÁDEK):
funkce NEJMÉNĚ (MALÝ) vytáhne z pole (sloupec A) n-tý nejmenší prvek v řadě. Tito. SMALL(A:A;1) je nejmenší číslo ve sloupci, SMALL(A:A;2) je druhé nejmenší a tak dále.
funkce LINE (ŘÁDEK) vrací číslo řádku pro zadanou buňku, tj. ŘÁDEK(A1)=1, ŘÁDEK(A2)=2 atd. V tomto případě se používá jednoduše jako generátor posloupnosti čísel n=1,2,3… pro náš seřazený seznam. Se stejným úspěchem bylo možné vytvořit další sloupec, ručně jej vyplnit číselnou sekvencí 1,2,3 … a odkazovat na něj místo funkce ROW.
Metoda 2. Textový seznam a pravidelné vzorce
Pokud seznam neobsahuje čísla, ale text, pak funkce SMALL přestane fungovat, takže musíte jít jinou, trochu delší cestou.
Nejprve přidáme sloupec služby se vzorcem, kde se bude pomocí funkce počítat pořadové číslo každého jména v budoucím seřazeném seznamu COUNTIF (COUNTIF):
V anglické verzi to bude:
=COUNTIF(A:A,»<"&A1)+COUNTIF($A$1:A1,"="&A1)
První člen je funkce pro počítání počtu buněk, které jsou menší než aktuální. Druhým je záchranná síť pro případ, že by se nějaké jméno objevilo více než jednou. Pak nebudou mít stejná, ale postupně se zvyšující čísla.
Nyní musí být přijatá čísla uspořádána postupně ve vzestupném pořadí. K tomu můžete použít funkci NEJMÉNĚ (MALÝ) z prvního způsobu:
No a nakonec zbývá jen vytáhnout jména ze seznamu podle jejich čísel. Chcete-li to provést, můžete použít následující vzorec:
funkce VÍCE EXPOZOVANĚ (ZÁPAS) vyhledá ve sloupci B požadované sériové číslo (1, 2, 3 atd.) a ve skutečnosti vrátí číslo řádku, kde se toto číslo nachází. Funkce INDEX (INDEX) vytáhne ze sloupce A název na tomto čísle řádku.
Metoda 3: Vzorec pole
Tato metoda je ve skutečnosti stejný algoritmus umístění jako v metodě-2, ale implementován pomocí maticového vzorce. Pro zjednodušení vzorce dostal rozsah buněk C1:C10 název Seznam (vyberte buňky, stiskněte Ctrl + F3 a tlačítko Vytvořit):
Do buňky E1 zkopírujte náš vzorec:
=INDEX(Seznam; MATCH(SMALL(COUNTIF(Seznam; "<"&Seznam); ŘÁDEK(1:1)); COUNTIF(Seznam; "<"&Seznam); 0))
Nebo v anglické verzi:
=INDEX(Seznam, MATCH(SMALL(COUNTIF(Seznam, «<"&Seznam), ŘÁDEK(1:1)), COUNTIF(Seznam, "<"&Seznam), 0))
a tlačit Ctrl + Shift + Enterzadejte jej jako maticový vzorec. Potom lze výsledný vzorec zkopírovat po celé délce seznamu.
Pokud chcete, aby vzorec nebral v úvahu pevný rozsah, ale mohl se upravit při přidávání nových prvků do seznamu, budete muset mírně změnit strategii.
Nejprve bude třeba dynamicky nastavit rozsah seznamu. Chcete-li to provést, musíte při vytváření zadat nikoli pevný rozsah C3:C10, ale speciální vzorec, který bude odkazovat na všechny dostupné hodnoty bez ohledu na jejich počet. Klikněte Alt + F3 nebo otevřete kartu Vzorce – Správce názvů (Vzorce — Správce jmen), vytvořte nový název a v poli Odkaz (Odkaz) zadejte následující vzorec (předpokládám, že rozsah dat k třídění začíná buňkou C1):
=СМЕЩ(C1;0;0;СЧЁТЗ(C1:C1000);1)
=OFFSET(C1,0,0;1,SCHÖTZ(C1000:C1);XNUMX)
Za druhé, výše uvedený maticový vzorec bude muset být roztažen dolů s okrajem – s očekáváním dalších dat zadaných v budoucnu. V tomto případě maticový vzorec začne udělovat chybu #NUMBER u buněk, které ještě nejsou vyplněny. Chcete-li jej zachytit, můžete použít funkci IFERROR, který je třeba přidat „kolem“ našeho maticového vzorce:
=IFERROR(INDEX(Seznam; MATCH(SMALL(COUNTIF(Seznam; "<"&Seznam); ŘÁDEK(1:1)); COUNTIF(Seznam; "<"&Seznam); 0));»»)
=IFERROR(NDEX(Seznam, MATCH(SMALL(COUNTIF(Seznam, «<"&Seznam), ŘÁDEK(1:1)), COUNTIF(Seznam, "<"&Seznam), 0));"")
Zachytí chybu #NUMBER a místo toho vypíše void (prázdné uvozovky).
:
- Seřadit rozsah podle barvy
- Co jsou maticové vzorce a proč jsou potřebné
- SORT řazení a dynamická pole v nových Office 365