Перейти до основного матеріалу

Підсумуйте значення за групами в Excel

Іноді може виникнути вимога підсумовувати значення на основі групи в таблиці. Наприклад, у мене є список продуктів із відповідними сумами в іншому стовпці, тепер я хочу отримати проміжну суму для кожного продукту, як показано нижче. У цьому посібнику будуть представлені деякі формули для вирішення цього завдання в Excel.


Сума значень за групами – проміжні підсумки в таблиці вихідних даних

Тут загальний синтаксис для підсумовування значень на основі групи в Excel має бути таким:

=IF(group_name=cell_above_group_name, “” ,SUMIF(group_range,group_name,sum_range))
  • group_name: осередок з назвою групи, яку потрібно підсумувати;
  • cell_above_group_name: клітинка над назвою групи;
  • group_range: діапазон комірок містить назви груп;
  • sum_range: Діапазон комірок, що відповідають вказаній назві групи, яку потрібно підсумувати.

Якщо дані відсортовано за стовпцем групування, той самий продукт упорядковується разом, як показано на знімку екрана нижче. Щоб отримати проміжні дані за групами, ви можете створювати формули на основі функцій IF і SUMIF.

1. Скопіюйте та вставте таку формулу в порожню клітинку поруч із даними:

=IF(A2=A1,"",SUMIF($A$2:$A$13,A2,$B$2:$B$13))
  • примітки: У формулі:
  • A1 є клітинкою заголовка, і A2 це перша клітинка, яка містить назву продукту, який ви хочете використовувати;
  • A2: A13 список містить назви продуктів, на основі яких ви хочете підвести підсумки;
  • B2: B13 це дані стовпця, які ви хочете отримати проміжний підсумок.

2. Потім перетягніть маркер заповнення до клітинок, у яких ви хочете використовувати цю формулу, і проміжні підсумки обчислюються на основі назви кожного продукту, див. знімок екрана:


Пояснення формули:

=IF(A2=A1,"",SUMIF($A$2:$A$13,A2,$B$2:$B$13))

  • SUMIF($A$2:$A$13,A2,$B$2:$B$13): Ця функція SUMIF сумує лише значення в діапазоні B2:B13, якщо їх відповідні значення в діапазоні A2:A13 дорівнюють критерію A2.
  • IF(A2=A1,"",SUMIF($A$2:$A$13,A2,$B$2:$B$13)): Ця функція IF перевіряє кожне значення в стовпці A, якщо його значення збігається зі значенням у наведеній вище клітинці. Наприклад, якщо значення комірки A2 дорівнює комірці A1, нічого ( “” ) не повернеться, якщо не збігається, буде повернуто результат функції SUMIF.

Сума значень за групами – проміжні підсумки в іншому місці

Якщо значення кожної групи не впорядковані разом і перераховані випадковим чином у стовпці, щоб підсумувати відповідні значення на основі групи або категорії, ви повинні витягти назву кожної групи з назв продуктів, а потім отримати проміжний підсумок на основі цієї назви групи як нижче показано скріншот.

1. Спочатку витягніть унікальне ім’я групи, використовуючи наступну формулу масиву, а потім натисніть Ctrl + Shift + Enter клавіші разом, щоб отримати перший результат.

=INDEX($A$2:$A$13,MATCH(0,COUNTIF($D$1:D1,$A$2:$A$13),0))
  • примітки: У формулі:
  • A2: A13 – діапазон комірок, куди витягуються всі унікальні значення;
  • D1 - комірка над введеною формулою.

2. Потім виберіть клітинку формули та перетягніть маркер заповнення до клітинок, доки не відобразяться всі назви продуктів, дивіться знімок екрана:

3. Тепер ви можете підсумувати значення за назвою групи, яку ви витягли. У цьому випадку тут буде використовуватися функція SUMIF, будь ласка, введіть формулу нижче в порожню клітинку, у цьому прикладі я поміщу її в клітинку E2.

