Jos Excel-laskentataulukossasi on laskelmia, jotka perustuvat muuttuvaan solualueeseen, käytä SUMMA- ja OFFSET-funktioita yhdessä SUMMASIIRTO-kaavassa yksinkertaistaaksesi laskelmien pitämistä ajan tasalla.
Tämän artikkelin ohjeet koskevat Excel for Microsoft 365-, Excel 2019-, Excel 2016-, Excel 2013- ja Excel 2010 -versioita.
Luo dynaaminen alue SUM- ja OFFSET-funktioilla
Jos käytät laskelmia jatkuvasti muuttuvalle ajanjaksolle - kuten määrität myyntiä kuukaudelle - käytä Excelin OFFSET-toimintoa määrittääksesi dynaamisen alueen, joka muuttuu, kun kunkin päivän myyntiluvut lisätään.
SUMMA-funktio itsessään voi yleensä mukauttaa uusien tietosolujen lisäämisen summattavalle alueelle. Yksi poikkeus tapahtuu, kun tiedot lisätään soluun, jossa funktio tällä hetkellä sijaitsee.
Alla olevassa esimerkissä kunkin päivän uudet myyntiluvut lisätään luettelon alaosaan, mikä pakottaa kokonaismäärän siirtymään jatkuvasti yhden solun alaspäin aina, kun uusia tietoja lisätään.
Jatka tätä opetusohjelmaa avaamalla tyhjä Excel-laskentataulukko ja syöttämällä mallitiedot. Työtaulukkoasi ei tarvitse muotoilla kuten esimerkissä, mutta muista kirjoittaa tiedot samoihin soluihin.
Jos tietojen summaamiseen käytetään vain SUMMA-funktiota, funktion argumenttina käytettyä solualuetta on muutettava aina, kun uusia tietoja lisätään.
Käyttämällä SUM- ja OFFSET-funktioita yhdessä summattava alue muuttuu dynaamiseksi ja muuttuu uusien tietosolujen mukaiseksi. Tietojen uusien solujen lisääminen ei aiheuta ongelmia, koska alue muuttuu jatkuvasti, kun uusia soluja lisätään.
Syntaksi ja argumentit
Tässä kaavassa SUMMA-funktiota käytetään laskemaan yhteen argumenttina toimitetut tiedot. Tämän alueen aloituspiste on staattinen ja se tunnistetaan soluviittaukseksi ensimmäiseen numeroon, joka lasketaan yhteen kaavan avulla.
OFFSET-funktio on sisäkkäinen SUMMA-funktion sisällä ja luo dynaamisen päätepisteen kaavan summaamalle tietoalueelle. Tämä saavutetaan asettamalla alueen päätepiste yhdeksi soluksi kaavan sijainnin yläpuolelle.
Kaavan syntaksi on:
=SUMMA(alueen alku: SIIRTO(viite, rivit, sarakkeet))
Argumentit ovat:
- Alueen alku: SUMMA-funktion summaaman solualueen aloituspiste. Tässä esimerkissä aloituspiste on solu B2.
- Viite: Vaadittu soluviittaus, jota käytetään alueen päätepisteen laskemiseen. Esimerkissä Reference-argumentti on kaavan soluviittaus, koska alue päättyy yhden solun kaavan yläpuolelle.
- Rivit: Poikkeaman laskennassa käytetyn viiteargumentin ylä- tai alapuolella olevien rivien määrä vaaditaan. Tämä arvo voi olla positiivinen, negatiivinen tai nolla. Jos offset-sijainti on Reference-argumentin yläpuolella, arvo on negatiivinen. Jos offset on pienempi, Rivit-argumentti on positiivinen. Jos offset sijaitsee samalla rivillä, argumentti on nolla. Tässä esimerkissä offset alkaa yhden rivin Reference-argumentin yläpuolella, joten argumentin arvo on negatiivinen yksi (-1).
- Sarakkeet: Viiteargumentin vasemmalla tai oikealla puolella olevien sarakkeiden määrä, jota käytetään siirtymän laskemiseen. Tämä arvo voi olla positiivinen, negatiivinen tai nolla. Jos siirtymäpaikka on Reference-argumentin vasemmalla puolella, tämä arvo on negatiivinen. Jos offset on oikea, Cols-argumentti on positiivinen. Tässä esimerkissä summattava data on samassa sarakkeessa kuin kaava, joten tämän argumentin arvo on nolla.
Käytä SUM OFFSET -kaavaa kokonaismyyntitietojen saamiseksi
Tässä esimerkissä käytetään SUM OFFSET -kaavaa palauttamaan laskentataulukon sarakkeessa B lueteltujen päivittäisten myyntilukujen summa. Aluksi kaava syötettiin soluun B6 ja siinä laskettiin yhteen neljän päivän myyntitiedot.
Seuraava vaihe on siirtää SUM OFFSET -kaavaa yhden rivin alaspäin, jotta viidennen päivän myyntisummalle tulee tilaa. Tämä saavutetaan lisäämällä uusi rivi 6, joka siirtää kaavan riville 7.
Siirron seurauksena Excel päivittää automaattisesti Reference-argumentin soluun B7 ja lisää solun B6 kaavan summaamaan alueeseen.
- Valitse solu B6, joka on paikka, jossa kaavan tulokset näkyvät aluksi.
-
Valitse nauhan Kaavat-välilehti.
Image -
Valitse Math & Trig.
Image -
Valitse SUM.
Image - Aseta osoitin Function Arguments-valintaikkunassa Number1-tekstiruutuun.
-
Syötä tämä soluviittaus valintaikkunaan valitsemalla laskentataulukosta solu B2. Tämä sijainti on kaavan staattinen päätepiste.
Image - Aseta osoitin Function Arguments-valintaikkunassa Number2-tekstiruutuun.
-
Syötä OFFSET(B6, -1, 0). Tämä OFFSET-funktio muodostaa kaavan dynaamisen päätepisteen.
Image -
Valitse OK suorittaaksesi toiminnon loppuun ja sulkeaksesi valintaikkunan. Kokonaissumma näkyy solussa B6.
Image
Lisää seuraavan päivän myyntitiedot
Seuraavan päivän myyntitietojen lisääminen:
- Napsauta rivin 6 otsikkoa hiiren kakkospainikkeella.
-
Valitse Insert lisätäksesi uuden rivin laskentataulukkoon. SUM OFFSET -kaava siirtyy yhden rivin alaspäin soluun B7 ja rivi 6 on nyt tyhjä.
Image - Valitse solu A6 ja kirjoita numero 5 sen osoituksena, että viidennen päivän myyntisummat syötetään.
-
Valitse solu B6, kirjoita $1458.25 ja paina sitten Enter.
Image - Cell B7 päivityksiä uuteen kokonaishintaan 7137,40 $.
Kun valitset solun B7, päivitetty kaava tulee näkyviin kaavapalkkiin.
=SUMMA(B2:SIIRTO(B7, -1, 0))
OFFSET-funktiolla on kaksi valinnaista argumenttia: Height ja Width, joita ei käytetty tässä esimerkissä. Nämä argumentit kertovat OFFSET-funktiolle tulosteen muodon rivien ja sarakkeiden lukumääränä.
Jos nämä argumentit jätetään pois, funktio käyttää sen sijaan Reference-argumentin korkeutta ja leveyttä, joka tässä esimerkissä on yhden rivin korkea ja yhden sarakkeen leveä.
