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