Вставка промежуточных итогов в списке данных на листе
Важно: В Excel не поддерживаются Excel. Если вы работаете с Excel таблицей, команда «Excel», будет неавтоматна. Чтобы добавить в таблицу подытую, необходимо сначала преобразовать таблицу в обычный диапазон данных, а затем добавить подытую. Обратите внимание, что в этом случае будут исключены все функции таблицы, кроме форматирования таблицы. Вы также можете создать сетную.
При вставке подытов:
- Промежуточные итоги вычисляются с помощью итоговая функция, например Sum или Average,с помощью функции SUBTOTAL. Для каждого столбца можно отобразить несколько типов итогов.
- Общие итоги получаются из детали, а не из значений в сумме. Например, если вы используете суммарную функцию Среднее, в строке итогов отображается среднее значение всех строк с подробными данными в списке, а не среднее значение в строках итогов.
Если в книге настроено автоматическое пересчет формул, при редактировании подробных данных команда Итоги автоматически пересчитывает итоговые и общие значения. Команда «Подытая» также определяет структуру списка, чтобы можно было отобразить и скрыть строки с подробными подробностями для каждого из них.
Вставка подытов
Примечание: При фильтрации данных, которые содержат подытог, они могут отображаться скрытыми. Чтобы снова отобразить их, очистка всех фильтров Дополнительные сведения о применении фильтров см. в кратком запуске. Фильтрация данных с помощью автофильтра.
- Убедитесь, что каждый столбец в диапазон данных, для которого вычисляются подытогов, имеет метку в первой строке, содержит похожие факты в каждом столбце и что в диапазоне нет пустых строк или столбцов.
- Выделите ячейку в диапазоне.
- Выполните одно из указанных ниже действий. Вставка одного уровня подытогов Вы можете вставить один уровень подытогов для группы данных, как показано в примере ниже. 1. При каждом изменении в столбце Спорт. 2. . подытожите столбец «Продажи».
- Для сортировки столбца, содержащего данные, по которым требуется выполнить группировку, выделите этот столбец и на вкладке Данные в группе Сортировка и фильтр нажмите кнопку Сортировка от А до Я или Сортировка от Я до А.
- На вкладке Данные в группе Структуру нажмите кнопку Подытая.Отобразилось диалоговое окно «Подытая».
- В поле При каждом изменении выберите столбец для подытверха. Например, в примере выше нужно выбрать Sport.
- В поле Использовать функцию выберите суммарную функцию, которую вы хотите использовать для вычисления итогов. Например, в примере выше нужно выбрать Sum.
- В поле Добавить подытожную сумму в выберите каждый столбец со значениями, которые вы хотите выложить. Например, в примере выше нужно выбрать Продажи.
- Если вы хотите, чтобы после каждого из подытов был автоматический разрыв страницы, выберите разрыв страницы между группами.
- Чтобы указать итоговую строку над строкой с подробными данными, разо убедитесь в том, что под данными не указаны данные. Чтобы указать итоговую строку под строкой сведений, выберите поле Итоги под данными. Например, в примере выше этот квадрат можно было бы очистить.
- При желании вы можете использовать команду Итоги еще раз, повторив шаги с 1 по 7, чтобы добавить дополнительные итоги с различными суммарными функциями. Чтобы не переописывать существующие подытоговые, разместим поле Заменить текущие.
Вставка вложенных уровней подытогов
Вы можете вставить подытогов для внутренних вложенных групп в соответствующие внешние группы, как показано в примере ниже.
1. При каждом изменении внешнего столбца Регион.
2. . вы можете вычитать для каждого изменения внутренний столбец Sport и столбец Sales для этого региона.
- Для сортировки столбца, содержащего данные, по которым требуется выполнить группировку, выделите этот столбец и на вкладке Данные в группе Сортировка и фильтр нажмите кнопку Сортировка от А до Я или Сортировка от Я до А.
- Вставка внешних подытогов. Вставка внешних подытогов
- На вкладке Данные в группе Структуру нажмите кнопку Подытая.Отобразилось диалоговое окно «Подытая».
- В поле При каждом изменении выберите столбец для внешних подытогов. Например, в примере выше нужно щелкнуть Регион.
- В поле Использовать функцию выберите суммарную функцию, которую вы хотите использовать для вычисления итогов. Например, в примере выше нужно выбрать Sum.
- В поле Добавить подытожную сумму в выберите каждый столбец со значениями, которые вы хотите выложить. В примере выше нужно выбрать Продажи.
- Если вы хотите, чтобы после каждого из подытов был автоматический разрыв страницы, выберите разрыв страницы между группами.
- Чтобы указать итоговую строку над строкой с подробными данными, разо убедитесь в том, что под данными не указаны данные. Чтобы указать итоговую строку под строкой сведений, выберите поле Итоги под данными. Например, в примере выше этот квадрат можно было бы очистить.
- При желании вы можете использовать команду Итоги еще раз, повторив шаги 1–6, чтобы добавить дополнительные итоги с различными суммарными функциями. Чтобы не переописывать существующие подытоговые, разместим поле Заменить текущие.
- На вкладке Данные в группе Структуру нажмите кнопку Подытая.Отобразилось диалоговое окно «Подытая».
- В поле При каждом изменении в щелкните вложенный столбец подытая. Например, в примере выше нужно выбрать Sport.
- В поле Использовать функцию выберите суммарную функцию, которую вы хотите использовать для вычисления итогов. Например, в примере выше нужно выбрать Sum. Выберите другие нужные параметры.
- Чтобы заменить текущие подытки, сберем.
Совет: Чтобы отобразить сводку только итогов и общих итогов, щелкните символы рядом с номерами строк. Используйте символы и , чтобы отобразить или скрыть строки с подробными данными для отдельных подытов.
Промежуточные итоги в Excel с примерами функций
Подвести промежуточные итоги в таблице Excel можно с помощью встроенных формул и соответствующей команды в группе «Структура» на вкладке «Данные».
Важное условие применения средств – значения организованы в виде списка или базы данных, одинаковые записи находятся в одной группе. При создании сводного отчета промежуточные итоги формируются автоматически.
Вычисление промежуточных итогов в Excel
Чтобы продемонстрировать расчет промежуточных итогов в Excel возьмем небольшой пример. Предположим, у пользователя есть список с продажами определенных товаров:
Необходимо подсчитать выручку от реализации отдельных групп товаров. Если использовать фильтр, то можно получить однотипные записи по заданному критерию отбора. Но значения придется подсчитывать вручную. Поэтому воспользуемся другим инструментом Microsoft Excel – командой «Промежуточные итоги».
Чтобы функция выдала правильный результат, проверьте диапазон на соответствие следующим условиям:
- Таблица оформлена в виде простого списка или базы данных.
- Первая строка – названия столбцов.
- В столбцах содержатся однотипные значения.
- В таблице нет пустых строк или столбцов.
- Отсортируем диапазон по значению первого столбца – однотипные данные должны оказаться рядом.
- Выделяем любую ячейку в таблице. Выбираем на ленте вкладку «Данные». Группа «Структура» — команда «Промежуточные итоги».
- Заполняем диалоговое окно «Промежуточные итоги». В поле «При каждом изменении в» выбираем условие для отбора данных (в примере – «Значение»). В поле «Операция» назначаем функцию («Сумма»). В поле «Добавить по» следует пометить столбцы, к значениям которых применится функция.
- Закрываем диалоговое окно, нажав кнопку ОК. Исходная таблица приобретает следующий вид:
Если свернуть строки в подгруппах (нажать на «минусы» слева от номеров строк), то получим таблицу только из промежуточных итогов:
При каждом изменении столбца «Название» пересчитывается промежуточный итог в столбце «Продажи».
Чтобы за каждым промежуточным итогом следовал разрыв страницы, в диалоговом окне поставьте галочку «Конец страницы между группами».
Чтобы промежуточные данные отображались НАД группой, снимите условие «Итоги под данными».
Команда промежуточные итоги позволяет использовать одновременно несколько статистических функций. Мы уже назначили операцию «Сумма». Добавим средние значения продаж по каждой группе товаров.
Снова вызываем меню «Промежуточные итоги». Снимаем галочку «Заменить текущие». В поле «Операция» выбираем «Среднее».
Формула «Промежуточные итоги» в Excel: примеры
Функция «ПРОМЕЖУТОЧНЫЕ.ИТОГИ» возвращает промежуточный итог в список или базу данных. Синтаксис: номер функции, ссылка 1; ссылка 2;… .
Номер функции – число от 1 до 11, которое указывает статистическую функцию для расчета промежуточных итогов:
- – СРЗНАЧ (среднее арифметическое);
- – СЧЕТ (количество ячеек);
- – СЧЕТЗ (количество непустых ячеек);
- – МАКС (максимальное значение в диапазоне);
- – МИН (минимальное значение);
- – ПРОИЗВЕД (произведение чисел);
- – СТАНДОТКЛОН (стандартное отклонение по выборке);
- – СТАНДОТКЛОНП (стандартное отклонение по генеральной совокупности);
- – СУММ;
- – ДИСП (дисперсия по выборке);
- – ДИСПР (дисперсия по генеральной совокупности).
Ссылка 1 – обязательный аргумент, указывающий на именованный диапазон для нахождения промежуточных итогов.
Особенности «работы» функции:
- выдает результат по явным и скрытым строкам;
- исключает строки, не включенные в фильтр;
- считает только в столбцах, для строк не подходит.
Рассмотрим на примере использование функции:
- Создаем дополнительную строку для отображения промежуточных итогов. Например, «сумма отобранных значений».
- Включим фильтр. Оставим в таблице только данные по значению «Обеденная группа «Амадис»».
- В ячейку В2 введем формулу: .
Формула для среднего значения промежуточного итога диапазона (для прихожей «Ретро»): .
Формула для максимального значения (для спален): .
Промежуточные итоги в сводной таблице Excel
В сводной таблице можно показывать или прятать промежуточные итоги для строк и столбцов.
- При формировании сводного отчета уже заложена автоматическая функция суммирования для расчета итогов.
- Чтобы применить другую функцию, в разделе «Работа со сводными таблицами» на вкладке «Параметры» находим группу «Активное поле». Курсор должен стоять в ячейке того столбца, к значениям которого будет применяться функция. Нажимаем кнопку «Параметры поля». В открывшемся меню выбираем «другие». Назначаем нужную функцию для промежуточных итогов.
- Для выведения на экран итогов по отдельным значениям используйте кнопку фильтра в правом углу названия столбца.
В меню «Параметры сводной таблицы» («Параметры» — «Сводная таблица») доступна вкладка «Итоги и фильтры».
Таким образом, для отображения промежуточных итогов в списках Excel применяется три способа: команда группы «Структура», встроенная функция и сводная таблица.
- Excel Formula Examples
- Создать таблицу
- Форматирование
- Функции Excel
- Формулы и диапазоны
- Фильтр и сортировка
- Диаграммы и графики
- Сводные таблицы
- Печать документов
- Базы данных и XML
- Возможности Excel
- Настройки параметры
- Уроки Excel
- Макросы VBA
- Скачать примеры
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше
В этой статье описаны синтаксис формулы и использование функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ в Microsoft Excel.
Описание
Возвращает промежуточный итог в список или базу данных. Обычно проще создать список с промежуточными итогами, используя в настольном приложении Excel команду Промежуточные итоги в группе Структура на вкладке Данные. Но если такой список уже создан, его можно модифицировать, изменив формулу с функцией ПРОМЕЖУТОЧНЫЕ.ИТОГИ.
Синтаксис
Аргументы функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ описаны ниже.
- Номер_функции — обязательный аргумент. Число от 1 до 11 или от 101 до 111, которое обозначает функцию, используемую для расчета промежуточных итогов. Функции с 1 по 11 учитывают строки, скрытые вручную, в то время как функции с 101 по 111 пропускают такие строки; отфильтрованные ячейки всегда исключаются.
Function_num
(включая скрытые строки)Function_num
(игнорирует скрытые строки)- Ссылка1 Обязательный. Первый именованный диапазон или ссылка, для которых требуется вычислить промежуточные итоги.
- Ссылка2;. Необязательный. Именованные диапазоны или ссылки 2—254, для которых требуется вычислить промежуточные итоги.
Примечания
- Если уже имеются формулы подведения итогов внутри аргументов «ссылка1;ссылка2;. » (вложенные итоги), эти вложенные итоги игнорируются, чтобы избежать двойного суммирования.
- Для констант «номер_функции» от 1 до 11 функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ учитывает значения строк, скрытых с помощью команды Скрыть строки (меню Формат, подменю Скрыть или отобразить) в группе Ячейки на вкладке Главная в настольном приложении Excel. Эти константы используются для получения промежуточных итогов с учетом скрытых и нескрытых чисел списка. Для констант «номер_функции» от 101 до 111 функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ исключает значения строк, скрытых с помощью команды Скрыть строки. Эти константы используются для получения промежуточных итогов с учетом только нескрытых чисел списка.
- Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ исключает все строки, не включенные в результат фильтра, независимо от используемого значения константы «номер_функции».
- Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ применяется к столбцам данных или вертикальным наборам данных. Она не предназначена для строк данных или горизонтальных наборов данных. Так, при определении промежуточных итогов горизонтального набора данных с помощью значения константы «номер_функции» от 101 и выше (например, ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;B2:G2)), скрытие столбца не повлияет на результат. Однако на него повлияет скрытие строки при подведении промежуточного итога для вертикального набора данных.
- Если среди ссылок есть трехмерные ссылки, функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ возвращает значение ошибки #ЗНАЧ!.
Пример
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу Enter. При необходимости измените ширину столбцов, чтобы видеть все данные.
Значение промежуточного итога диапазона ячеек A2:A5, полученное с использованием числа 9 в качестве первого аргумента.
Среднее значение промежуточного итога диапазона ячеек A2:A5, полученное с использованием числа 1 в качестве первого аргумента.
В качестве первого аргумента функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ необходимо использовать числовое значение (1–11, 101–111). Этот числовой аргумент используется для промежуточного итога значений (диапазонов ячеек, именованных диапазонов), указанных в качестве следующих аргументов.
Формула промежуточные итоги в excel как сделать
Вопросы по покупке sales@onlyoffice.com
Запросы на партнерство partners@onlyoffice.com
Запросы от прессы press@onlyoffice.com
Следите за нашими новостями:
© Ascensio System SIA 2023. Все права защищены
© Ascensio System SIA 2023. Все права защищены
Не пропустите обновление!
Получайте последние новости ONLYOFFICE на ваш email
Имя не указано.
Email не указан.
На ваш адрес электронной почты отправлено сообщение с подтверждением.
В Справочном центре ONLYOFFICE используются файлы cookie для обеспечения максимального удобства работы пользователей. Продолжая использовать этот сайт, вы соглашаетесь с тем, что мы можем сохранять файлы cookie в вашем браузере.