Обчислення проміжних підсумків в Excel з прикладами

2

Функція обчислення проміжних підсумків в Excel – це простий і зручний спосіб аналізу даних певного стовпця або таблиці.

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

До речі, щоб ефективніше працювати з таблицями можете ознайомитися з нашим матеріалом Гарячі клавіші Excel — найнеобхідніші варіанти.

Зміст:

Аркуші документа Excel містять велику кількість інформації. Часто переглядати і шукати дані незручно.

Щоб спростити життя користувачів, розробники програми створили функції для групування даних.

З їх допомогою можна легко шукати потрібні поля та стовпці, а також приховувати інформацію, яка в цей момент не потрібна.

Узагальнити кілька груп листа можна за допомогою функції «Проміжні підсумки».

Розглянемо детальніше принцип роботи цієї функції і її реалізацію у всіх версіях табличного процесору Excel.

Синтаксис функції

У програмі Excel опція відображається у вигляді ПРОМІЖНІ.ПІДСУМКИ (№; посилання 1 посилання 2; ссылка3;…;посиланняN), де номер – позначення функції, посилання – стовпець, по якому підводиться підсумок.

Поля посилань можуть включати в себе не більше 29 посилань і інтервалів, що використовуються при проведенні проміжних підсумків.

Номером може бути будь-яка цифра від 1 до 11. Це число показує програмі, яку функцію слід використовувати для підрахунку підсумків всередині вибраного списку.

Синтаксис опції підсумків зазначено в таблиці:

Синтаксис

Дія

1-AVERAGE

Визначення середнього арифметичного двох і більше значень.

2-РАХУНОК

Розрахунок кількості чисел, які представлені в списку аргументів.

3-COUNTA

Підрахунок всіх переданих аргументів.

4-МАКС

Показ максимального значення з визначеного набору чисел.

5-ХВ

Показ мінімального значення з визначеного набору чисел.

6-PRODUCT

Перемноження значень і повернення результату.

7-СТАНД ВІДХИЛ

Аналіз стандартного відхилення кожної вибірки.

8-СТАД ВІДХИЛ П

Аналіз відхилення загальної сукупності даних.

9-СУМ

Повертає суму вибраних чисел.

10-ДИСПЛЕ

Аналіз вибіркової дисперсії.

11-VARP

Аналіз загальної дисперсії.

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

Читайте також:

Як закріпити рядок в Excel — Докладні інструкції

Випадаючий список в Excel — Інструкція по створенню

Зведені таблиці Excel — Приклади створення

Практичні поради — Як об’єднати комірки в Excel

Приклад реалізації проміжних підсумків

Виконання проміжних підсумків включає в себе такі операції:

  • Групування даних;
  • Створення підсумків;
  • Створення рівнів для груп.

Далі функція проміжних підсумків зберігається в документі або ж віддаляється для створення нових варіантів підсумкової звітності.

Групування таблиць

Група таблиці – це перелік взаємопов’язаних елементів Excel. Зручність цієї функції полягає в тому, що при перенесенні одного рядка зі списку згрупованих елементів, на нове місце буде переміщена вся група. Аналогічним чином проводяться розрахунки із застосуванням стандартних функцій – для групи разом.

Виконуйте інструкції, щоб згрупувати рядка. У нашому випадку, ми пов’язуємо стовпці А, В, С:

1 За допомогою мишки виділіть стовпці А, В, С, як показано на малюнку нижче.

Обчислення проміжних підсумків в Excel з прикладами

Рис.2 – виділення декількох стовпців

 

2 Тепер, зберігаючи виділення, відкрийте на панелі інструментів програми полі «Дані». Потім в правій частині вікна опцій знайдіть іконку «Структура» і натисніть на неї;

3У списку натисніть на «Групувати». Якщо ви помилилися на етапі виділення потрібних стовпчиків таблиці, натисніть на «Розгрупувати» і повторіть операцію заново.

Обчислення проміжних підсумків в Excel з прикладами

Рис.3 – групування інформації

4 У новому вікні виберіть пункт «Рядка» і натисніть «ОК»;

Обчислення проміжних підсумків в Excel з прикладами

Рис. 4 – групування рядків

5 Тепер знову виділіть стовпці А, В, С і проведіть групування, але вже по стовпцях:

Обчислення проміжних підсумків в Excel з прикладами

Рис.5 – створення групи стовпців

В результаті виконання дії, всі вибрані раніше рядки утворюють нову групу елементів. Біля стовпців і рядків з’явиться панель управління групами. З її допомогою ви зможете приховати деталі або ж переглянути приховані елементи. Для цього натискайте на знаки «-» і «+» зліва від рядків або зверху від стовпчиків.
Обчислення проміжних підсумків в Excel з прикладами

