Завантажте зразок робочої книги
Цей підручник продемонструє, як обчислювати проміжні підсумки за групами за допомогою функції SUMIFS в Excel та Google Таблицях.
Проміжна таблиця за категоріями або групами
По -перше, ми продемонструємо, як створити динамічну підсумкову таблицю проміжних підсумків з діапазону даних або в Excel 365, або в Таблицях Google.
Ми використовуємо функцію UNIQUE та функцію SUMIFS для автоматичного проміжного підсумку Кількість продуктів автор: Група продуктів:
| 1 | = SUMIFS (C3: C11, B3: B11, E3) |
Для створення цієї таблиці проміжних підсумків ми підсумовуємо стандартну програму функції SUMIFS Кількість продуктів які відповідають кожному Група продуктів. Однак, перш ніж це стане можливим, нам потрібно створити список унікальних Групи продуктів. Користувачі Microsoft Excel 365 та Google Таблиць мають доступ до функції UNIQUE для створення динамічного списку унікальних значень із діапазону клітинок. У цьому прикладі ми додаємо до комірки E3 таку формулу:
| 1 | = Унікальний (B3: B11) |
Коли ця формула введена, під коміркою автоматично створюється список для відображення всіх унікальних значень, знайдених у Група продуктів діапазон даних. У цьому прикладі список поширився на E3: E5, щоб показати всі 3 унікальних Група продуктів цінності.
Це функція динамічного масиву, де розмір списку результатів визначати не потрібно, і він автоматично зменшуватиметься та зростатиме зі зміною значень вхідних даних.
Зауважте, що в Excel 365 функція UNIQUE не чутлива до регістру, але в Таблицях Google так. Розглянемо список {“А”; «А»; "В"; “C”}. Вихід функції UNIQUE залежить від програми:
- {“А”; "В"; “C”} в Excel 365
- {“А”; «А»; "В"; “C”} у Google Таблицях
Якщо ви використовуєте версію Excel до Excel 365, вам доведеться застосувати інший підхід. Про це йдеться у наступному розділі.
Таблиця проміжних підсумків за категоріями або групами - до Excel 365
Якщо ви використовуєте версію Excel до Excel 365, функція UNIQUE недоступна для використання. Щоб повторити таку саму поведінку, можна об’єднати функцію INDEX та функцію MATCH з функцією COUNTIF, щоб створити формулу масиву для створення списку унікальних значень із діапазону клітинок:
| 1 | {= ІНДЕКС ($ B $ 3: $ B $ 11, МАТЧ (0, COUNTIF ($ E $ 2: E2, $ B $ 3: $ B $ 11), 0))} |
Для того, щоб ця формула функціонувала, фіксовані посилання на клітинки повинні бути написані ретельно, при цьому функція COUNTIF посилається на діапазон $ E $ 2: E2, який є діапазоном, починаючи від E2 до клітинки над коміркою, що містить формулу.
Формулу також потрібно ввести як формулу масиву, натиснувши CTRL + SHIFT + ENTER після її написання. Ця формула є а Формула масиву з 1 клітиною, які потім можна скопіювати в комірки E4, E5 тощо. Не вводьте це як формулу масиву для всього діапазону E3: E5 за одну дію.
Точно так само, як і в попередньому прикладі, функція SUMIFS потім використовується для проміжного підсумку Кількість продуктів автор: Група продуктів:
| 1 | = SUMIFS (C3: C11, B3: B11, E3) |
Сума за категоріями або групами - проміжні підсумки в таблицях даних
Як альтернативу методу зведеної таблиці, показаному вище, ми можемо додавати проміжні підсумки безпосередньо до таблиці даних. Ми продемонструємо це за допомогою функцій IF разом із функцією SUMIFS для додавання Проміжний підсумок за групами до вихідної таблиці даних.
| 1 | = IF (B3 = B2, "", SUMIFS (C3: C11, B3: B11, B3)) |
У цьому прикладі використовується функція SUMIFS, вкладена у функцію IF. Розберемо приклад на кроки:
Щоб додати підсумкову статистику безпосередньо до таблиці даних, ми можемо використовувати функцію SUMIFS. Ми починаємо з підсумовування Кількість продуктів які відповідають відповідним Група продуктів:
| 1 | = SUMIFS (C3: C11, B3: B11, B3) |
Ця формула дає проміжне підсумкове значення для кожного рядка даних. Показувати проміжні підсумки лише в першому рядку даних кожного Група продуктів, ми використовуємо функцію IF. Зауважте, що дані вже потрібно сортувати за Група продуктів щоб переконатися, що проміжні підсумки відображаються правильно.
| 1 | = IF (B3 = B2, "", SUMIFS (C3: C11, B3: B11, B3)) |
Функція IF порівнює кожен рядок даних Група продуктів значення з рядком даних над ним, і якщо вони мають однакове значення, воно виводить порожню клітинку (“”).
Якщо Група продуктів значення різні, відображається сума. Таким чином, кожен Група продуктів сума відображається лише один раз (у рядку її першої інстанції).
Сортування наборів даних за групами
Якщо дані ще не відсортовані, ми все ще можемо використовувати ту саму формулу для проміжного підсумку.
Набір даних вище не відсортовано Група продуктів, так що Проміжний підсумок за групами стовпець відображає кожен проміжний підсумок більше одного разу. Щоб отримати дані у потрібному форматі, ми можемо вибрати таблицю даних і натиснути «Сортувати від А до Я».
Блокування посилань на клітинки
Щоб полегшити читання наших формул, ми показали деякі формули без заблокованих посилань на клітинки:
| 1 | = IF (B3 = B2, "", SUMIFS (C3: C11, B3: B11, B3)) |
Але ці формули не працюватимуть належним чином при копіюванні та вставці в інше місце у файлі. Замість цього, ви повинні використовувати заблоковані посилання на клітинки, як це:
| 1 | = IF (B3 = B2, "", SUMIFS ($ C $ 3: $ C $ 11, $ B $ 3: $ B $ 11, B3)) |
Прочитайте нашу статтю про блокування посилань на клітинки, щоб дізнатися більше.
Використання зведених таблиць для відображення проміжних підсумків
Щоб зняти вимогу попередньої сортування даних за Група продуктівзамість цього ми можемо використовувати можливості зведених таблиць для узагальнення даних. Зведені таблиці автоматично обчислюють проміжні підсумки та відображають підсумки та проміжні підсумки у кількох різних форматах.
Підсумок за категоріями або групами в Таблицях Google
Ці формули працюють так само в Google Таблицях, як і в Excel. Однак функція UNIQUE чутлива до регістру в Google Таблицях.
