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 manuaalisesti lukuja 1, 2, 3… laskentataulukkoon , tiedät, että se on yksi maailman työläimmistä tehtävistä. Ja kun rivien määrä ei ole vain 10, vaan 500 tai 1000, työ muuttuu työläästä sietämättömäksi. Onneksi Excelissä ja Google Sheetsissä on juuri tätä varten suunniteltu funktio: SEQUENCE , joka pystyy luomaan numeroluetteloita irtonumeroilla yhdellä kaavalla.

Vaikka se saattaa kuulostaa hieman tekniseltä, SEKVENSI-funktio on paljon yksinkertaisempi kuin miltä se näyttää. Muutamalla hyvin määritellyllä argumentilla voit luoda täydellisiä taulukoita järjestetyistä luvuista, kasvavista tai laskevista sarjoista , linkitetyistä päivämääristä, kirjanpitokoodeista ja jopa esimerkkitiedoista raporttejasi varten. Katsotaanpa tarkemmin, miten se toimii Excelissä ja Google Sheetsissä, ja miten saat siitä kaiken irti reaalimaailman esimerkkien ja hyödyllisten vinkkien avulla.

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

SEKVENS-funktiota käytetään luomaan peräkkäisten numeroiden matriisi , joka "täyttää" laskentataulukon ja täyttää useita rivejä ja sarakkeita. Toisin sanoen, kun syötät kaavan soluun, se täyttää automaattisesti koko lohkon peräkkäisillä numeroilla.

Sen toiminta on hyvin samanlaista Excelissä ja Google Sheetsissä: molemmissa tapauksissa kaavan syöttäminen antaa sinulle dynaamisen taulukon . Tämä tarkoittaa, että tulos ei ole yksittäinen arvo, vaan joukko soluja, jotka täytetään automaattisesti alkaen ensimmäisestä solusta.

Kuvittele, että tarvitset taulukon, joka sisältää numeroita 1–20 neljällä rivillä ja viidellä sarakkeella. Sen sijaan, että täyttäisit sen numero numerolta, käyttäisit kaavaa =SEKVENSI(4;5) (alueellisissa asetuksissa käytetään puolipistettä) tai =SEKVENSI(4,5) (alueellisissa asetuksissa käytetään pilkkua), ja siinä kaikki: sinulla on 4x5-lohko, jossa on numeroita 1–20.

Tämä funktio on erityisen hyödyllinen, kun haluat luoda hallittuja numeerisia sarjoja : laskea ylös- tai alaspäin, hypätä kymmenillä, luoda tunnisteita, simuloida tietoja tai valmistella rakenteita raportteja, koontinäyttöjä tai pivot-taulukoita varten Excelissä.

SEQUENCE-syntaksi Excelissä ja Google Sheetsissä

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

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

Jokaisella argumentilla on tietty rooli, ja on tärkeää ymmärtää se hyvin, jotta sen funktio 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.

Aluekohtaisista asetuksistasi riippuen funktio voi hyväksyä joko pilkun tai puolipisteen argumenttien erottimena. Espanjassa puolipiste on Excelin ja Google Sheetsin standardi: =SEQUENCE(4;5) . Jos kuitenkin liität esimerkin, jossa on pilkkuja, ja se palauttaa virheen, korvaa ne puolipisteillä.

Olennaista on ymmärtää, että rivit ja sarakkeet määrittävät taulukon koon , kun taas aloitus- ja lisäys-operaattorit ohjaavat ensimmäisen luvun arvoa ja askeleen kokoa luvun ja seuraavan välillä.

Esimerkkejä SEQUENCE-ominaisuuden käytöstä

Klassisia esimerkkejä siitä, miten ylivuotoinen matriisi "maalataan" arkille.

Esimerkki 1: SEQUENCE, jossa on vain rivejä

Esimerkki solussa A1: Jos kirjoitat soluun A1 seuraavan:

Nopea kaava: =SECUENCIA(2)

Kaava palauttaa pystysuuntaisen taulukon, jossa on kaksi riviä ja yksi sarake , ja joka täytetään seuraavasti:

	A	B
1	1
2	2

Huomaa, että jos sarakkeita ei määritetä, funktio palauttaa yhden sarakkeen arvoilla 1 ja 2. Tätä toimintaa käytetään usein yksinkertaisten pystysuuntaisten listojen luomiseen, jotka on numeroitu 1:stä n:ään.

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

A1-solussa: Jos nyt kirjoitat A1-soluun:

Matriisikaava: =SECUENCIA(2;3)

