Хитрости »

28 Декабрь 2017 Дмитрий 22192 просмотров

Основные понятия (27)
Сводные таблицы и анализ данных (10)
Графики и диаграммы (5)
Работа с VB проектом (12)
Power BI и Power Query (23)

Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (68)
Разное (43)
Баги и глюки Excel (5)

Замена значений по списку в PowerQuery

Проблему замены значений по списку в Excel я уже затрагивал в статье Массовая замена слов. Там я использовал макросы, т.к. других вариантов по сути не было. Но с появлением PowerQuery многие задачи стало возможно решить и без применения VBA(макросов). В том числе и замену значений по списку соответствия. Правда, чуть менее универсально получается и в применении может быть даже не проще. Но это в любом случае выход для тех, у кого выполнение макросов запрещено политикой компании или когда даже само слово "макрос" уже вызывает легкую рвоту :)
Например, необходимо перевести текст из ячеек столбца на транслит или наоборот(с транслита на русский):
Исходная таблица
Для этого есть список соответствия:

Список представляет собой умную таблицу(Вставка(Insert) -Таблица(Table)), чтобы в запросе PowerQuery было удобно к ней обращаться. Таблица называется "translate_list"(имя можно изменить, выделить любую ячейку таблицы -вкладка Конструктор(Designer) -Имя таблицы(Table name))

Прежде чем читать далее и пробовать применить, необходимо знать азы работы в Power Query(Power Query - что такое и почему её необходимо использовать в работе?), уметь создавать и редактировать запросы и вызвать расширенный редактор: переходим в нужный запрос -Главная -Расширенный редактор:

К статье приложен файл с готовым запросом замены по списку, который разберем ниже:
Скачать пример:
Замена по списку через PowerQuery.xlsx (39,3 KiB, 1 369 скачиваний)

Для начала загружаем в PowerQuery таблицу для перевода: переходим на вкладку Вставка и выбираем – Таблица. Снимаем галку с пункта "Таблица с заголовками" -Ок:
Создание умной таблицы
Сразу переходим на вкладку Данные(или на вкладку Power Query) -группа Загрузить и преобразовать -Из таблицы
Данные -Из таблицы
Появится окно редактора запросов. В нем сразу переходим на вкладку Главная -Расширенный редактор:

