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

 

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? :-)

 

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!

További olvasnivalók:

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

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

 

 

á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

:-)

 

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

 

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


már 29

Kimutatás értékmezők berakása Szeletelőből!

Párbeszéd részlet egy kollégámmal:

– Lehet olyat csinálni, hogy a kimutatásba az értékmezőt egy szeletelőből rakom be?
– Nem.
– Kár… Pedig milyen jó lenne abba a kimutatásba, ahol a választott mutatószám időbeli trendjét mutatjuk. A felhasználók nem nagyon szeretik a jobb oldali Mezőlistát megjeleníteni és abban berakni a mutatószámot.
– Ja… erre tök jó lenne… hm…. tulajdonképpen meg is lehetne csinálni. Ez tök jó ötlet!

Pár perc múlva már kész is volt. :-) Itt a minta fájl és egy kép belőle:

slicer_measure_1

Tehát a felső szeletelőben lehet meagdni az alsó kimutatás mutatószámát. Ha (Ctrl lenyomásával) többet választunk, mind meg is jelenik! :-)

Fontos: Az itt bemutatott megoldás hagyományos kimutatásokhoz készült. A PowerPivot-tal használható verziót lásd a folytatásban.

Nem kezdőknek való megoldás, de elmagyarázom, hogyan kell csinálni.

Szükség lesz pár sornyi VBA kódra, egy segéd táblára és egy segéd kimutatásra. Aki gyakorlott kimutatások használatában, és nem idegenkedik a VBA szerkesztőtől, annak nem lesz nehéz felépíteni ezt a kényelmes kis eszközt.

Adattábla

slicer_measure_2

Kékkel jelöltem az érték oszlopokat (ezek a mutatószámok) – ezekből lehet majd szeletelő segítségével választani. (Nem szükséges, hogy egymás mellett legyenek.) Ebből az adattáblából készül az az alap kimutatás, amihez majd a trükkös szeletelőt kapcsoljuk.

A VBA kódban felhasználjuk majd a kimutatás nevét. Ezt a menüben találod meg, a Kimutatáseszközök / Beállítások (Pivot table tools / Analyze) alatt a bal oldalon. Az én fájlomban PivotTable1 a név.

Mutatószám lista, kimutatás és szeletelő

slicer_measure_3

Egy új, üres munkalapon a mutatószámok neveiből csinálj egy kis táblázatot, majd ebből készíts egy kimutatást. A mutatószám mező kerüljön a sorcímkékhez. Vedd le a végösszegeket (grand total) a kimutatásról! Adj hozzá egy szeletelőt a kimutatáshoz: Kimutatáseszközök / Beállítások / Szeletelő beszúrása (Pivot table tools / Analyze / Insert Slicer). A szeletelőt helyezd át az alap kimutatás fölé.

(Természetesen nem kötelező minden mutatószámot felvenni a listába – ez a megoldás arra is jó lehetőség, hogy szűkítsd a választható mutatók körét az adott kimutatás céljának megfelelően!)

Jegyezd meg a munkalap nevét, ahova a kis kimutatást tetted. Nálam Szeletelo a neve.

Elnevezett cella

slicer_measure_4

A mutatószám kimutatás első celláját (amiben az első – kiválasztott – mutatószám neve olvasható) nevezd el choice-nak (vagy másnak, csak jegyezd meg a nevet! :-)) (Az elnevezés munkafüzet hatókörű legyen.)

A szeletelőben választott első érték mindig itt jelenik majd meg, ha többet választunk, akkor azok alatta lévő cellákban lesznek.

VBA kód

A VBA Editort Alt+F11 billentyűkombinációval éred el. A kódot a Szeletelo munkalap (vagyis ahol a kis mutatószámos kimutatásod van) kód moduljába kell írni – ezt a Project Explorer ablakból (jobb oldalon) tudod megnyitni:

slicer_measure_6

És a kód:

slicer_measure_5

(A kimásolható kódot a cikk alján találod.)

Az ábrán látod, hogy milyen konstansokkal dolgozik a kód – ezeket kell átírnod, ha nálad mások az elnevezések.

S hogy mi történik itt? A Szeletelo munkalapra egy eseményvezérlést raktunk fel: A kimutatás megváltozásakor (PivotTableUpdate) fut le ez a kis VBA “programka”. Először “elkapja” a kimutatást, amiben az mutatószám mezőt cserélgetni kell (ezért kell a munkalap neve és a kimutatás neve). Aztán az összes értékmezőt “kiszedi” – ezt az xlHidden beállítással lehet megtenni.

Majd végignézi, hogy mit választottunk a szeletelőben: ehhez a choice elnevezést és az alatta levő cellákat vizsgálja. Amit itt talál, azt beteszi a kimutatás értékmezőihez (AddDataField).

És ennyi.  :-)

Nem kell megijedni, és se kisujjból ráztam ki ezt. Picit belekukkantottam az Object Browserbe. :-) Ha tetszik a megoldás, és szeretnéd alkalmazni, próbáld ki a minta fájlunkat, abban látni fogod a VBA kód ablakot, és ki tudod másolni a kódot a saját fájlodba is.

Ha nem szeretnél lemaradni az új bejegyzésekről, iratkozz fel blog értesítőnkre!

 


Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    
    Dim ptMain As PivotTable
    Dim pfMeasure As PivotField
    Dim i As Long
   
    On Error GoTo Errorhandler
    
    Set ptMain = Worksheets("Kimutatas").PivotTables("PivotTable1")
    
    For Each pfMeasure In ptMain.DataFields
        pfMeasure.Orientation = xlHidden
    Next
    
    i = 0
    Do While [choice].Offset(i, 0).Value <> ""
        ptMain.AddDataField ptMain.PivotFields([choice].Offset(i, 0).Value)
        i = i + 1
    Loop
    
    Exit Sub
    
Errorhandler:
Debug.Print Now(), Err.Description
    
End Sub

 

már 21

Két vonal közti terület kiszínezése

area-lineaKolléganőmtől kérték, hogy készítsen olyan grafikont, amin két év adatait mutatja be, és zölddel színezi, amikor az idei év adatai meghaladják a tavalyit, pirossal, amikor alatta maradnak. Sajnos nem volt alkalmam gyorsan segíteni neki – picit át kellett volna alakítani Roberto egy régebbi megoldását, de erre nem volt már idő – ezért ebben a bejegyzésben közzéteszem a sablon fájlt, remélve, hogy sokaknak hasznos lesz.

UPDATE: Megújítottam a fájlt, hogy negatív számokat is tudjon kezelni!

Innen letölthetitek a fájlt, amiben megtaláljátok azt is, hogyan kell beírni az adatokat. Fontos, hogy a tengelyfeliratok hozzárendelése csak 2013-as Excelben működik. Ha korábbi verziót használtok, kézzel kell a feliratokat a diagramra feltenni.

A fájl maximum 100 sornyi adat kezelésére alkalmas, a sárga cellákba írhatjátok az adatokat, a tengely oszlopba pedig, hogy mi legyen a vízszintes tengely felirata.

A szürke cellákban levő számok a vízszintes tengely pozícióit jelölik. Alapvetően 0-val kezdődő számsornak kell itt lennie. Ha xy koordináta párokat szeretnél megjeleníteni, akkor értelemszerűen ezek lesznek az x koordináták, egyéb esetben csak egy folytatólagos számozásra van szükséged.

area-linea_1

A diagramról annyit kell tudni, hogy xy scatter és terület diagram kombinációja, ahol a terület diagram elemek vízszintes tengelybeosztása át van skálázva a megfelelő vonal-keresztezések kezeléséhez. A gyakorlatban ebből annyi a lényeg, hogy ha a vonalak színét vagy a kitöltő színeket akarod megváltoztatni, akkor a megfelelő adatsor kiválasztása után a formázás menüben tudod ezt megtenni. (Vagy jobb klikk a formázandó diagram elemen: )

area-linea_2

Az diagram építés elméleti hátterét itt most nem részletezem – a munkalapon láttok sok számoszlopot, amik az adatsorokat felépítik. Akit érdekel, annak esetleg egy kávé mellett elmondom majd, hogyan működik. :-)

Örülnék, ha kommentben megírnátok, mire-hogyan használtátok a sablont! :-)

 

már 16

Hyperlink és legördülő lista kombinálása

A hyperlink egyszerű, gyakran használt eszköz a munkafüzetben való navigáció megkönnyítésére: könnyű vele egyik munkalapról a másikra ugrani. Gondoltatok rá, hogy ha sok munkalapunk van, milyen egyszerű lenne kiválasztani egy legördülő listából, hogy hová akarunk ugrani? Nem kell nagy helyet elfoglaló link-listát fenntartani minden munkalapon: csak kiválasztod és rákattintasz – ahogy az ábrán látod: a munkalap nevek olasz városok, ezek jelennek meg a legördülő listában:

H_name2

Igen, ezt meg lehet csinálni – mutatom, hogyan!

Alapvetés

Először nézzük, hogyan működik a hyperlink beszúrás! A Hyperlink beszúrása menüpontban nem csak munkalap cellákat, hanem elnevezett tartományokat is ki lehet választani – így ezekhez egyszerűen kapcsolható hyperlink, ami rögtön a tartományhoz ugrik. Azonban ha jobban megfigyelitek, nem jelennek itt meg azok az elnevezések, amelyek mögött formula áll – hiába ad vissza tartományt a formula. Csak a “statikus”, közvetlen cella referenciát tartalmazó neveket lehet itt a hyperlinkhez adni.

H_name1

Kérdés tehát: Hogyan tudunk hyperlinket kapcsolni olyan elnevezéshez, ami formulát tartalmaz?

Kicsit be kell ehhez csapni az Excelt: az elnevezést azután változtatod meg, hogy hyperlinkként használtad! Tehát:

  1. először csinálj egy elnevezést, ami tartományra hivatkozik
  2. tedd bele a hyperlinkbe
  3. majd változtasd meg az elnevezést: írd be a formulát, amit használni szeretnél.

Ez lesz a megoldásunk alapja. Nézzük lépésről-lépésre, hogyan lehet felépíteni a legördülő listával kombinált hyperlinket!

1. Munkalap nevek listája

Szükséged lesz egy elnevezett tartományra, ami a legördülő listában használni kívánt munkalap neveket tartalmazza. Írd be egy munkalapra a neveket (ezek az példa szerint az olasz városnevek) és nevezd a tartományt Sheets_List -nek.

H_name3

2. Érvényesítési lista

Fontos, hogy minden munkalapon ugyanabba a cellába kerüljön majd az érvényesítés! Az egyik munkalapon, ahol használni akarod a trükköt, készíts érvényesítést (Data validation) legördülő listával, a Sheets_List nevet használva.

H_name4

Ez eddig ugye semmi extra: ki lehet választani egy munkalap nevet a listából, és nem történik semmi. Nézzük tovább!

3. Elnevezés készítése a hyperlinkhez

Csinálj egy elnevezést My_Sheet_Choice névvel, ami az aktuális aktív cellára hivatkozik – ezt fogjuk majd megváltoztatni.

H_name5

4. Hyperlink felrakása a cellára

Szúrd be a hyperlinket a cellára, ahova a legördülő listát tetted! A hyperlink a My_Sheet_Choice névre mutasson – ahogy az alábbi ábrán látod.

H_name6

5. A trükk: hivatkozás átírása

Változtasd meg a hivatkozást a My_Sheet_Choice elnevezésben! Olyan formulát fogunk berakni a név referenciájába, ami az adott legördülő listában kiválasztott munkalap celláját adja vissza – így fog a hyperlink a megfelelő munkalapra ugrani.

=INDIRECT( ADDRESS( 1,2,,,INDIRECT( ADDRESS(1,2) ) ) )

=INDIREKT( CÍM( 1;2;;;INDIREKT( CÍM(1;2) ) ) )

Figyelem! a fenti formula a példában bemutatott esethez tartozik, amikor a B1 cellában van a legördülő lista. Ha te máshova tetted, mindkét CÍM formulában kell átírnod a sor és oszlop számot: első paraméter a sor, második az oszlop száma: 1;2 -> B1.

H_name7

6. Hyperlink másolása

Minden munkalapra, ahol használni szeretnéd a legördülő listás hyperlinket, másold át a cellát, ami a trükkös linket tartalmazza. Figyelj, hogy minden munkalapon ugyanoda kerüljön a linkes cella!

Hogyan működik?

A beágyazott INDIREKT( CÍM(1;2) ) formula kiolvassa a munkalap nevet, amit a B1 cellába írtál. (B1-nek a CÍM formulában az 1;2 paraméter felel meg.) Ez a munkalap név lesz a külső CÍM formula 5. paramétere. Az első két paraméterrel együtt, ami itt is az 1;2 (sor;oszlop) hivatkozás, a formula cella referenciát ad vissza. Összerakja a cellát és a munkalap nevet a megfelelő (szöveges) formátumba. Például: ‘New York’!$B$1 -> aposztrófok közé kerül a munkalap név, és felkiáltójel jön a cellahivatkozás elé. Ebből a szöveges referenciából csinál az INDIREKT valódi cella hivatkozást, amit aztán az elnevezett tartományon keresztül megkap a hyperlink, így ez a dinamikus hivatkozás fogja a hyperlinket a megfelelő munkalapra vezetni.

Innen letölthetitek a példa fájlunkat, amiben fel van építve egy működő modell.

Az elnevezett tartományok alapjairól itt olvashatsz.

Ez az írás egy korábbi angol cikkünk alapján készült.

 

már 03

Mennyiségi egység beírása a szám mögé: egyéni adat formázás

1kgEbben a bejegyzésben visszatérünk az alapokhoz: röviden leírom, hogyan kell adatot bevinni az Excelbe – úgy, hogy azzal utána számolni is tudjunk. A cikk alapvetően kezdőknek szól, de haladók sem mindig ismerik azt a formázási beállítást, amit mutatni fogok: hogyan írjuk a számadat mögé, hogy kg, fő, liter, stb.

Az Excelt általában azért használjuk, mert számolni szeretnénk vele. Számolni pedig csak számokkal lehet – ez elég triviálisan hangzik. De amikor a kezdő felhasználó elkezdi összerakni az első táblázatát, amibe beírja, hogy naponta hány kg terméket állítottak elő, akkor adódhat némi “félreértés” a felhasználó és az Excel között.

Az így bevitt adatot nem lehet összeadni:

kg1

Fontos, hogy a cellába mindig csak egyféle adat kerüljön. Amit itt látunk az két adat: szám és mennyiségi egység. Ezeket külön kell választani, hogy a számok önmagukban szerepeljenek:

kg2

A mennyiségi egységet (kg) külön oszlopba írtam, így a SZUM képlet már működik. Figyeld meg, hogy a számok jobbra vannak igazítva, a szöveg pedig balra. Ez az Excel alap beállítása, erről fel lehet ismerni, hogy számadat van a cellában.

Másik megoldás: Egyéni formázás

Haladó felhasználók választhatják azt a megoldást, hogy az Excelt “kérik meg”, hogy írja a számadat mögé a mennyiségi egységet. Ezt nagyon egyszerűen a Formátum beállításával tudod megtenni. Jelöld ki a számokat, majd a Kezdőlap menü közepén a Szám menücsoportban válaszd a legördülő menüből a További számformátumokat.

A Kategória alatt kattints az Egyénire, és írd be a formátumkódot: 0″ kg”. (0 után idézőjelben egy szóköz és kg)

kg3

A formátum kód nagyon egyszerűen épül fel: a 0 azt jelenti, hogy a cellába írt számot megjeleníti, tizedes jegy nélkül. Utána időzőjelbe a szám mögé írandó karaktersort tesszük: a szóköz azért kell, hogy szépen elváljon a mennyiségi egység a számtól. A Minta mezőben látható, hogyan fog kinézni az adat.

Így néz ki a formázott adat:

kg4

Figyeld meg, hogy a cellában balra van igazítva a szöveg – tehát ez itt szám adat! A szerkesztőlécen az eredetileg beírt számot látod, a kg csak a cellában jelenik meg! (Úgy is mondhatnám, a kg valójában nincs ott, nincs beírva, csak az Excel mutatja nekünk.)

Ha tizedes jegyet is szeretnél megjeleníteni, így írd a kódot: 0,0″ kg” – ahány nullát írsz a kódba a vessző után, annyi tizedes jegyet fogsz látni a munkalapon is.

Kapcsolódó írás:

“Ott van a számban a pont”

 

feb 27

Sor, oszlop beszúrás letiltása a munkalap egy részén

NemszurbeVan egy nagyon hasznos, de kevéssé ismert trükkös megoldás arra, hogyan lehet megakadályozni sorok, oszlopok beszúrását a munkalap egy részén – a nélkül, hogy a lapvédelmet bekapcsolnád.

Szükség lehet erre például olyan, komplexebb munkalapok esetében, ahol valami összesítés, lista, fix tábla van a munkalap tetején, amit nem szabad “szétvagdosni” sor beszúrással.

 

Íme a trükk:

A sárgával jelölt részen szeretném megakadályozni a sorok beszúrását:

Nemszurbe1a

  1. Jelöld ki a táblázat bal oldalán levő tartományt
  2. Írd be a szerkesztőlécre ezt a formulát: =”x” (egyenlőségjel után egy x idézőjelben)Nemszurbe2
  3. A formulát Ctrl+Shift+Enter-rel zárd le: a kijelölt tartomány minden cellájában megjelenik az x.
    Nemszurbe3
  4. Jöjjön a teszt: sor beszúráskor hibaüzenetet kapsz:
    Nemszurbe4

Ugyanezt meg tudod csinálni az oszlopokra is, a táblázat feletti cellák kijelölésével.

A x helyett más karaktert is beírhatsz, sőt, üres string-et is: =”” (két idézőjel). Arra figyelj, hogy az üres string esetében nem fogod látni, hol van a képlet!

S most egy pici elmélet: Mit csináltunk itt?

Tömbképletet írunk be a cellákba. Erről itt olvashatsz részletesen, de a legfontosabbakat összefoglalom:

  • A tömbképletbe nem szúrható be és nem törölhető oszlop/sor.
  • A tömbképletet az Excel mindig egy képletként kezeli.
  • A tömbképletet csak Ctrl+Shift+Enter-rel tudod módosítani. A tömbképletet bármely cellában módosíthatod, a módosítás az egész tömbre érvényes lesz
  • A tömbképlet kitörléséhez az egész tömböt ki kell jelölni, így Delete dombbal törölhető.

Az oszlopot, sort el lehet rejteni, hogy a felhasználót ne zavarja az, ami a cellákban megjelenik. Én nagyon sokszor használom ezt a megoldást – és eddig nem sok embernek árultam el a trükköt. :-)

 

feb 04

Hogyan távolíthatóak el a kimutatás mező szűrő listából a nem létező elemek?

wtf_s

 

Találkoztatok már olyannal, hogy a kimutatás mező szűrő listájában oda nem illő, régi adatok jelentek meg?

Már réges-rég nincs ilyen vevő, olyan termék, a szűrő lista mégis tele van ilyen “szeméttel”.

Miért történik ez? És hogyan lehet ezeket eltüntetni?

 

 

Így néz ki a kimutatás, amiben az összes vevő látszik, ami az adattáblában szerepel:

Pivot_rendezett_9b

Ez pedig a mező szűrő listája – tele “szeméttel”:

Pivot_rendezett_10b

Hogyan kerülnek ezek be a szűrő listába?

A kimutatás frissítésekor a szűrők megjegyzik (megőrzik) az összes elemet, ami eddig az adott mezőben szerepelt. Ha például az előző havi adatokban még volt “A”, “B”, “C” vevő, de az aktuális hónapban már nincs, hiába törlöd ki az előző havi adatokat és teszed be helyette az újat, ezek a régi vevők továbbra is szerepelnek a szűrő listában. Ez az kimutatás egy sajátossága, de meg lehet változtatni.

Mit lehet tenni?

Be kell állítani, hogy ezeket a régi elemeket ne őrizgesse tovább a szűrő. Jobb kattintás a kimutatáson, válaszd a Kimutatás beállításai menüt. Itt az Adatok fülön találod a Mezőnként megőrzendő elemek száma opciót. A Semmennyit kell beállítanod, majd utána frissíteni a kimutatást, hogy mindig csak az adattáblában létező elemeket lásd.
(Angol Excelben: a PivotTable Options menüben a Data fülön a Retain Items rész alatt válaszd a None-t.)

Pivot_rendezett_11b

Nálunk a gyakorlatban időnkét előfordul, hogy véletlenül egy oszloppal elcsúszva, vagy rossz oszlop sorrenddel másoljuk be az új adatokat az adat táblába. Például a vevő alá kerülnek a márkák. A kimutatás frissítése után a márkák megjelennek a vevő mező szűrőjében, és ott is ragadnak, miután az oszlop sorrendet javítottuk. Ha beállítjuk a megőrzendő elemek számát Semennyire, a listából eltűnnek az oda nem illő elemek.

Érdemes emlékezni erre a beállításra, ha gyakran változnak az adatok a kimutatásod háttértáblájában!

Utóirat: ne felejtsétek el a kimutatást frissíteni a Semennyi beállítása után! :-)

 

feb 02

Kimutatás jelentésszűrő mező legördülő listájának rendezése

A kimutatás és sorba rendezés témakört folytatva ez a bejegyzés is egy kollégám kérdésére ad választ. Ez a kérdés azonban sok évvel ezelőtt hangzott el, és kellett egy kis Google segítséget kérnem annak idején, hogy megtaláljam a megoldást.

Hogyan lehet a kimutatás szűrő mezőjének (jelentésszűrő) legördülő listáját sorba rendezni?

Pivot_rendezett_6a

Látható a képen is, hogy itt nincs sorba rendezési lehetőség, ezért én sem gondoltam arra, hogy ezt meg lehet csinálni. Pedig ez egy jogos felhasználói igény: ha sok elemű listából kell választani, akkor ABC sorrendben könnyebb megtalálni, amit keresünk.

A trükk egyszerű: Le kell rakni a mezőt a jelentésszűrőkből a sorcímkék közé.

Pivot_rendezett_7a

Itt már be lehet állítani a rendezést:

Pivot_rendezett_8a

…majd visszarakva a jelentésszűrőkhöz, a lista ott is rendezett marad.

Ennyi az egész!

S hogy miért látunk a legördülő listában időnként olyan elemeket, amiknek már régen nem kellene ott lennie? Erről szól a következő bejegyzés.

 

feb 01

Sorba rendezés a kimutatásban érték szerint

Régen írtam már kollégám kérdésére választ adó cikket, ezért örültem a mai kérdésnek – duplán is: rövid kérdés, egyszerű válasz, és megint egy hasznos apróság.

Feladat: Kimutatásban kell sorba rendezni az adatokat. Azt szeretnénk, hogy árbevétel szerint csökkenő sorrendben legyenek a márkák:

Pivot_rendezett_1

Ilyen beállításokkal már kezdő felhasználók is lényegre törő, hasznos kimutatásokat tudnak készíteni!

A megoldás nagyon egyszerű, mindössze 3 kattintás:

A Sorcímkék mellett kattints a lefele nyil gombon, és válaszd a További rendezési lehetőségek (More sort option) menüpontot.

Pivot_rendezett_2

A kis ablakban beállíthatod, hogy milyen sorrendben és melyik mező alapján akarsz rendezni:

Pivot_rendezett_3

Alul egy kis összegzést is olvashatsz: “Márka rendezése Összeg / Árbevétel k EUR szerint csökkenő sorrendben”.

Ha a sorcímkékhez több mező is be van húzva, a menüpont tetején tudod kiválasztani a mezőt, amelynek rendezését be szeretnéd állítani:

Pivot_rendezett_5

Így már azt is egyszerűen meg tudod csinálni, hogy a márkákon belül a vevők is az árbevétel nagysága szerint legyenek rendezve. Először állítsd be a márkákra a rendezést árbevétel szerint, aztán a vevőkre is ugyanezt:

Pivot_rendezett_4

Egyszerű, ugye?

És ami a legjobb: frissítés után is megmarad a sorrend – vagyis az Excel automatikusan újra rendezi az adatokat.

A következő bejegyzésben arról olvashattok, hogyan lehet a jelentésszűrő mezőt rendezni.

 

dec 12

PowerPivot: konstans a számított mezőben – tapasztalatok

Nemrég részt vettem az Önkiszolgáló BI workshop-on (itt írtam erről). Azóta már fel is építettem egy adatbázist, amit nagy megelégedéssel használnak a kollégák. Az építgetés közben azonban szembetalálkoztam egy furcsa jelenséggel, ami nekünk a gyakorlatban sajnos bosszúságot okozott. Ebben a cikkben bemutatom, mi volt ez, és hogyan sikerült áthidalni a problémát.

