Вопрос или проблема
У меня есть набор данных в Excel, который выглядит следующим образом:
| Клиент | Сумма | Город |
|---|---|---|
| Джон | 4 | Город B |
| Патрик | 3 | Город A |
И множество других столбцов.
Я ищу способ получить следующее:
| Клиент | Сумма | Город |
|---|---|---|
| Джон | 4 | Город B |
| Джон | 4 | Город B |
| Джон | 4 | Город B |
| Джон | 4 | Город B |
| Патрик | 3 | Город A |
| Патрик | 3 | Город A |
| Патрик | 3 | Город A |
Таким образом, по сути, получить запись для каждого значения Суммы. Это для одноразовой операции импорта, поэтому не нужно делать это динамически или что-то подобное.
Это можно сделать с помощью Power Query, доступного в Windows Excel 2010+ и Microsoft 365 (Windows или Mac)
Чтобы использовать Power Query
- Выберите некоторую ячейку в вашей таблице данных
Данные => Получение и преобразование => из Таблицы/Диапазона- Когда откроется редактор PQ:
Главная => Редактор Advanced Editor - Обратите внимание на Имя Таблицы на строке 2
- Вставьте код M ниже вместо того, что вы видите
- Измените имя таблицы во второй строке обратно на то, что было сгенерировано изначально.
- Читайте комментарии и исследуйте
Примененные шаги, чтобы понять алгоритм
let
//Измените следующие строки, чтобы отразить фактический источник данных и столбцы
Source = Excel.CurrentWorkbook(){[Name="Table11"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Клиент", type text},
{"Сумма", Int64.Type}, {"Город", type text}}),
#"Repeat Rows" =
Table.FromRecords(
List.Combine(
List.Accumulate(
{0..Table.RowCount(#"Changed Type")-1},
{},
(state, current)=>
state &
{List.Repeat(
{#"Changed Type"{current}},
Record.Field(#"Changed Type"{current},"Сумма"))
})
))
in
#"Repeat Rows"
Скрипт Power Query выше работает идеально. Но возможно ли также получить сумму, разделенную и вставленную в каждую ячейку. Как в моем примере, например 1,2,3,4 и вставить 1 в первую ячейку, 2 в следующую и так далее. Смотрите мой пример ниже.
.
Ответ или решение
Теория
Microsoft Excel — это популярный инструмент для работы с электронными таблицами. Он широко используется для анализа данных, создания отчетов и решения различных бизнес-задач. Одной из функций, которую часто используют в Excel, является "Get & Transform", также известная как Power Query. Она позволяет импортировать, чистить и преобразовывать данные из разных источников и в разных форматах. Одной из полезных функций, которую можно реализовать с помощью Power Query, является создание множества строк для каждого значения в столбце "Amount" (Сумма). Эта задача может быть особенно полезной при подготовке данных для анализа или отчетности.
Пример
Рассмотрим пример, представленный в вопросе. У нас есть исходная таблица с тремя столбцами: "Customer" (Клиент), "Amount" (Сумма) и "City" (Город). Данные организованы следующим образом:
| Customer | Amount | City |
|---|---|---|
| John | 4 | City B |
| Patrick | 3 | City A |
Для каждого клиента необходимо создать столько строк, сколько указано в столбце "Amount". Таким образом, результат должен выглядеть следующим образом:
| Customer | Amount | City |
|---|---|---|
| John | 4 | City B |
| John | 4 | City B |
| John | 4 | City B |
| John | 4 | City B |
| Patrick | 3 | City A |
| Patrick | 3 | City A |
| Patrick | 3 | City A |
Применение
Чтобы достичь желаемого результата, можно использовать Power Query, который доступен в Excel 2010 и более поздних версиях, а также в Microsoft 365. Ниже описан пошаговый процесс реализации:
-
Выбор ячейки в исходной таблице: Откройте Excel и выберите любую ячейку внутри вашей таблицы данных.
-
Открытие Power Query: Перейдите в меню "Данные" и выберите "Из таблицы/диапазона" в разделе "Получить и преобразовать данные". Это откроет редактор Power Query.
-
Редактирование кода в Power Query:
- В редакторе Power Query выберите "Главная" -> "Дополнительный редактор".
- Замените текущий M-код на предложенный, не забыв изменить имя таблицы во второй строке на ваше имя таблицы, сгенерированное Excel.
- Код будет выглядеть следующим образом:
let
// Измените следующую строку в соответствии с источником данных и названиями столбцов
Source = Excel.CurrentWorkbook(){[Name="ВашеИмяТаблицы"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"Amount", Int64.Type}, {"City", type text}}),
#"Repeat Rows" =
Table.FromRecords(
List.Combine(
List.Accumulate(
{0..Table.RowCount(#"Changed Type")-1},
{},
(state, current) =>
state &
{List.Repeat(
{#"Changed Type"{current}},
Record.Field(#"Changed Type"{current},"Amount"))
})
))
in
#"Repeat Rows"-
Понимание шагов:
- Source: Задает источник данных из текущей рабочей книги Excel.
- Changed Type: Преобразует типы данных столбцов в соответствующие типы (текст для клиентов и городов, целочисленный для суммы).
- Repeat Rows: Задает создание дополнительной таблицы, где строки повторяются в соответствии с значением в столбце "Amount".
-
Завершение работы: Закончите редактирование и загрузите результат обратно в Excel для дальнейшего использования.
Таким образом, Power Query позволяет автоматизировать процесс дублирования строк в зависимости от значения в определенном столбце. Это особенно полезно для пользователей, которым необходимо повторно обращаться к аналогичной задаче или которым требуется быстро подготовить данные в нужном формате для однократной задачи импорта.