Функция subtotal в excel

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

​Смотрите также​В любой версии.​ и не скрытых​8​ аргумент се прилага​ ред в междинна​Скриване и разкриване​MAX​

​ междинни суми, като​ а затем —​ Для констант "номер_функции"​8​ изменив формулу с​Применим Автофильтр и отберем​от 101 до​

Синтаксис функции

​Пусть имеется исходная таблица.​​СТАНДОТКЛОН​

​Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ(), английский вариант​​Dragondeath​ чисел списка. Для​108​ за междинната сума​ сума от вертикален​

​на командата​
​5​​ използвате командата​
​ клавишу ВВОД. При​ от 101 до​​108​

​9​Аргументы функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ описаны​ функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() подсчитает​ скрытых при помощи​ товаром Товар1. Пусть​СТАНДОТКЛОНП​ (сумма, среднее, количество​Необходимо описать аналог​ 111 функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ​109​ диапазони), които са​

​Ако някои от препратките​​Клетки​6​Структура​ все данные.​ скрытых с помощью​109​

​ ниже.​ среднее геометрическое цен​ команды Главная/ Ячейки/​ функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() подсчитает​9​ значений и т.д.)​ функции subtotal, которая​

​ исключает значения строк,​​СУММ​ зададени като следващи​ са 3D препратки,​на раздела​106​на раздела​Данные​ команды​СУММ​Номер_функции​

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() и Автофильтр

​ товаров Товар1 (пример​

​ Формат/ Скрыть или​ сумму товаров Товар1,​109​ в диапазоне, в​ расчитывает промежуточные итоги,​ скрытых при помощи​10​ аргументи.​

​ функцията SUBTOTAL връща​Начало​PRODUCT​Данни​120​​Скрыть строки​​10​ (обязательный). Число от​ не очень жизненный,​ отобразить. Эти коды​

​ следовательно будем использовать​СУММ​ котором имеются скрытые​ но в которую​ команды​110​

​СИНТАКСИС:​ грешната стойност #VALUE!.​в настолното приложение​7​в настолното приложение​10​. Эти константы используются​

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() и Скрытые строки

​110​ 1 до 11​ но он показывает​ используются для получения​ код функции 9 или​10​ строки.​ возможно добавить условия.​

​Скрыть строки​ДИСП​=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(номер_функции, ссылка1, [ссылка2],​Копирайте примерните данни в​ на Excel. Използвайте​107​ на Excel. След​150​​ для получения промежуточных​​ДИСП​ или от 101​ принцип). Будем использовать​ промежуточных итогов только​ 109.​110​Особенность функции состоит в​Функция sumif не​. Эти константы используются​11​ . ])​

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() и средство EXCEL Промежуточные итоги

​ следващата таблица и​ тези константи, когато​STDEV​ като веднъж сте​23​

​ итогов с учетом​11​ до 111, которое​ код функции 3 -​

​ для не скрытых​Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() исключает все​ДИСП​​ том, что она​​ подходит, так как​ для получения промежуточных​111​АРГУМЕНТЫ:​ ги поставете в​

Другие функции

​ искате да изчислите​8​ създали списък с​Формула​ только нескрытых чисел​111​ обозначает функцию, используемую​ подсчет значений.​ чисел списка.​ строки не включенные​11​ предназначена для использования​ она производит суммирование​ итогов только для​ДИСПР​1. номер_функции -​ клетка A1 на​ междинна сума на​

​108​ междинни суми, можете​

​Описание​ списка.​ДИСПР​ для расчета промежуточных​Для подсчета будем использовать​Пусть имеется также исходная​ в результат фильтра​111​ совместно с другими​ всех значений в​ не скрытых чисел​

​2. ссылка1 -​ Обязательный аргумент. Число​ нов работен лист​

​STDEVP​ да го модифицирате,​Результат​Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ исключает все​Ссылка1​ итогов. Функции с​ формулу массива (см. файл​ таблица. Создадим структуру​ независимо от используемого​ДИСПР​ средствами EXCEL: Автофильтром​ таблице по определенным​ списка.​ Обязательный аргумент. Первый​

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

​ от 1 до​ на Excel. За​ скрити числа в​​9​​ като редактирате функцията​

Описание

​=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;A2:A5)​ строки, не включенные​ Обязательный. Первый именованный​ 1 по 11​ примера, лист2)​ с использованием встроенного​ значения константы​​Например, функция СУММ() имеет​​ и Промежуточными итогами.​​ критериям, а игра​​Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ исключает все​​ именованный диапазон или​​ 11 (с включением​ да покажат резултати​ списък. За константите​109​ SUBTOTAL.​

Синтаксис

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

​ в результат фильтра,​ диапазон или ссылка,​

​ учитывают строки, скрытые​​=СРГЕОМ(ЕСЛИ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;СМЕЩ($B$10;СТРОКА(ДВССЫЛ("A1:A"&ЧСТРОК(B10:B19)))-1;));B10:B19;""))​ средства EXCEL -​номер_функции​ код 9. Функция​ См. Файл примера.​ фильтрами в ней​ строки, не включенные​ ссылка, для которых​ скрытых значений) или​ формулите, изберете ги,​ на функция_ном от​SUM​SUBTOTAL(функция_ном;препр1;[препр2];. ])​ ячеек A2:A5, полученное​ независимо от используемого​

​ для которых требуется​
​ вручную, в то​С помощью выражения СТРОКА(ДВССЫЛ("A1:A"&ЧСТРОК(B10:B19)))-1​

​ Промежуточные итоги.​
​и, в нашем​ СУММ() также имеет​

​ в результат фильтра,​

​ требуется вычислить промежуточные​

​ натиснете клавиша F2​

​Синтаксисът на функцията SUBTOTAL​

​ с использованием числа​

​ значения константы "номер_функции".​

​ вычислить промежуточные итоги.​

​ время как функции​

​ в качестве второго​

​Скроем строки с Товар2,​

​ случае, подсчитывает сумму​

​ независимо от используемого​

​ 111 (с исключением​

​ функцията SUBTOTAL игнорира​

​ има следните аргументи:​

​Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ применяется к​

​ аргумента функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ()​

​ нажав на соответствующую​

​ отобранных значений (сумму​

​ можно записать формулу​

​Номер_функции​​ желаемый результат приложен​ значения константы "номер_функции".​3. ссылка2 -​ скрытых значений), которое​

​ натиснете клавиша Enter.​​ стойностите на редовете,​VAR​функция_ном​ первого аргумента.​

Примечания

​ столбцам данных или​ Необязательный. Именованные диапазоны​ 111 пропускают такие​ подается не один​ кнопку "минус" в​ цен товара Товар1). ​ =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;A2:A10) или =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;A2:A10).​

​ — это число от​ в файле ниже. ​Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ применяется к​ Необязательный аргумент. Именованные​ указывает, какую функцию​​ Ако е необходимо,​​ скрити с командата​​11​​ Задължително. Число от​​303​​ вертикальным наборам данных.​​ или ссылки 2—254,​​ строки; отфильтрованные ячейки​​ диапазон, а несколько​​ структуре.​Если бы мы записали​ В чем различие​ 1 до 11, которое​При этом в​ столбцам данных или​ диапазоны или ссылки​ следует использовать при​ коригирайте ширините на​Скрий редовете​111​ 1 до 11​​=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(1;A2:A5)​​ Она не предназначена​ для которых требуется​ всегда исключаются.​ (равного числу строк).​Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() исключает все​

​ формулу =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;B11:B20) или​ — читайте ниже.​ указывает какую функцию​ файле отсутствуют столбцы,​ вертикальным наборам данных.​

​ 2—254, для которых​ вычислении итогов внутри​ колоните, за да​. Използвайте тези константи,​VARP​ или от 101​Среднее значение промежуточного итога​ для строк данных​ вычислить промежуточные итоги.​Номер_функции​ Если строка скрыта,​ неотображаемые строки структурой независимо от​ =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(103;B11:B20), то мы​ Обычно используют коды​ использовать при вычислении​ значения в которых​ Она не предназначена​ требуется вычислить промежуточные​ списка.​ видите всичките данни.​

​ когато искате да​Препр1​ до 111, което​ диапазона ячеек A2:A5,​

Пример

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

​ изчислите междинна сума​

​ Задължително. Първият наименуван​

​ указва коя функция​

​ полученное с использованием​

​ подведения итогов внутри​

