Mielőtt kisebb kihagyás után folytatnánk a makrós projektünket, a mai napi posztban egy elég egyszerű Excel-kérdést fogunk megválaszolni, ami nem más, minthogy hogyan tudjuk elérni, hogy az Excel-celláinkban szereplő, egyenlőtlen hosszúságú tartalmainkat (lehet akár szöveg, akár szám, akár mindkettő) nullákkal vagy egyéb karakterekkel úgy töltsük fel, hogy elérjünk egy meghatározott karakterszámot.

Ha ez így elsőre kicsit komplex megfogalmazás volt, akkor álljon itt a következő példa:

Értelemszerűen a cél, hogy bármi is van az első oszlopban, duzzasszuk fel nullákkal úgy, hogy minden esetben 7 legyen a karakterek száma.

Nem egy komplex probléma, kapásból több megoldást tudna valószínűleg az olvasók többsége mondani rá, de remélhetőleg a következő javaslatok közül egyik-másik azért segíthet valakinek a feladatában.

1. Egyszerűen használjunk egy RIGHT és CONCATENATE függvényt együtt (utóbbit helyettesítsük simán a & jellel), azaz első körben fűzzük össze az adott cellánk tartalmát hét nullával (így garantálhatjuk, hogy üres cellák esetén hét nulla jelenjen meg), majd az így kialakult, legrosszabb esetben 7 nullából, legjobb esetben 14 karakterből álló cellatartalmunkból vágjuk le az utolsó hét karaktert:

=RIGHT("0000000"&C4,7)

2. Az első példa valamivel bonyolultabb verziója a REPT, LEN és CONCATENATE függvények együttes alkalmazása. REPT függvény segítségével a nullát (vagy a szükséges karaktert) megsokszorozzuk annyiszor ahányszor kell (REPT("0",7)), de a sokszorozás számából vonjuk ki eredeti cellánk karakterszámát (ezt egy LEN függvénnyel egyszerűen meghatározhatjuk: (REPT("0",7-LEN(C4))) és ezt fűzzük össze az eredeti cellával:

=REPT("0",7-LEN(C4))&C4

Emellett természetesen használhatjuk a LEN, CONCATENATE és valamiféle "darabolós" függvény további változatait is, VBA-t ilyen esetben bevetni talán már luxus lenne. Viszont felmerül még egy kérdés: mi van ha bekerül egy olyan cella is az oszlopunkba, ami már alapból több, mint hét karakter?

Az 1. megoldás levágja az első számát, ami nem igazán elfogadható, a 2. megoldás #VALUE! értéket fog visszaadni, így a biztonság kedvéért megbolondíthatjuk függvényeinket még egy IF formulával is. Így a megoldások így fognak kinézni.

1. =IF(LEN(C4)>7,C4,RIGHT("0000000"&C4,7))

2. =IF(LEN(C4)>7,C4,REPT("0",7-LEN(C4))&C4)

Kérdések, javaslatok jöhetnek a szokásos csatornákon.

A következőkben egy olyan poszttal folytatom a blogot, ami önmagában roppantul egyszerű kis VBA-kódot tartalmaz, de a terveimben szereplő következő írások alapjául szolgál majd a benne lévő kis tudás - szóval, ha valaki úgy ítéli meg, hogy túlságosan egyszerű vagy értelmetlen az itteni írás, nyugodtan ignorálja.

A probléma, amit meg fogunk oldani, szintén nem túl komplex és nem is lenne hozzá szükség VBA-ra, hiszen a lentebb látható VLOOKUPot kellene csak végrehajtanunk a Település azonosító felhasználásával:

Alt+F11 segítségével szépen menjünk csak át a VBA-editorunkba, hogy hozzá is láthassunk a rövid kódunk megalkotásához. Nem kell hozzá nagy ördöngösség, hogy belássuk, ugyanazt a Vlookup funkciót fogjuk használni VBA-ban is, mint ami elérhető a formulák közül.

Kezdésként létrehozunk két változót, Nevsor illetve Nevoszlop néven Integer típussal, később ezekben fogjuk tárolni majd a kitöltendő celláink sor- illetve oszlopszámait:

Ezután adjuk meg a kis programunknak, hogy melyik az a tábla, ahol az érték bekeresésének alapjául szolgáló azonosító van és melyik az a tábla, amelyikből felkeressük a szükséges értékünket ezen azonosító alapján - én ezeket Főtábla illetve Altábla néven neveztem el ezeket:

Ahogy látszik, a Főtáblánk azon táblánk azonosító oszlopa, amelyben az értékekre lesz szükségünk, az Altábla pedig az, ami az azonosítót és magát az értékeket tartalmazza. Folytatásként feltöltjük a Nevsor illetve Nevoszlop változóinkat, értelemszerűen az első, feltöltésre váró üres cellánk sor- illetve oszlopszámával:

Egy lépés van még hátra, méghozzá egy For..Next ciklus, hiszen mit is fogunk csinálni? Minden egyes, Főtáblánkban található cellán haladjunk végig (For Each cl in Fotabla), majd az első Sheetünk (itt bármit megadhatunk persze) Nevsor illetve Nevoszlop által meghatározott cellája legyen egyenlő egy konkrét Vlookup függvénnyel:

Sheet1.Cells(Nevsor, Nevoszlop) = Application.WorksheetFunction.VLookup(cl, Altabla, 2, False)

Látható, hogy szimplán ugyanazt a funkciót meghívjuk, amit a Formulas képernyőről érünk el, majd megadjuk ugyanazok a paramétereket:

Lookup_value = értelemszerűen a Főtábla aktuális azonosítója
Table_array = ahol keressen, ez pedig az Altablank lesz, amelynek első oszlopa az azonosító
Col_index_num = hányadik oszlop értékét írja ki találat esetén
Range_lookup = csak pontos egyezést fogadjunk-e el

Hátra már csak az a lépés marad, hogy folytassuk a ciklusunkat, azaz egyrészt növeljük meg eggyel a kitöltendő celláknál a sorok számát, majd ugorjunk a Főtábla következő cellájára.

Ahogy látható, bármilyen plusz üzenettel megspékelhetjük még ezt a kis kódot, de ez nem is létfontosságú, a lényeg, hogy ha ezt most lefuttatjuk, meg is van az eredményünk:

Tehát megvan az első lépés, a következőkben az itt megszerzett ismeretre fogunk majd építeni, ahogy elkészítünk egy nagyobb lélegzetvételű (ám önmagában még mindig csak aprónak számító) makrót.

Történelmi poszt lesz a mai, hiszen a blog fennállása során első alkalommal fogok írni a főleg az Office 365-ös család berkein belül értékesített SharePointról, amelynek rengeteg különféle verziója létezik, de ezek többsége közép- és nagyvállalkozások számára készül, kisvállalati környezetben annyira nem elterjedt a használata.

Miután a blognak nem célja Microsoft termékek történetének és alapvető céljának, felhasználási területének bemutatása, ezért aki nem ismeri az applikációt, az inkább ne olvasson tovább, hiszen a következőkben mindenféle bevezető nélkül egy konkrétan felmerült problémát vagyis inkább kérdést járunk körbe.

Aki használ SharePointot, az valószínűleg elég sok esetben használ Custom Listet, hiszen hiába az alapvetően beépített tucatnyi lehetőség (Task List, Contacts stb.), elég sokszor előfordul, hogy saját igényeink szerint szeretnénk egy listát felépíteni. Ilyenkor kreálunk szépen egy Custom Listet, amit úgy építünk fel, ahogy csak akarunk.

Viszont pont az, ami a Custom List előnye, az egyben a hátránya is, hiszen semmiféle előre definiált funkcióval nem bír, így a SharePoint Designer nélkül elérhető minimális workflow-lehetőségek (ami pl. a Task List egyik fegyvere) vagy éppen az Issue Trackerben elérhető hozzászólás-naplózás sincs alapesetben definiálva.

A mai posztban ehhez kapcsolódóan arra a kérdésre adom meg a választ, hogy tudjuk mégis a hozzászólás-naplózást bekapcsolni egy Custom List esetén egy Comment/Hozzászólás oszlopban.

Ennek előfeltétele azonban a Versioning bekapcsolása, amit az adott lista List Settings menüpontja alatt található Versioning Settings almenüből érhetünk el:

Szépen kapcsoljuk be a versioninget:

Ezután ballagjunk szépen vissza a listánkhoz, ahol hozzunk létre egy teljesen új oszlopot annak a mezőnek, amelyben a hozzászólás-naplózást szeretnénk végrehajtani. A felugró ablakban, ahol az oszlop típusát tudjuk beállítani, válasszuk a Multiple lines of text rádiógombot:

Majd ugyanezen ablak legalsó részén van két opció, ami még fontos számunkra. Egyrészt állítsuk YES-re az Append Changes to Existing Text kérdést (ezzel garantáljuk a naplózást), másrészt vegyük ki a pipát az Add to default view opció mellől, hiszen egyébként egy gyakran frissített elemünk miatt szétesne teljesen a listánk képe, annak eredményeként, hogy egy mezőbe próbálna rengeteg mondatot beerőszakolni.

Ezzel pedig el is értük azt, amit szerettünk volna, máris működik a tracking/naplózás Custom List esetén.

Ezzel a rövid poszttal tehát elindul az OfficeGuru-blog keretein belül egy SharePoint-vonal is, szóval ha bárkinek bármilyen kérdése lenne az alkalmazással kapcsolatban, bátran írjon a megszokott fórumokon.

Ha vonalkód készítésére gondolunk akármilyen célból, elsőként alapvetően mindig komoly programokra, támogatott fejlesztésekre fókuszálunk, ami helyes is, hiszen ezekkel a programokkal tényleg csak a csillagos ég a határ az ötleteinket tekintve, de mi van ha nagyon komplex kódokra nincs szükségünk, szimplán csak leveleinket, számláinkat akarjuk ellátni egy ilyen azonosítóval? Ha kutakodunk kicsit, láthatjuk, hogy rengeteg különféle fizetős és ingyenes add-in állhat rendelkezésünkre ezen céljaink megvalósításához, pedig a Word beépített funkcióként tud ilyet készíteni nekünk.

A mostani posztban az Office vonalkód-készítés alapjait mutatom be, hogy aztán egy későbbi posztban már komolyabb kérdéseket és problémákat is körbejárjunk.

Tehát tegyük fel, hogy szeretnénk borítékokat nyomtatni, amelyen valamilyen információt a cím mellett vonalkódban akarunk feltünteni. Első lépésként Excelben dobjuk össze azt az adatbázist, amely a borítékokra kerülő adatainkat tartalmazza, köztük azt is, amelyet vonalkóddal fogunk majd megjeleníteni.

Ezzel meg is vagyunk nagyjából Excelben, a létrehozott kis táblánkat aztán már bármikor frissíthetjük is majd, de most átmegyünk Wordbe, ahol a Mailings ribbonfül alatt találunk egy Start Mail Merge szekciót és azon belül egy ugyanilyen nevű funkciót:

Itt dönthetjük el, hogy pontosan mire is akarjuk felhasználni az adatainkat (pl. körlevél készítésére stb.), mi most választjuk a borítékot, hiszen arra fogunk egy vonalkódot elhelyezni. Az alapformátumot most hagyom úgy, ahogy van, de értelemszerűen a saját igényeink szerint alakíthatjuk ezt.

Létrejött a borítékunk nézete, úgyhogy következő lépésként kapcsoljuk ide az Exceles táblánkat, amelyet az ugyanezen szekcióban található Select Recipients funkcióra kattintva kezdhetünk el:

Miután a listánk már megvan, értelemszerűen a Use an Existing List opciót választjuk. Szépen felkeressük a fentebb létrehozott Excel fájlt, majd annak első sheetjét betöltjük:

Ezzel szépen aktívvá is válnak a Write & Insert Fields szekció menüpontjai, köztük az Insert Merge Field is, ahol már láthatjuk az Excelünk kis oszlopait is, mint kiválasztható körlevélmezőket:

Na de az alap körlevélkészítés helyett mi most itt váltunk egy kicsit és a legutóbbi posztban már megismert CTRL+F9-et nyomjuk le, amellyel egy ún. field code zárójelpárt szúrunk be:

A zárójelek közé a következő kódot kell beszúrnunk:

MERGEBARCODE Név QR \h 1 \q L

Nem varázslóként szedtem elő ezt a fejemből, a Microsoft kapcsolódó oldalán szépen le van írva a MERGEBARCODE és paraméterei, így a QR értelemszerűen egy QR kétdimenziós kódot fog majd létrehozni, a \h és a mögötte szereplő 1-es a kód magasságát határozza meg, míg a \q szekció a hibakezelésre vonatkozik.

Ezután fent a Preview Results szekcióban már csak az ugyanilyen nevű funkcióra kell kattintanunk és láthatjuk is a vonalkódunkat, amely a Név értékét tárolja:

Értelemszerűen vonalkód nélkül is tudunk beszúrni értékeket a kis tábláink mezői szerint, normál körlevélkészítési "üzemmódban":

Az elmúlt hetek kihagyásai okán eléggé elmaradtam az olvasói kérdések megválaszolásával, úgyhogy a következő posztokban igyekszem felzárkózni az elmaradásokkal, előre is köszönet mindenkinek a türelemért. A mai kérdés egy friss felvetés, amelyet én egy kicsit majd továbbgondolok, hogy még többen találhassanak benne hasznos dolgokat, de az alapprobléma:

Hogyan tudjuk elintézni Excelben, hogy egy bizonyos cellába beírt szövegünk felett egy vonal legyen? Mintha aláhúzott lenne, csak most a vonal a szöveg felett van.

Az első, valószínűleg eszünkbe jutó megoldás talán a Borderrel való machinálás lenne, de az sajnálatos módon nem úgy néz ki, ahogy szeretnénk, ezért muszáj kicsit kutakodnunk az Excel funkció között.

Ne számítsunk túlságosan automatikus és egyszerű megoldásra, de a következő megoldás pontosan az lesz, amire szükségünk van. A szó, ami fölé egy vonalat szeretnénk húzni, legyen a PARIS. Ne írjuk ezt még be így a cellánkba, elsőként csak egy P karaktert:

Ezután menjünk a Ribbonunk Insert füle alatt található Symbols szekció Symbol funkciójához, ahol van is egy nekünk szükséges karakter, méghozzá a (normal text) fonton belül a Combining Diacritical Marks subsetben található Combining Overline.

Szépen szúrjuk be a P betűnk után:

Aztán folytassuk ugyanígy végig a szövegünkben, előbb egy betű, majd egy Combining Overline karakter, megint egy betű, megint egy szimbólum és meg is vagyunk:

Na de hogy csináljuk meg ezt WORD-ben, ahol valószínűleg jóval gyakrabban merülhet fel erre az igényünk? Álljunk a dokumentumunkban arra a helyre, ahol a felső vonással ellátott szövegrészünk, szavunk lenne, majd nyomjunk CTRL+F9-et. Ezzel ún. field code zárójeleket szúrunk be:

Ezután a zárójelek közé szúrjuk be a következő kódot:

EQ \x \to()

Mit is jelent ez? Rengeteg kód van, ami használhatunk, az EQ az egyenletek létrehozására szolgál, tehát EQ kóddal egy egyenlet kódját kezdjük el gépelni és ennek az EQ kódnak a rengeteg paramétere közül az \x létrehozna egy bordert az egyenletünkben, \to pedig létrehoz egy felülvonást a zárójelben megadott szöveghez.

Következő lépésként tehát a \to zárójelébe írjuk be a szövegünket:

Ezután jelöljük ki az egész kódot és a szövegünket, majd jobb gomb lenyomása után a felugró menüből válasszuk ki a Toggle Field Codes nevűt:

És kész is vagyunk!

A mai rövid, "visszatérő" posztban két egyszerű Outlook kérdésre próbálom megadni a választ - mindkettő roppant zavaró és a kikapcsolásuk önmagában nem túlságosan magától értetődő.

Hogyan lehet eltüntetni a leveleink tetején megjelenő Apps Bart?

Ahogy a képen is látható, ez lenne az a kékes csík, amely különféle applikációkat és lehetőségeket tartalmaz, úgy mint Bing Maps, Actions vagy éppen Suggested Meetings néven.

Elsőként fontos tudni, hogy ez az opció csak akkor működik, ha van Exchange szerverünk beállítva, ellenkező esetben ezzel a kérdéssel nem is fogunk szembesülni. És ha van Exchange szerverünk beállítva, akkor a megoldás is könnyen adja magát, egyszerűen csak a File menübe kell lépnünk, ahol szép nagy betűkkel látszik középen a Manage Apps funkció.

Ezzel eljutunk arra az oldalra, ahol tudunk hozzáadni vagy eltávolítani applikációkat.

A másik mai kérdés: Hogyan tudjuk azt elkerülni/megoldani, hogy ha bármilyen mailboxunkban bekategorizálunk, bezászlózunk egy levelet, az automatikus ne kerüljön be a Task (To-Do) listünkre?

A megoldásom nem biztos, hogy a legjobb vagy a legegyszerűbb, de elvileg ezzel kezelhető a probléma (ha ezt annak nevezhetjük). Magáról a To-Do listről manuálisan, a jobb gombbal előhívható Remove from List opcióval eltávolíthatjuk az adott levelet, de ez eltávolítja a kategorizálást is a levelünkről, amit szintén nem akarunk.

Úgyhogy első lépésként menjünk a Ribbonunk View füle alatt található View Settings menüpontba:

Itt válasszuk ki a Filter menüpontot, ugyanis a tervünk az, hogy kiszűrjük ezen e-maileket a task listünkről:

A Filter ablak Advanced fülére lépjünk át, itt a Define more criteria szekcióban lévő Field legördülő menüből válasszuk ki és szúrjuk be a Message Class feltételt:

A feltétel pedig legyen Message Class contains task és ezzel csak a taskokat jelenítjük meg, a zászlózott leveleket nem.

Működő megoldás lehet még a Flag Status feltétel kiválasztása és egyenlővé tétele az Unflagged értékkel:

Folytatva az olvasói kérdések megválaszolását, ezúttal egy egyszerűbb probléma megoldását járjuk körül, főként azért, mert az erőssége ellenére az egyik kevésbé elterjedt használattal bíró formula, a SUMPRODUCT fogja adni az én megoldásom gerincét.

A kérdés az alábbi képen tökéletesen látható is: számoljuk össze, hogy egy adott dátum után pontosan hány esemény (ezúttal értékesítés) történik.

A megoldások között kapásból valószínűleg nem a SUMPRODUCT jut senki eszébe, hanem a több feltétellel működő COUNTIF, azaz a COUNTIFS és ez így is van rendjén, hiszen nagyjából ugyanazt az eredményt elérhetjük a COUNTIFS függvénnyel, amit a jelen kérdéskörben szeretnénk, viszont a SUMPRODUCT-nak a jövőre nézve nagyobb lehetőségei vannak, hiszen míg később a COUNTIFS formulát nehéz lenne kibővíteni bizonyos feltételekre, addig a SUMPRODUCT nagyjából bármit lekezel.

Hogy oldjuk meg a mostani feladatot? Nagyjából egyetlen dolognak kell eszünkbe jutnia, a * használatának, amellyel arra kényszeríthetjük az alapvetően nem ilyen célokra megalkotott SUMPRODUCT formulát, hogy több feltételnek megfelelő értékeket számoljon össze.

Hiszen ha ezt tudjuk, akkor már csak a feltételeinkre van szükségünk, egyrészt

($B$2:$B$23>=$H$2)

ami azt vizsgálja, hogy az értékesítések napjai közül mely van a megadott dátumunk után, másrészt

($A$2:$A$23=E2)

ami megvizsgálja, hogy a fentebb említett dátumvizsgálat elvégzése után kalapban maradó értékek közül mely esetén egyezik meg az A oszlopban található településnév az első városunk nevével. Aztán ezt a kettőt szépen összepréseljük:

=SUMPRODUCT(($B$2:$B$23>=$H$2)*($A$2:$A$23=E2))

Ahogy látható, a lehetőségeink innen korlátlanok, hiszen további tucatnyi cellát is beintegrálhatnánk, mint feltétel, lehetne intervallumra szűrni és így tovább.

De folytathatjuk még a SUMPRODUCT bevetési területeit hasonlóan egyszerű kérdésekkel, mondjuk egy jó kis házifeladattal: anélkül, hogy továbbolvasnánk a posztban, számoljuk csak össze a SUMPRODUCT segítségével, hogy pontosan hány darab páratlan szám van a listámban.

Első kérdés, amire kezdésként választ kell adnunk, az az, hogy hogyan állapítjuk meg egy számról, hogy az páratlan-e vagy sem. Bizony, elosztjuk kettővel és ha van maradék, akkor az már biztos nem páros. Erre a célra ott is van szépen a MOD függvényünk, amely az első paramétereként megadott számot elosztja a második paramétereként megadott számmal és visszaadja a maradékot - kettővel való osztás esetén páratlan számnál tehát egyet.

Ha tehát a

=MOD(5,2)

függvényt egyenlővé tesszük eggyel

=MOD(5,2)=1

akkor vagy TRUE vagy FALSE eredményt kapunk vissza, attól függően, hogy egy a maradék vagy sem. De hogy ezt összegezni tudjuk, vissza kell alakítanunk ezeket az értékeket egyesre vagy nullára, amit a korábban már megtanult kötőjel illetve dupla kötőjel segítségével tudunk megtenni.

A kötőjel (illetve a dupla kötőjel) Boolean (IGAZ,HAMIS - TRUE, FALSE) értékeket alakít egyesekké és nullákká, sőt, mivel a program a kötőjelet "tagadásként" is értelmezi, még az előjelet is megfordítja, ergó egy kötőjellel egy TRUE értékből -1 lenne (a nulla nem változik), azaz még egy kötőjelre van szükségünk, hogy megkapjuk a vágyott egyesünket.

--(MOD(5,2)=1)

Ezután már csak az ötös számot kell lecserélnünk a tartományunkra, majd a SUMPRODUCT segítségével összegeznünk kell és kész is vagyunk az eredménnyel.

=SUMPRODUCT(--(MOD($B$3:$B$16,2)=1))

A matematika iránti amatőr és semmilyen tudományos elismerésben ki nem teljesedő vonzódásom adja a mai poszt témáját, méghozzá egy alapvetően egyszerűnek tűnő kérdésből kiindulva - Excelben kinek milyen módszere van annak megállapítására, hogy egy szám prímszám-e vagy sem? A prímek ugyebár azok a természetes számok, amelyeknek mindösszesen két osztójuk van, saját maguk és 1. Ilyen például az egyetlen páros prímszám a 2 vagy mondjuk a 19, a 7 vagy a 23.

A kérdés nem újkeletű, már sok-sok évvel ezelőtt is voltak Excel-guruk, akik előjöttek ilyen-olyan megoldásokkal, a mostani kérdés igazából a legegyszerűbb és legrövidebb utat keresi az Excelben, legyen szó akár VBA-ról, akár formuláról - én most elsőként egy VBA-kódot fogok bemutatni, de kommentben jöhet bármilyen javaslat és ötlet.

Mit fogunk tenni? Magán az Excel munkalapon elég egyszerű a kérdés, az A1 cellába beírunk egy számot, VBA-ban pedig készítünk egy funkciót, amelynek egyetlen paramétere az A1 lesz és a funkció visszaad egy TRUE vagy FALSE értéket, attól függően, hogy prímszám van az A1 cellában vagy sem.

Szóval ALT+F11 lenyomásával átmegyünk VBA-editorba, beszúrunk egy modult, majd létrehozunk egy PRIM nevű funkciót:

Azt már a korábbi posztok vagy saját tapasztalat alapján valószínűleg elég sokan tudják, hogy miért Boolean lett az eredmény adattípusa, hiszen ez a típus tárol True vagy False értékeket, a paraméterként megadható számunk pedig Integer lesz.

Miután a kód egy For..Next ciklussal fog operálni, szükségünk van egy változó deklarálására is, ez lesz most az i változó, Integer típussal.

Ezután következzen egy If utasítás, amelyben két roppant egyszerű feltételt fogunk megvizsgálni, egyrészt, hogy a számunk kisebb-e mint kettő (tehát 1) illetve, hogy a számunk osztható-e maradék nélkül kettővel (feltéve, hogy a szám maga nem a kettes, mert kettő prímszám):

If Szam < 2 Or (Szam <> 2 And Szam Mod 2 = 0) Then Exit Function

Figyeljünk fel két dologra: Szam Mod 2 = 0 a Mod operátort használja fel, amely az elsőként megadott számot (szam változó) elosztja a másodikként megadott számmal (2) és visszaadja a maradékot, itt pedig azt vizsgáljuk, hogy ez a maradék nulla-e. A másik az Exit Function lépés, amely Boolean típusú funkciónál, ha előtte nem állítottuk be másként, alapból False értéket dob majd vissza - jelen esetben pedig ezt akarjuk, hiszen a maradék nélkül kettővel osztható számok biztos nem prímek, ahogy egy sem.

Vegyük észre azt is, hogy ha az egész If utasítást egy soron tüntetjük fel, akkor nincs szükség End If lezárásra.

A kódunk másik részében pedig a For...Next ciklust fogjuk használni, amelyben egyszerűen nem mást fogunk tenni, mint háromtól kezdődően egészen számunk négyzetgyökéig elvégezzük azt az osztást, amelyben a számunkat elosztjuk i értékével és ha nullát kapunk, akkor ismét Exit Functionnel kilépünk a funkcióból és FALSE értéket adunk vissza, ellenkező esetben funkciónk TRUE értékkel zárul.

Step 2 utasítás azt jelenti a For ciklusunk első sorában, hogy kettesével lépkedünk a ciklusban, azaz 3,5,7..stb. követi egymást egészen számunk négyzetgyökéig.

És kész is vagyunk, teszteljük nyugodtan, elvileg jól működik, kommentben pedig jöhetnek az egyéb ötletek, megoldási javaslatok!

Function Prim (Szam As Integer) As Boolean
Dim i As Integer
If Szam < 2 Or (Szam <> 2 And Szam Mod 2 = 0) Then Exit Function
For i = 3 To Sqr(Szam) Step 2
If Szam Mod i = 0 Then Exit Function
Next
Prim = True
End Function

A mai könnyedebb hangvételű, rövidke Excel-posztban a táblázatkezelő egy másik, kevéssé ismert funkcióját, az alap képszerkesztési lehetőségeket fogjuk megvizsgálni egy egyszerű feladaton keresztül (amelyet valószínűleg bármelyik képszerkesztővel jobban meg is tudnánk csinálni). Avagy hogy vegyük rá a legendás Grigori Perelmant, hogy ő maga írjon Excelünk egyik cellájába valamit?

Először is fogjunk egy képet a legendás matematikusról, majd szimplán illesszük be a munkalapunkra:

Kattintsunk a képre, majd a Ribbonunkon megjelenő Format fül alatt keressük meg a Remove Background funkciót és szimplán kattintsunk rá:

Bár többnyire csak igen jól elkülöníthető képrészek esetén van erre esélye, a program kísérletet tesz a háttér elválasztására a kiemelendő résztől:

Ahogy számíthatunk rá, ez nem nagyon megy neki, így a most már aktívan látható Background Removal Ribbonfülre lépjünk, ahol a Mark Areas to Keep, Mark Areas to Remove és Delete Mark funkciók segítségével apró pontokat tehetünk a képünkre, amelyekkel nagyjából körbe tudjuk határolni, hogy mi az, amit szeretnénk megtartani illetve eltávolítani:

Ha megvagyunk a körbehatárolással, amelyet egyébként menet közben folyamatosan nézhetünk is, hiszen a program automatikusan frissít mindig, az ugyanezen Ribbonfül alatt található Keep Changes funkcióval jóvá tudjuk hagyni a változtatásokat:

És ahogy láthatjuk, el is készültünk, maga Perelman próbálja felvázolni nekünk a SUMPRODUCT szépségeit.

Érdekes Excel-fejtörővel örvendeztetett meg egyik kedves Olvasóm, aki egy nagyon egyszerű Data Validation List kérdésre kereste a választ. Tegyük fel, hogy van két sheetünk (Lista és Termékcsoport), az egyiken van egy egyszerű legördülő menünk, amelynek elemei a másik sheeten szereplő listából kerülnek beolvasásra. Valahogy így:

Eddig ez nagyon egyszerű történet, de mi van ha ennél jóval több legördülő menünk van, amelyekre aztán kész hivatkozáserdőt építünk fel és közben változtatunk valamit a validációs listán? Az eredetileg kiválasztott értékek maguktól nem fognak frissülni, újra választani kellene őket a legördülő menükből. Az aktuális példánál mondjuk kiválasztottuk a Termék5 nevű terméket a legördülő menüből, de később a forráslistában Termék5 nevét lecseréltük valami másra, akkor magától ez nem fog átíródni, újra ki kell választani az új névre hallgató terméket. A kérdés tehát az, hogy ezt hogy tudjuk elkerülni.

Egy rövidke VBA-kódra van ehhez szükségünk, amelyet a forráslistánkat tartalmazó sheetre építünk be azzal a logikával, hogy ha azon a sheeten bármi változik, akkor a másikon frissít a változásnak megfelelően.

De kezdjük az elején, menjünk át ALT+F11 lenyomásával a VBA-editorba, ahol a második sheetünkre, jelen esetben a Termékcsoport munkalapunkra a Worksheet Change eseményhez rendeljük hozzá kódunkat.

A ByVal Target As Range jelen esetünkben azt jelenti, a tartományunk cellájának változása indítja el az eseményt. Változók definiálásával folytatjuk a kódunkat, deklarálunk egy változót Integer típussal az érintett celláink számának ("cellaszam"), egy-egy változót a régi és az új cellatartalomnak String típussal ("uj" és "regi") valamint egyet Range típussal a legördülő menüt tartalmazó sheetünkre, a konkrét menüt tartalmazó cellára ("rng").

Nagyjából amit most majd tenni fogunk az az, hogy egy For...Next utasítás segítségével a validation listünket tartalmazó sheeten az összes listaelemünkön végig fogunk menni és ha közöttük találunk olyat, ami nincs a legördülő menüt tartalmazó sheeten, akkor cserét hajt végre.

Tehát kezdjük azzal, hogy a listaelemeket tartalmazó sheetünkön az első értéket tartalmazó cellától indulva megszámoljuk, hogy hány sorunk, azaz elemünk van, tehát a ciklust hányszor fogjuk végrehajtani:

For cellaszam = 1 To Range("A1").CurrentRegion.Rows.Count

A fentebb deklarált rng változóra beállítjuk a konkrét legördülő menüt tartalmazó cellánkat a másik sheetről:

Set rng = Worksheets("Lista").Range("A3")

Aztán most következik kódunk motorja, egy Intersect metódus, ami egy olyan tartományt fog visszaadni, ami két vagy több másik tartomány metszete. Arra is használhatjuk többek között, hogy meghatározzuk, egy bizonyos tartomány és egy másik tartomány között van-e átfedés.

Jelen esetben megnézzük, hogy a fentebb deklarált rng tartomány és a listaelemeket tartalmaz tartomány meghatározott/megváltozott eleme között van-e átfedés, ha van, tehát megegyezik a legördülő menünkből kiválasztott listaelem az adott elemmel a listaelemeket tartalmazó sheeten, akkor ne csináljon semmit, ha nem, akkor jön a változtatás.

If Intersect(Target, Range("A" & cellaszam)) Is Nothing Then

Most jön a kódunk azon része, hogy mi történjen, ha nincs egyezés, tehát változott valami a listaelemek között és ennek tükröződnie kellene a legördülő listából már kiválasztott elemben is.

Itt fontos egy Application.EnableEvents = False utasítás, hogy a most következő változtatásunk ne váltson ki egy újabb Worksheet_Change eseményt, ami a makró újbóli lefutását eredményezné majd megint és megint és így tovább.

Ezután az uj változó vegye fel a megváltozott listaelem értékét.

Application.Undo utasítással a megváltoztatott listaelemünk értékét írjuk vissza eredeti értékére és ezt töltsük be a regi változóba. Ezután írjuk vissza megint a listaelemünk értékét az új értékre.

Már csak egy dolog van hátra, azaz updatelnünk kell azt a legördülő menüt tartalmazó cellát, ahol olyan értékünk szerepel, ami már nincs is a listában.

És ezzel kész is vagyunk, ugorhatunk a ciklusban egyet, majd ne felejtsük el a ciklus befejeződése után visszakapcsolni Application.EnableEvents = True utasítással a Worksheet változásra való reagálását.

Másolható szövegként:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cellaszam As Integer
Dim uj As String
Dim regi As String
Dim rng As Range
For cellaszam = 1 To Range("A1").CurrentRegion.Rows.Count
Set rng = Worksheets("Lista").Range("A3")
If Intersect(Target, Range("A" & cellaszam)) Is Nothing Then
Else
Application.EnableEvents = False
uj = Target.Value
Application.Undo
regi = Target.Value
Target.Value = uj
rng.Replace What:=regi, Replacement:=uj
Target.Select
End If
Next cellaszam
Application.EnableEvents = True
End Sub