Excel SUM ja OFFSET kaava

Jos Excel-laskentataulukossa on laskelmia, jotka perustuvat muuttuviin solualueisiin, SUM- ja OFFSET-toiminnot SUM OFFSET -kaavion avulla yksinkertaistavat laskutoimitusten ajantasaistamista.

Luo dynaaminen alue SUM- ja OFFSET-toiminnoilla

Jos käytät jatkuvasti muuttuvia ajanjaksoja - esimerkiksi kuukauden myyntiä - OFFSET-funktiota Excelissä, voit määrittää dynaamisen alueen, joka muuttuu päivittäisten myyntilukujen lisäämiseksi.

Itse itsessään SUM-toiminto voi tavallisesti sijoittaa uusien datasolujen lisäämisen summautuvalle alueelle.

Yksi poikkeus syntyy, kun tieto asetetaan soluun, jossa toiminto sijaitsee tällä hetkellä.

Yllä olevassa esimerkkikuvauksessa lisätään jokaisen päivän uudet myyntiluvut listan pohjaan, pakottamalla kokonaissumma jatkuvasti siirtymään alas yhdelle solulle aina kun uutta tietoa lisätään.

Jos SUM-funktiota käytettiin yksinään tietojen kokoamiseksi, olisi välttämätöntä muokata funktion argumentiksi käytettyä solualuetta aina, kun uusia tietoja lisättiin.

Käyttämällä SUM- ja OFFSET-toimintoja yhdessä kuitenkin kokonaisalue muuttuu dynaamiselta. Toisin sanoen se muuttuu uusien datasolujen mukauttamiseksi. Uusien solujen lisääminen ei aiheuta ongelmia, koska alue jatkaa säätämistä kun jokainen uusi solu lisätään.

Syntaksi ja argumentit

Katso edellä kuvattu kuva edellä tämän opetusohjelman kanssa.

Tässä kaavassa SUM-funktiota käytetään osoittamaan argumenttina toimitetun datamäärän koko. Tämän alueen alkupiste on staattinen ja se identifioidaan solun viittaukseksi ensimmäiseen numeroon, jonka kaava on yhteensä.

OFFSET-funktio on upotettu SUM-toiminnon sisäpuolelle ja sitä käytetään dynaamisen päätepisteen muodostamiseen kaavalle asetetulle datavälille. Tämä toteutetaan asettamalla alueen loppupisteen yhteen soluun kaavan sijainnin yläpuolelle.

Kaavan syntaksi:

= SUM (Alue Aloitus: OFFSET (Viite, Rivit, Cols))

• Alue Aloita: Lähtökohtana solualueelle, joka tulee olemaan SUM-toiminnolla, tarvitaan. Esimerkkikuvaan tämä on solu B2.
• Viite: Tarvittava solun viite, jota käytetään alueen loppupisteen laskemiseen, sijaitsee useita rivejä ja sarakkeita poissa. Esimerkkikuvaan referenssi-argumentti on itse kaavan soluviite, koska haluat aina, että alue lopettaa yhden solun kaavan yläpuolella.
• riviä: Siirtymän laskemisessa käytettävän viite-argumentin ylä- tai alapuolella olevia rivejä tarvitaan. Tämä arvo voi olla positiivinen, negatiivinen tai asetettu nollaksi. Jos offsetin sijainti on yläpuolella Viite argumentti, arvo on negatiivinen. Jos se on alla, Rivi-argumentti on positiivinen. Jos offset sijaitsee samassa rivissä, argumentti on nolla. Tässä esimerkissä offset alkaa yhden rivin Viite-argumentin yläpuolella, joten tämän argumentin arvo on negatiivinen (-1).
• työtoverit: Siirtymän laskemisessa käytettävän viite-argumentin vasemmalle tai oikealle sarakkeiden lukumäärä vaaditaan. Tämä arvo voi olla positiivinen, negatiivinen tai asetettu nollaksi. Jos offsetin sijainti on viite-argumentin vasemmalla puolella, tämä arvo on negatiivinen. Jos oikealle Colsin argumentti on myönteinen. Tässä esimerkissä summaava tieto on samassa sarakkeessa kuin kaava, joten tämän argumentin arvo on nolla.

SUM OFFSET -kaavion käyttäminen kokonaismyyntiarvoihin

Tässä esimerkissä käytetään SUM OFFSET -kaavaa, joka palauttaa laskentataulukon sarakkeessa B lueteltujen päivittäisten myyntilukujen kokonaismäärän.

Alun perin kaava syötettiin soluun B6, ja myynti oli yhteensä neljä päivää.

Seuraava askel on siirtää SUM OFFSET-kaava alas riviin, jotta tilaa voidaan käyttää viidennen päivän myynnistä. Tämä toteutetaan lisäämällä uusi rivi 6, joka siirtää kaavan alas riviin 7.

Siirron seurauksena Excel päivittää Viite-argumentin automaattisesti soluunB7 ja lisää solunB6 kaavalle summattuun alueeseen.

SUM OFFSET -kaavan syöttäminen

1. Napsauta solua B6, joka on paikka, jossa kaavan tulokset näytetään alunperin.
2. Klikkaakaavat nauhavalikon välilehti.
3. ValitaMath & Trigavattavasta toiminnosta avattavasta luettelosta.
4. KlikkaaSUMMA luettelossa, jolloin toiminnon valintaikkuna avautuu.
5. Napsauta valintaikkunassa painikettaNumero 1linja.
6. Napsauta solua B2 syöttää tämä soluviittaus valintaikkunaan. Tämä sijainti on kaavan staattinen päätepiste.
7. Napsauta valintaikkunassa painikettaNumero 2linja.
8. Syötä seuraava OFFSET-toiminto: OFFSET (B6, -1,0) muodostaa kaavan dynaaminen päätepiste.
9. Klikkaus kunnossa Lopeta toiminto ja sulje valintaikkuna.

Kokonaismäärä näkyy solussa B7.

Seuraavan päivän myynnin tiedot lisätään

Voit lisätä seuraavan päivän myyntitiedot:

1. Avaa kontekstivalikko hiiren kakkospainikkeella rivi rivin otsikkoa varten.
2. Napsauta valikossaInsert lisätä uusi rivi laskentataulukkoon.
3. Tämän seurauksena SUM OFFSET -kaava siirtyy alaspäin soluun B7 ja rivi 6 on nyt tyhjä.
4. Napsauta solua A6.
5. Syötä numero5 osoittaa, että viidennen päivän myyntimäärä on ilmoitettu.
6. Napsauta solua B6.
7. Kirjoita numero$1458.25 ja painaTulla sisään näppäintä.

Solu B7 päivittää uuteen kokonaismäärään$7137.40.

Kun napsautat solua B7, päivitetty kaava= SUM (B2: OFFSET (B7, -1,0)) näkyy kaavassa.

OFFSET-toiminnolla on kaksi vaihtoehtoista argumenttia: korkeus ja leveys , joita ei käytetty tässä esimerkissä.

Näitä argumentteja voidaan käyttää ilmaisemaan OFFSET-funktiolle tulosteen muoto niin monta riviä korkeana ja niin monta saraketta leveä.

Jättämällä nämä argumentit, toiminto käyttää oletusarvoisesti referenssimerkin korkeutta ja leveyttä, joka tässä esimerkissä on yksi rivi korkealle ja yksi sarake leveä.