Përmbajtje
Kërkimi i fjalëve kyçe në tekstin burimor është një nga detyrat më të zakonshme kur punoni me të dhëna. Le ta shohim zgjidhjen e tij në disa mënyra duke përdorur shembullin e mëposhtëm:
Le të supozojmë se ju dhe unë kemi një listë me fjalë kyçe - emrat e markave të makinave - dhe një tabelë të madhe me të gjitha llojet e pjesëve rezervë, ku përshkrimet ndonjëherë mund të përmbajnë një ose disa marka të tilla në të njëjtën kohë, nëse pjesa rezervë përshtatet më shumë se një. markë e makinës. Detyra jonë është të gjejmë dhe shfaqim të gjitha fjalët kyçe të zbuluara në qelizat fqinje përmes një karakteri ndarës të caktuar (për shembull, një presje).
Metoda 1. Query Power
Sigurisht, së pari ne i kthejmë tabelat tona në dinamike ("të zgjuara") duke përdorur një shkurtore tastiere Ctrl+T ose komandat Faqja kryesore – Formatoni si tabelë (Shtëpia - Formatoni si tabelë), jepini emra (për shembull Pullaи Pjesë ndërrimi) dhe ngarkoni një nga një në redaktorin e Power Query duke zgjedhur në skedën Të dhënat – Nga tabela/vargu (Të dhënat - nga tabela/vargu). Nëse keni versione më të vjetra të Excel 2010-2013, ku Power Query është instaluar si një shtesë e veçantë, atëherë butoni i dëshiruar do të jetë në skedën Kërkesa e energjisë. Nëse keni një version krejt të ri të Excel 365, atëherë butoni Nga tabela/gama thirri atje tani Me gjethe (Nga Fleta).
Pas ngarkimit të secilës tabelë në Power Query, kthehemi në Excel me komandën Faqja kryesore — Mbyll dhe ngarko — Mbylle dhe ngarko te… — Krijo vetëm lidhje (Faqja kryesore - Mbyll & Ngarko - Mbylle & Ngarko te... - Krijo vetëm lidhje).
Tani le të krijojmë një kërkesë të kopjuar Pjesë ndërrimiduke klikuar me të djathtën mbi të dhe duke zgjedhur Kërkesë dublikatë (Pyetje dublikatë), më pas riemëroni kërkesën e kopjimit që rezulton në Rezultatet dhe ne do të vazhdojmë të punojmë me të.
Logjika e veprimeve është si më poshtë:
- Në skedën Avancuar Shtimi i një kolone zgjidhni një ekip Kolona e personalizuar (Shto kolonë - kolonë e personalizuar) dhe shkruani formulën = Markat. Pasi të klikoni mbi OK do të marrim një kolonë të re, ku në secilën qelizë do të ketë një tabelë të mbivendosur me një listë të fjalëve tona kyçe - markat e prodhuesve të automjeteve:
- Përdorni butonin me shigjeta të dyfishta në kokën e kolonës së shtuar për të zgjeruar të gjitha tabelat e mbivendosura. Në të njëjtën kohë, linjat me përshkrimet e pjesëve rezervë do të shumëzohen me një shumëfish të numrit të markave, dhe do të marrim të gjitha çiftet e mundshme të kombinimeve të "markës së pjesëve rezervë":
- Në skedën Avancuar Shtimi i një kolone zgjidhni një ekip Kolona e kushtëzuar (kolona e kushtëzuar) dhe vendosni një kusht për të kontrolluar shfaqjen e një fjale kyçe (markë) në tekstin burimor (përshkrimi i pjesës):
- Për ta bërë rastin e kërkimit të pandjeshëm, shtoni manualisht argumentin e tretë në shiritin e formulave Krahaso.OrdinalIgnoreCase te funksioni i kontrollit të ndodhisë Teksti.Përmban (nëse shiriti i formulës nuk është i dukshëm, atëherë mund të aktivizohet në skedë Rishikim):
- Ne filtrojmë tabelën që rezulton, duke lënë vetëm ato në kolonën e fundit, pra ndeshjet dhe heqim kolonën e panevojshme. Ngjarjet.
- Grupimi i përshkrimeve identike me komandën Grupo nga tab Transformim (Transformimi - Grupi sipas). Si një operacion grumbullimi, zgjidhni Të gjitha linjat (Të gjitha rreshtat). Në dalje, marrim një kolonë me tabela, e cila përmban të gjitha detajet për secilën pjesë rezervë, duke përfshirë markat e prodhuesve të automjeteve që na duhen:
- Për të nxjerrë notat për secilën pjesë, shtoni një kolonë tjetër të llogaritur në skedë Shtimi i një kolone - Kolona e personalizuar (Shto kolonë - kolonë e personalizuar) dhe përdorni një formulë të përbërë nga një tabelë (ato janë të vendosura në kolonën tonë Detaje) dhe emrin e kolonës së nxjerrë:
- Ne klikojmë në butonin me shigjeta të dyfishta në kokën e kolonës që rezulton dhe zgjedhim komandën Ekstraktoni vlerat (Ekstraktoni vlerat)për të nxjerrë pulla me çdo karakter ndarës që dëshironi:
- Heqja e një kolone të panevojshme Detaje.
- Për të shtuar në tabelën që rezulton pjesët që u zhdukën prej saj, ku nuk u gjet asnjë markë në përshkrime, ne kryejmë procedurën e kombinimit të pyetjes Rezultat me kërkesë origjinale Pjesë ndërrimi buton Kombinoj tab Fillimi (Faqja kryesore - Bashkoni pyetjet). Lloji i lidhjes - Bashkohu i jashtëm djathtas (Lidhja e jashtme e djathtë):
- Gjithçka që mbetet është të hiqni kolonat shtesë dhe të riemërtoni- zhvendosni ato të mbetura - dhe detyra jonë është zgjidhur:
Metoda 2. Formulat
Nëse keni një version të Excel 2016 ose më vonë, atëherë problemi ynë mund të zgjidhet në një mënyrë shumë kompakte dhe elegante duke përdorur funksionin e ri COMBINE (TEKSTI BON):
Logjika pas kësaj formule është e thjeshtë:
- funksion KERKO (GJEJ) kërkon ndodhjen e çdo marke me radhë në përshkrimin aktual të pjesës dhe kthen ose numrin serial të simbolit, nga i cili u gjet marka, ose gabimin #VALUE! nëse marka nuk është në përshkrim.
- Më pas duke përdorur funksionin IF (NESE) и EOSHIBKA (ISERROR) ne i zëvendësojmë gabimet me një varg teksti bosh "", dhe numrat rendorë të karaktereve me vetë emrat e markave.
- Grupi rezultues i qelizave boshe dhe markave të gjetura mblidhet në një varg të vetëm përmes një karakteri ndarës të caktuar duke përdorur funksionin COMBINE (TEKSTI BON).
Krahasimi i performancës dhe Buffering Query Query për Speedup
Për testimin e performancës, le të marrim një tabelë me 100 përshkrime të pjesëve të këmbimit si të dhëna fillestare. Mbi të marrim rezultatet e mëposhtme:
- Koha e rillogaritjes sipas formulave (Metoda 2) – 9 sek. kur kopjoni për herë të parë formulën në të gjithë kolonën dhe 2 sek. në të përsëritura (ndikon buffering, ndoshta).
- Koha e përditësimit të pyetjes së Power Query (Metoda 1) është shumë më e keqe - 110 sekonda.
Sigurisht, shumë varet nga pajisja e një kompjuteri të veçantë dhe versioni i instaluar i Office dhe përditësimet, por pamja e përgjithshme, mendoj, është e qartë.
Për të përshpejtuar një pyetje të Power Query, le të fshijmë tabelën e kërkimit Pulla, sepse nuk ndryshon në procesin e ekzekutimit të pyetjes dhe nuk është e nevojshme të rillogaritet vazhdimisht (siç bën Power Query de facto). Për këtë përdorim funksionin Tabela.Bufer nga gjuha e integruar e Power Query M.
Për ta bërë këtë, hapni një pyetje Rezultatet dhe në skedën Rishikim Shtyp butonin Redaktues i avancuar (Shiko - Redaktues i avancuar). Në dritaren që hapet, shtoni një rresht me një ndryshore të re Marky 2, i cili do të jetë një version i buferuar i drejtorisë sonë të prodhuesit të automjeteve dhe përdorni këtë ndryshore të re më vonë në komandën e mëposhtme të pyetjes:
Pas një përsosjeje të tillë, shpejtësia e përditësimit të kërkesës tonë rritet me pothuajse 7 herë - deri në 15 sekonda. Krejt ndryshe 🙂
- Kërkimi i tekstit fuzzy në Power Query
- Zëvendësimi i tekstit në masë me formula
- Zëvendësimi i tekstit në masë në Power Query me funksionin List.Acumulate