7 hämmastavat Exceli kohandatud numbrivormingu trikki (peate teadma)

Exceli kohandatud numbrivorming on Exceli lahtrites olevate andmete riietus. Saate neid riietada nii, nagu soovite. Kõik, mida vajate, on natuke oskusteavet Exceli kohandatud numbrivormingu toimimise kohta.

Excelis kohandatud numbrivormingu abil saate muuta lahtrite väärtuste kuvamise viisi, kuid samal ajal hoiate algväärtuse puutumatuna.

Näiteks võin panna kuupäeva väärtuse kuvama erinevates kuupäevavormingutes, samas kui alusväärtus (kuupäeva tähistav number) jääb samaks.

Selles õpetuses käsitlen kõike Exceli kohandatud numbrivormingu kohta ja näitan teile ka tõeliselt kasulikke näiteid, mida saate oma igapäevatöös kasutada.

Nii et alustame!

Exceli kohandatud numbrivormingu konstruktsioon

Enne kui näitan teile selle suurepärasust, lubage mul lühidalt selgitada, kuidas kohandatud numbrivorming Excelis töötab.

Kujunduse järgi saab Excel lahtris vastu võtta nelja tüüpi andmeid:

• Positiivne number
• negatiivne arv
• 0
• Tekstistringid (see võib sisaldada nii puhta teksti stringe kui ka tähtnumbrilisi stringe)

Teine andmetüüp, mida Excel saab aktsepteerida, on kuupäevad, kuid kuna kõik kuupäeva ja kellaaja väärtused salvestatakse Excelis arvudena, kaetakse need positiivse arvu osana.

Exceli töölehe mis tahes lahtri puhul saate määrata, kuidas kõiki neid andmetüüpe lahtris kuvatakse.

Ja allpool on süntaks, milles saate selle vormingu määrata.

;;;

Pange tähele, et kõik need on eraldatud semikoolonitega (;).

Kõik, mida sisestate Exceli lahtrisse, kuulub ühte neist neljast kategooriast ja seega saab selle jaoks kohandatud vormingu määrata.

Kui mainite ainult:

• Üks formaat: Seda rakendatakse kõigi nelja sektsiooni puhul. Näiteks kui kirjutate lihtsalt üldist, rakendatakse seda kõigi nelja jaotise jaoks.
• Kaks formaati: Esimest rakendatakse positiivsetele numbritele ja nullidele ning teist negatiivsetele. Vaikimisi muutub tekstivorminguks Üldine.
• Kolm vormingut: Esimest rakendatakse positiivsetele numbritele, teist negatiivsetele, kolmandat nullile ja tekst kaob, kuna teksti jaoks pole midagi määratud.

Kui soovite lisateavet kohandatud numbrivormingu kohta, soovitan soojalt Office'i spikri jaotist.

Kuidas see töötab ja kuhu need vormingud sisestada, käsitletakse selles õpetuses hiljem. Praegu pidage meeles, et see on mis tahes lahtri vormingu süntaks

Kuidas kohandatud numbrivorming Excelis töötab?

Seejärel avate uue Exceli töövihiku, vaikimisi on kõik selle töövihiku töölehtede lahtrid üldises vormingus, kus:

• Positiivsed numbrid kuvatakse sellisena, nagu need on (joondatud vasakule)
• Negatiivsed numbrid on näidatud miinusmärgiga (ja joondatud vasakule)
• Null kuvatakse kui 0 (ja joondatud vasakule)
• Tekst kuvatakse sellisena, nagu see on (joondatud paremale)

Kui sisestate kuupäeva, kuvatakse see teie piirkonna seadete alusel (vormingus DD-MM-YYYY või MM-DD-YYYY).

Seega on lahtri numbri vormindamine vaikimisi järgmine:

Üldine; Üldine; Üldine; Kindral

Kui iga andmetüüp on vormindatud üldiseks (ja järgige eespool mainitud reegleid, lähtudes sellest, kas see on positiivne arv, negatiivne arv, 0 või tekst)

Nüüd on vaikeseade korras, aga kuidas vormingut muuta. Näiteks kui ma tahan, et minu negatiivsed numbrid kuvataks punase värviga või kui ma sooviksin, et minu kuupäevad kuvataks kindlas vormingus, siis kuidas seda teha.

Vormingu rippmenüü kasutamine lindil

Mõned kõige sagedamini kasutatavad numbrivormingud leiate grupi Number vahekaardilt Kodu.

See on rippmenüü, mis näitab paljusid levinumaid vorminguid, mida saate lahtrile rakendada, lihtsalt lahtri valides ja seejärel loendist vormingu valides.

Näiteks kui soovite teisendada numbrid protsentideks või teisendada kuupäevad lühikeseks kuupäevavorminguks või pikaajaliseks kuupäevavorminguks, saate seda hõlpsalt teha, kasutades rippmenüü valikuid.

Lahtrite vormindamise dialoogiboksi kasutamine

Lahtrite vormindamise dialoogiboksid, kus saate Exceli lahtrile vormingute rakendamisel maksimaalse paindlikkuse.

Sellel on juba palju eelvalmis vorminguid, mida saate kasutada, või kui soovite luua oma kohandatud numbrivormingu, saate seda ka teha.

Siin on, kuidas avada dialoogiboks Lahtrite vormindamine;

1. Klõpsake vahekaarti Kodu
2. Klõpsake rühmas Number numbrit dialoogiboksi käivitaja ikoonil (väike kallutatud nool rühma paremas alanurgas).

See avab dialoogiboksi Lahtrite vormindamine. Selle alt leiate kõik vormingud vahekaardilt Number (koos võimalusega luua kohandatud lahtrivorminguid)

Lahtrite vormindamise dialoogiboksi avamiseks võite kasutada ka järgmist kiirklahvi:

CONTROL + 1 (kõik juhtklahvid ja seejärel vajutage klahvi 1).

Kui kasutate Maci, saate seda kasutada CMD + 1

Nüüd toome paar praktilist näidet, kus loome oma kohandatud numbrivormingud.

Näiteid kohandatud numbrivormingu kasutamisest Excelis

Nüüd toome paar praktilist näidet, kus loome oma kohandatud numbrivormingud.

Peida lahtri väärtus (või peida kõik väärtused lahtrite vahemikus)

Mõnikord võib tekkida vajadus lahtri sisu peita (lahtrit sisaldavat rida või veergu peitmata).

Praktiline juhtum on see, kui loote Exceli armatuurlaua ja juhtpaneelil on paar väärtust, kuid te ei soovi, et kasutajad seda näeksid. nii et saate neid väärtusi lihtsalt varjata

Oletame, et teil on allpool näidatud andmekogum ja soovite kõik arvväärtused peita.

Excelis kohandatud vormindamise abil saate seda teha järgmiselt.

1. Valige lahter või lahtrivahemik, mille jaoks soovite lahtri sisu peita
2. Lahtrite vormindamise dialoogiboksi avamine (kiirklahv Control + 1)
3. Klõpsake vahekaardil Number nuppu Kohandatud
4. Sisestage väljale Tüüp järgmine vorming:

   ;;;

Kuidas see töötab?

Nelja eri tüüpi andmetüübi vorming on jagatud semikoolonitega järgmises vormingus:

;;;

Selles tehnikas eemaldasime vormingu ja määrasime ainult semikooloni, mis näitab, et me ei soovi lahtrite vormindamist. See näitab, et Excel peidab lahtris oleva sisu.

Pange tähele, et kuigi see peidab lahtri sisu, on see endiselt olemas. nii et kui teil on peidetud numbreid, saate neid siiski arvutustes kasutada (mis teeb sellest suurepärase triki Exceli armatuurlaudade ja aruannete jaoks).

Kuva negatiivsed numbrid punase värviga

Vaikimisi kuvatakse negatiivsed numbrid miinusmärgiga (või mõnes piirkondlikus seadistuses sulgudes).

Kohandatud numbrivormingu abil saate muuta negatiivsete numbrite värvi ja muuta need punaseks.

Oletame, et teil on allpool näidatud andmekogum ja soovite kõik negatiivsed väärtused punasega esile tõsta.

Allpool on toodud sammud selle tegemiseks.

1. Valige lahtrite vahemik, kus soovite negatiivseid numbreid punasega näidata
2. Lahtrite vormindamise dialoogiboksi avamine (kiirklahv Control + 1)
3. Klõpsake vahekaardil Number nuppu Kohandatud
4. Sisestage väljale Tüüp järgmine vorming:

Kindral; [Punane]-Üldine

Ülaltoodud vorming näitab negatiivse väärtusega lahtreid punase värviga.

Kuidas see töötab?

Kui määrate kohandatud numbrivorminguks ainult kaks vormingut, arvestatakse esimest positiivsete ja 0 -ga ning teist negatiivsete arvudega (järgmine väärtus on vaikimisi üldvorming).

Ülaltoodud vormingus, mida oleme kasutanud, oleme nurksulgudes määranud värvi, mida soovime selle vormingu jaoks kasutada.

Seega, kuigi negatiivsed numbrid on üldises vormingus, on need punase värviga näidatud miinusmärgiga.

Kui soovite negatiivsed numbrid sulgudes punase värviga peatada (miinusmärgi asemel), võite kasutada järgmist vormingut:

Üldine; [punane] (üldine)

Teksti lisamine numbritele (näiteks miljonitele/miljarditele)

Üks hämmastav asi kohandatud numbrivormingu puhul on see, et saate numbrile lisada eesliite või järelliite teksti, säilitades samal ajal lahtris esialgse numbri.

Näiteks võin lasta „100” kuvada kui „100 miljonit dollarit”, säilitades samas lahtris algse väärtuse, et saaksin seda arvutustes kasutada.

Oletame, et teil on allpool näidatud andmekogum ja soovite neid numbreid näidata dollarimärgi ja sõna miljon ees.

Allpool on toodud sammud selle tegemiseks.

1. Valige andmekogum, kuhu soovite teksti numbritele lisada
2. Lahtrite vormindamise dialoogiboksi avamine (kiirklahv Control + 1)
3. Klõpsake vahekaardil Number nuppu Kohandatud
4. Sisestage väljale Tüüp järgmine vorming:

$ Üldine "Miljon"

Pange tähele, et see kohandatud numbrivorming mõjutab ainult numbreid. Kui sisestate teksti samasse lahtrisse, kuvatakse see sellisena, nagu see on.

Kuidas see töötab?

Selles näites olen üldvormingusse lisanud sõna miljon kahekordse jutumärgiga. kõik, mis lisan vormingusse topeltjutumärkides, kuvatakse lahtris sellisena.

Lisasin ka üldise vormingu ette dollarimärgi, et kõik mu numbrid näitaksid ka numbri ees olevat dollarimärki.

Märkus. Kui te ei tea, miks dollarimärk ei ole topeltjutumärkides, samal ajal kui sõna Million on topeltjutumärkides, siis sellepärast, et Excel tuvastab dollari sümbolina, mida kasutatakse sageli kohandatud numbrivormingus, ja see ei nõua teid lisada sellele topelt jutumärke. Võite jätkata ja lisada dollarimärgile kahekordseid jutumärke, kuid kui sulgete dialoogiboksi vormindusrakud ja avate selle uuesti, märkate, et Excel on selle eemaldanud

Maskeeri numbrid ja tekst

Kohandatud numbrivormingu abil saate hinnata maksimaalselt kahte tingimust ja nende tingimuste põhjal saate lahtrile vormingu rakendada.

Oletame näiteks, et teil on mõne õpilase kohta punktide loend, soovite näidata tulemuse põhjal teksti Pass või Fail (kui iga skoor alla 35 peaks näitama „Fail”, muidu peaks see näitama „Pass”.

Allpool on toodud sammud tulemuste kuvamiseks kohandatud numbrivormingu asemel väärtuse asemel Pass või Fail.

1. Valige andmestik, kus teil on hinded
2. Lahtrite vormindamise dialoogiboksi avamine (kiirklahv Control + 1)
3. Klõpsake vahekaardil Number nuppu Kohandatud
4. Sisestage väljale Tüüp järgmine vorming:

   [<35] "Ebaõnnestumine"; "Läbi"

Allpool on teie andmete väljanägemine pärast ülaltoodud vormingu rakendamist.

Pange tähele, et see ei muuda lahtrite väärtust. Lahtril on jätkuvalt arvväärtuste skoor, näidates olenevalt lahtri väärtusest „Pass” või „Fail”.

Kuidas see töötab?

Selles näites tuleb tingimus nurksulgudes täpsustada. kohandatud numbrivorming hindab seejärel lahtris oleva väärtuse ja rakendab selle vormingu vastavalt.

Nii et kui lahtri väärtus on väiksem kui 35, siis number on peidetud ja selle asemel kuvatakse tekst „Ebaõnnestunud”, lisades kõik muud juhtumid, „Pass”.

Samamoodi, kui soovite teisendada rea ​​lahtreid, millel on 0 ja 1, tõeks ja valeks, saate kasutada järgmist vormingut:

[= 0] "FALSE"; [= 1] "TRUE"

Pidage meeles, et kohandatud numbrivormingus saate kasutada ainult kahte tingimust. Kui proovite kasutada rohkem kui kahte tingimust, annab see teile märku, et see ei aktsepteeri seda vormingut.

Peida tekst, kuid kuvage numbrid

Mõnikord, kui laadite oma andmed andmebaasidest alla, kuna need on tühjad, täidetakse väärtus, näiteks Pole saadaval või NA või -

Kohandatud numbrivormingu abil saate peita kõik tekstiväärtused, jättes numbrid samaks. see tagab ka teie esialgsete andmete puutumatuse (kuna te ei kustuta tekstiväärtust, peidate selle lihtsalt ära)

Oletame, et teil on allpool näidatud andmekogum, kuhu soovite kõik tekstiväärtused peita.

Allpool on toodud sammud selle tegemiseks.

1. Valige andmekogum, kus teil on andmed
2. Lahtrite vormindamise dialoogiboksi avamine (kiirklahv Control + 1)
3. Klõpsake vahekaardil Number nuppu Kohandatud
4. Sisestage väljale Tüüp järgmine vorming:

Üldine; -üldine; Üldine;

Pange tähele, et olen numbrite jaoks kasutanud üldvormingut. Saate vormindada mis tahes vormingus (näiteks 0, 0. #, #0.0%). Samuti pange tähele, et teise üldvormingu ees on negatiivne märk (-), kuna see tähistab negatiivsete numbrite vormingut.

Kuidas see töötab?

Selles näites, kuna soovisime peita kõik tekstiväärtused, ei määranud me lihtsalt selle vormingut.

Ja kuna me määrasime ülejäänud kolme andmetüübi (positiivne arv, negatiivne arv ja null) vormingu, sai Excel aru, et oleme sihilikult jätnud tekstiväärtuse vormingu tühjaks ja see ei tohiks midagi näidata, kui lahtris on tekst string.

Kuva numbrid protsendina (%)

Kohandatud numbrivormingu abil saate numbreid ka muuta ja protsentides kuvada.

Oletame, et teil on allpool näidatud andmekogum ja soovite teisendada need numbrid samaväärse protsentuaalse väärtuse kuvamiseks:

Allpool on toodud sammud selle tegemiseks.

1. Valige lahtrid, kus on numbrid (mille soovite teisendada protsendiks)
2. Lahtrite vormindamise dialoogiboksi avamine (kiirklahv Control + 1)
3. Klõpsake vahekaardil Number nuppu Kohandatud
4. Sisestage väljale Tüüp järgmine vorming:

0%

See muudab numbrid nende protsentideks. Näiteks muutub see 0,11 -lt 11%-le.

Kui kõik, mida soovite teha, on teisendada arv samaväärseks protsentväärtuseks, oleks parem viis seda teha, kui kasutada dialoogiboksi Lahtrite vormindamine suvandit Protsent.

Kohandatud vormindamine võib olla kasulik, kui soovite rohkem kontrollida protsentuaalse väärtuse kuvamist.

Näiteks kui soovite protsendiga näidata täiendavat teksti, kõik negatiivsed protsendid punase värviga, on parem kasutada kohandatud vormingut

Kuva numbrid erineva ühikuna (miljonid miljarditena või grammid kilogrammidena)

Kui töötate suurte numbritega, võiksite neid näidata teises üksuses. Näiteks kui teil on mõne ettevõtte müügiandmed, võiksite näidata müüki miljonites või miljardites (säilitades lahtrites siiski esialgse müügiväärtuse)

Õnneks saate seda hõlpsalt kohandatud numbrivormingu abil teha.

Oletame, et teil on allpool näidatud andmekogum ja soovite neid müügiväärtusi näidata miljonites.

Allpool on toodud sammud selle tegemiseks.

1. Valige lahtrid, kus on numbrid
2. Lahtrite vormindamise dialoogiboksi avamine (kiirklahv Control + 1)
3. Klõpsake vahekaardil Number nuppu Kohandatud
4. Sisestage väljale Tüüp järgmine vorming:

0.0,,

Ülaltoodu annab teile tulemuse, nagu on näidatud allpool (väärtused miljonites pärast kümnendkohta):

Kuidas see töötab?

Ülaltoodud vormingus näitab null enne komakohta, et Excel peaks näitama kogu arvu (täpselt nii, nagu see on lahtris), ja 0 pärast koma näitab, et see peaks pärast koma esitama ainult ühe olulise numbri.

Nii et 0.0 oleks alati arv, mis on ühekohaline pärast koma.

Kui lisate koma pärast numbrit 0,0, raseerib see numbri viimase kolmekohalise numbri maha. Näiteks 123456 muutuks 123,4 (kolm viimast numbrit eemaldatakse ja kuna see peab pärast koma esitama 1 numbri, näitab see 4)

Kui järele mõelda, sarnaneb see numbri jagamisega 1000 -ga. Seega, kui soovite numbrit kuvada miljonite ühikuna, peate lisama 2 koma, et see raseeriks maha viimased 6 numbrit.

Ja kui soovite numbri järel näidata ka sõna miljon või tähestikku M, kasutage allolevat vormingut

0.0 ,, "M"