7 nuostabios „Excel“ pasirinktinio skaičiaus formato gudrybės (turite žinoti)

„Excel“ pasirinktinio skaičiaus formatavimas yra „Excel“ ląstelių duomenų apranga. Galite juos rengtis taip, kaip norite. Viskas, ko jums reikia, yra šiek tiek žinių apie tai, kaip veikia „Excel“ pasirinktinio skaičiaus formatas.

Naudodami pasirinktinį skaičių formatavimą programoje „Excel“, galite pakeisti langelių reikšmių rodymo būdą, tačiau tuo pačiu išlaikydami nepažeistą pradinę vertę.

Pavyzdžiui, galiu nustatyti, kad datos reikšmė būtų rodoma skirtingais datos formatais, o pagrindinė vertė (skaičius, nurodantis datą) liktų ta pati.

Šioje pamokoje aptarsiu viską apie pasirinktinį skaičių formatą „Excel“, taip pat parodysiu tikrai naudingų pavyzdžių, kuriuos galite naudoti kasdieniame darbe.

Taigi pradėkime!

„Excel“ pasirinktinio skaičiaus formato konstrukcija

Prieš parodydamas jums nuostabumą, leiskite trumpai paaiškinti, kaip „Excel“ veikia pasirinktinis skaičių formatavimas.

Pagal konstrukciją „Excel“ lange gali priimti šių keturių tipų duomenis:

• Teigiamas skaičius
• neigiamas skaičius
• 0
• Teksto eilutės (tai gali būti gryno teksto eilutės ir raidinės ir skaitmeninės eilutės)

Kitas duomenų tipas, kurį „Excel“ gali priimti, yra datos, tačiau kadangi visos datos ir laiko reikšmės „Excel“ saugomos kaip skaičiai, jos bus įtrauktos kaip teigiamo skaičiaus dalis.

Bet kuriame „Excel“ darbalapio langelyje galite nurodyti, kaip kiekvienas iš šių duomenų tipų turi būti rodomas langelyje.

Žemiau yra sintaksė, kuria galite nurodyti šį formatą.

;;;

Atminkite, kad visa tai atskirtos kabliataškiais (;).

Viskas, ką įvedate į „Excel“ langelį, patenka į bet kurią iš šių keturių kategorijų, todėl galima nurodyti pasirinktinį jo formatą.

Jei tik paminėsite:

• Vienas formatas: Jis taikomas visiems keturiems skyriams. Pavyzdžiui, jei tiesiog parašysite Bendra, jis bus taikomas visoms keturioms skiltims.
• Du formatai: Pirmasis taikomas teigiamiems skaičiams ir nuliams, o antrasis - neigiamiems. Pagal numatytuosius nustatymus teksto formatas tampa Bendras.
• Trys formatai: Pirmasis taikomas teigiamiems skaičiams, antrasis - neigiamiems, trečias - nuliui, o tekstas dingsta, nes tekstui nieko nenurodyta.

Jei norite sužinoti daugiau apie pasirinktinį skaičių formatavimą, labai rekomenduoju „Office“ pagalbos skyrių.

Kaip tai veikia ir kur įvesti šiuos formatus, bus aptarta vėliau šioje pamokoje. Šiuo metu tiesiog nepamirškite, kad tai yra bet kurios ląstelės formato sintaksė

Kaip pritaikytas skaičių formatas veikia „Excel“

Tada atidarote naują „Excel“ darbaknygę, pagal numatytuosius nustatymus visos tos darbaknygės darbalapių ląstelės yra bendro formato, kur:

• Teigiami skaičiai rodomi tokie, kokie yra (sulygiuoti kairėje)
• Neigiami skaičiai rodomi su minuso ženklu (ir sulygiuoti kairėje)
• Nulis rodomas kaip 0 (ir sulygiuotas kairėje)
• Tekstas rodomas toks, koks yra (sulygiuotas dešinėje)

Jei įvedate datą, ji rodoma pagal jūsų regiono nustatymus (DD-MM-YYYY arba MM-DD-YYYY formatu).

Taigi pagal numatytuosius nustatymus langelio numerio formatavimas yra toks:

Bendra; Bendra; Bendra; Bendra

Kur kiekvienas duomenų tipas yra suformatuotas kaip Bendrasis (ir laikykitės anksčiau minėtų taisyklių, atsižvelgiant į tai, ar tai teigiamas skaičius, neigiamas skaičius, 0 ar tekstas)

Dabar numatytasis nustatymas yra gerai, bet kaip pakeisti formatą. Pvz., Jei noriu, kad neigiami skaičiai būtų rodomi raudona spalva arba mano datos būtų rodomos tam tikru formatu, kaip tai padaryti.

Naudojant juostos išskleidžiamąjį formatą

Kai kuriuos dažniausiai naudojamus skaičių formatus galite rasti grupės „Skaičius“ skirtuke „Pagrindinis“.

Tai išskleidžiamasis meniu, rodantis daugelį dažniausiai naudojamų formatų, kuriuos galite taikyti langeliui tiesiog pasirinkę langelį ir pasirinkę formatą iš šio sąrašo.

Pvz., Jei norite skaičius paversti procentais arba datas paversti trumpos datos formatu arba ilgos formos datos formatu, tai galite lengvai padaryti naudodami išskleidžiamojo meniu parinktis.

Dialogo lango „Format Cells“ naudojimas

Dialogo langai „Formatuoti langelius“, kuriuose gausite maksimalų lankstumą, kai reikia pritaikyti formatus langeliui „Excel“.

Jame jau yra daug iš anksto paruoštų formatų, kuriuos galite naudoti, arba jei norite sukurti savo pasirinktinį skaičių formatą, galite tai padaryti.

Štai kaip atidaryti langelių formatavimo dialogo langą;

1. Spustelėkite skirtuką Pagrindinis
2. Grupėje „Skaičius“ spustelėkite dialogo lango paleidimo priemonės piktogramą (maža pakreipta rodyklė apatiniame dešiniajame grupės kampe).

Bus atidarytas dialogo langas Langelių formatavimas. Joje rasite visus formatus skirtuke Skaičius (kartu su galimybe kurti pasirinktinius langelių formatus)

Taip pat galite naudoti šį spartųjį klavišą, kad atidarytumėte langelių formatavimo dialogo langą:

CONTROL + 1 (visi valdymo klavišai ir paspauskite 1 klavišą).

Jei naudojate „Mac“, galite naudoti CMD + 1

Dabar turėkime keletą praktinių pavyzdžių, kai sukursime savo pasirinktinius skaičių formatus.

Pasirinktinio skaičių formato naudojimo „Excel“ pavyzdžiai

Dabar turėkime keletą praktinių pavyzdžių, kai sukursime savo pasirinktinius skaičių formatus.

Slėpti langelio vertę (arba slėpti visas reikšmes ląstelių diapazone)

Kartais gali tekti paslėpti langelio turinį (neslėpiant eilutės ar stulpelio, kuriame yra langelis).

Praktinis atvejis yra tai, kai kuriate „Excel“ prietaisų skydelį ir yra keletas vertybių, kurias naudojate prietaisų skydelyje, bet nenorite, kad vartotojai tai matytų. todėl galite tiesiog paslėpti šias vertybes

Tarkime, kad turite duomenų rinkinį, kaip parodyta žemiau, ir norite paslėpti visas skaitines reikšmes:

Štai kaip tai padaryti naudojant „Excel“ pasirinktinį formatavimą:

1. Pasirinkite langelį arba langelių diapazoną, kuriam norite paslėpti langelio turinį
2. Atidarykite dialogo langą Langelių formatavimas (spartusis klavišas „Control“ + 1)
3. Skirtuke Skaičius spustelėkite Pasirinktinis
4. Lauke Tipas įveskite šį formatą:

   ;;;

Kaip tai veikia?

Keturių skirtingų tipų duomenų formatas yra padalintas kabliataškiais tokiu formatu:

;;;

Taikydami šią techniką, pašalinome formatą ir nurodėme tik kabliataškį, o tai rodo, kad nenorime jokio langelių formatavimo. Tai nurodo „Excel“ paslėpti bet kokį langelio turinį.

Atminkite, kad nors tai slepia langelio turinį, jis vis tiek yra. taigi, jei turite skaičių, kuriuos paslėpėte, vis tiek galite juos naudoti skaičiuodami (todėl tai puikus „Excel“ prietaisų skydelių ir ataskaitų triukas).

Rodyti neigiamus skaičius raudona spalva

Pagal numatytuosius nustatymus neigiami skaičiai rodomi su minuso ženklu (arba kai kuriuose regioniniuose nustatymuose skliausteliuose).

Galite naudoti pasirinktinį skaičių formatavimą, kad pakeistumėte neigiamų skaičių spalvą ir kad jie būtų rodomi raudona spalva.

Tarkime, kad turite duomenų rinkinį, kaip parodyta žemiau, ir norite visas neigiamas reikšmes paryškinti raudona spalva.

Žemiau pateikiami žingsniai, kaip tai padaryti:

1. Pasirinkite langelių diapazoną, kuriame norite rodyti neigiamus skaičius raudonai
2. Atidarykite dialogo langą Langelių formatavimas (spartusis klavišas „Control“ + 1)
3. Skirtuke Skaičius spustelėkite Pasirinktinis
4. Lauke Tipas įveskite šį formatą:

Bendra; [Raudona]-Bendra

Naudojant aukščiau pateiktą formatą, ląstelės su neigiamomis reikšmėmis bus rodomos raudonai.

Kaip tai veikia?

Kai kaip pasirinktinį skaičių formatą nurodote tik du formatus, pirmasis laikomas teigiamais skaičiais ir 0, o antrasis - neigiamais skaičiais (kita reikšmė pagal numatytuosius nustatymus tampa bendru formatu).

Naudodami aukščiau pateiktą formatą, laužtiniuose skliaustuose nurodėme spalvą, kurią norime naudoti.

Taigi, nors neigiami skaičiai yra bendro formato, jie rodomi raudona spalva su minuso ženklu.

Jei norite, kad neigiami skaičiai skliausteliuose sustotų raudonai (vietoj minuso ženklo), galite naudoti toliau pateiktą formatą:

Bendra; [Raudona] (Bendra)

Teksto pridėjimas prie skaičių (pvz., Milijonai/milijardai)

Vienas nuostabus dalykas, susijęs su pasirinktinio skaičiaus formatavimu, yra tai, kad prie skaičiaus galite pridėti priešdėlį arba priesagos tekstą, tuo pačiu išlaikydami pradinį numerį langelyje.

Pavyzdžiui, „100“ gali būti rodomas kaip „100 milijonų JAV dolerių“, o ląstelėje vis dar yra pradinė vertė, kad galėčiau ją naudoti skaičiavimuose.

Tarkime, kad turite duomenų rinkinį, kaip parodyta žemiau, ir norite šiuos skaičius parodyti su dolerio ženklu ir žodžiu milijonas priešais jį.

Žemiau pateikiami žingsniai, kaip tai padaryti:

1. Pasirinkite duomenų rinkinį, prie kurio norite pridėti tekstą prie skaičių
2. Atidarykite dialogo langą Langelių formatavimas (spartusis klavišas „Control“ + 1)
3. Skirtuke Skaičius spustelėkite Pasirinktinis
4. Lauke Tipas įveskite šį formatą:

Bendras "Milijonas"

Atminkite, kad šis pasirinktinis skaičių formatas paveiks tik skaičius. Jei įvesite tekstą į tą patį langelį, jis atrodys toks, koks yra.

Kaip tai veikia?

Šiame pavyzdyje prie bendro formato pridėjau žodį milijonas dvigubose kabutėse. viskas, ką pridedu dvigubomis kabutėmis prie formato, rodoma taip, kaip yra langelyje.

Aš taip pat pridėjau dolerio ženklą prieš bendrą formatą, kad visi mano skaičiai taip pat rodytų dolerio ženklą prieš skaičių.

Pastaba: Jei jums įdomu, kodėl dolerio ženklas nėra dvigubose kabutėse, o žodis „Million“-dvigubose kabutėse, taip yra todėl, kad „Excel“ atpažįsta dolerį kaip simbolį, dažnai naudojamą formuojant pasirinktinius skaičius, ir jums to nereikia prie jo pridėti dvigubas kabutes. Galite eiti į priekį ir pridėti dvigubų kabučių prie dolerio ženklo, tačiau kai uždarysite dialogo lango formatavimo langelius ir vėl jį atidarysite, pastebėsite, kad „Excel“ jį pašalino

Užmaskuokite skaičius ir tekstą

Naudodami pasirinktinį skaičių formatavimą, galite įvertinti ne daugiau kaip dvi sąlygas ir, remdamiesi šiomis sąlygomis, langeliui pritaikyti formatą.

Pvz., Tarkime, kad turite kai kurių studentų balų sąrašą, o pagal rezultatą norite parodyti tekstą „Atitinka“ arba „Ne“ (kur bet koks mažesnis nei 35 balas turėtų rodyti „Nepavyko“, kitaip - „Išlaikyti“.

Toliau pateikiami žingsniai, kaip rodyti balus kaip „Pass“ arba „Fail“, o ne vertę naudojant pasirinktinį skaičių formatą:

1. Pasirinkite duomenų rinkinį, kuriame yra balai
2. Atidarykite dialogo langą Langelių formatavimas (spartusis klavišas „Control“ + 1)
3. Skirtuke Skaičius spustelėkite Pasirinktinis
4. Lauke Tipas įveskite šį formatą:

   [<35] „Nepavyko“; „Priimti“

Žemiau yra tai, kaip jūsų duomenys atrodytų pritaikius aukščiau pateiktą formatą.

Atminkite, kad tai nekeičia langelių vertės. Ląstelė ir toliau turi skaičių reikšmes, tuo tarpu rodydama „Pass“ arba „Fail“, priklausomai nuo langelio vertės.

Kaip tai veikia?

Šiame pavyzdyje sąlyga turi būti nurodyta laužtiniuose skliaustuose. pasirinktinis skaičių formatavimas įvertina langelio vertę ir atitinkamai taiko formatą.

Taigi, jei langelio vertė yra mažesnė nei 35, tada skaičius yra paslėptas, o vietoje to rodomas tekstas „Nepavyko“, pridedant visus kitus atvejus, rodomas „Leidimas“.

Panašiai, jei norite konvertuoti langelių, turinčių 0 ir 1, diapazoną į TRUE ir FALSE, galite naudoti toliau nurodytą formatą.

[= 0] „FALSE“; [= 1] „TRUE“

Atminkite, kad formatuodami pasirinktą skaičių galite naudoti tik dvi sąlygas. Jei bandysite naudoti daugiau nei dvi sąlygas, jūsų raginimas praneš, kad negali priimti šio formato.

Slėpti tekstą, bet rodyti skaičius

Kartais, kai atsisiunčiate duomenis iš duomenų bazių, nes jų nėra, užpildoma tokia verte kaip „Nėra“ arba „NA“ arba -

Naudodami pasirinktinį skaičių formatavimą, galite paslėpti visas teksto reikšmes, išlaikydami skaičius taip, kaip yra. tai taip pat užtikrina, kad jūsų pradiniai duomenys yra nepažeisti (kadangi neištrinate teksto vertės, tiesiog ją paslepiate)

Tarkime, kad turite duomenų rinkinį, kaip parodyta žemiau, kur norite paslėpti visas teksto reikšmes:

Žemiau pateikiami žingsniai, kaip tai padaryti:

1. Pasirinkite duomenų rinkinį, kuriame turite duomenis
2. Atidarykite dialogo langą Formatuoti langelius (spartusis klavišas „Control“ + 1)
3. Skirtuke Skaičius spustelėkite Pasirinktinis
4. Lauke Tipas įveskite šį formatą:

Bendra; -Bendra; Bendra;

Atminkite, kad skaičiams naudojau bendrąjį formatą. Galite formuoti bet kokį formatą (pvz., 0, 0. #, #0.0%). Taip pat atkreipkite dėmesį, kad prieš antrąjį bendrąjį formatą yra neigiamas ženklas (-), nes jis reiškia neigiamų skaičių formatą.

Kaip tai veikia?

Šiame pavyzdyje, kadangi norėjome paslėpti visas teksto reikšmes, tiesiog nenurodėme jo formato.

Ir kadangi mes nurodėme kitų trijų duomenų tipų formatą (teigiamas skaičius, neigiamas skaičius ir nulis), „Excel“ suprato, kad mes tikslingai palikome tuščią teksto vertės formatą ir nieko neturėtų būti rodoma, jei langelyje yra tekstas eilutė.

Rodyti skaičius kaip procentus (%)

Naudodami pasirinktinį skaičių formatavimą, taip pat galite pakeisti skaičius ir rodyti juos procentais.

Tarkime, kad turite duomenų rinkinį, kaip parodyta žemiau, ir norite konvertuoti šiuos skaičius, kad būtų rodoma lygiavertė procentinė vertė:

Žemiau pateikiami žingsniai, kaip tai padaryti:

1. Pasirinkite langelius, kuriuose yra skaičiai (kuriuos norite konvertuoti į procentą)
2. Atidarykite dialogo langą Langelių formatavimas (spartusis klavišas „Control“ + 1)
3. Skirtuke Skaičius spustelėkite Pasirinktinis
4. Lauke Tipas įveskite šį formatą:

0%

Tai pakeis skaičius į jų procentus. Pavyzdžiui, jis keičiasi nuo 0,11 iki 11%.

Jei viskas, ką norite padaryti, yra konvertuoti skaičių į lygiavertę procentinę vertę, geresnis būdas tai padaryti būtų naudoti procentinę parinktį dialogo lango formatavimo langeliuose.

Tinkintas formatavimas gali būti naudingas, kai norite labiau kontroliuoti, kaip norite rodyti procentinę vertę.

Pvz., Jei norite rodyti papildomą tekstą su procentais, visus norimus rodyti neigiamus procentus raudona spalva, geriau naudoti pasirinktinį formatavimą

Rodyti skaičius kitu vienetu (milijonai milijardų arba gramai kaip kilogramai)

Jei dirbate su dideliais skaičiais, galbūt norėsite juos parodyti kitu vienetu. Pvz., jei turite kai kurių įmonių pardavimo duomenis, galbūt norėsite parodyti pardavimus milijonais ar milijardais (išlaikydami pradinę pardavimo vertę langeliuose)

Laimei, tai galite lengvai padaryti naudodami pasirinktinį skaičių formatavimą.

Tarkime, kad turite duomenų rinkinį, kaip parodyta žemiau, ir norite parodyti šias pardavimo vertes milijonais.

Žemiau pateikiami žingsniai, kaip tai padaryti:

1. Pasirinkite langelius, kuriuose yra skaičiai
2. Atidarykite dialogo langą Langelių formatavimas (spartusis klavišas „Control“ + 1)
3. Skirtuke Skaičius spustelėkite Pasirinktinis
4. Lauke Tipas įveskite šį formatą:

0.0,,

Pirmiau pateiktas rezultatas bus pateiktas žemiau (vertės milijonais po vieno skaitmens po kablelio):

Kaip tai veikia?

Aukščiau pateiktame formate nulis prieš dešimtainį rodo, kad „Excel“ turėtų rodyti visą skaičių (tiksliai taip, kaip yra langelyje), o 0 po kablelio rodo, kad po kablelio turėtų būti rodomas tik vienas reikšmingas skaitmuo.

Taigi 0,0 visada būtų skaičius su vienu skaitmeniu po kablelio.

Kai po kablelio pridėsite kablelį, jis nuskus paskutinį trijų skaitmenų skaitmenį. Pvz., 123456 taps 123,4 (paskutiniai trys skaitmenys pašalinami, o kadangi po kablelio turi būti rodomas 1 skaitmuo, jis rodo 4)

Jei pagalvosite, tai panašu į skaičių padalijimą iš 1000. Taigi, jei norite parodyti skaičių milijonų vienetu, turite pridėti 2 kablelius, kad jis nusiskustų paskutinius 6 skaitmenis.

O jei po skaičiaus taip pat norite parodyti žodį milijonas arba raidę M, naudokite žemiau esantį formatą

0,0 ,, "M"