Ивазкунии матни оммавӣ дар Power Query бо функсияи List.Accumate

Чӣ тавр зуд ва ба таври оммавӣ матнро мувофиқи рӯйхати истинодҳо бо формулаҳо иваз кардан мумкин аст - мо онро аллакай ҷудо кардаем. Акнун биёед кӯшиш кунем, ки онро дар Power Query иҷро кунем.

Тавре ки аксар вақт рӯй медиҳад иҷро кунед ин вазифа назар ба фахмонда додан хеле осонтар аст чаро он кор мекунад, аммо биёед кӯшиш кунем, ки ҳарду корро кунем 🙂

Ҳамин тавр, мо ду ҷадвали динамикии "интеллектуалӣ" дорем, ки аз диапазони оддӣ бо миёнабури клавиатура сохта шудаанд Ctrl+T ё даста Хона - Формат ҳамчун ҷадвал (Хона — Формат ҳамчун ҷадвал):

Ман ба мизи аввал занг задам Маълумот, ҷадвали дуюм - директорӣистифодаи майдон Номи ҷадвал (Номи ҷадвал) ҷадвали Конструктор (Тарроҳӣ).

Вазифа: иваз кардани суроғаҳо дар ҷадвал Маълумот ҳама рӯйдодҳо аз сутун Ёфтан Маълумотнома ба хамтоёни дурусти худ аз сутун Тағирдиҳӣ. Қисми боқимондаи матн дар ҳуҷайраҳо бояд бетағйир боқӣ монад.

Қадами 1. Директорияро ба Power Query бор кунед ва онро ба рӯйхат табдил диҳед

Пас аз гузоштани чашмаки фаъол ба ягон ҷои ҷадвали истинод, ҷадвалро клик кунед Маълумот (Сана)ё дар ҷадвал Дархости барқ (агар шумо версияи кӯҳнаи Excel дошта бошед ва шумо Power Query-ро ҳамчун замима дар ҷадвали алоҳида насб карда бошед) дар тугма Аз ҷадвал / диапазон (Аз ҷадвал/диапазон).

Ҷадвали истинод ба муҳаррири дархости Power Query бор карда мешавад:

Барои халал нарасондан, қадами ба таври худкор иловашуда навъи дигаргуншуда (Намуди ивазшуда) дар панели рост қадамҳои татбиқшавандаро бехатар нест кардан мумкин аст ва танҳо қадамро боқӣ мемонад сарчашма (Сарчашма):

Ҳоло, барои анҷом додани тағирот ва ивазкунии минбаъда, мо бояд ин ҷадвалро ба рӯйхат (рӯйхат) табдил диҳем.

Интиқоли лирикӣ

• Љадвали массиви дученакаест, ки аз чанд сатру сутун иборат аст.
• Сабт (Сабт) – массив-сатри якченака, ки аз якчанд майдон-элементҳо бо ном иборат аст, масалан [Ном = "Маша", Ҷинс = "f", Синну сол = 25]
  
• рӯйхат – массив-сутуни якченака, ки аз якчанд элемент иборат аст, масалан {1, 2, 3, 10, 42} or { "Имон ба умеди муҳаббат" }
  

Барои ҳалли мушкили мо, мо пеш аз ҳама ба намуд таваҷҷӯҳ хоҳем кард рӯйхат.

Дар ин ҷо ҳилла дар он аст, ки ҷузъҳои рӯйхат дар Power Query метавонад на танҳо рақамҳо ё матни ғайриоддӣ, балки дигар рӯйхатҳо ё сабтҳо бошад. Маҳз дар чунин рӯйхат (рӯйхат), ки аз сабтҳо (сабтҳо) иборат аст, ки мо бояд феҳристи худро гардонем. Дар Power Query нотаҳои синтаксисӣ (воридҳо дар қавсҳои мураббаъ, рӯйхатҳо дар қавсҳои ҷингила) ин чунин менамояд:

{

    [ Ҷустуҷӯ = "Ст. Петербург", Иваз кунед = "Ст. Петербург»] ,

    [ Ҷустуҷӯ = "Ст. Петербург", Иваз кунед = "Ст. Петербург»] ,

    [ Ҷустуҷӯ = "Петр", Иваз кардан = "Сент. Петербург»] ,

ва ѓайра

}

Чунин табдилдиҳӣ бо истифода аз функсияи махсуси забони M, ки дар Power Query сохта шудааст, анҷом дода мешавад - Ҷадвали ба сабтҳо. Барои бевосита дар сатри формула татбиқ кардани он, ин функсияро ба рамзи қадами он ҷо илова кунед сарчашма.

Буд:

Баъд аз:

Пас аз илова кардани функсияи Table.ToRecords намуди ҷадвали мо тағир меёбад - он ба рӯйхати сабтҳо табдил меёбад. Мундариҷаи сабтҳои инфиродӣ метавонад дар поёни панели намоиш бо пахш кардани заминаи чашмак дар паҳлӯи ҳар як калима дидан мумкин аст. рекорд (вале на дар як калима!)

Илова ба гуфтаҳои дар боло зикршуда, илова кардани як зарбаи дигар - барои кэш (буфер кардани) рӯйхати офаридаамон маъно дорад. Ин Power Query-ро маҷбур мекунад, ки рӯйхати ҷустуҷӯи моро як бор ба хотира бор кунад ва вақте ки мо баъдтар барои иваз кардани он ба он дастрасӣ пайдо мекунем, онро дубора ҳисоб накунад. Барои ин, формулаи моро дар функсияи дигар печонед - Рӯйхат. Буфер:

Чунин кэш суръати хеле назаррасро афзоиш медиҳад (якчанд маротиба!) Бо миқдори зиёди маълумоти ибтидоӣ тоза карда мешавад.

Бо ин тайёр кардани дастурамал анҷом меёбад.

Барои пахш кардан боқӣ мемонад Хона – Пӯшед ва бор кунед – Пӯшед ва бор кунед… (Хона — Пӯшед ва бор кунед — Пӯшед ва бор кунед..), интихоберо интихоб кунед Танҳо пайвастшавӣ эҷод кунед (Танҳо пайваст эҷод кунед) ва ба Excel баргардед.

Қадами 2. Боркунии ҷадвали маълумот

Дар ин ҷо ҳама чиз оддӣ аст. Мисли пештара бо маълумотнома, мо ба ягон ҷои ҷадвал мерасем, ҷадвалро клик кунед Маълумот тугма Аз ҷадвал/диапазон ва мизи мо Маълумот ба Power Query ворид мешавад. Қадами ба таври худкор иловашуда навъи дигаргуншуда (Намуди ивазшуда) шумо инчунин метавонед хориҷ кунед:

Бо он ягон амали махсуси омодагӣ талаб карда намешавад ва мо ба чизи муҳимтарин мегузарем.

Қадами 3. Бо истифода аз функсияи List.Accumate ивазкуниро иҷро кунед

Биёед бо истифода аз фармон ба ҷадвали маълумотамон сутуни ҳисобшударо илова кунем Илова кардани сутун - Сутуни фармоишӣ (Иловаи сутун - Сутуни фармоишӣ): ва дар равзанаи кушодашуда номи сутуни иловашударо ворид кунед (масалан, суроғаи ислоҳшуда) ва вазифаи ҷодугарии мо Рӯйхат. Ҷамъ кардан:

Барои пахш кардан боқӣ мемонад OK - ва мо сутунеро бо ивазкуниҳо ба даст меорем:

Дар назар гиред, ки:

• Азбаски Power Query ба ҳарфҳои хурд ҳассос аст, дар хати охирин иваз карда нашуд, зеро дар директория мо "SPb" дорем, на "SPb".
• Агар дар маълумоти манбаъ якбора якчанд зерсатрҳо ҷой дошта бошанд (масалан, дар сатри 7 ба шумо лозим меояд, ки ҳам “S-Pb” ва ҳам “Prospectus” -ро иваз кунед), пас ин ягон мушкилот эҷод намекунад (баръакси иваз кардани формулаҳои аз усули қаблӣ).
• Агар дар матни ибтидоӣ (сатри 9-ум) чизе барои иваз кардан мавҷуд набошад, он гоҳ ягон хатогӣ рух намедиҳад (баръакси иваз кардани формулаҳо).

Суръати чунин дархост хеле ва хеле сазовор аст. Масалан, барои ҷадвали маълумоти ибтидоӣ бо андозаи 5000 сатр, ин дархост дар муддати камтар аз як сония нав карда шуд (бе буферӣ, дар омади гап, тақрибан 3 сония!)

Функсияи List.Accumate чӣ гуна кор мекунад

Аслан, ин метавонад анҷоми ин мақола бошад (барои ман навиштан ва барои шумо хондан). Агар шумо хоҳед, ки на танҳо тавонед, балки инчунин фаҳмед, ки он чӣ гуна кор мекунад "дар зери сарпӯш" он гоҳ ба шумо лозим меояд, ки каме амиқтар ба сӯрохи харгӯш ғарқ шавед ва бо функсияи List.Accumulate, ки ҳама ивазкуниро анҷом дод, кор кунед. барои мо кор.

Синтаксиси ин функсия ин аст:

=Рӯйхат.Ҷамъ кардан(рӯйхат, тухм, аккумулятор)

ки дар

• рӯйхат рӯйхатест, ки мо унсурҳои онро такрор мекунем. 
• тухм - ҳолати ибтидоӣ
• аккумулятор – функсияе, ки дар элементи навбатии рӯйхат ягон амалро (риёзӣ, матнӣ ва ғ.) иҷро мекунад ва натиҷаи коркардро дар тағирёбандаи махсус ҷамъ мекунад.
  

Умуман, синтаксиси навиштани функсияҳо дар Power Query чунин менамояд:

(argument1, argument2, … argumentN) => баъзе амалҳо бо далелҳо

Масалан, функсияи ҷамъбастӣ метавонад чунин ифода карда шавад:

(а, б) => a + b

Барои List.Acculate, ин функсияи аккумулятор ду далели зарурӣ дорад (онҳоро ҳама гуна номгузорӣ кардан мумкин аст, аммо номҳои маъмулӣ давлат и ҷорӣ, тавре ки дар кӯмаки расмӣ барои ин функсия, дар куҷо:

• давлат – тағирёбанда, ки дар он натиҷа ҷамъ мешавад (қимати ибтидоии он арзиши дар боло зикршуда мебошад тухм)
  
• ҷорӣ – арзиши навбатии такрорӣ аз рӯйхат рӯйхат
  

Масалан, биёед ба қадамҳои мантиқи сохтмони зерин назар андозем:

=Рӯйхат.Ҷамъ кардан({3, 2, 5}, 10, (ҳолат, ҷорӣ) => ҳолат + ҷорӣ)

Ин охирин ҷамъшуда аст давлат арзиш Функсияи Рӯйхати мост. Ҷамъоварӣ ва натиҷаҳо дар натиҷа:

Агар шумо каме хаёл кунед, пас бо истифода аз Функсияи List.Accumute, шумо метавонед, масалан, функсияи Excel CONCATENATE -ро тақлид кунед (дар Power Query аналоги он номида мешавад. Матн. Якҷоя) бо истифода аз ифода:

Ё ҳатто арзиши максималиро ҷустуҷӯ кунед (тақлид ба функсияи MAX Excel, ки дар Power Query номида мешавад Рӯйхат. Макс):

Аммо, хусусияти асосии List.Acculate ин қобилияти коркарди на танҳо матни оддӣ ё рӯйхатҳои ададӣ ҳамчун далел, балки объектҳои мураккабтар аст - масалан, рӯйхатҳо аз рӯйхатҳо ё рӯйхатҳо аз сабтҳо (салом, директория!)

Биёед бори дигар ба сохтмоне назар андозем, ки дар мушкилоти мо иваз карда шудааст:

Рӯйхат. Ҷамъ кардан(директорӣ, [Суроға], (ҳолат, ҷорӣ) => Матн.Иваз кардан(ҳолат, ҷорӣ[Ҷустуҷӯ], ҷорӣ[Иваз кардан]) )

Дар ҳақиқат дар ин ҷо чӣ рӯй дода истодааст?

1. Ҳамчун арзиши ибтидоӣ (тухм) аз сутуни аввалин матни кавиро мегирем [Суроға] мизи мо: 199034, Санкт-Петербург, к. Беринга, д. 1
2. Сипас List.Accumate элементҳои рӯйхатро як ба як такрор мекунад - Маълумотнома. Ҳар як унсури ин рӯйхат сабтест, ки аз як ҷуфт майдонҳои "Чӣ бояд ёфт - Бо чӣ иваз карда шавад" ё ба ибораи дигар, сатри навбатии директория.
3. Функсияи аккумулятор ба тағирёбанда мегузорад давлат арзиши ибтидоӣ (суроғаи аввал 199034, Санкт-Петербург, к. Беринга, д. 1) ва дар он вазифаи аккумулятор - амалиёти ивазкуниро бо истифода аз функсияи стандартии M - иҷро мекунад Матн.Иваз (аналогӣ ба функсияи SUBSTITUTE Excel). Синтаксиси он чунин аст:

   Text.Replace (матни аслӣ, он чизеро, ки мо меҷӯем, бо он чизе иваз мекунем)

   ва дар ин ҷо мо дорем:

• давлат суроғаи ифлоси мост, ки дар он ҷойгир аст давлат (аз он ҷо расидан тухм)
  
• ҷорӣ[Ҷустуҷӯ] - арзиши майдон Ёфтан аз вуруди такрории навбатии рӯйхат директорӣ, ки дар тағирёбанда ҷойгир аст ҷорӣ
• ҷорӣ[Иваз кардан] - арзиши майдон Тағирдиҳӣ аз вуруди такрории навбатии рӯйхат директорӣхобидан дар ҷорӣ

Ҳамин тариқ, барои ҳар як суроға, як давраи пурраи номбаркунии ҳамаи сатрҳои директория ҳар дафъа иҷро карда мешавад, ки матни майдони [Ҷустуҷӯ] бо арзиши майдони [Иваз кардан] иваз карда мешавад.

Умедворам, ки шумо идеяро гирифтаед 🙂

• Матнро дар рӯйхат бо истифода аз формулаҳо иваз кунед
  
• Ифодаҳои муқаррарӣ (RegExp) дар Power Query