Az adatbázis

Az adatbázisunk cikkszámonkénti értékesítési adatokat (mennyiség, árbevétel, stb.) és egy cikktörzs táblát tartalmaz. A cikktörzs táblában a cikkszámok különböző szempontok szerint csoportosítása van tárolva (termék kategória, márka, stb.) A két táblát a cikkszám mező kapcsolja össze. Fontos, hogy a cikktörzs táblában több cikkszám van, mint amennyire árbevételünk van az adott időszakban (tartalmaz régi cikkeket, más országokban értékesített cikkeket és technikai cikkszámokat is).

A megfigyelt probléma

A PowerPivot-ra épített pivot táblában olyan cikkek, cikk csoportok is megjelentek, melyekre egyáltalán nem volt értékesítési adat. Ez sok esetben több száz üres, adattal nem rendelkező sort jelent, amik bekeverednek a többi cikk közé:

PP_const_1

Miért kerülnek be ezek a cikkek a pivot táblába?

Nyomozás

Megfigyeltem, hogy a gyanús cikkeknél csak a olyan mezőben van érték, ami kalkulált mező, ÉS konstans van a formulában!

Készítettem egy kis példát, hogy jobban érthető legyen.

Ez lesz a cikktörzs tábla:

PP_const_4

Ez pedig az adat tábla:

PP_const_3

Mindkettőt betöltöttem PowerPivot-ba, és a cikkszám mezővel összekapcsoltam.

Látható, hogy a 7, 8, 9 és 10-es számú cikkekre nincs adat – ezek az x és y csoportba tartoznak.

Ezután kalkulált mezőt készítettem az árbevétel növekedés számszerűsítésére:

Növekedés % 1    := DIVIDE( [árbevétel 2015] ; [árbevétel 2014] ; 0 )

A növekedést cikk csoportonként mutatja a pivot tábla:

PP_const_5

Nálunk azonban a növekedés mutatószám definíció szerint a 100%-on felüli részt jelenti, tehát így kell számolnom:

Növekedés % 2    := DIVIDE( [árbevétel 2015] ; [árbevétel 2014] ; 1 ) – 1

Ezzel a mutatószámmal a pivot tábla így néz ki:

PP_const_6

Látható, hogy megjelent az x és y csoport, amire nem volt adat az értékesítési adatok táblában!

A problémát egyértelműen a formula végén található -1 okozza. Felépítettem a kalkulációt IFERROR-ral is:

Növekedés % 3    := IFERROR( [árbevétel 2015] / [árbevétel 2014] – 1 ; 0 )

Ebben az esetben ugyanúgy megjelentek az felesleges sorok.

Csak akkor sikerült kiküszöbölni ezeket a sorokat, ha számítást konstans nélkülivé alakítottam:

Növekedés % 4    := IFERROR( ( [árbevétel 2015] – [árbevétel 2014] ) / [árbevétel 2014] ; 0 )

Mi történt itt?

A táblák közötti kapcsolat (JOIN) segítségével összeállítódik a kalkuláció (virtuális) alaptáblája. Ebben minden cikkszám szerepel, ami megjelenik akár a cikk törzs, akár az adat táblában. Azok a sorok, amelyek üresek (BLANK), vagyis nem szerepelnek az adat táblában, azok nem fognak megjelenni a pivot táblában.

A probléma a kalkulált mezőből ered: a PowerPivot-ban a BLANK + konstans = konstans. Ebben az esetben tehát a kalkuláció az üres sorokra is értéket ad, vagyis ezek benn maradnak a pivot táblában.

Gábor: Ha SQL-ben gondolkodunk, úgy néz ki, mintha a táblákat összekapcsoló JOIN típusa változna meg: mintha INNER JOIN-ból OUTER JOIN lenne. Ez így nem jó, mert olyan kalkulált adatok jelennek meg a végeredményben, amelyek a valóságban nem léteznek – csak azért mert egy bizonyos módon írtuk fel a képletet: ha másképp írjuk fel, akkor ezek a nem létező eredmények nem jelennek meg.

Megoldás

Nos… Nem tudom, miért hasznos ez a furcsa BLANK kezelés, nekem egyenlőre bosszúságot okoz. Szerencsére csak olyan kalkulált mezőket kell felépítenem, ahol a hányadosba be tudom építeni a konstanst – így átalakítva már nem okoz problémát a pivot táblában.

Remélem, másnak is hasznos lesz ez a tapasztalat! Innen letölthetitek a példa fájlunkat is, amiben látjátok a kalkulációt és a pivot táblákat.

nov 22

Pivot cache-ről: együtt frissülő pivot táblák készítése

Pivot1Nem túlzás, ha azt mondom, nálunk, egy nagy multinacionális cégnél a pénzügyi riportok 99%-a pivot táblán alapul – nem tudnánk létezni pivot tábla nélkül… Ahhoz, hogy jól tudjuk használni, felépíteni a pivot táblákat, tudni kell, mi történik a háttérben: mi az a pivot cache. Technikai dolognak tűnik, de aki nem csak “egyszer használatos” pivot táblát készít, az sok kellemetlenségtől megkímélheti magát, ha megfogadja az alábbi javaslatokat – enélkül a pivot tábláink könnyen hatalmas, belassult Excel fájlokat növesztenek.

Tovább a teljes tartalomhoz »

nov 02

Kimutatás szűrő mezők több oszlopba rendezése

Érdekes megoldást láttam egy belső kimutatásban pár nappal ezelőtt: Két oszlopba voltak darabolva a Pivot tábla szűrő mezői (magyarul lapozó mezők, vagy jelentésszűrők).

Valahogy így nézett ki:

Pivot2col01a

Nos, NAGYON egyszerű ezt megcsinálni!

Tovább a teljes tartalomhoz »

okt 13

PowerPivot szeletelő (Slicer) szűrőfeltételeinek kiolvasása tömbképlettel

A tömbképletekről szóló cikksorozatot (első és második rész) kicsit megakasztotta az adat vizualizációs tréninghez írt sorozat, de most újra felvesszük a fonalat.

Rögtön egy különleges használati esetet mutatok be: hogyan lehet tömbképlettel kiolvasni a PowerPivot adatbázist szűrő szeletelők szűrési feltételeit?

Tovább a teljes tartalomhoz »

okt 11

10 éves a BI projekt blog!

Rendszeres olvasója vagyok a BI projekt blognak, s ezúton is szeretnék gratulálni Attilának, hogy immár 10 éve folyamatosan publikál adattárház és üzleti intelligencia témában! Nosztalgiázva olvastam a megemlékező bejegyzést, és visszagondoltam, valóban mennyi minden megváltozott ez alatt a pár év alatt.

Én azt tapasztalom az utóbbi időben, hogy egyre több adattal dolgozunk, az üzleti igények egyre komplexebbek és sokkal gyorsabb adatszolgáltatást vár el az üzleti terület a pénzügytől. Közben az adatszolgáltatás (mi úgy mondjuk, riportok) minősége is átalakul: sokkal határozottabb az igény az egységes, szépen formázott, gyorsan áttekinthető táblázatokra, valamint az hatékony adat vizualizációra. (Nálunk most a vízesés grafikon a sztár!) A riport legyen összesített, de részletes is, szűrhető és átalakítható is és dinamikus is…

Ezek a folyamatos kihívások minket is a PowerPivot irányába tereltek, s az önkiszolgáló BI workshoppon való részvétel után lassan elkészül az első PowerPivot alapú riport fejlesztésem. Közben azon is gondolkodom, milyen tapasztalatokat, ötleteket tudok majd veletek is megosztani – az első téma már készülőben van! :-)

 

okt 10

Mindentudó vízesés grafikon (Waterfall chart 4. rész)

Közkívánatra elkészítettem az előző bejegyzésben szereplő, automatikus tengely minimum beállítást lehetővé tevő vízesés grafikon extra-haladó változatát.

Ebben már nem csak a tengely minimum jelenik meg egy kis feliratban, hanem az egész tengely látható, a beállított lépésköznek megfelelő beosztással:

waterfall19

A tengely persze nem igazi, egy adatsor és némi trükközés segítségével raktam rá a grafikonra. A technikai részletekre most nem térek ki, csak a modellt szeretném közzétenni, hogy a saját adataitokkal használhassátok.

Tovább a teljes tartalomhoz »

Régebbi bejegyzések «

css.php