Excelin SUMMA- ja OFFSET-kaava

Jos Excel-laskentataulukko sisältää 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 -sovelluksia.

Luo dynaaminen alue SUM- ja OFFSET-toiminnoilla

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 myyntinumerot lisätään. SUM-funktio itsessään pystyy yleensä sovittamaan uusien tietosolujen lisäämisen summattavalle alueelle. 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ä edellyttää, että kokonaissumma siirtyy yhden solun alaspäin aina, kun uusia tietoja lisätään. Noudata tätä opetusohjelmaa avaamalla tyhjä Excel-laskentataulukko ja syöttämällä mallitiedot. Laskentataulukkoasi ei tarvitse muotoilla kuten esimerkissä, mutta varmista, että syötät tiedot samoihin soluihin.

Jos tietojen summaamiseen käytetään vain SUMMA-funktiota, funktion argumenttina käytettyä solualuetta on muutettava aina, kun uutta tietoa lisätään. SUM- ja OFFSET-funktioiden käyttäminen yhdessä tekee kokonaisalueesta dynaamisen ja muuttuu uusien tietosolujen mukaiseksi. Uusien solujen lisääminen datalla ei aiheuta ongelmia, koska alue mukautuu jatkuvasti jokaisen uuden solun myötä.

Syntaksi ja argumentit

Tämä kaava käyttää SUM-funktiota summaamaan argumentiksi määritetyn tietoalueen. Tämän alueen aloituspiste on staattinen ja se tunnistetaan soluviittaukseksi ensimmäiseen kaavan lisäämään numeroon. OFFSET-funktio on sisäkkäinen SUMMA-funktion sisällä ja luo dynaamisen päätepisteen data-alueelle, jonka kaava summaa. Tämä saadaan aikaan asettamalla alueen loppupisteeksi yksi solu kaavan sijainnin yläpuolella. Kaavan syntaksi on: =SUM(alueen alku:OFFSET(viite, rivit, sarakkeet))

Argumentit ovat:

• Alueen aloitus: SUM-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: Poikkeamaa laskettaessa käytettävä viiteargumentin ylä- tai alapuolella olevien rivien määrä vaaditaan. Tämä arvo voidaan asettaa positiiviseksi, negatiiviseksi tai nollaksi. Jos siirtymäpaikka on Reference-argumentin yläpuolella, arvo on negatiivinen. Jos offset on pienempi, rivit-argumentti on positiivinen. Jos offset on samalla rivillä, argumentti on nolla. Tässä esimerkissä offset alkaa yhden rivin viiteargumentin yläpuolella, joten argumentin arvo on yksi negatiivinen (-1).
• Cols: Poikkeaman laskemiseen käytetyn viiteargumentin vasemmalla tai oikealla puolella olevien sarakkeiden määrä. Tämä arvo voidaan asettaa positiiviseksi, negatiiviseksi tai nollaksi. Jos siirtymäpaikka on viiteargumentin vasemmalla puolella, tämä arvo on negatiivinen. Jos siirtymä on oikealla, Cols-argumentti on positiivinen. Tässä esimerkissä lisättävä tieto 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 myyntitiedot laskettiin yhteen neljän päivän aikana. Seuraava vaihe on siirtää SUM OFFSET -kaavaa yhden rivin alaspäin, jotta viidennen päivän myynnin kokonaismäärälle tulee tilaa. Tämä saadaan aikaan lisäämällä uusi rivi 6, joka siirtää kaavan riville 7. Siirron seurauksena Excel päivittää automaattisesti viiteargumentin soluun B7 ja lisää solun B6 kaavan summaamaan alueeseen.

1. Valitse solu B6paikka, jossa kaavan tulokset alun perin näytetään.

2. Valitse kaavat nauha välilehti.

   

3. Valita Matematiikka & Trig†

   

4. Valitse SUMMA†

   

5. Vuonna Funktioargumentit -valintaikkunassa, aseta kohdistin kohtaan Numero 1 tekstilaatikko.

6. Valitse solu laskentataulukosta B2 syöttääksesi tämän soluviittauksen valintaikkunaan. Tämä sijainti on kaavan staattinen päätepiste.

   

7. Vuonna Funktioargumentit -valintaikkunassa, aseta kohdistin kohtaan Numero 2 tekstilaatikko.

8. Tulla sisään SIIRTYMÄ(B6;-1;0)† Tämä OFFSET-funktio on kaavan dynaaminen päätepiste.

   

9. Valitse Okei suorittaaksesi toiminnon loppuun ja sulkeaksesi valintaikkunan. Kokonaissumma näkyy solussa B6.

   

Lisää seuraavan päivän myyntitiedot

Lisää seuraavan päivän myyntitiedot seuraavasti:

1. Napsauta hiiren kakkospainikkeella rivin 6 otsikkoa.

2. Valitse Lisää lisätäksesi uuden rivin laskentataulukkoon. Kaava SUMMASIIRTYMÄ siirtyy yhden rivin alas soluun B7 ja rivi 6 on nyt tyhjä.

   

3. Valitse solu A6 ja syötä numero 5 osoittamaan, että viidennen päivän myynnin kokonaismäärä on syötetty.

4. Valitse solu B6tulla sisään 1458,25 dollariaja paina sitten Tulla sisään†

   

5. Solu B7 päivitetään uuteen kokonaishintaan 7137,40 dollaria.

Kun valitset solun B7, päivitetty kaava tulee näkyviin kaavapalkkiin. =SUMMA(B2:SIIRTO(B7;-1;0))

OFFSET-funktiolla on kaksi valinnaista argumenttia: Korkeus ja Leveys† joita ei käytetä tässä esimerkissä. Nämä argumentit kertovat OFFSET-funktiolle tulosteen muodon rivien ja sarakkeiden lukumääränä. Jättämällä nämä argumentit pois, funktio käyttää sen sijaan Reference-argumentin korkeutta ja leveyttä, joka tässä esimerkissä on yhden rivin korkea ja yhden sarakkeen leveys.