V tem članku se bomo naučili različne načine dodajanja ničl (0) spredaj v Excelu.
Scenarij:
Dodajanje nič pred številko v Excelu. Privzeti Excel ne vnese ničle pred številko v celici. Ste že kdaj poskusili v Excel vnesti nekatere podatke, kot je 000123? Verjetno boste hitro opazili, da bo Excel samodejno odstranil začetne ničle iz vnosne številke. To je lahko zelo nadležno, če želite tiste vodilne ničle v podatkih in ne veste, kako jih Excel obdržati. Na srečo obstaja kar nekaj načinov, kako na začetku zabeležiti svoje številke z ničlami. V tem članku bom razložil različne načine, kako tem številkam dodati ali ohraniti te vodilne ničle.
Različne funkcije za dodajanje nič (0) spredaj
Kot vemo, je funkcija TEXT najbolj uporabljena funkcija za dodajanje 0 spredaj, tukaj pa bom razložil več načinov za dodajanje nič (0) številkam.
- TEXT funkcija
- Funkcija DESNO
- BASE funkcija
- V vrednosti vrtilne tabele dodajte 0
Oblikuj kot besedilo
Ena od rešitev je pretvorba oblike številke v besedilo, ker če vnesemo 00xyz, Excel to vrednost upošteva kot besedilo in vodi 0 pred. Številčno celico lahko pretvorite v besedilno obliko na različne načine.
- Uporaba oblike po meri
- Uporaba funkcije TEXT
- Uporaba Apostrophe (') na začetku vrednosti med vnosom
Oblika po meri
Razumejmo te metode eno za drugo. Če želite spremeniti obliko po meri, je najlažji način, da spremenite že obstoječe številke v razponu. Samo izberite obseg in uporabite bližnjico Ctrl + 1 ali izberite Več oblika zapisa števil s spustnega seznama na zavihku Domov, ki prikazuje Splošno.
Spremenite obliko iz Splošno v Besedilno.
- Izberite obseg celic, v katerega želite vnesti začetne ničle.
- Pojdite na zavihek Domov.
- V razdelku Številke kliknite spustni meni Oblika.
- Med možnostmi oblike izberite Besedilo.
Če poskusite vnesti številke z začetnimi ničlami, ne bodo izginile, ker so vnesene kot besedilne vrednosti namesto številk. Uporabniki Excela lahko dodajo oblikovanje po meri za oblikovanje številk z vodilnimi ničlami.
Zdi se, da imajo le vodilne ničle. Temeljni podatki se ne bodo spremenili v besedilo z dodanimi ničlami.
Dodajte obliko po meri, da prikažete začetne ničle.
- Izberite obseg celic, v katere želite dodati začetne ničle, in odprite pogovorno okno Oblikuj celice.
- Z desno miškino tipko kliknite in izberite Oblikuj celice.
- Uporabite bližnjico na tipkovnici Ctrl + 1.
- Pojdite na zavihek Številka.
- V možnostih kategorije izberite Po meri.
- V vnos Vrsta dodajte novo obliko po meri. Če želite, da je skupno število števk, vključno z vodilnimi ničlami, 6, dodajte 000000 kot obliko po meri.
- Pritisnite gumb OK.
Funkcija BESEDILO
Funkcija BESEDILO vam omogoča uporabo oblikovanja po meri za vse podatke o številkah, ki so že v vaši preglednici.
= TEXT (vrednost, oblika)
- Vrednost - To je vrednost, ki jo želite pretvoriti v besedilo in uporabiti oblikovanje.
- Oblika - To je oblikovanje, ki ga je treba uporabiti.
= BESEDILO (B3, "000000")
Če želite številki v celici B3 dodati ničle, tako da je skupno število mest 6, lahko uporabite zgornjo formulo.
Uporaba Apostrophe (') na začetku
Excel lahko prisilite, da vnese številko kot besedilo z začetnim apostrofom.
To pomeni, da boste lahko ob vnosu podatkov obdržali te ničle.
Ta metoda je hitra in enostavna pri vnosu podatkov. Pred številkami vnesite znak '. To bo Excelu povedalo, da so podatki besedilo in ne številka.
Ko pritisnete Enter, bodo vodilne ničle ostale vidne na delovnem listu. Znak 'ne bo viden na delovnem listu, vendar je še vedno tam in ga je mogoče videti v vrstici s formulami, ko je kazalec aktivne celice na celici.
DESNA funkcija
Drug način, da svoje formule postavite na prvo mesto, je uporaba funkcije DESNO. Niz nič lahko povežete s številko in nato z funkcijo DESNO odrežete dodatke.
Funkcija RIGHT bo iz besedilne vrednosti izvlekla skrajno desnih N znakov.
= DESNO (Besedilo, [Število])
- Besedilo - To je besedilo, iz katerega želite izvleči znake.
- Številka (Neobvezno)- To je število znakov, ki jih je treba izvleči iz besedila. Če tega argumenta ne vnesete, bo izvlečen le prvi znak.
= DESNO ("000000" & B3, 6)
Zgornja formula bo združila več ničel na začetek števila v celici B3, nato pa bo vrnila najbolj desnih 6 znakov, kar bo povzročilo nekaj vodilnih ničel.
OSNOVNA funkcija
Opazili boste, da ta članek opisuje 9 načinov za dodajanje vodilnih nič, vendar moj videoposnetek v YouTubu prikazuje le 8 načinov.
To je zato, ker nisem vedel, da lahko z uporabo funkcije BASE dodate vodilne ničle, dokler tega kdo ne omenja v komentarjih videoposnetka.
Funkcija BASE vam omogoča pretvorbo števila v besedilno predstavo z dano osnovo.
Številke, ki jih običajno uporabljate, so osnova 10, vendar lahko s to funkcijo pretvorite številko baze 10 v bazo 2 (binarno) predstavitev.
= BASE (Število, osnova, [MinLength])
- Številka - To je številka, ki jo želite pretvoriti v besedilno vrednost v drugi bazi.
- Osnova - to je osnova, v katero želite pretvoriti vrednost.
- Najdaljša dolžina (Neobvezno) - To je najmanjša dolžina znakov pretvorjene besedilne vrednosti.
Uporabite formulo
| = BAZA (B3,10,6) |
Zgornja formula pretvori številko v celici B3 v osnovo 10 (to je že osnovna številka 10, vendar bo tako ostala kot osnova 10) in jo pretvori v besedilno vrednost z najmanj 6 znaki. Če je pretvorjena vrednost manjša od 6 znakov, bo vrednost označila z ničlami, da dobite najmanj 6 znakov.
Vsekakor kul nova uporaba funkcije, ki je sicer nikoli ne uporabljam.
Dodajte ničlo (0) samo pred številkami, ne pri besedilnih vrednostih
Tu bomo obravnavali scenarij, ki zajema tako besedilo kot številke na istem seznamu. Zdaj Excel meni, da je vsaka celica drugačna glede na obliko. Zdaj, kako Excelu sporočiti, da če ima celica številsko vrednost, dodaj 0 spredaj ali pa če ima celica besedilo ali katero koli drugo vrednost, pusti tako, kot je.
Za to bomo uporabili kombinacijo dveh funkcij IF in ISNUMBER. Tukaj imamo nekaj vrednot, ki jih lahko preizkusimo.
Splošna formula gre takole
= IF (ISNUMBER (cell_ref), "0" & cell_ref, cell_ref)
Tukaj
cell_ref: sklic na celico ustrezne celice
Uporabite formulo v celici
| = IF (ISNUMBER (F3), "0" & F3, F3) |
Pojasnilo: Funkcija ISNUMBER preveri celico v številki. Funkcija IF preveri, ali ima celica številko, nato doda nič pred številko, sicer vrne samo vrednost celice.
Kot lahko vidite na zgornji sliki, se pred številko doda nič (0). Preverimo, ali ta funkcija deluje na drugih vrednostih. Kopirajte formulo v druge celice, izberite celice v prvi celici, kjer je formula že uporabljena, uporabite bližnjico Ctrl+D
vrednost bo dodala z ničlami, da bo pred znaki dobila najmanj eno ničlo. Vsekakor kul nova uporaba funkcije, ki je sicer nikoli ne uporabljam.
Izračunani stolpec Power Pivot
Obstaja še ena možnost, da v vrtilno tabelo dodate ničle. Lahko jih dodate v izračunani stolpec v Power Pivot. Na ta način lahko uporabite nov stolpec v območju Filtriraj, Vrstice ali Stolpec vrtilne tabele.
= FORMAT (številke [številka], "000000")
V dodatku Power Pivot lahko dodate nov stolpec in s funkcijo FORMAT ustvarite začetne ničle v stolpcu s formulo, kot je opisano zgoraj. Izračunani stolpec izračuna vrednost za vsako vrstico, zato funkcije ni treba zaviti v celico. Zdaj lahko uporabite ta nov stolpec znotraj območja vrstic vrtilne tabele, tako kot kateri koli drug stolpec podatkov.
Povzetek
Morda je frustrirajuće videti, da vse vaše ničle izginejo, ko ne vemo, zakaj se to dogaja ali kako to preprečiti. Kot lahko vidite, obstaja veliko načinov, kako zagotoviti, da se vse te ničle držijo v vaših podatkih. Ne glede na vaše zahteve, zagotovo obstaja dobra rešitev za vas.
Tu so vse opombe, ki uporabljajo formulo v Excelu
Opombe:
- Formulo lahko uporabimo tako za besedila kot za številke.
- Besedilo za iskanje in zamenjavo je treba razvrstiti v tabelo. Ker uporabljamo indekse za iskanje in zamenjavo, se prepričajte, da ima besedilo »najdi« enak indeks kot besedilo »zamenjaj«. Bolje je, da imamo oba seznama drug ob drugem, kot imamo v drugem primeru.
- Splošna formula ima dve funkciji ZAMENJAVA. Potrebovali boste toliko nadomestnih funkcij, kolikor jih morate narediti.
- nadomestni znaki pomagajo pri pridobivanju vrednosti iz podnizov.
- Funkcija podpira logične operatorje, kot so,, = vendar se ti uporabljajo z dvojnimi narekovaji ( " ) z argumenti. Če pa za merila uporabljate referenco celic, lahko narekovaje prezrete.
- Argument meril mora uporabljati znake narekovajev (") za besedila (na primer" Gillen ") ali številke (na primer"> 50 ").
Upam, da je ta članek o različnih načinih dodajanja ničl (0) spredaj v Excelu razložljiv. Več člankov o izračunu vrednosti in sorodnih formulah Excel najdete tukaj. Če so vam bili naši blogi všeč, jih delite s prijatelji na Facebooku. Prav tako nas lahko spremljate na Twitterju in Facebooku. Radi bi slišali od vas, nam sporočite, kako lahko izboljšamo, dopolnimo ali inoviramo svoje delo in ga izboljšamo. Pišite nam na spletnem mestu e -pošte.
Excel REPLACE vs SUBSTITUTE funkcija: Funkciji REPLACE in SUBSTITUTE sta najbolj napačno razumljeni funkciji. Za iskanje in zamenjavo danega besedila uporabimo funkcijo SUBSTITUTE. Kjer se REPLACE uporablja za zamenjavo številnih znakov v nizu.
Zamenjajte besedilo s konca niza, začenši s spremenljivega položaja: Za zamenjavo besedila s konca niza uporabimo funkcijo REPLACE. Funkcija REPLACE uporablja položaj besedila v nizu za zamenjavo.
Kako preveriti, ali niz vsebuje eno od številnih besedil v Excelu: S to formulo preverimo, ali niz vsebuje katero od več besedil. S funkcijo SUM povzamemo vsa ujemanja in nato izvedemo logiko, da preverimo, ali niz vsebuje katerega od več nizov.
Preštej celice, ki vsebujejo določeno besedilo: Preprosta funkcija COUNTIF bo naredila čarovnijo. Za štetje števila več celic, ki vsebujejo dani niz, uporabimo nadomestni operator s funkcijo COUNTIF.
Priljubljeni članki:
50 bližnjic v Excelu za večjo produktivnost : Pospešite svoje naloge v Excelu. Te bližnjice vam bodo pomagale povečati učinkovitost dela v Excelu.
Kako uporabljati funkcijo VLOOKUP v Excelu : To je ena najpogosteje uporabljenih in priljubljenih funkcij programa Excel, ki se uporablja za iskanje vrednosti iz različnih obsegov in listov.
Kako uporabljati funkcijo IF v Excelu : Stavek IF v Excelu preveri pogoj in vrne določeno vrednost, če je pogoj TRUE, ali vrne drugo posebno vrednost, če je FALSE.
Kako uporabljati funkcijo SUMIF v Excelu : To je še ena bistvena funkcija nadzorne plošče. To vam pomaga povzeti vrednosti za posebne pogoje.
Kako uporabljati funkcijo COUNTIF v Excelu : Štejte vrednosti s pogoji s to neverjetno funkcijo. Za štetje določenih vrednosti vam ni treba filtrirati podatkov. Funkcija Countif je bistvena za pripravo vaše armaturne plošče.