Появится редактор текста запросов со следующим запросом:
let
Источник = Excel.CurrentWorkbook(){[Name="rus"]}[Content],
#"Измененный тип" = Table.TransformColumnTypes(Источник,{{"Текст", type text}})
in
#"Измененный тип"
где "rus" - имя таблицы для перевода
удаляем все, начиная с in и до конца, в конце второй строки(#"Измененный тип" = Table.TransformColumnTypes(Источник,{{"Текст", type text}})) добавляем запятую и вставляем следующий текст:

//определяем направление перевода(0 - с транслита на русский, 1 - с русского на транслит)
    from="1",
    WhatChange = if (from="0") then "WhatChange" else "ChangeWith",
    ChangeWith = if (from="0") then "ChangeWith" else "WhatChange",
    //определяем столбцы со значениями для поиска и замены слов/букв
    t_1 = Table.Column(Excel.CurrentWorkbook(){[Name="translate_list"]}[Content],WhatChange),
    t_2 = Table.Column(Excel.CurrentWorkbook(){[Name="translate_list"]}[Content],ChangeWith),
    //определяем длину текста каждого слова из списка соответствия
    t1_len = List.Generate(()=> [c=1, t=Text.Length(t_1{1})], 
                          each [c]<=List.Count(t_1), 
                          each [c=[c]+1, 
                                t=Text.Length(t_1{[c]})], 
                          each [t]),
    //сортируем таблицу на основании списка длин слов, чтобы сначала заменялись самые длинные
    tbl1 = Table.Sort(Table.FromColumns({t_1, t_2, t1_len}),{{"Column3", Order.Descending},"Column1","Column2","Column3"}),
    //берем столбцы отсортированной таблицы(t1 - что заменяем, t2 - на что заменяем)
    t1 = Table.Column(tbl1,"Column1"),
    t2 = Table.Column(tbl1,"Column2"),
    //функция замены значений по списку
    TranslateFunction = (InputText)=> 
     let
       //функция замены значений
       DoRep = List.Generate(
                          ()=> [cnt=0, text=Text.Upper(InputText)], 
                          each [cnt]<=List.Count(t1), 
                          each [cnt=[cnt]+1, 
                                text=Text.Replace([text], t1{[cnt]}, t2{[cnt]})], 
                          each [text]),
       GetLastValue = List.Last(DoRep)
     in
      GetLastValue,
    Output = Table.AddColumn(#"Измененный тип", "Changed text", each TranslateFunction([Текст]))
in
    Output

//определяем направление перевода(0 - с транслита на русский, 1 - с русского на транслит) from="1", WhatChange = if (from="0") then "WhatChange" else "ChangeWith", ChangeWith = if (from="0") then "ChangeWith" else "WhatChange", //определяем столбцы со значениями для поиска и замены слов/букв t_1 = Table.Column(Excel.CurrentWorkbook(){[Name="translate_list"]}[Content],WhatChange), t_2 = Table.Column(Excel.CurrentWorkbook(){[Name="translate_list"]}[Content],ChangeWith), //определяем длину текста каждого слова из списка соответствия t1_len = List.Generate(()=> [c=1, t=Text.Length(t_1{1})], each [c]<=List.Count(t_1), each [c=[c]+1, t=Text.Length(t_1{[c]})], each [t]), //сортируем таблицу на основании списка длин слов, чтобы сначала заменялись самые длинные tbl1 = Table.Sort(Table.FromColumns({t_1, t_2, t1_len}),{{"Column3", Order.Descending},"Column1","Column2","Column3"}), //берем столбцы отсортированной таблицы(t1 - что заменяем, t2 - на что заменяем) t1 = Table.Column(tbl1,"Column1"), t2 = Table.Column(tbl1,"Column2"), //функция замены значений по списку TranslateFunction = (InputText)=> let //функция замены значений DoRep = List.Generate( ()=> [cnt=0, text=Text.Upper(InputText)], each [cnt]<=List.Count(t1), each [cnt=[cnt]+1, text=Text.Replace([text], t1{[cnt]}, t2{[cnt]})], each [text]), GetLastValue = List.Last(DoRep) in GetLastValue, Output = Table.AddColumn(#"Измененный тип", "Changed text", each TranslateFunction([Текст])) in Output

должен получиться такой запрос(вместо "rus" в строке Источник = Excel.CurrentWorkbook(){[Name="rus"]}[Content], может быть имя другой таблицы):

let
    Источник = Excel.CurrentWorkbook(){[Name="rus"]}[Content],
    #"Измененный тип" = Table.TransformColumnTypes(Источник,{{"Текст", type text}}),
    //определяем направление перевода(0 - с транслита на русский, 1 - с русского на транслит)
    from="1",
    WhatChange = if (from="0") then "WhatChange" else "ChangeWith",
    ChangeWith = if (from="0") then "ChangeWith" else "WhatChange",
    //определяем столбцы со значениями для поиска и замены слов/букв
    t_1 = Table.Column(Excel.CurrentWorkbook(){[Name="translate_list"]}[Content],WhatChange),
    t_2 = Table.Column(Excel.CurrentWorkbook(){[Name="translate_list"]}[Content],ChangeWith),
    //определяем длину текста каждого слова из списка соответствия
    t1_len = List.Generate(()=> [c=1, t=Text.Length(t_1{1})], 
                          each [c]<=List.Count(t_1), 
                          each [c=[c]+1, 
                                t=Text.Length(t_1{[c]})], 
                          each [t]),
    //сортируем таблицу на основании списка длин слов, чтобы сначала заменялись самые длинные
    tbl1 = Table.Sort(Table.FromColumns({t_1, t_2, t1_len}),{{"Column3", Order.Descending},"Column1","Column2","Column3"}),
    //берем столбцы отсортированной таблицы(t1 - что заменяем, t2 - на что заменяем)
    t1 = Table.Column(tbl1,"Column1"),
    t2 = Table.Column(tbl1,"Column2"),
    //функция замены значений по списку
    TranslateFunction = (InputText)=> 
     let
       //функция замены значений
       DoRep = List.Generate(
                          ()=> [cnt=0, text=Text.Upper(InputText)], 
                          each [cnt]<=List.Count(t1), 
                          each [cnt=[cnt]+1, 
                                text=Text.Replace([text], t1{[cnt]}, t2{[cnt]})], 
                          each [text]),
       GetLastValue = List.Last(DoRep)
     in
      GetLastValue,
    Output = Table.AddColumn(#"Измененный тип", "Changed text", each TranslateFunction([Текст]))
in
    Output

let Источник = Excel.CurrentWorkbook(){[Name="rus"]}[Content], #"Измененный тип" = Table.TransformColumnTypes(Источник,{{"Текст", type text}}), //определяем направление перевода(0 - с транслита на русский, 1 - с русского на транслит) from="1", WhatChange = if (from="0") then "WhatChange" else "ChangeWith", ChangeWith = if (from="0") then "ChangeWith" else "WhatChange", //определяем столбцы со значениями для поиска и замены слов/букв t_1 = Table.Column(Excel.CurrentWorkbook(){[Name="translate_list"]}[Content],WhatChange), t_2 = Table.Column(Excel.CurrentWorkbook(){[Name="translate_list"]}[Content],ChangeWith), //определяем длину текста каждого слова из списка соответствия t1_len = List.Generate(()=> [c=1, t=Text.Length(t_1{1})], each [c]<=List.Count(t_1), each [c=[c]+1, t=Text.Length(t_1{[c]})], each [t]), //сортируем таблицу на основании списка длин слов, чтобы сначала заменялись самые длинные tbl1 = Table.Sort(Table.FromColumns({t_1, t_2, t1_len}),{{"Column3", Order.Descending},"Column1","Column2","Column3"}), //берем столбцы отсортированной таблицы(t1 - что заменяем, t2 - на что заменяем) t1 = Table.Column(tbl1,"Column1"), t2 = Table.Column(tbl1,"Column2"), //функция замены значений по списку TranslateFunction = (InputText)=> let //функция замены значений DoRep = List.Generate( ()=> [cnt=0, text=Text.Upper(InputText)], each [cnt]<=List.Count(t1), each [cnt=[cnt]+1, text=Text.Replace([text], t1{[cnt]}, t2{[cnt]})], each [text]), GetLastValue = List.Last(DoRep) in GetLastValue, Output = Table.AddColumn(#"Измененный тип", "Changed text", each TranslateFunction([Текст])) in Output

Нажимаем Готово. В получившемся запросе уже будет создан новый столбец с именем Changed text, в котором все значения будут заменены согласно списку соответствия. В нашем случае весь русский текст будет переведен на транслит.
Здесь стоит отметить, что при помощи PowerQuery данным методом удобно будет переводить один столбец. Если надо перевести еще какой-то столбец, то переходим на вкладку Добавить столбец -Настраиваемый столбец. В окне задаем имя столбца и вписываем следующее:

=ReplacementFunction([Текст])
Вместо Текст указывается имя столбца, который надо перевести. Таким образом мы вызываем функцию перевода, которую вставили ранее в редактор запросов. Появится новый столбец, в котором весь текст указанного столбца будет переведен на транслит.
Единственная ложка дегтя - весь текст будет переведен в верхний регистр. В данном случае это необходимость и пока я не придумал, как обойти этот момент именно для перевода с транслита. Но текст можно преобразовывать уже на выходе, применив функцию Text.Proper(или Text.Lower) в момент создания пользовательского столбца:
Output = Table.AddColumn(#"Измененный тип", "Changed text", each Text.Proper(TranslateFunction([Текст])))

Теперь разберем принцип работы того кода, который мы использовали для перевода.
Сам код, кстати, мог бы быть значительно проще, буквально в пару строк. Но в замене по списку есть нюансы. На примере перевода с транслита на русский: группе символов SHCH соответствует буква Щ. И если не сортировать список по длине символов, то сначала будет заменена отдельно буква S на С, а H на Х. И в итоге для слова "Shchebet" получим "схесхебет" вместо ожидаемого "щебет".
Начнем с того, что в коде есть возможность изменять направление перевода - либо данные из первого столбца таблицы соответствий заменяем на соответствующие им значения второго, либо наоборот. За это отвечает отдельный параметр, который мы обозначаем в самом начале:
from="1",
0 - ищем значение в первом столбце и заменяем соответствующим значением из второго столбца(с транслита на русский)
1 - ищем значение во втором столбце и заменяем соответствующим значением из первого столбца(с русского на транслит).
Таким образом для изменения направления надо всего лишь изменить один символ в запросе. Его можно сделать и динамическим параметром, если использовать трюк из этой статьи - Относительный путь к данным PowerQuery. Создаем таблицу параметров и далее просто ссылаемся на ячейку:
from=Excel.CurrentWorkbook(){[Name="Parameters"]}[Content]{0}[Значение],
Далее на основании именно этого значения мы определяем в каком столбце искать и из какого брать значения на замену:

WhatChange = if (from="0") then "WhatChange" else "ChangeWith",
ChangeWith = if (from="0") then "ChangeWith" else "WhatChange",
//определяем столбцы со значениями для поиска и замены слов/букв
t_1 = Table.Column(Excel.CurrentWorkbook(){[Name="translate_list"]}[Content],WhatChange),
t_2 = Table.Column(Excel.CurrentWorkbook(){[Name="translate_list"]}[Content],ChangeWith),

Т.к. список должен быть обязательно отсортирован по убыванию на основании количества символов в слове, то создаем дополнительный виртуальный столбец с количеством символов в каждой строке столбца значений на замену:

//определяем длину текста каждого слова из списка соответствия
    t1_len = List.Generate(()=> [c=1, t=Text.Length(t_1{1})], 
                          each [c]<=List.Count(t_1), 
                          each [c=[c]+1, 
                                t=Text.Length(t_1{[c]})], 
                          each [t]),

Здесь при помощи List.Generate мы проходим циклом по каждой ячейке столбца значений для замены и определяем количество символов в каждом при помощи функции Text.Length. И именно это значение используем для формирования виртуального списка.
Далее надо объединить все столбцы(два столбца непосредственно значений и столбец с кол-вом символов) в одну таблицу, чтобы отсортировать все столбца на основании только созданного столбца кол-ва символов. Для объединения используем функцию Table.FromColumns({t_1, t_2, t1_len}), а далее это все сортируем, указывая столбец, по которому сортировать(это всегда будет "Column3", т.к. при объединении столбцов мы столбец с кол-вом символов(t1_len) всегда ставим последним. Таким же образом у нас в первом столбце всегда значения для поиска, а во втором - для замены:

//сортируем таблицу на основании списка длин слов, чтобы сначала заменялись самые длинные
    tbl1 = Table.Sort(Table.FromColumns({t_1, t_2, t1_len}),{{"Column3", Order.Descending},"Column1","Column2","Column3"}),

на всякий случай там же указываем порядок столбцов(т.к. их у нас всего три) - "Column1","Column2","Column3". Хотя этого можно не делать, они будут по умолчанию расположены в правильном для нас порядке.
Теперь для простоты обращения к столбцам списка соответствия назначаем им краткие имена:

//берем столбцы отсортированной таблицы(t1 - что заменяем, t2 - на что заменяем)
t1 = Table.Column(tbl1,"Column1"),
t2 = Table.Column(tbl1,"Column2"),

и непосредственно функция замены значений. Она чуть сложнее в понимании:

//функция замены значений по списку
    TranslateFunction = (InputText)=> 
     let
       //функция замены значений
       DoRep = List.Generate(
                          ()=> [cnt=0, text=Text.Upper(InputText)],
                          each [cnt]<=List.Count(t1),
                          each [cnt=[cnt]+1,
                                text=Text.Replace([text], t1{[cnt]}, t2{[cnt]})],
                          each [text]),
       GetLastValue = List.Last(DoRep)
     in
      GetLastValue,

Здесь мы опять используем List.Generate, чтобы создать цикл по списку соответствия и каждое из его значений попробовать найти в указанном тексте(InputText) и заменить на соответствующее значение из столбца значений на замену.
И последним шагом является добавление нового столбца, в котором мы в функцию TranslateFunction передаем значение каждой ячейки столбца Текст:

Output = Table.AddColumn(#"Измененный тип", "Changed text", each TranslateFunction([Текст]))

С легкой руки Максима Зеленского изменил слегка текст запроса для более быстрой сортировки:

let
    Источник = Excel.CurrentWorkbook(){[Name="translit"]}[Content],
    #"Измененный тип" = Table.TransformColumnTypes(Источник,{{"Текст", type text}}),
    //определяем направление перевода
    from="0",
    WhatChange = if (from="0") then "WhatChange" else "ChangeWith",
    ChangeWith = if (from="0") then "ChangeWith" else "WhatChange",
    //определяем столбцы со значениями для поиска и замены слов/букв
    t_1 = Table.Column(Excel.CurrentWorkbook(){[Name="translate_list"]}[Content],WhatChange),
    t_2 = Table.Column(Excel.CurrentWorkbook(){[Name="translate_list"]}[Content],ChangeWith),
    //определяем длину текста каждого слова из списка соответствия
    //и сортируем таблицу на основании длин слов, чтобы сначала заменялись самые длинные
    tbl1 = Table.Sort(Table.FromColumns({t_1, t_2}), each -Text.Length(Record.Field(_, "Column1"))),
    t1 = Table.Column(tbl1,"Column1"),
    t2 = Table.Column(tbl1,"Column2"),
    //функция замены значений по списку
    TranslateFunction = (InputText)=> 
     let
       //функция замены значений
       DoRep = List.Generate(
                          ()=> [cnt=0, text=Text.Upper(InputText)], 
                          each [cnt]<=List.Count(t1), 
                          each [cnt=[cnt]+1, 
                                text=Text.Replace([text], t1{[cnt]}, t2{[cnt]})], 
                          each [text]),
       GetLastValue = List.Last(DoRep)
     in
      GetLastValue,
    Output = Table.AddColumn(#"Измененный тип", "Changed text", each TranslateFunction([Текст]))
in
    Output

В принципе изменилась исключительно часть, отвечающая за сортировку. Весь остальной запрос остался таким же.

Несложно догадаться, что таблицу соответствия можно использовать не только для перевода на транслит и обратно - туда можно забивать любые значения и слова для замены.

Скачать пример с запросом в двух направлениях - с русского на транслит и наоборот:

Замена по списку через PowerQuery.xlsx (39,3 KiB, 1 369 скачиваний)

Так же см.:
Power Query - что такое и почему её необходимо использовать в работе?
Получить данные из файлов XML при помощи Power Query
Относительный путь к данным PowerQuery
Оставить цифры или текст при помощи PowerQuery

Статья помогла? Поделись ссылкой с друзьями!

Видеоуроки

Поиск по меткам

Обсуждение: 4 комментария

Максим Зеленский:

29.12.2017 в 11:09

Дмитрий, добрый день! Хорошее решение, только с сортировкой, мне кажется, перегружено, генератор тут кажется излишним

Сортируем таблицу соответствий сразу по убыванию длины строки в столбце (после определения направления):
= Table.Sort(translate_table, each -Text.Length(Record.Field(_, WhatChange)))
= Table.Sort(translate_table, each -Text.Length(Record.Field(_, WhatChange)))
Мы ведь длину используем только для сортировки?

С уважением,
Максим

Ответить
- Дмитрий:
  
  29.12.2017 в 12:01
  
  Максим, спасибо. Да, используем только для сортировки.
  
  Ответить
Сергей Александров:

17.08.2018 в 11:44

Дмитрий, день добрый! Если еще актуально
"Единственная ложка дегтя - весь текст будет переведен в верхний регистр. В данном случае это необходимость и пока я не придумал, как обойти этот момент именно для перевода с транслита"

1. Я незнаю правил транслита, но мне кажеться, что для верхнего регистра можно ставить первую букву в верхнем регистре, а остальные в нижнем. Например для Щ вместо SHCH, можно указать Shch
2. Если в таблице "translate_list" хранить буквы в верхнем и нижнем регистре (Щ - Shch, щ - shch), то можно будет получать результат корректным.

Ответить
- Дмитрий:
  
  19.08.2018 в 13:03
  
  Сергей, тут проблема в том, что мы заранее не знаем будет ли текст с заглавной буквы или нет.
  1. Плохая идея, т.к. заранее неизвестно как в реальности в тексту будет записана эта самая Щ.
  2. Здесь Вы правы, но мы не знаем заранее одна буква будет или все заглавные. Если расширять справочник - то придется записывать весьма много разных комбинаций для получения 100% результата(Shch, SHch, SHCH, cShc и т.д. - фантазия у людей богатая :)). Я этого делать не стал и если кому-то реально надо - думаю сделает по своим правилам, благо справочники расширяемые.
  
  Ответить

Поделитесь своим мнением

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум

Замена значений по списку в PowerQuery

Поиск по меткам

Спасибо за сообщение