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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Скопировать только строки с промежуточными итогами в другой диапазон не так просто: если даже таблица сгруппирована на 2-м уровне (см. рисунок выше), то выделив ячейки с итогами (на самом деле выделится диапазон А4:D92) и скопировав его в другой диапазон мы получим всю таблицу. Чтобы скопировать только Итоги используем Расширенный фильтр (будем использовать тот факт, что MS EXCEL при создании структуры Промежуточные итоги вставляет строки итогов с добавлением слова Итог или в английской версии — Total).

создайте в диапазонеD5:D6табличку с критериями: в D5поместите заголовок столбца, в котором содержатся слова Итог, т.е. слово Товар; в D6введите *Итог (будут отобраны все строки, у которых в столбце Товар содержится значения, заканчивающиеся на слово Итог) Звездочка означает подстановочный знак *;

• выделите любую ячейку таблицы;
• вызовите Расширенный фильтр ( Данные/ Сортировка и фильтр/ Дополнительно );
• в поле Диапазон условий введите D5:D6;
• установите опцию Скопировать результат в другое место;
• в поле Поместить результат в диапазон укажите пустую ячейку, например А102;

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

СОВЕТ : Перед добавлением новых данных в таблицу лучше удалить Промежуточные итоги ( Данные/ Структура/ Промежуточные итоги кнопка Убрать все).

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

​ Автоматическая перенумерация строк​​ СУММ() также имеет​

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

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

​ подвести итог. В​	​Выделенные строки или столбцы​	​ выход есть.​
​ при применении фильтра.​	​ код 109, т.е.​	​ SUBTOTAL(), используется для​
​Скрыть строки​	​Номер_функции​	​ фильтром элементам страницы​
​Задание отображения или скрытия​	​ генеральной совокупности, где​	​Выберите вариант​
​Число значений данных. Подведения​	​ и столбцов, отображать​	​ (такие формулы доступны​
​ воспользуйтесь пунктом​	​ иметь возможность регулировать​	​ этом примере мы​
​ будут сгруппированы. В​	​Воспользуемся той же исходной​	​Пусть имеется та же​
​ можно записать формулу​	​ вычисления промежуточного итога​	​(меню​
​(с включением скрытых​	​, чтобы включить или​	​ общих итогов по​
​ выборка является подмножеством​	​Показывать все промежуточные итоги​	​ итогов работает так​
​ или скрывать строки​	​ только в таблицах​	​Удалить структуру​

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

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

​ для каждого размера​ столбцы A, B​Применим Автофильтр и отберем​ строки с товаром​ В чем различие​ значений и т.д.)​, подменю​

​(с исключением скрытых​​ страницы.​   ​Смещенная дисперсия​ группы​ СЧЕТЗ . Число​ итогов всего отчета,​ Использование структурированных ссылок​ команды​ 1, 2, 3​ футболок, поэтому информация​

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

​ и C.​

​ только строки с​ Товар2  через меню​ — читайте ниже.​ в диапазоне, в​Скрыть/Показать​ значений) Функция​Примечание:​Щелкните отчет сводной таблицы.​

​Смещенная оценка дисперсии генеральной​.​ — функция по​ а также вычислять​ в таблицах Excel.​​Разгруппировать​​ в левой части​ на листе Excel​Чтобы разгруппировать данные в​ товаром Товар1. Пусть​

​ Главная/ Ячейки/ Формат/​ Обычно используют коды​ котором имеются скрытые​) в группе​1​ Источник данных OLAP должен​

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

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

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

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

​Примечание:​ в заголовке группы​Среднее​ элементов при помощи​ функции, щелкнув​

​Вы можете быстро подвести​ между всеми тремя​Размер​ а затем щелкните​

​ товаров Товар1 (пример​ меню.​Ссылка1​​ том, что она​​Лист​2​Помечать итоги *​Сводная таблица​ С источниками данных OLAP​

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

​.​Среднее чисел.​ фильтра или без​Другие функции​ итоги в таблице​ уровнями структуры.​от меньшего к​ команду​ не очень жизненный,​В этом случае имеется​; Ссылка2; — от​ предназначена для использования​. Эти константы используются​102​, чтобы отображать или​нажмите кнопку​ использовать нестандартные функции​Отобразить промежуточные итоги для​

​Max​ него.​

​или создав их​ Excel, включив​Хоть в этом примере​ большему.​Разгруппировать​ но он показывает​ разница между использованием​ 1 до 29​ совместно с другими​ для получения промежуточных​СЧЁТ​

​ скрывать звездочку рядом​Параметры​ невозможно.​

​ заголовка внутренней строки​

​Максимальное число.​Поля строк и столбцов​ самостоятельно.​строку итогов​ представлено всего три​Откройте вкладку​.​ принцип). Будем использовать​ кода функции СУММ(): 9​ ссылок на диапазон,​ средствами EXCEL: Автофильтром​ итогов для скрытых​3​ с итогами. Звездочка​

excel2.ru>

Написание формулы промежуточных итогов вручную

Есть еще один способ посчитать промежуточные итоги – с помощью специальной функции.

1. Для начала отмечаем ячейку, где должен быть выведен итог подсчета. Далее нажимаем на значок «Вставить функцию» (fx) рядом со строкой формул с левой стороны от нее.
2. Откроется Мастер функций. Выбираем категорию “Полный алфавитный перечень”, находим из предложенного перечня функцию “ПРОМЕЖУТОЧНЫЕ.ИТОГИ”, ставим на нее курсор и нажимаем OK.
3. Теперь нужно задать настройки функции. В поле «Номер_функции» указываем цифру, которой соответствует нужному варианту обработки информации. Всего опций одиннадцать:
4. цифра 1 – расчет среднего арифметического значения
5. цифра 2 – подсчет количества ячеек
6. цифра 3 – подсчет количества заполненных ячеек
7. цифра 4 – определение максимального значения в выбранном массиве данных
8. цифра 5 – определение минимального значения в выбранном массиве данных
9. цифра 6 – перемножение данных в ячейках
10. цифра 7 – выявление стандартного отклонения по выборке
11. цифра 8 – выявление стандартного отклонения по генеральной совокупности
12. цифра 9 – расчет суммы (ставим в нашем варианте согласно задаче)
13. цифра 10 – нахождение дисперсии по выборке
14. цифра 11 – нахождение дисперсии по генеральной совокупности
15. В поле «Ссылка 1» указываем координаты диапазона, для которого требуется просчитать итоги. Всего можно указать до 255 диапазонов. После введения координат первой ссылки, появится строка для добавления следующей. Прописывать координаты вручную не совсем удобно, к тому же, велика вероятность ошибиться. Поэтому просто ставим курсор в поле для ввода информации и затем левой кнопкой мыши отмечаем нужную область данных. Аналогичным образом можно добавить следующие ссылки, если потребуется. По завершении подтверждаем настройки нажатием кнопки OK.
16. В итоге в ячейке с формулой будет выведен результат подсчета промежуточных итогов.

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

= ПРОМЕЖУТОЧНЫЕ.ИТОГИ(номер обработки данных;координаты ячеек)

Далее жмем клавишу Enter и получаем желаемый результат в заданной ячейке.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Заключение

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

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