Як користуватися функцією Excel SUBTOTAL -

Зміст

Пов’язані відео

Резюме

Функція Excel SUBTOTAL повертає сукупний результат для наданих значень. SUBTOTAL може повертати SUM, AVERAGE, COUNT, MAX та інші (див. Таблицю нижче), а функція SUBTOTAL може включати або виключати значення у прихованих рядках.

Призначення

Отримайте проміжну суму в списку або базі даних

Повернене значення

Число, що представляє конкретний вид проміжних підсумків

Синтаксис

= ПІДЗВІТ (номер_функції, ref1, (ref2),…)

Аргументи

function_num - число, яке вказує, яку функцію використовувати для обчислення проміжних підсумків у списку. Повний список наведено в таблиці нижче.
ref1 - Іменований діапазон або посилання на проміжні підсумки.
ref2 - (необов’язково) Іменований діапазон або посилання на проміжні підсумки.

Версія

Excel 2003

Примітки щодо використання

Використовуйте функцію SUBTOTAL, щоб отримати проміжний підсумок у списку або базі даних. Незважаючи на назву, SUBTOTAL має можливість виконувати різноманітні математичні функції, включаючи AVERAGE, COUNT, MAX та багато інших (повний список див. У таблиці нижче). За замовчуванням SUBTOTAL виключає значення в рядках, прихованих фільтром, що робить SUBTOTAL дуже корисним у таблицях Excel.

Функція SUBTOTAL автоматично ігнорує інші формули SUBTOTAL, які існують у посиланнях, щоб запобігти подвійному підрахунку.

Приклади

Нижче наведено кілька прикладів SUBTOTAL, налаштованих на SUM, COUNT та AVERAGE значень у діапазоні. Зверніть увагу, що різницею є лише значення, яке використовується для аргументу function_num :

=SUBTOTAL(109,range) // SUM =SUBTOTAL(103,range) // COUNT =SUBTOTAL(101,range) // AVERAGE

Доступні розрахунки

Поведінка SUBTOTAL управляється аргументом function_num , який надається як числове значення. Доступно 11 функцій, кожна з яких має два варіанти, як видно з таблиці нижче. Зверніть увагу, що значення "спарені" (наприклад, 1-101, 2-102, 3-103 тощо). Це пов’язано з тим, як SUBTOTAL має справу з прихованими вручну рядками. Коли function_num знаходиться між 1-11, SUBTOTAL включає комірки, які були приховані вручну. Коли function_num знаходиться між 101-111, SUBTOTAL виключає значення в рядках, які були приховані вручну.

Функція	Включити прихований	Ігнорувати прихований
СЕРЕДНІЙ	1	101
РАХУВАТИ	2	102
КОНТА	3	103
МАКС	4	104
ХВ	5	105
ПРОДУКТ	6	106
СТДЕВ	7	107
СТДЕВП	8	108
СУММА	9	109
VAR	10	110
VARP	11	111

Примітка: SUBTOTAL завжди ігнорує значення в клітинках, які приховані фільтром. Значення в рядках, які були "відфільтровані", ніколи не включаються, незалежно від функції_номери .

ПІДСУМОК у таблицях Excel

Функція SUBTOTAL використовується, коли у таблиці Excel відображається рядок Total. Excel автоматично вставляє функцію SUBTOTAL, і ви можете використовувати спадне меню, щоб змінити поведінку та показати max, min, середнє значення тощо. Причина, по якій Excel використовує SUBTOTAL для обчислень у рядку Total у таблиці Excel, полягає в тому, що SUBTOTAL автоматично виключає рядки прихований елементами керування фільтром у верхній частині таблиці. Тобто, коли ви фільтруєте рядки в таблиці з рядком Total, ви побачите, що обчислення оновлюються автоматично для відповідності фільтру.

ПІДЗЕМЛО з контурами

Excel має функцію проміжних підсумків, яка автоматично вставляє формули ПІДГОТОВКА в відсортовані дані. Ви можете знайти цю функцію в меню Дані> Контур> Проміжні підсумки. ВСТУПЛЕНІ формули, вставлені таким чином, використовують стандартні числа 1-11. Це дозволяє результатам проміжних підсумків залишатися видимими, навіть коли рядки приховані та відображаються, коли контур згортається та розширюється.

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

Примітки

Коли Function_Num між 1-11, SUBTOTAL включає в себе значення, які приховані
Коли function_num знаходиться між 101-111, SUBTOTAL виключає значення, які приховані
У відфільтрованих списках SUBTOTAL завжди ігнорує значення у прихованих рядках, незалежно від function_num .
SUBTOTAL ігнорує інші формули SUBTOTAL, які існують у посиланнях, щоб запобігти подвійному підрахунку.
SUBTOTAL призначений для роботи з вертикальними значеннями даних, розташованими вертикально. У горизонтальних діапазонах значення в прихованих стовпцях завжди включаються.

Пов’язані відео

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

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

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