V tomto článku se naučíme různé způsoby, jak v aplikaci Excel přidat nuly (0 s).
Scénář:
Přidání nuly před číslo v aplikaci Excel. Výchozí Excel nebere nuly před číslem v buňce. Zkoušeli jste někdy zadat některá data jako 000123 do Excelu? Pravděpodobně si rychle všimnete, že Excel automaticky odstraní úvodní nuly ze vstupního čísla. To může být opravdu nepříjemné, pokud chcete, aby v datech byly ty nuly a nevíte, jak je přimět, aby si je Excel nechal. Naštěstí existuje několik způsobů, jak na začátku zadat čísla nulami. V tomto článku vysvětlím různé způsoby, jak přidat nebo ponechat úvodní nuly k vašim číslům.
Různé funkce pro přidání nuly (0) vpředu
Jak víme, funkce TEXT je nejpoužívanější funkcí pro přidání 0 dopředu, ale zde vysvětlím více způsobů, jak přidat nuly (0 s) k číslům.
- Funkce TEXT
- PRAVÁ funkce
- Funkce BASE
- Přidejte 0 do hodnot kontingenční tabulky
Formátovat jako text
Jedním z řešení je převod formátu čísla na text, protože pokud zadáme 00xyz, aplikace Excel tuto hodnotu považuje za text a ponechá před sebou její 0. Existuje několik způsobů, jak převést buňku čísla na textový formát.
- Použití vlastního formátu
- Pomocí funkce TEXT
- Použití Apostrophe (') na začátku hodnoty při zadávání
Vlastní formát
Pojďme porozumět těmto metodám jeden po druhém. Použití vlastního formátu ke změně formátu je nejjednodušší způsob, jak změnit již existující čísla v rozsahu. Stačí vybrat rozsah a použít klávesovou zkratku Ctrl + 1 nebo z rozevíracího seznamu na kartě Domů zobrazit Obecné.
Změňte formát z Obecné na Text.
- Vyberte rozsah buněk, do kterých chcete zadat úvodní nuly.
- Přejděte na kartu Domů.
- V sekci Čísla klikněte na výběr rozevíracího seznamu Formát.
- Vyberte Text z možností formátu.
Pokud se nyní pokusíte zadat čísla s úvodními nulami, nezmizí, protože jsou zadána jako textové hodnoty namísto čísel. Uživatelé aplikace Excel mohou k formátování čísel s úvodními nulami přidat vlastní formátování.
Zdá se však, že mají pouze úvodní nuly. Podkladová data nebudou s přidanými nulami změněna na text.
Přidejte vlastní formát, který bude zobrazovat úvodní nuly.
- Vyberte rozsah buněk, do kterých chcete přidat úvodní nuly, a otevřete dialogové okno Formát buněk.
- Klikněte pravým tlačítkem a vyberte Formát buněk.
- Použijte klávesovou zkratku Ctrl + 1.
- Přejděte na kartu Číslo.
- Z možností kategorie vyberte Vlastní.
- Přidejte nový vlastní formát do vstupu Typ. Pokud chcete, aby celkový počet číslic včetně všech počátečních nul byl 6, přidejte 000000 jako vlastní formát.
- Stiskněte tlačítko OK.
Funkce TEXT
Funkce TEXT vám umožní použít vlastní formátování na jakákoli čísla, která již máte v tabulce.
= TEXT (hodnota, formát)
- Hodnota - Toto je hodnota, kterou chcete převést na text a použít formátování.
- Formát - toto je formátování, které se má použít.
= TEXT (B3, "000000")
Pokud jste chtěli přidat nuly k číslu v buňce B3 tak, aby celkový počet číslic byl 6, pak můžete použít výše uvedený vzorec.
Použití Apostrophe (') na začátku
Můžete vynutit aplikaci Excel, aby zadala číslo jako text pomocí úvodního apostrofu.
To znamená, že tyto nuly budete moci ponechat vpředu při zadávání dat.
Tato metoda je rychlá a snadná při zadávání dat. Před libovolná čísla zadejte znak „a“. To řekne Excelu, že data mají být textová a ne čísla.
Když stisknete Enter, úvodní nuly zůstanou v listu viditelné. Písmeno „nebude v listu viditelné, ale stále je k dispozici a lze jej zobrazit na řádku vzorců, když je v buňce kurzor aktivní buňky.
PRAVÁ funkce
Další způsob, jak dostat nuly pomocí vzorce, je pomocí funkce VPRAVO. K číslu můžete zřetězit řetězec nul a poté odříznout doplňky pomocí funkce DOPRAVA.
Funkce VPRAVO z textové hodnoty extrahuje N znaků nejvíce vpravo.
= RIGHT (text, [číslo])
- Text - Toto je text, ze kterého chcete extrahovat znaky.
- Číslo (Volitelný)- Toto je počet znaků, které je třeba z textu extrahovat. Pokud tento argument není zadán, bude extrahován pouze první znak.
= VPRAVO ("000000" & B3, 6)
Výše uvedený vzorec zřetězí několik nul na začátek čísla v buňce B3, poté vrátí 6 znaků zcela vpravo, což má za následek některé úvodní nuly.
Funkce BASE
Všimněte si, že tento článek popisuje 9 způsobů, jak přidat úvodní nuly, ale moje video na YouTube ukazuje pouze 8 způsobů.
Je to proto, že jsem nevěděl, že můžete použít funkci BASE k přidání úvodních nul, dokud to někdo nezmíní v komentářích k videu.
Funkce BASE vám umožňuje převést číslo na textovou reprezentaci s danou základnou.
Čísla, která obvykle používáte, jsou základna 10, ale pomocí této funkce můžete převést číslo na základně 10 (binární) reprezentaci.
= BASE (číslo, základna, [min. Délka])
- Číslo - Toto je číslo, které chcete převést na textovou hodnotu v jiném základu.
- Base - Toto je základna, na kterou chcete převést hodnotu.
- Min. Délka (Volitelný) - Toto je minimální délka znaků převedené textové hodnoty.
Použijte vzorec
| = ZÁKLAD (B3,10,6) |
Výše uvedený vzorec převede číslo v buňce B3 na základ 10 (je to již číslo na základně 10, ale zachová jej jako základ 10) a převede jej na textovou hodnotu s alespoň 6 znaky. Pokud je převedená hodnota menší než 6 znaků, vynuluje hodnotu nulami, aby získala minimálně 6 znaků.
Rozhodně skvělé nové použití pro funkci, kterou jinak nikdy nepoužívám.
Přidejte nulu (0) pouze před čísla, která nejsou na textových hodnotách
Zde zvážíme scénář, který pokrývá textové i číselné hodnoty ve stejném seznamu. Nyní excel považuje každou buňku z hlediska formátu za jinou. Jak nyní nechat Excelu vědět, že pokud má buňka číselnou hodnotu, přidejte před ni 0, nebo pokud má buňka text nebo jinou hodnotu, nechte ji tak, jak je.
K tomu použijeme kombinaci dvou funkcí IF a ISNUMBER. Zde máme několik hodnot, které je třeba vyzkoušet.
Obecný vzorec pokračuje jako
= IF (ISNUMBER (cell_ref), "0" & cell_ref, cell_ref)
Tady
cell_ref: odkaz na buňku odpovídající buňky
Použijte vzorec v buňce
| = IF (ISNUMBER (F3), "0" & F3, F3) |
Vysvětlení: Funkce ISNUMBER kontroluje číslo buňky. Pokud funkce IF zkontroluje, zda má buňka číslo, přidá před číslo nulu, jinak vrátí hodnotu buňky.
Jak vidíte na výše uvedeném obrázku, před číslo se přidá nula (0). Pojďme zkontrolovat, zda tato funkce funguje na jiných hodnotách. Zkopírujte vzorec do jiných buněk, vyberte buňky, které vezmou první buňku, kde je vzorec již použit, použijte klávesovou zkratku Ctrl+D
hodnotu vynuluje nulami, aby se před znaky dostala minimálně jedna nula. Rozhodně skvělé nové použití pro funkci, kterou jinak nikdy nepoužívám.
Vypočtený sloupec Power Pivot
Existuje další možnost přidání nul do kontingenční tabulky. Můžete je přidat do vypočítaného sloupce v Power Pivotu. Tímto způsobem můžete použít nový sloupec v oblasti Filtr, Řádky nebo Sloupec kontingenční tabulky.
= FORMÁT (čísla [číslo], "000000")
V doplňku Power Pivot můžete přidat nový sloupec a pomocí funkce FORMAT vytvořit ve sloupci úvodní nuly podle vzorce uvedeného výše. Vypočítaný sloupec vypočítá hodnotu pro každý řádek, takže není nutné zabalit funkci do buňky. Nyní můžete tento nový sloupec použít v oblasti řádků kontingenční tabulky stejně jako jakýkoli jiný sloupec dat.
souhrn
Může být frustrující vidět všechny vaše nuly zmizet, když nevíme, proč se to děje nebo jak tomu zabránit. Jak vidíte, existuje mnoho způsobů, jak zajistit, aby se všechny ty nuly ve vašich datech držely. Ať už je váš požadavek jakýkoli, určitě pro vás existuje dobré řešení.
Zde jsou všechny poznámky k pozorování pomocí vzorce v aplikaci Excel
Poznámky:
- Vzorec lze použít jak pro texty, tak pro čísla.
- Text pro hledání a nahrazování by měl být seřazen v tabulce. Protože k hledání a nahrazování používáme rejstříky, ujistěte se, že text „najít“ má stejný rejstřík jako text „nahradit“. Je lepší mít oba seznamy vedle sebe, jako máme ve druhém příkladu.
- Obecný vzorec má dvě funkce SUBSTITUTE. Budete potřebovat tolik náhradních funkcí, kolik jich máte udělat.
- zástupné znaky pomáhají s extrahováním hodnoty z podřetězců.
- Funkce podporuje logické operátory jako,, = ale ty se používají pomocí dvojité uvozovky ( " ) s argumenty. Pokud však pro kritéria používáte odkaz na buňku, uvozovky lze ignorovat.
- Argument kritéria by měl používat znak uvozovek (") pro texty (jako" Gillen ") nebo čísla (jako"> 50 ").
Doufám, že tento článek o různých způsobech přidání nul (0 s) vpředu v aplikaci Excel je vysvětlující. Další články o výpočtu hodnot a souvisejících vzorcích Excelu najdete zde. Pokud se vám naše blogy líbily, sdílejte je se svými přáteli na Facebooku. A také nás můžete sledovat na Twitteru a Facebooku. Rádi bychom od vás slyšeli, dejte nám vědět, jak můžeme zlepšit, doplnit nebo inovovat naši práci a zlepšit ji pro vás. Napište nám na e -mail.
Funkce Excel REPLACE vs SUBSTITUTE: Funkce REPLACE a SUBSTITUTE jsou nejvíce nepochopenými funkcemi. K nalezení a nahrazení daného textu používáme funkci SUBSTITUTE. Kde REPLACE se používá k nahrazení řady znaků v řetězci.
Nahradit text od konce řetězce počínaje od proměnné polohy: K nahrazení textu z konce řetězce používáme funkci VYMĚNIT. Funkce REPLACE nahradí pozici textu v řetězci.
Jak zkontrolovat, zda řetězec obsahuje jeden z mnoha textů v aplikaci Excel: Abychom zkontrolovali, zda řetězec obsahuje některý z více textů, použijeme tento vzorec. Pomocí funkce SUMA sečteme všechny shody a poté provedeme logiku, abychom zkontrolovali, zda řetězec obsahuje některý z více řetězců.
Počítejte buňky, které obsahují konkrétní text: Jednoduchá funkce COUNTIF udělá kouzlo. Pro počítání počtu více buněk, které obsahují daný řetězec, používáme zástupný operátor s funkcí COUNTIF.
Oblíbené články:
50 zkratek aplikace Excel pro zvýšení produktivity : Získejte rychleji své úkoly v aplikaci Excel. Tyto zkratky vám pomohou zvýšit efektivitu práce v Excelu.
Jak používat funkci VLOOKUP v Excelu : Toto je jedna z nejpoužívanějších a nejoblíbenějších funkcí Excelu, která se používá k vyhledávání hodnot z různých rozsahů a listů.
Jak používat funkci IF v aplikaci Excel : Příkaz IF v aplikaci Excel zkontroluje podmínku a vrátí konkrétní hodnotu, pokud je podmínka PRAVDA, nebo vrátí jinou konkrétní hodnotu, pokud NEPRAVDA.
Jak používat funkci SUMIF v aplikaci Excel : Toto je další základní funkce řídicího panelu. To vám pomůže shrnout hodnoty za konkrétních podmínek.
Jak používat funkci COUNTIF v aplikaci Excel : Pomocí této úžasné funkce spočítejte hodnoty s podmínkami. K počítání konkrétních hodnot nemusíte data filtrovat. Funkce Countif je nezbytná pro přípravu řídicího panelu.