Funktio luo matriisin, jossa on kaksi riviä ja kolme saraketta . Väli A1:C2 näyttää tältä:

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

Prosessi etenee rivi riviltä vasemmalta oikealle : ensin A1, sitten B1, C1, sitten A2, B2, C2 jne. Tämä täyttää automaattisesti numerot 1–6 ilman manuaalista 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 kohdasta A1 eteenpäin on:

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

Sarja alkaa luvusta 3 ja kasvaa kahdella : 3, 5, 7, 9, 11, 13… jälleen, täyttäen ensin ylimmän rivin ja sitten alimman rivin.

Esimerkki 4: laskeva järjestys

Negatiivinen lisäys: 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 numerojärjestys: Luo taulukon, jossa numerot laskevat alkaen 10:stä:

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

Tämän kikan avulla voit asettaa hallittuja lähtölaskuja vain muutamalla vaiheella 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 sarakkeet-argumentin pois, SEQUENCE palauttaa pystysuuntaisen luettelon . Eli sinulla on yksi sarake, jossa on niin monta riviä kuin ensimmäisessä parametrissa määrität. Tämä on yleisintä, kun haluat numeroluettelon yhdessä sarakkeessa.

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

Lyhyt esimerkki: Esimerkiksi rivin luomiseksi numeroilla 1–10, käyttäisit seuraavaa:

Vaakasuora kaava: =SECUENCIA(1;10)

Ja jos sinulla on jo pystysuora sekvenssi, voit muuntaa sen vaakasuoraksi käyttämällä esimerkiksi Excelissä TRANSPOSE()- funktiota kyseiseen matriisiin: tällä tavoin siirtyisit yhden sarakkeen luettelosta riviin, jolla on samat arvot.

Tämäntyyppinen yhdistelmä on erittäin käytännöllinen, kun valmistelet taulukon otsikoita, peräkkäisiä kuukausia tai vaakasuuntaista numerointia 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 varsin mielenkiintoisia SEQUENCE-funktion käyttötarkoituksia yhdistettynä muihin funktioihin, jotka on suunniteltu dynaamisen datan nopeaan rakentamiseen.

Klassinen esimerkki sisältää neljän rivin ja viiden sarakkeen matriisin luomisen yksinkertaisesti seuraavasti:

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ää TEXT-, DATE-, YEAR-, TODAY- ja SEQUENCE-arvot tällä tavalla:

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

Tämä kaava tekee seuraavaa: se ottaa kuluvan vuoden käyttämällä YEAR(TODAY()) , luo kuukausien numerot 1-6 (tammikuusta kesäkuuhun) käyttämällä SEQUENCE(1;6 ), luo päivämäärät käyttämällä DATE(year; month; day) ja lopuksi näyttää ne lyhennettyinä TEXT(…;"mmm") . Tuloksena on rivi, joka sisältää vuoden kuusi ensimmäistä kuukautta, aina päivitettynä kuluvaan vuoteen.

Luo kasvava joukko satunnaisia ​​kokonaislukuja

Toinen virallinen esimerkki yhdistää SEQUENCE-funktiot INTEGER- ja RANDOM-funktioiden kanssa saadakseen kasvavien satunnaislukujen taulukon, joka on hyödyllinen testausdatan 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 5x6- matriisi kokonaisluvuista, 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 luotava viitekoodeja , SEQUENCE voi säästää paljon aikaasi. Esimerkiksi:

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

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

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

Google Sheetsissä SEQUENCE -funktio toimii lähes identtisesti Excel-vastineensa kanssa. Se palauttaa myös taulukon, joka jatkuu lähdesolusta ja jolla on sama argumenttisyntaksi: rivit, sarakkeet, alku, lisäys.

Lyhyt 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-funktion suuri etu on, että sen avulla voit hallita numeerisia sarjoja dynaamisesti ja yhdistää ne helposti työkalun muihin toimintoihin tai jo luomiisi taulukoihin esimerkiksi ARRAYFORMULA- tai ROW-funktioilla.

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

On totta, että Google Sheetsissä voit numeroida soluja nopeasti klassisella tempulla vetämällä täyttökahvasta . Esimerkiksi kirjoitat 1 soluun A1, 2 soluun A2, valitset molemmat ja vedät alaspäin: taulukko tunnistaa kuvion ja jatkaa täyttökahvasta 3, 4, 5…

