jan 14

Csoportba foglalás (Group) alatta, fölötte, jobbra, balra

Biztosan sokan használjátok a Csoportosítás (angolban: Group) funkciót – ezzel lehet kis kibontó-összecsukó gombokat tenni sorokhoz, oszlopokhoz, ami megkönnyíti a részletező adatok elrejtését vagy mutatását.

(Aki nem ismeri: legegyszerűbb, ha kijelölöd a sorokat (vagy oszlopokat), amiket egy csoportba szeretnél tenni, majd az Adatok (Data) menü alatt megnyomod a Csoportosítás (Group) gombot.)

Group_1

Csak az a probléma, hogy alapbeállításként ezek mindig a csoport alá kerülnek, így a fenti képen látható struktúrában rossz helyen lesznek a nyitó/csukó gombok… (Hányszor kaptunk már a központból olyan fájlokat, amiben az országcsoportok adatait mutatják… csak épp a mi országainkat nem a „Central Europe” hanem a „Deutch Austria…” melletti gombbal lehet kinyitni…)

A gyakorlatban sokszor előfordul, hogy az összesítő sor fent van, és alatta a részletek. Ahhoz, hogy a csoportosítást is ennek megfelelően tudjuk felrakni, át kell állítani az összesítő gomb helyzetét. Nagyon egyszerű, csak kicsit el van rejtve a beállítási lehetőség:

Group_2

Tehát a Csoportosító gombok menücsoportja (ezt hívják Tagolásnak, angolban Outline) jobb alsó sarkában a kis (eh, mini) gombocskára kell kattintani, hogy megnyíljon a beállító ablak. Itt kell kipöckölni az Összesítő sorok a részletsorok alatt beállítást, hogy felülre kerüljenek… Oszlopok csoportosítása esetén értelemszerűen a jobbra (pöckölve) balra (nincs pöcök) érvényes. (Mindez a már felrakott gombok helyzetét ez nem változtatja meg, csak azokra érvényes, amit ezután teszel fel!)

Nagyon vicces ez a beállítási lehetőség, ha be van pöckölve, akkor kerül az összesítés a sorok alá, ha nincs bepöckölve, akkor fölé. Kicsit olyan, mintha a népszámlálási kérdőíven csak egy „Férfi” opció lenne, és ha nem ikszeled be, akkor nő vagy. 🙂

No, tehát a beállítás után már helyesen lehet a csoportosítást megcsinálni:

Group_3

Én pedig ezennel megfogadom, hogy ha még egyszer olyan fájlt kapok a központból, amiben rossz helyen vannak a gombok, elküldöm nekik ezt a bejegyzést. Nem kell, hogy a szöveget értsék, a kép is jól mutatja, hogy mit kell csinálni, ugye?

 

jan 12

Az előző parancs megismétlése – billentyűkombináció

Az Excel felhasználóknak két fő csoportja van: az egyik, akik főképp egérrel kattintgatnak, a másik, akik szinte mindent megoldanak billentyűkombinációkkal. Én az elsőbe tartozom, több ok miatt is: először is, nem tudok emlékezni a sok kombinációra, hogy melyik mit csinál, másodszor, angol és magyar Excelt is használok, s a billentyűkombinációk egy része nyelvfüggő. Pedig ezek nagyon meg tudják gyorsítani a munkát – ezen a téren talán lenne hova fejlődnöm, bár azért jobbkezesként a balkezes egérrel én se vagyok kimondottan lassú. 🙂

Van azonban egy kedvenc – és talán kevésbé ismert – billentyűkombináció, amit sokszor használok, szerencsére ez a magyar és angol Excelben azonos:

Az „univerzális” Ctrl+Y vagy F4 megismétli az előző parancsot.

Például: ha beszíneztél egy cellát sárgára, és egy másik cellán megnyomod a Ctrl+Y-t, az is sárga lesz.

De mondok jobbat: egy egész formázási „csomagot” meg lehet ismételni (hasonlóan, csak sokkal gyorsabban, mint a formátum másolás). Ehhez csak az kell, hogy ne egyesével állítsd be a különböző formázásokat (legyen sárga a háttér, középre írt a szöveg, keret itt-ott, stb) hanem egyszerre, a párbeszédablakban. Ha itt az OK-t megnyomod, utána a Ctrl+Y az összes beállított formázást fogja megismételni azokon a cellákon, amiket kijelölsz, és annyiszor, ahányszor akarod.

Ctrl_y_1

