エクセルのVBAを使って、ひらがな（あいうえお）をローマ字に変換する。
条件
- ヘッダー部分は無視する。
- A列は「ID」、B列は「文字」、C列は「ローマ字読み」
- 文字に入るのは「あ」「い」「う」「え」「お」の5文字の組み合わせのみ。
- 「あ」=「a」、「い」=「i」、「う」=「u」、「え」=「e」、「お」=「o」に変換したい。
- 文字数の制限はなし。
- B列全体を参照するが、空欄の場合は無視してストップする。
- シート名を指定するように。（今回は「変換」というシート名にした。）
表の例
右から、A列 B列 C列とする。
| ID | 文字 | ローマ字読み |
| あ | ||
| い | ||
| う | ||
| え | ||
| お | ||
| ああ | ||
| あい | ||
| あう | ||
| あえ | ||
| あお |
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