Трчање укупно (такође се назива кумулативни збир) се често користи у многим ситуацијама. То је метрика која вам говори колики је збир досадашњих вредности.
На пример, ако имате месечне податке о продаји, тада ће вам укупни збир рећи колико је продаје обављено до одређеног дана од првог дана у месецу.
Постоје и неке друге ситуације у којима се често користи укупни износ, као што је израчунавање стања готовине у банковним изводима/главној књизи, бројање калорија у вашем оброку итд.
У Мицрософт Екцелу постоји више различитих начина за израчунавање укупних износа.
Метод који одаберете зависиће и од тога како су ваши подаци структурирани.
На пример, ако имате једноставне табеларне податке, можете користити једноставну формулу СУМ, али ако имате Екцел табелу, најбоље је да користите структуриране референце. За то можете користити и Повер Куери.
У овом водичу ћу покрити све ове различите методе израчунајте укупне износе у Екцелу.
Па да почнемо!
Израчунавање укупног износа са табеларним подацима
Ако имате табеларне податке (тј. Табелу у Екцелу која није претворена у Екцел табелу), можете користити неке једноставне формуле за израчунавање текућих збира.
Коришћење оператора сабирања
Претпоставимо да имате датумске податке о продаји и желите да израчунате укупан износ у колони Ц.
Испод су кораци за то.
Корак 1 - У ћелију Ц2, која је прва ћелија у коју желите текући збир, унесите
= Б2
Ово ће једноставно добити исте продајне вредности у ћелији Б2.
Корак 2 - У ћелију Ц3 унесите доњу формулу:
= Ц2+Б3
Корак 3 - Примените формулу на целу колону. Можете користити ручицу Филл да је изаберете и превучете или једноставно копирате и налепите ћелију Ц3 у све преостале ћелије (што би аутоматски прилагодило референцу и дало прави резултат).
Ово ће вам дати резултат као што је приказано испод.
То је заиста једноставан метод и добро функционише у већини случајева.
Логика је једноставна - свака ћелија преузима вредност изнад себе (што је кумулативни збир до претходног датума) и додаје вредност у ћелији поред ње (што је продајна вредност за тај дан).
Постоји само један недостатак - у случају да избришете било који од постојећих редова у овом скупу података, све ћелије испод које би вратиле референтну грешку (#РЕФ!)
Ако је то могуће са вашим скупом података, користите следећи метод који користи формулу СУМ
Коришћење СУМ -а са делимично закључаном референцом ћелије
Претпоставимо да имате датумске податке о продаји и желите да израчунате укупан износ у колони Ц.
Испод је формула СУМ која ће вам дати укупни укупни резултат.
= СУМ ($ Б $ 2: Б2)
Дозволите ми да објасним како ова формула функционише.
У горњој формули СУМ користио сам референцу за додавање као $ Б $ 2: Б2
- $ Б $ 2 - ово је апсолутна референца, што значи да када копирам исту формулу у ћелије испод, ова референца се неће променити. Дакле, приликом копирања формуле у доњу ћелију, формула би се променила у СУМ ($ Б $ 2: Б3)
- Б2 - ово је други део референце који је релативна референца, што значи да би се ово прилагодило док копирам формулу надоле или удесно. Дакле, приликом копирања формуле у ћелију испод, ова вредност ће постати Б3
Такође прочитајте: Апсолутне, релативне и мешовите ћелијске референце у Екцелу
Одлична ствар у вези са овом методом је то што у случају да избришете било који ред у скупу података, ова формула би се прилагодила и још увек би вам дала праве укупне износе.
Израчунавање укупног износа у Екцел табели
Када радите са табеларним подацима у Екцелу, добра је идеја да их претворите у Екцел табелу. Много олакшава управљање подацима, а такође и олакшава употребу алата као што су Повер Куери и Повер Пивот.
Рад у Екцел табелама има предности као што су структуриране референце (што олакшава позивање на податке у табели и њихову употребу у формулама) и аутоматско прилагођавање референци у случају да додате или избришете податке из табеле.
Иако још увек можете да користите горњу формулу коју сам вам показао у Екцел табели, дозволите ми да вам покажем неке боље методе за то.
Претпоставимо да имате Екцел табелу као што је приказано испод и желите да израчунате укупан број у колони Ц.
Испод је формула која ће то учинити:
= СУМ (Подаци о продаји [[#заглавља], [Распродаја]]: [@распродаја])
Горња формула може изгледати дуго, али не морате да је пишете сами. оно што видите у формули за збир назива се структуриране референце, што је Екцел -ов ефикасан начин позивања на одређене тачке података у Екцел табели.
На пример, СалесДата [[#Хеадерс], [Сале]] се односи на заглавље Сале у табели СалесДата (СалесДата је назив Екцел табеле коју сам дао приликом креирања табеле)
А [@Продаја] се односи на вредност у ћелији у истом реду у колони Продаја.
Објаснио сам ово овде ради вашег разумевања, али чак и ако не знате ништа о структурираним референцама, и даље можете лако да направите ову формулу.
У наставку су наведени кораци за то:
- У ћелију Ц2 унесите = СУМ (
- Изаберите ћелију Б1, која је заглавље колоне која има вредност продаје. Можете користити миш или тастере са стрелицама. Приметићете да Екцел аутоматски уноси структурирану референцу за ту ћелију
- Додајте: (симбол двотачке)
- Изаберите ћелију Б2. Екцел би поново аутоматски уметнуо структурирану референцу за ћелију
- Затворите заграду и притисните ентер
Такође ћете приметити да не морате да копирате формулу у целу колону, већ то уместо вас ради Екцел табела.
Још једна сјајна ствар у вези са овом методом је то што у случају да додате нови запис у овај скуп података, Екцел табела би аутоматски израчунала укупан број за све нове записе.
Иако смо у формулу укључили заглавље колоне, запамтите да ће формула занемарити текст заглавља и узети у обзир само податке у колони
Израчунавање укупног трчања користећи Повер Куери
Повер Куери је невероватан алат када је у питању повезивање са базама података, издвајање података из више извора и њихова трансформација пре него што их ставите у Екцел.
Ако већ радите са Повер Куери -ом, било би ефикасније додати укупне износе док трансформишете податке унутар самог уређивача Повер Куери -ја (уместо да прво добијете податке у Екцелу, а затим додате укупне износе користећи било шта од горе наведеног горе описане методе).
Иако у Повер Куери -ју нема уграђене функције за додавање укупних износа (што бих волео да постоји), то ипак можете учинити помоћу једноставне формуле.
Претпоставимо да имате Екцел табелу као што је приказано испод и желите да додате укупне износе овим подацима:
У наставку су наведени кораци за то:
- Изаберите било коју ћелију у Екцел табели
- Кликните на Подаци
- На картици Гет & Трансформ кликните на икону фром Табле/Ранге. Ово ће отворити табелу у уређивачу програма Повер Куери
- [Опционално] У случају да колона Датум већ није сортирана, кликните на икону филтера у колони Датум, а затим кликните на Сортирај узлазно
- Кликните на картицу Додај колону у уређивачу програма Повер Куери
- У групи Опћенито кликните на падајући изборник Колона индекса (немојте кликнути на икону Колона индекса, већ на малу црну нагнуту стрелицу поред ње да бисте приказали више опција)
- Кликните на опцију „Од 1“. Ово ће додати нову индексну колону која ће почети од један и уносити бројеве повећавајући се за 1 у целу колону
- Кликните на икону „Прилагођена колона“ (која се такође налази на картици Додај колону)
- У дијалогу за прилагођену колону који се отвори унесите назив за нову колону. у овом примеру користићу назив „Трчање укупно“
- У поље Формула прилагођене колоне унесите формулу испод: Лист.Сум (Лист.Ранге (#”Додани индекс” [Продаја], 0, [Индекс]))
- Уверите се да постоји поље за потврду при дну оквира за дијалог који каже - „Нису откривене синтаксичке грешке“
- Притисните ОК. Ово би додало нову активну колону укупног износа
- Уклоните индексну колону
- Кликните на картицу Датотека, а затим кликните на „Затвори и учитај“
Горе наведени кораци би убацили нови лист у вашу радну свеску са табелом која садржи укупне износе.
Ако мислите да је ово превише корака у поређењу са претходним методама коришћења једноставних формула, у праву сте.
Ако већ имате скуп података и све што требате учинити је додати укупне износе, боље је да не користите Повер Куери.
Коришћење програма Повер Куери има смисла тамо где морате да извучете податке из базе података или да комбинујете податке из више различитих радних свески, а у том процесу такође да додате укупне износе.
Такође, када направите ову аутоматизацију користећи Повер Куери, следећи пут када се ваш скуп података промени не морате то да радите поново, можете једноставно освежити упит и то ће вам дати резултат на основу новог скупа података.
Како ово ради?
Дозволите ми да брзо објасним шта се дешава у овој методи.
Прва ствар коју радимо у уређивачу програма Повер Куери је да уметнемо индексну колону почевши од једне и повећавајући је за један док се спушта низ ћелије.
То чинимо јер морамо да користимо ову колону док израчунавамо укупни износ у другој колони коју убацујемо у следећем кораку.
Затим убацујемо прилагођену колону и користимо формулу испод
Лист.Сум (Лист.Ранге (#"Додани индекс" [Продаја], 0, [Индекс]))
Ово је формула Лист.Сум која би вам дала збир распона који је у њој наведен.
И тај опсег је наведен помоћу функције Лист.Ранге.
Функција Лист.Ранге даје наведени опсег у колони продаје као излаз и овај распон се мења на основу вредности индекса. На пример, за први запис распон би једноставно био прва продајна вредност. И док се спуштате низ ћелије, овај опсег би се повећавао.
Дакле, за прву ћелију. Лист.Сум би вам дао само збир прве продајне вредности, а за другу ћелију би вам дао збир за прве две продајне вредности итд.
Иако овај метод добро функционише, постаје веома спор са великим скуповима података - хиљадама редова. Ако се бавите великим скупом података и желите да му додате укупне износе, погледајте овај водич који приказује друге методе које су брже.Израчунавање укупног износа на основу критеријума
До сада смо видели примере где смо израчунали текући збир за све вредности у колони.
Али могу постојати случајеви у којима желите да израчунате укупни износ за одређене записе.
На пример, испод имам скуп података и желим да израчунам укупни рад за штампаче и скенере одвојено у две различите колоне.
Ово се може урадити помоћу формуле СУМИФ која израчунава укупни укупни број притом пазећи да је наведени услов испуњен.
Испод је формула која ће то учинити за колоне штампача:
= СУМИФ ($ Ц $ 2: Ц2, $ Д $ 1, $ Б $ 2: Б2)
Слично, за израчунавање укупног износа за скенере, користите доњу формулу:
= СУМИФ ($ Ц $ 2: Ц2, $ Е $ 1, $ Б $ 2: Б2)
У горњим формулама користио сам СУМИФ, који би ми дао збир у опсегу када су испуњени наведени критеријуми.
Формула има три аргумента:
- домет: ово је опсег критеријума који би се проверио према наведеним критеријумима
- критеријума: ово су критеријуми који би били пријављени само ако је овај критеријум испуњен, тада би се додале вредности у трећем аргументу, а то је опсег збира
- [сум_ранге]: ово је опсег збира из којег би се вредности додале ако су критеријуми испуњени
Такође, у домет и сум_ранге аргумент, закључао сам други део референце, тако да би се, док се спуштамо низ ћелије, опсег стално повећавао. Ово нам омогућава да разматрамо и додајемо вредности само до тог опсега (дакле текући збројеви).
У овој формули сам користио колону заглавља (штампач и скенер) као критеријум. такође можете хардкодирати критеријуме ако заглавља колона нису потпуно иста као текст критеријума.
У случају да имате више услова које морате да проверите, можете користити формулу СУМИФС.
Покретање резултата Тотал у изведеним табелама
Ако желите да додате укупне износе у резултат изведене табеле, то можете лако учинити помоћу уграђене функције у изведене табеле.
Претпоставимо да имате изведену табелу као што је приказано испод где ја имам датум у једној колони, а вредност продаје у другој колони.
У наставку су наведени кораци за додавање додатне колоне која ће приказивати укупну продају по датуму:
- Превуците поље Продаја и ставите га у област Вредност.
- Ово ће додати још једну колону са вредностима продаје
- Кликните на опцију Сум оф Сале2 у области Валуе
- Кликните на опцију „Поставке поља вредности“
- У дијалогу Поставке поља вредности промените прилагођено име у „Текући износи“
- Кликните на картицу „Прикажи вредност као“
- У падајућем менију Прикажи вредност као изаберите опцију „Покретање укупног износа“
- У опцијама основног поља проверите да ли је изабран датум
- Притисните У реду
Горе наведени кораци би променили другу колону продаје у колону Руннинг Тотал.
Ово су неки од начина на које можете користити за израчунавање укупног износа у Екцелу. Ако имате податке у табеларном формату, можете користити једноставне формуле, а ако имате Екцел табелу, онда можете користити формуле које користе структуриране референце.
Такође сам описао како израчунати укупни укупни рад помоћу програма Повер Куери и у изведеним табелама.
Надам се да вам је овај водич био користан.
