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.
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ű
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.
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
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.
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 #.
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.
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
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.
Idézőjel nélkül megjelenő 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.
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.
Pozitív szám IGAZ, minden egyéb 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.
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.
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.
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
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
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.
Természetesen (szinte) mindent meg lehet oldani az informatikában, többek között a kérdésedre is van válasz!
A segítséget itt találod: tizedes (és a többiek)
@horvimi <- ha valamire nem ismered a választ, inkább ne szólj hozzá!
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.
Az a macskakaparás az nem egyenlő „”-vel akart lenni mind a két helyen.
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
Szia,
köszönöm
Péter