Номера функций промежуточные итоги в excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Читать еще:  Итого в excel формула

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

    Сумма видимых строк. Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ

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

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

    Рис. 1. Серия 100 в первом аргументе функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ используется для обработки видимых строк

    Скачать заметку в формате Word или pdf, примеры в формате Excel

    ПРОМЕЖУТОЧНЫЕ.ИТОГИ может выполнить 11 операций. Первый аргумент функции указывает ей на следующие операции: (1) СРЗНАЧ, (2) СЧЁТ, (3) СЧЁТЗ, (4) МАКС, (5) МИН, (6) ПРОИЗВЕД, (7) СТАНДОТКЛОН, (8) СТАНДОТКЛОНП, (9) СУММ, (10) ДИСП, (11) ДИСПР. При добавлении сотни выполняются те же операции, но только над видимыми ячейкам. Например, 104 найдет максимум среди видимых ячеек. Под видимыми имеется ввиду, не видимые на экране (например, 120 строк не уместятся на экране), а не скрытые, командой Скрыть.

    В ячейке Е566 (см. рис. 1) используется формула =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;E2:E564). Excel возвращает сумму только видимых (не скрытых) ячеек в диапазоне, а именно – Е2;Е30;Е72;Е78;Е564.

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

    Рис. 2. Формула не игнорирует ячейки в скрытых столбцах

    Дополнительные сведения: существует необычное исключение в поведении функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ. Когда строки были скрыты по какой-либо из команд фильтра (расширенный фильтр, автофильтр или фильтр), Excel суммирует только видимые строки даже в варианте ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;диапазон). Нет необходимости использовать версию 109 (рис. 3). Здесь фильтр используется для поиска записей Chevron.

    Рис. 3. Достаточно аргумента 9 если строки скрыты в результате применения фильтра

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

    1. Выбрать любую ячейку в вашем наборе данных.
    2. Пройдите по меню ДАННЫЕ –>Фильтр (или нажмите Alt + Ы, а затем не отпуская Alt, нажмите Ф; или нажмите Ctrl+Shift+L). Excel добавляет фильтр (выпадающее меню) для всех заголовков столбцов.
    3. Откройте одно из выпадающих меню, например, Customer. Снимите флажок Выделить все, а затем выберите одного клиента. В нашем примере – Chevron.
    4. Выберите ячейки непосредственно под отфильтрованными данными. В нашем примере –ячейки Е565:H565.
    5. Нажмите клавиши Alt+= или щелкните значок Автосумма (меню ГЛАВНАЯ). Вместо того, чтобы использовать СУММ, Excel применит функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;диапазон), которая просуммирует только строки, выбранные фильтром (см. рис. 3).

    В Excel 2010 появилась еще одна подобная функция – АГРЕГАТ (подробнее см. Сравнение массивов и выборки по одному или нескольким условиям; раздел Функция АГРЕГАТ). Она имеет больше функций в своем «репертуаре» и больше опций, какие строки исключать, а какие обрабатывать. Основное ее достоинство – обработка ошибочных значений (например, #ДЕЛ/0!). К сожалению, эта функция также не применима к суммированию видимых столбцов.

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

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

    В этой статье описаны синтаксис формулы и использование функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ в Microsoft Excel.

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

    Аргументы функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ описаны ниже.

    Номер_функции — обязательный аргумент. Число от 1 до 11 или от 101 до 111, которое обозначает функцию, используемую для расчета промежуточных итогов. Функции с 1 по 11 учитывают строки, скрытые вручную, в то время как функции с 101 по 111 пропускают такие строки; отфильтрованные ячейки всегда исключаются.

    Номер_функции
    (с включением скрытых значений)

    Номер_функции
    (с исключением скрытых значений)

    Читать еще:  Как в excel оставить значение и убрать формулу

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

    Ссылка2;. Необязательный. Именованные диапазоны или ссылки 2—254, для которых требуется вычислить промежуточные итоги.

    Примечания

    Если уже имеются формулы подведения итогов внутри аргументов «ссылка1;ссылка2;. » (вложенные итоги), эти вложенные итоги игнорируются, чтобы избежать двойного суммирования.

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

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

    Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ применяется к столбцам данных или вертикальным наборам данных. Она не предназначена для строк данных или горизонтальных наборов данных. Так, при определении промежуточных итогов горизонтального набора данных с помощью значения константы «номер_функции» от 101 и выше (например, ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;B2:G2)), скрытие столбца не повлияет на результат. Однако на него повлияет скрытие строки при подведении промежуточного итога для вертикального набора данных.

    Если среди ссылок есть трехмерные ссылки, функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ возвращает значение ошибки #ЗНАЧ!.

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

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

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

    ПРОМЕЖУТОЧНЫЕ. ИТОГИ(номер_функции; ссылка1; ссылка2;. )

    Номер_функции — это число от 1 до 11, которое указывает, какую функцию использовать при вычислении итогов внутри списка.

    Ссылка1; Ссылка2; — от 1 до 29 интервалов или ссылок, для которых подводятся итоги.

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

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

    Если среди ссылок есть трехмерные ссылки, функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ возвращает значение ошибки #ЗНАЧ!.

    Трехмерные ссылки используются при необходимости анализа данных из одной и той же ячейки или диапазона ячеек на нескольких листах одной книги. Трехмерная ссылка включает в себя ссылку на ячейку или диапазон, перед которой ставятся имена листов. Microsoft Excel использует все листы, хранящиеся между начальным и конечным именами, указанными в ссылке. Например, формула =СУММ(Лист2:Лист13!B5) суммирует все значения, содержащиеся в ячейке B5 на всех листах в диапазоне от Лист2 до Лист13 включительно.

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

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

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

    Такой оперативный анализ очень полезен техническим исполнителя. Представьте, Вы спокойно сидите перед своим персональным компьютером, на своем рабочем месте и мирно занимаетесь своими личными делами. И вдруг, звонок начальника: «а скажи-ка мне Тяпкин-Ляпкин, скока у нас того-то и того-то»? Пока начальник выговаривает, что ему надо, Вы быстренько запускаете файл с базой данных и по фильтру отбираете то, что интересует руководство. Ответ готов, начальник доволен, товарищи по работе в шоке, а Вы опять спокойно занимаетесь своими делами.

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

    Читать еще:  Как сделать рейтинг в excel формула

    Промежуточные итоги в Excel с примерами функций

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Похожие статьи

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