Объединение таблиц в excel с помощью power pivot
Добавьте таблицы поиска в свои наборы данных с помощью этого изобретательного дополнения
У вас есть данные и их много. Если вы хотите проанализировать все эти данные, узнайте, как использовать надстройку Power Pivot с Excel для импорта наборов данных, определения связей, создания сводных таблиц и создания сводных диаграмм.
Инструкции в этой статье применяются к Excel 2019, 2016, 2013 и Excel для Office 365.
Как получить надстройку Excel Power Pivot
Power Pivot предоставляет вам мощное приложение для анализа и анализа бизнес-данных. Вам не нужно специализированное обучение для разработки моделей данных и выполнения расчетов. Вам просто нужно включить его, прежде чем вы сможете его использовать.
Откройте Excel .
Выберите Файл > Параметры .
Выберите Надстройки .
Откройте раскрывающееся меню Управление и выберите Надстройки COM .
Выберите Перейти .
Выберите Microsoft Power Pivot для Excel .
Выберите ОК . Вкладка Power Pivot добавлена в Excel.
Следуйте вместе с учебником
Если вы хотите быстро приступить к работе с Power Pivot, учитесь на примере. У Microsoft есть несколько примеров наборов данных, доступных для бесплатной загрузки, которые содержат необработанные данные, модель данных и примеры анализа данных. Это отличные инструменты обучения, которые позволяют понять, как профессионалы анализируют большие данные.
В этом руководстве используется образец рабочей книги Microsoft Student Data Model. В первой заметке на странице вы найдете ссылку для загрузки учебного пособия и заполненной модели данных.
Данные в этом образце книги Excel имеют следующее:
- Рабочая тетрадь содержит четыре рабочих листа.
- Каждый лист содержит связанные данные, то есть, по крайней мере, один заголовок столбца на листе соответствует заголовку столбца в другом листе.
- Данные в каждом рабочем листе форматируются в виде таблицы.
- Каждая ячейка в таблице содержит данные. В таблицах нет пустых ячеек, строк или столбцов.
Есть другие примеры наборов данных на веб-сайте Microsoft. Изучите эти учебные ресурсы:
- Загрузите данные из базы данных Microsoft Access, в которой описаны олимпийские медали.
- Загрузите три образца Business Intelligence, которые показывают, как использовать Power Pivot для импорта данных, создания отношений, создания сводных таблиц и проектирования сводных диаграмм.
Перед использованием любого набора данных очистите его. Используйте функцию CLEAN в Excel, чтобы избавиться от непечатных символов, запустить проверку орфографии, удалить дублирующиеся строки данных, преобразовать числа и даты в нужный формат и перегруппировать данные.
Как добавить данные в файл Excel и построить модель данных
Вы собрали данные, которые вам нужны. Теперь пришло время импортировать ваши наборы данных в Excel и автоматически создать модель данных. Модель данных похожа на реляционную базу данных и предоставляет табличные данные, используемые в сводных таблицах и сводных диаграммах.
Если вам нужны данные для школьного задания, рабочего проекта или для выполнения этого учебного руководства, вы найдете отличные общедоступные наборы данных на GitHub.
Чтобы импортировать данные Excel в модель данных Power Pivot:
Откройте пустой лист и сохраните файл с уникальным именем.
Выберите Данные , затем выберите Получить данные > Из файла > Из рабочей книги , чтобы открыть Импорт данных диалоговое окно.
В Excel 2013 выберите Power Query > Получить внешние данные и выберите источник данных.
Перейдите в папку, содержащую файл Excel, выберите файл, затем выберите Импорт , чтобы открыть навигатор.
Установите флажок Выбрать несколько элементов .
Выберите таблицы, которые вы хотите импортировать.
При импорте двух или более таблиц Excel автоматически создает модель данных.
Выберите Загрузить , чтобы импортировать таблицы данных в модель данных.
Чтобы убедиться, что импорт прошел успешно и модель данных была создана, перейдите в раздел Данные и в группе Инструменты данных выберите Перейти в окно Power Pivot .
Окно Power Pivot отображает ваши данные в формате рабочего листа и состоит из трех основных областей: таблица данных, область расчета и вкладки таблицы данных.
Вкладки в нижней части окна Power Pivot соответствуют каждой из импортированных таблиц.
Закройте окно Power Pivot.
Если вы хотите добавить новые данные в модель данных, в окне Excel перейдите на Power Pivot и выберите Добавить в модель данных . Данные отображаются в виде новой вкладки в окне Power Pivot.
Создание связей между таблицами с помощью Power Pivot Excel
Теперь, когда у вас есть модель данных, пришло время создать отношения между каждой из таблиц данных.
Выберите Power Pivot , затем выберите Управление , чтобы открыть окно Power Pivot.
Выберите Главная , затем выберите Вид диаграммы .
Импортированные таблицы отображаются в виде отдельных блоков в представлении схемы. Перетащите, чтобы переместить таблицы в другое место. Перетащите угол окна, чтобы изменить его размер.
Перетащите заголовок столбца из одной таблицы в другую или в таблицы, содержащие одинаковый заголовок столбца.
Продолжайте сопоставлять заголовки столбцов.
Выберите Главная , затем выберите Просмотр данных .
Как создавать сводные таблицы
Когда вы используете Power Pivot для создания модели данных, большая часть тяжелой работы, включающей сводные таблицы и сводные диаграммы, была выполнена для вас. Отношения, которые вы создали между таблицами в вашем наборе данных, используются для добавления полей, которые вы будете использовать для создания сводных таблиц и сводных диаграмм.
В окне Power Pivot выберите Главная , затем выберите Сводная таблица .
В диалоговом окне Создать сводную таблицу выберите Новый лист , затем выберите ОК .
На панели Поля сводной таблицы выберите поля, которые нужно добавить в сводную таблицу. В этом примере создается сводная таблица, которая содержит имя учащегося и его среднюю оценку.
Чтобы отсортировать данные сводной таблицы, перетащите поле в область «Фильтры». В этом примере поле «Имя класса» добавляется в область «Фильтры», поэтому список можно отфильтровать, чтобы показать среднюю оценку ученика для класса.
Чтобы изменить метод расчета, используемый полем в области «Значения», выберите раскрывающийся список рядом с именем поля и выберите Настройки поля значения . В этом примере сумма оценки была изменена на среднюю оценку.
Проанализируйте ваши данные. Поэкспериментируйте с фильтрами и отсортируйте данные, используя стрелки раскрывающегося списка заголовков столбцов.
Преобразовать сводную таблицу в сводную диаграмму
Если вы хотите визуализировать данные сводной таблицы, превратите сводную таблицу в сводную диаграмму.
- Выберите сводную таблицу, затем перейдите в Анализ инструментов сводной таблицы .
- Выберите Сводная диаграмма , чтобы открыть диалоговое окно Вставить диаграмму .
- Выберите диаграмму, затем нажмите ОК .
Создать сводные диаграммы
Если вы предпочитаете анализировать данные в визуальном формате, создайте сводную диаграмму.
В окне Power Pivot выберите Главная , затем выберите стрелку раскрывающегося списка Сводная таблица . Появится список параметров.
Выберите Сводная диаграмма .
Выберите Новый лист и выберите ОК . Заполнитель PivotChart появится на новом листе.
Перейдите на страницу Анализ инструментов сводной диаграммы и выберите Список полей , чтобы отобразить панель полей сводной диаграммы.
Перетащите поля для добавления в сводную диаграмму. В этом примере создается сводная диаграмма, показывающая среднюю оценку для классов, отфильтрованных по семестрам.
Проанализируйте ваши данные. Поэкспериментируйте с фильтрами и отсортируйте данные с помощью стрелок раскрывающегося списка заголовков столбцов.
Power Pivot Особенности
Что делает Power Pivot сильным инструментом, так это набор его функций. Вы узнаете о различных функциях Power Pivot в главе «Функции Power Pivot».
Данные Power Pivot из различных источников
Power Pivot может сопоставлять данные из различных источников данных для выполнения необходимых расчетов. Вы узнаете, как получить данные в Power Pivot, в главе «Загрузка данных в Power Pivot».
Модель данных Power Pivot
Управление моделью данных и отношениями
Вам необходимо знать, как вы можете управлять таблицами данных в модели данных и связями между ними. Подробнее об этом вы узнаете в главе «Управление моделью данных Power Pivot».
Создание Power Pivot Tables и Power Pivot Charts
Power PivotTables и Power Pivot Chart предоставляют вам возможность проанализировать данные, чтобы прийти к выводам и / или решениям.
Основы DAX
Изучение и представление данных Power Pivot
Вы можете изучить данные Power Pivot, которые находятся в модели данных, с помощью Power PivotTables и Power Pivot Charts. В этом уроке вы узнаете, как вы можете исследовать данные и сообщать о них.
Иерархии
Вы можете определить иерархии данных в таблице данных, чтобы было легко обрабатывать связанные поля данных вместе в Power PivotTables. Вы узнаете подробности создания и использования Иерархий в главе «Иерархии в Power Pivot».
Эстетические отчеты
Вы можете создавать эстетические отчеты о своем анализе данных с помощью Power Pivot Charts и / или Power Pivot Charts. У вас есть несколько вариантов форматирования, чтобы выделить важные данные в отчетах. Отчеты являются интерактивными по своей природе, что позволяет человеку, просматривающему компактный отчет, быстро и легко просматривать любую необходимую информацию.
Эти подробности вы узнаете в главе «Эстетические отчеты с данными Power Pivot».
Power Pivot в Excel предоставляет модель данных, соединяющую различные источники данных, на основе которых можно анализировать, визуализировать и исследовать данные. Простой в использовании интерфейс, предоставляемый Power Pivot, позволяет человеку с практическим опытом работы в Excel легко загружать данные, управлять данными как таблицами данных, создавать связи между таблицами данных и выполнять необходимые вычисления для получения отчета. ,
В этой главе вы узнаете, что делает Power Pivot сильным и востребованным инструментом для аналитиков и лиц, принимающих решения.
Power Pivot на ленте
Если у вас Excel 2010, вкладка POWERPIVOT может не отображаться на ленте, если вы еще не включили надстройку Power Pivot.
Надстройка Power Pivot
В поле Управление выберите Надстройки COM из раскрывающегося списка.
Проверьте Power Pivot и нажмите ОК.
В поле Управление выберите Надстройки COM из раскрывающегося списка.
Проверьте Power Pivot и нажмите ОК.
Что такое Power Pivot?
Удобный интерфейс Power Pivot в окне PowerPivot позволяет выполнять операции с данными без знания какого-либо языка запросов к базе данных. Затем вы можете создать отчет о вашем анализе в течение нескольких секунд. Отчеты являются универсальными, динамичными и интерактивными и позволяют вам дополнительно исследовать данные, чтобы получить представление и прийти к выводам / решениям.
Данные, с которыми вы работаете в Excel и в окне Power Pivot, хранятся в аналитической базе данных внутри книги Excel, а мощный локальный механизм загружает, запрашивает и обновляет данные в этой базе данных. Поскольку данные находятся в Excel, они сразу доступны для сводных таблиц, сводных диаграмм, Power View и других функций Excel, которые вы используете для агрегирования и взаимодействия с данными. Представление данных и интерактивность обеспечиваются Excel, а объекты данных и презентации Excel содержатся в одном файле рабочей книги. Power Pivot поддерживает файлы размером до 2 ГБ и позволяет работать с 4 ГБ данных в памяти.
Мощные функции в Excel с Power Pivot
Функции Power Pivot бесплатны в Excel. Power Pivot повысил производительность Excel благодаря мощным функциям, которые включают следующее:
Способность обрабатывать большие объемы данных, сжатые в небольшие файлы, с удивительной скоростью.
Фильтруйте данные и переименовывайте столбцы и таблицы при импорте.
Организовать таблицы в отдельные страницы с вкладками в окне Power Pivot по сравнению с таблицами Excel, распределенными по всей книге или по нескольким таблицам в одной рабочей таблице.
Создайте связи между таблицами, чтобы совместно анализировать данные в таблицах. До Power Pivot приходилось полагаться на интенсивное использование функции VLOOKUP, чтобы объединить данные в одну таблицу перед таким анализом. Раньше это было трудоемким и подверженным ошибкам.
Добавьте мощность в простую сводную таблицу со многими дополнительными функциями.
Предоставить язык выражений анализа данных (DAX) для написания расширенных формул.
Добавьте вычисляемые поля и вычисляемые столбцы в таблицы данных.
Создайте KPI для использования в сводных таблицах и отчетах Power View.
Способность обрабатывать большие объемы данных, сжатые в небольшие файлы, с удивительной скоростью.
Фильтруйте данные и переименовывайте столбцы и таблицы при импорте.
Организовать таблицы в отдельные страницы с вкладками в окне Power Pivot по сравнению с таблицами Excel, распределенными по всей книге или по нескольким таблицам в одной рабочей таблице.
Создайте связи между таблицами, чтобы совместно анализировать данные в таблицах. До Power Pivot приходилось полагаться на интенсивное использование функции VLOOKUP, чтобы объединить данные в одну таблицу перед таким анализом. Раньше это было трудоемким и подверженным ошибкам.
Добавьте мощность в простую сводную таблицу со многими дополнительными функциями.
Предоставить язык выражений анализа данных (DAX) для написания расширенных формул.
Добавьте вычисляемые поля и вычисляемые столбцы в таблицы данных.
Создайте KPI для использования в сводных таблицах и отчетах Power View.
Вы поймете особенности Power Pivot подробно в следующей главе.
Использование Power Pivot
Вы можете использовать Power Pivot для следующих целей:
Для выполнения мощного анализа данных и создания сложных моделей данных.
Быстрое объединение больших объемов данных из нескольких разных источников.
Для анализа информации и обмена знаниями в интерактивном режиме.
Для написания расширенных формул на языке выражений анализа данных (DAX).
Создать ключевые показатели эффективности (KPI).
Для выполнения мощного анализа данных и создания сложных моделей данных.
Быстрое объединение больших объемов данных из нескольких разных источников.
Для анализа информации и обмена знаниями в интерактивном режиме.
Для написания расширенных формул на языке выражений анализа данных (DAX).
Создать ключевые показатели эффективности (KPI).
Моделирование данных с помощью Power Pivot
Power Pivot предоставляет расширенные возможности моделирования данных в Excel. Данные в Power Pivot управляются в модели данных, которая также называется базой данных Power Pivot. Вы можете использовать Power Pivot, чтобы получить новое представление о ваших данных.
Вы можете создавать отношения между таблицами данных, чтобы вы могли выполнять общий анализ данных в таблицах. С DAX вы можете писать расширенные формулы. Вы можете создавать вычисляемые поля и вычисляемые столбцы в таблицах данных в модели данных.
Вы можете определить иерархии в данных, чтобы использовать их везде в книге, включая Power View. Вы можете создавать KPI для использования в сводных таблицах и отчетах Power View, чтобы сразу увидеть, является ли производительность включенной или выключенной для одной или нескольких метрик.
Бизнес-аналитика с Power Pivot
Вы можете предоставить доступ к своей книге в среду SharePoint, в которой включены службы Excel. На сервере SharePoint службы Excel обрабатывают и отображают данные в окне браузера, где другие могут анализировать данные.
В этой главе вы получите краткий обзор функций Power Pivot, которые подробно будут показаны позже.
Загрузка данных из внешних источников
Вы можете загрузить данные в модель данных из внешних источников двумя способами:
Загрузите данные в Excel, а затем создайте модель данных Power Pivot.
Загрузка данных непосредственно в модель данных Power Pivot.
Загрузите данные в Excel, а затем создайте модель данных Power Pivot.
Загрузка данных непосредственно в модель данных Power Pivot.
Второй способ более эффективен благодаря эффективному способу обработки данных в памяти Power Pivot.
Для получения более подробной информации см. Главу «Загрузка данных в Power Pivot».
Окно Excel и Power Pivot Window
Вам не нужно иметь данные в таблицах Excel при импорте данных из внешних источников. Если у вас есть данные в виде таблиц Excel в книге, вы можете добавить их в модель данных, создавая таблицы данных в модели данных, которые связаны с таблицами Excel.
При создании сводной таблицы или сводной диаграммы из окна Power Pivot они создаются в окне Excel. Однако данные по-прежнему управляются из модели данных.
Вы всегда можете легко переключаться между окном Excel и окном Power Pivot в любое время.
Модель данных
Подробнее о модели данных вы узнаете в главе «Общие сведения о модели данных (Power Pivot Database)».
Оптимизация памяти
Модель данных Power Pivot использует хранилище xVelocity, которое сильно сжимается при загрузке данных в память, что позволяет хранить в памяти сотни миллионов строк.
Таким образом, если вы загружаете данные непосредственно в модель данных, вы будете делать это в эффективной сильно сжатой форме.
Компактный размер файла
Если данные загружаются непосредственно в модель данных, при сохранении файла Excel они занимают очень мало места на жестком диске. Вы можете сравнить размеры файлов Excel, первый с загрузкой данных в Excel, а затем с созданием модели данных, а второй с загрузкой данных непосредственно в модель данных, пропустив первый шаг. Второй будет в 10 раз меньше первого.
Power PivotTables
Вы можете создать Power PivotTables из окна Power Pivot. Созданные таким образом сводные таблицы основаны на таблицах данных в модели данных, что позволяет объединять данные из связанных таблиц для анализа и составления отчетов.
Power PivotCharts
Вы можете создать Power PivotCharts из окна Power Pivot. Созданные таким образом сводные диаграммы основаны на таблицах данных в модели данных, что позволяет объединять данные из связанных таблиц для анализа и составления отчетов. Power PivotCharts обладает всеми функциями сводных диаграмм Excel и многими другими, такими как кнопки полей.
Вы также можете иметь комбинации Power PivotTable и Power PivotChart.
DAX Language
Преимущество Power Pivot заключается в языке DAX, который можно эффективно использовать в модели данных для выполнения расчетов с данными в таблицах данных. У вас могут быть рассчитанные столбцы и вычисляемые поля, определенные DAX, которые можно использовать в сводных таблицах и сводных диаграммах питания.
В этой главе мы научимся загружать данные в Power Pivot.
Вы можете загрузить данные в Power Pivot двумя способами:
Загрузить данные в Excel и добавить их в модель данных
Загружайте данные непосредственно в PowerPivot, заполняя модель данных, которая является базой данных PowerPivot.
Загрузить данные в Excel и добавить их в модель данных
Загружайте данные непосредственно в PowerPivot, заполняя модель данных, которая является базой данных PowerPivot.
Если вам нужны данные для Power Pivot, сделайте это вторым способом, даже если Excel даже не знает об этом. Это потому, что вы будете загружать данные только один раз, в сильно сжатом формате. Чтобы понять разницу, предположим, что вы загружаете данные в Excel, сначала добавляя их в модель данных, размер файла, скажем, 10 МБ.
Если вы загружаете данные в PowerPivot и, следовательно, в модель данных, пропуская дополнительный шаг Excel, размер вашего файла может составлять всего 1 МБ.
Источники данных, поддерживаемые Power Pivot
Предположим, у вас есть три таблицы в книге, теперь вы хотите объединить эти таблицы в одну таблицу на основе соответствующих ключевых столбцов, чтобы получить результат, как показано на скриншоте ниже. Это может быть сложной задачей для большинства из нас, но, пожалуйста, не волнуйтесь, в этой статье я расскажу о некоторых методах решения этой проблемы.
 |  |  | ||
 | ||||
