Telefonszámunk: 1-472-0679

Szöveg keresés lista alapján

2017-07-19 - horvimi - Megtekintések száma: 450 - Kategória: Függvények, Tömbképletek
Hivatkozott Excel verzio: Excel 2007 Excel 2010 Excel 2013 Excel 2016 Office365

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.

Tagolt-szoveg-szetszedese

Ha a nevek némelyike 3 részből állna, máris újabb kérdés merülne fel, hogy akkor mi legyen?

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:

kozterulet-nev-lista

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

kozterulet-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

Vélemény, hozzászólás?