jún 25

Kombinált diagram: adatsor diagram típusának megváltoztatása

Az Excelben nagyon sok jól használható alap diagram típus van, amiket kombinálhatunk is egymással. Leggyakrabban a vonal és oszlop diagramot szoktuk együtt alkalmazni, általában akkor, ha érték és % adatokat szeretnénk együtt mutatni. Ilyenkor szépen elkülönül egymástól az érték oszlopokkal ábrázolva, és a % vonalként.

Például igy:

ComboChart_1

Az Excel 2013 nagyon jól támogatja efféle kombinált diagramok létrehozását – a fenti képen láthatót például 3-4 kattintással meg lehet csinálni. Sajnos a 2010-es verzióban ez kicsit küzdelmesebb, nem annyira kézenfekvő, de azért ott sem túl bonyolult.

Ebben a cikkben megmutatom, hogyan lehet e két verzióban kombinált diagramot készíteni, és a már meglevő diagramon az adatsorok diagram típusát megváltoztatni. Kezdjük a 2013-assal, abból szerintem jobban megérthető az elv, utána talán könnyebb a 2010-es megoldást megjegyezni.

Egyszerű kombinált diagram létrehozása 2013-as Excelben

…mintha egy hagyományos diagramot készítenél. Jelöld ki az adattáblát, majd a Beszúrás (Insert) menü alatt bármely diagram típus menüjét kinyitva válaszd a Minden…diagramtípust (More…charts)

ComboChart_2

Az a fontos, hogy ide, a Diagram beszúrás (Insert Chart) párbeszédpanelre juss el:

ComboChart_3

A Kombinált (Combo) típusra kattintva már látod is a megjelenő diagram előnézet alatt, hogy minden egyes adatsorhoz, amiből a diagram felépül, külön be tudod állítani a diagram típust. A fenti esetben automatikusan a változás (piros) adatsor vonal típust kapott, a forgalom pedig oszlopot. Ezeket egyszerűen változtathatod, természetesen a kombinálhatóság ésszerű határain belül.

Ugyanitt tudod másodlagos tengelyen ábrázolni az adatsort – csak jelöld be a jobb oldali kis négyzetben.

A lényeg tehát: a “diagramtípus” – nevével ellentétben – az egyes adatsorokhoz tartozik (szaknyelven: a diagramtípus az adatsor tulajdonsága), tehát minden adatsornak külön-külön állíthatjuk a diagramtípusát.

Meglevő adatsor diagram típusának megváltoztatása 2013-as Excelben

Ebben az esetben is a fenti képen látható párbeszédablak lesz segítségedre. Jelöld ki a diagramot, és a Diagram eszközök (Chart tools) menüben kattints a Diagramtípus megváltoztatása (Change Chart Type) gombra.

ComboChart_4

Válaszd a kombinált típust, és állítsd be az adatsorokhoz a megfelelő típusokat!

Egyszerű kombinált diagram létrehozása 2010-es Excelben

Sajnos ebben a verzióban nincs kombinált diagram a diagram beszúrás párbeszédablakon…

ComboChart_9

…ezért egy “hagyományos” diagramot kell először készítenünk. Jelöld ki az adattáblát, és a Beszúrás (Insert) menü segítségével hozz létre egy oszlop diagramot:

ComboChart_5

(Hát… az új Excelben azért ez alapból jobban néz ki…)

A “változás előző évhez” adatsort kellene tehát átváltoztatni vonal típusúra. Ehhez azt kell tudni, hogy a diagram típus az adatsorhoz tartozik, nem az egész diagramhoz. Tehát először is ki kell jelölni az adatsort. Ezt legegyszerűbben a menüből tudod megtenni: A Diagram eszközök menü alatt az Elrendezés (vagy a Formátum) menüt választva találsz egy legördülő listát a bal oldalon:

ComboChart_7

Itt kattints az adatsor nevére – látni fogod, hogy a diagramon ki lesz jelölve a kis bordó adatsor.

Most lehet megnyitni a Tervezés alatt a Más diagramtípus menüt .

ComboChart_8

Megnyílt a Diagram beszúrás panel, de most (habár erre sajnos semmi nem utal a panelen) csak a kijelölt adatsor diagramtípusát változtatjuk.

Miután sikerült vonalra változtatnod az adatsort, még pluszban be kell menned az adatsor formázása menübe is, mert csak ott tudod másodlagos tengelyhez hozzárendelni…  Hány kattintás volt ez eddig?

Picit könnyíthet a helyzeten, ha jobb egér gombbal kattintasz az adatsoron (magán a diagramon) és a menüből kiválasztod a Sorozat-diagramtípus módosítása pontot. Az adatsor formázását is megtalálod itt. Ha az adatsor annyira picike, hogy nem tudsz rákattintani (pl. épp azért, mert nem jó tengelyen van) akkor a fent leírt módon biztos, hogy mindig ki tudod jelölni.

Meglevő adatsor diagram típusának megváltoztatása 2010-es Excelben

Tulajdonképpen pont ezt csináltuk a diagram létrehozásánál is… Csak úgy tudod egy adatsor diagram típusát változtatni, ha kijelölöd az adatsort, és utána kattintasz a Más diagramtípusra.

Ez a kijelölés történhet a diagram létrehozásnál leírt módon: az Elrendezés vagy a Formátum menü alatt a bal oldali legördülő listában, vagy jobb kattintással – így rögtön a Sorozat-diagramtípus módosítását tudod választani.

 


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


jan 10

Box plot diagram statisztikai adatokhoz

boxplot_1Egy kedves kolléganőm statisztikát tanul, s az egyik házi feladatában box plot diagramot kellett készítenie. Az oktató azt mondta nekik, hogy “rajzolják meg vonalakkal Excelben”. Hát hogy is mondjam… rajzolni Paintben szoktunk, nem?

Szóval gondoltam, nem hagyhatom én ezt annyiban, megcsinálom Excelben DIAGRAMKÉNT.

Nem nehéz, nem kell megijedni, csak kattintgatni kell párszor a megfelelő menükben. Természetesen használhatjátok a sablon fájlt is, ahol csak az adatokat kell átírni. :-)

Mi ez a Box Plot?

Ez egy statisztikai elemzésekhez használt diagram, amin a minta mutatószámait jelenítjük meg: a “doboz” alsó éle az alsó kvartilist, felső éle a felső kvartilist mutatja, az alsó vonal végpontja a mintában található legkisebb értéket, a minimumot, a felső vonal végpontja pedig a maximumot jelzi. Az ábrán kereszttel a mediánt jelöltem.

Nem tűnik bonyolultnak… egy tőzsdei grafikont fogunk alkalmazni, ehhez adjuk majd hozzá extra adatsorként a mediánt.

Az adattábla

Van egy különleges diagram típus az Excelben, amit átlag felhasználóként kevéssé szoktunk használni. Ez egy tőzsdei elemzéseket támogató diagram, ahol az időszaki nyitó, záró, maximum és minimum árat lehet megjeleníteni. Ezekből az adatokból pontosan olyan box plot-ot rajzol az Excel, ami a képen is látható – kivéve a mediánt, amit a kereszt jelöl. Ezt kell majd trükkösen beletenni…

Tehát a kiinduló adatok:

boxplot_2

Nos két trükk: az első az adatok sorrendje. A diagram Nyitó – Maximim – Minimum – Záró sorrendben várja az adatot, ennek megfelelően nekünk Felső kvartilis – Maximum – Minimum – Alsó kvartilis sorrendben kell a táblázatot összerakni.

A másik: legalább 5 adatsort ki kell jelölni – csak így hajlandó az Excel megcsinálni a grafikont. Ezt majd ki fogjuk törölni, mert egy adatsort is lehet ábrázolni, de valamiért az elkészítéshez 5 kell… (Hogy miért jó az, ha az 5-ből 4 üres, azt nem tudom, de ha így működik, hát legyen így. :-) )

Arra is figyelj, hogy a mediánt még nem jelöltem ki!

Ha megvan a megfelelő kijelölés, jöhet…

A diagram

2013-as Excelben:

A Beszúrás (Insert) menüben a Diagramok (Charts) alatt nyisd le a Pont (Scatter) diagramokat, és a További Pontdiagramok (More Scatter Charts…) válaszd.

boxplot_3

A megnyíló ablakban az Árfolyam (Stock) alatt kell megkeresned a Nyit-Max-Min-Zár (Open-High-Low-Close) típust.

boxplot_4

2010-es Excelben:

A Diagramok között az Egyéb alatt találod meg a Nyit-Max-Min-Zár típust, vagy ha a Minden diagram típus menüpontra kattintasz, a fenti képernyőn is kiválaszthatod.

boxplot_1

Ilyen lett… még nem az igazi, de jó úton járunk. Az 5 soros adattáblát lecsökkentjük egyre – csak “told fel” a kijelölést:

boxplot_5

Egy kis formázás

Töröljük a felesleget: nincs szükség a diagram címre, a jelmagyarázatra (Series 1….) és a vízszintes tengely feliratra (1). Ezeket kattintás után egyszerűen töröld.

Ezután a “dobozka” színét változtatjuk meg. A “dobozka” valójában egy Alsó-felső sáv (Up-Down bar) elem… ha esetleg a Diagrameszközök (Chart tools) menüből akarod kiválasztani, akkor Alsó sáv vagy Felső sáv (Down bar / Up bar) néven találod meg. De jobb egér kattintással is egyszerűen előjön a menüben a formázás opció:

boxplot_6

A kitöltő színt állítsd át valami világosabbra.

Medián hozzáadása – új adatsor

Már csak a medián hiányzik. Ezt egy új adatsorként fogjuk hozzáadni a diagramhoz. A diagramon jobb klikk után eléred az Adatok kijelölése (Select data…) menüt. Itt a Hozzáadással (Add) tudsz új adatsort felvenni.

boxplot_7

A névhez a fejlécet, az értékhez a medián értéket add meg:

boxplot_8

S még mielőtt kilépnél az ablakból, a medián adatsort mozgasd a minimum és a maximum közé:

boxplot_9

Van már mediánunk, csak még nem látszik! A Diagrameszközök (Chart tools) menüben a Format alatt a bal oldali legördülő listában ki tudod választani a medián adatsort – ha kiválasztottad, kattints az alatta található Kijelölés formázása ponton (Format Selection).

(2010-es Excelben a Diagrameszközök alatt az Elrendezésen belül találod a bal oldalon a legördülő listát, alatta pedig a Kijelölés formázását.)

boxplot_10

Állíts be egy alkalmas jelölőt (Marker) az adatsorhoz – én a keresztet választottam, s egy kicsit a méretét is megnöveltem.

boxplot_11

Kész is van!

Remélem, sikerült elkészíteni – ha mégsem, itt a sablon fájl!

Ja, még valami. Természetesen több box plot-ot is készíthetsz egymás mellé – több soros adattáblából is fel tudod építeni ezt a diagramot, és a medián oszlopot is ugyanúgy hozzá tudod adni. Az eredeti tőzsdei grafikon is hosszabb időtartam adatainak ábrázolására lett kitalálva – talán ezért is van, hogy minimum 5 sornyi adattal működik.

 


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


jan 04

Diagram varázslat 3: Szövegdoboz hozzáadása a diagramhoz

chart_w_box_2

Szövegdobozt rakni egy diagramra nagyon egyszerű. Csak oda kell rajzolni. A problémát az szokta jelenteni, hogy ott is maradjon – vagyis tényleg a diagramra kerüljön, vele együtt mozogjon.

Az ábrán ez nem sikerült: az alcím nem mozgott együtt a diagrammal… :-(

Hol a hiba? Ott, hogy valójában hova szúrtad be a szövegdobozt. Nem elég csak úgy odarajzolni a diagramra a szövegdobozt – ténylegesen kell rajzolni. Nem, nem a szavakon lovagolok, lássuk, hogy kell ezt csinálni.

A lényeg, hogy a KIJELÖLT diagramra rajzold a szövegdobozt.

Tehát:

  1. Kattints a diagramon, hogy ki legyen jelölve.
  2. A Beszúrás (Insert) menüben a jobb oldalon találod a Szövegdobozt (Text Box)
  3. Rajzold a szövegdobozt teljes egészében a diagramra (ne lógjon ki!)

Ha így csinálod, a szövegdoboz együtt fog mozogni a diagrammal, mert annak része lett. Ez abból is látszik, hogy nem tudod “lemozgatni” a diagramról.

Tehát ami nagyon fontos: legyen kijelölve a diagram!!

Olvasd el a sorozat korábbi részeit is!

Dinamikus grafikon feliratok készítése

Kép használata a jelölőben

 


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


dec 30

Évösszegzés – ilyen volt 2016

20162017

Nem írtam még évösszegzést a blogon, de talán eljött ennek is az ideje, hiszen ez volt az eddigi legaktívabb évünk!

Már több, mint három éve, hogy elkezdtük az Adatkertészet blogot Gáborral… remélem, mostanra kinőttük a kezdeti gyermekbetegségeket, kicsit tisztult a kép, hogy mit akarunk, milyen stílusban írunk…

Örülök, hogy gyarapszik az olvasók és a Facebook csoporttagok száma. Remélem, hogy ez a sok idei bejegyzésnek is köszönhető! Idén összesen 24 bejegyzést írtunk – ez a huszonötödik. :-)

Mozgalmas év volt ez. Év elején sokat írtam, tartottam tréningeket nagyvállalatoknál, amikről nagyon pozitív visszajelzést kaptam. Az egyik ilyen (4 napos!) egyedi fejlesztői konzultáció és tréning kapcsán kristályosodott ki, hogy mennyire fontos az úgynevezett “on the job”, személyre szabott fejlesztés. Fontos, hogy itt nem csak az egyén fejlesztéséről volt szó – sokkal inkább egyedi Excel alkalmazás-fejlesztéssel egybekötött tréningről. Én nagyon jól éreztem magam ezen a különleges konzultáción, ebben a kettős szerepben, és hatalmas tudásmennyiséget sikerült így átadni és dokumentálni.

Aztán nyáron beindult egy komoly munkahelyi projekt, ami eléggé lefoglalt, sajnos kicsit háttérbe szorult a blog.

A projekt végeztével elgondolkodtam: merre tovább, mi legyen az Adatkertészettel? Valahogy tovább kellene lépni, a blog mellett többet megmutatni magunkból. S ekkor jött a Meetup: pont jókor pottyant az ölembe a lehetőség, hogy felkarolhattam az Excel tippek és trükkök csoportot. Nagy lelkesedéssel szerveztük az első találkozót, és rengeteg pozitív visszajelzést kaptunk!

Most így az év utolsó napján – sose volt még ilyen – 3 megírt blogbejegyzés várakozik, hogy publikáljam! (Nem akarlak túlterhelni titeket, ezért kicsit visszafogom a lelkesedést, amikor kész van egy-egy írás, és lassan adagolom a cikkeket. :-) )

Sok terv van a jövő évre, de én nem szeretek tervekről beszélni, csak arról, amiben biztos vagyok: a Meetup találkozókat szervezni fogjuk, a következő alkalom témája már kezd összeállni a fejemben.

Lesznek cikkek, kicsit több figyelmet kap a Facebook csoport, és már készül a Facebookon az Adatkertészet oldal is! Mindenképpen szeretnénk továbbvinni az egyedi fejlesztés vonalat is… na, de ez már tényleg a még-nem-beszélünk-róla terv! :-)

Mindenkinek köszönjük az egész éves figyelmet, a kérdéseket, hozzászólásokat, téma javaslatokat!

Reméljük, hogy jövőre még több Excel használónak tudunk hasznos tudást adni – akár tréningen, akár csak egy apró tippel is! :-)

Utóirat: Vajon kitalálja-e valaki, hogyan készült a fenti kép?

 


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


dec 29

Sortörés és automatikus sormagasság egyesített cellákon

A Facebook segítő csoportunkban érkezett egy kérdés sortörés beállítási problémáról.

A kérdező hiába állította be a sortörést (wrap text) a cellán, hiába kattintott a sor elválasztón duplán az automatikus sor magasság beállításához, nem jelent meg több sorban a szöveg.

sortores_1

Mi lehet a probléma? Az ábrán látjátok, hogy a kérdéses szöveg egyesített cellába* (merged cell) van írva! Sajnos egyesített cellákon nem jól jelenik meg a több sorba tördelés, akkor sem, ha cellán belüli sortörést (Alt+Enter) alkalmazol. :-( Az automatikus sormagasság ilyenkor nem nagyobbítja meg a cellát, csak kézzel tudod megnövelni a sor magasságát.

Mit tehetünk?

Áthidaló megoldáshoz kell nyúlnunk. Egy segéd oszlopra lesz szükség, aminek a szélességét ugyanolyanra kell beállítani, mint az egyesített cellák együttes szélessége. Be kell hivatkozni képlettel az egyesített cella szövegét az adott sorból, és beállítani a sortörést. Így ezen a “normális”, nem egyesített cellán működik a tördelés, a sor magassága megfelelő lesz, így az egyesített cella is “jól fog kinézni”. (A sortördeléshez az egyesített cella minden, betűket érintő formázását is be kell állítani a segéd cellán: betűtípus, méret, félkövér, stb!)

sortores_2

Tehát, ha nem tudod elkerülni a cella egyesítés alkalmazását, de tördelésre és az automatikus sormagasságra is szükséged van, ezt a kis trükköt tudod alkalmazni.

 

* Megjegyzés: Az egyesített cellák sokszor megnehezítik az adatok másolását, beillesztését, nehezen követhetővé teszik a hivatkozásokat és a tartomány elnevezéseket. Én amennyire csak lehet, kerülöm a cella egyesítést, és titeket is arra biztatlak, hogy csak igazán szükséges esetben használjátok ezt a lehetőséget.

Kapcsolódó témák:

Sormagasság beállítása formulával

 


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


dec 29

Cellaegyesítés (merge) helyett: kijelölés közepére igazítás

stop_merge_1

Nagyon sokszor látom, hogy több cellát egyesítve, a kijelölés közepére kell írni szöveget – például táblázatok fejlécében. Ekkor általában cella egyesítés (merge) funkciót szokás használni – aminek sajnos az esetek többségében több a hátránya, mint az előnye.

Általában másoláskor szokott ez problémát okozni, de a hivatkozások és elnevezett tartományok esetében sem mindig egyértelmű, hogy mi a helyzet az egyesített cellákkal.

Van azonban egy kevéssé ismert alternatív megoldást, amivel az egyesítést sokszor el lehet kerülni – ez pedig a kijelölés közepére igazítás:

Picit el van rejtve – a Kezdőlapon (Home) az Igazítás (Alignment) csoport jobb alsó sarkában levő kis gombocskára kell kattintani.

stop_merge_3

A megnyíló kis ablakban az Igazítás fül alatt a Vízszintes igazítást kell kinyitni, itt megtalálod a Kijelölés közepére (Center across selection) lehetőséget:

stop_merge_4

Az eredmény látványra pont ugyanolyan, mint az egyesítés, de a cellák a középre írt szöveg alatt megmaradnak különállónak.


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


dec 15

Diagram varázslat 2: kép használata a jelölőben

pic_markerApró kis trükk, amivel kezdők is érdekesebbé, látványosabbá tehetik a grafikonjaikat!

A jelölőbe (marker) bármilyen képet be tudsz tenni! – Ahogy a képen is látod, én egy zöld csillagot raktam be az adatpontok ábrázolásához.

Két módja is van a kép berakásának: alkalmazhatsz már meglevő, lementett képet, vagy “bemásolhatod” a vágólap tartalmát.

Lássuk, hogyan!

Készítsd el a diagramodat, (én vonal diagramot használok a példában) és válaszd ki az adatsor formázását. (Jobb klikk a “vonalon”, majd adatsor formázása (Format data series))

A menüben sokféle beállítási, formázási lehetőség érhető el. Minket most a “festékesvödör” (Kitöltés és vonal) érdekel, azon belül is a Jelölő (Marker).

(2010-es Excelben kicsit más ez a menü, ott külön látod a bal oldalon a jelölőre vonatkozó beállításokat.)

pic_marker_1

A jelölő alatt három beállítási lehetőség-csoport van:

  1. A Jelölő beállítások alatt kiválaszthatod, hogy milyen jelölőt akarsz (nincs nagy választék, kör, négyzet, iksz, ilyesmi… vagy egyedi kép – erről majd később *).

2013:

pic_marker_2a

2010:

pic_marker_2b

2. A kitöltésnél határozhatod meg, hogy milyen színe legyen a választott jelölőnek, vagy megadhatod, hogy milyen képpel töltse azt ki az Excel. (Ez kell majd nekünk!)

3. A szegélynél azt tudod megadni, hogy a választott jelölő határvonala milyen legyen. (A 2010-es Excelben ez ketté van választva: Jelölővonal színe és Jelölővonal stílusa.)

Például: választottál egy kört a beállítások alatt, megadtad, hogy zöld legyen a kitöltése, és vékony fekete vonallal legyen körberajzolva a határvonala. Nincs benne nagy tudomány, de tény, hogy sokféle dologgal lehet kísérletezni.

Ami viszont nekünk most érdekes, az a képpel való kitöltés – úgyhogy térjünk vissza a 2. pontra!

2013:

pic_marker_3a

2010:

pic_marker_3b

Tehát a Kitöltés alatt kattints a Kitöltés képpel vagy anyagmintával pontra (Picture or texture fill). Használhatsz egy már meglevő fájlt, vagy a vágólapról is beillesztheted a képet! Én a fenti példában egyszerűen rajzoltam egy zöld csillagot a munkalapra, nyomtam rá egy másolást (Ctrl+C) s ebben a menüpontban már használhatom is, mint vágólap tartalmat – anélkül, hogy elmenteném a képet!

Amit fontos tudni:

Mint a nevéből is következik, a választott jelölőt fogja az Excel a képpel KITÖLTENI – ebből két dolog következik, amit a jelölő beállításokban határozol meg:

  • a jelölő alakja, amit választottál, megfelel-e a képnek, amivel ki akarod tölteni? A csillaghoz például kiváló a kör alakú jelölő, de a háromszög már nem annyira.
  • a jelölő mérete – ezt általában nagyobbra kell állítani a beállításoknál, hogy érvényesüljön a bele kerülő kép.

* Térjünk vissza egy mondat erejéig a jelölő beállításokra (1. pont). A Beépített alatt Itt is találsz kép lehetőséget, ha a típusnál az utolsó elemet választod (ez egy kis kép ikonka). Az a fontos különbség, hogy itt megadhatsz egy már lementett képet – ami abban a méretben lesz jelölőként használva, ahogy le van mentve! Lehet ezzel is kísérletezni, a lehetőségek szinte végtelenek!


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


dec 13

Diagram varázslat 1: Dinamikus grafikon cím és feliratok

Dinamikus grafikon cím… azaz, a cím hozzá van kötve egy cellához a munkalapon. Így a grafikon címe mindig a cella értéke. Használhatsz képletet a cellában, ami összerakja a szükséges felirat szöveget – s ez rögtön látható a diagramon! Nagyon praktikus, ha a diagram adatok is valamilyen képletre épülnek, aminek valamely paraméterét egy cellában adod meg. Például egy kiválasztott áruház adatai vannak a kis táblázatban összerakva…

Nagyon egyszerű ezt megcsinálni, egy képen el is tudom magyarázni a lépéseket:

dynamic_title_1

Van tehát egy diagramod, aminek van egy fix címe. Kattints erre a címre, majd állj a szerkesztőlécre, s mintha képletet akarnál írni, egyenlőségjel után kattints a cellára, amiben a kívánt diagram címszöveg van.

Ha átírod a cella értékét, a diagram címe is változni fog ennek megfelelően.

Ezt a cella-hozzákötős trükköt bármilyen, a diagramra helyezett szövegdobozzal meg tudod csinálni. Például az alábbi képen a függőleges tengely mértékegységét kötöttem egy cellához, ahol meghatározom, hogy a táblázatban az adatok ezerben vagy millióban szerepelnek.

dynamic_title_2

Ennyi. :-)

S hogy hogyan került zöld csillag a pontok helyére? Erről szól a következő cikk!

 


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


dec 08

“Miért lassú az Excel?” Meetup beszámoló és letölthető vázlat

meetuppic← Az ott én vagyok… ellenfényben. :-)

Megvolt az első meetupunk!

Nagyon köszönjük minden résztvevőnek, hogy eljött, és kíváncsi volt az előadásra! Köszönjük a kérdéseket és a témához hozzáfűzött gyakorlati megjegyzéseket is!

Nagyon jól esett a sok pozitív visszajelzés – örülök, hogy hasznosnak találtátok az anyagot, az elméleti részt és a gyakorlati példákat is. Külön köszönjük azt is, hogy a javítandó dolgokra is felhívtátok a figyelmünket!

Mi nagyon jól éreztük magunkat, én észre se vettem, hogy milyen sokáig beszéltem, de sikerült mindent elmondani, amit szerettem volna. És egyáltalán nem fáradtam el! …de azért jól esett utána egy korsó meggyes sör! :-)

Miről volt szó?

  • Elmagyaráztam, mik a függési fák (dependency tree), s hogy miért fontos elemei ezek a kalkulációnak.
  • Beszéltünk volatile formulákról, amik gyakran okoznak kalkulációs problémákat.
  • Megmutattam, mik a kalkulációt kiváltó események – köztük néhány olyan eseménnyel, amiről nem gondolnánk, hogy bármi köze van a kalkulációhoz.
  • Megemlítettük a feltételes formázás és az érvényesítés specialitásait.
  • Beszéltünk a hivatkozásokról: mi lehet a kockázata, ha teljes oszlopra, sorra hivatkozol, s hogyan függ ez össze a used range-el.
  • Megismertük az FKERES keresési algoritmusait, s hogy hogyan lehet a gyorsabb algoritmus előnyeit kihasználni.
  • És végül, ezen elmélet alapján kalkuláció optimalizálási lehetőségeket soroltunk fel.

Aki kimaradt, lemaradt?

Nos, igen… felvétel nem készült, s ilyesmit a jövőben sem tervezünk… De egy kis kárpótlás azoknak, akik nem tudtak eljönni, valamit ismétlés-összefoglalás a résztvevőknek: innen letölthetitek a kibővített vázlatot, amiben megtaláljátok az olvasnivalók linkjét és a javasolt formula megoldások példáit. Persze sokból cikk is született már itt a blogon. :-)

 

nov 30

Gyorsítsd fel a Kimutatás (Pivot tábla) építést!

Nagy, sok értékoszlopot tartalmazó adattáblára épített kimutatás összerakásakor, változtatásakor sokszor tapasztaljuk, hogy az Excelnek szüksége van egy kis időre ahhoz, hogy az adott mező berakása után módosítsa a kimutatást. Ha egymás után sok mezőt, értékoszlopot raksz a kimutatásba, akkor minden egyes mező berakás után frissítés történik… mi meg nézzük a homokórát.

Ilyenkor lehet hasznos, ha megkérjük az Excelt, hogy kicsit “tegye magát takarékra”, és majd csak akkor rendezze át a kimutatásunkat, ha már minden mezőt a helyére raktunk. Csodálatos módon erre gondoltak is a Microsoftnál, és a kimutatás mezőlista alján elhelyeztek egy checkboxot:

Elrendezésfrissítés elhalasztása (angolul: Defer Layout Update)

Így néz ki angol Excel 2013-ban és magyar Excel 2010-ben:

pt_update_1elrendezesfrissites

Ha ezt bejelölöd, akkor nyugodtan pakolhatod a mezőket az oszlopokhoz, sorokhoz, értékekhez vagy a szűrőkhöz, a kimutatásod nem fog változni, amíg meg nem nyomod a Frissítés (UPDATE) gombot.

Fontos, hogy ha kész a kimutatás, szedd ki a pipát, mert a kimutatás funkciók (pl. szűrők) sem működnek, míg a frissítés elhalasztása be van kapcsolva!

Én a mindenapi fejlesztői gyakorlatban nagyon hasznosnak találom ezt a lehetőséget, különösen PowerPivot modellek esetében, ahol a kimutatás rendezgetése tényleg időigényes lehet.

 


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


nov 25

Meetup: Személyes találkozók! – örömmel jelentjük rovat

Hosszú várakozás után, több olvasónk érdeklődésére reagálva, s nem utolsó sorban egy véletlennek (bár tudjuk, hogy azok nincsenek… :-)) köszönhetően van egy Meetup csoportunk!

bck1

Mi ez a Meetup?

Egy közösségi oldal, ahol bármilyen témában találsz csoportokat. A csoportok fő tevékenysége a személyes találkozók szervezése. Én a Derrick és Harry blog olvasása során találtam rá az ő meetup csoportjukra, ami projekt menedzsmenttel foglalkozik. Kicsit bátortalanul mentem el az első találkozóra, persze jött Gábor is, aki ismeri “Derrickéket”, így “nem volt akkora para”, hogy 50-60 vadidegen IT-sal voltam egy nagy teremben. Előadást hallgattunk, beszélgettünk sok emberrel. (Volt, akit az Excel is érdekelt! :-))

Felbátorodtam, jelentkeztem több más csoportba, főleg BI és Data Science témakörben.

Aztán találtam egy olasz csoportot is, ahol heti találkozókat szerveznek, nagyon kedvesek, nagyon lelkesek – eljárni ezekre az összejövetelekre kifejezetten jó nyelvgyakorlási lehetőség!

No, és mostmár nekünk is van csoportunk! Egyenlőre havi egyszeri összejövetelt tervezünk, ahol egy kis előadás-tréning előtt / után lehet kérdezni, tapasztalatot cserélni, tippeket megosztani, vagy csak beszélgetni, ismerkedni “hasonszőrűekkel”.

Én tehát nagy lelkesedéssel bíztatok mindenkit, hogy nézzetek körül a Meetup oldalon, csatlakozzatok a mi csoportunkhoz, és keressetek más, számotokra érdekes közösségeket is!

A mi találkozóinkról a Meetup csoportban vagy a Facebook csoportban értesülhettek, vagy a hírlevélben kaphattok róla információt.

Várunk mindenkit szeretettel!

 

nov 14

Szöveg szétvágása adott karakter mentén (splitting)

split_2Mutatok nektek egy érdekes formulát, amivel szöveget adott karakter mentén lehet szétdarabolni cellákba – lehet vesszővel, pontosvesszővel elválasztott szöveg, de akár egy mondatot is tudsz szavakra “darabolni”.

Nem lesz nehéz, és minta fájl is van a cikk végén. 😉

 

 

Kezdjük a példával! Az alábbi képen a B oszlopban levő szöveget daraboltam szét a D-G oszlopokba.

split_1

A példában vessző az elválasztó karakter, minden vesszővel elválasztott szövegrész külön cellába került – de lehet ez bármi más is. Nálunk a munkában például tervezési kódokat használunk, amik négy részből állnak, és a részeket aláhúzás választja el. Ilyesmi:

terület_kategória_márka_értékesítési csatorna

Amikor ad-hoc beszámolót, kimutatást készítünk, akkor az alázúzás mentén szétdaraboljuk ezeket a kódokat, hogy könnyen szűrhető, összesíthető kimutatást lehessen rá csinálni: külön oszlopba teszem a területet, a kategóriát, a márkát és a csatornát.

No, de hogyan tudjuk ezt megoldani?

Egy régi trükköt mutatok erre, amit Roberto-tól tanultam. :-)

Egy segéd sorra lesz szükség karakter-pozíció számokkal – ezt szürkével jelöltem.

Így néz ki a formula a D4 cellában:

= KIMETSZ( KÖZÉP( HELYETTE( $B4 ; “,” ; SOKSZOR(” “;100) ) ; D$2 ; 100) )

= TRIM( MID( SUBSTITUTE( $B4 ; “,” ; REPT(” “;100) ) ; D$2 ; 100) )

Ezt másoljuk lefelé és jobbra.

 

Mi történik itt?

A HELYETTEsítjük a B4 cella szövegében a vesszőket 100 darab szóközzel. Ezáltal egy hosszú szöveget kapunk, amiben jó messze vannak egymástól a szövegrészek (aláhúztam, hogy jobban lássátok, szóközök vannak közte):

Személygépkocsi                                                                                                    BP                                                                                                    egy                                                                                                    322

Ebből a szövegből kivágunk a D2-ben található karakter pozíciótól kezdve 100 karaktert, majd megtisztítjuk a felesleges szóközöktől…. s így tovább, az E2 pozíció számával… csak másolni kell a formulát lefele és jobbra.

Olyan, mintha egy gumira fűznél gyöngyöket, aztán a gumit kinyújtanád és szétvagdosnád.

A számokra kell csak figyelni: legalább annyi szóköz kell, mint a leghosszabb szövegdarab hossza szorozva a szövegdarabok számával. (Szóval sok legyen. Akár 1000 :-)) A kivágott karakterek szám ugyanennyi. A pozíció számok pedig ennek többszörösei.

Itt a minta fájl a formulával – próbáljátok ki!


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


nov 08

Sorszámozás rejtett sorok esetén – ÖSSZESÍT formula

Egy blog olvasó keresett meg egy rövid kérdéssel: sorszámozást szeretne a listája mellé. A trükk az, hogy akkor is 1-től kezdődő, egyesével haladó számokat szeretne, ha el vannak rejtve (szűrve) sorok. Másképp fogalmazva: csak a látható sorokat kell számozni.

Készítettem egy kis példát.

Tehát:

filternumber_1

Szűrés esetén ilyen eredményt várunk:

filternumber_2

Hogyan lehetne ezt elérni anélkül, hogy kézzel irkálnánk be a számokat? Csak egy alkalmas formulát kellene találni, aminek van olyan képessége, hogy a rejtett sorokat nem veszi figyelembe.

Nos, van ilyen, kettő is! A 2010-nél régebbi Excelben a RÉSZÖSSZEG (SUBTOTAL) formula ilyen, 2010 és újabb verziókban már az ÖSSZESÍT (AGGREGATE) is rendelkezésre áll. Ez utóbbit fogom bemutatni, de a feladat nagyon hasonlóan megoldható a RÉSZÖSSZEGgel is.

Pár szó az ÖSSZESÍT formuláról:

Praktikus, ha úgy gondolkodunk erről a formuláról, mint egy “burok-formula”. Ez a “burok” képes arra, hogy az általunk választott számítást bizonyos beállításokkal végezze el. Ezek a beállítások lehetnek többek között, hogy megkérjük a “burkot”, hogy a számítás során hagyja ki az elrejtett sorokat.

A buroknak meg kell mondani, hogy:

  • milyen számítást végezzen – számkóddal lehet kiválasztani a függvényt, amit a “burokba” teszünk. (Tudom, furcsa, de így működik a “burok”.)
  • mit hagyjon ki – ezt is egy szám kóddal tudod megadni
  • milyen tartományon végezze a számítást – a szokásos tartomány kijelölés, amit egyébként az első pontban választott függvényben beállítanál
  • ha a számításhoz további paraméter szükséges, akkor ezt itt kell megadni – erre most nem lesz szükség.

Amit itt felsoroltam, azok az ÖSSZESÍT argumentumai – ha elkezded beírni a formulát, látni fogod, hogy milyen lehetőségek vannak az egyes pontokon belül.

Mi a terv?

Meg kellene számolni, hogy az adott cella fölött hány sor van. Ehhez a DARAB2 függvényt alkalmaznánk, feltételezve, hogy a számolni kívánt tartományban nincs üres cella.

Tehát ezt tesszük a “burokba”:

  • a DARAB2 függvényt fogjuk használni, aminek 3 a számkódja
  • kihagyjuk a rejtett sorokat – ennek 5 a számkódja
  • mindig az adott cella fölötti tartományban számoljuk meg a cellákat

Így néz ki a formula az első sorban:

=ÖSSZESÍT( 3 ; 5 ; $F$3:F3 )

=AGGREGATE( 3 ; 5 ; $F$3:F3 )

A tartomány hivatkozás első tagja abszolút hivatkozás (“le van dollározva”). Ha a képletet lefele másoljuk, mindig az adott cella feletti tartományra fog hivatkozni. (Fontos, hogy tulajdonképpen DARAB2 formulát használunk, ami az üres cellákat nem számolja meg. Olyan oszlopra kell hivatkozni, ahol biztos, hogy nincs üres cella.)

Ennyi az egész – bármilyen szűrés esetén működik!

Nézzétek meg a példa fájlt, amiben megtaláljátok a felépített formulát is!


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


nov 04

Értékek szűrése a kimutatásban

no-magicAvagy: nem kell ide Visual Basic!

Egy nagyon érdekes projekten dolgozok épp: SAP-ból hívunk le adatot, feldolgozzuk és kimutatást készítünk belőle a felhasználóknak. Mindezt automatizálva, egy gombnyomásra.

Az alap feladat egyszerű: a kimutatásban megmutatjuk, hogy bizonyos alapanyagokból az adott időszak termeléséhez mennyire van szükség. Az egyik fontos igény ezzel a kimutatással kapcsolatban az volt, hogy a 0 értékeket ne mutassuk, szűrjük ki a kimutatásból. (Nyilván: ne mutogassuk azokat az anyagokat, amik most épp nem kellenek a termeléshez.)

A prototípusban, amit Visual Basic-kel automatizálni kellett, egy régi megoldásomat alkalmazta a megrendelő: feltett egy szűrőt a munkalapra, és kiszűrte a 0-kat. Kérte, hogy a VBA kód ezt a szűrést frissítse a kimutatás frissítésekor.

Nos, be kell vallanom, azt a régi megoldást csak ad-hoc esetekben használom, ha a frissítés szóba kerül, akkor már macerás a dolog… Muszáj elővenni valami jobbat a Kimutatás rejtett bugyraiból, mert…

pfilter_1

…ha az alap táblázatodban megváltoznak az értékek, és frissíted a Kimutatást, akkor sajnos a munkalapon levő szűrő nem frissül vele együtt – újra “rá kell nyomni” a szűrő gombjára. Mondhatnám úgy is, hogy a kimutatás és a munkalap szűrő nem beszélget egymással. Lehetne persze VBA-val automatizálni, de nem szükséges, ha van jobb, VBA-mentes megoldás:

A kimutatás saját szűrőjének használata értékek szűréséhez.

Hogyan működik? A képek elmondják a lényeget. :-)

pfilter_2

pfilter_3

Nagyon fontos előny a munkalap szűréjéhez képest, hogy ez a szűrő a kimutatással együtt működik és frissül – ha egyszer beállítottad a feltételt, a kimutatás minden frissítésnél ennek megfelelően fogja mutatni az adatokat.

Ha ki akarjátok próbálni, itt a teszt fájl teszt adatokkal.

 


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


sze 14

Formátum másolás több cellába

Tudom, kicsit leállt a cikkírás, de örülök, hogy ti azért látogatjátok, olvassátok a blogot. :-)

Nyár van, projekt is volt, az idő meg kevés. Ez se lesz egy hosszú bejegyzés, de nagyon hasznos apróságot találtam, amit gyorsan megosztok veletek is.

Úgy kezdődött, hogy egy nagyon kedves barátnőmmel találkoztunk, ő mondta nekem, hogy a Wordben be lehet kapcsolni a formátum másolást, és több helyre másolni a kijelölt formázást. EzMilyenKirály! A billentyű kombinációra persze már nem emlékeztem, mire hazaértem, de dupla kattintással is működik a dolog!

pasteformat

Ezzel “bekapcsolod” a formátum másolót, s addig másolsz vele, amíg akarsz. Kikapcsoláshoz kattints rá még egyszer.

Köszönöm, Lyány! :-) Nálam bekerült a TOP 5 trükk közé!
 


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


máj 23

Időtartam diagram egyszerűen

Egy kedves olvasónk kérdezte, hogy hogyan lehetne érkezett-távozott adatokat diagramon ábrázolni. Olyan diagramot szeretett volna, ahol vízszintes vonalak jelzik egymás fölött, hogy az egyes személyek mennyi ideig tartózkodtak valahol.

A megoldásban halmozott sáv diagramot javasoltam:

idotartam02

Hogyan készül?

Az adattáblában az időpontok mellé az időtartamot is fel kell venni. Mivel az Excel a dátumot számként tárolja, egyszerűen kivonhatod egymásból a két dátumot.

idotartam03

  1. Jelöld ki az Érkezett oszlopot és szúrj be halmozott sáv diagramot!
    idotartam04
  2. Add hozzá az Időtartam adatokat a diagramhoz új adatsorként. Mutatok most erre egy nagyon gyors másolás-beillesztéses trükköt:
    idotartam06
    Jelöld ki az Időtartam oszlopot (felirattal együtt)!
    Nyomj Ctrl+C-t

    Kattints a diagramon!
    Nyomj Ctrl+V-t!
    Ilyen lett:
    idotartam05
  3. És a lényeg már kész is van, innentől csak formázzuk!
    Szedd le a jelmagyarázatot – nincs rá szükség.
    A kék sávokat formázd kitöltés nélkülire (jobb klikk a kék sávon, majd adatsorok formázása menü)
    Ugyanitt az adatsor beállításainál a térközt én 50%-ra vettem, hogy kicsit szélesebbek legyenek a sávok.
    A bordó sávok színét is átállíthatod, hogy ne legyen olyan “Excel” színű.

Egyszerű, ugye? :-)

 


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


máj 19

Függvény (formula) beírós trükkök

Tegnap levelet kaptam egy kanadai Excel szakértőtől. Egy gyorsbillentyű kombinációról kérdezett, ami nyelv függő. Nem ismertem ezt a gyorsbillentyűt (már írtam róla régebben, hogy nem tudok ilyeneket megjegyezni) de végigpróbáltam a billentyűzeten – és megtaláltam! Hasznos kis trükk, ezért meg is osztom gyorsan veletek!

Ti hogy írtok be formulát a cellába?

  1. Biztosan ismeritek a függvény varázslót, amit a kis ikonra kattintva lehet elérni. A megjelenő kis ablakban egyszerűen lehet a függvény argumentumait kitölteni. Nagyon sokan ezt használják.
    fx
  2. Másik megoldás, ha az egyenlőségjel után elkezditek begépelni a függvény nevét. Ekkor az Excel felajánlja egy listában a beírható függvény neveket. A megfelelő függvénynéven állva TAB-ot kell nyomni, és a függvénynév a kezdő zárójellel együtt beíródik a cellába. Az argumentumokat “kézzel” kell kitölteni, de egy kis dobozkában mindig megjelenik a szintaktikai segítség. (Ha valaki másnak a gépénél vagyok, nagy segítség, hogy látom, p pontosvesszőt vagy vesszőt használ elválasztó karakterként. Innen szoktam puskázni. :-))
    fx2
  3. És most jön a trükk. Ha beírtad a formulát a kezdő zárójellel, nyomj Ctrl + Shift + N-t (angol Excelben Ctrl + Shift + A) így az argumentum lista bekerül a cellába. Ha duplán kattintasz egy-egy argumentum nevén, akkor az feketével kijelölődik, a munkalapon pedig ki tudod jelölni a hozzá tartozó tartományt. Kicsit olyan, mintha a függvény varázslót a cellába építenénk.
    fx3
  4. És még egy: Biztosan veletek is előfordult, hogy a szerkesztőlécen írt formula alatt megjelenő szintaktikai segítség dobozkája eltakarta az oszlopok fejlécét – és pont arra az oszlopra kellene kattintani. Nos, ezt a dobozkát arrébb lehet mozgatni – csak a doboz végéhez kell vinni az egeret, s ha a kurzor 4 nyíllá változik, már mozgatható is!
    fx4

Nos, remélem, hasznosak ezek az apróságok! Én a 4-et biztos, hogy használni fogom!

Kapcsolódó témák:

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

Képlet kiértékelés egyszerűen

 


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


ápr 29

Miből számol az Excel? Nagy segítség képlet, formula kiértékeléséhez: F9

Öcsém egészen aktív Excel használó lett, amit mi sem bizonyít jobban, minthogy már a második blogbejegyzést dedikálom neki. :-) Egy furcsa, becsapós problémát küldött el nekem, aminek felfedéséhez egy nagyon egyszerű képlet kiértékelő megoldást mutattam neki – most megosztom veletek is, hogyan lehet ellenőrizni, miből számol az Excel.

Nézzük ezt: 10-szer 10 az 103 ???

F9_01

Miből számolta ezt ki a képlet?

Jelöld ki a szerkesztőlécen a D2 cella hivatkozást:

F9_02

Majd nyomj F9-et:

F9_03

Az Excel megmutatja a cella értékét: ez 10,3 – nem 10, amit a cellában látunk!

Az F9 megnyomásával az Excel kiértékeli (kiszámolja) a formula-részt.

Ahhoz, hogy visszakapd az eredeti formulát, nyomj ESC-et, vagy visszavonást!

Persze ez csak egy nagyon egyszerű eset, amit cella formázással, tizedes jegyek beállításával is el lehet érni.

A trükk ilyen izgalmas formulákhoz is használható (most kivételesen angol nyelvű formulákkal):

F9_04

Ahol az x_b egy elnevezett formula:

F9_05

A kiértékelés eredménye pedig egy tömb:

F9_06

Kijelölhetsz beágyazott formulákat is – ilyenkor a zárójelekre figyelj, úgy kell kijelölni, hogy minden, a formulához tartozó zárójel benne legyen a kijelölésben:

F9_07

Az ISNUMBER eredménye TRUE:

F9_08

Én nagyon sokat használom ezt a trükköt – szerintem sokkal praktikusabb, mit a menüben megtalálható, beépített formula kiértékelő.

És most jöjjön valami személyes:

F9_09

:-)


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


ápr 20

Adat vizualizáció: mutasd meg a részleteket az összesen mögött!

Jól ismert probléma, hogy egy összesen, egy total érték elfedi azokat az adatokat, mikből összeadódik. Megnézed egy termékcsoport profitabilitását, és látod, hogy szép nagy pozitív szám. Aztán ha elkezded nézegetni a termékeket, kiderül, hogy van benne néhány, ami igencsak rosszul teljesít – így már nem olyan szép a kép. Megalapozott üzleti döntéshez figyelni kell ezekre a részletekre is. Nade hogyan lehet ezt vizuálisan is megjeleníteni?

