今回は、 SUBSTITUTE 関数と TRIM 関数です。
取引先の担当者名簿、自分の会社の社員名簿、入社希望社の名簿など、名簿を使うことは私たちの周りに頻繁にあると思います。そして、エクセルだと手軽にしかも簡単に、名簿が作れますので、エクセルを使って管理しているという会社は多いのでは無いでしょうか。
エクセルで名簿を作るのは、本当に簡単で、会社のネット上にある共有のフォルダに入れておいて、皆で追記していくというやり方でしている会社も多いと思います。
複数の担当が入力できるので、効率よくどんどんと溜まっていく反面、入力のルールがあっても入力のスタイルがばらつきが発生してしまうのも仕方がないことですね。
でも、この入力したデータは使えてこそ有効ですから、入力済みのデータをうまく使いやすい形に整えることがとても大切になってきます。
VLOOKUP関数などで検索する時は、入力したままのデータの形ではなく、人によるばらつきを取り除いたデータベースにしておくと気持ちよく、とても使いやすいです。
整ったデータベースなら、多くの人が利用するときに、トラブルが少なく効率的に運用が出来るようになりますからね。
今回はその1つ、氏名の元データに空白文字、全角や半角が入り込んでいるデータを整える方法について紹介します。
氏名のデータを整える
方針としては、次の3ステップで処理します。
- 姓名の前と後の空白を全て削除
- 姓と名の間の空白を一文字残して他は削除
- 全角の空白は、半角に変換する
そこで、使う関数が、TRIM 関数と SUBSTITUTE 関数です。
TRIM 関数は、文字列の先頭にあるスペース文字を全角と半角どちらも削除します。
また、文字列の終わりにあるスペース文字も同様に削除します。
更に、文字列の途中にあるスペースは複数のスペース文字が連なる場合1文字のみにします。
先頭から始まるスペースは全部削除、途中のスペースは1文字に減少、最後のスペースも全部削除ということです。ですから、TRIM 関数で方針のステップ1と2がいっぺんに出来てしまいます。
SUBSTITUTE 関数は、文字列の中から指定の文字を探して目的の文字に変換する関数です。
今回は、方針の3つめのステップの全角スペースを半角スペースに変換する目的で使います。
データの氏名のカラムについて、姓と名の間は一文字の半角スペースとなるようにし、逆にスペースが全くないセルについては、背景を色づけして注意を促す仕様とします。
SUBSTITUTE 関数と TRIM 関数のネストで整える
目的の結果を得るためにTRIM関数でスペース文字の数を整えて、その後、SUBSTITUTE関数で全角のスペース文字を半角スペースに変換することにします。
SUBSTITUTE関数のネストで全角スペースと半角スペースを同時に削除する方法については、こちらの記事を参照してみてください
どちらの順で使っても結果は同じですが、整理整頓の原則「要らないものを捨てて次に整える」の順で処理する様に式を書きます。
こうすることで、処理の時間にも影響してきますし、大量に変換が必要なときには効果が出ると思います。
今回作成した数式は、このようになりました。
=SUBSTITUTE(TRIM(B3), " ", " ")
この式で、先にTRIM関数が実行され、B3セルの文字列から先頭からのスペース文字と終わりのスペース文字、途中のスペースが連なるものを一文字に減らした文字列が返されます。
その文字に残る全角のスペース文字をSUBSTITUTE関数で半角に変換して完成です。
空白がない場合にエラー表示
次に、入力が足りない場合や、空白を挟んでいない場合に、警告表示として背景をオレンジにする条件付き書式を入れておきましょう。
空白文字がないのを判断する関数はないので、FIND関数で半角スペースを探して、無い場合にエラーを返すため、ISERROR関数で判断して背景に色を付けます。
例によって、alt H L 2 Nで新しい書式ルールのダイアログを表示させます。
「数式を使用して、書式を設定するセルを決定」を選択後、下の数式欄に次のように入力
=ISERROR(FIND(" ",C3))
条件付き書式の数式に指定する、セルはどうしたら良いか迷うことがありませんか？
私は最初の頃、ずっと混乱していましたが、今はすぐにできるようになりました。
コツを覚えれば簡単です。
今回ので言えば、新しい書式ルールを開く前に、C3:C6を選んでから開きましたね。
そうすると、上の図を見てください。左側の図の左肩に赤丸で囲んだところに、C3と表示されていますね。これを使うと覚えています。
どんなときも、すべて旨くいくわけではありませんが、まずこれを覚えてください。
結果は、次のようになります。
まとめ
- データベースは、入力データから整えたものを使う
- 氏名を整えるには、TRIM関数とSUBSTITUTE関数の組み合わせで処理する
- 氏名の不完全は、簡易的に空白が無いことで判断。ISERROR関数とFIND関数で判定する
＜広告＞始まり—–
どうしても、なくせないのがFAXでの書類の受け渡し。相手がありますのでFAXでと言われると、メールでお願いしますとはなかなか言えないこともあります。そんなときに、インターネットFAXが代替してくれます。
FAX機の故障や寿命での取替え、インクカートリッジの交換など、保守にかかる手間は結構かかってしまいます。また、紙代・通信費は細かい費用かもしれませんが、固定的に掛かります。複合機であれば、結構な場所をとりますし電源、通信線も結構邪魔になります。 隙間には埃もたまりますので、掃除も大変で、放っておくとトラッキングによる火事も心配です。
といったように、FAXが有るだけで、こんなにも労力・費用・安全に対する負担があります。そうであれば、FAXでのやり取りは、電子化の流れでemailやクラウドなどの他の手段に変えていきたいところです。
これらの課題への対応に、インターネットFAXはいかがでしょうか。
スマホで受信して内容を確認し、スマホからFAXでの返信ができます。
電子帳簿保存法の電子取引データ保存が義務化されましたが、この法改正への対応策として活用できるのではないでしょうか。
＜広告＞終わり—–
コメント