Итого в excel

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

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

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

Выделите строки или столбцы, которые необходимо сгруппировать. В следующем примере мы выделим столбцы A, B и C.
Откройте вкладку Данные на Ленте, затем нажмите команду Группировать.
Выделенные строки или столбцы будут сгруппированы. В нашем примере это столбцы A, B и C.

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

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

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

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

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

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

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

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

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

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

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

Хоть в этом примере представлено всего три уровня, Excel позволяет создавать до 8 уровней вложенности.

Щелкните нижний уровень, чтобы отобразить минимальное количество информации. Мы выберем уровень 1, который содержит только общее количество заказанных футболок.
Щелкните следующий уровень, чтобы отобразить более подробную информацию. В нашем примере мы выберем уровень 2, который содержит все строки с итогами, но скрывает остальные данные на листе.
Щелкните наивысший уровень, чтобы развернуть все данные на листе. В нашем случае это уровень 3.

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

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

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

Откройте вкладку Данные, затем нажмите команду Промежуточный итог.
Откроется диалоговое окно Промежуточные итоги. Нажмите Убрать все.
Все данные будут разгруппированы, а итоги удалены.

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

Функция «Промежуточные итоги» в Microsoft Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Читать еще: Word длинный пробел

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

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

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

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

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

Отблагодарите автора, поделитесь статьей в социальных сетях.

Итого в excel формула

Полные сведения о формулах в Excel

Смотрите также по выборке);Приступаем… отдельную ячейку. Единственное поля ввода данных. Excel – это кнопку «минус» в чтобы избежать двойного в диапазоне, в этой ячейке в с формулами. Перед строки выше вЧто происходит при перемещении, функция СРЗНАЧ вычисляетСсылка указывает на ячейку доступна для скачивания.Примечание:– СТАНДОТКЛОНП (стандартное отклонениеОтсортируем диапазон по значению условие состоит в

После этого, окно аргументов австосумма. структуре. суммирования.

 котором имеются скрытые значение, выполнив следующие заменой формулы на том же столбце копировании, вставке илиСмешанные ссылки среднее значение в или диапазон ячеек Если вы впервые Мы стараемся как можно по генеральной совокупности); первого столбца –

Создание формулы, ссылающейся на значения в других ячейках

функции сворачивается, а

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

строки. действия. ее результат рекомендуется

R[2]C[2] удалении листов . Смешанная ссылка содержит либо

диапазоне B1:B10 на листа и сообщает пользуетесь Excel или

оперативнее обеспечивать вас– СУММ; однотипные данные должны

Просмотр формулы

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

Особенность функции состоит вНажмите клавишу F2 для сделать копию книги.Относительная ссылка на ячейку,

Ввод формулы, содержащей встроенную функцию

. Нижеследующие примеры поясняют, какие

абсолютный столбец и листе «Маркетинг» в Microsoft Excel, где даже имеете некоторый актуальными справочными материалами

– ДИСП (дисперсия по оказаться рядом.

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

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

Скачивание книги «Учебник по формулам»

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

Подробные сведения о формулах

предназначена для использованияНажмите клавишу F9, а рассматриваются параметры и строки ниже и

Части формулы Excel

трехмерных ссылках при абсолютную строку и1. Ссылка на лист значения или данные. этой программой, данный Эта страница переведена– ДИСПР (дисперсия по таблице. Выбираем на едином массиве. значений которых хотим или строки, и,

и, в нашем данных. Она не

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

относительный столбец. Абсолютная «Маркетинг». С помощью ссылок учебник поможет вам

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

случае, подсчитывает сумму предназначена для строк средствами EXCEL: Автофильтром ВВОД. о включении и правее и удалении листов,

Использование констант в формулах Excel

ссылка столбцов приобретает2. Ссылка на диапазон можно использовать в ознакомиться с самыми текст может содержатьСсылка 1 – обязательный Группа «Структура» - сумму данных которых как массив выделен, «Главная», жмем на только товара Товар1. Этот данных или горизонтальных и Промежуточными итогами.После преобразования формулы в выключении автоматического пересчетаR2C2 на которые такие вид $A1, $B1 ячеек от B1 одной формуле данные, распространенными формулами. Благодаря неточности и грамматические аргумент, указывающий на команда «Промежуточные итоги». нужно узнать, и и его адрес

Использование ссылок в формулах Excel

кнопку «Автосумма». результат аналогичен ситуации наборов данных (ее См. Файл примера. ячейке в значение листа см. вАбсолютная ссылка на ячейку, ссылки указывают. В и т.д. Абсолютная до B10 находящиеся в разных наглядным примерам вы ошибки. Для нас именованный диапазон дляЗаполняем диалоговое окно «Промежуточные смотрим результат в появился в специальномПрограмма выводит формулу в с автофильтром. использование в этомПРОМЕЖУТОЧНЫЕ.ИТОГИ(номер_функцииссылка1 это значение (1932,322) статье Изменение пересчета,

 расположенную во второй

примерах используется формула ссылка строки приобретает3. Восклицательный знак (!) частях листа, а сможете вычислять сумму, важно, чтобы эта нахождения промежуточных итогов. итоги». В поле строке состояния программы поле, жмем на ячейку.Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() может подсчитать случае может приводить;ссылка2;. )) будет показано в итерации или точности строке второго столбца =СУММ(Лист2:Лист6!A2:A5) для суммирования вид A$1, B$1 отделяет ссылку на

 также использовать значение

 количество, среднее значение

статья была вамОсобенности «работы» функции: «При каждом изменении

кнопку справа отДля того, чтобы посмотреть

сумму, количество и

к непредсказуемым результатам).Номер_функции

строке формул. Обратите

значений в ячейках

и т.д. При лист от ссылки одной ячейки в

и подставлять данные

полезна. Просим васвыдает результат по явным

в» выбираем условие

Как видим, существует несколько этого поля. результат, нужно нажать

среднее отобранных значений,

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

внимание, что 1932,322

Замена формул на вычисленныеОтносительная ссылка на строку, с A2 по изменении позиции ячейки, на диапазон ячеек.

нескольких формулах. Кроме не хуже профессионалов. уделить пару секунд и скрытым строкам; для отбора данных способов суммирования данных

Мы опять возвращаемся в на кнопку Enter

а также включаетПрименим Автофильтр и отберем 1 до 11, которое

— это действительное значения расположенную выше текущей A5 на листах

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

 окно аргументов функции. на клавиатуре.

 еще 8 других только строки с указывает какую функцию вычисленное значение, аЗамена части формулы на ячейки со второго по ссылка изменяется, а Если название упоминаемого листа ссылки на ячейки определенных элементах формулы, ли она вам, в фильтр; «Значение»). В поле Excel. Каждый из Если нужно добавитьМожно сделать и немного функций (см. синтаксис). товаром Товар1. Пусть использовать при вычислении 1932,32 — это вычисленное значениеR шестой.

 абсолютная ссылка не содержит пробелы или разных листов одной

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

Читать еще: Как работать с таблицами в word

Номер_функции ячейке в формате вычисленные значения Microsoft

 строку Если вставить листы между или заполнении формулы заключить в апострофы ячейки из другихФормула также может содержать удобства также приводим подходит. «Добавить по» следует и гибкости. Как сумму, то повторяем не всей строки но иногда требуется следовательно будем использовать(включая скрытые значения) «Денежный». Office Excel удаляетПри записи макроса в листами 2 и вдоль строк и (‘), например так: книг. Ссылки на один или несколько ссылку на оригиналРассмотрим на примере использование пометить столбцы, к правило, чем проще те же действия, или столбца, а

 расширить возможности функции код функции 9 илиНомер_функции

 эти формулы без Microsoft Excel для 6, Microsoft Excel вдоль столбцов относительная ‘123’!A1 или =’Прибыль ячейки других книг таких элементов, как (на английском языке). функции: значениям которых применится вариант, тем он о которых говорилось только определенного диапазона, ПРОМЕЖУТОЧНЫЕ.ИТОГИ(). Рассмотрим пример 109.(за исключением скрытых Если при редактировании ячейки возможности восстановления. При некоторых команд используется прибавит к сумме ссылка автоматически корректируется, за январь’!A1. называются связями илифункции

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

а абсолютная ссылкаРазличия между абсолютными, относительными

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

 в поле с диапазон. Затем кликаем для отобранных автофильтром строки не включенныеФункция клавишу F9, формула на значение нажмите Например, если записывается A2 по A5 не корректируется. Например, и смешанными ссылкамиСтиль ссылок A1ссылки чтобы выполнять расчеты Например, «сумма отобранных кнопку ОК. Исходная с помощью автосуммы,

 параметром «Число 2». по уже знакомой значений. Функция СРГЕОМ() в результат фильтра1 будет заменена на кнопку команда щелчка элемента

 на новых листах. при копировании илиОтносительные ссылкиПо умолчанию Excel использует, и решать задачи. значений».

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

 . Относительная ссылка в формуле, стиль ссылок A1,операторыВажно:Включим фильтр. Оставим в вид: данными выстроенными в

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

сразу после ввода

для вставки формулы, . Если удалить листы из ячейки A2 например A1, основана в котором столбцыи Вычисляемые результаты формул и таблице только данныеЕсли свернуть строки в ряд. Поэтому, в адреса практически неограниченного на экран. соответствующие коды, нономер_функции2К началу страницы

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

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

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

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

Предварительно отсортировать таблицу по столбцу, для которого будут подводиться итоги.
Отметить любую ячейку в таблице.
Выбрать вкладку Данные [Data], затем найти группу кнопок Структура [Outline] и нажать команду Промежуточные итоги [Subtotal].

В появившемся диалоговом окне в выпадающем списке При каждом изменении в: [At each change in] отметить столбец, по значениям которого будут добавлены промежуточные итоги.
В выпадающем списке Операция [Use function] выбрать функцию для расчета промежуточных итогов.
В списке Добавить итоги по: [Add subtotal to] отметить все поля, для которых нужно подвести итоги.
ОК.

Итоги размещаются в автоматически добавляемых новых строках с добавлением функций =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(Номер_функции; Ссылка) [SUBTOTAL(Function_num; Ref)]. У этой функции два параметра:

[Номер_функции] – номер в фиксированном списке, который предлагает Excel для расчета итогов (11 функций).
[Ссылка] — интервал исходных данных для расчета итогов.

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

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

Кнопки уровня, обозначенные цифрами , находятся в верхней строке. Они указывают на уровень организации в таблице. Нажав на кнопку уровня, можно скрыть все итоги на этом уровне.
Кнопки Скрыть детали [Hide Detail] стоят рядом со строками с промежуточные итоги. Они обозначены символом «-«. При нажатии на такую кнопку группа исходных записей, по которой подводились итоги, скрывается, и высвечивается лишь итоговая строка.
Кнопки Отобразить детали [Show Detail] стоят рядом со строками с промежуточными итогами. Они обозначены символом «+». При нажатии на такую кнопку над итоговой строкой появляется группа исходных записей.

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

Если нужно вычислить другие промежуточные итоги , то следует убрать флажок в строке Заменить текущие итоги [Replace current subtotals] и повторить описанные выше действия по расчету одного промежуточного итога.
Если нужно к имеющимся добавить еще один промежуточный итог, следует убрать флажок в строке Заменить текущие итоги [Replace current subtotals] и повторить описанные выше действия по расчету промежуточного итога.
Если нужно удалить все итоги, достаточно щелкнуть по кнопке Убрать все [Remove All].

Многоуровневые итоги

Если требуется подвести итоги по нескольким полям одновременно (допустим, по столбцу Наименование товара, а затем, внутри каждого товара, по Поставщикам), то нужно:

Выполнить многоуровневую сортировку (например, вначале по Наименованию товара, а затем, внутри товаров, по Поставщикам).
Выполнить подведение итогов, как описано выше, для поля, задающего верхний уровень сортировки (например, для поля Наименование товара).
Убрать флажок в строке Заменить промежуточные итоги [Replace current subtotals].
Выбрать параметры для подведения итогов по следующему уровню (например, по полю Поставщик).
Повторить пункт 4 для всех уровней.

Как сделать строку итого в excel?

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

Как добавить строку итогов в таблицу?

1 способ

В окне открытого листа выделите любую ячейку таблицы.
Перейдите к вкладке «Конструктор» и в группе «Параметры стилей таблиц» активируйте значение «Строка итогов» (рис. 5.25).

Рис. 5.25. Вкладка «Конструктор». Значение «Строка итогов»

2 способ

1. В окне открытого листа щелкните правой кнопкой мыши на любой ячейке таблицы.
В контекстном меню наведите курсор на пункт «Таблица».
В списке команд выберите пункт «Строка итогов» (рис. 5.26).

Рис. 5.26. Контекстное меню таблицы данных. Меню пункта «Таблица». Пункт «Строка итогов»

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

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

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

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

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

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

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

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

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

Читать еще: Как вычислить корень в excel

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

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

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

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

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

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

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

Функция «ПРОМЕЖУТОЧНЫЕ.ИТОГИ» возвращает промежуточный итог в список или базу данных. Синтаксис: номер функции, ссылка 1; ссылка 2;… .

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

– СРЗНАЧ (среднее арифметическое);
– СЧЕТ (количество ячеек);
– СЧЕТЗ (количество непустых ячеек);
– МАКС (максимальное значение в диапазоне);
– МИН (минимальное значение);
– ПРОИЗВЕД (произведение чисел);
– СТАНДОТКЛОН (стандартное отклонение по выборке);
– СТАНДОТКЛОНП (стандартное отклонение по генеральной совокупности);
– СУММ;
– ДИСП (дисперсия по выборке);
– ДИСПР (дисперсия по генеральной совокупности).

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

Особенности «работы» функции:

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

Рассмотрим на примере использование функции:

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

Формула для среднего значения промежуточного итога диапазона (для прихожей «Ретро»): .

Формула для максимального значения (для спален): .

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

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

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

В меню «Параметры сводной таблицы» («Параметры» — «Сводная таблица») доступна вкладка «Итоги и фильтры».

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

Таким образом, для отображения промежуточных итогов в списках Excel применяется три способа: команда группы «Структура», встроенная функция и сводная таблица.