Összeg betűvel: számok átírása szöveggé képlettel

csekkGyakran látom a kérdést különböző fórumokon, hogy lehet-e Excelben a számokat betűvel kiírni. Sajnos beépített formula nincsen erre, de Visual Basicben lehet programozni úgynevezett User Defined Function-t.

Én most egy ettől eltérő megoldást szeretnék mutatni, ami nem igényel programozást. A történet még évekkel ezelőtt indult, mikor a LinkedIn-en az Excel Hero csoportban felvetődött, hogy angol nyelven rakjunk össze egy formulát, ami a számokat szöveggé alakítja. Persze én, mint magyar, rögtön elgondolkodtam, hogy magyarul is meg kellene ezt csinálni – hiszen a magyar nyelv egyszerűbb szabály alapján képezi a számokat (gondoljatok csak a tizen- számok képzésére).

Van tehát olyan formula, ami csak az Excel beépített formuláit használva megoldja a szöveges átalakítást – csak eddig nem volt publikálva. Most itt az ideje, hogy megosszam a magyar olvasókkal is!

Innen letölthetitek a fájlt.

Számszöveg

 

A formulában két elnevezett konstanst használunk – ezek azért szükségesek, hogy ne legyen túl hosszú a képlet:

n_1={“0″;”egy”;”kettő”;”három”;”négy”;”öt”;”hat”;”hét”;”nyolc”;”kilenc”}

n_t={“”\”tizen”\”huszon”\”harminc”\”negyven”\”ötven”\”hatvan”\”hetven”\”nyolcvan”\”kilencven”}

Ha a képletet másik fájlban szeretnéd használni, csak át kell másolnod a cellát a szokásos módon, (másolás-beillesztés, Ctrl+C, Ctrl+V) és ezek az elnevezett konstansok automatikusan át fognak kerülni a fájlodba is. A Névkezelőben (Ctrl+F3) tudod ellenőrizni.

 

Ez a bejegyzés az angol blogunk cikke alapján készült, ahol megtaláljátok az angol és olasz nyelvű formulát is, valamint ott gyűjtjük a további nyelveket.


Kérdezz tőlünk Excel segítő csoportunkban vagy kövesd az Adatkertészetet a Facebookon!


13 comments

Skip to comment form

  1. Kedves Kris!

    A segítségedet szeretném kérni abban, hogy a cikkben bemutatott módszer esetében hogy lehet * karaktert beilleszteni a kiírt szöveg elejére és végére? Csekk kitöltéshez kéne ez a plusz karakter.

    Válaszodat előre is köszönöm.

    Horváth Gergely

    • Avatar
    • ttdani on 2015-07-22 at 16:31
    • Válasz

    2003-ban nem működik. A fájl megnyílik, abban jó, amint átmásolom a képletet, nem megy.

  2. Kedves Kris!

    Időközben megoldottam. Köszönöm a fáradozásodat.

    Tisztelettel:

    Horváth Gergely

    1. Szia Gergely!
      Elnézést, hogy csak most válaszolok – nyaralni voltunk. 🙂
      Örülök, hogy sikerült megoldanod!
      Üdv:
      Kris

    • Avatar
    • Virág Imre on 2015-08-31 at 19:16
    • Válasz

    Nagyon jó a megoldás, mert a belső képleteket használja. Viszont sajnos nem teljesen fele meg a magyar helyesírás szabályainak. Lásd 100 azaz száz, nem egyszáz, vagy a 200 azaz kétszáz és nem kettőszáz. De gondolom ez is megoldható lenne.
    Én inkább készítettem egy vba class modult aminek a hátránya persze, hogy csak az adott munkafüzetben fut (vagy az adott gépen megnyitott munkafüzetben).
    Így nézne ki:

    Function szoveggel(valtozo As Double)

    ‘foglalások, tisztázások
    Dim helyiertek(100)
    szam = valtozo
    hossz = Len(szam)
    szoveg = “”
    szovegteljes = “”
    szamneve = “”

    ‘szám feltagolása
    If Fix(szam) = szam Then
    For i = hossz To 1 Step -1
    helyiertek(i) = Val(Mid(szam, hossz – (i – 1), 1))
    Next i
    Else
    szoveggel = “#NEM EGÉSZ!”
    Exit Function
    End If

    ‘nulla külön kezelve
    If szam = 0 Then szoveggel = “nulla”: Exit Function

    ‘átalakítás
    For i = hossz To 1 Step -1
    Select Case i
    Case 24, 21, 18, 15, 12, 9, 6, 3
    GoSub Szazasok
    Case 23, 20, 17, 14, 11, 8, 5, 2
    GoSub Tizesek
    Case 22, 19, 16, 13, 10, 7, 4, 1
    GoSub Egyesek
    End Select
    GoSub Elnevezes
    szovegteljes = szovegteljes & szoveg & szamneve
    szoveg = “”
    szamneve = “”
    Next i

    ‘eredmény visszaadása
    szoveggel = szovegteljes

    Exit Function

    ‘Választók
    Szazasok:
    Select Case helyiertek(i)
    Case 1
    szoveg = “száz”
    Case 2
    szoveg = “kétszáz”
    Case 3
    szoveg = “háromszáz”
    Case 4
    szoveg = “négyszáz”
    Case 5
    szoveg = “ötszáz”
    Case 6
    szoveg = “hatszáz”
    Case 7
    szoveg = “hétszáz”
    Case 8
    szoveg = “nyolcszáz”
    Case 9
    szoveg = “kilencszáz”
    End Select
    Return

    Tizesek:
    Select Case helyiertek(i)
    Case 1
    If helyiertek(i – 1) = 0 Then szoveg = “tíz” Else szoveg = “tizen”
    Case 2
    If helyiertek(i – 1) = 0 Then szoveg = “húsz” Else szoveg = “huszon”
    Case 3
    szoveg = “harminc”
    Case 4
    szoveg = “negyven”
    Case 5
    szoveg = “ötven”
    Case 6
    szoveg = “hatvan”
    Case 7
    szoveg = “hetven”
    Case 8
    szoveg = “nyolcvan”
    Case 9
    szoveg = “kilencven”
    End Select
    Return

    Egyesek:
    Select Case helyiertek(i)
    Case 1
    If i = 1 Then szoveg = “egy”
    If i = 4 And helyiertek(5) “” Then szoveg = “egy”
    If i = 4 And helyiertek(5) = “” Then szoveg = “”
    Case 2
    If i = 1 Then szoveg = “kettő” Else szoveg = “két”
    Case 3
    szoveg = “három”
    Case 4
    szoveg = “négy”
    Case 5
    szoveg = “öt”
    Case 6
    szoveg = “hat”
    Case 7
    szoveg = “hét”
    Case 8
    szoveg = “nyolc”
    Case 9
    szoveg = “kilenc”
    End Select
    Return

    Elnevezes:
    Select Case i
    Case 22
    szamneve = “trilliárd-”
    Case 19
    szamneve = “trillió-”
    Case 16
    szamneve = “billiárd-”
    Case 13
    szamneve = “billió-”
    Case 10
    szamneve = “milliárd-”
    Case 7
    szamneve = “millió-”
    Case 4
    If szam <= 2000 Then szamneve = "ezer" Else szamneve = "ezer-"
    End Select
    Return

    End Function

    Használata a munkafüzetben mint általában a képletek cellába beírva =szoveggel(cellehivatkozás)

      • Avatar
      • Szilágyi Árpád on 2016-02-06 at 19:54
      • Válasz

      Szia! Köszönöm a megoldásod! Közzétettem a kissé javított változatot itt:
      http://stackoverflow.com/questions/35245017/how-to-convert-a-number-into-hungarian-text-in-excel-using-a-vba-function
      Üdv, Árpád

    • Avatar
    • Szilágyi Árpád on 2016-02-06 at 13:01
    • Válasz

    Köszönöm!

    • Avatar
    • Szilágyi Árpád on 2016-02-06 at 19:16
    • Válasz

    Function num2txthu(cellvalue As Double)
    ‘——————————————————————————
    ‘VBA-function, ami Excel-ben használható.
    ‘Egy cella tartalmát (0 és 1 milliárd közötti egész számot) konvertálja szöveggé.

    ‘Virág Imre megoldása alapján, köszönettel! Kissé módosítva, tesztelve.
    ‘(http://www.adatkerteszet.hu/2014/08/osszeg-betuvel-szamok-atirasa-szovegge-keplettel/)

    ‘hulloalma@gmail.com

    ‘Példák
    ‘ A1 A2 =num2txthu(A1)
    ‘ ———— —————————————————————-
    ‘ 1 egy
    ‘ 10 tíz
    ‘ 19 tizenkilenc
    ‘ 20 húsz
    ‘ 25 huszonöt
    ‘ 1 000 ezer
    ‘ 1 999 ezerkilencszázkilencvenkilenc
    ‘ 2 001 kétezer-egy
    ‘ 3 016 háromezer-tizenhat
    ‘ 47 563 negyvenhétezer-ötszázhatvanhárom
    ‘ 100 000 százezer
    ‘ 1 100 000 egymillió-egyszázezer
    ‘ 7 001 530 hétmillió-egyezer-ötszázharminc
    ‘ 7 491 530 hétmillió-négyszázkilencvenegyezer-ötszázharminc
    ‘ 7 490 530 hétmillió-négyszázkilencvenezer-ötszázharminc
    ‘ 10 000 000 tízmillió
    ‘ 999 999 999 kilencszázkilencvenkilencmillió-kilencszázkilencvenkilencezer-kilencszázkilencvenkilenc
    ‘1 000 000 000 egymilliárd
    ‘ ———— —————————————————————-
    ‘ A1 A2 =CONCATENATE(“*”,UPPER(MID(num2txthu(A1),1,1)),MID(num2txthu(A1),2,200),”*”)
    ‘ ———— —————————————————————-
    ‘ 4672 *Négyezer-hatszázhetvenkettõ*
    ‘ 4911 *Négyezer-kilencszáztizenegy*
    ‘——————————————————————————

    Dim v_arr(100)
    v_number = cellvalue
    v_length = Len(v_number)
    v_number_name = “”
    v_group_name = “”
    v_num2txthu = “”

    ‘It will then reevaluate whenever the workbook changes (if your calculation is set to automatic).
    Application.Volatile

    ‘szám feltagolása
    If v_number > 1000000000 Then
    num2txthu = “#Túl nagy szám! (jó: pozitív egész szám 0 és 1milliárd között)”
    Exit Function
    End If
    If Fix(v_number) = v_number Then
    For pos = v_length To 1 Step -1
    v_arr(pos) = Val(Mid(v_number, v_length – (pos – 1), 1))
    Next pos
    Else
    num2txthu = “#Nem egész szám! (jó: pozitív egész szám 0 és 1milliárd között)”
    Exit Function
    End If

    ‘nulla külön kezelve
    If v_number = 0 Then num2txthu = “nulla”: Exit Function

    ‘ pos – position in v_arr
    ‘ felosztás a csoportoknak (egyesek, tízesek, százasok) megfelelöen
    For pos = v_length To 1 Step -1
    Select Case pos
    Case 24, 21, 18, 15, 12, 9, 6, 3
    GoSub l_group_100s
    Case 23, 20, 17, 14, 11, 8, 5, 2
    GoSub l_group_10s
    Case 22, 19, 16, 13, 10, 7, 4, 1
    GoSub l_group_1s
    End Select
    GoSub l_group_names

    ‘az eredmény kiegészítése a csoporton belül létrehozott szöveggel
    v_num2txthu = v_num2txthu & v_number_name & v_group_name

    ‘a csoporton belüli szöveg inicializálása
    v_number_name = “”
    v_group_name = “”
    Next pos

    ‘eredmény visszaadása
    num2txthu = v_num2txthu

    Exit Function

    ‘——————————————————————————
    l_group_100s:
    Select Case v_arr(pos)
    Case 1
    ‘száz, százezer vs. ezeregyszáz, egymillióegyszázezer
    If (v_number >= 100 And v_number = 100000 And v_number < 200000) Then
    v_number_name = "száz"
    Else
    v_number_name = "egyszáz"
    End If
    Case 2
    v_number_name = "kétszáz"
    Case 3
    v_number_name = "háromszáz"
    Case 4
    v_number_name = "négyszáz"
    Case 5
    v_number_name = "ötszáz"
    Case 6
    v_number_name = "hatszáz"
    Case 7
    v_number_name = "hétszáz"
    Case 8
    v_number_name = "nyolcszáz"
    Case 9
    v_number_name = "kilencszáz"
    End Select
    Return

    '——————————————————————————
    l_group_10s:
    Select Case v_arr(pos)
    Case 1
    If v_arr(pos – 1) = 0 Then v_number_name = "tíz" Else v_number_name = "tizen"
    Case 2
    If v_arr(pos – 1) = 0 Then v_number_name = "húsz" Else v_number_name = "huszon"
    Case 3
    v_number_name = "harminc"
    Case 4
    v_number_name = "negyven"
    Case 5
    v_number_name = "ötven"
    Case 6
    v_number_name = "hatvan"
    Case 7
    v_number_name = "hetven"
    Case 8
    v_number_name = "nyolcvan"
    Case 9
    v_number_name = "kilencven"
    End Select
    Return

    '——————————————————————————
    l_group_1s:
    Select Case v_arr(pos)
    Case 1
    If pos = 1 Then v_number_name = "egy"
    If pos = 3 Then 'száz vs. ezeregyszáz
    If v_number < 200 Then
    v_number_name = ""
    Else
    v_number_name = "egy"
    End If
    End If
    If pos = 4 Then 'ezer vs. tizenegyezer
    If v_number < 2000 Then
    v_number_name = ""
    Else
    v_number_name = "egy"
    End If
    End If
    If pos = 6 Then 'százezer vs. egymillióegyszázezer
    If v_number 0 Then
    v_group_name = “milliárd-”
    Else
    v_group_name = “milliárd”
    End If
    Case 7
    If Modulo(v_number, 1000000) > 0 Then
    v_group_name = “millió-”
    Else
    If v_number >= 1000000000 And Modulo(v_number, 1000000) = 0 Then
    v_group_name = “”
    Else
    v_group_name = “millió”
    End If
    End If
    Case 4
    If v_number 0 Then
    v_group_name = “ezer-”
    Else
    v_group_name = “ezer”
    End If
    End If
    End If
    End Select
    Return

    End Function

    Function Modulo(a, b)
    ‘Modulo = a – (b * (a \ b))
    Modulo = a Mod b
    End Function

    • Avatar
    • Bottlik Gábor on 2016-02-09 at 10:27
    • Válasz

    Kedves Kris, kedves Urak!

    Mellékelem a saját verziómat. 🙂 A kettőszáz vs kétszáz kérdéskörrel nem foglalkozik ez a verzió, bár érzésem szerint ez a tejföl vs tejfel kategória. 🙂 Ja, és ez a verzió “csak” +/- százmillió között működik, illetve a számot egészre kerekíti. Persze kis bütyköléssel szélesíthető az intervallum.

    Function SZAMBETU(ertek As Double) As String
    Dim egyes
    Dim tizes
    Dim seged
    Dim szam As Integer
    Dim t As Integer
    egyes = Array(“”, “egy”, “kettő”, “három”, “négy”, “öt”, “hat”, “hét”, “nyolc”, “kilenc”)
    tizes = Array(“”, “tíz”, “húsz”, “harminc”, “negyven”, “ötven”, “hatvan”, “hetven”, “nyolcvan”, “kilencven”, “tizen”, “huszon”)
    seged = Array(“”, “száz”, “ezer-“, “millió-“)
    If ertek = 0 Then
    SZAMBETU = “nulla”
    Exit Function
    End If
    If ertek > 999999999 Or ertek < -999999999 Then
    SZAMBETU = Error$(6)
    Exit Function
    End If
    If ertek < 0 Then
    SZAMBETU = "mínusz "
    ertek = ertek * (-1)
    End If
    For t = 8 To 0 Step -1
    If t = 0 Or t = 2 Or t = 3 Or t = 5 Or t = 6 Or t = 8 Then
    SZAMBETU = SZAMBETU & egyes(Int((ertek Mod 10 ^ (t + 1)) / (10 ^ t)))
    Else
    If Int((ertek Mod 10 ^ (t)) / (10 ^ (t – 1))) 0 And (Int((ertek Mod 10 ^ (t + 1)) / (10 ^ t)) = 2 Or Int((ertek Mod 10 ^ (t + 1)) / (10 ^ t)) = 1) Then
    SZAMBETU = SZAMBETU & tizes(Int((ertek Mod 10 ^ (t + 1)) / 10 ^ t) + 9)
    Else
    SZAMBETU = SZAMBETU & tizes(Int((ertek Mod 10 ^ (t + 1)) / 10 ^ t))
    End If
    End If
    Select Case t
    Case 8
    If ertek >= 100000000 Then SZAMBETU = SZAMBETU & seged(1)
    Case 6
    If ertek >= 1000000 Then SZAMBETU = SZAMBETU & seged(3)
    Case 5
    If ertek >= 100000 And Int((ertek Mod 10 ^ (t + 1)) / (10 ^ (t))) 0 Then SZAMBETU = SZAMBETU & seged(1)
    Case 3
    If Int((ertek Mod (10 ^ (t + 1))) / (10 ^ t)) 0 Or Int((ertek Mod (10 ^ (t + 2))) / (10 ^ (t + 1))) 0 Or Int((ertek Mod (10 ^ (t + 3))) / (10 ^ (t + 2))) 0 Then SZAMBETU = SZAMBETU & seged(2)
    Case 2
    If ertek >= 100 And Int((ertek Mod 10 ^ (t + 1)) / (10 ^ (t))) 0 Then SZAMBETU = SZAMBETU & seged(1)
    End Select
    Next t
    If Right(SZAMBETU, 1) = “-” Then SZAMBETU = Left(SZAMBETU, Len(SZAMBETU) – 1)
    End Function

    • Avatar
    • Horváth Imre József on 2016-11-24 at 09:35
    • Válasz

    Sziasztok!

    Én mindenki munkáját szeretném megköszönni! Rengeteget segítettetek!

    Nagyon Köszönöm! Imre

    Esetleg, ha valaki unatkozna:

    A postai szabályzat szerint ötágú csillagokat kötelező használni a fizetendő részek számos, és a szöveges megjelenítése előtt is.

    A számoknál az Alt Gr/* segített, de a szövegnél változatlanul hatágú csillag jelenik meg a szöveg előtt.

    Esetleg valakinek ötlet?

  3. Szia Imre!

    > A számoknál az Alt Gr/* segített, de a szövegnél változatlanul hatágú csillag jelenik meg a szöveg előtt.
    Ezt nem értem – aszámok is “szöveggel” vannak leírva, nincs különbség.

    Olyan lehet, hogy bizonyos betűtípusokban a csillag az ötágú, másikban meg hatágú. Betűtípus nincs eőírva?

    Egyébként, Te látod annak esélyét, hogy posta ilyen alapon visszadobja a küldeményt?…
    Szerintem nem (kellene hogy) foglalkoznak ezzel, nagyon béna OCR-nek kell lennie annak, ami a hatágú csillagot nem ismeri fel ugyanúgy…

    (Már én is megküzdöttem azzal, hogy a “R” betű az tényleg a posta által előírt Times New Roman legyen még Linuxon készült levélen is, de szerintem csak a megrendelő bank vette túl komolyan a dolgot. Nem hinném, hogy ez konkrétan nemzetközi előírás lenne… 😉 )

    Üdv,
    Gábor

    • Avatar
    • Kiss Mónika on 2018-06-04 at 08:01
    • Válasz

    Köszönöm ez nagy segítség volt!

    • Avatar
    • wendykee on 2020-01-07 at 17:45
    • Válasz

    El sem tudom mondani mennyire nagyon Hálás vagyok! Köszönöm, Köszönöm, Köszönöm! Küldök sok virtuális boldogságot!

Vélemény, hozzászólás?

Your email address will not be published.

css.php