() translation by (you can also view the original English article)
Что вы будете делать, если нужно создать сводную таблицу, а ваши данные на разных листах? С установленным Excel 2013 у вас есть для этого простой способ. Существует технология, называемая Data Model и она использует отношения данных так, как это делает база данных.
В этом уроке я покажу вам всё для создания сводной таблицы в Excel 2013 из данных на нескольких листах, используя Data Model.
Видеоролик
Если вы хотите следовать уроку, используя собственный файл Excel, можете так и сделать. Или загрузите zip-файл к этому уроку, в котором содержится образец книги Pivot Consolidate.xlsx.
Изучение данных
В этой книге есть три рабочих листа: информация о клиенте, информация о заказе и информация об оплате.
Нажмите на лист Customer Info и убедитесь, что в нём содержатся номера заказов, а также название и состояние клиентов.
Нажмите на лист Order Info и посмотрите, что в нём содержатся номера заказов, а также поля за месяц, заказанные товары и то, являются ли эти продукты органическими.
Перейдите на вкладку Payment Info и убедитесь, что в ней содержатся номера заказов, сумма в долларах каждой продажи, способ оплаты и порядок размещения заказа новым или существующим клиентом.
Соединив все эти листы в области задач сводной таблицы, мы можем выбрать данные из каждого листа. Поскольку номера заказов существуют на всех трёх листах, они станут точками подключения. Это то, что база данных вызывает primary key. Обратите внимание: не обязательно иметь primary key, но он уменьшает вероятность ошибки.
Создание именованных таблиц
Перед созданием сводной таблицы давайте создадим таблицу из каждого листа.
Нажмите обратно в Customer Table, затем щёлкните в любом месте внутри области данных. Перейдите на вкладку Insert на панели ленты и щёлкните значок 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 (это самый первый значок).
В появившемся диалоговом окне необходимо правильно определить таблицу и выбрать, чтобы PivotTable перешла на новый рабочий лист. Внизу установите флажок Add this data to the Data Model. Нажмите OK.
Теперь у вас будет 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 окно выглядит так:
Обратите внимание, что нет необходимости создавать отношения между таблицами 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 and PowerPoint templates, а также scripts and apps для преобразования данных Excel в веб-форматы и наоборот.