Voit myös käyttää Muokkaa → Täytä → Sarja -valikkoa luodaksesi pidemmän listan ilman, että sinun tarvitsee vetää sitä ikuisuuden verran. Siellä voit määrittää askeleen (1, 2, 10 jne.) ja lopullisen rajan.

Kaikki nämä metodit ovat kuitenkin staattisia : kun lista on luotu ja lisäät rivejä, muutat rakennetta tai sinun on muokattava aluetta, sinun on tehtävä täyttö uudelleen. Toisaalta SEQUENCE-metodilla numerointi mukautuu automaattisesti, jos muutat rivien määrää taulukossa 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ää ROW() -funktiota seuraavasti:

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

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

Jos haluat mennä askeleen pidemmälle, voit yhdistää ROW-funktion ARRAYFORMULA-funktion kanssa 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 numerot 1–100 valitsemaasi sarakkeeseen ilman vetämistä. Muuttamalla A100-arvoa voit säätää kokoa tarpeen mukaan.

Lopuksi, edistyneille käyttäjille Google Apps Scriptin avulla voit luoda sekvenssejä mukautetulla logiikalla . Excelissä voit myös automatisoida tehtäviä ohjelmoimalla makroita . Voit esimerkiksi kirjoittaa lyhyen komentosarjan, joka täyttää vain parittomat luvut, laskee taaksepäin epäsäännöllisillä ohituksilla tai numeroi alueita tiettyjen ehtojen perusteella.

Yleisiä ongelmia: yhdistetyt solut ja ylivuotoiset taulukot

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

Sekä Sheetsissä että Excelissä dynaamiset taulukot tarvitsevat esteetöntä yhtenäistä tilaa ylivuotoa varten. Jos alue, johon ne yrittävät laajentaa, sisältää yhdistettyjä soluja, olemassa olevaa sisältöä tai rajoituksia, todennäköisesti kohtaat virheitä tai et yksinkertaisesti näe odotettua tulosta.

Näissä tapauksissa voi olla tehokkaampaa luoda ensin sarja puhtaalle, yhdistämättömälle alueelle ja sitten käyttää viittauksia, lisäkaavoja tai jopa pientä komentosarjaa kunkin arvon lisäämiseksi kiinnostavaan yhdistettyyn solulohkoon.

Lisäksi nykyaikaisessa Excelissä näytetään yleensä virhe #OVERFLOW!, kun ylivuotoinen taulukko kohtaa estäviä tietoja . On suositeltavaa tarkistaa nämä viestit, tyhjentää alue tarvittaessa ja varmistaa, ettei mikään ennestään oleva data häiritse SEQUENCE-funktion 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ä funktiot, kuten PHAKU ja muut tukifunktiot, toimivat usein erittäin hyvin yhdessä SARJA-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ä funktiolohkot yhdessä dynaamisten taulukoiden ja ylivuotokäyttäytymisen kanssa mahdollistavat melko edistyneiden ratkaisujen rakentamisen ilman makroja tai monimutkaista ohjelmointia.

Google Sheetsissä SEQUENCE-funktion yhdistäminen ARRAYFORMULA- , FILTER- , SORT- , UNIQUE- tai päivämääräfunktioiden, kuten TODAY ja EOMONTH , kanssa 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

Verrattuna perinteisiin menetelmiin (käsin kirjoittaminen, täyttökahvan vetäminen tai Täytä/Sarja-valikon käyttö), SARJA-funktiolla on useita tärkeitä etuja jokapäiväisessä käytössä :

Toisaalta sen avulla voit luoda erittäin pitkiä luetteloita (satoja tai tuhansia rivejä) ilman vierittämistä tai vetämistä. Määritä vain rivien ja sarakkeiden määrä, ja matriisi tulee näkyviin välittömästi – erittäin kätevää työskenneltäessä suurten tietomäärien kanssa.

Toisaalta SEQUENCE-funktiolla luotuja sarjoja on helppo ylläpitää . Jos 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: raporttien rivien numeroinnista päivämääräsarjojen luomiseen, teknisten koodien generointiin tai datasimulaatioiden rakenteiden määrittämiseen.

Vähemmän aikaa: Heti 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ä Excelin ja Google Sheetsin SEQUENCE-funktiota ja yhdistämällä sen päivämäärä-, teksti-, suodatus- tai dynaamisiin taulukkofunktioihin, taulukkolaskentaohjelma tekee raskaan työn: numerointi, sarjojen luominen ja strukturoidun datan tuottaminen hoituu sekunneissa, ja kaavoja on helppo muokata, eikä manuaalisten virheiden riskiä ole käytännössä lainkaan.