Formatarea numerelor în Excel VBA
Numerele sunt disponibile în tot felul de formate în foile de lucru Excel. Este posibil să fiți deja familiarizați cu fereastra pop-up din Excel pentru a utiliza diferite formate numerice:
Formatarea numerelor facilitează citirea și înțelegerea numerelor. Valoarea implicită Excel pentru numerele introduse în celule este formatul „General”, ceea ce înseamnă că numărul este afișat exact așa cum l-ați introdus.
De exemplu, dacă introduceți un număr rotund de ex. 4238, va fi afișat ca 4238 fără separatoare zecimale sau mii. Un număr zecimal precum 9325.89 va fi afișat cu punctul zecimal și zecimalele. Aceasta înseamnă că nu se va alinia în coloană cu numerele rotunde și va arăta extrem de dezordonat.
De asemenea, fără a afișa separatoarele de mii, este dificil să se vadă cât de mare este un număr fără a număra cifrele individuale. Este în milioane sau zeci de milioane?
Din punctul de vedere al unui utilizator care caută în jos o coloană de numere, acest lucru face destul de dificilă citirea și compararea.
În VBA aveți acces la exact aceeași gamă de formate pe care le aveți în partea frontală a Excel. Acest lucru se aplică nu numai unei valori introduse într-o celulă dintr-o foaie de lucru, ci și lucrurilor precum casetele de mesaje, comenzile UserForm, diagrame și grafice și bara de stare Excel din colțul din stânga jos al foii de lucru.
Funcția Format este o funcție extrem de utilă în VBA în termeni de prezentare, dar este, de asemenea, foarte complexă în ceea ce privește flexibilitatea oferită în modul de afișare a numerelor.
Cum se folosește funcția Format în VBA
Dacă afișați o casetă de mesaj, funcția Format poate fi utilizată direct:
| 1 | Format MsgBox (1234567.89, "#, ## 0.00") |
Aceasta va afișa un număr mare folosind virgule pentru a separa mii și pentru a afișa 2 zecimale. Rezultatul va fi de 1.234.567,89. Zero-urile în locul hash-ului asigură faptul că zecimalele vor fi afișate ca 00 în numere întregi și că există un zero de conducere pentru un număr mai mic de 1
Simbolul hashtag (#) reprezintă un substituent de cifră care afișează o cifră dacă este disponibil în poziția respectivă sau altceva nimic.
De asemenea, puteți utiliza funcția de formatare pentru a vă adresa unei celule individuale sau o gamă de celule pentru a modifica formatul:
| 1 | Foi ("Sheet1"). Interval ("A1: A10"). NumberFormat = "#, ## 0.00" |
Acest cod va seta intervalul de celule (A1 la A10) într-un format personalizat care separă mii cu virgule și afișează 2 zecimale.
Dacă verificați formatul celulelor de pe front-end-ul Excel, veți descoperi că a fost creat un nou format personalizat.
De asemenea, puteți formata numerele pe bara de stare Excel din colțul din stânga jos al ferestrei Excel:
| 1 | Application.StatusBar = Format (1234567.89, "#, ## 0.00") |
Ștergeți acest lucru din bara de stare folosind:
| 1 | Application.StatusBar = "" |
Crearea unui șir de format
Acest exemplu va adăuga textul „Vânzări totale” după fiecare număr, precum și un separator de mii
| 1 | Foi de calcul („Foaie1”). Interval („A1: A6”). NumberFormat = "#, ## 0.00" "Vânzări totale" "" |
Așa vor arăta numerele tale:
Rețineți că celula A6 are o formulă „SUM”, iar aceasta va include textul „Vânzări totale” fără a necesita formatare. Dacă se aplică formatarea, ca în codul de mai sus, aceasta nu va pune o instanță suplimentară de „Vânzări totale” în celula A6
Deși celulele afișează acum caractere alfanumerice, numerele sunt încă prezente sub formă numerică. Formula „SUM” funcționează în continuare, deoarece folosește valoarea numerică din fundal, nu modul în care este formatat numărul.
Virgula din șirul de format oferă separatorul de mii. Rețineți că trebuie să puneți acest lucru în șir o singură dată. Dacă numărul se ridică la milioane sau miliarde, va separa cifrele în grupuri de 3
Zero-ul din șirul de format (0) este un substituent de cifre. Afișează o cifră dacă este acolo sau un zero. Poziționarea sa este foarte importantă pentru a asigura uniformitatea cu formatarea
În șirul de format, caracterele hash (#) nu vor afișa nimic dacă nu există nicio cifră. Cu toate acestea, dacă există un număr ca .8 (toate zecimale), vrem să se afișeze ca 0.80, astfel încât să se alinieze cu celelalte numere.
Utilizând un singur zero la stânga punctului zecimal și două zerouri la dreapta punctului zecimal în șirul de format, se va obține rezultatul necesar (0,80).
Dacă ar exista doar un zero la dreapta punctului zecimal, atunci rezultatul ar fi „0,8” și totul ar fi afișat la o zecimală.
Utilizarea unui șir de formatare pentru aliniere
S-ar putea să dorim să vedem toate numerele zecimale într-un interval aliniat pe punctele lor zecimale, astfel încât toate punctele zecimale să fie direct unul sub celălalt, oricât de multe zecimale există pe fiecare număr.
Puteți utiliza un semn de întrebare (?) În șirul de format pentru a face acest lucru. „?” Indică faptul că un număr este afișat dacă este disponibil sau un spațiu
| 1 | Foi ("Sheet1"). Interval ("A1: A6"). NumberFormat = "#, ## 0.00 ??" |
Aceasta vă va afișa numerele după cum urmează:
Toate punctele zecimale se aliniază acum una sub cealaltă. Celula A5 are trei zecimale și acest lucru ar arunca alinierea în mod normal, dar folosirea caracterului „?” Aliniază totul perfect.
Utilizarea caracterelor literale în cadrul șirului de formate
Puteți adăuga orice caracter literal în șirul de format precedându-l cu o bară inversă (\).
Să presupunem că doriți să afișați un anumit indicator valutar pentru numerele dvs., care nu se bazează pe locale. Problema este că, dacă utilizați un indicator valutar, Excel se referă automat la local și îl modifică la cel adecvat pentru localizarea setată pe Panoul de control Windows. Acest lucru ar putea avea implicații dacă aplicația dvs. Excel este distribuită în alte țări și doriți să vă asigurați că, indiferent de localizare, indicatorul valutar este întotdeauna același.
De asemenea, vă recomandăm să indicați că numerele sunt în milioane în următorul exemplu:
| 1 | Foi de calcul („Sheet1"). Interval („A1: A6"). NumberFormat = "\ $ #, ## 0.00 \ m" |
Aceasta va produce următoarele rezultate pe foaia dvs. de lucru:
Când utilizați o bară inversă pentru a afișa caractere literale, nu este necesar să utilizați o bară inversă pentru fiecare caracter individual dintr-un șir. Poți să folosești:
| 1 | Foi de calcul („Sheet1"). Interval („A1: A6"). NumberFormat = "\ $ #, ## 0.00 \ mill" |
Aceasta va afișa „moară” după fiecare număr din intervalul formatat.
Puteți utiliza cele mai multe caractere ca litere, dar nu caractere rezervate, cum ar fi 0, # ,?
Utilizarea virgulelor într-un șir de format
Am văzut deja că virgulele pot fi folosite pentru a crea mii de separatoare pentru un număr mare, dar pot fi folosite și în alt mod.
Folosindu-le la sfârșitul părții numerice a șirului de format, ele acționează ca scalatori de mii. Cu alte cuvinte, vor împărți fiecare număr la 1.000 de fiecare dată când există o virgulă.
În datele de exemplu, le arătăm cu un indicator că este în milioane. Prin inserarea unei virgule în șirul de format, putem arăta acele numere împărțite la 1.000.
| 1 | Foi de calcul („Sheet1"). Interval („A1: A6"). NumberFormat = "\ $ #, ## 0.00, \ m" |
Aceasta va arăta numerele împărțite la 1.000, deși numărul original va rămâne în fundal în celulă.
Dacă puneți două virgule în șirul de format, atunci numerele vor fi împărțite la un milion
| 1 | Foi ("Sheet1"). Interval ("A1: A6"). NumberFormat = "\ $ #, ## 0.00 ,, \ m" |
Acesta va fi rezultatul folosind o singură virgulă (împărțiți la 1.000):
Crearea formatării condiționate în șirul de formate
Puteți configura formatarea condiționată pe partea frontală a Excel-ului, dar o puteți face și în codul dvs. VBA, ceea ce înseamnă că puteți manipula șirul de format programatic pentru a face modificări.
Puteți utiliza până la patru secțiuni în cadrul șirului de format. Fiecare secțiune este delimitată de un punct și virgulă (;). Cele patru secțiuni corespund pozitiv, negativ, zero și text
| 1 | Gama ("A1: A7"). NumberFormat = "#, ## 0.00; [Roșu] - #, ## 0.00; [Verde] #, ## 0.00; [Albastru]” |
În acest exemplu, folosim aceleași caractere hash, virgulă și zero pentru a furniza mii de separatoare și două puncte zecimale, dar acum avem secțiuni diferite pentru fiecare tip de valoare.
Prima secțiune este pentru numere pozitive și nu diferă de ceea ce am văzut deja anterior în ceea ce privește formatul.
A doua secțiune pentru numerele negative introduce o culoare (roșu) care este ținută într-o pereche de paranteze drepte. Formatul este același ca și pentru numerele pozitive, cu excepția faptului că un semn minus (-) a fost adăugat în față.
A treia secțiune pentru numerele zero utilizează o culoare (verde) între paranteze pătrate cu șirul numeric la fel ca pentru numerele pozitive.
Secțiunea finală este pentru valorile textului și tot ceea ce are nevoie este o culoare (albastru) din nou între paranteze drepte
Acesta este rezultatul aplicării acestui șir de format:
Puteți merge mai departe cu condițiile din șirul de format. Să presupunem că ați vrut să arătați fiecare număr pozitiv de peste 10.000 ca verde și orice alt număr ca roșu ați putea folosi acest șir de format:
| 1 | Interval ("A1: A7"). NumberFormat = "[> = 10000] [Verde] #, ## 0.00; [<10000] [Roșu] #, ## 0.00" |
Acest șir de format include condiții pentru> = 10000 setate între paranteze pătrate, astfel încât culoarea verde să fie utilizată numai în cazul în care numărul este mai mare sau egal cu 10000
Acesta este rezultatul:
Utilizarea fracțiilor în formatarea șirurilor
Fracțiile nu sunt adesea folosite în foile de calcul, deoarece echivalează în mod normal cu zecimale cu care toată lumea este familiarizată.
Cu toate acestea, uneori ele servesc unui scop. Acest exemplu va afișa dolari și cenți:
| 1 | Gama ("A1: A7"). NumberFormat = "#, ## 0" "dolari și" "00/100" "cenți" "" |
Acesta este rezultatul care va fi produs:
Amintiți-vă că, în ciuda faptului că numerele sunt afișate ca text, acestea sunt încă acolo în fundal ca numere și toate formulele Excel pot fi folosite în continuare pe ele.
Formate de dată și oră
Datele sunt de fapt numere și puteți utiliza formate pe ele în același mod ca și pentru numere. Dacă formatați o dată ca număr numeric, veți vedea un număr mare la stânga punctului zecimal și un număr de zecimale. Numărul din stânga punctului zecimal arată numărul de zile începând cu 01-ianuarie-1900, iar zecimalele arată ora bazată pe 24 de ore
| 1 | Format MsgBox (Now (), "dd-mmm-aaaa") |
Aceasta va forma data curentă pentru a afișa „08-Iulie-2020”. Utilizarea „mmm” pentru lună afișează primele trei caractere ale numelui lunii. Dacă doriți numele întregii luni, utilizați „mmmm”
Puteți include orele în șirul de format:
| 1 | Format MsgBox (Acum (), "zz-ll mm-aaaa hh: mm AM / PM") |
Se va afișa „08-Iul-2020 13:25 PM”
„Hh: mm” reprezintă ore și minute, iar AM / PM folosește un ceas de 12 ore spre deosebire de un ceas de 24 de ore.
Puteți încorpora caractere text în șirul de format:
| 1 | Format MsgBox (Now (), "dd-mmm-aaaa hh: mm AM / PM" "azi" "") |
Aceasta va afișa „08-Iul-2020 13:25 azi”
De asemenea, puteți utiliza caractere literale folosind o bară inversă în față, în același mod ca și pentru șirurile de format numeric.
Formate predefinite
Excel are un număr de formate încorporate atât pentru numere, cât și pentru date, pe care le puteți utiliza în cod. Acestea reflectă în principal ceea ce este disponibil pe partea frontală de formatare a numărului, deși unele dintre ele depășesc ceea ce este disponibil în mod normal în fereastra pop-up. De asemenea, nu aveți flexibilitatea în ceea ce privește numărul de zecimale sau dacă se utilizează separatoare de mii.
Număr general
Acest format va afișa numărul exact așa cum este
| 1 | Format MsgBox (1234567.89, „Număr general”) |
Rezultatul va fi 1234567.89
Valută
| 1 | Format MsgBox (1234567.894, „Monedă”) |
Acest format va adăuga un simbol valutar în fața numărului, de ex. $, £ în funcție de locația dvs., dar va format și numărul la 2 zecimale și va separa mii cu virgule.
Rezultatul va fi de 1.234.567,89 USD
Fix
| 1 | Format MsgBox (1234567.894, „Fix”) |
Acest format afișează cel puțin o cifră la stânga, dar doar două cifre la dreapta punctului zecimal.
Rezultatul va fi 1234567.89
Standard
| 1 | Format MsgBox (1234567.894, „Standard”) |
Aceasta afișează numărul cu mii de separatoare, dar numai cu două zecimale.
Rezultatul va fi de 1.234.567,89
La sută
| 1 | Format MsgBox (1234567.894, „Procent”) |
Numărul este înmulțit cu 100 și se adaugă un simbol procentual (%) la sfârșitul numărului. Formatul se afișează cu 2 zecimale
Rezultatul va fi 123456789.40%
Științific
| 1 | Format MsgBox (1234567.894, „Științific”) |
Aceasta convertește numărul în format exponențial
Rezultatul va fi 1.23E + 06
Da nu
| 1 | Format MsgBox (1234567.894, „Da / Nu”) |
Se afișează „Nu” dacă numărul este zero, altfel se afișează „Da”
Rezultatul va fi „Da”
Adevarat fals
| 1 | Format MsgBox (1234567.894, „Adevărat / Fals”) |
Aceasta afișează „False” dacă numărul este zero, altfel afișează „True”
Rezultatul va fi „Adevărat”
Pornit / Oprit
| 1 | Format MsgBox (1234567.894, „On / Off”) |
Aceasta afișează „Dezactivat” dacă numărul este zero, altfel afișează „Activat”
Rezultatul va fi „Activat”
Data generală
| 1 | Format MsgBox (Acum (), „Data generală”) |
Aceasta va afișa data ca dată și oră folosind notația AM / PM. Modul în care este afișată data depinde de setările dvs. din Panoul de control Windows (Ceas și Regiune | Regiune). Poate fi afișat ca „zz / ll / aaaa” sau „zz / ll / aaaa”
Rezultatul va fi „07.07.2020 15:48:25”
Data lungă
| 1 | Format MsgBox (Acum (), „Data lungă”) |
Aceasta va afișa o dată lungă, așa cum este definită în Panoul de control Windows (Ceas și Regiune | Regiune). Rețineți că nu include ora.
Rezultatul va fi „marți, 7 iulie 2022”
Data medie
| 1 | Format MsgBox (Acum (), „Data medie”) |
Aceasta afișează o dată așa cum este definită în setările de dată scurtă, definite de locale în panoul de control Windows.
Rezultatul va fi '07-Iul-20 '
Data scurtă
| 1 | Format MsgBox (Acum (), „Data scurtă”) |
Afișează o dată scurtă, așa cum este definită în Panoul de control Windows (Ceas și Regiune | Regiune). Modul în care este afișată data depinde de setările locale. Poate fi afișat ca „zz / ll / aaaa” sau „zz / ll / aaaa”
Rezultatul va fi „7/7/2020”
Perioadă lungă de timp
| 1 | Format MsgBox (Acum (), „Mult timp”) |
Afișează o perioadă lungă de timp, așa cum este definit în Panoul de control Windows (Ceas și Regiune | Regiune).
Rezultatul va fi „16:11:39 PM”
Timp mediu
| 1 | Format MsgBox (Acum (), „Timp mediu”) |
Afișează o perioadă medie de timp, așa cum este definită de setările locale din panoul de control Windows. Acesta este de obicei setat ca format de 12 ore folosind ore, minute și secunde și formatul AM / PM.
Rezultatul va fi '04: 15 PM '
Timp scurt
| 1 | Format MsgBox (Acum (), „Timp scurt”) |
Afișează o perioadă medie de timp, așa cum este definită în Panoul de control Windows (Ceas și regiune | Regiune). Acesta este de obicei setat ca format de 24 de ore cu ore și minute
Rezultatul va fi '16: 18 '
Pericolele utilizării formatelor predefinite ale Excel în date și ore
Utilizarea formatelor predefinite pentru date și ore în Excel VBA depinde foarte mult de setările din Panoul de control Windows și, de asemenea, de setările locale
Utilizatorii pot modifica cu ușurință aceste setări și acest lucru va avea un efect asupra modului în care datele și orele dvs. sunt afișate în Excel
De exemplu, dacă dezvoltați o aplicație Excel care utilizează formate predefinite în codul dvs. VBA, acestea se pot schimba complet dacă un utilizator se află într-o altă țară sau utilizează o altă locație locală. Este posibil să constatați că lățimile coloanelor nu se potrivesc cu definiția datei sau, pe un formular de utilizator, controlul Active X, cum ar fi o casetă combinată (drop-down), controlul este prea îngust pentru ca datele și orele să fie afișate corect.
Trebuie să luați în considerare unde se află audiența geografic atunci când vă dezvoltați aplicația Excel
Formate definite de utilizator pentru numere
Există o serie de parametri diferiți pe care îi puteți utiliza atunci când vă definiți șirul de format:
| Caracter | Descriere |
| Null String | Fără formatare |
| 0 | Substituent de cifră. Afișează o cifră sau un zero. Dacă există o cifră pentru poziția respectivă, atunci afișează cifra în caz contrar, afișează 0. Dacă există mai puține cifre decât zerouri, atunci veți obține zerouri inițiale sau finale. Dacă există mai multe cifre după punctul zecimal decât sunt zerouri, atunci numărul este rotunjit la numărul de zecimale afișat de zerouri. Dacă există mai multe cifre înainte de punctul zecimal decât zerouri, acestea vor fi afișate în mod normal. |
| # | Substituent de cifră. Aceasta afișează o cifră sau nimic. Funcționează la fel ca substituentul zero de mai sus, cu excepția faptului că zero-urile inițiale și finale nu sunt afișate. De exemplu, 0,75 ar fi afișat folosind zero substituenți, dar acesta ar fi 0,75 folosind # substituenți. |
| . Punct zecimal. | Doar unul permis pentru fiecare șir de format. Acest caracter depinde de setările din Panoul de control Windows. |
| % | Procent de substituent. Multiplicările numerotează cu 100 și plasează% caracter în locul în care apare în șirul de format |
| , (virgulă) | Separator de mii. Aceasta este utilizată dacă se utilizează 0 sau # substituenți și șirul de format conține o virgulă. O virgulă la stânga punctului zecimal indică rotunjirea la cea mai apropiată mie. De exemplu. ## 0, Două virgule adiacente la stânga separatorului de mii indică rotunjirea la cel mai apropiat milion. De exemplu. ## 0 ,, |
| E- E + | Format științific. Aceasta afișează numărul exponențial. |
| : (colon) | Separator de timp - utilizat la formatarea unui timp pentru a împărți ore, minute și secunde. |
| / | Separator de date - este utilizat atunci când se specifică un format pentru o dată |
| - + £ $ ( ) | Afișează un caracter literal.Pentru a afișa un alt caracter decât cel enumerat aici, precedați-l cu o bară inversă (\) |
Formate definite de utilizator pentru date și ore
Aceste caractere pot fi folosite în formatul șirului dvs. atunci când formatați datele și orele:
| Caracter | Sens |
| c | Afișează data ca ddddd și ora ca ttttt |
| d | Afișați ziua ca un număr fără zero în față |
| dd | Afișați ziua ca un număr cu zero din partea de sus |
| ddd | Afișează ziua ca o prescurtare (Duminică - Sâmbătă) |
| dddd | Afișați numele complet al zilei (duminică - sâmbătă) |
| ddddd | Afișați un număr de serie de dată ca o dată completă în conformitate cu Scurtă dată în setările internaționale ale panoului de control al ferestrelor |
| dddddd | Afișează un număr de serie de dată ca o dată completă în conformitate cu Data lungă în setările internaționale ale panoului de control Windows. |
| w | Afișează ziua săptămânii ca număr (1 = duminică) |
| ww | Afișează săptămâna anului ca număr (1-53) |
| m | Afișează luna sub forma unui număr fără zero |
| mm | Afișează luna ca număr cu zerouri din partea de sus |
| mmm | Afișează luna ca prescurtare (ianuarie-decembrie) |
| mmmm | Afișează numele complet al lunii (ianuarie - decembrie) |
| q | Afișează trimestrul anului ca număr (1-4) |
| y | Afișează ziua anului ca număr (1-366) |
| yy | Afișează anul ca număr din două cifre |
| yyyy | Afișează anul ca număr din patru cifre |
| h | Afișează ora ca număr fără zero înaintea |
| hh | Afișează ora ca număr cu zero din partea de sus |
| n | Afișează minutul ca un număr fără zero |
| nn | Afișează minutul ca un număr cu zero în față |
| s | Afișează al doilea sub formă de număr fără zero |
| ss | Afișează al doilea ca număr cu zero înaintea |
| ttttt | Afișați un număr de serie de timp ca oră completă. |
| AM PM | Utilizați un ceas de 12 ore și afișați AM sau PM pentru a indica înainte sau după prânz. |
| am pm | Utilizați un ceas de 12 ore și utilizați am sau pm pentru a indica înainte sau după prânz |
| A / P | Utilizați un ceas de 12 ore și utilizați A sau P pentru a indica înainte sau după prânz |
| a / p | Utilizați un ceas de 12 ore și utilizați a sau p pentru a indica înainte sau după prânz |
