Ha jártál már úgy, hogy egy elkészült PIVOT tábla egyik adatcellájára próbáltál hivatkozni, akkor bizonyára ismerős a jelenség. Ha a hivatkozást egérrel vagy a billentyűzet nyilaival próbáljuk bevinni, akkor az egyszerű hivatkozás helyett az Excel automatikusan a GETPIVOTDATA ( KIMUTATÁSADATOT.VESZ ) függvényt illeszti be a képletbe a sima ‘B8’ vagy ‘D32’ típusú cím helyett. Mit kell tenni, hogy mégis az egyszerű cellacímet szeretnénk használni?
A megoldás
Az egyik megoldás az, hogy kézzel beírjuk a hivatkozás címét. Ez kissé kényelmetlen.
A másik megoldás egy egyszerű PIVOT beállítás kérdése, amit bármikor módosíthatunk.
Kattintsunk a PIVOT belsejébe, hogy aktív legyen, és a szalag felett megjelenik a Kimutatáseszközök menüpont.
A Kimutatáseszközök Beállítások lapjának bal oldalán, a kimutatásnév alatt kattintsunk a Beállítások legördülő menüre.
Vegyük ki a pipát a „Kimutatásadatot.vesz művelet létrehozása” ( Generate GetPivotData ) elem elől
Ezután már használhatjuk rákattintással is az egyszerű hivatkozást.
Előfordulhat, hogy egy makró projekt kapcsán szembejön az a probléma, hogy nem tudjuk előre azt, hogy hol is kezdődik a feldolgozandó tartomány, vagy az éppen mindig változó. Emiatt jól jöhet ez az egy soros snippet a probléma megoldására.
[vb]
Range("A1").Select
Cells.Find(What:="*").Activate
[/vb]
Az első sor az aktuális munkalap első celláját aktiválja, hogy onnan kezdődjön a keresés. A második pedig megkeresi az első kitöltött cellát, amiben bármilyen érték lehet, szám, vagy szöveg, akár képlet.
A fenti kód csak akkor működik, ha az aktuális munkalap nem teljesen üres. Ellenkező esetben hibát fog jelezni.
Ezért csak akkor használjuk így, ha biztosan tudjuk, hogy van kitöltött cella a vizsgált munkalapon.
Munkalap ürességének ellenőrzése
Készítünk egy függvényt, ami paraméterként a vizsgálandó munkalapot kapja meg, vissza pedig egy logikai értéket ad.
Ha egy munkalap üres, akkor a használt tartomány címe az ‘A1’ lesz. És ha az üres, akkor a munkalapot üresnek tekinthetjük.
[vb]
Function is_empty_sheet(sname As Worksheet) As Boolean
is_empty_sheet = sname.UsedRange.Address = "$A$1" And IsEmpty(sname.Range("A1"))
End Function
[/vb]
A javított kód
[vb]
Function is_empty_sheet(sname As Worksheet) As Boolean
is_empty_sheet = sname.UsedRange.Address = "$A$1" And IsEmpty(sname.Range("A1"))
End Function
Sub find_first_filled()
If Not is_empty_sheet(ActiveSheet) Then
Range("A1").Select
Cells.Find(What:="*").Activate
End If
End Sub
[/vb]
Így az első kitöltött cella keresése csak akkor fut le, ha az aktuális munkalap nem üres.
A munkalap nem csak cellákat, hanem egyéb elemet is tartalmazhat, pl.: Grafikonok, rajz elemek, képek, stb..
A feltétel szerinti összegzés (Sumif/Szumha), illetve a megszámlálás (Countif/Darabteli) függvényeket legtöbben ismerik. Sőt, az excel 2007-től ezek több feltételt is használni képes nagy-testvérei is egyre inkább bekerülnek a köztudatba. (SumIfs/Sumhatöbb, Countifs/Darabhatöbb). Ezekkel a függvényekkel remek riportokat lehet készíteni, de azért van néhány korlát, amiket nem tudnak átlépni.
Az oktatóanyagban ezt a táblázatot használjuk példaként.
Érdekesebb kérdések ehhez a táblázathoz
A nyereséges sorok száma
Az „Északi” nyereségesek száma
Az „Északi” nyereségesek összbevétele
Északiak és Déliek együttes bevétele vagy nyeresége vagy vesztesége.
Természetesen lehetne egy „Nyereség” nevű számított oszlopot tenni a végére, de most ezt szándékosan nem tesszük.
Mi a gond az ismert feltételes összesítő függvényekkel?
A feltételek csak konkrét értékek lehetnek (konstans, cellacím vagy számítás), de olyat már nem tudnak, hogy csak azokat a sorokat vegyék figyelembe, ahol pl. a ‘B’ oszlopban nagyobb érték van, mint a ‘C’ oszlopban
A feltételeket csak ÉS kapcsolatba tudják hozni, azaz egy következő feltétel tovább szűkíti az előző feltéteknek megfelelő sorok számát. A feltételeket nem képesek VAGY kapcsolatba tenni.
Akkor mi a megoldás?
Egyik lehetőség, hogy egy külön oszlopba elvégezzük a vizsgálatot. majd ezt az oszlopot értékeljük ki. Ha pl. a nyereséges sorok számát keressük, akkor az utolsó oszlopba írhatunk egy olyan képletet, ami nullát ad eredményül, ha nincs nyereség, különben pedig egyet. Ezen oszlop összesítésével megkapjuk a nyereséges cégek számát. Ez két lépés. Egyik a segédoszlop elkészítése, és a második az összegzés.
A segédoszlop képlete lehetne egy HA() függvény, de Én ezt javaslom beírni, majd lehúzni az oszlopra:
=--(B2>C2)
A zárójelben lévő kifejezés TRUE/FALSE eredményt ad, a dupla mínusz jel pedig ezt alakítja át 0/1 értékekre.
Második lehetőség, hogy Adatbázis függvényt próbálunk bevetni, és a kritérium tartományt olyan számítással adjuk meg, ami a táblázat első sorában értékel ki. Az egész kritériumtartományt elnevezzük „Criteria” néven.
A függvény pedig a következő:
=DCOUNTA($A$1:$D$13;"Kateg";Criteria)
=DARAB2($A$1:$D$13;"Kateg";Criteria)
És újra a SUMPRODUCT (Szorzatösszeg)
Harmadik lehetőség, hogy az egészet egy képlettel oldjuk meg.
Ez a képlet belül a ‘B’ és a ‘C’ oszlopot hasonlítja össze, és egy olyan tömböt ad eredményül, aminek annyi eleme van, ahány sora a két megadott tartománynak, de ott van benne TRUE, ahol a B>C, különben FALSE. A dupla mínusz jel a logikai értékeket 0/1 értékekké alakítja. Végül a SUMPRODUCT összeadja, az eredmény a tömbben szereplő egyesek összege, azaz a nyereséges sorok száma lesz.
Az egyéni makró munkafüzet (Personal Macro Workbook) egy speciális munkafüzet, amit leginkább arra használhatunk, hogy kisebb, speciális vagy általános segéd makrókat tárolunk benne, és a napi munkánk során ezeket a makrókat használva felgyorsíthatjuk a munkánkat. Néhány példa:
A mai dátum beillesztése a kijelölt cellákba
Az aktív munkafüzet útvonalának kiíratása (honnan nyitottuk meg)
Aktuális kijelölés képleteinek értékké alakítása
A kijelölés közepére igazítás
Aktuális táblázat formázása adott sablon szerint
stb…
Hol van a helye? – XLSTART
A nevében is benne van, hogy személyes, azaz ha egy gépet többen használunk, akkor mindenkinek lehet saját ilyen makró füzete. Emiatt a személyes felhasználói profilunkban van a helye.
A saját gépünkön aktuális helyet meg is kérdezhetjük az Exceltől:
ALT-F11-el lépjünk át a Visual Basic szerkesztőbe
Ctrl-G kombinációval hívjuk elő az Excel parancssorát (Immediate Window)
Írjuk be a következőt: ?Application.StartupPath
Válaszként megkapjuk a helyes útvonalat
Az AppData mappa alapból rejtett, ezért ha meg is szeretnénk nézni ennek a mappának a tartalmát, akkor be kell kapcsolni a rejtett fájlok és mappák megjelenítését.
Mi ez a mappa?
Ez a mappa arra való, hogy az ide helyezett munkafüzetek, vagy azokra mutató parancsikonokhoz tartozó munkafüzetek az Excel indításakor automatikusan megnyílnak.
Ha ezen a helyen létrehozunk egy PERSONAL.XLSB fájlt ( bináris munkafüzet), akkor az Excel ezt felismeri, és tud bele rögzíteni makrókat, illetve tudunk belőle futtatni makrólat attól függetlenül, hogy különben éppen melyik munkafüzettel dolgozunk.
Az XLSTART mappa az Exel telepítésekor legtöbbször automatikusan létrejön, és üres. Ritkábban, de előfordul, hogy az XLSTART mappa sem jön létre telepítéskor. A személyes makró munkafüzet tehát alapból nem létezik, azt nekünk kell létrehozni.
A PERSONAL.XLSB létrehozása
Erre három módszert is használhatunk
Egy üres munkafüzetet nyitunk, és elmentjük bináris fájlként, PERSONAL néven az előbb megadott helyre
Rögzítünk egy akármilyen makrót a személyes makró füzetbe. Ilyenkor az excel létrehozza a fájlt.
Egy másik felhasználó profiljából vagy akár egy másik gépről származó fájlt bemásolunk ide
A PERSONAL.XLSB rejtetten nyílik meg, azaz nem látszódik az ablaka, csak a Visual Basic felületen látható, hogy nyitva van. A rejtettségét mi tudjuk beállítani a Nézet (View) szalagon az elrejtés/felfedés (hide/unhide) parancsokkal.
Példa makró
Legyen itt egy példa makró, amit a videó alapján létrehozott PERSONAL.XLSB fájlba illesztve ki is tudunk próbálni. A makró egy ablakba kiírja az aktuális munkafüzet elérési útját.
Egy korábbi anyagban arról volt szó, hogy ha lehetséges, kerüljük a cella egyesítést (Merge Cells), és helyette inkább a kijelölés közepére igazítást (Center Across Selection) használjuk.
Mivel ennek a menüpontnak az elérése több kattintást igényel, így érdemes lehet a PERSONAL makrómunkafüzetbe tenni egy olyan makrót, ami elvégzi az igazítást.
Az egyéni makró munkafüzet (PERSONAL.XLS(B)
Az egyéni makró munkafüzetről egy külön oktatóanyagban van szó. Röviden, ez egy olyan speciális makrófájl, ami ha létezik, akkor az Excel indításakor automatikusan megnyílik, de rejtett ablakban. A benne lévő makrók pedig láthatóvá, használhatóvá válnak. Kisebb segéd makrókat szoktunk bele tenni, amik felgyorsítanak egyes műveleteket, és hagyományos módon csak körülményesen tudnánk megcsinálni. Ilyen például egy cella tartalmának a kijelölés közepére igazítása.
Lépjünk át a Visual Basic Editor felületére (ALT+F11)
Ha nem látszódik, Kapcsoljuk be a Project Explorer ablakot (Ctrl+R). Ha még nem létezik a PERSONAL makrófüzet, akkor létre kell hozni az említett tutorial alapján.
A PERSONAL makrófüzetbe illesszük be a következő kódot.
[vb]
Sub WrapAcross()
With Selection
.HorizontalAlignment = xlCenterAcrossSelection
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.ShrinkToFit = False
.MergeCells = False
End With
End Sub
[/vb]
Használata
Egy cellába beírjuk a szöveget, majd kijelöljük azt a tartományt, amin belül középre szeretnénk igazítani.
Lefuttatjuk a makrót. Rendelhetünk hozzá billentyűkombinációt, vagy gyors elérési eszköztár gombot is . Nem egyesíti a cellákat, mégis középen látszik.
Az egész folyamatot megtekinthetjük a VIDEÓ anyagban
Gyakori feladat, hogy egy szűrt táblázatban az egyik oszlopban az adatokat teljesen vagy részben másikra szeretnénk cserélni. Ezt kézi szerkesztéssel nagyon egyszerű megtenni, de ha a feladatot makró kódon belül szeretnénk megoldani (mert pl. egy nagyobb feladat része), akkor már nem annyira egyértelmű a helyzet.
Volt már egy korábbi anyag arról, hogy ha szűrt tartományba szeretnénk a vágólapról beilleszteni adatokat, de most nem erről van szó, hanem egyszerű felülírásról.
Kézi módszer
Ez nagyon egyszerű, hiszen már jópár verzió óta az Excel kiválóan kezeli ezt a problémát.
Leszűrjük a táblát
A módosítandó oszlopban az első elemet átírjuk
Lehúzzuk az aljáig
Ha megszüntetjük a szűrést, akkor látni fogjuk, hogy csak az előzőleg szűrt adatok módosultak, a közbensők, amik a szűréskor rejtve voltak, nem változtak.
Makró – VBA módszer
Ha az előző műveletsort rögzítjük makróval, akkor a kódból nagyon gyorsan kiderül, hogy általánosan nem használható, tehát változó adattartalom esetén, ahol a szűrés más-más eredményt adna. Emiatt ezt a problémát kicsit komolyabban kell leprogramozni.
Elméleti háttér
Ha egy oszlop szűrt adatokat mutat, és abban módosítani szeretnénk, akkor előbb ki kell jelölni CSAK a látható cellákat.
Kijelöljük a teljes, szűrt táblázatot (Ctl-A)
Speciális kijelölés -> Csak a látható cellák
Így viszont az oszlopban lévő adatok szétválnak egymástól, és ha újra kinyitjuk, azaz megszüntetjük a szűrést, akkor több, nem összefüggő tartományt fogunk látni.
Tehát a makróval a kategória oszlopban lévő, látható tartományokat kell módosítani. Példánkban a „Beverages” szót, másik kategória névvel.
A Selection objektum
Ha több, nem összefüggő tartományt jelölünk ki, vagy hozunk létre a fenti módszerrel, attól az még a SELECTION objektummal lefedhető, és egy lépésben átírható a benne lévő összes cella értéke.
A Fejléc problémája
Mivel előre nem tudjuk, hogy milyen sorok fognak beleesni a szűrésbe, legegyszerűbb, ha a fejlécet is belevesszük a kijelölésbe. Viszont így annak az értéke is felülíródik. Ezt úgy oldjuk meg, hogy a felülírás előtt elmentjük a fejléc eredeti értékét, majd a végén visszaírjuk.
A VBA kód
A példában egy általánosabb megoldást adok, ahol a subrutin argumentumként kapja meg azt, hogy melyik oszlopban kell dolgoznia, és mire kell cserélni a szűrt elemeket.
A futás előtt feltételezi, hogy már a szűrés megtörtént, és az aktív cella a módosítandó oszlop fejlécén áll.
[vb]
Sub Replace_filtered_data(header_cell As Range, new_value As Variant)
Dim h As Variant
‘A fejléc tartalmának megjegyzése
h = header_cell.Value
‘A módosítandó oszlop kijelölése fejléccel együtt
Range(header_cell, header_cell.End(xlDown)).Select
Az anyagban megmutatom, hogy lehet képlettel megoldani azt a feladatot, amikor a sor, illetve oszlop azonosítók alapján egyszerre kellene összesítést vagy megszámlálást csinálni. Volt egy korábbi anyag, ahol 2D keresést mutattam, de ez más.
Íme a probléma
Tehát ugyanazok az értékek ismétlődgetnek a fejlécben, mint az első oszlopban. Nekünk pedig az alsó, összesített tábálázatot kell összehozni
Hű, ez nagyon érdekes. nem tudom, hogy mennyire gyakori probléma, de Nekem annyira megtetszett, hogy azt éreztem, ezt mindenképpen fel kell tenni az Excel Bázisra.
PIVOT?
Mivel ismétlődő fejléc elemek vannak, PIVOT-al nem lehet megcsinálni. Legalább is Én nem találtam rá megoldást. ha lenne valaki, aki IGEN, akkor commentben ossza már meg velünk!
MACRO?
Igen, makróval meg lehet csinálni. Egymásba ágyazott ciklusokkal. Már ha érted mire gondolok. Ez nagyobb táblázat esetén meglehetősen lassú, ráadásul tudni kell programozni, és a VBA nyelv ismerete is szükséges.
A megoldás – Képlet – SUM vagy SUMPRODUCT
Nem gondolnád, Igaz? Nézzük a feladatot konkretizálva az ‘A‘ kategóriára: Összesíteni kell az adattartomány azon elemeit, ahol a fejlécben ‘A‘ van és a sor elején is ‘A‘ van. Mivel az egyik feltétel vízszintesen van, a SUMIFS (Darabhatöbb) függvény nem jön számításba. Tömbökkel kell dolgoznunk, így esélyes, hogy tömbképlet lesz a megoldás.
A SUMPRODUCT (SZORZATÖSSZEG) remek függvény. Kiválón alkalmas hasonló módon több feltétel szerinti összegzésre. Amíg nem volt 2007-es Excel, és nem nem volt SUMIFS (DARABHATÖBB), addig így lehetett csak hasonló problémákat megoldani.
Hogy működik?
Hát, ezt írásban meglehetősen hosszadalmas lenne elmagyarázni. Inkább nézd meg a videót. Abban elmondom, hogy mi a működés alapja, és megnézzük az összegzésen kívül a megszámlálást is.
Gyakran felmerül az igény, hogy egy munkalapon a képleteket tartalmazó oszlopokat értékké alakítsuk. Ennek több oka is lehet:
Nem szeretnénk, hogy lássák a képleteket
Az értékké alakítással gyorsítjuk a működést, mert az Excel-nek nem kell állandóan újraszámolni a képleteket
A probléma
A másolás / érték beillesztés műveletet mindenki ismeri. A baj az vele, hogy csak egy, összefüggő tartományban működik. Hiába jelölünk ki több tartományt pl. a Ctrl lenyomása mellett. A másolás is csak akkor működik, ha azonos a kijelölt területek sorainak a száma. De saját magára értékként nem lehet beilleszteni két egymással nem összefüggő területet.
A megoldás
A megoldás egy pár soros makró, ami kijelöli a képleteket tartalmazó cellákat (sorokat/oszlopokat), majd egy ciklussal végiglépked a kijelöléskor képződött területeken, és mindegyiket értékké alakítja.
[vb]
Sub keplet_helyett_ertek()
Range("a1").Select
‘Képleteket tartalmazó tartományok kijelölése
Selection.SpecialCells(xlCellTypeFormulas, 23).Select
‘A keletkezett területek bejárása és képlet-érték csere
For Each akt_range In Selection.Areas
akt_range.Formula = akt_range.Value
Next
End Sub
[/vb]
A fenti makrót másoljuk egy makró-munkafüzet egyik moduljába, vagy a Personal (Egyéni) makró munkafüzet ,megfelelő moduljába, és máris kipróbálhatjuk.
A kapcsolódó videóban láthatjuk a folyamatot és a működést is. Ezen kívül mi van még a videóban?
Speciális kijelölés lehetőségei
Képleteket tartalmazó cellák kijelölése
Egyéni makró munkafüzet létrehozása (ha még nincs)
Az ‘autofill’ eszközt mindenki ismeri. Rengeteg dologra lehet használni, de ebben az esetben egy érdekes problémára mutatnék három megoldást.
Mit tennénk akkor, ha az ANGOL ABC-t szeretnénk a kitöltő eszközzel megoldani egy tartományban?
Egyéni lista – A triviális megoldás
Az első lehetőség adja magát. Egyéni listát (Custom List…) hozunk létre. Ezzel úgy fog működni a dolog, mint a hónapok vagy a hét napjai esetén. Ehhez egyszer le kell írni a listát egy tartományba, és onnan importálni.
Az egyéni listák szerkesztése Excel verziónként különböző helyen van az Excel beállítások között.
Excel 2007: Népszerű elemek -> Egyéni listák szerkesztése…
Excel 2010: Speciális -> Általános szakasz -> Egyéni listák szerkesztése…
Ha nagy nehezen megtaláltuk, akkor a beállító párbeszédablak már ismerős lehet, ez nem változott az Excel 2003-as, vagy talán még korábbi verziók óta.
Begépeljük az ABC betűit egymás alá
Beimportáljuk egy Egyéni listába
Ennek a megoldásnak az előnye az, hogy csak egyszer kell megcsinálni az importot, és bármely munkafüzetbe használhatjuk. Csak be kell írni egy cellába az ABC bármely betűjét, és lehúzni.
Kitöltés képlettel – első lehetőség
Ez a megoldás azon alapul, hogy a számítógép valójában a betűk kódját használja. Az „A” betű kódja a 65, a „B” betűé a 66, és így tovább. Az angol ABC-ben 26 betű van, az utolsó „Z” kódja a 90.
Kisbetűknél az „a” kódja a 97, a „z” kódja pedig a 122.
Az Excel CHAR() függvénye (magyar verziója a Karakter ) a kódból betűt állít elő. Az „A” betűt pl. így kapjuk meg:
=CHAR(65)
=KARAKTER(65)
Hogy lehúzható és automatikusan kitöltődő legyen, a 65 helyére egy 65-től folyamatosan növekvő képletet kellene találni, ami a következő:
=CHAR(ROW(A65))
=KARAKTER(SOR(A65))
A fenti képletet beírva egy cellába, majd lehúzva, máris megvan az ABC.
A SOR ( ROW ) függvénynek bármely 65. sorban lévő cellát adhatjuk kezdőértéknek.
B65, C65, stb..
Kitöltés képlettel – második lehetőség
Itt azt csináljuk, hogy a kezdőbetűt beírjuk a lista elejére, és a következőket már számoljuk úgy, hogy az előtte lévő kódját képezzük, majd ahhoz egyet hozzáadva visszaalakítjuk betűvé. Így kapjuk a következő betűt.
1. Egy cellába(mondjuk C2-be) írjuk be az „A” betűt
