Как использовать Power Query и Power Pivot в Excel как профессионал

Если вы хотите стать настоящим пользователем электронных таблиц, вам нужно узнать о функциях Power Query и Power Pivot в Excel. Хотя вы можете многое сделать только с Excel, использование этих встроенных “мощных” функций превратит вас в продвинутого профессионального пользователя Excel. В этом руководстве вы узнаете, как использовать Power Query для импорта потенциально сотен различных файлов с миллионами строк и использовать Power Pivot для генерации сложных анализов на огромных моделях данных, которые вы импортируете.

Содержание

• 1. Импорт данных из нескольких файлов
• 2. Фильтрация импортированных файлов и строк
• 3. Загрузка отфильтрованных данных в модель данных
• 4. Анализ данных с помощью сводной таблицы
• 5. Представление ваших данных с помощью PowerChart
• Часто задаваемые вопросы

Хотите защитить свою работу от любопытных глаз? Узнайте, как добавить пароль к вашей книге Excel.

1. Импорт данных из нескольких файлов

С помощью Power Query вы можете импортировать данные из различных файлов в одной папке. Однако будьте внимательны, так как вы все равно должны убедиться, что все данные следуют одному и тому же формату. Сохраните количество столбцов и названия заголовков столбцов одинаковыми и убедитесь, что тип данных для значений в каждом столбце согласован.

Для этого руководства мы используем пять файлов, которые содержат ~100 миллионов строк данных из Kaggle. Скачайте их, если хотите следовать за нами, или используйте свои собственные данные. Учитывая это, вот шаги для импорта данных:

1. Нажмите на вкладку “Данные” на ленте.
2. Выберите “Получить данные -> Из файла -> Из папки”, затем перейдите к вашей папке и выберите ее.

1. Вы должны увидеть новое окно. В нашем случае у нас есть пять тестовых файлов, названных в соответствии с их размером: “adult10m” содержит 10 миллионов строк данных, “adult1m” содержит 1 миллион строк, “adult100k” содержит 100 000 строк и так далее. Нажмите “Преобразовать данные”, чтобы открыть редактор Power Query.

2. Фильтрация импортированных файлов и строк

В редакторе Power Query вы можете применять фильтры, чтобы импортировать только те файлы, которые соответствуют установленным вами критериям, что очень полезно для исключения источников данных, которые вам не нужны.

1. У нас есть четыре файла без расширения, но один файл с расширением .DATA. Давайте удалим странный файл, нажав на стрелку рядом с “Расширение”.

1. Мы видим список всех расширений файлов. В этом примере это просто (пусто) и .data. Снимите отметку с расширений, которые вам не нужны.

1. Каждый примененный фильтр появится в разделе “Примененные шаги”.
2. Мы также можем фильтровать по имени файла, нажав на стрелку рядом с “Имя”.

1. Допустим, мы хотим исключить любые файлы, содержащие “k”, такие как “adult100k”, потому что мы не хотим работать с маленькими файлами, содержащими менее одного миллиона строк. Мы можем применить текстовый фильтр. Есть много вариантов, но для наших целей фильтр “Не содержит” - это то, что нам нужно.

1. Введите текст, который вы не хотите видеть в имени файла.

1. Прокрутите немного вправо, пока не увидите столбцы “Дата изменения” и “Дата создания”. Давайте отфильтруем, чтобы выбрать только файлы, созданные в определенном диапазоне времени. Нажмите на стрелку рядом с “Дата создания”.

1. Выберите “Фильтры даты/времени -> Между”.

1. Введите значения времени для двух условий и подтвердите, нажав “ОК”.

1. Мы завершили фильтрацию наших данных на уровне каждого файла.
2. Давайте объединим оставшиеся файлы, чтобы мы могли фильтровать сами данные. Нажмите на двойные стрелки рядом с заголовком “Содержимое”.

1. Теперь, когда вы можете видеть все значения для отдельных строк, каждая строка имеет дополнительный столбец “Source.Name”, который указывает файл, из которого она произошла.

1. Чтобы дополнительно улучшить наши данные: поскольку наши CSV-файлы изначально не имели названий заголовков столбцов, вы можете щелкнуть правой кнопкой мыши по некоторым заголовкам столбцов и выбрать “Переименовать”, чтобы дать им подходящее имя.

1. Фильтры работают и здесь. Мы можем применить фильтр диапазона чисел. Например, нажмите на стрелку рядом с заголовком столбца “Возраст”, затем выберите “Фильтры чисел -> Больше чем”.

1. Введите число, например 30, затем нажмите “ОК”. Все строки с возрастом ниже указанного числа будут исключены.

Совет: при работе с электронными таблицами в Microsoft Excel может быть полезно знать, как переместить столбец.

3. Загрузка отфильтрованных данных в модель данных

Вы можете продолжать очищать и фильтровать данные, но давайте перейдем к загрузке их в модель данных, чтобы начать анализ.

1. Завершите работу с Power Query, нажав “Закрыть и загрузить -> Закрыть и загрузить в”.
2. В открывшемся всплывающем окне выберите “Только создать соединение” и отметьте “Добавить эти данные в модель данных”.

1. Подождите, пока данные загрузятся. Это может занять несколько минут, если у вас большой объем данных.

Использование модели данных Power Pivot в Excel

1. После завершения загрузки данных мы можем вносить изменения (например, добавлять новые столбцы) в модель данных, нажав “Данные -> Инструменты данных -> Управление моделью данных”.

1. Вы должны увидеть окно Power Pivot. Давайте добавим столбец с формулой DAX (выражения анализа данных). Формулы DAX очень похожи на формулы Excel, с которыми вы, вероятно, уже знакомы. Прокрутите вправо, пока не увидите последний столбец ваших данных, затем нажмите “Добавить столбец”.
2. Давайте напишем формулу для этого столбца, которая применяет простое вычисление к каждой строке. По сравнению с формулами Excel синтаксис DAX немного отличается. Например, мы можем добавить столбец с названием “Описание” и ввести формулу =CONCATENATE([Column9],[Column10]). Обратите внимание на использование квадратных скобок ([ ]) и доступ через названия заголовков столбцов.

1. Если хотите, вы также можете выполнять дополнительные изменения, такие как переименование или удаление столбцов, щелкнув правой кнопкой мыши по заголовку столбца.

4. Анализ данных с помощью сводной таблицы

После того как вы внесли изменения в модель данных, пришло время анализировать данные в листе Excel с помощью сводной таблицы.

1. Нажмите “Главная -> Сводная таблица” на ленте окна Power Pivot, чтобы открыть окно “Создать сводную таблицу”.

1. Выберите “Новый лист” и нажмите “ОК”.

1. Панель полей сводной таблицы откроется на новом листе в основном окне Excel. В поле под строкой поиска вы должны увидеть ваши источники данных, “kaggle_adult_csv” в этом примере. Нажмите на него, чтобы развернуть и отобразить все его столбцы.

1. Давайте проведем анализ, где мы подсчитаем количество каждого “Описание” в наших данных. Мы можем выбрать столбцы, с которыми хотим работать, нажав на флажок рядом с их названиями. Выберите “Описание” и обратите внимание, как фокус автоматически смещается в область “Строки” ниже. Это Power Pivot демонстрирует свой интеллект – он может точно угадать подходящую область для выборок. Поскольку мы начинаем с пустой сводной таблицы, имеет смысл, что нашим первым выбором являются строки.

1. Перетащите отмеченный столбец “Описание” в пустое пространство в “Значениях”. Обратите внимание, что Power Pivot снова правильно предполагает, что мы хотим “Количество Описания” из-за текстового типа данных. Также наша таблица была обновлена с колонкой, предоставляющей количество каждого вхождения каждого значения “Описание”.

1. Мы можем добавить еще одно измерение к нашему подсчету. Например, перетащите столбец “Возраст” в пустую область “Столбцы”, чтобы добавить больше деталей к таблице, подсчитывая количество описаний по каждому возрасту.

1. Вы также можете добавить возможность фильтрации в таблицу, перетащив столбец “Рабочий класс” в область “Фильтры”. Обратите внимание на новую интерактивную ячейку, которая появляется над нашей таблицей.

1. Нажмите на стрелку рядом с “Все”, затем выберите “Никогда не работал”. Это изменит таблицу так, чтобы в расчетах учитывались только люди с рабочим классом “Никогда не работал”. Вы можете изменить фильтр в любое время, чтобы просмотреть таблицу с другой точки зрения.

Имеете дело с множеством дубликатов в вашей книге Excel? Узнайте, как найти и удалить дубликаты и очистить ваши электронные таблицы.

5. Представление ваших данных с помощью PowerChart

Вы можете создать больше листов, чтобы анализировать ваши данные различными способами. На этот раз давайте создадим более визуально впечатляющий результат с помощью PowerChart.

1. Вернитесь в окно “Power Pivot для Excel”. Если оно закрыто, вы можете открыть его из окна Excel через “Данные -> Инструменты данных -> Управление моделью данных”.
2. Нажмите “Сводная таблица -> Сводная диаграмма -> Новый лист”.

1. В панели справа нажмите на источник данных (“kaggle_adult_csv”), чтобы развернуть его и отобразить все его столбцы.
2. Перетащите и отпустите столбцы из источника данных в одну из четырех областей ниже, чтобы построить диаграмму. Переместите столбец “Тип профессии” в область “Ось (Категории)”.

1. Перетащите “Тип профессии” снова, и на этот раз отпустите его в область “Значения”. Вы сразу заметите, что столбчатая диаграмма автоматически сгенерирована. Она показывает количество каждого типа профессии.

1. Вы также можете перетащить столбец “Этническая принадлежность” в область “Легенда (Серии)”. Вы мгновенно увидите более детализированную диаграмму, которая сравнивает, сколько из каждой этнической группы составляют каждую профессию.

1. Далее мы можем применить фильтры к нашей Легенде, чтобы упростить нашу диаграмму и рассмотреть только определенные значения. Наведите курсор на “Этническая принадлежность”, чтобы появилась стрелка с правой стороны. Нажмите на стрелку.

1. Установите флажки рядом с значениями, которые вы хотите удалить, оставив только два.

1. Давайте попробуем что-то новое. В разделе “Значения” нажмите “Количество Типа профессии”, затем нажмите “Удалить поле”. Перетащите и отпустите столбец “Возраст”, чтобы заменить его.

1. Вы заметите, что значение автоматически определяется как “Сумма Возраста”, но это не очень полезно в реальном мире. Давайте изменим это. Нажмите на него и выберите “Настройки поля значений”.

1. Поскольку возраст - это число, есть много вычислений, которые мы можем применить к этому разделу. Попробуйте выбрать “Среднее”, например.

1. Визуализация обновится, чтобы показать средний возраст для каждой профессии по конкретным этническим группам (которые мы отфильтровали).

Хотите освежить свои знания основ Excel? У нас есть исчерпывающее руководство по всем сочетаниям клавиш для Excel.

Часто задаваемые вопросы

Каковы истоки Power Pivot?

Microsoft представила Power Pivot как отдельное дополнение, предоставляемое SQL Server 2008 R2 от Microsoft, который был выпущен в 2010 году. В то время он назывался “PowerPivot” без пробела. В 2013 году он был переименован в “Power Pivot”. Он стал встроенной функцией Excel только с 2016 года.

Что такое выражения анализа данных и чем они отличаются от формул Excel?

Выражения анализа данных - это язык формул, используемый Power Pivot в Excel и Power BI. По сути, это то, как вы пишете формулы для вычислений в вышеупомянутых приложениях: утверждения, синтаксис и функции, которые вы используете.

Основное преимущество DAX перед формулами Excel заключается в том, что он предназначен для работы с агрегированными данными. Стандартные формулы Excel могут выполнять вычисления по строкам (например, вычислять сумму нескольких значений в строке), но формулы DAX могут работать по столбцам (например, подсчитывать количество определенного значения в столбце) или даже по всей таблице.

Как исправить ошибку “Эта таблица была создана запросом. Чтобы изменить эту таблицу, измените запрос вместо этого”?

Эта ошибка в Excel Power Pivot возникает, когда вы пытаетесь изменить модель данных в Power Pivot, когда эта модель данных изначально была загружена через Power Query. Вы не можете этого сделать, поэтому вы должны изменить ее через оригинальный метод загрузки: Power Query.

Справа в Excel нажмите на значок “Запросы и подключения”, затем найдите и дважды щелкните на вашем источнике данных запроса, чтобы открыть окно, где вы можете изменить таблицу.

Кредит изображения: Pexels. Все скриншоты сделаны Брендоном Ли.