Групна замена текста у Повер Куери-у са функцијом Лист.Аццумулате

Како брзо и масовно заменити текст према листи референци формулама – то смо већ средили. Сада покушајмо да то урадимо у Повер Куери-ју.

Као што се често дешава извршити овај задатак је много лакши од објашњавања зашто ради, али хајде да покушамо да урадимо обоје 🙂

Дакле, имамо две „паметне“ динамичке табеле креиране из обичних опсега са пречицом на тастатури Цтрл+T или тим Почетна – Формат као табела (Почетна — Формат као табела):

Позвао сам први сто Датум, друга табела – имениккористећи поље Назив табеле (име табеле) табулатор Конструктор (Дизајн).

Задатак: заменити у адресама у табели Датум сва појављивања из колоне Да пронађу Приручник њиховим одговарајућим тачним колегама из колоне Субституте. Остатак текста у ћелијама треба да остане нетакнут.

Корак 1. Учитајте директоријум у Повер Куери и претворите га у листу

Након што поставите активну ћелију на било које место у референтној табели, кликните на картицу Датум (Датум)или на картици Повер Куери (ако имате стару верзију Екцел-а и инсталирали сте Повер Куери као додатак на посебној картици) на дугмету Из табеле/опсега (Из табеле/опсега).

Референтна табела ће бити учитана у Повер Куери уређивач упита:

Да се ​​не мешате, аутоматски додат корак модификованог типа (промењен тип) у десном панелу, примењени кораци се могу безбедно избрисати, остављајући само корак извор (Извор):

Сада, да бисмо извршили даље трансформације и замене, треба да претворимо ову табелу у листу (листу).

Лирска дигресија

• Табела је дводимензионални низ који се састоји од неколико редова и колона.
• Запис (Снимање) – једнодимензионални низ-низ, који се састоји од неколико поља-елемената са именима, нпр. [Име = „Маша“, Пол = „ф“, Старост = 25]
  
• Листа – једнодимензионални низ-колона, који се састоји од неколико елемената, нпр {КСНУМКС, КСНУМКС, КСНУМКС, КСНУМКС, КСНУМКС} or { "Вера Нада Љубав" }
  

Да бисмо решили наш проблем, првенствено ће нас занимати тип Листа.

Трик је у томе што ставке листе у Повер Куерију могу бити не само банални бројеви или текст, већ и друге листе или записи. Управо у тако лукавој листи (листи), која се састоји од записа (записа) треба да окренемо наш именик. У Повер Куери синтаксичкој нотацији (уноси у угластим заградама, листе у витичастим заградама) ово би изгледало овако:

{

    [ Финд = „Св. Петерсбург”, Замени = „Св. Петерсбург”] ,

    [ Финд = „Св. Петерсбург”, Замени = „Св. Петерсбург”] ,

    [ Пронађи = „Петар“, Замени = „Св. Петерсбург”] ,

итд

}

Таква трансформација се врши помоћу посебне функције М језика уграђене у Повер Куери – Табле.ТоРецордс. Да бисте је применили директно у траци формуле, додајте ову функцију у код корака тамо извор.

Било је:

После:

Након додавања функције Табле.ТоРецордс, изглед наше табеле ће се променити – претвориће се у листу записа. Садржај појединачних записа може се видети на дну окна за приказ кликом на позадину ћелије поред било које речи Запис (али ни једном речју!)

Поред наведеног, има смисла додати још један потез – за кеширање (баферовање) наше креиране листе. Ово ће приморати Повер Куери да учита нашу листу за тражење једном у меморију и да је неће поново израчунати када јој касније приступимо да бисмо је заменили. Да бисте то урадили, умотајте нашу формулу у другу функцију - Лист.Буффер:

Такво кеширање ће дати веома приметно повећање брзине (за неколико пута!) са великом количином почетних података које треба обрисати.

Овим је завршена припрема приручника.

Остаје да кликнете на Почетна – Затвори и учитај – Затвори и учитај на… (Почетна — Затвори&Учитај — Затвори&Учитај у..), изаберите опцију Само створите везу (Само креирајте везу) и вратите се у Екцел.

Корак 2. Учитавање табеле са подацима

Овде је све отрцано. Као и раније са референтном књигом, долазимо до било ког места у табели, кликнемо на картицу Датум дугме Из табеле/опсега и наш сто Датум улази у Повер Куери. Аутоматски додат корак модификованог типа (промењен тип) такође можете уклонити:

Са њим нису потребне никакве посебне припремне радње и прелазимо на оно најважније.

Корак 3. Извршите замене помоћу функције Лист.Аццумулате

Хајде да додамо израчунату колону у нашу табелу података помоћу команде Додавање колоне – Прилагођена колона (Додај колону — Прилагођена колона): и унесите назив додате колоне у прозор који се отвори (нпр. исправљена адреса) и нашу магијску функцију Лист.Аццумулате:

Остаје да кликнете на OK – и добијамо колону са извршеним заменама:

Напоменути да:

• Пошто је Повер Куери осетљив на велика и мала слова, није било замене у претпоследњем реду, јер у директоријуму имамо „СПб“, а не „СПб“.
• Ако у изворним подацима постоји неколико поднизова које треба заменити одједном (на пример, у 7. реду морате да замените и „С-Пб“ и „Проспект“), онда то не ствара никакве проблеме (за разлику од замене формулама из претходни метод).
• Ако нема шта да се замени у изворном тексту (9. ред), онда нема грешака (за разлику од, опет, замене формулама).

Брзина таквог захтева је веома, веома пристојна. На пример, за табелу почетних података величине 5000 редова, овај упит је ажуриран за мање од једне секунде (без баферовања, иначе, око 3 секунде!)

Како функционише функција Лист.Аццумулате

У принципу, ово би могао бити крај (да ја напишем, а да ти прочиташ) овај чланак. Ако желите да не само да будете у могућности, већ и да разумете како то функционише „испод хаубе“, онда ћете морати да зароните мало дубље у зечју рупу и да се позабавите функцијом Лист.Аццумулате, која је извршила све масовне замене ради за нас.

Синтакса за ову функцију је:

=Листа.Акумулирај(листа, семе, акумулатор)

где

• листа је листа чије елементе понављамо. 
• семе - почетно стање
• акумулатор – функција која врши неку операцију (математичку, текстуалну итд.) на следећем елементу листе и акумулира резултат обраде у посебној променљивој.
  

Генерално, синтакса за писање функција у Повер Куерију изгледа овако:

(аргумент1, аргумент2, … аргументН) => неке акције са аргументима

На пример, функција сумирања се може представити као:

(а, б) => а + б

За Лист.Аццумулате , ова функција акумулатора има два потребна аргумента (могу се именовати било шта, али уобичајена имена су били су и струја, као у званичној помоћи за ову функцију, где:

• били су – променљива у којој се акумулира резултат (њена почетна вредност је она која је горе поменута семе)
  
• струја – следећа итерирана вредност са листе листа
  

На пример, хајде да погледамо кораке логике следеће конструкције:

=Листа.Акумулирај({КСНУМКС, КСНУМКС, КСНУМКС}, 10, (стање, тренутно) => стање + струја)

Ово је последње акумулирано били су вредност је наша функција Лист.Аццумулате и као резултат даје:

Ако мало маштате, онда помоћу функције Лист.Аццумулате можете симулирати, на пример, Екцел функцију ЦОНЦАТЕНАТЕ (у Повер Куери-у, њен аналог се зове Тект.Цомбине) користећи израз:

Или чак потражите максималну вредност (имитација Екцел-ове МАКС функције, која се у Повер Куерију зове Лист.Мак):

Међутим, главна карактеристика Лист.Аццумулате је могућност обраде не само једноставних текстуалних или нумеричких листа као аргумената, већ и сложенијих објеката – на пример, листе-са-листе или листе-из-записа (здраво, Директоријум!)

Погледајмо поново конструкцију која је извршила замену у нашем задатку:

Лист.Аццумулате(именик, [Адреса], (стање, тренутно) => Тект.Реплаце(стање, тренутно[Пронађи], тренутно[Замени]) )

Шта се стварно дешава овде?

1. Као почетна вредност (семе) узимамо први неспретни текст из колоне [Адреса] наш сто: 199034, Санкт Петербург, ул. Беринга, д. 1
2. Затим Лист.Аццумулате понавља елементе листе један по један – Приручник. Сваки елемент ове листе је запис који се састоји од пара поља „Шта пронаћи – чиме заменити” или, другим речима, следећи ред у директоријуму.
3. Функција акумулатора ставља у променљиву били су почетна вредност (прва адреса 199034, Санкт Петербург, ул. Беринга, д. 1) и на њему врши функцију акумулатора – операцију замене помоћу стандардне М-функције Тект.Реплаце (аналогно Екцел-овој функцији СУБСТИТУТЕ). Његова синтакса је:

   Тект.Реплаце( оригинални текст, шта тражимо, чиме замењујемо)

   и овде имамо:

• били су је наша прљава адреса, која лежи у били су (доћи тамо од семе)
  
• тренутно[Претрага] – вредност поља Да пронађу од следећег понављаног уноса листе именик, који лежи у променљивој струја
• тренутно [Замени] – вредност поља Субституте од следећег понављаног уноса листе имениклежи у струја

Тако се за сваку адресу сваки пут покреће пуни циклус набрајања свих редова у директоријуму, замењујући текст из поља [Пронађи] вредношћу из поља [Замени].

Надам се да сте добили идеју 🙂

• Групно замените текст на листи помоћу формула
  
• Регуларни изрази (РегЕкп) у Повер Куерију