【Excel】でデータを「ヘボン式」ローマ字変換し「値」を確定するExcelVBA

Excel
スポンサーリンク

「 ヘボン式ローマ字変換アドイン 」Excel拡張機能を使った関数

  • 漢字やカナは、ローマ字にするとパッと見わからない。
  • 更にローマ字をExcel機能の置換などを使えば、簡単にわけの分からないデータを大量作成できる

かな表記、カナ表記にするならローマ字までやる方がよい

Excelで文字列をローマ字に変換する方法

「Excelで文字列をローマ字に変換する方法」とググると「 ヘボン式ローマ字変換アドイン 」というExcel拡張機能を使った関数を使えることが分かるので表で使えるぐらいのところまでは実行して頂けるはず。

要領は 「PHONETIC 関数」 を使うときと同じで、一度ふりがな機能でルビを振っていないと使用できないもの!と理解した方がよい。

Excelを開き「ホーム」タブから「亜」をクリックする

  • ふりがなの表示(S)
  • ふりがなの編集(E)
  • ふりがなの設定(T)

これである程度の使い方は理解できると思う。

=hebon(A1,2)でローマ字変換できたら次へ進むとよい。

サンプルデータで「 ヘボン式ローマ字変換」を実践

サンプルを[Excel]Sheet1に貼り付ける

ゴールドアリュール
トーセンラー
パイロ
トーセンブライト
キングカメハメハ
アイルハヴアナザー
エイシンフラッシュ
フェノーメノ
クロフネ
シニスターミニスター

A列に上記「父」の名称があるとして話を進めていく。

ここで先ほどの「亜」でうまく「ふりがな設定」ができているとB列に=hebon(a1,2)・・・と10行設定すると、下記の表のように変換されるはず。

CHICHI
ゴールドアリュールGORUDOARYURU
トーセンラーTOSENRA
パイロPAIRO
トーセンブライトTOSEMBURAITO
キングカメハメハKINGUKAMEHAMEHA
アイルハヴアナザーAIRUHABUANAZA
エイシンフラッシュEISHINFURASSHU
フェノーメノFUENOMENO
クロフネKUROFUNE
シニスターミニスターSHINISUTAMINISUTA

ここまでくれば「勘が良い」方なら、マクロ記録機能で処理できるはず!と思うでしょうが!その通りできるので、テストしてみるのがよろしいかと・・・

マクロ記録機能でカナ変換を記録する

「ひらがな設定」で記録

Sub SEThiragana()
'
' ひらがな設定
'
''    Range("A1:A1").Select
''    Range(Selection, Selection.End(xlDown)).Select
    Selection.SetPhonetic
    Selection.Phonetics.CharacterType = xlHiragana
    Selection.Phonetics.Alignment = xlPhoneticAlignLeft
    Selection.Phonetics.Visible = False
End Sub

「半角カナ設定」で記録

Sub SETkanahan()
'
' 半角カナ設定
'

''    Range("A1:A1").Select
''    Range(Selection, Selection.End(xlDown)).Select
    Selection.SetPhonetic
    Selection.Phonetics.CharacterType = xlKatakanaHalf
    Selection.Phonetics.Alignment = xlPhoneticAlignLeft
    Selection.Phonetics.Visible = False
End Sub

重要な文は「Selection.SetPhonetic」ではないか!?と推測できる

上記サンプルを使用して、下記のようなVBAを作成してみる

  • B列:ローマ字
  • C列:ひらがな
  • D列:半角カナ

完成させるサンプル表「 ヘボン式ローマ字変換」

chichiちちチチ
ゴールドアリュールgorudoaryuruごーるどありゅーるゴールドアリュール
トーセンラーtosenraとーせんらートーセンラー
パイロpairoぱいろパイロ
トーセンブライトtosemburaitoとーせんぶらいとトーセンブライト
キングカメハメハkingukamehamehaきんぐかめはめはキングカメハメハ
アイルハヴアナザーairuhabuanazaあいるはヴあなざーアイルハヴアナザー
エイシンフラッシュeishinfurasshuえいしんふらっしゅエイシンフラッシュ
フェノーメノfuenomenoふぇのーめのフェノーメノ
クロフネkurofuneくろふねクロフネ
シニスターミニスターshinisutaminisutaしにすたーみにすたーシニスターミニスター

