V tomto článku sa naučíme, ako v programe Excel skombinovať textové a formátované hodnoty.
Scenár:
Excel spracováva hodnoty v mnohých formátoch, ako je číslo, dátum, čas, text, percento alebo mena. Tieto je možné navzájom meniť. Excel prevádza čísla na text, aby používal čísla ako text vo vzorcoch, ako je vlookup s číslami a textom. Funkcia Excel TEXT vám umožňuje previesť číslo na text. Na prevod čísel na text sa používa funkcia TEXT v Exceli. Zábavné na tom je, že toto číslo môžete naformátovať tak, aby sa zobrazovalo v požadovanom formáte. Naformátujte napríklad dátum rr-mm-dd do formátu dd-mm-rr. Pridajte znaky meny pred číslo a mnoho ďalších.
TEXTový vzorec v programe Excel
Funkcia TEXT je reťazcová funkcia, ktorá prevádza ľubovoľnú hodnotu na daný formát. Výsledok sa môže zdať, že je to číslo, ale je to v textovom formáte.
| =TEXT(cell_ref, text_format) |
cell_ref: hodnota na konverziu pomocou odkazu na bunku
Formát textu: Formát na konverziu
| Formát | Výstupný formát |
| d | 7 |
| ddd | Št |
| dddd | Štvrtok |
| m | 2 |
| mmm | Február |
| mmmm | Február |
| mmmmm | F |
| rr | 19 |
| rrrr | 2019 |
| m/d/r | 2/7/19 |
| mm/dd/rr | 02/07/19 |
| mm/dd/rrrr | 02/07/2019 |
| ddd, mmm d | Št, 7. februára |
| mm/dd/rrrr h: mm AM/PM | 7. februára 2019 14:15 |
| mm/dd/rrrr hh: mm: ss | 07/02/2019 14:15:00 |
Príklad:
Toto všetko môže byť mätúce na pochopenie. Poďme pochopiť, ako používať funkciu na príklade. Tu uvádzame niekoľko príkladov na prevod hodnôt dátumu do textového formátu alebo akéhokoľvek iného požadovaného formátu.
Preveďte hodnotu v bunke A2.
V bunke C2 použite vzorec
| = TEXT (A2; „0“) |
„0“: vráti všeobecný textový formát.
Stlačte Enter a skopírujte vzorec do zostávajúcich buniek pomocou Ctrl + D
Ako vidíte, čísla sme dostali ako textový výstup, pretože číslo významnosti sa vždy líšilo.
Tu uvádzame niekoľko čísel na konverziu do textového formátu alebo akéhokoľvek iného požadovaného formátu.
Použite vzorec:
| = TEXT (A2, "d") |
Ako vidíte, hodnota vo výstupnej bunke je v textovom formáte.
Môžete použiť ľubovoľný formát_text a vykonávať svoju prácu v programe Excel bez akéhokoľvek prerušenia
Niekedy budete musieť pred nejakú pevnú číslicu čísel, ako je telefónne číslo alebo číslo PIN, pridať 0. Na tento účel použite tento textový vzorec …
| =TEXT( 1234, “00000”) |
Ak máte N číslic čísla, v argumente textového formátu napíšte n+1 0 s.
Napíšte tento textový vzorec na pridanie meny.
| =TEXT( 1234, “$0”) |
Ako vidíte, čísla sme dostali ako textový výstup, pretože číslo významnosti sa vždy líšilo.
Kód VBA na prevod meny vo formáte TEXT
Tento kód je rozdelený na štyri jednotlivé funkcie. Hlavnou funkciou je NUM_TO_IND_RUPEE_WORD. A ďalšie tri funkcie GetHunderds (), GetTens () a GetDigits sú pomocné funkcie, ktoré pomáhajú hlavnej funkcii pri vytváraní reťazca.
Kód
| Funkcia NUM_TO_IND_RUPEE_WORD (ByVal MyNumber, voliteľné prírastky ako logická hodnota = pravda)
Dim Crores, Lakhs, Rupees, Paise, Temp Dim DecimalPlace as Long, Count As Long Dim myLakhs, myCrores ReDimovo miesto (9) ako reťazec Miesto (2) = „Tisíc“: Miesto (3) = „Milión“ Miesto (4) = "Miliarda": Miesto (5) = "Bilión"
„Reťazcové zobrazenie sumy. MyNumber = Trim (Str (MyNumber))
„Poloha desatinného miesta 0, ak nie je. DecimalPlace = InStr (MyNumber, ".")
„Previesť Paise a nastaviť Moje číslo na sumu rupií. Ak je DecimalPlace> 0 Potom Paise = GetTens (vľavo (v strede (moje číslo, desatinné miesto + 1) a „00“, 2)) Moje číslo = Orezať (vľavo (Moje číslo, DecimalPlace - 1)) Koniec Ak
myCrores = Moje číslo \ 10000000 myLakhs = (MyNumber - myCrores * 10000000) \ 100000 MyNumber = MyNumber - myCrores * 10000000 - myLakhs * 100000 Počet = 1
Robiť, kým myCrores "" Teplota = GetHundreds (vpravo (myCrores, 3)) Ak je teplota "", potom Crores = teplota a miesto (počet) & Crores Ak Len (myCrores)> 3 Potom myCrores = vľavo (myCrores, Len (myCrores) - 3) Inak myCrores = "" Koniec Ak Count = Count + 1 Slučka Počet = 1
Robiť, kým myLakhs "" Teplota = GetHundreds (vpravo (myLakhs, 3)) Ak je teplota "", potom Lakhs = teplota a miesto (počet) a lakš Ak Len (myLakhs)> 3 Potom myLakhs = vľavo (myLakhs, Len (myLakhs) - 3) Inak myLakhs = "" Koniec Ak Count = Count + 1 Slučka Počet = 1
Robiť pri mojom čísle „“ Teplota = GetHundreds (vpravo (moje číslo, 3)) Ak je teplota "", potom rupie = teplota a miesto (počet) & rupie Ak Len (Moje číslo)> 3 Potom MyNumber = vľavo (MyNumber, Len (MyNumber) - 3) Inak Moje číslo = "" Koniec Ak Count = Count + 1 Slučka
Vyberte položku Case Crores Prípad "": Crores = "" Prípad „One“: Crores = „One Crore“ Iný prípad: Crores = Crores & "Crores" Ukončiť výber Vyberte položku Case Lakhs Prípad "": Lakhs = "" Prípad „One“: Lakhs = „One Lakh“ Iný prípad: Lakhs = Lakhs & „Lakhs“ Ukončiť výber Vyberte prípadové rupie Prípad "": Rupie = "Nula" Prípad „jeden“: rupie = „jeden“ Iný prípad: Rupie = Rupie Ukončiť výber Vyberte možnosť Case Paise Prípad "": Paise = "a Paise Zero Only" Prípad „One“: Paise = „a Paise One Only“ Iný prípad: Paise = "a Paise" & Paise & "Only" Ukončiť výber „vytvorenie reťazca slov na preklad čísla do slov NUM_TO_IND_RUPEE_WORD = IIf (vrátane rupií, „rupií“, „“ “) & Crores & _ lakhov a rupií a Paise Koncová funkcia „Skonvertuje číslo zo 100-999 na text Funkcia GetHundreds (ByVal MyNumber) Stmaviť výsledok ako reťazec Ak Val (MyNumber) = 0, potom funkciu ukončite MyNumber = Right ("000" & MyNumber, 3) „Premeňte stovky. Ak je stredná (Moje číslo, 1, 1) „0“, potom Výsledok = GetDigit (stred (moje číslo, 1, 1)) a „stovka“ Koniec Ak „Preveďte miesto v desiatkach a v jednotkách. Ak je stredná (Moje číslo, 2, 1) „0“, potom Výsledok = Výsledok a počet (stredné (Moje číslo, 2)) Inak Výsledok = Výsledok a získanie číslice (stred (moje číslo, 3)) Koniec Ak GetHundreds = Výsledok Koncová funkcia „Skonvertuje číslo z 10 na 99 na text. Funkcia GetTens (TensText) Stmaviť výsledok ako reťazec Výsledok = "" 'Vynuluje dočasnú hodnotu funkcie. Ak Val (vľavo (TensText, 1)) = 1, potom 'If hodnota medzi 10-19… Select Case Val (TensText) Prípad 10: Výsledok = „Desať“ Prípad 11: Výsledok = „Jedenásť“ Prípad 12: Výsledok = „Dvanásť“ Prípad 13: Výsledok = „Trinásť“ Prípad 14: Výsledok = „Štrnásť“ Prípad 15: Výsledok = „Pätnásť“ Prípad 16: Výsledok = „Šestnásť“ Prípad 17: Výsledok = „Sedemnásť“ Prípad 18: Výsledok = „Osemnásť“ Prípad 19: Výsledok = „Devätnásť“ Prípad inak Ukončiť výber Inak 'Ak je hodnota medzi 20-99… Select Case Val (vľavo (TensText, 1)) Prípad 2: Výsledok = „Dvadsať“ Prípad 3: Výsledok = „Tridsať“ Prípad 4: Výsledok = „štyridsať“ Prípad 5: Výsledok = „Päťdesiat“ Prípad 6: Výsledok = „šesťdesiat“ Prípad 7: Výsledok = „sedemdesiat“ Prípad 8: Výsledok = „Osemdesiat“ Prípad 9: Výsledok = „Deväťdesiat“ Prípad inak Ukončiť výber Výsledok = Výsledok a GetDigit _ (Right (TensText, 1)) 'Načítaj svoje miesto. Koniec Ak GetTens = Výsledok Koncová funkcia „Skonvertuje číslo z 1 na 9 na text. Funkcia GetDigit (číslica) Select Case Val (číslica) Prípad 1: GetDigit = „Jeden“ Prípad 2: GetDigit = „Dva“ Prípad 3: GetDigit = „Tri“ Prípad 4: GetDigit = „Štyri“ Prípad 5: GetDigit = „Päť“ Prípad 6: GetDigit = „Šesť“ Prípad 7: GetDigit = „Sedem“ Prípad 8: GetDigit = „Osem“ Prípad 9: GetDigit = „Deväť“ Iný prípad: GetDigit = "" Ukončiť výber Koncová funkcia |
Vyššie uvedený kód dostane výsledok v správnom formáte. Teraz si vytvorte vlastnú prispôsobenú funkciu.
Spojenie textového reťazca a hodnoty vypočítanej podľa vzorca. Aby bol výsledok vrátený nejakým vzorcom pre používateľov zrozumiteľnejší, môžete ho zreťaziť pomocou textového reťazca, ktorý vysvetľuje, aká je hodnota v skutočnosti.
Na vrátenie aktuálneho dátumu môžete napríklad použiť nasledujúci vzorec:
| = CONCATENATE („Dnes je“, TEXT (DNES (), „dd-mmm-rr“)) |
Použitie CONCATENATE v programe Excel - veci, ktoré si treba zapamätať
Aby ste zaistili, že vaše vzorce CONCATENATE vždy poskytnú správne výsledky, pamätajte na nasledujúce jednoduché pravidlá:
Na to, aby funkcia Excel CONCATENATE fungovala, je potrebný aspoň jeden argument „text“.
Tu sú všetky poznámky k pozorovaniu pomocou vzorca v programe Excel
Poznámky:
- Na kombináciu textu použite & operátor. Operátor & funguje rovnako ako funkcia CONCAT
- V nových verziách Excelu je CONCATENATE nahradený funkciou CONCAT, ktorá má úplne rovnakú syntax. Funkcia CONCATENATE je zachovaná kvôli spätnej kompatibilite. Bežnou praxou je používať CONCAT, pretože Excel nedáva žiadne prísľuby, že CONCATENATE bude k dispozícii v budúcich verziách programu Excel.
Dúfam, že tento článok o tom, ako skombinovať text a formátované hodnoty 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.
Ako používať funkciu ISTEXT v programe Excel : vráti PRAVDU hodnotu logiky, ak je hodnota bunky textová pomocou funkcie ISTEXT v Exceli.
Ako zvýrazniť bunky, ktoré obsahujú konkrétny text v programe Excel : Zvýraznite bunky na základe vzorca a nájdite konkrétnu textovú hodnotu v bunke v programe Excel.
Skonvertuje desatinné sekundy na časový formát : Ako vieme, čas v programe Excel sa považuje za čísla. Hodiny, minúty a sekundy sa považujú za desatinné čísla. Keď teda máme sekundy ako čísla, ako konvertujeme na časový formát? Tento článok to rozobral.
Vypočítajte minúty medzi dátumami a časom v programe Excel : výpočet časového rozdielu je celkom jednoduchý. Stačí odpočítať čas začiatku od času konca. Viac informácií o tomto vzorci získate kliknutím na odkaz
Nahradiť text od konca reťazca začínajúc od premennej polohy: Na nahradenie textu z konca reťazca používame funkciu REPLACE. Funkcia REPLACE používa na nahradenie pozíciu textu v reťazci.
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.