List.Accumate функциясы менен Power Queryде жапырт текст алмаштыруу

Кантип тез жана жапырт түрдө шилтемелер тизмесине ылайык текстти формулалар менен алмаштыруу керек - биз аны иргеп алганбыз. Эми муну Power Queryде жасаганга аракет кылалы.

Көбүнчө болот аткаруу бул милдет түшүндүрүүгө караганда алда канча жеңил неге ал иштейт, бирок экөөнү тең кылганга аракет кылалы 🙂

Ошентип, бизде клавиатура жарлыгы менен кадимки диапазондордон түзүлгөн эки "акылдуу" динамикалык таблицалар бар Ctrl+T же команда Башкы бет – Таблица катары форматтоо (Башкы бет — Таблица катары формат):

Биринчи столго чалдым маалыматтар, экинчи таблица – справочникталаа колдонуу Таблица аты (таблица аты) табулатура Конструктор (Дизайн).

Тапшырма: таблицадагы даректерди алмаштыруу маалыматтар бир тилкеден бардык көрүнүштөр Табуу колдонмо китеп колоннадан алардын тиешелүү туура кесиптештерине алмаштыруу. Уячалардагы тексттин калган бөлүгү тийилбей калышы керек.

Кадам 1. Каталогду Power Queryге жүктөңүз жана аны тизмеге айлантыңыз

Активдүү уячаны маалымдама таблицасынын каалаган жерине коюп, өтмөктү чыкылдатыңыз маалыматтар (Дата)же өтмөктө күч суроо (эгерде сизде Excelдин эски версиясы болсо жана сиз Power Queryди өзүнчө өтмөктө кошумча катары орнотсоңуз) баскычта Таблицадан/диапазондон (Таблицадан/Арападан).

Маалымат таблицасы Power Query суроо редакторуна жүктөлөт:

Жолтоо болбоо үчүн, автоматтык түрдө кошумча кадам өзгөртүлгөн түрү (Өзгөртүлгөн түрү) оң панелде колдонулган кадамдарды коопсуз жок кылып, кадамды гана калтырса болот булак (Source):

Эми андан аркы трансформацияларды жана алмаштырууларды жүргүзүү үчүн бул таблицаны тизмеге (тизмеге) айландыруу керек.

Лирикалык чегинүү

• стол бир нече саптардан жана мамычалардан турган эки өлчөмдүү массив.
• Жаздыруу (жазуу) – мисалы, аталыштары бар бир нече талаа-элементтерден турган бир өлчөмдүү массив-сап [Аты = "Маша", Жынысы = "f", Жашы = 25]
  
• тизме – мисалы, бир нече элементтерден турган бир өлчөмдүү массив-мамыча {1, 2, 3, 10, 42} or { "Ишеним үмүт сүйүү" }
  

Биздин көйгөйдү чечүү үчүн, биз биринчи кезекте түрү кызыкдар болот тизме.

Бул жердеги куулук Power Query'деги тизме элементтери баналдык сандар же текст гана эмес, башка тизмелер же жазуулар да болушу мүмкүн. Бул жазуулардан (жазуулардан) турган ушунчалык татаал тизмеде (тизмеде) биздин каталогду бурушубуз керек. Power Query синтаксистик белгилеринде (чарчы кашаадагы жазуулар, тармал кашаадагы тизмелер) бул төмөнкүдөй көрүнөт:

