7 trucuri uimitoare pentru formatul numărului personalizat Excel (trebuie să știți)

Formatarea numărului personalizat Excel este îmbrăcămintea pentru datele din celulele Excel. Poți să le îmbraci așa cum vrei. Tot ce aveți nevoie este un pic de cunoștințe despre modul în care funcționează formatul de număr personalizat Excel.

Cu formatarea personalizată a numărului în Excel, puteți schimba modul în care valorile din celule apar, dar păstrând în același timp valoarea originală intactă.

De exemplu, pot face ca o dată să apară în diferite formate de dată, în timp ce valoarea subiacentă (numărul care reprezintă data) ar rămâne aceeași.

În acest tutorial, voi acoperi totul despre formatul de număr personalizat în Excel și vă voi arăta, de asemenea, câteva exemple cu adevărat utile pe care le puteți folosi în activitatea dvs. de zi cu zi.

Asadar, haideti sa începem!

Format de număr personalizat Excel

Înainte de a vă arăta grozavul, permiteți-mi să explic pe scurt cum funcționează formatarea numerelor personalizate în Excel.

Prin proiectare, Excel poate accepta următoarele patru tipuri de date într-o celulă:

• Un număr pozitiv
• un număr negativ
• 0
• Șiruri de text (aceasta poate include șiruri de text pur, precum și șiruri alfanumerice)

Un alt tip de date pe care Excel îl poate accepta sunt datele, dar întrucât toate valorile de dată și oră sunt stocate ca numere în Excel, acestea ar fi acoperite ca parte a numărului pozitiv.

Pentru orice celulă dintr-o foaie de lucru în Excel, puteți specifica modul în care fiecare dintre aceste tipuri de date trebuie afișate în celulă.

Și mai jos este sintaxa în care puteți specifica acest format.

;;;

Rețineți că toate acestea sunt separate prin punct și virgulă (;).

Orice lucru pe care îl introduceți într-o celulă din Excel ar intra în oricare dintre aceste patru categorii și, prin urmare, poate fi specificat un format personalizat pentru aceasta.

Dacă menționați numai:

• Un format: Se aplică tuturor celor patru secțiuni. De exemplu, dacă scrieți doar General, acesta va fi aplicat pentru toate cele patru secțiuni.
• Două formate: Primul se aplică numerelor pozitive și zerourilor, iar al doilea se aplică numerelor negative. Formatul textului implicit devine General.
• Trei formate: Primul se aplică numerelor pozitive, al doilea se aplică numerelor negative, al treilea se aplică la zero și textul dispare deoarece nu este specificat nimic pentru text.

Dacă doriți să aflați mai multe despre formatarea numerelor personalizate, aș recomanda cu tărie secțiunea Ajutor Office.

Cum funcționează acest lucru și unde să introduceți aceste formate sunt prezentate mai târziu în acest tutorial. Deocamdată, rețineți că aceasta este sintaxa pentru formatul pentru orice celulă

Cum funcționează formatul de număr personalizat în Excel

Apoi deschideți un nou registru de lucru Excel, în mod implicit, toate celulele din toate foile de lucru din acel registru de lucru au formatul general unde:

• Numere pozitive sunt afișate așa cum este (aliniat la stânga)
• Numere negative sunt afișate cu un semn minus (și aliniate la stânga)
• Zero este afișat ca 0 (și aliniat la stânga)
• Text este afișat așa cum este (aliniat la dreapta)

Și în cazul în care introduceți o dată, aceasta se afișează pe baza setărilor dvs. regionale (în format ZZ-LL-AAAA sau MM-ZZ-AAAA).

Deci, în mod implicit, formatarea numărului celulei este:

General; General; General; General

Unde fiecare tip de date este formatat ca General (și urmați regulile menționate mai sus, în funcție de numărul pozitiv, numărul negativ, 0 sau text)

Acum setarea implicită este în regulă, dar cum schimbați formatul. De exemplu, dacă vreau ca numerele mele negative să apară în culoarea roșie sau doresc ca datele mele să apară într-un format specific, atunci cum o fac.

Folosind meniul drop-down format din panglică

Puteți găsi unele dintre formatele numerice utilizate în mod obișnuit în fila Acasă din grupul Număr.

Este o listă derulantă care arată multe dintre formatele utilizate în mod obișnuit pe care le puteți aplica unei celule prin simpla selectare a celulei și apoi selectarea formatului din această listă.

De exemplu, dacă doriți să convertiți numerele în procente sau să convertiți datele în format de dată scurt sau format de dată lung, atunci puteți face acest lucru cu ușurință utilizând opțiunile din meniul derulant.

Utilizarea casetei de dialog Formatare celule

Formatați casetele de dialog ale celulelor, unde obțineți flexibilitatea maximă atunci când vine vorba de aplicarea formatelor unei celule în Excel.

Are deja o mulțime de formate premade pe care le puteți utiliza sau, dacă doriți să creați propriul format de număr personalizat, atunci puteți face și asta.

Iată cum se deschide caseta de dialog Formatare celule;

1. Faceți clic pe fila Acasă
2. În grupul Număr, faceți clic pe pictograma de lansare a casetei de dialog (săgeata mică înclinată din partea dreaptă jos a grupului).

Aceasta va deschide caseta de dialog Formatare celule. În cadrul acestuia, veți găsi toate formatele în fila Număr (împreună cu opțiunea de a crea formate de celule personalizate)

De asemenea, puteți utiliza următoarea comandă rapidă de la tastatură pentru a deschide caseta de dialog formatarea celulelor:

CONTROL + 1 (toată tasta Control și apoi apăsați tasta 1).

În cazul în care utilizați Mac, puteți utiliza CMD + 1

Acum să avem câteva exemple practice în care vom crea propriile noastre formate de numere personalizate.

Exemple de utilizare a formatării numerelor personalizate în Excel

Acum să avem câteva exemple practice în care vom crea propriile noastre formate de numere personalizate.

Ascundeți o valoare a celulei (sau Ascundeți toate valorile într-un interval de celule)

Uneori, este posibil să aveți nevoie să ascundeți conținutul celulei (fără a ascunde rândul sau coloana care are celula).

Un caz practic este atunci când creați un tablou de bord Excel și există câteva valori pe care le utilizați în tabloul de bord, dar nu doriți ca utilizatorii să îl vadă. astfel încât să puteți ascunde pur și simplu aceste valori

Să presupunem că aveți un set de date așa cum se arată mai jos și doriți să ascundeți toate valorile numerice:

Iată cum puteți face acest lucru folosind formatarea personalizată în Excel:

1. Selectați celula sau gama de celule pentru care doriți să ascundeți conținutul celulei
2. Deschideți caseta de dialog Formatare celule (comandă rapidă de la tastatură Control + 1)
3. În fila Număr, faceți clic pe Personalizat
4. Introduceți următorul format în câmpul Tip:

   ;;;

Cum funcționează asta?

Formatul pentru patru tipuri diferite de tipuri de date este împărțit la punct și virgulă în următorul format:

;;;

În această tehnică, am eliminat formatul și am specificat doar punctul și virgula, ceea ce indică faptul că nu dorim nicio formatare pentru celule. Aceasta este o indicație pentru Excel pentru a ascunde orice conținut care este acolo în celulă.

Rețineți că, deși acest lucru ascunde conținutul celulei, acesta este încă acolo. deci, dacă aveți numere pe care le-ați ascuns, le puteți utiliza în continuare în calcule (ceea ce face ca acesta să fie un truc excelent pentru tablourile de bord și rapoarte Excel).

Afișați numerele negative în culoare roșie

În mod implicit, numerele negative apar cu un semn minus (sau în paranteză în unele setări regionale).

Puteți utiliza formatarea numerelor personalizate pentru a schimba culoarea numerelor negative și a le face să apară în culoare roșie.

Să presupunem că aveți un set de date așa cum se arată mai jos și doriți să evidențiați toate valorile negative în culoare roșie.

Mai jos sunt pașii pentru a face acest lucru:

1. Selectați gama de celule în care doriți să afișați numere negative cu roșu
2. Deschideți caseta de dialog Formatare celule (comandă rapidă de la tastatură Control + 1)
3. În fila Număr, faceți clic pe Personalizat
4. Introduceți următorul format în câmpul Tip:

General; [Roșu] -General

Formatul de mai sus va face ca celulele dvs. cu valori negative să fie afișate în culoare roșie.

Cum funcționează asta?

Când specificați doar două formate ca format de număr personalizat, primul este considerat pentru numerele pozitive și 0, iar al doilea este considerat pentru numerele negative (următoarea valoare implicită devine formatul general).

În formatul de mai sus pe care l-am folosit, am specificat culoarea pe care dorim să o ia formatul respectiv între paranteze.

Deci, în timp ce numerele negative iau formatul general, acestea sunt afișate în culoare roșie cu un semn minus.

În cazul în care doriți să faceți ca numerele negative să se oprească în culoare roșie în paranteză (în loc de semnul minus), puteți utiliza formatul de mai jos:

General; [Roșu] (General)

Adăugați text la numere (cum ar fi milioane / miliarde)

Un lucru uimitor despre formatarea personalizată a numărului este că puteți adăuga un prefix sau un text de sufix la un număr, păstrând în același timp numărul original în celulă.

De exemplu, pot să apar „100” ca „100 milioane USD”, având în același timp valoarea inițială în celulă, astfel încât să o pot folosi în calcule.

Să presupunem că aveți un set de date așa cum se arată mai jos și doriți să arătați aceste numere cu un semn de dolar și cu cuvântul milion în față.

Mai jos sunt pașii pentru a face acest lucru:

1. Selectați setul de date în care doriți să adăugați textul la numere
2. Deschideți caseta de dialog Formatare celule (comandă rapidă de la tastatură Control + 1)
3. În fila Număr, faceți clic pe Personalizat
4. Introduceți următorul format în câmpul Tip:

$ General „Milioane”

Rețineți că acest format de număr personalizat va afecta numai numerele. Dacă introduceți text în aceeași celulă, acesta va apărea așa cum este.

Cum funcționează asta?

În acest exemplu, la formatul general, am adăugat cuvântul milion între ghilimele duble. tot ceea ce adaug în ghilimele duble la format este afișat așa cum este în celulă.

Am adăugat, de asemenea, semnul dolarului înainte de formatul general, astfel încât toate numerele mele să arate și semnul dolarului înaintea numărului.

Notă: dacă vă întrebați de ce semnul dolarului nu este în ghilimele duble, în timp ce cuvântul Milion este în ghilimele duble, se datorează faptului că Excel recunoaște dolarul ca un simbol care este adesea utilizat în formatarea numerelor personalizate și nu vă solicită pentru a adăuga ghilimele. Puteți continua și adăuga ghilimele duble la semnul de dolar, dar când închideți caseta de dialog formatarea celulelor și o deschideți din nou, veți observa că Excel a eliminat-o

Numere de deghizare și text

Cu formatarea numerelor personalizate, puteți evalua maximum două condiții, iar pe baza acestor condiții puteți aplica un format celulei.

