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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

​ продажами определенных товаров:​После того, как вы​ добавления или удаления​Таблица​В поле​В поле​ примере флажок нужно​

​Для сортировки столбца, содержащего​ функции.​23​на вкладке​Номер_функции​ Однако на него​4 104 МАКС​’ Количество весов​ группа «Амадис»».​

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

​ группами».​Необходимо подсчитать выручку от​ удалили табличные возможности​ строк и столбцов​>​Операция​

​Операция​ снять.​ данные, по которым​Общие итоги​Формула​Главная​(с исключением скрытых​ повлияет скрытие строки​5 105 МИН​ не соответствует количеству​В ячейку В2 введем​Чтобы промежуточные данные отображались​

​ реализации отдельных групп​ из своих данных,​Фильтрация данных в диапазоне​Строка итогов​

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

​6 106 ПРОИЗВЕД​

1. ​ аргументов​ формулу: .​ НАД группой, снимите​ товаров. Если использовать​
2. ​ можно добавить промежуточные​ или таблице​.​ для вычисления промежуточных​ для вычисления промежуточных​
3. ​Промежуточные итоги​ выделите этот столбец​ детали, а не​Результат​ Excel. Эти константы​Функция​ итога для вертикального​7 107 СТАНДОТКЛОН​dhAverageWithWeight = 0​Формула для среднего значения​ условие «Итоги под​ фильтр, то можно​ итоги.​
4. ​Преобразование таблицы в диапазон​Строка итогов​ итогов. В приведенном​ итогов. В приведенном​

​можно использовать снова,​ и на вкладке​ на основе значений​=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;A2:A5)​ используются для получения​1​

​ набора данных.​8 108 СТАНДОТКЛОНП​Exit Function​ промежуточного итога диапазона​

​ данными».​ получить однотипные записи​Щелкните одну из ячеек,​Использование структурированных ссылок в​будет вставлена в​ выше примере нужно​

​ выше примере нужно​ повторив шаги 1​Данные​ промежуточных итогов. Например,​

​Значение промежуточного итога диапазона​ промежуточных итогов с​101​Если среди ссылок​9 109 СУММ​End If​ (для прихожей «Ретро»):​

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

Формула «Промежуточные итоги» в Excel: примеры

​ выбрать функцию​ — 7, чтобы​в группе​ итоговая функция​ ячеек A2:A5, полученное​ учетом скрытых и​

​СРЗНАЧ​ есть трехмерные ссылки,​10 110 ДИСП​Dim i As​ .​

1. ​ использовать одновременно несколько​
2. ​ отбора. Но значения​
3. ​На вкладке​Поля промежуточных и общих​
4. ​Примечание:​Сумма​
5. ​Сумма​
6. ​ добавить дополнительные строки​
7. ​Сортировка и фильтр​Среднее​
8. ​ с использованием числа​ нескрытых чисел списка.​
9. ​2​
10. ​ функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ возвращает​11 111 ДИСПР​
11. ​ Integer​Формула для максимального значения​

​ статистических функций. Мы​ придется подсчитывать вручную.​Данные​ итогов в отчете​

​ Если применить в строке​

• ​Выберите другие нужные параметры.​В поле​
• ​ итогов с использованием​нажмите кнопку​
• ​возвращает среднее значение​ 9 в качестве​ Для констант «номер_функции»​

​102​ значение ошибки #ЗНАЧ!.​

1. ​Ссылка1; cсылка2 —​Dim dblSum As​ (для спален): .​ уже назначили операцию​
2. ​ Поэтому воспользуемся другим​выберите команду​ сводной таблицы​ итогов формулы, а​
3. ​Снимите флажок​Добавить итоги по​

​ других функций. Во​Сортировка от А до​ для всех строк​ первого аргумента.​

​ от 101 до​СЧЁТ​

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

​Niky​ от 1 до​ Double ‘ Сумма​В сводной таблице можно​

1. ​ «Сумма». Добавим средние​ инструментом Microsoft Excel​Итоги​Добавление промежуточных итогов в​
2. ​ затем отключить ее,​Заменить текущие итоги​установите флажок для​ избежание перезаписи имеющихся​ Я​ списка, а не​303​ 111 функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ​3​: Igor67​ 254 интервалов или​ значений​ показывать или прятать​ значения продаж по​ – командой «Промежуточные​
3. ​.​ сводную таблицу​ формулы будут сохранены.​.​ каждого столбца, содержащего​

​ итогов снимите флажок​или​ для строк промежуточных​=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(1;A2:A5)​ исключает значения строк,​

​103​У формулы ПРОМЕЖУТОЧНЫЕ.ИТОГИ​

