Összefűzés formátumokkal együtt - VBA nélkül

2017. október 14. - Office Guru

Nem kell hozzá felmérést elvégezni, hogy biztosak lehessünk benne, az Excel legtöbbet használt függvényei között az első helyen álló VLOOKUP mögött szorosan ott szerepel a CONCAT vagy CONCATENATE függvény is, amelyről már hosszasan értekeztem itt többször is a blogon, legutóbb például az ezen formuláknak tökéletes alternatívát kínáló TEXTJOIN kapcsán.

A mai írásban azt fogjuk feszegetni, hogy hogyan lehet különböző cellák tartalmát úgy összefűzni, hogy azok eredeti formátuma megmaradjon. Adott mondjuk a következő három cella, amelyeket szeretnénk összefűzni:

1_1.JPGNézzük meg, hogy mi történik, ha CONCAT/CONCATENATE függvénnyel megcsináljuk az összefűzést:

2_1.JPGCélcellánk alapformátumával egyáltalán nem foglalkoztunk előzetesen, így defaultként, hogy csak a szövegünk formátuma maradt meg, a számunk és a dátumunk esetén elveszítettük a formázást:

3_1.JPGAki már szembesült ilyen problémákkal, az tudja, hogy megoldásként a TEXT függvény segíthet nekünk első körben, ami az első paramétereként megadott értéket konvertáljá át szöveggé a második paramétereként megadott formátum szerint.

Ha ezt ráhúzzuk a jelenlegi példánkra, akkor ez azt jelenti, hogy a D oszlopunkban szereplő dátumot szövegformátumra fogjuk konvertálni:

 

4_1.JPG

5_1.JPGAhogy láthatjuk, ez tökéletesen bevált, így ennek mintájára fogjuk a számunk formátumát is megőrizni az utókornak, de itt előzetesen elmélkedjünk egy kicsit. Először jobb gombbal kattintsunk a számunkat tartalmazó cellán, válasszuk ki a Format Cells menüpontot, majd nézzünk körül a Custom formátumok között:

6_1.JPGUgyebár most az próbáljuk megtudni, hogyan tudjuk a TEXT függvény felhasználásával megőrizni számunk eredeti formátumát, tehát mit kell a TEXT második paramétereként megadnunk, hogy azt kapjuk vissza, amit szeretnénk. Ahogy látható a fenti képen is, én kapásból a #,##0.00 előre definált formátumra ugrottam rá, mert ez milyen formátumot is jelent?

Egyrészt lesz benne ezres elválasztó vessző (#,##), másrészt kettő tizedesjegyre fog kerekíteni (0.00), úgyhogy ez most tökéletes lesz nekünk. Adjuk be a fentebb említett szövegformátumos TEXT formulához hasonlóan most a számot tartalmazó cellánkhoz a CONCATENATE függvénybe ezt.

 

7_1.JPG

8_1.JPGÉs látható, hogy tökéletesen működik a dolog, most már nincs más hátra, mint a szövegformátumot és a számformátumot tartalmazó TEXT formulánkat egy CONCATba ágyazni:

9.JPGÉs kész is vagyunk.

De ezenkívül is van még megoldás, az egyikhez például a WORDöt hívjuk segítségül!

Jelöljük ki a három cellánkat, majd CTRL+C és CTRL+V alkalmazásával helyezzük át WORDbe táblaként:

10.JPGJelöljük ki, amit beillesztettünk, majd a fent megjelenő LAYOUT Ribbonfül alatt a Data szekcióból válasszuk a Convert to Text funkciót:

11.JPGA felugró kis ablakban a "Separate text with..." lehetőségek közül válasszuk ki az Other opciót és tegyünk bele egy szóközt:

12.JPGHa megnyomjuk az okét, a következőt fogjuk látni:

13.JPGÉs ha ezt az átalakított szöveget most visszapakoljuk egy Excel-cellába, akkor ezt fogjuk látni:

14.JPGDe azért ezek a megoldások alapvetően elég lassúak és manuálisak, bizonyos jellegű formázásokat nem is tudunk így kezelni (pl. színeket stb.), plusz sok cella és sok különféle formátum esetén nehézkes is a használatuk, úgyhogy olyankor már érdemes VBA-hoz fordulni. Ennek tükrében csak a tényleg elvetemült érdeklődők és Camera Tool fanatikusok olvassanak tovább, ugyanis a következőben egy olyan megoldást fogok bemutatni, ami beépített funkció segítségével tud színezést, boldolást stb. minden formázást egyesíteni több cella esetén.

Adott tehát a következő két cella, amelyek értékeit szeretnénk egy cellában megjeleníteni összefűzve, úgy, hogy a formázások is megmaradjanak:

15.JPGSima CONCAT alkalmazásával az eredmény összejön, csak a formázások vesznek el:

16.JPGDe ne adjuk fel, fogjuk az összefűzött cellát tartalmazó oszlopunkat, nézzük meg, hogy milyen széles és pontosan olyan szélességre állítsuk az összefűzendő cellákat tartalmazó oszlopokat is, hogy azok összege megegyezzen az összefűzést tartalmazó cella szélességével:

17.JPGA munkalapunkon valahol egy elrejtett részen két cellát tegyünk egyenlővé az összefűzendő celláinkkal, tehát az egyik cella az Autó cellára fog mutatni, a másik pedig a 43-as számra:

18.JPGJelöljük ki ezt a két új, elrejtett cellát, majd a jobb gombbal előhívható context menü Format Cells menüpontjában válasszuk a Border fület:

19.JPGItt kapcsoljuk ki a külső (Outline) és belső (Inside) vonalakat is.

Ezután pedig már csak a Camera Toolt kell bevetnünk (ugyebár ezt egyetlen módon hívhatjuk csak elő, ha előtte hozzáadtuk a Quick Access Toolbarhoz), méghozzá oly módon, hogy az eredeti CONCAT függvényt tartalmazó cella tartalmazza azt a "fényképet", ami a két, "rejtett" cellánkra mutat:

20.JPGTúl sok időt nem töltöttem ezzel most, úgyhogy kicsit nyomorultul fest szegényke, de remélhetőleg ebből is látható volt az, hogy a Camera Tool megint olyasmiben segíthet nekünk, amit nem is gondoltunk volna előzetesen.

A bejegyzés trackback címe:

http://officeguru.blog.hu/api/trackback/id/tr2012974418

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.

Nincsenek hozzászólások.