Kā soli pa solim lietot funkciju SEQUENCE programmā Excel un Google Sheets

Informatec Digital » Resursi » Kā soli pa solim lietot funkciju SEQUENCE programmā Excel un Google Sheets

Ja kādreiz esat manuāli ierakstījis izklājlapā skaitļus 1, 2, 3… , jūs zināt, ka tas ir viens no pasaulē nogurdinošākajiem uzdevumiem. Un, ja runa nav tikai par 10 rindām, bet gan 500 vai 1000, tas no garlaicīgā kļūst par nepanesamu. Par laimi, programmā Excel un Google Sheets ir funkcija, kas paredzēta tieši šim nolūkam: SEQUENCE , kas spēj izveidot skaitļu sarakstus vairumā, izmantojot vienu formulu.

Lai gan tas varētu šķist nedaudz tehniski, funkcija SEQUENCE ir daudz vienkāršāka, nekā šķiet. Ar pāris precīzi definētiem argumentiem varat ģenerēt pilnīgas sakārtotu skaitļu tabulas, augošas vai dilstošas ​​virknes , saistītus datumus, grāmatvedības kodus un pat parauga datus savām atskaitēm. Aplūkosim tuvāk, kā tā darbojas programmā Excel un Google Sheets, un kā to maksimāli izmantot, izmantojot reālus piemērus un noderīgus padomus.

Kas ir funkcija SEQUENCE un kam tā tiek izmantota?

Funkcija SEQUENCE tiek izmantota, lai ģenerētu secīgu skaitļu masīvu , kas "pārpilda" darblapu, aizņemot vairākas rindas un kolonnas. Citiem vārdiem sakot, jūs ievadāt formulu šūnā, un tā automātiski aizpilda visu bloku ar secīgiem skaitļiem.

Tās darbība programmā Excel un Google Sheets ir ļoti līdzīga: abos gadījumos, ievadot formulu, tiks iegūts dinamisks masīvs . Tas nozīmē, ka rezultāts nav viena vērtība, bet gan šūnu kopa, kas tiek automātiski aizpildīta, sākot no sākotnējās šūnas.

Iedomājieties, ka jums ir nepieciešama tabula, kurā ir skaitļi no 1 līdz 20 4 rindās un 5 kolonnās. Tā vietā, lai to aizpildītu ar skaitļiem pa skaitļiem, jūs vienkārši izmantotu =SEQUENCE(4;5) (reģionālajā iestatījumā, kurā tiek izmantots semikols) vai =SEQUENCE(4,5) (reģionālajā iestatījumā, kurā tiek izmantots komats), un tas arī viss: jums būs 4x5 bloks ar skaitļiem no 1 līdz 20.

Šī funkcija ir īpaši noderīga, ja vēlaties izveidot kontrolētas skaitliskas sērijas : skaitīt uz augšu vai uz leju, pārlēkt pa 10 sekundēm, ģenerēt identifikatorus, simulēt datus vai sagatavot struktūras atskaitēm, informācijas paneļiem vai rakurstabulām programmā Excel.

SEQUENCE sintakse programmā Excel un Google Sheets

SEQUENCE formālā definīcija abos rīkos ir ļoti līdzīga. Vispārīgā sintakse ir šāda :

Vispārīgā sintakse: =SECUENCIA(filas; columnas; inicio; incremento)

Katram argumentam ir noteikta loma, un ir svarīgi to labi izprast, lai apgūtu tā funkciju un gūtu maksimālu labumu no tā:

daļa	apraksts
filas	nepieciešams. Norāda rindu skaitu iegūtajā matricā. Tas ir ģenerētā skaitļu bloka augstums.
columnas	Pēc izvēlesNorāda kolonnu skaitu masīvā. Ja atstāj tukšu, funkcija atgriež tikai vienu kolonnu.
inicio	Pēc izvēlesŠis ir skaitlis, ar kuru sāksies secība. Ja nav norādīts, skaitīšana pēc noklusējuma sākas ar 1.
incremento	Pēc izvēlesTas norāda, cik lielā mērā secība virzīsies uz priekšu vai atpakaļ. Ja netiek ņemts vērā, tas palielinās par 1; ja negatīvs, secība regresēs.

Atkarībā no jūsu reģionālajiem iestatījumiem funkcija var pieņemt vai nu komatu, vai semikolu kā argumentu atdalītāju. Spānijā semikols ir standarts programmā Excel un Google Sheets: =SEQUENCE(4;5) . Tomēr, ja ielīmējat piemēru ar komatiem un tas atgriež kļūdu, vienkārši nomainiet tos ar semikoliem.

Svarīgi ir saprast, ka rindas un kolonnas nosaka masīva lielumu , savukārt sākums un pieaugums kontrolē pirmā skaitļa vērtību un soļa lielumu starp vienu skaitli un nākamo.

SECĪBAS lietošanas pamatpiemēri

Klasiski piemēri , lai saprastu, kā pārpildītā matrica ir "nokrāsota" uz lapas.

1. piemērs: SEQUENCE, kurā ir tikai rindas

Piemērs šūnā A1: Ja šūnā A1 ierakstāt sekojošo:

Ātrā formula: =SECUENCIA(2)

Formula atgriezīs vertikālu masīvu ar 2 rindām un 1 kolonnu , kas tiks aizpildīts šādi:

	A	B
1	1
2	2

Ņemiet vērā, ka, nenorādot kolonnas, funkcija atgriež vienu kolonnu ar vērtībām 1 un 2. Šo darbību bieži izmanto, lai izveidotu vienkāršus vertikālus sarakstus, kas numurēti no 1 līdz n.

2. piemērs: matrica no vairākām rindām un kolonnām

A1 šūnā: Ja tagad rakstāt A1 šūnā:

Matricas formula: =SECUENCIA(2;3)

Funkcija ģenerēs matricu ar 2 rindām un 3 kolonnām . Diapazons A1:C2 izskatīsies šādi:

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

Process notiek rindu pa rindai, no kreisās uz labo : vispirms A1, tad B1, C1, tad A2, B2, C2 utt. Tas automātiski aizpilda skaitļus no 1 līdz 6, neko manuāli nerakstot.

3. piemērs: sākuma un pieauguma vadība

Ja vēlaties, lai masīvs nesāktos ar 1 vai nepalielinātos par vieniniekiem, varat norādīt argumentus `start` un `increment`. Izmēģiniet šo:

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

Rezultāts: Rezultāts, sākot no A1, būs šāds:

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

Secība sākas ar 3 un palielinās par 2 : 3, 5, 7, 9, 11, 13… atkal, vispirms aizpildot augšējo rindu un pēc tam apakšējo rindu.

4. piemērs: dilstoša secība

Negatīvs pieaugums: Varat arī izveidot sarakstus, kas iet atpakaļ, izmantojot negatīvu pieaugumu. Piemēram:

Dilstošā formula: =SECUENCIA(2;3;10;-1)

Dilstoša secība: ģenerē tabulu ar dilstošiem skaitļiem, sākot no 10:

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

Ar šo triku jūs varat iestatīt kontrolētu atpakaļskaitīšanu tikai dažos soļos, vienkārši pielāgojot rindu un kolonnu skaitu, lai iekļautu visas vēlamās vērtības.

Vertikālie saraksti, horizontālie saraksti un SEQUENCE transponēšana

Pēc noklusējuma, ja izlaižat argumentu “columns”, funkcija SEQUENCE atgriež vertikālu sarakstu . Tas nozīmē, ka jums būs viena kolonna ar tik daudz rindām, cik norādāt pirmajā parametrā. Tas visbiežāk notiek, ja vēlaties skaitļu sarakstu vienā kolonnā.

Ja jums ir nepieciešams horizontāls saraksts (vienā rindā), jums ir divas ļoti vienkāršas iespējas: vai nu iestatāt rindas = 1 un pārējo izmēru kontrolējat ar kolonnām, vai arī ģenerējat vertikālo sarakstu un pēc tam to transponējat ar citu funkciju.

Ātrs piemērs: Piemēram, lai izveidotu rindu ar skaitļiem no 1 līdz 10, jūs izmantotu:

Horizontālā formula: =SECUENCIA(1;10)

Un, ja jums jau ir vertikāla secība, varat to pārveidot par horizontālu, piemēram, programmā Excel šai matricai piemērojot funkciju TRANSPOSE() : šādā veidā jūs pārietu no saraksta, kurā ir viena kolonna, uz rindu ar vienādām vērtībām.

Šāda veida kombinācija ir ļoti praktiska, ja sagatavojat tabulas galvenes, mēnešus pēc kārtas vai horizontālu numerāciju, nevajadzot kopēt, ielīmēt vai vilkt.

Praktiski SEQUENCE piemēri programmā Excel

Praktisks pielietojums: Excel dokumentācijā ir parādīti daži diezgan interesanti SEQUENCE pielietojumi kombinācijā ar citām funkcijām, kas paredzēti, lai ātri izveidotu dinamiskos datus.

Klasisks piemērs ietver 4 rindu un 5 kolonnu matricas ģenerēšanu , vienkārši izmantojot:

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

Ar šo formulu jūs iegūsiet sakārtotu skaitļu bloku no 1 līdz 20, kas var kalpot gan kā testa dati, gan veidņu vai izklājlapu strukturēšanai, kurām nepieciešama skaidra numerācija.

Izveidojiet kārtējā gada mēnešu rindu

Daudz interesantāks pielietojums ir izmantot funkciju SEQUENCE kopā ar datuma un teksta formatēšanas funkcijām. Iedomājieties, ka vēlaties mēnešu rindu (Jan, Feb, Mar utt.), kas vienmēr tiek atjaunināta līdz pašreizējam gadam, bez nepieciešamības manuāli neko mainīt.

Microsoft piedāvātā formula apvieno TEXT, DATE, YEAR, TODAY un SEQUENCE šādā veidā:

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

Šī formula veic šādas darbības: tā ņem pašreizējo gadu, izmantojot YEAR(TODAY()) , ģenerē mēnešu numurus no 1 līdz 6 (no janvāra līdz jūnijam) , izmantojot SEQUENCE(1;6 ), izveido datumus ar DATE(year; month; day) un visbeidzot tos saīsināti parāda ar TEXT(…;"mmm") . Rezultāts ir rinda ar gada pirmajiem sešiem mēnešiem, kas vienmēr tiek atjaunināti uz pašreizējo gadu.

Ģenerējiet pieaugošu nejaušu skaitļu masīvu

Citā oficiālā piemērā ir apvienotas funkcijas SEQUENCE ar INTEGER un RANDOM , lai iegūtu pieaugošu nejaušo skaitļu masīvu, kas ir noderīgs fiktīvu datu ģenerēšanai testēšanai:

Nejauša formula: =SECUENCIA(5;6;ENTERO(ALEATORIO()*100);ENTERO(ALEATORIO()*100))

Šeit nejaušas funkcijas tiek izmantotas, lai definētu gan sākuma skaitli, gan pieaugumu. Rezultāts ir 5x6 veselu skaitļu matrica, kas seko progresijai, bet kuras vērtības mainās katru reizi, kad izklājlapa tiek pārrēķināta.

Grāmatvedības kodu vai identifikatoru saraksti

Ja strādājat ar grāmatvedību vai jums ir jāģenerē atsauces kodi , SEQUENCE var ietaupīt daudz laika. Piemēram, ja:

Formulas kodi: =SECUENCIA(5;1;1001;1000)

Iegūtās vērtības: Jūs iegūstat 5 rindu kolonnu ar šādām vērtībām: 1001, 2001, 3001, 4001 un 5001. Tas ir ļoti ātrs veids, kā ģenerēt numerāciju ar fiksētām "atstarpēm", ko pēc tam var izmantot kā izmaksu centru kodus, grāmatvedības kontus vai klientu vai projektu identifikatorus.

SEQUENCE pakalpojumā Google Sheets: tipiski lietojumi un īpatnības

Google izklājlapās funkcija SEQUENCE darbojas gandrīz identiski tās Excel versijai. Tā arī atgriež masīvu, kas pārklājas ar avota šūnu un kam ir tāda pati argumenta sintakse: rindas, kolonnas, sākums, pieaugums.

Ātrs piemērs: vienkāršs piemērs būtu šāds:

Formulu lapas: =SEQUENCE(5;1;1;1)

kas ģenerē 5 rindu kolonnu, sākot ar 1 un palielinot par 1. Tā ir tieši tāda pati ideja kā programmā Excel, tikai Google Sheets saskarnē.

Lielākā SEQUENCE priekšrocība programmā Sheets ir tā, ka tā ļauj dinamiski kontrolēt skaitliskās secības un viegli apvienot tās ar citām rīka funkcijām vai ar masīviem, ko jau esat izveidojis, izmantojot ARRAYFORMULA, ROW utt.

Vienkārša numerācija salīdzinājumā ar vilkšanas un nomešanas trikiem

Ir taisnība, ka pakalpojumā Google Sheets var ātri numurēt šūnas, izmantojot klasisko triku — velkot aizpildīšanas turi . Piemēram, ja A1 šūnā ierakstāt 1, A2 — 2, atlasāt abus un velkat uz leju: lapa nosaka shēmu un turpina ar 3, 4, 5…

Varat arī izmantot izvēlni Rediģēt → Aizpildīt → Sērija , lai ģenerētu garāku sarakstu, to nevelkot vairākus gadus. Tur varat norādīt soli (1, 2, 10 utt.) un galīgo robežu.

Tomēr visas šīs metodes ir statiskas : kad saraksts ir ģenerēts, ja ievietojat rindas, maināt struktūru vai ir nepieciešams modificēt diapazonu, aizpildīšana ir jāatkārto. Savukārt, izmantojot SEQUENCE, numerācija pielāgojas automātiski, ja maināt rindu skaitu masīvā vai pielāgojat formulu.

ROW, ARRAYFORMULA un Apps Script kā alternatīvas

Google izklājlapās ir arī citas populāras numerācijas pieejas, īpaši, ja vēlaties kaut ko dinamisku, bet nav nepieciešama SEQUENCE pilna elastība. Viena tipiska iespēja ir izmantot ROW() funkciju šādi:

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

Pārrēķins: Ideja ir tāda, ka, ielīmējot šo formulu sākotnējā šūnā un velkot uz leju, katra rinda aprēķina savu kārtas numuru. Ja ievietojat jaunu rindu augšpusē, pārrēķins paliek nemainīgs un skaitļi paliek pareizi pielāgoti.

Ja vēlaties spert soli tālāk, varat apvienot ROW ar ARRAYFORMULA , lai aizpildītu visu diapazonu vienlaikus, izmantojot vienu formulu:

MASSĪVA formula: =ARRAYFORMULA(FILA(A1:A100)-FILA(A1)+1)

Numerēšana no 1 līdz 100: Šī metode automātiski izveido skaitļus no 1 līdz 100 izvēlētajā kolonnā bez nepieciešamības vilkt. Mainot A100, varat pielāgot izmēru pēc nepieciešamības.

Visbeidzot, pieredzējušiem lietotājiem Google Apps Script ļauj ģenerēt secības ar pielāgotu loģiku ; programmā Excel varat arī automatizēt uzdevumus, programmējot makro . Piemēram, varat uzrakstīt īsu skriptu, kas aizpilda tikai nepāra skaitļus, skaita atpakaļ ar neregulāriem izlaidumiem vai nu skaita diapazonus, pamatojoties uz konkrētiem nosacījumiem.

Bieži sastopamas problēmas: apvienotas šūnas un pārpildīti masīvi

Viena no vismulsinošākajām lietām jaunajiem Google Sheets lietotājiem ir mēģinājums izmantot funkciju SEQUENCE apvienotajās šūnās . Piemēram, ja jums ir četru apvienotu šūnu grupas un vēlaties, lai katrā blokā tiktu parādīts skaitlis atpakaļskaitīšanā (1500, 1496, 1492 utt.), funkcija SEQUENCE vien šajā struktūrā nedarbojas labi.

Dinamiskiem masīviem gan programmā Sheets, gan Excel ir nepieciešama netraucēta nepārtraukta vieta , lai tie varētu pārpildīties. Ja diapazons, kurā tie mēģina paplašināties, satur apvienotas šūnas, esošu saturu vai ierobežojumus, visticamāk, radīsies kļūdas vai vienkārši netiks parādīts gaidītais rezultāts.

Šādos gadījumos var būt efektīvāk vispirms izveidot secību tīrā, nesapludinātā diapazonā un pēc tam izmantot atsauces, papildu formulas vai pat nelielu skriptu, lai katru vērtību ievietotu jūs interesējošajā sapludinātajā šūnu blokā.

Turklāt mūsdienu programmā Excel, ja pārpildīts masīvs saskaras ar bloķējošiem datiem, parasti tiek parādīta kļūda #OVERFLOW!. Ieteicams pārskatīt šos ziņojumus, nepieciešamības gadījumā notīrīt apgabalu un pārliecināties, ka iepriekš esoši dati netraucē funkcijas SEQUENCE rezultātu.

Saistītās funkcijas un noderīgas kombinācijas

Noderīgas kombinācijas: Meklējot jaudīgus risinājumus, SEQUENCE reti tiek izmantota atsevišķi. Parasti to kombinē ar citām dinamisko masīvu un datu ģenerēšanas funkcijām.

Programmā Excel tādas funkcijas kā VLOOKUP un citas atbalsta funkcijas bieži vien ļoti labi darbojas kopā ar SEQUENCE:

• M.VIENĪBA (vai līdzīgs jūsu versijā), kas atgriež identitātes matrica no norādītā izmēra, ar vieniniekiem pa diagonāli un nullēm pārējā daļā.
• RANDARRAY / MATRIZALEAT, kas spēj ģenerēt nejaušu skaitļu matricas, kuras var kalpot par pamatu simulācijām vai testiem.
• FILTRA, kas ļauj atlasīt tikai tās rindas vai kolonnas, kas atbilst noteiktiem nosacījumiem no matricas, kas ģenerēta ar SEQUENCE.
• Kārtot un kārtot pēc, lai reorganizētu datus, kas ģenerēti ar SEQUENCE, piemēram, ja vēlaties numerāciju, kas ir atkarīga no citas kolonnas secības.
• UNICOSTas ir noderīgi, ja ģenerējat sarakstus un vēlaties saglabāt tikai neatkārtojas vērtības.

Šie funkciju bloki kopā ar dinamisko masīvu un pārpildes uzvedības koncepciju ļauj konstruēt diezgan sarežģītus risinājumus, neizmantojot makro vai sarežģītu programmēšanu.

Google izklājlapās, apvienojot SEQUENCE ar ARRAYFORMULA , FILTER , SORT , UNIQUE vai datuma funkcijām, piemēram, TODAY, EOMONTH utt., var izveidot atjaunināmas atskaites un informācijas paneļus, kas ģenerē savas secības, pamatojoties uz pieejamajiem datiem.

Priekšrocības salīdzinājumā ar numerāciju ar roku vai ar vienkāršiem trikiem

Salīdzinot ar tradicionālajām metodēm (rakstīšanu ar roku, aizpildīšanas tura vilkšanu vai izvēlnes Aizpildīt/Sērija izmantošanu), funkcijai SEQUENCE ir vairākas svarīgas priekšrocības ikdienas lietošanā :

No vienas puses, tas ļauj izveidot ārkārtīgi garus sarakstus (simtiem vai tūkstošiem rindu) bez ritināšanas vai vilkšanas. Vienkārši norādiet rindu un kolonnu skaitu, un matrica parādās uzreiz — ļoti ērti, strādājot ar lieliem datu apjomiem.

No otras puses, ar SEQUENCE ģenerētās secības ir viegli uzturēt . Ja vēlāk ir jāpaplašina vai jāsamazina diapazons, varat vienkārši mainīt rindas vai kolonnas argumentu, neko manuāli neatkārtojot. Turklāt, tā kā tās ir formulas, tās var būt atkarīgas no citām šūnām, ļaujot izveidot secības, kas automātiski palielinās vai samazinās, pamatojoties uz noteiktiem nosacījumiem.

Izvairieties no cilvēciskām kļūdām: Vēl viens interesants aspekts ir tas, ka funkcija SEQUENCE izvairās no tipiskām cilvēciskām kļūdām: skaitļa aizmirsšanas, nejaušas izlaišanas vai netīšas atkārtošanas. Deleģējot šo uzdevumu funkcijai, jūs nodrošināt konsekventu un nepārtrauktu numerāciju gan augošā, gan dilstošā secībā.

Visbeidzot, spējot kontrolēt sākumu un pieaugumu, funkcija lieliski pielāgojas ļoti dažādiem kontekstiem: sākot no rindu numerācijas atskaitēs līdz datumu laika rindu izveidei, tehnisko kodu ģenerēšanai vai struktūru iestatīšanai datu simulācijām.

Mazāk laika: Tiklīdz sāksiet to lietot, pamanīsiet, ka uzdevumi, kuru veikšanai agrāk bija nepieciešamas vairākas minūtes (vai pat vairāk), tagad ir jāsamazina līdz vienas, pārdomātas formulas rakstīšanai.

Izmantojot funkciju SEQUENCE programmā Excel un Google Sheets, apvienojot to ar datuma, teksta, filtrēšanas vai dinamiskā masīva funkcijām, izklājlapa paveic smago darbu: numerācija, sēriju izveide un strukturētu datu ģenerēšana kļūst par sekunžu jautājumu, izmantojot formulas, kuras ir viegli pielāgot, un praktiski bez manuālu kļūdu riska.