Хитрости »
Основные понятия (22)Здесь собраны статьи, в которых разъясняются базовые понятия работы в Excel и VBA, а так же проблемы, с которыми сталкивается большинство начинающих
Сводные таблицы и анализ данных (5) Раздел поможет изучить сводные таблицы и научиться их использовать "на полную"
Графики и диаграммы (4) Раздел поможет научиться создавать диаграммы и графики в Excel, в том числе нестандартные
Работа с VB проектом (10) С помощью статей раздела вы научитесь создавать процедуры программно и выполнять различные операции с объектами самого VBA
Power BI и Power Query (5) Здесь собраны статьи, раскрывающие различные возможности мощнейшего инструмента для визуализаций бизнесс-процессов Power BI и надстройки для Excel Power Query
Условное форматирование (5) Этот раздел поможет поближе познакомиться с Условным форматированием на примерах различных ситуаций
Списки и диапазоны (5) Статьи, посвященные работе не только с выпадающими списками, но и с диапазонами и хитростями их применения в рабочих файлах
Макросы(VBA процедуры) (59) Статьи раздела направлены на изучение VBA с детальным разбором кодов. Множество статей с примерами кодов под всевозможные ситуации с комментариями и пояснениями
Разное (34) Собраны статьи, которые не подходят ни под одну из представленных выше категорий или входят сразу в несколько. Но эти статье не менее полезные!

Группировка данных в сводной таблице


Скачать файл, используемый в видеоуроке:

  Группировка в сводных таблицах (831,4 KiB, 274 скачиваний)


Основное предназначение сводной таблицы - анализ данных. И для этого она снабжена достаточным довольно обширным функционалом, включая группировку элементов. Для чего она нужна. Предположим, имеется таблица примерно такого вида:

И необходимо проанализировать данные в разрезе месяца или квартала или на основании групп артикулов с определенным шагом.


 

ГРУППИРОВКА ДАТЫ И ВРЕМЕНИ
Если необходимо просмотреть суммарную стоимость предложений по кварталам, то пригодиться группировка по датам.

  1. Выделить любую ячейку нужного поля из области строк или столбцов и щелкнуть правой кнопкой мыши;
  2. Выбрать из контекстного меню пункт Группировать (Group);
  3. В поле Начиная с(Starting at) ввести начальную дату для группы;
  4. В поле по(Ending at) ввести конечную дату для группы;
  5. В поле с шагом(By)  выбрать диапазон группировки: секунды, минуты, часы, дни, месяцы, кварталы, годы (seconds, minutes, hours, days, months, quarters, years);
  6. Нажать OK

 

ГРУППИРОВКА ЧИСЛОВЫХ ПОЛЕЙ
Может пригодиться для группировки по занятым местам или по ценам предложений. Например, можно отобрать все предложения от 110 000р до 130 000р с шагом 10 000р. В данном случае получим таблицу, в которой будут интересующие предложения из указанного диапазона, разбитые с нужным шагом. Если какие значения превышают указанную сумму(130 000р), то будет отдельная группа: >130000, если меньше: <110000.

  1. Выделить любую ячейку нужного поля из области строк или столбцов и щелкнуть правой кнопкой мыши;
  2. Выбрать из контекстного меню пункт Группировать (Group);
  3. В поле Начиная с(Starting at) ввести первый элемент группы (110000);
  4. В поле по(Ending at) ввести последний элемент группы (130000);
  5. В поле с шагом(By) указать шаг для детализации в каждой группе (10000).

