Советы и лайфхаки

Эксель промежуточные итоги формула – Промежуточные итоги в Excel с примерами функций

Содержание

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ - Служба поддержки Office

В этой статье описаны синтаксис формулы и использование функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ в Microsoft Excel.

Описание

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

Синтаксис

ПРОМЕЖУТОЧНЫЕ.ИТОГИ(номер_функции;ссылка1;[ссылка2];…])

Аргументы функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ описаны ниже.

  • Номер_функции    (обязательный). Число от 1 до 11 или от 101 до 111, которое обозначает функцию, используемую для расчета промежуточных итогов. Функции с 1 по 11 учитывают строки, скрытые вручную, в то время как функции с 101 по 111 пропускают такие строки; отфильтрованные ячейки всегда исключаются.

Номер_функции
(с включением скрытых значений)

Номер_функции
(с исключением скрытых значений)

Функция

1

101

СРЗНАЧ

2

102

СЧЁТ

3

103

СЧЁТЗ

4

104

МАКС

5

105

МИН

6

106

ПРОИЗВЕД

7

107

СТАНДОТКЛОН

8

108

СТАНДОТКЛОНП

9

109

СУММ

10

110

ДИСП

11

111

ДИСПР

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

  • Ссылка2;...     Необязательный. Именованные диапазоны или ссылки 2—254, для которых требуется вычислить промежуточные итоги.

Примечания

  • Если уже имеются формулы подведения итогов внутри аргументов "ссылка1;ссылка2;..." (вложенные итоги), то эти вложенные итоги игнорируются, чтобы избежать двойного суммирования.

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

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

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

  • Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ применяется к столбцам данных или вертикальным наборам данных. Она не предназначена для строк данных или горизонтальных наборов данных. Так, при определении промежуточных итогов горизонтального набора данных с помощью значения константы "номер_функции" от 101 и выше (например, ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;B2:G2)), скрытие столбца не повлияет на результат. Однако на него повлияет скрытие строки при подведении промежуточного итога для вертикального набора данных.

  • Если среди ссылок есть трехмерные ссылки, функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ возвращает значение ошибки #ЗНАЧ!.

Пример

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

Данные

120

10

150

23

Формула

Описание

Результат

=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;A2:A5)

Значение промежуточного итога диапазона ячеек A2:A5, полученное с использованием числа 9 в качестве первого аргумента.

303

=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(1;A2:A5)

Среднее значение промежуточного итога диапазона ячеек A2:A5, полученное с использованием числа 1 в качестве первого аргумента.

75,75

Примечания

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

support.office.com

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

Исправляем ошибки в Excel при расчетах Формула массива Excel. Как пользоваться?

Если у вас есть таблица данных, то посчитать сумму по столбцу можно обычной функцией =СУММ(). Вы наверное, замечали, что в случае отбора фильтром определенных значений такая функция работает неверно. Как посчитать сумму только отобранных значений. Для этого есть возможность создать промежуточные итоги в Excel.

Функция промежуточные итоги в Excel имеет 2 аргумента:

1. Номер_функции. Это функция которой нужно воспользоваться для расчета итогов.

2. Ссылка. Это тот диапазон по которому нужно найти итог.

Пример. Посчитайте сумму по столбцу Сумма для всех месяцев где продано больше 20 единиц. 

Как вы видите на первой картинке, мы отобрали фильтром все значение по столбцу «Продано единиц» большие 20. В ячейки С15 записываем формулу вида

=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;C2:C13)

Значение функции будет меняться если изменить условия фильтра.

Пример

Прошу не забывать, что это же действие можно сделать функцией =СУММЕСЛИ()

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

Как посчитать только те ячейки, которые вы не делали скрытыми?

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

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

т.е. с промежуточными итогами по каждой группе строк, например по полугодиям в нашем примере.

Что нужно сделать:

1. Убедитесь что данные отсортированы, хотя бы по одному столбцу

2. Выберите диапазон

3. Зайдите в ленте инструментов в меню Данные, раздел Структура и выберите Промежуточные итоги

В открывшемся меню делаем настройки, выбираем по какому столбцу сортироваться (Полугодие) и что делать (Операция), а так же в каком столбце проводить расчет:

Должно получиться как на рисунке выше.

Пример2

Очень удобно, когда таблица большая и нужно отсортировать по номенклатуре, кварталу, счету и т.д.

Важно отметить:

