Green-sell.info

Новые технологии
6 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Подведение итогов в 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 для всех уровней.

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

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

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

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

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

Читать еще:  Как убрать верхний колонтитул в excel

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

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

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

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

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

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

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

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

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

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

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

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

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

Данные итогов в таблице Excel

В этом курсе:

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

Щелкните любое место таблицы.

Выберите Работа с таблицами > Конструктор и установите флажок Строка итогов.

Строка итогов будет вставлена в нижней части таблицы.

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

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

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

Вы увидите, что Excel создал следующую формулу: = подытог (109, [средне]). Это промежуточный итог для функции сумм, а также структурированная формула, которая является исключительно для таблиц Excel. Дополнительные сведения об использовании структурированных ссылок в таблицах Excel.

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

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

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

Щелкните любое место таблицы.

Перейти к таблице > строка «итог«.

Строка итогов будет вставлена в нижней части таблицы.

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

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

Вы увидите, что Excel создал следующую формулу: = подытог (109, [средне]). Это промежуточный итог для функции сумм, а также структурированная формула, которая является исключительно для таблиц Excel. Дополнительные сведения об использовании структурированных ссылок в таблицах Excel.

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

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

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

Щелкните любое место таблицы.

Откройте вкладку Конструктор таблиц > Параметры стиля > строка«Итого».

Строка итогов будет вставлена в нижней части таблицы.

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

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

Функция Excel Промежуточные.итоги()

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

Особенность функции состоит в том, что она предназначена для использования совместно с другими средствами Excel (например, автофильтрами).

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

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

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

Для этого можно воспользоваться функцией Excel =Промежуточные.итоги(). Функция =Промежуточные.итоги() делает расчет по отфильтрованным значениям.

В формуле можно задать расчет:

  • Средней – номер 1 или 101 – для выборки
  • Счет значений – номер 3 или 103
  • Сумма — 9 или 109
  • И т.д.

  • Если номер функции трехзначный, например, 109, то функция работает как промежуточные итоги, т.е. рассчитывает значения по отфильтрованным параметрам.
  • Если номер однозначный или двузначный 2 или 11, то формула промежуточные.итоги() работает, как стандартная формула по выбранной функции.
Перейдем к нашим мероприятиям:

У нас есть выгрузка из базы по следующим столбцам:

  • Ситуация
  • Действия
  • Кол-во клиентов
  • Объём_Продаж_До
  • Объём_Продаж_План
  • Объём_Продаж_Факт
  • Затраты_План
  • Затраты_Факт
Читать еще:  Как сделать итого в excel

Для оценки ситуации в целом введем стандартную формулу суммирования:

Для расчета промежуточных итогов по выбранным параметрам введем =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;R[3]C:R[65533]C), где

  • 109 – функция суммы для расчета промежуточных итогов;
  • R[3]C:R[65533]C – ссылка на диапазон суммирования.

Теперь, отфильтровав столбец «Ситуация» — «Ситуация 4» и «Действие» — Действие 2 и 3, мы получим перерасчет промежуточных итогов по каждому из показателей и можем их сравнить с показателями «Итого»:

В результате: видно, что % прироста продаж по отфильтрованным мероприятиям выше, чем по всем, причем планировали результаты получить ниже, чем по итого, а получили выше. Т.е. сработали лучше, чем планировали и лучше чем по всем мероприятиям.

Формула Excel =промежуточные.итоги() – отличный инструмент для расчета показателей по отфильтрованным параметрам.

Если есть вопросы — обращайтесь!

Присоединяйтесь к нам!

Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа:

  • Novo Forecast Lite — автоматический расчет прогноза в Excel .
  • 4analytics — ABC-XYZ-анализ и анализ выбросов в Excel.
  • Qlik Sense Desktop и QlikView Personal Edition — BI-системы для анализа и визуализации данных.

Тестируйте возможности платных решений:

  • Novo Forecast PRO — прогнозирование в Excel для больших массивов данных.

Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.

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

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

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

Предположим, в распоряжении исследователя находится список жителей района (рис. 1), который довольно обширен.

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

· Скопируем таблицу на новый лист с именем Итоги.

· Отсортируем по параметрам «Образование», «Участие в работе общественных организаций».

· Проведем операцию подведения итогов. Для Excel 2003 необходимо выделить одну из ячеек таблицы, выбрать в строке Меню-Данные-Итоги. Для Excel 2007 на ленте выбрать закладку Данные-Промежуточные итоги. В результате появится диалоговое окно, представленное на рис. 2.

· В окне Промежуточные итоги в окне: При каждом изменении в : — установить условие отбора данных (в примере – Образование).

· В окне Операция установить – Количество.

· В окне: Добавить итоги по: следует пометить Пол и Участие в общественных организациях.

· Пометка Итоги под данными может быть снята, тогда итоги будут отображаться над данными.

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

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

Вопросы для самопроверки

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

2. Как заполнить окно: Промежуточные итоги, когда имеется таблица с данными?

3. Как можно увидеть отдельные группировки промежуточных итогов?

4. Какие правила целесообразно соблюдать при построении итогов?

Ссылка на основную публикацию
ВсеИнструменты
Adblock
detector