Az Excelben általánosságban elmondható, hogy a Conditional Formatting alatt elérhető ikonkészletek vagy éppen az Insert Ribbonfül Symbol parancsával beszúrható szimbólumok tárháza elképesztően hatalmas, mindennemű alapelváráshoz és általános feladathoz találhatunk itt nekünk megfelelő ikonokat. Na de mi van akkor, ha mi mégsem ezeket szeretnénk használni, hanem például az általunk interneten talált képeket, emotikonokat vagy egyéb ikonokat akarjuk egy táblázatunkban bizonyos státuszokhoz rendelni? És most itt akár az arcképünkre vagy bármilyen más képre is gondolhatunk, a következő kis módszerrel akármilyen képekkel végrehajtható módosításról van szó.

Adott tehát egy táblázatunk, amelynek az egyik, kisegítő sheetjére beszúrjuk azon képeket, amelyeket fel szeretnénk használni, az én esetemben ezek most képformátumú emotikonok:

A számok a képek mögött a státuszt jelentik, de ez akár lehet szöveg is, most azonban az egyszerűség kedvéért egy számos skálát alkalmazunk.

Van egy egyszerű kis táblázatunk is, amelyben megyénként kitűzött bevételi célokat, eredményeket és ez alapján kiértékelt státuszt láthatunk:

A feladat végrehajtása érdekében már kapásból kezdhetünk is egy segédoszlop beszúrásával. Erre azért van szükség, mert itt fogjuk meghatározni az adott cél-eredmény kombinációra vonatkozó státuszértéket (a fentebb említett skálának megfelelően). Ezt a következőképpen tesszük meg:

=MATCH((C2/B2)*100,{0,95,99,105})

A rendszeres Olvasóknak és gyakorlott felhasználóknak valószínűleg ezt már nem kell magyarázni, de röviden azt tesszük, hogy az eredményünket százalékosítjuk (ez lesz a MATCH függvény első paramétere, mint lookup_value), majd besoroljuk a második paraméterként megadott tartomány értékei alapján - így pedig a skálának megfelelő értékeket fogjuk visszakapni mindenféle százalék esetén. Értelemszerűen mondjuk 94%-os teljesítési aránnyal egyest kapunk, 100%-os teljesítéssel pedig hármast.

Ezután a Státusz oszlopba a segédsheetről másoljuk be ide az értéknek megfelelő kis képformátumú ikonunkat (de bármelyiket átmásolhatjuk, nincs jelentősége):

Majd rákattintunk és a Formulas Ribbonfül Defined Names szekciójában nevet adunk neki (Define Name), legyen mondjuk ez Status:

És itt jön az egész történet kulcsa, a korábban már itt is többször bemutatott INDEX-MATCH párosítás, amelyet hozzárendelünk ehhez az elnevezett elemhez:

=INDEX(HelpSheet!$A$1:$A$4,MATCH(Sheet1!$D$2,HelpSheet!$B$1:$B$4,0))

Ahogy azt már megtanultuk korábban, az INDEX egy megadott tömbben megadja a megadott sor és oszlop találkozásánál lévő értéket, MATCH pedig az első paramétereként megadott érték helyzetét fogja a második paramétereként megadott tartományból megadni nekünk (a harmadik paraméter a pontos egyezés, kisebb-nagyobb feltétel megadására ad lehetőséget).

Így értelemszerűen az INDEX függvényünk oszlop-paramétere a HelpSheet ikonokat tartalmazó A oszlopa lesz, az ezen oszlopból szükséges sor számát pedig a beágyazott MATCH eredménye adja - amely ugyebár az eredeti sheet Segédoszlopába kerülő státuszérték HelpSheet B oszlopában lévő sor száma lesz.

Azaz ha a státuszértékünk 3, akkor a HelpSheet A oszlopában a harmadik sorban szereplő ikont kapjuk vissza.

Ennek a trükknek azonban van egy óriási problémája, ami miatt túlságosan nem is javasolt belemélyedni - ez pedig az, hogy az adott sorba az alapikont mindig nekünk kell beszúrni, plusz minden egyes beszúrt ikonhoz nekünk kell definiálni a nevet és a hivatkozott formulát. Erre mondjuk VBA-ban lehetne kódsort összehozni, de annyira valószínűleg már nincs szükségünk customizált ikonok használatára.

Ha bármit automatizálunk, customizálunk (nehéz már túllendülni ennek a szónak a magyarosított használatán), mindig fontos a megfelelő dokumentáció, egyrészt a későbbi fejlesztések, hibajavítások érdekében, másrészt a felhasználók segítésére. Ez utóbbiból mutatok be egy apró, szine minimális időráfordítással végrehajtható kis módosítást. Tehát szépen megírtuk a saját kis custom funkciónkat VBA-ban - az én példámban ez most egy végletekig egyszerű kis kódsor:

Értelemszerűen ez a funkció az első paramétereként megadott számot összeszorozza a második paramétereként megadott számmal, majd az eredményt az aktuális cellába írja be.

A Visual Basic Editorban a View menüre kattintva hívjuk meg az Object Browsert (ezt megtehetjük egyébként simán egy F2 megnyomásával is):

Itt válasszuk ki a VBAProject könyvtárat a felső legördülő menüből:

Az itt felugró osztályok listájából értelemszerűen válasszuk ki a megfelelő funkciót majd a jobb gombbal való kattintás után felugró menüből válasszuk ki a Properties menüpontot:

Itt szépen megadhatunk egy kis leírást a funkciónkhoz:

Ha ezután pedig egy másik egyetlen soros makróval hozzáadjuk a funkciónkat valamelyik formula-kategóriához, akkor meg is fogjuk látni, hogy miért jó ez a leírás. A kis makrónk kulcsa a következő sor:

Application.MacroOptions Macro:="szorzas", Category:=3

Az Application.MacroOptions metódust használhatjuk fel arra, hogy az Insert Function párbeszédablakot megbuheráljuk - rengeteg paramétere van, a Macro paraméterrel a makrónk nevét adjuk meg, a Category paraméter pedig értelemszerűen azt határozza meg, hogy melyik függvénycsoportba helyezzük el a funkciónkat (a hármas a matematikai függvények csoportja). De adhatunk itt meg billentyűkombinációt vagy akár a paramétereinkhez is leírást, szóval elég nagy a lehetőségeink tárháza.

Ha szükség van rá, természetesen arra is van mód, hogy custom függvénycsoportot hozzunk létre. Mindenesetre mi most maradunk az egyszerűbb megoldásnál és a fenti makró lefuttatásával (akár indításhoz is rendelhetjük és így tovább), máris láthatjuk az Insert Function listában a "szorzas" névre hallgató funkciónkat és még a leírásunkat is megtaláljuk a párbeszédablakban:

Viszont arra, hogy custom funkciónkhoz felugró kis tippeket rendelhessünk hozzá, egyszerű megoldás nem áll rendelkezésünkre. Erre a kis sárga felugró ablakocskára gondolok:

Ha valaki mégis tud egy ilyen megoldást, bátran dobja be a közösbe, mert az általam ismert megoldás egy custom megoldás, azaz egy speciális add-int kell feltelepítenünk hozzá. De ettől független egyéb ötletek és kommentek jöhetnek bátran.

Nem ez lesz az első és valószínűleg nem is az utolsó alkalom, amikor az Excel VLOOKUP függvénye lesz terítéken egy posztban a blogon, hiszen szerintem az egyik leggyakrabban használt függvényről van szó a táblázatkezelőben, amelyet ráadásul elég rugalmasan lehet használni. Az rengetegszer elhangzik, hogy legnagyobb hibája az, hogy kizárólag balról jobbra tud keresni, fordított esetben mindenképpen az INDEX és MATCH függvények együttes használatára van szükség. Még én is írtam róla ITT.

Igen, mondhatjuk akár azt is, hogy ez már ezer éve lerágott csont és mai makrós világunkban egy valamire való hozzáértő fejből megírja VBA-ban egy ilyen helyzet lekezelését, de én mégis kitartok amellett, hogy fontos megismernünk a formulákat és ha lehetséges, használjuk is őket.

A mai posztban ugyanis egy másik megoldást is megmutatok arra, hogy hogyan lehet a VLOOKUP-ot arra ösztökélni, hogy jobbról-balra is keressen - mindössze egy beágyazott CHOOSE függvényre van ehhez szükségünk.

Adott a következő feladat:

Maga a megoldás roppantul egyszerű lesz, egyetlen új dolgot kell hozzá megértenünk, méghozzá a CHOOSE függvény és a tömbök kapcsolatát. Hiszen ennek a formulának a legegyszerűbb felhasználási módját valószínűleg mindenki ismeri, de senki nem használja:

=CHOOSE(2,"Tamás","Dániel")

Értelemszerűen ez a fenti a Dániel eredményt fogja megadni nekünk, hiszen a CHOOSE alapesetben az első paramétereként megadott "indexnek" megfelelő értéket fogja visszaadni a második, harmadik stb. paramétereként megadott értékhalmazból.

Tömbök esetén már viccesebb a helyzet, hiszen mondjuk:

=CHOOSE({1,2},A:A,B:B)

értelemszerűen az 1 és a 2 indexek mindkét lehetőségét megvizsgálja, ergó létrehoz nekünk egy két oszlopból (A-ból és B-ből) álló táblát. Ha viszont kicsit bedurvulunk és a következőt adjuk be a formulába:

=CHOOSE({1,2},A1:B3,C1:D3)

Ez egy olyan kétoszlopos táblát fog létrehozni, amelynek első oszlopába az A1:A3 (hiszen az első paraméter egyes indexével az első értékparaméter első oszlopát hivatkoztuk meg), második oszlopába pedig a D1:D3 értékek kerülnek (hiszen az első paraméter kettes indexével a második értékparaméter második oszlopát hivatkoztuk meg).

És ha most átgondoljuk a feladványunkat, máris látjuk, hogy miért volt jó ez nekünk - a CHOOSE segítségével létrehozhatunk egy olyan táblát, amelynek első oszlopa az eredeti táblában szereplő név-oszlop lesz, második oszlopa pedig a város neve.

Valahogy így:

CHOOSE({1,2},E3:E7,C3:C7)

Túlságosan meg sem kellett bonyolítani, hiszen annyit kellett csak tennünk, hogy előbb írtuk be az E3:E7 tartományt paraméterként, mint a C3:C7 tartományt.

És innentől kezdve erre szépen ráereszthetjük a VLOOKUP-ot is:

=VLOOKUP(I2,CHOOSE({1,2},E3:E7,C3:C7),2,0)

Tehát az I2 cellában megadott nevet megkeresi abban az ideiglenes, CHOOSE által megalkotott táblában, amelynek első oszlopa a név, második oszlopa pedig a város. Ugye milyen egyszerű?

Sajnálatos módon egyéb elfoglaltságok vagy éppen más problémák fejtegetése okán időnként előfordul, hogy egy-egy Olvasó levelére nem vagy csak jelentős késéssel tudok válaszolni - a következő posztban is egy ilyen felvetésre fogok megoldást kínálni, a szomorú a történetben, hogy ez a kérdés már sok héttel ezelőtt érkezett hozzám. Elnézést ezért.

A probléma elég egyszerűen bemutatható - adott a következő táblázat:

Fontos észrevétel, hogy az összes érték kalkulált érték, tehát más cellák értékeivel végrehajtott műveletek után "érkeznek" meg a célcellákba. Ha ebből most megcsináljuk a legegyszerűbb vonaldiagramot az Insert Ribbonfül Charts szekciójából, akkor valami ilyesmit kapunk:

Látható, hogy amíg van értékünk, szépen haladnak a vonalaink, viszont az aktuális hónaptól kezdve a jövőt érintő időszak nullái simán leugranak a diagramunk aljára. A manuális kézzel történő frissítgetés nem elfogadható megoldás, hiszen bár a mostani kis mintatáblán ez nem tűnik nagy feladatnak, egy jóval komplexebb táblában már lenne vele szenvedés.

De akkor mit csináljunk? Ki fogjuk használni a diagramjaink azon funkcióját, hogy például egy #N/A üzenettel nem igazán képesek mit kezdeni, ezért azt fogjuk elérni, hogy a 0-k helyett formuláink #N/A értéket vegyenek fel, ha nincs még érték az inputcellákban.

Tehát az eredeti formulánk helyett egy beágyazott IF formulát használjunk:

