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!


„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. 🙂

 

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!


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!

 

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!


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!


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


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!


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!


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!


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

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

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

F9_01

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

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

F9_02

Majd nyomj F9-et:

F9_03

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

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

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

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

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

F9_04

Ahol az x_b egy elnevezett formula:

F9_05

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

F9_06

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

F9_07

Az ISNUMBER eredménye TRUE:

F9_08

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

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

F9_09

🙂


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


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

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

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

1. lépés: adattábla

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

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

total_and_parts_h_v2

2. Alap diagram létrehozása

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

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

total_and_parts_h_v3

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

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

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

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

total_and_parts_h_v4

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

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

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

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

Most így néz ki:

total_and_parts_h_v5

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

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

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

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

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

total_and_parts_h_v6

Készen is vagyunk!

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

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

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

7. Adatsor formázások

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

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

Íme a végeredmény:

total_and_parts_v2

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

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

 


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


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


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

 

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


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


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.

 

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”

 

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 gombbal 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. 🙂

 

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

 

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.

 

css.php