Existují situace, kdy je třeba přidat úvodní nuly do datové sady v aplikaci Excel. To může být případ, pokud uchováváte záznamy v aplikaci Excel, například ID zaměstnanců nebo ID transakcí.
Můžete například chtít získat konzistentní vzhled ve své sadě dat, jak je uvedeno níže:
V tomto tutoriálu se naučíte různé způsoby, jak přidat úvodní nuly do Excelu:
- Převod formátu na text
- Použití vlastního formátování čísel
- Pomocí textové funkce
- Používání funkcí REPT/LEN
- Pomocí VBA
Každá z těchto metod má určité výhody a nevýhody (popsané v každé části).
Podívejme se, jak každý z nich funguje.
Přidejte úvodní nuly převedením formátu na text
Kdy použít: Pokud máte malou číselnou sadu dat a plánujete tuto úpravu provést ručně.
Předpokládejme, že máte ID zaměstnanců marketingového oddělení, jak je uvedeno níže, a chcete, aby tyto ID vypadaly konzistentně přidáním úvodních nul.
Zkusíte tedy změnit ID zadáním úvodních nul (00001 místo 1).
Ale k vašemu úžasu to Excel převede zpět na 1.
K tomu dochází, protože aplikace Excel chápe, že 00001 a 1 jsou stejná čísla a měla by se řídit stejnými pravidly zobrazení.
Nyní, stejně frustrující, jak to může být pro vás, Excel má své důvody.
Abyste mohli pracovat bez ohýbání pravidel aplikace Excel, budete muset využít toho, že toto pravidlo neplatí pro formátování textu.
Zde je tedy to, co musíte udělat:
- Vyberte buňky, do kterých chcete ručně přidat úvodní nuly.
- Přejděte na Domů → Skupina čísel a z rozevíracího seznamu vyberte Text.
A je to!
Když nyní ručně zadáte úvodní nuly, Excel to snadno splní.
Upozornění: Při převodu formátu na text nebudou některé funkce aplikace Excel fungovat správně. Například funkce SUM/COUNT by buňku ignorovala, protože je v textovém formátu.
Přidejte úvodní nuly pomocí vlastního formátování čísel
Kdy použít: Když máte číselnou datovou sadu a chcete, aby výsledek byl numerický (ne textový).
Když zobrazujete číslo v určitém formátu, nemění to základní hodnotu čísla. Například mohu zobrazit číslo 1000 jako 1000 nebo 1 000 nebo 1 000,00 nebo 001 000 nebo 26-09-1902 (sudá data jsou čísla v backendu v aplikaci Excel).
Při všech různých způsobech zobrazení čísla se hodnota čísla nikdy nemění. Změní se pouze způsob, jakým je zobrazen.
Chcete -li přidat úvodní nuly, můžeme jej naformátovat tak, aby to bylo vidět, zatímco základní hodnota by zůstala nezměněna.
Zde jsou kroky k použití této techniky k přidání úvodních nul v aplikaci Excel:
- Vyberte buňky, do kterých chcete přidat úvodní nuly.
- Přejděte na Domů → Skupina čísel a klikněte na spouštěč dialogů (malá nakloněná šipka vpravo dole). Otevře se dialogové okno Formát buněk. Alternativně můžete také použít klávesovou zkratku: Control + 1.
- V dialogovém okně Formát buněk na kartě Číslo vyberte v seznamu Kategorie vlastní.
- Do pole Typ zadejte 00000
- Klikněte na OK.
Tím se vždy zobrazí všechna čísla jako pět číslic, kde se počáteční 0 automaticky přidají, pokud je číslo menší než 5 číslic. Takže 10 by se stalo 00010 a 100 by se stalo 00100.
V tomto případě jsme použili šest nul, ale pokud mají vaše data čísla s více číslicemi, musíte podle toho použít formát.
Poznámka: Tato technika by fungovala pouze pro číselnou datovou sadu. V případě, že máte ID zaměstnance jako A1, A2, A3 atd., Pak se jedná o text a nezmění se, pokud použijete vlastní formát, jak je uvedeno výše.
Přidejte úvodní nuly pomocí funkce TEXT
Kdy použít: Když chcete, aby výsledkem byl text.
Funkce TEXT vám umožňuje změnit hodnotu na požadovaný formát.
Pokud například chcete, aby 1 byla zobrazena jako 001, můžete k tomu použít funkci TEXT.
Pamatujte však, že funkce TEXT by změnila formát a změnila jej na TEXT. To znamená, že když vytvoříte 1 jako 001, Excel bude nový výsledek považovat za text se třemi znaky (stejně jako abc nebo xyz).
Zde je návod, jak přidat úvodní nuly pomocí funkce TEXT:
- Pokud máte čísla ve sloupci A (řekněme od A2: A100), vyberte B2: B100 a zadejte následující vzorec:
= TEXT (A2, „00000 ″) - Stisknutím Ctrl + Enter použijete vzorec na všechny vybrané buňky.
Zobrazí se všechna čísla jako pět číslic, kde se počáteční 0 automaticky přidají, pokud je číslo menší než 5 číslic.
Jednou z výhod převodu dat na text je, že je nyní můžete použít ve vyhledávacích vzorcích, jako je VLOOKUP nebo INDEX/MATCH, k načtení podrobností zaměstnance pomocí jeho ID zaměstnance.
Poznámka: Tato technika by fungovala pouze pro číselnou datovou sadu. V případě, že máte ID zaměstnance jako A1, A2, A3 atd., Pak se jedná o text a nezmění se, pokud použijete vlastní formát, jak je uvedeno výše.
Přidejte úvodní nuly pomocí funkcí REPT a LEN
Kdy použít: Pokud máte sadu dat, která je číselná/alfanumerická a chcete, aby výsledkem byl text.
Nevýhodou použití funkce TEXT bylo, že by fungovala pouze s číselnými daty. Ale v případě, že máte alfanumerickou datovou sadu (řekněme A1, A2, A3 atd.), Pak by funkce TEXT selhala.
V takových případech funguje kombinace REPT a LEN.
Postupujte takto:
- Pokud máte čísla ve sloupci A (řekněme od A2: A100), vyberte B2: B100 a zadejte následující vzorec:
= REPT (0,5-LEN (A2)) & A2 - Stisknutím Ctrl + Enter použijete vzorec na všechny vybrané buňky.
Tím by byla všechna čísla/řetězce dlouhá 5 znaků s úvodními nulami, kdykoli je potřeba.
Tento vzorec funguje takto:
- LEN (A2) udává délku řetězce/čísel v buňce.
- = REPT (0,5-LEN (A2)) by dalo číslo 0, které by mělo být přidáno. Zde jsem použil 5 ve vzorci, protože to byla maximální délka řetězce/čísel v mé datové sadě. Můžete to změnit podle svých údajů.
- = REPT (0,5-LEN (A2)) & A2 by jednoduše přidalo počet nul k hodnotě buňky. Pokud je například hodnota v buňce 123, vrátí se 00123.
Přidejte úvodní nuly pomocí vlastní funkce (VBA)
Pokud přidávání úvodních nul do Excelu musíte provádět poměrně často, je dobré použít vlastní funkci.
Zde je kód VBA, který vytvoří jednoduchou funkci pro přidání úvodních nul:
'Code by Sumit Bansal z Odkaz <= StrLen Then Result = Result & Mid (ref, i, 1) Else Result = "0" & Result End If Next i AddLeadingZeroes = Result End Function
Jednoduše přidejte tento kód do okna kódu modulu a budete jej moci používat stejně jako jakoukoli jinou funkci listu.
Nebo pro něj vytvořte doplněk a budete jej moci sdílet se svými kolegy.
