Функція «Проміжний результат» у таблицях Google

Система підрахунку проміжного підсумку в таблицях Google є розширений функціонал для роботи з даними.

Існує три основні способи застосування функції «Проміжний результат»:

  1. Обчислення проміжних підсумків для списків даних
  2. Обчислення показників за допомогою відфільтрованих та/або прихованих даних
  3. Як динамічний селектор функцій

Як видно, функція є досить різноплановою.

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

Зразок підрахунку проміжного підсумку

Клацніть тут, щоб переглянути табличку і зробити копію.

Це робиться дуже просто: Файл → Створити копію.

Якщо у вас не виходить доступу до зразка, значить є проблема з налаштуваннями Google Workspace вашої організації. Щоб побачити її, натисніть на посилання, яке потім відкрийте у вікні інкогніто.

Тепер розглянемо синтаксис:

Синтаксис команди “Проміжний підсумок”

=ПРОМІЖНІ.ПІДСУМКИ(код_функції; діапазон_1; [діапазон_2; …])

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

Код функції – це число, яке визначає тип операції, яку функція «Проміжний підсумок» виконуватиме з вашими даними. Наприклад, число 9 відповідає функції СУМ.

Зразкова формула проміжного підсумку може бути такою:

=ПРОМІЖНІ.ПІДСУМКИ(9; A1:A10)

Зауважте, що число 9 – перший аргумент цієї функції. Це означає, що в даному прикладі функція SUM буде застосована до діапазону A1:A10.

З «Проміжним підсумком» є 11 різних варіантів виконання функцій, для кожного з яких визначається, використовувати або ігнорувати приховані рядки даних.

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

Якщо номер коду функції знаходиться між 100 і 111, приховані ряди ігноруються при обчисленні.

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

Нижче наведено варіанти, доступні для опції коду функції:

Агрегація Опис агрегації Код, що включає приховані значення Код, що ігнорує приховані значення
Average()
СРЗНАЧ()
Середнє арифметичне 1 101
Count
РАХУНОК()
Кількість осередків 2 102
Counta
РАХУНОК()
Кількість заповнених осередків 3 103
Max
МАКС()
Максимальне значення 4 104
Min
МІН()
Мінімальне значення 5 105
Product
ВИРОБ()
Добуток чисел 6 106
Standard Deviation
СТАНДОТКЛОН()
Стандартне відхилення за вибіркою 7 107
Standard Deviation Population
СТАНДОТКЛОНП()
Стандартне відхилення за генеральною сукупністю 8 108
Sum
СУМ()
Підсумовування діапазону 9 109
Variance
ДИСП()
Дисперсія по діапазону 10 110
Variance Population
ДИСПР()
Дисперсія з генеральної сукупності 11 111

Використання функції «Проміжні підсумки» для створення проміжних підсумків

Припустимо, що у вас є наступний набір даних, де кожна підтаблиця містить проміжний результат з використанням функції СУМ:

= СУМ (C2: C5)

Функція «Проміжний результат» у таблицях Google

При обчисленні загальної суми за допомогою функції СУМ, ви ризикуєте двічі перерахувати дохід.

Функція СУММ збільшує значення доходу та проміжні підсумки. Це означає, що загальна сума буде вдвічі більшою, ніж має бути. Це погано!

Функція «Проміжний результат» у таблицях Google

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

= C6 + C13 + C20

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

Однак ця проблема вирішується, якщо використовувати функцію “Проміжний результат” у таблицях Google.

Замініть кожну з формул СУМ формулами, що використовують функцію «Проміжний підсумок», наприклад,

=ПРОМІЖНІ.ПІДСУМКИ(9;C2:C5)

Функція «Проміжний результат» у таблицях Google

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

=ПРОМІЖНІ.ПІДСУМКИ(9;C2:C20)

Функція «Проміжний результат» у таблицях Google

На цей раз видається правильна відповідь – 51 385 доларів США.

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

Використання функції «Проміжний результат» для відфільтрованих або прихованих даних

Припустимо, що у вас є цей набір даних:

Функція «Проміжний результат» у таблицях Google

І у вас є ці три формули згідно з даними:

=СУМ(D2:D21) =ПРОМІЖНІ.ПІДСУМКИ(9; D2:D21) =ПРОМІЖНІ.ПІДСУМКИ(109; D2:D21)

Дані, що пройшли фільтрацію

Використовуючи функцію, що фільтрує, вибір впав на осередок «Квартира» з області нерухомості.

Формула СУМ не змінюється і, як і раніше, повертає загальну суму всього набору даних.

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

Функція «Проміжний результат» у таблицях Google

Приховані ряди

Якщо зараз сховаємо кілька рядів, виділивши їх клацанням правої кнопки миші і вибравши «Приховати рядки…», то виведення функції підсумкового проміжного підсумку оновиться.

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

Функція «Проміжний результат» у таблицях Google

Примітка щодо прихованих стовпців : функція «Проміжний результат» не враховує приховані стовпці. Якщо ви використовуєте «Проміжний підсумок» по рядку, він завжди включає всі колонки. Тому він використовується в списках даних, виконаних стовпцями.

Створюйте динамічний селектор функцій із формулою проміжного підсумку в таблицях Google

Використовуючи наведений вище список кодів функцій як таблиця підстановки, можна створити динамічний селектор, щоб користувач міг вибрати певну функцію для її застосування в проміжному підсумку:

Функція «Проміжний результат» у таблицях Google

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

Виберіть порожню комірку (в даному прикладі C24) та перейдіть в меню:

Дані → Перевірка даних

Виберіть список, що розкривається, і виділіть імена агрегування (Average, Count, Counta…) як діапазон.

Ця формула пошуку поверне код на основі обраного списку, що розкривається:

= ВПР (C24; F24: G34; 2; false)

Якщо хочете, сміливо використовуйте ІНДЕКС + ПОШУКПОЗ (або просто ІНДЕКС) замість ВПР. Я користувався ВПР, тому що більшість користувачів добре з ним знайомі.

Потім цей код можна включити до формули проміжного результату:

=ПРОМІЖНІ.ПІДСУМКИ(ВПР(C24;F24:G34;2;false); D2:D21)

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

Функція «Проміжний результат» у таблицях Google

Це досягається за допомогою включення оператора.

=якщо(C24=”Yes”;0;100)

Видається відповідь 0 або 100, яку можна додати до коду функції, щоб вибрати діапазон від 1 до 11, або діапазон від 100 до 111 (див. таблицю кодів функцій, зазначеної раніше).

Повна динамічна формула проміжного підсумку набуває наступного вигляду:

=ПРОМІЖНІ.ПІДСУМКИ(ЯКЩО(C24=”Yes”;0;100) + ВПР(C24;F24:G34;2;false); D2:D21)

Вивчення функції «Проміжний результат» також входить до змісту 27-го уроку мого безкоштовного 30-денного курсу «Удосконалені формули».

Додатково про це можна прочитати у документації Google.

Автор: Ben Collins

редакція ExcelExcel

nBook
Додати коментар