=IF(eredeti formula=0,#N/A,eredeti formula)

Azaz ha az alapformulánk eredménye nulla, akkor #N/A legyen az eredmény, egyébként az eredeti formula dobja vissza az értékünket.

Persze az is elképzelhető, hogy alapból is lehet nulla a számított értékünk, ebben az esetben a dátummal való machinálást javasolnám, azaz a mai dátumhoz képest jövőbeni dátumok adjanak vissza #N/A-t.

Jelen példánknál maradva szépen alkalmazzuk a fentebb bemutatott beágyazott IF formulát és máris látjuk az #N/A-kat szépen sorakozni táblánkban - de ami fontosabb, a vonaldiagramon a vonalak ott érnek véget, ahol kell nekik.

Aki elég régóta gyűri az Excelt, az valószínűleg még emlékszik a 2007-es illetve azelőtti verziókból a SUBTOTAL funkcióra, ami ugyebár lehetővé tette, hogy elrejtett sorok vagy éppen hibakódot tartalmazó cellák esetén is megfelelően összegezhessünk vagy éppen átlagolhassunk (a funkció még most is létezik későbbi verziókban is, de nem igazán látszik felvenni a versenyt következő jelentkezőnkkel). Aki akkoriban használta ezt a funkciót, az valószínűleg az AGGREGATE funkció 2010-től kezdődő evoluciójával is tisztában van, viszont akinek ezekre még nem volt szüksége, az biztos hasznosnak fogja találni a sokak által az Excel egyik legösszetettebb formulájának tartott AGGREGATE bemutatását.

Anélkül, hogy már ezer és ezer helyen meglévő információkat akarnék duplikálni, érdemes megnézni, hogy mit is csinál ez a függvény - adathalmazunkban tudunk vele úgy összegző funciókat használni, hogy a rejtett vagy errort tartalmazó sorokat figyelmen kívül hagyhatjuk.

Magának a formulának van egyszerű referenciafüggvényként illetve tömbfüggvényként történő alkalmazása is, a paraméterek eszerint változnak, de alapvetően mindkét irány teljesen magáért beszél. Amúgy is a formula begépelése szó szerint végigvezet minket az alkalmazáson is, legyen szó bármelyik felhasználási módról.

Tehát ezek lennének a kötelezően illetve opcionálisan megadható paraméterek tömb- vagy referenciafüggvény felhasználás esetén:

=AGGREGATE(function_name,options, array, [k])
=AGGREGATE(function_name,options, ref1..)

Értelemszerűen a felugró listából kiválasztjuk a minket érdeklő függvényt - ez lesz a function_name sorszáma:

A vessző után az options paraméter mezőre lépve szintén felugró listából kiválasztjuk, hogy pontosan mit hagyjon figyelmen kívül a függvény - ez lesz az options sorszáma:

Ezután dől majd el, hogy tömbfüggvény vagy szimpla hivatkozás irányába mozdulunk el, hiszen szimplán beadhatjuk azon tartomány hivatkozását, ahol az AGGREGATE függvényt használni szeretnénk, de például LARGE függvény esetén még azt is meg kell adnunk, hogy a sorban hányadik legnagyobb itemre vagyunk kíváncsiak, így értelemszerűen nem lesz elég a referenciafüggvény sorban megadott ref1, hanem az array, [k] megoldásra lesz szükségünk, ahol [k] a nagyság sorrendjében elfoglalt helyezés lesz.

A fentebb említett összetettség tehát nem a formula értelmezéséből, hanem az általa nyújtott lehetőségekből adódik, hiszen bőven 150 felett van a felhasználható irányaink száma - alkalmazása ezzel szemben olyan egyszerű, mint egy faék, elég ha a következő két printscreent megnézzük és máris biztosan egyértelmű lesz minden kérdésünk.

Ahogy látható is a képeken, az eredeti függvények egyszerű felhasználásával esélytelenek vagyunk, viszont AGGREGATE bevetésével mind a 19 függvényt működésre tudjuk bírni - lehet persze IF és ISERROR felhasználásával más megoldást is kitervelni, de a rejtett sorok és hibák ennyire egyszerű és hatékony megoldásánál nem nagyon látok jobbat formulák terén.

Persze ki kell emelni azért a függvény hibáit is: csak sorokban tud gondolkodni, oszlopokban nem (ergó egy elrejtett oszlop semmit sem jelent a formulának, illetve ha bármelyik hivatkozás "háromdimenziós", az szintén megpadlóztatja a függvényt.

Napjainkban már elég ritka az olyan Excel-dashboard, amely nem tartalmaz legalább egy linket, akár egy Home névre hallgató sheetre, akár egy rendszerre mutatva, de sűrűn előfordul az e-mailek használata is. Így ma ismét egy kicsit az Exceles hivatkozásokkal fogunk foglalkozni, amelyek legegyszerűbb alkalmazási módját, a Hyperlink függvényt valószínűleg minden idelátogató ismeri:

Ha nem szeretnénk használni az Insert Ribbonfül beépített Hyperlink funkcióját, akkor arra is van módunk, hogy az Excel-fájlon belül hivatkozzunk mondjuk egy másik sheet egyik cellájára az előbb már idézett Hyperlink formulával. Itt jön képbe a sokszor zavart okozó # karakter, amely a Hyperlink formulában megmutatja a programnak, hogy "házon belül" fogunk linkelni. Azaz példánknál maradva:

=HYPERLINK("#1!A1","1st sheet")

Ez fájlunk "1" névre hallgató sheetjének A1-es cellájára fog hivatkozni, majd a hivatkozásnak a "1st sheet" nevet fogja adni.

Teljesen ugyanezzel a módszerrel tudunk egy nevesített tartományra is lépni:

=HYPERLINK("#Hazaklistaja","Hazaklistaja") - ez például a Hazaklistaja nevezetű rangere fog hivatkozni.

Egy teljesen másik Excel-fájlra hivatkozhatunk a fentebb már említett beépített Insert Ribbonfül Hyperlink funkciójával is, de folytatva eddigi elmélkedésünket, használhatjuk a Hyperlink függvényt továbbra is:

=HYPERLINK("..\Masikexcelfajl.xlsx","MasikExcelFajl")

Ez a formula egy könyvtárral feljebb lép a struktrúrában és abban a folderben fogja megnyitni nekünk a Masikexcelfajl.xlsx névre hallgató fájlt, majd a második paraméterben megadott névre nevezi ezt a hivatkozást.

Na de ha megvan már a sok szép hivatkozásunk és most el akarjuk őket távolítani, akkor mit teszünk?

Ugyebár van az egyik, elég egyszerű módszer, amikor szimplán jobb gombbal kattintunk a cellákon, majd nyomunk egy Remove Hyperlinks parancsot:

VBA-val majdnem ugyanilyen egyszerű a történet, hiszen simán írhatunk rá egy apró kis kódsort, ugyanis ennyi lenne az egész:

Sub Torles()
Selection.Hyperlinks.Delete
End Sub

Sokkal akkor sem bonyolultabb a dolog, ha egy Excel-munkafüzet összes sheetjének összes linkjét szeretnénk eltávolítani, hiszen akkor minden ciklusok egyik legegyszerűbbjére van szükségünk, valami ilyesmire:

Sub Test()
Dim sheet As Worksheet
For Each sheet In ThisWorkbook.Worksheets
sheet.Hyperlinks.Delete
Next
End Sub

De ezeknél az egyszerű kis feladványoknál tudjuk magasabbra is tenni a lécet, hiszen a Pivot-táblák alapvetően nehezen boldogulnak a hivatkozásokkal, mi pedig mégis megpróbálkozunk valahogy legalább látványban linket varázsolni egy ilyen Pivotba.

Ebből a táblából szeretnénk egy Pivot-táblát készíteni:

Ha megcsináljuk az Insert Ribbonfül PivotTable parancsával, akkor a következőt tapasztaljuk:

A Home fül Styles szekciójában található Cell Styles menüpont Hyperlink stílusa segítségével kinézetre linkké tudjuk alakítani:

De ez így még mindig nem kattintható. Egy egyszerű VBA-kódra lesz szükségünk, hogy ez működjön, szóval ALT+F11 lenyomásával lépjünk gyorsan a VBA-editorba, majd a Pivot-táblát tartalmazó sheetünkön felépítünk egy egyszerű kódot.

Első lépésként nem gombnyomásra futtatható makrót fogunk létrehozni, hanem bármelyik Selection change esetén aktivizálódó kódot, azaz a makró akkor fog lefutni, ha valahova "átkattintunk" a sheeten:

Deklaráljunk két változót. Az első egy PivotField típusú változó lesz, ami a Pivot-táblánk mezőit hivatott majd tartalmazni, a második pedig azt mutatja majd meg, hogy éppen melyik mezőt akarjuk ellenőrizni a táblában. Ez utóbbit egyébként fel is tudjuk tölteni, hiszen értelemszerűen az "Oldal" mezőit szeretnénk validálni:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim pivotmezo As PivotField
Dim pivotmezoNev As String
pivotmezoNev = "Oldal"

Hiba esetén menjen a következő lépésre a kódunk:

On Error Resume Next

Ezután állítsuk be a pivotmezo (PivotField típusú) változónk értékét, méghozzá úgy, hogy csak akkor vegyen fel értéket, ha az adott cella egy Pivot-tábla részét képezi, egyébként maradjon üresen és ha üres, akkor ne is csináljon semmit a program. Ezzel azt érjük el, hogy csak Pivot-táblánk celláit vizsgáljuk.

Set pivotmezo = Target.PivotField
If Not pivotmezo Is Nothing Then

Ha a pivotmezo változó Pivot-tábla cellájára mutat, akkor nézzük meg, hogy az adott mező neve a fentebbi pivotmezoNev változóban megadott "Oldal" érték-e. Ha igen, akkor új ablakban nyissa meg az abban a cellában szereplő hivatkozásunkat.

If pivotmezo.Name = pivotmezoNev Then
ThisWorkbook.FollowHyperlink Address:=Target.Value, NewWindow:=True

Ezzel pedig el is készültünk, a stílust alapból átállítottuk Hyperlink-kinézetre, a kis kódunkkal pedig a link kattinthatóvá is válik.

Szövegként:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim pivotmezo As PivotField
Dim pivotmezoNev As String
pivotmezoNev = "Oldal"
On Error Resume Next
Set pivotmezo = Target.PivotField
If Not pivotmezo Is Nothing Then
If pivotmezo.Name = pivotmezoNev Then
ThisWorkbook.FollowHyperlink Address:=Target.Value, NewWindow:=True
End If
End If
End Sub

Aki már régebb óta gyűri ezt a verziót, ő valószínűleg együtt tud már ezzel élni, de az újonnan érkező felhasználók számára igencsak meglepő lehet Outlook 2016-ban, hogy Microsoft Exchange Server mailbox használata esetén az Inboxban már eltűnt a Click here to view more on Microsoft Exchange link a levelek alatti sávból és a program már csak az újabb elemeket jeleníti meg.

Elvileg ezt az üzenetet keresnénk:

Helyette szimplán már csak ennyit látunk:

Aki használ beállított mozgatási szabályokat vagy régebbi leveleket már nem tárol az Inboxában, azok számára ez nem okoz problémát, de aki nem így tesz és nem volt erre felkészülve, annak igazából workaround megoldás után kell néznie, mert ahogy olvastam, a Microsofttól elvileg várható egy fix kiadása, ami visszahelyezi majd a korábbi verziókból megismert linket, de ez még a kanyarban sincs.

Az átmeneti megoldások közül az első a legegyszerűbb, de valószínűleg a legkevésbé komfortos, hiszen ez azt várná tőlünk, hogy az Outlook webes applikációba lépjünk be és a weben böngésszünk leveleink között.

Napjaink tárhelykapacitásait figyelembe véve a második a legjobb talán, amelynek első lépéseként a File Ribbonfülre kattintunk, majd a felugró menüben az Account Settings almenüt válasszuk:

Itt a saját mailboxunkon állva válasszuk a Change menüpontot az E-mail fülről:

A felugró ablakban pedig a Use Cached Exchange Mode boxba tegyünk pipát (ha még nem lett volna, de elvileg defaultban ott van), majd a Mail to keep offline csúszkát húzzuk fel Allra, azaz az összes, Inboxban lévő levelünket letöltve, saját gépen tároljuk.

Nagy tárhelyigényre lesz hozzá szükség (főleg ha az egész családi fotóalbumot itt tároljuk), de vagy ezt választjuk vagy megpróbáljuk elkerülni a régi levelek bejövő postafiókban történő megőrzését.

Egyébként az offline tárolás default mérete tárhelyünk méretétől függ automatikusan, 32 GB vagy az alatti merevlemezkapacitásnál 1 hónapra van belőve a csúszka, 64 GB és 32 GB között 3 hónapra, afölött pedig 12 hónapra. Persze célszerű a leveleinket folyamatosan archiválnunk, mert már előfordult olyan eset is, hogy a tárolt levelek mennyisége olyan óriási volt, hogy a csúszka maxra húzásánál a program egyszerűen összeomlott.

Harmadik workaroundként még elvileg használhatjuk a Folder Ribbonfül View on Server funkcióját is, de ez bizonyos esetekben nem érhető el, így talán a második megoldás lehet a legjobb - persze egészen addig, amíg a Microsoft elő nem áll valamilyen új updatettel, ami visszahelyezi a jól megszokott linkünket az Inboxba.

Természetesen a fentiek csak saját elmélkedések, így ha esetleg van valakinek jobb ötlete vagy javaslata, ne habozzon megosztani.

A mai posztban ismét kicsit elszakadunk az Exceltől és visszatérünk a szívemnek oly kedves Powerpointos szerkesztgetéshez, ezúttal egy kilométeróra-kezdeményt fogunk pillanatok alatt összedobni, ami jelenleg egy elég népszerű forma, ha projektek állapotát, megoldandó esetek számát stb. szeretnénk bemutatni. A következő forma valójában nem is kilométeróra, hanem egy szaggatott köröcske, addig feltöltve egy másik színnel, ameddig szeretnénk - nettó elkészítési idejét úgy 10 percre becsülöm.

Első lépésként egy "fánkformát" az Insert Ribbonfül Illustrations szekciójának Shapes menüjéből:

Ha ez megvan:

Akkor ismét menjünk vissza a Shapes menübe, majd ezúttal egy elnyújtott téglalap-formát szúrjunk be:

Valahogy így:

Ha ez megvan, akkor szimplán CTRL+C és CTRL+V lenyomásával másoljuk le ezt az elvékonyított kis téglalapot, majd a Format Ribbonfül Arrange szekciójának Rotate menüjében állítsuk a dőlési szöget 15%-ra:

Ezt egészen addig ismételgessük a dőlési szög 15 százalékkal történő folyamatos növelésével, amíg el nem jutunk ehhez:

Ezt most jelöljük ki, majd másoljuk le:

Az egyik formát teljesen kijelölve lépjünk a Format Ribbonfül Insert Shapes szekciójában található Merge Shapes menüre, majd válasszuk ki a Subtract típust:

Tehát valahogy most így állunk.

Ezúttal a másik formánkat jelöljük ki, majd térjünk vissza az előbb már említett Merge Shapes menühöz, ám ezúttal a Fragment típust válasszuk:

A töredékesítés után ha kijelölnénk a formánkat, valami ilyesmit látnánk:

De most erre nincs nagyon szükségünk, amit tennünk kell az az, hogy a státuszunknak (feltöltendő színünknek) megfelelő mértéket kijelölünk a Fragmentált formánkból, majd lemásoljuk és áthelyezzük a másik forma mellé:

A körív nagyobb formáit leszámítva az összes többit kijelöljük majd töröljük:

Ha ezzel megvagyunk, valahol itt tartunk:

Ezután már csak színeznünk és forgatnunk kell a formákat, majd összeillesztve őket megkapjuk az eredményünket:

Nagyobb mennyiségű adat elemzésekor, főként ha ezen adatok forrása valamilyen manuális bevitel, elég gyakran előfordul, hogy azért torzul elemzésünk/diagramunk vagy kimutatásunk, mert a manuális bevitel során hiba történt, azaz mondjuk valaki eggyel több nullát írt a száma végére, vagy éppen ellenkezőleg, egy nullával felülírt egy korábban berögzített számot - igen, erre lehet azt mondani, hogy eliminálni kell ezeket a hibalehetőségeket, de előfordulhat olyan eset, amikor már csak hozott anyagból dolgozunk.

Mint például a következő kis mintatáblázat, amelynél tökéletesen látszik, hogy azért torzul az átlagunk, mert egyes cellákban nulla vagy éppen túlságosan nagy szám szerepel:

De még nem is kell hibára gondolnunk, simán megtörténhet, hogy egyszerűen azokhoz a cellákhoz vagy nincs adatunk, vagy tényleg valamilyen különös okból kiugró eredményünk van - amit elemeznünk kell tovább, mindenesetre valóságosabb átlageredményt szeretnénk kimutatni.

Erre, ahogy az Excelben szinte mindenre, több megoldási lehetőségünk van, elsőként az annyira nem ismert TRIMMEAN függvény siethet segítségünkre, amely roppant egyszerű módon, az első paramétereként megadott tartományból a második paramétereként megadott százaléknyi adatpontot kiveszi az átlagszámításból.

Tehát mostani példatáblánkat nézve:

=TRIMMEAN(D3:D19,0.25)

Ez azt jelenti, hogy a 17 városhoz tartozó értékünk lesz a tömb, majd mielőtt az átlagot kiszámolná, a függény eltávolítja az adatok 25%-át, értelemszerűen arányos módon, azaz a legalacsonyabb és legmagasabb értékekből ugyanannyit.

Jelen esetünkben ez azt jelenti, hogy 17 darab érték van a D3:D19 tartományban, ha 17-nek vesszük a 25%-át, akkor 4.25-öt kapunk, majd ha ezt elosztjuk kettővel és kerekítünk, akkor látjuk is, hogy 2 értéket fog eliminálni fentről és lentről is, így kapjuk meg a valósághoz jóval közelebb álló átlagértékünket.

Tehát van két igen fontos tudnivaló a TRIMMEAN-ről és ezen tudnivalók egyébként be is határolják alkalmazási lehetőségeit:

- mindig szimmetrikusan működik, azaz fentről és lentről is ugyanannyi értéket zár ki az átlagból
- mindig kerekít

De ami igazán megmutatja, hogy nagyjából semmi szükségünk a TRIMMEAN függvényre, az pont két másik formula, az AVERAGEIF és AVERAGEIFS, mert mindkettő meghatározott feltételek alapján képes átlagot számolni, az első értelemszerűen csak egy feltétel szerint, a második pedig akár több feltétel alapján is.

Ergó nincs szükségünk szimmetriára, egyszerűen határozzuk meg azon tartományt, amelyben helyes értékeink lehetnek és már meg is van az az átlag, amit igazán szerettük volna elérni.

Ahogy a képen látható is, AVERAGEIFS függvényt használva megadhatjuk, hogy egynél nagyobb, de tízezernél kisebb számainkból számoljunk átlagot:

=AVERAGEIFS(D3:D19,D3:D19,">1",D3:D19,"<10000")

Az első paraméter az a tartomány, ahol átlagolandó számaink szerepelnek, a második paraméter a kritérium tartománya, azaz itt akár már városaink nevére is írhatnánk feltételt, a harmadik kötelező paraméter pedig maga a kritérium - a többi pedig opcionális, feltételeink számától függően.

Persze azt ne felejtsük el, hogy VBA-ban is roppant egyszerűen végrehajtható műveletekről van szó, példának okáért az alábbi kód a G12 cellába fogja beírni a megadott tartományunk átlagértékét:

Persze, a tartomány és az eredmény is lehetne dinamikus, de most nem ez a lényeg. Ahogy látható is, a WorksheetFunction.Average metódus a lelke a történetnek, amely az ugyanilyen névre hallgató formula VBA leképezése.

És hát nem kell se programozónak, se tapasztalt VBA ismerőnek lenni ahhoz, hogy a fenti alapján lássuk a TRIMMEAN VBA-verzióját:

Azoknak persze, akik egész nap gyűrik a VBA-t és az Excelt, valószínűleg a fenti poszt minimális hozzáadott értékkel sem bír, de ha valakinek sikerült kicsit segíteni bármilyen feladatban vagy akár gondolatébresztőként volt segítség, már megérte.

És végül, ha van esetleg valakinek a fentiek után egy kicsit szórakozni az átlagolással, akkor álljon itt egy aprócska feladvány: hogy érjük el formulával (vagy akár VBA-val), hogy nagyság szerint csak minden második értékünk kerüljön be az átlagolandó populációba, majd abból számoljunk átlagot?

Elég sűrűn felmerül az az igény, hogy egy jól megtervezett Excel dashboardban vagy templateben a felhasználók csak bizonyos cellákhoz férjenek hozzá, ábrákat, képeket vagy meghatározott cellatartományokat ne legyenek képesek módosítani - legegyszerűbb módja az ilyen kérések teljesítésének valamilyen módon levédeni ezeket a cellatartományokat, de azt tudjuk, hogy a Protect mód segítségével elérhető lehetőségeink végesek, egy bizonyos szintnél jobban nem tudjuk specifikálni mit és mi ellen szeretnénk levédeni.

A következő egyszerű kis trükk ugyanazt a megoldást használja fel, mint amit egy sok héttel ezelőtti posztban a több hyperlink beszúrásánál használtuk és amelynek segítségével el tudjuk érni, hogy bizonyos ábrákra, formákra ne lehessen semmilyen formában rákattintani - egyébként ezt a módszert használhatjuk üzenet küldésére is.

A példa kedvéért gyorsan az Insert menüből beszúrok egy Rectangle formát:

Tehát ezt a formát fogjuk a felhasználóktól egy üzenet küldésével egyidőben elzárni, megakadályozzuk, hogy hozzáférjen bárki és módosítani tudja akár szándékosan, akár véletlenül.

Ehhez menjünk első lépésként a Developer Ribbonfül Controls szekciójába, ahonnan az Insert menüből szúrjunk be egy ActiveX vezérelt Labelt.

Ezt egy az egyben helyezzük az előbb beszúrt formánkra:

A beszúrt Labelre jobb gombbal kattintva előugró menüből a Propertiest válasszuk, majd a Label két tulajdonságát módosítsuk. Az egyik módosítás a Caption kitörlése legyen, a másik pedig a BackStyle Transparent típusúra történő átállítása. Ha ezeket megtettük, látjuk is nagyjából az eredményt, nyoma sincs a formánkra illesztett Labelnek.

Ha a most már nagyjából láthatatlannak számító Labelre duplán kattintunk, akkor a VBA-editorba kerülünk, ahol a Click eseményhez tudnánk kódot rendelni. Nekünk most erre nincs szükségünk, viszont ha az esemény típusát átállítjuk MouseMove-ra, akkor a Label fölé húzott kurzor esetén indíthatunk el egy kódot:

Rengeteg lehetőségünk van, a példa kedvéért most egy szimpla MsgBox utasítással kiíratok egy üzenetet a felhasználónak:

Ezután már látjuk is, hogy mi történik, ha a forma (valójában pedig a Label) fölé húzzuk az egeret.