Käytä SUM ja OFFSET löytääksesi kokonaismäärät dynaamisille tietoalueille
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 - kuten kuukausittaista myyntiä - OFFSET-toiminto mahdollistaa dynaamisen alueen luomisen, joka muuttuu päivittäisten myyntilukujen lisäämiseksi.
Itsestään SUM-funktio voi tavallisesti sijoittaa uudet datasolut, jotka lisätään summaaviin alueisiin.
Yksi poikkeus syntyy, kun tieto asetetaan soluun, jossa toiminto sijaitsee tällä hetkellä.
Tämän artikkelin mukana tulevassa esimerkkikuvauksessa lisätään listan pohjaan uusia päivämääriä, jotka pakottavat kokonaissumman siirtymään alaspäin yhteen soluun aina kun uusia tietoja 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 tämän artikkelin mukana tulevaa kuvaa seuraamaan tätä opetusohjelmaa.
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 - (vaaditaan) solualueen alkupisteen, joka vastaa SUM-toimintoa. Esimerkkikuvaan tämä on solu B2.
Viite - (vaaditaan) solun viite, jota käytetään alueen loppupisteen laskemiseen, sijaitsevat monet rivit ja sarakkeet pois. Esimerkkikuvaan Referenssi- argumentti on itse kaavan soluviite, koska haluamme aina, että alue päättyy yhden kaavan yläpuolelle.
Rivit - (vaaditaan) Rivien määrä ylittää tai alittaa Viite- argumentin, jota käytetään laskennassa. Tämä arvo voi olla positiivinen, negatiivinen tai asetettu nollaksi.
Jos offsetin sijainti on viite- argumentin yläpuolella, tämä arvo on negatiivinen. Jos se on alla, Rivi- argumentti on positiivinen. Jos offset sijaitsee samassa rivissä, tämä argumentti on nolla. Tässä esimerkissä offset alkaa yhden rivin Viite- argumentin yläpuolella, joten tämän argumentin arvo on negatiivinen (-1).
Cols - (vaaditaan) sarakkeiden lukumäärä viite- argumentin vasemmalle tai oikealle puolelle laskennassa. 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 soluun B7 ja lisää solun B6 kaavalle summattuun alueeseen.
SUM OFFSET -kaavan syöttäminen
- Napsauta solua B6, joka on paikka, jossa kaavan tulokset näytetään alunperin.
- Napsauta nauha- valikon Lomakkeet -välilehteä.
- Valitse Math & Trig nauhalta avattavan toiminnon avattavasta luettelosta.
- Napsauta SUM-painiketta listassa saadaksesi toiminnon valintaikkunan .
- Napsauta valintaikkunan Numero1- riviä.
- Napsauta solua B2 syöttääksesi tämän soluviitteen valintaikkunaan. Tämä sijainti on kaavan staattinen päätepiste;
- Napsauta valintaikkunan Numero2- riviä.
- Syötä seuraava OFFSET-toiminto: OFFSET (B6, -1,0) kaavan dynaamisen päätepisteen muodostamiseksi.
- Lopeta toiminto ja sulje valintaikkuna valitsemalla OK .
Yhteensä 5679,15 dollaria ilmenee solussa B7.
Kun napsautat solua B3, koko toiminto = SUM (B2: OFFSET (B6, -1,0)) näkyy laskentataulukon yläpuolella olevassa kaavassa .
Seuraavan päivän myynnin tiedot lisätään
Voit lisätä seuraavan päivän myyntitiedot:
- Avaa kontekstivalikko hiiren kakkospainikkeella rivi rivin otsikkoa varten.
- Lisää valintaruutuun uusi rivi napsauttamalla Lisää- painiketta.
- Tämän seurauksena SUM OFFSET -kaava siirtyy alaspäin soluun B7 ja rivi 6 on nyt tyhjä.
- Napsauta solua A6 .
- Anna numero 5, joka ilmoittaa, että viidennen päivän myyntimäärä on syötetty.
- Napsauta solua B6.
- Kirjoita numero 1458,25 dollaria ja paina näppäimistön Enter- näppäintä.
Cell B7 päivittää uuteen yhteensä 7137,40 dollariin.
Kun napsautat solua B7, päivitetty kaava = SUM (B2: OFFSET (B7, -1,0)) näkyy kaavapalkissa.
Huomautus : OFFSET-toiminnolla on kaksi lisävarusteena saatavaa argumenttia: korkeus ja leveys, jotka jätettiin pois tässä esimerkissä.
Näitä argumentteja voidaan käyttää ilmaisemaan OFFSET-funktiolle tulosteen muoto sen mukaan, että se on niin monta riviä korkealla 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ä.