Бягаща сума (наричана още кумулативна сума) се използва често в много ситуации. Това е показател, който ви казва каква е сумата от стойностите досега.
Например, ако имате данни за месечните продажби, тогава текущата сума ще ви каже колко продажби са направени до определен ден от първия ден на месеца.
Съществуват и някои други ситуации, при които често се използва текуща сума, като например изчисляване на паричното ви салдо в банковите ви извлечения/счетоводна книга, броене на калории в плана ви за хранене и т.н.
В Microsoft Excel има няколко различни начина за изчисляване на текущите суми.
Избраният от вас метод също ще зависи от това как са структурирани вашите данни.
Например, ако имате прости таблични данни, можете да използвате проста формула SUM, но ако имате таблица в Excel, тогава е най -добре да използвате структурирани препратки. Можете също да използвате Power Query, за да направите това.
В този урок ще разгледам всички тези различни методи изчислете текущите суми в Excel.
Така че нека започнем!
Изчисляване на текущата сума с таблични данни
Ако имате таблични данни (т.е. таблица в Excel, която не се преобразува в таблица на Excel), можете да използвате някои прости формули за изчисляване на текущите суми.
Използване на оператора на добавяне
Да предположим, че имате данни за продажбите по дати и искате да изчислите текущата сума в колона C.
По -долу са описани стъпките за това.
Етап 1 - В клетка C2, която е първата клетка, в която искате текущата сума, въведете
= В2
Това просто ще получи същите стойности за продажба в клетка В2.
Стъпка 2 - В клетка C3 въведете формулата по -долу:
= C2+B3
Стъпка 3 - Приложете формулата към цялата колона. Можете да използвате дръжката за запълване, за да я изберете и плъзнете, или просто и копирате и поставите клетката C3 във всички останали клетки (което автоматично ще коригира препратката и ще даде правилния резултат).
Това ще ви даде резултат, както е показано по -долу.
Това е наистина прост метод и работи добре в повечето случаи.
Логиката е проста - всяка клетка взема стойността над нея (която е кумулативната сума до датата преди) и добавя стойността в клетката в съседство с нея (която е стойността на продажбата за този ден).
Има само един недостатък - в случай, че изтриете някой от съществуващите редове в този набор от данни, всички клетки по -долу, които биха върнали референтна грешка (#REF!)
Ако това е възможно с вашия набор от данни, използвайте следващия метод, който използва формулата SUM
Използване на SUM с частично заключена справка за клетка
Да предположим, че имате данни за продажбите по дати и искате да изчислите текущата сума в колона C.
По -долу е формулата SUM, която ще ви даде текущата сума.
= SUM ($ B $ 2: B2)
Позволете ми да обясня как работи тази формула.
В горната формула SUM използвах препратката, за да добавя като $ B $ 2: B2
- $ B $ 2 - това е абсолютна справка, което означава, че когато копирам същата формула в клетките по -долу, тази препратка няма да се промени. Така че, когато копирате формулата в клетката по -долу, формулата ще се промени на SUM ($ B $ 2: B3)
- B2 - това е втората част на препратката, която е относителна препратка, което означава, че това ще се коригира, докато копирам формулата надолу или надясно. Така че, когато копирате формулата в клетката по -долу, тази стойност ще стане B3
Прочетете също: Абсолютни, относителни и смесени клетъчни препратки в Excel
Голямото нещо за този метод е, че в случай, че изтриете някой от редовете в набора от данни, тази формула ще се коригира и все пак ще ви даде правилните текущи суми.
Изчисляване на текущата сума в таблица на Excel
Когато работите с таблични данни в Excel, е добра идея да ги преобразувате в таблица на Excel. Това прави много по -лесно управлението на данните, а също така позволява и улеснява използването на инструменти като Power Query и Power Pivot.
Работата с таблиците на Excel идва с предимства, като структурирани препратки (което прави наистина лесно препращането към данните в таблицата и използването им във формули), както и автоматично коригиране на препратки в случай, че добавяте или изтривате данни от таблицата.
Въпреки че все още можете да използвате горната формула, която ви показах в таблица на Excel, нека ви покажа някои по -добри методи за това.
Да предположим, че имате таблица в Excel, както е показано по -долу и искате да изчислите текущата сума в колона C.
По -долу е формулата, която ще направи това:
= SUM (SalesData [[#Headers], [Sale]]: [@Sale])
Горната формула може да изглежда малко дълга, но не е нужно да я пишете сами. това, което виждате във формулата за сумата, се нарича структурирани препратки, което е ефективният начин на Excel да се позовава на конкретни точки от данни в таблица на Excel.
Например SalesData [[#Headers], [Sale]] се отнася до заглавката Sale в таблицата SalesData (SalesData е името на таблицата на Excel, която дадох, когато създадох таблицата)
И [@Sale] се отнася до стойността в клетката в същия ред в колоната Sale.
Току -що обясних това тук за ваше разбиране, но дори и да не знаете нищо за структурираните препратки, все пак можете лесно да създадете тази формула.
По -долу са описани стъпките за това:
- В клетка C2 въведете = SUM (
- Изберете клетка B1, която е заглавката на колоната, която има продажната стойност. Можете да използвате мишката или клавишите със стрелки. Ще забележите, че Excel автоматично въвежда структурираната справка за тази клетка
- Добавете: (символ на двоеточие)
- Изберете клетка В2. Excel отново автоматично ще вмъкне структурираната справка за клетката
- Затворете скобата и натиснете enter
Ще забележите също, че не е нужно да копирате формулата в цялата колона, таблица в Excel автоматично го прави вместо вас.
Друго чудесно нещо за този метод е, че в случай, че добавите нов запис в този набор от данни, таблицата на Excel автоматично ще изчисли текущата сума за всички нови записи.
Докато сме включили заглавката на колоната във формулата си, не забравяйте, че формулата ще игнорира заглавния текст и ще вземе предвид само данните в колоната
Изчисляване на текущата сума с помощта на Power Query
Power Query е невероятен инструмент, когато става въпрос за свързване с бази данни, извличане на данни от множество източници и трансформиране, преди да ги поставите в Excel.
Ако вече работите с Power Query, би било по -ефективно да добавите текущи суми, докато трансформирате данните в самия редактор на Power Query (вместо първо да получите данните в Excel и след това да добавите текущите суми, използвайки някое от горните описаните по -горе методи).
Въпреки че в Power Query няма вградена функция за добавяне на текущи суми (което бих искал да има), все пак можете да направите това, като използвате проста формула.
Да предположим, че имате таблица в Excel, както е показано по -долу, и искате да добавите текущите суми към тези данни:
По -долу са описани стъпките за това:
- Изберете всяка клетка в таблицата на Excel
- Кликнете върху Данни
- В раздела Get & Transform щракнете върху иконата от Table/Range. Това ще отвори таблицата в редактора на Power Query
- [Незадължително] В случай, че колоната ви „Дата“ още не е сортирана, щракнете върху иконата на филтъра в колоната „Дата“ и след това върху „Сортиране по възходящ ред“
- Щракнете върху раздела Добавяне на колона в редактора на Power Query
- В групата General кликнете върху падащото меню Index Column (не щракнете върху иконата Index Column, а върху малката черна наклонена стрелка до нея, за да покажете повече опции)
- Кликнете върху опцията „От 1“. Това ще добави нова индексна колона, която ще започне от една и ще въведе числа, увеличаващи се с 1 в цялата колона
- Кликнете върху иконата „Персонализирана колона“ (която също е в раздела Добавяне на колона)
- В диалоговия прозорец за персонализирана колона, който се отваря, въведете име за новата колона. в този пример ще използвам името „Running Total“
- В полето Формула за персонализирана колона въведете формулата по -долу: List.Sum (List.Range (#”Добавен индекс” [Продажба], 0, [Индекс]))
- Уверете се, че в долната част на диалоговия прозорец има квадратче за отметка - „Не са открити синтаксични грешки“
- Щракнете върху OK. Това би добавило нова колона за обща сума
- Премахнете индексната колона
- Кликнете върху раздела Файл и след това върху „Затваряне и зареждане“
Горните стъпки биха вмъкнали нов лист във вашата работна книга с таблица с текущите суми.
Сега, ако мислите, че това са твърде много стъпки в сравнение с предишните методи за използване на прости формули, прав сте.
Ако вече имате набор от данни и всичко, което трябва да направите, е да добавите текущи суми, по -добре е да не използвате Power Query.
Използването на Power Query има смисъл, когато трябва да извличате данни от база данни или да комбинирате данни от множество различни работни книги и в процеса също да добавяте текущи суми към нея.
Също така, след като направите тази автоматизация с помощта на Power Query, следващия път, когато вашият набор от данни се промени, не е нужно да го правите отново, можете просто да опресните заявката и това ще ви даде резултат въз основа на новия набор от данни.
Как работи това?
Сега нека бързо да обясня какво се случва в този метод.
Първото нещо, което правим в редактора на Power Query, е да вмъкнем колона с индекс, започваща от една и увеличавайки с една, докато слиза надолу по клетките.
Правим това, защото трябва да използваме тази колона, докато изчисляваме текущата сума в друга колона, която вмъкваме в следващата стъпка.
След това вмъкваме персонализирана колона и използваме формулата по -долу
List.Sum (List.Range (#"Добавен индекс" [Продажба], 0, [Индекс]))
Това е формула List.Sum, която ще ви даде сумата от диапазона, който е посочен в нея.
И този диапазон се определя с помощта на функцията List.Range.
Функцията List.Range дава зададения диапазон в колоната за продажба като изход и този диапазон се променя въз основа на стойността на индекса. Например, за първия запис диапазонът просто ще бъде първата стойност на продажбата. И когато слизате по клетките, този диапазон ще се разширява.
И така, за първата клетка. List.Sum ще ви даде само сумата от стойността на първата продажба, а за втората клетка ще ви даде сумата за първите две стойности на продажбите и т.н.
Въпреки че този метод работи добре, става много бавен с големи набори от данни - хиляди редове. Ако имате работа с голям набор от данни и искате да добавите текущи суми към него, погледнете този урок, който показва други методи, които са по -бързи.Изчисляване на текущата сума въз основа на критериите
Досега сме виждали примери, в които сме изчислили текущата сума за всички стойности в колона.
Но може да има случаи, в които искате да изчислите текущата сума за конкретни записи.
Например по -долу имам набор от данни и искам да изчисля текущата сума за принтери и скенери поотделно в две различни колони.
Това може да стане с помощта на формула SUMIF, която изчислява текущата сума, като същевременно се уверява, че посоченото условие е изпълнено.
По -долу е формулата, която ще направи това за колоните на принтера:
= SUMIF ($ C $ 2: C2, $ D $ 1, $ B $ 2: B2)
По същия начин, за да изчислите текущата сума за скенерите, използвайте формулата по -долу:
= SUMIF ($ C $ 2: C2, $ E $ 1, $ B $ 2: B2)
В горните формули съм използвал SUMIF, който би ми дал сумата в диапазон, когато са изпълнени посочените критерии.
Формулата приема три аргумента:
- диапазон: това е диапазонът от критерии, който би бил проверен спрямо посочените критерии
- критерии: това са критериите, които биха били проверени само ако този критерий е изпълнен, тогава стойностите в третия аргумент, който е сумарният диапазон, ще бъдат добавени
- [sum_range]: това е диапазонът от суми, от който биха били добавени стойностите, ако критериите са изпълнени
Също така, в диапазон и sum_range аргумент, заключих втората част на препратката, така че докато слизаме по клетките, диапазонът ще продължи да се разширява. Това ни позволява да разглеждаме и добавяме стойности само до този диапазон (следователно текущи суми).
В тази формула използвах заглавната колона (принтер и скенер) като критерии. можете също така да кодирате критериите твърдо, ако заглавките на колоните ви не са точно същите като текста на критериите.
В случай, че имате множество условия, които трябва да проверите, тогава можете да използвате формулата SUMIFS.
Изпълнение на общо в обобщени таблици
Ако искате да добавите текущи суми в резултат от обобщена таблица, можете лесно да направите това, като използвате вградена функционалност в обобщени таблици.
Да предположим, че имате обобщена таблица, както е показано по -долу, където имам датата в една колона и стойността на продажбата в другата колона.
По -долу са стъпките за добавяне на допълнителна колона, която ще показва текущата сума на продажбите по дата:
- Плъзнете полето Продажба и го поставете в областта Стойност.
- Това ще добави друга колона със стойностите на продажбите
- Щракнете върху опцията Сума на продажбите2 в областта Стойност
- Кликнете върху опцията „Настройки на полето за стойност“
- В диалоговия прозорец Настройки на полето за стойност променете персонализираното име на „Текущи суми“
- Кликнете върху раздела „Показване на стойността като“
- В падащото меню Показване на стойността като изберете опцията „Изпълнение на общото количество“
- В опциите на полето Base се уверете, че е избрана Date
- Щракнете върху OK
Горепосочените стъпки биха променили втората колона за продажба в колона Running Total.
Така че това са някои от начините, които можете да използвате за изчисляване на текущата сума в Excel. Ако имате данни в табличен формат, можете да използвате прости формули, а ако имате таблица в Excel, тогава можете да използвате формули, които използват структурирани препратки.
Разгледах също как да се изчисли текущата сума с помощта на Power Query и в обобщени таблици.
Надявам се, че сте намерили този урок за полезен.
