「住所録を活用するための」Excel関数CLEAN! 「今さら…」の基本から解説！その2
目次
はじめに
「住所録を活用する」という視点から「Excel関数」について解説していますが、
第2回目はより高度な関数活用についてご紹介します。
具体的内容としては、上記のような、「改行」に加え、「不要スペース」が入っているようなものをクリーニングして、1行の形にするにはどうしたらいいかを解説していきます。
印刷できない文字を削除する関数「CLEAN」
前回解説した「TRIM」を使えば、不要スペースは削除できます。
しかし、今回問題となっている「改行」については、改行専門に削除する関数はないので、「改行も」削除できる方法で対処しなくてはなりません。
いくつか方法がありますが、一番簡単なのは関数「CLEAN」を使うものです。
この関数は、「改行」「Tab」といった制御文字や特殊文字のみを削除するものですが、ここではとりあえず簡単に「Excelでは印刷できない文字を削除する」と覚えておいてください。
では、実際に「CLEAN」を使って処理をしてみましょう。
今回はこの関数の機能を説明するため、5行目の例の方分のみを処理してみます。
前回行ったように、I列を選択し、右クリック→挿入を選び、関数適用のための列をつくります。
タイトルは「改行削除」としておきます。
セルI５を選択したら、数式タブをクリック。
並んでいるボタンの中から「文字列操作」→「▼」をクリックすると、プルダウンで関数一覧が表示されますので「CLEAN」を選びます。
関数の引数を指定します。
セルH５をクリックして「OK」ボタン。
セルH5 のデータに「CLEAN」が適用されます。
しかし、見た目には改行が削除されているかどうかわかりません。
先ほど「CLEAN」は「Excelでは印刷できない文字を削除する」と説明しましたが、この例では「改行」以外に「スペース」が入っています。
実は「CLEAN」には「スペース」削除の機能がありません。
したがってこの場合、たくさんのスペースがあるため「米田マンション」の部分が後方に追いやられて目視できない状態になっているのです。
この状態を解消するためには、セルI５のデータに対して更に「TRIM」を適用させて「スペース削除」を行わなければなりません。
そこで、関数を適用するために1行新たな列を挿入します。
タイトルは「スペース削除」として、先ほどの要領でセルJ５に「TRIM」を挿入して適用させます。
「TRIM」は単語間以外のスペースを全て削除する関数ですので、「4－4－8」と「米田マンション」の間のスペースを残して、「改行」「スペース」が全て削除されました。
説明のために1行分しか関数を適用していませんが、実際には1列全てに関数が適用されているので、前回解説したとおりに「コピー」→「貼付のオプション」→「値」で、「文字データ化」し、元データと差し替える操作を行います。
関数「SUBSUTITUTE」と「CHAR」を使う方法
「CLEAN」以外では、関数「SUBSUTITUTE」を使う方法があります。
この関数は、文字列の中の「特定の文字」を指定してそれを「他の文字」に置き換える働きをもっています。
例えば、勤務先の一覧で、「（株）」と「株式会社」が混在しているのはマズイので「株式会社」に統一することになったとしましょう。
これを一つずつ「株式会社」に入力し直していくのは非常に手間がかかります。
そこで関数「SUBSTITUTE」を使います。
対象となる文字だけを一括で変換できるので合理的なうえに、入力ミスもなく安全です。
この関数を利用して「改行」を「””」という文字に置き換えて「何もない」ことにしてしまい、結果的に削除してしまうという方法ですが、ただ、この方法を使う場合に一つ問題があります。
「””」に置き換えるためには、「改行」を「文字」として入力しなければなりませんが、「印刷できない制御文字」である「改行」はそのままキーボードから入力することはできません。
そこで、制御文字を「文字」として表すためにもう一つ関数を使います。
それが内部コードに対応する文字変換を行う関数「CHAR」です。
「内部コード」について、ここでは詳しい説明は省きますが、「改行」「Tab」をはじめ、「％」や「＊」といった制御文字・記号などを設定したもの（ナンバー）、といった感じで認識しておけばいいと思います。
「改行」のコードは「10」と決まっているので、「CHAR（10）」とすれば、「改行」を表すことになります。
先ほど「CLEAN」で行ったように、関数適用のために1列挿入して、そこに「SUBSUTITUTE」関数を挿入します。
関数の引数の設定において、「検索文字列」には「改行」を示す「CHAR（10）」を入力します。
「CLEAN」で行ったのと同じように、この結果に対して「スペース」の削除（「TRIM」の適用）を行い、最終的に「文字データ化」と元データとの差し替えを行い完成です。
関数の組み合わせ「関数のネスト」
「SUBSTITUTE」を使用した際、引数の設定の中に関数そのものを入れました。
このように、関数の中に関数を入れ込んで、より複雑な処理を行うことを「関数のネスト」と呼んでいます。
「ネスト」をすることで、何列もに渡って処理を繰り返していたものを1列でおさめることができます。
では、前回の「TRIM」から「CHAR」までを「ネスト」でまとめて処理してみましょう。
引数の設定時にキーボードで入力するのもいいのですが、もう少し簡単に行う方法があります。
これまでのものと比べるために、「関数のネスト」というタイトルで1列挿入します。
まず「TRIM」を挿入します。
「TRIM（ ）」の（ ）内に「SUBSTITUTE」と「CHAR」の組み合わせを入れ込みます。
「TRIM」の引数設定ウィンドウが開いている状態のまま、数式バー左に表示されている「▼」をクリックしプルダウンメニューを出します。
その中に「SUBSTITUTE」がありますので、選びます。
「TRIM（ ）」の（ ）の中の設定を開始する「SUBSTITUTE」の引数設定ウィンドウが開きます。
まず、セルH5をクリックして「文字列」を入力します。
次に、「置換文字列」に「””」をキーボードから入力します。
次にカーソルを「検索文字列」に置いた状態で、「▼」をクリックしてプルダウンメニューを表示させ、「CHAR」を選びます。
「CHAR」の引数を設定するウィンドウが開きますので、「数値」のところに「10」とキーボードで入力し、「OK」ボタンをクリックします。
見ての通り、2段階に分けて処理したものと同じ結果となりました。
ここで設定したネストは
=TRIM(SUBSTITUTE(H5,CHAR(10),””))
と3段階分が1つの式に含まれていますが、今回最初に紹介しました「CLEAN」を使用すればもっと簡略なネストになります。
=TRIM(CLEAN(H5))
2段階で済んで、時間もこちらの方が早いでしょう。
どのような式を展開するかは、その内容や状況により異なってきますので、その時々にあった組み合わせを常に考えていくことが重要です。
おわりに
今回のポイントは
- 「改行」や「Tab」といった目視できない「制御文字」を削除するには、「CLEAN」や「SUBSTITUTE」と「CHAR」の組み合わせといった関数を使用する。
- 関数を組み合わせて使用することで、より高度な処理が可能となる。これを「関数のネスト」という。
ここまでスペースや改行といった「余分なモノ」の削除を行ってきましたが、実際のデータではさらに活用の障害になるような「不備」が入り込んでいることがまだまだあります。
全く同じ文字列が同じセル内で続いていたり、ある行にあった内容が別の行にもあったり、といったことがよくあります。
「ダブリ」ともいわれるものですが、次回はそれらに対処するための関数をご紹介していきたいと思います。
