Note: The other languages of the website are Google-translated. Back to English

Підсумуйте значення за групами в 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 для Excel надає 300 потужних розширених функцій (об’єднання робочих книг, підсумовування за кольором, розділення вмісту клітинок, перетворення дати тощо) і заощаджує 80% вашого часу.

  • Розроблений для 1500 сценаріїв роботи, допомагає вирішити 80% проблем Excel.
  • Щодня зменшуйте тисячі клацань клавіатури та миші, знімайте втомлені очі та руки.
  • Станьте експертом Excel за 3 хвилини. Більше не потрібно згадувати будь-які болісні формули та коди VBA.
  • Безкоштовна 30-денна безкоштовна пробна версія. 60-денна гарантія повернення грошей. Безкоштовне оновлення та підтримка протягом 2 років.
Стрічка Excel (із встановленим Kutools для Excel)

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

  • Одна секунда для перемикання між десятками відкритих документів!
  • Щодня зменшуйте сотні клацань мишею, прощайте руку миші.
  • Збільшує вашу продуктивність на 50% під час перегляду та редагування декількох документів.
  • Приносить ефективні вкладки в Office (включаючи Excel), як і Chrome, Firefox та New Internet Explorer.
Знімок екрана Excel (із встановленою вкладкою Office)
Коментарі (0)
Поки немає оцінок. Оцініть першим!
There are no comments posted here yet
Залишайте свої коментарі
Публікація як гість
×
Оцініть цю публікацію:
0   Персонажі
Рекомендовані місця

Слідуй за нами

Copyright © 2009 - WWW.extendoffice.com. | Всі права захищені. На основі ExtendOffice. | Карта сайту
Microsoft та логотип Office є товарними знаками або зареєстрованими товарними знаками Microsoft Corporation у США та / або інших країнах.
Захищений Sectigo SSL