Green-sell.info

Новые технологии
0 просмотров
Рейтинг статьи
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 для всех уровней.

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

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

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

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

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

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

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

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

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

    Читать еще:  Excel для чайников

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

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

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

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

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

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

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

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

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

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

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

    Данные итогов в таблице 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, то формула промежуточные.итоги() работает, как стандартная формула по выбранной функции.
    Перейдем к нашим мероприятиям:

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

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

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

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

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

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

    Читать еще:  Как делать перенос в ячейке excel

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

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

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

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

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

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

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

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

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

    Функция ПРОМЕЖУТОЧНЫЕ ИТОГИ в Excel

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

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

    В строках 18-19 посчитаны суммарные и средние продажи в каждом месяце. Для этого использованы функции СУММ и СРЗНАЧ соответственно. Так сделает каждый, кто неплохо знает программу. А мы поступим иначе, используем функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ. Синтаксис ее таков:

    =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(функция ; ссылка 1 ; [ссылка 2] ; … ; [ссылка n])

    Её аргументы:

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

    Предусмотрены такие функции:

    Функция Excel

    Код, когда скрытые строки учитываются

    Код, когда скрытые строки НЕ учитываются

    Описание

    Количество ячеек, содержащих числа

    Количество непустых ячеек

    Максимальное в массиве

    Минимальное в массиве

    Произведение всех элементов

    Стандартное отклонение по выборке

    Стандартное отклонение по генеральной совокупности

    Сумма всех элементов

    Дисперсия по выборке

    Дисперсия по генеральной совокупности

    Тогда формулу суммирования можно записать так:

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

    Так в чём преимущество, если функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ выглядит сложнее? Я приведу несколько:

    1. Для интерактивных таблиц, где клиент сам указывает, какой показатель вывести на экран, эта функция позволяет легко организовать изменение показателя в пределах ячейки.
    2. Используется лишь одна функция, в которой достаточно изменить один код, чтобы пересчитать все итоги
    3. Самое важное: функцию можно настроить на отображение итогов лишь для видимых ячеек!

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

    В январе эта величина составила 485 шт.

    Обратите внимание, код функции – 1, т.е. «среднее значение, скрытые ячейки учитываются». Теперь скроем строки 5-10 и убедимся, что среднее значение в январе осталось таким же, 485.

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

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

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

    Это всё, что я планировал рассказать. Делитесь статьёй с друзьями, если она вам понравилась, а если возникли сложности – пишите комментарии!

    Ссылка на основную публикацию
    Adblock
    detector