Nemrég az angol blogon írtam erről, megosztottam egy oszlop diagram megoldást, amit néhány egyszerű lépésben összeraktam. Roberto természetesen egy órán belül csinált egy másik megoldást, teljesen más megközelítésben. Az ő megoldását fogom most nektek bemutatni, részben azért, mert picit egyszerűbb, másrészt mert szépen illeszkedik a vízesés grafikonnál megtanult technikára.

1. lépés: adattábla

A termékcsoportok (kategóriák) szerepeljenek külön oszlopokban, alattuk a termék adatok sorba rendezve. A termék adatok nevére nincs szükség, azok nem fognak szerepelni a diagramon.

Alulra tegyél egy összesítő sort, alá pedig írj nullákat – ez egy segéd adatsorhoz kell majd.

total_and_parts_h_v2

2. Alap diagram létrehozása

Jelöld ki az adattáblát, a feliratokkal együtt, de az összesen sor nélkül, és szúrj be oszlop diagramot.

Ezután meg kell cserélned a sorokat és oszlopokat: a Diagrameszközök menüben a Tervezés alatt találod a Sor/Oszlop váltása gombot (Chart tools / Design / Switch Row/Column)

total_and_parts_h_v3

A jelmagyarázat félrevezető, ezért töröld ki.

3. Két új adatsor hozzáadása

Két új adatsorra lesz szükségünk. Egyik a termékcsoport összesen értékéből, a másikat a 0 sorból rakjuk fel. Mindkettőt vonal diagramtípussal fogjuk ábrázolni. Ezek közé kerülnek majd a termékcsoport oszlopok.

Tehát: a Diagrameszközök / Tervezés / Adatok kijelölése (Select Data) (vagy jobb klikk a diagramon). Válaszd a Hozzáadás gombot, jelöld ki az adatokat és adj egy nevet az adatsornak. (Nálam: top)

total_and_parts_h_v4

Ismételd meg ugyanezt még egyszer, és a 0-kat is add hozzá adatsorként a diagramhoz. (Nálam ez a bottom nevű lett.)

4. A két adatsor diagram típusának módosítása

Minkét utóbb hozzáadott adatsor diagram típusát vonalra módosítjuk. Jelöld ki az egyiket (a Diagram eszközök / Elrendezés alatt a bal oldalon a legördülő listában tudod kiválasztani név alapján)! Ha ki van jelölve, a Tervezés menüben kattints a Más diagram típus gombra, és a vonalat válaszd. (Az adatsor jobb klikk menüjéből is eléred ezt a Sorozat-diagramtípus módosítása alatt.)

Ne felejtsd ugyanezt megcsinálni a másik adatsorral (bottom) is!

Most így néz ki:

total_and_parts_h_v5

Van egy halvány vonal, és egy másik is, ami nem látszik, mert 0 értékekből áll.

Tudom, ez egy picit macerás lépés – a 2013-as Excelben sokkal könnyebb az adatsorok diagram típusának módosítása – talán ezt szeretem legjobban az új verzióban. :-)

5. Pozitív/negatív eltérés oszlopok hozzáadása

Most jön a legérdekesebb lépés! Jelöld ki az egyik vonal adatsort, és az Elrendezés menüben az Elemzés csoport alatt megtalálod a Pozitív/negatív eltérés gombot – add ezt hozzá a diagramhoz. (2013-as Excelben: Add chart element / Up/down bar)

Ezek az oszlopok “beülnek” a két vonal adatsor közé:

total_and_parts_h_v6

Készen is vagyunk!

Na jó, egy kis formázás még hátravan, de a lényegi rész már felépült!

6. Oszlop szélesség beállítása

Az igazán szép megjelenéshez az eltérés oszlopok szélességét növelni kell. Ezt kicsit trükkös helyre rakták, nem az eltérés oszlopok formázásánál, hanem a vonal adatsornál találod meg. Jelöld ki a vonal adatsort, és kattints a kijelölés formázására.

7. Adatsor formázások

A vonal adatsort formázd vonal nélkülire – nincs szükség rá, hogy ez látszódjon.

Az eltérés oszlopok különleges helyzetben vannak: ahhoz, hogy a mögötte levő kis oszlopok látszódjanak, ezeket átlátszóvá kell formázni. Teheted teljesen átlátszóvá, és használhatsz csak keretet a megjelenítéshez. Másik megoldás, ha választasz egy színt és az átlátszóságot magasra (kb 80%) állítod. Én egy kombinált megoldást választottam: színátmenetes színezésben adtam meg, hogy az alsó rész teljesen átlátszó, a felső pedig zöld alapszínnel részben átlátszó. Jó ki játék és próbálgatás ez az alsó sávok formázása menüben! (Legegyszerűbben: jobb kattintás az eltérés oszlopokon.)

Íme a végeredmény:

total_and_parts_v2

Ha sablonból szeretnéd a grafikont használni, vagy kíváncsi vagy a beállításokra, töltsd le a minta fájlunkat!

Ha többet szeretnél tudni az eltérés oszlopokról, olvasd el a vízesés grafikonról írt sorozat első részét!

 


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


már 29

PowerPivot kimutatás értékmezők berakása Szeletelőből

Az előző bejegyzésben megmutattam, hogyan lehet egy segéd kimutatás és néhány sor VBA kód segítségével szeletelőből berakni érték mezőket (value fields) a kimutatásba.

PowerPivotra is alkalmazható a megoldás, csak a VBA kódban kell egy kicsit változtatni: itt CubeFields-t használunk és más string-el rakjuk be a choice elnevezésből kiolvasott mezőnevet.

Az alábbi kódot tudjátok bemásolni a megfelelő munkalap kód moduljába – minden előző lépés és magyarázat azonos az előző bejegyzésben a hagyományos kimutatásnál leírtakkal.

 


 

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    
    Dim ptMain As PivotTable
    Dim pfMeasure As CubeField
    Dim i As Long
    
    On Error GoTo Errorhandler

    Set ptMain = ThisWorkbook.Worksheets("Pivot").PivotTables("PivotTable1")

    For Each pfMeasure In ptMain.CubeFields
        If pfMeasure.Orientation = xlDataField Then
            pfMeasure.Orientation = xlHidden
        End If
    Next
    
    i = 0
    Do While [choice].Offset(i, 0).Value <> ""
        ptMain.AddDataField ptMain.CubeFields("[Measures].[" & [choice].Offset(i, 0).Value & "]")
        i = i + 1
    Loop
    
    Exit Sub
    
Errorhandler:
Debug.Print Now(), Err.Description
    
End Sub


Régebbi bejegyzések «

css.php