皆さま引き続きコロナには気をつけてお過ごしください！

【Excel】入力データをローマ字変換する方法（②ひらがな→ローマ字変換）

ローマ字変換 MS Excel
コードの一部に誤りがあり、スパゲッティの「ティ」が正しく変換できない状況でした。
修正してありますので、下記コードをご利用ください。
にゃこ
ごめんなさい

 

前回は、Excel関数を使って、入力した漢字交じり文をすべてカタカナ／ひらがなに変える方法をご紹介しました。

今回は、VBAを使って、ひらがなの文字をローマ字に変換する方法をご紹介します。

例えば、

「漢字からひらがなへの変換」
↓
「kannjikarahiraganahenohennkann」

にします。

 

contents
  1. ローマ字への変換方法
    1. 準備
    2. 開発
  2. まとめ
スポンサーリンク

ローマ字への変換方法

では、前回使ったExcelファイルを使って、変換させたいと思います。

 

前回のファイルでは、 A1セルに漢字交じり文「漢字からひらがなへの変換」が入り、

A2セルに漢字交じり文をひらがなに変えたもの「かんじからひらがなへのへんかん」の文字が入っていると思います。

まだ実施されていない方は、ぜひ以下リンク先をご覧の上、やってみてくださいね。

【Excel】入力データをローマ字変換する方法（①漢字→ひらがな変換）
皆さんは、何を使ってタイピング練習をしましたか？ 巷には、タイピング練習ソフトや、オンラインのタイピング練習サイトなどがたくさんあります。 それらを使うことで、楽しく練習することができます。 ...
nekopress.castelodelego.org
2021.10.25

 

では、A2セルのひらがな文をローマ字に変えた文をA3セルに入るよう、VBAを書いてみたいと思います。

 

準備

まずは、VBAを使えるようにするための準備をしましょう。

 

開発タブが表示されていない方は、次のようにして、開発タブを表示します。

開発タブ表示方法
  • Step1
    ファイルタブをクリックする。

    ファイルクリック

  • Step2
    オプションをクリックする。

    オプション

  • Step3
    リボンのユーザー設定をクリックする。
  • Step4
    右側のリボンのユーザー設定内にある「開発」のチェックをONにし、「OK」をクリックする。

    リボンのユーザー設定→開発

  • Step5
    タブの右側に「開発」タブが表示される。

    開発タブ

 

開発

変換方法
  • Step1
    Excelの開発タブをクリックする。
    開発タブ
  • Step2
    左側の「Visual Basic」をクリックする。
  • Step3
    左側のナビゲーションウィンドウから、
    VBAを入れたいファイルの中のSheet1(Sheet1)をダブルクリックし、編集画面を表示させる。
    sheet1選択
  • Step4
    次のコードを貼り付ける。
    コピー
    コピーしました

     
Private Sub ローマ字変換_Click()
	'変換したいひらがな/カタカナが入っているセルをコピペする
	'<注意>
	' 範囲を指定し、一度に変換させる場合は、
	' Rangeの引数に"B6:B25"のように"左上の最初のセル:右下の最後のセル"で指定する。

	Worksheets("Sheet1").Range("A2").Copy
	'次のように値を貼り付ける
	'　値のみ貼り付ける（Paste:=xlPasteValues）
	'　計算をしない（Operation:=xlNone）
	'　空白セルを無視しない（SkipBlanks:=False）
	'　行列入れ替えをしない（Transpose:=False）
	Worksheets("Sheet1").Range("A3").PasteSpecial _
                                 Paste:=xlPasteValues, _
                                 Operation:=xlNone, _
                                 SkipBlanks:=False, _
                                 Transpose:=False
	

	'コピーしたセルを選択する

	Worksheets("Sheet1").Range("A3").Select


	'選択した範囲のひらがな（拗音）をローマ字に変換する
      	Selection.Replace What:="ぃ", Replacement:="li"
      	Selection.Replace What:="ふぁ", Replacement:="fa"
        Selection.Replace What:="りゃ", Replacement:="rya"
        Selection.Replace What:="みゃ", Replacement:="mya"
        Selection.Replace What:="ひゃ", Replacement:="hya"
        Selection.Replace What:="にゃ", Replacement:="nya"	
        Selection.Replace What:="ちゃ", Replacement:="cha"	'cyaでもOK
        Selection.Replace What:="しゃ", Replacement:="sha"	'syaでもOK
        Selection.Replace What:="きゃ", Replacement:="kya"
        Selection.Replace What:="ぴゃ", Replacement:="pya"
        Selection.Replace What:="びゃ", Replacement:="bya"
        Selection.Replace What:="ぢゃ", Replacement:="dya"
        Selection.Replace What:="じゃ", Replacement:="ja"
        Selection.Replace What:="ぎゃ", Replacement:="gya"
        Selection.Replace What:="ふぃ", Replacement:="fi"
        Selection.Replace What:="ぴゅ", Replacement:="pyu"
        Selection.Replace What:="びゅ", Replacement:="byu"
        Selection.Replace What:="ぢゅ", Replacement:="dyu"
        Selection.Replace What:="じゅ", Replacement:="ju"
        Selection.Replace What:="ぎゅ", Replacement:="gyu"
        Selection.Replace What:="ふぇ", Replacement:="fe"
        Selection.Replace What:="りゅ", Replacement:="ryu"
        Selection.Replace What:="みゅ", Replacement:="myu"
        Selection.Replace What:="ひゅ", Replacement:="hyu"
        Selection.Replace What:="にゅ", Replacement:="nyu"
        Selection.Replace What:="ちゅ", Replacement:="chu"	'cyuでもOK
        Selection.Replace What:="しゅ", Replacement:="shu"	'syuでもOK
        Selection.Replace What:="きゅ", Replacement:="kyu"
        Selection.Replace What:="ぴょ", Replacement:="pyo"
        Selection.Replace What:="びょ", Replacement:="byo"
        Selection.Replace What:="ぢょ", Replacement:="dyo"
        Selection.Replace What:="じょ", Replacement:="jo"
        Selection.Replace What:="ぎょ", Replacement:="gyo"
        Selection.Replace What:="ふぉ", Replacement:="fo"
        Selection.Replace What:="りょ", Replacement:="ryo"
        Selection.Replace What:="みょ", Replacement:="myo"
        Selection.Replace What:="ひょ", Replacement:="hyo"
        Selection.Replace What:="にょ", Replacement:="nyo"
        Selection.Replace What:="ちょ", Replacement:="cho"	'cyoでもOK
        Selection.Replace What:="しょ", Replacement:="sho"	'syoでもOK
        Selection.Replace What:="きょ", Replacement:="kyo"
        Selection.Replace What:="ちぇ", Replacement:="che"	'cyeでもOK


	'選択した範囲のカタカナ（拗音）をローマ字に変換する

        Selection.Replace What:="ィ", Replacement:="li"
        Selection.Replace What:="ファ", Replacement:="fa"
        Selection.Replace What:="リャ", Replacement:="rya"
        Selection.Replace What:="ミャ", Replacement:="mya"
        Selection.Replace What:="ヒャ", Replacement:="hya"
        Selection.Replace What:="ニャ", Replacement:="nya"
        Selection.Replace What:="チャ", Replacement:="cha"	'cyaでもOK
        Selection.Replace What:="シャ", Replacement:="sha"	'syaでもOK
        Selection.Replace What:="キャ", Replacement:="kya"
        Selection.Replace What:="ピャ", Replacement:="pya"
        Selection.Replace What:="ビャ", Replacement:="bya"
        Selection.Replace What:="ヂャ", Replacement:="dya"
        Selection.Replace What:="ジャ", Replacement:="ja"
        Selection.Replace What:="ギャ", Replacement:="gya"
        Selection.Replace What:="フィ", Replacement:="fi"
        Selection.Replace What:="ピュ", Replacement:="pyu"
        Selection.Replace What:="ビュ", Replacement:="byu"
        Selection.Replace What:="ヂュ", Replacement:="dyu"
        Selection.Replace What:="ジュ", Replacement:="ju"
        Selection.Replace What:="ギュ", Replacement:="gyu"
        Selection.Replace What:="フェ", Replacement:="fe"
        Selection.Replace What:="リュ", Replacement:="ryu"
        Selection.Replace What:="ミュ", Replacement:="myu"
        Selection.Replace What:="ヒュ", Replacement:="hyu"
        Selection.Replace What:="ニュ", Replacement:="nyu"
        Selection.Replace What:="チュ", Replacement:="chu"	'cyuでもOK
        Selection.Replace What:="シュ", Replacement:="shu"	'syuでもOK
        Selection.Replace What:="キュ", Replacement:="kyu"
        Selection.Replace What:="ピョ", Replacement:="pyo"
        Selection.Replace What:="ビョ", Replacement:="byo"
        Selection.Replace What:="ヂョ", Replacement:="dyo"
        Selection.Replace What:="ジョ", Replacement:="jo"
        Selection.Replace What:="ギョ", Replacement:="gyo"
        Selection.Replace What:="フォ", Replacement:="fo"
        Selection.Replace What:="リョ", Replacement:="ryo"
        Selection.Replace What:="ミョ", Replacement:="myo"
        Selection.Replace What:="ヒョ", Replacement:="hyo"
        Selection.Replace What:="ニョ", Replacement:="nyo"
        Selection.Replace What:="チョ", Replacement:="cho"	'cyoでもOK
        Selection.Replace What:="ショ", Replacement:="sho"	'syoでもOK
        Selection.Replace What:="キョ", Replacement:="kyo"
        Selection.Replace What:="チェ", Replacement:="che"	'cyeでもOK


	'選択した範囲のひらがな（拗音以外）をローマ字に変換する
        Selection.Replace What:="ん", Replacement:="nn"
        Selection.Replace What:="わ", Replacement:="wa"
        Selection.Replace What:="ら", Replacement:="ra"
        Selection.Replace What:="や", Replacement:="ya"
        Selection.Replace What:="ま", Replacement:="ma"
        Selection.Replace What:="は", Replacement:="ha"
        Selection.Replace What:="な", Replacement:="na"
        Selection.Replace What:="た", Replacement:="ta"
        Selection.Replace What:="さ", Replacement:="sa"
        Selection.Replace What:="か", Replacement:="ka"
        Selection.Replace What:="あ", Replacement:="a"
        Selection.Replace What:="ぱ", Replacement:="pa"
        Selection.Replace What:="ば", Replacement:="ba"
        Selection.Replace What:="だ", Replacement:="da"
        Selection.Replace What:="ざ", Replacement:="za"
        Selection.Replace What:="が", Replacement:="ga"
        Selection.Replace What:="り", Replacement:="ri"
        Selection.Replace What:="み", Replacement:="mi"
        Selection.Replace What:="ひ", Replacement:="hi"
        Selection.Replace What:="に", Replacement:="ni"
        Selection.Replace What:="ち", Replacement:="chi"	'tiでもOK
        Selection.Replace What:="し", Replacement:="shi"	'siでもOK
        Selection.Replace What:="き", Replacement:="ki"
        Selection.Replace What:="い", Replacement:="i"
        Selection.Replace What:="ぴ", Replacement:="pi"
        Selection.Replace What:="び", Replacement:="bi"
        Selection.Replace What:="ぢ", Replacement:="di"
        Selection.Replace What:="じ", Replacement:="ji"
        Selection.Replace What:="ぎ", Replacement:="gi"
        Selection.Replace What:="る", Replacement:="ru"
        Selection.Replace What:="ゆ", Replacement:="yu"
        Selection.Replace What:="む", Replacement:="mu"
        Selection.Replace What:="ふ", Replacement:="fu"		'huでもOK
        Selection.Replace What:="ぬ", Replacement:="nu"
        Selection.Replace What:="つ", Replacement:="tu"
        Selection.Replace What:="す", Replacement:="su"
        Selection.Replace What:="く", Replacement:="ku"
        Selection.Replace What:="う", Replacement:="u"
        Selection.Replace What:="ぷ", Replacement:="pu"
        Selection.Replace What:="ぶ", Replacement:="bu"
        Selection.Replace What:="づ", Replacement:="du"
        Selection.Replace What:="ず", Replacement:="zu"
        Selection.Replace What:="ぐ", Replacement:="gu"
        Selection.Replace What:="ヴ", Replacement:="vu"
        Selection.Replace What:="れ", Replacement:="re"
        Selection.Replace What:="め", Replacement:="me"
        Selection.Replace What:="へ", Replacement:="he"
        Selection.Replace What:="ね", Replacement:="ne"
        Selection.Replace What:="て", Replacement:="te"
        Selection.Replace What:="せ", Replacement:="se"
        Selection.Replace What:="け", Replacement:="ke"
        Selection.Replace What:="え", Replacement:="e"
        Selection.Replace What:="ぺ", Replacement:="pe"
        Selection.Replace What:="べ", Replacement:="be"
        Selection.Replace What:="で", Replacement:="de"
        Selection.Replace What:="ぜ", Replacement:="ze"
        Selection.Replace What:="げ", Replacement:="ge"
        Selection.Replace What:="を", Replacement:="wo"
        Selection.Replace What:="ろ", Replacement:="ro"
        Selection.Replace What:="よ", Replacement:="yo"
        Selection.Replace What:="も", Replacement:="mo"
        Selection.Replace What:="ほ", Replacement:="ho"
        Selection.Replace What:="の", Replacement:="no"
        Selection.Replace What:="と", Replacement:="to"
        Selection.Replace What:="そ", Replacement:="so"
        Selection.Replace What:="こ", Replacement:="ko"
        Selection.Replace What:="お", Replacement:="o"
        Selection.Replace What:="ぽ", Replacement:="po"
        Selection.Replace What:="ぼ", Replacement:="bo"
        Selection.Replace What:="ど", Replacement:="do"
        Selection.Replace What:="ぞ", Replacement:="zo"
        Selection.Replace What:="ご", Replacement:="go"
        Selection.Replace What:="ゑ", Replacement:="we"
        Selection.Replace What:="ゐ", Replacement:="wi"
        Selection.Replace What:="っ", Replacement:="t"
        Selection.Replace What:="ー", Replacement:="-"


	'選択した範囲のカタカナ（拗音以外）をローマ字に変換する

        Selection.Replace What:="ン", Replacement:="nn"
        Selection.Replace What:="ワ", Replacement:="wa"
        Selection.Replace What:="ラ", Replacement:="ra"
        Selection.Replace What:="ヤ", Replacement:="ya"
        Selection.Replace What:="マ", Replacement:="ma"
        Selection.Replace What:="ハ", Replacement:="ha"
        Selection.Replace What:="ナ", Replacement:="na"
        Selection.Replace What:="タ", Replacement:="ta"
        Selection.Replace What:="サ", Replacement:="sa"
        Selection.Replace What:="カ", Replacement:="ka"
        Selection.Replace What:="ア", Replacement:="a"
        Selection.Replace What:="パ", Replacement:="pa"
        Selection.Replace What:="バ", Replacement:="ba"
        Selection.Replace What:="ダ", Replacement:="da"
        Selection.Replace What:="ザ", Replacement:="za"
        Selection.Replace What:="ガ", Replacement:="ga"
        Selection.Replace What:="リ", Replacement:="ri"
        Selection.Replace What:="ミ", Replacement:="mi"
        Selection.Replace What:="ヒ", Replacement:="hi"
        Selection.Replace What:="ニ", Replacement:="ni"
        Selection.Replace What:="チ", Replacement:="chi"	'tiでもOK
        Selection.Replace What:="シ", Replacement:="shi"	'siでもOK
        Selection.Replace What:="キ", Replacement:="ki"
        Selection.Replace What:="イ", Replacement:="i"
        Selection.Replace What:="ピ", Replacement:="pi"
        Selection.Replace What:="ビ", Replacement:="bi"
        Selection.Replace What:="ヂ", Replacement:="di"
        Selection.Replace What:="ジ", Replacement:="ji"
        Selection.Replace What:="ギ", Replacement:="gi"
        Selection.Replace What:="ル", Replacement:="ru"
        Selection.Replace What:="ユ", Replacement:="yu"
        Selection.Replace What:="ム", Replacement:="mu"
        Selection.Replace What:="フ", Replacement:="fu"		'huでもOK
        Selection.Replace What:="ヌ", Replacement:="nu"
        Selection.Replace What:="ツ", Replacement:="tsu"
        Selection.Replace What:="ス", Replacement:="su"
        Selection.Replace What:="ク", Replacement:="ku"
        Selection.Replace What:="ウ", Replacement:="u"
        Selection.Replace What:="プ", Replacement:="pu"
        Selection.Replace What:="ブ", Replacement:="bu"
        Selection.Replace What:="ヅ", Replacement:="du"
        Selection.Replace What:="ズ", Replacement:="zu"
        Selection.Replace What:="グ", Replacement:="gu"
        Selection.Replace What:="ヴ", Replacement:="vu"
        Selection.Replace What:="レ", Replacement:="re"
        Selection.Replace What:="メ", Replacement:="me"
        Selection.Replace What:="ヘ", Replacement:="he"
        Selection.Replace What:="ネ", Replacement:="ne"
        Selection.Replace What:="テ", Replacement:="te"
        Selection.Replace What:="セ", Replacement:="se"
        Selection.Replace What:="ケ", Replacement:="ke"
        Selection.Replace What:="エ", Replacement:="e"
        Selection.Replace What:="ペ", Replacement:="pe"
        Selection.Replace What:="ベ", Replacement:="be"
        Selection.Replace What:="デ", Replacement:="de"
        Selection.Replace What:="ゼ", Replacement:="ze"
        Selection.Replace What:="ゲ", Replacement:="ge"
        Selection.Replace What:="ヲ", Replacement:="wo"
        Selection.Replace What:="ロ", Replacement:="ro"
        Selection.Replace What:="ヨ", Replacement:="yo"
        Selection.Replace What:="モ", Replacement:="mo"
        Selection.Replace What:="ホ", Replacement:="ho"
        Selection.Replace What:="ノ", Replacement:="no"
        Selection.Replace What:="ト", Replacement:="to"
        Selection.Replace What:="ソ", Replacement:="so"
        Selection.Replace What:="コ", Replacement:="ko"
        Selection.Replace What:="オ", Replacement:="o"
        Selection.Replace What:="ポ", Replacement:="po"
        Selection.Replace What:="ボ", Replacement:="bo"
        Selection.Replace What:="ド", Replacement:="do"
        Selection.Replace What:="ゾ", Replacement:="zo"
        Selection.Replace What:="ゴ", Replacement:="go"
        Selection.Replace What:="ヱ", Replacement:="we"
        Selection.Replace What:="ヰ", Replacement:="wi"
        Selection.Replace What:="ッ", Replacement:="t"
        Selection.Replace What:="ー", Replacement:="-"
        

	'選択した範囲の大文字を小文字に変換する

        Selection.Replace What:="A", Replacement:="a"
	Selection.Replace What:="B", Replacement:="b"
	Selection.Replace What:="C", Replacement:="c"
	Selection.Replace What:="D", Replacement:="d"
	Selection.Replace What:="E", Replacement:="e"
	Selection.Replace What:="F", Replacement:="f"
	Selection.Replace What:="G", Replacement:="g"
	Selection.Replace What:="H", Replacement:="h"
	Selection.Replace What:="I", Replacement:="i"
	Selection.Replace What:="J", Replacement:="j"
	Selection.Replace What:="K", Replacement:="k"
	Selection.Replace What:="L", Replacement:="l"
	Selection.Replace What:="M", Replacement:="m"
	Selection.Replace What:="N", Replacement:="n"
	Selection.Replace What:="O", Replacement:="o"
	Selection.Replace What:="P", Replacement:="p"
	Selection.Replace What:="Q", Replacement:="q"
	Selection.Replace What:="R", Replacement:="r"
	Selection.Replace What:="S", Replacement:="s"
	Selection.Replace What:="T", Replacement:="t"
	Selection.Replace What:="U", Replacement:="u"
	Selection.Replace What:="V", Replacement:="v"
	Selection.Replace What:="W", Replacement:="w"
	Selection.Replace What:="X", Replacement:="x"
	Selection.Replace What:="Y", Replacement:="y"
	Selection.Replace What:="Z", Replacement:="z"


	'選択した範囲で促音（っ）になるよう変換する

	Selection.Replace What:="tb", Replacement:="bb"
	Selection.Replace What:="tc", Replacement:="cc"
	Selection.Replace What:="td", Replacement:="dd"
	Selection.Replace What:="tf", Replacement:="ff"
	Selection.Replace What:="tg", Replacement:="gg"
	Selection.Replace What:="th", Replacement:="hh"
	Selection.Replace What:="tj", Replacement:="jj"
	Selection.Replace What:="tk", Replacement:="kk"
	Selection.Replace What:="tl", Replacement:="ll"
	Selection.Replace What:="tm", Replacement:="mm"
	Selection.Replace What:="tn", Replacement:="nn"
	Selection.Replace What:="tp", Replacement:="pp"
	Selection.Replace What:="tq", Replacement:="qq"
	Selection.Replace What:="tr", Replacement:="rr"
	Selection.Replace What:="ts", Replacement:="ss"
	Selection.Replace What:="tt", Replacement:="tt"
	Selection.Replace What:="tv", Replacement:="vv"
	Selection.Replace What:="tw", Replacement:="ww"
	Selection.Replace What:="tx", Replacement:="xx"
	Selection.Replace What:="ty", Replacement:="yy"
	Selection.Replace What:="tz", Replacement:="zz"


	'コピー切り取りを解除
	'（コピーや切り取りしたときに選択したセルの周りでくるくるするのを止める）

	Application.CutCopyMode = False  


	'A1セルを選択する

	Worksheets("Sheet1").Range("A1").Select

End Sub
    人によってタイピング方法は様々です。
    ご自身のタイピング方法方法に合わせて、変換する文字を変えてください。

    コード追加

  • Step5
    を押して画面上部のVBAを実行し、問題なく動くかを確認する。

    実行

    F5キーを押してもVBAが実行できます。

 

スポンサーリンク

まとめ

うまく変換できたでしょうか？

セルの範囲（Range）の部分を変えてあげると、

値を貼り付ける場所や、変換する対象などを変えることができます。

 

A列に元のデータ、

B列に値を貼り付けてひらがな/カタカナに変換

C列にローマ字を入れるようにすると見やすいと思います。

 

ただし、全行を選択してしまうと処理に時間がかかり、

最悪はクラッシュしてしまうこともあるかもしれませんので、注意してくださいね。

スポンサーリンク
ホーム
トップ
タイトルとURLをコピーしました