V tomto článku sa naučíme Rôzne spôsoby, ako v programe Excel pridať nuly (0 s) dopredu.
Scenár:
Pridanie nuly pred číslo v programe Excel. Predvolený Excel neberie pred číslo v bunke nuly. Skúsili ste niekedy zadať niektoré údaje, ako napríklad 000123, do programu Excel? Pravdepodobne si rýchlo všimnete, že Excel automaticky odstráni úvodné nuly zo vstupného čísla. To môže byť skutočne nepríjemné, ak chcete, aby v údajoch boli vedúce nuly, a neviete, ako ich udržať v Exceli. Našťastie existuje niekoľko spôsobov, ako na začiatku vynulovať čísla nulami. V tomto článku vysvetlím rôzne spôsoby, ako pridať alebo ponechať tieto počiatočné nuly k vašim číslam.
Rôzne funkcie na pridanie nuly (0) vpredu
Ako vieme, funkcia TEXT je najpoužívanejšou funkciou na pridanie 0 dopredu, ale tu vysvetlím viac spôsobov, ako pridať nuly (0 s) k číslam.
- Funkcia TEXT
- PRAVÁ funkcia
- Funkcia BASE
- Pridajte 0 do hodnôt kontingenčnej tabuľky
Formátovať ako text
Jedným z riešení je prevod formátu čísla na text, pretože ak zadáme 00xyz, Excel túto hodnotu považuje za text a ponechá pred sebou jej 0. Existujú rôzne spôsoby prevodu bunky s číslom na textový formát.
- Použitie vlastného formátu
- Použitie funkcie TEXT
- Použitie Apostrophe (') na začiatku hodnoty pri vstupe
Vlastný formát
Poďme porozumieť týmto metódam jeden po druhom. Použitie vlastného formátu na zmenu formátu je najľahší spôsob, ako zmeniť už existujúce čísla v rozsahu. Stačí vybrať rozsah a použiť skratku Ctrl + 1 alebo vybrať položku Viac číselného formátu z rozbaľovacieho zoznamu na karte Domov so zobrazením Všeobecné.
Zmeňte formát z General na Text.
- Vyberte rozsah buniek, do ktorých chcete zadať úvodné nuly.
- Prejdite na kartu Domov.
- V sekcii Čísla kliknite na rozbaľovací zoznam Formát.
- Vyberte Text z možností formátu.
Ak sa teraz pokúsite zadať čísla s úvodnými nulami, nezmiznú, pretože sú namiesto čísel zadané ako textové hodnoty. Používatelia Excelu môžu pridať vlastné formátovanie na formátovanie čísel s úvodnými nulami.
Zdá sa však, že majú iba počiatočné nuly. Základné údaje sa s pridanými nulami nezmenia na text.
Pridajte vlastný formát, ktorý bude zobrazovať úvodné nuly.
- Vyberte rozsah buniek, do ktorých chcete pridať úvodné nuly, a otvorte dialógové okno Formát buniek.
- Kliknite pravým tlačidlom myši a zvoľte Formát buniek.
- Použite klávesovú skratku Ctrl + 1.
- Prejdite na kartu Číslo.
- Z možností kategórie vyberte položku Vlastné.
- Pridajte nový vlastný formát do vstupu Typ. Ak chcete, aby bol celkový počet číslic vrátane všetkých počiatočných núl 6, pridajte ako vlastný formát 000000.
- Stlačte tlačidlo OK.
Funkcia TEXT
Funkcia TEXT vám umožní použiť vlastné formátovanie na akékoľvek číselné údaje, ktoré už sú vo vašej tabuľke.
= TEXT (hodnota, formát)
- Hodnota - je to hodnota, ktorú chcete previesť na text a použiť na ňu formátovanie.
- Formát - toto je formátovanie, ktoré sa má použiť.
= TEXT (B3, "000000")
Ak chcete k číslu v bunke B3 pridať nuly tak, aby celkový počet číslic bol 6, môžete použiť vyššie uvedený vzorec.
Na začiatku použite Apostrophe (')
Môžete vynútiť, aby Excel zadával číslo ako text pomocou úvodného apostrofu.
To znamená, že pri zadávaní údajov budete môcť držať tieto nuly vpredu.
Táto metóda je pri zadávaní údajov rýchla a jednoduchá. Pred akékoľvek čísla zadajte znak „a“. Excelu to povie, že údaje majú byť textové, a nie číslo.
Keď stlačíte Enter, úvodné nuly zostanú v pracovnom hárku viditeľné. Písmeno „nebude v pracovnom hárku viditeľné, ale stále je k dispozícii a je ho možné vidieť na paneli vzorcov, keď je v bunke aktívny kurzor bunky.
PRAVÁ funkcia
Ďalším spôsobom, ako predniesť nuly pomocou vzorca, je použiť funkciu DOPRAVA. K číslu môžete zreťaziť reťazec núl a potom odrezať doplnky pomocou funkcie DOPRAVA.
Funkcia VPRAVO vytiahne z textovej hodnoty N znakov úplne vpravo.
= PRÁVO (text, [číslo])
- Text - Toto je text, z ktorého chcete extrahovať znaky.
- Číslo (Voliteľné)- Toto je počet znakov, ktoré je potrebné z textu extrahovať. Ak tento argument nie je zadaný, bude extrahovaný iba prvý znak.
= SPRÁVNE („000000“ & B3, 6)
Vyššie uvedený vzorec spojí niekoľko núl na začiatok čísla v bunke B3, potom vráti 6 znakov úplne vpravo, čo spôsobí niektoré úvodné nuly.
BASE (funkcia)
Všimnite si tento článok, ktorý popisuje 9 spôsobov, ako pridať úvodné nuly, ale moje video na YouTube ukazuje iba 8 spôsobov.
Je to preto, že som nevedel, že môžete použiť funkciu BASE na pridanie úvodných núl, kým to niekto neuviedol v komentároch k videu.
Funkcia BASE vám umožňuje previesť číslo na textovú reprezentáciu s daným základom.
Čísla, ktoré zvyčajne používate, sú základňou 10, ale pomocou tejto funkcie môžete previesť číslo základne 10 na (binárne) znázornenie na základe 2.
= BASE (číslo, základňa, [min. Dĺžka])
- Číslo - toto je číslo, ktoré chcete previesť na textovú hodnotu v inom základe.
- Základňa - je to základňa, na ktorú chcete hodnotu previesť.
- Min. Dĺžka (Voliteľné) - Toto je minimálna dĺžka znakov skonvertovanej textovej hodnoty.
Použite vzorec
| = ZÁKLAD (B3,10,6) |
Vyššie uvedený vzorec prevedie číslo v bunke B3 na základ 10 (už je to číslo na základe 10, ale zostane ako základ 10) a skonvertuje ho na textovú hodnotu s najmenej 6 znakmi. Ak je konvertovaná hodnota menšia ako 6 znakov, hodnotu sa vynuluje nulami, aby sa získalo minimálne 6 znakov.
Rozhodne skvelé nové použitie pre funkciu, ktorú inak nikdy nepoužívam.
Nulu (0) pridajte iba pred čísla, nie pre textové hodnoty
Tu zvážime scenár, ktorý pokrýva textové aj číselné hodnoty v rovnakom zozname. Teraz program Excel považuje každú bunku z hľadiska formátu za inú. Teraz nechajme Excel vedieť, že ak má bunka číselnú hodnotu, pridajte pred ňu 0, alebo ak má bunka text alebo inú hodnotu, nechajte ju tak, ako je.
Na to použijeme kombináciu dvoch funkcií IF a ISNUMBER. Tu uvádzame niekoľko hodnôt, ktoré je potrebné vyskúšať.
Generický vzorec pokračuje podobne
= IF (ISNUMBER (cell_ref), "0" & cell_ref, cell_ref)
Tu
cell_ref: odkaz na bunku zodpovedajúcej bunky
V bunke použite vzorec
| = IF (ISNUMBER (F3), "0" & F3, F3) |
Vysvetlenie: Funkcia ISNUMBER kontroluje číslo bunky. Ak funkcia IF skontroluje, či má bunka číslo, pridá pred číslo nulu, inak vráti hodnotu bunky.
Ako vidíte na obrázku vyššie, pred číslo je pridaná nula (0). Skontrolujeme, či táto funkcia funguje na iných hodnotách. Skopírujte vzorec do iných buniek, vyberte bunky, ktoré preberajú prvú bunku, v ktorej je vzorec už použitý, použite klávesovú skratku Ctrl+D
hodnotu vynuluje nulami, aby sa pred znaky dostala minimálne jedna nula. Rozhodne skvelé nové použitie pre funkciu, ktorú inak nikdy nepoužívam.
Stĺpec vypočítaný pre Power Pivot
Existuje ďalšia možnosť, ako pridať nuly do kontingenčnej tabuľky. Môžete ich pridať do vypočítaného stĺpca v doplnku Power Pivot. Týmto spôsobom môžete použiť nový stĺpec v oblasti Filter, Riadky alebo Stĺpec v kontingenčnej tabuľke.
= FORMÁT (čísla [číslo], "000000")
V doplnku Power Pivot môžete pridať nový stĺpec a pomocou funkcie FORMAT vytvoriť v stĺpci úvodné nuly podľa vzorca uvedeného vyššie. Vypočítaný stĺpec vypočíta hodnotu pre každý riadok, takže nie je potrebné funkciu zabaľovať do bunky. Teraz môžete tento nový stĺpec použiť v oblasti Riadky kontingenčnej tabuľky rovnako ako ktorýkoľvek iný stĺpec údajov.
Zhrnutie
Vidieť zmiznúť všetky nuly, keď nevieme, prečo sa to deje alebo ako tomu zabrániť, môže byť frustrujúce. Ako vidíte, existuje veľa spôsobov, ako zaistiť, aby sa všetky tieto nuly držali vo vašich údajoch. Nech už sú vaše požiadavky akékoľvek, určite pre vás existuje dobré riešenie.
Tu sú všetky poznámky k pozorovaniu pomocou vzorca v programe Excel
Poznámky:
- Vzorec je možné použiť pre texty aj čísla.
- Text na nájdenie a nahradenie by mal byť usporiadaný v tabuľke. Pretože na nájdenie a nahradenie používame indexy, uistite sa, že text „nájsť“ má rovnaký register ako text „nahradiť“. Je lepšie mať oba zoznamy vedľa seba, ako to máme v druhom príklade.
- Generický vzorec má dve funkcie SUBSTITUTE. Budete potrebovať toľko náhradných funkcií, koľko ich máte urobiť.
- zástupné znaky pomáhajú extrahovať hodnotu z podreťazcov.
- Funkcia podporuje logické operátory ako,, = ale používajú sa pomocou znaku dvojitých úvodzoviek ( " ) s argumentmi. Ak však pre kritériá používate odkaz na bunku, úvodzovky môžete ignorovať.
- Argument kritérií by mal používať text úvodzovky (") pre texty (napríklad" Gillen ") alebo čísla (ako"> 50 ").
Dúfame, že tento článok o rôznych spôsoboch pridávania núl (0 s) dopredu v programe Excel je vysvetľujúci. Tu nájdete ďalšie články o výpočte hodnôt a súvisiacich vzorcoch programu Excel. Ak sa vám páčili naše blogy, zdieľajte ich so svojimi priateľmi na Facebooku. A tiež nás môžete sledovať na Twitteri a Facebooku. Radi by sme vás počuli, dajte nám vedieť, ako môžeme zlepšiť, doplniť alebo inovovať našu prácu a zlepšiť ju pre vás. Napíšte nám na emailovú stránku.
Funkcia Excel REPLACE vs SUBSTITUTE: Funkcie REPLACE a SUBSTITUTE sú najviac nepochopenými funkciami. Na nájdenie a nahradenie daného textu používame funkciu SUBSTITUTE. Kde sa REPLACE používa na nahradenie počtu znakov v reťazci.
Nahradiť text od konca reťazca počnúc od variabilnej pozície: Na nahradenie textu z konca reťazca používame funkciu REPLACE. Funkcia REPLACE používa na nahradenie pozíciu textu v reťazci.
Ako skontrolovať, či reťazec obsahuje jeden z mnohých textov v programe Excel: Na kontrolu, či reťazec obsahuje niektorý z viacerých textov, použijeme tento vzorec. Na zhrnutie všetkých zápasov použijeme funkciu SUMA a potom vykonáme logiku, aby sme skontrolovali, či reťazec obsahuje niektorý z viacerých reťazcov.
Spočítajte bunky, ktoré obsahujú konkrétny text: Kúzlo urobí jednoduchá funkcia COUNTIF. Na spočítanie počtu viacerých buniek, ktoré obsahujú daný reťazec, používame zástupný znak s funkciou COUNTIF.
Populárne články:
50 skratiek programu Excel na zvýšenie produktivity : Zrýchlite sa so svojimi úlohami v Exceli. Tieto skratky vám pomôžu zvýšiť efektivitu práce v programe Excel.
Ako používať funkciu VLOOKUP v programe Excel : Toto je jedna z najpoužívanejších a najobľúbenejších funkcií programu Excel, ktorá sa používa na vyhľadávanie hodnôt z rôznych rozsahov a hárkov.
Ako používať funkciu IF v programe Excel : Príkaz IF v programe Excel skontroluje podmienku a vráti konkrétnu hodnotu, ak je podmienka PRAVDA, alebo vráti inú konkrétnu hodnotu, ak je NEPRAVDA.
Ako používať funkciu SUMIF v programe Excel : Toto je ďalšia zásadná funkcia palubnej dosky. To vám pomôže zhrnúť hodnoty za konkrétnych podmienok.
Ako používať funkciu COUNTIF v programe Excel : Počítajte hodnoty s podmienkami pomocou tejto úžasnej funkcie. Na počítanie konkrétnych hodnôt nie je potrebné filtrovať údaje. Funkcia Countif je potrebná na prípravu palubnej dosky.