— при таком способе расчета итогов Excel группирует данные (плюсы слева на панели), т.е. вы можете скрывать лишние данные. Подробнее здесь

— если используете возможность Таблица (Вставка -Таблица) в эту интерактивную таблиц при расчете суммы автоматом добавляется «Строка итогов». Которая во многом заменяет промежуточные итоги. В открывшейся вкладке Работа с таблицами – Конструктор вы можете настроить интерактивную таблицу почти под любые условия. Но я, честно говоря, пока почти не использую эту возможность, т.к. такая таблица даже на 10 тысяч строк значительно грузить систему. Хотя в Excel я работаю до 500 тыс. строк без проблем, даже без PowerPivot.

Поделитесь нашей статьей в ваших соцсетях:

Похожие статьи

(Visited 3 845 times, 10 visits today)

Исправляем ошибки в Excel при расчетах Формула массива Excel. Как пользоваться?

excelworks.ru

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Заключение

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

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

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

os-helper.ru

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

 

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

topexcel.ru

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

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

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

Кстати, чтобы эффективнее работать с таблицами можете ознакомиться с нашим материалом Горячие клавиши Excel — Самые необходимые варианты.

Содержание: 

Листы документа Excel содержат большое количество информации. Часто просматривать и искать данные неудобно.

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

С их помощью можно легко искать нужные поля и столбцы, а также скрывать информацию, которая в этот момент не нужна.

Обобщить несколько групп листа можно с помощью функции «Промежуточные итоги».

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

Синтаксис функции

В программе Excel опция отображается в виде ПРОМЕЖУТОЧНЫЕ.ИТОГИ (№; ссылка 1; ссылка 2; ссылка3;…;ссылкаN), где номер – обозначение функции, ссылка – столбец, по которому подводится итог.

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

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

Синтаксис опции итогов указан в таблице:

Синтаксис

Действие

1-СРЗНАЧ

Определение среднего арифметического двух и более значений.

2-СЧЁТ

Расчет количества чисел, которые представлены в списке аргументов.

3-СЧЁТЗ

Подсчёт всех непустых аргументов.

4-МАКС

Показ максимального значения из определяемого набора чисел.

5-МИН

Показ минимального значения из определяемого набора чисел.

6-ПРОИЗВЕД

Перемножение указанных значений и возврат результата.

7-СТАНД ОТКЛОН

Анализ стандартного отклонения каждой выборки.

8-СТАД ОТКЛОН П

Анализ отклонения по общей совокупности данных.

9-СУММ

Возвращает сумму выбранных чисел.

10-ДИСП

Анализ выборочной дисперсии.

11-ДИСПР

Анализ общей дисперсии.

Пример реализации промежуточных итогов

Выполнение промежуточных итогов включает в себя такие операции:

  • Группировка данных;
  • Создание итогов;
  • Создание уровней для групп.

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

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

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

С помощью мышки выделите столбцы А, В, С, как показано на рисунке ниже. 

Рис.2 – выделение нескольких столбцов

2 Теперь, сохраняя выделение, откройте на панели инструментов программы поле «Данные». Затем в правой части окна опций найдите иконку «Структура» и нажмите на неё; 

3В выпадающем списке нажмите на «Группировать». Если вы ошиблись на этапе выделения нужных столбиков таблицы, нажмите на «Разгруппировать» и повторите операцию заново. 

Рис.3 – группировка информации

4 В новом окне выберите пункт «Строки» и нажмите на «ОК»;

Рис. 4 – группировка строк

5 Теперь снова выделите столбцы А, В, С и проведите группировку, но уже по столбцам: 

Рис.5 – создание группы столбцов

Рис.6 – панель управления группами

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

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

Рассмотрим практическое использование этой функции.

Результатом выполнения действия будет появление групп, которые отсортированы по каждому размеру вещи. Следуйте инструкции:

  • Определите, для каких данных нужно проводит итоговые вычисления. В нашем случае это столбец «Размер». Его содержимое нужно отсортировать. Сортировка проводится от большего элемента к меньшему. Выделяем столбец «Размер»;

  • Далее необходимо перейди во вкладку «Данные» на панели инструментов;

  • Найдите поле «Сортировка» и нажмите на него;

  • В появившемся окне выберите пункт «Сортировать в диапазоне указных значений» и нажмите на клавишу «ОК».

Рис.7 – сортировка содержимого столбца

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

Рис.8 – результат сортировки

Теперь перейдем к реализации функции «Промежуточный итог»:

  • На панели инструментов программы откройте поле «Данные»;
  • Выберите плитку «Структура»;
  • Нажмите на «Промежуточные итоги»;

Рис.9 – расположение опции на панели инструментов

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

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

  • В писке «Добавить итоги по» вы выбираете тот столбец, в котором будут отображаться промежуточные итоги. Выбираем пункт «Размер»;

  • После настройки всех параметров еще раз перепроверьте введённые значения и нажмите на ОК.

Рис.10 – обработка результата

В результате, на листе отобразится таблица в сгруппированном по размерам виде. Теперь очень легко посмотреть все заказанные вещи размера Small, Extra Large и так далее.

Рис.11 – результат выполнения функции

Как видно из рисунка выше, все итоги выводятся между показанными группами в новой строчке.

Итог для одежды с размером Small – 5 штук, для одежды с размером Extra Large – 2 штуки. Под таблицей показывается и общее количество элементов.

Уровни групп

Просматривать группы можно еще и по уровням.

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

Нажмите на первый второй или третий уровень в панели управления группами. Выберите наиболее подходящий вариант представления информации:

Рис.12 – просмотр второго уровня

Удаление итогов

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

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

Чтобы удалить итог, выберите вкладку «Данные»—«Структура»—«Промежуточные итоги».

В открывшемся окне нажмите на клавишу «Убрать все» и подтвердите действие кнопкой «ОК».

Рис.13 – удаление данных

Рекомендовать

ddr64.ru

Промежуточные итоги в Excel (Эксель): примеры использования (2018)

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

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

Кстати, чтобы эффективнее работать с таблицами можете ознакомиться с нашим материалом Горячие клавиши Excel — Самые необходимые варианты.

Содержание: 

Листы документа Excel содержат большое количество информации. Часто просматривать и искать данные неудобно.

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

С их помощью можно легко искать нужные поля и столбцы, а также скрывать информацию, которая в этот момент не нужна.

Обобщить несколько групп листа можно с помощью функции «Промежуточные итоги».

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

Синтаксис функции

В программе Excel опция отображается в виде ПРОМЕЖУТОЧНЫЕ.ИТОГИ (№; ссылка 1; ссылка 2; ссылка3;…;ссылкаN), где номер – обозначение функции, ссылка – столбец, по которому подводится итог.

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

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

Синтаксис опции итогов указан в таблице:

Синтаксис

Действие

1-СРЗНАЧ

Определение среднего арифметического двух и более значений.

2-СЧЁТ

Расчет количества чисел, которые представлены в списке аргументов.

3-СЧЁТЗ

Подсчёт всех непустых аргументов.

4-МАКС

Показ максимального значения из определяемого набора чисел.

5-МИН

Показ минимального значения из определяемого набора чисел.

6-ПРОИЗВЕД

Перемножение указанных значений и возврат результата.

7-СТАНД ОТКЛОН

Анализ стандартного отклонения каждой выборки.

8-СТАД ОТКЛОН П

Анализ отклонения по общей совокупности данных.

9-СУММ

Возвращает сумму выбранных чисел.

10-ДИСП

Анализ выборочной дисперсии.

11-ДИСПР

Анализ общей дисперсии.

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

вернуться к меню ↑

Пример реализации промежуточных итогов

Выполнение промежуточных итогов включает в себя такие операции:

  • Группировка данных;
  • Создание итогов;
  • Создание уровней для групп.

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

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

Группа таблиц – это перечень взаимосвязанных элементов Excel. Удобство этой функции заключается в том, что при переносе одной строки из списка сгруппированных элементов, на новое место будет перемещена вся группа. Аналогичным образом проводятся и расчеты с применением стандартных функций – для всех группы вместе. 

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

С помощью мышки выделите столбцы А, В, С, как показано на рисунке ниже. 

Рис.2 – выделение нескольких столбцов

 

2 Теперь, сохраняя выделение, откройте на панели инструментов программы поле «Данные». Затем в правой части окна опций найдите иконку «Структура» и нажмите на неё; 

3В выпадающем списке нажмите на «Группировать». Если вы ошиблись на этапе выделения нужных столбиков таблицы, нажмите на «Разгруппировать» и повторите операцию заново. 

Рис.3 – группировка информации

4 В новом окне выберите пункт «Строки» и нажмите на «ОК»;

Рис. 4 – группировка строк

5 Теперь снова выделите столбцы А, В, С и проведите группировку, но уже по столбцам: 

Рис.5 – создание группы столбцов

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

Рис.6 – панель управления группами

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

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

Рассмотрим практическое использование этой функции.

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

Результатом выполнения действия будет появление групп, которые отсортированы по каждому размеру вещи. Следуйте инструкции:

  • Определите, для каких данных нужно проводит итоговые вычисления. В нашем случае это столбец «Размер». Его содержимое нужно отсортировать. Сортировка проводится от большего элемента к меньшему. Выделяем столбец «Размер»;

  • Далее необходимо перейди во вкладку «Данные» на панели инструментов;

  • Найдите поле «Сортировка» и нажмите на него;

  • В появившемся окне выберите пункт «Сортировать в диапазоне указных значений» и нажмите на клавишу «ОК».

Рис.7 – сортировка содержимого столбца

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

Рис.8 – результат сортировки

Теперь перейдем к реализации функции «Промежуточный итог»:

  • На панели инструментов программы откройте поле «Данные»;
  • Выберите плитку «Структура»;
  • Нажмите на «Промежуточные итоги»;

Рис.9 – расположение опции на панели инструментов

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

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

  • В писке «Добавить итоги по» вы выбираете тот столбец, в котором будут отображаться промежуточные итоги. Выбираем пункт «Размер»;

  • После настройки всех параметров еще раз перепроверьте введённые значения и нажмите на ОК.

Рис.10 – обработка результата

В результате, на листе отобразится таблица в сгруппированном по размерам виде. Теперь очень легко посмотреть все заказанные вещи размера Small, Extra Large и так далее.

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

Рис.11 – результат выполнения функции

Как видно из рисунка выше, все итоги выводятся между показанными группами в новой строчке.

Итог для одежды с размером Small – 5 штук, для одежды с размером Extra Large – 2 штуки. Под таблицей показывается и общее количество элементов.

вернуться к меню ↑

Уровни групп

Просматривать группы можно еще и по уровням.

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

Нажмите на первый второй или третий уровень в панели управления группами. Выберите наиболее подходящий вариант представления информации:

Рис.12 – просмотр второго уровня

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

вернуться к меню ↑

Удаление итогов

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

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

Чтобы удалить итог, выберите вкладку «Данные»—«Структура»—«Промежуточные итоги».

В открывшемся окне нажмите на клавишу «Убрать все» и подтвердите действие кнопкой «ОК».

Рис.13 – удаление данных

Тематические видеоролики:

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

Проголосовать

geek-nose.com

Изменяемые диапазоны и функция промежуточные итоги в Excel

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

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

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

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



Так как размер нашей клиентской базы неопределенный мы будем использовать динамические имена с изменяемыми диапазонами:

  1. Выберите инструмент: «Формулы»-«Определенные имена»-«Присвоить имя».
  2. В диалоговом окне «Создание имени» в первое поле вводим слово «клиенты», а для поля «Диапазон:» вводим функцию со следующими параметрами: =СМЕЩ($A$1;1;0;СЧЁТЗ($A:$A)-1;1)
  3. В ячейку F1 введите формулу: =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3; клиенты)/СЧЁТЗ(клиенты)
  4. Перейдите на любую ячейку нашей базы в диапазоне A1:C11 и выберите инструмент: «Данные»-«Сортировка и фильтр»-«Фильтр».
  5. Отфильтруйте данные по городу «Москва» (столбец C), а в ячейке F1 сразу же отобразиться процент наших клиентов приживающих в данном городе.

При создании динамического имени, мы использовали функцию СЧЁТЗ($A:$A)-1. Данная функция считает количество заполненных ячеек в столбце A. Но нам нужно было исключить заголовок столбца «Имя» поэтому сразу после функции стоит -1.

В ячейке F1 мы использовали функцию =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3; клиенты). Первым параметром мы указываем, какую функцию использовать для нашего динамического диапазона «клиенты». Код параметр 3 (или 103) – это использование той же функции СЧЁТЗ() только уже для отфильтрованного результата. То есть считаются только те непустые ячейки, которые остались после использования фильтра.

Формула в ячейке F1 показывает точные данные, если база клиентов заполонена правильно. Например, нельзя, чтобы клиент был внесен в базу без имени, а только фамилия и город. Иначе значение в ячейке F1 не соответствует действительности. Также нельзя указывать только имя и фамилию без города.

exceltable.com

Отправить ответ

avatar
  Подписаться  
Уведомление о