Telefonszámunk: 1-472-0679

Egyéni számformátumok – az alapokon túl…

2015-01-25 - horvimi - Kategória: Formázás
Hivatkozott Excel verzio: Excel 2007 Excel 2010 Excel 2013

Az egyéni számformátumokhoz (Custom Numbert Format) akkor szoktunk folyamodni, amikor az egyszerűbb, kattintgatós-kiválasztós felületen nem tudjuk beállítani azt, amit szeretnénk. Azt feltételezem, hogy az olvasó a számformázás alapjaival már tisztában van. Tehát ismeri az általános, pénznem, ezres tagolás, tizedesek, dátum, százalék alapszintű beállításait, jobb esetben már készített egyéni formátumot is, pl. egy egyedi mértékegységet.

Az egyéni számformázás lehetősége azonban olyasmiket is tartogat, ami egészen meglepő, amiről azt gondolnánk, hogy csak feltételes formázással oldható meg.

Lényeges alaptétel, hogy a számformázás a cellában lévő értékhez nem nyúl, annak csupán a megjelenítését szabályozza. A szerkesztőlécen mindig az eredeti érték látható, a cellában pedig a formázásnak megfelelő.

Az Egyéni számformázás menü

Ez a lehetőség a Cellaformázás párbeszéd első lapjának utolsó kategóriája. A párbeszédablak többféle módon előhívható, miután kijelöltük a formázandó cellákat.

  • Jobb klikk->Cellaformázás
  • Számformázó menü utolsó menüpont: „További számformátumok…”
  • Kezdőlap/Szám csoport jobb alsó sarkán kattintunk
  • Ctrl+1 gyorsbillentyű

Egyéni-számformátum-menü

A bal oldali kategóriákban megtalálható formátumok mindegyikének megvan a saját kódsora az Egyéni listában. Ezek közül néhány ki is van vezetve a szalagra ikonként vagy menüparancsként.

A számformátum részei

A számformátum több, de maximum 4 részből (szekcióból) állhat. Ezeket pontosvessző választja el egymástól. A fenti kép ötödik sorától például, már két részből álló kódokat látunk. A formátumkód részei:

Pozitív számok; Negatív számok; Nulla; Szöveg

Értelemszerűen ezekkel azt szabályozhatjuk, hogy hogy nézzenek ki  a számok a cellában, ha pozitívak, ha negatívak, ha esetleg nullák, vagy éppen szöveget tartalmaznak.

Nem kötelező minden részt megadni, de akkor legyünk tisztában azzal, hogy ilyenkor mi történik. Ha valamelyiket nem akarjuk szabályozni, akkor a „Normál” (Angol verziónál a „General” ) szót kell a megfelelő helyre írni.

Pozitív számok;Normál;Nulla

A különböző verziók

  • Ha csak egy szekciónyi formátumot adunk meg, akkor a pozitív, negatív értékek, és a Nulla is ugyanezzel a formátummal lesz formázva.
  • Ha két szekciót adunk meg, akkor az elsőt használja a pozitív és a Nulla értékekhez és a másodikat a negatív értékekhez.
  • Szöveget ezzel a módszerrel nagyon ritkán szoktunk formázni, és csak akkor lesz érvényes, ha a másik hármat is megadjuk, és a cellába szöveg kerül. (Ami egy HA függvény esetén simán lehetséges)

Színek megadása

Az egyik legegyszerűbb dolog, amit tehetünk az az, hogy megszínezzük az egyes szekciókat. Az Excel nyolc színkódot ismer, ezeket szögletes zárójelben kell alkalmazni. Számformázással csak a betűszínt lehet formázni.

szinkodok

Ha színeket alkalmazunk, akkor a formátumkódnak ezzel kell kezdődnie. Negatív számok színezésénél a mínuszjelet is ki kell tennünk a formátumkódba.

Pozitív, negatív és nulla legyen más-más színű

[Piros]Normál;[Kék]-Normál;[Bíbor]Normál


szinezes

Szekciók elrejtése

Érdekes lehetőség, hogy ha bármely szekció helyére egy üres pontosvesszőt írunk, akkor azt a részt egyszerűen nem jeleníti meg. Így nagyon egyszerűen elérhető például az, hogy a nullák ne jelenjenek meg, vagy ha egy cellában szöveg lenne az maradjon rejtve, és fordítva is lehetséges.

Ne felejtsük, hogy az értékek benne maradnak a cellában, csak éppen a nem láthatók.

szekciok-elrejtese

A számok formázása

A számjegyek, illetve az ezres tagolás és a tizedesjegyek számát a következő karakterekkel formázhatjuk:

