змест
Пошук ключавых слоў у зыходным тэксце - адна з найбольш частых задач пры працы з дадзенымі. Давайце разгледзім яго рашэнне некалькімі спосабамі на наступным прыкладзе:
Выкажам здагадку, што ў нас з вамі ёсць спіс ключавых слоў - назваў марак аўтамабіляў - і вялікая табліца ўсіх відаў запчастак, дзе ў апісаннях часам можа быць адна ці некалькі такіх марак адразу, калі запчастка падыходзіць да некалькіх марка аўтамабіля. Наша задача - знайсці і адлюстраваць усе выяўленыя ключавыя словы ў суседніх вочках праз зададзены знак падзельніка (напрыклад, коску).
Спосаб 1. Power Query
Вядома, спачатку мы ператворым нашы табліцы ў дынамічныя («разумныя») з дапамогай спалучэння клавіш Ctrl+T або каманды Галоўная - фармат у выглядзе табліцы (Галоўная — фармат у табліцу), дайце ім імёны (напрыклад Маркіи Запасныя часткі) і загружайце адзін за адным у рэдактар Power Query, выбіраючы на ўкладцы Дадзеныя – з табліцы/дыяпазону (Дадзеныя — з табліцы/дыяпазону). Калі ў вас старыя версіі Excel 2010-2013, дзе Power Query усталяваны асобнай надбудовай, то патрэбная кнопка будзе на ўкладцы Запыт на харчаванне. Калі ў вас новая версія Excel 365, то кнопка З табліцы/дыяпазону патэлефанаваў туды зараз З лісточкамі (З аркуша).
Пасля загрузкі кожнай табліцы ў Power Query мы вяртаемся ў Excel з дапамогай каманды Дадому — Закрыць і загрузіць — Закрыць і загрузіць у… — Толькі стварыць злучэнне (Дадому — Зачыніць і загрузіць — Зачыніць і загрузіць у… — Толькі стварыць злучэнне).
Зараз давайце створым дублікат запыту Запасныя часткіпстрыкнуўшы па ім правай кнопкай мышы і выбраўшы Дублікат запыту (Дублікат запыту), затым перайменуйце атрыманы запыт на копію ў Вынікі і мы будзем працягваць з ім працаваць.
Логіка дзеянняў такая:
- На ўкладцы Дадаткова Даданне слупка выбраць каманду Карыстальніцкі слупок (Дадаць слупок — карыстальніцкі слупок) і ўвядзіце формулу = Брэнды. Пасля націску на OK мы атрымаем новы слупок, дзе ў кожнай ячэйцы будзе ўкладзеная табліца са спісам нашых ключавых слоў - брэндаў аўтавытворцаў:
- Выкарыстоўвайце кнопку з падвойнымі стрэлкамі ў загалоўку дададзенага слупка, каб разгарнуць усе ўкладзеныя табліцы. Пры гэтым радкі з апісаннямі запчастак памножым на колькасць марак, і мы атрымаем усе магчымыя пары-камбінацыі «запчастка-марка»:
- На ўкладцы Дадаткова Даданне слупка выбраць каманду Умоўны слупок (Умоўны слупок) і задаць умову праверкі ўваходжання ключавога слова (брэнд) у зыходны тэкст (апісанне часткі):
- Каб зрабіць пошук неадчувальным да рэгістра, уручную дадайце трэці аргумент у радок формул Compare.OrdinalIgnoreCase да функцыі праверкі ўваходжання Тэкст.Змяшчае (калі радок формул не бачны, яго можна ўключыць на ўкладцы агляд):
- Атрыманую табліцу фільтруем, пакідаючы ў апошнім слупку толькі адзінкі, гэта значыць супадзенні, і выдаляем непатрэбны слупок З'явы.
- Групоўка аднолькавых апісанняў з дапамогай каманды група па таб Пераўтварэнне (Пераўтварэнне — Група па). У якасці аперацыі агрэгавання выберыце Усе радкі (Усе радкі). На выхадзе атрымліваем слупок з табліцамі, у якім змяшчаецца ўся інфармацыя па кожнай запчастцы, у тым ліку патрэбныя нам маркі аўтавытворцаў:
- Каб атрымаць адзнакі для кожнай часткі, дадайце яшчэ адзін вылічальны слупок на ўкладцы Даданне слупка - Карыстальніцкі слупок (Дадаць слупок — карыстальніцкі слупок) і выкарыстоўваць формулу, якая складаецца з табліцы (яны знаходзяцца ў нашым слупку дэталі) і імя вынятага слупка:
- Націскаем на кнопку з падвойнымі стрэлкамі ў загалоўку атрыманага слупка і выбіраем каманду Выманне значэнняў (Выняць значэнні)каб вывесці штампы з любым сімвалам падзельніка, які вы хочаце:
- Выдаленне непатрэбнага слупка дэталі.
- Каб дадаць у атрыманую табліцу зніклыя з яе запчасткі, у апісаннях якіх не знойдзены маркі, выконваем працэдуру аб'яднання запыту Вынік з арыгінальным запытам Запасныя часткі кнопка Спалучаць таб Галоўная (Галоўная — аб'яднаць запыты). Тып злучэння - Вонкавае злучэнне справа (Правае вонкавае злучэнне):
- Засталося толькі прыбраць лішнія слупкі і перайменаваць-перанесці тыя, што засталіся – і наша задача вырашана:
Спосаб 2. Формулы
Калі ў вас версія Excel 2016 або больш позняя, наша праблема можа быць вырашана вельмі кампактна і элегантна з дапамогай новай функцыі Камбінат (ДАЛУЧЭННЕ ТЭКСТУ):
Логіка гэтай формулы простая:
- функцыя ПОШУК (ЗНАЙСЦІ) шукае ўваходжанне кожнай маркі па чарзе ў бягучым апісанні дэталі і вяртае або парадкавы нумар сімвала, пачынаючы з якога была знойдзена марка, або памылку #ЗНАЧЭННЕ! калі брэнда няма ў апісанні.
- Затым з дапамогай функцыі IF (КАЛІ) и ЕАШЫБКА (ISПАМЫЛКА) памылкі замяняем пустым тэкставым радком «», а парадкавыя нумары знакаў самімі назвамі брэндаў.
- Атрыманы масіў пустых вочак і знойдзеных брэндаў збіраецца ў адзіны радок праз зададзены сімвал-падзельнік з дапамогай функцыі Камбінат (ДАЛУЧЭННЕ ТЭКСТУ).
Параўнанне прадукцыйнасці і буферызацыя запытаў 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