Данные итогов в таблице Excel
Вы можете быстро подвести итоги в таблице Excel, включив строку итогов и выбрав одну из функций в раскрывающемся списке для каждого столбца. По умолчанию в строке итогов применяется функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ, которая позволяет включать или пропускать скрытые строки таблицы. Но вы также можете использовать другие функции.
- Щелкните любое место таблицы.
- Выберите Работа с таблицами >Конструктор и установите флажок Строка итогов.
- Строка итогов будет вставлена в нижней части таблицы.
Примечание: Если применить в строке итогов формулы, а затем отключить ее, формулы будут сохранены. В приведенном выше примере мы применили функцию СУММ для строки итогов. При первом использовании строки итогов ячейки будут пустыми.
Выделите нужный столбец, а затем выберите вариант из раскрывающегося списка. В этом случае мы применили функцию СУММ к каждому столбцу: Excel создает следующую формулу: =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;[Midwest]). Это функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ для функции СУММ, которая является формулой со структурированными ссылками (такие формулы доступны только в таблицах Excel). См. статью Использование структурированных ссылок в таблицах Excel. К итоговому значению можно применить и другие функции, щелкнув Другие функции или создав их самостоятельно.
Примечание: Если вы хотите скопировать формулу в смежную ячейку строки итогов, перетащите ее вбок с помощью маркера заполнения. При этом ссылки на столбцы обновятся, и будет выведено правильное значение. Не используйте копирование и вставку, так как при этом ссылки на столбцы не обновятся, что приведет к неверным результатам.
Вы можете быстро подвести итоги в таблице Excel, включив строку итогов и выбрав одну из функций в раскрывающемся списке для каждого столбца. По умолчанию в строке итогов применяется функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ, которая позволяет включать или пропускать скрытые строки таблицы. Но вы также можете использовать другие функции.
- Щелкните любое место таблицы.
- Выберите Таблица >Строка итогов.
- Строка итогов будет вставлена в нижней части таблицы.
Примечание: Если применить в строке итогов формулы, а затем отключить ее, формулы будут сохранены. В приведенном выше примере мы применили функцию СУММ для строки итогов. При первом использовании строки итогов ячейки будут пустыми.
Выделите нужный столбец, а затем выберите вариант из раскрывающегося списка. В этом случае мы применили функцию СУММ к каждому столбцу: Excel создает следующую формулу: =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;[Midwest]). Это функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ для функции СУММ, которая является формулой со структурированными ссылками (такие формулы доступны только в таблицах Excel). См. статью Использование структурированных ссылок в таблицах Excel. К итоговому значению можно применить и другие функции, щелкнув Другие функции или создав их самостоятельно.
Примечание: Если вы хотите скопировать формулу в смежную ячейку строки итогов, перетащите ее вбок с помощью маркера заполнения. При этом ссылки на столбцы обновятся, и будет выведено правильное значение. Не используйте копирование и вставку, так как при этом ссылки на столбцы не обновятся, что приведет к неверным результатам.
Вы можете быстро подвести итоги в таблице Excel, включив параметр Переключить строку итогов.
- Щелкните любое место таблицы.
- Щелкните вкладку Конструктор таблиц >Параметры стилей >Строка итогов. Строка Итог будет вставлена в нижней части таблицы.
Настройка агрегатной функции для ячейки строки итогов
Примечание: Это одна из нескольких бета-функций, и в настоящее время она доступна только для части инсайдеров Office. Мы будем оптимизировать такие функции в течение следующих нескольких месяцев. Когда они будут готовы, мы сделаем их доступными для всех участников программы предварительной оценки Office и подписчиков Microsoft 365.
Строка итогов позволяет выбрать агрегатную функцию, используемую для каждого столбца.
- Щелкните ячейку в строке итогов под столбцом, который нужно настроить, а затем выберите раскрывающийся список, отображаемый рядом с ячейкой.
- Выберите агрегатную функцию, используемую для столбца. Обратите внимание, что вы можете щелкнуть Другие функции, чтобы просмотреть дополнительные параметры.
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.
Вставка промежуточных итогов в списке данных на листе
Важно: В Excel не поддерживаются Excel. Если вы работаете с Excel таблицей, команда «Excel», будет неавтоматна. Чтобы добавить в таблицу подытую, необходимо сначала преобразовать таблицу в обычный диапазон данных, а затем добавить подытую. Обратите внимание, что в этом случае будут исключены все функции таблицы, кроме форматирования таблицы. Вы также можете создать сетную.
При вставке подытов:
- Промежуточные итоги вычисляются с помощью итоговая функция, например Sum или Average,с помощью функции SUBTOTAL. Для каждого столбца можно отобразить несколько типов итогов.
- Общие итоги получаются из детали, а не из значений в сумме. Например, если вы используете суммарную функцию Среднее, в строке итогов отображается среднее значение всех строк с подробными данными в списке, а не среднее значение в строках итогов.
Если в книге настроено автоматическое пересчет формул, при редактировании подробных данных команда Итоги автоматически пересчитывает итоговые и общие значения. Команда «Подытая» также определяет структуру списка, чтобы можно было отобразить и скрыть строки с подробными подробностями для каждого из них.
Вставка подытов
Примечание: При фильтрации данных, которые содержат подытог, они могут отображаться скрытыми. Чтобы снова отобразить их, очистка всех фильтров Дополнительные сведения о применении фильтров см. в кратком запуске. Фильтрация данных с помощью автофильтра.
- Убедитесь, что каждый столбец в диапазон данных, для которого вычисляются подытогов, имеет метку в первой строке, содержит похожие факты в каждом столбце и что в диапазоне нет пустых строк или столбцов.
- Выделите ячейку в диапазоне.
- Выполните одно из указанных ниже действий. Вставка одного уровня подытогов Вы можете вставить один уровень подытогов для группы данных, как показано в примере ниже. 1. При каждом изменении в столбце Спорт. 2. . подытожите столбец «Продажи».
- Для сортировки столбца, содержащего данные, по которым требуется выполнить группировку, выделите этот столбец и на вкладке Данные в группе Сортировка и фильтр нажмите кнопку Сортировка от А до Я или Сортировка от Я до А.
- На вкладке Данные в группе Структуру нажмите кнопку Подытая.Отобразилось диалоговое окно «Подытая».
- В поле При каждом изменении выберите столбец для подытверха. Например, в примере выше нужно выбрать Sport.
- В поле Использовать функцию выберите суммарную функцию, которую вы хотите использовать для вычисления итогов. Например, в примере выше нужно выбрать Sum.
- В поле Добавить подытожную сумму в выберите каждый столбец со значениями, которые вы хотите выложить. Например, в примере выше нужно выбрать Продажи.
- Если вы хотите, чтобы после каждого из подытов был автоматический разрыв страницы, выберите разрыв страницы между группами.
- Чтобы указать итоговую строку над строкой с подробными данными, разо убедитесь в том, что под данными не указаны данные. Чтобы указать итоговую строку под строкой сведений, выберите поле Итоги под данными. Например, в примере выше этот квадрат можно было бы очистить.
- При желании вы можете использовать команду Итоги еще раз, повторив шаги с 1 по 7, чтобы добавить дополнительные итоги с различными суммарными функциями. Чтобы не переописывать существующие подытоговые, разместим поле Заменить текущие.
Вставка вложенных уровней подытогов
Вы можете вставить подытогов для внутренних вложенных групп в соответствующие внешние группы, как показано в примере ниже.
1. При каждом изменении внешнего столбца Регион.
2. . вы можете вычитать для каждого изменения внутренний столбец Sport и столбец Sales для этого региона.
- Для сортировки столбца, содержащего данные, по которым требуется выполнить группировку, выделите этот столбец и на вкладке Данные в группе Сортировка и фильтр нажмите кнопку Сортировка от А до Я или Сортировка от Я до А.
- Вставка внешних подытогов. Вставка внешних подытогов
- На вкладке Данные в группе Структуру нажмите кнопку Подытая.Отобразилось диалоговое окно «Подытая».
- В поле При каждом изменении выберите столбец для внешних подытогов. Например, в примере выше нужно щелкнуть Регион.
- В поле Использовать функцию выберите суммарную функцию, которую вы хотите использовать для вычисления итогов. Например, в примере выше нужно выбрать Sum.
- В поле Добавить подытожную сумму в выберите каждый столбец со значениями, которые вы хотите выложить. В примере выше нужно выбрать Продажи.
- Если вы хотите, чтобы после каждого из подытов был автоматический разрыв страницы, выберите разрыв страницы между группами.
- Чтобы указать итоговую строку над строкой с подробными данными, разо убедитесь в том, что под данными не указаны данные. Чтобы указать итоговую строку под строкой сведений, выберите поле Итоги под данными. Например, в примере выше этот квадрат можно было бы очистить.
- При желании вы можете использовать команду Итоги еще раз, повторив шаги 1–6, чтобы добавить дополнительные итоги с различными суммарными функциями. Чтобы не переописывать существующие подытоговые, разместим поле Заменить текущие.
- На вкладке Данные в группе Структуру нажмите кнопку Подытая.Отобразилось диалоговое окно «Подытая».
- В поле При каждом изменении в щелкните вложенный столбец подытая. Например, в примере выше нужно выбрать Sport.
- В поле Использовать функцию выберите суммарную функцию, которую вы хотите использовать для вычисления итогов. Например, в примере выше нужно выбрать Sum. Выберите другие нужные параметры.
- Чтобы заменить текущие подытки, сберем.
Совет: Чтобы отобразить сводку только итогов и общих итогов, щелкните символы рядом с номерами строк. Используйте символы и , чтобы отобразить или скрыть строки с подробными данными для отдельных подытов.
Нарастающий итог в Excel
Начнём, для разогрева, с наиболее простого варианта — формул. Если мы имеем на входе небольшую отсортированную по дате таблицу, то для расчёта нарастающего итога в отдельном столбце нам потребуется элементарная формула:
Основная фишка тут в хитром закреплении диапазона внутри функции СУММ — ссылка на начало диапазона делается абсолютной (со знаками доллара), а на конец — относительной (без долларов). Соответственно, при копировании формулы вниз на весь столбец мы получаем растягивающийся диапазон, сумму которого и считаем. Минусы такого подхода очевидны:- Таблица обязательно должна быть отсортирована по дате.
- При добавлении новых строк с данными формулу придётся допротягивать вручную.
Способ 2. Сводная таблица
Этот способ чуть сложнее, но гораздо приятнее. Причём для усугубления давайте рассмотрим задачку посерьезнее — таблицу из 2000 строк с данными, где по столбцу даты нет сортировки, зато есть повторы (т.е. мы можем несколько раз продать в один и тот же день):
Конвертируем нашу исходную таблицу в «умную» (динамическую) сочетанием клавиш Ctrl + T или командой Главная — Форматировать как таблицу (Home — Format as Table) , а затем строим по ней сводную таблицу командой Вставка — Сводная таблица (Insert — Pivot Table) . В область строк в сводной закидываем дату, а в область значений — число проданных товаров:
Обратите внимание, что если у вас не совсем старая версия Excel, то даты автоматически сгруппируются по годам, кварталам и месяцам. Если вам нужна другая группировка (или она совсем не нужна), то это можно исправить, щёлкнув правой кнопкой мыши по любой дате и выбрав команды Группировать / Разгруппировать (Group / Ungroup) .
Если хочется видеть одновременно и получившиеся итоги по периодам и нарастающий итог в отдельном столбце, то имеет смысл закинуть в область значений поле Продано ещё раз, чтобы получить дубликат поля — в нём мы и включим отображение нарастающих итогов. Для этого щёлкните правой кнопкой мыши по полю и выберите команду Дополнительные вычисления — Нарастающий итог (Show Values as — Running Totals) :
Там же можно выбрать и вариант нарастающих итогов в процентах, а в следующем окне нужно выбрать поле, по которому пойдет накопление — в нашем случае это поле даты:
Плюсы такого подхода:
- Быстро считается большой объем данных.
- Не нужно вводить вручную никаких формул.
- При изменении в исходных данных достаточно обновить сводную правой кнопкой мыши или командой Данные — Обновить всё (Data — Refresh All).
Способ 3. Запрос Power Query
Загрузим нашу «умную» таблицу с исходными данными в редактор запросов Power Query, используя команду Данные — Из таблицы / диапазона (Data — From Table/Range) . В последних версиях Excel её, кстати, переименовали — теперь она называется С листа (From Sheet) :
Затем выполним следующие шаги:
1 . Отсортируем таблицу по возрастанию по столбцу даты командой Сортировать по возрастанию в выпадающем списке фильтра в шапке таблицы.
2 . Чуть позже для подсчета нарастающего итога нам потребуется вспомогательный столбец с порядковым номером строки. Добавим его командой Добавление столбца — Столбец индекса — От 1 (Add column — Index column — From 1) .
3 . Также для подсчёта нарастающего итога нам потребуется ссылка на столбец Продано, где лежат наши суммируемые данные. В Power Query столбцы ещё называются списками (list) и чтобы получить ссылку на него, щёлкнем правой кнопкой мыши по заголовку столбца и выберем команду Детализация (Show detail) . В строке формул появится нужное нам выражение, состоящее из имени предыдущего шага #»Добавлен индекс», откуда мы берём таблицу и имени столбца [Продано] из этой таблицы в квадратных скобках:
Копируем это выражение в буфер для дальнейшего использования.
4 . Удаляем ненужный более последний шаг Продано и добавляем вместо него вычисляемый столбец для подсчета нарастающего итога командой Добавление столбца — Настраиваемый столбец (Add column — Custom column) . Нужная нам формула будет выглядеть так:
Здесь функция List.Range берёт исходный список (столбец [Продано]) и извлекает из него элементы, начиная с первого (в формуле это 0, т.к. нумерация в Power Query начинается с нуля). Количество извлекаемых элементов — это номер строки, который мы берём из столбца [Индекс]. Таким образом, эта функция для первой строки возвращает только одну первую ячейку столбца Продано. Для второй строки — уже первых две ячейки, для третьей — первых три и т.д.
Ну, а затем функция List.Sum суммирует извлечённые значения и мы получаем в каждой строке сумму всех предыдущих элементов, т.е. нарастающий итог:
Осталось удалить ненужный нам больше столбец Индекс и выгрузить результаты обратно в Excel командой Главная — Закрыть и загрузить (Home — Close&Load to).
Форсаж
В принципе, на этом можно было бы и остановиться, но есть небольшая ложка дёгтя — созданный нами запрос работает со скоростью черепахи. Для примера, на моем не самом слабом ПК таблица всего в 2000 строк обрабатывается 17 секунд. А если данных будет больше?
Для ускорения можно использовать буферизацию при помощи специальной функции List.Buffer, которая загружает заданный ей в качестве аргумента список (list) в оперативную память, что сильно ускоряет обращение к нему в дальнейшем. В нашем случае имеет смысл буферизовать список #»Добавлен индекс»[Продано], к которому Power Query вынужден обращаться при расчёте нарастающего итога в каждой строке нашей 2000-строчной таблицы.
Для этого в редакторе Power Query на Главной вкладке жмём кнопку Расширенный редактор (Home — Advanced Editor), чтобы открыть исходный код нашего запроса на встроенном в Power Query языке М:
А затем добавляем туда строку с переменной MyList, значение которой возвращает функция буферизации и подменяем на следующем шаге обращение к списку на эту переменную:
После внесения таких изменений наш запрос станет существенно резвее и справится с 2000-строчной таблицей всего за 0.3 секунды!
Другое дело, правда? 🙂
Ссылки по теме
- Диаграмма Парето (80/20) и как построить её в Excel
- Поиск ключевых слов в тексте и буферизация запроса в Power Query
Как сделать итого в excel
Допустим у нас есть некая таблица. Мы «нарисовали» ее сами или получили в виде отчета из учетной системы. В ней все хорошо только нет итогов. Придется вводить формулы и потом их копировать. В зависимости от размеров таблицы этот процесс может занять длительное время. В этой статье я расскажу, как это можно сделать очень быстро.
Итак, приступим. Есть у нас вот такая таблица:
Мы выделяем все числовые данные плюс одну строку снизу и один столбец справа, чтобы получилось так:
Переходим во вкладку «Главная» и в разделе «Редактирование» нажимаем кнопку «Автосумма«:
И MS Excel все сделал за нас:
Нам осталось только написать недостающие названия строки и столбца.
А если наша таблица выглядит чуть сложнее и нам недостаточно посчитать общие итоги? Давайте разберем такую таблицу:
Помимо общих итогов по столбцам и строкам, нам необходимо получить промежуточные итоги по менеджерам, чтобы было так:
Давайте приступим к решению. Выделяем всю таблицу, переходим во вкладку меню «Данные» и в разделе «Структура» нажимаем кнопку «Промежуточный итог«:
В открывшемся диалоговом окне в списке «Добавить итоги по:» устанавливаем галочки напротив пунктов «Январь«, «Февраль«, «Март» и нажимаем кнопку «ОК«:
После чего наша таблица приобретет следующий вид:
Если вас «напрягают» плюсики и минусики, образовавшиеся слева, во вкладке «Данные» в разделе «Структура» нажмите кнопку «Разгруппировать«, в выпавшем списке нажмите пункт «Удалить структуру«:
Что делать дальше вы уже, наверное, догадались? Выделяем все числовые данные и один пустой столбец справа, во вкладке меню «Главная» в разделе «Редактирование» нажимаем «Автосумма«:
Таблица приобретет такой вид:
Форматируем необходимые строки и столбцы и получаем что хотели: