Szöveg keresés lista alapján

Power BI Desktop alapok Hamarosan Megnézem Két Online délelőtt PowerQuery bevezető Hamarosan Megnézem Két online délelőtt Videó Megnézem - Excel makró alapok - Power BI alapok - Excel PIVOT mester Tanfolyamok - PowerQuery alapok

Meglehetősen gyakori adattisztítási vagy adat-kinyerési feladatnak tekinthetjük azt az esetet, mikor egy cellában lévő szöveg egy részét szeretnénk megkapni. A kereséshez több szöveg-kezelő függvény is rendelkezésünkre áll, és ott van még a „Szövegből oszlopok (Text to Columns)” eszköz is.

Például egy viszonylag egyszerű feladatnak nevezhető, ha egy névlistának az első, vagy a második részére van csak szükségünk (vezetéknév, keresztnév), esetleg szét szeretnénk dobni külön-külön cellákba az egyes részeket.

Probléma felvetés

Az egyik céges tanfolyamon (már nem először) találkoztam azzal a kérdéssel, hogy hogy lehetne kézzel rögzített  címeket valahogy oszlopokra bontani. Hát, a kézzel rögzített címek tisztítása, vagy azok oszlopokra bontása igazi rémálom. Ha csak azt nézzük, hogy hányféleképpen írható le az „utca” szó egy címben, …

A közterületeknek állhatnak egy, két, három, vagy akár több szóból is.

Tegyük fel, egy Excel oszlopban címek vannak, és a címekből ki kell valahogy bányászni a közterület típusát. Ha ez megvan, akkor nyilván az előtte lévő rész lesz a neve. Nézzünk egy példát:

Ahogy már megbeszéltük, problémát az okozza, hogy a közterület típusa nem mindig a második szó lesz. Hagyományos szövegfüggvényekkel vagy szövegből oszlopok funkcióval nem oldható meg ez a probléma egyhamar.

Megoldás tömbképlettel

Mi lenne, ha a lehetséges közterület típusokat egy külön tartományban felsorolnánk, és eldöntenénk, hogy ezek közül melyik szerepel a címben? Feltételezzük, hogy mindig csak az egyik szerepel, esetleg egyik sem. Ezt a bizonyos tartományt célszerű táblázattá alakítani, hogy bármikor tetszőlegesen bővíthessük a típusokat, a képlethez ne kelljen nyúlni.ű

Így néz ki  a modell

Az E oszlopban van a táblázat a típusokkal. A tábla neve: kozter

A B2-ben pedig a következő tömbképletet írtam:

=INDEX(kozter[közter fajták];HOL.VAN(1;--(HAHIBA(SZÖVEG.KERES(kozter[közter fajták];A2);0)<>0);0))

vagy ez is helyes, kicsit rövidebb

=INDEX(kozter[közter fajták];HOL.VAN(1;--SZÁM(SZÖVEG.KERES(kozter[közter fajták];A2));0))

Természetesen CSE, azaz Ctrl-Shift-Enter-rel kell lezárni, majd lehúzni az egész oszlopban.

Letölthető munkafüzet

Kozterulet-megallapitas

Magyarázat a videóban