エクセルでひらがな（あいうえお）をローマ字読みに変換する

エクセルのVBAを使って、ひらがな（あいうえお）をローマ字に変換する。

条件
表の例
VBAコード例
使用方法
結果
応用
応用変更

条件

ヘッダー部分は無視する。
A列は「ID」、B列は「文字」、C列は「ローマ字読み」
文字に入るのは「あ」「い」「う」「え」「お」の5文字の組み合わせのみ。
「あ」=「a」、「い」=「i」、「う」=「u」、「え」=「e」、「お」=「o」に変換したい。
文字数の制限はなし。
B列全体を参照するが、空欄の場合は無視してストップする。
シート名を指定するように。（今回は「変換」というシート名にした。）

表の例

右から、A列　B列　C列とする。

ID	文字	ローマ字読み
	あ	a
	い	i
	う	u
	え	e
	お	o
	ああ	aa
	あい	ai
	あう	au
	あえ	ae
	あお	ao

VBAコード例

Option Explicit

Sub HiraganaToRomaji()
    Dim ws As Worksheet
    Dim i As Long
    Dim s As String
    Dim result As String
    Dim j As Long
    Dim c As String
    
    ' シート名を指定
    Set ws = ThisWorkbook.Sheets("変換")
    
    ' 2行目以降
    i = 2
    Do While ws.Cells(i, "B").Value <> ""
        s = ws.Cells(i, "B").Value
        result = ""
        
        ' 一文字ずつ変換
        For j = 1 To Len(s)
            c = Mid(s, j, 1)
            Select Case c
                Case "あ": result = result & "a"
                Case "い": result = result & "i"
                Case "う": result = result & "u"
                Case "え": result = result & "e"
                Case "お": result = result & "o"
                Case Else: result = result & c  ' 想定外はそのまま
            End Select
        Next j
        
        ' C列に出力
        ws.Cells(i, "C").Value = result
        
        i = i + 1
    Loop
End Sub

シート名は、

' シート名を指定
    Set ws = ThisWorkbook.Sheets("変換")

この部分の「"変換"」を変えればOK、sheet1というシート名だったら、

' シート名を指定
    Set ws = ThisWorkbook.Sheets("sheet1")

に変える。

使用方法

ExcelでAlt + F11を押してVBAエディタを開く
「挿入」→「標準モジュール」を選択
上記コードを貼り付ける
VBAエディタを閉じてExcel画面に戻る
Alt + F8でマクロ実行ダイアログから「HiraganaToRomaji」を選んで実行
B列のひらがなをローマ字に変換し、C列に出力します

結果

ID	文字	ローマ字読み
	あ	a
	い	i
	う	u
	え	e
	お	o
	ああ	aa
	あい	ai
	あう	au
	あえ	ae
	あお	ao

応用

ひらがな50音も、変換を指定すれば、同様の形で変換が可能になる。

                Case "あ": result = result & "a"
                Case "い": result = result & "i"
                Case "う": result = result & "u"
                Case "え": result = result & "e"
                Case "お": result = result & "o"
                Case Else: result = result & c  ' 想定外はそのまま

この部分に付け足す。

応用変更

「じゃ」が入ったりする２文字、「ちぇっく」のちいさい「っ」が入る場合は別のソースになる。


Option Explicit

Sub HiraganaToRomaji()
    Dim ws As Worksheet
    Dim i As Long, j As Long
    Dim s As String, result As String
    Dim ch1 As String, ch2 As String
    Dim romajiNext As String
    
    Set ws = ThisWorkbook.Sheets("変換")
    
    i = 2
    Do While ws.Cells(i, "B").Value <> ""
        s = ws.Cells(i, "B").Value
        result = ""
        j = 1
        
        Do While j <= Len(s)
            ch1 = Mid(s, j, 1)
            
            ' 小さい「っ」の場合
            If ch1 = "っ" And j < Len(s) Then
                ch2 = Mid(s, j + 1, 2)
                romajiNext = Romaji2(ch2)
                If romajiNext = "" Then romajiNext = GetRomaji(Mid(s, j + 1, 1))
                If romajiNext <> "" Then
                    result = result & Left(romajiNext, 1)
                End If
                j = j + 1
                ch1 = Mid(s, j, 1)
            End If
            
            ' 拗音（2文字）判定
            If j < Len(s) Then
                ch2 = Mid(s, j, 2)
                romajiNext = Romaji2(ch2)
                If romajiNext <> "" Then
                    result = result & romajiNext
                    j = j + 2
                    GoTo NextChar
                End If
            End If
            
            ' 1文字変換
            result = result & GetRomaji(ch1)
            j = j + 1
            
NextChar:
        Loop
        
        ws.Cells(i, "C").Value = result
        i = i + 1
    Loop
End Sub

' ひらがな1文字 → ローマ字
Function GetRomaji(ch As String) As String
    Select Case ch
        Case "あ": GetRomaji = "a"
        Case "い": GetRomaji = "i"
        Case "う": GetRomaji = "u"
        Case "え": GetRomaji = "e"
        Case "お": GetRomaji = "o"
		Case "か": GetRomaji = "ka"
		Case "き": GetRomaji = "ki"
		Case "く": GetRomaji = "ku"
		Case "け": GetRomaji = "ke"
		Case "こ": GetRomaji = "ko"
		Case "さ": GetRomaji = "sa"
		Case "し": GetRomaji = "si"
		Case "す": GetRomaji = "su"
		Case "せ": GetRomaji = "se"
		Case "そ": GetRomaji = "so"
		Case "た": GetRomaji = "ta"
		Case "ち": GetRomaji = "chi"
		Case "つ": GetRomaji = "tsu"
		Case "て": GetRomaji = "te"
		Case "と": GetRomaji = "to"
		Case "な": GetRomaji = "na"
		Case "に": GetRomaji = "ni"
		Case "ぬ": GetRomaji = "nu"
		Case "ね": GetRomaji = "ne"
		Case "の": GetRomaji = "no"
		Case "は": GetRomaji = "ha"
		Case "ひ": GetRomaji = "hi"
		Case "ふ": GetRomaji = "fu"
		Case "へ": GetRomaji = "he"
		Case "ほ": GetRomaji = "ho"
		Case "ま": GetRomaji = "ma"
		Case "み": GetRomaji = "mi"
		Case "む": GetRomaji = "mu"
		Case "め": GetRomaji = "me"
		Case "も": GetRomaji = "mo"
		Case "や": GetRomaji = "ya"
		Case "い": GetRomaji = "yi"
		Case "ゆ": GetRomaji = "yu"
		Case "え": GetRomaji = "ye"
		Case "よ": GetRomaji = "yo"
		Case "ら": GetRomaji = "ra"
		Case "り": GetRomaji = "ri"
		Case "る": GetRomaji = "ru"
		Case "れ": GetRomaji = "re"
		Case "ろ": GetRomaji = "ro"
		Case "わ": GetRomaji = "wa"
		Case "ゐ": GetRomaji = "wyi"
		Case "ゑ": GetRomaji = "wye"
		Case "を": GetRomaji = "wo"
		Case "ん": GetRomaji = "n"
		Case "が": GetRomaji = "ga"
		Case "ぎ": GetRomaji = "gi"
		Case "ぐ": GetRomaji = "gu"
		Case "げ": GetRomaji = "ge"
		Case "ご": GetRomaji = "go"
		Case "ざ": GetRomaji = "za"
		Case "じ": GetRomaji = "zi"
		Case "ず": GetRomaji = "zu"
		Case "ぜ": GetRomaji = "ze"
		Case "ぞ": GetRomaji = "zo"
		Case "だ": GetRomaji = "da"
		Case "ぢ": GetRomaji = "di"
		Case "づ": GetRomaji = "du"
		Case "で": GetRomaji = "de"
		Case "ど": GetRomaji = "do"
		Case "ば": GetRomaji = "ba"
		Case "び": GetRomaji = "bi"
		Case "ぶ": GetRomaji = "bu"
		Case "べ": GetRomaji = "be"
		Case "ぼ": GetRomaji = "bo"
		Case "ぱ": GetRomaji = "pa"
		Case "ぴ": GetRomaji = "pi"
		Case "ぷ": GetRomaji = "pu"
		Case "ぺ": GetRomaji = "pe"
		Case "ぽ": GetRomaji = "po"
        Case Else: GetRomaji = "?"
    End Select
End Function

' 拗音（2文字） → ローマ字
Function Romaji2(ch2 As String) As String
    Select Case ch2
		Case "きゃ": Romaji2 = "kya"
		Case "きぃ": Romaji2 = "kyi"
		Case "きゅ": Romaji2 = "kyu"
		Case "きぇ": Romaji2 = "kye"
		Case "きょ": Romaji2 = "kyo"
		Case "しゃ": Romaji2 = "sha"
		Case "しぃ": Romaji2 = "sixi"
		Case "しゅ": Romaji2 = "shu"
		Case "しぇ": Romaji2 = "she"
		Case "しょ": Romaji2 = "sho"
		Case "ちゃ": Romaji2 = "cha"
		Case "ちぃ": Romaji2 = "chixi"
		Case "ちゅ": Romaji2 = "chu"
		Case "ちぇ": Romaji2 = "che"
		Case "ちょ": Romaji2 = "cho"
		Case "にゃ": Romaji2 = "nya"
		Case "にぃ": Romaji2 = "nyi"
		Case "にゅ": Romaji2 = "nyu"
		Case "にぇ": Romaji2 = "nye"
		Case "にょ": Romaji2 = "nyo"
		Case "ひゃ": Romaji2 = "hya"
		Case "ひぃ": Romaji2 = "hyi"
		Case "ひゅ": Romaji2 = "hyu"
		Case "ひぇ": Romaji2 = "hye"
		Case "ひょ": Romaji2 = "hyo"
		Case "ふぁ": Romaji2 = "fa"
		Case "ふぃ": Romaji2 = "fi"
		Case "ふゅ": Romaji2 = "fyu"
		Case "ふぇ": Romaji2 = "fe"
		Case "ふょ": Romaji2 = "fyo"
		Case "みゃ": Romaji2 = "mya"
		Case "みぃ": Romaji2 = "myi"
		Case "みゅ": Romaji2 = "myu"
		Case "みぇ": Romaji2 = "mye"
		Case "みょ": Romaji2 = "myo"
		Case "りゃ": Romaji2 = "rya"
		Case "りぃ": Romaji2 = "ryi"
		Case "りゅ": Romaji2 = "ryu"
		Case "りぇ": Romaji2 = "rye"
		Case "りょ": Romaji2 = "ryo"
		Case "ぎゃ": Romaji2 = "gya"
		Case "ぎぃ": Romaji2 = "gyi"
		Case "ぎゅ": Romaji2 = "gyu"
		Case "ぎぇ": Romaji2 = "gye"
		Case "ぎょ": Romaji2 = "gyo"
		Case "じゃ": Romaji2 = "ja"
		Case "じぃ": Romaji2 = "jyi"
		Case "じゅ": Romaji2 = "ju"
		Case "じぇ": Romaji2 = "je"
		Case "じょ": Romaji2 = "jo"
		Case "ぢゃ": Romaji2 = "dya"
		Case "ぢぃ": Romaji2 = "dyi"
		Case "ぢゅ": Romaji2 = "dyu"
		Case "ぢぇ": Romaji2 = "dye"
		Case "ぢょ": Romaji2 = "dyo"
		Case "びゃ": Romaji2 = "bya"
		Case "びぃ": Romaji2 = "byi"
		Case "びゅ": Romaji2 = "byu"
		Case "びぇ": Romaji2 = "bye"
		Case "びょ": Romaji2 = "byo"
		Case "ぴゃ": Romaji2 = "pya"
		Case "ぴぃ": Romaji2 = "pyi"
		Case "ぴゅ": Romaji2 = "pyu"
		Case "ぴぇ": Romaji2 = "pye"
		Case "ぴょ": Romaji2 = "pyo"
		Case "ゔぁ": Romaji2 = "va"
		Case "ゔぃ": Romaji2 = "vi"
		Case "ゔ": Romaji2 = "vu"
		Case "ゔぇ": Romaji2 = "ve"
		Case "ゔぉ": Romaji2 = "vo"
        Case Else: Romaji2 = ""
    End Select
End Function