=SUMIF($A$2:$A$13,D2,$B$2:$B$13)

4. Потім перетягніть маркер заповнення вниз, щоб скопіювати цю формулу, щоб повернути загальний порядок інших груп, див. знімок екрана:


Використана відносна функція:

  • SUMIF:
  • Функція SUMIF може допомогти підсумувати клітинки на основі одного критерію.
  • IF:
  • Функція IF перевіряє конкретну умову і повертає відповідне значення, яке ви надаєте для TRUE або FALSE.

Більше статей:

  • Проміжна сума рахунків-фактур за віком
  • Щоб підсумувати суми рахунків-фактур за віком, як показано нижче, може бути звичайним завданням у Excel. У цьому посібнику буде показано, як підрахувати суми рахунків-фактур за віком за допомогою звичайної функції SUMIF.
  • Сума найменших або найнижчих N значень
  • У Excel нам легко підсумувати діапазон комірок за допомогою функції SUM. Іноді вам може знадобитися підсумувати найменші чи нижчі 3, 5 чи n чисел у діапазоні даних, як показано нижче. У цьому випадку SUMPRODUCT разом із функцією SMALL можуть допомогти вам вирішити цю проблему в Excel.

Найкращі інструменти для підвищення продуктивності офісу

Kutools для Excel - допомагає виділитися з натовпу

🤖 Kutools AI Aide: Революціонізуйте аналіз даних на основі: Інтелектуальне виконання   |  Згенерувати код  |  Створення спеціальних формул  |  Аналізуйте дані та створюйте діаграми  |  Викликати функції Kutools...
Популярні функції: Знайдіть, виділіть або визначте дублікати  |  Видалити порожні рядки  |  Об’єднайте стовпці або клітинки без втрати даних  |  Раунд без Формули ...
Супер VLookup: Кілька критеріїв  |  Множинне значення  |  На кількох аркушах  |  Нечіткий пошук...
Adv. Випадаючий список: Легкий спадний список  |  Залежний спадний список  |  Виберіть розкривний список, що вибирається...
Менеджер колонок: Додайте конкретну кількість стовпців  |  Перемістити стовпці  |  Перемкнути статус видимості прихованих стовпців  Порівняти стовпці з Виберіть однакові та різні клітини ...
Особливості: Фокус сітки  |  Перегляд дизайну  |  Велика панель формул  |  Диспетчер робочих книг і аркушів | Бібліотека ресурсів (автотекст)  |  Вибір дати  |  Об’єднайте робочі аркуші  |  Шифрування/розшифрування клітинок  |  Надсилайте листи за списком  |  Супер фільтр  |  Спеціальний фільтр (фільтр жирний/курсив/закреслений...) ...
Топ-15 наборів інструментів12 текст Tools (додати текст, Видалити символи ...)  |  50 + Графік типи (діаграма Ганта ...)  |  40+ Практичний Формули (Розрахуйте вік на основі дня народження ...)  |  19 вставка Tools (Вставте QR-код, Вставити зображення зі шляху ...)  |  12 Перетворення Tools (Числа до слів, Валютна конверсія ...)  |  7 Злиття та розділення Tools (Розширені комбіновані ряди, Розділити клітинки Excel ...)  |  ... і більше

Kutools для Excel має понад 300 функцій, Переконайтеся, що те, що вам потрібно, знаходиться лише на відстані одного кліка...

Опис


Вкладка Office - увімкніть читання та редагування вкладок у Microsoft Office (включаючи Excel)

  • Одна секунда для перемикання між десятками відкритих документів!
  • Щодня зменшуйте сотні клацань мишею, прощайте руку миші.
  • Збільшує вашу продуктивність на 50% під час перегляду та редагування декількох документів.
  • Додає ефективні вкладки в Office (включно з Excel), як у Chrome, Edge та Firefox.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations