содржина
Пребарувањето клучни зборови во изворниот текст е една од најчестите задачи при работа со податоци. Ајде да го разгледаме неговото решение на неколку начини користејќи го следниов пример:
Да претпоставиме дека јас и ти имаме листа на клучни зборови – имиња на марки на автомобили – и голема табела со сите видови резервни делови, каде описите понекогаш може да содржат една или неколку такви брендови одеднаш, ако резервниот дел одговара на повеќе од една марка на автомобил. Наша задача е да ги пронајдеме и прикажеме сите откриени клучни зборови во соседните ќелии преку даден знак за сепаратор (на пример, запирка).
Метод 1. Power Query
Се разбира, прво ги претвораме нашите табели во динамични („паметни“) користејќи кратенка на тастатурата Ctrl+T или команди Дома – Форматирајте како табела (Дома - Формат како табела), дајте им имиња (на пример Поштенски маркии Резервни делови) и вчитајте еден по еден во уредувачот Power Query со избирање на јазичето Податоци – од табела/опсег (Податоци - од табела/опсег). Ако имате постари верзии на Excel 2010-2013, каде што Power Query е инсталиран како посебен додаток, тогаш саканото копче ќе биде на јазичето Барање за напојување. Ако имате сосема нова верзија на Excel 365, тогаш копчето Од Табела/Опсег се јави таму сега Со лисја (Од лист).
Откако ќе ја вчитаме секоја табела во Power Query, се враќаме во Excel со командата Дома — Затвори и вчитај — Затвори и вчитај во… — Креирај само врска (Дома — Затвори и вчитај — Затвори и вчитај во… — Создај само врска).
Сега ајде да создадеме дупликат барање Резервни деловисо десен клик на неа и селектирање Дупликат барање (Дупликат барање), потоа преименувајте го добиеното барање за копирање во Резултатите и ќе продолжиме да работиме со него.
Логиката на дејствијата е следна:
- На јазичето Напредно Додавање колона изберете тим Прилагодена колона (Додај колона - Прилагодена колона) и внесете ја формулата = Марки. Откако ќе кликнете на OK ќе добиеме нова колона, каде што во секоја ќелија ќе има вгнездена табела со список на нашите клучни зборови – брендови на производители на автомобили:
- Користете го копчето со двојни стрелки во заглавието на додадената колона за да ги проширите сите вгнездени табели. Во исто време, линиите со описи на резервни делови ќе се помножат со повеќекратно од бројот на брендови и ќе ги добиеме сите можни парови-комбинации на „резервен дел-бренд“:
- На јазичето Напредно Додавање колона изберете тим Условна колона (Условна колона) и поставете услов за проверка на појавата на клучен збор (марка) во изворниот текст (опис на дел):
- За да го направите пребарувањето нечувствително, рачно додајте го третиот аргумент во лентата со формули Спореди.OrdinalIgnoreCase до функцијата за проверка на појава Текст.Содржи (ако лентата со формула не е видлива, тогаш може да се овозможи на табот преглед):
- Ја филтрираме добиената табела, оставајќи ги само оние во последната колона, односно совпаѓањата и ја отстрануваме непотребната колона Појавувања.
- Групирање идентични описи со командата Група од табот Трансформација (Трансформација - Група по). Како операција за агрегација, изберете Сите линии (Сите редови). На излезот, добиваме колона со табели, која ги содржи сите детали за секој резервен дел, вклучувајќи ги и брендовите на производителите на автомобили што ни се потребни:
- За да извлечете оценки за секој дел, додадете уште една пресметана колона на картичката Додавање колона – Прилагодена колона (Додај колона - Прилагодена колона) и користете формула која се состои од табела (тие се наоѓаат во нашата колона детали за) и името на извлечената колона:
- Ние кликнуваме на копчето со двојни стрелки во заглавието на добиената колона и ја избираме командата Извлечете вредности (Извлечете вредности)за да излезете поштенски марки со кој било знак за разграничување што го сакате:
- Отстранување на непотребна колона детали за.
- За да ги додадеме во добиената табела деловите што исчезнале од неа, каде што не се пронајдени марки во описите, ја извршуваме постапката за комбинирање на барањето Резултат со оригинално барање Резервни делови копчето. Комбинираат табот Почетна (Дома - Спојување на прашања). Тип на поврзување - Надворешно спојување десно (Десен надворешен спој):
- Останува само да ги отстраниме дополнителните колони и да ги преименуваме-преместиме преостанатите - и нашата задача е решена:
Метод 2. Формули
Ако имате верзија на Excel 2016 или понова, тогаш нашиот проблем може да се реши на многу компактен и елегантен начин користејќи ја новата функција КОМБИНА (ПРИКЛУЧЕТЕ ТЕКСТ):
Логиката зад оваа формула е едноставна:
- функција Пребарување (НАЈДЕТЕ) ја бара појавата на секоја марка по ред во тековниот опис на делот и го враќа или серискиот број на симболот, од кој е пронајден брендот, или грешката #VALUE! ако брендот не е во описот.
- Потоа користејќи ја функцијата IF (АКО) и ЕОШИБКА (ИСЕРА) ги заменуваме грешките со празна текстуална низа „“, а редните броеви на знаците со самите имиња на брендови.
- Резултирачката низа од празни ќелии и пронајдени марки се составува во една низа преку даден разделувачки знак со помош на функцијата КОМБИНА (ПРИКЛУЧЕТЕ ТЕКСТ).
Споредба на изведба и баферирање на барањето за моќност за забрзување
За тестирање на перформансите, да земеме табела од 100 описи на резервни делови како првични податоци. На него ги добиваме следните резултати:
- Време на повторна пресметка по формули (Метод 2) – 9 сек. кога првпат ќе ја копирате формулата во целата колона и 2 сек. на повторени (влијае на пуферирање, веројатно).
- Времето на ажурирање на барањето Power Query (Метод 1) е многу полошо – 110 секунди.
Се разбира, многу зависи од хардверот на одреден компјутер и инсталираната верзија на Office и ажурирањата, но целокупната слика, мислам, е јасна.
За да го забрзаме барањето за Power Query, ајде да ја тампонираме табелата за пребарување Поштенски марки, бидејќи не се менува во процесот на извршување на барањето и не е неопходно постојано да се пресметува (како што тоа де факто го прави Power Query). За ова ја користиме функцијата Табела.Бафер од вградениот јазик Power Query М.
За да го направите ова, отворете барање Резултатите и на јазичето преглед притисни го копчето Напреден уредник (Преглед - Напреден уредник). Во прозорецот што се отвора, додадете линија со нова променлива Марки 2, што ќе биде баферирана верзија на директориумот на нашиот производител на автомобили и користете ја оваа нова променлива подоцна во следната команда за барање:
По ваквото усовршување, брзината на ажурирање на нашето барање се зголемува за речиси 7 пати - до 15 секунди. Сосема поинаква работа 🙂
- Нејасно пребарување на текст во Power Query
- Масовно замена на текст со формули
- Масовна замена на текст во Power Query со функција List.Acumulate