Загальний біг (також називається сукупна сума) досить часто використовується в багатьох ситуаціях. Це метрика, яка показує, яка сума значень на даний момент.
Наприклад, якщо у вас є щомісячні дані про продажі, то загальна сума покаже вам, скільки продажів було зроблено до певного дня з першого дня місяця.
Існують також деякі інші ситуації, коли часто використовується загальна сума, наприклад, розрахунок залишку готівки у виписках/бухгалтерській книзі, підрахунок калорій у вашому плані харчування тощо.
У Microsoft Excel існує кілька різних способів обчислення поточних підсумків.
Вибраний вами метод також залежатиме від структури даних.
Наприклад, якщо у вас є прості табличні дані, ви можете використовувати просту формулу SUM, але якщо у вас є таблиця Excel, то найкраще використовувати структуровані посилання. Для цього також можна використовувати Power Query.
У цьому підручнику я розгляну всі ці різні методи обчислити загальні підсумки в Excel.
Тож почнемо!
Розрахунок загальної суми за допомогою табличних даних
Якщо у вас є табличні дані (тобто таблиця в Excel, яка не перетворюється на таблицю Excel), ви можете скористатися деякими простими формулами для розрахунку поточних підсумків.
Використання оператора додавання
Припустимо, у вас є дані про продажі за датами, і ви хочете обчислити поточний підсумок у стовпці C.
Нижче наведено кроки для цього.
Крок 1 - Введіть у клітинку С2, яка є першою клітинкою, у якій потрібно отримати загальну кількість результатів
= В2
Це просто отримає однакові значення продажу у комірці В2.
Крок 2 - У клітинку С3 введіть формулу нижче:
= С2+В3
Крок 3 - Застосуйте формулу до всього стовпця. Ви можете використовувати маркер заповнення, щоб вибрати та перетягнути його, або просто скопіювати та вставити клітинку С3 до всіх інших клітин (що автоматично налаштує посилання та дасть правильний результат).
Це дасть вам результат, як показано нижче.
Це дійсно простий метод і добре працює в більшості випадків.
Логіка проста - кожна клітинка підбирає значення над нею (що є сукупною сумою до попередньої дати) і додає значення в сусідню з нею комірку (що є ціною продажу за цей день).
Є лише один недолік - у разі видалення будь -якого із наявних рядків у цьому наборі даних усі клітинки нижче, які повертатимуть помилку посилання (#REF!)
Якщо це можливо з вашим набором даних, використовуйте наступний метод, який використовує формулу SUM
Використання SUM з частково заблокованим посиланням на клітинку
Припустимо, у вас є дані про продажі за датами, і ви хочете обчислити поточний підсумок у стовпці C.
Нижче наведена формула SUM, яка дасть вам поточний підсумок.
= SUM ($ B $ 2: B2)
Дозвольте мені пояснити, як працює ця формула.
У наведеній вище формулі SUM я використав посилання, щоб додати як $ B $ 2: B2
- $ B $ 2 - це абсолютна посилання, а це означає, що коли я копіюю ту саму формулу в комірки нижче, це посилання не зміниться. Тож під час копіювання формули у комірку нижче формула зміниться на SUM ($ B $ 2: B3)
- B2 - це друга частина посилання, яка є відносною, що означає, що це буде коригуватися, коли я копіюю формулу вниз або праворуч. Отже, при копіюванні формули в комірку нижче це значення стане В3
Читайте також: Абсолютні, відносні та змішані осередки в Excel
Найкраще в цьому методі те, що у разі видалення будь -якого з рядків у наборі даних ця формула коригуватиметься і надаватиме вам правильні поточні підсумки.
Розрахунок загальної суми в таблиці Excel
Під час роботи з табличними даними в Excel рекомендується перетворити їх у таблицю Excel. Це значно полегшує керування даними, а також дозволяє спростити використання таких інструментів, як Power Query та Power Pivot.
Робота з таблицями Excel має такі переваги, як структуровані посилання (що робить дійсно легким посилання на дані у таблиці та використання їх у формулах), а також автоматичне коригування посилань у разі додавання або видалення даних із таблиці.
Хоча ви все ще можете використовувати вищезгадану формулу, яку я показав вам у таблиці Excel, дозвольте мені показати вам кілька кращих методів для цього.
Припустимо, у вас є таблиця Excel, як показано нижче, і ви хочете обчислити загальну суму в стовпці C.
Нижче наведена формула, яка дозволить це зробити:
= SUM (SalesData [[#Headers], [Sale]]: [@Sale])
Наведена вище формула може виглядати дещо довгою, але вам не обов’язково писати її самостійно. те, що ви бачите у формулі суми, називається структурованими посиланнями, що є ефективним способом Excel для посилання на певні точки даних у таблиці Excel.
Наприклад, SalesData [[#Заголовки], [Розпродаж]] посилається на заголовок Sale у таблиці SalesData (SalesData - це назва таблиці Excel, яку я дав під час створення таблиці)
А [@Sale] посилається на значення в комірці в тому ж рядку у стовпці Продаж.
Я щойно пояснив це для вашого розуміння, але навіть якщо ви нічого не знаєте про структуровані посилання, ви все одно можете легко створити цю формулу.
Нижче наведено кроки для цього.
- У клітинці C2 введіть = SUM (
- Виберіть комірку B1, яка є заголовком стовпця, що має вартість продажу. Ви можете використовувати мишу або клавіші зі стрілками. Ви помітите, що Excel автоматично вводить структуроване посилання для цієї комірки
- Додайте: (символ двокрапки)
- Виберіть клітинку В2. Excel знову автоматично вставить структуровану посилання для комірки
- Закрийте дужки та натисніть Enter
Ви також помітите, що вам не потрібно копіювати формулу у весь стовпець, таблиця Excel автоматично робить це за вас.
Ще одна чудова особливість цього методу полягає в тому, що у разі додавання нового запису до цього набору даних таблиця Excel автоматично обчислюватиме загальну кількість усіх нових записів.
Хоча ми включили заголовок стовпця у нашу формулу, пам’ятайте, що формула ігнорує текст заголовка і враховує лише дані у стовпці
Розрахунок загальної суми за допомогою Power Query
Power Query - це чудовий інструмент, коли йдеться про з'єднання з базами даних, вилучення даних з кількох джерел та перетворення їх перед тим, як помістити в Excel.
Якщо ви вже працюєте з Power Query, було б ефективніше додати поточні підсумки під час перетворення даних у самому редакторі Power Query (замість того, щоб по -перше отримати дані в Excel, а потім додати поточні підсумки за допомогою будь -якого з наведених вище способів). описані вище методи).
Хоча в Power Query немає вбудованої функції для додавання загальних підсумків (що я хотів би, щоб вона була), ви все ще можете зробити це за допомогою простої формули.
Припустимо, у вас є таблиця Excel, як показано нижче, і ви хочете додати поточні підсумки до цих даних:
Нижче наведено кроки для цього.
- Виберіть будь -яку клітинку в таблиці Excel
- Натисніть на Дані
- На вкладці Отримати та перетворити клацніть піктограму з таблиці/діапазону. Це відкриє таблицю в редакторі Power Query
- [Необов’язково] Якщо ваш стовпець «Дата» ще не відсортовано, натисніть на значок фільтра у стовпці «Дата», а потім натисніть «Сортувати за зростанням»
- Натисніть вкладку Додати стовпець у редакторі Power Query
- У групі Загальні натисніть спадне меню Стовпець індексу (не клацніть піктограму Стовпець індексу, а маленьку нахилену стрілку праворуч біля неї, щоб показати більше опцій)
- Натисніть на опцію "Від 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.
Виконання підсумків у зведених таблицях
Якщо ви хочете додати поточні підсумки до результату зведеної таблиці, ви можете легко це зробити за допомогою вбудованої функції зведених таблиць.
Припустимо, у вас є зведена таблиця, як показано нижче, де я маю дату в одному стовпці, а вартість продажу - в іншому.
Нижче наведено кроки, щоб додати додатковий стовпець, який відображатиме загальну суму продажів за датою:
- Перетягніть поле Продаж і розмістіть його в області Значення.
- Це додасть ще один стовпець зі значеннями Sales
- Натисніть на опцію Сума продажу2 в області Значення
- Натисніть на опцію «Параметри поля значень»
- У діалоговому вікні "Параметри поля значень" змініть користувацьке ім'я на "Загальні підсумки"
- Натисніть на вкладку «Показати значення як»
- У спадному меню «Показати значення як» виберіть опцію «Виконати загальну суму»
- У параметрах базового поля переконайтеся, що вибрано дату
- Натисніть ОК
Вищезазначені кроки змінять другий стовпець продажу на стовпець Загальна сума виконання.
Отже, це деякі з способів, які можна використовувати для розрахунку загальної суми роботи в Excel. Якщо у вас є дані у табличному форматі, ви можете використовувати прості формули, а якщо у вас є таблиця Excel, то ви можете використовувати формули, які використовують структуровані посилання.
Я також розглянув, як обчислити загальну суму за допомогою Power Query та у зведених таблицях.
Сподіваюся, цей підручник був вам корисним.