És még egy nagyon hasznos tipp, amit ezekben a napokban sokszor kellett használnom. Emlékeztek az előző bejegyzésre, amiben arról volt szó, hogyan fordítsuk meg egyszerűen a beírt számok előjelét? Nos, én nem -1-gyel, hanem az idén érvényes új árfolyammal szoroztam át sok-sok adatot, s ehhez a parancs-megismétlő Ctrl+Y-t használtam. [Gábor megjegyzése: Figyelem! Ilyesmit csak végszükség esetén szabad használni, normális esetben szépen oda kell képletezni az árfolyamot. Nagyon ügyeljünk arra, nehogy véletlenül kétszer nyomjuk meg a Ctrl+Y-t, mert akkor kétszer szorzódik fel, és ha nem vesszük észre, leharapja a fejünket a főnök.]

Próbáljátok ki, [körültekintően használva] rengeteg időt megtakarít, biztos, hogy ez lesz a kedvence annak is, aki máskülönben nem használ billentyűkombinációkat.

 

jan 03

Beírt számok előjelének megfordítása

A gyakorlatban (különösen könyvelés, kontrolling, ellenőrzés területen 🙂 ) sokszor előfordul, hogy egy tartomány számainak előjelét kell megfordítani, vagyis -1-egyel beszorozni. Erre mutatok most egy nagyon-nagyon egyszerű apróságot: az Excel Irányított beillesztés műveletét fogjuk használni.

Lássuk lépésről lépésre egy pici példa segítségével: sárgával jelöltem a B2:D4 tartományt, ezeket a számokat kellene -1-egyel szorozni.

  1. Írj be -1-gyet egy üres cellába (lehet bárhol a munkalapon, később kitörölheted). Én itt az A1-be írtam:
    Iranyitott_1
  2. Másold ezt a számot a vágólapra… vagyis… nyomd meg a Ctrl+C-t.
  3. Jelöld ki a számokat, amiknek az előjelét meg akarod fordítani:
    Iranyitott_2
  4. A menüben válaszd a Beillesztés / Irányított beillesztést:
    Iranyitott_3
  5. Megjelenik egy ablak, itt válaszd a Művelet alatt a Szorzást:
    (Ezzel azt választod ki, hogy a kimásolt -1-gyet nem „csak úgy” beillesztjük, hanem megszorozzuk vele a kijelölt számokat.)
    Iranyitott_4
  6. OK – és már kész is vagy, íme a megfordított előjelű számok:
    Iranyitott_5

 

S ha már egy pillantást vetettünk az Irányított beillesztés ablakra, akkor nézzük meg kicsit alaposabban!

  • A Művelet alatt nem csak szorzást, de összeadást, kivonást, osztást is választhatsz, és nyilván -1 helyett bármilyen más számmal is végezheted a műveletet.
  • Ha formázott adatokon végeznéd a műveletet, a Beillesztés alatt válaszd az Értéket.
  • Haladóbbak akár képlettel is végezhetnek műveletet. Ha a kijelölt tartományod képleteket tartalmaz, és a Beillesztés alatt a Képletet választod, a művelet a meglevő képlethez fog kapcsolódni. Például: =a2+B2 -ből lesz: =(a2+b2)*-1

Remélem, hasznosnak találjátok ezt az apróságot. Próbáljátok ki, így könnyebben eszetekbe jut majd, amikor szükség lesz rá!

 

dec 17

Szűrő (Filter) alkalmazása kimutatás (Pivot tábla) érték mezőkön

Ez az apró tipp kimutatás (angolul Pivot table) használóknak lesz hasznos. Alapból a kimutatásba felvett sor, oszlop és lapozó mezők mindegyike tartalmaz szűrőt, de időnként szükség van rá, hogy a táblában látható értékeket szűrjük.

Például, ahogy az alábbi képen látod, az érték mezők: érték és mennyiség. Szeretnék ide egy szűrőt feltenni, és az 1000-nél nagyobb mennyiségeket szűrni. Sajnos kimutatás esetében azonban a szűrő nem aktív… 🙁

Pivot-filter_1

A megoldás egyszerű, csak egy picikét túl kell járni az Excel eszén. Az Excel ugyanis nem azt érzékeli ilyenkor, hogy egy kimutatás fejlécét jelölted ki, hanem azt, hogy honnan KEZDTED a kijelölést! Tehát: indulj a fejléc melletti cellából, így:

Pivot-filter_2

És már fent is van a szűrő, lehet használni ugyanúgy, mint bárhol máshol! Ugye, milyen egyszerű?

A gyakorlatban én sokszor használom ezt a megoldást, amikor egy nagy adatlistát kell összesítenem, megfelelő formátumba rendeznem, de a végén csak azokra a sorokra van szükségem, amiknek nem 0 az értéke. Miután összeraktam a kimutatást, egy gyors filterezéssel kiszűröm az adatokat, amik nem kellenek a további feldolgozás során.

Update: a fenti ad-hoc megoldást kiválthatod a kimutatás saját szűrőjével. Praktikus, ha nem csak egyszeri szűrésről van szó, hanem frissítened is kell majd a kimutatást.

dec 12

Függvények egymásba ágyazása a függvény szerkesztőben

Ebben a bejegyzésben azt szeretném megmutatni, hogyan tudtok nagyon egyszerűen egymásba ágyazott függvényeket készíteni a függvény szerkesztő segítségével.

Formula_editor1

A gyakorlatban egymásba ágyazott függvények gyakran a HA (IF) függvény esetében fordulnak elő, például amikor szöveget akarunk hozzárendelni érték tartományokhoz:

Ha A1 >= 10, akkor „magas”
Ha A1 >=5, akkor „közepes”,
egyébként „alacsony”.

Egymásba ágyazott függvénnyel így írható fel:

=HA(A1>=10;”magas”;HA(A1>=5;”közepes”;”alacsony”))

=IF(A1>=10,”magas”,IF(A1>=5,”közepes”,”alacsony”))

Ezt a függvényt nagyon egyszerűen összerakhatod a függvény szerkesztővel. Kattints az ikonra, válaszd ki a HA függvényt és kezdd el kitölteni a paramétereket. A Hamis (False) ágra kell beágyaznod a második HA függvényt – s most jön a trükk! A beágyazáshoz kattints a név mezőben megjelenő HA függvényre:

Formula_editor2-1

Újra megnyílik a függvény szerkesztő ablak, amiben már a beágyazott függvényt paraméterezheted.

 

Ha másik függvényt szeretnél beágyazni, akkor a név mező legördülő nyilacskájára kattintva választhatsz a legutóbb használt, vagy az összes függvény közül:

Formula_editor3

Találtam erről a trükkről egy kis angol nyelvű youtube videót is, amiben szépen el van magyarázva a beágyazott HA függvény, bár sajnos az a fontos elem, hogy más függvényt is beágyazhatsz, hiányzik belőle.

Remélem, hasznosnak találjátok ezt az apró kis trükköt!

 

Kérdezz tőlünk Excel segítő csoportunkban vagy kövesd az Adatkertészetet a Facebookon!

dec 04

Név mező – második rész: cellák és tartományok elnevezése

MadeiraiKeresztelöKápolna200

Keresztelő kápolna Madeirán (Gábor fotója)

Egy kis szünet után – ami leginkább munkával, és nem pihenéssel telt – folytassuk a név mezővel való ismerkedést.
(Ebben a cikkben az alapoktól indulunk, és első lépésként csak a név mező segítségével definiált nevekkel foglalkozunk. Későbbi cikkekben térünk majd ki a nevek fantasztikus birodalmának további sajátosságaira.)

Az előző bejegyzés végén (aki nem olvasta, most tegye meg – szex is van benne! ;-)) ígértem, hogy egy keresztelő kápolnába látogatunk (eh, a szex után…). Ez a kápolna maga a név mező: cellákat vagy cella tartományokat nevezhetünk itt el. Ezek a nevek olyan azonosítók lesznek, amiket a cella hivatkozások helyett használhatunk.

Nézzünk egy egyszerű példát. Feladat: át kell számolni forintot euróra a C oszlopban. Az árfolyam a C1 cellában van:

NameBox1

Mielőtt beírnánk a képletet, nevezzük el a C1 cellát arfolyam-nak: Először állj a C1 cellára, kattints a név mezőbe és gépeld be: arfolyam, majd nyomj Entert:

NameBox2

Innentől kezdve a C1 cellát már új nevén használhatjuk: hivatkozhatunk rá úgy, hogy arfolyam. Próbáljuk meg most felírni az euró számító képletet. A C4 cellába állok, és = után kattintok a B4-en, majd osztás jel, és kattintok a C1-en – s már látható is, az Excel nem C1-et, hanem a cella új nevét, az arfolyam-ot használja:

NameBox3

Ha ezt a képletet lefelé másolod, mindenhol az arfolyam név marad a hivatkozásban – az így definiált nevek ugyanis mindig ugyanarra a cellára mutatnak, tehát pontosan úgy működnek, mint az abszolút hivatkozások.

Nem csak egy cellát, hanem egy tartományt is elnevezhetsz ugyanezzel a módszerrel:

NameBox4

Innentől kezdve $B$3:$C$5 helyett azt írhatod: nevlista – például egy FKERES (VLOOKUP) formulában.

Elnevezési szabályok:

  • egyértelmű, hogy nem használhatsz olyan nevet, ami egy másik cella hivatkozása (tehát: SEX69 már van az Excelben, ezt sajnos nem tudod névként használni)
  • szóközt nem használhatsz, helyette az aláhúzást javaslom (a pont is megengedett)
  • az ékezetes betűket jobb elkerülni
  • kis és nagybetűkkel javíthatod az olvashatóságot, bár az Excel nem tesz különbséget (Arfolyam és arfolyam ugyanaz, nem adható kétszer – de ez nem is baj, mert mi is összekevernénk :-))
  • képzeld el, hogyan jelenik majd meg a név a képletben: mindig törekedj arra, hogy értelme, jelentése legyen az adott névnek – ezzel nagyon megkönnyíted a későbbi munkádat!

Mi az előnye a nevek használatának? Három fontos dolgot emelnék most ki:

  • olvasható a képlet: a fenti képen látod, hogy a B4 tényleg az árfolyammal van osztva, nem kell gondolkodni, hogy vajon milyen adat van a C1 cellában.
  • nem kell $ jelekkel foglalkozni, a név abszolút hivatkozás
  • a név mező segítségével definiált nevek hatóköre (angolul scope-ja) a munkafüzet, vagyis ezek munkafüzet szinten egyedi nevek. Ebből az következik, hogy másik munkalapon is ugyanígy lehet őket használni, nem kell elé kitenni a munkalap hivatkozást – tehát megszabadulhatunk a Munka1!$C$1 tipusú „rémségektől”.

Ahogy az előző cikkben olvashattátok, a név mező segítségével lehet egy cellára „ugrani” (SEX69, ugye mindenki kipróbálta?) Ugyanezt a nevekkel is meg lehet tenni: ha a név mezőbe begépeled például a fent definiált arfolyam vagy nevlista nevet, az Excel a megfelelő cellára (C1) vagy tartományra (B3:C5) ugrik. Sőt, begépelés helyett választhatod a név mező sarkában levő kis legördülő nyilat is, így a munkafüzetben definiált cella vagy tartomány nevek listájából választhatsz.

NameBox5

És még egy fontos dolog, ami nélkül nem lenne kerek ez a kis téma:

Meglevő nevek átnevezése, hivatkozásának megváltoztatása

Mint fentebb írtam, a név mező keresztelő kápolna, átkeresztelkedni itt sajnos nem lehet, ehhez kérvényt kell benyújtani, mégpedig a Névkezelőben (Name Manager). No, nem kell megijedni, annyira nem bürokratikus a dolog, szerencsére egyszerűen elérhető a Névkezelő a Képletek (Formulas) menü közepe táján található, a Definiált nevek (Defined Names) csoportban. A Ctrl+F3 beillentyű kombinációval nyithatjuk meg még gyorsabban.

A Névkezelő ablakban a Szerkesztés… (Edit…) megnyomása után változtathatod meg magát a nevet (átkeresztelés). Alul, a Hivatkozás: (Refers to:) alatt pedig módosíthatod a tartományt, ahova a meglevő név mutat. (És egy apró trükk haladóbbaknak: a Hivatkozás mezőben kurzor mozgatáskor az Excel cella hivatkozásokat próbál beszúrni. Az F2 gomb megnyomása után tudjuk a kurzort rendesen mozgatni.)

NameBox6

 

Kérdezz tőlünk Excel segítő csoportunkban vagy kövesd az Adatkertészetet a Facebookon!

nov 24

Név mező – első rész: szex és kijelölés

Ma félre kell dobnunk a prüdériát: keresünk egy kis erotikus tartalmat az Excelben.

Először is, ismerkedjünk meg a Név mezővel:

Name_box_1

Az ábrán lilával keretezett kis „dobozka” nagy segítség a munkalapon való navigációban. Most épp a B2 cellán áll a kurzorom, s a név mezőben is ez a cella cím, vagy más szóval név olvasható. Ha egérrel kattintasz ezen a mezőn, és beleírsz egy másik cella címet, akkor az automatikusan oda ugrik a kurzorod.

No, és most jön az erotika! Kattints a név mezőre, és írd bele: SEX69 – és nyomj Enter-t. Hoppá! Van ilyen cella a munkalapon, és már oda is ugrott a kurzorod. Az Excelben a 2007-es verziótól felfelé már 3 betűs oszlop nevek is szerepelnek, egészen XFD oszlopig.

S most tisztázzuk a fogalmakat. A munkalapon minden cellának van neve, ami jelen esetben ugyanaz, mint a cella címe – vagyis az oszlop betűje és a sor száma: például A1, B2 vagy SEX69. A Név mezőt használhatjuk az így megnevezett cellákra való „ugrásra”. Lehet próbálkozni mindenféle 3 karakter és sor szám kombinációkkal! 🙂

És most jöjjön egy gyors módszer tartomány kijelölésére. (Különösen akkor lesz ez praktikus, amikor nem összefüggő adat táblát kell kijelölnöd, hanem olyat, amiben sok üres cella van, tehát nem praktikus a Shift+Ctrl+nyíl vagy Shift+End és nyíl billentyű kombinációt használni.)

Tegyük fel, hogy a táblád a B2 cellától az AX628-ig tart.

1) állj a B2 cellára
2) kattints egérrel a név mezőbe
3) írd a név mezőbe: AX628 DE még ne nyomd meg az entert!
4) nyomj Shift+Enter-t!

És máris ki van jelölve a tartomány B2-től AX628-ig!

(Mi történt itt? A Shift lenyomása mindig kijelölést jelent. A Név mezőbe írt cella, majd Enter a cellára való ugrást jelenti, de mivel „ugrás közben” nyomva volt a Shift, valójában kijelölés történt.)

A következő részben sokkal konzervatívabb területtel fogunk megismerkedni: a mai erotikus nap után ellátogatunk egy „keresztelő kápolnába”. 🙂

 

Kérdezz tőlünk Excel segítő csoportunkban vagy kövesd az Adatkertészetet a Facebookon!

nov 19

Több nyelvű Excel formula fordító

Ebben a bejegyzésben szeretnék megosztani veletek egy hasznos eszközt, ami Excel formulákat fordít és alakít át. Különösen jól jön, ha az interneten (Google a barátunk) találtok megoldást valamilyen kérdésre, és más nyelven elnevezett függvényekkel, eltérő elválasztó karakterekkel van megadva a formula, amit használni szeretnétek. Természetesen, leggyakrabban angol nyelvű Excel-ből származó példákat lehet találni, de hiába érti meg esetleg valaki az angol leírást, ha fogalma sincs, hogy azokat a függvényeket hogy hívják a magyar Excel-ben…

Két fontos nyelvi eltérés fordul elő:

  • Az első nyilvánvaló: a magyar nyelvű Excelben a formulák is le vannak fordítva magyarra.
  • A második a géped (Windows) beállítása: ez határozza meg többek között azt, hogy a formulákban ; (pontosvessző) vagy , (vessző) az elválasztó karakter.

A formula fordító szerencsére nem csak fordít, hanem az elválasztó karaktereket is kicseréli: csak be kell állítanod a kívánt cserét Az argumentumok elválasztó karakterével… mezőnél.

No, és akkor a link:

http://hu.excel-translator.de/

Az eszközt Mourad Louha, egy német Excel fejlesztő készítette, de egy nagyon pici közöm nekem is van hozzá: én fordítottam le a magyar felhasználói felületet.

Translator

Remélem, hasznosnak találjátok majd!

 

nov 18

Ronda Grafikon 2: Százalékos megoszlás korcsoportonként

Megint találtam valamit, és megint nem tudtam megállni, hogy 3 perc alatt csináljak egy kicsit jobban kinéző grafikont.

Ilyen volt:

ilyen_volt_2

Mi a baj ezzel?

Először is, a grafikonon a korcsoportokon belül két %-os adat van feltüntetve, ami nem teszi ki a 100%-ot, így a halmozott oszlop megjelenítés zavaró. Hiányzik a bizonytalanok, nem válaszolók aránya – bár ez az adat az oszlop feletti üres részből leolvasható lenne… Furcsa koncepció, ezért én feltüntetem ezt a csoportot is.

Másodszor: a színek. Ezek a színek az Excel 2003(!) alap beállításai, szemrontó sötétbordó alapon fekete kombinációt felvillantva. Kezdő Excel felhasználótól még csak elnézhető, hogy az adatokból egy gombnyomásra előálló grafikont úgy hagyja, ahogy van, de egy efféle illusztrációban ez nagyon slampos. A minimum elvárás legalább az lenne, hogy ha feltesszük az adat feliratokat, akkor ügyeljünk az olvashatóságra – legalább ennek az egy oszlopnak adjunk valami világosabb színt.

 

Ilyen lett az én verzióm:

ilyen_lett_2

Ugye, hogy szebb és áttekinthetőbb? És tényleg csak  3 percet szántam rá!

 

A többi ronda grafikon

 

nov 14

Duplikációk eltüntetése

Az előző bejegyzésben leírtam, hogyan lehet formulák segítségével megjelölni, majd kiszűrni a többször előforduló adatot. Most megmutatom az Excel beépített megoldását ugyanerre a problémára. (Az Excel 2007-ben jött ez be.)

A módszer nagyon egyszerűen használható, az Excel menüjéből elérhető: Adatok / Adateszközök csoportban: Ismétlődések eltávolítása (Data / Data Tools group: Remove duplicates). Azonban mielőtt elkezded használni, egy dologra figyelj: mint a neve is mutatja, ismétlődések eltávolításáról van szó, tehát ki fog törölni adatokat! Ha szeretnéd az eredeti listát megtartani, és külön „legyártani” az egyedi adatokat, akkor először másold le az adataidat valahova, ahol végrehajtod majd a törlést.

Jelöld ki az adatokat, nyomd meg a menüpont gombját, majd ellenőrizd le, hogy van-e fejléce a kijelölésednek (jobb felső sarokban). OK – és kész.

De az igazi előny még csak most jön: Ezt a funkciót használhatod több oszlop esetében is, így az oszlopokban levő adatok kombinációinak ismétlődését vizsgálja az Excel:

duplication2

Tehát megmarad az alma – pálinka és az alma – bor (még szerencse! :-)) és csak egy duplikáció lesz eltávolítva: a körte – pálinka.

Egy másik megoldási lehetőség egy gyors kimutatás, vagy angolul pivot tábla készítése. Ehhez kell, hogy legyen fejléce az oszlopnak. Csak húzd be a Sorcímkékhez (Rows) és már kész is van! Több oszlop esetében már körülményesebb a megoldás, nem érdemes küzdeni vele.

nov 12

Duplikációk kiszűrése képlettel

Pár napja épp egy kolléganőmnél bukkant fel a probléma: ellenőrizzük le, hogy egy listában (cikkszámok) van-e többször előforduló adat, s ő ügyesen meg is oldotta egy formulával. Egyszerűen megszámolta, hogy az egyes adatok hányszor fordulnak elő:

duplication1

=DARABTELI( $B$2:$B$6;B2 )

=COUNTIF( $B$2:$B$6;B2 )

Látjátok, hogy a DARABTELI formulát használta. Sajnos ez az egyik formula, melynek nem igazán sikerült jól magyarítani a nevét. Az angol név COUNTIF – ennek tükörfordítása, a „darabha” pontosan azt fejezné ki, ami történik: a formula megszámolja, hogy a megadott tartományban (ez az első paraméter) hányszor fordul elő a feltételnek (második paraméter) megfelelő adat. Ezzel a második, vagy feltétel paraméterrel sok trükkös dolgot lehet csinálni, de most elég egyszerűen megadni a megszámolni kívánt értéket.

Mit lehet tenni, ha szeretnénk ezeket a duplikációkat eltüntetni? Több lehetőség is adódik, itt most egy szép és egyszerű módszert szeretnék bemutatni, ami egyben jó gyakorlás az abszolút és relatív hivatkozások alkalmazására. A következő bejegyzésben mutatok majd más megoldást is.

Kolléganőm fenti megoldásából láttátok, hogy a DARABTELI (COUNTIF) formulát használta, azonban minden adat mellé azt a darabszámot kapta, ahányszor az adat előfordul: vagyis minden körte mellett 2 szerepel – így nem tudjuk eltávolítani a duplikációt. A trükk a hivatkozásban lesz: ha mindig csak az adott cella fölötti (adott cellát is tartalmazó) tartományban számoljuk az előfordulást, akkor az első előfordulásra 1-et, a másodikra 2-t, stb. kapunk.

duplication4

Legegyszerűbb, ha az utolsó sorba írod fel az eredeti képletet, és módosítod a hivatkozást (kitörlöd a $ jelet a $B$6-ból) majd felfelé másolod a formulát. Ezután egy egyszerű szűrővel (filter) kiszűrheted az első előfordulásokat a Hányszor? oszlopból.

És ha több oszlopból áll az adattábla? Akkor a DARABTELI formula több paraméteres verziójára lesz szükség – amit másképpen sikerült magyarítani: DARABHATÖBB vagy COUNTIFS.

duplication5

=DARABHATÖBB( $B$2:B6; B6; $C$2:C6; C6 )

=COUNTIFS( $B$2:B6; B6; $C$2:C6; C6 )

Figyeld meg, hogy a formula paraméter-párokkal dolgozik: mindig egy tartomány és utána egy kritérium van megadva. Ennek megfelelően csak a második körte – pálinka adatnál látunk 2 értéket. Itt is a szűrőt lehet alkalmazni, ha az egyedi adatok listájára van szükség, vagy ha a duplikációt akarod kitörölni.

Kérdezz tőlünk Excel segítő csoportunkban vagy kövesd az Adatkertészetet a Facebookon!

nov 08

Abszolút és relatív hivatkozások

avagy: a $ jel az Excelben a ragasztó! Nagyon fontos, hogy ezt nagyon jól megértsétek.

Ha felírtad már akárcsak a legegyszerűbb képletet Excelben, akkor biztosan használtál már relatív hivatkozást. Például így néz ki:

=A1

ez a kis képlet az A1 cellában levő adatot adja vissza. Ha ezt a képletet lemásoljuk, az a másolás, kitöltés irányától függően megváltozik: ha lefelé másolod, mondjuk egy sorral lejjebb =A2 -t látsz. Ha egy oszloppal jobbra, akkor =B1 lesz belőle. A cella hivatkozások, amik a képletben szerepelnek, automatikusan „elmozdulnak” a másolás irányába. Azért van ez, mert nem a cellát magát, hanem a relatív pozícióját figyeli az Excel ebben az esetben.

(Az alábbi képeken a képletet mindig a D3 cellába írtam, majd lemásoltam jobbra, és lefelé, az E4-ig.)

 Relative

Sokszor van arra szükség, hogy a hivatkozások ne mozogjanak – vagyis, hogy „odaragasszuk” a cellát. Például, ha egy árfolyamot írunk a cellába, és mindenhol ezt szeretnénk használni, akkor ezt „be kell ragasztózni”. Mint írtam, az Excelben a ragasztó a $ jel, így néz ki, ha az árfolyamot tartalmazó cella (legyen az A1) sorát és oszlopát is jól bekenjük ragasztóval:

=$A$1

Ezt bárhova másolom, mindig az A1-re fog mutatni. Ezt hívjuk abszolút hivatkozásnak.

Absolute1

Sokszor előfordul, hogy nem az egész cellát, hanem csak a sort vagy az oszlopot szeretnénk odaragasztani. A magyar terminológia ezt vegyes hivatkozásnak hívja, hiszen ezek esetében a $ jelet csak az oszlop vagy csak a sor elé tesszük ki a képletben:

=$A1 – itt az oszlop fixen marad, ha a képletet „oldalra” másoljuk, de a sor változik, ha lefelé másoljuk.

 Absolute2

Ennek fordítottja: =A$1 – a sor marad fix, az oszlop változhat.

Absolute3

A $ jelet nem kell elkeseredetten keresni a klaviatúrán – én mindig elfelejtem, hol van, most, hogy írok róla, már talán sikerül megjegyezni. Ha a képlet beírásakor az F4 gombot nyomogatod, egymás után adja a négyféle hivatkozást. ($A$1, A$1, $A1, A1)

Tehát mire emlékezz?

A $  jel a ragasztó. Odaragaszthatod vele csak a sort, csak az oszlopot vagy mindkettőt. Amit beragasztóztál, az nem mozog, amikor a képletet másolod. És: a ragasztó az F4 billentyűből folyik.

nov 06

Ronda Grafikon 1: Diagram adatokkal

Néhány napja találtam az alábbi diagramot, és kissé elcsodálkoztam, hogy ilyet bárki kiad a kezéből… Nem részletezem, szerintem önmagáért beszél.

De ha már megláttam, nem tarthatom vissza magam, hogy mutassak egy jobb verziót. Feleslegesnek tartom az összes adatot feltenni a diagramra, de ha már ez volt az eredeti koncepció, hát ragaszkodjunk hozzá.

Ilyen volt:

BadChart-GoodChart_01

Ilyen lett:

BadChart-GoodChart_02

Az adatokat egyszerűen leírtam az eredeti diagramról. A megoldás egyszerű vonal diagram (line chart) a megfelelő méretűre állított jelölőkkel (marker). Az adat feliratokat (data label) középre igazítottam és a hátterét kitöltés nélkül hagytam (no fill). Az egyetlen igazi trükk a bal oldalon elhelyezkedő nevek felrakása: ezek egy pont (scatter) diagram típusú adatsorhoz tartoznak. De erről majd később, hiszen ezeket egyszerűen szövegdobozok (text box) hozzáadásával is fel lehet rakni a diagramra – így került oda a cím, az alcím és a két évszám is.

Nektek melyik tetszik jobban?

nov 06

„Ott van a számban a pont”

„Nem vagyok én szájsebész.”

A munkahely kiváló ihlető forrása az írásoknak. Részben azért, mert igazi, mindennapi felhasználói problémák vetődnek fel, másrészt, mert időnként vicces formában hangzanak el a kérdések.

Tehát, a probléma: gyakran előfordul más, külső rendszerből történő adat átvételnél, hogy a szám adatok számként nem értelmezhető karaktereket tartalmaznak. Sokszor van a számok előtt szóköz, esetleg a tizedes pont vagy ezres elválasztó karakter különbözik az Excel beállításaitól. Egyszerű megoldásként általában Keres/Cserél-t (Find / Replace) alkalmazunk a menüből. Jelen esetben a „számban” levő pontot kellett volna eltüntetni, azaz semmire kicserélni. Ez azonban valamiért nem sikerült, az Excel nem cserélte ki a pontokat. Ne foglalkozzunk most az okokkal (többféle is lehet), inkább mutatok egy jobb megoldást.

Az adattisztítás megoldható két képlet használatával.

A KIMETSZ vagy TRIM (a 2007-es Excelben is TRIM) formula az összes felesleges szóközt eltávolítja a szövegből (csak a szavak közti egyszeres szóközt hagyja meg). Így működik:

Trim1

=KIMETSZ(A2)

=TRIM(A2)

 

A HELYETTE vagy SUBSTITUTE formula  a pontot fogja eltávolítani ebből a tisztított adatból. Első paramétere az a szöveg (cella) amiben a helyettesítést végezzük, második paraméter: amit cserélünk, harmadik paraméter: amire cseréljük. Kolléganőmek így építettem fel:

Trim2

=HELYETTE(B2;”.”;””)+0

=SUBSTITUTE(B2;”.”;””)+0

A harmadik paraméter egy üres idézőjel („”) – ez jelenti Excel-nyelven a semmit, vagyis hogy a pontot semmire cseréljük. Természetesen bármi másra is cserélhetnénk, például vesszőre, ha épp tizedes jegy elválasztó karakterről lenne szó.

A képlet végén +0 áll, ez fogja számmá konvertálni az adatot, hiszen a karakter helyettesítés szöveg művelet, tehát az Excel alapból szövegként adja vissza az eredményt – így viszont nem tudunk vele számolni. A legegyszerűbb matematikai művelet (+0) már elegendő, hogy számmá alakuljon az adat.

Miért jobb ez a megoldás?

Mert nem kell manuálisan elvégezned a keres-cserél műveletet (kétszer!) – a képletek ott maradnak a munkalapon, ha új adatot másolsz be, akkor rögtön „megtisztítják” az új adatokat is.

Tehát mire emlékezz?

=KIMETSZ(szöveg) vagy =TRIM() a szóközök eltávolításához

=HELYETTE( szöveg ; régi szöveg ; új szöveg ) vagy SUBSTITUTE() karakterek cseréléséhez.

 

 

nov 05

Anyukám születésnapjára

Mi is lehetne jobb alkalom induló Excel blogom első bejegyzésének megírására, mint anyukám születésnapja!

Boldog születésnapot anyu!!

Torta_gyertyak

Gondolom, sejtitek, hogy a kép egy Excel diagramot ábrázol.

Nem, most nem magyarázom el, hogyan készült. (Bár aki nagyon kíváncsi, angolul utánaolvashat, és persze le is töltheti a fájlt.) Ez a blog nem ilyen bonyolult dolgokról fog szólni. (Legalábbis egyelőre… :-)). Gyakorlatban jól használható, egyszerű dolgokat szeretnék megosztani, megtanítani. Olyan dolgokat, amik tapasztalatom szerint nagyban megkönnyítik a mindennapi munkát.

Persze azért mégiscsak van tanulsága a fenti képnek: szinte mindent meg lehet csinálni Excelben! Tehát: bátran kérdezzetek, ne vesszetek el a manuális munkában, a régről örökölt, sok vesződséggel járó megoldásokban. MINDIG lehet találni olyan megoldást, ami egyszerűbb, gyorsabb, kevesebb hibalehetőséget tartalmaz, ÉS könnyen megérthető. Sokszor csak egy egészen pici „plusz” kell, és szinte csodákat tehetünk. Ilyen pici pluszokat fogtok itt találni!

Jó olvasást – és még egyszer: Isten éltesse anyukámat!

css.php