[Excel]任意の文字セットを別の文字セットに置換する数式を生成する方法

概要

Excelワークシートの数式だけで、任意の文字セットを別の文字セットに変換する数式を生成する方法を説明します。既に生成した数式を見たい場合は下記を参照して下さい。

[Excelユーティリティ]半角カタカナ⇔全角カタカナ変換やファイル名予約文字の削除などを行うワークシート

ここでいう文字セットとは、「ユーザーが定義した任意の文字列のグループ」です。例えば、半角カタカナも1つの文字セットですし、漢数字も1つの文字セットです。もちろん、全く規則性の無い「XYZ ああぁぁぁ 黒箱 Σ」も、これが文字セットだと言えば文字セットになります。

任意の文字セットを別の文字セットに変換するとは、例えば半角カタカナを全角カタカナに変換したり、漢数字(一,二,三,…)をアラビア数字(1,2,3,…)に変換する、という意味です。

アルゴリズム

この方法はSUBSTITUTE関数を使用します。SUBSTITUTE関数は、指定した文字列を別の文字列に置換する関数ですが、この関数をネストすることで複数の文字列を別の文字列に変換することができます。しかし、例えば半角カタカナを全角カタカナに変換する場合、手入力で数十回もネストするのは現実的ではありませんので、このネストを数式で自動生成します。

仕様

  • 1つの文字セットに含められる文字列の数の上限は64個です。これは、Excelの仕様上、関数に設定できるネストレベルの上限が64個であることに起因しています。
  • 2つの文字セットに含められる文字列の合計の長さの上限は7030文字です。これは、生成する数式の文字数が最長11162文字であることに加え、Excelの仕様上、数式の長さの上限が8192文字であり、セルアドレスを文字列にした際の文字数が最長10文字であることに起因しています。

方法

準備

Excelを起動して新規ワークシートを用意し、下記のような表を作成します。

B4:C12セル及びD3セル(ついでにG2セル)はユーザー入力欄となりますので、一目で分かるように適当な色を付けておきます。

変換する文字セットを定義します。例として丸囲み数字(①,②,③,…)をアラビア数字(1,2,3,….)にします。B4:B12セルに丸囲み数字を、C4:C12セルにアラビア数字を入力します。なお、丸囲み数字はオートフィルが出来ないため、地道に入力していくか、数式+オートフィルで自動入力しましょう。後者の手順は、B4セルに「①」、B5セルに「=CHAR(CODE(B4)+1)」を入力し、B5セルをB12セルまでオートフィルをして下さい。

変換数式の生成

D4セルに下記の数式を入力します。

=IF(B5="","=","")&"SUBSTITUTE(" & D$3 & ", """ & B4 & """, """ &C4 & """)"

D5セルに下記の数式を入力します。

=IF(B6="","=","")&"SUBSTITUTE(" & D4 & ",""" & B5 & """,""" & C5 &""")"

D5セルをD12セルまでオートフィルします。この時、変換文字セットを入力した列(B列)の一番下のセルに、実際に使用する数式が表示されています。今回は、D12セルの内容が、目的の数式となっています。

最後に、変換したい文字を入力するセルのアドレスを、文字列でD3セルに入力します。今回は動作確認用の文字列をG2セルに入力しているので、D3セルには「G2」と入力します。

動作確認

生成した数式が期待通りに動くか確認します。数式は既に出来上がっているため、数式を入力するセルは任意のセルで良いのですが、枠を用意しているので、G3セルにしましょう。D12セルをコピーしてG3セルに値貼り付けをして下さい。通常の貼り付けではありませんのでご注意ください。

ディスプレイの彼方までSUBSTITUTEが伸びていますが、問題ありません。このままG2セルを編集状態にして(G2セルをダブルクリックするか、G2セルを選択した状態でF2キーを押す)、何もせずにEnterキーを押します。

丸囲み数字がすべてアラビア数字に変換されれば成功です。

運用

変換数式をオートフィルして使用

生成した変換数式は、下方向へオートフィルして使用することを前提として組んだため、本来は下記のように、左側(F列)に変換したい文字列を入力し、右側(G列)に変換後の文字列を出力する形で使用します。変換結果を固定したい場合はコピー&値貼り付けをして下さい。

文字列を削除する数式として使用

この数式の別の使い方として、変換後の文字列(C列)を空欄にしておくと、指定文字(B列)を削除する数式として使用できます。固有名詞や機種依存文字を一括削除するのに便利です。

数式生成表自体のコピー

数式生成表(A2:D12セル)は、それ自体をコピーしても正しく作動するように数式を組んでいます。そのため、数式生成表自体を単純にコピー&ペーストし、変換文字を編集するだけで使用することができます。

数式生成表と数式の分離

数式を生成した後は、数式生成表(A2:D12セル)が無くても動作するため、不要な場合は数式生成表を削除しても問題ありません。数式生成表は残しておきたいが、ユーザーの見える場所に置きたくない場合は、数式を生成するブックと数式を使用するブックを分けておくことも出来ます。

スポンサーリンク

Excel講座

Posted by 黒箱