A SOROZAT függvény használata Excelben és Google Táblázatokban lépésről lépésre

Informatec Digital » Erőforrás » A SOROZAT függvény használata Excelben és Google Táblázatokban lépésről lépésre

Ha valaha is azon kaptad magad, hogy kézzel kell begépelned az 1, 2, 3… számokat egy táblázatba , akkor tudod, hogy ez a világ egyik legunalmasabb feladata. És ha nem csak 10 sorról van szó, hanem 500-ról vagy 1000-ről, akkor az unalmasból elviselhetetlenné válik. Szerencsére az Excelben és a Google Táblázatokban van egy erre a célra tervezett függvény: a SEQUENCE , amely egyetlen képlettel képes tömegesen számlistákat létrehozni.

Bár kissé technikainak tűnhet, a SOROZAT függvény sokkal egyszerűbb, mint amilyennek látszik. Néhány jól definiált argumentummal teljes táblázatokat generálhatsz rendezett számokból, növekvő vagy csökkenő sorozatokból , összekapcsolt dátumokból, számviteli kódokból, sőt akár mintaadatokból is a jelentéseidhez. Nézzük meg közelebbről, hogyan működik Excelben és Google Táblázatokban, és hogyan hozhatod ki belőle a legtöbbet valós példákkal és hasznos tippekkel.

Mi a SEQUENCE függvény és mire használják?

A SOROZAT függvény egy egymást követő számokból álló tömb létrehozására szolgál , amely "túlcsordul" a munkalapon, több sort és oszlopot foglalva el. Más szóval, ha beírja a képletet egy cellába, az automatikusan kitölt egy teljes blokkot egymást követő számokkal.

A viselkedése nagyon hasonló az Excelben és a Google Táblázatokban: mindkét esetben a képlet beírásával egy dinamikus tömböt kapunk . Ez azt jelenti, hogy az eredmény nem egyetlen érték, hanem egy cellák halmaza, amelyek automatikusan kitöltődnek a kezdeti cellától kezdve.

Képzeld el, hogy szükséged van egy táblázatra, ami 1-től 20-ig terjedő számokat tartalmaz 4 sorban és 5 oszlopban. Ahelyett, hogy számról számra kitöltenéd, egyszerűen használd a =SORREND(4;5) (pontosvesszőt használó regionális beállításokban) vagy a =SORREND(4,5) (vesszőt használó regionális beállításokban) képletet, és kész is: lesz egy 4x5-ös blokkod 1-től 20-ig terjedő számokkal.

Ez a függvény különösen hasznos, ha vezérelt numerikus sorozatokat szeretne létrehozni : felfelé vagy lefelé számlálás, 10-es ugrás, azonosítók generálása, adatok szimulálása, vagy struktúrák előkészítése jelentésekhez, irányítópultokhoz vagy pivot táblázatokhoz az Excelben.

SEQUENCE szintaxis Excelben és Google Táblázatokban

A SEQUENCE formális definíciója mindkét eszközben nagyon hasonló. Az általános szintaxis a következő:

Általános szintaxis: =SECUENCIA(filas; columnas; inicio; incremento)

Minden argumentumnak megvan a maga szerepe, és fontos jól megérteni azt ahhoz, hogy elsajátítsuk a funkcióját és a legtöbbet hozhassuk ki belőle:

rész	leírás
filas	kívánt. Azt jelzi, hogy a kapott mátrix hány sorból fog állni. Ez a generált számblokk magassága.
columnas	választhatóMeghatározza a tömb oszlopainak számát. Ha üresen hagyja, a függvény csak egy oszlopot ad vissza.
inicio	választhatóEz az a szám, amelynél a sorozat elkezdődik. Ha nincs megadva, a számlálás alapértelmezés szerint 1-től kezdődik.
incremento	választhatóEz jelzi, hogy a sorozat mennyire fog előre vagy visszafejlődni. Ha elhagyjuk, 1-gyel növekszik; ha negatív, a sorozat visszafejlődik.

A regionális beállításoktól függően a függvény vesszőt vagy pontosvesszőt fogadhat el argumentum elválasztóként. Spanyolországban a pontosvessző az Excel és a Google Táblázatok szabványos használata: =SORREND(4;5) . Ha azonban egy vesszőket tartalmazó példát illeszt be, és az hibát ad vissza, egyszerűen cserélje ki őket pontosvesszővel.

