List.Accumate функциясы бар Power Query бағдарламасында жаппай мәтінді ауыстыру

Анықтамалық тізімге сәйкес мәтінді формулалармен қалай тез және жаппай ауыстыруға болады - біз оны сұрыптадық. Енді оны Power Query бағдарламасында орындауға тырысайық.

Жиі болатындай орындау бұл тапсырма түсіндіруден әлдеқайда оңай неге ол жұмыс істейді, бірақ екеуін де жасауға тырысайық 🙂

Сонымен, бізде пернелер тіркесімі арқылы қарапайым диапазондардан жасалған екі «ақылды» динамикалық кесте бар Ctrl+T немесе команда Басты – кесте ретінде пішімдеу (Үй — Кесте ретінде пішімдеу):

Мен бірінші үстелге қоңырау шалдым мәліметтер, екінші кесте – анықтамалықөрісті қолдану Кесте атауы (Кесте атауы) қойындысы Конструктор (Дизайн).

Тапсырма: кестедегі мекенжайларды ауыстырыңыз мәліметтер бағандағы барлық оқиғалар Табу Анықтамалық бағаннан олардың сәйкес дұрыс әріптестеріне Ауыстыру. Ұяшықтардағы мәтіннің қалған бөлігі қозғалмай қалуы керек.

1-қадам. Каталогты Power Query ішіне жүктеп, оны тізімге айналдырыңыз

Белсенді ұяшықты анықтамалық кестедегі кез келген жерге орнатқаннан кейін, қойындыны басыңыз мәліметтер (Күні)немесе қойындыда Қуат сұрауы (егер сізде Excel бағдарламасының ескі нұсқасы болса және Power Query қолданбасын бөлек қойындыда қондырма ретінде орнатқан болсаңыз) түймедегі Кестеден/ауқымнан (Кестеден/ауқымнан).

Анықтамалық кесте Power Query сұрау өңдегішіне жүктеледі:

Кедергі жасамау үшін автоматты түрде қосылған қадам өзгертілген түрі (Өзгертілген түрі) оң жақ панельде қолданылатын қадамдарды қауіпсіз жоюға болады, тек қадамды қалдырады қайнар көз (Көз):

Енді әрі қарай түрлендірулер мен ауыстыруларды орындау үшін осы кестені тізімге (тізімге) айналдыру керек.

Лирикалық шегіну

• үстел бірнеше жолдар мен бағандардан тұратын екі өлшемді массив.
• Жазба (жазба) – аты бар бірнеше өріс-элементтерден тұратын бір өлшемді массив-жолы, мысалы [Аты = «Маша», Жынысы = «f», Жасы = 25]
  
• тізім – бірнеше элементтерден тұратын бір өлшемді массив-баған, мысалы {1, 2, 3, 10, 42} or { "Сенім үміт махаббат" }
  

Біздің мәселемізді шешу үшін біз ең алдымен түрге қызығушылық танытамыз тізім.

Мұндағы қулық мынада: Power Query ішіндегі тізім элементтері тек қана сандар немесе мәтін емес, сонымен қатар басқа тізімдер немесе жазбалар болуы мүмкін. Жазбалардан (жазбалардан) тұратын соншалықты күрделі тізімде (тізімде) біздің анықтамалықты айналдыру керек. Power Query синтаксистік белгілеуінде (төртбұрышты жақшадағы жазбалар, бұйра жақшадағы тізімдер) бұл келесідей болады:

{

    [ Табыңыз = «Сент. Петербург», ауыстырыңыз = «Санкт. Петербург»] ,

    [ Табыңыз = «Сент. Петербург», ауыстырыңыз = «Санкт. Петербург»] ,

    [ Табыңыз = «Петр», ауыстырыңыз = «Әулие. Петербург»] ,

және т.б.

}

Мұндай түрлендіру Power Query ішіне енгізілген M тілінің арнайы функциясы арқылы орындалады – Жазбалар кестесі. Оны формула жолағында тікелей қолдану үшін осы функцияны қадам кодына қосыңыз қайнар көз.

Ол болды:

Кейін:

Table.ToRecords функциясын қосқаннан кейін кестеміздің сыртқы түрі өзгереді – ол жазбалар тізіміне айналады. Жеке жазбалардың мазмұнын кез келген сөздің жанындағы ұяшық фонында басу арқылы қарау тақтасының төменгі жағында көруге болады. рекорд (бірақ бір сөзбен емес!)

Жоғарыда айтылғандарға қосымша, біз жасаған тізімді кэштеу (буферлеу) үшін тағы бір штрих қосу орынды. Бұл Power Query қолданбасын іздеу тізімін жадқа бір рет жүктеуге мәжбүр етеді және оны ауыстыру үшін кейінірек кірген кезде оны қайта есептемеу керек. Ол үшін формуламызды басқа функцияға ораңыз – Тізім.Буфер:

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

Бұл анықтамалықты дайындауды аяқтайды.

Оны басу қалды Үй – Жабу және жүкте – Жабу және жүкте… (Басты бет — Жабу&Жүктеу — Жабу&Жүктеу..), опцияны таңдаңыз Тек қосылым жасаңыз (Тек қосылым жасау) және Excel бағдарламасына оралыңыз.

2-қадам. Деректер кестесін жүктеу

Мұнда бәрі қарапайым. Анықтамалықтағыдай, біз кестенің кез келген жеріне көтерілеміз, қойындыны басыңыз мәліметтер түйме Кестеден/Ауқымнан және біздің үстел мәліметтер Power Query ішіне кіреді. Автоматты түрде қосылған қадам өзгертілген түрі (Өзгертілген түрі) жоюға болады:

Онымен арнайы дайындық әрекеттерін жасау қажет емес, біз ең маңызды нәрсеге көшеміз.

3-қадам. List.Accumate функциясын пайдаланып ауыстыруларды орындаңыз

Пәрменді пайдаланып деректер кестесіне есептелген бағанды ​​қосамыз Баған қосу – теңшелетін баған (Баған қосу — реттелетін баған): және ашылатын терезеде қосылған бағанның атын енгізіңіз (мысалы, түзетілген мекенжай) және біздің сиқырлы функциямыз Тізім. Жинақтау:

Оны басу қалды OK – және біз ауыстырулары бар бағанды ​​аламыз:

Ескертіп қой:

• Power Query регистрді ескеретіндіктен, соңғы жолда ауыстыру болмады, себебі каталогта бізде «SPb» емес, «SPb» бар.
• Бастапқы деректерде бірден ауыстырылатын бірнеше ішкі жолдар болса (мысалы, 7-жолда «S-Pb» және «Анықтамалық» екеуін де ауыстыру қажет), онда бұл ешқандай қиындық тудырмайды (формулалармен ауыстырудан айырмашылығы). алдыңғы әдіс).
• Бастапқы мәтінде ауыстыратын ештеңе болмаса (9-жол), онда қателер болмайды (қайтадан формулалармен ауыстырудан айырмашылығы).

Мұндай сұраныстың жылдамдығы өте жақсы. Мысалы, өлшемі 5000 жолды құрайтын бастапқы деректер кестесі үшін бұл сұрау бір секундтан аз уақыт ішінде жаңартылды (айтпақшы, буферлеусіз, шамамен 3 секунд!)

List.Accumate функциясы қалай жұмыс істейді

Негізінде, бұл мақаланың соңы болуы мүмкін (мен жазу үшін, ал сіз оқу үшін). Егер сіз «капюшонның астында» қалай жұмыс істейтінін түсініп қана қоймай, сонымен қатар оның қалай жұмыс істейтінін түсінгіңіз келсе, сіз қоянның тесігіне сәл тереңірек сүңгіп, барлық көлемді ауыстыруды орындаған List.Accumulation функциясымен айналысуыңыз керек. біз үшін жұмыс.

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

=Тізім. Жинақтау(тізім, ұрық, аккумулятор)

қайда

• тізім элементтерін қайталайтын тізім. 
• ұрық – бастапқы күй
• аккумулятор – тізімнің келесі элементі бойынша қандай да бір операцияны (математикалық, мәтіндік және т.б.) орындайтын және өңдеу нәтижесін арнайы айнымалыға жинақтайтын функция.
  

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

(аргумент1, аргумент2, … аргументN) => аргументтері бар кейбір әрекеттер

Мысалы, жинақтау функциясын келесідей көрсетуге болады:

(a, b) => a + b

List.Acculate үшін бұл жинақтаушы функцияның екі қажетті аргументі бар (оларды кез келген атаумен атауға болады, бірақ әдеттегі атаулар мемлекет и ағымдағы, осы функцияның ресми анықтамасындағыдай, мұнда:

• мемлекет – нәтиже жинақталатын айнымалы (оның бастапқы мәні жоғарыда көрсетілген мән болып табылады ұрық)
  
• ағымдағы – тізімнен келесі қайталанатын мән тізім
  

Мысалы, келесі құрылыс логикасының қадамдарын қарастырайық:

=Тізім. Жинақтау({3, 2, 5}, 10, (күй, ағымдағы) => күй + ток)

Бұл соңғы жинақталған мемлекет мән біздің List.Accumate функциясы болып табылады және нәтиже ретінде шығады:

Егер сіз аздап қиялдасаңыз, онда List.Accumute функциясын пайдаланып, мысалы, Excel CONCATENATE функциясын модельдеуге болады (Power Query-де оның аналогы деп аталады) Мәтін. Біріктіру) өрнекті пайдаланып:

Немесе тіпті максималды мәнді іздеңіз (Excel бағдарламасының MAX функциясына еліктеу, ол Power Query қызметінде аталады Тізім. Макс):

Дегенмен, List.Acculate бағдарламасының басты ерекшелігі қарапайым мәтінді немесе сандық тізімдерді аргументтер ретінде ғана емес, күрделірек нысандарды – мысалы, тізімдерден тізімдерді немесе жазбалардан тізімдерді (сәлеметсіз бе, каталог!) өңдеу мүмкіндігі болып табылады.

Біздің мәселемізде ауыстыруды орындаған құрылысты қайта қарастырайық:

Тізім. Жинақтау(анықтамалық, [Мекен-жайы], (күй, ағымдағы) => Мәтін.Ауыстыру(күй, ағымдағы[Табу], ағымдағы[Ауыстыру]) )

Мұнда шынымен не болып жатыр?

1. Бастапқы мән ретінде (ұрық) бағаннан бірінші ебедейсіз мәтінді аламыз [Мекен-жайы] біздің үстел: 199034, Санкт-Петербург, көш. Беринга, д. 1
2. Содан кейін List.Accumate тізімнің элементтерін бір-бірден қайталайды – Анықтамалық. Бұл тізімнің әрбір элементі «Нені табу керек – Нені ауыстыру керек» жұбынан немесе басқаша айтқанда, каталогтағы келесі жолдан тұратын жазба.
3. Аккумулятор функциясы айнымалыға қояды мемлекет бастапқы мән (бірінші мекенжай 199034, Санкт-Петербург, көш. Беринга, д. 1) және онда аккумуляторлық функцияны орындайды – стандартты M-функциясы арқылы ауыстыру операциясы Мәтін.Ауыстыру (Excel бағдарламасының SUBSTITUTE функциясына ұқсас). Оның синтаксисі:

   Text.Replace (түпнұсқа мәтін, біз не іздеп жатырмыз, немен ауыстырамыз)

   және бізде:

• мемлекет орналасқан біздің лас мекенжайымыз мемлекет (ол жерден жету ұрық)
  
• ағымдағы[Іздеу] – өріс мәні Табу тізімнің келесі қайталанған жазбасынан анықтамалық, ол айнымалыда жатыр ағымдағы
• ағымдағы[Ауыстыру] – өріс мәні Ауыстыру тізімнің келесі қайталанған жазбасынан анықтамалықжату ағымдағы

Осылайша, әрбір мекенжай үшін [Табу] өрісіндегі мәтінді [Ауыстыру] өрісіндегі мәнмен ауыстыра отырып, каталогтағы барлық жолдарды санаудың толық циклі әр жолы орындалады.

Сізге идея келді деп үміттенемін 🙂

• Формулаларды пайдаланып тізімдегі мәтінді жаппай ауыстырыңыз
  
• Power Query ішіндегі тұрақты өрнектер (RegExp).