Excelin ja Google Sheetsin SEQUENCE-funktion käyttäminen vaihe vaiheelta

Informatec Digital » Resurssit » Excelin ja Google Sheetsin SEQUENCE-funktion käyttäminen vaihe vaiheelta

Jos olet joskus huomannut kirjoittavasi 1, 2, 3… käsin taulukkolaskentaohjelmallaTiedäthän, että se on yksi maailman tylsimmistä tehtävistä. Ja kun rivien määrä ei ole 10, vaan 500 tai 1000, se muuttuu tylsästä sietämättömäksi. Onneksi Excelissä ja Google Sheetsissä on juuri tätä varten suunniteltu funktio: SEKVENSSI, joka pystyy luomaan numerolistoja irtotavarana yhdellä kaavalla.

Vaikka se saattaa kuulostaa hieman "tekniseltä", SEQUENCE-funktio on paljon yksinkertaisempi kuin miltä se näyttää. Muutamalla hyvin määritellyllä argumentilla voit luoda täydelliset taulukot järjestetyistä numeroista, sarjoista, jotka kasvavat tai pienenevätLinkitetyt päivämäärät, kirjanpitokoodit ja jopa esimerkkidata raporttejasi varten. Katsotaanpa tarkemmin, miten se toimii Excelissä ja Google Sheetsissä ja miten saat siitä kaiken irti tosielämän esimerkkien ja hyödyllisten vinkkien avulla.

Mikä on SEQUENCE-funktio ja mihin sitä käytetään?

SEKVENSI-funktio (englanniksi) Sekvenssi) käytetään luo peräkkäisten lukujen matriisi Se "vuotaa" taulukon yli ja täyttää useita rivejä ja sarakkeita. Toisin sanoen, kirjoitat kaavan yhteen soluun ja se täyttää koko lohkon peräkkäisillä numeroilla.

Sen toiminta on hyvin samanlaista Excelissä ja Google Sheetsissä: molemmissa tapauksissa, kun syötät kaavan, saat dynaaminen matriisiTämä tarkoittaa, että tulos ei ole yksittäinen arvo, vaan joukko soluja, jotka täytetään automaattisesti alkaen alkuperäisestä solusta.

Kuvittele, että tarvitset taulukon, joka sisältää numeroita yhdestä kahteenkymmeneen neljällä rivillä ja viidellä sarakkeella. Sen sijaan, että täyttäisit sen numero numerolta, käyttäisit vain =SEKVENSI(4;5) (alueellisella asetuksella, jossa käytetään puolipisteitä) tai =SEKVENSI(4,5) (pilkkumuodossa), ja siinä kaikki: sinulla on 4x5-lohko, jossa on numerot 1-20.

Tämä toiminto on erityisen hyödyllinen, kun haluat luoda kontrolloidut numeeriset sarjatLaske ylös tai alas, hyppää kymmenillä, luo tunnisteita, simuloi tietoja tai valmistele rakenteita raportteja, koontinäyttöjä tai muita vastaavia sovelluksia varten. pivot-taulukot Excelissä.

SEQUENCE-syntaksi Excelissä ja Google Sheetsissä

La muodollinen määritelmä SEQUENCE-syntaksi on hyvin samanlainen molemmissa työkaluissa. Yleinen syntaksi on:

Yleinen syntaksi: =SECUENCIA(filas; columnas; inicio; incremento)

Jokainen argumentti Sillä on tietty rooli, ja on tärkeää ymmärtää se hyvin, jotta toiminto hallitaan ja siitä saadaan kaikki irti:

osa	kuvaus
filas	vaaditaan. Ilmaisee tuloksena olevan matriisin rivien lukumäärän. Se on luotavan lukulohkon korkeus.
columnas	valinnainenMäärittää taulukon sarakkeiden määrän. Jos tämä jätetään tyhjäksi, funktio palauttaa vain yhden sarakkeen.
inicio	valinnainenTämä on numero, josta sekvenssi alkaa. Jos sitä ei ole määritetty, laskenta alkaa oletusarvoisesti yhdestä.
incremento	valinnainenTämä osoittaa, kuinka paljon sarja etenee tai taantuu. Jos se jätetään pois, se kasvaa yhdellä; jos negatiivinen, sarja taantuu.

Alueellisista asetuksistasi riippuen ominaisuus saattaa tukea pilkku tai puolipiste argumenttien erottimena. Espanjassa puolipiste on Excelin ja Google Sheetsin standardi: =SEKVENSI(4;5)Joka tapauksessa, jos liität esimerkin pilkkujen kanssa ja saat virheen, korvaa pilkut puolipisteillä.

