Excelでひらがなとカタカナを変換する数式を作ってみた

ネットで情報収集していたら、ふと「ひらがなをカタカナに変換したい。」という文字が見えた。

最近、文字列処理の関数ばかり見ていた私はCONCATを使えば出来るとピンと来たため、お盆休み最後の全ての予定を放り投げて早速作ってみることにした。

f:id:Kotori-ChunChun:20190819023144p:plain

従来までの方法
新しく作成した数式
数式作成までの話
まとめ

従来までの方法

まず、Excelには一発でひらがなをカタカナに変換するような関数は存在しない。

VBAが使えるならStrConv()関数で簡単に変換できるのでこれを使わない手はない。

'例　これらをCtrl+G→イミディエイトウィンドウで実行すると、選択中のセルの文字列が変換される
MsgBox StrConv("あいうえお", vbKatakana)

'ひらがな→カタカナ
ActiveCell.Value = StrConv(ActiveCell.Value, vbKatakana)

'カタカナ→ひらがな
ActiveCell.Value = StrConv(ActiveCell.Value, vbHiragana)

大量のセルを一気に変換したいなら、For Eachで回せば良い。

'ひらがな→カタカナ
Sub SelectionHira2Kata()
    Dim rng As Range
    For Each rng In ActiveWindow.RangeSelection
        rng.Value = StrConv(rng.Value, vbKatakana)
    Next
End Sub

'カタカナ→ひらがな
Sub SelectionKata2Hira()
    Dim rng As Range
    For Each rng In ActiveWindow.RangeSelection
        rng.Value = StrConv(rng.Value, vbHiragana)
    Next
End Sub

ワークシート上で使いたいなら、ユーザー定義関数にすれば良いだけだ。

'ひらがな→カタカナ
Function Hira2Kata(s)
    On Error Resume Next
    Hira2Kata = StrConv(s, vbKatakana)
End Function

'カタカナ→ひらがな
Function Kata2Hira(s)
    On Error Resume Next
    Kata2Hira = StrConv(s, vbHiragana)
End Function

意地でもVBAを使わずにやりたい人に有名な方法が「ふりがな」を使う方法である。

=PHONETIC(A1)

ただし、この方法は多くの致命的な問題がある。

まず、Excelの設定（ふりがなの設定）に依存するため、「ひらがな→カタカナ」「カタカナ→ひらがな」どちらにするかの選択権は数式に存在しない。

f:id:Kotori-ChunChun:20190819014238p:plain

さらに、読み込んでいるのはあくまで「ふりがな」なので、漢字にふりがな情報が入っていると、漢字がカナに変わってしまう。

あとは、式の入っているセルや文字列定数を変換することができないという制限もある。

もう一つが大量の作業セルを使用した数式による変換である。

今回は詳しく書かないが、

MID関数で一文字毎に抽出した結果をセルに出力し
それぞれの文字をCODE関数でJISコード化し
256を「ひらがな→カタカナ」なら加算、「カタカナ→ひらがな」なら減算し　※BITXOR(コード, 256)なら双方に利用可
CHAR関数で文字に戻し
CONCATENATE関数で気合で連結

という流れで出来ないこともない。

同じことを下記のサイトが紹介していた。

PHONETIC関数を使わないひらがな⇔カタカナ変換方法

今回作成した数式は、この計算を単一のセルで実行できるようにしたものである。

そのために、Excel2019で完全対応したCONCAT関数を使用する。

Excel 2016以前ではほぼ確実に動作しないので注意してほしい。

www.excel-chunchun.com

新しく作成した数式

ひらがなをカタカナに変換

=CONCAT(INDEX(
  UNICHAR(
     UNICODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))+
       96*BITAND(
         UNICODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))>=12353,
         UNICODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<=12438
      )),
0))

カタカナをひらがなに変換

=CONCAT(INDEX(
  UNICHAR(
     UNICODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))-
       96*BITAND(
         UNICODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))>=12449,
         UNICODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<=12534
      )),
0))

動作要件

これらの式は4バイト文字には対応していないが、事実上そんな文字は滅多に入力されないだろうということで無視している。

仮に入力された場合は#VALUE!が出るので注意して欲しい。

ここで言う4バイト文字とは、LEN関数の結果が2となる文字のことである。

文字	LEN
あ	1
ヴ	1
ゔ	1
㉒	1
😁	2
𠮷	2

LEN=2の文字が含まれていると、MIDで適切に文字分割が出来ないためエラーとなる。

UNICODE、UNICHARは対応しているので、何らかの方法できれいに分割することが出来れば、完全対応の数式を作ることができそうだが私には無理だった。

数式作成までの話

