Что вы будете делать, если нужно создать сводную таблицу, а ваши данные на разных листах? С установленным Excel 2013 у вас есть для этого простой способ. Существует технология, называемая Data Model и она использует отношения данных так, как это делает база данных.
В этом уроке я покажу вам всё для создания сводной таблицы в Excel 2013 из данных на нескольких листах, используя Data Model.
Видеоролик
Если вы хотите следовать уроку, используя собственный файл Excel, можете так и сделать. Или загрузите zip-файл к этому уроку, в котором содержится образец книги Pivot Consolidate.xlsx.
Изучение данных
В этой книге есть три рабочих листа: информация о клиенте, информация о заказе и информация об оплате.
Нажмите на лист Customer Info и убедитесь, что в нём содержатся номера заказов, а также название и состояние клиентов.
Customer Info лист
Нажмите на лист Order Info и посмотрите, что в нём содержатся номера заказов, а также поля за месяц, заказанные товары и то, являются ли эти продукты органическими.
Order Info листПерейдите на вкладку Payment Info и убедитесь, что в ней содержатся номера заказов, сумма в долларах каждой продажи, способ оплаты и порядок размещения заказа новым или существующим клиентом.
Payment Info листСоединив все эти листы в области задач сводной таблицы, мы можем выбрать данные из каждого листа. Поскольку номера заказов существуют на всех трёх листах, они станут точками подключения. Это то, что база данных вызывает primary key. Обратите внимание: не обязательно иметь primary key, но он уменьшает вероятность ошибки.
Создание именованных таблиц
Перед созданием сводной таблицы давайте создадим таблицу из каждого листа.
Нажмите обратно в Customer Table , затем щёлкните в любом месте внутри области данных. Перейдите на вкладку Insert на панели ленты и щёлкните значок Table .
Преобразуйте данные на листе, выбрав Insert > TableДиалоговое окно Create Table правильно определяет область таблицы. Флажок внизу должен также идентифицировать, что первая строка таблицы предназначена для заголовков. (Если нет, выберите этот вариант.)
Диалог Create Table должен правильно угадывать область данныхНажмите OK , и теперь у вас есть таблица с чередующимися штрихами и кнопками фильтра. Вы можете щёлкнуть внутри, чтобы снять выделение, если хотите лучше рассмотреть её (просто не нажимайте вне таблицы). На панели ленты также отображается вкладка Design для таблицы. На левой стороне ленты в поле Table Name отображается временное имя Table1. Удалите это и назовите его Customer_Info (используйте символ подчёркивания вместо пробела). Нажмите Enter .
Применить имя к каждой таблицеПовторите эти действия с листами Order Info и Payment Info. Назовите таблицы Order_Info и Payment_Info .
Теперь мы готовы вставить PivotTable.
Вставка PivotTable
Убедитесь, что на листе Payment Info курсор находится где-то в таблице. Вернитесь на вкладку Insert ленты и щёлкните значок PivotTable (это самый первый значок).
С помощью курсора внутри одной из таблиц выберите Insert > PivotTableВ появившемся диалоговом окне необходимо правильно определить таблицу и выбрать, чтобы PivotTable перешла на новый рабочий лист. Внизу установите флажок Add this data to the Data Model . Нажмите OK .
Добавление данных в Data Model - это то, что позволяет соединениям работатьТеперь у вас будет PivotTable на новом листе, в правой части экрана будет панель задач, а на ленте появится вкладка Analyze.
На панели задач отображается таблица и поля только активного листа, поэтому нажмите ALL , чтобы увидеть все созданные вами таблицы. Но прежде чем мы сможем их использовать, мы должны связать их друг с другом, а это означает создание отношений. Нажмите кнопку Relationships на панели ленты.
Настройка отношений таблиц
Нажатие этой кнопки отображает диалоговое окно Manage Relationships. Нажмите кнопку New и появится диалоговое окно Create Relationship . Мы создадим два отношения, используя поле Order # в качестве соединителя.
В раскрывающихся списках выберите Payment_Info для таблицы, а рядом с ним выберите Order # в раскрывающемся списке Column. Во второй строке выберите Customer_Info из раскрывающегося списка Related Table, а рядом с ней выберите Order # из раскрывающегося списка Related Column.
Есть три таблицы, поэтому создайте два отношенияЭто означает, что таблицы Payment_Info и Customer_Info связаны между собой по совпадению номера заказов.
Нажмите кнопку OK и мы увидим эти отношения, перечисленные в окне Manage Relationships.
Повторите этот процесс, чтобы создать связь между Payment_Info и Order_Info, также используя поле Order #. Теперь Manage Relationships окно выглядит так:
В диалоговом окне Manage Relationships отображаются отношения, которые вы создаётеОбратите внимание, что нет необходимости создавать отношения между таблицами Order_Info и Customer_Info, так как они автоматически соединяются через таблицу Payment_Info.
Нажмите кнопку Close в нижней части окна. Теперь мы можем, наконец, перетащить поля в PivotTable.
Вставка полей в PivotTable
В разделе ALL на панели задач щёлкните маленькие стрелки, чтобы, развернув три таблицы, увидеть их поля. Перетащите поля в области PivotTable следующим образом:
- State и Month в строки
- Product в колонки
- $ Sale в значения
- Status в фильтры
Теперь вы можете использовать и изменять её, как и любую другую PivotTable.
Заключение
Используя новую функцию Object Data Model в Excel 2013, вы можете выбрать розовые поля из нескольких листов для создания единой PivotTable. Имейте в виду, что строки каждой таблицы должны быть каким-то образом связаны друг с другом. У вас больше шансов на успех, когда таблицы имеют общее поле с уникальными значениями.
Если вы ищете хорошие способы представления своих данных, Envato Market имеет хороший выбор , а также для преобразования данных Excel в веб-форматы и наоборот.
Из этой статьи Вы узнаете, как быстро объединить данные из двух таблиц Excel, когда в ключевых столбцах нет точных совпадений. Например, когда уникальный идентификатор из первой таблицы представляет собой первые пять символов идентификатора из второй таблицы. Все предлагаемые в этой статье решения протестированы мной в Excel 2013, 2010 и 2007.
Итак, есть два листа Excel, которые нужно объединить для дальнейшего анализа данных. Предположим, в одной таблице содержатся цены (столбец Price) и описания товаров (столбец Beer), которые Вы продаёте, а во второй отражены данные о наличии товаров на складе (столбец In stock). Если Вы или Ваши коллеги составляли обе таблицы по каталогу, то в обеих должен присутствовать как минимум один ключевой столбец с уникальными идентификаторами товаров. Описание товара или цена могут изменяться, но уникальный идентификатор всегда остаётся неизменным.
Трудности начинаются, когда Вы получаете некоторые таблицы от производителя или из других отделов компании. Дело может ещё усложниться, если вдруг вводится новый формат уникальных идентификаторов или самую малость изменятся складские номенклатурные обозначения (SKU). И перед Вами стоит задача объединить в Excel новую и старую таблицы с данными. Так или иначе, возникает ситуация, когда в ключевых столбцах имеет место только частичное совпадение записей, например, “12345 ” и “12345-новый_суффикс “. Вам-то понятно, что это тот же SKU, но компьютер не так догадлив! Это не точное совпадение делает невозможным использование обычных формул Excel для объединения данных из двух таблиц.
И что совсем плохо – соответствия могут быть вовсе нечёткими, и “Некоторая компания ” в одной таблице может превратиться в “ЗАО «Некоторая Компания» ” в другой таблице, а “Новая Компания (бывшая Некоторая Компания) ” и “Старая Компания ” тоже окажутся записью об одной и той же фирме. Это известно Вам, но как это объяснить Excel?
Замечание: Решения, описанные в этой статье, универсальны. Вы можете адаптировать их для дальнейшего использования с любыми стандартными формулами, такими как ВПР (VLOOKUP) , ПОИСКПОЗ (MATCH) , ГПР (HLOOKUP) и так далее.
Выберите подходящий пример, чтобы сразу перейти к нужному решению:
Ключевой столбец в одной из таблиц содержит дополнительные символы
Рассмотрим две таблицы. Столбцы первой таблицы содержат номенклатурный номер (SKU), наименование пива (Beer) и его цену (Price). Во второй таблице записан SKU и количество бутылок на складе (In stock). Вместо пива может быть любой товар, а количество столбцов в реальной жизни может быть гораздо больше.
В таблице с дополнительными символами создаём вспомогательный столбец. Можно добавить его в конец таблицы, но лучше всего вставить его следующим справа после ключевого столбца, чтобы он был на виду.
Ключевым в таблице в нашем примере является столбец A с данными SKU, и нужно извлечь из него первые 5 символов. Добавим вспомогательный столбец и назовём его SKU helper :
Готово! Теперь у нас есть ключевые столбцы с точным совпадением значений – столбец SKU helper в основной таблице и столбец SKU в таблице, где будет выполняться поиск.
Теперь при помощи функции ВПР (VLOOKUP) мы получим нужный результат:
Другие формулы
- Извлечь первые Х
символов справа: например, 6 символов справа из записи “DSFH-164900”. Формула будет выглядеть так:
ПРАВСИМВ(A2;6)
=RIGHT(A2,6) - Пропустить первые Х
символов, извлечь следующие Y
символов: например, нужно извлечь “0123” из записи “PREFIX_0123_SUFF”. Здесь нам нужно пропустить первые 8 символов и извлечь следующие 4 символа. Формула будет выглядеть так:
ПСТР(A2;8;4)
=MID(A2,8,4) - Извлечь все символы до разделителя, длина получившейся последовательности может быть разной. Например, нужно извлечь “123456” и “0123” из записей “123456-суффикс” и “0123-суффикс” соответственно. Формула будет выглядеть так:
ЛЕВСИМВ(A2;НАЙТИ("-";A2)-1)
=LEFT(A2,FIND("-",A2)-1)
Одним словом, Вы можете использовать такие функции Excel, как ЛЕВСИМВ (LEFT), ПРАВСИМВ (RIGHT), ПСТР (MID), НАЙТИ (FIND), чтобы извлекать любые части составного индекса. Если с этим возникли трудности – , мы сделаем всё возможное, чтобы помочь Вам.
Данные из ключевого столбца в первой таблице разбиты на два или более столбца во второй таблице
Предположим, таблица, в которой производится поиск, содержит столбец с идентификаторами. В ячейках этого столбца содержатся записи вида XXXX-YYYY , где XXXX – это кодовое обозначение группы товаров (мобильные телефоны, телевизоры, видеокамеры, фотокамеры), а YYYY – это код товара внутри группы. Главная таблица состоит из двух столбцов: в одном содержатся коды товарных групп (Group), во втором записаны коды товаров (ID). Мы не можем просто отбросить коды групп товаров, так как один и тот же код товара может повторяться в разных группах.
В ячейке C2 запишем такую формулу:
СЦЕПИТЬ(A2;"-";B2)
=CONCATENATE(A2,"-",B2)
Здесь A2 – это адрес ячейки, содержащей код группы; символ “– ” – это разделитель; B2 – это адрес ячейки, содержащей код товара. Скопируем формулу в остальные строки.
Теперь объединить данные из наших двух таблиц не составит труда. Мы будем сопоставлять столбец Full ID первой таблицы со столбцом ID второй таблицы. При обнаружении совпадения, записи из столбцов Description и Price второй таблицы будут добавлены в первую таблицу.
Данные в ключевых столбцах не совпадают
Вот пример: Вы владелец небольшого магазина, получаете товар от одного или нескольких поставщиков. У каждого из них принята собственная номенклатура, отличающаяся от Вашей. В результате возникают ситуации, когда Ваша запись “Case-Ip4S-01” соответствует записи “SPK-A1403” в файле Excel, полученном от поставщика. Такие расхождения возникают случайным образом и нет никакого общего правила, чтобы автоматически преобразовать “SPK-A1403” в “Case-Ip4S-01”.
Плохая новость: Данные, содержащиеся в этих двух таблицах Excel, придётся обрабатывать вручную, чтобы в дальнейшем было возможно объединить их.
Хорошая новость: Это придётся сделать только один раз, и получившуюся вспомогательную таблицу можно будет сохранить для дальнейшего использования. Далее Вы сможете объединять эти таблицы автоматически и сэкономить таким образом массу времени
1. Создаём вспомогательную таблицу для поиска.
Создаём новый лист Excel и называем его SKU converter . Копируем весь столбец Our.SKU из листа Store на новый лист, удаляем дубликаты и оставляем в нём только уникальные значения.
Рядом добавляем столбец Supp.SKU и вручную ищем соответствия между значениями столбцов Our.SKU и Supp.SKU (в этом нам помогут описания из столбца Description ). Это скучная работёнка, пусть Вас радует мысль о том, что её придётся выполнить только один раз:-).
В результате мы имеем вот такую таблицу:
2. Обновляем главную таблицу при помощи данных из таблицы для поиска.
В главную таблицу (лист Store) вставляем новый столбец Supp.SKU .
Далее при помощи функции ВПР (VLOOKUP) сравниваем листы Store и SKU converter Our.SKU , а для обновлённых данных – столбец Supp.SKU .
Столбец Supp.SKU заполняется оригинальными кодами производителя.
Замечание: Если в столбце Supp.SKU появились пустые ячейки, то необходимо взять все коды SKU , соответствующие этим пустым ячейкам, добавить их в таблицу SKU converter и найти соответствующий код из таблицы поставщика. После этого повторяем шаг 2.
3. Переносим данные из таблицы поиска в главную таблицу
В нашей главной таблице есть ключевой столбец с точным совпадением с элементами таблицы поиска, так что теперь эта задача не вызовет сложностей
При помощи функции ВПР (VLOOKUP) объединяем данные листа Store с данными листа Wholesale Supplier 1 , используя для поиска соответствий столбец Supp.SKU .
Вот пример обновлённых данных в столбце Wholesale Price :
Всё просто, не так ли? Задавайте свои вопросы в комментариях к статье, я постараюсь ответить, как можно скорее.
Сводные таблицы удобно использовать для анализа данных и создания отчетов с ними. А если это реляционные данные (т. е. такие, которые хранятся в отдельных таблицах, но при этом их можно объединить благодаря общим значениям), вы можете всего за несколько минут создать такую сводную таблицу:
Чем отличается эта сводная таблица? Обратите внимание, что в списке полей справа отображается не одна таблица, а целый набор таблиц. Каждая из этих таблиц содержит поля, которые можно объединить в одну сводную таблицу для получения различных срезов данных. Не требуются ручное форматирование и подготовка данных. Сразу после импорта данных можно создать сводную таблицу на основе связанных таблиц.
Чтобы объединить несколько таблиц в списке полей сводной таблицы:
Ниже приведена процедура импорта нескольких таблиц из базы данных SQL Server.
Обратите внимание: список полей содержит несколько таблиц. Это все таблицы, выбранные вами во время импорта. Каждую таблицу можно развернуть и свернуть для просмотра ее полей. Так как таблицы связаны, вы можете создать сводную таблицу, перетянув поля из любой таблицы в область ЗНАЧЕНИЯ , СТРОКИ или СТОЛБЦЫ .
Импорт таблиц из других источников
Помимо SQL Server, вы можете импортировать таблицы из ряда других реляционных баз данных.
Реляционные базы данных - это не единственный источник данных, который поддерживает работу с несколькими таблицами в списке полей сводной таблицы. Вы можете использовать таблицы в своей книге или импортировать каналы данных, а затем интегрировать их с другими таблицами данных в книге. Чтобы все эти несвязанные данные работали вместе, нужно каждую таблицу добавить в модель данных, а затем создать связи между ними с помощью соответствующих значений полей.
Использование модели данных для создания новой сводной таблицы
Возможно, вы создали связи между таблицами в модели данных и теперь готовы использовать эти данные для анализа. Ниже описано, как создать новую сводную таблицу или сводную диаграмму с помощью модели данных в книге.
Очень часто при формировании отчётов (аналитики) основанных на больших объемах данных приходиться сводить в одну таблицу информацию с нескольких листов книги «Excel» .
В идеале для сбора и аналитики информации с разных листов «Эксель» желательно сделать сводную таблицу, которая будет брать данные из нескольких источников (листов книги).
Такую таблицу можно выполнить двумя разными способами.
Рассмотрим первый способ.
Создание сводной таблицы с разных листов при помощи стандартных возможностей и инструментов.
Шаг первый.
Необходимо добавить в ленту инструментов кнопку мастера создания сводных таблиц и диаграмм.
Для этого следует кликнуть правой кнопкой мыши по ленте (панели инструментов) и выбрать из выпадающего меню пункт «Настройка ленты»
либо войти во вкладку
«Файл» => «Параметры» => «Настройка ленты».
Далее в настройках ленты из выпадающего списка под надписью «Выбрать команды» выбрать пункт «Все команды». В окне ниже отобразятся все возможные команды, которые можно разместить на панели, в алфавитном порядке.
Из списка выбираем «Мастер сводных таблиц и диаграмм»
В правом окне при помощи кнопки «Создать группу» создаем новую группу инструментов. Для группы можно выбрать удобное для Вас наименование. Например, «Своя группа». Можно выбрать на какой вкладке будет создана группа. В своем примере я выбрал вкладку «Главная».
Когда группа создана, выделите ее курсором, выделите курсором «Мастер сводных таблиц и диаграмм» в левом окне и нажмите кнопку «Добавить >>».
После нажмите «Ок».
Теперь на главной вкладке панели инструментов находится инструмент «Мастер сводных таблиц и диаграмм».
Шаг второй. Построение сводной таблицы из нескольких источников данных.
Второй способ.
Создание таблицы берущей данные с нескольких листов при помощи запроса сформированного в надстройке Power Query .
Данный способ заключается в использовании запроса надстройки Power Query.
Создание запроса Power Query для сведения нескольких страниц книги в одну таблицу.
Шаг первый.
Нужно создать два запроса, каждый из которых будет брать информацию из отдельной таблицы.
Шаг два.
Для этого во вкладке Power Query нужно нажать кнопку «Из таблицы» и указать в появившемся окне диапазон – источник данных. После чего нажать «Ок».
Шаг три.
Когда создан второй запрос, нужно во вкладке Power Query кликнуть по кнопке «Слияние запросов» и настроит в появившемся окне вид получившейся общей таблицы.
Источником данных для сводной таблицы служит список данных, где, как правило, каждый столбец выступает в роли поля сводной таблицы. Но что если к вам пришла, только с виду похожая на сводную, таблица (она отформатирована и внешне напоминает ее, но использовать инструменты работы со сводными таблицами невозможно). И вам необходимо превратить ее в список данных, т.е. выполнить обратную операцию. В этом посте вы узнаете, как преобразовать сводную таблицу с двумя переменными в список данных.
На рисунке показан принцип, который я описал. Т.е. в диапазоне A2:E5 находится исходная сводная таблица, которая преобразуется в список данных (диапазон H2:J14). Вторая таблица представляет тот же набор данных, только в другом ракурсе. Каждое значение исходной сводной таблицы выглядит в виде строки, состоящее из пункта поля строки, поля столбца и соответствующего им значения. Такое отображение данных бывает полезно, когда необходимо отсортировать и манипулировать данными другими способами.
Для того чтобы реализовать возможность создания такого списка, мы воспользуемся инструментами сводной таблицы. Добавим кнопку Мастер сводных таблиц в панель быстрогодоступа, которая недоступна нам на ленте, но осталась как рудимент от более ранних версий Excel.
Перейдите по вкладке Файл -> Параметры . В появившемся диалоговом окне Параметры Excel, во вкладке Панель быстрого доступа в левом поле найдите пункт Мастер сводных таблиц и диаграмм и добавьте его в правый. Нажмите ОК.
Теперь на панели быстрого доступа у вас появился новый значок.
Щелкните по этой вкладке, чтобы запустить Мастер сводных таблиц.
На первом шаге мастера необходимо выбрать тип источника данных сводной таблицы. Устанавливаем переключатель В нескольких диапазонах консолидации и жмем Далее.
На шаге 2а укажите, как следует создавать поля страницы. Поместите переключатель Создать поля страницы -> Далее.
На шаге 2б, в поле Диапазон выберите диапазон, содержащий данные, и щелкните Добавить. В нашем случае это будет местоположение исходной сводной таблицы A1:E4.
На третьем шаге необходимо определиться, куда необходимо поместить сводную таблицу, и нажмите кнопку Готово.
Excel создаст сводную таблицу с данными. В левой части экрана вы увидите область Список полей сводной таблицы. Уберите все пункты с полей строк и столбцов. Более подробно о я писал в предыдущей статье.
У вас получится небольшая сводная таблица, состоящая из одной ячейки, которая содержит сумму всех значений исходной таблицы.
Дважды щелкните по этой ячейке. Excel создаст новый лист, который будет содержать таблицу со списком значений.
Заголовки этой таблицы представляют общую информацию, вероятно, вы захотите сделать их более информативными.
