SUM призначений для початківців – професіонали Excel використовують замість нього

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

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

Чому SUM зменшується?

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

SUM додає все, видиме чи ні. Це його робота. Але коли ви фільтруєте дані чи приховуєте деякі рядки? SUM, на щастя, містить усі ці приховані числа. Як результат, це збільшує вашу колекцію та тихо псує ваші звіти. Якщо ви коли-небудь з’ясовували, чому ваша «загальна сума» більша за відфільтровані дані, ви точно знаєте, про що я говорю.

Саме тут більшість людей вдарилася в стіну – я знаю, що я вдарився. Я покладався на SUM, COUNT та всі звичайні підозрювані та постійно змінював свої формули, коли щось не підходило. Потім я виявив функцію, яка виконує всі ці основи, але без звичних головних болів. Тоді я знайшов SUBTOTAL.

Як працює SUBTOTAL

Відфільтровані рядки припиняються

Уявіть, що у вас є аркуш, наповнений даними про продажі, сотнями чи тисячами рядків. Можливо, ви хочете побачити лише продажі “Продукту А”, тому ви фільтруєте стовпець. Числа зникають з поля зору, але SUM не вловлює пам’ятку. Він все одно додає кожен рядок у фоновому режимі, включно з рядками, які ви не бачите. Це також стосується функцій COUNT і AVERAGE.

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

=SUBTOTAL(function_num, range)

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

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

Використовуйте SUBTOTAL в Excel і Google Таблицях

Змініть формулу, збережіть робочий процес

Що робить SUBTOTAL таким очевидним вибором, так це його гнучкість – ви отримуєте більше варіантів, ніж SUM, без жодних додаткових ускладнень. Давайте візьмемо той самий приклад і побачимо, як використання SUBTOTAL спрощує роботу.

Щоб отримати суму комірок, формула буде виглядати так:

=SUBTOTAL(109, C2:C15)

Ця формула підсумовує клітинки від C2 до C15, ігноруючи приховані клітинки. Я застосував це до двох інших рядків, і тепер у мене є зведення. Вони добре працюють для повної таблиці, і коли я фільтрую таблицю, вони працюють добре. Тепер цифри мають сенс.

Однак інші цифри все одно не мають сенсу. Підрахунок все ще показує 14, і моє середнє значення тепер доповнює цей показник, тому показники нижчі, ніж повинні бути. Нічого страшного. SUBTOTAL також підтримує функції COUNT і COUNTA.

Тож для клітинки мого облікового запису (B16), замість того, щоб писати:

=COUNTA(A2:A15)

Я заміню це на:

=SUBTOTAL(103, A2:A15)

103 — номер функції для COUNTA. Тепер, коли я фільтрую дані, мої розрахунки коригуються автоматично, і моє середнє значення завжди показує правильне значення. SUBTOTAL також підтримує MAX і MIN, що є порятунком.

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

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

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