márc 17

Excel fájl típusok: xlsx, xlsm, xlsb

Az Excelben a 2007-es verziótól kezdődően új fájl típusok érhetőek el. Az alap fájl már .xlsx kiterjesztést kap, nem .xls-t, mint a régi, 2003-as Excelben. Az Excel újításai (amelyek közül a legszembetűnőbb, hogy a sheet már több, mint egymillió sort tartalmaz) csak az új fájl típusokban érhetőek el. Ha régi, .xls fájlt nyitsz meg, az kompatibilis üzemmódban (Compatibility Mode) működik. Ez tapasztalatom szerint sokszor lefagyást és egyéb problémákat okoz.

compatible_1

Ha 2003-nál újabb Excelt használsz, ajánlott, hogy a régi fájlokat is az új formátumok valamelyikébe mentsd el. Ezt egyszerűen megtehető a Mentés másként (Save As) gombbal. FONTOS: Ha megtörtént a mentés, be kell csukni és újra kinyitni a fájlt, hogy a kompatibilis mód megszűnjön.

No de melyek ezek a formátumok és mire jók?

Normál fájl, makró nélkül

Az alap beállítás fájl mentéskor az .xlsx (Excel workbook) kiterjesztés. Nem túl nagy méretű (<5Mb) fájloknál, amikben nem használsz makrókat, ezt a fájl típust használhatod, de választhatod az .xlsm (Excel Macro-Enabled workbook) vagy .xlsb (Excel Binary Workbook) kiterjesztést is.

Fontos tudni, hogy az .xlsx fájl nem tartalmazhat makrókat és más Visual Basic elemeket. Mentéskor az Excel figyelmeztet, ha ilyesmi van a fájlodban – figyelj erre az üzenetre, mert könnyen el lehet veszteni a makrókat egy óvatlan OK megnyomással!

compatible_2

 

Makrós fájlok
Ha makrós fájlt mentesz, akkor az .xlsm (Excel Macro-Enabled workbook) vagy .xlsb (Excel Binary Workbook) kiterjesztést válaszd.

 

Nagy méretű fájlok
Nagy méretű fájlok esetében nálunk a gyakorlatban elég jól bevált az .xlsb (Excel Binary Workbook) fájl típus. Ez ugyanúgy működik, mint a „hagyományos” Excel fájlok, csak abban van különbség, ahogy a lementett fájlban technikailag tárolódnak az adatok, de ezzel nekünk nem kell foglalkozni. Ez a fájl típus a mentésre és a megnyitásra van optimalizálva, tehát gyorsabban mentődik és nyílik meg, valamint a mérete is kisebb az .xlsx fájlnál. Makrós fájlokat is lehet ebbe a formátumba menteni, tehát ez egy gyors, tömör és univerzális fájl típus.

 

Kövesd az  Adatkertészetet a Facebookon!

márc 16

Milánóban jártunk

Az elmúlt héten tartottunk egy kis szünetet, és elmentünk vakációzni Milánóba. Néhány olvasónk talán tudja, hogy egy olasz barátunkkal, Robertoval együtt írunk egy angol nyelvű blogot is. Roberto Milánóban él, ezért természetesen vele is találkoztunk. 🙂 A családjával és barátaikkal együtt elmentünk egy kis erdei túrára az Alpok szélére. Canzo városkáig vonatoztunk, és a Corni di Canzo csúcs irányába sétáltunk.
Egy menedékházban nagyon jót ettünk, ittunk finom vörösbort és grappát is, majd egy tanösvényen jöttünk lefelé, ahol sok mókás faragott szobrot láttunk az út mellett: manók, teknősbéka, szitakötő… a részeg szamár volt a legviccesebb. Nagyon sok sárga kankalin nyílt az út mellett, hóvirágot viszont nem láttunk (pedig itt-ott voltak hófoltok). Megnéztünk egy kis erdei múzeumot is, amiben a környező hegyek makettjét láttuk a hegyi ösvényekkel.
Itt egy kis kép válogatás:

[slideshow_deploy id=’585′]
És két közös kép a „nemzetközi” csapatról:

07_team_rifugio

08_team_Duomo

Az angol blogon is megemlékeztünk erről a találkozásról, ott egy grafikont is publikáltunk, ami az Alpokat ábrázolja több, mint 21 ezer képpontból kirajzolva – a Corni di Canzo csúcs is megtalálható közöttünk, keressétek meg!

 

febr 18

Pontatlan számítási eredmények: amikor a nulla nem nulla

floatingpoint1Öcsém mutatott pár napja egy érdekes hibát az Excelben. Összeadott néhány számot, az eredménynek 0-nak kellett volna lennie, e helyett azonban valami ilyesmit kapott: 1,4210854715202E-14

Például az alábbi nagyon egyszerű esetben, ahol egy SZUM formulával számoltuk az összeget:

Mi ez az E betűs furcsaság?

Olvass tovább

jan 31

Sávos terület diagram – avagy Kifli-grafikon

Van már annak pár hónapja, hogy megakadt a szemem egy jó kis tanulmányon a bostoni metróforgalomról (Visualizing MBTA data project by Mike Barry and Brian Card) – fantasztikus dinamikus adat vizualizációkkal van tele. Persze ezeket nem Excellel, hanem speciálisabb programokkal (programnyelvekkel) készítik, de ilyesmik szokták inspirálni az angol blogunk cikkeit. Most is ez történt – angolul már írtunk az általunk Croissant chart-nak (magyarul kiflinek) nevezett grafikonról. Mivel ez egy látványos és egyszerű megoldás, ebben a cikkben megosztom veletek is, hogyan lehet Excelben elkészíteni.

Miről van tehát szó? A Kisföldalatti egy napi utasforgalmi adatait(1) szeretnénk ábrázolni, órára lebontva. Mivel nagy a különbség a kora reggeli és a csúcsidőszaki adatok között (0-2200 fő), ezért sávokra osztjuk az adatokat, és színárnyalatokkal fogjuk érzékeltetni a nagyobb forgalmat – kicsit hasonlóan a domborzati térképhez.

Így fog kinézni a végeredmény egy állomásra:

kifli_chart1v

Így az egész értéktartományban jól látszanak a részletek, könnyen leolvashatóak az adatok. Az alacsony formátum miatt egyszerűen egymás alá lehet majd tenni több állomás diagramját,  hogy áttekinthetőbben lehessen összevetni őket.

Lényegében csak annyi történt, hogy az eredeti, hegyhez hasonlító grafikont elvágtuk 500, 1000, 1500… limit-értékeknél, és ezeket a szeleteket egymásra csúsztattuk – ezt látjátok a narancssárga keretben:

kifli0

Az ábráról látszik is, hogy mit kell csinálni: az eredeti adatból (ami a legvilágosabb szürke, legmagasabb hegy) kivonunk 500-at, így kapjuk az egyel sötétebb részt. Ezután kivonunk 1000-et, majd 1500-at, így csúszik a hegy egyre lejjebb, s kerül bele a csúcsa is a narancssárga keretbe.

Ezután egyszerűen lehagyjuk a felesleget, vagyis beállítjuk, hogy a függőleges tengely csak 0-500-ig mutassa az adatokat.

Nézzük lépésről lépésre!

1. Az eredeti adatok mellé számoljuk ki az elcsúsztatott adatokat. Ehhez le kell vonni az adatokból rendre 0-t, 500-at, 1000-et, stb., tehát a választott vágás-limit többszöröseit. Én ezt egy kis táblázatban csináltam meg, a fejléc fölé felírtam a többszörösöket:

kifli_data_h

Az E4 cella képlete: =$C3-E$1*$C$1

2. Jelöld ki a szükséges adat-tartományokat: jelöld ki az adatokat (E3-ból I23-ig, fejléccel együtt!) és a vízszintes tengely feliratát (B3-tól B23-ig, szintén a fejléccel együtt). (Több tartomány együttes kijelöléséhez a CTRL-t kell nyomva tartanod.)

3. Elkészítjük a diagramot. A Beszúrás menüben a Diagramok csoport alól válaszd a Terület-et. A legelső, Két dimenziós terület diagramra lesz szükség. (Insert Charts / 2-D area)

Létrejött a mű – az Excel alap színeivel, és automatikus függőleges tengellyel:

kifli_v1

4. Állítsd be a tengely határokat: Kattints a függőleges tengelyen, hogy ki legyen jelölve, ahogy a fenti képen látod, majd kattints rajta jobb gombbal, válaszd a Tengely formázása (Format axis) menüt, és állítsd be a minimumot 0-ra, a maximumot 500-ra:

kifli_axis

5. És ezzel a lényeg kész is van – a színekkel való játszadozást rátok bízom!

Ja, s hogy miért kifli? Gábornak a feltekert kifli jutott róla eszébe. 🙂

Innen letölthetitek a minta-fájlt is, ahol minden állomásra megcsináltuk a grafikont, és egymás alá raktuk őket az „All” munkalapon.

kifli_all_

(1) adatok forrása: http://lazarus.elte.hu/hun/digkonyv/szakdolg/2012-msc/zsiga_erika.pdf p.10

 

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

dec 18

Halmozott oszlop diagram dupla adatsorral

Nagyon fontos bejegyzés következik – a cikkírás apropóját felső vezetői kérés adja. Azzal keresett meg ma az igazgató, hogy készítsek halmozott oszlop diagramot (Stacked column chart), amin több termékcsoport tavalyi és idei adatait tüntetjük fel negyedéves bontásban. Ilyesmit:

StackedCol1

Nem tűnik nehéznek, hiszen csak egy halmozott oszlop diagram – sajnos azonban az Excel nem támogatja azt, hogy ezen a diagram típuson csoportba legyenek rendezve az oszlopok, és minden második oszlopot másképp színezzünk. Némi trükköt kell alkalmazni. Bár a leírás hosszúnak tűnik, nagyon egyszerű lépésekből áll. A cikk végén pedig megosztom az én minta fájlomat is, amiben akár át is írhatjátok az adatokat, így nem kell a grafikon építéssel küzdeni.

Lássuk tehát lépésről lépésre a diagram építést!

1. Induljunk ki egy ilyen adattáblából:

StackedCol2a

Az a lényeg, hogy ami majd a csoport lesz a grafikonon (Prod 1, 2, 3 – ezekre lesz két-két oszlop) az legyen függőlegesen lefelé a táblázatban.

 

2. Elkészítjük az alap diagramot.

2.1. Jelölj ki egy sorral többet a táblázatban!

StackedCol3b

2.2. A Beszúrás (Insert) menüben a Diagramok csoportból a Oszlop alatt találod a Halmozott oszlopot (Stacked column) típust.

StackedCol4

Ilyen lett?

StackedCol5a

 

3.  Meg kell kell cserélni a diagramon a sorokat és oszlopokat – ez csak egy kattintás! Jobb egér gombbal kattints a diagramon, a menüben válaszd az Adatok kijelölése (Select data) pontot, és nyomd meg a Sor/oszlop váltása (Switch Row/Column) gombot.

StackedCol6

Most így néz ki a mű:

StackedCol7a

 

4. Kicsit szétcincáljuk az adattáblát.

4.1. Először is: a Prod 1 és a Prod 2 alá is két-két sort kell beszúrni. Ettől a diagramon szépen eltávolodnak egymástól az oszlopok.

StackedCol8d

4.2. Ezután másold le a 2014-es adatokat egy sorral lejjebb, majd töröld ki az eredeti helyéről. (Fontos, hogy másold, utána töröld, semmiképpen NE MOZGASD az adatot! A mozgatástól szétesik a diagram!)

StackedCol8e

Most már alakul!

StackedCol9b

 

5. Némi formázásra van még szükség:

1.1. Kattints jobb gombbal az egyik oszlopon, és válaszd az Adatsorok formázását (Format Data series). Az Adatsor beállításainál (Series Options) a Térközt (Gap Width) állítsd 10%-ra.

StackedCol10

5.2. Ugyanebben a menüben formázhatod az adatsorokat: beállíthatod az egyes oszlop-szegmensek színét.

5.3. A vízszintes tengely feliratát a táblázat első oszlopa adja. Másold le a Prod 1, 2, 3 feliratokat, hogy kétszer szerepeljenek egymás alatt – így lesz minden oszlop alatt felirat.

Íme az eredmény: (Ezután jöhet a színezés, ízlés szerint.)

StackedCol11

 

Most pedig egy pici elmélet: Valójában egy hagyományos halmozott oszlop diagramot készítettünk, ami 8 oszlopból áll: két-két oszlop a három termékhez, és két üres oszlop. Minden oszlopban 8 adat van egymásra halmozva: két év négy-négy negyedéve. Azonban minden oszlopban üres az egyik év adata – így alakul ki a két különböző év oszlopa. Ha figyelmesen megnézitek az adattáblát, az pont ezzel a felépítéssel harmonizál: a két üres sor adja az üres oszlopokat, a lemásolt, elcsúsztatott éves adatok sorából pedig épp az a 4-4 adat hiányzik, ami az adott diagram-oszlopba nem kell.

Ennek alapján láthatjátok, hogy nem csak két, hanem több oszlopot tartalmazó csoportokat is lehet ezen a diagramon képezni, így viszont már fennáll a veszélye, hogy nem lesz áttekinthető a végeredmény. Én 3 vagy több oszlop esetében inkább külön diagramokat csinálnék.

Innen letölthetitek a példa fájlt, amiben csak az adatokat kell átírni.

Jon Peltier kiváló Excel guru oldalán pedig olvashattok egy nagyon részletes leírást ugyanerről a grafikonról, rengeteg további részlettel.

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

dec 09

Körkörös hivatkozás – Circular reference

Múlt héten tréninget tartottam, ami nagyon jól sikerült a visszajelzések szerint. 🙂 Úgy érzem, jól összeérett az anyag, és szép, áttekinthető jegyzetet is sikerült írnom hozzá. A tréning után tartottunk egy kis fogadóóra-félét, ahol felmerült a körkörös hivatkozás kérdése. Előtte nap az irodában is kérdezték ezt tőlem, ideje hát írásban is közreadnom a választ.

Tünet: hibaüzenetet kapsz – magyarul egy kicsit bővebb a tájékoztatás, mint angolul:

Circular1

Circular2

Ha a hibaüzenetet egy OK-val elintézed, akkor az Excel státusz sorában, a bal alsó sarokban jelenik meg egy Körkörös hivatkozás figyelmeztetés, ami általában a problémás cellát is mutatja.

