текуща обща сума (известна още катокумулативна сума) се използва много често в много случаи.Това е показател, който ви казва каква е сумата от стойностите до момента.
Например, ако имате месечни данни за продажбите, текущата сума ще ви каже колко продажби са направени от първия ден на месеца до конкретна дата.
Има и други ситуации, при които често се използват суми, като например изчисляване на парични салда в банкови извлечения/счетоводни книги, изчисляване на калории в планове за хранене и др.
В Microsoft Excel има няколко различни начина за изчисляване на текущите суми.
Избраният от вас метод зависи и от структурата на данните.
Например, ако имате прости таблични данни, можете да използвате проста формула SUM, но ако имате таблица в Excel, по-добре е да използвате структурирани препратки.Можете също да използвате Power Query, за да направите това.
В този урок ще разгледам всички тяхИзчислете текущата сума в Excelразлични методи.
Така че нека започваме!
Съдържание
Изчислете текущите суми с помощта на таблични данни
Ако имате таблични данни (тоест таблици в Excel, които не се преобразуват в таблици на Excel), можете да използвате някои прости формули за изчисляване на текущи суми.
Използвайте оператора за добавяне
Да предположим, че имате данни за продажбите по дата и искате在 в колона CизчислениеТечаща обща сума.
Ето стъпките за това.
етап 1 – Въведете в клетка C2, първата клетка, за която искате да изпълните общата сума
= B2
Това просто получава същата стойност на продажбите в клетка B2.
етап 2– В клетка C3 въведете следната формула:
= C2 + B3
етап 3– Приложете формулата към цялата колона.Можете да използвате манипулатора за запълване, за да го изберете и плъзнете, или просто да копирате и поставите клетка C3 във всички останали клетки (това автоматично ще коригира препратката и ще даде правилния резултат).
Това ще ви даде резултата, показан по-долу.
Това е много прост метод, който работи добре в повечето случаи.
Логиката е проста - всяка клетка взема стойността върху нея (която е кумулативната сума до предишния ден) и добавя стойността в съседната клетка (която е стойността на продажбите за текущия ден).
Има само един недостатък - ако изтриете който и да е съществуващ ред в този набор от данни, всички клетки по-долу ще върнат референтна грешка (#REF!)
Ако вашият набор от данни има тази възможност, използвайте следващия метод, като използвате формулата SUM
Използвайте SUM с частично заключени препратки към клетки
Да предположим, че имате данни за продажбите по дата и искате да изчислите текущата сума в колона C.
По-долу е формулата SUM, която ще ви даде текуща сума.
=СУМА($B$2:B2)
Нека обясня как работи тази формула.
Във формулата SUM по-горе използвах препратката за добавяне като $B$2:B2
- $B$2 - Това е абсолютна препратка, което означава, че тази препратка не се променя, когато копирам същата формула в клетката по-долу.Така че, когато копирате формулата в клетката по-долу, формулата ще се промени на SUM($B$2:B3)
- B2 - Това е втората част на препратката, тя е относителна препратка, което означава, че когато копирам формулата надолу или вдясно, това се коригира.Така че, когато копирате формулата в клетката по-долу, стойността става B3
Хубавото на този подход е, че ако изтриете някои редове в набора от данни, тази формула се коригира и все още ви дава правилната текуща сума.
Изчислете текущата обща сума в лист на Excel
Когато работите с таблични данни в Excel, най-добре е да ги преобразувате в таблица на Excel.Това улеснява управлението на данни, а също така улеснява използването на инструменти като Power Query и Power Pivot.
Използването на таблица в Excel предоставя няколко предимства, като например структурирани препратки (които правят много лесно препратките към данни в таблицата и използването им във формули) и автоматично коригиране на препратките, докато добавяте или премахвате данни от таблицата.
Въпреки че все още можете да използвате горната формула, която ви показах в лист на Excel, позволете ми да ви покажа някои по-добри начини да го направите.
Да предположим, че имате таблица в Excel като тази по-долу и искате да изчислите текущата сума в колона C.
Ето формулата, за да направите това:
=SUM(Данни за продажби[[#Headers],[Продажба]]:[@Продажба])
Горната формула може да изглежда малко дълга, но не е нужно да я пишете сами.Това, което виждате във формулата за сума, се нарича структурирана препратка и е ефективен начин за Excel да препраща към конкретни точки от данни в таблица на Excel.
Например SalesData[[#Headers],[Sale]] се отнася до заглавката Sales в таблицата SalesData (SalesData е името на таблицата в Excel, която дадох, когато създадох таблицата)
Докато [@Sale] се отнася до стойността в клетка в същия ред в колоната Продажба.
Тук просто обяснявам това за ваше разбиране, но дори и да не знаете нищо за структурираните препратки, пак можете лесно да създадете тази формула.
Ето стъпките, за да направите това:
- В клетка C2 въведете =SUM(
- Изберете клетка B1, която е заглавието на колоната със стойност на продажба.Можете да използвате мишката или клавишите със стрелки.Ще забележите, че Excel автоматично въвежда структурирана препратка към клетката
- Добавете: (двоеточие)
- Изберете клетка B2.Excel автоматично ще вмъкне отново структурирани препратки към клетки
- Затворете скобата и натиснете Enter
Ще забележите също, че не е нужно да копирате цялата колона с формули, листът на Excel го прави автоматично вместо вас.
Друго предимство на този подход е, че ако добавите нов запис към този набор от данни, листът на Excel автоматично изчислява текущата сума за всички нови записи.
Въпреки че включихме заглавките на колоните във формулата, имайте предвид, че формулата игнорира текста на заглавката и разглежда само данните в колоната
Изчислете текущите суми с Power Query
Power Query е невероятен инструмент, когато става въпрос за свързване с бази данни, извличане на данни от множество източници и трансформиране, преди да ги поставите в Excel.
Ако вече използвате Power Query, по-ефективно е да добавяте текущи суми при трансформиране на данните в самия Power Query Editor (вместо първо да получавате данните в Excel и след това да добавяте текущи суми, като използвате някой от методите по-горе).
Въпреки че няма вградена функционалност в Power Query за добавяне на текущи суми (желах да има), все пак можете да го направите с проста формула.
Да предположим, че имате таблица в Excel, която изглежда така, и искате да добавите текущи суми към тези данни:
Ето стъпките, за да направите това:
- Изберете произволна клетка в таблица на Excel
- данни за щракване
- В раздела Получаване и трансформиране щракнете върху иконата от таблица/обхват.Това ще отвори таблицата в Power Query Editor
- [По избор] Ако вашата колона с дата вече не е сортирана, щракнете върху иконата на филтър в колоната за дата, след което щракнете върху Сортиране по възходящо
- Щракнете върху раздела Добавяне на колона в Power Query Editor
- В групата Общи щракнете върху падащото меню Индексна колона (не щракайте върху иконата на Индексна колона, а малката черна наклонена стрелка до нея, за да разкриете още опции)
- Кликнете върху опцията „От 1“.Правейки това ще добавите нова индексна колона, която ще започне от 1 и ще въведе числа, увеличени с 1 в цялата колона
- Щракнете върху иконата Персонализирани колони (също в раздела Добавяне на колони)
- В диалоговия прозорец Персонализирани колони, който се отваря, въведете име за новата колона.В този пример ще използвам името "Running Total"
- В полето формула за персонализирана колона въведете следната формула:List.Sum(List.Range(#"Added Index"[Sale],0,[Index]))
- Уверете се, че в долната част на диалоговия прозорец има квадратче за отметка „Не са открити синтактични грешки“
- Щракнете върху OK.Това ще добави нова колона за текущо общо количество
- пускане на индексна колона
- Щракнете върху раздела Файл, след което щракнете върху Затвори и Зареди
Горните стъпки ще вмъкнат нов работен лист във вашата работна книга с таблица с текущи суми.
Сега, ако смятате, че това са твърде много стъпки в сравнение с предишния подход с прости формули, вие сте прави.
Ако вече имате набор от данни и всичко, което трябва да направите, е да добавите текущи суми, тогава е най-добре да не използвате Power Query.
Използването на Power Query има смисъл в ситуации, в които трябва да извлечете данни от база данни или да комбинирате данни от няколко различни работни книги и също така да добавите текущи суми към тях в процеса.
Освен това, след като автоматизирате това с Power Query, следващия път, когато вашият набор от данни се промени, не е нужно да го правите отново, можете просто да опресните заявката и тя ще ви даде резултати въз основа на новия набор от данни.
Как работи това?
Сега нека набързо да обясня какво се случва с този подход.
Първото нещо, което правим в Power Query Editor, е да вмъкнем колона с индекс, която започва от 1 и се увеличава, докато се спуска надолу в клетката.
Правим това, защото трябва да използваме тази колона, когато изчисляваме текущата сума в друга колона, която се вмъква в следващата стъпка.
След това вмъкваме персонализирана колона и използваме следната формула
List.Sum(List.Range(#"Added Index"[Sale],0,[Index]))
Това е формула 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, която ще ми даде сума в диапазон, когато са изпълнени определени условия.
Формулата приема три параметъра:
- обхват: Това е диапазонът от условия, които ще бъдат проверени спрямо определеното условие
- критерии: това е критерият, който ще се проверява само ако този критерий е изпълнен, след това стойността в третия параметър, диапазонът на сумата ще бъде добавена
- [сума_диапазон]: това е диапазонът на сумата от стойностите, които трябва да се добавят, когато условието е изпълнено
Освен това вобхват和сума_диапазонпараметър, заключих втората част на препратката, така че докато се движим надолу по клетката, диапазонът продължава да се разширява.Това ни позволява да разглеждаме и добавяме само стойности до този диапазон (следователно текуща сума).
В тази формула използвам заглавната колона (Принтер и скенер) като критерии.Можете също така да кодирате стандарта, ако заглавията на колоните ви не са точно същите като стандартния текст.
Ако трябва да проверите множество условия, можете да използвате формулата SUMIFS.
Текущи суми в обобщена таблица
Ако искате да добавите текуща сума към резултатите от обобщената таблица, можете лесно да го направите, като използвате вградената функционалност в обобщените таблици.
Да предположим, че имате опорна таблица като тази по-долу с дати в една колона и стойности на продажбите в друга.
Ето стъпките за добавяне на допълнителна колона, която ще показва кумулативните продажби по дата:
- Плъзнете полето Продажба и го пуснете в областта Стойност.
- Това ще добави още една колона със стойността на продажбите
- Щракнете върху опцията Общи продажби 2 в областта Стойност
- Щракнете върху опцията Настройки на полето за стойност
- В диалоговия прозорец Настройки на полето за стойност променете Custom Name на Running Total
- Щракнете върху раздела „Показване на стойност като“.
- В падащото меню Показване на стойност като изберете опцията „Текуща обща сума“.
- В опциите за основни полета се уверете, че е избрана дата
- Щракнете върху OK
Горните стъпки ще променят втората колона за продажби в колона „текуща обща сума“.
И така, това са някои от методите, които можете да използвате за изчисляване на текущи суми в Excel.Ако имате данни в табличен формат, можете да използвате прости формули, а ако имате таблици в Excel, можете да използвате формули, които използват структурирани препратки.
Също така разгледах как да изчислим текущите суми с помощта на Power Query и обобщени таблици.
Надявам се, че сте намерили този урок за полезен.
