Съдържание
Търсенето на ключови думи в изходния текст е една от най-честите задачи при работа с данни. Нека разгледаме решението му по няколко начина, като използваме следния пример:
Да предположим, че вие и аз имаме списък с ключови думи – имената на марките автомобили – и голяма таблица с всички видове резервни части, където описанията понякога могат да съдържат една или няколко такива марки наведнъж, ако резервната част пасва на повече от една марка автомобил. Нашата задача е да намерим и покажем всички открити ключови думи в съседни клетки чрез даден разделителен знак (например запетая).
Метод 1. Power Query
Разбира се, първо превръщаме нашите таблици в динамични („умни“) с помощта на клавишна комбинация Ctrl+T или команди Начало – Форматиране като таблица (Начало — Форматиране като таблица), дайте им имена (напр Маркии Резервни части) и заредете един по един в редактора на Power Query, като изберете в раздела Данни – От таблица/диапазон (Данни — от таблица/обхват). Ако имате по-стари версии на Excel 2010-2013, където Power Query е инсталиран като отделна добавка, тогава желаният бутон ще бъде в раздела Запитване за захранване. Ако имате чисто нова версия на Excel 365, тогава бутонът От таблица/обхват обади се там сега С листа (От лист).
След като заредим всяка таблица в Power Query, се връщаме обратно в Excel с командата Начало — Затвори и зареди — Затвори и зареди до… — Създай само връзка (Начало — Затваряне и зареждане — Затваряне и зареждане в… — Само създаване на връзка).
Сега нека създадем дублирана заявка Резервни частикато щракнете с десния бутон върху него и изберете Дублирана заявка (Дублирана заявка), след което преименувайте получената заявка за копиране на Резултатите и ще продължим да работим с него.
Логиката на действията е следната:
- В раздела Разширени Добавяне на колона изберете екип Персонализирана колона (Добавяне на колона — Персонализирана колона) и въведете формулата = Марки. След като щракнете върху OK ще получим нова колона, където във всяка клетка ще има вложена таблица със списък на нашите ключови думи – марки на автомобилни производители:
- Използвайте бутона с двойни стрелки в заглавката на добавената колона, за да разгънете всички вложени таблици. В същото време редовете с описания на резервни части ще се умножат по кратно на броя на марките и ще получим всички възможни двойки-комбинации „резервна част-марка“:
- В раздела Разширени Добавяне на колона изберете екип Условна колона (Условна колона) и задайте условие за проверка на появата на ключова дума (марка) в изходния текст (описание на част):
- За да направите търсенето нечувствително към главни и малки букви, ръчно добавете третия аргумент в лентата за формули Compare.OrdinalIgnoreCase към функцията за проверка на събития Текст. Съдържа (ако лентата с формули не се вижда, тогава тя може да бъде активирана в раздела Оценки на посетители):
- Филтрираме получената таблица, оставяйки само единици в последната колона, т.е. съвпадения и премахваме ненужната колона Случаи.
- Групиране на идентични описания с командата Групирай по етикет Трансформация (Трансформиране — Групиране по). Като операция за агрегиране изберете Всички линии (Всички редове). На изхода получаваме колона с таблици, която съдържа всички подробности за всяка резервна част, включително марките производители на автомобили, от които се нуждаем:
- За да извлечете оценки за всяка част, добавете друга изчислена колона в раздела Добавяне на колона – Персонализирана колона (Добавяне на колона — Персонализирана колона) и използвайте формула, състояща се от таблица (те се намират в нашата колона Детайли) и името на извлечената колона:
- Щракваме върху бутона с двойни стрелки в заглавката на получената колона и избираме командата Извлечете стойности (Извличане на стойности)за извеждане на печати с произволен разделителен знак, който искате:
- Премахване на ненужна колона Детайли.
- За да добавите към получената таблица частите, които са изчезнали от нея, където в описанията не са намерени марки, изпълняваме процедурата за комбиниране на заявката Резултат с оригинална заявка Резервни части бутон Комбинирам етикет Начало (Начало — Обединяване на заявки). Вид на връзката - Външно съединяване вдясно (Дясно външно съединение):
- Всичко, което остава, е да премахнем излишните колони и да преименуваме-преместим останалите – и нашата задача е решена:
Метод 2. Формули
Ако имате версия на Excel 2016 или по-нова, тогава нашият проблем може да бъде решен по много компактен и елегантен начин с помощта на новата функция COMBINE (СЪЕДИНЯВАНЕ НА ТЕКСТА):
Логиката зад тази формула е проста:
- функция ТЪРСИ (НАМИРАМ) търси появата на всяка марка последователно в текущото описание на частта и връща или серийния номер на символа, започвайки от който е открита марката, или грешката #VALUE! ако марката я няма в описанието.
- След това с помощта на функцията IF (АКО) и ЕОШИБКА (ISERROR) заместваме грешките с празен текстов низ “”, а поредните номера на знаците със самите имена на марки.
- Полученият масив от празни клетки и намерени марки се събира в единичен низ чрез даден разделителен знак с помощта на функцията COMBINE (СЪЕДИНЯВАНЕ НА ТЕКСТА).
Сравнение на производителността и Power Query буфериране на заявки за ускоряване
За тестване на производителността, нека вземем таблица от 100 описания на резервни части като първоначални данни. На него получаваме следните резултати:
- Време за преизчисляване по формули (Метод 2) – 9 сек. когато за първи път копирате формулата в цялата колона и 2 сек. при многократно (буферирането засяга вероятно).
- Времето за актуализиране на заявката за Power Query (Метод 1) е много по-лошо – 110 секунди.
Разбира се, много зависи от хардуера на конкретен компютър и инсталираната версия на Office и актуализации, но общата картина, мисля, е ясна.
За да ускорим заявка на Power Query, нека буферираме справочната таблица Марки, защото не се променя в процеса на изпълнение на заявката и не е необходимо постоянно да се преизчислява (както де факто прави Power Query). За целта използваме функцията Таблица.Буфер от вградения език на Power Query M.
За да направите това, отворете заявка Резултатите и на раздела Оценки на посетители Натисни бутона Разширен редактор (Преглед — Разширен редактор). В прозореца, който се отваря, добавете ред с нова променлива Марки 2, която ще бъде буферирана версия на нашата директория на автомобилния производител, и използвайте тази нова променлива по-късно в следната команда за заявка:
След такова усъвършенстване скоростта на обновяване на заявката ни се увеличава почти 7 пъти – до 15 секунди. Съвсем различно нещо 🙂
- Търсене на размит текст в Power Query
- Групово заместване на текст с формули
- Групово заместване на текст в Power Query с функция List.Accumulate