Angol Magyar Név Mire jó
0 0 Nulla  Mindenképpen megjelenő karakter
# # Hashmark  Opcionálisan megjelenő karakter
, (vessző)  szóköz Ezres elválasztó  Ezzel adhatjuk meg, hogy tagolja a nagyobb számokat
. (pont) , (vessző) Tizedesjel  Ez után adhatjuk meg a szükséges tizedes számjegyek számát

Az ezres elválasztó és a tizedes jelet a vezérlőpultból, vagy Excel 2010 verziótól a beállításokból veszi.

Kerekítés adott számú tizedesre vagy egészre

Ha az adott nyelvi verziójú tizedes elválasztóval nem adunk meg kívánt számú tizedes számjegyet, akkor a cellában lévő számot egész számra kerekítve fogja megjeleníteni. A formázó kód lehet 0 vagy #.

 nulla-hashmark-tizedes

Az első két sorból látszik, hogy a nulla esetében nincs kötelezően megjelenítendő számjegy, ezért a hashmark nem is jelenít meg semmit, de a nulla legalább egy számjegyet, azaz a nullát megjeleníti.

A harmadik-negyedik sorokból pedig az látszik, hogy nem attól kerekít egy számot egészre, mert nulla van a formázó kód végén, hanem azért, mert nincs megadva tizedes rész.

Kerekítés ezresre vagy millióra

A nagyobb számokat ezresekre vagy milliókra kerekítve is megjeleníthetjük, ha a formázó kód végére egy (ezres), illetve kettő (millió) ezres szeparátorjelet teszünk.

ezer-millio

A képen nem igazán látszik, de az első sorban a formázó kód utolsó nullája után, a nyitó idézőjel előtt egy db. szóköz, a második sorban pedig kettő darab szóköz van. Angol verziónál ez természetesen vessző lenne.

Kiegészítés adott darab számjegyre

Például, ha a beírt szám rövidebb, mint 6 számjegy, akkor a formázással előírhatjuk, hogy vezető nullákkal egészítse ki 6 számjegyre.

000000;-000000

vezeto-nullak

Szöveg a számok között

Ha begépeléskor bármilyen nem numerikus értéket írunk egy cellába, azt az Excel azonnal szövegként fogja értelmezni. De mit tehetünk, ha a számokba tagoló jeleket vagy egyedi mértékegységet szeretnénk tenni?

Több karakteres szöveg

Ha a formázó kód elé vagy mögé idézőjelek közé tetszőleges szöveget írunk, akkor azt az Excel megjeleníti a cellában a megadott helyen.

mértékegység

Idézőjel nélkül megjelenő karakterek

idézojel-nelkuli-karakterek

Tagoló karakterek a számjegyek között

A fenti táblázatban szereplő karakterek idézőjel nélkül, egyéb karakterek a visszaper ( \ ) szimbólummal tehetők a számjegyek közé. Így tudunk pl. telefonszámokat, személyi azonosítót, vonalkódot, stb. formázni.

 számjegyek-tagolása

Ilyesmi csak akkor követhető el, amikor az eredeti adatok számok. Olyan adatokat, amelyekkel nem tervezünk matematikai műveletet végezni, álljanak akár tisztán számjegyekből, általában szövegként tárolunk.

Nullák helyett szöveg?

Az eddigiek alapján nem tűnik túl bonyolultnak, ha azt szeretnénk, hogy a kijelölt tartományban a nullák helyett más, valami szöveg vagy csak egy kötőjel jelenjen meg.

nulla-helyett-szöveg

Pozitív szám IGAZ, minden egyéb HAMIS

igaz-hamis

Karakter ismétlés vagy vezető jel

Ritkán ugyan, de előfordulhat, hogy a cellában a szám előtt vagy utána valamilyen karakterrel kell kitölteni a helyet a cella végéig vagy a szövegig, amit még megadunk. Erre szolgál a csillag ( * ) szimbólum. A formázó kódban a csillag után azonnal egy karakteben megadjuk, amit ismételni kell. Lehet szóköz is.

 kitolto-karakter

Feltétel szerinti formátum

A cellában lévő érték vizsgálatával egy maximum három ágú feltételes formázást is végrehajthatunk. A feltételek konstanshoz képesti relációk lehetnek.

Általános formája

[Feltétel 1] Formatum1; [Feltétel 2] Formatum2; Formatum3

Ha a cellára az első feltétel teljesül, akkor Formatum1-el formáz, ha a második feltétel teljesül, akkor Formatum2-vel, és ha egyik sem, akkor Formatum3-al. Az utolsó tag elhagyható, vagy a „Normál” formázás írható. Ebben az esetben, ha van ilyen eset, a szám nem lesz formázva.

Feltételes-kód

 

Karakterhely kihagyása – (jobb és/vagy bal indent)

Az alulvonás szimbólummal ( _ ) egy karakternyi üres helyet tehetünk a formátumba. Úgy használjuk, hogy az alulvonás jel után megadunk egy karaktert, és annak a szélessége adja a kihagyandó helyet.

Általános formája

Formátum_karakter vagy _karakterFormátum

Legtöbbször arra használják, hogy a szám elejére és/vagy végére egy-két karakternyi helyet tesznek, ezzel elérve, hogy mintegy margóként funkcionálva a szám nem lóg ki teljesen a cella széléig. Ezért hívják cella indent-nek is.

Ha több karakternyi térközt szeretnénk, akkor kétszer kell megismételni a szekvenciát

térköz

Néhány Fontos javaslat!

A számformátumokat a dokumentum hordozza. Ezért fontos tudni, hogy ha elkészítettünk néhány hasznos egyéni formátumot, akkor az új dokumentumainkban azok nem kerülnek maguktól bele.

Javaslom, hogy a fontosabb egyéni formátumokhoz készítsünk stílust!

Formátumok másolása másik dokumentumba

  • Ha egy cellát átmásolunk egy másik dokumentumba, akkor átviszi a formátumát is, hacsak másként nem rendelkezünk (pl. érték beillesztés)
  • Stílusokat tudunk egy másik nyitva lévő dokumentumból áthozni az aktuálisba
  • Gyűjtsük egybe a fontosabb beállításainkat és formátumainkat, majd készítsünk Excel sablont, és az új dokumentumokat ezalapján kezdjük.
  • Végül az excel alap sablonját is megváltoztathatjuk a saját sablonunkra, így nem kell többé ezzel szórakozni. Erről bővebben itt.

Letölthető gyakorló munkafüzet

Szamformazas-halado-feladat

További olvasnivaló

Ez a cikk nem szól minden lehetőségről. Lehet, hogy sokak számára több, mint amire valaha szükség lehet, de azért adódhatnak feladatok még a dátum és idő értékekkel. Ezért íme néhány további link:

Elkészült a videó is

  • Egyéni számformátum PLUSZ

 

 

9 hozzászólás
  1. Szia!

    Érdeklődöm, hogy tudok egy cell formátumát úgy beállítani, hogy 2 tizedes jegyig jelenítsen meg számot, ha van érték a cellában, ha nincs, vagy egész szám, ne jelenítse meg a tizedes vesszőt?
    Eddig jutottam: # ###,##
    Köszönöm előre is.

  2. Szia!

    Olyat nem lehet csinálni, hogy ha egész számot írsz be, akkor ne tegyen tizedest mögé, ha pedig törtet írsz be, akkor meg igen.

    Imre

    • „Látszólagosan” lehet megcsinálni.

      3 cellával.
      Az első (pl A1) írható, a formátuma egész szám.
      A második (B1) nem írható tartalma (rejtett képlettel: =HA(C1″”;”,”;””)
      A C1 szintén nem írható, rejtett képlettel: =HA(A1″”;A1-INT(A1);””) (És balra ütköztetjük, a vesszőhöz)

      De lehet úgy is, hogy 4 cellát használunk rá, csak ami írható, az hátérszínű betűvel lesz, és akkor az első cellában az INT()-es szám kerül, utána vesző, törtrész.

    • Ejnye – bejnye! Még akkor is, ha ez négy évvel ezelőtt volt!

      • Kedves Imre!

        Én balga most látom csak, hogy a cikk íróját „ejnye – bejnye”-m le. Csak arra szerettem volna felhívni a figyelmed, hogy szélesebb körben nézz körül a lehetséges megoldásokat tekintve válaszadáskor. @psanta nyilván a válaszban megnyugodva legyintve adta fel a formázást, pedig hát a megoldás az Excelben van…
        Igaz, a cikk az egyéni formázásról szól, a megoldás pedig a „feltételes formázás”-ban volt elrejtve – de ettől nagyszerű az Excel!

        Üdv.

        • Teljesen igazad van.
          Én csak egyéni számformátumban gondolkodtam a válaszadáskor.

          Nagyon szép megoldás.

          A feltételes formázásokat amikor csak lehet, javaslom elkerülni, mert az érintett cellákba valójában képleteket helyeznek el, (a mi esetünkben kettőt is) amelyek ráadásul szuper VOLATILE módon viselkednek.

          Azaz nem csak akkor számolódnak újra, ha bármit változtatsz a munkafüzet bármely cellájában, hanem görgetés közben is.

          Ez viszont csak nagy táblák esetén okoz gondot, egy begépelős tábla pedig általában nem szokott sok rekordot (több tíz vagy százezer) tartalmazni.

          Úgyhogy ott a pont, kösz!
          Már csak @psanta vegye észre, hogy érkezett megoldás a problémájára pár év után 🙂

          Imre

  3. Szia,
    köszönöm
    Péter

Vélemény, hozzászólás?