Итоговая строка — это дополнительная строка в таблице данных, которая позволяет обрабатывать числовые данные в таблице с помощью агрегатной функции. Расположен внизу таблицы.
Как добавить строку итогов в таблицу?
1 способ
- В открывшемся окне листа выберите любую ячейку в таблице.
- Перейдите на вкладку «Конструктор» и в группе «Параметры стиля таблицы» активируйте значение «Строка итогов» (Рис. 5.25).
Рис. 5.25. Вкладка «Дизайн». Значение «итого ряда»
2 способ
- 1. В открытом окне листа щелкните правой кнопкой мыши любую ячейку таблицы.
- В контекстном меню наведите курсор на пункт «Таблица».
- В списке команд выберите пункт «Строка итогов» (рис. 5.26).
Рис. 5.26. Контекстное меню таблицы данных. Пункт меню «Таблица». Элемент «Итоговая строка»
Отключение строки итогов может привести к ошибкам в формулах, которые ссылаются на эту строку.
Подвести промежуточные итоги в таблицу Excel можно с помощью встроенных формул и соответствующей команды в группе «Структура» на вкладке «Данные».
Важным условием использования инструментов является то, что значения организованы в виде списка или базы данных, одинаковые записи находятся в одной группе. Когда вы создаете сводный отчет, промежуточные итоги генерируются автоматически.
Вычисление промежуточных итогов в Excel
Чтобы продемонстрировать вычисление промежуточных итогов в Excel, давайте возьмем небольшой пример. Допустим, у пользователя есть список с продажами определенных товаров:
Необходимо рассчитать доход от реализации отдельных групп товаров. Используя фильтр, вы можете получить записи одного типа на основе определенных критериев отбора. Но значения придется рассчитывать вручную. Поэтому мы будем использовать другой инструмент Microsoft Excel: команду «Промежуточные итоги».
Чтобы функция возвращала правильный результат, проверьте диапазон на следующие условия:
- Таблица выполнена в виде простого списка или базы данных.
- Первая строка — это имена столбцов.
- Столбцы содержат значения одного типа.
- В таблице нет пустых строк или столбцов.
Начнем…
- Отсортируем диапазон по значению первого столбца: данные одного типа должны быть близки.
- Выберите любую ячейку в таблице. Выберите вкладку Данные на ленте. Группа «Структура» — команда «Промежуточные итоги».
- Заполните диалоговое окно «Промежуточные итоги». В поле «При каждом изменении в» выберите условие для выбора данных (в примере «Значение»). В поле «Операция» назначьте функцию («Сумма»). В поле «Добавить по» нужно отметить столбцы, к значениям которых будет применяться функция.
- Закройте диалоговое окно, нажав кнопку ОК. Исходная таблица имеет следующий вид:
Если свернуть строки на подгруппы (нажмите на «минус» слева от номеров строк), то получим таблицу только промежуточных итогов:
При каждом изменении столбца «Имя» промежуточный итог для столбца «Продажи» пересчитывается».
Чтобы после каждого промежуточного итога следовал разрыв страницы, в диалоговом окне установите флажок «Конец страницы между группами».
Для отображения промежуточных данных НАД группой снимите флажок «Итоги ниже данных».
Команда промежуточного итога позволяет использовать несколько статистических функций одновременно. Мы уже назначили операцию Sum. Добавим средние значения продаж для каждой группы товаров.
Снова откройте меню «Промежуточные итоги». Снимите флажок «Заменить текущий». В поле «Операция» выберите «Средняя».
Формула «Промежуточные итоги» в Excel: примеры
Функция SUBTOTAL возвращает промежуточный итог в список или базу данных. Синтаксис: номер функции, ссылка 1; ссылка 2;… .
Номер функции: число от 1 до 11, обозначающее статистическую функцию для расчета промежуточных итогов:
- – СРЗНАЧ (среднее арифметическое);
- – COUNT (количество ячеек);
- – COUNT (количество непустых ячеек);
- – MAX (максимальное значение в диапазоне);
- – МИН (минимальное значение);
- – ПРОИЗВЕДЕНИЕ (произведение чисел);
- – СТАНДОТКЛОН (стандартное отклонение выборки);
- – STDEV (стандартное отклонение для генеральной совокупности);
- — СУММА;
- – VASP (выборочная дисперсия);
- – ВАСП (дисперсия населения).
Ссылка 1 — обязательный аргумент, указывающий на именованный диапазон для поиска промежуточных итогов.
Характеристики «работы» функции:
- возвращает результат для явных и скрытых строк;
- исключать строки, не вошедшие в фильтр;
- считает только в столбцах, не подходит для строк.
Давайте используем функцию в качестве примера:
- Создаем дополнительную строку для отображения промежуточных итогов. Например, «сумма выбранных значений».
- Включим фильтр. Оставим в таблице только данные о стоимости «Обеденная группа «Амадис»».
- В ячейку B2 введите формулу: .
Формула среднего значения промежуточного итога ассортимента (для прохода «Ретро»): .
Формула максимального значения (для спален): .
Промежуточные итоги в сводной таблице Excel
В сводной таблице можно показать или скрыть промежуточные итоги для строк и столбцов.
- При создании сводного отчета уже встроена функция автоматического суммирования для подсчета итогов.
- Чтобы применить другую функцию, в разделе «Работа со сводными таблицами» на вкладке «Параметры» находим группу «Активное поле». Курсор должен находиться в ячейке столбца, к которому будет применяться функция. Нажмите кнопку «Параметры поля». В открывшемся меню выберите «другое». Назначьте желаемую функцию для промежуточных итогов.
- Чтобы отобразить итоги для отдельных значений, используйте кнопку фильтра в правом углу имени столбца.
В меню «Параметры сводной таблицы» («Параметры» — «Сводная таблица») доступна вкладка «Итоги и фильтры».
Скачать примеры с промежуточными итогами
Поэтому для вывода промежуточных итогов в списках Excel используются три метода: групповая команда «Структура», встроенная функция и сводная таблица.
Листы Excel, содержащие много информации, иногда могут выглядеть беспорядочно и даже трудно читать. Excel позволяет группировать данные, чтобы легко скрывать и отображать различные разделы электронной таблицы. Кроме того, вы можете суммировать различные группы с помощью команды «Промежуточный итог» и структурировать электронную таблицу Excel. В этом уроке мы рассмотрим эти два инструмента максимально подробно и с наглядными примерами.
Группировка строк и столбцов в Excel
- Выберите строки или столбцы, которые вы хотите сгруппировать. В следующем примере мы выделим столбцы A, B и C.
- Щелкните вкладку «Данные» на ленте, затем щелкните «Группа.
- Выбранные строки или столбцы будут сгруппированы. В нашем примере это столбцы A, B и C.
Чтобы разгруппировать данные в Excel, выберите сгруппированные строки или столбцы, а затем нажмите команду «Разгруппировать.
Как скрыть и показать группы
- Чтобы скрыть группу в Excel, щелкните значок Скрыть сведения (минус).
- Группа будет скрыта. Чтобы отобразить скрытую группу, щелкните значок Показать сведения (плюс).
Подведение итогов в Excel
Команда «Промежуточный итог» позволяет автоматически создавать группы и использовать основные функции, такие как СУММ, СЧЕТ и СРЗНАЧ, чтобы упростить добавление. Например, команда «Промежуточный итог» может рассчитать стоимость канцелярских товаров по группам при крупном заказе. Команда создаст иерархию групп, также называемую структурой, для организации информации на листе.
Ваши данные должны быть правильно отсортированы перед использованием команды «Промежуточный итог». Для получения дополнительной информации вы можете обратиться к серии уроков «Сортировка данных в Excel.
Создание промежуточного итога
В следующем примере мы будем использовать команду Subtotal, чтобы определить, сколько заказано рубашек каждого размера (S, M, L и XL). В результате таблица Excel будет структурирована по группам для каждого размера футболки, а затем будет рассчитано общее количество футболок в каждой группе.
- Сначала отсортируйте данные, которые вы хотите обобщить. В этом примере мы вычисляем промежуточный итог для каждого размера футболки, поэтому информация на листе Excel должна быть отсортирована по столбцу «Размер» от наименьшего к наибольшему.
- Перейдите на вкладку «Данные», затем нажмите «Промежуточный итог.
- Откроется диалоговое окно Промежуточные итоги. В раскрывающемся списке для поля При каждом изменении выберите столбец, который вы хотите суммировать. В нашем случае это столбец Размер.
- Нажмите кнопку со стрелкой в поле «Операция», чтобы выбрать тип используемой функции. Мы выберем Количество, чтобы подсчитать количество заказанных футболок каждого размера.
- В поле Добавить итоги по выберите столбец, в котором вы хотите отобразить итог. В нашем примере это столбец «Размер.
- Если все параметры верны, нажмите ОК.
- Информация на листе будет сгруппирована, и промежуточные итоги появятся под каждой группой. В нашем случае данные сгруппированы по размеру рубашки, а под соответствующей группой указано количество заказанных рубашек каждого размера.
Просмотр групп по уровням
При суммировании промежуточных итогов в Excel рабочий лист делится на разные уровни. Вы можете переключаться между этими уровнями, чтобы регулировать количество отображаемой информации, используя значки контура 1, 2, 3 в левой части листа. В следующем примере мы будем переключаться между тремя уровнями структуры.
Хотя в этом примере показаны только три уровня, Excel позволяет создать до 8 уровней вложенности.
- Нажмите на самый нижний уровень, чтобы отобразить минимальное количество информации. Мы выберем уровень 1, который содержит только общее количество заказанных футболок.
- Нажмите на следующий уровень, чтобы отобразить более подробную информацию. В нашем примере мы выберем уровень 2, который содержит все строки с итогами, но скрывает остальные данные на листе.
- Щелкните верхний уровень, чтобы развернуть все данные на листе. В нашем случае это уровень 3.
Вы также можете использовать значки Показать или Скрыть сведения, чтобы скрыть или показать группы.
Удаление промежуточных итогов в Excel
Со временем потребность в промежуточных итогах отпадает, особенно когда нужно по-другому перегруппировать данные на листе Excel. Если вы больше не хотите видеть промежуточные итоги, вы можете удалить их.
- Перейдите на вкладку «Данные», затем нажмите «Промежуточный итог.
- Откроется диалоговое окно Промежуточные итоги. Щелкните Удалить все.
- Все данные будут разгруппированы, а итоги удалены.
Чтобы удалить только группы, оставив промежуточные итоги, используйте пункт «Удалить структуру» в раскрывающемся меню команды «Разгруппировать.