Вставка проміжних підсумків у списку даних на аркуші

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

Важливо: Проміжні підсумки не підтримуються у таблицях Excel. Команда Проміжні підсумки буде відображатися неактивно, якщо ви працюєте з таблицею Excel. Щоб додати проміжні підсумки до таблиці, необхідно спочатку перетворити таблицю на звичайний діапазон даних, а потім додати проміжний результат. Зверніть увагу, що це призведе до видалення даних усіх функцій таблиці, крім форматування таблиці. Або можна створити зведену таблицю.

При вставці проміжних підсумків:

• Проміжні підсумки обчислюються за допомогою підсумкова функція, наприклад Sum або Average, за допомогою функції ПРОМІЖНІ ПІДСУМКИ. Для кожного шпальти можна відобразити кілька типів сумарних функцій.
• Загальні підсумки є похідними від деталі, а чи не з значень проміжних результатів. Наприклад, якщо ви використовуєте функцію Зведення по середньому значення, у рядку із загальним підсумком відображається середнє значення всіх рядків відомостей у списку, а не середнє значення у рядках проміжних підсумків.

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

Примітка: При фільтрації даних, що містять проміжні підсумки, проміжні підсумки можуть здатися прихованими.Щоб знову відобразити їх, очистіть всі фільтри Додаткові відомості про використання фільтрів див. у статті Короткий посібник. Фільтрування даних за допомогою автофільтра.

1. Переконайтеся, що кожен стовпець у діапазоні даних, для якого потрібно обчислити проміжні підсумки, має мітку в першому рядку, містить аналогічні факти в кожному стовпці і що діапазон не містить порожніх рядків або стовпців.
2. Виділіть комірку в діапазоні.
3. Виконайте одну з наведених нижче дій. Вставка одного рівня проміжних підсумків Можна вставити один рівень проміжних підсумків групи даних, як показано в наступному прикладі.

1. Для сортування стовпця, що містить дані, за якими потрібно виконати угруповання, виділіть цей стовпець і на вкладці Дані у групі Сортування та фільтр натисніть кнопку Сортування від А до Я або Сортування від Я до А.
2. На вкладці Дані у групі Структура клацніть Проміжний підсумок.

Вставка вкладених рівнів проміжних підсумків

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

1. При кожній зміні у зовнішньому стовпці Регіон.

2. . проміжні підсумки продажів для цього регіону та при кожній зміні внутрішнього стовпця Sport.

1. Для сортування стовпця, що містить дані, за якими потрібно виконати угруповання, виділіть цей стовпець і на вкладці Дані у групі Сортування та фільтр натисніть кнопку Сортування від А до Я або Сортування від Я до А.
2. Вставте зовнішні проміжні результати. Вставка зовнішніх проміжних підсумків
   1. На вкладці Дані у групі Структура клацніть Проміжний підсумок.

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

   поряд із номерами рядків. Використовуйте символи

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

   Проміжні підсумки в Excel з прикладами функцій

   Підбити проміжні підсумки у таблиці Excel можна за допомогою вбудованих формул та відповідної команди у групі «Структура» на вкладці «Дані».

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

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

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

   Необхідно підрахувати виручку від окремих груп товарів. Якщо використовувати фільтр, можна отримати однотипні записи за заданим критерієм відбору. Але значення доведеться підраховувати вручну. Тому скористаємось іншим інструментом Microsoft Excel – командою «Проміжні підсумки».

   Щоб функція видала правильний результат, перевірте діапазон на відповідність наступним умовам:

   • Таблиця оформлена у вигляді простого списку чи бази даних.
   • Перший рядок – назви стовпців.
   • У шпальтах містяться однотипні значення.
   • У таблиці немає порожніх рядків чи стовпців.
   1. Відсортуємо діапазон за значенням першого стовпця – однотипні дані мають бути поруч.
   2. Виділяємо будь-яку комірку в таблиці. Вибираємо на стрічці вкладку "Дані". Група "Структура" - команда "Проміжні підсумки".
   3. Заповнюємо діалогове вікно "Проміжні підсумки".У полі «При кожній зміні» вибираємо умову для відбору даних (у прикладі – «Значення»). У полі "Операція" призначаємо функцію ("Сума"). У полі «Додати до» слід позначити стовпці, до значень яких застосовується функція.
   4. Закриваємо діалогове вікно, натиснувши кнопку ОК. Вихідна таблиця набуває наступного вигляду:

   Якщо згорнути рядки в підгрупах (натиснути на «мінуси» зліва від номерів рядків), то отримаємо таблицю лише з проміжних підсумків:

   При кожній зміні стовпця «Назва» перераховується проміжний результат у стовпці «Продажі».

   Щоб за кожним проміжним підсумком розрив сторінки, у діалоговому вікні поставте галочку «Кінець сторінки між групами».

   Щоб проміжні дані відображалися НАД групою, зніміть умову «Підсумки під даними».

   Команда проміжні підсумки дозволяє одночасно використовувати кілька статистичних функцій. Ми вже призначили операцію "Сума". Додамо середні значення продажу кожної групи товарів.

   Знову викликаємо меню "Проміжні підсумки". Знімаємо галочку "Замінити поточні". У полі "Операція" вибираємо "Середнє".

   Формула "Проміжні підсумки" в Excel: приклади

   Функція «ПРОМІЖНІ.ПІДСУМКИ» повертає проміжний підсумок до списку або бази даних. Синтаксис: номер функції, посилання 1; посилання 2; .

   Номер функції – число від 1 до 11, що вказує статистичну функцію до розрахунку проміжних результатів:

   1. - СРЗНАЧ (середнє арифметичне);
   2. - РАХУНОК (кількість осередків);
   3. - РАХУНОК (кількість непустих осередків);
   4. - МАКС (максимальне значення в діапазоні);
   5. - МІН (мінімальне значення);
   6. - ВИРОБ (твір чисел);
   7. - СТАНДОТКЛОН (стандартне відхилення за вибіркою);
   8. - СТАНДОТКЛОНП (стандартне відхилення за генеральною сукупністю);
   9. - СУМ;
   10. - ДІСП (дисперсія за вибіркою);
   11. - ДІСПР (дисперсія по генеральній сукупності).

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

   Особливості роботи функції:

   • видає результат за явними та прихованими рядками;
   • виключає рядки, що не включені у фільтр;
   • вважає лише у стовпцях, для рядків не підходить.

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

   1. Створюємо додатковий рядок для відображення проміжних результатів. Наприклад, «сума відібраних значень».
   2. Увімкнемо фільтр. Залишимо у таблиці лише дані за значенням «Обідня група «Амадіс»».
   3. У комірку В2 введемо формулу: .

   Формула для середнього значення проміжного підсумку діапазону (для передпокою «Ретро»): .

   Формула для максимального значення (для спалень): .

   Проміжні підсумки у зведеній таблиці Excel

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

   1. При формуванні зведеного звіту вже закладено автоматичну функцію підсумовування розрахунку підсумків.
   2. Щоб застосувати іншу функцію, у розділі "Робота зі зведеними таблицями" на вкладці "Параметри" знаходимо групу "Активне поле". Курсор повинен стояти в комірці стовпця, до значень якого буде застосовуватися функція. Натискаємо кнопку "Параметри поля". У меню вибираємо «інші». Призначаємо необхідну функцію для проміжних результатів.
   3. Для виведення на екран підсумків за окремими значеннями використовуйте кнопку фільтра у правому куті назви стовпця.

   У меню "Параметри зведеної таблиці" ("Параметри" - "Зведена таблиця") доступна вкладка "Підсумки та фільтри".

   Таким чином, для відображення проміжних підсумків у списках Excel застосовується три способи: команда групи "Структура", вбудована функція та зведена таблиця.

   • Створити таблицю
   • Форматування
   • Функції Excel
   • Формули та діапазони
   • Фільтр та сортування
   • Діаграми та графіки
   • Зведені таблиці
   • Друк документів
   • Бази даних та XML
   • Можливості Excel
   • Налаштування параметри
   • Уроки Excel
   • Макроси VBA
   • Завантажити приклади