A lényeg annak megértése, hogy a sorok és oszlopok határozzák meg a tömb méretét , míg a start és az increment az első szám értékét és a két szám közötti lépésköz méretét szabályozza .

A SEQUENCE használatának alapvető példái

Klasszikus példák annak megértésére, hogyan "festik" a túlcsorduló mátrixot a lapon.

1. példa: Csak sorokat tartalmazó SEQUENCE

Példa az A1 cellában: Ha a következőket írja be az A1 cellába:

Gyors képlet: =SECUENCIA(2)

A képlet egy 2 soros és 1 oszlopos függőleges tömböt ad vissza , amely a következőképpen lesz kitöltve:

	A	B
1	1
2	2

Megjegyzendő, hogy az oszlopok megadásának elmulasztása esetén a függvény egyetlen oszlopot ad vissza 1-es és 2-es értékekkel. Ezt a viselkedést gyakran használják egyszerű, 1-től n-ig számozott függőleges listák létrehozására.

2. példa: több soros és oszlopos mátrix

Az A1 cellában: Ha most az A1 cellába ezt írod:

Mátrixképlet: =SECUENCIA(2;3)

A függvény egy 2 soros és 3 oszlopos mátrixot generál . Az A1:C2 tartomány így fog kinézni:

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

A folyamat sorról sorra halad, balról jobbra : először A1, majd B1, C1, majd A2, B2, C2 stb. Ez automatikusan kitölti az 1-től 6-ig terjedő számokat anélkül, hogy kézzel kellene begépelni.

3. példa: indítás és növekmény vezérlése

Ha azt szeretnéd, hogy a tömb ne 1-gyel kezdődjön, és ne egyesekkel növekedjen, megadhatod a `start` és `increment` argumentumokat. Próbáld ki ezt:

3-2. képlet: =SECUENCIA(2;3;3;2)

Eredmény: Az A1-től kezdődően az eredmény a következő lesz:

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

A sorozat 3- mal kezdődik és 2- vel növekszik : 3, 5, 7, 9, 11, 13… ismét, először a felső sort töltve ki, majd az alsót.

4. példa: csökkenő sorrend

Negatív növekmény: Létrehozhat olyan listákat is, amelyek negatív növekménnyel visszafelé haladnak. Például:

Csökkenő képlet: =SECUENCIA(2;3;10;-1)

Csökkenő számsor: Létrehoz egy táblázatot, amelyben a számok csökkennek 10-től kezdődően:

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

Ezzel a trükkel néhány lépésben beállíthatsz szabályozott visszaszámlálásokat , egyszerűen a sorok és oszlopok számának beállításával, hogy az összes kívánt értéket tartalmazzák.

Függőleges listák, vízszintes listák és SEQUENCE transzponálás

Alapértelmezés szerint, ha elhagyjuk az oszlopok argumentumot, a SEQUENCE egy függőleges listát ad vissza . Vagyis egyetlen oszlopunk lesz annyi sorral, amennyit az első paraméterben megadtunk. Ez a leggyakoribb, ha egyetlen oszlopban számok listáját szeretnénk.

Ha egy vízszintes listára van szükséged (egyetlen sorban), két nagyon egyszerű lehetőséged van: vagy a sorok értékét 1-nek állítod be, és a többi méretet oszlopokkal szabályozod, vagy létrehozod a függőleges listát, majd egy másik függvénnyel transzponálod.

Gyors példa: Például egy 1-től 10-ig terjedő számokat tartalmazó sor létrehozásához a következőt kell használnia:

Vízszintes képlet: =SECUENCIA(1;10)

És ha már van egy függőleges sorozatod, akkor azt vízszintessé alakíthatod, ha például az Excelben a TRANSPOSE() függvényt alkalmazod erre a mátrixra: így egyetlen oszlopból álló listából egy azonos értékekkel rendelkező sorba jutnál.

Ez a kombinációtípus nagyon praktikus, ha táblázatfejléceket, egymás utáni hónapokat vagy vízszintes számozást készítünk másolás, beillesztés vagy húzás nélkül.

Gyakorlati példák a SEQUENCE használatára Excelben

Gyakorlati felhasználások: Az Excel dokumentációja a SEQUENCE függvény néhány meglehetősen érdekes felhasználási módját mutatja be más függvényekkel kombinálva, amelyek célja a dinamikus adatok gyors létrehozása.

Egy klasszikus példa egy 4 soros és 5 oszlopos mátrix létrehozására vonatkozik egyszerűen a következőképpen:

4×5-ös képlet: =SECUENCIA(4;5)

Ezzel a képlettel 1-től 20-ig terjedő, rendezett számblokkot kapsz, amely tesztadatként és egyértelmű számozást igénylő sablonok vagy táblázatok strukturálására is szolgálhat.

Hozzon létre egy sort az aktuális év hónapjaiból

Sokkal érdekesebb felhasználási mód a SEQUENCE függvény használata dátum- és szövegformázó függvényekkel együtt. Képzeljük el, hogy egy olyan hónapsort szeretnénk (január, február, márc. stb.), amely mindig frissül az aktuális évre anélkül, hogy bármit is manuálisan módosítanunk kellene.

A Microsoft által javasolt képlet a következőképpen kombinálja a TEXT, DATE, YEAR, TODAY és SEQUENCE értékeket :

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

Ez a képlet a következőket teszi: a YEAR(TODAY()) segítségével veszi az aktuális évet , a SEQUENCE(1;6 ) segítségével generálja a hónapok számát 1-től 6-ig (januártól júniusig) , a DATE(year; month; day) segítségével létrehozza a dátumokat , végül a TEXT(…;"mmm") segítségével rövidítve jeleníti meg őket . Az eredmény egy sor, amely az év első hat hónapját tartalmazza, mindig az aktuális évre frissítve.

Generáljon növekvő tömböt véletlenszerű egész számokból

Egy másik hivatalos példa a SEQUENCE, az INTEGER és a RANDOM függvényeket kombinálja, hogy növekvő véletlenszám-tömböt kapjon, ami hasznos teszteléshez használt próbaadatok előállításához:

Véletlenszerű képlet: =SECUENCIA(5;6;ENTERO(ALEATORIO()*100);ENTERO(ALEATORIO()*100))

A véletlenszerű függvényeket itt használjuk mind a kezdőszám, mind a növekmény meghatározásához. Az eredmény egy 5x6-os egész számokból álló mátrix, amely egy sorozatot követ, de értékei minden alkalommal változnak, amikor a táblázatot újraszámoljuk.

Könyvelési kódok vagy azonosítók listái

Ha könyveléssel foglalkozik, vagy referenciakódokat kell generálnia , a SEQUENCE sok időt takaríthat meg Önnek. Például:

Képletkódok: =SECUENCIA(5;1;1001;1000)

Eredő értékek: Egy 5 soros oszlopot kapsz a következő értékekkel: 1001, 2001, 3001, 4001 és 5001. Ez egy nagyon gyors módja a számozás generálásának fix "hézagokkal", amelyeket aztán költséghelykódokként, könyvelési számlákként vagy ügyfelek vagy projektek azonosítóiként használhatsz.

SEQUENCE a Google Táblázatokban: tipikus felhasználások és sajátosságok

A Google Táblázatokban a SORSZÁM függvény szinte ugyanúgy működik, mint az Excel megfelelője. Ez is egy olyan tömböt ad vissza, amely a forráscellából indul ki, és ugyanazt az argumentumszintaxist használja: sorok, oszlopok, kezdés, növekmény.

Gyors példa: Egy egyszerű példa a következő lenne:

Képletlapok: =SEQUENCE(5;1;1;1)

ami egy 5 soros oszlopot generál, 1-gyel kezdve és 1-gyel növelve. Pontosan ugyanaz az ötlet, mint az Excelben, csak a Google Táblázatok felületén.

A Táblázatokban a SEQUENCE függvény nagy előnye, hogy lehetővé teszi a numerikus sorozatok dinamikus vezérlését , és azok egyszerű kombinálását az eszköz más funkcióival, vagy a már létrehozott tömbökkel, például az ARRAYFORMULA, ROW stb. használatával.

Egyszerű számozás kontra húzás-és-elengedés trükkök

Igaz, hogy a Google Táblázatokban gyorsan számozhatod a cellákat a kitöltőjel húzásának klasszikus trükkjével . Például, ha az A1 cellába 1-et, az A2 cellába 2-t írsz be, mindkettőt kijelölöd, és lefelé húzod: a munkalap érzékeli a mintát, és 3, 4, 5…-tel folytatja.

A Szerkesztés → Kitöltés → Sorozat menüvel hosszabb listát is létrehozhatsz anélkül, hogy korszakokon át kellene húznod. Itt megadhatod a lépésközt (1, 2, 10 stb.) és a végső határértéket.

Azonban ezek a metódusok mind statikusak : a lista létrehozása után, ha sorokat szúrunk be, megváltoztatjuk a szerkezetet, vagy módosítanunk kell a tartományt, újra kell végeznünk a kitöltést. A SEQUENCE esetében ezzel szemben a számozás automatikusan igazodik, ha megváltoztatjuk a tömb sorainak számát, vagy módosítjuk a képletet.

ROW, ARRAYFORMULA és Apps Script alternatívákként

A Google Táblázatokban más népszerű számozási megközelítések is léteznek, különösen akkor, ha valami dinamikusat szeretnénk, de nincs szükségünk a SEQUENCE teljes rugalmasságára. Az egyik tipikus lehetőség a ROW() függvény használata, például így:

ROW képlet: =FILA()-FILA($A$1)+1

Újraszámítás: Az ötlet az, hogy ha beilleszti ezt a képletet a kezdőcellába, és lefelé húzza, minden sor kiszámítja a saját sorszámát. Ha egy új sort szúr be fentebb, az újraszámítás konzisztens marad, és a számok helyesen vannak beállítva.

Ha egy lépéssel tovább szeretnél menni, a ROW és az ARRAYFORMULA kombinálásával egyetlen képlettel kitölthetsz egy teljes tartományt:

TÖMB képlet: =ARRAYFORMULA(FILA(A1:A100)-FILA(A1)+1)

Számozás 1-100: Ez automatikusan létrehozza az 1-től 100-ig terjedő számokat a kiválasztott oszlopban, húzás nélkül. Az A100 módosításával szükség szerint módosíthatja a méretet.

Végül, a haladó felhasználók számára a Google Apps Script lehetővé teszi sorozatok létrehozását egyéni logikával ; az Excelben a feladatok automatizálására is lehetőség van makrók programozásával . Írhat például egy rövid szkriptet, amely csak páratlan számokat tölt ki, szabálytalan kihagyásokkal visszafelé számol, vagy adott feltételek alapján számozza a tartományokat.

Gyakori problémák: egyesített cellák és túlcsorduló tömbök

Az új Google Táblázatok felhasználók számára az egyik legzavaróbb dolog a SEQUENCE használata egyesített cellákban . Ha például négy egyesített cellából álló csoportokkal rendelkezik, és azt szeretné, hogy minden blokkban egy szám jelenjen meg visszaszámlálásban (1500, 1496, 1492 stb.), akkor a SEQUENCE önmagában nem működik jól ezzel a struktúrával.

A dinamikus tömböknek, mind a Táblázatokban, mind az Excelben, akadálytalan, összefüggő helyre van szükségük a túlcsorduláshoz. Ha a kibontani kívánt tartomány egyesített cellákat, meglévő tartalmat vagy korlátozásokat tartalmaz, valószínűleg hibákba ütközünk, vagy egyszerűen nem a várt eredményt látjuk.

Ilyen esetekben hatékonyabb lehet először egy tiszta, nem egyesített tartományban létrehozni a sorozatot , majd hivatkozások, további képletek vagy akár egy kis szkript segítségével minden értéket a kívánt egyesített cellablokkba kiíratni.

Ezenkívül a modern Excelben, amikor egy túlcsorduló tömb blokkoló adatokba ütközik, általában a #TÚLCSORDULÁS! hiba jelenik meg . Célszerű áttekinteni ezeket az üzeneteket, szükség esetén kiüríteni a területet, és ellenőrizni, hogy nincsenek-e már meglévő adatok, amelyek zavarnák a SEQUENCE eredményét.

Kapcsolódó függvények és hasznos kombinációk

Hasznos kombinációk: A SEQUENCE függvényt ritkán használják önmagában, amikor hatékony megoldásokat keresnek. Általában más dinamikus tömb- és adatgeneráló függvényekkel kombinálják.

Az Excelben az olyan függvények, mint a FKERES és más támogató függvények, gyakran nagyon jól működnek együtt a SOROZAT függvénnyel:

• M.EGYSÉG (vagy hasonló a te verziódban), amely egy identitásmátrix a megadott méretből, átlón egyesekkel, a többi részen nullákkal.
• RANDARRAY / MATRIZALEAT, amely képes véletlenszám-mátrixok generálására, amelyek szimulációk vagy tesztek alapjául szolgálhatnak.
• SCREENING, amely lehetővé teszi, hogy a SEQUENCE függvénnyel generált mátrixból csak azokat a sorokat vagy oszlopokat jelölje ki, amelyek megfelelnek bizonyos feltételeknek.
• RENDEZÉS és RENDEZÉS SZERINT, a SEQUENCE függvénnyel előállított adatok átrendezéséhez, például ha olyan számozást szeretne, amely egy másik oszlop sorrendjétől függ.
• EGYEDIEz akkor hasznos, ha listákat generál, és csak a nem ismétlődő értékeket szeretné megtartani.

Ezek a függvényblokkok, a dinamikus tömbök és a túlcsordulási viselkedés koncepciójával együtt , lehetővé teszik meglehetősen fejlett megoldások létrehozását makrók vagy összetett programozás igénybevétele nélkül.

A Google Táblázatokban a SEQUENCE függvény és az ARRAYFORMULA , FILTER , SORT , UNIQUE , vagy olyan dátumfüggvények kombinálása, mint a TODAY, EOMONTH stb., megnyitja az utat a frissíthető jelentések és irányítópultok előtt, amelyek a rendelkezésre álló adatok alapján saját sorozatokat generálnak.

Előnyök a kézi vagy alapvető trükkökkel végzett számozással szemben

A hagyományos módszerekkel (kézzel írás, a kitöltőjel húzása vagy a Kitöltés/Sorozat menü használata) összehasonlítva a SOROZAT függvény számos fontos előnnyel rendelkezik a mindennapi használat során :

Egyrészt lehetővé teszi rendkívül hosszú listák (több száz vagy több ezer sor) létrehozását görgetés vagy húzás nélkül. Egyszerűen adja meg a sorok és oszlopok számát, és a mátrix azonnal megjelenik – nagyon kényelmes nagy mennyiségű adattal való munka esetén.

Másrészről a SEQUENCE függvénnyel generált sorozatok könnyen karbantarthatók . Ha később bővíteni vagy csökkenteni kell a tartományt, egyszerűen módosíthatja a sor- vagy oszlopargumentumot anélkül, hogy bármit is manuálisan újra kellene végeznie. Továbbá, mivel ezek képletek, más celláktól is függhetnek, lehetővé téve olyan sorozatok létrehozását, amelyek bizonyos feltételek alapján automatikusan növekednek vagy zsugorodnak.

Kerülje az emberi hibákat: Egy másik érdekes szempont, hogy a SEQUENCE függvény elkerüli a tipikus emberi hibákat: egy szám elfelejtését, véletlen kihagyását vagy nem szándékos megismétlését. Ha ezt a feladatot a függvényre delegálja, biztosíthatja a konzisztens és folyamatos számozást, mind növekvő, mind csökkenő számokban.

Végül, a kezdés és a növekmény szabályozásának képességével a függvény tökéletesen alkalmazkodik a legkülönbözőbb kontextusokhoz: a jelentések sorainak számozásától kezdve az idősorok létrehozásán át a technikai kódok generálásáig vagy az adatszimulációk struktúráinak beállításáig.

Kevesebb idő: Amint elkezded használni, észre fogod venni, hogy a korábban több percet (vagy akár többet) igénybe vevő feladatok egyetlen, jól átgondolt képlet megírására redukálódnak.

Az Excel és a Google Táblázatok SEQUENCE függvényének dátum-, szöveg-, szűrő- vagy dinamikus tömbfüggvényekkel való kombinálásával a táblázatkezelő elvégzi a nehéz munkát: a számozás, a sorozatok létrehozása és a strukturált adatok generálása másodpercek kérdése, a képletek könnyen módosíthatók, és gyakorlatilag nincs kockázata a manuális hibáknak.