Vannak helyzetek, amikor az Excelben egy adathalmazhoz kell hozzáadni a kezdő nullákat. Ez akkor lehet így, ha Excelben rekordokat vezet, például munkavállalói azonosítókat vagy tranzakcióazonosítókat.
Például érdemes lehet következetes képet kapni az adatkészletről, az alábbiak szerint:
Ebben az oktatóanyagban számos módon megtanulhatja, hogyan adhat hozzá kezdő nullákat az Excelben:
- A formátum átalakítása szöveggé
- Egyéni számformázás használata
- Szöveg funkció használata
- A REPT/LEN funkciók használata
- A VBA használata
Ezen módszerek mindegyikének van néhány előnye és hátránya (mindegyik fejezetben tárgyaljuk).
Nézzük meg, hogyan működik ezek mindegyike.
Vegye fel a vezető nullákat a formátum szöveggé alakításával
Mikor kell használni: Ha van egy kis numerikus adathalmaza, és ezt a szerkesztést kézzel kívánja elvégezni.
Tegyük fel, hogy rendelkezik a marketing osztály alkalmazotti azonosítóival, amint az alább látható, és szeretné, hogy ezek az azonosítók konzisztensek legyenek az első nullák hozzáadásával.
Tehát megpróbálja megváltoztatni az azonosítót az első nullák megadásával (00001 az 1 helyett).
Meglepetésére az Excel visszaállítja 1 -re.
Ez akkor történik, amikor az Excel megérti, hogy a 00001 és az 1 ugyanaz a szám, és ugyanazokat a megjelenítési szabályokat kell követniük.
Bármennyire is frusztráló az Ön számára, az Excelnek megvannak az okai.
Tehát ahhoz, hogy az Excel szabályok meghajlítása nélkül végezze el a munkát, ki kell használnia azt a tényt, hogy ez a szabály nem vonatkozik a szövegformázásra.
Tehát a következőket kell tennie:
- Válassza ki azokat a cellákat, amelyekhez manuálisan szeretné hozzáadni az első nullákat.
- Lépjen a kezdőlapra → Számcsoport, és válassza a Szöveg lehetőséget a legördülő menüből.
Ez az!
Most, amikor manuálisan adja meg a kezdő nullákat, az Excel könnyen megfelel.
Vigyázat: Ha a formátumot szöveggé konvertálja, egyes Excel -funkciók nem fognak megfelelően működni. Például a SUM/COUNT függvény figyelmen kívül hagyja a cellát, mivel szöveges formátumú.
Adjon hozzá vezető nullákat egyéni számformázással
Mikor kell használni: Ha numerikus adatkészlete van, és azt szeretné, hogy az eredmény numerikus legyen (nem szöveg).
Ha egy számot meghatározott formátumban jelenít meg, az nem változtatja meg a szám mögöttes értékét. Például az 1000-es számot 1000-ként vagy 1000-ként vagy 1000,00-ként vagy 001000-ként vagy 26-09-1902-ként tudom megjeleníteni (a dátumok számok az Excelben a háttérben).
A szám megjelenítésének különböző módjain a szám értéke soha nem változik. Csak a megjelenítés módja változik.
A kezdő nullák hozzáadásához formázhatjuk, hogy így mutassák, miközben a mögöttes érték változatlan marad.
Íme a lépések ennek a technikának a használatához az első nullák hozzáadásához Excelben:
- Válassza ki azokat a cellákat, amelyekhez kezdő nullákat szeretne hozzáadni.
- Lépjen a Kezdőlap → Számcsoport menüpontra, és kattintson a párbeszédablak indítójára (egy kis ferde nyíl a jobb alsó sarokban). Ezzel megnyílik a Cellák formázása párbeszédpanel. Alternatív megoldásként a billentyűparancsot is használhatja: Control + 1.
- A Cellák formázása párbeszédpanel Számszám lapján válassza a Kategória listában az Egyéni lehetőséget.
- A Típus mezőbe írja be a 00000 értéket
- Kattintson az OK gombra.
Ha ezt megteszi, akkor az összes szám öt számjegyű lesz, és ha a szám kevesebb, mint 5 számjegy, akkor a rendszer automatikusan hozzáadja az első 0 -t. Tehát 10 00010, 100 pedig 00100 lesz.
Ebben az esetben hat nullát használtunk, de ha adatai több számjegyű számokat tartalmaznak, akkor ennek megfelelően kell használni a formátumot.
Megjegyzés: Ez a technika csak numerikus adatkészleteknél működik. Abban az esetben, ha olyan alkalmazotti azonosítókkal rendelkezik, mint az A1, A2, A3 és így tovább, akkor ezek szövegek, és nem változnak, ha a fent látható egyéni formátumot alkalmazza.
Adja hozzá a vezető nullákat a TEXT funkció használatával
Mikor kell használni: Amikor azt szeretné, hogy az eredmény szöveges legyen.
A TEXT funkció lehetővé teszi, hogy az értéket a kívánt formátumra változtassa.
Például, ha azt szeretné, hogy az 1 -et 001 -ként jelenítse meg, akkor használja a SZÖVEG funkciót.
Ne feledje azonban, hogy a SZÖVEG függvény megváltoztatja a formátumot, és SZÖVEG -re változtatja. Ez azt jelenti, hogy amikor az 1 -et 001 -ként adja meg, az Excel az új eredményt három karakterből álló szövegként kezeli (mint az abc vagy az xyz).
A következőképpen adhatja hozzá a kezdő nullákat a SZÖVEG funkció segítségével:
- Ha az A oszlopban vannak a számok (mondjuk az A2 -ből: A100), akkor válassza a B2: B100 lehetőséget, és írja be a következő képletet:
= SZÖVEG (A2, "00000") - Nyomja le a Control + Enter billentyűkombinációt, hogy a képletet az összes kijelölt cellára alkalmazza.
Ez az összes számot öt számjegyként jeleníti meg, ahol az első 0 -k automatikusan hozzáadódnak, ha a szám kevesebb, mint 5 számjegy.
Az adatok szöveggé alakításának egyik előnye, hogy mostantól használhatja azokat olyan keresési képletekben, mint a VLOOKUP vagy az INDEX/MATCH, hogy lekérje az alkalmazott adatait az alkalmazott azonosítójával.
Megjegyzés: Ez a technika csak numerikus adatkészleteknél működik. Abban az esetben, ha olyan alkalmazotti azonosítókkal rendelkezik, mint az A1, A2, A3 és így tovább, akkor ezek szövegek, és nem változnak, ha a fent látható egyéni formátumot alkalmazza.
Adjon hozzá vezető nullákat a REPT és a LEN funkciók használatával
Mikor kell használni: Ha numerikus/alfanumerikus adatkészlete van, és azt szeretné, hogy az eredmény szöveges legyen.
A TEXT funkció használatának hátránya az volt, hogy csak numerikus adatokkal működik. De abban az esetben, ha rendelkezik alfanumerikus adatkészlettel (mondjuk A1, A2, A3 és így tovább), akkor a SZÖVEG funkció sikertelen lesz.
Ilyen esetekben a REPT és a LEN funkció kombinációja teszi a dolgát.
Ezt a következőképpen teheti meg:
- Ha az A oszlopban vannak a számok (mondjuk az A2 -ből: A100), akkor válassza a B2: B100 lehetőséget, és írja be a következő képletet:
= REPT (0,5-LEN (A2)) és A2 - Nyomja le a Control + Enter billentyűkombinációt, hogy a képletet az összes kijelölt cellára alkalmazza.
Ezzel az összes számot/karakterláncot 5 karakter hosszúságúvá teheti, ahol az első nullák szükségesek.
Ez a képlet így működik:
- A LEN (A2) megadja a karakterlánc/számok hosszát a cellában.
- = REPT (0,5-LEN (A2)) megadná a hozzáadandó 0 számot. Itt az 5 -öt használtam a képletben, mivel ez volt a karakterlánc/szám maximális hossza az adathalmazomban. Ezt az adatoknak megfelelően módosíthatja.
- = REPT (0,5-LEN (A2)) & A2 egyszerűen hozzáadja a nullák számát a cella értékéhez. Például, ha a cella értéke 123, akkor ez 00123 értéket ad vissza.
Vezető nullák hozzáadása egyéni funkcióval (VBA)
Ha az Excelben az első nullák hozzáadását gyakran kell elvégeznie, akkor érdemes egyéni funkciót használni.
Itt található a VBA kód, amely egyszerű funkciót hoz létre a kezdő nullák hozzáadásához:
'Sumit Bansal kódja a http://trumpexcel.com oldalról <= StrLen Then Result = Eredmény és közepes (ref, i, 1) Más eredmény = "0" & Result End if Next i AddLeadingZeroes = Eredmény vége funkció
Egyszerűen adja hozzá ezt a kódot a modulkód ablakhoz, és ugyanúgy használhatja, mint bármely más munkalap funkciót.
Vagy hozzon létre egy bővítményt, és ossza meg kollégáival.
