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

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

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

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

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

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

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

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

Приступаем…

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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



Функция «ПРОМЕЖУТОЧНЫЕ.ИТОГИ» возвращает промежуточный итог в список или базу данных. Синтаксис: номер функции, ссылка 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.Промежуточные итоги в excel как сделать
  2. Откройте вкладку Данные на Ленте, затем нажмите команду Группировать.Промежуточные итоги в excel как сделать
  3. Выделенные строки или столбцы будут сгруппированы. В нашем примере это столбцы A, B и C.Промежуточные итоги в excel как сделать

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

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

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

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

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

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

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

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

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

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

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

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

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

При подведении промежуточных итогов в 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. Если вы даже слышали или знаете, что это такое, всё же будет полезно прочитать или послушать мою информацию. По большому счёту, промежуточные итоги – это автоматическое подведение итогов по заданному критерию и группирование их для удобства и хорошей визуализации данных.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Читайте также:  Как сделать дробь в powerpoint 2007?

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

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

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

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

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

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

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

  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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

14 Авг

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

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

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

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

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

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

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

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

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

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

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

Читайте также:  Как сделать тест в access?

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

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

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

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

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

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

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

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

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

Источник: 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;… (вложенные итоги), то эти вложенные итоги игнорируются, чтобы избежать двойного суммирования.

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

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() и Автофильтр

Пусть имеется исходная таблица.

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

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

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

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

Если бы мы записали формулу =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;B11:B20) или =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(103;B11:B20), то мы бы подсчитали число отобранных фильтром значений (5).

Таким образом, эта функция «чувствует» скрыта ли строка автофильтром или нет. Это свойство используется в статье Автоматическая перенумерация строк при применении фильтра.

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() и Скрытые строки

Пусть имеется та же исходная таблица. Скроем строки с товаром Товар2  через меню или через контекстное меню.

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

В этом случае имеется разница между использованием кода функции СУММ(): 9 и 109. Функция с кодом 109 «чувствует» скрыта строка или нет.

Другими словами для диапазона кодов номер_функции от 101 до 111 функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() исключает значения строк скрытых при помощи команды Главная/ Ячейки/ Формат/ Скрыть или отобразить.

Эти коды используются для получения промежуточных итогов только для не скрытых чисел списка.

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() и средство EXCEL Промежуточные итоги

Пусть имеется также исходная таблица. Создадим структуру с использованием встроенного средства EXCEL — Промежуточные итоги.

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

Скроем строки с Товар2, нажав на соответствующую кнопку «минус» в структуре.

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

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

Другие функции

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  • Заменить текущие итоги – если установлен, то итоги, подведенные ранее, будут заменены, если нет, то к старым результатам добавятся новые;
  • Конец страницы между группами – если установлен, то при выводе на печать каждая отдельная группа с итогом будет располагаться на новом листе;
  • Итоги под данными – если установлен, то результат будет располагаться в конце группы, если нет, то в начале.
  1. Кнопка «Убрать все» удаляет всю структуру данных.
  2. Так преобразовалась таблица, после применения к ней описываемой функции:
  3. Промежуточные итоги в excel как сделать

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

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

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

Ссылка на основную публикацию
Adblock
detector