Jak krok za krokem používat funkci SEQUENCE v Excelu a Tabulkách Google

Informatec Digital » Zdroje » Jak krok za krokem používat funkci SEQUENCE v Excelu a Tabulkách Google

Pokud jste se někdy ocitli v situaci, kdy ručně píšete 1, 2, 3… do tabulky , víte, že je to jeden z nejnudnějších úkolů na světě. A když to není jen 10 řádků, ale 500 nebo 1000, stává se to z únavného až nesnesitelným. Naštěstí Excel a Tabulky Google mají funkci navrženou právě pro tento účel: SEQUENCE , která dokáže hromadně vytvářet seznamy čísel pomocí jediného vzorce.

I když to může znít trochu technicky, funkce SEQUENCE je mnohem jednodušší, než se zdá. S několika dobře definovanými argumenty můžete generovat kompletní tabulky seřazených čísel, rostoucích nebo klesajících řad , propojených dat, účetních kódů a dokonce i vzorových dat pro vaše sestavy. Pojďme se blíže podívat na to, jak funguje v Excelu a Tabulkách Google a jak z ní vytěžit maximum s pomocí reálných příkladů a užitečných tipů.

Co je funkce SEQUENCE a k čemu se používá?

Funkce SEQUENCE se používá ke generování pole po sobě jdoucích čísel , které „přetéká“ z listu a zabírá více řádků a sloupců. Jinými slovy, zadáte vzorec do buňky a ten automaticky vyplní celý blok po sobě jdoucími čísly.

Jeho chování je v Excelu a Tabulkách Google velmi podobné: v obou případech zadání vzorce vrátí dynamické pole . To znamená, že výsledkem není jedna hodnota, ale sada buněk, které se automaticky vyplní od počáteční buňky.

Představte si, že potřebujete tabulku, která má čísla od 1 do 20 ve 4 řádcích a 5 sloupcích. Místo vyplňování po jednotlivých číslech byste použili =SEQUENCE(4;5) (v místním nastavení, které používá středník) nebo =SEQUENCE(4,5) (v místním nastavení, které používá čárku), a to je vše: budete mít blok 4x5 s čísly od 1 do 20.

Tato funkce je obzvláště užitečná, když chcete vytvářet řízené číselné řady : počítání nahoru nebo dolů, skoky po desítkách, generování identifikátorů, simulace dat nebo příprava struktur pro reporty, dashboardy nebo kontingenční tabulky v Excelu.

Syntaxe SEQUENCE v Excelu a Tabulkách Google

Formální definice SEQUENCE je v obou nástrojích velmi podobná. Obecná syntaxe je:

Obecná syntaxe: =SECUENCIA(filas; columnas; inicio; incremento)

Každý argument má specifickou roli a je důležité mu dobře porozumět, abyste zvládli jeho funkci a vytěžili z něj maximum:

část	popis
filas	Požadované. Určuje počet řádků, které bude mít výsledná matice. Je to výška bloku čísel, který bude vygenerován.
columnas	volitelnýUrčuje počet sloupců v poli. Pokud je ponecháno prázdné, funkce vrátí pouze jeden sloupec.
inicio	volitelnýToto je číslo, na kterém bude sekvence začínat. Pokud není zadáno, počítání standardně začíná od 1.
incremento	volitelnýToto číslo udává, o kolik se posloupnost posune nebo ustoupí. Pokud je vynecháno, zvětší se o 1; pokud je záporné, posloupnost ustoupí.

V závislosti na vašem regionálním nastavení může funkce jako oddělovač argumentů akceptovat čárku nebo středník . Ve Španělsku je středník standardem v Excelu a Tabulkách Google: =SEQUENCE(4;5) . Pokud však vložíte příklad s čárkami a vrátí chybu, jednoduše je nahraďte středníky.

Klíčem je pochopit, že řádky a sloupce určují velikost pole , zatímco start a increment řídí hodnotu prvního čísla a velikost kroku mezi jednotlivými čísly.

Základní příklady použití SEQUENCE

Klasické příklady pro pochopení toho, jak je přetékající matice „malována“ na listu.

Příklad 1: SEQUENCE pouze s řádky

Příklad v buňce A1: Pokud do buňky A1 zadáte následující:

Rychlý vzorec: =SECUENCIA(2)

Vzorec vrátí vertikální pole se 2 řádky a 1 sloupcem , které bude vyplněno následovně:

	A	B
1	1
2	2

Všimněte si, že pokud nezadáte sloupce, funkce vrátí jeden sloupec s hodnotami 1 a 2. Toto chování se často používá k vytváření jednoduchých vertikálních seznamů číslovaných od 1 do n.

Příklad 2: matice s několika řádky a sloupci

V A1: Pokud nyní do A1 napíšete:

Maticový vzorec: =SECUENCIA(2;3)

Funkce vygeneruje matici o 2 řádcích a 3 sloupcích . Rozsah A1:C2 bude vypadat takto:

	A	B	C
1	1	2	3
2	4	5	6
3

Proces probíhá řádek po řádku, zleva doprava : nejprve A1, poté B1, C1, pak A2, B2, C2 atd. Tím se automaticky doplní čísla od 1 do 6 bez nutnosti ručního psaní.

Příklad 3: řízení začátku a přírůstku

Pokud chcete, aby pole nezačínalo od 1 nebo se nezvyšovalo o jedničky, můžete zadat argumenty `start` a `increment`. Zkuste toto:

Vzorec 3-2: =SECUENCIA(2;3;3;2)

Výsledek: Výsledek od A1 dále bude:

	A	B	C
1	3	5	7
2	9	11	13
3

Posloupnost začíná na čísle 3 a zvyšuje se o 2 : 3, 5, 7, 9, 11, 13… opět, přičemž nejprve vyplní horní řádek a poté spodní.

Příklad 4: sestupná posloupnost

Záporný přírůstek: Můžete také vytvořit seznamy, které se vrací zpět pomocí záporného přírůstku. Například:

Sestupný vzorec: =SECUENCIA(2;3;10;-1)

Klesající posloupnost: Generuje tabulku s klesajícími čísly počínaje od 10:

	A	B	C
1	10	9	8
2	7	6	5
3	4	3	2

S tímto trikem si můžete nastavit řízené odpočítávání v několika krocích, jednoduše úpravou počtu řádků a sloupců tak, aby obsahovaly všechny požadované hodnoty.

Vertikální seznamy, horizontální seznamy a transpozice SEQUENCE

Ve výchozím nastavení, když vynecháte argument columns, funkce SEQUENCE vrátí vertikální seznam . To znamená, že budete mít jeden sloupec s tolika řádky, kolik zadáte v prvním parametru. To se nejčastěji stává, když chcete seznam čísel v jednom sloupci.

Pokud potřebujete horizontální seznam (v jednom řádku), máte dvě velmi jednoduché možnosti: buď nastavíte rows = 1 a zbytek velikosti ovládáte pomocí sloupců, nebo vygenerujete vertikální seznam a poté ho transponujete jinou funkcí.

Rychlý příklad: Například pro vytvoření řádku s čísly od 1 do 10 byste použili:

Horizontální vzorec: =SECUENCIA(1;10)

A pokud již máte vertikální posloupnost, můžete ji transformovat na horizontální použitím například v Excelu funkce TRANSPOSE() na danou matici: tímto způsobem byste přešli ze seznamu s jedním sloupcem na řádek se stejnými hodnotami.

Tento typ kombinace je velmi praktický, když připravujete záhlaví tabulek, měsíce po sobě nebo horizontální číslování bez nutnosti kopírování, vkládání nebo přetahování.

Praktické příklady POSLEDNOSTI v Excelu

Praktické využití: Dokumentace k Excelu ukazuje některá poměrně zajímavá využití funkce SEQUENCE v kombinaci s dalšími funkcemi, určenými k rychlému vytváření dynamických dat.

Klasický příklad zahrnuje generování matice o 4 řádcích a 5 sloupcích jednoduše pomocí:

Vzorec 4×5: =SECUENCIA(4;5)

Pomocí tohoto vzorce získáte uspořádaný blok čísel od 1 do 20, který může sloužit jak jako testovací data, tak i pro strukturování šablon nebo tabulek, které vyžadují jasné číslování.

Vytvořte řádek měsíců aktuálního roku

Mnohem zajímavějším využitím je využití funkce SEQUENCE ve spojení s funkcemi pro formátování data a textu. Představte si, že chcete řádek měsíců (leden, únor, březen atd.), který se vždy aktualizuje na aktuální rok, aniž byste museli cokoli ručně měnit.

Vzorec navržený společností Microsoft kombinuje TEXT, DATE, YEAR, TODAY a SEQUENCE takto:

=TEXTO(FECHA(AÑO(HOY());SECUENCIA(1;6);1);"mmm")

Tento vzorec provede následující: vezme aktuální rok pomocí funkce YEAR(TODAY()) , vygeneruje čísla měsíců od 1 do 6 (leden až červen) pomocí SEQUENCE(1;6 ), vytvoří data pomocí DATE(year;month;day) a nakonec je zobrazí zkráceně pomocí TEXT(…;"mmm") . Výsledkem je řádek s prvními šesti měsíci roku, vždy aktualizovaný na aktuální rok.

Generujte rostoucí pole náhodných celých čísel

Další oficiální příklad kombinuje SEQUENCE s INTEGER a RANDOM a získává pole rostoucích náhodných čísel, což je užitečné pro generování fiktivních dat pro testování:

Náhodný vzorec: =SECUENCIA(5;6;ENTERO(ALEATORIO()*100);ENTERO(ALEATORIO()*100))

Zde se používají náhodné funkce k definování počátečního čísla i přírůstku. Výsledkem je matice 5x6 celých čísel, která sledují posloupnost, ale jejichž hodnoty se mění při každém přepočítání tabulky.

Seznamy účetních kódů nebo identifikátorů

Pokud pracujete s účetnictvím nebo potřebujete generovat referenční kódy , SEQUENCE vám může ušetřit spoustu času. Například s:

Kódy vzorců: =SECUENCIA(5;1;1001;1000)

Výsledné hodnoty: Získáte sloupec s 5 řádky a následujícími hodnotami: 1001, 2001, 3001, 4001 a 5001. Je to velmi rychlý způsob, jak generovat číslování s pevnými „mezerami“, které pak můžete použít jako kódy nákladových středisek, účetní účty nebo identifikátory pro klienty či projekty.

POŘADÍ v Google Tabulkách: typické použití a specifika

V Tabulkách Google funguje funkce SEQUENCE téměř identicky jako její excelovský protějšek. Vrací také pole, které přetéká ze zdrojové buňky a sdílí stejnou syntaxi argumentů: řádky, sloupce, začátek, přírůstek.

Rychlý příklad: Jednoduchý příklad by byl:

Vzorce: =SEQUENCE(5;1;1;1)

který generuje sloupec s 5 řádky, počínaje 1 a zvyšující se o 1. Je to přesně stejný princip jako v Excelu, jen v rozhraní Tabulek Google.

Velkou výhodou funkce SEQUENCE v Tabulkách je, že umožňuje dynamicky řídit číselné sekvence a snadno je kombinovat s dalšími funkcemi samotného nástroje nebo s poli, která jste již vytvořili pomocí funkcí ARRAYFORMULA, ROW atd.

Jednoduché číslování versus triky s přetahováním

Je pravda, že v Tabulkách Google můžete buňky rychle číslovat pomocí klasického triku s přetažením úchytu pro výplň . Například do buňky A1 napíšete 1, do buňky A2 2, vyberete obě buňky a přetáhnete je dolů: list detekuje vzorec a pokračuje s 3, 4, 5…

Delší seznam můžete vygenerovat také pomocí nabídky Upravit → Vyplnit → Série, aniž byste jej museli táhnout po celé délce. Zde můžete zadat krok (1, 2, 10 atd.) a konečný limit.

Všechny tyto metody jsou však statické : jakmile je seznam vygenerován a vy vložíte řádky, změníte strukturu nebo potřebujete upravit rozsah, musíte znovu provést odsazení. U metody SEQUENCE se číslování automaticky přizpůsobí, pokud změníte počet řádků v poli nebo upravíte vzorec.

ROW, ARRAYFORMULA a Apps Script jako alternativy

V Tabulkách Google existují i ​​další populární přístupy k číslování, zejména pokud chcete něco dynamického, ale nepotřebujete plnou flexibilitu funkce SEQUENCE. Jednou z typických možností je použití funkce ROW() takto:

Vzorec ROW: =FILA()-FILA($A$1)+1

Přepočet: Myšlenka je taková, že vložením tohoto vzorce do původní buňky a přetažením dolů si každý řádek vypočítá vlastní číslo objednávky. Pokud nad něj vložíte nový řádek, přepočet zůstane konzistentní a čísla zůstanou správně upravena.

Pokud chcete jít ještě o krok dál, můžete kombinovat ROW s ARRAYFORMULA a vyplnit tak celý rozsah najednou pomocí jediného vzorce:

Vzorec ARRAY: =ARRAYFORMULA(FILA(A1:A100)-FILA(A1)+1)

Číslování 1–100: Toto automaticky vytvoří čísla od 1 do 100 ve vybraném sloupci, aniž byste museli táhnout myší. Změnou hodnoty A100 můžete upravit velikost podle potřeby.

A konečně, pro pokročilé uživatele umožňuje Google Apps Script generovat sekvence s vlastní logikou ; v Excelu můžete také automatizovat úlohy programováním maker . Můžete například napsat krátký skript, který vyplňuje pouze lichá čísla, počítá zpětně s nepravidelnými přeskakováními nebo rozmezí čísel na základě specifických podmínek.

Běžné problémy: sloučené buňky a přetékající pole

Jednou z nejmatoucích věcí pro nové uživatele Tabulek Google je použití funkce SEQUENCE ve sloučených buňkách . Pokud máte například skupiny čtyř sloučených buněk a chcete, aby každý blok zobrazoval číslo v odpočítávání (1500, 1496, 1492 atd.), samotná funkce SEQUENCE s touto strukturou dobře nefunguje.

Dynamická pole, a to jak v Tabulkách, tak v Excelu, potřebují k přetečení volný souvislý prostor . Pokud oblast, do které se pokoušejí rozbalit, obsahuje sloučené buňky, existující obsah nebo omezení, pravděpodobně narazíte na chyby nebo se jednoduše nedostaví očekávaný výsledek.

V takových případech může být efektivnější nejprve vytvořit sekvenci v čistém, nesloučeném rozsahu a poté použít odkazy, další vzorce nebo dokonce malý skript k výpisu každé hodnoty do sloučeného bloku buněk, který vás zajímá.

V moderním Excelu se navíc obvykle zobrazí chyba #OVERFLOW!, když přetékající pole narazí na blokující data . Je vhodné si tyto zprávy zkontrolovat, v případě potřeby oblast vymazat a ověřit, zda žádná již existující data nekolidují s výsledkem funkce SEQUENCE.

Související funkce a užitečné kombinace

Užitečné kombinace: Funkce SEQUENCE se při hledání výkonných řešení zřídka používá samostatně. Obvykle se kombinuje s dalšími funkcemi pro generování dynamických polí a dat.

V Excelu funkce jako VLOOKUP a další podpůrné funkce často velmi dobře fungují společně s funkcí SEQUENCE:

• M.JEDNOTA (nebo podobné ve vaší verzi), které vrací matice identity o vámi zadané velikosti, s jedničkami na diagonále a nulami ve zbytku.
• RANDARRAY / MATRIZALEAT, schopný generovat matice náhodných čísel, které mohou sloužit jako základ pro simulace nebo testy.
• FILTR, což umožňuje vybrat z matice vygenerované pomocí funkce SEQUENCE pouze řádky nebo sloupce, které splňují určité podmínky.
• ŘAZENÍ a ŘAZENÍ PODLE, k reorganizaci dat vytvořených pomocí funkce SEQUENCE, například pokud chcete číslování, které závisí na pořadí jiného sloupce.
• UNIKÁTNÍTo je užitečné, když generujete seznamy a chcete zachovat pouze neopakující se hodnoty.

Tyto funkční bloky spolu s konceptem dynamických polí a chování při přetečení umožňují konstrukci poměrně pokročilých řešení bez nutnosti uchylovat se k makrům nebo složitému programování.

V Tabulkách Google otevírá kombinace funkce SEQUENCE s funkcemi ARRAYFORMULA , FILTER , SORT , UNIQUE nebo s funkcemi pro práci s daty, jako je TODAY, EOMONTH atd., dveře k aktualizovatelným sestavám a dashboardům, které generují vlastní sekvence na základě dostupných dat.

Výhody oproti ručnímu číslování nebo číslování pomocí základních triků

Ve srovnání s tradičními metodami (psaní rukou, tažením úchytu pro výplň nebo použitím nabídky Výplň/Série) má funkce SEQUENCE několik důležitých výhod při každodenním použití :

Na jedné straně umožňuje vytvářet extrémně dlouhé seznamy (stovky nebo tisíce řádků) bez nutnosti posouvání nebo tažení. Stačí zadat počet řádků a sloupců a matice se zobrazí okamžitě – což je velmi praktické při práci s velkými objemy dat.

Na druhou stranu, sekvence generované pomocí SEQUENCE se snadno udržují . Pokud později potřebujete rozšířit nebo zmenšit rozsah, jednoduše změníte argument řádku nebo sloupce, aniž byste museli cokoli ručně opakovat. Navíc, protože se jedná o vzorce, mohou záviset na jiných buňkách, což vám umožňuje vytvářet sekvence, které se automaticky zvětšují nebo zmenšují na základě určitých podmínek.

Vyhněte se lidským chybám: Dalším zajímavým bodem je, že funkce SEQUENCE se vyhýbá typickým lidským chybám: zapomenutí čísla, jeho nechtěnému vynechání nebo jeho neúmyslnému opakování. Delegováním tohoto úkolu na funkci zajistíte konzistentní a nepřetržité číslování, a to jak vzestupně, tak sestupně.

A konečně, díky možnosti ovládat začátek a přírůstek se funkce dokonale přizpůsobí velmi různým kontextům: od číslování řádků v sestavách až po vytváření časových řad dat, generování technických kódů nebo nastavování struktur pro datové simulace.

Méně času: Jakmile jej začnete používat, všimnete si, že úkoly, které dříve trvaly několik minut (nebo i déle), se nyní omezují na napsání jediného, ​​promyšleného vzorce.

Využitím funkce SEQUENCE v Excelu a Tabulkách Google a její kombinací s funkcemi pro práci s datem, textem, filtrováním nebo dynamickými funkcemi pole se tabulkový procesor postará o těžkou práci: číslování, vytváření řad a generování strukturovaných dat se stává otázkou několika sekund, se vzorci, které lze snadno upravovat, a prakticky bez rizika manuálních chyb.