tartalom
A kulcsszavak keresése a forrásszövegben az egyik leggyakoribb feladat az adatokkal való munka során. Nézzük meg többféleképpen a megoldását a következő példa segítségével:
Tegyük fel, hogy neked és nekem van egy kulcsszavas listánk – az autómárkák nevei – és egy nagy táblázat mindenféle alkatrészről, ahol a leírások néha egy vagy több ilyen márkát is tartalmazhatnak egyszerre, ha az alkatrész többre is passzol. márkájú autó. Feladatunk az összes észlelt kulcsszó megtalálása és megjelenítése a szomszédos cellákban egy adott elválasztó karakteren (például vesszőn) keresztül.
1. módszer: Power Query
Természetesen először a táblázatainkat dinamikussá („intelligenssé”) alakítjuk egy billentyűparancs segítségével Ctrl+T vagy parancsokat Kezdőlap – Táblázat formázása (Főoldal — Táblázat formázása), adjon nekik nevet (pl Bélyegи Alkatrészek), és egyenként töltse be a Power Query szerkesztőbe a lapon található kiválasztással Adatok – Táblázatból/Tartományból (Adatok – táblázatból/tartományból). Ha az Excel 2010-2013 régebbi verzióival rendelkezik, ahol a Power Query külön bővítményként van telepítve, akkor a kívánt gomb a lapon lesz Teljesítmény lekérdezés. Ha az Excel 365 vadonatúj verziójával rendelkezik, akkor a gombot Táblázatból/Tartományból most odahívott Levélekkel (A lapról).
Az egyes táblák Power Queryben való betöltése után a paranccsal visszatérünk az Excelbe Kezdőlap — Bezárás és betöltés — Bezárás és betöltés... — Csak kapcsolat létrehozása (Kezdőlap – Bezárás és betöltés – Bezárás és betöltés… – Csak kapcsolat létrehozása).
Most hozzunk létre egy ismétlődő kérelmet Alkatrészekjobb gombbal rákattintva és kiválasztva Ismétlődő kérés (Duplikált lekérdezés), majd nevezze át a kapott másolási kérelmet erre: Az eredmények és továbbra is dolgozunk vele.
A műveletek logikája a következő:
- A Speciális lapon Oszlop hozzáadása válassz csapatot Egyéni oszlop (Oszlop hozzáadása – Egyéni oszlop) és írja be a képletet = Márkák. Kattintás után OK kapunk egy új oszlopot, ahol minden cellában lesz egy beágyazott táblázat kulcsszavaink listájával – autógyártó márkák:
- Az összes beágyazott táblázat kibontásához használja a hozzáadott oszlop fejlécében található dupla nyilakkal ellátott gombot. Ugyanakkor a pótalkatrészek leírását tartalmazó sorok megsokszorozódnak a márkák számának többszörösével, és megkapjuk az összes lehetséges „pótalkatrész-márka” pár-kombinációt:
- A Speciális lapon Oszlop hozzáadása válassz csapatot Feltételes oszlop (Feltételes oszlop) és állítson be egy feltételt egy kulcsszó (márka) előfordulásának ellenőrzésére a forrásszövegben (alkatrész leírásban):
- A keresési kis- és nagybetűk érzéketlenné tételéhez manuálisan adja hozzá a harmadik argumentumot a képletsávhoz Compare.OrdinalIgnoreCase az előfordulásellenőrző funkcióhoz Szöveg.Tartalmaz (ha a képletsor nem látható, akkor a lapon engedélyezhető Felülvizsgálat):
- A kapott táblázatot szűrjük, az utolsó oszlopban csak egyet hagyunk, azaz egyezést, és eltávolítjuk a felesleges oszlopot Előfordulások.
- Azonos leírások csoportosítása a paranccsal Csoportosít lap Átalakítás (Átalakítás – Csoportosítás alapja). Összesítési műveletként válassza a Minden sor (Minden sor). A kimeneten egy táblázatos oszlopot kapunk, amely tartalmazza az egyes alkatrészek összes részletét, beleértve a szükséges autógyártók márkáját is:
- Az egyes részek osztályzatainak kinyeréséhez adjon hozzá egy másik számított oszlopot a laphoz Oszlop hozzáadása – Egyéni oszlop (Oszlop hozzáadása – Egyéni oszlop) és használjon egy táblázatból álló képletet (ezek az oszlopunkban találhatók Részletek) és a kivont oszlop neve:
- A kapott oszlop fejlécében kattintunk a dupla nyilakkal ellátott gombra, és kiválasztjuk a parancsot Értékek kibontása (Értékek kibontása)a bélyegek kiadásához tetszőleges határoló karakterrel:
- Felesleges oszlop eltávolítása Részletek.
- Ahhoz, hogy a kapott táblázathoz hozzáadjuk azokat a részeket, amelyek eltűntek belőle, ahol nem található márka a leírásokban, elvégezzük a lekérdezés kombinálásának eljárását Eredmény eredeti kéréssel Alkatrészek gomb Kombájn lap Kezdőlap (Főoldal – Lekérdezések egyesítése). Kapcsolat típus - Külső csatlakozás jobbra (jobb oldali külső csatlakozás):
- Már csak a felesleges oszlopok eltávolítása és a megmaradt oszlopok átnevezése-áthelyezése van hátra – és a feladatunk meg is van oldva:
2. módszer. Képletek
Ha az Excel 2016-os vagy újabb verziója van, akkor a problémánk nagyon kompakt és elegáns módon megoldható az új funkcióval KOMBÁJN (TEXTJOIN):
A képlet mögötti logika egyszerű:
- Funkció KERESÉS (MEGTALÁLJA) sorra megkeresi az egyes márkák előfordulását az alkatrész aktuális leírásában, és vagy annak a szimbólumnak a sorozatszámát adja vissza, amelytől kezdve a márkát megtalálták, vagy az #ÉRTÉK hibát! ha a márka nem szerepel a leírásban.
- Ezután a funkció használatával IF (HA) и EOSHIBKA (HIBA) a hibákat egy üres „”” szöveggel helyettesítjük, a karakterek sorszámait pedig magukkal a márkanevekkel.
- Az eredményül kapott üres cellák és talált márkák tömbje egyetlen karakterláncba áll össze egy adott elválasztó karakteren keresztül a függvény segítségével. KOMBÁJN (TEXTJOIN).
Teljesítmény-összehasonlítás és Power Query Query-pufferelés a gyorsítás érdekében
A teljesítmény teszteléséhez vegyünk kiinduló adatként egy 100 alkatrész leírást tartalmazó táblázatot. Ezen a következő eredményeket kapjuk:
- Újraszámítási idő képletekkel (2. módszer) – 9 mp. amikor először másolja a képletet a teljes oszlopba és 2 mp. ismétlődőnél (valószínűleg pufferhatások).
- A Power Query lekérdezés (1. módszer) frissítési ideje sokkal rosszabb – 110 másodperc.
Természetesen sok múlik az adott PC hardverén és az Office telepített verzióján és a frissítéseken, de az összkép szerintem egyértelmű.
A Power Query lekérdezés felgyorsításához puffereljük a keresési táblát Bélyeg, mert nem változik a lekérdezés végrehajtása során, és nem szükséges folyamatosan újraszámolni (ahogy a Power Query de facto teszi). Ehhez a függvényt használjuk Táblázat.Puffer a beépített M Power Query nyelvből.
Ehhez nyissa meg a lekérdezést Az eredmények és a lapon Felülvizsgálat nyomja meg a gombot Speciális szerkesztő (Nézet – Speciális szerkesztő). A megnyíló ablakban adjon hozzá egy sort egy új változóval Marky 2, amely az autógyártó-könyvtárunk pufferelt változata lesz, és ezt az új változót később a következő lekérdezési parancsban használja:
Egy ilyen finomítás után a kérésünk frissítési sebessége közel 7-szeresére nő – akár 15 másodpercig. Egészen más 🙂
- Fuzzy szöveges keresés a Power Queryben
- Tömeges szövegcsere képletekkel
- Tömeges szövegcsere a Power Queryben a List.Accumulate funkcióval