Gyakran 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.
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
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
2003-ban nem működik. A fájl megnyílik, abban jó, amint átmásolom a képletet, nem megy.
Kedves Kris!
Időközben megoldottam. Köszönöm a fáradozásodat.
Tisztelettel:
Horváth Gergely
Author
Szia Gergely!
Elnézést, hogy csak most válaszolok – nyaralni voltunk. 🙂
Örülök, hogy sikerült megoldanod!
Üdv:
Kris
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)
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
Köszönöm!
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
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
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?
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
Köszönöm ez nagy segítség volt!
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!