Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTAL)
Возвращает промежуточный итог в список или базу данных. Обычно проще создать список с промежуточными итогами, используя в настольном приложении Excel команду Промежуточные итоги в группе Структура на вкладке Данные. Но если такой список уже создан, его можно модифицировать, изменив формулу с функцией ПРОМЕЖУТОЧНЫЕ.ИТОГИ.
Аргументы
Обязательный. Число от 1 до 11 (с включением скрытых значений) или от 101 до 111 (с исключением скрытых значений), которое указывает, какую функцию следует использовать при вычислении итогов внутри списка:
| Номер_функции (с включением скрытых значений) | Номер_функции (с исключением скрытых значений) | Функция |
|---|---|---|
| 1 | 101 | СРЗНАЧ |
| 2 | 102 | СЧЁТ |
| 3 | 102 | СЧЁТЗ |
| 4 | 104 | МАКС |
| 5 | 105 | МИН |
| 6 | 106 | ПРОИЗВЕД |
| 7 | 107 | СТАНДОТКЛОН |
| 8 | 108 | СТАНДОТКЛОНП |
| 9 | 109 | СУММ |
| 10 | 110 | ДИСП |
| 11 | 111 | ДИСПР |
Обязательный. Первый именованный диапазон или ссылка, для которых требуется вычислить промежуточные итоги.
Необязательный. Именованные диапазоны или ссылки 2—254, для которых требуется вычислить промежуточные итоги.
Замечания
- Если уже имеются формулы подведения итогов внутри аргументов «ссылка1;ссылка2;…» (вложенные итоги), то эти вложенные итоги игнорируются, чтобы избежать двойного суммирования.
- Для констант «номер_функции» от 1 до 11 функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ учитывает значения строк, скрытых с помощью команды Скрыть строки (меню Формат, подменю Скрыть или отобразить) в группе Ячейки на вкладке Главная в настольном приложении Excel. Эти константы используются для получения промежуточных итогов с учетом скрытых и нескрытых чисел списка. Для констант «номер_функции» от 101 до 111 функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ исключает значения строк, скрытых с помощью команды Скрыть строки. Эти константы используются для получения промежуточных итогов с учетом только нескрытых чисел списка.
- Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ исключает все строки, не включенные в результат фильтра, независимо от используемого значения константы «номер_функции».
- Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ применяется к столбцам данных или вертикальным наборам данных. Она не предназначена для строк данных или горизонтальных наборов данных. Так, при определении промежуточных итогов горизонтального набора данных с помощью значения константы «номер_функции» от 101 и выше (например, ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;B2:G2)), скрытие столбца не повлияет на результат. Однако на него повлияет скрытие строки при подведении промежуточного итога для вертикального набора данных.
- Если среди ссылок есть трехмерные ссылки, функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ возвращает значение ошибки #ЗНАЧ!.
Вычисления в сводной таблице (в области значений) в Excel 2013
Важно : Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() разработана для столбцов данных или вертикальных наборов данных. Она не предназначена для строк данных или горизонтальных наборов данных (ее использование в этом случае может приводить к непредсказуемым результатам).
| Номер_функции (с включением скрытых значений) | Номер_функции (с исключением скрытых значений) | Функция |
|---|---|---|
| 1 | 101 | СРЗНАЧ |
| 2 | 102 | СЧЁТ |
| 3 | 102 | СЧЁТЗ |
| 4 | 104 | МАКС |
| 5 | 105 | МИН |
| 6 | 106 | ПРОИЗВЕД |
| 7 | 107 | СТАНДОТКЛОН |
| 8 | 108 | СТАНДОТКЛОНП |
| 9 | 109 | СУММ |
| 10 | 110 | ДИСП |
| 11 | 111 | ДИСПР |
Расчет промежуточных итогов с использованием промежуточной функции на панели инструментов
Таблица должна быть отсортирована в порядке возрастания в столбце и не содержит формул или вычислений, потому что формулы вставляются только с функцией «частичный результат».
Как создать разделенный результат с помощью функции «Сплит результат» на вкладке «Данные»
В этой маске также выбраны следующие параметры: Заменить существующие частичные результаты, отобразить результаты ниже данных. В поле «Использовать» выберите функцию «Сумма». . Общая сумма отображается ниже таблицы.
Вычисление частичных результатов с использованием динамической таблицы
- Создать таблицу с заголовком столбца.
- Таблица не должна иметь автофильтр.
- Строка результата вставлена ниже таблицы.
- Щелкните в этой строке.
- Расчет соответствует формуле «частичный результат».
При подведении итогов Excel автоматически создает формулу, добавляет строку или строки для записи промежуточных итогов и подставляет адреса ячеек данных. Общие итоги подводятся с помощью данныхсписка, а не с помощью значений промежуточных итогов.
Значения общих и промежуточных итогов пересчитываются автоматически при каждом изменении данных в списке.
Промежуточные итоги в Excel (Эксель): примеры использования (2019)
- Используйте автофильтр для своей таблицы.
- Поместите себя на пустую строку под своей таблицей.
- Оставьте пробел между формулой и последней строкой таблицы.
- Введите следующую формулу: = частичный результат.
Важное условие применения средств – значения организованы в виде списка или базы данных, одинаковые записи находятся в одной группе. При создании сводного отчета промежуточные итоги формируются автоматически.
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() и Автофильтр
Пусть имеется та же исходная таблица. Скроем строки с товаром Товар2 через меню или через контекстное меню.
Функция промежуточные итоги в excel. Как в эксель посчитать сумму столбца
- убедиться, что названия столбцов имеют заголовки;
- данные по столбцу Товары, например с помощью ;
- выделив любую ячейку в таблице, вызвать Промежуточные итоги (в меню Данные/ Структура );
Определите с помощью мыши необходимую область, после чего смотрим на раздел «Данные». Находим там блок под названием «Структура» и выбираем в небольшом списке последнюю строчку — «Промежуточные итог».
Уровни групп
Этот способ отображения позволяет уменьшить количество визуальной информации на экране – вы увидите только нужные данные итогов – количество одежды каждого размера и общее количество.
Нажмите на первый второй или третий уровень в панели управления группами. Выберите наиболее подходящий вариант представления информации:
Первый уровень отображает только общее количество вещей, второй – количество каждого размера и итоговое число, третий – разворот всех данных листа.
Функция промежуточные итоги в Excel формула » Компьютерная помощь
Теперь у вас вновь появится уже знакомое окно аргументов, где уже по желанию вы можете указать дополнительные массивы данных, выполнив те же самые шаги, что и раньше. Если же таких не имеется, то кликайте по кнопке «ОК».
Функция промежуточные итоги в Excel формула
Использование функции «Промежуточные итоги» в Excel
Однако, как может показаться на первый взгляд, функция промежуточного итога не всегда реализуется во всех таблицах Excel. Вот несколько главных критериев, которые помогут вам разобраться, подходит ли ваша база данных к оным условиям:
- Строчки таблицы обязаны быть формата обычной области ячейки;
- Верхняя часть таблицы обязана быть выполненной только из единственной строки и находиться на первой строчке листа;
- Таблица ни в коем случае не должна быть пустой.
Теперь перейдем непосредственно к самому главному — применению функции на деле. Отдел, отвечающий за «Промежуточные итоги», находится в верхнем меню Excel.
Определите с помощью мыши необходимую область, после чего смотрим на раздел «Данные». Находим там блок под названием «Структура» и выбираем в небольшом списке последнюю строчку — «Промежуточные итог».
У вас должно высветиться новое окно, где надо настроиться отображение вашего промежуточного итога. В приведенном нами примере мы смотрим на уже имеющийся итог от общей суммы продаж за всю продукцию за все дни. Дата продажи указанна в отдельном столбце. Следовательно, в области «При каждом изменении в. « указываем колонку «Дата».
Теперь переходим в поле «Операция» и указываем значение «Сумма» за каждые сутки, которое необходимо нам вывести. Если покопаться, то вы можете так же там найти настройку значений: максимум, произведение, количество, минимум.
После общаемся к полю «Добавить итоги по», где теперь нужно указать столбец с суммой выручки, то есть колонку «Сумма выручки, руб.».
Поставьте галочку напротив «Заменить текущие итоги», если таковой там не имеется. Данный шаг сделает так, что при вычислении базы данных у вас не будет дублироваться записанная информация, приходящиеся на операцию функции «Промежуточный итог».
Так же вам следует поместить галочку напротив пункта «Конец страницы между группами». Это позволит печатать на некоторой странице отдельные блоки таблицы с указанными на ней промежуточными итогами.
Галочка, находящаяся рядом с «Итоги под данными» отвечает за установку суммы промежуточных итогов в области под строчкой. При удалении галочки данные перенесутся в область сверху строки. Эта настройка всегда делается пользователем индивидуально, чтобы ему было удобно обращаться к выведенной информацией.
После завершения вышеуказанного алгоритма действий вы придете к тому, что в таблице появятся промежуточные итоги ваших продаж. Хотим обратить ваше внимание к тому, что вы пожеланию можете скрыть все строки соединенные промежуточным итогом, всего лишь нажав ПКМ на иконку «-«, находящуюся в левой части таблицы оной группы.
Либо у вас есть возможно наоборот скрыть все остальные строки, кроме одних промежуточных итогов.
Можете не беспокоиться за функционирование используемого инструмента. Если же вам понадобиться внести какие-либо изменения в таблицу, то она сама автоматически обновит свои данные.
Существует отдельная кнопка нужной нам операции, позволяющая посчитать с помощью специально созданного инструмента для этого. Называется он «Вставить функцию».
Заранее кликнете ПКМ по нужной ячейке, куда хотите указать промежуточные итоги. Теперь нажимайте на названую нами кнопку, находящуюся в левой части от поля ввода формул.
У вас должно высветиться окно «Мастер функций». В списке выбираете строчку «ПРОМЕЖУТОЧНЫЕ.ИТОГИ», после чего нажимайте на «ОК».
В соответствующих полях определяем аргументы функции. После в области «Номер функции» необходимо указать один из 11 параметров обработки, а именно:
После этого вам будет доступен визуальный редактор аргумента функции, где вы уже самолично можете указать курсором необходимый массив данных. Теперь просто нажмите на кнопку справа от адреса массива, как показано на скриншоте, и он автоматически появится в форме функции.
Теперь у вас вновь появится уже знакомое окно аргументов, где уже по желанию вы можете указать дополнительные массивы данных, выполнив те же самые шаги, что и раньше. Если же таких не имеется, то кликайте по кнопке «ОК».
Необходимые промежуточные итоги теперь будут вычислены и указаны в определенных ячейках, где была прописана формула.
В статье мы рассмотрели два главенствующих способа, способных помочь нам вычислить промежуточный итог: это либо через специальную кнопку, либо через определенную формулу. Вам решать, какой из этих методов удобнее в использовании, а мы надеемся, что данные знания окажутся вам полезны.
Excel промежуточные итоги с условием
Как использовать их для поиска промежуточных итогов в серии чисел и для четкого выделения вычисленных результатов в вашей таблице является темой этой статьи. На практике особенно часто встречаются промежуточные результаты в таблице. Необходимо рассчитать промежуточные суммы в зависимости от значения.
Вычисления в сводной таблице (в области значений) в Excel 2013
Вычисления в сводной таблице подчиняются правилам по умолчанию. При формировании сводной таблицы данные в поле значений обрабатывают данные источника следующим образом:
Возьмем, например, исходные данные, представленные на рис. 1. Если включить в область значений сводной таблицы поля Заказчик и Доход, то по первому полю посчитается число заказчиков, а по второму – сумма дохода (рис. 2).
Рис. 1. Исходные данные, используемые во всех примерах заметки
Рис. 2. В сводной таблице для числовых полей в области значений по умолчанию находится сумма, для остальных типов данных – количество
Рис. 3. Окно Параметры поля значений: (а) вкладка Операция, (б) вкладка Дополнительные вычисления
Рис. 4. Основные функции вычислений в сводной таблице
Некоторые функции требуют пояснения. Смещенное отклонение – оно же среднеквадратичное отклонение выборки. Несмещенное отклонение – стандартное отклонение в генеральной совокупности. Аналогично и два вида дисперсии. Видно, что смещенные значения немного больше несмещенных. Более подробно о сути различий этих двух оценок см., например, Выборочная дисперсия.
Дополнительные вычисления покрывают самые разнообразные требования, которые могут возникнуть при анализе данных. В зависимости от выбранной функции (область 1 на рис. 5) дополнительная настройка может не потребоваться (рис. 5а), или может потребоваться выбрать только базовое поле (рис. 5б) или, и базовое поле, и базовый элемент (рис. 5в).
Рис. 5. В зависимости от выбранной функции (область 1): (а) дополнительная настройка не требуется, (б) требуется выбор базового поля, (в) требуется выбор базового поля и базового элемента
Рис. 6. Совместное влияние на вычисления основной и дополнительной функций
Работа дополнительных функций «% от общей суммы», «% от суммы по столбцу» и «% от суммы по строке» показана на рис. 7. Эти функции не требуют дополнительных настроек.
Рис. 7. Дополнительные функции: (а) % от общей суммы, (б) % от суммы по столбцу, (в) % от суммы по строке
Несколько более сложной для понимания является дополнительная функция «доля». Чтобы создать сводную таблицу, изображенную на рис. 8:
Видно, что значения в столбце Доля показывают процент от продаж в Нью-Йорке.
Начиная с версии 2010 в Excel появились дополнительные функции Сортировки. На рис. 9а показана сортировка от минимального значения (которому присваивается ранг 1) до максимального. На рис. 9б добавлено поле Регион, а сортировка осуществляется от максимального значения (ранг 1) к минимальному. Видно, что сортировка выполняется в каждом регионе отдельно.
Рис. 9. Дополнительная функция Сортировка: (а) сортировка от минимального к максимальному; (б) сортировка от максимальному к минимальному
Вычисление суммы с нарастающим итогом обычно выполняется для таблиц, в которых месяцы расположены по строкам (рис. 10а), либо, когда нужно показать, что первые Nклиентов дают N% дохода/прибыли (рис. 10б). Видно, что первые 9 клиентов обеспечивают 80% продаж.
Рис. 10. Дополнительная функция: (а) С нарастающим итогом в поле; (б) % от суммы с нарастающим итогом в поле
Дополнительная функция Приведенное отличие похожа на функцию Доля (см. рис. 8). Например, на рис. 9 в ячейке С4 функция Приведенное отличие показывает на сколько процентов доход в Детройте меньше, чем доход в Нью-Йорке.
Дополнительная функция «% от суммы по родительской строке» показывает долю текущего элемента в промежуточных итогах. Например, в ячейке Е13 (рис. 12) значение 2,75% показывает долю дохода в Чикаго ($ 184 425) от общего по стране ($6 707 812). В ячейке D9 значение 78,84% показывает долю Детройта ($1 372 957) от общего по региону Средний Запад ($1 741 424).
Рис. 12. Дополнительная функция % от суммы по родительской строке
Самой загадочной является дополнительная функция Индекс (рис. 13). Обратите внимание, индекс персиков в Джорджии 2,55, а в Калифорнии – 0,5. Если в следующем году урожай персиков пострадает, это нанесет сильный удар по фермерам Джорджии и лишь слегка затронет фермеров Калифорнии.
Например, в Огайо индекс яблок = 4,91, поэтому производство яблок имеет первостепенную важность для этого штата.
[2] Заметка написана на основе книги Билл Джелен, Майкл Александер. Сводные таблицы в Microsoft Excel 2013. Глава 3.Вычисление частичных результатов с использованием динамической таблицы
Как подсчитываются промежуточные итоги. Вы можете отображать промежуточные итоги в списке с несколькими типами вычислений за раз. Значения общей суммы получены из подробных данных, а не из значений промежуточных строк. Вы можете вставлять промежуточные итоги меньших групп в существующие промежуточные группы.
Данные итогов в таблице Excel
Вы можете быстро подвести итоги в таблице Excel, включив строку итогов и выбрав одну из функций в раскрывающемся списке для каждого столбца. В строке итогов по умолчанию используется функция ИТОГИ,которая позволяет включать или игнорировать скрытые строки таблицы, но вы также можете использовать другие функции.
Выберите Работа с таблицами > Конструктор и установите флажок Строка итогов.
Строка итогов будет вставлена в нижней части таблицы.
Примечание: Если применить в строке итогов формулы, а затем отключить ее, формулы будут сохранены. В приведенном выше примере мы применили функцию СУММ для строки итогов. При первом использовании строки итогов ячейки будут пустыми.
Выделите нужный столбец, а затем выберите вариант из раскрывающегося списка. В этом случае мы применили функцию СУММ к каждому столбцу:
Вы увидите, что Excel формулу: =ПРОМЕЖУТОЧНЫЕ.109;[Средний]). Это функция SUBTOTAL для функции СУММ, а также формула структурированной ссылки, которая является исключительно Excel таблицами. Подробнее об использовании структурированных ссылок в Excel таблицах.
К итоговому значению можно применить и другие функции, щелкнув Другие функции или создав их самостоятельно.
Примечание: Если вы хотите скопировать формулу в смежную ячейку строки итогов, перетащите ее вбок с помощью маркера заполнения. При этом ссылки на столбцы обновятся, и будет выведено правильное значение. Не используйте копирование и вставку, так как при этом ссылки на столбцы не обновятся, что приведет к неверным результатам.
Вы можете быстро подвести итоги в таблице Excel, включив строку итогов и выбрав одну из функций в раскрывающемся списке для каждого столбца. В строке итогов по умолчанию используется функция ИТОГИ,которая позволяет включать или игнорировать скрытые строки таблицы, но вы также можете использовать другие функции.
Строка итогов будет вставлена в нижней части таблицы.
Примечание: Если применить в строке итогов формулы, а затем отключить ее, формулы будут сохранены. В приведенном выше примере мы применили функцию СУММ для строки итогов. При первом использовании строки итогов ячейки будут пустыми.
Выделите нужный столбец, а затем выберите вариант из раскрывающегося списка. В этом случае мы применили функцию СУММ к каждому столбцу:
Вы увидите, что Excel формулу: =ПРОМЕЖУТОЧНЫЕ.109;[Средний]). Это функция SUBTOTAL для функции СУММ, а также формула структурированной ссылки, которая является исключительно Excel таблицами. Подробнее об использовании структурированных ссылок в Excel таблицах.
К итоговому значению можно применить и другие функции, щелкнув Другие функции или создав их самостоятельно.
Примечание: Если вы хотите скопировать формулу в смежную ячейку строки итогов, перетащите ее вбок с помощью маркера заполнения. При этом ссылки на столбцы обновятся, и будет выведено правильное значение. Не используйте копирование и вставку, так как при этом ссылки на столбцы не обновятся, что приведет к неверным результатам.
Вы можете быстро подытогить итоги в Excel, включив параметр Строка итогов.
На вкладке Конструктор таблиц > стилей > строке итогов.
Строка Итог будет вставлена в нижнюю часть таблицы.
Настройка агрегатной функции для ячейки «Строка итогов»
Примечание: Это одна из нескольких бета-функций, и в настоящее время она доступна только для части инсайдеров Office. Мы будем оптимизировать такие функции в течение следующих нескольких месяцев. Когда они будут готовы, мы сделаем их доступными для всех участников программы предварительной оценки Office и подписчиков Microsoft 365.
Строка итогов позволяет выбрать агрегатную функцию для каждого столбца.
Щелкните ячейку в строке итогов под столбцом, который вы хотите изменить, а затем щелкните ссылку, которая появится рядом с ячейкой.
Выберите агрегатную функцию для столбца. Обратите внимание, что вы можете щелкнуть Другие функции, чтобы увидеть дополнительные параметры.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.
Данные итогов в таблице Excel
- 1. В окне открытого листа щелкните правой кнопкой мыши на любой ячейке таблицы.
- В контекстном меню наведите курсор на пункт «Таблица».
- В списке команд выберите пункт «Строка итогов» (рис. 5.26).
Определите, для каких данных нужно проводит итоговые вычисления. В нашем случае это столбец «Размер». Его содержимое нужно отсортировать. Сортировка проводится от большего элемента к меньшему. Выделяем столбец «Размер»;
