Если вы хотите стать опытным пользователем электронных таблиц, вам необходимо узнать о функциях Power Query и Power Pivot в Excel. Хотя вы можете многое сделать только с помощью Excel, использование этих встроенных «мощных» функций превратит вас в продвинутого профессионального пользователя Excel. В этом руководстве вы узнаете, как использовать Power Query для импорта сотен различных файлов с миллионами строк и использовать Power Pivot для создания комплексного анализа огромных импортируемых моделей данных.
Хотите защитить свою работу от посторонних глаз? Узнайте, как получить добавить пароль в книгу Excel .
1. Импорт данных из нескольких файлов
Используя Power Query, вы можете импортировать данные из разных файлов в одну папку. Однако будьте внимательны: вы все равно должны убедиться, что все данные имеют один и тот же формат. Сохраняйте количество столбцов и названия заголовков столбцов одинаковыми и убедитесь, что тип данных для значений в каждом столбце единообразен.
В этом руководстве мы используем Contents
1. Импорт данных из нескольких файлов
tle="пять файлов, которые содержат около 100 миллионов строк данных из Kaggle">пять файлов, которые содержат около 100 миллионов строк данных из Kaggle . Загрузите их, если хотите следовать инструкциям, или используйте свои собственные данные. Учитывая это, вот шаги для импорта данных:- Нажмите на вкладку «Данные» на ленте.
- Выберите «Получить данные ->Из файла ->Из папки», затем перейдите к своей папке и выберите ее.
<старый старт="3">
2. Фильтрация импортированных файлов и строк
В редакторе Power Query вы можете применять фильтры для импорта только тех файлов, которые соответствуют заданным вами критериям, что очень полезно для исключения ненужных источников данных.
- У нас есть четыре файла без расширения, но один файл с расширением.DATA. Давайте удалим лишнее, нажав стрелку рядом с надписью «Расширение».
- Мы можем увидеть список всех расширений файлов наших файлов. В этом примере это просто (пусто) и.data. Снимите флажки с ненужных расширений.
- Каждый примененный нами фильтр появится в разделе «Примененные действия».
- Мы также можем фильтровать файлы по имени, нажав стрелку рядом с надписью «Имя».
- Предположим, мы хотим отфильтровать любые файлы с буквой «k», например «adult100k», потому что мы не хотим работать с небольшими файлами, содержащими менее одного миллиона строк. Мы можем применить текстовый фильтр. Вариантов много, но для наших целей подойдет фильтр «Не содержит».
2. Фильтрация импортированных файлов и строк который вы не хотите видеть в имени файла.
- Прокрутите немного вправо, пока не увидите столбцы «Дата изменения» и «Дата создания». Давайте отфильтруем, чтобы выбрать только файлы, созданные в определенном диапазоне времени. Нажмите стрелку рядом с надписью «Дата создания».
- Выберите «Фильтры даты и времени ->Между».
- Введите значения времени для двух условий и подтвердите, нажав «ОК».
- Мы завершили фильтрацию наших данных на уровне файлов.
- Давайте объединим оставшиеся файлы, чтобы можно было фильтровать сами данные. Нажмите двойные стрелки рядом с заголовком «Содержимое».
- Теперь, когда вы можете видеть все значения для отдельных строк, каждая строка имеет дополнительный столбец «Source.Name», который указывает файл, из которого она возникла.
- Чтобы еще больше улучшить наши данные: поскольку наши файлы CSV изначально поставлялись без названий заголовков столбцов, вы можете щелкнуть правой кнопкой мыши некоторые заголовки столбцов и нажать «Переименовать», чтобы дать им подходящее имя.
- Фильтры здесь тоже работают. Мы можем применить фильтр диапазона номеров. Например, нажмите стрелку рядом с заголовком столбца «Возраст», затем выберите «Числовые фильтры ->Больше».
<старый старт="14">
Совет : при работе с электронными таблицами в Microsoft Excel может быть полезно знать как переместить столбец .
3. Загрузка отфильтрованных данных в модель данных
Вы можете продолжить очистку и фильтрацию данных, но давайте двинемся дальше и загрузим их в модель данных, чтобы начать анализировать их.
- Завершите работу с Power Query, нажав «Закрыть и загрузить ->Закрыть и загрузить в».
- В открывшемся всплывающем окне выберите «Только создавать соединение» и установите флажок «Добавить эти данные в модель данных».
- Подождите, пока данные загрузятся. Если у вас большой объем данных, это может занять несколько минут.
Использование модели данных Power Pivot в Excel
- После завершения загрузки данных мы можем внести изменения (например, добавить новые столбцы) в модель данных, нажав «Данные ->Инструменты для работы с данными ->Управление моделью данных».
- Вы должны увидеть окно Power Pivot. Давайте добавим столбец с формулой выражений анализа данных (DAX). Формулы DAX очень похожи на формулы Excel, с которыми вы, вероятно, уже знакомы. Прокрутите вправо, пока не увидите последний столбец ваших данных, затем нажмите «Добавить столбец».
- Давайте напишем формулу для этого столбца, которая будет применять простые вычисления к каждой строке. По сравнению с формулами Excel синтаксис DAX немного отличается. Например, мы можем добавить столбец «Описание» и ввести формулу
=CONCATENATE([Column9],[Column10]). Обратите внимание на использование квадратных скобок ([ ]) и доступ через имена заголовков столбцов.
- При желании вы также можете выполнить дополнительные изменения, например переименовать или удалить столбцы, щелкнув правой кнопкой мыши заголовок столбца.
4. Анализ данных с помощью сводной таблицы
После того, как вы закончили вносить изменения в модель данных, пришло время проанализировать данные на листе Excel с помощью сводной таблицы.
- Нажмите «Главная ->Сводная таблица» на ленте окна Power Pivot, чтобы открыть окно «Создать сводную таблицу».
<ол старт="2">
- Панель «Поля сводной таблицы» откроется на новом листе в главном окне Excel. В поле под строкой поиска вы должны увидеть список своих источников данных, в этом примере «kaggle_adult_csv». Нажмите на него, чтобы развернуть и отобразить все столбцы.
- Давайте проведем анализ и подсчитаем количество каждого «Описания» в наших данных. Мы можем выбрать столбцы, с которыми хотим работать, установив флажок рядом с их именами. Выберите «Описание» и обратите внимание, как фокус автоматически смещается на область «Строки» ниже. Это Power Pivot демонстрирует свой интеллект — он может точно угадать подходящую область для выбора. Поскольку мы начинаем с пустой сводной таблицы, имеет смысл сначала выбрать строки.
3. Загрузка отфильтрованных данных в модель данныхi>Перетащите отмеченный столбец «Описание» в пустое место в разделе «Значения». Обратите внимание, что Power Pivot снова правильно предполагает, что нам нужен «счетчик описаний», поскольку тип данных — текстовый. Кроме того, в нашу таблицу добавлен столбец, в котором указано количество вхождений каждого значения «Описание».
- Мы можем добавить еще одно измерение к нашим подсчетам. Например, перетащите столбец «Возраст» в пустую область «Столбцы», чтобы добавить в таблицу больше деталей, подсчитав количество описаний по каждому возрасту.
- Вы также можете добавить в таблицу возможность выбора фильтра, перетащив столбец «WorkClass» в область «Фильтры». Обратите внимание на новую интерактивную ячейку, которая появляется над нашей таб
Использование модели данных Power Pivot в Excel
//saintist.ru/wp-content/uploads/2024/05/Excel-Power-Pivot-Field-WorkClass-Filters.jpg" alt="Фильтры рабочих классов полей Power Pivot Excel">
- Нажмите стрелку рядом с надписью «Все» и выберите «Никогда не работало». Это изменит таблицу, и в расчеты будут включены только люди с рабочим классом «Никогда не работал». Вы можете изменить фильтр в любой момент, чтобы просмотреть таблицу с другой точки зрения.
Имеете дело со многими дубликаты в вашей книге Excel ? Узнайте, как находить и удалять дубликаты, а также очищать таблицы.
5. Представление ваших данных с помощью PowerChart
Вы можете создать дополнительные листы для анализа данных различными способами. На этот раз давайте создадим более впечатляющий результат с помощью PowerChart.
- Вернитесь в окно «Power Pivot для Excel». Если он закрыт, его можно открыть из окна Excel через «Данные ->Инструменты для работы с данными ->Управление моделью данных».
- Нажмите «Сводная таблица ->Сводная диаграмма ->Новый лист».
- На панели справа нажмите источник данных («kaggle_adult_csv»), чтобы развернуть его и отобразить все его столбцы.
- Перетащите столбцы из источника данных в одну из четырех областей ниже, чтобы построить диаграмму. Переместите столбец «Тип профессии» в область «Ось (Категории)».
- Снова перетащите «Тип профессии» и на этот раз в область «Значения». Вы сразу заметите, что гистограмма генерируется автоматически. Он показывает количество каждого типа занятий.
4. Анализ данных с помощью сводной таблицызанятий сводной диаграммы Excel">- Вы также можете перетащить столбец «Этническая принадлежность» в область «Легенда (серия)». Вы сразу же увидите более подробную диаграмму, в которой сравнивается количество представителей каждой национальности, принадлежащих к каждой профессии.
- Далее мы можем применить фильтры к нашей легенде, чтобы упростить диаграмму и просматривать только определенные значения. Наведите указатель мыши на «Этническая принадлежность», чтобы справа появилась стрелка. Нажмите на стрелку.
- Установите флажки рядом со значениями, которые хотите удалить, оставив только два.
- Давайте попробуем что-то новое. В разделе «Значения» нажмите «Количество типов занятий», затем нажмите «Удалить поле». Перетащите столбец «Возраст», чтобы заменить его.
- Вы заметите, что значение автоматически определяется как «Сумма возраста», но в реальном мире это не очень полезно. Давайте изменим это. Нажмите на него и выберите «Настройки поля значения».
- Поскольку возраст — это число, к этому разделу можно применить множество расчетов. Например, попробуйте выбрать «Средний».
- Визуализация обновится и отобразит средний возраст для каждой профессии по определенной этнической принадлежности (которую мы отфильтровали).
Хотите освежить основы Excel? У нас есть подробное руководство по все сочетания клавиш для Excel .
Часто задаваемые вопросы
Каково происхождение Power Pivot?
Microsoft представила Power Pivot как отдельное дополнение , предоставленный Microsoft SQL Server 2008 R2, выпущенный в 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. Все скриншоты Брэндона Ли.
