Uzu SUM kaj OFFSET por trovi totalaĵojn por dinamikaj gamoj de datumoj
Se via Excel- skatolo inkludas kalkulojn bazitan sur ŝanĝiĝanta gamo de ĉeloj, uzante la SUM kaj OFFSET-funkcioj kune en formulo de SUM OFFSET, simpligas la taskon konservi la kalkulojn ĝisdatigitaj.
Krei dinamikan gamon Kun la SUM kaj OFFSET-Funkcioj
Se vi uzas kalkulojn dum tempo da tempo, kiuj konstante ŝanĝas - kiel plenigi vendojn por la monato - la OFFSET-funkcio ebligas al vi agordi dinamikan rangon, kiu ŝanĝiĝas, ĉar aldoniĝas ciferoj de vendado de ĉiu tago.
En si mem, la SUM-funkcio kutime povas akomodi novajn ĉelojn de datumoj enmetitaj en la gamon alvokita.
Unu escepto okazas kiam la datumoj estas enmetitaj en la ĉelon kie la funkcio estas nun situata.
En la ekzempla bildo, kiu akompanas ĉi tiun artikolon, la novaj vendaj ciferoj por ĉiu tago aldoniĝas al la malsupro de la listo, kiuj devigas la totalon senĉese movi unu ĉelon ĉiufoje kiam la nova datumo aldoniĝas.
Se la funkcio SUM estis uzata sole al la tuta datumo, necese modifi la gamon de ĉeloj uzataj kiel la argumento de la funkcio ĉiufoje kiam nova datumo estis aldonita.
Per uzado de la SUM kaj OFFSET-funkcioj kune, tamen, la gamo kiu estas plenigita fariĝas dinamika. Alivorte, ĝi ŝanĝas por akomodi novajn ĉelojn de datumoj. La aldono de novaj ĉeloj de datumoj ne kaŭzas problemojn ĉar la gamo daŭre ĝustigas kiam ĉiu nova ĉelo aldoniĝas.
Sintakso kaj Argumentoj
Legu la bildon, kiu akompanas ĉi tiun artikolon por sekvi kune kun ĉi tiu lernilo.
En ĉi tiu formulo, la funkcio SUM estas uzata por entute la gamo de datumoj provizitaj kiel ĝia argumento. La komenca punkto por ĉi tiu gamo estas statika kaj estas identigita kiel la ĉela referenco al la unua nombro por esti sumigita per la formulo.
La OFFSET-funkcio nestiĝas ene de la SUM-funkcio kaj estas uzata por krei dinamikan finpunkton al la rango de datumoj totalata per la formulo. Ĉi tio plenumas per la fiksa punkto de la rango al unu ĉelo super la loko de la formulo.
La sintakso de la formulo:
= SUM (Range Start: OFFSET (Referenco, Rendoj, Koloj))Range Start - (postulita) la komencpunkto por la gamo de ĉeloj, kiuj estos plenigita de la SUM-funkcio. En la ekzempla bildo, ĉi tio estas ĉelo B2.
Referenco - (postulita) la ĉela referenco uzita por kalkuli la punktopunkton de la rango situanta multajn vicojn kaj kolumnojn for. En la ekzemplo de bildo, la Referenca argumento estas la ĉelo-referenco por la formulo mem, ĉar ni ĉiam volas ke la gamo finu unu ĉelon super la formulo.
Rows - (postulita) la nombro da vicoj supre aŭ sub la Referenca argumento uzita kalkulanta la kompenson. Ĉi tiu valoro povas esti pozitiva, negativa, aŭ fiksita al nulo.
Se la situacio de la kompenso estas super la Reklara argumento, ĉi tiu valoro estas negativa. Se ĝi estas malsupre, la Rows- argumento estas pozitiva. Se la kompenso situas la saman vicon, ĉi tiu argumento estas nulo. En ĉi tiu ekzemplo, la kompenso komencas unu vico super la Referenca argumento, do la valoro por ĉi tiu argumento estas negativa (-1).
Cols - (postulita) la nombro da kolumnoj maldekstre aŭ dekstra de la Referenca argumento uzita kalkulanta la kompenson. Ĉi tiu valoro povas esti pozitiva, negativa, aŭ fiksita al nulo
Se la situacio de la kompenso estas maldekstre de la Reklara argumento, ĉi tiu valoro estas negativa. Se dekstre, la kalkulado argumentas pozitivan. En ĉi tiu ekzemplo, la datumo estas plenigita estas en la sama kolumno kiel la formulo do la valoro por ĉi tiu argumento estas nulo.
Uzante la SUM OFFSET Formula al Tutaj Vendoj Datumoj
Ĉi tiu ekzemplo uzas SUM OFFSET-formulon por redoni la totalon por la ĉiutagaj vendaj ciferoj listigitaj en kolumno B de la foliaro.
Komence, la formulo estis enmetita en ĉelon B6 kaj kompletigis la vendajn datumojn dum kvar tagoj.
La sekva paŝo estas movi la formulon SUM OFFSET laŭ unu vico por fari ĉambron por la tuta vendo de la kvina tago.
Ĉi tio plenumas enmetante novan vicon 6, kiu movas la formulon malsupren al vico 7.
Kiel rezulto de la movo, Excel aŭtomate ĝisdatigas la Referencan argumenton al ĉelo B7 kaj aldonas ĉelon B6 al la gamo resumita per la formulo.
Enirante la SUM OFFSET Formula
- Alklaku la ĉelon B6, kiu estas la loko, kie la rezultoj de la formulo unue montriĝos.
- Alklaku la pestaña Formulo de la rubando menuo.
- Elektu Math & Trig de la rubando por malfermi la funkcion de malsupren-listo.
- Alklaku SUM en la listo por alporti la dialogon de la funkcio.
- En la dialogujo, alklaku la numeron 1 .
- Alklaku la ĉelon B2 por eniri ĉi tiun ĉelon-referencon en la dialogon. Ĉi tiu loko estas la statika fina punkto por la formulo;
- En la dialogujo, alklaku la linio Number2 .
- Enmetu la jenan OFFSET-funkcion: OFFSET (B6, -1,0) por formi la dinamikan finpunkton por la formulo.
- Alklaku OK por kompletigi la funkcion kaj fermi la dialogon.
La tuta $ 5679.15 aperas en la ĉelo B7.
Kiam vi alklakas ĉelon B3, la kompleta funkcio = SUM (B2: OFFSET (B6, -1,0)) aperas en la formulo-baron super la foliaro.
Aldonante la datumajn vendojn de la sekva tago
Por aldoni la datumajn vendojn de la sekva tago:
- Deklaku sur la vico kaplinio por vico 6 por malfermi la kuntekston menuo.
- En la menuo, alklaku Enŝovu por enmeti novan vicon en la foliumon.
- Kiel rezulto, la formulo SUM OFFSET moviĝas malsupren al ĉelo B7 kaj vico 6 nun estas malplena.
- Alklaku la ĉelon Al6 .
- Enmetu la numeron 5 por indiki ke la tuta vendo por la kvina tago estas enirita.
- Alklaku ĉelon B6.
- Tajpu la nombron $ 1458.25 kaj premu la Enŝlosilon sur la klavaro.
Ĉeloj B7 ĝisdatigoj al la nova tuta de $ 7137.40.
Kiam vi alklakas ĉelon B7, la ĝisdatigita formulo = SUM (B2: OFFSET (B7, -1,0)) aperas en la formala trinkejo.
Noto : La OFFSET-funkcio havas du laŭvolajn argumentojn: Alteco kaj Larĝeco, kiuj estis preterlasitaj en ĉi tiu ekzemplo.
Ĉi tiuj argumentoj povas esti uzataj por rakonti la OFFSET-funkcion la formon de la eligo laŭ ĝi estas tiom multe da vicoj altaj kaj tiom multaj kolumnoj larĝe.
Per preterlasante ĉi tiujn argumentojn, la funkcio, defaŭlte, uzas la altecon kaj larĝon de la Referenca argumento anstataŭe, kiu, en ĉi tiu ekzemplo, estas unu vico alta kaj unu kolumno larĝa.