Рис.6 – панель управління групами

Створення проміжних підсумків

Опція проміжних підсумків, завдяки використанню груп і стандартних одинадцяти функції (таблиця 1), дозволяє швидко проводити підсумки по заповненим даними таблиці.

Розглянемо практичне використання цієї функції.

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

Результатом виконання дії буде поява груп, які відсортовані по кожному розміром речі. Дотримуйтесь інструкції:

  • Визначте, для яких даних потрібно проводить підсумкові обчислення. У нашому випадку це стовпець «Розмір». Його вміст потрібно відсортувати. Сортування проводиться від більшого елемента до меншого. Виділяємо стовпець «Розмір»;

  • Далі необхідно перейти у вкладку «Дані» на панелі інструментів;

  • Знайдіть поле «Сортування» і натисніть на нього;

  • У вікні виберіть пункт «Сортувати в діапазоні вказних значень» і натисніть на клавішу «ОК».

Обчислення проміжних підсумків в Excel з прикладами

Рис.7 – сортування вмісту стовпця

  • В результаті, всі розміри будуть відображатися за спаданням. Тільки після цього ми можемо проводити проміжні підсумки, які зачіпають дані про розмірній сітці замовлених речей.
Обчислення проміжних підсумків в Excel з прикладами

Рис.8 – результат сортування

Тепер перейдемо до реалізації функції «Проміжний підсумок»:

  • На панелі інструментів програми відкрийте полі «Дані»;
  • Виберіть плитку «Структура»;
  • Натисніть на «Проміжні підсумки»;
Обчислення проміжних підсумків в Excel з прикладами

Рис.9 – розташування опції на панелі інструментів

  • Далі відкриється вікно настройки відображення проміжних підсумків. У першому полі виберіть ім’я стовпця, для якого будуть підводитися підсумки. У нашому випадку це «Розмір». При кожній зміні в цьому полі дані будуть змінюватися і в підсумках;

  • У полі «Операція» виберіть тип функції, яка застосовується до всіх елементів вибраного в першому поле стовпця. Для прорахунку розмірів необхідно використовувати підрахунок кількості;

  • В списку «Додати підсумки по» ви вибираєте той стовпець, в якому будуть відображатися проміжні підсумки. Вибираємо пункт «Розмір»;

  • Після налаштування всіх параметрів ще раз перевірте введені значення і натисніть ОК.

Обчислення проміжних підсумків в Excel з прикладами

Рис.10 – обробка результату

В результаті, на аркуші з’явиться таблиця в згрупованому за розмірами вигляді. Тепер дуже легко подивитися всі замовлені речі розміру Small, Extra Large і так далі.

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

Рис.11 – результат виконання функції

Як видно з малюнка вище, всі підсумки виводяться між показаними групами в новій сходинці.

Підсумок для одягу з розміром Small – 5 штук, для одягу з розміром Extra Large – 2 штуки. Під таблицею показується і загальна кількість елементів.

Читайте також:

Ексель (Excel) для чайників: робота з таблицями, графіками, сортуванням даних і математичними розрахунками

Excel помилка при направленні команди додатком: 6 кращих способів вирішити проблему

Функція VLOOKUP в Excel – приклади використання та поради

Як швидко порахувати відсотки в Excel (Ексель) — графічна інструкція

Рівні груп

Переглядати групи можна ще й за рівнями.

Цей спосіб відображення дозволяє зменшити кількість візуальної інформації на екрані ви побачите тільки потрібні дані підсумків – кількість одягу кожного розміру і загальна кількість.

Натисніть на перший другий або третій рівень в панелі керування групами. Вибрати найбільш відповідний варіант подання інформації:

Обчислення проміжних підсумків в Excel з прикладами

Рис.12 – перегляд другого рівня

 Перший рівень відображає лише загальну кількість речей, другий – кількість кожного розміру і підсумкове число, третій – розворот всіх даних аркуша.

Видалення підсумків

Через деякий час в таблицю будуть вноситися інші дані, тому з’явиться необхідність у повторній групування інформації.

Старі проміжні підсумки і тип їх розрахунку буде неактуальним, тому їх потрібно видалити.

Щоб видалити підсумок, виберіть вкладку «Дані»«Структура»«Проміжні підсумки».

У вікні, натисніть на клавішу «Прибрати всі» і підтвердіть дію кнопкою «ОК».

Обчислення проміжних підсумків в Excel з прикладами

Рис.13 – видалення даних

Тематичні відеоролики: