Промежуточные итоги в excel как сделать

Подвести промежуточные итоги в таблице Excel можно с помощью встроенных формул и соответствующей команды в группе «Структура» на вкладке «Данные».

Важное условие применения средств – значения организованы в виде списка или базы данных, одинаковые записи находятся в одной группе. При создании сводного отчета промежуточные итоги формируются автоматически.

Чтобы продемонстрировать расчет промежуточных итогов в Excel возьмем небольшой пример. Предположим, у пользователя есть список с продажами определенных товаров:

Необходимо подсчитать выручку от реализации отдельных групп товаров. Если использовать фильтр, то можно получить однотипные записи по заданному критерию отбора. Но значения придется подсчитывать вручную. Поэтому воспользуемся другим инструментом Microsoft Excel – командой «Промежуточные итоги».

Чтобы функция выдала правильный результат, проверьте диапазон на соответствие следующим условиям:

• Таблица оформлена в виде простого списка или базы данных.
• Первая строка – названия столбцов.
• В столбцах содержатся однотипные значения.
• В таблице нет пустых строк или столбцов.

Приступаем…

1. Отсортируем диапазон по значению первого столбца – однотипные данные должны оказаться рядом.



2. Выделяем любую ячейку в таблице. Выбираем на ленте вкладку «Данные». Группа «Структура» — команда «Промежуточные итоги».



3. Заполняем диалоговое окно «Промежуточные итоги». В поле «При каждом изменении в» выбираем условие для отбора данных (в примере – «Значение»). В поле «Операция» назначаем функцию («Сумма»). В поле «Добавить по» следует пометить столбцы, к значениям которых применится функция.



4. Закрываем диалоговое окно, нажав кнопку ОК. Исходная таблица приобретает следующий вид:

Если свернуть строки в подгруппах (нажать на «минусы» слева от номеров строк), то получим таблицу только из промежуточных итогов:

При каждом изменении столбца «Название» пересчитывается промежуточный итог в столбце «Продажи».

Чтобы за каждым промежуточным итогом следовал разрыв страницы, в диалоговом окне поставьте галочку «Конец страницы между группами».

Чтобы промежуточные данные отображались НАД группой, снимите условие «Итоги под данными».

Команда промежуточные итоги позволяет использовать одновременно несколько статистических функций. Мы уже назначили операцию «Сумма». Добавим средние значения продаж по каждой группе товаров.

Снова вызываем меню «Промежуточные итоги». Снимаем галочку «Заменить текущие». В поле «Операция» выбираем «Среднее».



Функция «ПРОМЕЖУТОЧНЫЕ.ИТОГИ» возвращает промежуточный итог в список или базу данных. Синтаксис: номер функции, ссылка 1; ссылка 2;… .

Номер функции – число от 1 до 11, которое указывает статистическую функцию для расчета промежуточных итогов:

1. – СРЗНАЧ (среднее арифметическое);
2. – СЧЕТ (количество ячеек);
3. – СЧЕТЗ (количество непустых ячеек);
4. – МАКС (максимальное значение в диапазоне);
5. – МИН (минимальное значение);
6. – ПРОИЗВЕД (произведение чисел);
7. – СТАНДОТКЛОН (стандартное отклонение по выборке);
8. – СТАНДОТКЛОНП (стандартное отклонение по генеральной совокупности);
9. – СУММ;
10. – ДИСП (дисперсия по выборке);
11. – ДИСПР (дисперсия по генеральной совокупности).

Ссылка 1 – обязательный аргумент, указывающий на именованный диапазон для нахождения промежуточных итогов.

Особенности «работы» функции:

• выдает результат по явным и скрытым строкам;
• исключает строки, не включенные в фильтр;
• считает только в столбцах, для строк не подходит.

• Рассмотрим на примере использование функции:
• Формула для среднего значения промежуточного итога диапазона (для прихожей «Ретро»): .
• Формула для максимального значения (для спален): .

Промежуточные итоги в сводной таблице Excel

В сводной таблице можно показывать или прятать промежуточные итоги для строк и столбцов.

1. При формировании сводного отчета уже заложена автоматическая функция суммирования для расчета итогов.
2. Чтобы применить другую функцию, в разделе «Работа со сводными таблицами» на вкладке «Параметры» находим группу «Активное поле». Курсор должен стоять в ячейке того столбца, к значениям которого будет применяться функция. Нажимаем кнопку «Параметры поля». В открывшемся меню выбираем «другие». Назначаем нужную функцию для промежуточных итогов.
3. Для выведения на экран итогов по отдельным значениям используйте кнопку фильтра в правом углу названия столбца.

1. В меню «Параметры сводной таблицы» («Параметры» — «Сводная таблица») доступна вкладка «Итоги и фильтры».
2. Скачать примеры с промежуточными итогами
3. Таким образом, для отображения промежуточных итогов в списках Excel применяется три способа: команда группы «Структура», встроенная функция и сводная таблица.

Источник: https://exceltable.com/otchety/promejutochnye-itogi-v-excel

Группы и промежуточные итоги в Excel — Microsoft Excel для начинающих

Листы Excel, содержащие большой объем информации, иногда могут выглядеть перегруженными и даже трудночитаемыми. Excel позволяет группировать данные, чтобы с легкостью скрывать и показывать различные разделы листа.

К тому же Вы можете обобщить различные группы при помощи команды Промежуточный итог и придать структуру рабочему листу Excel.

В этом уроке мы рассмотрим оба этих инструмента максимально подробно и на наглядных примерах.

Группировка строк и столбцов в Excel

1. Выделите строки или столбцы, которые необходимо сгруппировать. В следующем примере мы выделим столбцы A, B и C.
2. Откройте вкладку Данные на Ленте, затем нажмите команду Группировать.
3. Выделенные строки или столбцы будут сгруппированы. В нашем примере это столбцы A, B и C.

Чтобы разгруппировать данные в Excel, выделите сгруппированные строки или столбцы, а затем щелкните команду Разгруппировать.

Как скрыть и показать группы

1. Чтобы скрыть группу в Excel, нажмите иконку Скрыть детали (минус).
2. Группа будет скрыта. Чтобы показать скрытую группу, нажмите иконку Показать детали (плюс).

Подведение итогов в Excel

Команда Промежуточный итог позволяет автоматически создавать группы и использовать базовые функции, такие как СУММ, СЧЁТ и СРЗНАЧ, чтобы упростить подведение итогов.

Например, команда Промежуточный итог способна вычислить стоимость канцтоваров по группам в большом заказе.

Ваши данные должны быть правильно отсортированы перед использованием команды Промежуточный итог, Вы можете изучить серию уроков Сортировка данных в Excel, для получения дополнительной информации.

Создание промежуточного итога

В следующем примере мы воспользуемся командой Промежуточный итог, чтобы определить сколько заказано футболок каждого размера (S, M, L и XL). В результате рабочий лист Excel обретет структуру в виде групп по каждому размеру футболок, а затем будет подсчитано общее количество футболок в каждой группе.

1. Прежде всего отсортируйте данные, для которых требуется подвести итог. В этом примере мы подводим промежуточный итог для каждого размера футболок, поэтому информация на листе Excel должна быть отсортирована по столбцу Размер от меньшего к большему.
2. Откройте вкладку Данные, затем нажмите команду Промежуточный итог.
3. Откроется диалоговое окно Промежуточные итоги. Из раскрывающегося списка в поле При каждом изменении в, выберите столбец, который необходимо подытожить. В нашем случае это столбец Размер.
4. Нажмите на кнопку со стрелкой в поле Операция, чтобы выбрать тип используемой функции. Мы выберем Количество, чтобы подсчитать количество футболок, заказанных для каждого размера.
5. В поле Добавить итоги по выберите столбец, в который необходимо вывести итог. В нашем примере это столбец Размер.
6. Если все параметры заданы правильно, нажмите ОК.
7. Информация на листе будет сгруппирована, а под каждой группой появятся промежуточные итоги. В нашем случае данные сгруппированы по размеру футболок, а количество заказанных футболок для каждого размера указано под соответствующей группой.

Просмотр групп по уровням

При подведении промежуточных итогов в Excel рабочий лист разбивается на различные уровни. Вы можете переключаться между этими уровнями, чтобы иметь возможность регулировать количество отображаемой информации, используя иконки структуры 1, 2, 3 в левой части листа. В следующем примере мы переключимся между всеми тремя уровнями структуры.

Хоть в этом примере представлено всего три уровня, Excel позволяет создавать до 8 уровней вложенности.

1. Щелкните нижний уровень, чтобы отобразить минимальное количество информации. Мы выберем уровень 1, который содержит только общее количество заказанных футболок.
2. Щелкните следующий уровень, чтобы отобразить более подробную информацию. В нашем примере мы выберем уровень 2, который содержит все строки с итогами, но скрывает остальные данные на листе.
3. Щелкните наивысший уровень, чтобы развернуть все данные на листе. В нашем случае это уровень 3.

• Вы также можете воспользоваться иконками Показать или Скрыть детали, чтобы скрыть или отобразить группы.

Удаление промежуточных итогов в Excel

Со временем необходимость в промежуточных итогах пропадает, особенно, когда требуется иначе перегруппировать данные на листе Excel. Если Вы более не хотите видеть промежуточные итоги, их можно удалить.

1. Откройте вкладку Данные, затем нажмите команду Промежуточный итог.
2. Откроется диалоговое окно Промежуточные итоги. Нажмите Убрать все.
3. Все данные будут разгруппированы, а итоги удалены.

Чтобы удалить только группы, оставив промежуточные итоги, воспользуйтесь пунктом Удалить структуру из выпадающего меню команды Разгруппировать.

Оцените качество статьи. Нам важно ваше мнение:

Источник: https://office-guru.ru/excel/gruppy-i-promezhutochnye-itogi-v-excel-118.html

Промежуточные итоги в Excel

      Доброго времени суток друзья!

     Эту статью я посвящаю такой возможности, как создать промежуточные итоги в Excel. Если вы даже слышали или знаете, что это такое, всё же будет полезно прочитать или послушать мою информацию. По большому счёту, промежуточные итоги – это автоматическое подведение итогов по заданному критерию и группирование их для удобства и хорошей визуализации данных.

     Это достаточно полезный и удобный инструмент работы в электронных таблицах, который вам пригодится, а для большего удобства я советую использовать эти итоги в тандеме с группированием ячеек, это вообще супер сила. Также я прошу обратить ваше внимание, что перед работой с итогами, необходимо в обязательном порядке произвести правильную сортировку ваших данных.

     Итак, давайте же более детально рассмотрим, что это за итоги, как их создать, как с ними работать, ну и собственно, как их убрать, если нужда в этой возможности отпадёт. И начнём все наши исследования сначала, а точнее с того, как можно создать промежуточный итог.

Как сделать промежуточные итоги в Excel

     А теперь давайте на созданном примере посмотрим поэтапно, как это осуществить:

1. Первым делом нам нужно правильно отсортировать наши данные, для которых мы будем подводить итог. Мы выбираем нужные нам данные, по которым будем создавать промежуточный итог и предварительно выделив всю таблицу, производим сортировку по указанному столбику от меньшего к большему.
2. Вторым этапом будет выбор на вкладке «Данные», в группе «Структура», команду «Промежуточный итог».
3. В диалоговом окне «Промежуточные итоги», во-первых, вам нужно в списке поля «При каждом изменении в» выбрать столбик, для которого создаем промежуточные итоги, во-вторых, в списке поля «Операция» мы выбираем какой тип функции нам нужен (количество, сумма и т.п.), в-третьих, в поле «Добавить итоги» вы определяете столбик по которому будет формироваться итог. После этого вы с чистой совестью вы нажимаете кнопочку «ОК».
4. Данные на вашем листе автоматически будут сгруппированы по признаку, который вы установили, и под каждой группой будет создан промежуточный итог.

Работа со сгруппированными данными

     После проведенных манипуляций, работа с данными значительно упростится. Данные можно удобно визуализировать, используя группировку данных и создавать столько уровней, сколько вам будет удобно, но, увы, не более 8 вложенных, с опыта могу сказать, что этого хватит за глаза.

     Нажимая на иконки группирования данных, вы скрываете и отображаете ту структуру данных, которая вам наиболее актуальна на данный момент.      К примеру, можно отобразить только общий итог, отобразить итоги по группам данных и полный разворот данных в деталях.      Также, всё это можно проделать, используя пиктограммы «Скрыть детали» или «Показать».

Убрать промежуточные итоги

      В случае, когда промежуточные итоги в Excel станут, не нужны, ну или потребуется перегруппировка по-новому ваших данных в таблице, в этом случае итоги можно удалить без потери качества исходных данных. Для этого нужно:

1. Открыть вкладку на панели управления «Данные» и в блоке «Структура» нажимаете на пиктограмму «Промежуточный итог».
2. В появившемся диалоговом окне «Промежуточные итоги» нажимаем кнопку «Убрать всё».
3. После нажатия все ваши данные разгруппируются, а промежуточные итоги будут удалены.

     Если же вам всё-таки промежуточные итогинужно оставить, а вот группирование не нужно, вы можете в панели задач на вкладке на вкладке «Данные» в выпадающем меню кнопки «Разгруппировать» выбрать пункт «Удалить структуру», структура группировки будет удалена, оставив вам на прощание промежуточные итоги.

     А на этом у меня всё! Я очень надеюсь, что всё вышеизложенное вам понятно. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями прочитанным и ставьте лайк!

     Не забудьте поблагодарить автора!

Пока богатство еще не приобретено, стремление к нему изнуряет, будучи же приобретено, оно изводит заботами, когда же оно утрачено, мучает тоска по нем. Демокрит

Статья помогла? Поделись ссылкой с друзьями, твитни или лайкни!

Источник: http://topexcel.ru/promezhutochnye-itogi-v-excel/

Промежуточные итоги в Excel: как работает функция

Промежуточные итоги в Excel – очень удобный инструмент для анализа информации. Воспользоваться им можно несколькими способами: при помощи специального функционала, формул или сводных таблиц.

При этом есть обязательные условия:

• наименования должны повторяться больше одного раза;
• записи должны быть расположены в один столбец.

Рассмотрим каждый способ более детально.

Как вычислить промежуточные итоги в Excel

Чтобы воспользоваться стандартной функцией, нужно выполнить несколько несложных операций.

1. Создайте таблицу, которая соответствует требованиям, описанным выше.

1. Выделите весь диапазон значений. Используйте иконку сортировки. Выберите инструмент «от А до Я».

1. Благодаря этому все клетки будут отсортированы по наименованию товара.

1. После этого сделайте активной какую-нибудь клетку (необязательно выделять всё целиком). Перейдите на вкладку «Данные». Используйте инструмент «Структура».

1. В появившемся меню выберите указанную кнопку.
2. Далее укажите нужную операцию. Чтобы продолжить, нажмите на «OK».

1. Исход будет следующим. В левой части редактора появятся дополнительные кнопки.

1. Если хотите, можете нажать на иконку «-». Благодаря этому будет происходить группировка ячеек. Вследствие данного действия останутся только результаты расчетов.

Если этот фильтр вам не нравится, вы можете вернуть всё обратно при помощи кнопок с символом «+».

Вложенность имеет несколько уровней. Можно свернуть всю информацию целиком.

Как сделать несколько результатов

Для этой цели потребуется выполнить следующие шаги.

1. Допустим, что у вас уже есть какое-нибудь подведение итогов. Мы будем использовать наш пример. Сделайте активной любую ячейку. Затем нажмите на инструмент «Структура».

1. После этого снова выбираем знакомый нам пункт.

1. Далее выбираем какую-нибудь другую операцию для расчета. Обязательно уберите галочку возле пункта «Заменить текущие итоги».

  Как прибавить НДС 20% к сумме: формула

1. Только после этого можно нажать на кнопку «OK».

1. Благодаря этому вы увидите, что теперь в таблице несколько промежуточных значений.

Как убрать промежуточные итоги

Процесс происходит почти так же, как при создании.

1. Выделите весь диапазон клеток. Нажмите на пункт меню «Структура».

1. Выберите указанный инструмент.

1. Кликните на кнопку «Убрать всё».

1. В результате этого редактор Эксель примет свой прежний вид.

Таким способом вы сможете удалить любое количество промежуточных подсчетов.

Расчёты при помощи функции

Помимо описанного выше метода, в данном редакторе существует специальная команда, при помощи которой можно вывести итоговое значение в какой-нибудь другой клетке. То есть она может находиться даже за пределами этой таблицы.

Для этого достаточно совершить несколько простых действий.

1. Перейдите на любую клетку. Нажмите на иконку «Fx». В окне «Вставка функции» выберите категорию «Полный алфавитный перечень». Найдите там нужный пункт. Кликните на кнопку «OK».

1. После этого появится окно, в котором вас попросят указать аргументы функции.

В первом поле необходимо указать номер нужной операции. Более подробно можно прочитать на официальном сайте Microsoft. В этом случае английский знать не нужно – вся информация доступна на русском языке.

Обратите внимание на то, что здесь используется формула для подсчета обычной суммы. Функция СУММЕСЛИ в этом списке отсутствует.

1. В качестве примера поставим цифру 9.

1. Затем нужно кликнуть на второе поле, иначе выделенный диапазон ячеек подставится в первую строчку, а это приведет к ошибке.

1. Затем выделите какие-нибудь значения. В нашем случае посчитаем сумму продаж первого товара. В конце кликаем на «OK».

1. Результат будет следующим.

В данном случае мы посчитали сумму только для одного товара. Если хотите, можете повторить те же самые действия для каждого пункта по отдельности. Либо просто скопировать формулу и вручную исправить диапазон ячеек. Кроме этого, желательно оформить расчеты так, чтобы было понятно, какому именно товару соответствует результат вычислений.

  Как в Excel сделать нумерацию по порядку автоматически

Использование сводной таблицы Excel

Чтобы воспользоваться данной возможностью, нужно сделать следующее:

1. Укажите нужный диапазон.
2. Перейдите на вкладку «Вставка».
3. Используйте инструмент «Таблица».
4. Выберите нужный пункт.

1. Подберите что-то самое оптимальное для вас. Количество вариантов зависит от выделенной информации.
2. После этого кликните на «OK».

1. По умолчанию редактор Excel попробует сложить все значения в таблице.

Для того чтобы это изменить, нужно выполнить следующие шаги:

1. Сделайте двойной клик по заголовку указанного поля.
2. Выберите нужную вам операцию. Например, «Среднее значение».
3. Сохраняем изменения нажатием на «OK».

1. Вследствие этого вы увидите следующее:

Заключение

В данном самоучителе мы рассмотрели все основные способы создания промежуточных итогов в редакторе Эксель. Если у вас что-то не получилось, возможно, ваша информация не отвечает указанным в начале требованиям.

Видеоинструкция

Если у вас возникли какие-нибудь трудности, попробуйте посмотреть приложенное ниже видео. Там рассмотрены различные нюансы с дополнительными ми.

Источник: https://os-helper.ru/excel/promezhutochnye-itogi.html

Как быстро посчитать итоги в таблице?

Допустим у нас есть некая таблица. Мы «нарисовали» ее сами или получили в виде отчета из учетной системы. В ней все хорошо только нет итогов. Придется вводить формулы и потом их копировать. В зависимости от размеров таблицы этот процесс может занять длительное время. В этой статье я расскажу, как это можно сделать очень быстро.

Итак, приступим. Есть у нас вот такая таблица:

Мы выделяем все числовые данные плюс одну строку снизу и один столбец справа, чтобы получилось так:

• Переходим во вкладку «Главная» и в разделе «Редактирование» нажимаем кнопку «Автосумма«:
• 
• И MS Excel все сделал за нас:
• 
• Нам осталось только написать недостающие названия строки и столбца.
• А если наша таблица выглядит чуть сложнее и нам недостаточно посчитать общие итоги? Давайте разберем такую таблицу:

Помимо общих итогов по столбцам и строкам, нам необходимо получить промежуточные итоги по менеджерам, чтобы было так:

Давайте приступим к решению. Выделяем всю таблицу, переходим во вкладку меню «Данные» и в разделе «Структура» нажимаем кнопку «Промежуточный итог«:

В открывшемся диалоговом окне в списке «Добавить итоги по:» устанавливаем галочки напротив пунктов «Январь«, «Февраль«, «Март» и нажимаем кнопку «ОК«:

После чего наша таблица приобретет следующий вид:

Если вас «напрягают» плюсики и минусики, образовавшиеся слева, во вкладке «Данные» в разделе «Структура» нажмите кнопку «Разгруппировать«, в выпавшем списке нажмите пункт «Удалить структуру«:

1. Структура исчезнет.
2. Что делать дальше вы уже, наверное, догадались? Выделяем все числовые данные и один пустой столбец справа, во вкладке меню «Главная» в разделе «Редактирование» нажимаем «Автосумма«:
3. Таблица приобретет такой вид:
4. Форматируем необходимые строки и столбцы и получаем что хотели:

Источник: https://e-xcel.ru/nachinayushchim/kak-bystro-poschitat-itogi-v-tablitse?_utl_t=vk

Промежуточные итоги в Excel

14 Авг

Добрый день, уважаемые читатели! Сегодня речь пойдёт о формуле ПРОМЕЖУТОЧНЫЕ ИТОГИ. Для меня самым большим вопросом всегда оставалось предназначение этой формулы. В этой статье я постараюсь раскрыть суть наших манипуляций с данной функцией.

Судя по названию, формула подводит итоги (т.е. суммирует) нужные нам показатели. Чтобы было нагляднее давайте представим себе следующую таблицу. 

Если мне по каким-либо причинам понадобится скрыть отдельные строки (фильтр, к примеру) обычная формула автосуммы (СУММ), всё равно будет включать даже скрытые значения.

А что делать если я хочу сделать отбор только по контрагенту «Юлмарт» и посчитать итоги только по нему? Вот здесь и пригодится формула промежуточных итогов.

К слову, как только мы включаем фильтр, Excel, при нажатии на кнопку автосуммы, будет преобразовывать формулу в промежуточные итоги самостоятельно, то есть учитывая отфильтрованное содержимое таблицы. 

Взглянем на формулу — =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;[Сумма]). Нас особенно будут интересовать аргументы в скобках, так как название формулы, думается, пояснять не стоит.

В скобках мы видим загадочную цифру 109 и слово Сумма. Здесь тоже ничего загадочного нет, так как взглянув на справку от компании Майкрософт всё тут же станет ясно.

Цифра 109 обозначает что нужно делать, если включён фильтр (в названии столбца с сотнями так и написано — «с исключением скрытых значений» если же нам нужно «с включением скрытых значений» просто заменим на нужный элемент из таблицы). Если бы мне понадобилось другое значение, к примеру, 104 — максимальное значение в таблице, я просто могу от руки поправить формулу или просто выбрать её из выпадающего списка.

Видите, мне тут же покажет максимальную сумму продажи другому контрагенту — «Топкомпьютер». Аналогично можно попробовать и минимальное значение.

Также можно исправлять всё не вручную, а использовать открывающийся список после нашей ячейки с формулой.

Если же вы всё-таки решили исправлять руками, программа не оставит вас без подсказок.

Вот и всё! Функция ПРОМЕЖУТОЧНЫЕ ИТОГИ поможет вам быстро производить расчёты даже при скрытых данных!

Если что-то осталось непонятным — смотрите наше новое видео! А вот ССЫЛКА на таблицу из статьи! Наша ПРОШЛАЯ СТАТЬЯ про функцию «Заменить»! 

Источник: http://pcandlife.ru/promezhutochnie-itogi-v-excel/

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() MS EXCEL

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ(), английский вариант SUBTOTAL(), используется для вычисления промежуточного итога (сумма, среднее, количество значений и т.д.) в диапазоне, в котором имеются скрытые строки.

Особенность функции состоит в том, что она предназначена для использования совместно с другими средствами 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 других функций (см. синтаксис).

Как правило, этик функций вполне достаточно, но иногда требуется расширить возможности функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ(). Рассмотрим пример вычисления среднего геометрического для отобранных автофильтром значений.

Функция СРГЕОМ() отсутствует среди списка функций доступных через соответствующие коды, но выход есть.

Воспользуемся той же исходной таблицей.

Применим Автофильтр и отберем только строки с товаром Товар1. Пусть функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() подсчитает среднее геометрическое цен товаров Товар1 (пример не очень жизненный, но он показывает принцип). Будем использовать код функции 3 — подсчет значений.

Для подсчета будем использовать формулу массива (см. файл примера, лист2)

=СРГЕОМ(ЕСЛИ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;СМЕЩ($B$10;СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК(B10:B19)))-1;));B10:B19;»»))

С помощью выражения СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК(B10:B19)))-1 в качестве второго аргумента функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ() подается не один диапазон, а несколько (равного числу строк). Если строка скрыта, то вместо цены выводится значение Пустой текст «», которое игнорируется функцией СРГЕОМ(). Таким образом, подсчитывается среднее геометрическое цен товара Товар1.

Источник: https://excel2.ru/articles/funkciya-promezhutochnyeitogi-ms-excel-promezhutochnyeitogi

Промежуточные итоги в Excel

Раздел «Структура», помимо группировки и разгруппировки данных, имеет еще одну функцию – «Промежуточные итоги». С ее помощью Вы можете задать автоматическую группировку для данных с подведенным по ним итогом. Чтобы стало понятнее, о чем идет речь, рассмотрим в виде примера таблицу по сотрудникам с результатами их продаж за квартал.

• 
• Выделив таблицу и нажав на значок «Промежуточные итоги», Excel выдаст окно:
• 

В поле «При каждом изменении в:» необходимо указать заголовок столбца таблицы по которому будет определяться подведение итогов. В качестве заголовка выступают ячейки верхних строк выделенной таблицы. Надписи в ячейках, указанного столбца, будут служить заголовками строк.

Программа будет проверять заголовки на сходство. Если заголовки строк совпадают, то она добавляет его в подсчет итога, если не совпадают, то дополнительной строкой выводится результат по группе, определенных одним заголовком.

Поэтому рекомендуем произвести заранее сортировку по данному столбцу.

Во втором поле «Операция:» выбирается какой итог необходимо подвести: Сумма, количество, среднее и т.д.. В последнем поле «Добавить итог по:» выбирается столбец, который содержит необходимые данные для вывода результата.

Ниже располагается 3 чекбокса (флага):

• Заменить текущие итоги – если установлен, то итоги, подведенные ранее, будут заменены, если нет, то к старым результатам добавятся новые;
• Конец страницы между группами – если установлен, то при выводе на печать каждая отдельная группа с итогом будет располагаться на новом листе;
• Итоги под данными – если установлен, то результат будет располагаться в конце группы, если нет, то в начале.

1. Кнопка «Убрать все» удаляет всю структуру данных.
2. Так преобразовалась таблица, после применения к ней описываемой функции:
3. 

Добавленные новые ячейки содержат в себе математическую функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ(), ознакомиться с которой можно в статье про математические функции — Математические функции Excel.

Механизм подведения промежуточных итогов имеет общие черты с другим более мощным инструментом приложения Excel – сводные таблицы.

Источник: http://office-menu.ru/uroki-excel/13-uverennoe-ispolzovanie-excel/20-promezhutochnye-itogi-v-excel