Existujú situácie, keď potrebujete do množiny údajov v programe Excel pridať úvodné nuly. To môže byť prípad, ak uchovávate záznamy v programe Excel, napríklad ID zamestnancov alebo ID transakcií.
Môžete napríklad chcieť získať konzistentný vzhľad vo svojej množine údajov, ako je uvedené nižšie:
V tomto tutoriáli sa naučíte rôzne spôsoby, ako pridať úvodné nuly do Excelu:
- Konvertovanie formátu na text
- Použitie vlastného formátovania čísel
- Použitie textovej funkcie
- Používanie funkcií REPT/LEN
- Použitie VBA
Každá z týchto metód má určité výhody a nevýhody (popísané v každej časti).
Pozrime sa, ako každý z nich funguje.
Pridajte úvodné nuly prevodom formátu na text
Kedy použiť: Keď máte malý súbor numerických údajov a plánujete vykonať túto úpravu ručne.
Predpokladajme, že máte ID zamestnancov marketingového oddelenia, ako je uvedené nižšie, a chcete, aby tieto ID vyzerali konzistentne pridaním úvodných núl.
Skúste teda zmeniť ID zadaním úvodných núl (00001 namiesto 1).
Ale na vaše prekvapenie, Excel ho prevedie späť na 1.
Stáva sa to, pretože Excel chápe, že 00001 a 1 sú rovnaké čísla a mali by sa riadiť rovnakými pravidlami zobrazovania.
Teraz, keď je to pre vás frustrujúce, Excel má svoje dôvody.
Aby ste prácu vykonali bez ohýbania pravidiel programu Excel, budete musieť využiť skutočnosť, že toto pravidlo sa nevzťahuje na formátovanie textu.
Tu je teda to, čo musíte urobiť:
- Vyberte bunky, do ktorých chcete manuálne pridať úvodné nuly.
- Choďte na Domov → Skupina čísel a z rozbaľovacieho zoznamu vyberte Text.
To je všetko!
Keď teraz manuálne zadáte úvodné nuly, Excel to ochotne splní.
Upozornenie: Keď konvertujete formát na text, niektoré funkcie programu Excel nebudú správne fungovať. Napríklad funkcia SUM/COUNT by bunku ignorovala, pretože je v textovom formáte.
Pridajte úvodné nuly pomocou vlastného formátovania čísel
Kedy použiť: Ak máte číselnú množinu údajov a chcete, aby bol výsledok numerický (nie textový).
Keď zobrazíte číslo v konkrétnom formáte, nezmení to základnú hodnotu čísla. Môžem napríklad zobraziť číslo 1000 ako 1000 alebo 1 000 alebo 1 000,00 alebo 001 000 alebo 26-09-1902 (párne dátumy sú čísla v backende v Exceli).
Pri všetkých rôznych spôsoboch zobrazenia čísla sa hodnota čísla nikdy nemení. Mení sa iba spôsob, akým sa zobrazuje.
Ak chcete pridať úvodné nuly, môžeme ho naformátovať, aby to takto ukázal, pričom základná hodnota by zostala nezmenená.
Tu sú kroky na použitie tejto techniky na pridanie úvodných núl v programe Excel:
- Vyberte bunky, do ktorých chcete pridať úvodné nuly.
- Choďte na Domov → Skupina čísel a kliknite na spúšťač dialógov (malá naklonená šípka vpravo dole). Otvorí sa dialógové okno Formát buniek. Alternatívne môžete tiež použiť klávesovú skratku: Ctrl + 1.
- V dialógovom okne Formát buniek na karte Číslo vyberte v zozname Kategória položku Vlastné.
- Do poľa Typ zadajte 00000
- Kliknite na tlačidlo OK.
Tým sa vždy zobrazia všetky čísla ako päť číslic, pričom úvodné 0 sa automaticky pridajú, ak je číslo menšie ako 5 číslic. Takže 10 by sa stalo 00010 a 100 by sa stalo 00100.
V tomto prípade sme použili šesť núl, ale ak majú vaše údaje čísla s viacerými číslicami, musíte podľa toho použiť formát.
Poznámka: Táto technika by fungovala iba pre číselnú množinu údajov. V prípade, že máte ID zamestnancov ako A1, A2, A3 a tak ďalej, potom ide o text a nezmenia sa, ak použijete vlastný formát, ako je uvedené vyššie.
Pridajte úvodné nuly pomocou funkcie TEXT
Kedy použiť: Keď chcete, aby výsledkom bol text.
Funkcia TEXT vám umožňuje zmeniť hodnotu na požadovaný formát.
Ak napríklad chcete, aby sa 1 zobrazovalo ako 001, môžete na to použiť funkciu TEXT.
Nezabudnite však, že funkcia TEXT zmení formát a zmení ho na TEXT. To znamená, že keď zadáte 1 ako 001, Excel bude nový výsledok považovať za text s tromi znakmi (rovnako ako abc alebo xyz).
Tu je postup, ako pridať úvodné nuly pomocou funkcie TEXT:
- Ak máte čísla v stĺpci A (povedzme od A2: A100), vyberte B2: B100 a zadajte nasledujúci vzorec:
= TEXT (A2, „00000 ″) - Stlačením klávesov Control + Enter použijete vzorec na všetky vybraté bunky.
Toto zobrazí všetky čísla ako päť číslic, kde sa úvodné 0 automaticky pridajú, ak je číslo menšie ako 5 číslic.
Jednou z výhod prevodu údajov na text je, že ich teraz môžete použiť vo vyhľadávacích vzorcoch, ako sú VLOOKUP alebo INDEX/MATCH, na načítanie podrobností o zamestnancovi pomocou jeho ID zamestnanca.
Poznámka: Táto technika by fungovala iba pre číselnú množinu údajov. V prípade, že máte ID zamestnancov ako A1, A2, A3 a tak ďalej, potom ide o text a nezmenia sa, ak použijete vlastný formát, ako je uvedené vyššie..
Pridajte úvodné nuly pomocou funkcií REPT a LEN
Kedy použiť: Ak máte množinu údajov, ktorá je číselná/alfanumerická, a chcete, aby výsledkom bol text.
Nevýhodou použitia funkcie TEXT bolo, že bude fungovať iba s číselnými údajmi. Ale v prípade, že máte alfanumerický súbor údajov (povedzme A1, A2, A3 a tak ďalej), funkcia TEXT zlyhá.
V takýchto prípadoch funguje kombinácia REPT a LEN.
Postupujte takto:
- Ak máte čísla v stĺpci A (povedzme od A2: A100), vyberte B2: B100 a zadajte nasledujúci vzorec:
= REPT (0,5-LEN (A2)) & A2 - Stlačením klávesov Control + Enter použijete vzorec na všetky vybraté bunky.
To by znamenalo, že všetky čísla/reťazce budú mať 5 znakov a budú vždy na začiatku obsahovať nuly.
Tento vzorec funguje takto:
- LEN (A2) udáva dĺžku reťazca/čísel v bunke.
- = REPT (0,5-LEN (A2)) by dalo číslo 0, ktoré by malo byť pridané. Tu som použil 5 vo vzorci, pretože to bola maximálna dĺžka reťazca/čísel v mojej množine údajov. Môžete to zmeniť podľa svojich údajov.
- = REPT (0,5-LEN (A2)) & A2 by jednoducho pripočítalo počet núl k hodnote bunky. Ak je napríklad hodnota v bunke 123, vráti sa 00123.
Pridajte úvodné nuly pomocou vlastnej funkcie (VBA)
Ak je pridávanie úvodných núl v programe Excel niečo, čo musíte vykonávať pomerne často, je vhodné použiť vlastnú funkciu.
Tu je kód VBA, ktorý vytvorí jednoduchú funkciu na pridanie úvodných núl:
'Code by Sumit Bansal z http://trumpexcel.com Funkcia AddLeadingZeroes (odkaz ako rozsah, dĺžka ako celé číslo) Dim i ako celé číslo Dim Výsledok ako reťazec Dim StrLen Ako celé číslo StrLen = Len (odkaz) Pre i = 1 Na dĺžku If i <= StrLen Then Result = Result & Mid (ref, i, 1) Else Result = "0" & Result End If Next i AddLeadingZeroes = Result End Function
Jednoducho pridajte tento kód do okna kódu modulu a budete ho môcť používať rovnako ako všetky ostatné funkcie pracovného hárka.
Alebo k nemu vytvorte doplnok a budete ho môcť zdieľať so svojimi kolegami.
