Ez az oktatóanyag bemutatja a kezdő nullák hozzáadásának vagy megtartásának, valamint a kezdő nullák eltávolításának különböző módszereit az Excelben.
Amikor egy vagy több kezdő nullát tartalmazó számokat ír be vagy importál, az Excel automatikusan eltávolítja ezeket a nullákat, és csak maga a szám jelenik meg a cellákban (652). Ennek az az oka, hogy a kezdő nullák nem szükségesek a számításokhoz, és nem számítanak.
Előfordul azonban, hogy szükség van ezekre a kezdő nullákra, például amikor azonosítószámokat, telefonszámokat, hitelkártyaszámokat, termékkódokat, irányítószámokat stb. ír be. Szerencsére az Excel számos lehetőséget kínál a kezdő nullák hozzáadására vagy megtartására. sejtekben. Ebben a cikkben bemutatjuk a kezdő nullák hozzáadásának vagy megtartásának, valamint a kezdő nullák eltávolításának különböző módjait.
Vezető nullák hozzáadása az Excelben
Lényegében két módszer használható a kezdő nullák hozzáadására: az egyik, formázza a számot „Szöveg”-ként; Másodszor, használjon egyéni formázást a kezdő nullák hozzáadásához. A használni kívánt módszer attól függhet, hogy mit szeretne kezdeni a számmal.
Érdemes lehet a kezdő nullát hozzáadni, amikor egyedi azonosítószámokat, számlaszámokat, társadalombiztosítási számokat vagy irányítószámokat ír be. De ezeket a számokat nem fogja használni számításokhoz vagy függvényekhez, ezért a legjobb, ha ezeket konvertálja. számokat szöveggé. Soha nem összegezné vagy átlagolná a telefonszámokat vagy számlaszámokat.
Többféleképpen is hozzáadhat vagy beilleszthet nullákat a számok elé, szöveges formázással:
- A cellaformátum módosítása szövegre
- Aposztróf hozzáadása (')
- SZÖVEG funkció használata
- A REPT/LEN funkció használata
- Használja a CONCATENATE funkciót/és jel operátort (&)
- A RIGHT funkció használata
A cellaformátum módosítása szövegre
Ez az egyik legegyszerűbb módja a kezdő nullák hozzáadásának a számokhoz. Ha csak számokat akar beírni, és a kezdő nullákat szeretne beírni, akkor ez a módszer az Ön számára. Ha a cellaformátumot Általánosról vagy Számról szövegre változtatja, arra kényszerítheti az Excelt, hogy a számokat szöveges értékként kezelje, és bármi, amit a cellába ír be, pontosan ugyanaz marad. Íme, hogyan kell csinálni:
Jelölje ki azt a cella(ka)t, amely(ek)be kezdő nullákat szeretne hozzáadni. Lépjen a „Főoldal” fülre, kattintson a „Formátum” legördülő listára a Számok csoportban, és válassza a „Szöveg” lehetőséget a formázási lehetőségek közül.
Most, amikor beírja a számokat, az Excel nem törli a kezdő nullát.
Előfordulhat, hogy a cella bal felső sarkában egy kis zöld háromszöget (Hibajelző) láthat, és amikor kiválasztja azt a cellát, megjelenik egy figyelmeztető tábla, amely jelzi, hogy a számot szövegként tárolta.
A hibaüzenet eltávolításához válassza ki a cella(ka)t, kattintson a figyelmeztető jelre, majd válassza a „Hiba figyelmen kívül hagyása” lehetőséget a listából.
A telefonszámokat szóközzel vagy kötőjellel is beírhatja a számok közé, az Excel ezeket a számokat automatikusan szövegként kezeli.
A Leading használata Aposztróf (')
A kezdő nullák hozzáadásának másik módja az Excelben, ha a szám elejére egy aposztrófot (') adunk meg. Ez arra kényszeríti az Excelt, hogy számot írjon be szövegként.
Csak írjon be egy aposztrófot a számok elé, és nyomja meg az „Enter” billentyűt. Az Excel érintetlenül hagyja a kezdő nullákat, de a (') csak akkor lesz látható a munkalapon, ha kijelöli a cellát.
Szövegfüggvény használata
A fenti módszer nullákat ad a számokhoz, ahogy beírja őket, de ha már van számlistája, és a kezdő nullákat szeretné eléjük illeszteni, akkor a SZÖVEG funkció a megfelelő módszer az Ön számára. A SZÖVEG funkció lehetővé teszi számok szöveges karakterláncokká alakítását, miközben egyéni formázást alkalmaz.
A SZÖVEG szintaxisa függvény:
= SZÖVEG(érték, formátum_szöveg)Ahol,
- érték - Ez az a számérték, amelyet szöveggé kell konvertálnia és formázást kell alkalmaznia.
- format_text – az alkalmazni kívánt formátum.
A SZÖVEG funkcióval megadhatja, hogy a szám hány számjegyű legyen. Például, ha azt szeretné, hogy a számok 8 számjegyből álljanak, akkor írjon be 8 nullát a függvény második argumentumába: „00000000”. Ha egy cellában 6 jegyű szám van, akkor a függvény manuálisan hozzáad 2 kezdő nullát, és ha 2 jegyű számok vannak, például 56, a többi nulla lesz (00000056).
Például a kezdő nullák hozzáadásához és a számok hatjegyűvé tételéhez használja ezt a képletet:
=SZÖVEG(A2"000000")Mivel a képlet második argumentumában 6 nulla van, a függvény a számsort szöveges karakterláncsá alakítja, és 5 kezdő nullát ad hozzá, hogy a karakterlánc 6 számjegyű legyen.
Jegyzet: Kérjük, ne felejtse el a formátumkódokat idézőjelbe tenni a függvényben.
Most ugyanezt a képletet alkalmazhatja a többi cellára is a kitöltő fogantyújának húzásával. Amint láthatja, a függvény a számokat szöveggé alakítja, és a számokhoz bevezető nullákat ad, hogy a számjegyek teljes száma 6 legyen.
A SZÖVEG függvény mindig szöveges karakterláncként ad vissza értéket, nem számként, ezért nem fogja tudni használni őket aritmetikai számításokhoz, de továbbra is használhatja őket olyan keresési képletekben, mint például a VLOOKUP vagy az INDEX/MATCH, hogy lekérje a részleteket. termék azonosítók használatával.
CONCATENATE funkció/és operátor (&) használata
Ha egy oszlopban az összes szám elé fix számú kezdő nullát szeretne hozzáadni, használhatja a CONCATENATE funkciót vagy az "és" operátort (&).
A CONCATENATE függvény szintaxisa:
=ÖSSZEGZÉS(szöveg1, [szöveg2], ...)Ahol,
szöveg1 – A szám elé beszúrandó nullák száma.
szöveg2 – Az eredeti szám vagy cellahivatkozás
Az "és" jel operátor szintaxisa:
=Érték_1 és Érték_2 Ahol,
Az érték_1 a szám elé szúrandó kezdő nullák, az Érték_2 pedig a szám.
Ha például csak két nullát szeretne hozzáadni egy szám elé, használja a következő képletet:
=ÖSSZESÍTÉS("00",A2)Az első argumentum két nulla ("00"), mert az A2-ben lévő szám elé szeretnénk két nullát beírni (ami a második argumentum).
Vagy,
="00"&A2Itt az első argumentum 2 nulla, ezt követi az „&” operátor, a második argumentum pedig a szám.
Amint látható, a képlet csak két kezdő nullát ad egy oszlopban lévő összes számhoz, függetlenül attól, hogy a szám hány számjegyet tartalmaz.
Mindkét képlet bizonyos számú nullát egyesít az eredeti számok előtt, és szöveges karakterláncként tárolja azokat.
A REPT/LEN funkció használata
Ha a numerikus vagy alfanumerikus adatokhoz vezető nullákat szeretne hozzáadni, és a karakterláncot szöveggé szeretné alakítani, használja a REPT funkciót. A REPT funkció egy karakter(ek) meghatározott számú ismétlésére szolgál. Ez a funkció arra is használható, hogy a szám elé fix számokat szúrjon be kezdő nullákból.
=REPT(szöveg, szám_szorok)Ahol a „szöveg” az a karakter, amelyet meg akarunk ismételni (esetünkben „0”), a „number_times” argumentum pedig az, hogy hányszor szeretnénk megismételni ezt a karaktert.
Például, ha számok előtt öt nullát szeretne generálni, a képlet így néz ki:
=REPT(0,5)&A2A képlet annyit tesz, hogy megismétli 5 nullát, és összekapcsolja az A2-ben lévő számsort, és visszaadja az eredményt. Ezután a képletet a kitöltő fogantyúval alkalmazza a B2:B6 cellára.
A fenti képlet fix számú nullákat ad a szám elé, de a szám teljes hossza a számtól függően változik.
Ha egy adott karakter hosszúságú (fix hosszúságú) karakterlánc létrehozásához bevezető nullákat szeretne hozzáadni, akkor a REPT és a LEN függvényeket együtt használhatja.
Szintaxis:
=REPT(szöveg, szám_szorok-LEN(szöveg))&cellaPéldául, ha az A2-ben lévő értékhez előtagozott nullákat szeretne hozzáadni, és egy 5 karakter hosszúságú karakterláncot szeretne létrehozni, próbálja ki a következő képletet:
=REPT(0,5-LEN(A2))&A2Itt a „LEN(A2)” megkapja az A2 cellában lévő karakterlánc/számok teljes hosszát. Az „5” a karakterlánc/számok maximális hossza a cellában. És a „REPT(0,5-LEN(A2))” rész hozzáadja a nullák számát úgy, hogy az A2-ben lévő karakterlánc hosszát kivonja a nullák maximális számából (5). Ezután néhány 0-t összekapcsolunk az A2 értéke előtt, hogy egy rögzített hosszúságú karakterláncot hozzunk létre.
A RIGHT funkció használata
Egy másik módja annak, hogy az Excelben a karakterlánc előtt nullákat töltsön ki, a JOBB funkció használata.
A RIGHT függvény számos nullát tud hozzáadni egy szám elejéhez, és kivonja a jobb szélső N karaktert az értékből.
Szintaxis:
= JOBBRA (szöveg, karakterek száma)- szöveg az a cella vagy érték, amelyből karaktereket szeretne kinyerni.
- karakterek száma a szövegből kivonható karakterek száma. Ha ez az argumentum nincs megadva, akkor csak az első karakter kerül kibontásra.
Ennél a módszernél a nullák maximális számát összefűzzük a „szöveg” argumentumban szereplő karakterláncot tartalmazó cellahivatkozással.
Ha egy hatjegyű számot szeretne létrehozni az A-beli számsor alapján, kezdő nullákkal, próbálja ki a következő képletet:
=JOBBRA("0000000"&A2;6)A képlet első argumentuma (szövege) 7 nullát ad az A2-ben lévő értékhez ("0000000" és A2), majd visszaadja a jobb szélső 7 karaktert, ami néhány kezdő nullát eredményez.
Vezető nullák hozzáadása egyéni számformázással
Ha a fenti módszerek bármelyikét alkalmazza a bevezető nullák szám elé helyezésére, mindig szöveges karakterláncot kap, nem számot. És nem sok hasznuk lesz a számításokban vagy a numerikus képletekben.
A kezdő nullák hozzáadásának legjobb módja az Excelben egyéni számformázás alkalmazása. Ha a kezdő nullákat úgy ad hozzá, hogy egyéni számformátumot ad a cellához, az nem módosítja a cella értékét, hanem csak a megjelenítési módját. Az érték továbbra is szám marad, nem szöveg.
A cellák számformátumának megváltoztatásához kövesse az alábbi lépéseket:
Válassza ki azt a cellát vagy cellatartományt, ahol a kezdő nullákat meg szeretné jeleníteni. Ezután kattintson a jobb gombbal bárhová a kiválasztott tartományon belül, és válassza ki a „Cellák formázása” lehetőséget a helyi menüből. Vagy nyomja meg a Ctrl + 1 gyorsbillentyűket.
A Cellák formázása ablakban lépjen a „Szám” fülre, és a Kategória opciók alatt válassza az „Egyéni” lehetőséget.
Írja be a nullák számát a „Típus:” mezőbe, hogy megadja a cellában megjeleníteni kívánt számjegyek teljes számát. Ha például azt szeretné, hogy a szám 6 számjegyből álljon, akkor egyéni formátumkódként írja be a „000000” kódot. Ezután kattintson az „OK” gombra az alkalmazáshoz.
Ez a bevezető nullákat jeleníti meg a számok előtt, és ha a szám kevesebb, mint 6 számjegy, akkor nullát ír be.
Úgy tűnik, hogy a számok kezdő nullákat tartalmaznak, miközben a mögöttes érték változatlan marad. Ha egyéni formázású cellát választ ki, akkor a képletsorban az eredeti szám jelenik meg
Számos digitális helyőrző használható egyéni számformátumban. De csak két elsődleges helyőrző használható a számok kezdő nulláinak hozzáadásához.
- 0 – Ez a számjegy helyőrző, amely extra nullákat jelenít meg. Kényszerített számjegyeket jelenít meg 0-9 között, függetlenül attól, hogy a számjegy releváns-e az értékhez, vagy sem. Például, ha 000.00 formátumkóddal 2.5-öt ír be, akkor 002.50 jelenik meg.
- # – Ez a számjegy helyőrző, amely opcionális számjegyeket jelenít meg, és nem tartalmaz extra nullákat. Ha például 123-at ír be a 000# formázási kóddal, akkor a 0123 jelenik meg.
Ezenkívül a formátumkódban szereplő írásjelek vagy egyéb karakterek úgy jelennek meg, ahogy vannak. Használhat karaktereket, például kötőjelet (-), vesszőt (,), perjelet (/) stb.
Például egyéni formátum használatával a számokat telefonszámként is formázhatja.
A Kód formázása a Cellák formázásában párbeszédpanel:
Az eredmény:
Alkalmazzuk ezt a formázási kódot a következő példában:
##0000
Amint látható, a „0” plusz nullákat ad hozzá, míg a „#” nem jelent jelentéktelen nullákat:
A Cellák formázása párbeszédpanel „Speciális formátumok” részében előre meghatározott formátumkódokat is használhat irányítószámokhoz, telefonszámokhoz és társadalombiztosítási számokhoz.
A következő táblázat azokat a számokat mutatja be nullákkal, amelyeknél különböző „Speciális” formátumú kódok vannak alkalmazva a különböző oszlopokhoz:
A vezető nullák eltávolítása az Excelben
Most megtanulta, hogyan kell kezdő nullákat hozzáadni az Excelhez, lássuk, hogyan távolíthatja el a kezdő nullákat a karakterláncok számából. Néha, amikor adatokat importál egy külső forrásból, előfordulhat, hogy a számok előtagjai nullák, és szövegként formázódnak. Ilyen esetekben el kell távolítani a kezdő nullákat, és vissza kell alakítani őket számokká, hogy felhasználhassák a képletekben.
Különféle módon távolíthatja el a kezdő nullákat az Excelben, és egyenként fogjuk látni őket.
Távolítsa el a vezető nullákat a cellaformázás megváltoztatásával
Ha a kezdő nullákat egyéni számformázással adtak hozzá, akkor a cellák formátumának megváltoztatásával könnyen eltávolíthatók. A címsorból megtudhatja, hogy a cellái egyedi formátumúak-e (a nullák a cellában, nem a címsorban fognak megjelenni).
Az előtagozott nullák eltávolításához jelölje ki a cellákat a kezdő nullákkal, kattintson a „Számformátum” mezőre, és válassza az „Általános” vagy a „Szám” formázási lehetőséget.
Most eltűntek a kezdő nullák:
Törölje a bevezető nullákat a szöveg számokká alakításával
Ha a kezdő nullákat a cellaformátum megváltoztatásával vagy aposztrófokkal adták meg a számok elé, vagy automatikusan hozzáadták az adatok importálásakor, akkor a legegyszerűbb módja a Hibaellenőrzés opció használatával számokká alakítani. Íme, hogyan kell csinálni:
Ezt a módszert akkor használhatja, ha a számok balra vannak igazítva, és a cellákban egy kis zöld háromszög (hibajelző) található a cellák bal felső sarkában. Ez azt jelenti, hogy a számok szövegként vannak formázva.
Jelölje ki ezeket a cellákat, és egy sárga figyelmeztetés jelenik meg a kijelölés jobb felső részén. Ezután kattintson a „Konvertálás számra” lehetőségre a legördülő menüben.
A nullák törlődnek, és a számok vissza lesznek konvertálva számformátumba (jobbra igazítva).
A vezető nullák eltávolítása által Szorzás/osztás 1-gyel
Egy másik egyszerű és legjobb módja az első lépés eltávolításának a számok 1-gyel való szorzása vagy osztása. Az érték osztása vagy szorzása nem változtatja meg az értéket, egyszerűen visszaváltja az értéket számmá, és eltávolítja a kezdő nullákat.
Ehhez írja be az alábbi példában szereplő képletet egy cellába, és nyomja meg az ENTER billentyűt. A bevezető nullákat eltávolítja, és a karakterláncot számmá alakítja vissza.
Ezután alkalmazza ezt a képletet más cellákra a kitöltő fogantyú segítségével.
Ugyanezt az eredményt érheti el a „Speciális beillesztés” parancs használatával. Itt van, hogyan:
Írja be az „1” számértéket egy cellába (mondjuk a B2-be), és másolja ki az értéket.
Ezután jelölje ki azokat a cellákat, amelyekből el kívánja távolítani a kezdő nullákat. Ezután kattintson a jobb gombbal a kijelölésre, majd válassza a „Speciális beillesztés” lehetőséget.
A Speciális beillesztés párbeszédpanel Művelet alatt válassza a „Szorzás” vagy „Osztás” lehetőséget, majd kattintson az „OK” gombra.
Ez az, a bevezető nullák el lesznek távolítva, így a karakterláncok számként maradnak meg.
Távolítsa el a vezető nullákat képletek segítségével
Az előtagozott nullák törlésének másik egyszerű módja az ÉRTÉK függvény használata. Ez a módszer akkor is hasznos lehet, ha a kezdő nullákat más képlet vagy aposztróf használatával, vagy egyéni formázással adta hozzá.
=ÉRTÉK(A1)A képlet argumentuma lehet egy érték vagy az értéket tartalmazó cellahivatkozás. A képlet eltávolítja a kezdő nullákat, és az értéket szövegből számmá alakítja. Ezután alkalmazza a képletet a többi cellára.
Néha előfordulhat, hogy el szeretné távolítani a kezdő nullákat, de szeretné megtartani a számokat szöveges formátumban. Ilyen esetekben a TEXT() és VALUE () függvényeket együtt kell használni, így:
=SZÖVEG(ÉRTÉK(A1),"#")Az ÉRTÉK függvény az A1-ben lévő értéket számmá alakítja. De a második argumentum, a „#” az értéket visszakonvertálja szöveges formátumba minden további nullák nélkül. Ennek eredményeként a számokat kezdő nullák nélkül kapja meg, de továbbra is szöveges formátumban (balra igazítva).
Távolítsa el a vezető nullákat az Excel Szöveg oszlopok funkciójával
A kezdő nullák eltávolításának egy másik módja az Excel Szöveg oszlopok funkciójának használata.
Válassza ki a cellák azon tartományát, amelyekben a számok kezdő nullákkal vannak ellátva.
Ezután lépjen az „Adatok” fülre, és kattintson a „Szöveg az oszlopokba” gombra az Adateszközök csoportban.
Megjelenik a „Szöveg konvertálása oszlopokká” varázsló. A 3/1. lépésben válassza a „Határozott” lehetőséget, és kattintson a „Tovább” gombra.
A 3/2. lépésben törölje az összes határoló jelét, és kattintson a „Tovább” gombra.
Az utolsó lépésben hagyja meg az Oszlop adatformátum opciót az „Általános” értékben, és válassza ki azt a célhelyet (a tartomány első celláját), ahová a számokat nullák nélkül szeretné megadni. Ezután kattintson a „Befejezés” gombra
És a számokat a kezdőbetűkkel együtt egy külön oszlopban kapja meg, az alábbiak szerint.
Ez minden.