Circular4

Ez komoly probléma lehet, a körkörös hivatkozást meg kell szüntetni, mert amíg ez ott van, az Excel nem biztos, hogy kalkulálja a munkafüzetet!

Ok: Ahogy a hibaüzenetben is olvashatjátok, a baj az, hogy egy képlet önmagára hivatkozik. Például – ami a munkahelyen is előfordult – egy SZUM formula véletlenül túl volt húzva, és önmagát is tartalmazta:

Circular3

Egyértelmű, hogy ilyenkor szegény Excel ciklusba keveredne, és a végtelenségig kellene számolnia, mert mindig változna az összeg… Ezek az esetek a számítási logikában alapesetben le vannak tiltva, az Excel érzékeli, hogy a cella saját magára hivatkozik, és leállítja a kalkulációt. Persze ez nekünk nem jó… 🙁

Megoldás: Meg kell keresni azt (vagy azokat) a cellát, amiben a körkörös hivatkozás van, és ki kell javítani. A nehézség ott adódik, hogy hogyan lehet a cellákat megtalálni? Ha épp azon a munkalapon állsz, ahol probléma van, akkor alul a státusz sorban az Excel kiírja a cella címét, ahogy a fenti képen a zöld nyíl mutatja.

Ha nem látod itt a címet, akkor a menüben fogod megtalálni – a Képletek (Formulas) alatt keresd a Hibaellenőrzést (Error Checking)

Circular6

Itt megtalálod a cella címét a munkalap nevével együtt. Ebben a cellában kell javítani / kitörölni a hibás képletet.

Ha a javítás után még mindig látszik alul a Körkörös hivatkozások üzenet, akkor van még hibás képlet a munkafüzetben – addig kell keresni és javítani, amíg az üzenet eltűnik.

 

nov 19

Nő a fájl mérete, begyorsul a görgetősáv

Ma két kollégám is megdicsérte a blogot. 🙂 Tegnap pedig Gábor unokatestvére írta, hogy „Rohadt jó”. 🙂 Örülök nagyon! S mivel megfogadtam, hogy ha egy nap ketten kérdezik ugyanazt, akkor arról posztot írok, így most igazán lelkesen teszem ezt – ma ugyanis egy órán belül kétszer is hívtak az alábbi miatt.

A probléma, ami felmerült, gyakran előfordul a munkahelyen: látszólag ok nélkül megnő a fájl mérete. Ez együtt jár azzal, hogy „elromlik” a görgetősáv: egy picit mozdítasz rajta, és máris több tízezer sort ugrik le vagy jobbra. Úgy is meg szokták fogalmazni: túl gyors a görgetősáv.

gorget_1

Hogy jobban értsük a problémát, tudni kell, hogyan működik a görgetősáv: minden Excel munkalap (2007 és későbbi verziók) több, mint egymillió sort és 16 384 oszlopot tartalmaz. A görgetősáv szerencsére „intelligens” és csak a munkalap használt részét mozgatja. Normális esetben tehát, ha a függőleges görgetősávot lehúzod a legalsó pontjáig, akkor nagyjából a munkalapon levő utolsó adat sornál kell megállnia. Ugyanígy kell ennek lennie a vízszintes görgetősávval is: az utolsó adat oszlopnál áll meg. Ezt a „használt részt” hívjuk Used Range-nek.

Próbáld ki: írj be valamit a munkalap legutolsó cellájába (XFD1048576) és nézd meg, mi történik a görgetősávval! (Könnyen megtaláljuk az utolsó cellát így: Ctrl+<Jobbra nyíl>-lal jobbra navigálunk a teli/üres szakszokon, majd Ctrl+<Le nyíl>-lal ugyanez lefelé. Ezek egyébként is nagyon praktikus navigációs funkciók. 🙂 )

Persze az, hogy a munkalap utolsó cellájában van adat, még nem ok a fájl méretének növekedésére. Esetünkben a probléma az, hogy az Excel úgy érzékeli, a munkalapon minden cellában van valami… nem látjuk, hogy mi, de mégis ott van. Ne foglalkozzunk most azzal, hogy éppen mi ez a valami, és hogyan került oda (talán majd egyszer erről is írok). Most a megoldásra fókuszáljunk: el kell ezt a valamit tüntetni, ki kell törölni.

Amit én javasolni szoktam:

1. Keresd meg az utolsó sort, ahol még értékes adat van a munkalapon.

2. Jelöld ki az összes ez alatt levő sort. (Legyegyszerűbben: Shift+Space-el kijelöljük a teljes sort, majt Ctrl+Shift+<Le nyíl>-lal lemegyünk a munkalap aljáig.)

3. Töröld ki ezeket a sorokat – teljes sorokat törölj, nem elég a delete gomb! A menüben: Kezdőlap / Cellák csoport / Törlés (az alsó nyilat nyomd meg!) / Sorok törlése munkalapról. (Angol Excelben: Home / Cells / Delete / Delete Sheet rows) (Vagy egyszerűbben, egérrel: <Jobb gomb> / Törlés)

4. Csináld meg ugyanezt az oszlopokra is. (Egyszerű kijelölés: Ctrl+Space-el kijelöljük a teljes oszlopot, majd Ctrl+Shift+<Jobbra nyíl>-lal elmegyünk az utolsó oszlopig.)

Ezután mentsd el a fájlt, csukd be és nyisd ki újra. Ezzel – tapasztalatom szerint – az esetek többségében megoldódik a probléma.

Ma azonban láttam egy kivételt: egy kedves kolléganőm fájlja nem javult meg a fentiek elvégzése után. Az alternatív megoldás az, hogy beszúrsz egy új, üres munkalapot, és átmásolod rá az elromlott munkalapon levő hasznos tartalmat. NAGYON fontos, hogy csak azt a részt jelöld ki, ahol adat, információ van! Semmiképpen NE használd a teljes munkalap-tartalom, teljes oszlop vagy sor  kijelölést! (Hiszen akkor a szemét is átmenne…)

Kérlek, írjátok meg, hogy nálatok előfordult-e hasonló probléma, s hogy sikerült-e megoldani ezzel az eljárással!

 

nov 10

Egy apróság: sorok, oszlopok mozgatása

Nos, ez a cikk jó példa lesz arra, hogy én sem tudok mindent, SŐT! a legalapvetőbb dolgokban is hiányosságaim vannak.

Nemrég történt, hogy egy kollégám kérdezte tőlem, hogyan lehet az Excelben sorokat, oszlopokat mozgatni – megfogni egérrel és húzni úgy, hogy ahova húzom, oda beszúródjon. Úgy emlékezett, hogy valamit meg kell nyomni, de nem sikerült válaszolnom a kérdésre. Nem is foglalkoztam tovább a dologgal, pedig tudtam, hogy ez hasznos lenne, én is többször küzdöttem már vele.

Aztán a minap egész komoly oszlop-átrendezgetést kellett végrehajtanom egy munkalapon, miközben Gábor a hátam mögül nézte, mit csinálok. Elcsodálkozott a bénázásomon, és rögtön mondta a megoldást: miért nem nyomom le a Shift-et? Hát, izé… Mert nem tudtam, hogy azt kell lenyomni.

Tehát:

Oszlop mozgatás egérrel (megfogod a szélét, és húzod) azt jelenti, hogy az F oszlop a D helyére kerül, vagyis a D oszlop felül lesz írva (erre az Excel figyelmeztet is).

oszlop_mozgatás_f

Ha oszlop mozgatás közben (mielőtt „elengednéd” az oszlopot) lenyomod a SHIFT-et, akkor az F oszlop a C és D közé szúródik be:

oszlop_mozgatás_b

Én nagyon örülök, hogy ezt végre (khm…) megtudtam, remélem, hogy nem én vagyok az egyetlen Excel-használó, akinek ez újdonság volt – és hasznos is! 🙂

 

nov 05

Egy éves a blog! Játék: rajzolj gráfot betűkből

Adatkertészet_gÉpp ma egy éve, hogy elindítottuk a blogot- az első bejegyzés egy Excelben rajzolt születésnapi torta volt, elfújható gyertyákkal – anyukámnak.

Ebben az egy évben 26 bejegyzést írtunk. Nem túl sok. Talán többet terveztem, de ezek szerint átlagosan 2 hetenként írtunk valamit. Az nem is olyan rossz. Ehm… bár az utóbbi hetekben nem osztottam meg semmit… De dolgozunk valamin. Egy fontos projekten, amit már régóta szeretnék megvalósítani. Hamarosan itt is publikálni fogjuk. Addig is, hogy ne unatkozzatok, egy kis játék következik!

Egy szójátékot mutatunk nektek – Roberto és Gábor régebbi munkája ez a rejtvény készítő alkalmazás, ami eddig kissé elrejtve maradt az angol postok között. A játék neve: Rajzolj gráfot betűkből! Nagyon egyszerű a szabály: az ábrán levő betűket a nyilak mentén kell összeolvasni. Az első feladat: meg kell találni a kezdőpontot, az első betűt, utána már könnyű lesz!

Ti is készíthettek ilyen rejtvényeket az alábbi generátor program segítségével:

Rajzolj gráfot betűkből – rejtvény generátor

Egy csapatépítő tréningen kipróbáltuk, és jó mókának bizonyult a cég sajátos szókincséből, a vezetők nevéből készült ábrák elolvasása.

Jó szórakozást kívánunk mindenkinek!

És boldog születésnapot anyukámnak!

 

szept 01

Sor magasság beállítása formulával!

TolomeroElég sok Excel hírlevelet, blogot, fórumot követek az interneten, de általában csupa olyasmit olvasok, amit már ismertem vagy láttam valahol. A minap azonban egy zseniális trükköt találtam, ami… na jó, bevallom, kicsit irigy vagyok, hogy nem én találtam ki. 🙂

A lényeg: ha a cellában hosszabb szöveg szerepel, a Sortöréssel több sorba igazítás beállításával az Excel több sorba tördeli a szöveget, és ezzel együtt automatikusan megnöveli a sor magasságát. (Az automatikus sormagasságot legegyszerűbben a sorokat elválasztó kis vonalon dupla kattintással beállíthatod te is.)

Sortores_1

A cella szövegébe kézzel is be lehet szúrni sortörést (cellán belüli sortörésnek hívják ezt), ami azért hasznos, mert így te magad szabályzod, hogy honnantól kerüljön a szöveg új sorba a cellán belül. Ehhez az Alt+Enter-t kell nyomni a cella szövegében:

Sortores_2

Az Alt+Enter kombinációval egy láthatatlan sortörés karaktert teszünk a cellába. S itt jön a trükk lényege: a sortörés karaktert formula segítségével fogjuk beírni, méghozzá annyiszor, ahány sornyi magasságot szeretnénk beállítani az adott sorhoz!

Szó volt már a SOKSZOR(szöveg; hányszor) (angolul: REPT()) formuláról itt a blogon egy korábbi írásban mini grafikont csináltunk a segítségével – most is ezt a formulát fogjuk használni a sortörés karakterek megismétléséhez. A sortörés egy speciális, nem nyomtatandó karakter, amit legegyszerűbben a kódjával tudunk elérni: a KARAKTER(10) formulával, angol Excelben: CHAR(10)

Tehát: SOKSZOR( KARAKTER(10) ;  2)

Angolul: REPT( CHAR(10) , 2)

 

Ez a formula 2 sortörés karaktert rak a cellába, ami így 3 sor magas lesz. Ha beírtad, csak annyi a dolgod, hogy beállítsd a sortöréssel több sorba igazítást és az automatikus sormagasságot.

Így egyszerűen tudsz azonos sor magasságot beállítani egy táblázathoz:

Sortores_3

A 2 helyett cellahivatkozással is meg lehet adni az ismétlés számát, de a változtatásra sajnos rögtön nem nő meg a sormagasság, mindig újra rá kell kattintani az automatikus sormagasság beállításra.

A Contextures blogon éritek el az eredeti blog bejegyzést (angolul) ahol további alkalmazási tippeket találtok!

 

aug 19

Összeg betűvel: számok átírása szöveggé képlettel

csekkGyakran látom a kérdést különböző fórumokon, hogy lehet-e Excelben a számokat betűvel kiírni. Sajnos beépített formula nincsen erre, de Visual Basicben lehet programozni úgynevezett User Defined Function-t.

Én most egy ettől eltérő megoldást szeretnék mutatni, ami nem igényel programozást. A történet még évekkel ezelőtt indult, mikor a LinkedIn-en az Excel Hero csoportban felvetődött, hogy angol nyelven rakjunk össze egy formulát, ami a számokat szöveggé alakítja. Persze én, mint magyar, rögtön elgondolkodtam, hogy magyarul is meg kellene ezt csinálni – hiszen a magyar nyelv egyszerűbb szabály alapján képezi a számokat (gondoljatok csak a tizen- számok képzésére).

Van tehát olyan formula, ami csak az Excel beépített formuláit használva megoldja a szöveges átalakítást – csak eddig nem volt publikálva. Most itt az ideje, hogy megosszam a magyar olvasókkal is!

Innen letölthetitek a fájlt.

Számszöveg

 

A formulában két elnevezett konstanst használunk – ezek azért szükségesek, hogy ne legyen túl hosszú a képlet:

n_1={„0″;”egy”;”kettő”;”három”;”négy”;”öt”;”hat”;”hét”;”nyolc”;”kilenc”}

n_t={„”\”tizen”\”huszon”\”harminc”\”negyven”\”ötven”\”hatvan”\”hetven”\”nyolcvan”\”kilencven”}

Ha a képletet másik fájlban szeretnéd használni, csak át kell másolnod a cellát a szokásos módon, (másolás-beillesztés, Ctrl+C, Ctrl+V) és ezek az elnevezett konstansok automatikusan át fognak kerülni a fájlodba is. A Névkezelőben (Ctrl+F3) tudod ellenőrizni.

 

Ez a bejegyzés az angol blogunk cikke alapján készült, ahol megtaláljátok az angol és olasz nyelvű formulát is, valamint ott gyűjtjük a további nyelveket.

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

júl 08

Buborék diagram kombinálása vonallal

Különleges bejegyzés következik – az angol nyelvű blogunk legutóbbi postjának magyar változatát olvashatjátok. A napokban Roberto-val (ki ő? lásd: Rólunk) egy nagyon ötletes és egyszerű megoldást találtunk arra, hogyan lehet a gyakorlatban közkedvelt buborék diagramot vonallal kombinálni.

Az Excelben rengeteg hasznos és különleges megoldást lehet felépíteni különböző diagram típusok kombinálásával – ebben az esetben is az az egyszerű megoldás adódna, hogy kombináljuk a buborék diagramot pont-vonal diagrammal. Ez azonban zsákutca, sajnos a buborék diagram nem kombinálható semmi más típussal. Mi ezt a buborék diagram saját eszközeivel valósítjuk meg.

Nagyon hasznos lehet ez a megoldás, ha például egy termék hónapról hónapra változó pozícióját szeretnénk bemutatni egy Profit + Növekedés és Volumen adatokból felépített buborék diagramon. Így nézhet ez ki például:

Bubble-line-1

A buborékokhoz az alábbi adat táblát használtuk:

Bubble-line-2

A vonal beillesztéshez a megoldás alapötletét a trendvonal adta. A trendvonal a diagramon mindig egy adatsorhoz illeszkedik, és az általunk választott statisztikai módszer alapján kiszámítja és megjeleníti az adatokban levő tendenciát. Mi a mozgóátlag trendvonalat fogjuk használni – ez egy egyszerű számítási módszeren alapul (idézet a MS Office oldalról):

A mozgóátlag meghatározott számú adatpont (ez a Szakasz beállítással adható meg) átlagát veszi, ez az átlagérték adja a trendvonal egy pontját. Ha a Szakasz beállítás értéke például 2, akkor az első két adatpont átlaga képezi a trendvonal első adatpontját, a harmadik és a negyedik adatpont átlaga a trendvonal második pontját, és így tovább.

Nos, az ötlet alapján én csináltam egy egyszerű, de nem túl elegáns (sok manuális munkát igénylő) megoldást, amit Roberto továbbfejlesztett – így most egy gyors és dinamikus módszert osztok meg veletek. Az ő ötlete alapján a vonal, amit a fenti képen láttok, egy 2 tagú mozgóátlag trendvonal, ami egy, a grafikonon láthatatlanra állított adatsorhoz tartozik.

Tehát: Fel kell tennünk a grafikonra egy olyan (dummy) adatsort, aminek a trendvonala a buborékokat összekötő vonal lesz. Másképp fogalmazva: a mozgóátlag-értékek az adottak, ezek az eredeti adataink. Ki kell számolnunk, hogy mi lesz az az adatsor, aminek ezek az eredeti adatok a mozgóátlagai. Ez az adatsor eggyel több adatot fog tartalmazni, mint az eredeti adataink, hiszen az első ponthoz tartozó mozgóátlag értékhez is két adatra van szükségünk.

Nem olyan bonyolult, mint amilyennek hangzik – nézzük lépésről lépésre!

1. A dummy adatsor értékeinek kiszámítása:

Egy kis segéd táblát készítünk, ennek oszlopait -help oszlopnak neveztem el. Egy sorral több adatra lesz szükségünk, ezt sárgával jelöltem az alábbi ábrán – ez a sor fix 0 értékeket tartalmaz.

  • Growth-help: itt a kis táblázatban 2. sortól az eredeti Growth adatok szerepelnek.
  • Profit-help: Az ábrán látjátok a képletet: 2-vel szorozzuk az eredeti profitot, és kivonjuk belőle a felette levő cella értékét. Ezt a képletet kell lefelé másolni. Ha megnézitek a H oszlop egy celláját, például a H6-ot, itt 8% szerepel márciusra. Ez a 8%  az eredeti márciusi adat és az eredeti februári adat átlaga (11%+5%)/2 = 8%
  • Size-help: itt minden cellába 0-t írunk – így a dummy adatsor buborékainak 0 a mérete, nem fognak megjelenni a grafikonon.

Bubble-line-3

 2. Adjuk hozzá a grafikonhoz a dummy adatsort.

Jobb kattintás a grafikonon, Adatok kijelölése / Hozzáadás (Select data / Add)

A Growth-help oszlop adatai lesznek az x értékek, a Profit-help adatok mennek az y értékekhez, a Size-help pedig a buborék mérethez:

Bubble-line-4

OK, OK után a grafikonon nem látszik semmi (csak a jel a jelmagyarázatban, ha fent hagytad) hiszen a buborék mérete 0.

3. Trendvonal hozzáadása a dummy adatsorhoz:

2007/2010-es Excelben: A Diagrameszközök menüben az Elrendezés alatt jelöld ki a „dummy” sorozatot (bal oldalon, az Aktuális kijelölés alatt a legördülő kiválasztó dobozka) (angolban: Chart Tools / Layout)

Ugyanebben a menüben a jobb oldalon találod a Trendvonal gombot, itt válaszd a Kétperiódusú mozgóátlagot.

2013-as Excelben: A Chart tools / Format menü alatt találod a bal oldalon a Current selection-t, itt van a legördülő dobozka, amiben ki tudod választani a dummy adatsort. A Chart tools / Design / Add chart element alatt tudod felrakni a Trendline/Moving average-et.

És ott a vonal!

Bubble-line-5

4. Formázd a trendvonalat igényeknek megfelelően.

A trendvonalon jobb kattintás után, vagy a menüben ugyanott, ahol a vonalat felraktad, megtalálod a formázási lehetőségeket. Én a vonalstílus alatt a kezdő és vég nyíl beállításával tettem fel a pontot és nyilat a vonal végére.

Innen letölthetitek a minta fájlunkat.

+1. A megoldást dinamikussá teheted elnevezett formulák használatával.

Ennek részleteire azonban itt nem térek ki, mert itt a blogon még nem írtam a dinamikus range-ekről. Akit érdekel ez a megoldás, az megtalálja a részleteket az angol nyelvű cikkben.

Az angol nyelvű cikk itt érhető el, és innen letölthető a dinamikus minta fájl.

Kérlek, írjátok meg, hasznosnak tartjátok-e ezt a megoldást, s osszátok meg bárkivel, akiről úgy gondoljátok, hogy neki is hasznos lehet!

 

jún 26

Printscreen, screenshot, képkivágás: Képmetsző

A cikkek írása során nagyon sok képet használok, hogy bemutassam, hogyan lehet elérni egy-egy menüpontot, beállítani formázásokat, paramétereket. Ezek nagyon hasznosak, (egy kép többet mond 1000 szónál :-)) de picit macerás az előállításuk: PrintScreen-t csinálok, majd bemásolom Paint-be, ott kivágom a megfelelő részt, utána pedig Excelben szoktam rátenni a nyilakat, keretezéseket, mert ott sokkal szebben jelennek meg ezek, mint a Paint-ben.

Pár napja azonban egy kedves kolléganőm mutatott egy szuper kis eszközt, ami a Windows 7-nek része, s nagyon megkönnyíti a képkivágások készítését. Képmetszőnek (angolban Snipping Tool) hívják, és a Start menü / Minden Program / Kellékek alatt lehet megtalálni. (angolban: All programs / Accessories).

Így néz ki:

Kepmetszo

Használata fantasztikusan egyszerű: elindítod, majd kijelölöd a képernyőn a kivágni kívánt részt. Ezután a kivágást rögtön elmentheted, vagy Ctrl+C, Ctrl+V-vel beilleszthető bárhova máshová.

Viszlát, Paint-ben vagdosás!!!

Ráadásul nem csak téglalap alakú kivágás készíthető vele: a menüben (Új, New alatt) legördítve beállítható a szabadkézi alakzat, ablakmetszet vagy teljes képernyő is.

Én egyetlen problémát fedeztem fel a használata közben: ha Internet Explorerből próbáltam kivágást készíteni, akkor lefagyott az Explorer.

 

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

jún 13

„Mielőtt az Egyebek menü Nyomtatási címek parancsát választaná, teljes sorokat és/vagy oszlopokat jelöljön ki”

Ismét egy rövid bejegyzés egy nagyon furcsa hibáról, ami egy kolléganőmnél jelentkezett. Nyomtatás elindításakor, vagy nyomtatási kép megjelenítésekor az alábbi hibaüzenetet kapta:

„Mielőtt az Egyebek menü Nyomtatási címek parancsát választaná, teljes sorokat és/vagy oszlopokat jelöljön ki.”

PrintProblem_1

Na jó, valójában ezt kapta, mert angol nyelvű Excelt használunk:

„Print titles must be contiguous and complete rows or columns.”

PrintProblem_2

A probléma gyökerét sajnos nem sikerült felfedni, bármit csináltunk a fájllal (új munkalapra másolás, értékként beillesztés, nyomtatási terület újra kijelölése…) semmi sem segített. (Print titles egyáltalán nem volt kijelölve!)

Kis Google-nyomozás után megtaláltam a megoldást:

Az Oldalbeállítás panelen a Cellahibák esetén: beállítást kell módosítani: ahogy látható helyett <üres> legyen itt.

Ezt a panelt megtalálod, ha a menüben a Lap elrendezése alatt az Oldalbeállításnál a Címek nyomtatása gombra kattintasz.

PrintProblem_3

Angol Excelben a panelt a Page Layout / Print titles alatt éred el, és a Cell errors as: -t kell <blank> -re állítani.

Remélem, rátalálnak erre a bejegyzésre azok, akiknél ez a hiba előfordul, s így gyorsan orvosolni tudják!

 

jún 11

Navigálás sok munkalapon

Egy apróság jutott eszembe. A munkában legtöbbször olyan fájlokat használunk, amikben sok munkalap van. Hogyan tudunk ezek között egyszerűen mozogni, váltani?

  1. Kattintunk a sheet neveken. Ezt mindenki ismeri.
  1. Használhatjuk a billentyűkombinációkat is: CTRL+PageUp, CTRL+PageDown – ezt is sokan ismerik: egy munklapot ugrunk balra, jobbra.

Ha sok sheetet tartalmaz a munkafüzetünk, akkor ezek már nem praktikusak: ilyenkor gyakran nem is fér ki a sheetek neve egymás mellé, a billentyű kombinációt nagyon sokszor kellene ismételni. A munkalap nevek mellett, a bal oldalon találsz kis nyilakat – ezzel lehet „tekergetni” a munkalap neveket. De…

  1. … ha jobb egér gombbal kattintunk a munkalapok melletti kis nyilakon (a bal oldalon), akkor egy listában megkapjuk a munkalapok neveit. Innen már csak egy (bal)kattintás, és a kívánt sheeten vagyunk 🙂

Excel 2007-ben és 2013-ban itt találod:

SheetNav_2007  SheetNav_2013

Az eredmény pedig:

SheetNav_List

 

máj 21

Hiányzó adatok megismétlése: Kimutatás / Pivot tábla, képlet, manuális megoldás

A mai bejegyzés is egy kedves kollégám kérdése alapján születik. Sokszor előfordul, hogy egy oszlopban az üres cellákat a felette levő adattal kell kitölteni. Ezt a témát fogjuk most körüljárni. Először megnézzük, hol fordul elő gyakran ez a probléma, aztán mutatok képletes és manuális megoldást is.

 

Kimutatás / Pivot tábla klasszikus megjelenítésben

PivotFill1

Nos, a kép önmagáért beszél: a bal oldalon minden hónap megnevezés csak egyszer szerepel, a jobb oldalon meg van ismételve minden sorban. Azok a szerencsés olvasók, akik 2010-es Excelt használnak, nagyon egyszerűen beállíthatják az ismétlődést a kimutatásban:

Kimutatáseszközök menü / Tervezés fül / Kimutatás elrendezése: itt lehet kiválasztani Az összes elemcímke megismétlése opciót.

Angol Excelben:

Pivottable tools / Design / Report layout: Repeat All Item Labels

Sajnos régebbi Excel verziókban nem áll rendelkezésre ez a beállítás. Ilyenkor csak a kimutatás mellett, külön oszlopban, képlettel tudjuk megismételni a címkéket.

 

Megoldás képlettel

Nem lesz nehéz, mindössze egy egyszerű HA (IF) formulára lesz szükség:

PivotFill2

Tehát, ha nincs érték a B oszlop cellájában, akkor használjuk az adott oszlop (F) eggyel feljebb levő celláját. Így néz ki a képlet:

=HA(B4=””; F3; B4)

=IF(B4=””, F3, B4)

 

Manuális megoldás

Ezzel a megoldással az eredeti adat oszlopot tudod kitölteni elemekkel. Kimutatáson persze nem használható, hiszen abba nem írhatunk bele, de egyéb listákon hasznos lehet ez a módszer is. Lássuk lépésről lépésre:

  1. Jelöld ki a tartományt, amiben a kitöltést szeretnéd megcsinálni. Ahhoz, hogy az utolsó adatot is biztosan jól kezelje a módszer, a lista végére tegyél egy x-et.
  2. A Ctrl+G billentyűparanccsal nyisd meg az Ugrás (Go To) párbeszédablakot.
  3. Kattints az Irányított… (Special…) gombra.
  4. Válaszd az Üres cellák-at (Blanks) és nyomj OK-t.

PivotFill3a

Most az eredeti kijelölésen belül már csak az üres cellák vannak kiválasztva. Ezekbe a cellákba fogunk beszúrni egy képletet, ami mindig a felette levő cellára hivatkozik. A trükk csak annyi lesz, hogy ezekbe a kiválasztott cellákba egy lépésben fogjuk beírni a képletet Ctrl+Enter segítségével. Tehát:

  1. Miközben csak az üres cellák vannak kijelölve, kattints a szerkesztőlécre (vagy csak nyomd meg az F2-t) és írd be a formulát, ami az aktív cella fölötti cellára mutat. (Ez a cella fehér a kijelölésen belül – ebbe írod a formulát.)
  2. A formula „lezárásához” Ctrl+Enter-t nyomj – ez fogja az összes kijelölt cellába másolni a formulát.

PivotFill3b

Leírni hosszabb, mint megcsinálni! Próbáljátok ki, sokkal egyszerűbb, mint amilyennek tűnik a sok szöveg alapján!

Megjegyzés: Az F2 majd Ctrl+Enter máskor is praktikus lehet: az összes kijelölt cellába beírja a szerkesztőlécen felvitt formulát, adatot.

 

máj 07

Fájl megnyitása csak olvasásra – beállítási lehetőségek

A napokban többször is felmerült a kérdés, hogyan lehet egy fájlt úgy beállítani, hogy aki megnyitja, ne tudja felülírni az eredeti fájlt. Más szavakkal, hogy csak olvashatóként (read only) nyithassa meg. Erre két lehetőségünk van:

 

1. Fájl megnyitása csak olvasásra – csak figyelmeztető kérdés. A felhasználó figyelmeztető üzenetet kap, ahol kiválaszthatja, hogy csak olvasásra nyitja-e meg a fájlt. Ha a Nem-et választja, akkor szerkesztheti és felülírhatja az eredeti fájlt.

readonly2

readonly3

 

2. Módosítás csak jelszóval. A fájlt csak a jelszó megadása után lehet módosításra megnyitni, tehát csak az tudja a fájlt felülírni, aki tudja a jelszót.

readonly5

readonly4

 

Hogyan tudod ezeket beállítani?

Mindig a Fájl menü Mentés másként (File / Save as) opciót kell választani, akkor is, ha a fájl már el volt mentve.
A Mentés másként párbeszédpanelen válaszd az Eszközök menü Beállítások parancsát (Tools / General options). Az Eszközök (Tools) menüt a jobb oldalon alul találod, a mentés gomb mellett.

1. Ha csak figyelmeztetést szeretnél (ahogy az 1. pontban írtam) akkor klikkeld be a Figyelmeztet a módosítás elkerülésére négyzetet, (Read only recommended) majd kattints az OK gombra és mentsd el a fájlt.

readonly1

 

2. Ha jelszót szeretnél (ahogy a 2. pontban írtam) akkor írd be azt a Jelszó a módosításhoz (Password to modify) mezőbe. Az Excel kérni fogja a jelszó megismétlését, utána lehet a fájlt menteni.

Khm… és egy fontos dolog, amit én is rosszul csináltam egy ideig: A két opciót nem kell együtt használni! Ha jelszót írtál be, akkor nem kell bepöckölni a négyzetet, mert az Excel duplán fog rákérdezni a módosítás engedélyezésére a fájl megnyitásakor.

És még valami: ha meg akarod változtatni a fenti beállításokat (pl. már nem kell a jelszó) akkor ugyanott, a Mentés másként / Eszközök / Beállítások alatt tudod a jelszót törölni, és a fájlt újra el kell menteni.

 

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

febr 10

Ronda grafikon 4: Kördiagram dupla felirattal – trükk!

A legutóbb talált cikk egyik grafikonját már megszépítettük az előző bejegyzésben – most foglalkozzunk a másikkal, a kördiagrammal. Ebben a cikkben mutatni fogok egy (picit haladóbb) trükköt, amivel a kördiagram feliratait és adatait tudjátok szépen elhelyezni: a % a körcikkekbe, az adat neve kívülre kerül.

No, de haladjunk lépésről lépésre, először is itt a kozmetikai beavatkozás alanya:

rondagrafikon4_1

Az európai országokban bevándorlóként lévő magyarok 2012-ben
Fotó: NKI – Gödri Irén

Az első, ami nem tetszik, hogy túl színes, szinte szivárvány – így elterelődik a figyelem a lényegről. A színek számát csökkenteni fogom; csak a három nagy országot és a „más országok”-at fogom külön színezni, a kicsiknek azonos színt adok. Így a három nagy lesz kihangsúlyozva, a kicsik majdnem teljesen egyformák, ezeket elég, ha a szegély elválasztja.

A másik probléma, hogy az adat feliratok nincsenek szépen pozícionálva, a kis országoknál pedig össze is folynak kissé. A %-okat be fogom tenni a körcikkekbe, az ország neveket hagyom a körön kívül.

Így néz ki a végeredmény:

Rondagrafikon4_2

És akkor jöjjön a technikai magyarázat!
Hogyan készült ez a grafikon – lépésről lépésre. (Kombinált diagramot fogunk készíteni – ezt a részt azoknak ajánlom, akik nem teljesen kezdők grafikonok készítésében. Bár nem lesz bonyolult, de aki még nem használt grafikont, annak sok lehet ez így egyszerre.)

A trükk abban rejlik, hogy a %-os adatfeliratok valójában nem a kördiagramhoz, hanem egy vele összekombinált perec (doughnut) diagramhoz tartoznak. Ezzel fogjuk kezdeni a grafikon elkészítését.

  1. Jelöld ki az adat táblát és szúrj be egy perec diagramot (a Beszúrás / Diagramok / Egyéb alatt találod). Ilyesmit kell látnod:

Rondagrafikon4_3

  1. Töröld ki a jelmagyarázatot és adj a grafikonhoz adat feliratokat (jobb klikk, majd Adatfeliratok hozzáadása, vagy a Diagram eszközök alatt Elrendezés / Adatfeliratok / Megjelenítés). Az adatfeliratok az értéket mutassák.

Rondagrafikon4_4

És most jön a trükk. Még egyszer hozzáadjuk a grafikonhoz ugyanezeket az adatokat, de most kördiagramként.

  1. A Diagrameszközök menüben a Tervezés alatt találsz egy Adatok kijelölése menüpontot (vagy ahogy én, az egérhez nőtt kezemmel szoktam: jobb klikk a diagramon, majd Adatok kijelölése)

 Rondagrafikon4_5

 

  1. A Hozzáadás gombbal tudod felvenni az új adatsort. Adj neki valami nevet, hogy be lehessen azonosítani (nálam: kör) és jelöld ki az értékeket.

Rondagrafikon4_6

 Így kell kinéznie – a külső körív az új adatsor:

Rondagrafikon4_7

  1. És most megváltoztatjuk ennek a kör nevű adatsornak a diagram típusát. Lehet egérrel: jobb klikk a most hozzáadott, külső gyűrűn, majd Sorozat-diagramtípus módosítása. Vagy menüből: először jelöld ki a külső gyűrűt (az Elrendezés alatt tudod kiválasztani a bal oldalon) majd a Tervezés alatt kattints a Más diagramtípus-ra. A típus választó ablakban keresd meg a kör diagramot, és válaszd ki. Így kell kinéznie:

Rondagrafikon4_8

  1. Már csak egy lépés van hátra: a kör diagramhoz kell hozzáadni adat feliratokat. Jelöld ki a kör diagramot (ha a diagram közepén kattintasz, akkor kijelölődik, vagy az Elrendezés alatt is ki tudod választani) A feliratokat ugyanúgy tudod hozzáadni, mint a perechez: Elrendezés / Adat feliratok / Értéknél kívül. Alapból az értékeket fogja hozzáadni, ezt meg kell változtatnod az Adatfeliratok formázása alatt: vedd ki az értéket és pöcköld be a Kategória nevét. Ilyen lett:

Rondagrafikon4_9

A lényeg kész is van, innentől már csak egy kis formázgatás van hátra. Kicsit kisebbre kell venni magát a diagramot, átszínezni az adat pontokat, beállítani a szegélyt vékony fehérre. Arra figyelj, hogy mindig a kördiagramot formázd, ez legyen kijelölve. (Ha látszik a perec diagram, állítsd be, hogy ne legyen kitöltése, se szegélye.)

Rondagrafikon4_10

Sajnos a további színezést csak egyedileg, adat pontonként (körcikkenként) egyesével lehet megcsinálni.

Ha a %-okat szeretnék kijjebb vagy beljebb mozgatni, akkor a perec diagram belsejének méretét kell kisebbre vagy nagyobbra állítani (megtalálod az Adatsor formázása / Adatsor beállításai alatt).

Remélem, érthető volt a leírás, és hasznosnak találjátok ezt a fajta feliratozást. Ha bármi kérdés felmerül, nagyon szívesen válaszolunk.

febr 08

Ronda grafikon 3: Egymást részben fedő sávok

Ma találtam egy cikket, amiben kétféle grafikon is szerepel: egy sáv diagram és egy kördiagram. Mindkettőn sokat lehet javítani, mind forma, mind színezés tekintetében. Kezdjük a sáv diagrammal – a kördiagramról a következő cikkben lesz majd szó.

Íme, így néz ki a grafikon a HVG cikkben:

rondagrafikon3_1

Társadalmi csoportok, akik nagyobb arányban akarnak külföldre menni
Fotó: NKI Gödri Irén

Sajnos a cikkből nem derül ki egyértelműen, hogy a „Komolyan tervezők” a „Tervezők” részhalmazát jelentik-e, de valószínűsíthető, hogy igen. A diagram típus (sáv, angolban bar) jól használható, ha hosszabb szöveget kell megjeleníteni az adatok mellett. Amivel itt probléma van, az a színválasztás és a tengelyek, rácsvonalak formázása. A piros és a sötétkék bántja a szemet, a fekete rácsvonalak és szegélyek túl erősek, kicsit börtön-szerű hatást adnak.

Az én alap verzióm így néz ki:

Rondagrafikon3_2

A grafikon lényegében ugyanaz, csak formáztam egy kicsit:

  • Leszedtem a jelmagyarázatról a keretet (jobb klikk a jelmagyarázaton, majd: Jelmagyarázat formázása / Szegélyszín / Nincs vonal).
  • A sávokat átszíneztem és a szegélyét is „Nincs vonal”-ra állítottam (jobb klikk a sávokon, majd: Adatsorok formázása / Kitöltés, Szegélyszín: nincs vonal).
  • Mindkét tengelyről levettem a kis „tüskéket” (ez a fő lépték: jobb klikk a tengelyen, majd: Tengely formázása / Tengely beállításai: a Fő léptéknél válaszd, hogy nincs).
  • A vízszintes tengelynél a vonalat is eltüntettem (ugyanebben a menüben: Vonal színe / Nincs vonal)
  • A rácsvonalakat halvány szürke színűre állítottam, hogy kevésbé legyen feltűnő.
  • Minden értéket önálló feliratként, nagybetűvel kezdve írtam. A betűméretet is megnöveltem az olvashatóság érdekében.

Látható, hogy ez a verzió geometriailag megegyezik az eredetivel, mégis jobban áttekinthető.

Tovább javított változat:

Rondagrafikon3_3

Két fontos módosítás történt:

  • Az első fontos változás, hogy a tengely maximumot 100%-ra állítottam, mert így jobban látható, hogy a teljes csoportnak mekkora része tervez külföldre menni.
  • Másodsorban pedig, ha figyelembe vesszük, hogy a Tervezőknek részhalmaza a Komolyan tervezők, akkor érdemes ezt kihangsúlyozni a grafikonon is, ezért a sávokat átfedőre állítottam. (Jobb klikk a sávokon, majd Adatsorok formázása / Adatsor beállításai / Átfedő: itt állíts be kb. 75%-ot.)
    Figyelem! Attól függően, hogy milyen sorrendben vetted fel az adatokat a grafikonra, előfordulhat, hogy a Tervezők adatsora eltakarja a Komolyan tervezőket. Ezt egyszerűen átállíthatod: módosítani kell az adatsorok sorrendjét. (Jobb klikk a diagramon, majd: Adatok kijelölése: a kis nyilakkal módosítsd a sorrendet.) A listában legalulra kerülő adatsor lesz legfölül (az fenti grafikonon a bordó), tehát ezt nem fogja kitakarni egyetlen más adatsor sem.

Rondagrafikon3_4

 A cikkben szereplő kördiagramról a következő cikkben olvashattok.

 

febr 02

Mini grafikon a cellában 1.

Ebben a cikkben egy minden Excel verzióban működő megoldást mutatok be, amivel adatok nagyságát lehet érzékeltetni. A módszer a SOKSZOR formulára épül, ami a 2003-as Excelben is elérhető. A 2007-es verzióban már feltételes formázással is lehet hasonló grafikonokat készíteni – erről a következő cikkben lesz szó.

Anyukám kért meg, hogy segítsek egy kérdőív feldolgozásában. Szerencsére az adatok már be voltak gépelve Excelbe és a szükséges %-okat is kiszámolták, nekem csak át kellett másolni kérdésenként a kis táblázatokat Word-be.

Így nézett ki eredetileg:

Kérdöiv1

Gondoltam, fel kellene dobni valami grafikonnal, hogy ránézésre könnyebben feldolgozható legyen a sok kérdésre adott válasz.

Ilyen lett:

Kérdöiv2

Több dolgot formáztam rajta, de most fókuszáljunk a BB oszlopban levő kis grafikon-szerűségre. Nagyon-nagyon egyszerű ezt előállítani egy képlet és egy kis formázás segítségével. Valójában ezek a kék vonalkák kivastagított „l” (kis L) betűk, annyiszor ismételve (sokszorosítva :-)), ahány fő az adott választ bejelölte.

S hogy hogyan lehet sokszorosítani a betűket? Nagyon egyszerű: a SOKSZOR (angolul: REPT) formulával. Például a fenti ábra BB7 cellájában ez a formula áll:

=SOKSZOR( „l” ; AZ7 )

=REPT( „l” , AZ7 )

Ez a formula annyiszor ismétli meg az első paramétert (jelen esetben a l betűt) amennyi a második paraméter értéke (jelen esetben ez az AZ7 cella értéke, vagyis a válaszolók száma: 10). Figyelj rá, hogy az első paramétert, vagyis a betűt idézőjelek közé kell tenni!

Ha beírtad a képletet a megfelelő cellába, már csak egy kis formázás van vissza. Válassz olyan betűtípust, ami egyenes vonalként ábrázolja a „l” betűt (például Arial, Arial Black) játszhatsz a vastagítással, színezéssel, betűmérettel.

Lehet próbálkozni „l” helyett „o” betűvel, vagy ponttal is.

Bátrabbak kipróbálhatják a Wingdings betűtípust is…

És aki még ennél is többet akar, keresgélhet szimbólumokat. A Beszúrás menü jobb oldali végén találod az omega ikonnal jelölt Szimbólum menüpontot (angolban: Insert / Symbol). Praktikus először egy üres cellába beszúrni a szimbólumot, majd ha tetszik, a szerkesztőlécről másolással beszúrni a képletbe.

Kérdöiv3

Picit furcsa ennek az ablaknak a működkése: a beszúrás gomb megnyomásával a választott szimbólum bekerül abba a cellába, ahol állsz, de az ablak nem csukódik be. Ha csak egy szimbólumot szerettél volna beszúrni, csukd be az ablakot (Mégse vagy piros X), a szimbólumot meg fogod találni az aktív cellában.

Remélem, hasznosnak találjátok ezt az egyszerű „adat vizualizációs” megoldást. Persze, azért óvatosan bánjatok vele: az egyszerűség legyen a fő szempont: „l” betű, négyzet, pont… a girlandok, levelek, smiley-k nem biztos, hogy mindig jól mutatnak sokszor egymás mellé rakva. 🙂

 

css.php