Формула промежуточные итоги в excel как сделать
В этой статье описаны синтаксис формулы и использование функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ в Microsoft Excel.
Описание
Возвращает промежуточный итог в список или базу данных. Обычно проще создать список с промежуточными итогами, используя в настольном приложении Excel команду Промежуточные итоги в группе Структура на вкладке Данные. Но если такой список уже создан, его можно модифицировать, изменив формулу с функцией ПРОМЕЖУТОЧНЫЕ.ИТОГИ.
Синтаксис
Аргументы функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ описаны ниже.
Номер_функции — обязательный аргумент. Число от 1 до 11 или от 101 до 111, которое обозначает функцию, используемую для расчета промежуточных итогов. Функции с 1 по 11 учитывают строки, скрытые вручную, в то время как функции с 101 по 111 пропускают такие строки; отфильтрованные ячейки всегда исключаются.
Function_num
(включая скрытые строки)
Function_num
(игнорирует скрытые строки)
Ссылка1 Обязательный. Первый именованный диапазон или ссылка, для которых требуется вычислить промежуточные итоги.
Ссылка2;. Необязательный. Именованные диапазоны или ссылки 2—254, для которых требуется вычислить промежуточные итоги.
Примечания
Если уже имеются формулы подведения итогов внутри аргументов «ссылка1;ссылка2;. » (вложенные итоги), эти вложенные итоги игнорируются, чтобы избежать двойного суммирования.
Для констант «номер_функции» от 1 до 11 функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ учитывает значения строк, скрытых с помощью команды Скрыть строки (меню Формат, подменю Скрыть или отобразить) в группе Ячейки на вкладке Главная в настольном приложении Excel. Эти константы используются для получения промежуточных итогов с учетом скрытых и нескрытых чисел списка. Для констант «номер_функции» от 101 до 111 функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ исключает значения строк, скрытых с помощью команды Скрыть строки. Эти константы используются для получения промежуточных итогов с учетом только нескрытых чисел списка.
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ исключает все строки, не включенные в результат фильтра, независимо от используемого значения константы «номер_функции».
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ применяется к столбцам данных или вертикальным наборам данных. Она не предназначена для строк данных или горизонтальных наборов данных. Так, при определении промежуточных итогов горизонтального набора данных с помощью значения константы «номер_функции» от 101 и выше (например, ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;B2:G2)), скрытие столбца не повлияет на результат. Однако на него повлияет скрытие строки при подведении промежуточного итога для вертикального набора данных.
Если среди ссылок есть трехмерные ссылки, функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ возвращает значение ошибки #ЗНАЧ!.
Пример
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу Enter. При необходимости измените ширину столбцов, чтобы видеть все данные.
Формула ПРОМЕЖУТОЧНЫЕ ИТОГИ — основные функции с примерами.
В предыдущей статье мы обсудили автоматический способ вставки промежуточных итогов в Excel с помощью инструмента Промежуточные итоги. Сегодня вы узнаете, как можно самостоятельно создавать формулы промежуточных итогов и какие преимущества это дает.
Синтаксис и использование.
Microsoft определяет ПРОМЕЖУТОЧНЫЕ.ИТОГИ как функцию, которая возвращает промежуточный итог в таблице данных. И это не просто суммирование чисел в определенном диапазоне ячеек.
В отличие от других функций Excel, которые предназначены только для одной конкретной задачи, она удивительно универсальна. Она может выполнять различные арифметические и логические операции, такие как подсчет количества значений, вычисление среднего, поиск минимального или максимального и многое другое.
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ (на английском — SUBTOTAL) доступна во всех версиях Excel 2019, 2016 и ниже.
Синтаксис ее следующий:
ПРОМЕЖУТОЧНЫЕ.ИТОГИ(номер_функции; ссылка1; [ссылка2];…)
| Номер функции |
Номер функции |
Функция | Описание |
| 1 | 101 | СРЗНАЧ | Возвращает среднее значение чисел. |
| 2 | 102 | СЧЁТ | Подсчитывает ячейки, содержащие числовые значения. |
| 3 | 103 | СЧЁТЗ | Считает непустые ячейки. |
| 4 | 104 | МАКС | Возвращает наибольшее значение. |
| 5 | 105 | МИН | Возвращает наименьшее значение. |
| 6 | 106 | ПРОИЗВЕД | Вычисляет произведение ячеек. |
| 7 | 107 | СТАНДОТКЛОН | Возвращает стандартное отклонение генеральной совокупности на основе выборки чисел. |
| 8 | 108 | СТАНДОТКЛОНП | Возвращает стандартное отклонение, основанное на генеральной совокупности чисел. |
| 9 | 109 | СУММ | Складывает числа. |
| 10 | 110 | ДИСП | Оценивает дисперсию генеральной совокупности на основе выборки чисел. |
| 11 | 111 | ДИСПР | Оценивает дисперсию генеральной совокупности на основе всей совокупности чисел. |
На самом деле нет необходимости запоминать все эти номера. Как только вы начнете вводить формулу промежуточного итога в ячейку или в строку формул, Microsoft Excel отобразит для вас список доступных функций с номерами. Останется только выбрать подходящую.
Например, вот как вы можете составить формулу с номером 9 для суммирования значений в ячейках с D2 по D10:
Чтобы добавить номер функции в формулу, дважды щелкните его в списке, затем поставьте точку с запятой, укажите диапазон, введите закрывающую скобку и нажмите Enter. Готовая формула будет выглядеть так:
Аналогичным образом вы можете написать формулу с 1 для получения среднего значения, с 2 для подсчета ячеек с числами, указать 3 для подсчета непустых значений и т. д. На скриншоте ниже показаны несколько других формул с различными функциями в действии:
Особенности формулы ПРОМЕЖУТОЧНЫЕ.ИТОГИ:
- Считает только по столбцам, для строк не подходит.
- Отфильтрованные значения не учитываются. Скрытые вручную значения учитываются функциями с номерами 1 – 11.
- Подсчитывает только исходные данные. Никакие другие промежуточные итоги не учитываются.
- Когда вы используете формулу промежуточных итогов с функцией суммирования, например СУММ или СРЗНАЧ, она вычисляет только ячейки с числами, игнорируя пустые и содержащие нечисловые значения.
- Применяется только к обычным диапазонам, с таблицами Excel не работает.
Существенное преимущество этой формулы в том, что ее можно использовать почти в любом месте вашего рабочего листа. То есть, таблица может не быть настолько жестко упорядоченной, как при использовании инструмента «Промежуточные итоги».
Теперь, когда вы знаете, как создать формулу промежуточных итогов в Excel, главный вопрос: зачем вообще тратить силы на ее изучение? Почему бы просто не использовать обычные функции, такие как СУММ, СЧЁТ, МАКС и т.д.? Вы найдете ответ ниже.
3 основных причины использовать ПРОМЕЖУТОЧНЫЙ ИТОГ в Excel
По сравнению с традиционными функциями Excel, ПРОМЕЖУТОЧНЫЕ.ИТОГИ дает следующие важные преимущества.
1. Вычисляет значения с учетом применяемого фильтра.
Стандартная функция СУММ с подсчетом только нужных ячеек не справится. Придется либо копировать эти данные в другое место, чтобы все другие не мешали, либо применять функцию СУММЕСЛИ и постоянно корректировать ее в соответствии с нужными целями.
Примечание. Поскольку оба набора номеров функций (1-11 и 101-111) игнорируют отфильтрованные ячейки, в этом случае вы можете использовать формулу с функцией номер 9 или 109.
2. Вычисление только видимых ячеек.
Как вы помните, формулы промежуточных итогов с номером функции от 101 до 111 игнорируют все скрытые ячейки – отфильтрованные и скрытые вручную. Итак, когда вы используете функцию «Скрыть» в Excel для удаления из представления нерелевантных данных, используйте функцию номер 101-111, чтобы исключить значения в скрытых строках из подсчета итогов.
Номер функции Отфильтрованные значения Скрытые вручную 1-11 игнорируются учитываются 101-111 игнорируются игнорируются На рисунке ниже вы видите, что не следует использовать функцию 9, если вы руками скрывали какие-либо строки в вашей таблице. При этом функция 109 всегда возвращает результат исходя только из видимых на экране значений.
Следующий пример поможет вам лучше понять, как это работает: Промежуточный итог 9 против 109.
3. Игнорируются значения во вложенных формулах промежуточных итогов.
Если диапазон, указанный в вашей формуле промежуточных итогов Excel, содержит любые другие формулы промежуточных итогов, эти вложенные итоги будут проигнорированы, поэтому одни и те же числа не будут вычисляться дважды. Ведь нам не нужен двойной счёт. Классно, не правда ли?
На снимке экрана ниже формула
игнорирует результаты формул промежуточных итогов в ячейках G7 и G120, как если бы вы использовали функцию СУММ с двумя отдельными диапазонами: СУММ(G2:C6, G8:G11).
Использование промежуточных итогов в Excel — примеры формул
Когда вы впервые сталкиваетесь с промежуточными итогами, это может показаться сложным, запутанным и даже не слишком нужным. Но как только вы узнаете все подробности, то поймете, что овладеть ими не так уж и сложно, а пользы может быть много. Следующие примеры дадут вам несколько полезных советов и новых идей.
Пример 1. Промежуточный итог с функцией 9 или 109?
Как вы уже знаете, ПРОМЕЖУТОЧНЫЕ.ИТОГИ использует 2 набора номеров функций: 1-11 и 101-111. Все они игнорируют отфильтрованные строки, но номера 1–11 учитывают скрытые вручную строки, а номера 101–111 исключают их. Чтобы лучше понять разницу, давайте рассмотрим следующий пример.
Чтобы суммировать отфильтрованные строки, вы можете использовать формулу Промежуточный итог с функцией 9 или с 109, как показано на рисунке ниже:
Но если вы скрыли отдельные элементы вручную,
- используя команду «Скрыть строки» на вкладке «Главная»> «Ячейки»> «Формат» > «Скрыть и отобразить»,
- или щелкнув строки правой кнопкой мыши и выбрав «Скрыть»,
и теперь хотите суммировать значения только в видимых строках, использовать функцию 109 – это единственный вариант:
Остальные номера функций работают таким же образом. Например, для подсчета непустых отфильтрованных ячеек подойдет формула с функцией 3 или 103. Но только функция 103 может правильно подсчитывать видимые значения, если в диапазоне есть скрытые вручную строки.
Примечание. Функция Excel ПРОМЕЖУТОЧНЫЕ.ИТОГИ с номерами 101-111 игнорирует значения в скрытых строках, но не в скрытых столбцах. Например, если вы используете формулу =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;D2:H10) для суммирования чисел в горизонтальном диапазоне (по строке), то скрытие столбца F не повлияет на результат.
Пример 2. Промежуточные итоги с условием.
Если вы создаете сводный отчет или информационную панель, где вам нужно отображать различные сводные данные, но у вас ограничено место, чтобы разместить множество вариантов итогов, то можно сделать так:
- В одной ячейке создайте раскрывающийся список, содержащий названия функций, такие как СУММ, МАКС, МИН и др.
- В ячейке рядом с раскрывающимся списком введите вложенную формулу ЕСЛИ со встроенными функциями промежуточных итогов, соответствующими именам функций в раскрывающемся списке.
Например, если предположить, что значения для промежуточного итога находятся в ячейках C2: C16, а раскрывающийся список в A17 содержит элементы «Итого», «Среднее», «Максимум» и «Минимум», «динамическая» формула промежуточного итога будет выглядеть следующим образом:
=ЕСЛИ(A17=»Сумма»;ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;C2:C16); ЕСЛИ(A17=»Среднее»;ПРОМЕЖУТОЧНЫЕ.ИТОГИ(1;C2:C16);ЕСЛИ(A17=»Минимум»;ПРОМЕЖУТОЧНЫЕ.ИТОГИ(5;C2:C16); ЕСЛИ(A17=»Максимум»;ПРОМЕЖУТОЧНЫЕ.ИТОГИ(4;C2:C16);»»))))
И теперь, в зависимости от того, какую функцию ваш пользователь выбирает из раскрывающегося списка, соответствующая функция будет вычислять значения в отфильтрованных строках:
Примечание. Если вдруг раскрывающийся список и ячейка формулы исчезнут с вашего рабочего листа, обязательно снова выберите их в списке фильтра – значит, вы их случайно отфильтровали.
Думаю, при помощи этого способа вы можете заменить этой универсальной функцией стандартные функции суммирования, нахождения максимума и минимума, среднего значения, подсчёта и т.д. Ведь в данном случае даже сортировка не нужна, так как мы не группируем данные, а просто делаем подсчёт. Главное, чтобы не было пустых строк.
Обратите внимание также на то, что если бы мы использовали для расчёта суммы продаж функцию СУММ, то в этой таблице мы не смогли бы подсчитать продажи по какому-то определённому покупателю или группе покупателей. Пришлось бы для каждого из них использовать функцию СУММЕСЛИ.
Вы можете достаточно просто посчитать промежуточные итоги с условием, если отфильтровать нужные строки по товарам, по покупателям либо по другим критериям. Результат будет показан только по значениям, оставшимся после фильтрации. Соответственно и расчет будет сделан только по ним.
К примеру, вот как можно подсчитать сумму продаж черного шоколада, исключив молочный при помощи фильтра:
Как видите, при помощи СУММ это сделать невозможно. А использовать СУММЕСЛИ — это каждый раз корректировать условие в формуле либо делать несколько формул «на все случаи жизни». А здесь мы просто отбираем нужное при помощи фильтра. Согласитесь, это гораздо проще и удобнее, нежели писать и затем корректировать громоздкие формулы.
Почему промежуточные итоги не работают? Распространенные ошибки.
Если формула промежуточного итога возвращает ошибку, вероятно, это связано с одной из следующих причин:
#ЗНАЧЕН!— аргумент номер_функции не является целым числом от 1 до 11 или от 101 до 111; или любой из аргументов ref содержит трехмерную ссылку сразу на несколько листов вашей рабочей книги.
#ДЕЛ/0! — возникает, если указанная функция должна выполнить деление на ноль (например, вычисление среднего значения или стандартного отклонения для диапазона ячеек, не содержащего ни одного числа).
#ИМЯ? — неправильно написано название функции — исправить ошибку проще простого:)
Совет. Если вы еще не чувствуете себя комфортно с функцией ПРОМЕЖУТОЧНЫЕ.ИТОГИ, вы можете использовать встроенный инструмент ПРОМЕЖУТОЧНЫЙ ИТОГ и автоматически вставлять формулы.
Функция СУММПРОИЗВ с примерами формул — В статье объясняются основные и расширенные способы использования функции СУММПРОИЗВ в Excel. Вы найдете ряд примеров формул для сравнения массивов, условного суммирования и подсчета ячеек по нескольким условиям, расчета средневзвешенного значения…
Сумма по цвету и подсчёт по цвету в Excel — В этой статье вы узнаете, как посчитать ячейки по цвету и получить сумму по цвету ячеек в Excel. Эти решения работают как для окрашенных вручную, так и с условным форматированием. Если…
Промежуточные итоги в Excel — В руководстве объясняется, как использовать инструмент промежуточных итогов Excel для автоматического суммирования, подсчета или усреднения различных групп ячеек. Вы также узнаете, как отображать или скрывать детали промежуточных итогов, копировать только строки…
Формула суммы в Excel — несколько полезных советов и примеров — Как вычислить сумму в таблице Excel быстро и просто? Попробуйте различные способы: взгляните на сумму выбранных ячеек в строке состояния, используйте автосумму для сложения всех или только нескольких отдельных ячеек,…
7 примеров использования формулы СУММЕСЛИ в Excel с несколькими условиями — В таблицах Excel можно не просто находить сумму чисел, но и делать это в зависимости от заранее определённых критериев отбора. Хорошо знакомая нам функция ЕСЛИ позволяет производить вычисления в зависимости…
8 простых способов как посчитать в Excel сумму столбца — Как посчитать сумму в Excel быстро и просто? Чаще всего нас интересует итог по столбцу либо строке. Попробуйте различные способы найти сумму по столбцу, используйте функцию СУММ или же преобразуйте…
Промежуточные итоги в Excel с примерами функций
Подвести промежуточные итоги в таблице Excel можно с помощью встроенных формул и соответствующей команды в группе «Структура» на вкладке «Данные».
Важное условие применения средств – значения организованы в виде списка или базы данных, одинаковые записи находятся в одной группе. При создании сводного отчета промежуточные итоги формируются автоматически.
Вычисление промежуточных итогов в Excel
Чтобы продемонстрировать расчет промежуточных итогов в Excel возьмем небольшой пример. Предположим, у пользователя есть список с продажами определенных товаров:
Необходимо подсчитать выручку от реализации отдельных групп товаров. Если использовать фильтр, то можно получить однотипные записи по заданному критерию отбора. Но значения придется подсчитывать вручную. Поэтому воспользуемся другим инструментом Microsoft Excel – командой «Промежуточные итоги».
Чтобы функция выдала правильный результат, проверьте диапазон на соответствие следующим условиям:
- Таблица оформлена в виде простого списка или базы данных.
- Первая строка – названия столбцов.
- В столбцах содержатся однотипные значения.
- В таблице нет пустых строк или столбцов.
- Отсортируем диапазон по значению первого столбца – однотипные данные должны оказаться рядом.
- Выделяем любую ячейку в таблице. Выбираем на ленте вкладку «Данные». Группа «Структура» — команда «Промежуточные итоги».
- Заполняем диалоговое окно «Промежуточные итоги». В поле «При каждом изменении в» выбираем условие для отбора данных (в примере – «Значение»). В поле «Операция» назначаем функцию («Сумма»). В поле «Добавить по» следует пометить столбцы, к значениям которых применится функция.
- Закрываем диалоговое окно, нажав кнопку ОК. Исходная таблица приобретает следующий вид:
Если свернуть строки в подгруппах (нажать на «минусы» слева от номеров строк), то получим таблицу только из промежуточных итогов:
При каждом изменении столбца «Название» пересчитывается промежуточный итог в столбце «Продажи».
Чтобы за каждым промежуточным итогом следовал разрыв страницы, в диалоговом окне поставьте галочку «Конец страницы между группами».
Чтобы промежуточные данные отображались НАД группой, снимите условие «Итоги под данными».
Команда промежуточные итоги позволяет использовать одновременно несколько статистических функций. Мы уже назначили операцию «Сумма». Добавим средние значения продаж по каждой группе товаров.
Снова вызываем меню «Промежуточные итоги». Снимаем галочку «Заменить текущие». В поле «Операция» выбираем «Среднее».
Формула «Промежуточные итоги» в Excel: примеры
Функция «ПРОМЕЖУТОЧНЫЕ.ИТОГИ» возвращает промежуточный итог в список или базу данных. Синтаксис: номер функции, ссылка 1; ссылка 2;… .
Номер функции – число от 1 до 11, которое указывает статистическую функцию для расчета промежуточных итогов:
- – СРЗНАЧ (среднее арифметическое);
- – СЧЕТ (количество ячеек);
- – СЧЕТЗ (количество непустых ячеек);
- – МАКС (максимальное значение в диапазоне);
- – МИН (минимальное значение);
- – ПРОИЗВЕД (произведение чисел);
- – СТАНДОТКЛОН (стандартное отклонение по выборке);
- – СТАНДОТКЛОНП (стандартное отклонение по генеральной совокупности);
- – СУММ;
- – ДИСП (дисперсия по выборке);
- – ДИСПР (дисперсия по генеральной совокупности).
Ссылка 1 – обязательный аргумент, указывающий на именованный диапазон для нахождения промежуточных итогов.
Особенности «работы» функции:
- выдает результат по явным и скрытым строкам;
- исключает строки, не включенные в фильтр;
- считает только в столбцах, для строк не подходит.
Рассмотрим на примере использование функции:
- Создаем дополнительную строку для отображения промежуточных итогов. Например, «сумма отобранных значений».
- Включим фильтр. Оставим в таблице только данные по значению «Обеденная группа «Амадис»».
- В ячейку В2 введем формулу: .
Формула для среднего значения промежуточного итога диапазона (для прихожей «Ретро»): .
Формула для максимального значения (для спален): .
Промежуточные итоги в сводной таблице Excel
В сводной таблице можно показывать или прятать промежуточные итоги для строк и столбцов.
- При формировании сводного отчета уже заложена автоматическая функция суммирования для расчета итогов.
- Чтобы применить другую функцию, в разделе «Работа со сводными таблицами» на вкладке «Параметры» находим группу «Активное поле». Курсор должен стоять в ячейке того столбца, к значениям которого будет применяться функция. Нажимаем кнопку «Параметры поля». В открывшемся меню выбираем «другие». Назначаем нужную функцию для промежуточных итогов.
- Для выведения на экран итогов по отдельным значениям используйте кнопку фильтра в правом углу названия столбца.
В меню «Параметры сводной таблицы» («Параметры» — «Сводная таблица») доступна вкладка «Итоги и фильтры».
Таким образом, для отображения промежуточных итогов в списках Excel применяется три способа: команда группы «Структура», встроенная функция и сводная таблица.
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() EXCEL
Особенность функции состоит в том, что она предназначена для использования совместно с другими средствами EXCEL: Автофильтром и Промежуточными итогами . См. Файл примера .
Синтаксис функции
ПРОМЕЖУТОЧНЫЕ.ИТОГИ( номер_функции ; ссылка1 ;ссылка2;. ))
Номер_функции — это число от 1 до 11, которое указывает какую функцию использовать при вычислении итогов внутри списка.
Номер_функции (включая скрытые значения) Номер_функции (за исключением скрытых значений) Функция 1 101 СРЗНАЧ 2 102 СЧЁТ 3 103 СЧЁТЗ 4 104 МАКС 5 105 МИН 6 106 ПРОИЗВЕД 7 107 СТАНДОТКЛОН 8 108 СТАНДОТКЛОНП 9 109 СУММ 10 110 ДИСП 11 111 ДИСПР Например, функция СУММ() имеет код 9. Функция СУММ() также имеет код 109, т.е. можно записать формулу = ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;A2:A10) или = ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;A2:A10). В чем различие — читайте ниже. Обычно используют коды функций от 1 до 11.
Ссылка1 ; Ссылка2; — от 1 до 29 ссылок на диапазон, для которых подводятся итоги (обычно используется один диапазон).
Если уже имеются формулы подведения итогов внутри аргументов ссылка1;ссылка2;. (вложенные итоги), то эти вложенные итоги игнорируются, чтобы избежать двойного суммирования.
Важно : Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() разработана для столбцов данных или вертикальных наборов данных. Она не предназначена для строк данных или горизонтальных наборов данных (ее использование в этом случае может приводить к непредсказуемым результатам).
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() и Автофильтр
Пусть имеется исходная таблица.
Применим Автофильтр и отберем только строки с товаром Товар1 . Пусть функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() подсчитает сумму товаров Товар1 , следовательно будем использовать код функции 9 или 109.
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() исключает все строки не включенные в результат фильтра независимо от используемого значения константы номер_функции и, в нашем случае, подсчитывает сумму отобранных значений (сумму цен товара Товар1 ).
Если бы мы записали формулу = ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;B11:B20) или = ПРОМЕЖУТОЧНЫЕ.ИТОГИ( 103;B11:B20), то мы бы подсчитали число отобранных фильтром значений (5).
Таким образом, эта функция «чувствует» скрыта ли строка автофильтром или нет. Это свойство используется в статье Автоматическая перенумерация строк при применении фильтра .
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() и Скрытые строки
Пусть имеется та же исходная таблица. Скроем строки с товаром Товар2 через меню Главная/ Ячейки/ Формат/ Скрыть или отобразить или через контекстное меню.
В этом случае имеется разница между использованием кода функции СУММ() : 9 и 109. Функция с кодом 109 «чувствует» скрыта строка или нет. Другими словами для диапазона кодов номер_функции от 101 до 111 функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() исключает значения строк скрытых при помощи команды Главная/ Ячейки/ Формат/ Скрыть или отобразить . Эти коды используются для получения промежуточных итогов только для не скрытых чисел списка.
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() и средство EXCEL Промежуточные итоги
Пусть имеется также исходная таблица. Создадим структуру с использованием встроенного средства EXCEL — Промежуточные итоги .
Скроем строки с Товар2 , нажав на соответствующую кнопку «минус» в структуре.
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() исключает все неотображаемые строки структурой независимо от используемого значения кода номер_функции и, в нашем случае, подсчитывает сумму только товара Товар1 . Этот результат аналогичен ситуации с автофильтром.
Другие функции
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() может подсчитать сумму, количество и среднее отобранных значений, а также включает еще 8 других функций (см. синтаксис). Как правило, этик функций вполне достаточно (учитывая введенную с 2010 года функцию АГРЕГАТ() ), но иногда требуется расширить возможности функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ() . Рассмотрим пример вычисления среднего геометрического для отобранных автофильтром значений. Функция СРГЕОМ() отсутствует среди списка функций доступных через соответствующие коды, но выход есть.
Воспользуемся той же исходной таблицей.
Применим Автофильтр и отберем только строки с товаром Товар1 . Пусть функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() подсчитает среднее геометрическое цен товаров Товар1 (пример не очень жизненный, но он показывает принцип). Будем использовать код функции 3 — подсчет значений.
Для подсчета будем использовать формулу массива (см. файл примера , лист2)
С помощью выражения СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК(B10:B19)))-1 в качестве второго аргумента функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ() подается не один диапазон, а несколько ( равного числу строк ). Если строка скрыта, то вместо цены выводится значение Пустой текст «» , которое игнорируется функцией СРГЕОМ() . Таким образом, подсчитывается среднее геометрическое цен товара Товар1 .
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() с условием
Предположим, что у нас есть таблица продаж товаров (см. файл примера, лист3, диапазон А9:С19).
Автофильтром мы планируем отбирать определенный вид товаров, например, Товар1. Также дополнительно, нам нужно посчитать сумму Продаж отобранных Товаров1, Продажи которых более 50 млн. руб.
Это сделать легко стандартными средствами. Сначала по столбцу Товар выбираем Товар1.
Затем настраиваем фильтр по столбцу Продажи.
И, через 4 клика мышкой имеем:
Но, есть и альтернативный вариант подсчета промежуточных итогов без настраивания фильтра по второму столбцу.
Очистим фильтр у второго столбца.
В желтой ячейке Е7 над таблицей введем значение критерия = 50. В другой ячейке запишем довольно длинную формулу:
В результате в ячейке В7 будет получен тот же результат, что и при применении Автофильтра (92).
Этот вариант удобнее, если часто приходится изменять значения фильтрации по второму столбцу. Чтобы лишний раз не кликать, вычисления можно переложить на формулы. Посмотрим как работает эта формула.
Функция СУММПРОИЗВ() имеет аргумент, состоящий из 3х умножаемых.
- ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;СМЕЩ($B$10;СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК(B10:B19)))-1;))
- B10:B19
- —((B10:B19)>$E$7)
Первое умножаемое возвращает массив 0 и 1 вида: <1:1:1:1:1:0:0:0:0:0>. Если строка скрыта фильтром, то возвращается 0, если нет, то 1. Обычно ПРОМЕЖУТОЧНЫЕ.ИТОГИ() возвращает только одно число, а не массив чисел. Чтобы заставить эту функцию вернуть массив понадобилось выражение СМЕЩ($B$10;СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК(B10:B19)))-1;), которое возвращает массив чисел из диапазона B10:B19. Если бы мы указали ссылку на этот же диапазон, т.е. ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;B10:B19), то функция вернула бы просто число и мы не получили бы нужный результат. Т.е. передавая в качестве аргумента массив чисел (а не ссылку на диапазон, содержащий этот же массив) мы заставили функцию вернуть массив.
Второе умножаемое — это ссылка на столбец с продажами. Если в строке формул выделить B10:B19 и нажать F9 , то получим массив
Третье — возвращает массив <0:0:0:1:0:0:0:1:0:1>. Если в строке значение Продаж больше 50, то возвращается 1, если нет, то 0.
Функция СУММПРОИЗВ() построчно перемножает 3 полученных массива. В результате получим 92 (т.к. только в 4й строке у нас не скрытая строка и Продажи более 50).
Теперь, изменив в желтой ячейке критерий, мы сразу же получим нужный результат (4 товара1 удовлетворяют условиям).
Если кому-то подобные формулы сложно воспринимать, то можно использовать дополнительный столбец с формулой проверки выполнения условия и стандартный вид функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ()