szept 24
A tréningek végén mindig kiosztok egy értékelő kérdőívet, hogy visszajelzéseket gyűjtsek a résztvevőktől. Ezeket általában csak el szoktam olvasgatni, sosem csináltam még belőle diagramokat. Most igen. És ha már diagram, sok kérdés, sok szöveges értékelés – összeraktam egy infografikát is. Excelben készült, a munka legnagyobb része formázgatás, elhelyezés, dizájn volt. 🙂
Ilyen lett:
Tréning értékelő infograf
Kövesd az Adatkertészetet a Facebookon!
szept 11
Mostanában foglalkoztam kicsit dizájnnal és tipográfiával (jól kiegészítik a kalligráfiát :-)). A Coursera-n találtam színvonalas és érdekes tanfolyamokat, és a munkahelyen is volt egy elég jó tréning.
Roberto, régi Excel-mester szerzőtársunk talált egy adat vizualizációs kihívást – érdemes elolvasni a tejles bejegyzést (angolul van), sok hasznos gondolat van benne azzal kapcsolatban, hogyan gondolkodjunk, milyen szempontokat vegyünk figyelembe diagramok, vagy több diagramot tartalmazó illusztrációk tervezésekor.
A kihívásban ezt az adat vizualizációt kellett újragondolni:
Mit látunk itt? Az utazási piac megoszlását kontinensek között.
Mi a fő üzenet? (Mit emel ki a cím?) Hogy az Asia Pacific régió átvette a vezetést 2016-ra.
Mi a legszembetűnőbb a kördiagramon? Hogy Európa részesedése csökkent.
Le tudjuk olvasni a kördiagramokról a fő üzenetet? Hát… Ha jobban megnézem a világoskék szeletet, és a hozzá tartozó számokat, akkor végülis le.
Végülis…
A kördiagram használható részek és az egész viszonyának ábrázolására – ha max. 4-5 részt mutatunk. Ez itt a példában 6 rész – így már nem a legjobb választás. A fő hiba viszont az, hogy kördiagramot nem szabad időbeli változás bemutatására használni, mert nem tudjuk követni a körcikkek változását. Ez egy nagyon fontos alapszabály a diagram választásnál! Ezért nem jön át a fő üzenet, nem látszik a rangsor.
Ilyen adatok megjelenítéséhez a halmozott oszlop diagram az ajánlott megoldás.
Zöld színnel hangsúlyoztuk azt, amire az olvasó figyelmét fel szeretnénk hívni. Szépen látszik, hogy jelentős volt a növekedés, egyből ez ragad meg a grafikonból. Az adatok a 2016-os értékek alapján vannak sorbarendezve – így ezen a megoldáson a rangsorbeli változás nem egyértelmű, de a részesedés változása (mivel egymás mellett van a két zöld rész) leolvasható.
Ha a rangsort is ábrázolni szeretnénk, Roberto egy korábbi megoldására lesz szükség, ez a stacked column flow chart:
Nos, mi ezzel neveztünk a kördiagramos kihívásra. 🙂
Itt jobban hangsúlyozzuk a rangsorbeli helycseréket, de a részesedéseket nem tudjuk olyan jól egymáshoz viszonyítani. Hogy melyik a jó megoldás? Mindegyik. 🙂 Attól függ, mit szeretnél hangsúlyozni, mi a fő üzeneted!
Kövesd az Adatkertészetet a Facebookon!
márc 26
Öcsémnek segítettem a hétvégén a munkájához kapcsolódó táblázatok összerakásában. Szépen leképeztük a folyamatot és az adatbevitelt, az összesítéseket pedig kimutatásokkal készítettük el. Ahhoz, hogy az összesítések mindig a legfrissebb adatot mutassák, egy kis VBA kód segítségével megoldottuk, hogy automatikusan frissüljenek a kimutatások minden alkalommal, amikor a kimutatásokat tartalmazó munkalapra kattintunk. Lássuk, hogyan csináltuk!
ThisWorkbook.RefreshAll
Ezt kell beírni az alábbi módon:
Kész is van, becsukhatod az ablakot! Próbáld ki, hogy jól működik-e!
Fontos: a fájl mostantól nem menthető a hagyományos .xlsx formátumba! .xlsm (Makróbarát Excel-munkafüzet) vagy .xlsb (Bináris Excel-munkafüzet) formátumot használhatsz. Erről itt írtam részletesebben.
Kövesd az Adatkertészetet a Facebookon!
márc 22
Ha már az előző bejegyzésben foglalkoztunk a tartomány első értékének megkeresésével, nézzük most meg, hogyan találhatjuk meg az utolsó kitöltött cellát – például egy oszlopban. Az alábbi módszer nagy előnye, hogy biztosan a legutolsó cellát találja meg – akkor is, ha üres cellák is vannak az oszlopban!
Két különböző formulát használunk, attól függően, hogy a tartomány számokat vagy szöveget tartalmaz.
=HOL.VAN(10^10 ; B:B ; 1)
=MATCH(10^10 ; B:B ; 1)
A trükk annyi, hogy egy nagyon nagy számot (10 a tizediken) keresünk meg a tartományban úgy, hogy harmadik paraméterként (egyezés típusa) 1-et állítottam be. Ez két fontos dolgot jelent: a legnagyobb olyan értéket keressük, ami nem nagyobb a keresési értéknél (10^10) ÉS feltételezzük, hogy a keresési tartományban növekvő sorrendben vannak az értékek. Emiatt a keresés folytatódik a lista legutolsó (jelen esetben legalsó) eleméig, (ami a feltételezés szerint a legnagyobb érték lenne) – de természetesen még mindig kisebb, mint a keresett nagyon nagy szám. Itt pedig megáll a keresés.
(Látjátok, az adatok valójában nincsenek növekvő sorrendben! – az Excel az 1-es paraméter alkalmazása esetén ezt nem vizsgálja, mindössze úgy működik, mintha a tartomány rendezett lenne. Ezt a „naivságát” használjuk ki ezzel a megoldással!)
Eredményül 11-et kapunk – ez az utolsó cella sorszáma a B:B tartományon belül.
Ugyanazt a formulát és logikát alkalmazzuk, mint az előző esetben, csak most nem egy nagyon nagy számot, hanem egy, az ábécében nagyon hátul levő karaktersort használunk:
=HOL.VAN(„zzzz” ; B:B ; 1)
=MATCH(„zzzz” , B:B , 1)
Én itt a „zzzz”-t választottam – ez eléggé nagy bizonyossággal az utolsó „szó” lenne egy abécébe rendezett listában. Ha egészen biztosra akartok menni, főleg, ha különleges karaktereket is tartalmazhatnak az adataitok, akkor az omega jelet javaslom a formulában használni:
=HOL.VAN(„Ω” ; B:B ; 1)
=HOL.VAN(„Ω” , B:B , 1)
(Megtaláljátok a Beszúrás menüben a Szimbólumok alatt.)
Akkor a legegyszerűbb mindkét formulát felírni, és a kettő közül a nagyobbik sorszámot használni:
=MAX( HOL.VAN(10^10;B:B;1) ; HOL.VAN(„Ω”;B:B;1) )
=MAX( MATCH(10^10,B:B,1) , MATCH(„Ω”,B:B,1) )
Kérdezz tőlünk Excel segítő csoportunkban vagy kövesd az Adatkertészetet a Facebookon!
márc 20
Hazafelé a metrópótlón ketten ültek mellettem és Excelről beszélgettek:
Hát én még azt se tudom, hogyan kell kijelölni egy táblázatot. A főnököm meg csak lenyomott két gombot, és már ki is jelölte.
Sajnáltam, hogy nincs nálam egy névjegy a blog címével… annyira meg nem vagyok bátor, hogy beleszóljak a beszélgetésbe. De talán majd megtalálja ezt a cikket az, aki ihlette. 🙂
Nézzük, hogyan lehet gyorsan, nagy táblázatot kijelölni!
Először is az ugrás: állj a fejléc cellára, és nyomj Ctrl + lefele nyilat. Ezzel leugrasz az utolsó adatra az oszlopban. Ugyanez történik, ha megnyomod az Endet, és utána a lefele nyilat. (Tehát Ctrl nyomva tart, vagy End csak megnyom.)
A nyilakkal mindenféle irányba tudsz így gyorsan ugrálni.
És most kijelölés: A kijelöléshez a Shiftet kell nyomva tartani. Tehát: fejléc cellán állsz, Ctrl + Shift + lefele nyíl:
És már ki is jelöltük az oszlop összes adatát. (Működik az End-es változattal is, csak az Endet nem kell nyomva tartani.)
Ezután még egy Ctrl + Shift + jobbra nyíl, és az oszlopokat is kijelölted.
Ennyi az egész.
Persze lehet ez küzdelmes, ha üres cellák vannak az oszlopban… ilyenkor sajnos megáll az ugrás, megáll a kijelölés az üres cella előtt. Figyeljetek erre!
Kérdezz tőlünk Excel segítő csoportunkban vagy kövesd az Adatkertészetet a Facebookon!
márc 19
Tegnap egy kedves kolléganőm keresett meg egy egyszerűnek tűnő kérdéssel. Havi bontású táblázatban termék értékesítési adatai vannak, és arra kíváncsi, hogy egy-egy terméket melyik hónapban kezdtük értékesíteni.
Így néz ki a táblázat:
Azt kellene megmondanunk, hogy az A terméket először februárban, a B-t márciusban, a C-t júniusban… értékesítettük.
Formula-nyelven: minden sorban meg kell keresni, hogy melyik cellában van először szám érték, s ehhez meghatározni a hónapot.
A megoldáshoz tömbképletre lesz szükségünk – először meghatározzuk, melyik cellában van az első nem 0 érték:
=HOL.VAN(IGAZ; (C3:N3)<>0; 0)
=MATCH(TRUE, (C3:N3)<>0, 0)
Ne felejtsd! Ctrl+Shift+Enterrel kell lezárnod a formulát!
Mi csinál ez a formula? Nézzük a belsejét: (C3:N3)<>0 – ez a logikai tömbkifejezés egy IGAZ-HAMIS tömböt ad vissza. Az F9-es gyors kiértékeléssel nézzük ezt meg a szerkesztőlécen – íme az első sorhoz tartozó formulában a (C3:N3)<>0 rész kiértékelve:
HAMISt kapunk, ahol 0 a cella értéke (kihasználjuk, hogy az üres cellát az Excel 0-nak értelmezi), és IGAZat, ha nullától eltérő az érték. Ez most egy tömb, mert a (C3:N3)<>0 kifejezésben nem egy cella, hanem egy tartomány szerepel. Azért kell Ctrl+Shift+Enterrel lezárni a formulát, hogy a fenti HAMIS\IGAZ… tömböt kapjuk eredményül.
Ebben a tömbben az első IGAZ érték felel meg annak a cellának, ahol az értékesítés kezdődött. A HOL.VAN ezt meg tudja nekünk találni, és megmondja a pozícióját.
A pozíció segítségével az INDEX formula pedig a hónap nevét adja:
=INDEX($C$2:$N$2 ; ; P3)
Így néz ki a munkalapon:
Innen letölthetitek a példa fájlt a formulákkal.
Folytatás: A tartomány utolsó kitöltött cellájának megkeresése.
Kérdezz tőlünk Excel segítő csoportunkban vagy kövesd az Adatkertészetet a Facebookon!
jan 16
Kimutatás trükkökből sosem elég! Lássuk, hogyan tudsz gyorsan, egyszerűen havi összesítést csinálni egy dátumokat tartalmazó táblázatból!
Tulajdonképpen nem is kellene írnom semmit, a képeken látszik, mit kell csinálni. 🙂
Kiindulunk egy kimutatásból, ahol a Dátum mezőt behúztam az Oszlopokhoz – így az egyedi dátum adatok kerülnek oszlopokba, jó széles lesz a táblázat.
Ezután elég egy jobb kattintás az egyik dátumon, a felugró menüből válaszd a Csoportosítás…-t (Group):
Kiválaszthatod, milyen csoportokba akarod a dátumokat rendezni. Én csak hónapok szerint csoportosítottam.
És már kész is, itt vannak a hónapra összesített adatok:
Fontos: a dátum tényleg dátum legyen – ne egy dátumnak látszó karaktersor. Csak a megfelelő adattípust tudja az Excel így csoportosítani.
Kérdezz tőlünk Excel segítő csoportunkban vagy kövesd az Adatkertészetet a Facebookon!
dec 06
Az előző bejegyzésben, mely a Visual Basic Editor ablak részeiről szólt, megígértem, hogy mutatok egy jó kis trükköt: Elrejtünk egy munkalapot úgy, hogy a trükköt nem ismerő nem tudja láthatóvá tenni azt!
A Project Explorerben kattints duplán egy munkalap objektumra, hogy a Properties ablakban lásd a tulajdonságait. A Visible tulajdonságra figyeljünk most!
A tulajdonság értéke egy látható (nem rejtett) munkalap esetén -1, vagy xlSheetVisible.
Ha visszamész az Excel felületre, és elrejted a munkalapot (jobb kattintás a lapfülön, aztán Elrejtés), akkor a tulajdonság értéke 0, vagyis xlSheetHidden lesz.
Tehát a nem rejtett munkalap tulajdonság-értéke xlSheetVisible, a rejtetté xlSheetHidden.
És most jön a trükk: nézzük meg, hogyan lehet a tulajdonság értékét a Properties ablakban állítani! Mit rejt a legördülő menü?
Van itt egy harmadik tulajdonság-érték: 2, vagy xlSheetVeryHidden. Ez a vicces „nagyon-rejtett” érték azt jelenti, hogy a munkalap nem jelenik meg az Excel felületen a felfedhető munkalapok listájában! Próbáld ki, állítsd be a tulajdonságot – ha visszamész az Excelbe, és jobb-klikkelsz a munkalap neveken, a Felfedés nem érhető el! (Vagy a munkalap neve nem szerepel a felfedhetőek között.)
Csak úgy tudod láthatóvá tenni a munkalapot, ha a Properties ablakban visszaállítod xlSheetVisible-re.
Hasznosnak találjátok? Én szoktam alkalmazni, ha nem akarom, hogy a felhasználók nézegessenek bizonyos munkalapokat… persze most már elég sok embernek megtanítottam a trükköt, úgyhogy nem jelent valami nagy védelmet. 🙂
Kérdezz tőlünk Excel segítő csoportunkban vagy kövesd az Adatkertészetet a Facebookon!
dec 04
A „makróírás” vagy ahogy én jobban szeretem nevezni: VBA programozás egy nagyon szép, komplex terület – különösen Excelben. Ebben a különleges bejegyzésben (azt ünnepeljük, hogy 400 tagja van a Facebook csoportunknak!) egy régóta tervben levő témába vágunk bele: megismerkedünk a Visual Basic Editor felülettel, és rögtön egy jó kis trükköt is megosztok veletek.
A Visual Basic Editor az Excel programozási felülete. A Fejlesztőeszközök (Development) menüpont alatt találod meg, vagy az Alt+F11 billentyűkombinációval tudod megnyitni. (Ha nincs Fejlesztőeszközök menüd: jobb klikk valamelyik menüelemen, majd Menüszalag testreszabása…)
Megnyílt egy új ablak – fontos tudni, hogy ez az ablak független az Exceledtől, bármikor becsukhatod, nem fog semmi elveszni.
Most az ablak két fontos részére fókuszálnunk. A bal felső „dobozka” a Project Explorer, alatta a Properties Window. Ha nem látszanak, a View menüben tudod őket bekapcsolni.
Nézzük, mik ezek!
Itt látod felsorolva az Exceledben épp nyitva levő fájlokat. Nálam épp egy nem mentett munkafüzet van, amiben dolgozom, ez a Munkafüzet1, valamint a Solver van nyitva, ami egy bővítmény (automatikusan megnyíló rejtett fájl.)
VBAProject (munkafüzet neve) bal oldalán a kis + jelet kinyitva a „Microsoft Excel Objects” alatt láthatod, milyen részei vannak a munkafüzetnek. „Szaknyelven” ezek a részek az objektumok. Mindig megtalálod itt a fájl munkalapjait és egy ThisWorkbook objektumot, ami maga a munkafüzet.
Itt látod a Project Explorerben kiválasztott objektum tulajdonságait. Most a Munkafüzet1 első, Képek nevű munkalapja van kiválasztva (dupla kattintás). Ezek a tulajdonságai:
A kétoszlopos kis táblázatban a tulajdonság neve és értéke szerepel. Például munkalap esetében van Name tulajdonság, melynek értéke: Képek. Ha a munkalapot átnevezed, azzal a tulajdonság értékét változtatod meg. Próbáld ki, menj vissza az Excel ablakra, és nevezd át a munkalapot! A Name tulajdonságnál most már az új nevet kell látnod.
De most van egy másik lehetőséged is: a Properties Windowban is átírhatod a tulajdonság értékét: csak kattints bele a Name tulajdonság értéket tartalmazó cellába, és írj be valami mást. Így az Excel felületen is megváltozott a munkalap neve.
És épp ez a kettősség lesz itt a lényeg: vannak ugyanis olyan tulajdonság-értékek, amiket CSAK ITT, a Properties Windowban lehet beállítani! No, de ezt majd inkább a következő cikkben részletezem!
(Például felül található itt egy másik „(Name)” tulajdonság is. Ezt csak a Visual Basic használja, és csak itt lehet megadni. Ez azért hasznos, mert ha a felhasználó át is nevezi a munkalapot, ez a név megmarad, így ha ezt használjuk hivatkozásra, akkor nem romlik el az átnevezéstől a makrónk. De ezekről majd később!… 😉 – Gábor)
Kérdezz tőlünk Excel segítő csoportunkban vagy kövesd az Adatkertészetet a Facebookon!
nov 06
Régen írtam már grafikonokról – itt az ideje, hogy megosszak valami különlegeset. Haladóknak.
Olyan grafikont készítettünk, amivel kétdimenziós táblázat adatait tudod ábrázolni. Például – ahogy a képen is látod – levegőminták értékét különböző városokban. A buborék nagysága jelzi, hogy az adott minta az adott városban mekkora értéket ért el.
Az eredetit Roberto találta ki és készítette el. Az angol oldalunkon ez az egyik legnépszerűbb különleges grafikonunk. Sokan kerestek meg minket levélben, hogy különböző módosításokkal segítsünk egy-egy konkrét problémára alkalmazni ezt a fajta vizualizációt.
A megoldás, amit most megosztok veletek, dinamikus elnevezett tartományra és elnevezett formulákra épül. Ezt a technológiát nagyon szeretjük alkalmazni grafikon építésnél, mert sokkal könnyebb így dinamikus megoldásokat építeni: nem kell a munkalapon formulákat másolgatni. Az eredeti cikkben angolul megtaláljátok a leírást – magyarul pedig talán majd egy következő meetup témája lesz ez. 🙂
Innen letölthetitek a minta fájlt, amiben egyszerűen átírhatjátok az adatokat, és új sort, oszlopot is vehettek fel – a grafikon automatikusan bővülni fog!
Kérdezz tőlünk Excel segítő csoportunkban vagy kövesd az Adatkertészetet a Facebookon!
júl 03
Bosszantó… hogy miért ad vissza #HIÁNYZIK! (#N/A) hibát az FKERES, amikor biztos vagy benne, hogy a keresett adat szerepel a keresési táblában. Látod a szemeddel, hogy ott van, akkor miért nem találja meg?
Leellenőrizted a tartományt, a hivatkozásokat? Igen, jók. Ledollároztad? Igen, ebben nincs hiba. Mégse működik.
Nos, akkor itt az ideje pár dolgot megtanulni az adatformátumról.
Dióhéjban: akkor kell adatformátum problémára gondolnod, ha valamilyen számot keresel. Olyan számot, amit nem számolásra használunk: kódszám, cikkszám, ilyesmi. Ilyenkor előfordul, hogy az Excel úgy érzékeli, hogy ez a szám valójában szöveg – úgy mondjuk:szövegként tárolt szám. Általában ez okoz „miért nem működik???” problémát…
A gyanús jelek:
Az FKERES esetében akkor fog ez problémát okozni, ha a keresett érték és a keresési tartomány adatformátuma eltér. A szövegként tárolt számot az Excel szövegként értelmezi, és úgy gondolja, hogy ez semmiképpen nem lehet „egyenlő” egy számmal – tehát hiába van ott a kód, nem fogja megtalálni, ha más az adatformátum.
Mi NEM megoldás? Látjátok, hogy következetesen adatformátumról beszélek, nem cellaformátumról. Sőt, ezt illene is inkább cellaformázásnak nevezni. Az adatformátum probléma nem oldható meg cellaformázással. Hiába állítod át a cellát szám formátumúra, ettől még szöveg marad benne. Ezzel tehát felesleges próbálkozni.
Az adatformátum változtatáshoz mindenképpen konvertálni kell.
Nem kell megijedni, egyszerű! Több lehetőség is van:
elöld ki a tartományt. Az Adatok (Data) menüben találsz egy Szövegből oszlopok gombot (Text to column) – a megjelenő ablakban csak nyomd meg a Befejezést.
Mostmár működik, ugye? 🙂
Ez a korábbi cikkünk az adattisztításról még érdekes lehet a témában!
Kérdezz tőlünk Excel segítő csoportunkban vagy kövesd az Adatkertészetet a Facebookon!
júl 02
Meg kellene mutatni egy mérőszám időbeli alakulását úgy, hogy megjelenítjük az elvárt értékek sávját is?
Vagy mérések eredményeit vizualizálni az elfogadható értékek tartományával kombinálva?
Nos, ez egyszerű lesz. Olyat fogunk csinálni, mint a felső diagram, narancssárga sávval:
A vonaldiagramot halmozott területtel fogjuk kombinálni. A halmozott (angolul stacked) diagram típus azt jelenti, hogy két vagy több adatsort teszünk egymás tetejére. A mi esetünkben egy átlátszó terület fogja „tartani” a tartományt megjelenítő (narancssárga) adatsort.
Induljunk ki ilyen adattáblából:
Jelöld ki mindhárom oszlopot és szúrj be vonal diagramot (Insert / Charts csoport, 2-D line)
Ez született:
Most meg fogjuk változtatni a „sáv alsó értéke” és a „sáv nagysága” adatsorok diagram típusát – ezzel kombinált diagramot hozunk létre. A kész diagramon az adat vonalként, a sáv értékek halmozott területként lesznek ábrázolva.
Ebben a bejegyzésben olvashatsz részletesen arról, hogyan kell a 2010-es és 2013-as Excelben az adatsor diagram típusát megváltoztatni. Itt most csak néhány szóban fogom a lényeget leírni.
Excel 2010-ben:
Jelöld ki a „sáv nagysága” adatsort a diagramon – kattints rá, vagy a Diagrameszközök menüben a Formátum alatt a bal oldalon tudod kiválasztani.
Ha ki van jelölve, kattints a Tervezés alatt a Más diagramtípus-ra. Itt megtalálod a Terület alatt a Halmozott terület típust. Ismételd meg ugyanezt a sáv alsó értéke adatsorra is.
Excel 2013-ban:
A Change chart type alatt megtalálod a Combo típust, a sáv alsó értéke és a sáv nagysága adatsorokat is állítsd Halmozott terület (Stacked Area) típusra.
És kész is a lényeg – olyan lett, mint a fenti előnézet képen látható. Innentől már csak formázások következnek!
Az alsó sávot kitöltés nélkülire kell állítani: jobb klikk, Adatsor formázása – a kitöltésnél: nincs kitöltés (NEM FEHÉR!)
A sötét narancssárga sávot hasonló módon érdemes valami világosabbra állítani – a színe attól függ, hogy ez a jó tartomány (ilyenkor lehet pl. zöld) vagy a rossz (narancssárga-piros).
A vonalnak választhatsz vékonyabb, határozottabb feketét. Fontos az átláthatóság, a jó kontraszt!
Kérdezz tőlünk Excel segítő csoportunkban vagy kövesd az Adatkertészetet a Facebookon!
jún 27
Kollégák kérdéséről mindig szívesen írok bejegyzést, mert ezek valódi, gyakorlatban felmerülő példák, nem csak önmagáért való képlet magyarázatok. Ma egy elég komplex tervező fájlunk kapcsán merült fel a kérdés: ha az értékesítő kollégák egy kis két dimenziós táblázat alapján vannak termékcsoportokhoz és területekhez rendelve, akkor hogyan tudjuk ebből a táblából kivenni, hogy egy adott termékcsoportért és területért melyik értékesítő felelős.
Tehát ilyen a táblázat:
Ebből kellene kiszednünk, hogy pl. Szegeden ki a felelős a Kozmetika termékekért.
FKERES-re gondolnánk, dehát melyik oszlopot írjuk bele?
Nos, szoktam mondani, hogy INDEX formulával majdnem mindent meg lehet oldani, főleg, ha valami keresős jellegű feladatról van szó. Tehát most is bátran tippeljünk inkább az INDEX-re, ez lesz a legegyszerűbb megoldás kulcsa! (Ha még nem ismered ezt a formulát, kezdd itt!)
Az INDEX pont azt tudja, amire most szükségünk van: egy két dimenziós táblából a sor és az oszlop száma alapján kiszedi a „metszet” elemet. Tehát úgy működne a fenti esetben, hogy Szeged a 4. sorban van, a Kozmetika a 2. oszlopban, vagyis így néz ki a formula a tábla ; sor száma ; oszlop száma paramétersorral:
=INDEX( $C$3:$H$11 ; 4 ; 2 )
És az eredmény Szegedi K. Szuper, egy jó eredményünk van. Akkor most ki kellene találni, honnan szedjük elő a sor és oszlop számot. 🙂
Ebben a HOL.VAN (MATCH) lesz segítségünkre, ami az INDEX formula tökéletes kiszolgálója. Itt írtam róla.
=HOL.VAN( „Szeged” ; $B$3:$B$11 ; 0 )
=MATCH( „Szeged” ; $B$3:$B$11 ; 0 )
Ez a formula megadja, hogy hányadik helyen található Szeged a város oszlopban. Az eredmény 4.
A HOL.VAN egy sorban vagy egy oszlopban (szigorúan egy dimenzióban!) tud keresni, tehát ugyanígy egy HOL.VAN-t írunk a termékcsoport sorra, ami megadja a pozíciót (jelen esetben az oszlop számot):
=HOL.VAN( „Kozmetika” ; $C$2:$H$2 ; 0 )
=MATCH( „Kozmetika” ; $C$2:$H$2 ; 0 )
Az eredmény 2.
Nincs más hátra, mint ezeket a darabkákat összerakni egy szép formulába, a megfelelő hivatkozásokkal!
=INDEX( $C$3:$H$11 ; HOL.VAN($B15;$B$3:$B$11;0) ; HOL.VAN($C15;$C$2:$H$2;0) )
=INDEX( $C$3:$H$11 ; MATCH($B15;$B$3:$B$11;0) ; MATCH($C15;$C$2:$H$2;0) )
Az INDEX első paramétere a tábla „belseje”, az egyik HOL.VAN a fejlécre, a másik a sorfejlécre hivatkozik. Fontos, hogy összhangban legyenek a tartományok, nehogy elcsússzanak egymástól a koordináták!
Remélem, hasznosnak találjátok ezt az egyszerű, segédcellák nélküli megoldást!
Kérdezz tőlünk Excel segítő csoportunkban vagy kövesd az Adatkertészetet a Facebookon!
jún 25
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:
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.
…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)
Az a fontos, hogy ide, a Diagram beszúrás (Insert Chart) párbeszédpanelre juss el:
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.
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.
Válaszd a kombinált típust, és állítsd be az adatsorokhoz a megfelelő típusokat!
Sajnos ebben a verzióban nincs kombinált diagram a diagram beszúrás párbeszédablakon…
…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:
(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:
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 .
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.
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.
Diagram „jó értékek tartománya” sávval
Kérdezz tőlünk Excel segítő csoportunkban vagy kövesd az Adatkertészetet a Facebookon!
jan 10
Egy 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. 🙂
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.
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:
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…
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.
A megnyíló ablakban az Árfolyam (Stock) alatt kell megkeresned a Nyit-Max-Min-Zár (Open-High-Low-Close) típust.
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.
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:
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ó:
A kitöltő színt állítsd át valami világosabbra.
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.
A névhez a fejlécet, az értékhez a medián értéket add meg:
S még mielőtt kilépnél az ablakból, a medián adatsort mozgasd a minimum és a maximum közé:
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.)
Á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.
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
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 RÁ 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:
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!!
Dinamikus grafikon feliratok készítése
Kérdezz tőlünk Excel segítő csoportunkban vagy kövesd az Adatkertészetet a Facebookon!
dec 30
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
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.
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.
Á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!)
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.
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
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.
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:
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
Apró 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.)
A jelölő alatt három beállítási lehetőség-csoport van:
2013:
2010:
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:
2010:
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:
* 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!
Legutóbbi hozzászólások