Пользователи создают сводные таблицы для анализа, суммирования и представления большого объема данных. Такой инструмент Excel позволяет произвести фильтрацию и группировку информации, изобразить ее в различных разрезах (подготовить отчет).
Исходный материал – таблица с несколькими десятками и сотнями строк, несколько таблиц в одной книге, несколько файлов. Напомним порядок создания: «Вставка» – «Таблицы» – «Сводная таблица».
А в данной статье мы рассмотрим, как работать со сводными таблицами в Excel.
Как сделать сводную таблицу из нескольких файлов
Первый этап – выгрузить информацию в программу Excel и привести ее в соответствие с таблицами Excel. Если наши данные находятся в Worde, мы переносим их в Excel и делаем таблицу по всем правилам Excel (даем заголовки столбцам, убираем пустые строки и т.п.).
Дальнейшая работа по созданию сводной таблицы из нескольких файлов будет зависеть от типа данных. Если информация однотипная (табличек несколько, но заголовки одинаковые), то Мастер сводных таблиц – в помощь.
Мы просто создаем сводный отчет на основе данных в нескольких диапазонах консолидации.
Гораздо сложнее сделать сводную таблицу на основе разных по структуре исходных таблиц. Например, таких:
Первая таблица – приход товара. Вторая – количество проданных единиц в разных магазинах. Нам нужно свести эти две таблицы в один отчет, чтобы проиллюстрировать остатки, продажи по магазинам, выручку и т.п.
Мастер сводных таблиц при таких исходных параметрах выдаст ошибку. Так как нарушено одно из главных условий консолидации – одинаковые названия столбцов.
Но два заголовка в этих таблицах идентичны. Поэтому мы можем объединить данные, а потом создать сводный отчет.
Открывается заготовка Сводного отчета со Списком полей , которые можно отобразить.
Покажем, к примеру, количество проданного товара.
Можно выводить для анализа разные параметры, перемещать поля. Но на этом работа со сводными таблицами в Excel не заканчивается: возможности инструмента многообразны.
Детализация информации в сводных таблицах
Из отчета (см.выше) мы видим, что продано ВСЕГО 30 видеокарт. Чтобы узнать, какие данные были использованы для получения этого значения, щелкаем два раза мышкой по цифре «30». Получаем детальный отчет:
Как обновить данные в сводной таблице Excel?
Если мы изменим какой-либо параметр в исходной таблице либо добавим новую запись, в сводном отчете эта информация не отобразится. Такое положение вещей нас не устраивает.
Обновление данных:
Курсор должен стоять в любой ячейке сводного отчета.
Либо:
Правая кнопка мыши – обновить.
Чтобы настроить автоматическое обновление сводной таблицы при изменении данных, делаем по инструкции:
Изменение структуры отчета
Добавим в сводную таблицу новые поля:
После изменения диапазона в сводке появилось поле «Продажи».
Как добавить в сводную таблицу вычисляемое поле?
Иногда пользователю недостаточно данных, содержащихся в сводной таблице. Менять исходную информацию не имеет смысла. В таких ситуациях лучше добавить вычисляемое (пользовательское) поле.
Это виртуальный столбец, создаваемый в результате вычислений. В нем могут отображаться средние значения, проценты, расхождения. То есть результаты различных формул. Данные вычисляемого поля взаимодействуют с данными сводной таблицы.
Инструкция по добавлению пользовательского поля:
Группировка данных в сводном отчете
Для примера посчитаем расходы на товар в разные годы. Сколько было затрачено средств в 2012, 2013, 2014 и 2015. Группировка по дате в сводной таблице Excel выполняется следующим образом. Для примера сделаем простую сводную по дате поставки и сумме.
Щелкаем правой кнопкой мыши по любой дате. Выбираем команду «Группировать».
В открывшемся диалоге задаем параметры группировки. Начальная и конечная дата диапазона выводятся автоматически. Выбираем шаг – «Годы».
Получаем суммы заказов по годам.
По такой же схеме можно группировать данные в сводной таблице по другим параметрам.
Excel. Сводная таблица на основе нескольких листов
Если вы столкнулись с необходимостью создать сводную таблицу на основе данных, размещенных на нескольких листах одной книги (или разных книг), вас ждет разочарование. Стандартным образом Excel делает это «через одно место»… L
Да в Excel есть такая опция в мастере сводных таблиц, но привычно (как и для обычных сводных) она работает только для одного набора данных, например (см. файл Сводная_листы_один набор.xlsx ):
У вас есть список клиентов и объем продаж по ним по кварталам; данные за один квартал расположены на отдельном листе. Создадим сводную таблицу, консолидирующую данные за 4 квартала.
1. Поименуйте ваши исходные массивы данных; в принципе, это не обязательно, но, с другой стороны, это поднимает вашу работу на новый уровень J , облегчает восприятие информации другими пользователями, а также упрощает внесение любых изменений / дополнений в исходные данные в будущем (как создать динамически изменяемые именованные массивы см. здесь):
2. Запустите мастер сводных таблиц (как вывести мастер на панель быстрого доступа см. здесь); выберите опцию «в нескольких диапазонах консолидации », нажмите «далее»:
3. Оставьте предлагаемую по умолчанию опцию «Создать одно поле страницы»
4. Введите имя первого диапазона, нажмите «Добавить»:
5. Добавьте все четыре диапазона, нажмите «Далее»:
6. Оставьте предлагаемую по умолчанию опцию «новый лист», нажмите «Готово»:
Сводная таблица на основе данных, размещенных на четырех листах, создана. Сводная таблица (как и обычно) содержит четыре области. Вот только названия полей не соответствуют тому, что было в исходных данных:
http://pandia.ru/text/79/437/images/image010_66.jpg" width="348" height="233 id=">
Названия строк вместо «Клиент» – «Строка»; название столбца «Продажи» упрятано внутрь названия «Столбец»:
http://pandia.ru/text/79/437/images/image012_56.jpg" width="356 height=191" height="191">
В остальном с полученной сводной можно работать, как обычно. Например:
http://pandia.ru/text/79/437/images/image014_37.jpg" width="273" height="82 id=">
1. Поименуйте ваши исходные массивы данных.
2. Создайте сводную таблицу, как описано выше
Вот, что получилось:
EN-US">zip
(внутри два файла Excel 2007 с поддержкой макросов: от Кирилла Лапина и с моим набором данных) сводные.zipСводные таблицы удобно использовать для анализа данных и создания отчетов с ними. А если это реляционные данные (т. е. такие, которые хранятся в отдельных таблицах, но при этом их можно объединить благодаря общим значениям), вы можете всего за несколько минут создать такую сводную таблицу:
Чем примечательна эта сводная таблица? Обратите внимание: в списке полей справа отображается не одна таблица, а целая коллекция таблиц, содержащих поля, которые могут быть объединены в отдельную сводную таблицу для анализа данных в различных представлениях. Нет никакой необходимости в форматировании или подготовке данных вручную. Вы можете создать сводную таблицу, основанную на связанных таблицах, сразу после импорта данных.
Чтобы объединить несколько таблиц в списке полей сводной таблицы:
Ниже приведена процедура импорта нескольких таблиц из базы данных SQL Server.
Обратите внимание: список полей содержит несколько таблиц. Это все таблицы, выбранные вами во время импорта. Каждую таблицу можно развернуть и свернуть для просмотра ее полей. Так как таблицы связаны, вы можете создать сводную таблицу, перетянув поля из любой таблицы в область ЗНАЧЕНИЯ , СТРОКИ или СТОЛБЦЫ .
Импорт таблиц из других источников
Помимо SQL Server, вы можете импортировать таблицы из ряда других реляционных баз данных.
Реляционные базы данных - это не единственный источник данных, который поддерживает работу с несколькими таблицами в списке полей сводной таблицы. Вы можете использовать таблицы в своей книге или импортировать каналы данных, а затем интегрировать их с другими таблицами данных в книге. Чтобы все эти несвязанные данные работали вместе, нужно каждую таблицу добавить в модель данных, а затем создать связи между ними с помощью соответствующих значений полей.
Использование модели данных для создания новой сводной таблицы
Возможно, вы создали связи между таблицами в модели данных и теперь готовы использовать эти данные для анализа. Ниже описано, как создать новую сводную таблицу или сводную диаграмму с помощью модели данных в книге.
Консолидация данных представляет собой удобный способ объединения данных из нескольких источников в одном отчете. Например, если в каждом из региональных филиалов вашей компании есть сводный отчет о расходах, с помощью консолидации данных можно объединить такие значения в корпоративный отчет о расходах. Такой отчет может содержать итоговые и средние данные по продажам, текущим уровням запасов и наиболее популярным продуктам в рамках всей организации.
Для подведения итогов и обобщения результатов по данным в отдельных диапазонах можно консолидировать их в отчете сводной таблицы на главном листе. Диапазоны могут находиться в той же книге, что и главный лист, или в других книгах. Консолидированные данные легче обновлять и обобщать на регулярной основе или по мере необходимости.
Итоговый консолидированный отчет сводной таблицы может содержать следующие поля в области Список полей сводной таблицы , добавляемой в отчет сводной таблицы: "Строка", "Столбец" и "Значение". Кроме того, в отчет можно включить до четырех полей фильтра, которые называются "Страница1", "Страница2", "Страница3" и "Страница4".
Настройка исходных данных
Каждый из диапазонов данных следует преобразовать в формат перекрестной таблицы с совпадающими именами строк и столбцов для элементов, которые вы хотите объединить. В выбранные данные не следует включать итоговые строки и итоговые столбцы. В приведенном ниже примере показано четыре диапазона в формате перекрестной таблицы.
Поля страницы при консолидации данных
При консолидации данных можно использовать поля страницы, содержащие элементы, которые представляют один или несколько исходных диапазонов. Например, при консолидации данных бюджета отдела маркетинга, отдела продаж и производственного отдела поле страницы может содержать отдельный элемент с данными по каждому из этих отделов, а также элемент, содержащий сводные данные. В приведенном ниже примере показан отчет сводной таблицы, в котором выбраны одно поле страницы и несколько элементов.
Использование именованных диапазонов
Если велика вероятность того, что в следующий раз при консолидации данных исходный диапазон данных изменится (например, изменится число строк), рекомендуется задать имя для каждого из исходных диапазонов на разных листах. Эти имена можно использовать при консолидации диапазонов на главном листе. В случае расширения исходного диапазона перед обновлением отчета сводной таблицы можно обновить диапазон на отдельном листе для указанного имени таким образом, чтобы включить в него новые данные.
Другие способы консолидации данных
В Excel также доступны другие способы консолидации данных, которые позволяют работать с данными в разных форматах и макетах. Например, вы можете создавать формулы с объемными ссылками или использовать команду Консолидация (доступную на вкладке Данные в группе Работа с данными ).
Консолидация нескольких диапазонов
Для консолидации нескольких диапазонов вы можете воспользоваться мастером сводных таблиц и диаграмм. В нем можно указать, сколько полей страницы будет использоваться: ни одного, одно или несколько.
Консолидация данных без использования полей страницы
Чтобы объединить данные всех диапазонов и создать консолидированный диапазон без полей страницы, сделайте следующее:
Дополнительные команды .
В списке Выбрать команды из выберите пункт Все команды .
Выберите в списке пункт и нажмите кнопку Добавить , а затем - кнопку ОК .
Добавить .
Совет:
В разделе введите 0 , а затем нажмите кнопку Далее .
Щелкните значок мастера на панели быстрого доступа.
На странице Шаг 1 мастера выберите параметр , а затем нажмите кнопку Далее .
На странице Шаг 2а выберите параметр Создать поля страницы , а затем нажмите кнопку Далее .
На странице Шаг 2б сделайте следующее:
На странице Шаг 3 Готово .
Консолидация данных с использованием одного поля страницы
Чтобы включить одно поле страницы, содержащее элемент для каждого исходного диапазона, а также элемент для консолидации всех диапазонов, сделайте следующее:
Щелкните стрелку рядом с панелью инструментов и выберите Дополнительные команды .
В списке Выбрать команды из выберите пункт Все команды .
Выберите в списке пункт Мастер сводных таблиц и диаграмм и нажмите кнопку Добавить , а затем - кнопку ОК .
Перейдите в книгу и выделите диапазон ячеек, а затем вернитесь в мастер сводных таблиц и диаграмм и нажмите кнопку Добавить .
Совет: Если диапазон ячеек находится в другой книге, сначала откройте ее, чтобы упростить выбор данных.
Добавьте мастер сводных таблиц и диаграмм на панель быстрого доступа. Для этого:
Щелкните пустую ячейку книги, которая не является частью отчета сводной таблицы.
На странице Шаг 1 мастера выберите параметр в нескольких диапазонах консолидации , а затем нажмите кнопку Далее .
На странице Шаг 2а выберите параметр Создать одно поле страницы , а затем нажмите кнопку Далее .
На странице Шаг 2б сделайте следующее:
На странице Шаг 3 укажите, следует ли добавлять сводную таблицу на новый или же на существующий лист, и нажмите кнопку Готово .
Консолидация данных с использованием нескольких полей страницы
Вы можете создать несколько полей страницы и назначить имена элементов каждому из исходных диапазонов. Это позволяет выполнять частичную или полную консолидацию. Например, в одном поле страницы могут консолидироваться данных отдела маркетинга и отдела продаж без данных производственного отдела, а в другом поле - данные всех трех отделов. Чтобы создать консолидацию с использованием нескольких полей страницы, сделайте следующее:
Щелкните стрелку рядом с панелью инструментов и выберите Дополнительные команды .
В списке Выбрать команды из выберите пункт Все команды .
Выберите в списке пункт Мастер сводных таблиц и диаграмм и нажмите кнопку Добавить , а затем - кнопку ОК .
Перейдите в книгу и выделите диапазон ячеек, а затем вернитесь в мастер сводных таблиц и диаграмм и нажмите кнопку Добавить .
Совет: Если диапазон ячеек находится в другой книге, сначала откройте ее, чтобы упростить выбор данных.
В разделе Во-первых, укажите количество полей страницы сводной таблицы щелкните число полей, которые вы хотите использовать.
В группе Затем выберите диапазон в списке и укажите метку элемента в каждом из доступных окон полей. Повторите операцию для каждого диапазона для каждого из полей страницы выберите диапазон ячеек, а затем укажите его имя.
Пример
Если в разделе Во-первых, укажите количество полей страницы сводной таблицы задано число 1 , выберите каждый из диапазонов, а затем введите уникальное имя в поле Первое поле . Если у вас четыре диапазона, каждый из которых соответствует кварталу финансового года, выберите первый диапазон, введите имя "Кв1", выберите второй диапазон, введите имя "Кв2" и повторите процедуру для диапазонов "Кв3" и "Кв4".
Если в разделе Во-первых, укажите количество полей страницы сводной таблицы задано число 2 , выполните аналогичные действия в поле Первое поле . Затем выберите два диапазона и введите в поле Второе поле одинаковое имя, например "Пг1" и "Пг2". Выберите первый диапазон и введите имя "Пг1", выберите второй диапазон и введите имя "Пг1", выберите третий диапазон и введите имя "Пг2", выберите четвертый диапазон и введите имя "Пг2".
Добавьте мастер сводных таблиц и диаграмм на панель быстрого доступа. Для этого:
Щелкните пустую ячейку книги, которая не является частью отчета сводной таблицы.
На странице Шаг 1 мастера выберите параметр в нескольких диапазонах консолидации , а затем нажмите кнопку Далее .
На странице Шаг 2а выберите параметр Создать поля страницы , а затем нажмите кнопку Далее .
На странице Шаг 2б сделайте следующее:
На странице Шаг 3 укажите, следует ли добавлять сводную таблицу на новый или же на существующий лист, и нажмите кнопку Готово .
