Работая с большими объемами данных в Google Таблицах, легко запутаться в цифрах и расчетах. Чтобы упростить процесс анализа и сосредоточиться на ключевых аспектах, можно использовать функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTAL). Она позволяет выполнять вычисления только с определенной частью данных, игнорируя скрытые строки или фильтры. В этой статье я расскажу, как пользоваться функцией ПРОМЕЖУТОЧНЫЕ.ИТОГИ, разберем с вами ее синтаксис, создадим таблицу с примерами и рассмотрим задачи, которые она помогает решать.
Знакомимся с функцией ПРОМЕЖУТОЧНЫЕ.ИТОГИ: синтаксис и основы
Прежде чем мы начнем работать с примерами, давайте разберем синтаксис функции и выясним, что нужно знать, чтобы ее правильно использовать.
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(код_функции; диапазон1; [диапазон2; …])
- код_функции – это число, которое указывает, какой расчет будет выполнен. Например, 1 означает вычисление среднего, 9 – суммы, 3 – подсчет строк, и так далее.
- диапазон1; [диапазон2; …] – один или несколько диапазонов, к которым будет применяться выбранная функция.
Всего в функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ доступно 11 различных кодов, каждый из которых отвечает за определенный тип вычислений. Сразу ознакомимся с полным списком, чтобы закончить с общими пониманиями синтаксиса. В будущем вы сможете применять его, чтобы понимать, какой код выбрать для той или иной задачи.
- 1 – Среднее значение. Вычисляет среднее арифметическое для всех чисел в указанном диапазоне. Используется, если нужно оценить средний показатель, например, среднюю сумму продаж или среднее количество товаров.
- 2 – Подсчет чисел. Считает только числовые значения в указанном диапазоне, игнорируя текст и пустые ячейки. Это полезно, если вы хотите узнать количество числовых записей.
- 3 – Подсчет строк. Считает все заполненные строки, включая те, где есть текст, числа или даже ошибки. Отличается от предыдущего кода тем, что учитывает не только числа. Применяется для общего подсчета записей.
- 4 – Максимальное значение. Определяет наибольшее значение в указанном диапазоне. Подходит для анализа, если нужно быстро узнать максимальную сумму продаж или самый высокий показатель.
- 5 – Минимальное значение. Находит наименьшее значение в диапазоне. Часто используется для анализа минимальных затрат или самых маленьких продаж.
- 6 – Произведение. Умножает все числовые значения в диапазоне. Этот код может быть полезен для финансовых расчетов или моделирования, где требуется найти общий результат умножения.
- 7 – Стандартное отклонение (для выборки). Вычисляет, насколько данные разбросаны относительно среднего значения, но применимо только для выборки. Этот код актуален для статистических расчетов.
- 8 – Стандартное отклонение (для совокупности). Похож на предыдущий, но применяется ко всем данным, а не только к выборке.
- 9 – Сумма. Считает общую сумму чисел в указанном диапазоне. Это один из самых популярных кодов, поскольку он используется для подсчета итогов, например, общей выручки.
- 10 – Дисперсия (для выборки). Определяет, насколько данные варьируются в пределах выборки. Применяется в статистике для анализа вариативности.
- 11 – Дисперсия (для совокупности). Похож на предыдущий, но охватывает все данные, а не только выборку.
Выбор зависит от того, что именно вы хотите проанализировать. Если нужно просто сложить числа, используйте код 9. Однако если требуется детальный статистический анализ, коды 7, 8, 10 или 11 позволят определить разброс или вариативность данных. Вы наверняка поняли, что каждому коду присвоена своя отдельная функция, которая и так может отдельно использоваться в Google Таблицах. Больше об этом предлагаю прочитать в официальной документации:
Кроме того, функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ автоматически исключает скрытые строки из расчетов. Это делает ее подходящей для работы с фильтрами, поскольку стандартные функции, такие как СУММ или СРЗНАЧ, учитывают все данные, даже если они временно скрыты.
Еще один важный момент – вы можете использовать функцию с несколькими диапазонами. Если ваши данные разделены по разным частям таблицы, достаточно указать их через точку с запятой, чтобы объединить в один расчет. В результате функция возвращает итоговое значение, соответствующее выбранному коду.
Таблица для примеров
Для демонстрации я предлагаю рассмотреть следующую таблицу, где у нас есть данные о продажах. Столбцы включают информацию о сотрудниках, категориях товаров, количестве проданных единиц и общей сумме продаж. Вот как будет выглядеть таблица:
Данные в этой таблице мы будем использовать для выполнения расчетов. На основании приведенных значений мы сможем вычислять суммы продаж, средние показатели и многое другое. При этом каждая задача будет решаться уникальным способом, чтобы вы могли понять весь потенциал функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ. Далее вы либо сможете полностью копировать формулы, либо научитесь самостоятельно их создавать, чтобы применять в своих таблицах.
Пример 1: Сумма видимых строк
Теперь давайте начнем с одной из самых простых задач – подсчета общей суммы продаж для видимых строк. Это особенно полезно, когда вы используете фильтры в таблице. Допустим, вы отфильтровали данные так, чтобы отображались только продажи категории «Электроника». После этого в любой свободной ячейке запишем формулу.
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9; D2:D6)
Аргумент 9 указывает функции, что мы хотим сложить все числа в указанном диапазоне, который задается как D2:D6. Этот диапазон включает значения из столбца «Сумма продаж». После применения фильтра функция игнорирует скрытые строки и вычисляет итоговую сумму только для категории «Электроника», которая равна 125 000.
Таким образом, функция адаптируется к изменению видимости данных, что особенно удобно для анализа в больших таблицах. Вы можете не переживать, что в расчет попадут скрытые значения, как это было бы при использовании простой функции СУММ, так как ПРОМЕЖУТОЧНЫЕ.ИТОГИ автоматически исключает их из итоговой суммы.
Пример 2: Поиск среднего значения
Теперь рассмотрим ситуацию, когда нужно вычислить среднюю сумму продаж. Например, вы хотите понять, какова средняя сумма заказов по всем категориям. Используем формулу:
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(1; D2:D6)
Аргумент 1 задает среднее арифметическое, а диапазон D2:D6 – это значения суммы продаж. После применения формулы результат будет равен 35 400, Потому что функция просуммировала все видимые строки и поделила итоговое значение на их количество.
Далее скроем всего одну категорию и посмотрим, как значение обновится в автоматическом режиме и будет составлять среднее значение продаж только для видимых категорий.
Применение подобного инструмента будет полезным, если вы хотите быстро оценить средние показатели без необходимости вручную вычислять сумму и делить ее на количество строк. Особенно актуально это при работе с большими наборами данных, где каждая секунда экономии важна.
Пример 3: Подсчет количества строк
Иногда возникает необходимость узнать, сколько строк участвует в анализе. Например, вы хотите понять, сколько записей относится к категории «Одежда». Сначала применяем фильтр, оставляя только строки с этой категорией, а затем используем формулу:
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3; A2:A6)
Здесь аргумент 3 указывает на подсчет строк, а диапазон A2:A6 включает данные из столбца «Сотрудник». Функция возвращает значение 2, так как строки с категорией «Одежда» отображаются в таблице только для Марии и Ивана.
Это нам наглядно демонстрирует, как легко можно получить точное количество записей, соответствующих заданным условиям. ПРОМЕЖУТОЧНЫЕ.ИТОГИ экономит ваше время и упрощает подсчет, исключая скрытые данные.
Заключение
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ – это универсальный инструмент для работы с данными в Google Таблицах. Она помогает не только выполнять базовые вычисления, но и учитывать видимость строк, что значительно упрощает анализ. Я предложил несколько примеров, чтобы вы могли освоить разные сценарии использования функции. Попробуйте применить ее на практике, комбинируя с другими возможностями Google Таблиц, чтобы еще больше упростить свою задачу. Кстати, можете продолжить разбор функциональности с изучения фильтров, о которых говорилось в рамках разбора примеров в этой статье.
Читайте также в Комьюнити: Как сортировать и фильтровать данные в Google Таблицах
Комментарии