Ako krok za krokom používať funkciu SEQUENCE v Exceli a Tabuľkách Google

Informatec Digital » Zdroje » Ako krok za krokom používať funkciu SEQUENCE v Exceli a Tabuľkách Google

Ak ste sa niekedy pristihli pri manuálnom písaní čísel 1, 2, 3… do tabuľky , viete, že je to jedna z najzdĺhavejších úloh na svete. A keď to nie je len 10 riadkov, ale 500 alebo 1000, stane sa to už neznesiteľným. Našťastie, Excel a Tabuľky Google majú funkciu navrhnutú presne na tento účel: PORADOVNOSŤ , ktorá dokáže hromadne vytvárať zoznamy čísel pomocou jediného vzorca.

Hoci to môže znieť trochu technicky, funkcia SEQUENCE je oveľa jednoduchšia, ako sa zdá. S niekoľkými dobre definovanými argumentmi môžete vygenerovať kompletné tabuľky usporiadaných čísel, rastúcich alebo klesajúcich radov , prepojených dátumov, účtovných kódov a dokonca aj vzorových údajov pre vaše prehľady. Pozrime sa bližšie na to, ako funguje v Exceli a Tabuľkách Google a ako z nej vyťažiť maximum pomocou príkladov z reálneho sveta a užitočných tipov.

Čo je funkcia SEQUENCE a na čo sa používa?

Funkcia SEQUENCE sa používa na generovanie poľa po sebe idúcich čísel, ktoré „pretečie“ hárok a zaberie viacero riadkov a stĺpcov. Inými slovami, zadáte vzorec do bunky a ten automaticky vyplní celý blok po sebe idúcimi číslami.

Jeho správanie je veľmi podobné v Exceli a Tabuľkách Google: v oboch prípadoch vám zadanie vzorca poskytne dynamické pole . To znamená, že výsledkom nie je jedna hodnota, ale sada buniek, ktoré sa automaticky vyplnia od pôvodnej bunky.

Predstavte si, že potrebujete tabuľku s číslami od 1 do 20 v 4 riadkoch a 5 stĺpcoch. Namiesto vypĺňania číslo po čísle by ste použili =SEQUENCE(4;5) (v regionálnom nastavení, ktoré používa bodkočiarku) alebo =SEQUENCE(4,5) (v regionálnom nastavení, ktoré používa čiarku) a to je všetko: budete mať blok 4x5 s číslami od 1 do 20.

Táto funkcia je obzvlášť užitočná, keď chcete vytvárať riadené číselné rady : počítanie nahor alebo nadol, skákanie po desiatkach, generovanie identifikátorov, simulácia údajov alebo príprava štruktúr pre zostavy, dashboardy alebo kontingenčné tabuľky v Exceli.

Syntax SEQUENCE v Exceli a Tabuľkách Google

Formálna definícia SEQUENCE je v oboch nástrojoch veľmi podobná. Všeobecná syntax je:

Všeobecná syntax: =SECUENCIA(filas; columnas; inicio; incremento)

Každý argument má špecifickú úlohu a je dôležité mu dobre porozumieť, aby ste zvládli jeho funkciu a vyťažili z neho maximum:

časť	popis
filas	požadovanýOznačuje počet riadkov, ktoré bude mať výsledná matica. Je to výška bloku čísel, ktorý bude vygenerovaný.
columnas	voliteľnýUrčuje počet stĺpcov v poli. Ak sa ponechá prázdne, funkcia vráti iba jeden stĺpec.
inicio	voliteľnýToto je číslo, od ktorého bude postupnosť začínať. Ak nie je zadané, počítanie sa štandardne začína od 1.
incremento	voliteľnýToto číslo udáva, o koľko sa postupnosť posunie dopredu alebo dozadu. Ak sa vynechá, zvýši sa o 1; ak je záporné, postupnosť sa posunie dozadu.

V závislosti od vašich regionálnych nastavení môže funkcia akceptovať ako oddeľovač argumentov buď čiarku, alebo bodkočiarku . V Španielsku je bodkočiarka štandardom v Exceli a Tabuľkách Google: =SEQUENCE(4;5) . Ak však vložíte príklad s čiarkami a vráti chybu, jednoducho ich nahraďte bodkočiarkami.

