7 csodálatos Excel egyéni számformátum trükk (tudnia kell)

Az Excel egyéni számformázása az Excel cellákban lévő adatok ruházata. Ezeket úgy öltözheted, ahogy szeretnéd. Csak egy kis know-how szükséges az Excel egyéni számformátum működéséhez.

Az Excel egyéni számformázásával megváltoztathatja a cellák értékeinek megjelenítési módját, ugyanakkor megőrzi az eredeti értéket.

Például beállíthatok egy dátumértéket különböző dátumformátumokban, míg a mögöttes érték (a dátumot jelző szám) ugyanaz marad.

Ebben az oktatóanyagban mindent leírok az Excel egyéni számformátumáról, és mutatok néhány igazán hasznos példát, amelyeket a mindennapi munkája során használhat.

Kezdjük hát el!

Excel egyéni számformátum -konstrukció

Mielőtt megmutatnám ennek fantasztikus jellegét, hadd röviden elmagyarázzam, hogyan működik az egyéni számformázás az Excelben.

Az Excel tervezés szerint a következő négy típusú adatot tudja elfogadni egy cellában:

• Pozitív szám
• negatív szám
• 0
• Szöveg karakterláncok (ide tartozhatnak tiszta szöveges karakterláncok és alfanumerikus karakterláncok)

Egy másik adattípus, amelyet az Excel el tud fogadni, a dátumok, de mivel az összes dátum- és időérték számként kerül tárolásra az Excelben, ezeket a pozitív szám részeként kell lefedni.

Az Excel munkalapjának bármely cellájához megadhatja, hogy az egyes adattípusok hogyan jelenjenek meg a cellában.

Alább pedig az a szintaxis, amelyben megadhatja ezt a formátumot.

;;;

Vegye figyelembe, hogy mindezeket pontosvesszővel (;) kell elválasztani.

Bármi, amit az Excel cellába ír be, a négy kategória bármelyikébe tartozik, és ezért egyéni formátum adható meg.

Ha csak megemlíti:

• Egy formátum: Mind a négy szakaszra vonatkozik. Például, ha csak az Általános szót írja, akkor mind a négy szakaszra érvényes lesz.
• Két formátum: Az első pozitív számokra és nullákra, a második pedig negatív számokra vonatkozik. A szövegformátum alapértelmezés szerint Általános lesz.
• Három formátum: Az elsőt pozitív számokra alkalmazzák, a másodikat negatív számokra, a harmadikat nullára, és a szöveg eltűnik, mivel a szöveghez nincs megadva semmi.

Ha többet szeretne megtudni az egyéni számformázásról, nagyon ajánlom az Office súgóját.

Ennek a működésének módjáról és a formátumok megadásának módjáról később ebben az oktatóanyagban lesz szó. Egyelőre csak ne feledje, hogy ez a szintaxis bármely cella formátumához

Hogyan működik az egyéni számformátum az Excelben?

Ezután megnyit egy új Excel munkafüzetet, alapértelmezés szerint az adott munkafüzet összes munkalapjának összes cellája az Általános formátumú, ahol:

• Pozitív számok jelennek meg (balra igazítva)
• Negatív számok mínusz jellel vannak jelölve (és balra igazítva)
• Nulla 0 -ként jelenik meg (és balra igazítva)
• Szöveg úgy jelenik meg, ahogy van (jobbra igazítva)

Ha pedig dátumot ad meg, az a regionális beállítások alapján jelenik meg (DD-MM-YYYY vagy MM-DD-YYYY formátumban).

Tehát alapértelmezés szerint a cella számformázása a következő:

Általános; Általános; Általános; Általános

Az egyes adattípusok általános formátumúak (és kövesse a fent említett szabályokat, attól függően, hogy pozitív szám, negatív szám, 0 vagy szöveg)

Most az alapértelmezett beállítás rendben van, de hogyan lehet megváltoztatni a formátumot. Például, ha azt szeretném, hogy a negatív számok piros színben jelenjenek meg, vagy azt, hogy a dátumaim meghatározott formátumban jelenjenek meg, akkor hogyan tegyem ezt.

A Formátum legördülő menü használata a szalagon

A leggyakrabban használt számformátumok közül néhány megtalálható a Kezdőlap lapon a Számcsoporton belül.

Ez egy legördülő menü, amely számos gyakran használt formátumot mutat, amelyeket alkalmazhat egy cellára, ha egyszerűen kiválasztja a cellát, majd kiválasztja a formátumot a listából.

Ha például a számokat százalékos értékre szeretné konvertálni, vagy a dátumokat rövid dátumformátumra vagy hosszú formátumú dátumformátumra szeretné konvertálni, akkor ezt könnyedén megteheti a legördülő menüben található opciók használatával.

A Format Cells párbeszédpanel használata

Formázza a cellák párbeszédpaneleket, ahol a lehető legnagyobb rugalmasságot kapja, amikor a formátumokat egy cellában alkalmazza az Excelben.

Már sok előre elkészített formátumot tartalmaz, amelyeket használhat, vagy ha saját egyéni számformátumot szeretne létrehozni, akkor ezt is megteheti.

A cellák formázása párbeszédpanel megnyitása a következőképpen történik;

1. Kattintson a Kezdőlap fülre
2. A Szám csoportban kattintson a párbeszédpanel indító ikonjára (a kis jobbra dőlt nyíl a csoport jobb alsó sarkában).

Ezzel megnyílik a Cellák formázása párbeszédpanel. Ezen belül az összes formátumot megtalálja a Szám lapon (az egyéni cellaformátumok létrehozásának lehetőségével együtt)

A következő billentyűparanccsal is megnyithatja a cellák formázása párbeszédpanelt:

CONTROL + 1 (az összes Control billentyűt, majd nyomja meg az 1 gombot).

Ha Macet használ, használhatja CMD + 1

Lássunk néhány gyakorlati példát, ahol saját egyedi számformátumokat hozunk létre.

Példák az egyéni számformázás használatára Excelben

Lássunk néhány gyakorlati példát, ahol saját egyedi számformátumokat hozunk létre.

Cellaérték elrejtése (vagy az összes érték elrejtése egy sejttartományban)

Előfordulhat, hogy el kell rejtenie a cella tartalmát (anélkül, hogy elrejtené a cellát tartalmazó sort vagy oszlopot).

Ennek gyakorlati példája az, amikor Excel irányítópultot hoz létre, és néhány értéket használ az irányítópulton, de nem szeretné, hogy a felhasználók lássák. így egyszerűen elrejtheti ezeket az értékeket

Tegyük fel, hogy rendelkezik az alább látható adatkészlettel, és el szeretné rejteni az összes számértéket:

Ezt az Excel egyéni formázásával teheti meg:

1. Válassza ki azt a cellát vagy cellatartományt, amelyhez el szeretné rejteni a cellatartalmat
2. Nyissa meg a Cellaformázás párbeszédpanelt (Control + 1 billentyűparancs)
3. A Szám lapon kattintson az Egyéni gombra
4. Írja be a következő formátumot a Típus mezőbe:

   ;;;

Hogy működik ez?

A négy különböző típusú adattípus formátuma pontosvesszővel van osztva a következő formátumban:

;;;

Ebben a technikában eltávolítottuk a formátumot, és csak a pontosvesszőt adtuk meg, ami azt jelzi, hogy nem szeretnénk semmilyen formázást a cellákhoz. Ez azt jelzi, hogy az Excel elrejti a cellában található tartalmat.

Vegye figyelembe, hogy bár ez elrejti a cella tartalmát, még mindig ott van. így ha vannak számai, amelyeket elrejtett, akkor is használhatja ezeket a számítások során (ez nagyszerű trükk az Excel irányítópultjaihoz és jelentéseihez).

Negatív számok megjelenítése vörös színben

Alapértelmezés szerint a negatív számok mínuszjellel jelennek meg (vagy bizonyos regionális beállításokban zárójelben).

Egyéni számformázással módosíthatja a negatív számok színét, és piros színnel jelenítheti meg őket.

Tegyük fel, hogy rendelkezik egy adathalmazzal az alábbiak szerint, és az összes negatív értéket piros színnel szeretné kiemelni.

Ennek lépései az alábbiakban találhatók:

1. Válassza ki azt a cellatartományt, ahol a negatív számokat pirossal szeretné megjeleníteni
2. Nyissa meg a Cellaformázás párbeszédpanelt (Control + 1 billentyűparancs)
3. A Szám lapon kattintson az Egyéni gombra
4. Írja be a következő formátumot a Típus mezőbe:

Általános; [Piros]-Általános

A fenti formátum a negatív értékű cellákat vörös színnel jeleníti meg.

Hogy működik ez?

Ha csak két formátumot ad meg egyéni számformátumként, akkor az első a pozitív számok és a 0, a második pedig a negatív számok esetében (a következő érték alapértelmezés szerint az általános formátum lesz).

A fenti formátumban, amelyet használtunk, a szögletes zárójelben megadtuk azt a színt, amelyet szeretnénk, hogy ez a formátum felvegyen.

Tehát míg a negatív számok az általános formátumot veszik fel, piros színnel és mínusz jellel jelennek meg.

Ha azt szeretné, hogy a negatív számok zárójelben piros színnel álljanak le (a mínuszjel helyett), akkor használja az alábbi formátumot:

Általános; [Piros] (Általános)

Szöveg hozzáadása a számokhoz (például millió/milliárd)

Egy csodálatos dolog az egyéni számformázásban az, hogy hozzáadhat egy előtagot vagy utótagszöveget a számhoz, miközben megtartja az eredeti számot a cellában.

Például beállíthatom, hogy a „100” „100 millió dollár” -ként jelenjen meg, miközben továbbra is megmarad az eredeti érték a cellában, így felhasználhatom a számításokban.

Tegyük fel, hogy rendelkezik egy adathalmazzal, amint az alább látható, és ezeket a számokat dollárjellel és a millió szóval szeretné megjeleníteni.

Ennek lépései az alábbiakban találhatók:

1. Válassza ki azt az adatkészletet, amelyhez a szöveget hozzá szeretné adni a számokhoz
2. Nyissa meg a Cellaformázás párbeszédpanelt (Control + 1 billentyűparancs)
3. A Szám lapon kattintson az Egyéni gombra
4. Írja be a következő formátumot a Típus mezőbe:

Általános "Million"

Ne feledje, hogy ez az egyéni számformátum csak a számokra lesz hatással. Ha ugyanabba a cellába ír be szöveget, úgy fog megjelenni.

Hogy működik ez?

Ebben a példában az általános formátumhoz dupla idézőjelben adtam hozzá a millió szót. minden, amit idézőjelben adok hozzá a formátumhoz, a cellában látható módon jelenik meg.

A dollárjelet is hozzáadtam az általános formátum elé, hogy minden számom a dollárjelet is a szám előtt jelenítse meg.

Megjegyzés: Ha kíváncsi, miért nincs a dollárjel kettős idézőjelben, míg a Millió szó kettős idézőjelben van, akkor az Excel felismeri a dollárt olyan szimbólumként, amelyet gyakran használnak az egyéni számformázásban, és nem igényel hogy idézőjeleket fűzzön hozzá. Mehet tovább, és dupla idézőjelet adhat a dollárjelhez, de amikor bezárja a cellák formázása párbeszédpanelt, és újra megnyitja, észre fogja venni, hogy az Excel eltávolította

Álcázott számok és szöveg

Az egyéni számformázással legfeljebb két feltételt értékelhet, és ezek alapján alkalmazhat formátumot a cellára.

Tegyük fel például, hogy van egy listája egyes tanulók pontszámairól, és a pontszám alapján szeretné megjeleníteni a Pass vagy Fail szöveget (ahol a 35 -nél kisebb pontszámnak a „Fail” feliratot kell mutatnia, ellenkező esetben a „Pass” -t kell megjelenítenie.

Az alábbiakban bemutatjuk azokat a lépéseket, amelyekkel a pontszámokat sikeres vagy nem értékként jelenítjük meg az érték helyett az egyéni számformátum használatával:

1. Válassza ki azt az adatkészletet, ahol a pontszámok találhatók
2. Nyissa meg a Cellaformázás párbeszédpanelt (Control + 1 billentyűparancs)
3. A Szám lapon kattintson az Egyéni gombra
4. Írja be a következő formátumot a Típus mezőbe:

   [<35] "Sikertelen"; "Jóváhagyás"

Az alábbiakban bemutatjuk, hogyan néznének ki az adatok a fenti formátum alkalmazása után.

Ne feledje, hogy ez nem változtatja meg a cellák értékét. A cella továbbra is rendelkezik numerikus értékekkel, miközben a cella értékétől függően megjelenik a „Pass” vagy a „Fail”.

Hogy működik ez?

Ebben a példában a feltételt a szögletes zárójelben kell megadni. az egyéni számformázás ezután értékeli a cella értékét, és ennek megfelelően alkalmazza a formátumot.

Tehát ha a cella értéke kisebb, mint 35, akkor a szám el van rejtve, helyette a „Fail” szöveg jelenik meg, az összes többi eset hozzáadásával, „Pass”.

Hasonlóképpen, ha a 0 -s és 1 -es cellatartományt IGAZ és HAMIS értékre szeretné konvertálni, akkor használja az alábbi formátumot:

[= 0] "HAMIS"; [= 1] "IGAZ"

Ne feledje, hogy az egyéni számformázásban csak két feltételt használhat. Ha kettőnél több feltételt próbál használni, akkor a rendszer figyelmezteti Önt, hogy nem tudja elfogadni ezt a formátumot.

Szöveg elrejtése, de számok megjelenítése

Néha, amikor adatokat tölt le az adatbázisokból, mivel azok üresek, olyan értékekkel vannak feltöltve, mint a Nem elérhető vagy NA vagy -

Az egyéni számformázással elrejtheti az összes szöveges értéket, miközben a számokat változatlanul tartja. ez biztosítja az eredeti adatok sértetlenségét is (mivel nem törli a szöveges értéket, csak egyszerűen elrejti)

Tegyük fel, hogy rendelkezik egy adathalmazzal az alábbiak szerint, ahol el szeretné rejteni az összes szövegértéket:

Ennek lépései az alábbiakban találhatók:

1. Válassza ki azt az adatkészletet, ahol az adatok vannak
2. Nyissa meg a Cellaformázás párbeszédpanelt (Control + 1 billentyűparancs)
3. A Szám lapon kattintson az Egyéni gombra
4. Írja be a következő formátumot a Típus mezőbe:

Tábornok; -Tábornok; Tábornok;

Ne feledje, hogy az általános formátumot használtam a számokhoz. Bármilyen formátumot megadhat (például 0, 0. #, #0.0%). Ezenkívül vegye figyelembe, hogy a második általános formátum előtt van egy negatív előjel (-), mivel ez a negatív számok formátumát jelenti.

Hogy működik ez?

Ebben a példában, mivel el akartuk rejteni az összes szövegértéket, egyszerűen nem adtuk meg a formátumot.

És mivel megadtuk a formátumot a másik három adattípushoz (pozitív szám, negatív szám és nulla), az Excel megértette, hogy szándékosan hagytuk üresen a szövegérték formátumát, és nem jeleníthet meg semmit, ha egy cellában van szöveg húr.

Számok megjelenítése százalékban (%)

Az egyéni számformázással a számokat is módosíthatja, és százalékban megjelenítheti.

Tegyük fel, hogy rendelkezik egy adathalmazzal az alábbiak szerint, és ezeket a számokat át szeretné konvertálni az ezzel megegyező százalékos értékre:

Ennek lépései az alábbiakban találhatók:

1. Válassza ki azokat a cellákat, amelyekben a számok találhatók (amelyeket százalékba szeretne konvertálni)
2. Nyissa meg a Cellaformázás párbeszédpanelt (Control + 1 billentyűparancs)
3. A Szám lapon kattintson az Egyéni gombra
4. Írja be a következő formátumot a Típus mezőbe:

0%

Ez a számokat a százalékukra változtatja. Például 0,11 -ről 11%-ra változik.

Ha csak annyit szeretne tenni, hogy egy számot átalakít egyenértékű százalékos értékké, akkor erre jobb megoldás a cellák formázása párbeszédpanel százalékos beállítása.

Az egyéni formázás akkor hasznos, ha jobban szeretné szabályozni a százalékos érték megjelenítését.

Például, ha további szöveget szeretne megjeleníteni a százalékos értékkel, és minden negatív százalékot piros színnel szeretne megjeleníteni, akkor jobb, ha egyéni formázást használ

Számok megjelenítése más mértékegységben (Milliók milliárdokban vagy Grammokban kilogrammban)

Ha nagy számokkal dolgozik, érdemes ezeket más egységekben megjeleníteni. ha például rendelkezik egyes vállalatok értékesítési adataival, akkor érdemes milliós vagy milliárdos forgalmat megjeleníteni (miközben továbbra is megtartja az eredeti értékesítési értéket a cellákban)

Szerencsére ezt könnyen megteheti egyéni számformázással.

Tegyük fel, hogy rendelkezik adathalmazzal az alábbiak szerint, és ezeket az értékesítési értékeket millióban szeretné megjeleníteni.

Ennek lépései az alábbiakban találhatók:

1. Válassza ki azokat a cellákat, ahol a számok találhatók
2. Nyissa meg a Cellaformázás párbeszédpanelt (Control + 1 billentyűparancs)
3. A Szám lapon kattintson az Egyéni gombra
4. Írja be a következő formátumot a Típus mezőbe:

0.0,,

A fentiek az alábbi eredményt adják (az értékek millióban, egy számjegyben a tizedesjegy után):

Hogy működik ez?

A fenti formátumban a tizedes előtti nulla azt jelzi, hogy az Excelnek a teljes számot kell megjelenítenie (pontosan úgy, ahogy a cellában van), a 0 -as tizedesjegy után pedig azt, hogy csak egy jelentős számjegyet kell megjelenítenie a tizedespont után.

Tehát a 0.0 mindig egy számjegy lenne egy tizedesjegy után.

Ha vesszőt ad hozzá a 0.0 után, az leborotválja a szám utolsó három számjegyét. Például az 123456 123,4 lesz (az utolsó három számjegy törlődik, és mivel a tizedespont után 1 számjegyet kell mutatnia, ez 4 -et mutat)

Ha jobban belegondol, ez hasonló ahhoz, hogy a számot el kell osztani 1000 -gyel. Tehát ha egy számot a „milliós” egységben szeretne megjeleníteni, akkor 2 vesszőt kell hozzáadnia, hogy az leborotválhassa az utolsó 6 számjegyet.

Ha pedig a millió szót vagy az M ábécét is szeretné megjeleníteni a szám után, használja az alábbi formátumot

0.0 ,, "M"