【Excel】VBAマクロを使って表を一括変換し値を確定させるサンプル

変換後はセル値を一度変数に取り込んで値として確定させる

今回のようにExcel拡張機能のようなものを入れると、自分のメインパソコンでは表示するが違うパソコンだと表示しなくなるというのを回避するためにExcel関数の値を確定するやり方で、実務でのExcelコピペでも使用しているはずだ。(ctrl+cからのctrl+v)

Sub TEST_KANA()
''  A列のデータをB列:ローマ字 C列:ひらがな D列:半角カナ
''  テストモジュール

Dim i As Integer
Dim wk_nm1 As String
    Sheets("Sheet1").Select
    Columns("B:D").Select               'B,C,D列を削除
    Selection.Delete Shift:=xlToLeft
    
    Range("A1:A11").Select
    Call SETkanahan
    
    i = 0
    Do Until CHRCVT(Cells(i + 1, 1)) = ""
        i = i + 1
        'ローマ字変換
        Cells(i, 2) = "=Hebon(A" & i & ",2)"
        If Not IsError(Cells(i, 2)) Then
            wk_nm1 = Cells(i, 2)
            Cells(i, 2) = wk_nm1
        End If
        'カナ半角変換
        Cells(i, 4) = "=asc(phonetic(A" & i & "))"
        If Not IsError(Cells(i, 4)) Then
            wk_nm1 = Cells(i, 4)
            Cells(i, 4) = wk_nm1
        End If
    Loop
    
    Range("A1:A11").Select
    Call SEThiragana
    
    i = 0
    Do Until CHRCVT(Cells(i + 1, 1)) = ""
        i = i + 1
        'ひらがな変換
        Cells(i, 3) = "=phonetic(A" & i & ")"
        If Not IsError(Cells(i, 3)) Then
            wk_nm1 = Cells(i, 3)
            Cells(i, 3) = wk_nm1
        End If
    Loop

    Range("A1").Select
End Sub

Call SETkanahan,( SEThiragana)を使っている場所をよく見ておくこと。これをループ内に入れると恐ろしく時間がかかるモジュールになり実用的ではなくなる変換文字が少なかったら良いが100以上あると”お前はすでに死んでいる”状態になる!

【Excel】でサンプルデータをローマ字変換する方法

は以上。

編集後記

システムのテスト環境を作るのに、実際のデータを使用するのが一番確実にテストできるが、毎度それが可能になるとは限らない!実データ使用の許可が下りたとしても、自分のミス以外でデータが流出したとしたら目も当てられない。日本語の場合、母音となる[aiueo]をローマ字変換後、更に違う文字に置換してしまえば、もう原型の判別は簡単にはできなくなっているはずだ。

2022.5.12追記、Googleスプレッドシートを使うやり方

Googleアカウントを登録していれば、

「Googleスプレッドシート」を立ち上げて対象データをコピペする。

種牡馬Stallion
ゴールドアリュール
トーセンラー
パイロ
トーセンブライト
キングカメハメハ
アイルハヴアナザー
エイシンフラッシュ
フェノーメノ
クロフネ
シニスターミニスター

=GoogleTranslate関数を使う

Googleスプレッドシート:GoogleTranslate関数のサンプル

はっきり言って、こっちの方が楽チンだよね~🤭

=Arrayformula関数は、GoogleTranslate関数には使えないようだ。

英語変換だけでなく多言語に対応してるから、素晴らしい!

これだけでも業務で使う用途にはなる。

言語言語コード
アラビア語ar
英語en
韓国語ko
スペイン語es
中国語zh
ドイツ語de
日本語ja
ヒンドゥー語hi
フランス語fr
ロシア語ru

GoogleスプレッドシートからExcelに貼付ける場合は、必ずテキスト指定をする!

※但し、英字に限る。

GoogleスレッドシートからExcelに貼り付ける場合はテキスト指定すると「値」だけ貼り付く

そのまま貼り付けると、Googleスプレッドシートの情報がそのまま残ることになる!

見た目は普通なんだけどねぇ~

adono
adono

破損しても問題なし!という運用なら、コピペ問題なしだが…

複数人での使用なら正規の方法を使おう。

それでは、またね~😎

タイトルとURLをコピーしました