Tärkeintä on ymmärtää, mitkä rivit ja sarakkeet määrittävät matriisin koko, samalla kun käynnistät ja lisäät ohjausta ensimmäisen numeron arvo ja askeleen koko yhden numeron ja seuraavan välillä.

Esimerkkejä SEQUENCE-ominaisuuden käytöstä

Klassisia esimerkkejä ymmärtää, miten ylivuotoinen matriisi "maalataan" arkille.

Esimerkki 1: SEQUENCE, jossa on vain rivejä

Esimerkki A1-solussa: Jos kirjoitat soluun A1 seuraavan:

Nopea kaava: =SECUENCIA(2)

Kaava palauttaa pystysuuntaisen taulukon, jossa on 2 riviä ja 1 sarakejoka täytetään seuraavasti:

	A	B
1	1
2	2

Huomaa, että jos sarakkeita ei määritetä, funktio palauttaa yksi sarake, jonka arvot ovat 1 ja 2Tätä toimintaa käytetään usein yksinkertaisten pystysuuntaisten, 1:stä n:ään numeroitujen listojen luomiseen.

Esimerkki 2: matriisi, jossa on useita rivejä ja sarakkeita

A1-kohdassa: Jos nyt kirjoitat A1-soluun:

Matriisikaava: =SECUENCIA(2;3)

Funktio luo matriisin, jossa on 2 3 XNUMX riviä ja XNUMX XNUMX sarakettaAlue A1:C2 näyttää tältä:

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

Reitti kulkee rivien välein, vasemmalta oikealleEnsin A1, sitten B1, C1, sitten A2, B2, C2 jne. Tämä täyttää numerot 1-6 automaattisesti ilman käsin kirjoittamista.

Esimerkki 3: aloituksen ja lisäyksen hallinta

Jos haluat, että taulukko ei ala luvusta 1 eikä kasva ykkösillä, voit määrittää `start`- ja `increment`-argumentit. Kokeile tätä:

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

tulos: Tulos A1:stä eteenpäin on:

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

Sarja alkaa 3 ja se kasvaa jatkuvasti 2 ja 2: 3, 5, 7, 9, 11, 13… jälleen, täytä ensin ylin rivi ja sitten alin rivi.

Esimerkki 4: laskeva järjestys

Negatiivinen kasvu: Voit myös luoda listoja, jotka menevät taaksepäin käyttämällä negatiivista lisäystä. Esimerkiksi:

Laskeva kaava: =SECUENCIA(2;3;10;-1)

Laskeva järjestys: Luo taulukko, jossa numerot alkavat kymmenestä ja alkavat laskevasti:

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

Tällä tempulla voit ajaa hallitut lähtölaskennat vain muutamassa vaiheessa, yksinkertaisesti säätämällä rivien ja sarakkeiden määrää niin, että kaikki halutut arvot sisältyvät.

Pystysuuntaiset listat, vaakasuuntaiset listat ja SEQUENCE-transponointi

Oletusarvoisesti, kun jätät sarakeargumentin pois, SEQUENCE palauttaa pystysuora listaToisin sanoen, sinulla on yksi sarake, jossa on niin monta riviä kuin ensimmäisessä parametrissa määrität. Tämä on yleisin lähestymistapa, kun haluat numeroluettelon yhdessä sarakkeessa.

Jos tarvitset a vaakasuora lista (yhdellä rivillä), sinulla on kaksi hyvin yksinkertaista vaihtoehtoa: joko asetat rivit = 1 ja hallitset loput koosta sarakkeilla, tai luot pystysuuntaisen listan ja sitten transponoit sen toisella funktiolla.

Nopea esimerkki: Esimerkiksi rivin luomiseksi, jossa on numeroita 1-10, käyttäisit seuraavaa:

Vaakasuora kaava: =SECUENCIA(1;10)

Ja jos sinulla on jo pystysuora sarja, voit muuntaa sen vaakasuoraksi käyttämällä esimerkiksi Excelissä funktiota TRANSPONOI() tuolla matriisilla: tällä tavalla siirtyisit yhden sarakkeen luettelosta riviin, jolla on samat arvot.

Tämän tyyppinen yhdistelmä on erittäin käytännöllinen valmistautuessasi taulukon otsikot, peräkkäiset kuukaudet tai vaakasuuntainen numerointi ilman kopiointia, liittämistä tai vetämistä.

Käytännön esimerkkejä SEQUENCE-funktiosta Excelissä

Käytännön käyttötarkoitukset: Excelin dokumentaatiossa on esitetty joitakin melko mielenkiintoisia SEQUENCE-funktion käyttötapoja yhdistettynä muihin funktioihin, jotka on suunniteltu dynaamisen datan nopeaan rakentamiseen.

Klassinen esimerkki liittyy 4 rivin ja 5 sarakkeen matriisi yksinkertaisesti:

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

Tämän kaavan avulla saat järjestetyn numerolohkon 1–20, jota voidaan käyttää sekä testidatana että selkeää numerointia vaativien mallipohjien tai laskentataulukoiden rakentamiseen.

Luo rivi kuluvan vuoden kuukausista

Paljon mielenkiintoisempi käyttötapa on hyödyntää SEQUENCE-funktiota yhdessä päivämäärä- ja tekstinmuotoilufunktioiden kanssa. Kuvittele, että haluat kuukausirivin (tammi, helmikuu, maaliskuu jne.), joka päivittyy aina kuluvaan vuoteen ilman, että sinun tarvitsee muuttaa mitään manuaalisesti.

Microsoftin ehdottama kaava yhdistää TEKSTI, PÄIVÄMÄÄRÄ, VUOSI, TÄMÄ PÄIVÄ ja SARJA tällä tavalla:

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

Tämä kaava tekee seuraavaa: se ottaa kuluvan vuoden käyttäen VUOSI(TÄNÄÄN()), generoi SEKVENSIN(1;6) Kuukausinumerot 1–6 (tammikuusta kesäkuuhun), luo päivämäärät PÄIVÄMÄÄRÄ(vuosi; kuukausi; päivä)ja lopuksi lyhennetyt näytteet, joissa on TEKSTI(…;»mmm»)Tuloksena on rivi, joka sisältää vuoden kuusi ensimmäistä kuukautta ja joka on aina päivitetty kuluvaan vuoteen.

Luo kasvava joukko satunnaisia ​​kokonaislukuja

Toinen virallinen esimerkki yhdistää SEQUENCE- ja KOKONAINEN ja SATUNNAINEN kasvavan satunnaislukutaulukon saamiseksi, mikä on hyödyllistä testidatan luomiseen:

Satunnainen kaava: =SECUENCIA(5;6;ENTERO(ALEATORIO()*100);ENTERO(ALEATORIO()*100))

Satunnaisfunktioita käytetään tässä sekä aloitusluvun että lisäyksen määrittämiseen. Tuloksena on matriisi, jossa on 5 riviä kertaa 6 saraketta kokonaislukuja, jotka seuraavat jonoa, mutta joiden arvot muuttuvat joka kerta, kun taulukko lasketaan uudelleen.

Kirjanpitokoodien tai -tunnisteiden luettelot

Jos työskentelet kirjanpidon parissa tai sinun on tuotettava viitekooditSEQUENCE voi säästää paljon aikaa. Esimerkiksi seuraavilla tavoilla:

Kaavakoodit: =SECUENCIA(5;1;1001;1000)

Tuloksena olevat arvot: Saat viisirivisen sarakkeen, jossa on seuraavat arvot: 1001, 2001, 3001, 4001 ja 5001. Se on erittäin nopea tapa luoda numerointi kiinteillä "hyppyillä", joita voit sitten käyttää kustannuspaikkakoodeina, kirjanpitotileinä tai asiakkaiden tai projektien tunnisteina.

SEQUENCE Google Sheetsissä: tyypillisiä käyttötarkoituksia ja erityispiirteitä

Google Sheetsissä funktio SEKVENSSI Se toimii lähes identtisesti Excelin kanssa. Se palauttaa myös taulukon, joka on peräisin lähdesolusta ja jolla on sama argumenttisyntaksi: rivit, sarakkeet, alku, lisäys.

Nopea esimerkki: Yksinkertainen esimerkki olisi:

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

joka luo viiden rivin sarakkeen, alkaen yhdestä ja kasvaen yhdellä. Se on täsmälleen sama idea kuin Excelissä, paitsi Google Sheets -käyttöliittymässä.

Sheetsin SEQUENCE-ominaisuuden suuri etu on, että sen avulla voit hallita dynaamiset numeeriset sekvenssit ja yhdistää ne helposti työkalun muihin funktioihin tai jo luomiisi taulukoihin esimerkiksi käyttämällä ARRAYFORMULA- tai ROW-funktioita.

Yksinkertainen numerointi vs. vetämällä ja pudottamalla tehtävät temput