Объединение двух или более таблиц в одну на основе ключевых столбцов с помощью функции Power Query (Excel 2016 и более поздние версии)
Для использования Power Query функция для объединения нескольких таблиц в одну на основе соответствующих ключевых столбцов, выполните следующие действия шаг за шагом:
1. Если диапазоны данных не относятся к табличному формату, сначала вы должны преобразовать их в таблицы, выберите диапазон, а затем нажмите Вставить > Настольные, В Создать таблицу диалоговое окно, нажмите OK кнопку см. скриншоты:
 |  |  |
2. После создания таблиц для каждого из диапазонов данных выберите первую таблицу и щелкните Данные > Из таблицы / диапазона, см. снимок экрана:
3, Затем в Table1-Power Query Editor окна, нажмите Главная > Закрыть и загрузить > Закрыть и загрузить в, см. снимок экрана:
4. В выскочившем Импортировать данные, наведите на Только создать соединение , а затем нажмите OK кнопку, см. снимок экрана:
5. Затем создается первая таблица соединений в Запросы и связи Теперь повторите шаги 2–4, описанные выше, для создания таблиц соединений для двух других таблиц, которые вы хотите объединить. Когда закончите, вы получите скриншот, показанный ниже:
6. После создания соединений для таблиц вы должны объединить первые две таблицы в одну, нажмите Данные > Получить данные > Объединить запросы > идти, см. снимок экрана:
7. В идти диалоговом окне выполните следующие действия:
8, Затем нажмите OK кнопку в Редактор запросов Merge1-Power окна, нажмите кнопку, см. снимок экрана:
9. А затем в развернутом поле:
- (1.) Оставить значение по умолчанию Расширять выбран вариант;
- (2.) В Выбрать все столбцы поле со списком, отметьте имя столбца, который вы хотите объединить с первой таблицей;
- (3.) Снимите флажок Использовать исходное имя столбца в качестве префикса опцию.
10, Затем нажмите OK Кнопка, теперь вы можете видеть, что данные столбца во второй таблице были добавлены в первую таблицу, см. снимок экрана:
11. На этом этапе первая таблица и вторая таблица были успешно объединены ключевым столбцом, теперь вам нужно импортировать эту объединенную таблицу в новую таблицу подключения слияния, нажмите Главная > Закрыть и загрузить > Закрыть и загрузить в, см. снимок экрана:
12. В выскочившем Импортировать данные диалоговое окно, выберите Только создать соединение и нажмите OK кнопку, см. снимок экрана:
13. Здесь вы можете увидеть, что создается соединение с именем Слияние1 в Запросы и связи панель, см. снимок экрана:
14. После объединения первых двух таблиц теперь вам нужно объединить новые Слияние1 таблица с третьей таблицей, нажмите Данные > Получить данные > Объединить запросы > идтиИ в идти диалоговом окне выполните следующие операции:
15. А затем нажмите OK, В Редактор запросов Merge2-Power окна, нажмите и в развернутом поле отметьте имя столбца, который вы хотите объединить из третьей таблицы, и снимите флажок Использовать исходное имя столбца в качестве префикса вариант, см. снимок экрана:
16, Затем нажмите OK Кнопка, вы получите объединенную таблицу с тремя таблицами вместе, и теперь вы должны импортировать эту объединенную таблицу на новый лист таблицы, нажмите Главная > Закрыть и загрузить > Закрыть и загрузить в, см. снимок экрана:
17. В Импортировать данные диалоговое окно, выберите Настольные и Новый рабочий лист варианты, см. снимок экрана:
18. Наконец, новая таблица с данными из трех таблиц на основе соответствующих ключевых столбцов была создана на новом листе, как показано ниже:
Советы:
1. Если ваши исходные данные изменяются, вам нужно, чтобы объединенная таблица была также изменена, щелкните одну ячейку в объединенной таблице, а затем нажмите запрос > обновление чтобы получить обновленные данные. Смотрите скриншот:
2. С помощью этой функции вы также можете объединить гораздо больше таблиц, повторив вышеуказанные шаги.
Объединяйте две или более таблиц в одну на основе ключевых столбцов с удивительной функцией
В этом разделе я покажу полезную функцию - Слияние таблиц of Kutools for Excel, с помощью этой функции вы можете быстро объединить две или более таблиц в одну на основе ключевых столбцов.
Например, у меня есть две таблицы, которые нужно объединить, как показано на скриншоте ниже:
 |  |  |
