Промежуточные итоги в excel с примерами функций

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

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

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

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

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

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

Как этот код работает

1. Шаг 1 объявляет две переменные объекта. Этот макрос использует РТ в качестве контейнера памяти для сводной таблицы и использует PF в качестве контейнера памяти для полей. Это позволяет перебрать все сводные поля в сводной таблице. Этот макрос разработан таким образом, что мы выводим активную сводную таблицу на основе активной ячейки. То есть, активная ячейка должна быть внутри сводной таблицы для запуска этого макроса. Когда курсор находится внутри определенной сводной таблицы, мы хотим, выполнить действие макроса на этой оси.
2. Шаг 2 устанавливает переменную СТ к имени сводной таблицы на которой найдена активная ячейка. Мы делаем это, используя свойство ActiveCell.PivotTable.Name, чтобы получить имя целевого диапазона.Если активная ячейка не находится внутри сводной таблицы, выдается ошибка. Именно поэтому макрос использует On Error Resume Next Statement. Это говорит Excel продолжить макро, если есть ошибка.
3. Шаг 3 проверяет в переменной РТ заполнены ли сводные таблицы объекта. Если переменная PT установлена в настоящее время, активная ячейка была не на сводной таблице, таким образом, сводной таблице не может быть присвоена переменная. Если это так, то мы говорим пользователю в окне сообщения, а затем выходим из процедуры.
4. Если макрос достигает Шаг 4, он успешно указал на сводную таблицу. Мы готовы к циклу для всех полей в сводной таблице. Оператор For Each перебирает каждое поле сводной.Каждый раз, когда выбрано новое поле сводной, мы применяем нашу логику Итого. После того, как все поля были оценены, макрос заканчивается.

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

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

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

Группировка строк и столбцов в Excel — как сделать?

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

Группировка в Excel — возможности

С помощью специальных функций в Эксель (Excel) можно выполнять группировку строк, группировку столбцов и выводить промежуточные итоги. Можно создавать до 8 уровней вложенности.

• Группировка также позволяет «спрятать» промежуточные данные.
• Параметры группировки
• В программе Excel по умолчанию строчка/столбец с итоговыми данными располагается под данными (группировка строк) или справа от данных (группировка столбцов).
• Если вы хотите изменить отображение итогов, то нужно открыть вкладку «Данные» и в разделе «Структура» нажать на наклонную стрелку.

1. Откроется окно настройки, где вам нужно будет убрать соответствующие флажки.
2. Как сделать группировку данных в Excel?
3. Рассмотрим подробно на примере, как можно сгруппировать данные в таблице разными способами:
4. 1) Группировка по строкам.
5. 2) Группировка по столбцам.
6. 3) Автоматическая группировка.
7. Группировка по строкам

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

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

Для того, чтобы сделать группировку по строкам в Экселе (Excel), необходимо выделить требуемую область (группу) и в меню выбрать пункт «Данные» -> «Группировать». В появившемся окне выбираем «Группировать строки».

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

Если нужно скрыть группу, то нужно сбоку нажать на минус, а если развернуть — на плюс. Кроме того, Excel отображает число уровней группировки — в нашем примере их 2.

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

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

• Добавим в ранее созданную таблицу формулы, рассчитывающие суммарные цены и количество товаров по каждой группе (функция СУММ).
• Для создания автоматической группировки строк в Экселе заходим в «Данные» -> «Группировать» -> «Создание структуры».

Если всё сделали правильно, то строки автоматически будут сгруппированы. Если нет — Excel выдаст ошибку «Невозможно создать структуру документа». Это означает, что на листе отсутствуют формулы, позволяющие сгруппировать данные автоматически по определённым признакам.

Наша таблица будет иметь следующий вид:

1. Создание нескольких уровней группировки
2. Как было сказано выше, в Экселе можно создавать довольно сложные структуры данных.

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

После этого воспользуемся автоматической группировкой. Вот какая таблица получится:

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

• Группировка по столбцам
• Такой вид группировки в Excel осуществляется схожим образом — указываем требуемые столбцы и создаём группу (в диалоговом окне выбираем «Группировать столбцы»).

Убрать группировку

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

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

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

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

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

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

В нашем образце рабочего листа щелкните цифру 2, чтобы отобразить первую группировку по регионам :

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

Или же используйте кнопки «Показать детали» или «Скрыть детали» в меню «Данные» в группе «Структура».

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

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

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

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

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

1. Отобразите только те строки промежуточных итогов, которые вы хотите скопировать, используя цифры в структуре или символы «плюс» и «минус».
2. Выберите любую ячейку с промежуточным итогом и нажмите , чтобы выделить все ячейки.
3. Выделив промежуточные итоги, перейдите на вкладку «Главная»> «Редактирование» и нажмите «Найти и выделить» > «Выделить группу ячеек…»
4. В появившемся диалоговом окне выберите «Только видимые ячейки» и нажмите «ОК».
5. На текущем листе нажмите  для копирования выбранных ячеек с промежуточными итогами.
6. Откройте другой лист или книгу и нажмите , чтобы вставить промежуточные итоги.

Готово! В результате у вас есть только сводка данных, скопированная на другой рабочий лист. Обратите внимание, что этот метод копирует только значения, а не формулы:

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

Применение функции промежуточных итогов

Итак, теперь, когда мы определились с основными критериями “годности” таблиц, приступим к подсчету промежуточных итогов.

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

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

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

Итак, нам требуется произвести расчет ежедневных продаж всех наименований продукции. Информация о дате продажи размещается в одноименном столбце. Исходя из этого, заполняем требуемые поля настроек.раскрываем список для строки “При каждом изменении в” и останавливаем выбор на “Дате”.
мы хотим посчитать общую сумму ежедневных продаж, поэтому для параметра “Операция” выбираем функцию “Сумма”.
если бы пред нами стояла другая задача, то можно было бы выбрать другую функцию из четырех предложенных программой: произведение (умножение), минимум, максимум, количество.
далее требуется указать место вывода полученных данных

У нас в таблице имеется столбец под названием “Продано, в руб.” Его и укажем для параметра «Добавить итоги по».
также следует обратить внимание на пункт “Заменить текущие итоги”. Если напротив него нет установленной галочки, нужно ее поставить

В противном случае возникнут проблемы при внесении каких-либо изменений и повторном пересчете итогов.
перейдем к надписи “Конец страницы между группами” и разберемся, стоит ли ставить напротив нее галочку. Если этот параметр будет отмечен галочкой, это повлияет на внешний вид документа при отправке на принтер. Все блоки таблицы с подведенными промежуточными итогами распечатаются на отдельных листах каждый.
и, наконец, параметр “Итоги под данными” определяет расположение результата относительно строк. Если убрать отметку напротив этого пункта, то результат будет выводиться над строками. Приемлемы оба варианта, но всё-таки привычнее и визуально понятнее расположение итогов под данными.
закончив с настройками, подтверждаем действие нажатием на OK.

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

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

Примечание: После внесении каких-либо изменений и добавлении новых данных промежуточные итоги будут пересчитаны в автоматическом режиме.

Использование функции «Промежуточные итоги» в Excel

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

• Таблица должна иметь формат обычной области ячеек;
• Шапка таблицы должна состоять из одной строки и размещаться на первой строке листа;
• В таблице не должно быть строк с незаполненными данными.

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

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

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

Откроется окно, в котором нужно настроить выведение промежуточных итогов. В нашем примере нам надо просмотреть сумму общей выручки по всем товарам за каждый день. Значение даты расположено в одноименной колонке. Поэтому в поле «При каждом изменении в» выбираем столбец «Дата».
В поле «Операция» выбираем значение «Сумма», так как нам требуется подбить именно сумму за день. Кроме суммы доступны многие другие операции, среди которых можно выделить: количество, максимум, минимум, произведение.
Так как значения выручки выводятся в столбец «Сумма выручки, руб.», то в поле «Добавить итоги по», выбираем именно его из списка столбцов таблицы.
Кроме того, надо установить галочку, если ее нет, около параметра «Заменить текущие итоги». Это позволит при пересчете таблицы, если вы проделываете с ней процедуру подсчетов промежуточных итогов не в первый раз, не дублировать многократно запись одних и тех же итогов.
Если поставить галочку в пункте «Конец страницы между группами», при печати каждый блок таблицы с промежуточными итогами будет распечатываться на отдельной странице.
При добавлении галочки напротив значения «Итоги под данными» промежуточные итоги будут устанавливаться под блоком строк, сумма которых в них подбивается. Если же снять галочку, тогда они будут показываться над строками. Для большинства удобнее размещение под строками, но сам выбор сугубо индивидуален.

По завершении жмем на «OK».

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

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

Формула «ПРОМЕЖУТОЧНЫЕ.ИТОГИ»

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

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

Откроется «Мастер функций», где среди списка функций ищем пункт «ПРОМЕЖУТОЧНЫЕ.ИТОГИ». Выделяем его и кликаем «OK».

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

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

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

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

Синтаксис самой функции выглядит следующим образом: ПРОМЕЖУТОЧНЫЕ.ИТОГИ(номер_функции;адреса_массивов_ячеек) . В нашей ситуации формула будет выглядеть так: «ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;C2:C6)». Эту функцию, используя этот синтаксис, можно вводить в ячейки и вручную, без вызова «Мастера функций». Только важно не забывать перед формулой в ячейке ставить знак «=».

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

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

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

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

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

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

Ссылка1; Ссылка2; — от 1 до 29 интервалов или ссылок, для которых подводятся итоги.

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

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

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

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

Трехмерные ссылки используются при необходимости анализа данных из одной и той же ячейки или диапазона ячеек на нескольких листах одной книги. Трехмерная ссылка включает в себя ссылку на ячейку или диапазон, перед которой ставятся имена листов. Microsoft Excel использует все листы, хранящиеся между начальным и конечным именами, указанными в ссылке. Например, формула =СУММ(Лист2:Лист13!B5) суммирует все значения, содержащиеся в ячейке B5 на всех листах в диапазоне от Лист2 до Лист13 включительно.

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

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

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

Такой оперативный анализ очень полезен техническим исполнителя. Представьте, Вы спокойно сидите перед своим персональным компьютером, на своем рабочем месте и мирно занимаетесь своими личными делами. И вдруг, звонок начальника: “а скажи-ка мне Тяпкин-Ляпкин, скока у нас того-то и того-то”? Пока начальник выговаривает, что ему надо, Вы быстренько запускаете файл с базой данных и по фильтру отбираете то, что интересует руководство. Ответ готов, начальник доволен, товарищи по работе в шоке, а Вы опять спокойно занимаетесь своими делами.

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

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

Описание

​ значения.​ можете развернуть и​ содержать неточности и​ мы применили функцию​позволяют показать или​.​1. При каждом изменении​​Появится диалоговое окно​​ данных. Используя команду​​ (1–11, 101–111). Этот​​ в результат фильтра,​​7​​В этой статье описаны​ до 11 функция​dblSum = dblSum​ стоять в ячейке​ 2;… .​

Синтаксис

​В таблице нет пустых​

​ свернуть данные, нажав​ грамматические ошибки. Для​

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

​ нас важно, чтобы​ ​ столбцу:​ для отдельных итогов.​

​ над строкой данных,​ ​ «Область»…​.​

​, также можно отобразить​

​ для промежуточного итога​

​ значения константы «номер_функции».​

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

​ использование функции​

​ строк, скрытых при​

​Next​

​ значениям которого будет​

​ от 1 до​

​Приступаем…​

​ с числами в​

​ эта статья была​

​Excel создает следующую формулу:​

​Выделите ячейку в диапазоне,​

​ снимите флажок​

​2. …и при каждом​

​В поле​

​ список, что позволит​

​ значений (диапазонов ячеек,​

​Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ применяется к​

​8​

​ПРОМЕЖУТОЧНЫЕ.ИТОГИ​

​ помощи команды Скрыть​

​’ Возвращение средневзвешенного​

​ применяться функция

Нажимаем​

​ 11, которое указывает​

​Отсортируем диапазон по значению​

​ верхней части рисунка.​

​ вам полезна. Просим​

​=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;)​

​ который содержит промежуточные​

​Итоги под данными​

​ изменении во внутреннем​

​При каждом изменении в​

• ​ отображать и скрывать​​ именованных диапазонов), указанных​ столбцам данных или​108​в Microsoft Excel.​

• ​ строки (меню Формат,​​ значения​ кнопку «Параметры поля».​ статистическую функцию для​ первого столбца –​

Примечания

• ​Совет:​ вас уделить пару​. Это функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ​ итоги.​. Чтобы расположить итоговую​ столбце «Спорт»вычисляется промежуточный​выберите столбец для​

• ​ строки каждого из​ в качестве следующих​ вертикальным наборам данных.​СТАНДОТКЛОНП​Возвращает промежуточный итог в​​ подменю Скрыть/Показать) в​​dhAverageWithWeight = dblSumWeight​​ В открывшемся меню​​ расчета промежуточных итогов:​​ однотипные данные должны​​  Если вы решите,​​ секунд и сообщить,​​ для функции СУММ,​​На вкладке​​ строку под строкой​ итог столбца «Продажи»​ подсчета итогов. В​ промежуточных итогов.​ аргументов.​ Она не предназначена​9​ список или базу​ группе Ячейки на​ / dblSum​ выбираем «другие». Назначаем​– СРЗНАЧ (среднее арифметическое);​​ оказаться рядом.​​ что вам больше​ помогла ли она​ которая является формулой​Данные​ данных, установите флажок​

• ​ для этой области.​ приведенном выше примере​Примечание:​С помощью команды​ для строк данных​

• ​109​ данных. Обычно проще​ вкладке Лист. Эти​End Function​ нужную функцию для​– СЧЕТ (количество ячеек);​Выделяем любую ячейку в​ не нужны промежуточные​ вам, с помощью​ со структурированными ссылками​в группе​Итоги под данными​Для сортировки столбца, содержащего​ нужно выбрать столбец​ При фильтрации данных, содержащих​Промежуточные итоги​ или горизонтальных наборов​СУММ​ создать список с​ константы используются для​

• ​Спасибо!​ промежуточных итогов.​– СЧЕТЗ (количество непустых​ таблице. Выбираем на​

Пример

​ итоги, вы можете​ кнопок внизу страницы.​ (такие формулы доступны​Структура​. В приведенном выше​ данные, по которым​Спорт​ промежуточные итоги, промежуточные​можно автоматически подсчитать​ данных. Так, при​10​ промежуточными итогами, используя​ получения промежуточных итогов​

​Dophin​
​Для выведения на экран​
​ ячеек);​
​ ленте вкладку «Данные».​
​ удалить их. Для​
​ Для удобства также​	​ только в таблицах​	​выберите параметр​
​ примере флажок нужно​	​ требуется выполнить группировку,​.​ итоги могут быть​ промежуточные и общие​ определении промежуточных итогов​	​110​
​ в настольном приложении​	​ для скрытых и​: а пример? без​ итогов по отдельным​– МАКС (максимальное значение​ Группа «Структура» -​	​ этого щелкните любую​
​ приводим ссылку на​
​ Excel). См. статью​Промежуточные итоги​ снять.​ выделите этот столбец​В поле​ скрыты. Чтобы отобразить​ итоги в списке​ горизонтального набора данных​ДИСП​ Excel команду​

support.office.com>

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ в Excel, ее назначение и особенности.

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

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

https://youtube.com/watch?v=col6ZtzM17Y%3F

Подведем итоги.

Как мы видим, подвести промежуточные итоги с помощью одноименной функции Excel несложно. Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ имеет достаточно положительных моментов, выгодно отличающих ее от исходных аналогов. Прежде всего она очень гибкая. С ее помощью можно получить нужные итоги по любым позициям, просто изменив отбор нужных данных с помощью фильтра. Эта функция имеет достаточно простой синтаксис, поэтому несложно запомнить способ ее применения. Кроме этого, данная функция достаточно универсальна и позволяет заменить для расчетов 11 других отдельных функций. Однако, помимо плюсов, в данной функции есть и минусы. Эта функция не может работать с ошибочными данными. Поэтому если хотя бы один из диапазонов содержал значение ошибки, то и функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ тоже даст ошибку.Во-вторых, функция ПОМЕЖУТОЧНЫЕ.ИТОГИ не может распознать наличия среди данных уже рассчитанных итогов, поэтому если таковые имеются, то дублирование неизбежно. Впрочем, это относится к большинству других функций, обрабатываемых числовые данные. В-третьих, при использовании фильтра с ее помощью нельзя узнать и итоги по отображаемым данным, и общие итоги по диапазону. Чтобы получить общий итог, придется убирать фильтр. Однако в этом случае данные по отфильтрованным позициям исчезнут. Таким образом, посмотреть сразу и на общий итог, и на итог по отдельным данным нельзя. В-четвертых, нельзя вывести одновременно итог по каждой группе данных, только по отдельности.Всех этих недостатков лишены функция АГРЕГАТ  появившаяся в Excel 2013, и команда «Промежуточный итог». Однако об этих способах мы поговорим на новых занятиях. Наше же текущее занятие подошло к концу. Внизу вы найдете ссылку, по которой можете скачать материал для самостоятельной работы. Вы можете применить в нем те формулы, которые были здесь показаны, а также поэкспериментировать с данными, используя свои расчеты для выбранных уже вами позиций.

Кроме этого, мы приглашаем вас посетить наши занятия по интересующей вас тематике, где вы получите гораздо больше интересных, а главное – необходимых и полезных навыков по обработке и анализу данных в Excel и работе в других программах, в частности PowerPoint и Project.

Работа со строкой итогов «умной таблицы» Excel

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

Чтобы добавить строку итогов в таблицу, выберите любую ячейку внутри нее и на появившейся контекстной вкладке «Конструктор» в группе команд «Параметры стилей таблицы» установите галочку напротив «Строка итогов«.

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

Автоматические подсчитанные итоги (обычно это сумма по какому-то числовому столбцу) можно легко изменить. Кликните в нужную ячейку итоговой строки и из выпадающего списка выберите тот вариант подведения итогов, который Вам нужен.

Доступны следующие варианты:

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

Особенности работы со строкой итогов

• В строку итогов можно вводить любые формулы (не обязательно выбирать только варианты из выпадающего списка);
• При выборе варианта из выпадающего списка в ячейку вставляется функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ с соответствующим аргументом;
• Если отключить строку итогов, добавить/удалить строки таблицы и снова включить — в строке итогов будут те же формулы, что и до отключения (но соответствующим образом будут изменены ссылки на диапазоны ячеек);
• Чтобы добавить данные в таблицу без отключения строки итогов, нужно вставить новую строку перед ней. Это можно сделать командой «Вставить строку» или нажав Tab, когда активна ячейка последнего столбца последней строки;
• Если сослаться на ячейку строки итогов в формуле, то ссылка примет вид: ИмяТаблицы;]. Если после этого скрыть строку итогов — такая формула вернет ошибку #ССЫЛКА!

Видеоверсию данной статьи смотрите на нашем канале на YouTube

Много интересного по другим офисным приложениям от Microsoft (Word, Outlook, Power Point, Visio и т.д.) — на нашем канале в Telegram Office Killer