Что делать, если данные не группируются
При группировке дат и числовых полей есть небольшой нюанс: иногда, при наличии дат или чисел в ячейках и вызове группировки можно получить сообщение "Выделенные объекты нельзя объединить в группу.":
Выделенные объекты нельзя объединить в группу
И вроде бы в ячейках даты/числа и все равно. В данном случае следует проверить - а действительно ли числа это числа, а даты - даты? Потому как бывает и так, что выглядят в ячейках данные как числа или даты, а на деле это просто текст. В большинстве случаев Excel подсвечивает такие ячейки зелеными треугольничками в левом верхнем углу:
Число как текст
В этом случае все просто: находим самую первую ячейку с таким треугольничком и выделяем все нижестоящие ячейки(до конца таблицы(Ctrl+Shift+стрелка вниз). После чего прокручиваем лист обратно к этой ячейке, нажимаем на значок с воскл.знаком левее ячейки и в раскрывшемся меню выбираем Преобразовать в число(дату).
Преобразовать в число
После этого обязательно необходимо перейти в сводную таблицу и обновить её(выделить любую ячейку сводной таблицы→Правая кнопка мыши→Обновить(Refresh) или вкладка Данные(Data)→Обновить все(Refresh all)→Обновить(Refresh)). Вполне возможно, что это действие придется повторить еще один-два раза.

Но может быть и так, что зеленых треугольников нет, а группировка не работает. Для начала следует убедиться, что в столбце нет ячеек с явным текстом, а так же ячеек с пробелами. По хорошему - пустые ячейки тоже лучше убрать или заменить нолями. Опять обновить пару раз сводную.

Если после этого группировка все равно отказывается работать - значит где-то еще есть числа/даты, записанные как текст. Но они могут быть не подсвечены зеленым треугольником. Такое поведение часто наблюдается в файлах, выгруженных из 1С или иным программ. Часто побеждают это очень упорным трудом: выделяют ячейку, жмут F2(чтобы войти в режим редактирования ячейки) и Enter. Тогда Excel преобразует дату/число в настоящие дату/число. Но если таких ячеек хотя бы 100 - это уже не на пару минут рутины. Благо, все это можно сделать за пару секунд. Чтобы быстро преобразовать ячейки с датами/числами, записанными как текст в реальные даты/число необходимо:

  • скопировать любую пустую ячейку на листе
  • выделить все ячейки с датами/числами
  • правая кнопка мыши -Специальная вставка (Paste Special) -в окне выбрать Значения (Values), операция - Сложить (Multiply)
  • ОК

Excel автоматом преобразует даты и числа в нормальные данные. Возможно, придется заново задать формат датам - но это уже совершенно не сложно: правая кнопка мыши -Формат ячеек (Format Cells) -Дата (Date).
Про другие возможности Специальной вставки можно прочитать в этой статье: Как быстро умножить/разделить/сложить/вычесть из множества ячеек одно и то же число?


 

ГРУППИРОВКА ТЕКСТОВЫХ ПОЛЕЙ ИЛИ ОТДЕЛЬНЫХ ЭЛЕМЕНТОВ

  1. Выделить ячейку из области строк или столбцов с одним из элементов поля для группировки;
  2. Удерживая CTRL или SHIFT выделить другие элементы (ячейки) этого поля;
  3. Щелкнуть правой кнопкой по любой выделенной ячейке и выбрать из контекстного меню пункт Группировать (Group) или на вкладке Параметры(Options) в группе Группировать(Group) нажать кнопку Группа по выделенному(Group Selection);

  4. При необходимости задать свое имя группе

В полях с уровнями можно группировать только элементы, имеющие одинаковые подуровни. Например, если в поле есть два уровня «Страна» и «Город», нельзя сгруппировать города из разных стран.


 

ПЕРЕИМЕНОВАНИЕ ГРУППЫ ПО УМОЛЧАНИЮ
При группировке элементов Excel задает имена групп по умолчанию, например Группа1(Group1) для выбранных элементов или Кв-л1(Qtr1) для квартала 1(если работаем с датами). Задать группе более понятное имя совсем несложно:

    1. Выделить имя группы;
    2. Нажать клавишу F2;
    3. Ввести новое имя группы.


 

РАЗГРУППИРОВКА ЭЛЕМЕНТОВ

  1. Выделить группу элементов, которые требуется разгруппировать;
  2. На вкладке Параметры(Options) в группе Группировать(Group) нажать кнопку Разгруппировать(Ungroup) (или щелкнуть правой кнопкой мыши и выбрать из контекстного меню пункт Разгруппировать(Ungroup)).

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

Примечания:

  1. Для источников данных OLAP (Online Analytical Processing), не поддерживающих инструкцию CREATE SESSION CUBE, группировка элементов невозможна.
  2. При наличии одного или нескольких сгруппированных элементов использовать команду Преобразование в формулы (Параметры- Сервис- Средства OLAP) невозможно. Перед использованием этой команды необходимо сначала удалить сгруппированные элементы.
  3. Для быстрой работы c группами данных надо выделить ячейки в области названий строк или столбцов сводной таблицы, щелкнуть правой кнопкой мыши на любой из выделенных ячеек и выбрать Развернуть/Cвернуть (Expand/Collapse)

 

Так же см.:
Общие сведения о сводных таблицах
Сводная таблица из нескольких листов


Статья помогла? Сделай твит, поделись ссылкой с друзьями!

Поиск по меткам

Access Multex Outlook Power BI Power Query и Power BI VBA работа в редакторе VBA управление кодами Бесплатные надстройки Дата и время Диаграммы и графики Записки Защита Защита данных Интернет Картинки и объекты Листы и книги Макросы и VBA Настройка Поиск данных Почта Программы Работа с приложениями Работа с файлами Разработка приложений Сводные таблицы Списки Тренинги и вебинары Финансовые Форматирование Формулы и функции Функции Excel Функции VBA Ячейки и диапазоны акции MulTEx вебинар ссылки статьи тренинг
Обсуждение: есть 1 комментарий
  1. Иван:

    Можно ли добавить дополнительные "Промежуточные итоги" для сводной таблицы, содержащей большую структуру - столбцов.
    Так, чтобы эти дополнительные итоги - показывали итоги по каждой структуре.
    Например, есть сводная таблица по месяцам продаж (строки) по Магазинам, Маркам, Цветам товара (столбцы).

    Хотелось бы увидеть в столбцах: Общие итоги (+), Итоги по Магазину (+), Итоги по Марке (- не дает, только внутри каждого магазина), Итоги по Цвету (+), Итоги по Магазину-Марке(+), Итоги по Марке-Цвету(- не дает), Итоги по Магазину -Цвету (не дает).....

    Итого 7 итогов: 4 могу сделать, а 3 не получается ( в одной таблице)...... Приходится делать надстройку поверх Сводной....

Поделитесь своим мнением

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

Логин
Наши партнеры
Перейти
Перейти
Счетчики
Анализ сайта

Яндекс.Метрика
© 2016 Excel для всех  Войти