Составьте краткое изложение этой истории. Вот краткое изложение содержания истории, основанное на фактах: Вот упрощенная версия содержания истории: Вот беззаботный взгляд на содержание истории: Исследуйте другую точку зрения: Покажите мне факты Объясните это так, будто мне 5 лет Дайте мне беззаботный пересказ
Вы слышали о Power Query в Microsoft Excel, но всегда думали, что он предназначен только для экспертов Excel? Позвольте мне остановить вас на этом, потому что, на самом деле, Power Query разработан, чтобы быть удобным для пользователя и, что самое важное, сэкономить вам много хлопот при организации ваших данных.
Это руководство предназначено для читателей, которые впервые работают с Microsoft Excel или Power Query. Советы не требуют от вас знания или использования какого-либо языка формул Power Query M, и они предлагают хорошую отправную точку для знакомства с некоторыми командами, которые вы можете использовать в этом мощном инструменте для обработки данных.
1 Разделение ячеек по разделителю для изоляции значений
В Excel есть много способов разбить данные на несколько столбцов, включая инструмент Text To Columns, Flash Fill и встроенные функции. Однако наиболее интуитивный метод — запустить редактор Power Query и выполнить операцию там.
Давайте представим, что вы нашли таблицу в Википедии, которую хотите преобразовать с помощью Power Query. Чтобы импортировать таблицу, сначала вы нажали «Получить данные» на вкладке «Главная» на ленте, а затем выбрали «Из других источников» > «Из Интернета».
Затем, вставив URL-адрес в текстовое поле диалогового окна «Из Интернета» и нажав «ОК», вы выбрали таблицу в левой панели навигатора и нажали «Преобразовать данные».
Когда таблица загрузилась в редакторе Power Query, вы заметили, что столбец Career Span содержит два фрагмента данных — год начала карьеры каждого игрока и последний год, в котором он играл, — и вы хотите, чтобы они были в отдельных столбцах.
Для этого щелкните правой кнопкой мыши заголовок столбца, наведите курсор на «Разделить столбец» и выберите «По разделителю».
A ограничитель — это символ, знак или пробел, который используется для разделения элементов в последовательности.
На этом этапе редактор Power Query Editor просматривает данные в столбце, чтобы увидеть, может ли он обнаружить потенциальный разделитель. В этом случае он определяет, что каждая ячейка содержит тире, и правильно предполагает, что это точка, в которой ячейка должна быть разделена. Однако при необходимости вы можете выбрать другой разделитель из первого раскрывающегося меню.
Поскольку в этом примере в каждой ячейке выбранного столбца имеется только один разделитель, нет необходимости вносить какие-либо дополнительные изменения в параметры в диалоговом окне, поэтому нажмите «ОК» и просмотрите новые преобразованные данные в редакторе Power Query.
Чтобы переименовать новые столбцы, дважды щелкните заголовки столбцов и введите новые метки данных.
Теперь вы успешно разделили данные с помощью редактора Power Query, но заметили ли вы, что ячейки в двух новых столбцах выровнены по-разному? Это связано с тем, что все значения в столбце «First played» являются числами (на что указывает значок 123 рядом с заголовком столбца), но поскольку значения в столбце «Last played» включают как текст, так и числа, Excel рассматривает это как текстовый столбец (отсюда ABC в заголовке столбца).
Чтобы исправить это, выберите значок «ABC» в столбце «Последние воспроизведенные» и нажмите «Целое число».
Теперь оба столбца отформатированы как числовые наборы данных.
Однако, поскольку некоторые ячейки в столбце «Последнее воспроизведение» содержали текстовые значения, они теперь отображаются как ошибки. Но не волнуйтесь — продолжайте читать, чтобы узнать, как это исправить!
2. Заменить ошибки для использования в расчетах
Power Query в Excel — это мощный инструмент для устранения любых ошибок, которые могут возникнуть в ваших данных.
Если взять запрос из предыдущего примера, то некоторые ячейки в столбце «Последнее воспроизведение» содержат ошибки, поскольку ранее они содержали текстовое значение «Настоящее» в столбце, отформатированном как целые числа.
В настоящее время, если вы нажмете «Закрыть и загрузить» в верхнем левом углу редактора Power Query, ячейки с ошибочными значениями будут пустыми в результирующей таблице. Хотя это может показаться идеальным, пустые ячейки в столбце Excel могут вызвать проблемы при сортировке и фильтрации данных или ссылках на заголовки столбцов в формулах, поэтому лучше заполнить их осмысленными значениями.
Для этого щелкните правой кнопкой мыши заголовок столбца и выберите «Заменить ошибки».
У вас может возникнуть соблазн выбрать «Удалить ошибки» в контекстном меню. Однако это удалит всю строку, а не только ошибку, поэтому нажимайте эту опцию только в том случае, если вы этого хотите.
Теперь в диалоговом окне Replace Errors введите значение, которое вы хотите видеть вместо ошибок. В этом случае, поскольку ошибки появились там, где в ячейке было написано «Present», вы могли бы ввести текущий год.
При нажатии «ОК» все ошибочные значения заменяются этим новым значением.
Поскольку все ячейки в двух крайних правых столбцах содержат одинаковые типы данных без каких-либо ошибок, вы можете создать новый столбец, который вычисляет общее количество игровых лет для каждого игрока.
Здесь я нажал «Пользовательский столбец» на вкладке «Добавить столбец», изменил название столбца на «Годы игры» и использовал список столбцов в меню «Доступные столбцы», чтобы создать простое вычитание.
Затем, когда я нажал «ОК», этот новый столбец — без каких-либо ошибок — был добавлен в правую часть запроса.
Закончив, нажмите значок «Закрыть и загрузить» на вкладке «Главная» редактора Power Query, чтобы отправить таблицу в новую электронную таблицу в книге Excel.
3. Измените данные, чтобы облегчить анализ
При создании нового набора данных в Microsoft Excel, где это возможно, я следую принципу «запись-поле», где:
- Каждая строка содержит набор связанных, но разных типов данных (также известный как запись), а также расширение
- Каждый столбец содержит один уникальный тип данных (также известный как поле) относительно каждой записи.
В этом простом примере каждая страна — это запись, а ее континенты, население и валюты — это поля.
Использование этого формата подготавливает данные для дальнейшего анализа, поскольку вы можете легко фильтровать и сортировать данные, ссылаться на заголовки столбцов в формулах и визуализировать статистику.
Сравните таблицу в примере выше со следующим снимком экрана, где каждый столбец (поле) содержит идентичные типы данных.
В результате невозможно отфильтровать данные по годам, и вы не можете легко увидеть, какой год был самым прибыльным для каждой компании. Чтобы сделать эти анализы, вам нужно будет unpivot (или сгладить) данные, что подразумевает преобразование данных из широкой таблицы в длинную таблицу путем превращения каждого столбца года в строку. Другими словами, вы хотите, чтобы все финансовые значения хранились в одном поле.
Сначала выберите любую ячейку в данных и на вкладке «Данные» на ленте нажмите «Из таблицы/диапазона».
На этом этапе, если ваши данные еще не отформатированы как таблица Excel, вы увидите диалоговое окно, предлагающее исправить это. Это делается для того, чтобы редактору Power Query было легче читать ваши данные.
Затем в редакторе Power Query Editor выберите заголовки всех столбцов, которые вы хотите отменить сведение. В этом примере вам нужно будет щелкнуть заголовок «2020», нажать Shift, а затем щелкнуть заголовок «2024».
Теперь на вкладке «Преобразование» на ленте разверните раскрывающееся меню «Отменить поворот столбцов» и нажмите «Отменить поворот только выбранных столбцов».
Теперь каждый столбец представляет собой отдельное поле (компания, год и прибыль), а каждая строка — подробную запись о компании.
Наконец, дважды щелкните заголовки столбцов, чтобы переименовать их так, чтобы они соответствовали полю, которое они представляют, и щелкните значки формата чисел, чтобы убедиться, что каждый столбец содержит правильный тип данных.
Теперь, когда вы нажимаете «Закрыть и загрузить» на вкладке «Главная» на ленте, несведенная таблица отправляется в новую электронную таблицу в вашей книге Excel.
В результате вы можете выполнять анализ данных, который был невозможен до выравнивания таблицы, например, фильтровать данные по году и компании или сортировать прибыль компании в порядке убывания.
После добавления дополнительных данных в исходную таблицу, например, данных за другой год, перейдите к таблице, созданной ранее Power Query, и на вкладке «Запрос» нажмите «Обновить». Затем Excel добавит новые данные в результирующую таблицу в несведенном формате, избавляя вас от необходимости каждый раз возвращаться в редактор Power Query.
4. Заполните пустые ячейки на основе ячейки выше (или ниже)
Как я уже упоминал ранее, пустые ячейки в наборе данных могут привести к проблемам при сортировке и фильтрации данных или использовании формул, ссылающихся на заголовки столбцов, поэтому рекомендуется заполнить их.
В этом примере только первому игроку в каждой команде присвоен номер команды в столбце A, поэтому, если вы переставите данные, вы потеряете представление о том, какой игрок в какой команде. Кроме того, в настоящее время вы не можете фильтровать данные по номеру команды.
В идеале ячейки A3–A5 должны содержать число 1, ячейки A7–A9 должны содержать число 2 и т. д. Ввод этих чисел вручную может занять много времени, особенно если, как в этом примере, в наборе данных много строк. Вместо этого используйте Power Query, чтобы заполнить пустые ячейки за считанные секунды.
Сначала выберите любую ячейку данных и на вкладке «Данные» на ленте нажмите «Из таблицы/диапазона».
Когда откроется редактор Power Query, обратите внимание, что пустые ячейки в рассматриваемом столбце содержат слово «null». Чтобы исправить это, выберите столбец, щелкнув его заголовок, и на вкладке «Преобразование» нажмите «Заполнить».
Затем выберите, хотите ли вы заполнить данные сверху вниз или снизу вверх. Нажатие «Вниз» находит ячейку в выбранном диапазоне, содержащую значение, и заполняет все пустые ячейки ниже тем же значением. С другой стороны, нажатие «Вверх» заполняет пустые ячейки над ячейкой, содержащей значение. В этом случае, поскольку только первая строка каждой команды содержит число, вам нужно заполнить снизу вверх.
Microsoft Офис 2024
Получите постоянный доступ к Word, Excel, PowerPoint и OneNote с пакетом Office Home 2024. Это не полная замена подписке Microsoft 365, так как в нем отсутствуют некоторые приложения и не включены обновления функций, но это разовая покупка, поэтому она идеально подходит тем, кому нужны только основные функции.
$149.99 в Microsoft $149.99 в Amazon Развернуть Свернуть
Теперь каждому игроку правильно назначен номер команды, поэтому вы можете нажать «Закрыть и загрузить» на вкладке «Главная», чтобы отправить запрос на новый рабочий лист.
Благодаря этому быстрому, но важному изменению вы можете сортировать и фильтровать набор данных, будучи уверенными в том, что не потеряете информацию о том, какой игрок входит в каждую команду.
Если у вас есть несколько таблиц на отдельных листах Excel, при условии, что у них одинаковые заголовки столбцов, вы можете использовать Power Query для объединения данных в одну таблицу. Это включает в себя дополнительный (но простой) шаг создания связей данных между каждой таблицей перед объединением запросов в редакторе Power Query.
