変換 - 劇団コピュラ

エクセルの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 &lt;&gt; "" s = ws.Cells(i, "B").Value result = "" ' 一文字ずつ変換 For j = 1 To Len(s) c = Mid(s, j, 1) Select Case c Case "あ": result = result &amp; "a" Case "い": result = result &amp; "i" Case "う": result = result &amp; "u" Case "え": result = result &amp; "e" Case "お": result = result &amp; "o" Case Else: result = result &amp; 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 &amp; "a" Case "い": result = result &amp; "i" Case "う": result = result &amp; "u" Case "え": result = result &amp; "e" Case "お": result = result &amp; "o" Case Else: result = result &amp; 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 &lt;&gt; "" s = ws.Cells(i, "B").Value result = "" j = 1 Do While j &lt;= Len(s) ch1 = Mid(s, j, 1) ' 小さい「っ」の場合 If ch1 = "っ" And j &lt; Len(s) Then ch2 = Mid(s, j + 1, 2) romajiNext = Romaji2(ch2) If romajiNext = "" Then romajiNext = GetRomaji(Mid(s, j + 1, 1)) If romajiNext &lt;&gt; "" Then result = result &amp; Left(romajiNext, 1) End If j = j + 1 ch1 = Mid(s, j, 1) End If ' 拗音（2文字）判定 If j &lt; Len(s) Then ch2 = Mid(s, j, 2) romajiNext = Romaji2(ch2) If romajiNext &lt;&gt; "" Then result = result &amp; romajiNext j = j + 2 GoTo NextChar End If End If ' 1文字変換 result = result &amp; 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

変換

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

日本語URLをUTF-8に変換して短縮URLにする