7 nevjerojatnih trikova u Excel formatu prilagođenog broja (morate znati)

Formatiranje prilagođenog broja u Excelu odjeća je za podatke u Excel ćelijama. Možete ih odjenuti kako želite. Sve što trebate je znanje o načinu rada Excel prilagođenog formata brojeva.

Uz prilagođeno oblikovanje brojeva u Excelu možete promijeniti način na koji se vrijednosti u ćelijama prikazuju, ali istodobno zadržati izvornu vrijednost netaknutom.

Na primjer, mogu učiniti da se vrijednost datuma prikazuje u različitim formatima datuma, dok bi temeljna vrijednost (broj koji predstavlja datum) ostala ista.

U ovom ću vodiču pokriti sve o formatu prilagođenog broja u Excelu, a također ću vam pokazati neke zaista korisne primjere koje možete koristiti u svom svakodnevnom poslu.

Pa krenimo!

Excel Konstrukcija prilagođenog formata broja

Prije nego što vam pokažem njegovu izvrsnost, dopustite mi ukratko objasniti kako prilagođeno oblikovanje brojeva funkcionira u Excelu.

Po dizajnu, Excel može prihvatiti sljedeće četiri vrste podataka u ćeliji:

• Pozitivan broj
• negativan broj
• 0
• Tekstualni nizovi (ovo može uključivati ​​čiste tekstualne nizove kao i alfanumeričke nizove)

Druga vrsta podataka koju Excel može prihvatiti su datumi, ali budući da su sve vrijednosti datuma i vremena pohranjene kao brojevi u Excelu, oni bi bili obuhvaćeni kao dio pozitivnog broja.

Za bilo koju ćeliju na radnom listu u Excelu možete odrediti kako bi se svaka od ovih vrsta podataka trebala prikazivati ​​u ćeliji.

I ispod je sintaksa u kojoj možete navesti ovaj format.

;;;

Imajte na umu da su sve to odvojene točkom i zarezom (;).

Sve što unesete u ćeliju u Excelu spada u bilo koju od ove četiri kategorije, pa se za nju može navesti prilagođeni format.

Ako spomenete samo:

• Jedan format: Primjenjuje se na sva četiri odjeljka. Na primjer, ako samo napišete Općenito, primijenit će se za sva četiri odjeljka.
• Dva formata: Prvi se primjenjuje na pozitivne brojeve i nule, a drugi na negativne brojeve. Format teksta prema zadanim postavkama postaje Općenito.
• Tri formata: Prvi se primjenjuje na pozitivne brojeve, drugi se primjenjuje na negativne brojeve, treći se primjenjuje na nulu, a tekst nestaje jer ništa nije navedeno za tekst.

Ako želite saznati više o oblikovanju prilagođenog broja, toplo bih vam preporučio odjeljak Pomoć za Office.

Kako to funkcionira i gdje unijeti te formate, kasnije je opisano u ovom vodiču. Zasad samo imajte na umu da je ovo sintaksa za format bilo koje ćelije

Kako prilagođeni format broja funkcionira u Excelu

Zatim otvorite novu radnu knjigu programa Excel, prema zadanim postavkama sve ćelije na svim radnim listovima u toj radnoj knjizi imaju format Općenito gdje:

• Pozitivni brojevi prikazane su kakve jesu (poravnane lijevo)
• Negativni brojevi prikazani su znakom minus (poravnati lijevo)
• Nula prikazuje se kao 0 (i poravnato lijevo)
• Tekst prikazano je kako jest (poravnato desno)

A u slučaju da unesete datum, on će se prikazati na temelju vaše regionalne postavke (u formatu DD-MM-GGGG ili MM-DD-GGGG).

Dakle, prema zadanim postavkama oblikovanje broja ćelije je:

Općenito; Općenito; Općenito; Općenito

Gdje je svaka vrsta podataka oblikovana kao općenita (i slijedite gore navedena pravila, ovisno o tome je li riječ o pozitivnom broju, negativnom broju, 0 ili tekstu)

Sada je zadana postavka u redu, ali kako promijeniti format. Na primjer, ako želim da se moji negativni brojevi pojavljuju u crvenoj boji ili da se moji datumi prikazuju u određenom formatu, kako to mogu učiniti.

Korištenje padajućeg formata na vrpci

Neke od najčešće korištenih formata brojeva možete pronaći na kartici Početna unutar grupe Broj.

To je padajući izbornik koji prikazuje mnoge najčešće korištene formate koje možete primijeniti na ćeliju jednostavnim odabirom ćelije, a zatim odabirom formata s ovog popisa.

Na primjer, ako želite pretvoriti brojeve u postotke ili pretvoriti datume u format kratkog datuma ili format datuma u dugom obliku, to možete jednostavno učiniti pomoću opcija na padajućem izborniku.

