Как получить детальные данные из сводной таблицы
Иногда нужно получить исходные данные, на основании которых вычислена та или иная строка сводной таблицы. То есть, сделать выборку данных, удовлетворяющих какому-то критерию из сводной таблицы.
Вернемся к примеру из предыдущего поста. Там у нас была подневная таблица продаж по группам товара, регионам и менеджерам. Мы построили сводную таблицу, в которой посчитали сумму продаж с группировкой по менеджерам и группам товаров.
Представим, что по какой-то причине нас заинтересовали подневные продажи метизов у Романа. Выделим ячейку на пересечении строки «Роман» и столбца «Метизы». Жмем на нем правой кнопкой мыши и выбираем «Показать детали». Программа создаст новый лист и отобразит на нем выборку из исходной таблицы, т.е. все продажи Романа метизов.
А можно просто сделать двойной клик по нужной величине. Это альтернативный, и более простой способ.
Обратите внимание, что результат будет представлен в виде «официальной» таблицы Эксель, а не обычного диапазона. Можете еще раз оценить удобство такой структуры данных и воспользоваться всеми ее преимуществами
Форматирования сводной таблицы
В первую очередь поговорим о заголовках. Именно их хочется сразу изменить, но тут есть один нюанс, который стоит учитывать.
Заголовок изменяется самым обычным образом — щелкаем по ячейке с ним и затем меняем текст.
Также можно выделить нужную ячейку с заголовком и нажать клавишу F2 для перехода в режим редактирования ее содержимого.
При этом важно учитывать, что в сводной таблице название заголовка не может быть таким же, как и название поля. То есть если я захочу переименовать «Сумма по полю Итого» в «Итого», то ничего не выйдет и появится ошибка
Правда этот нюанс можно обойти. Если добавить в конце слова пробел, то для Эксель это будет уже другое значение, а пользователь разницы не увидит.
Я же просто переименую поля в «Сумма заказов» и «Количество заказов». Первый заголовок также можно изменить на «Город
Осталось отформатировать сами значения. В первую очередь изменим числовой формат, сделав его денежным. При этом сразу же приходит на ум воспользоваться соответствующим инструментом со вкладки Главная.
Однако, если выделенным будет только одна ячейка столбца, то и форматирования затронет только ее. В данном случае правильнее будет изменить числовой формат для всего столбца и для этого достаточно из контекстного меню, вызванного щелчком правой кнопки мышки на любой из ячеек столбца, выбрать пункт Числовой формат.
Затем в появившемся окне указываем нужный формат и задаем его параметры.
Форматирование будет применено сразу ко всем столбцу.
Ну а также на контекстной вкладке Конструктор, которая появляется только при выделении сводной таблицы, можно задать стиль оформления таблицы целиком. Для этого нужно либо выбрать одну из готовых цветовых схем, либо можно создать свой вариант стилевого оформления, задав форматирования для каждого элемента сводной таблицы индивидуально.
Даты в сводных таблицах
Ну и последняя важная тема — это даты. Пока мы вообще не трогали поле Дата, но сводные таблицы позволяют очень гибко выводить информацию, связанную с датами и сейчас я это продемонстрирую.
Создадим еще одну сводную таблицу, в которой выведем выручку за все время.
В исходной таблице указывалась конкретная дата каждой сделки, а сводная таблица автоматически сгруппировала даты при этом не только по годам, но и по кварталам и месяцам. При этом в области Строки поле Дата было автоматически преобразовано в три — Годы, Кварталы и Дата.
Если такая группировка не нужна, то можно ее отменить через контекстное меню.
Также с помощью контекстного меню можно вернуть группировку (пункт Группировать), указав необходимые группы. Здесь можно выбрать сразу несколько, например, месяцы и года.
Сортировка и фильтрация по датам работает также, как и с другими данными. Например, можно отключить какой-то временной период.
Для дат существует свой формат срезов — временная шкала.
Она также в интерактивном режиме позволяет выбирать только интересующие вас временные интервалы.
Таким образом на базе сводной таблицы можно создать интерактивный отчет, в котором с помощью срезов и временной шкалы можно очень тонко фильтровать данные. Ну а преобразовав данные сводной таблицы в диаграммы или графики можно получить отличный дашборд, с помощью которого легко можно анализировать или демонстрировать информацию.
Ну а сводные таблицы — это очень обширная и увлекательная тема, которой я посвятил отдельный очень подробный видеокурс, который так и называется «Сводные таблицы».
________________________________________
Ссылки на мои ресурсы по Excel
★ YouTube-канал Excel Master
★ Серия видеокурсов «Microsoft Excel Шаг за Шагом»
★ Авторские книги и курсы
Дополнительные вычисления в сводных таблицах
Интересный факт: часто встречаю пользователей, которые хорошо владеют инструментом сводных таблиц, но при этом не знают о такой их возможности, как дополнительные вычисления в сводных таблицах. Такие вычисления доступны в Excel 2010–2016, а в Excel 2007 дополнительные вычисления «спрятаны» в параметрах поля и их гораздо меньше.
Например, у нас есть простая таблица Excel по продажам вот с такими данными:
Предположим, нам нужно построить несколько отчетов:
- Процентная структура продаж.
- Продажи нарастающим итогом.
- Продажи с темпами роста.
Разберем, как создать такие отчеты с помощью дополнительных вычислений в сводных таблицах.
1. Процентная структура продаж
Чтобы с помощью сводных таблиц определить процентную структуру продаж, нужно сделать несколько простых действий.
Шаг 1. Постройте сводную таблицу, где в области строк Города и Товары, а в области сумм — Доходы (если вы не знаете, как создать сводную таблицу, посмотрите статью «Как построить сводную таблицу в Excel»).
Шаг 2. Щелкаем правой кнопкой мыши по любому числу в сводной таблице и выбираем раздел: Дополнительные вычисления → % от общей суммы. В появившемся меню доступно несколько способов вычисления процентов:
а) % от общей суммы – рассчитывается к итоговой сумме, от «угла».
Если переместить данные по Городам в область строк, а Товары в столбцы, мы увидим, что общий процент считается как по строкам, так и по колонкам, и сумма процентов равна 100%.
б) % от суммы по столбцу или по строке. Если требуется рассчитать структуру продаж, например, только по Городам, выбираем % от суммы по столбцу. Если только по товарам, соответственно – по строке.
в) А если нужно видеть структуру продаж и по товарам, и по городам? Не проблема! Нужно выбрать % от суммы по родительской строке. Тогда процент рассчитается от суммы группы, а не от общего итога. А сумма процентов внутри группы будет равна 100%.
Шаг 3. Все, конечно замечательно, НО хотелось бы рядом с процентами видеть суммы. И это тоже не проблема! Открою маленький секрет: в область значений сводной таблицы мы можем несколько раз перетащить один и тот столбец. Для этого просто захватываем мышкой нужное поле и несколько раз перетаскиваем его в область сумм.
В сводной таблице появится несколько одинаковых столбцов значений, к которым можно применить разные дополнительные вычисления.
2. Продажи нарастающим итогом
В сводной таблице можно показать суммы доходов нарастающим итогом по месяцам. Это делается также с помощью инструмента дополнительных вычислений.
Шаг 1. Постройте сводную таблицу. В строки поместите Города, в столбцы — Месяцы.
Шаг 2. Правой кнопкой мыши по любому числу, выберите Дополнительные вычисления → С нарастающим итогом в поле.
Шаг 3. В открывшемся окне выбираем, что нарастание нужно по Месяцам и все готово! Можно выбрать, относительно какого поля будет идти нарастание – строк и столбцов, городов или месяцев. В нашем случае выбран вариант нарастающего итога по месяцам. Кстати, столбец Общий итог пустой, потому что нарастающий итог рассчитан в декабре.
3. Темпы роста
Настроим отчет, в котором будут темпы роста, рассчитанные в сводной таблице.
Шаг 1. В новую сводную таблицу добавляем в строки Города, в столбцы Месяцы. В область значений – два одинаковых столбца Доходы. Когда в области Значений появляется более двух полей, в столбцах появляется «виртуальное» поле «∑ Значения», которое определяет размещение данных в сводной таблице – по строкам или столбцам. Переместите «∑ Значения» в область строк.
Шаг 2. Щелкаем правой кнопкой мышки по числам одного из полей сводной таблицы и выбираем Дополнительные вычисления → Приведенное отличие. Указываем Базовое поле «месяцы», элемент – «назад».
Январь будет пустым, потому что перед ним нет других данных. Это место можно занять спарклайнами. Чтобы их добавить, перейдите в меню Вставка → Спарклайны → График.
Легко группирует ячейки и вычислить по значениям в другом столбце в Excel
- В таблицах одинаковое количество столбцов, но при попытке использовать запрос получаем сообщение: «В таблицах или запросах, выбранных в запросе на объединение, не совпадает число столбцов.» Причина: в одном или нескольких листах с таблицами есть пустые столбцы (в них раньше были данные) и MS Excel считает их столбцами таблиц. Решение: Выделить и удалить все пустые столбцы на листах с таблицами.
- Все столбцы удалили, но ошибка осталась. Причина: файл сохранен на сетевом диске или открыт из почтовой программы. Решение: сохраните файл у себя на локальном компьютере, выполните запрос, вставьте в него макрос меняющий путь в запросе и выложите обратно в сеть (если нужно).
- В полученном запросе в некоторых ячейках пропадают данные. Причина: в ваших таблицах встречаются столбцы, которые одновременно содержатся и числовые и текстовые значения. MS Excel считает, что эти столбцы должны содержать только числа и не выводит текст. Решение:преобразуйте все числовые значения в этих столбцах во всех таблицах в текстовые. Обновите запрос — текстовые данные появятся.
Здравствуйте. При создании сводной из запроса выскакивает такая ошибка: Драйвер ODBC Excel Недопустимое использование скобок с именем ‘Эл.Думал, что проблема в названии листов (с точкой), где находятся таблицы: Эл. и Осн.Убрал из названия точки, создаю запрос заново, а в существующих подключениях он не отображается.В чем может быть проблема?
Технические характеристики → Полезное видео → Общие данные → Таблица размеров → Полезные советы → Как правильно рассчитать → Сыпучие продукты → Натяжные потолки и технологии→ Мера жидких продуктов
Источник данных сводной таблицы Excel
Для успешной работы со сводными таблицами исходные данные должны отвечать ряду требований. Обязательным условием является наличие названий над каждым полем (столбцом), по которым эти поля будут идентифицироваться. Теперь полезные советы.
1. Лучший формат для данных – это Таблица Excel. Она хороша тем, что у каждого поля есть наименование и при добавлении новых строк они автоматически включаются в сводную таблицу.
2. Избегайте повторения групп в виде столбцов. Например, все даты должны находиться в одном поле, а не разбиты по месяцам в отдельных столбцах.
3. Уберите пропуски и пустые ячейки иначе данная строка может выпасть из анализа.
4. Применяйте правильное форматирование к полям. Числа должны быть в числовом формате, даты должны быть датой. Иначе возникнут проблемы при группировке и математической обработке. Но здесь эксель вам поможет, т.к. сам неплохо определяет формат данных.
В целом требований немного, но их следует знать.
Работа со сводными таблицами в Excel
Изменить существующую сводную таблицу также легко. Посмотрим, как пожелания директора легко воплощаются в реальность.
Заменим выручку на прибыль.
Товары и области меняются местами также перетягиванием мыши.
Для фильтрации сводных таблиц есть несколько инструментов. В данном случае просто поместим поле «Менеджер» в область фильтров.
На все про все ушло несколько секунд. Вот, как работать со сводными таблицами. Конечно, не все задачи столь тривиальные. Бывают и такие, что необходимо использовать более замысловатый способ агрегации, добавлять вычисляемые поля, условное форматирование и т.д. Но об этом в другой раз.
8 комментариев
Сашуль, привет, help. такой вопрос — как в сводных таблицах построить структуру автоматически, не нажимая каждый раз кнопку Группировать?? при выделении таблицы сводной и потом создание Структуры выходит ошибка, хотя перед этим убрала Показывать общие итоги строк и столбцов((( и ничего не получается, руками группировать долго (таблицы большие), оч нужна помощь!!
Даша, здравствуй. Прости за долгое ожидание. Набери меня, разберем структуру твоей таблицы и постараемся найти решение.
Там где речь идет о «детальных данных» можно проще. Достаточно сделать дабл-клик на нужной цифре (двойной клик) и получим «умную»/»официальную» таблицу с разверткой (детализацией) этой цифры, которая будет располагаться на отдельном листе.
Константин, спасибо за уточнение. Добавил в текст.
При вычислении промежуточных итогов всей книги появляется необходимость в какие-либо листы добавить дополнительные данные. как это сделать, чтобы они сразу отразились в сводной таблице?
Ирина, здравствуйте. Вносите изменения в исходную таблицу. После этого, правой копкой мыши на сводную таблицу и в контекстном меню выберите «Обновить»
Как сделать сводную таблицу из нескольких файлов
Первый этап – выгрузить информацию в программу Excel и привести ее в соответствие с таблицами Excel. Если наши данные находятся в Worde, мы переносим их в Excel и делаем таблицу по всем правилам Excel (даем заголовки столбцам, убираем пустые строки и т.п.).
Дальнейшая работа по созданию сводной таблицы из нескольких файлов будет зависеть от типа данных. Если информация однотипная (табличек несколько, но заголовки одинаковые), то Мастер сводных таблиц – в помощь.
Мы просто создаем сводный отчет на основе данных в нескольких диапазонах консолидации.
Гораздо сложнее сделать сводную таблицу на основе разных по структуре исходных таблиц. Например, таких:
Первая таблица – приход товара. Вторая – количество проданных единиц в разных магазинах. Нам нужно свести эти две таблицы в один отчет, чтобы проиллюстрировать остатки, продажи по магазинам, выручку и т.п.
Мастер сводных таблиц при таких исходных параметрах выдаст ошибку. Так как нарушено одно из главных условий консолидации – одинаковые названия столбцов.
Но два заголовка в этих таблицах идентичны. Поэтому мы можем объединить данные, а потом создать сводный отчет.
- В ячейке-мишени (там, куда будет переноситься таблица) ставим курсор. Пишем = — переходим на лист с переносимыми данными – выделяем первую ячейку столбца, который копируем. Ввод. «Размножаем» формулу, протягивая вниз за правый нижний угол ячейки.
- По такому же принципу переносим другие данные. В результате из двух таблиц получаем одну общую.
- Теперь создадим сводный отчет. Вставка – сводная таблица – указываем диапазон и место – ОК.
Открывается заготовка Сводного отчета со Списком полей , которые можно отобразить.
Покажем, к примеру, количество проданного товара.
Можно выводить для анализа разные параметры, перемещать поля. Но на этом работа со сводными таблицами в Excel не заканчивается: возможности инструмента многообразны.