On totta, että Google Sheetsissä voit numeroida nopeasti käyttämällä klassista kikkaa vedä täyttökahvaaEsimerkiksi kirjoitat 1 soluun A1, 2 soluun A2, valitset molemmat ja vedät alaspäin: taulukko tunnistaa kuvion ja jatkaa luvuilla 3, 4, 5…

Voit käyttää myös valikkoa Muokkaa → Täytä → Sarja Pidemmän listan luominen ilman, että sitä tarvitsee raahata puoli kilometriä. Siellä voit määrittää askeleen (1, 2, 10 jne.) ja lopullisen rajan.

Kaikki nuo menetelmät ovat kuitenkin staattinenKun lista on luotu ja lisäät rivejä, muutat rakennetta tai sinun on muokattava aluetta, sinun on tehtävä täyttö uudelleen. SEQUENCE-metodilla numerointi kuitenkin mukautuu automaattisesti, jos muutat taulukon rivien määrää tai säädät kaavaa.

ROW, ARRAYFORMULA ja Apps Script vaihtoehtoina

Google Sheetsissä on muita suosittuja numerointitapoja, varsinkin kun haluat jotain dynaamista, mutta et tarvitse SEQUENCE-funktion täyttä joustavuutta. Yksi tyypillinen vaihtoehto on käyttää funktiota RIVI() Niin:

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

Uudelleenlaskenta: Ajatuksena on, että liittämällä kaava ensimmäiseen soluun ja vetämällä se alas, jokainen rivi laskee oman järjestysnumeronsa. Jos lisäät uuden rivin päälle, uudelleenlaskenta pysyy yhdenmukaisena ja luvut pysyvät oikein säädettyinä.

Jos haluat mennä askeleen pidemmälle, voit yhdistää FILAn seuraaviin: ARRAYFORMULA täyttääksesi koko alueen kerralla käyttämällä yhtä kaavaa:

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

Numerointi 1–100: Tämä luo automaattisesti numeroinnin 1–100 valitsemaasi sarakkeeseen ilman vetämistä. Muuttamalla A100-arvoa voit säätää kokoa tarpeen mukaan.

Lopuksi, edistyneille käyttäjille Google Apps Script mahdollistaa sekvenssien luomisen mukautettu logiikkaExcelissä voit myös automatisoida tehtäviä makroiden ohjelmointi ExcelissäVoit esimerkiksi kirjoittaa pienen komentosarjan, joka täyttää vain parittomat luvut, laskee taaksepäin epäsäännöllisillä hyppyillä tai numeroi alueita tiettyjen ehtojen perusteella.

Yleisiä ongelmia: yhdistetyt solut ja ylivuotoiset taulukot

Yksi hämmentävimmistä asioista uusille Google Sheetsin käyttäjille on SEQUENCE-funktion käyttäminen yhdistetyt solutJos sinulla on esimerkiksi neljän yhdistetyn solun ryhmiä ja haluat, että jokainen lohko näyttää numeron lähtölaskennasta (1500, 1496, 1492 jne.), SEQUENCE yksinään ei toimi hyvin kyseisen rakenteen kanssa.

Dynaamiset taulukot, sekä Sheetsissä että Excelissä, tarvitsevat vierekkäinen tila ilman esteitä ylivuotoa vastaan. Jos laajennettavalla alueella on yhdistettyjä soluja, olemassa olevaa sisältöä tai rajoituksia, saat todennäköisesti virheitä tai et yksinkertaisesti näe odotettua tulosta.

Näissä tapauksissa voi olla tehokkaampaa luoda sarja ensin. puhtaalla, yhdistämättömällä alueellaja käytä sitten viittauksia, lisäkaavoja tai jopa pientä komentosarjaa lisätäksesi jokaisen arvon sinua kiinnostavaan yhdistettyjen solujen lohkoon.

Lisäksi nykyaikaisessa Excelissä virhe ilmenee yleensä, kun ylivuotoinen taulukko kohtaa estäviä tietoja. #YLIVUOTO!On suositeltavaa tarkistaa nämä viestit, puhdistaa alue tarvittaessa ja tarkistaa, ettei mikään ennestään oleva data häiritse SEQUENCE-tulosta.

Liittyvät funktiot ja hyödylliset yhdistelmät

Hyödyllisiä yhdistelmiä: SEQUENCE-funktiota käytetään harvoin yksinään tehokkaita ratkaisuja etsittäessä. Se yhdistetään yleensä muihin dynaamisiin taulukko- ja datanluontifunktioihin.