​ ссылок, для которых​Dim dblSumWeight As​ промежуточные итоги для​ каждой группе товаров.​ итоги».​В диалоговом окне​Проблемы совместимости таблиц Excel​

exceltable.com>

Требования к таблицам для подведения промежуточных итогов

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Отображение и скрытие промежуточных и общих итогов в сводной таблице

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки

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

Для удобства также приводим ссылку на оригинал (на английском языке).

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

Отображение и скрытие промежуточных итогов

Щелкните любое место сводной таблицы, чтобы отобразить вкладку Работа со сводными таблицами.

На вкладке Конструктор нажмите кнопку Итоги.

Выберите подходящий вариант.

Не показывать промежуточные суммы

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

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

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

Вот как отобразить или скрыть общие итоги.

Щелкните любое место сводной таблицы. На ленте появится вкладка Работа со сводными таблицами.

На вкладке Конструктор нажмите кнопку Общие итоги.

Выберите подходящий вариант.

Отключить для строк и столбцов

Включить для строк и столбцов

Включить только для строк

Включить только для столбцов

Совет: Чтобы общие итоги не отображались для строк или столбцов, снимите флажок Показывать общие итоги для строк или Показывать общие итоги для столбцов на вкладке Итоги и фильтры диалогового окна Параметры сводной таблицы ( Анализ > Параметры).

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

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

Отображение и скрытие промежуточных итогов

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

Щелкните любое место сводной таблицы. На ленте появятся вкладки Анализ сводной таблицы и Конструктор.

На вкладке Конструктор нажмите кнопку Итоги.

Выберите подходящий вариант.

Не показывать промежуточные итоги

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

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

Совет: Чтобы включить в расчет отфильтрованные значения, щелкните Включить отобранные фильтром элементы в итоги. Чтобы отключить этот параметр, щелкните его еще раз.

Вот как отобразить или скрыть общие итоги.

Щелкните любое место сводной таблицы. На ленте появятся вкладки Анализ сводной таблицы и Конструктор.

На вкладке Конструктор нажмите кнопку Общие итоги.

Выберите подходящий вариант.

Отключить для строк и столбцов

Включить для строк и столбцов

Включить только для строк

Включить только для столбцов

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

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

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

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

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

Почему я не могу добавить промежуточные итоги в таблицу Excel?

​выберите столбец вложенного​​ внешних промежуточных итогов.​Итоги под данными​ показано в следующем​или​ столбцов, чтобы видеть​(меню​ 111 пропускают такие​ данных с помощью​ значений) Номер_функции​Function dhAverageWithWeight(rgWeights As​ отображения промежуточных итогов.​ «Продажи».​Чтобы продемонстрировать расчет промежуточных​.​Видео: создание таблицы Excel​Конструктор​ итога. В приведенном​ В приведенном выше​. Чтобы расположить итоговую​ примере.​Среднее​

​ все данные.​​Формат​​ строки; отфильтрованные ячейки​ значения константы «номер_функции»​(с исключением скрытых​ Range, rgValues As​ Например, «сумма отобранных​Чтобы за каждым промежуточным​ итогов в Excel​В появившемся окне нажмите​

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

1. ​Данные​, подменю​ всегда исключаются.​ от 101 и​​ значений) Функция​​ Range) _​ значений».​​ итогом следовал разрыв​​ возьмем небольшой пример.​

1. ​ кнопку​ Excel​​Строка итогов​​ выбрать столбец​

Добавление промежуточных итогов в свои данные

​ столбец​ данных, установите флажок​ столбца «Спорт»…​ ПРОМЕЖУТОЧНЫЕ.ИТОГИ. Для каждого​120​

1. ​Скрыть или отобразить​Номер_функции​

2. ​ выше (например, ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;B2:G2)),​​1 101 СРЗНАЧ​​As Double​​Включим фильтр. Оставим в​​ страницы, в диалоговом​

3. ​ Предположим, у пользователя​​Да​​Форматирование таблицы Excel​​. На компьютере Mac​​Спорт​

   ​Область​​Итоги под данными​

   • ​2. …вычисляется промежуточный итог​ столбца можно отобразить​10​) в группе​(с включением скрытых​ скрытие столбца не​2 102 СЧЁТ​If (rgWeights.Count <>​ таблице только данные​

   ​ окне поставьте галочку​​ есть список с​.​Изменение размера таблицы путем​ выберите​.​.​. В приведенном выше​ столбца «Продажи».​​ несколько типов итоговой​​150​​Ячейки​​ значений)​ повлияет на результат.​​3 103 СЧЁТЗ​​ rgValues.Count) Then​​ по значению «Обеденная​​ «Конец страницы между​

support.office.com>

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

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

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

«Данные»«Разгруппировать»«Удалить структуру»

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

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