Kľúčom je pochopiť, že riadky a stĺpce určujú veľkosť poľa , zatiaľ čo parametre začiatok a prírastok riadia hodnotu prvého čísla a veľkosť kroku medzi jednotlivými číslami.

Základné príklady použitia SEQUENCE

Klasické príklady na pochopenie toho, ako je pretekajúca matica „namaľovaná“ na hárku.

Príklad 1: PORADOVOSŤ iba s riadkami

Príklad v bunke A1: Ak do bunky A1 zadáte nasledujúci text:

Rýchly vzorec: =SECUENCIA(2)

Vzorec vráti vertikálne pole s 2 riadkami a 1 stĺpcom , ktoré bude vyplnené nasledovne:

	A	B
1	1
2	2

Všimnite si, že ak nezadáte stĺpce, funkcia vráti jeden stĺpec s hodnotami 1 a 2. Toto správanie sa často používa na vytváranie jednoduchých vertikálnych zoznamov číslovaných od 1 do n.

Príklad 2: matica s niekoľkými riadkami a stĺpcami

V bunke A1: Ak teraz napíšete do bunky A1:

Vzorec matice: =SECUENCIA(2;3)

Funkcia vygeneruje maticu s 2 riadkami a 3 stĺpcami . Rozsah A1:C2 bude vyzerať takto:

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

Proces pokračuje riadok po riadku, zľava doprava : najprv A1, potom B1, C1, potom A2, B2, C2 atď. Týmto sa automaticky doplnia čísla od 1 do 6 bez nutnosti manuálneho písania.

Príklad 3: riadenie začiatku a prírastku

Ak chcete, aby pole nezačínalo od 1 alebo sa nezvyšovalo o jednotky, môžete zadať argumenty `start` a `increment`. Skúste toto:

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

Výsledok: Výsledok od A1 bude:

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

Postupnosť začína na čísle 3 a zvyšuje sa o 2 : 3, 5, 7, 9, 11, 13… opäť, pričom najprv vyplní horný riadok a potom spodný riadok.

Príklad 4: zostupná postupnosť

Záporný prírastok: Môžete tiež vytvoriť zoznamy, ktoré idú dozadu pomocou záporného prírastku. Napríklad:

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

Klesajúca postupnosť: Vygeneruje tabuľku s klesajúcimi číslami od 10:

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

Pomocou tohto triku môžete nastaviť kontrolované odpočítavanie len v niekoľkých krokoch, jednoducho úpravou počtu riadkov a stĺpcov tak, aby zahŕňali všetky požadované hodnoty.

Vertikálne zoznamy, horizontálne zoznamy a transpozícia SEQUENCE

V predvolenom nastavení, keď vynecháte argument columns, funkcia SEQUENCE vráti vertikálny zoznam . To znamená, že budete mať jeden stĺpec s toľkými riadkami, koľko zadáte v prvom parametri. Toto sa najčastejšie stáva, keď chcete zoznam čísel v jednom stĺpci.

Ak potrebujete horizontálny zoznam (v jednom riadku), máte dve veľmi jednoduché možnosti: buď nastavíte rows = 1 a zvyšok veľkosti ovládate stĺpcami, alebo vygenerujete vertikálny zoznam a potom ho transponujete inou funkciou.

Rýchly príklad: Napríklad, na vytvorenie riadku s číslami od 1 do 10 by ste použili:

Horizontálny vzorec: =SECUENCIA(1;10)

A ak už máte vertikálnu postupnosť, môžete ju transformovať na horizontálnu použitím napríklad v Exceli funkcie TRANSPOSE() na túto maticu: týmto spôsobom by ste prešli zo zoznamu s jedným stĺpcom na riadok s rovnakými hodnotami.

Tento typ kombinácie je veľmi praktický pri príprave hlavičiek tabuliek, mesiacov za sebou alebo horizontálneho číslovania bez nutnosti kopírovania, vkladania alebo ťahania.

Praktické príklady PORADÍ v Exceli

Praktické využitie: Dokumentácia k Excelu ukazuje niekoľko zaujímavých využití funkcie SEQUENCE v kombinácii s inými funkciami, ktoré sú určené na rýchle vytváranie dynamických údajov.

Klasický príklad zahŕňa generovanie matice so 4 riadkami a 5 stĺpcami jednoducho pomocou:

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

Pomocou tohto vzorca získate usporiadaný blok čísel od 1 do 20, ktorý môže slúžiť ako testovacie dáta aj na štruktúrovanie šablón alebo tabuliek, ktoré vyžadujú jasné číslovanie.

Vytvoriť riadok mesiacov aktuálneho roka

Oveľa zaujímavejšie využitie je využiť funkciu SEQUENCE v spojení s funkciami formátovania dátumu a textu. Predstavte si, že chcete riadok mesiacov (jan, feb, mar atď.), ktorý sa vždy aktualizuje na aktuálny rok bez toho, aby ste museli čokoľvek manuálne meniť.

Vzorec navrhnutý spoločnosťou Microsoft kombinuje TEXT, DÁTUM, ROK, DNES a PORADIE týmto spôsobom:

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

Tento vzorec robí nasledovné: vezme aktuálny rok pomocou funkcie YEAR(TODAY()) , vygeneruje čísla mesiacov od 1 do 6 (január až jún) pomocou funkcie SEQUENCE(1;6 ), vytvorí dátumy pomocou funkcie DATE(year;month;day) a nakoniec ich zobrazí skrátene pomocou funkcie TEXT(…;"mmm") . Výsledkom je riadok s prvými šiestimi mesiacmi roka, vždy aktualizovaný na aktuálny rok.

Generovanie rastúceho poľa náhodných celých čísel

Ďalší oficiálny príklad kombinuje SEQUENCE s INTEGER a RANDOM , čím sa získa pole rastúcich náhodných čísel, čo je užitočné na generovanie fiktívnych údajov na testovanie:

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

Náhodné funkcie sa tu používajú na definovanie počiatočného čísla aj prírastku. Výsledkom je matica 5x6 celých čísel, ktoré sledujú postupnosť, ale ktorých hodnoty sa menia pri každom prepočítavaní tabuľky.

Zoznamy účtovných kódov alebo identifikátorov

Ak pracujete s účtovníctvom alebo potrebujete generovať referenčné kódy , SEQUENCE vám môže ušetriť veľa času. Napríklad s:

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

Výsledné hodnoty: Získate stĺpec s 5 riadkami s nasledujúcimi hodnotami: 1001, 2001, 3001, 4001 a 5001. Je to veľmi rýchly spôsob, ako vygenerovať číslovanie s pevnými „medzerami“, ktoré potom môžete použiť ako kódy nákladových stredísk, účtovné účty alebo identifikátory pre klientov alebo projekty.

PORADIE v Tabuľkách Google: typické použitie a špecifiká

V Tabuľkách Google funguje funkcia SEQUENCE takmer identicky ako jej excelovský ekvivalent. Vracia tiež pole, ktoré presahuje zdrojovú bunku a zdieľa rovnakú syntax argumentov: riadky, stĺpce, začiatok, prírastok.

Rýchly príklad: Jednoduchý príklad by bol:

Tabuľky vzorcov: =SEQUENCE(5;1;1;1)

ktorý generuje stĺpec s 5 riadkami, začínajúci od 1 a zvyšujúci sa o 1. Je to úplne rovnaký princíp ako v Exceli, len v rozhraní Tabuľiek Google.

Veľkou výhodou funkcie SEQUENCE v Tabuľkách je, že umožňuje dynamicky ovládať číselné postupnosti a jednoducho ich kombinovať s inými funkciami samotného nástroja alebo s poľami, ktoré ste už vytvorili pomocou funkcií ARRAYFORMULA, ROW atď.

Jednoduché číslovanie verzus triky s drag-and-drop

Je pravda, že v Tabuľkách Google môžete rýchlo číslovať bunky pomocou klasického triku s potiahnutím úchytu na vyplnenie . Napríklad, do bunky A1 napíšete 1, do bunky A2 2, vyberiete obe bunky a potiahnete ich nadol: hárok rozpozná vzor a pokračuje s 3, 4, 5…

Dlhší zoznam môžete vygenerovať aj pomocou ponuky Upraviť → Vyplniť → Séria bez toho, aby ste ho museli ťahať po sériách. Tam môžete zadať krok (1, 2, 10 atď.) a konečný limit.

Všetky tieto metódy sú však statické : po vygenerovaní zoznamu, ak vložíte riadky, zmeníte štruktúru alebo potrebujete upraviť rozsah, musíte znova vykonať doplnenie. Pri metóde SEQUENCE sa číslovanie automaticky prispôsobí, ak zmeníte počet riadkov v poli alebo upravíte vzorec.

ROW, ARRAYFORMULA a Apps Script ako alternatívy

V Tabuľkách Google existujú aj iné populárne prístupy k číslovaniu, najmä ak chcete niečo dynamické, ale nepotrebujete plnú flexibilitu funkcie SEQUENCE. Jednou z typických možností je použiť funkciu ROW() takto:

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

Prepočet: Myšlienka je taká, že vložením tohto vzorca do pôvodnej bunky a potiahnutím nadol sa každý riadok vypočíta s vlastným poradovým číslom. Ak nad riadok vložíte nový riadok, prepočet zostane konzistentný a čísla zostanú správne upravené.

Ak chcete ísť ešte o krok ďalej, môžete kombinovať ROW s ARRAYFORMULA a vyplniť celý rozsah naraz pomocou jedného vzorca:

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

Číslovanie 1-100: Toto automaticky vytvorí čísla od 1 do 100 vo vami vybranom stĺpci bez nutnosti ťahania. Zmenou hodnoty A100 môžete podľa potreby upraviť veľkosť.

Pre pokročilých používateľov umožňuje Google Apps Script generovať sekvencie s vlastnou logikou ; v Exceli môžete úlohy automatizovať aj programovaním makier . Môžete napríklad napísať krátky skript, ktorý vypĺňa iba nepárne čísla, počíta spätne s nepravidelnými preskokmi alebo rozsahy čísel na základe špecifických podmienok.

Bežné problémy: zlúčené bunky a preplnené polia

Jednou z najmätúcejších vecí pre nových používateľov Tabuliek Google je pokus o použitie funkcie SEQUENCE v zlúčených bunkách . Ak máte napríklad skupiny štyroch zlúčených buniek a chcete, aby každý blok zobrazoval číslo v odpočítavaní (1500, 1496, 1492 atď.), samotná funkcia SEQUENCE s touto štruktúrou dobre nefunguje.

Dynamické polia, v Tabuľkách aj Exceli, potrebujú na pretečenie voľný súvislý priestor . Ak rozsah, do ktorého sa pokúšajú rozšíriť, obsahuje zlúčené bunky, existujúci obsah alebo obmedzenia, pravdepodobne sa vyskytnú chyby alebo sa jednoducho nezobrazí očakávaný výsledok.

V takýchto prípadoch môže byť efektívnejšie najprv vytvoriť postupnosť v čistom, nezlúčenom rozsahu a potom použiť odkazy, ďalšie vzorce alebo dokonca malý skript na výpis každej hodnoty do zlúčeného bloku buniek, ktorý vás zaujíma.

Okrem toho sa v modernom Exceli, keď pretečené pole narazí na blokujúce dáta, zvyčajne zobrazí chyba #OVERFLOW!. Odporúča sa skontrolovať tieto správy, v prípade potreby vymazať oblasť a overiť, či žiadne existujúce dáta neovplyvňujú výsledok funkcie SEQUENCE.

Súvisiace funkcie a užitočné kombinácie

Užitočné kombinácie: Funkcia SEQUENCE sa zriedka používa samostatne pri hľadaní výkonných riešení. Zvyčajne sa kombinuje s inými funkciami dynamického generovania polí a dát.

V Exceli funkcie ako VLOOKUP a ďalšie podporné funkcie často veľmi dobre fungujú spolu s funkciou SEQUENCE:

• M.JEDNOTA (alebo podobné vo vašej verzii), ktoré vráti matica identity veľkosti, ktorú zadáte, s jednotkami na uhlopriečke a nulami vo zvyšku.
• RANDARRAY / MATRIZALEAT, schopný generovať matice náhodných čísel, ktoré môžu slúžiť ako základ pre simulácie alebo testy.
• FILTER, čo vám umožňuje vybrať z matice vygenerovanej pomocou funkcie SEQUENCE iba riadky alebo stĺpce, ktoré spĺňajú určité podmienky.
• ZORIEDIŤ a ZORIEDIŤ PODĽA, na reorganizáciu údajov vytvorených pomocou funkcie SEQUENCE, napríklad ak chcete číslovanie, ktoré závisí od poradia iného stĺpca.
• UNICOSToto je užitočné, keď generujete zoznamy a chcete zachovať iba neopakujúce sa hodnoty.

Tieto funkčné bloky spolu s konceptom dynamických polí a správania pri pretečení umožňujú vytvárať pomerne pokročilé riešenia bez použitia makier alebo zložitého programovania.

V Tabuľkách Google otvára kombinácia funkcie SEQUENCE s funkciami ARRAYFORMULA , FILTER , SORT , UNIQUE alebo dátumovými funkciami ako TODAY, EOMONTH atď. dvere k aktualizovateľným prehľadom a dashboardom, ktoré generujú vlastné sekvencie na základe dostupných údajov.

Výhody oproti číslovaniu ručným alebo základnými trikmi

V porovnaní s tradičnými metódami (písanie rukou, ťahanie úchytu výplne alebo použitie ponuky Výplň/Séria) má funkcia SEQUENCE niekoľko dôležitých výhod pri každodennom používaní :

Na jednej strane vám umožňuje vytvárať extrémne dlhé zoznamy (stovky alebo tisíce riadkov) bez rolovania alebo ťahania. Stačí zadať počet riadkov a stĺpcov a matica sa zobrazí okamžite – čo je veľmi praktické pri práci s veľkými objemami údajov.

Na druhej strane, sekvencie generované pomocou SEQUENCE sa ľahko udržiavajú . Ak neskôr potrebujete rozšíriť alebo zmenšiť rozsah, jednoducho zmeníte argument riadka alebo stĺpca bez toho, aby ste museli čokoľvek manuálne opakovať. Navyše, keďže ide o vzorce, môžu závisieť od iných buniek, čo vám umožňuje vytvárať sekvencie, ktoré sa automaticky zväčšujú alebo zmenšujú na základe určitých podmienok.

Vyhnite sa ľudským chybám: Ďalším zaujímavým bodom je, že funkcia SEQUENCE sa vyhýba typickým ľudským chybám: zabudnutiu čísla, náhodnému vynechaniu čísla alebo jeho neúmyselnému opakovaniu. Delegovaním tejto úlohy na funkciu zabezpečíte konzistentné a nepretržité číslovanie, a to vzostupne aj zostupne.

Nakoniec, vďaka možnosti ovládať začiatok a prírastok sa funkcia dokonale prispôsobí veľmi rôznym kontextom: od číslovania riadkov v prehľadoch až po vytváranie časových radov dátumov, generovanie technických kódov alebo nastavovanie štruktúr pre simulácie údajov.

Menej času: Hneď ako ho začnete používať, všimnete si, že úlohy, ktoré predtým trvali niekoľko minút (alebo aj viac), sa zredukovali na napísanie jediného, ​​premysleného vzorca.

Využitím funkcie SEQUENCE v Exceli a Tabuľkách Google a jej kombináciou s funkciami dátumu, textu, filtrovania alebo dynamického poľa, tabuľkový procesor urobí ťažkú ​​prácu: číslovanie, vytváranie sérií a generovanie štruktúrovaných údajov sa stane otázkou niekoľkých sekúnd, s ľahko upraviteľnými vzorcami a prakticky bez rizika manuálnych chýb.