Excelissä, toiminnot, kuten PHAKU Excelissä ja muut tukevat sekvenssit toimivat usein erittäin hyvin yhdessä SEQUENCE-funktion kanssa:

• M.YKSIKKÖ (tai vastaava versiossasi), joka palauttaa identiteettimatriisi määrittämäsi koon, ykköset lävistäjällä ja nollat ​​muualla.
• RANDARRAY / MATRIZALEAT, joka kykenee tuottamaan satunnaislukumatriiseja, jotka voivat toimia simulaatioiden tai testien pohjana.
• SUODATTAA, jonka avulla voit valita vain tietyt ehdot täyttävät rivit tai sarakkeet SEQUENCE-funktiolla luodusta matriisista.
• LAJITTELU ja LAJITTELUJEN MUKAAN, järjestääksesi SEQUENCE-funktiolla tuotetun datan uudelleen, esimerkiksi jos haluat numeroinnin riippuvan toisen sarakkeen järjestyksestä.
• UnicosTästä on hyötyä, kun luot listoja ja haluat säilyttää vain ei-toistuvia arvoja.

Nämä toimintolohkot yhdessä käsitteen kanssa dynaamiset matriisit ja ylivuotokäyttäytyminenNiiden avulla voit rakentaa melko edistyneitä ratkaisuja turvautumatta makroihin tai monimutkaiseen ohjelmointiin.

Google Sheetsissä SEQUENCE- ja ARRAYFORMULA, SUODATIN, SORT, UNIQUE tai päivämääräfunktioita, kuten TÄNÄÄN, EOMONTHjne., avaa oven päivitettäville raporteille ja koontinäytöille, jotka luovat omat sarjansa saatavilla olevan datan perusteella.

Edut käsin tai perusnikseihin verrattuna numerointiin

Perinteisiin menetelmiin (käsin kirjoittaminen, täyttökahvan vetäminen tai Täyttö/Sarja-valikon käyttö) verrattuna SARJA-funktiolla on useita tärkeitä etuja arkielämässä:

Toisaalta se mahdollistaa erittäin pitkien luetteloiden (satoja tai tuhansia rivejä) luomisen ilman vierittämistä tai vetämistä. Määritä vain rivien ja sarakkeiden lukumäärä, ja matriisi tulee näkyviin välittömästi – erittäin kätevää työskenneltäessä suuria tietomääriä.

Toisaalta SEQUENCE-funktiolla luodut sekvenssit ovat helppo huoltaaJos sinun on myöhemmin laajennettava tai pienennettävä aluetta, voit yksinkertaisesti muuttaa rivi- tai sarakeargumenttia ilman, että sinun tarvitsee tehdä mitään manuaalisesti uudelleen. Lisäksi, koska ne ovat kaavoja, ne voivat olla riippuvaisia ​​muista soluista, jolloin voit luoda sarjoja, jotka kasvavat tai kutistuvat automaattisesti tiettyjen ehtojen perusteella.

Vältä inhimillisiä virheitä: Toinen mielenkiintoinen seikka on, että SEQUENCE välttää tyypillisiä inhimillisiä virheitä: numeron unohtamisen, vahingossa tapahtuvan ohittamisen tai sen tahattoman toistamisen. Delegoimalla tämän tehtävän funktiolle varmistat yhdenmukaisen ja jatkuvan numeroinnin sekä nousevassa että laskevassa järjestyksessä.

Lopuksi, koska funktiolla on kyky hallita aloitusta ja lisäystä, se mukautuu täydellisesti hyvin erilaisiin konteksteihin: rivien numerointi raporteissa mukaan lukien aikasarjojen luominen päivämääristä, teknisten koodien generointi tai rakenteiden kokoaminen datasimulaatioita varten.

Vähemmän aikaa: Kun alat käyttää sitä, huomaat, että tehtävät, jotka aiemmin kestivät useita minuutteja (tai jopa enemmän), supistuvat yhden, hyvin harkitun kaavan kirjoittamiseen.

Hyödyntämällä SEQUENCE-funktiota Excelissä ja Google Sheetsissä ja yhdistämällä sen päivämäärä-, teksti-, suodatus- tai dynaamisiin taulukkofunktioihin voit antaa laskentataulukon tehdä raskaan työn: numerointi, sarjojen luominen ja strukturoidun datan generointi Se tapahtuu sekunneissa, ja kaavoja on helppo muokata, eikä manuaalisten virheiden riskiä käytännössä ole.