Советы: Чтобы применить это Слияние таблиц функция, во-первых, вы должны скачать Kutools for Excel, а затем быстро и легко примените эту функцию.
После установки Kutools for Excel, пожалуйста, сделайте так:
1. Нажмите Kutools Plus > Слияние таблиц, см. снимок экрана:
2. На первом этапе Слияние таблиц мастера, выберите основную таблицу и таблицу поиска отдельно, (Внимание: данные столбца в поисковой таблице будут добавлены в основную таблицу), см. снимок экрана:
3. На шаге 2 из Слияние таблиц мастера, проверьте имя ключевого столбца, на основе которого вы хотите объединить таблицы, см. снимок экрана:
4. Нажмите Следующая кнопку на шаге 3 Слияние таблиц мастер, пожалуйста, нажмите Следующая кнопку напрямую, см. снимок экрана:
5. Затем, на шаге 4 мастера, проверьте имя столбца из таблицы поиска, которую вы хотите добавить в основную таблицу, см. Снимок экрана:
6. Продолжайте нажимать Следующая на последнем шаге мастера в Добавить поле со списком опций, отметьте Добавить несовпадающие строки в конец основной таблицы вариант, в то же время вы также можете выбрать операции для повторяющихся строк по мере необходимости. Смотрите скриншот:
7, Затем нажмите Завершить Кнопка, соответствующий столбец данных в таблице поиска будет добавлен в основную таблицу, как показано ниже:
| | |
Советы:
1. С помощью этой функции вы можете обновлять данные в основной таблице другой таблицей по мере необходимости.
2. Чтобы объединить больше таблиц, вам просто нужно выбрать результат новых объединенных данных в качестве основной таблицы, а затем повторить вышеуказанные шаги.
В данной статье я хочу рассказать о некоторых возможностях бесплатной и крайне полезной, но пока еще мало известной надстройки над MS Excel под названием Power Query.
Power Query позволяет забирать данные из самых разных источников (таких как csv, xls, json, текстовых файлов, папок с этими файлами, самых разных баз данных, различных api вроде Facebook opengraph, Google Analytics, Яндекс.Метрика, CallTouch и много чего еще), создавать повторяемые последовательности обработки этих данных и загружать их внутрь таблиц Excel или самого data model.
И вот под катом вы можете найти подробности всего этого великолепия возможностей.
Совместимость и технические подробности
Power Query доступен бесплатно для всех версий Windows Excel 2010, 2013 и встроен по умолчанию в Windows Excel 2016. Для пользователей MacOS X Power Query недоступен (впрочем, даже без этого маковский Excel отвратителен на ощупь и продвинутые пользователи, включая меня, чаще всего работают с нормальным Excel через Parallels или запуская его на удаленной виндовой машинке).
Также, Power Query встроен в новый продукт для бизнес аналитики — Power BI, а еще, ходят слухи, что Power Query будет появляться и в составе других продуктов от Microsoft. Т.е. Power Query ждет светлое будущее и самое время для адептов технологий Microsoft (и не только) заняться его освоением.
Как оно работает
После установки Power Query в интерфейсе Excel 2010–2013 появляется отдельная одноименная вкладка.
В новом Excel 2016 функционал Power Query доступен на вкладке Data (данные), в блоке “Get & Transform”.
Сначала, в интерфейсе Excel мы выбираем конкретный источник данных, откуда нам их нужно получить, и перед нами открывается окошко самого Power Query с предпросмотром первых строчек загруженных данных (область 1). В верхней части окошка располагается Ribbon с командами по обработке данных (область 2). И в правой части экрана (область 3) у нас расположена панель с последовательностью всех действий, которые применяются к данным.
Возможности Power Query
У Power Query очень много возможностей и я хочу остановиться на некоторых из числа моих любимых.
Как я уже писал выше, Power Query замечателен тем, что позволяет подключаться к самым разным источникам данных. Так он позволяет загружать данные из CSV, TXT, XML, json файлов. Притом процесс выбора опций загрузки тех-же CSV файлов гибче и удобнее, чем он реализован штатными средствами Excel: кодировка автоматически выбирается часто правильно и можно указать символ разделителя столбцов.
Объединение файлов лежащих в папке
Power Query умеет забирать данные из указанной папки и объединять их содержимое в единые таблицы. Это может быть полезно, например, если вам периодически приходят какие-то специализированные отчеты за отдельный промежуток времени, но данные для анализа нужны в общей таблице. Гифка
Текстовые функции
- Разделить столбец по символу или по количеству символов. И в отличие от Excel можно задать максимальное количество столбцов, а также направление откуда нужно считать символы — слева, справа.
- Изменить регистр ячеек в столбце
- Подсчитать количество символов в ячейках столбца.
Числовые функции
К столбцам с числовыми значениями по нажатию на кнопки на Ribbon можно применять:
- Арифметические операции
- Возводить в степени, вычислять логарифмы, факториалы, корни
- Тригонометрические операции
- Округлять до заданных значений
- Определять четность и т.д.
Функции для работы с датами, временем и продолжительностью
К столбцам со значениями даты и времени по нажатию на кнопки на Ribbon можно применять:
- Автоматическое определение формата вписанной даты (в excel c этим большая боль)
- Извлекать в один клик номер месяца, дня недели, количество дней или часов в периоде и т.п.
Unpivot — Pivot
В интерфейсе Power Query есть функция “Unpivot”, которая в один клик позволяет привести данные с одной метрикой разложенные по столбцам по периодам к форме, которая будет удобна для использования в сводных таблицах (понимаю что трудно написал — смотрите пример). Также, есть функция с обратным действие Pivot. Гифка
Операция Merge — смерть ВПР
Функция ВПР (VLOOKUP) одна из наиболее используемых функций в MS Excel. Она позволяет подтягивать данные в одну таблицу из другой таблицы по единому ключу. И вот как раз для этой функции в Power Query есть гораздо более удобная альтернатива — операция Merge. При помощи этой операции соединение таблиц нескольких таблиц в одну по ключу (по простому или по составному ключу, когда соответствие нужно находить по нескольким столбцам) выполняется буквально в 7 кликов мыши без ввода с клавиатуры.
Операция Merge — это аналог join в sql, и ее можно настроить чтобы join был разных типов — Inner (default), Left Outer, Right Outer, Full Outer.
Upd.Мне тут подсказали, что Power Query не умеет делать Aproximate join, а впр умеет. Чистая правда, из коробки альтернатив нет. Гифка
Подключение к различным базам данных. Query Folding.
Power Query также замечателен тем, что умеет цепляться к самым разным базам данных — от MS SQL и MySQL до Postgres и HP Vertica. При этом, вам даже не нужно знать SQL или другой язык базы данных, т.к. предпросмотр данных отображается в интерфейсе Power Query и все те операции, которые выполняются в интерфейсе прозрачно транслируются в язык запросов к базе данных.
А еще в Power Query есть понятие Query Folding: если вы подключены к совместимой базе данных (на текущий момент это MS SQl), то тяжелые операции по обработке данных Power Query будет стараться выполнить на серверной стороне и забирать к себе лишь обработанные данные. Эта возможность радикально улучшает быстродействие многих обработок.
Язык программирования “М”
Надстройка Power Query — это интерпретатор нового, скриптового, специализированного для работы с данными, языка программирования М.
На каждое действие, которое мы выполняем с данными в графическом интерфейсе Power Query, в скрипт у нас пишется новая строчка кода. Отражая это, в панели с последовательностью действий (область 3), создается новый шаг с говорящим названием. Благодаря этому, используя панель с последовательностью действий, мы всегда можем посмотреть как выглядят у нас данные на каждом шаге обработки, можем добавить новые шаги, изменить настройки применяемой операции на конкретном шаге, поменять их порядок или удалить ненужные шаги. Гифка
Также, мы всегда можем посмотреть и отредактировать сам код написанного скрипта. И выглядеть будет он примерно так:
Язык M, к сожалению, не похож ни на язык формул в Excel, ни на MDX и, к счастью, не похож на Visual Basic. Однако, он очень прост в изучении и открывает огромные возможности по манипуляции данными, которые недоступны с использованием графического интерфейса.
Загрузка данных из Яндекс.Метрики, Google Analytics и прочих Api
Немного овладев языком “M” я смог написать программки в Power Query, которые умеют подключаться к API Яндекс.Метрики и Google Analytics и забирать оттуда данные с задаваемыми настройками. Программки PQYandexMetrika и PQGoogleAnalytics я выложил в опенсорс на гитхаб под лицензией GPL. Призываю пользоваться. И я буду очень рад, если эти программы будут дорабатываться энтузиастами.
Для Google Analytics подобного рода экспортеров в разных реализациях достаточно много, но вот для Яндекс.Метрики, насколько я знаю, мой экспортер был первым публично доступным, да еще и бесплатным :)
Power Query умеет формировать headers для post и get запросов и забирать данные из интернета. Благодаря этому, при должном уровне сноровки, Power Query можно подключить практически к любым API. В частности, я для своих исследований дергаю данные по телефонным звонкам клиентов из CallTouch API, из API сервиса по мониторингу активности за компьютером Rescuetime, занимаюсь парсингом нужных мне веб-страничек на предмет извлечения актуальной информации.
Еще раз про повторяемость и про варианты применения
Как я уже писал выше, скрипт Power Query представляет собой повторяемую последовательность манипуляций, применяемых к данным. Это значит, что однажды настроив нужную вам обработку вы сможете применить ее к новым файлам изменив всего один шаг в скрипте — указав путь к новому файлу. Благодаря этому можно избавиться от огромного количества рутины и освободить время для продуктивной работы — анализа данных.
Я занимаюсь веб-аналитикой и контекстной рекламой. И так уж получилось, что с момента, как я познакомился с Power Query в ее интерфейсах я провожу больше времени, чем в самом Excel. Мне так удобнее. Вместе с тем возросло и мое потребление другой замечательной надстройки в MS Excel — PowerPivot.
- разбираю семантику для Толстых проектов,
- Делаю частотные словари,
- Создаю веб-аналитические дашборды и отчеты для анализа конкретных срезов,
- Восстанавливаю достижение целей в системах веб-аналитики, если они не настроены на проекте,
- Сглаживаю прогноз вероятности методами Андрея Белоусова (+Байеса:),
- Делаю аудит контекстной рекламы на данных из K50 статистика,
- И много других разных ad-hoc analysis задач, которые нужно сделать лишь однажды
Вот bi систему, про которую я рассказывал на Yac/M 2015 (видео) я делал полнстью при помощи Power Query и загружал данные внутрь PowerPivot.
Пару слов про локализацию
На сайте Microsoft для пользователей из России по умолчанию скачивается Power Query с переведенным на русским язык интерфейсом. К счастью, локализаторы до перевода на русский языка программирования (как это сделано с языком формул в excel) не добрались, однако жизнь пользователям неоднозначными переводами сильно усложнили. И я призываю вас скачивать, устанавливать и пользоваться английской версией Power Query. Поверьте, она будет гораздо понятнее.
Читайте также: