Автоматическое подведение итогов в excel

Автоматическое подведение итогов в excel

8. Автоматическое подведение промежуточных итогов

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

1. Отсортируйте исходный список по столбцу, для которого необходимо подвести промежуточные итоги. Применительно к нашему примеру это столбец Фамилия продавца.
   Различают итоги двух видов: промежуточные и вложенные. Рассмотрим подведение каждый из итогов в отдельности.
2. Щелкните мышью в любой ячейке отсортированного списка.
3. Выберите команду ДанныеИтоги
4. В появившемся окне Промежуточные итоги (рис. 8.2) выберите столбец, содержащий группы, по которым необходимо подвести итоги, из списка При каждом изменении в. Это должен быть тот столбец, по которому проводилась сортировка списка на шаге 1.
5. Выберите функцию, необходимую для подведения итогов, из списка Операция. Для нашего примера выберите функцию Сумма.
6. Выберите столбцы, содержащие значения, по которым необходимо подвести итоги, в списке Добавить итоги по. Применительно к нашему примеру таким столбцом будет Сумма.

№ п.п.

Дата

Фамилия продавца

Категория товара

Сумма

Рис.8.1. Список отпущенных товаров

Задайте режимы отображения итогов:
— Заменить текущие итоги. Этот режим используется для вычисления новых промежуточных итогов с заменой текущих (если они были). При подведении обычных (не вложенных итогов) этот режим необходимо задавать всегда.

Рис.8.2. Вид окна Промежуточные итоги

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

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

Рис. 8.3. Вид экрана с подведенными промежуточными итогами

Замечание. Если на листе отсутствуют символы структуры, то установите флажок Символы структуры вкладки Вид диалогового окна Параметры (меню Сервис).

Структура позволяет скрыть и отобразить уровни детализации простым нажатием кнопки мыши на кнопках с изображением знака «-« или «+» соответственно. Для просмотра или скрытия детальных данных в структуре щелкайте мышью по символам уровня строки или столбца ( 1 2 3 ).

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

После подведения итогов Excel выводит информацию для низшего уровня структуры. Применительно к рассматриваемому примеру это уровень 3; информация для этого уровня детализации и отображена на рис. 8.3.

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

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

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

1. Чтобы вставить итоги для групп, находящихся внутри уже существующих групп, необходимо отсортировать список по двум или более столбцам, для которых необходимо подвести итоги. Например, чтобы просуммировать отпущенный товар по фамилиям продавцов и по категориям товара, следует отсортировать список по столбцу Фамилия продавца, а затем — по столбцу Категория товара.
2. Подведите итоги для первого (внешнего) уровня по столбцу Фамилия продавца. В окне Промежуточные итоги обязательно должен быть включен режим Заменить текущие итоги.
3. Подведите итоги для второго (внутреннего) уровня по столбцу Категория товара. Для этого:
   • Щелкните мышью в любой ячейке исходного списка и дайте команду Данныеa Итоги.
   • Выберите следующий столбец, по которому необходимо подвести итоги, из списка При каждом изменении в. (в нашем примере – это столбец Категория товара).
   • Выключите режим Заменить текущие итоги в окне Промежуточные итоги и нажмите кнопку OK.
4. Повторите пункт 3 для каждого столбца, по которому необходимо подвести внутренние (вложенные) итоги.

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

Рис. 8.4. Фрагмент списка с вложенными итогами

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

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

1. Щелкните мышью в любой ячейке списка, содержащего промежуточные итоги.
2. Дайте команду Данныеa Итоги
3. Нажмите кнопку Убрать все.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Чтобы не пропустить новые уроки и постоянно повышать свое мастерство владения Excel — подписывайтесь на наш канал в Telegram Excel Everyday

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

Вопросы по Excel можно задать нашему боту обратной связи в Telegram @ExEvFeedbackBot

Вопросы по другому ПО (кроме Excel) задавайте второму боту — @KillOfBot

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

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

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

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

1. Отсутствие пустых ячеек, т.е. все строки и столбцы должны быть заполнены данными.
2. В шапке таблицы нельзя использовать несколько строк. Она должна быть представлена лишь одной строкой. А также имеет значение ее расположение. Она должна находиться только на самой верхней строке и нигде больше.
3. Формат таблицы обязательно должен быть представлен в виде обычной области ячеек.

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

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

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

1. Отмечаем любую ячейку таблицы, переключаемся во вкладку “Данные”, находим раздел “Структура”, щелкаем по нему и в раскрывшемся перечне нажимаем по варианту “Промежуточный итог”.
2. В итоге появится окно, где мы осуществим дальнейшие настройки согласно нашей задаче.
3. Итак, нам требуется произвести расчет ежедневных продаж всех наименований продукции. Информация о дате продажи размещается в одноименном столбце. Исходя из этого, заполняем требуемые поля настроек.
   • раскрываем список для строки “При каждом изменении в” и останавливаем выбор на “Дате”.
   • мы хотим посчитать общую сумму ежедневных продаж, поэтому для параметра “Операция” выбираем функцию “Сумма”.
   • если бы пред нами стояла другая задача, то можно было бы выбрать другую функцию из четырех предложенных программой: произведение (умножение), минимум, максимум, количество.
   • далее требуется указать место вывода полученных данных. У нас в таблице имеется столбец под названием “Продано, в руб.” Его и укажем для параметра «Добавить итоги по».
   • также следует обратить внимание на пункт “Заменить текущие итоги”. Если напротив него нет установленной галочки, нужно ее поставить. В противном случае возникнут проблемы при внесении каких-либо изменений и повторном пересчете итогов.
   • перейдем к надписи “Конец страницы между группами” и разберемся, стоит ли ставить напротив нее галочку. Если этот параметр будет отмечен галочкой, это повлияет на внешний вид документа при отправке на принтер. Все блоки таблицы с подведенными промежуточными итогами распечатаются на отдельных листах каждый.
   • и, наконец, параметр “Итоги под данными” определяет расположение результата относительно строк. Если убрать отметку напротив этого пункта, то результат будет выводиться над строками. Приемлемы оба варианта, но всё-таки привычнее и визуально понятнее расположение итогов под данными.
   • закончив с настройками, подтверждаем действие нажатием на OK.
4. В результате проделанных действий в таблице будут отображены промежуточные итоги по группам (по датам). Напротив каждой группы можно увидеть значок минуса, при нажатии на который строки внутри нее сворачиваются.
5. При желании можно убрать лишние данные из поля видимости, оставив только общий итог и промежуточные суммы. Нажатием кнопки “плюс” можно обратно развернуть строки внутри групп.

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

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

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

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

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

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

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

Заключение

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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