Якщо ваш аркуш Excel містить приховані рядки, відфільтровані дані або згруповані дані, використовуйте функцію Excel SUBTOTAL. Функція SUBTOTAL може включати або виключати приховані значення в розрахунках. На додаток до знаходження загальної суми групи даних, Excel може обчислити середнє, максимальне, мінімальне, стандартне відхилення та дисперсію ваших даних. Ось як вставити проміжні підсумки в Excel. Інструкції в цій статті стосуються Excel для Microsoft 365, Excel 2019 та Excel 2016.
Змістовий покажчик
Синтаксис функції SUBTOTAL
Використовуйте функцію SUBTOTAL в Excel, щоб різними способами підсумовувати значення на аркуші. Особливо корисно, коли ваш аркуш містить приховані рядки, які ви хочете включити в розрахунок. Синтаксис функції SUBTOTAL: ПІДЗВІТ (номер_функції, ref1, ref2,…)
номер_функції Необхідний аргумент, який вказує тип математичної операції, яку слід використовувати для проміжної суми. Функція SUBTOTAL може додавати числа, обчислювати середнє значення вибраних чисел, знаходити максимальне та мінімальне значення в діапазоні, підраховувати кількість значень у вибраному діапазоні тощо. Функція SUBTOTAL ігнорує комірки, що не містять даних, і комірки з нечисловими значеннями. Цей аргумент є числом і залежить від того, чи хочете ви включити приховані рядки в результат або виключити приховані рядки з результату. Ці рядки можуть бути приховані вручну або приховані фільтром. До аргументів function_num належать:
| Завдання функції | номер_функції | номер_функції |
|---|---|---|
| (включає приховані значення) | (виключає приховані значення) | |
| СЕРЕДНІЙ | 1 | 101 |
| РАХУВАТИ | 2 | 102 |
| КОНТА | 3 | 103 |
| МАКС | 4 | 104 |
| ХВ | 5 | 105 |
| ПРОДУКТ | 6 | 106 |
| СТДЕВ | 7 | 107 |
| СТДЕВП | 8 | 108 |
| СУММА | 9 | 109 |
| VAR | 10 | 110 |
| VARP | 11 | 111 |
Аргументи посилання function_num з 1 по 11 включають значення лише у прихованих рядках, коли використовується команда Hide для приховування рядків. При використанні команди «Фільтр» обчислення ПІДЗВІТКИ не включають приховані результати фільтрування. ref1 необхідний аргумент. Це клітинки, що використовуються для обчислення результатів вибраного аргументу function_num. Цей аргумент може бути значенням, однією коміркою або діапазоном комірок. ref2,… аргументи необов’язкові. Це додаткові комірки, які включаються в обчислення.
Використовуйте функцію SUBTOTAL із прихованими рядками
Функції Excel можна вводити вручну або за допомогою діалогового вікна “Аргументи функцій”. Щоб проілюструвати, як ввести функцію вручну за допомогою рядка формул, у наступному прикладі використовується аргумент COUNT function_num для підрахунку кількості значень у видимих рядках, а також у видимих і прихованих рядках. Щоб використовувати функцію SUBTOTAL для підрахунку кількості рядків на аркуші:
-
Почніть з аркуша, який містить кілька рядків даних.
-
Виділіть комірку, яка буде містити кількість видимих рядків.
-
На панелі функцій введіть = ПІДЗВІТ. Під час введення текст Excel пропонує функцію. Двічі клацніть на ЗАГАЛЬНО функція. Щоб скористатися діалоговим вікном “Аргументи функцій”, щоб увійти до функції ПІДГРУЗКА, перейдіть до Формули і виберіть Math & Trig > ЗАГАЛЬНО.
-
У спадному меню, що з’явиться, двічі клацніть на 102 – КОЛ аргумент_ім’я_функції.
-
Введіть кому (,).
-
На робочому аркуші виберіть клітинки, які потрібно включити у формулу.
-
Натисніть Введіть щоб побачити результат у комірці, яку ви вибрали на кроці 2.
-
Виділіть клітинку, яка буде містити кількість видимих і прихованих рядків.
-
На панелі функцій введіть = ПІДЗВІТ. Під час введення текст Excel пропонує функцію. Двічі клацніть на ЗАГАЛЬНО функція.
-
У спадному меню, що з’явиться, двічі клацніть на 2 – КОЛИЧКА аргумент function_num, потім введіть кому (,).
-
На робочому аркуші виділіть комірки, які потрібно включити у формулу, а потім натисніть Введіть.
-
Сховати кілька рядків даних. У цьому прикладі були приховані рядки з обсягами продажів менше 100 000 доларів.
Використовуйте функцію SUBTOTAL із відфільтрованими даними
Використання функції SUBTOTAL для відфільтрованих даних ігнорує дані в рядках, видалених фільтром. Щоразу, коли критерії фільтра змінюються, функція перераховується, щоб показати проміжні підсумки для видимих рядків. Щоб використовувати функцію SUBTOTAL, щоб побачити різницю в результатах обчислення під час фільтрування даних:
-
Створіть ПІДСУМНІ формули. Наприклад, створіть формули для визначення проміжних і середніх значень відфільтрованих даних. Неважливо, використовуєте ви аргумент function_num для видимих або прихованих рядків. Обидва аргументи дають однаковий результат у відфільтрованих даних.
-
Виберіть будь-яку комірку в наборі даних.
-
Йти до Додому, потім виберіть Сортувати та фільтрувати > Фільтр.
-
Використовуйте стрілки спадного меню для фільтрування даних робочого аркуша.
-
Зверніть увагу, як значення змінюються кожного разу, коли ви обираєте різні критерії фільтрації.
Використовуйте функцію SUBTOTAL із згрупованими даними
Коли дані групуються, існує спосіб застосувати функцію SUBTOTAL до кожної окремої групи, а потім обчислити загальну суму для всього набору даних.
-
Виберіть будь-яку комірку в наборі даних.
-
Виберіть Дані > Проміжна сума щоб відкрити Проміжна сума діалогове вікно.
-
Виберіть При кожній зміні випадаючої стрілки та виберіть групування, до якого буде обчислюватися кожна проміжна сума.
-
Виберіть Використовуйте функцію стрілку спадного меню та виберіть номер_функції.
-
В Додати проміжок до списку, виберіть стовпець, до якого буде застосовано формулу.
-
Виберіть гаразд.
-
Проміжні підсумки вставляються для кожної групи даних, а загальна сума вставляється внизу набору даних.
-
Щоб змінити номер_функції, виділіть будь-яку комірку в наборі даних і виберіть Дані > Проміжна сума. Потім, зробіть вибір у Проміжна сума діалогове вікно.
Більше від tebapit
-
Як сортувати пов’язані дані в Excel за допомогою таблиць
-
Знайдіть частки року між датами за допомогою програми Excel YEARFRAC
-
Округлення чисел вгорі в електронних таблицях Google
-
Знайдіть найбільше від’ємне чи додатне число в Excel
-
Як використовувати логічні значення (логічні значення) в Excel
-
Використовуйте функцію TYPE в Excel, щоб перевірити тип даних у комірці
-
Як використовувати функцію COUNTIF Google Таблиць
-
Як поєднати функції ROUND та SUM у програмі Excel
-
Функція робочого дня Excel: знайти дати початку та закінчення проекту
-
Як створити базу даних в Excel
-
Як користуватися функцією Excel DATEVALUE
-
Як використовувати функцію IF-THEN у програмі Excel
-
Як множити числа в електронних таблицях Google
-
Як створити формулу пошуку Excel з кількома критеріями
-
Як підрахувати порожні або порожні клітинки в Google Таблицях
-
Як користуватися середньою функцією електронних таблиць Google
Ваші права на конфіденційність
tebapit та наші сторонні партнери використовують файли cookie та обробляють особисті дані, такі як унікальні ідентифікатори, на основі вашої згоди зберігати та / або отримувати доступ до інформації на пристрої, показувати персоналізовану рекламу та для вимірювання вмісту, аналізу аудиторії та розробки продуктів. Щоб змінити або скасувати вибір вашої згоди для tebapit.com, включаючи ваше право на заперечення, де використовується законний інтерес, натисніть нижче. Ви можете будь-коли оновити свої налаштування за посиланням “Конфіденційність ЄС” внизу будь-якої сторінки. Цей вибір буде повідомлений нашим партнерам у всьому світі і не впливатиме на дані веб-перегляду. Список партнерів (постачальників)
Ми та наші партнери обробляємо дані для:
Активно скануйте характеристики пристрою для ідентифікації. Використовуйте точні дані геолокації. Зберігайте та / або отримуйте доступ до інформації на пристрої. Виберіть персоналізований вміст. Створіть персоналізований профіль вмісту. Вимірюйте ефективність реклами. Виберіть основні оголошення. Створіть профіль персоналізованої реклами. Виберіть персоналізовану рекламу. Застосовуйте дослідження ринку, щоб генерувати уявлення про аудиторію. Виміряйте продуктивність вмісту. Розробляти та вдосконалювати продукти. Список партнерів (постачальників)