{

    [ Табуу = “Ст. Петербург», алмаштыруу = «Ст. Петербург»] ,

    [ Табуу = “Ст. Петербург», алмаштыруу = «Ст. Петербург»] ,

    [ Табуу = "Петр", алмаштыруу = "Ст. Петербург»] ,

жана башкалар

}

Мындай трансформация Power Queryге орнотулган М тилинин атайын функциясын колдонуу менен ишке ашырылат – Table.ToRecords. Аны түз формула тилкесинде колдонуу үчүн, бул функцияны ошол жердеги кадам кодуна кошуңуз булак.

Ал эле:

кийин:

Table.ToRecords функциясын кошкондон кийин, биздин таблицабыздын көрүнүшү өзгөрөт – ал жазуулардын тизмесине айланат. Жеке жазуулардын мазмунун көрүү панелинин ылдый жагында каалаган сөздүн жанындагы уячанын фонунда чыкылдатуу менен көрүүгө болот. жазуу (бирок бир сөз менен эмес!)

Жогоруда айтылгандардан тышкары, биздин түзүлгөн тизмени кэшке (буферге) дагы бир сокку кошуу мааниси бар. Бул Power Query'ди издөө тизмебизди эстутумга бир жолу жүктөөгө мажбурлайт жана кийинчерээк аны алмаштыруу үчүн ага киргенибизде аны кайра эсептеп чыкпайт. Бул үчүн, биздин формуланы башка функцияга ороп алыңыз - List.Buffer:

Мындай кэштөө ылдамдыгын (бир нече эсеге!) абдан байкаларлык жогорулатат, баштапкы маалыматтардын чоң көлөмүн тазалайт.

Бул колдонмону даярдоону аяктайт.

Бул басуу үчүн калды Башкы бет – Жабуу жана жүктөө – Жабуу жана жүктөө… (Башкы бет — Жабуу&Жүктөө — Жабуу&Жүктөө..), опцияны тандаңыз Жөн гана байланыш түзүңүз (Байланыш түзүү гана) жана Excelге кайтуу.

Кадам 2. Маалымат таблицасын жүктөө

Бул жерде баары жөнөкөй. Маалымдамадагыдай эле, биз таблицадагы каалаган жерге чыгабыз, өтмөктү басыңыз маалыматтар баскычы Таблицадан/Арападан жана биздин стол маалыматтар Power Queryге кирет. Автоматтык түрдө кошулган кадам өзгөртүлгөн түрү (Өзгөртүлгөн түрү) сиз дагы алып салсаңыз болот:

Аны менен эч кандай атайын даярдык көрүү иш-аракеттери талап кылынбайт жана биз эң негизги нерсеге өтөбүз.

Кадам 3. List.Accumate функциясын колдонуу менен алмаштырууларды аткарыңыз

Келгиле, команданы колдонуп, биздин маалымат таблицага эсептелген тилкени кошолу Мамыча кошуу – Ыңгайлаштырылган тилке (Кошуу тилкеси — Ыңгайлаштырылган тилке): жана ачылган терезеге кошулган тилкенин атын киргизиңиз (мисалы, оңдолгон дарек) жана биздин сыйкырдуу функциябыз List.Accumate:

Бул басуу үчүн калды OK – жана биз алмаштырылган тилкени алабыз:

Белгилей кетсек:

• Power Query регистрге сезимтал болгондуктан, акыркы сапта алмаштыруу болгон жок, анткени каталогдо бизде "SPb" эмес, "SPb" бар.
• Эгерде баштапкы маалыматтарда бир эле учурда алмаштыруу үчүн бир нече ички саптар бар болсо (мисалы, 7-сапта "S-Pb" менен "Проспектти" экөөнү тең алмаштыруу керек), анда бул эч кандай көйгөй жаратпайт (формулалар менен алмаштыруудан айырмаланып, мурунку ыкма).
• Эгерде баштапкы текстте алмаштыра турган эч нерсе жок болсо (9-сап), анда каталар болбойт (кайрадан, формулалар менен алмаштыруудан айырмаланып).

Мындай өтүнүчтүн ылдамдыгы абдан, абдан татыктуу. Мисалы, 5000 сап өлчөмүндөгү баштапкы маалыматтардын таблицасы үчүн бул суроо бир секундага жетпеген убакытта жаңыртылды (буферлөөсүз, демек, болжол менен 3 секунд!)

List.Accumate функциясы кантип иштейт

Негизи, бул макаланы бүтүшү мүмкүн (менин жазышым үчүн, ал эми сиз окушуңуз үчүн). Эгер сиз бир гана "капоттун астында" кантип иштээрин түшүнгүңүз келсе, анда сиз коёндун тешигине бир аз тереңирээк сүңгүп, бардык жапырт алмаштырууну аткарган List.Acculate функциясы менен күрөшүүгө туура келет. биз үчүн иште.

Бул функциянын синтаксиси:

=Тизме.Топтоо(тизме, тукум, аккумулятордук)

кайда

• тизме элементтерин кайталап жаткан тизме. 
• тукум - баштапкы абал
• аккумулятордук – тизменин кийинки элементи боюнча кандайдыр бир операцияны (математикалык, тексттик ж.б.) аткарган жана иштетүүнүн натыйжасын атайын өзгөрмөгө топтогон функция.
  

Жалпысынан, Power Queryде функцияларды жазуу синтаксиси төмөнкүдөй көрүнөт:

(argument1, argument2, … argumentN) => аргументтер менен кээ бир аракеттер

Мисалы, суммалоо функциясын төмөнкүчө чагылдырууга болот:

(a, b) => a + b

List.Acculate үчүн бул аккумулятор функциясынын эки талап кылынган аргументи бар (аларды каалагандай атаса болот, бирок кадимки аталыштар мамлекет и учурдагы, бул функциянын расмий жардамында болгондой, бул жерде:

• мамлекет – натыйжа топтолгон өзгөрмө (анын баштапкы мааниси жогоруда айтылган тукум)
  
• учурдагы – тизмеден кийинки кайталанган маани тизме
  

Мисалы, төмөнкү курулуштун логикасынын кадамдарын карап көрөлү:

=Тизме.Топтоо({3, 2, 5}, 10, (абал, учурдагы) => абал + учурдагы)

Бул акыркы топтолгон мамлекет маани биздин List.Accumate функциябыз жана натыйжада:

Эгер сиз бир аз кыялдансаңыз, анда List.Accumate функциясын колдонуп, мисалы, Excel функциясын имитациялай аласыз CONCATENATE (Power Queryде анын аналогу деп аталат) Text.Combine) туюнтманы колдонуу менен:

Же максималдуу маанини издеңиз (Excelдин MAX функциясын туурап, ал Power Queryде деп аталат Тизме. Макс):

Бирок, List.Acculate негизги өзгөчөлүгү аргумент катары жөнөкөй текстти же сандык тизмелерди гана эмес, татаалыраак объекттерди – мисалы, тизмелерден тизмелерди же жазуулардан тизмелерди (салам, Каталог!)

Биздин маселеде алмаштырууну ишке ашырган курулушту дагы бир жолу карап көрөлү:

List.Accumulation(справочник, [Дарек], (абал, учурдагы) => Текст.Алмаштыруу(абал, учурдагы[Тап], учурдагы[Алмаштыруу]) )

Бул жерде чынында эмне болуп жатат?

1. баштапкы маани катары (тукум) колоннадан биринчи олдоксон текстти алабыз [Дарек] биздин үстөл: 199034, Санкт-Петербург, көч. Беринга, д. 1
2. Андан кийин List.Accumate тизменин элементтерин бирден кайталайт – колдонмо китеп. Бул тизменин ар бир элементи "Эмне табуу керек - Эмне менен алмаштыруу керек" деген жуп талаадан же башкача айтканда, каталогдогу кийинки саптан турган жазуу.
3. Аккумулятор функциясы өзгөрмөгө коет мамлекет баштапкы маани (биринчи дарек 199034, Санкт-Петербург, көч. Беринга, д. 1) жана ага аккумулятордук функцияны аткарат – стандарттык М-функциясын колдонуу менен алмаштыруу операциясы Text.Replace (Excelдин SUBSTITUTE функциясына окшош). Анын синтаксиси:

   Text.Replace( түпнуска текст, биз эмне издеп жатабыз, эмне менен алмаштырып жатабыз )

   жана бул жерде бизде:

• мамлекет жайгашкан биздин кир дареги болуп саналат мамлекет (ал жакка жетүү тукум)
  
• учурдагы[Издөө] – талаанын мааниси Табуу тизменин кийинки кайталанган жазуусунан справочник, өзгөрмөдө жатат учурдагы
• учурдагы[алмаштыруу] – талаанын мааниси алмаштыруу тизменин кийинки кайталанган жазуусунан справочникжатып учурдагы

Ошентип, ар бир дарек үчүн каталогдогу бардык саптарды санап чыгуунун толук цикли ар бир жолу аткарылып, [Табу] талаасындагы текстти [Алмаштыруу] талаасындагы мааниге алмаштырат.

Идеяны алдыңыз деп үмүттөнөбүз 🙂

• Формулаларды колдонуу менен тизмедеги текстти жапырт алмаштыруу
  
• Power Queryдеги кадимки туюнтмалар (RegExp).