мазмуну
Булак текстинде ачкыч сөздөрдү издөө маалыматтар менен иштөөдө эң кеңири таралган иштердин бири болуп саналат. Төмөнкү мисалды колдонуп, бир нече жол менен анын чечүү жолун карап көрөлү:
Келгиле, сизде жана менде ачкыч сөздөрдүн тизмеси – унаалардын маркаларынын аттары – жана запастык бөлүктөрдүн бардык түрлөрүнүн чоң таблицасы бар деп коёлу, анда сүрөттөмөлөр кээде бир эле учурда бир же бир нече ушундай маркаларды камтышы мүмкүн, эгерде запастык бөлүк бирден ашык туура келсе машинанын маркасы. Биздин милдет - аныкталган бөлүүчү белги (мисалы, үтүр) аркылуу кошуна уячаларда табылган бардык ачкыч сөздөрдү табуу жана көрсөтүү.
Метод 1. Power Query
Албетте, алгач клавиатуранын жарлыгын колдонуп, таблицаларыбызды динамикалык («акылдуу») кылып коебуз Ctrl+T же буйруктар Башкы бет – Таблица катары форматтоо (Башкы бет — Таблица катары формат), аларга ысымдарды бериңиз (мисалы Маркалари запастык бөлүктөр) жана өтмөктөн тандоо менен Power Query редакторуна бирден жүктөңүз Берилиштер – Таблицадан/Аразиядан (Маалымат — Таблицадан/Аразиядан). Эгер сизде Power Query өзүнчө кошумча катары орнотулган Excel 2010-2013 эски версиялары болсо, анда керектүү баскыч өтмөктө болот. күч суроо. Эгер сизде Excel 365тин жаңы версиясы болсо, анда баскыч Таблицадан/Арападан азыр ошол жакка чалды Жалбырактары менен (Барактан).
Power Queryдеги ар бир таблицаны жүктөгөндөн кийин, биз буйрук менен Excelге кайтып келебиз Башкы бет — Жабуу жана жүктөө — Жабуу жана жүктөө... — Байланыш түзүү гана (Башкы бет — Жабуу жана жүктөө — Жабуу жана жүктөө... — Байланыш түзүү гана).
Эми кайталанма сурам түзөлү запастык бөлүктөраны оң баскыч менен чыкылдатып, тандоо Кайталанма өтүнүч (Дубликацияланган суроо), андан кийин алынган көчүрмө сурамынын атын өзгөртүңүз натыйжалары жана биз аны менен иштеше беребиз.
Иш-аракеттердин логикасы төмөнкүдөй:
- Advanced өтмөгүндө Колонка кошуу команда тандоо Ыңгайлаштырылган тилке (Кошуу тилкеси — Ыңгайлаштырылган тилке) жана формуланы киргизиңиз = Бренддер. Баскандан кийин OK биз жаңы тилкеге ээ болобуз, анда ар бир уячада биздин ачкыч сөздөрүбүздүн тизмеси менен уяланган таблица болот - автомобиль өндүрүүчүлөрдүн бренддери:
- Бардык уя салынган таблицаларды кеңейтүү үчүн кошумча тилкенин башындагы кош жебелери бар баскычты колдонуңуз. Ошол эле учурда, запастык бөлүктөрдүн сүрөттөлүшү менен саптар бренддердин санына бир эсе көбөйөт жана биз "запастык тетик брендинин" бардык мүмкүн болгон жуп-комбинацияларын алабыз:
- Advanced өтмөгүндө Колонка кошуу команда тандоо Шарттуу тилке (Шарттуу тилке) жана баштапкы текстте ачкыч сөздүн (бренддин) болушун текшерүү үчүн шарт коюңуз (бөлүктүн сүрөттөлүшү):
- Издөө регистрин сезбестик кылуу үчүн формула тилкесинде үчүнчү аргументти кол менен кошуңуз Compare.OrdinalIgnoreCase окуяны текшерүү функциясына Text.Contains (эгерде формула тилкеси көрүнбөсө, анда аны өтмөктө иштетсе болот кароо):
- Алынган таблицаны чыпкалап, акыркы тилкеге бирөөнү гана калтырабыз, б.а. дал келген тилкелерди жана керексиз тилкени алып салабыз Окуялар.
- Окшош сүрөттөмөлөрдү команда менен топтоо Боюнча топтоо табулатура кайра (Трансформация — Топтоо боюнча). Агрегация операциясы катары тандаңыз Бардык саптар (Бардык саптар). Жыйынтыгында биз ар бир запастык бөлүктүн бардык деталдарын камтыган таблицалары бар тилкени алабыз, анын ичинде бизге керек болгон унаа өндүрүүчүлөрдүн бренддери:
- Ар бир бөлүк үчүн бааларды алуу үчүн өтмөккө дагы бир эсептелген тилкени кошуңуз Мамыча кошуу – Ыңгайлаштырылган тилке (Кошуу тилкеси — Ыңгайлаштырылган тилке) жана таблицадан турган формуланы колдонуңуз (алар биздин тилкеде жайгашкан маалымат) жана алынган тилкенин аталышы:
- Пайда болгон тилкенин баш жагындагы кош жебелери бар баскычты басып, буйрукту тандайбыз Маанилерди чыгарыңыз (Маанилерди чыгаруу)каалаган бөлүүчү белги менен штамптарды чыгаруу үчүн:
- Керексиз тилкени алып салуу маалымат.
- Пайда болгон таблицага андан жоголуп кеткен бөлүктөрдү кошуу үчүн, анда эч кандай бренддер сыпаттамаларда табылган жок, биз суроону бириктирүү процедурасын аткарабыз. жыйынтык баштапкы өтүнүчү менен запастык бөлүктөр баскычы бириктирүү табулатура Home (Башкы бет — Суроолорду бириктирүү). Туташуу түрү - Тышкы кошулуу оң (Оң сырткы кошулуу):
- Болгону кошумча мамычаларды алып салуу жана калгандарынын атын өзгөртүү - жана биздин милдет чечилди:
Метод 2. Формулалар
Эгер сизде Excel 2016 же андан кийинки версиясы бар болсо, анда биздин көйгөй жаңы функцияны колдонуу менен абдан компакттуу жана көрктүү түрдө чечилиши мүмкүн. КОМБИНАТ (TEXTJOIN):
Бул формуланын логикасы жөнөкөй:
- милдети ИЗДӨӨ (ТАП) бөлүктүн учурдагы сүрөттөмөсүндө ар бир бренддин пайда болушун кезеги менен издейт жана бренд табылган символдун сериялык номерин же ката #VALUE! бренд сыпаттамада жок болсо.
- Андан кийин функцияны колдонуу IF (ЭГЕР) и ЕОШИБКА (КАТА) биз каталарды бош текст сабы "" менен алмаштырабыз, ал эми белгилердин иреттик сандарын бренд аталыштары менен алмаштырабыз.
- Натыйжадагы бош уячалардын массивдери жана табылган бренддер функцияны колдонуу менен берилген бөлүүчү символ аркылуу бир сапка чогултулат. КОМБИНАТ (TEXTJOIN).
Ылдамдатуу үчүн аткарууну салыштыруу жана Power Query сурамдарын буферлөө
Иштин натыйжалуулугун текшерүү үчүн, келгиле, баштапкы маалымат катары 100 запастык бөлүктөрдүн сыпаттамасын алалы. Андан биз төмөнкү натыйжаларды алабыз:
- Формулалар боюнча кайра эсептөө убактысы (2-ыкма) – 9 сек. формуланы биринчи жолу бүт мамычага көчүргөндө жана 2 сек. кайталап (буферлөө таасир этет, балким).
- Power Query сурамынын жаңыртуу убактысы (1-ыкма) алда канча начар – 110 секунд.
Албетте, көп нерсе белгилүү бир компьютердин аппараттык жабдыгынан жана Office'тин орнотулган версиясынан жана жаңыртуулардан көз каранды, бирок жалпы сүрөт, менимче, түшүнүктүү.
Power Query суроосун тездетүү үчүн, келгиле, издөө таблицасын буферлейли Маркалар, анткени ал суроону аткаруу процессинде өзгөрбөйт жана аны дайыма кайра эсептеп туруу зарыл эмес (Power Query де-факто сыяктуу). Бул үчүн биз функцияны колдонобуз Таблица.Буфер орнотулган Power Query тилинен М.
Бул үчүн, суроо ачуу натыйжалары жана өтмөктө кароо кнопкасын басып, Өркүндөтүлгөн редактор (Көрүү — Өркүндөтүлгөн редактор). Ачылган терезеде жаңы өзгөрмөлүү сапты кошуңуз Марки 2, бул биздин автоөндүрүүчүнүн каталогубуздун буфердик версиясы болот жана бул жаңы өзгөрмөнү кийинчерээк төмөнкү суроо буйругунда колдонуңуз:
Мындай тактоодон кийин, биздин суроо-талаптын жаңыртуу ылдамдыгы дээрлик 7 эсеге көбөйөт - 15 секундага чейин. Такыр башка нерсе 🙂
- Power Query'де бүдөмүк текст издөө
- Формулалар менен жапырт текст алмаштыруу
- List.Accumate функциясы менен Power Queryде жапырт текст алмаштыруу