軽く調べたところ、既に良い感じの式が作成されていた。さすがと言わざる負えない。

エクセルのワークシート関数でカタカナ→ひらがなに変換する式ですが。

とりあえず
=CONCAT(CHAR(CODE(MID(A1,ROW(OFFSET($A$1,0,0,LEN(A1),1)),1))-256))
※入力時にCtrl+Shift+Enterで確定

で雑には動いてます。
「ヴ」や、カタカナ以外の文字を入れると変な文字が表示されると思います。 pic.twitter.com/PFewXd68wn
— はけた＠経理のExcel仕事術（弥生会計ユーザ向けの本）発売中 (@excelspeedup) 2019年3月22日

この式の改良版や、従来の方法はこちらのサイトでも公開されていた。

excel-ubara.com

しかし、まだまだ色々と制限があるようなので、独自の式を作り直すことにした。

詳しい話は下記のツイートを参照

https://t.co/47KrbId4Gr

＞ネストレベル：64回

「ひらがな」は、9250～9331までの82種類みたいなので、SUBSTITUTEを入れ子では対応できなかったようだ。

ふむ・・面白そうだ。作ってみよう。
— ことりちゅん@えくせるちゅんちゅん (@KotorinChunChun) 2019年8月18日

というわけで、対策は
・BITAND()
・INT()*INT()
のどっちか。

CONCATはデバッグに使いやすくてマジで助かる。 pic.twitter.com/9ieligvrGG
— ことりちゅん@えくせるちゅんちゅん (@KotorinChunChun) 2019年8月18日

IF関数によって配列が消される様子
（左上から右下へ）

なーんか対処法あったような気がするんだけど、サンプルが見つからぬ・・・。

結局IFが必要ない式に変形して対応してたんだったろうか pic.twitter.com/8YhiavGoS4
— ことりちゅん@えくせるちゅんちゅん (@KotorinChunChun) 2019年8月18日

などということは無かった。

😁みたいな、LEN()=2の文字が混入すると、MID(a,b,1)で分離が起こるため、UNICODE/UNICHARで動作しない。

つまり、コード変換は厳禁ということ。。。 pic.twitter.com/lKL1UEbPll
— ことりちゅん@えくせるちゅんちゅん (@KotorinChunChun) 2019年8月18日

JISコードとUNICODEにおける、ひらがなとカタカナの差

	ぁ	ァ	差
CODE	9249	9505	256
UNICODE	12353	12449	96

	ん	ン	差
CODE	9331	9587	256
UNICODE	12435	12531	96

ひらがなとカタカナは、対応する文字が同じ間隔で並んでいることが分かる。

最初の文字は「ぁ」、終わりの文字は「ん」、しかし特殊な文字を含めると最後は「ヶ」である。

ひらがな	CODE	UNICODE	CODE 0x16	UNICODE 0x16
ぁ	9249	12353	2421	3041
ん	9331	12435	2473	3093
ゖ	63	12438	3F	3096

カタカナ	CODE	UNICODE	CODE 0x16	UNICODE 0x16
ァ	9505	12449	2521	30A1
ン	9587	12531	2573	30F3
ヶ	9590	12534	2576	30F6

Twitter上では「ん」のあとに続く「ヴヵヶ」については、変換しないように無視していたが、UNICODEにしたことで「ゔゕゖ」が使えるようになったので若干修正している。

ひらがな	UNI	0x16	カタカナ	UNI	0x16
ゔ	12436	3094	ヴ	12532	30F4
ゕ	12437	3095	ヵ	12533	30F5
ゖ	12438	3096	ヶ	12534	30F6

しかし良く考えたら、これらの文字をひらがなで表記することは有るのだろうか？

変換しないほうが良かったかもしれない。

全体の文字コード表は下記の記事などを参考

www.asahi-net.or.jp

まとめ

数式で安全に変換出来るようになったとは言え、普通に使うには複雑すぎる数式である。

最優先候補としては、やはりVBAを使うべきだろう。

それに、変換した後のカナは多くの場合「値」としても差し支えないはずなので、従来のふりがなを使った方法でも十分かもしれない。

ただ、リアルタイムに変換する場面においての数式の使いやすさは他を凌駕するので、IMEに登録したりコピーして使って頂けたらと思う。

「ひらがなとカタカナの変換」は、日本固有のものなので、世界中に展開するExcelに導入するのは難しいのかもしれないが、日本人としてはやはり公式で搭載して欲しいと期待するばかりである。

なお、この関数はリニューアルした文字列処理の数式集にも掲載する。

www.excel-chunchun.com

以上

何か御座いましたらコメント欄、またはTwitterからどうぞ♪

それではまた来週♪　ちゅんちゅん(・8・)