​ выводится значение Пустой​

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

​120​ само на не​ диапазон или препратка​ да се използва​ числа 1 в​

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

​ аргументов "ссылка1;ссылка2;. " (вложенные​

​Номер_функции​ текст "", которое​и, в нашем​ (5).​Ссылка1​(включая скрытые значения)​Казанский​ данных. Так, при​Если уже имеются формулы​(с включением скрытых​

SUBTOTAL функция

​10​ скрити числа в​ за, които искате​​ за междинната сума.​​ качестве первого аргумента.​

Описание

​ горизонтального набора данных​ итоги), то эти​(с исключением скрытых​ игнорируется функцией СРГЕОМ().​ случае, подсчитывает сумму​Таким образом, эта функция​; Ссылка2; — от​Номер_функции​​: Не понял вопроса.​​ определении промежуточных итогов​​ подведения итогов внутри​​ значений) Номер_функции​​150​​ списък.​ междинната сума.​ 1 – 11​75,75​ с помощью значения​ вложенные итоги игнорируются,​ значений)​ Таким образом, подсчитывается​

Синтаксис

​ только товара Товар1. Этот​

​ "чувствует" скрыта ли​ 1 до 29​

​(за исключением скрытых​​ Если надо суммировать​ горизонтального набора данных​ аргументов "ссылка1;ссылка2;. " (вложенные​(с исключением скрытых​23​Функцията SUBTOTAL игнорира всички​Препр2. ​ включва ръчно скритите​Примечания​ константы "номер_функции" от​ чтобы избежать двойного​Функция​ среднее геометрическое цен​ результат аналогичен ситуации​

​ строка автофильтром или​
​ ссылок на диапазон,​

На этом шаге мы рассмотрим параметры этого метода и пример использования .

Метод Subtotal добавляет промежуточные итоги в список данных, основываясь на изменениях в определенных полях данных. Промежуточные итоги позволяют обобщить данные. Метод Subtotal автоматически вставляет строки с промежуточными итогами, в которые введены формулы для подсчета итогов. Необходимо, чтобы до активизации этого метода данные были правильно отсортированы. В противном случае этот метод может привести к неверному выводу промежуточных итогов. Вручную метод Subtotal вызывается командой Данные | Итоги (Data | Subtotal) .

Основным методом, связанным с Subtotal , является метод RemoveSubtotal , удаляющий промежуточные итоги с рабочего листа. Метод RemoveSubtotal применяется к объекту Range .

Приведем соответствие между аргументами метода Subtotal и подведением промежуточных итогов на рабочем листе вручную при помощи команды Данные | Итоги (Data | Subtotal) на примере подсчета продаж компьютеров по месяцам и нахождения средних объемов продаж (рисунок 1).

Рис.1. Отчет о продажах компьютеров

Выделим ячейки списка, в данном случае диапазон A1:C16 . Выберем команду Данные | Итоги (Data | Subtotal) . На экране отобразится диалоговое окно Промежуточные итоги (Subtotal) (рисунок 2).

Рис.2. Диалоговое окно Промежуточные итоги

Нажмем кнопку ОК в диалоговом окне Промежуточные итоги (Subtotal) , что приведет к созданию промежуточных итогов на рабочем листе по выделенному диапазону данных (рисунок 3).

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

На VBA тот же результат достигается применением следующих инструкций:

Рис.4. Результат добавления показателя средней реализации компьютеров по месяцам

На VBA тот же результат достигается применением следующих инструкций:

Рис.5. Вывод промежуточных итогов без детализации

На VBA тот же результат достигается применением следующих инструкций:

Здесь свойство Outline рабочего листа возвращает объект Outline (Структура) , а свойство ShowLevels объекта Outline устанавливает отображаемый уровень детализации.

Для удаления промежуточных итогов следует выделить данные с этими итогами, выполнив команду Данные | Итоги (Data | Subtotal) и нажать кнопку Убрать все (Remove All) в появившемся диалоговом окне Промежуточные итоги (Subtotal) . На VBA тот же результат достигается применением следующих инструкций:

Со следующего шага мы начнем рассматривать сценарии .

В отличие от отдельных функций – сумма, среднее, минимум и проч., команда Промежуточный итог [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 для всех уровней.