Blogi

Dynaamiset alueet Excelissä: milloin kannattaa käyttää OFFSET-funktiota tai taulukkoa

Päivitä summat ja avattavien luetteloiden vaihtoehdot rivejä lisättäessä. Katso, miten OFFSET ja COUNTA toimivat, mitä tyhjä tunnus muuttaa ja miten Excel-taulukkoa käytetään.

Korostettu viittausalue laajenee alaspäin, kun taulukkoon lisätään rivi.

Lisäät tuotteen Excel-luetteloon, mutta summa ja avattavan luettelon vaihtoehdot pysyvät ennallaan. Jos viittaus on yhä A2:A10, uusi 11. rivi jää ulkopuolelle. Dynaamisella alueella Excel määrittää luettelon lopun, jolloin kaavoja ei tarvitse päivittää käsin.

Korkeuden ja leveyden laskeminen OFFSET-funktiolla

Jos tietueet ovat peräkkäin, laske otsikot ja tuotetunnukset OFFSET-funktion korkeutta ja leveyttä varten. Tee esimerkki Sheet1-välilehdelle ja syötä summakaava toiselle välilehdelle. Kirjoita japaninkieliset otsikot täsmälleen esitetyllä tavalla, jotta myöhemmät rakenteiset viittaukset toimivat.

Solu Sarake A Sarake B Sarake C
Rivi 1 商品ID (tuotetunnus) 商品名 (tuotteen nimi) 数量 (määrä)
Rivi 2 A01 Kynä 4
Rivi 3 A02 Muistivihko 6
=SUM(OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A$1:$A$1000),COUNTA(Sheet1!$A$1:$Z$1)))

Aloita solusta A1 ilman siirtymää. Sarakkeen A tietojen määrä antaa korkeuden ja rivin 1 otsikoiden määrä leveyden. Esimerkki palauttaa kolme riviä ja saraketta kattavan alueen A1:C3 ja summan 10. Lisää riville 4 A03, Viivain ja 2. Alue laajenee muotoon A1:C4 ja summa on 12. Tunnukset ovat tekstiä, eikä laskenta-alueilla ole muita tietoja. Microsoftin OFFSET-ohje

Tyhjennä vain tunnus solusta A3. Sarakkeessa A lasketaan kolme solua: otsikko A1 ja tunnukset A2 ja A4. OFFSET palauttaa siis kolme ensimmäistä riviä, A1:C3. Rivin 3 määrä 6 kuuluu yhä summaan, mutta rivin 4 määrä 2 jää pois. Summa palaa arvoon 10.

A3 on tyhjä. OFFSET-alue A1:C3 sisältää rivin 3 arvon 6 mutta ei rivin 4 arvoa 2. Summa on 10.
Vihreä kehys näyttää alueen A1:C3, jonka OFFSET palauttaa Excelin Windows-versiossa.

Tyhjä otsikko keskellä voi vastaavasti jättää viimeiset sarakkeet alueen ulkopuolelle. COUNTA laskee solut, joiden kaava palauttaa tyhjän merkkijonon (""). Pidä otsikot ja tunnukset peräkkäin olemassa olevassa työkirjassa ja tarkista laskenta-alueen rajat sekä tyhjät solut. Tyhjä määrä ei muuta korkeutta, jos tunnus on täytetty. Microsoftin COUNTA-ohje

Kun otsikot alkavat toiselta riviltä

Jos ensimmäisellä rivillä on raportin otsikko, aloita luettelo solusta A2. Laske korkeus ja leveys toiselta riviltä, jotta raportin otsikko jää pois. Kun esimerkki siirretään rivin verran alas, otsikot ja kaksi tuotetta ovat alueella A2:C4 ja summa on edelleen 10.

=SUM(OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$2:$A$1000),COUNTA(Sheet1!$A$2:$Z$2)))

Uusien rivien mukana laajenevan taulukon käyttäminen

Valitse uudesta luettelosta tiedot otsikoineen ja valitse Lisää → Taulukko. Anna taulukolle nimeksi Items. Seuraava kaava laskee määräsarakkeen summan taulukon ulkopuolisesta solusta. Summa-alue laajenee, kun taulukkoon lisätään rivejä. Microsoftin ohje rakenteisista viittauksista

=SUM(Items[数量])

Summaa vain rivit, joilla on tuotetunnus

Kun muutat alueen A1:C4 taulukoksi Items, yllä oleva kaava laskee 4 + 6 + 2 ja palauttaa 12, vaikka A3 olisi tyhjä. Laske vain tunnukselliset rivit käyttämällä SUMIF-funktiota ja ehtoa, ettei tunnussolu ole tyhjä. Tässä esimerkissä rivien 2 ja 4 summa on 6 (4 + 2). Microsoftin SUMIF-ohje

=SUMIF(Items[商品ID],"<>",Items[数量])

Kytke avattava luettelo taulukkoon

Jos haluat valita tuotteen nimen toisella välilehdellä, avaa Excelin työpöytäsovelluksessa Kaavat → Nimien hallinta. Luo koko työkirjan kattava nimi ItemChoices ja määritä sen viittaukseksi =Items[商品名]. Valitse syöttösolun tietojen kelpoisuusehdoksi luettelo ja lähteeksi =ItemChoices. Otsikko jää pois, ja taulukon loppuun lisätyt rivit tuovat uusia vaihtoehtoja. Täytä jokaiselle uudelle riville nimi, jotta vaihtoehtoihin ei jää tyhjiä kohtia. Microsoftin ohje avattavan luettelon luomiseen