jul 03

Nem működik az FKERES (VLOOKUP) – amit az adatformátumról tudnod kell

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:

  • Balra igazított számok
  • Nem adja össze az Excel a számokat a jobb alsó sarokban
  • Kis zöld háromszög a cella sarkában
  • Hibajelzés, mikor a cellára kattintasz, és szövegként tárolt szám hibaleírás

 

adatformatum_2

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:

  1. Használhatod a hibajelzés legördülő opciói közül az átalakítás számmá (Convert to number) lehetőséget. Ha kijelölöd a tartományt, akkor az összes cellára elvégzi a konverziót.
    adatformatum_4
  2. A menüben is van egy pont a konvertálásra. Jadatformatum_5elö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.
    adatformatum_6
  3. Haladók elvégezhetik a konverziót az FKERES formulában is. Ez akkor lesz praktikus, ha gyakran kell felülírnod az adatokat, és nem akarod minden alkalommal kézzel konvertálni a tartományt. A keresési érték adatformátumát fogjuk a keresési tartományéhoz igazítani.
    Szövegből szám: bármilyen, az értéket nem változtató matematikai műveletet használhatsz.
    Például:
    =FKERES( A2+0 ; <tábla> ; <oszlop szám> ; <tartományban keres> )
    Vagy az én kedvencem:
    =FKERES( A2 ; <tábla> ; <oszlop szám> ; <tartományban keres> ) (Itt két mínusz jel van a keresési érték előtt.)
    Számból szöveg: A karaktersort nem megváltoztató szöveg műveletre van szükség. A számhoz hozzáfűzünk egy üres karaktersort, hogy szöveg legyen belőle:
    =FKERES (A2&”” ; <tábla> ; <oszlop szám> ; <tartományban keres> ) (Az & jel után két idézőjel van.)

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!


jul 02

Diagram “jó értékek tartománya” sávval

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.

Lássuk lépésenként

Induljunk ki ilyen adattáblából:

savos_1

Jelöld ki mindhárom oszlopot és szúrj be vonal diagramot (Insert / Charts csoport, 2-D line)

Sav1

Ez született:

savos_2

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.

Diagramtípus változtatás

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.

savos_3

É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!

Formázások

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

Két dimenziós FKERES (2D VLOOKUP)

325px-3D_glasses_istockKollé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:

2D_1

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!

2D_2

=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

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.

Példák:

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

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!


Régebbi bejegyzések «

css.php