De exemplu, să presupunem că aveți o listă de scoruri pentru unii studenți, doriți să afișați textul Treceți sau Nu treceți pe baza scorului (în cazul în care orice scor mai mic de 35 ar trebui să afișeze „Nu reușește”, altfel ar trebui să afișeze „Treceți”.

Mai jos sunt pașii pentru a afișa scorurile ca Pass sau Fail în loc de valoare utilizând formatul de număr personalizat:

1. Selectați setul de date în care aveți scorurile
2. Deschideți caseta de dialog Formatare celule (comandă rapidă de la tastatură Control + 1)
3. În fila Număr, faceți clic pe Personalizat
4. Introduceți următorul format în câmpul Tip:

   [<35] „Fail”; „Pass”

Mai jos este modul în care ar arăta datele dvs. după ce ați aplicat formatul de mai sus.

Rețineți că nu modifică valoarea din celule. Celula continuă să aibă scorurile în valori numerice în timp ce afișează „Trece” sau „Eșuează” în funcție de valoarea celulei.

Cum funcționează asta?

În acest exemplu, condiția trebuie specificată între paranteze. formatarea numărului personalizat evaluează apoi valoarea din celulă și aplică formatul în consecință.

Deci, dacă valoarea din celulă este mai mică de 35, atunci numărul este ascuns și, în schimb, este afișat textul „Fail”, adăugând toate celelalte cazuri, este afișat „Pass”.

În mod similar, dacă doriți să convertiți o gamă de celule care au 0 și 1 în TRUE și FALSE, puteți utiliza formatul de mai jos:

[= 0] „FALS”; [= 1] „ADEVĂRAT”

Amintiți-vă că puteți utiliza doar două condiții în formatarea numerelor personalizate. Dacă încercați și utilizați mai mult de două condiții, se va întâmpla astfel încât promptul dvs. să vă anunțe că nu poate accepta acel format.

Ascundeți textul, dar afișați numerele

Uneori, când vă descărcați datele din baze de date, deoarece acestea sunt goale, sunt umplute cu valori precum Nedisponibil sau NA sau -

Cu formatarea numerelor personalizate, puteți ascunde toate valorile textului, păstrând în același timp numerele. acest lucru asigură, de asemenea, că datele dvs. originale sunt intacte (deoarece nu ștergeți valoarea textului, pur și simplu o ascundeți)

Să presupunem că aveți un set de date așa cum se arată mai jos, unde doriți să ascundeți toate valorile textului:

Mai jos sunt pașii pentru a face acest lucru:

1. Selectați setul de date în care aveți datele
2. Deschideți caseta de dialog Formatare celule (comandă rapidă de la tastatură Control + 1)
3. În fila Număr, faceți clic pe Personalizat
4. Introduceți următorul format în câmpul Tip:

General; -General; General;

Rețineți că am folosit formatul general pentru numere. Puteți oricare format doriți (cum ar fi 0, 0. #, # 0.0%). De asemenea, rețineți că există un semn negativ (-) înainte de al doilea format general, deoarece reprezintă formatul pentru numerele negative.

Cum funcționează asta?

În acest exemplu, deoarece am vrut să ascundem toate valorile textului, pur și simplu nu am specificat formatul pentru acesta.

Și din moment ce am specificat formatul pentru celelalte trei tipuri de date (număr pozitiv, număr negativ și zero), Excel a înțeles că am lăsat în mod intenționat formatul pentru valoarea textului gol și nu ar trebui să arate nimic dacă o celulă are un text şir.

Afișați numerele ca procente (%)

Cu formatarea numerelor personalizate, puteți schimba numerele și le puteți face să apară în procente.

Să presupunem că aveți un set de date așa cum se arată mai jos și doriți să convertiți aceste numere pentru a afișa valoarea procentuală echivalentă:

Mai jos sunt pașii pentru a face acest lucru:

1. Selectați celulele în care aveți numerele (pe care doriți să le convertiți în procente)
2. Deschideți caseta de dialog Formatare celule (comandă rapidă de la tastatură Control + 1)
3. În fila Număr, faceți clic pe Personalizat
4. Introduceți următorul format în câmpul Tip:

0%

Acest lucru va schimba numerele în procente. De exemplu, se schimbă de la 0,11 la 11%.

Dacă tot ce doriți să faceți este să convertiți un număr în valoarea procentuală echivalentă, o modalitate mai bună de a face acest lucru ar fi să utilizați opțiunea procentuală din caseta de dialog formatarea celulelor.

Unde poate fi utilă formatarea personalizată este atunci când doriți mai mult control asupra modului în care doriți să afișați valoarea procentuală.

De exemplu, dacă doriți să afișați un text suplimentar cu procentajul, tot ce doriți să afișați procentaje negative în culoare roșie, atunci este mai bine să utilizați formatarea personalizată

Afișați numerele într-o unitate diferită (milioane ca miliarde sau grame ca kilograme)

Dacă lucrați cu numere mari, vă recomandăm să le arătați într-o unitate diferită. de exemplu, dacă aveți datele despre vânzări ale unor companii, vă recomandăm să afișați vânzările în milioane sau miliarde (păstrând în același timp valoarea de vânzare inițială în celule)

Din fericire, puteți face acest lucru cu ușurință cu formatarea numerelor personalizate.

Să presupunem că aveți un set de date așa cum se arată mai jos și doriți să afișați aceste valori ale vânzărilor în milioane.

Mai jos sunt pașii pentru a face acest lucru:

1. Selectați celulele în care aveți numerele
2. Deschideți caseta de dialog Formatare celule (comandă rapidă de la tastatură Control + 1)
3. În fila Number, faceți clic pe Custom
4. Introduceți următorul format în câmpul Tip:

0.0,,

Cele de mai sus vă vor oferi rezultatul așa cum se arată mai jos (valori în milioane într-o singură cifră după zecimală):

Cum funcționează asta?

În formatul de mai sus, un zero înainte de zecimală indică faptul că Excel ar trebui să afișeze întregul număr (exact așa cum este în celulă), iar un 0 după zecimală indică faptul că ar trebui să afișeze doar o cifră semnificativă după punctul zecimal.

Deci 0,0 ar fi întotdeauna un număr cu o cifră după punctul zecimal.

Când adăugați o virgulă după 0,0, aceasta va rade ultimele trei cifre ale numărului. De exemplu, 123456 ar deveni 123,4 (ultimele trei cifre sunt eliminate și, din moment ce trebuie să afișeze 1 cifră după punctul zecimal, arată 4)

Dacă vă gândiți la asta, este similar cu împărțirea numărului la 1000. Deci, dacă doriți să afișați un număr în unitatea „milioane”, trebuie să adăugați 2 virgule, astfel încât să se radă ultimele 6 cifre.

Și dacă doriți, de asemenea, să afișați cuvântul milion sau alfabetul M după număr, utilizați formatul de mai jos

0,0 ,, „M”