Numbers, Excel, és e bejegyzés határai
Ami megy Numbersben, az természetesen megy Excelben is, a függvény nevek is azonosak - legalábbis ebben a hitben vagyok. A feladat megoldása során messze nem igyekszem kitérni az összes lehetséges, felmerülő hiba kiszűrésére, megoldására, aki az itt leírtak alapján úgy érzi, hogy többet szeretne, annak ajánlom a Numbers képzésünket vagy személyes konzultációnkat. A példát amúgy köszönjük az Akadémia egyik partnerének.
Az adatbázis
Tóth Gabor, 1111 Budapest Valami u. 211. , 06301234567 valami@probamail.hu
Így néz ki egy cella tartalma, képzeljük el, hogy ebből van pár száz, pár ezer - kézzel nagyon nem szeretnénk nekiesni szétszedni vezetéknév, keresztnév, irányítószám... adatokra. Azért sem, mert a számítógép feladata a számítás, adjunk hát munkát neki!
FIND szóköz
Az egyetlen cellába írt szöveget szóközök tagolják. A Numbers FIND függvényével egy cellában lévő szövegben rá tudunk keresni adott betűre, karakterre. A Numbers a FIND kérdésre válaszul megadja, az adott szövegben hanyadik karakter a keresett, a számolást - normálisan - balról kezdve. Ha tudjuk az összes szóközről, hogy hanyadik karakter, akkor már könnyen fel tudjuk darabolni a cella egybeírt szövegét.
Az A2 cellában van a szétdarabolandó szöveg. A B2 cellába beírva a =FIND(" ";$A2) képletet, a cella értéke öt lesz. Azért öt, mert balról az ötödik karakter az első szóköz, a Tóth név után. (A képzésen azt is elmondjuk, mit tehetünk akkor, ha a vicces adatrögzítő például szóközzel kezdte a nevet. Esetleg két szóközzel.)
A függvény szinte fel is olvasható: keress szóközt az A2-ben!
FIND második/sokadik szóköz
A második szóköz megkeresése a fenti paraméterezésű FINDdal nem tehető meg, hiszen újra ötöt kapnánk eredményül, hiszen az az első szóköz helye balról. Mi meg az első szóköz utántól szeretnénk keresni a következő - második - szóközt! Szerencsére a FIND függvénynek azt is megmondhatjuk, hogy hanyadik karaktertől kezdjen keresni. Nem azt fogjuk mondani, hogy az ötödiktől, hanem hivatkozunk az előbb kiszámolt eredményre. Ha nem Tóth van a szétdarabolandó mezőben, hanem mondjuk Kis, vagy a példák klasszikus neve, az Ó Pál, akkor bizony hibára futhatunk.
Másképp mondva, nem tudhatjuk, milyen hosszú a vezetéknév, de ez nem is szükséges, hiszen kiszámoltattuk a Mackel, hogy milyen hosszú - ezt Numbersül a FIND szóközzel tettük meg. Írjuk be a C2 cellába a =FIND(" ";A2;B2+1) képletet. Ha minden igaz - és Tóth Gábor a név, akkor 12-t kapunk eredményül. A FIND segítségével kerestük a szóközt az A2 cellában lévő szövegben, a B2-ben lévő pozíciótól. Illetve annál eggyel nagyobbtól, hiszen B2-be pont az első szóköz pozíciója számolódik ki.
Ugye logikus gondolat, hogy hasonló módon tudnánk kiszámolni a harmadik, negyedik ... sokadik szóköz helyét? Meg is ragadjuk a C2 cella jobb alsó sarkát, és jobbra húzva ki is töltjük a következő tíz cellát. Ám az eredmény kiábrándító, nem szépen növekvő számokat kapunk, hanem hiba jelzéseket. Miért?
Ha megnézzük a D2-ben a képletet, akkor látjuk, hogy ott már a B2 cellában lévő szövegben keressük a szóközt az A2 helyett. Ezért másolás előtt a C2 cellában rögzítjük az A2 cella címében az oszlopot. A képletsorban az A2 melletti kis legördülő nyílra kattintva válasszuk a $A2 kijelzést vagy egyszerűen írjunk dollár jelet az 'A' elé. Ha most másoljuk a képletet az előbbi módon, akkor egy ideig - L oszlop - szépen nőnek a számértékek, de utána hibajelzést kapunk.
ISERROR
A hiba oka az, hogy már az A2 szöveg végére írtunk, nincs további szóköz. Azért húzzuk a képletet mégis tovább, mert nem mindig azonos számú szóköz van a forrás (A2) szövegben, lehet valakinek két keresztneve, állhat az utca neve is két szóból...
Azért, hogy a táblázatunk ne tartalmazzon kezeletlen hibát, piros háromszög, használjuk az ISERROR függvényt. Az ISERROR függvény megvizsgál egy képletet, állítást. Ha az értelmezhető, nem hiba, akkor a képlet eredménye, az állítás lesz a cella értéke, ha hibára fut - is error -, akkor a második paramétert kapja a cella értékül.
Írjuk át az L2 cella tartalmát erre: =IFERROR(FIND(" ";$A2;K2+1);0) a függvényre - elég az iferror-t, a pontosvessző-nullát és a zárójeleket hozzágépelni a meglevöhöz. A cella értéke nulla lesz. Miért?
A belső FIND függvény hibát ad eredményül - láttuk az előbb, ezért küzdünk most. Így az IFERROR függvény hibát talált, ezért a második paramétert, az általunk megadott nullát, írja a cellába.
Húzzuk eggyel jobbra a képletet az M2 cellába. Ötöt kapunk eredményül. Miért? A belső FIND függvény az oka. Keresi az A2-ben a szóközt, de hanyadik helytől is? A nulladiktól, hiszen ez van az előző L2 cellában. Kvázi kezdjük előről az A2 szóköz-pozíció keresését.
Miért görcsöltünk ennyit ezzel az ISERRORral? Az email cím kinyerésénél elmondjuk.
Vezetéknév, keresztnév... LEFT, MID
Ennyi előkészítés után az O2 cellában állítsuk elő a vezeték-, a P2-ben pedig a keresztnevet!
Az O2 cellába írjuk be a =LEFT(A2;B2-1) képletet. A LEFT függvény a megadott szöveg - A2 cella tartalma - bal oldalán lévő B2-1 karaktert adja eredményül. B2-ben van az első szóköz helye, tehát a vezetéknév eggyel kisebb pozíción végződik, ezért a -1.
A P2 cellába írjuk be a =MID(A2;B2+1;C2-B2-2) képletet! A MID lesz a nagy barátunk a szövegdarabolásnál. Ez képes egy szövegből - A2 - adott pozíciótól - B2+1 - adott számú betűt - C2-B2-2 - kivágni. A keresztnév első betűje az első szóköz után áll, ezért a B2+1. A keresztnév hossza, figyelem hossza(!), a második szóköz és az első szóköz közti betűk száma, azaz C2-B2. Mivel a keresztnév végén vessző van, ezért a hosszból(!) ezt is levonjuk: -1. A másik -1 azért kell, mert B2+1 a kezdés.
Emailcím - RIGHT, LEN
Nem véletlenül ISERROR-oztunk, ahogy már említettük, nem mindig azonos számú szóközt tartalmaz a forrás, jelen esetben az A2. Az email cím az utolsó szóköz utáni rész (ha csak a huncut adatrögzítő nem írt szóközt, szóközöket a mail cím után is).
A legnagyobb, az utolsó, szóköz helyét a számoljuk ki az N2 cellában. Ez olyan egyszerű, hogy leírni is csak a rend kedvéért teszem: =MAX(B2:M2)
Az email-cím kinyerését a RIGHT függvénnyel végezzük. Megadjuk, hogy melyik cellában tárolt szövegből kell az utolsó szó, jelen esetben email cím, ez az A2. És meg kell adni, hogy hátulról hány karakterre van szükségünk, magyarul: milyen hosszú az email-cím. Ismerjük az email-cím előtti szóköz pozícióját - N2 cella adata -, így az email-cím hossza a teljes szöveg hosszából levont N2 adat. A képlet:
=RIGHT(A2;LEN(A2)-N2)
Egy cellában tárolt szöveg hosszát a LEN függvény adja meg.
Időt, energiát takarít meg az, aki időt és energiát takarít meg
Aki idáig elért, annak gratulálok! Megfelelő rutinnal körülbelül e bejegyzés elolvasási ideje alatt lehet egy összevont cellaadathalmazt képletekkel felhasználhatóvá darabolni. Elsőre persze, hogy küzdelmes lesz, sőt, másodjára is. A szétdaraboláshoz alig tíz függvény ismerete szükséges - jó részüket meg is említettem. Mi úgy gondoljuk, hogy inkább a gép dolgozzon, mint mi, ezért használunk függvényeket (is).
Ezért oktatjuk.
Nincsenek megjegyzések:
Megjegyzés küldése