У вас нет Power Pivot? Неважно. Большая часть Power Pivot встроена в Excel 2013 и даже больше в Excel 2016. Сегодня наш совет от Эша - объединение таблиц в сводную таблицу.
Каждую среду в течение семи недель я показываю один из любимых советов от Эша Шармы. Эш - менеджер по продукту в команде Excel. Его команда предлагает вам сводные таблицы и многое другое. Сегодня любимая функция Эша - объединение нескольких наборов данных с помощью отношений и модели данных.
Скажем, ваш ИТ-отдел предоставляет вам набор данных, показанный в столбцах A: D. Есть поля для покупателя и рынка. Вам нужно объединить определенные рынки в регионы. Каждый покупатель принадлежит к определенному сектору. Регион и сектор отсутствуют в исходных данных, но у вас есть справочные таблицы для предоставления этой информации.
Обычно вы сглаживаете данные, используя ВПР, чтобы перенести данные из оранжевой и желтой таблиц в синюю. Но поскольку ключевое поле находится не слева от каждой таблицы, вам придется либо переключиться на INDEX и MATCH, либо переупорядочить таблицы поиска.
Начиная с Excel 2013, вы можете оставить таблицы поиска на месте и объединить их в самом отчете сводной таблицы.
Чтобы этот метод работал, все три таблицы должны быть отформатированы как таблица. Выделите по одной ячейке в каждом наборе данных и выберите «Главная», «Форматировать как таблицу» или нажмите Ctrl + T. Первоначально эти три таблицы будут называться Table1, Table2 и Table3. Я использую вкладку «Дизайн инструментов для таблиц» на ленте и переименовываю каждую таблицу. Я также меняю цвет каждой таблицы. В этом примере синяя таблица называется «Данные». Оранжевая таблица - это RegionTable. Желтая таблица - это SectorTable.
Заметка
Некоторые скажут вам, что вам следует использовать такие странные имена, как Fact, TblSector и TblRegion. Если кто-то беспокоит вас, просто украдите его карманный протектор и дайте ему понять, что вы предпочитаете имена, звучащие по-английски.
Чтобы переименовать таблицу, введите новое имя в поле слева на вкладке «Работа с таблицами». В именах таблиц не должно быть пробелов.
Как только три таблицы определены, перейдите на вкладку «Данные» и нажмите «Взаимосвязи».
В диалоговом окне «Управление отношениями» нажмите «Создать». В диалоговом окне «Создание отношения» укажите, что поле «Клиент» таблицы данных связано с полем «Клиент» в SectorTable. Щелкните ОК.
Определите еще одну новую связь между полем Market в полях Data и RegionTable. После определения обоих отношений вы увидите их в диалоговом окне «Управление отношениями».
Поздравляем: вы только что создали модель данных в своей книге. Пришло время построить сводную таблицу.
Выберите пустую ячейку, в которой вы хотите разместить сводную таблицу. По умолчанию в диалоговом окне «Создание сводной таблицы» будет выбрано «Использовать модель данных этой книги». По умолчанию для сводной таблицы будет выбрана выбранная вами ячейка. Щелкните ОК.
В списке полей сводной таблицы будут перечислены все три таблицы. Используйте треугольник слева от таблицы, чтобы развернуть имя таблицы и отобразить поля.
Разверните таблицу данных. Выберите поле «Доход». Он автоматически переместится в область значений. Разверните SectorTable. Выберите поле Сектор. Он переместится в область строк. Разверните RegionTable. Перетащите поле «Регион» в область «Столбцы». Теперь у вас будет сводная таблица, суммирующая данные из трех таблиц.
Заметка
В каждой книге, которую я написал до сегодняшнего дня, я использую разные техники для построения этого отчета. После определения трех таблиц я выбираю ячейку A1 и вставляю, сводную таблицу. Я устанавливаю флажок «Добавить эти данные в модель данных». В списке полей сводной таблицы выберите Все в верхней части списка. Выберите поля для отчета, а затем определите отношения постфактум. Описанная выше техника кажется более плавной и на самом деле требует небольшого предварительного планирования. Людям, которые используют Option Explicit в своем коде VBA, определенно понравится этот метод.
Отношения в модели данных делают Excel больше похожим на Access или SQL Server, но со всеми достоинствами Excel.
Я люблю спрашивать команду Excel об их любимых функциях. Каждую среду я буду делиться одним из их ответов. Спасибо Ашу Шарме за эту идею.
Идея дня в Excel
Я попросил совета у моих друзей-мастеров Excel по поводу Excel. Сегодняшняя мысль задуматься:
«Не ищи, если ты в отношениях»
Джон Михалудис
