Egyesítsük celláinkat Excelben az adatok elvesztése nélkül

Van beépített funkció is, de az bőven nem tud annyit, mint egy apró kis makró

2015. október 13. - Office Guru

Nem vagyok egy túlságosan stresszes vagy sodrából könnyen kihozható ember, de eléggé szokott idegesíteni egy-két hiányosság/nehezen kezelhető funkció Excelben és ezek közül a lista egyik előkelő helyezettje az, amikor több cellában lévő szövegeimet szeretném egyesíteni Merge & Center segítségével, de mindig csak az egyik cella értékét tartja meg, a többi cellában szereplő adataim elvesznek.

Van ugyan egy beépített funkció a programban, amivel ezt elvileg ki lehetne küszöbölni, de ennek eléggé limitáltak a "képességei", például számokat, képleteket nem is tud kezelni, de ami a leginkább fájó, hogy csak azonos oszlopban szereplő cellákkal tud foglalkozni. Ez a Ribbonunk Home füle alatt található Editing szekcióban szereplő Fill, azon belül is a Justify. Adottak tehát a következő cellák (szám, képlet nélkül ugyanabban az oszlopban):

220.jpg
Szimplán jelöljük ki őket majd kattintsunk a Fill menüben a Justify menüpontra és lám, íme az eredmény:

221.jpgFontos, hogy oszlopunk olyan széles legyen, hogy beleférjen az egyesített szöveg!

A probléma megoldására vannak más lehetőségek is, használhatnánk függvényes módszert is, de ismét csak egy apró makrót rántsunk elő a tarsolyból, ami segíthet megoldani ezt a helyzetet - ezt aztán gombhoz rendelhetjük hozzá, de akár rávarázsolhatjuk Ribbonunkra is, mint saját kis funkció (erről a csodás kis lehetőségről még majd később) és utána bármikor használhatjuk.

Szóval adott a következő kis szöveg, különböző cellákban (ez a megoldás egyébként számokra és képletekre is ráhúzható):

merge1.bmpCsinálhatnánk kapásból gombbal, de először csak írjuk meg a kis makrónkat, úgyhogy most menjünk át a Developer fül alól a VBA editor felületre, majd ott az Insert menü alól szúrjunk be egy új procedúrát, egy Subot (a végletekig ismételt fontos különbség a Sub és Function között, hogy utóbbi ad vissza értéket, előbbi nem), amit nevezzünk el biztosan felismerhető néven, nálam ez a MergeText() lesz.

Ezután deklaráljunk egy változót szöveg típusként, ami konkrétan majd az egyesített szövegünket fogja tartalmazni, ahogy szépen egyesével feltöltjük majd egy ciklus segítségével. Ha akarunk szóközt a celláink tartalma között, akkor mindenképpen célszerű CONST utasítással létrehoznunk egy konstanst, mondjuk Spacekarakter néven, ami egy szóközzel lesz majd egyenlő - ezt később még fel fogjuk használni.

merge2.bmpElég sok kódban láttam már és én is talán túlságosan bátran használom, de ha el akarjuk kerülni a felesleges hibaüzeneteket és a teljes leállást, akkor beszúrhatunk egy On Error Resume Next utasítást, ami az On Error utasítás azon szintaxisa, amely lehetővé teszi, hogy hiba esetén egyszerűen a következő sorra ugorjon a kis program és onnan folytassa a végrehajtást. Az On Errornak egyébként meghatározhatunk olyan kitételeket is, amellyel megmondhatjuk például neki, hogy hiba esetén mely sorra ugorjon a program - mondjuk oda, ahova beépítettük a hibakezelési részünket.

A következő lépés pedig már magának programunknak a lelke, egy For Each...Next utasítás, amely általánosságban tömbökre, tartományokra használható, hiszen különböző utasításokat hajt végre tartományunk minden egyes elemén.

Az alap szintaxisa szerint (még nem mélyednék bele nagyon):

FOR EACH "elem" IN "csoport/tartomány"
"fusson le/történjen meg ez"
NEXT "elem"

Ergó most
FOR EACH cell IN Selection
NEXT CELL

felhasználásával a FOR EACH és NEXT sorok közé beékelt utasítást az összes cellánkra le tudjuk majd futtatni. És hogy mi legyen ez az utasítás, azt már azért könnyen ki tudjuk logikázni, hiszen a fentebb már deklarált MergeText változót kezdjük el szépen feltölteni celláink értékével, valahogy így:

MergeText = MergeText & cell.Value & Spacekarakter

Mit is csinál ez? Először az első cellánk értékéhez (erre szolgál a cell.Value) hozzápakol egy szóközt (& karakterrel összefűzünk), majd betölti az akkor még üres változónkba. Következő lépésként az ebben a változóban már benne lévő "első cella értéke + Space" értékhez hozzápakolja a következő cella értékét és egy újabb szóközt és ezt egészen addig folytatja, amíg a kijelölésünk végéhez nem ér.

merge3.bmpEzután már az eredményünk ott csücsül a MergeText változóban, úgyhogy fel kellene csak használnunk, amit meg tudunk tenni nagyon egyszerűen is, betöltve egy teljesen egyszerű cellába, de akár használhatjuk a With...End With utasítást.

Ennek alap szintaxisa a következő:

With "mivel csinálja a következő utasításokat"
.Utasítás1
.Utasítás2
End With

Azaz azzal az objektummal, amit megadunk neki, több utasítást is végre tudunk hajtani, anélkül, hogy külön-külön ezeket be kellene adnunk a programnak.

Jelen példánkban mondjuk csináljunk ilyeneket:

merge4.bmpSelection.Clear - kitörli a kijelölésünkből a tartalmat
Selection.Cells(1).Value - kijelölésünk első cellájának értéke a MergeText változó lesz
Selection.Merge - kijelölésünk összes celláját összevonja egy cellává
Selection.WrapText = True - ez pedig azért elég egyértelmű

Tehát így néz ki a teljes kód:

merge5.bmpEzután amire szükségünk van, az az, hogy ezt rendeljük hozzá egy gombhoz, amelyet a már megismert módon tudunk elkészíteni:

merge6.bmp

merge7.bmp

merge8.bmpInnentől kezdve pedig láss csodát, gombunkra kattintva a fentebb felhozott példát simán egyesíteni tudjuk egy cellába, azaz megoldva ez az idegesítő probléma:

merge9.bmpmerge10.bmp

 

A bejegyzés trackback címe:

https://officeguru.blog.hu/api/trackback/id/tr547966246

Kommentek:

A hozzászólások a vonatkozó jogszabályok  értelmében felhasználói tartalomnak minősülnek, értük a szolgáltatás technikai  üzemeltetője semmilyen felelősséget nem vállal, azokat nem ellenőrzi. Kifogás esetén forduljon a blog szerkesztőjéhez. Részletek a  Felhasználási feltételekben és az adatvédelmi tájékoztatóban.

l. 2015.10.14. 12:02:35

a szpészt a végéről meg ki fogja leszedni ? a nyuszika ?
aztán komparáláskor meg jön a meglepetés meg a wtf
egy trim ráfért volna a végén, spórólósoknak rtrim