Upotreba dijaloškog okvira Oblikovanje ćelija

Dijaloški okviri za oblikovanje ćelija u kojima dobivate maksimalnu fleksibilnost kada se radi o primjeni formata na ćeliju u Excelu.

Već ima mnogo unaprijed pripremljenih formata koje možete koristiti, ili ako želite stvoriti vlastiti format prilagođenog broja, možete to učiniti i vi.

Evo kako otvoriti dijaloški okvir Oblikovanje ćelija;

1. Kliknite karticu Početna
2. U grupi Broj kliknite ikonu pokretača dijaloškog okvira (mala nagnuta strelica u donjem desnom kutu grupe).

Ovo će otvoriti dijaloški okvir Oblikovanje ćelija. Unutar njega ćete pronaći sve formate na kartici Broj (zajedno s mogućnošću stvaranja prilagođenih formata ćelija)

Možete koristiti i sljedeću tipkovnu prečicu za otvaranje dijaloškog okvira za oblikovanje ćelija:

CONTROL + 1 (sve tipke Control, a zatim pritisnite tipku 1).

U slučaju da koristite Mac, možete koristiti CMD + 1

Sada imamo nekoliko praktičnih primjera gdje ćemo stvoriti vlastite prilagođene formate brojeva.

Primjeri korištenja prilagođenog oblikovanja brojeva u Excelu

Sada imamo nekoliko praktičnih primjera gdje ćemo stvoriti vlastite prilagođene formate brojeva.

Sakrij vrijednost ćelije (ili sakrij sve vrijednosti u rasponu ćelija)

Ponekad ćete možda morati sakriti sadržaj ćelije (bez skrivanja retka ili stupca koji sadrži ćeliju).

Praktičan je to slučaj pri stvaranju nadzorne ploče programa Excel i postoji nekoliko vrijednosti koje koristite na nadzornoj ploči, ali ne želite da je korisnici vide. tako da te vrijednosti možete jednostavno sakriti

Pretpostavimo da imate skup podataka kao što je prikazano u nastavku i želite sakriti sve numeričke vrijednosti:

Evo kako to učiniti pomoću prilagođenog oblikovanja u Excelu:

1. Odaberite ćeliju ili raspon ćelija za koje želite sakriti sadržaj ćelije
2. Otvorite dijaloški okvir Oblikovanje ćelija (tipkovni prečac Control + 1)
3. Na kartici Broj kliknite Prilagođeno
4. U polje Vrsta unesite sljedeći format:

   ;;;

Kako ovo radi?

Format četiri različite vrste podataka podijeljen je zarezom u sljedećem formatu:

;;;

U ovoj smo tehnici uklonili format i naveli samo točku -zarez, što znači da ne želimo nikakvo oblikovanje ćelija. Ovo je pokazatelj da Excel sakrije sav sadržaj koji se nalazi u ćeliji.

Imajte na umu da, iako ovo skriva sadržaj ćelije, ona je još uvijek tamo. pa ako imate brojeve koje ste sakrili, i dalje ih možete koristiti u izračunima (što ovo čini izvrstan trik za nadzorne ploče i izvješća programa Excel).

Prikažite negativne brojeve u crvenoj boji

Prema zadanim postavkama, negativni brojevi prikazuju se sa znakom minus (ili unutar zagrada u nekim regionalnim postavkama).

Možete koristiti prilagođeno oblikovanje brojeva za promjenu boje negativnih brojeva i njihovo pojavljivanje u crvenoj boji.

Pretpostavimo da imate skup podataka kao što je prikazano u nastavku i želite sve negativne vrijednosti istaknuti crvenom bojom.

U nastavku su navedeni koraci za to:

1. Odaberite raspon ćelija u kojima želite prikazati negativne brojeve crvenom bojom
2. Otvorite dijaloški okvir Oblikovanje ćelija (tipkovni prečac Control + 1)
3. Na kartici Broj kliknite Prilagođeno
4. U polje Vrsta unesite sljedeći format:

Općenito; [Crveno] -Općenito

Gore navedeni format učinit će vaše ćelije s negativnim vrijednostima prikazane crvenom bojom.

Kako ovo radi?

Kad navedete samo dva formata kao format prilagođenog broja, prvi se uzima u obzir za pozitivne brojeve i 0, a drugi za negativne brojeve (sljedeća vrijednost prema zadanim postavkama postaje opći format).

U gore navedenom formatu koji smo koristili, naveli smo boju koju želimo da taj format preuzme u uglatim zagradama.

Dakle, iako negativni brojevi imaju opći format, oni su prikazani crvenom bojom sa znakom minus.

U slučaju da želite da se negativni brojevi zaustave crvenom bojom unutar zagrada (umjesto znaka minus), možete upotrijebiti donji format:

Općenito; [Crveno] (Općenito)

Dodajte tekst u brojeve (kao što su milijuni/milijarde)

Jedna nevjerojatna stvar kod oblikovanja prilagođenog broja je to što broju možete dodati prefiks ili tekst sufiksa, a da izvorni broj ostane u ćeliji.

Na primjer, mogu imati da se "100" prikaže kao "100 milijuna dolara", a da i dalje ima izvornu vrijednost u ćeliji kako bih je mogao koristiti u izračunima.

Pretpostavimo da imate skup podataka kao što je prikazano u nastavku i želite te brojeve prikazati znakom dolara i riječju milijun ispred njega.

U nastavku su navedeni koraci za to:

1. Odaberite skup podataka u koji želite dodati tekst brojevima
2. Otvorite dijaloški okvir Oblikovanje ćelija (tipkovni prečac Control + 1)
3. Na kartici Broj kliknite Prilagođeno
4. U polje Vrsta unesite sljedeći format:

Opći milijun dolara

Imajte na umu da će ovaj prilagođeni format broja utjecati samo na brojeve. Ako tekst unesete u istu ćeliju, prikazat će se onakav kakav jest.

Kako ovo radi?

U ovom primjeru općem formatu dodao sam riječ milijun u dvostrukim navodnicima. sve što dodam u dvostruke navodnike formatu prikazano je kao što je u ćeliji.

Također sam dodao znak dolara prije općeg formata tako da svi moji brojevi također pokazuju znak dolara ispred broja.

Napomena: Ako se pitate zašto znak dolara nije u dvostrukim navodnicima, a riječ Million u dvostrukim navodnicima, to je zato što Excel prepoznaje dolar kao simbol koji se često koristi u oblikovanju prilagođenog broja i ne zahtijeva vas da mu se dodaju dvostruki navodnici. Možete nastaviti i dodavati dvostruke navodnike u znak dolara, ali kad zatvorite dijaloški okvir za oblikovanje ćelija i ponovno ga otvorite, primijetit ćete da ga je Excel uklonio

Prikrijte brojeve i tekst

Uz oblikovanje prilagođenog broja možete procijeniti najviše dva uvjeta, a na temelju tih uvjeta možete primijeniti format na ćeliju.

Na primjer, pretpostavimo da imate popis bodova za neke učenike, želite prikazati tekst Uspješno ili Neuspješno na temelju rezultata (gdje bi bilo koja ocjena manja od 35 trebala pokazati "Nije uspjela", inače bi trebala pokazati "Proći").

U nastavku su navedeni koraci za prikazivanje rezultata kao uspješni ili neuspješni umjesto vrijednosti u prilagođenom formatu broja:

1. Odaberite skup podataka u kojem imate ocjene
2. Otvorite dijaloški okvir Oblikovanje ćelija (tipkovni prečac Control + 1)
3. Na kartici Broj kliknite Prilagođeno
4. U polje Vrsta unesite sljedeći format:

   [<35] "Nije uspjelo"; "Prošlo"

U nastavku je prikazano kako bi vaši podaci izgledali nakon što primijenite gornji format.

Imajte na umu da to ne mijenja vrijednost u ćelijama. Ćelija i dalje ima ocjene u numeričkim vrijednostima dok prikazuje "Proslijedi" ili "Nije uspjelo" ovisno o vrijednosti ćelije.

Kako ovo radi?

U ovom primjeru uvjet je potrebno navesti unutar uglatih zagrada. oblikovanje prilagođenog broja tada procjenjuje vrijednost u ćeliji i primjenjuje format u skladu s tim.

Dakle, ako je vrijednost u ćeliji manja od 35, tada je broj skriven, a umjesto toga prikazuje se tekst 'Neuspjelo', dodajući sve ostale slučajeve, prikazuje se 'Prođi'.

Slično, ako želite pretvoriti niz ćelija koje imaju 0 i 1 u TRUE i FALSEs, možete upotrijebiti donji format:

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

Zapamtite da u oblikovanju prilagođenog broja možete koristiti samo dva uvjeta. Ako pokušate upotrijebiti više od dva uvjeta, vaš će se upit tako obavijestiti da ne može prihvatiti taj format.

Sakrij tekst, ali prikaži brojeve

Ponekad, kada preuzimate svoje podatke iz baza podataka, budući da su prazne, ispunjene su vrijednostima poput Nedostupno ili NA ili -

Uz prilagođeno oblikovanje brojeva možete sakriti sve tekstualne vrijednosti, a da brojevi ostanu takvi kakvi jesu. to također osigurava da su vaši izvorni podaci netaknuti (budući da ne brišete tekstualnu vrijednost, samo je jednostavno skrivate)

Pretpostavimo da imate skup podataka kao što je prikazano u nastavku gdje želite sakriti sve tekstualne vrijednosti:

U nastavku su navedeni koraci za to:

1. Odaberite skup podataka u kojem imate podatke
2. Otvorite dijaloški okvir Oblikovanje ćelija (tipkovni prečac Control + 1)
3. Na kartici Broj kliknite Prilagođeno
4. U polje Vrsta unesite sljedeći format:

Općenito; -Općenito; Općenito;

Imajte na umu da sam za brojeve koristio format Općenito. Možete oblikovati bilo koji format (na primjer 0, 0. #, #0.0%). Također imajte na umu da postoji negativan znak (-) prije drugog općeg formata, jer predstavlja format za negativne brojeve.

Kako ovo radi?

U ovom primjeru, budući da smo htjeli sakriti sve tekstualne vrijednosti, jednostavno nismo naveli format za to.

A budući da smo naveli format za ostale tri vrste podataka (pozitivan broj, negativan broj i nula), Excel je shvatio da smo namjerno ostavili format tekstualne vrijednosti praznim i ne bi trebao ništa prikazivati ​​ako ćelija ima tekst niz.

Prikaži brojeve kao postotke (%)

Uz prilagođeno oblikovanje brojeva možete promijeniti i brojeve i prikazati ih u postocima.

Pretpostavimo da imate skup podataka kao što je prikazano u nastavku i želite pretvoriti ove brojeve u ekvivalentnu postotnu vrijednost:

U nastavku su navedeni koraci za to:

1. Odaberite ćelije u kojima imate brojeve (koje želite pretvoriti u postotke)
2. Otvorite dijaloški okvir Oblikovanje ćelija (tipkovni prečac Control + 1)
3. Na kartici Broj kliknite Prilagođeno
4. U polje Vrsta unesite sljedeći format:

0%

To će promijeniti brojeve u njihove postotke. Na primjer, mijenja se s 0,11 na 11%.

Ako sve što želite učiniti pretvorite broj u ekvivalentnu postotnu vrijednost, bolji način za to bi bio upotreba opcije postotka u dijaloškom okviru za oblikovanje ćelija.

Prilagođeno oblikovanje može biti korisno kada želite veću kontrolu nad načinom prikazivanja vrijednosti postotka.

Na primjer, ako želite prikazati dodatni tekst s postotkom, sve što želite prikazati negativne postotke u crvenoj boji, bolje je koristiti prilagođeno oblikovanje

Prikaz brojeva u različitim jedinicama (milijuni u milijardama ili gram u kilogramima)

Ako radite s velikim brojevima, možda ćete ih htjeti prikazati u drugoj jedinici. na primjer, ako imate podatke o prodaji nekih tvrtki, možda ćete htjeti prikazati prodaju u milijunima ili milijardama (zadržavajući izvornu vrijednost prodaje u ćelijama)

Srećom, to možete lako učiniti s prilagođenim oblikovanjem brojeva.

Pretpostavimo da imate skup podataka kao što je prikazano u nastavku i želite prikazati te prodajne vrijednosti u milijunima.

U nastavku su navedeni koraci za to:

1. Odaberite ćelije u kojima imate brojeve
2. Otvorite dijaloški okvir Oblikovanje ćelija (tipkovni prečac Control + 1)
3. Na kartici Broj kliknite Prilagođeno
4. U polje Vrsta unesite sljedeći format:

0.0,,

Gore navedeno dalo bi vam rezultat kao što je prikazano u nastavku (vrijednosti u milijunima u jednoj znamenci nakon decimalnog mjesta):

Kako ovo radi?

U gore navedenom formatu nula ispred decimalnog mjesta označava da bi Excel trebao prikazati cijeli broj (točno onakav kakav je u ćeliji), a 0 nakon decimalnog mjesta znači da bi trebao pokazati samo jednu značajnu znamenku iza decimalnog zareza.

Dakle, 0.0 bi uvijek bio broj s jednom znamenkom iza decimalne točke.

Kad nakon 0.0 dodate zarez, izbrisat će se posljednja troznamenkasta brojka. Na primjer, 123456 postalo bi 123,4 (zadnje tri znamenke se uklanjaju, a budući da mora prikazivati ​​1 znamenku nakon decimalne točke, prikazuje 4)

Ako razmislite o tome, to je slično dijeljenju broja sa 1000. Dakle, ako želite prikazati broj u jedinici 'milijuni', morate dodati 2 zareza, kako bi uklonili posljednjih 6 znamenki.

A ako i želite prikazati riječ milijun ili abecedu M iza broja, upotrijebite donji format

0,0 ,, "M"