Садржај
Претраживање кључних речи у изворном тексту један је од најчешћих задатака при раду са подацима. Погледајмо његово решење на неколико начина користећи следећи пример:
Претпоставимо да ви и ја имамо листу кључних речи – имена марки аутомобила – и велику табелу свих врста резервних делова, где описи понекад могу да садрже једну или више таквих марки одједном, ако резервни део одговара више од једног марка аутомобила. Наш задатак је да пронађемо и прикажемо све откривене кључне речи у суседним ћелијама преко датог знака за раздвајање (на пример, зарез).
Метод 1. Повер Куери
Наравно, прво претварамо наше табеле у динамичке („паметне“) користећи пречицу на тастатури Цтрл+T или команде Почетна – Формат као табела (Почетна — Формат као табела), дајте им имена (нпр Маркеи Резервни делови) и учитавајте један по један у Повер Куери едитор тако што ћете изабрати на картици Подаци – из табеле/опсега (Подаци — из табеле/опсега). Ако имате старије верзије програма Екцел 2010-2013, где је Повер Куери инсталиран као посебан додатак, онда ће жељено дугме бити на картици Повер Куери. Ако имате потпуно нову верзију програма Екцел 365, онда дугме Из табеле/опсега позван тамо сада Са лишћем (Са листа).
Након учитавања сваке табеле у Повер Куери, враћамо се у Екцел са командом Почетна — Затвори и учитај — Затвори и учитај до… — Само креирај везу (Почетна — Затвори и учитај — Затвори и учитај у… — Само креирај везу).
Сада направимо дупли захтев Резервни деловитако што ћете кликнути десним тастером миша на њега и изабрати Дупликат захтева (Дупликат упита), а затим преименујте резултујући захтев за копирање у Резултати и наставићемо да радимо са њим.
Логика акција је следећа:
- На картици Напредно Додавање колоне изабрати тим Прилагођена колона (Додај колону — Прилагођена колона) и унесите формулу = Брендови. Након што кликнете на OK добићемо нову колону, где ће у свакој ћелији бити угнежђена табела са листом наших кључних речи – брендови произвођача аутомобила:
- Користите дугме са двоструким стрелицама у заглављу додате колоне да бисте проширили све угнежђене табеле. Истовремено, редови са описима резервних делова ће се множити вишеструким бројем марки и добићемо све могуће парове-комбинације „бренд резервних делова“:
- На картици Напредно Додавање колоне изабрати тим Условна колона (Условна колона) и поставити услов за проверу појављивања кључне речи (бренд) у изворном тексту (опис дела):
- Да бисте учинили претраживање неосетљивим на велика и мала слова, ручно додајте трећи аргумент у траку формуле Цомпаре.ОрдиналИгнореЦасе на функцију провере појаве Тект.Цонтаинс (ако трака формуле није видљива, онда се може омогућити на картици преглед):
- Филтрирамо резултујућу табелу, остављајући само оне у последњој колони, односно подударања и уклањамо непотребну колону Појаве.
- Груписање идентичних описа са командом Група од табулатор Трансформација (Трансформација — Групирај по). Као операцију агрегације, изаберите Све линије (Сви редови). На излазу добијамо колону са табелама, која садржи све детаље за сваки резервни део, укључујући брендове произвођача аутомобила који су нам потребни:
- Да бисте издвојили оцене за сваки део, додајте још једну израчунату колону на картицу Додавање колоне – Прилагођена колона (Додај колону — Прилагођена колона) и користите формулу која се састоји од табеле (налазе се у нашој колони Detalji) и назив издвојене колоне:
- Кликнемо на дугме са двоструким стрелицама у заглављу резултујуће колоне и изаберемо команду Издвој вредности (Издвоји вредности)да избаците марке са било којим знаком за раздвајање који желите:
- Уклањање непотребне колоне Detalji.
- Да бисмо у резултујућу табелу додали делове који су нестали из ње, где у описима нису пронађени брендови, спроводимо процедуру за комбиновање упита Резултат са оригиналним захтевом Резервни делови дугме Комбиновати табулатор Početna (Почетна — Обједини упите). Врста конекције - Десно спољно спајање (десни спољни спој):
- Остаје само да уклонимо додатне колоне и преименујемо-преместимо преостале – и наш задатак је решен:
Метод 2. Формуле
Ако имате верзију програма Екцел 2016 или новију, онда се наш проблем може решити на веома компактан и елегантан начин помоћу нове функције ЦОМБИНЕ (ТЕКСТ ПРИДРУЖИ СЕ):
Логика иза ове формуле је једноставна:
- функција СЕАРЦХ (ПРОНАЂИ) тражи појаву сваке марке редом у тренутном опису дела и враћа или серијски број симбола, почевши од ког је бренд пронађен, или грешку #ВРЕДНОСТ! ако марка није у опису.
- Затим користећи функцију IF (АКО) и ЕОСХИБКА (ИСГРЕШКА) грешке замењујемо празним текстуалним низом „“, а редне бројеве знакова самим називима брендова.
- Добијени низ празних ћелија и пронађених брендова се саставља у један стринг кроз дати знак за раздвајање помоћу функције ЦОМБИНЕ (ТЕКСТ ПРИДРУЖИ СЕ).
Поређење перформанси и баферовање упита Повер Куери за убрзање
За тестирање перформанси, узмимо табелу са 100 описа резервних делова као почетне податке. На њему добијамо следеће резултате:
- Време прерачунавања по формулама (2. метод) – 9 сек. када први пут копирате формулу у целу колону и 2 сек. при поновљеном (вероватно утиче пуферовање).
- Време ажурирања Повер Куери упита (Метода 1) је много горе – 110 секунди.
Наравно, много зависи од хардвера одређеног рачунара и инсталиране верзије Оффицеа и ажурирања, али укупна слика је, мислим, јасна.
Да бисмо убрзали Повер Куери упит, хајде да баферујемо табелу за тражење Марке, јер се не мења у процесу извршавања упита и није потребно да га стално прерачунавате (као што Повер Куери де фацто чини). За ово користимо функцију Табле.Буффер из уграђеног Повер Куери језика М.
Да бисте то урадили, отворите упит Резултати и на картици преглед притисните дугме Напредни уређивач (Приказ — Напредни уређивач). У прозору који се отвори додајте ред са новом променљивом Марки 2, који ће бити баферована верзија нашег директоријума произвођача аутомобила и користити ову нову променљиву касније у следећој команди упита:
Након таквог прецизирања, брзина ажурирања нашег захтева се повећава за скоро 7 пута – до 15 секунди. Сасвим другачија ствар 🙂
- Претраживање нејасног текста у Повер Куери-ју
- Групна замена текста формулама
- Групна замена текста у Повер Куери-у са функцијом Лист.Аццумулате