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

Листи Excel, що містять великий обсяг інформації, іноді можуть виглядати перевантаженими і навіть важко прочитані. Excel дозволяє групувати дані, щоб легко приховувати і показувати різні розділи листа. До того ж, Ви можете узагальнити різні групи за допомогою команди Проміжний підсумокі надати структуру робочого листа Excel. У цьому уроці ми розглянемо обидва ці інструменти максимально докладно і на наочних прикладах.

Угруповання рядків і стовпців в Excel

Щоб розгрупувати дані в Excel, виділіть згруповані рядки або стовпці, а потім натисніть команду Розгрупувати.

Як приховати та показати групи

Підбиття підсумків в Excel

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

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

Створення проміжного результату

У наступному прикладі ми скористаємося командою Проміжний підсумок, щоб визначити скільки замовлено футболок кожного розміру (S, M, L та XL). В результаті робочий лист Excel набуде структури у вигляді груп по кожному розміру футболок, а потім буде підраховано загальну кількість футболок у кожній групі.

Перегляд груп за рівнями

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

Хоча в цьому прикладі представлено лише три рівні, Excel дозволяє створювати до 8 рівнів вкладеності.

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

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

Згодом необхідність у проміжних підсумках зникає, особливо коли потрібно інакше перегрупувати дані на аркуші Excel. Якщо Ви більше не бажаєте бачити проміжні висновки, їх можна видалити.

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

Функція "Проміжні підсумки()"використовується для обчислення проміжного підсумку (сума, середня, кількість значень тощо) у діапазоні, в якому є приховані терміни.

Особливість функції полягає в тому, що вона призначена для використання спільно з іншими засобами Excel (наприклад, фільтрами).

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

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

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

Для цього можна скористатися функцією Excel = Проміжні підсумки (). Функція =Проміжні.результати() робить розрахунок за відфільтрованим значенням.

У формулі можна задати розрахунок:

• Середній – номер 1 або 101 – для вибірки
• Рахунок значень – номер 3 або 103
• Сума - 9 чи 109
• І т.д.

• Якщо номер функції тризначний, наприклад, 109, функція працює як проміжні підсумки, тобто. розраховує значення за відфільтрованими параметрами.
• Якщо номер однозначний або двозначний 2 або 11, то формула проміжні.підсумки() працює як стандартна формула за обраною функцією.

Перейдемо до наших заходів:

У нас є вивантаження з бази по наступним стовпцям:

• Ситуація
• Дії
• Кількість клієнтів
• Обсяг_Продаж_До
• Об'єм_Продаж_План
• Обсяг_Продаж_Факт
• Витрати_План
• Витрати_Факт

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

Для розрахунку проміжних підсумків за обраними параметрами введемо =ПРОМІЖНІ.ПІДСУМКИ(109;RC:RC), де

• 109 - функція суми для розрахунку проміжних підсумків;
• RC:RC – посилання діапазон сумування.

Тепер, відфільтрувавши стовпець «Ситуація» – «Ситуація 4» та «Дія» – Дія 2 та 3, ми отримаємо перерахунок проміжних підсумків за кожним із показників і можемо їх порівняти з показниками «Разом»:

В результаті:видно, що % приросту продажів по відфільтрованим заходам вище, ніж у всіх, причому планували результати отримати нижче, ніж у підсумку, а отримали вище. Тобто. спрацювали краще, ніж планували і краще, ніж у всіх заходах.

Формула Excel =проміжні.результати() – відмінний інструмент для розрахунку показників за відфільтрованими параметрами.

Якщо є питання – звертайтесь!

Приєднуйся до нас!

Завантажуйте безкоштовні програмидля прогнозування та бізнес-аналізу:

• Novo Forecast Lite- автоматичний розрахунок прогнозув Excel.
• 4analytics - ABC-XYZ-аналізта аналіз викидів у Excel.
• Qlik Sense Desktop та QlikViewPersonal Edition - BI-системи для аналізу та візуалізації даних.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Угруповання таблиць

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Рівні груп

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

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

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

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

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

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

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

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

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

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

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

У цій статті описано синтаксис формули та використання функції ПРОМІЖНІ ВИСНОВКИу Microsoft Excel.

Опис

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

Синтаксис

Аргументи функції ПРОМІЖНІ.ПІДСУМКИ описані нижче.

Номер_функції- Обов'язковий аргумент. Число від 1 до 11 або від 101 до 111, що означає функцію, що використовується для розрахунку проміжних підсумків. Функції з 1 по 11 враховують рядки, приховані вручну, тоді як функції з 101 по 111 пропускають такі рядки; відфільтровані осередки завжди виключаються.

Примітки

Якщо вже є формули підбиття підсумків усередині аргументів "посилання1;посилання2;..." (вкладені підсумки), ці вкладені підсумки ігноруються, щоб уникнути подвійного підсумовування.

Для констант "номер_функції" від 1 до 11 функція ПРОМІЖНІ. ПІДСУМКИ враховує значення рядків, прихованих за допомогою команди Приховати рядки(меню Формат, підміню Приховати або відобразити) у групі Осередкина вкладці Головнау настільному додатку Excel. Ці константи використовуються для отримання проміжних підсумків з урахуванням прихованих чи неприхованих чисел списку. Для констант "номер_функції" від 101 до 111 функція ПРОМІЖНІ.ПІДСУМКИ виключає значення рядків, прихованих за допомогою команди Приховати рядки. Ці константи використовують для отримання проміжних підсумків з урахуванням лише неприхованих чисел списку.

Функція ПРОМІЖНІ.ПІДСУМКИ виключає всі рядки, не включені в результат фільтра, незалежно від використовуваного значення константи "номер_функції".

Функція ПРОМІЖНІ.ПІДСУМКИ застосовується до стовпців даних або вертикальних наборів даних. Вона не призначена для рядків даних чи горизонтальних наборів даних. Так, при визначенні проміжних підсумків горизонтального набору даних за допомогою значення константи "номер_функції" від 101 і вище (наприклад, ПРОМІЖНІ.ПІДСУМКИ(109;B2:G2)), приховування стовпця не вплине на результат. Однак на нього вплине приховування рядка при підведенні проміжного підсумку вертикального набору даних.

приклад

Скопіюйте зразок даних з наступної таблиці та вставте їх у комірку A1 нового листа Excel. Щоб відобразити результати формул, виділіть їх та натисніть клавішу F2, а потім - клавішу ENTER. За потреби змініть ширину стовпців, щоб побачити всі дані.

У цій статті описано синтаксис формули та використання функції ПРОМІЖНІ ВИСНОВКИу Microsoft Excel.

Опис

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

Синтаксис

Аргументи функції ПРОМІЖНІ.ПІДСУМКИ описані нижче.

Номер_функції- Обов'язковий аргумент. Число від 1 до 11 або від 101 до 111, що означає функцію, що використовується для розрахунку проміжних підсумків. Функції з 1 по 11 враховують рядки, приховані вручну, тоді як функції з 101 по 111 пропускають такі рядки; відфільтровані осередки завжди виключаються.

Примітки

Якщо вже є формули підбиття підсумків усередині аргументів "посилання1;посилання2;..." (вкладені підсумки), ці вкладені підсумки ігноруються, щоб уникнути подвійного підсумовування.

Для констант "номер_функції" від 1 до 11 функція ПРОМІЖНІ. ПІДСУМКИ враховує значення рядків, прихованих за допомогою команди Приховати рядки(меню Формат, підміню Приховати або відобразити) у групі Осередкина вкладці Головнау настільному додатку Excel. Ці константи використовуються для отримання проміжних підсумків з урахуванням прихованих чи неприхованих чисел списку. Для констант "номер_функції" від 101 до 111 функція ПРОМІЖНІ.ПІДСУМКИ виключає значення рядків, прихованих за допомогою команди Приховати рядки. Ці константи використовують для отримання проміжних підсумків з урахуванням лише неприхованих чисел списку.

Функція ПРОМІЖНІ.ПІДСУМКИ виключає всі рядки, не включені в результат фільтра, незалежно від використовуваного значення константи "номер_функції".

Функція ПРОМІЖНІ.ПІДСУМКИ застосовується до стовпців даних або вертикальних наборів даних. Вона не призначена для рядків даних чи горизонтальних наборів даних. Так, при визначенні проміжних підсумків горизонтального набору даних за допомогою значення константи "номер_функції" від 101 і вище (наприклад, ПРОМІЖНІ.ПІДСУМКИ(109;B2:G2)), приховування стовпця не вплине на результат. Однак на нього вплине приховування рядка при підведенні проміжного підсумку вертикального набору даних.

приклад

Скопіюйте зразок даних з наступної таблиці та вставте їх у комірку A1 нового аркуша Excel. Щоб відобразити результати формул, виділіть їх та натисніть клавішу F2, а потім - клавішу ENTER. За потреби змініть ширину стовпців, щоб побачити всі дані.