Итоги Excel

  • автор:

Подсчитаем промежуточные итоги в таблице MS EXCEL. Например, в таблице содержащей сведения о продажах нескольких различных категорий товаров подсчитаем стоимость каждой категории .

Имеем таблицу продаж товаров (товары повторяются). См. Файл примера .

Подсчитаем стоимость каждого товара с помощью средства MS EXCEL Промежуточные итоги ( Данные/ Структура/ Промежуточные итоги ).

Для этого необходимо:

  • убедиться, что названия столбцов имеют заголовки;
  • отсортировать данные по столбцу Товары, например с помощью Автофильтра ;
  • выделив любую ячейку в таблице, вызвать Промежуточные итоги (в меню Данные/ Структура ) ;

  • в поле » При каждом изменении в: » выбрать Товар ;
  • в поле » Операция » выбрать Сумма ;
  • в поле » Добавить итоги по » поставить галочку напротив значения Стоимость ;
  • Нажать ОК.

СОВЕТ : Подсчитать промежуточные итоги можно также с помощью Сводных таблиц и формул .

Как видно из рисунка выше, после применения инструмента Промежуточные итоги, MS EXCEL создал три уровня организации данных: слева от таблицы возникли элементы управления структурой. Уровень 1: Общий итог (стоимость всех товаров в таблице); Уровень 2: Стоимость товаров в каждой категории; Уровень 3: Все строки таблицы. Нажимая соответствующие кнопки можно представить таблицу в нужном уровне детализации. На рисунках ниже представлены уровни 1 и 2.

В таблицах в формате EXCEL 2007 Промежуточные итоги работать не будут. Нужно либо преобразовать таблицу в простой диапазон либо использовать Сводные таблицы .

Копируем только строки с промежуточными итогами

Скопировать только строки с промежуточными итогами в другой диапазон не так просто: если даже таблица сгруппирована на 2-м уровне (см. рисунок выше), то выделив ячейки с итогами (на самом деле выделится диапазон А4:D92) и скопировав его в другой диапазон мы получим всю таблицу. Чтобы скопировать только Итоги используем Расширенный фильтр (будем использовать тот факт, что MS EXCEL при создании структуры Промежуточные итоги вставляет строки итогов с добавлением слова Итог или в английской версии — Total ).

  • создайте в диапазоне D5:D6 табличку с критериями: в D5 поместите заголовок столбца, в котором содержатся слова Итог, т.е. слово Товар ; в D6 введите *Итог (будут отобраны все строки, у которых в столбце Товар содержится значения, заканчивающиеся на слово Итог ) Звездочка означает подстановочный знак *;

  • выделите любую ячейку таблицы;
  • вызовите Расширенный фильтр ( Данные/ Сортировка и фильтр/ Дополнительно );
  • в поле Диапазон условий введите D5:D6 ;
  • установите опцию Скопировать результат в другое место ;
  • в поле Поместить результат в диапазон укажите пустую ячейку, например А102 ;

  • нажмите ОК.

В результате получим табличку содержащую только строки с итогами.

СОВЕТ : Перед добавлением новых данных в таблицу лучше удалить Промежуточные итоги ( Данные/ Структура/ Промежуточные итоги кнопка Убрать все ).

Если требуется напечатать таблицу, так чтобы каждая категория товара располагалась на отдельном листе, используйте идеи из статьи .

Функция «Промежуточные итоги()» используется для вычисления промежуточного итога (сумма, среднее, количество значений и т.д.) в диапазоне, в котором имеются скрытые сроки.

Особенность функции состоит в том, что она предназначена для использования совместно с другими средствами Excel (например, автофильтрами).

Для демонстрации пользы от функции Excel Промежуточные.итоги() рассмотрим урезанную выгрузку из базы мероприятий.

Задача: по выбранным параметрам оценить ключевые показатели в сравнении с ситуацией по всем направлениям.

Мероприятия можно фильтровать по разным параметрам, но нам для сравнения хотелось бы понимать, показатели по отфильтрованным параметрам и сравнивать с ситуацией в целом по всем направлениям.

Для этого можно воспользоваться функцией Excel =Промежуточные.итоги(). Функция =Промежуточные.итоги() делает расчет по отфильтрованным значениям.

В формуле можно задать расчет:

  • Средней – номер 1 или 101 – для выборки
  • Счет значений – номер 3 или 103
  • Сумма — 9 или 109
  • И т.д.

  • Если номер функции трехзначный, например, 109, то функция работает как промежуточные итоги, т.е. рассчитывает значения по отфильтрованным параметрам.
  • Если номер однозначный или двузначный 2 или 11, то формула промежуточные.итоги() работает, как стандартная формула по выбранной функции.
Перейдем к нашим мероприятиям:

У нас есть выгрузка из базы по следующим столбцам:

  • Ситуация
  • Действия
  • Кол-во клиентов
  • Объём_Продаж_До
  • Объём_Продаж_План
  • Объём_Продаж_Факт
  • Затраты_План
  • Затраты_Факт

Скачайте файл с примером

Для оценки ситуации в целом введем стандартную формулу суммирования:

Для расчета промежуточных итогов по выбранным параметрам введем =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;RC:RC), где

  • 109 – функция суммы для расчета промежуточных итогов;
  • RC:RC – ссылка на диапазон суммирования.

Теперь, отфильтровав столбец «Ситуация» — «Ситуация 4» и «Действие» — Действие 2 и 3, мы получим перерасчет промежуточных итогов по каждому из показателей и можем их сравнить с показателями «Итого»:

В результате: видно, что % прироста продаж по отфильтрованным мероприятиям выше, чем по всем, причем планировали результаты получить ниже, чем по итого, а получили выше. Т.е. сработали лучше, чем планировали и лучше чем по всем мероприятиям.

Формула Excel =промежуточные.итоги() – отличный инструмент для расчета показателей по отфильтрованным параметрам.

Скачайте файл с примером

Если есть вопросы — обращайтесь!

Присоединяйтесь к нам!

Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа:

  • Novo Forecast Lite — автоматический расчет прогноза в Excel.
  • 4analytics — ABC-XYZ-анализ и анализ выбросов в Excel.
  • Qlik Sense Desktop и QlikView Personal Edition — BI-системы для анализа и визуализации данных.

Тестируйте возможности платных решений:

  • Novo Forecast PRO — прогнозирование в Excel для больших массивов данных.

Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.

Листы Excel, содержащие большой объем информации, иногда могут выглядеть перегруженными и даже трудночитаемыми. Excel позволяет группировать данные, чтобы с легкостью скрывать и показывать различные разделы листа. К тому же Вы можете обобщить различные группы при помощи команды Промежуточный итог и придать структуру рабочему листу Excel. В этом уроке мы рассмотрим оба этих инструмента максимально подробно и на наглядных примерах.

Группировка строк и столбцов в Excel

  1. Выделите строки или столбцы, которые необходимо сгруппировать. В следующем примере мы выделим столбцы A, B и C.
  2. Откройте вкладку Данные на Ленте, затем нажмите команду Группировать.
  3. Выделенные строки или столбцы будут сгруппированы. В нашем примере это столбцы A, B и C.

Чтобы разгруппировать данные в Excel, выделите сгруппированные строки или столбцы, а затем щелкните команду Разгруппировать.

Как скрыть и показать группы

  1. Чтобы скрыть группу в Excel, нажмите иконку Скрыть детали (минус).
  2. Группа будет скрыта. Чтобы показать скрытую группу, нажмите иконку Показать детали (плюс).

Подведение итогов в Excel

Команда Промежуточный итог позволяет автоматически создавать группы и использовать базовые функции, такие как СУММ, СЧЁТ и СРЗНАЧ, чтобы упростить подведение итогов. Например, команда Промежуточный итог способна вычислить стоимость канцтоваров по группам в большом заказе. Команда создаст иерархию групп, также называемую структурой, чтобы упорядочить информацию на листе.

Ваши данные должны быть правильно отсортированы перед использованием команды Промежуточный итог, Вы можете изучить серию уроков Сортировка данных в Excel, для получения дополнительной информации.

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

В следующем примере мы воспользуемся командой Промежуточный итог, чтобы определить сколько заказано футболок каждого размера (S, M, L и XL). В результате рабочий лист Excel обретет структуру в виде групп по каждому размеру футболок, а затем будет подсчитано общее количество футболок в каждой группе.

  1. Прежде всего отсортируйте данные, для которых требуется подвести итог. В этом примере мы подводим промежуточный итог для каждого размера футболок, поэтому информация на листе Excel должна быть отсортирована по столбцу Размер от меньшего к большему.
  2. Откройте вкладку Данные, затем нажмите команду Промежуточный итог.
  3. Откроется диалоговое окно Промежуточные итоги. Из раскрывающегося списка в поле При каждом изменении в, выберите столбец, который необходимо подытожить. В нашем случае это столбец Размер.
  4. Нажмите на кнопку со стрелкой в поле Операция, чтобы выбрать тип используемой функции. Мы выберем Количество, чтобы подсчитать количество футболок, заказанных для каждого размера.
  5. В поле Добавить итоги по выберите столбец, в который необходимо вывести итог. В нашем примере это столбец Размер.
  6. Если все параметры заданы правильно, нажмите ОК.
  7. Информация на листе будет сгруппирована, а под каждой группой появятся промежуточные итоги. В нашем случае данные сгруппированы по размеру футболок, а количество заказанных футболок для каждого размера указано под соответствующей группой.

Просмотр групп по уровням

При подведении промежуточных итогов в Excel рабочий лист разбивается на различные уровни. Вы можете переключаться между этими уровнями, чтобы иметь возможность регулировать количество отображаемой информации, используя иконки структуры 1, 2, 3 в левой части листа. В следующем примере мы переключимся между всеми тремя уровнями структуры.

Хоть в этом примере представлено всего три уровня, Excel позволяет создавать до 8 уровней вложенности.

  1. Щелкните нижний уровень, чтобы отобразить минимальное количество информации. Мы выберем уровень 1, который содержит только общее количество заказанных футболок.
  2. Щелкните следующий уровень, чтобы отобразить более подробную информацию. В нашем примере мы выберем уровень 2, который содержит все строки с итогами, но скрывает остальные данные на листе.
  3. Щелкните наивысший уровень, чтобы развернуть все данные на листе. В нашем случае это уровень 3.

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

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

Со временем необходимость в промежуточных итогах пропадает, особенно, когда требуется иначе перегруппировать данные на листе Excel. Если Вы более не хотите видеть промежуточные итоги, их можно удалить.

  1. Откройте вкладку Данные, затем нажмите команду Промежуточный итог.
  2. Откроется диалоговое окно Промежуточные итоги. Нажмите Убрать все.
  3. Все данные будут разгруппированы, а итоги удалены.

Чтобы удалить только группы, оставив промежуточные итоги, воспользуйтесь пунктом Удалить структуру из выпадающего меню команды Разгруппировать.

Оцените качество статьи. Нам важно ваше мнение:

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *