Группы и промежуточные итоги в Excel

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

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

  1. Выделите строки или столбцы, которые необходимо сгруппировать. В следующем примере мы выделим столбцы A, B и C.

    Группы и промежуточные итоги в Excel

  2. Откройте вкладку Данные на Ленте, затем нажмите команду Группировать.

    Группы и промежуточные итоги в Excel

  3. Выделенные строки или столбцы будут сгруппированы. В нашем примере это столбцы A, B и C.

    Группы и промежуточные итоги в Excel

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

Группы и промежуточные итоги в Excel

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

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

    Группы и промежуточные итоги в Excel

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

    Группы и промежуточные итоги в Excel

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

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

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

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

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

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

    Группы и промежуточные итоги в Excel

  2. Откройте вкладку Данные, затем нажмите команду Промежуточный итог.

    Группы и промежуточные итоги в Excel

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

    Группы и промежуточные итоги в Excel

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

    Группы и промежуточные итоги в Excel

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

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

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

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

    Группы и промежуточные итоги в Excel

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

    Группы и промежуточные итоги в Excel

  3. Щелкните наивысший уровень, чтобы развернуть все данные на листе. В нашем случае это уровень 3.

    Группы и промежуточные итоги в Excel

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

Группы и промежуточные итоги в Excel

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

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

  1. Откройте вкладку Данные, затем нажмите команду Промежуточный итог.

    Группы и промежуточные итоги в Excel

  2. Откроется диалоговое окно Промежуточные итоги. Нажмите Убрать все.

    Группы и промежуточные итоги в Excel

  3. Все данные будут разгруппированы, а итоги удалены.

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

Группы и промежуточные итоги в Excel

11.02.2015 23:27
28210

3 комментария

14.06.2015 11:31
Очень хорошая статья, спасибо!
Но появился вопрос, как убрать не нужные значения? На картинке они обведены красным цветом, 3...5...7 и так далее, они по сути не нужны, мне просто нужен один итог, общий…
Здравствуйте! Очень рад, что статья вам понравилась!

Мне не совсем понятно, что вы хотите получить в итоге. И не совсем понятно, что вы пытались получить на приведенном выше скриншоте (заранее прошу прощения за качество скриншотов в комментариях, со временем я поправлю этот момент).

Итак, по вашему вопросу у меня есть 2 предположения (возможно, вы имели ввиду что-то другое):

1. Вы хотите избавится полностью от этих строк. Тогда мне не понятно, зачем вам вообще нужны промежуточные итоги. Все можно решить с помощью сортировки.

2. Вы хотите скрыть надпись — 4 Среднее, 3 Среднее и т.д. А значения промежуточных итогов оставить. В таком случае есть минимум 2 варианта:
а) Самый простой — просто удалить эти надписи.
б) Воспользоваться условным форматированием. В вашем случае все «ненужные» надписи содержат слово «Среднее» и находятся в столбце E. Поэтому выделяем столбец E. Идем: Условное форматирование > Правила выделения ячеек > Текст содержит... Откроется диалоговое окно Текст, который содержит в поле слева указываем «Среднее», а в поле справа выбираем Пользовательский формат..., где задаем цвет текста = цвету фона ячейки. Таким образом мы не удаляем этот текст, а маскируем его.

Если я что-то не так понял в вашем вопросе, опишите проблему более подробно. Я обязательно отвечу на него!
15.06.2015 06:06
Нет, Вы всё правильно поняли, и в 1-м варианте вашего ответа всё понятно рассказали, спасибо Вам огромное!!!:)