仕事効率化

エクセルで名簿管理!混在したデータ表記を統一する方法とは?

エクセルで社員名簿や顧客名簿を作成すると、半角と全角、小文字と大文字、カタカナとひらがななど、異なる表記のデータが混在してしまうことはありませんか?
データに半角と全角が混在
上図は極端な例ですが、データに統一感がないエクセル名簿は見づらいです。

ナル
ナル
1カ所や2カ所なら修正すれば良い話ですが、膨大なデータ量となったら手直しするだけで骨が折れそう。

そこで本記事では、エクセル名簿のデータの表記を統一変換する方法お伝えします。

マクロの記録を利用すると、より効率的に仕事ができます。

【エクセル】マクロの記録の使い方と初心者でもすぐにできる活用法毎日決まった手順で行うエクセルの作業を自動化したいと思ったことはありませんか? 「マクロの記録」機能を使えばプラグミラングの知識がまったくない初心者でも簡単にマクロを作成することが可能です!...

エクセル名簿で表記が混在したデータを統一変換する方法

エクセル名簿に入力されたデータを1カ所ずつ修正しなくても、関数や書式設定を利用することで表記が混在したデータを統一変換することができます。

表記が統一されているエクセル名簿は見栄えが良いだけではなく、データを管理する上でも便利です。

本記事で利用するエクセル関数を表にまとめてみました。

ASC関数半角にする
JIS関数全角にする
UPPER関数大文字にする
LOWER関数小文字にする
PROPER関数単語の先頭のみ大文字にする
PHONETIC関数フリガナを抽出する

初心者だからエクセル関数や書式設定は使いこなせない…という心配はありません。

今回お伝えする内容は、シンプルですぐに真似できる方法ばかりです。
スポンサーリンク


エクセル名簿で表記が混在すると起こる問題とは?

「エクセル名簿の見た目が悪くても、データが間違っていなければ問題ない」という方もいらっしゃいますが、絶対にダメです。

たとえば、下図のリストからフィルター機能で「ペン」のみの売上を抽出したいとします。
売上リスト
下図の抽出条件には全角の「ペン」半角の「ペン」が別々に表記されます。
半角と全角はエクセルで別物として認識
人間の目で見たらどちらも同じ商品だと判別できるのですが、エクセルではこの2つを別の商品名として認識してしまうんです。

これではデータの分析や集計が正しくできません

また、VLOOKUP関数を利用してもデータを上手く抽出できないこともあります。

【エクセル】VLOOKUP関数の使い方とすぐにできる6つの応用技VLOOKUP関数はビジネスで大活躍の関数。しかし、初心者にはとっくきにくいと苦手意識を持っている方も多いです。そこでエクセル歴20年の筆者が分かりやすく解説。さらに新エクセル関数XLOOKUPについても紹介。...

表記が「半角と全角」とバラバラで入力されているデータでは、集計や分析をするときに支障を及ぼす恐れもあります。
スポンサーリンク



エクセルの名簿管理①半角と全角の表記を統一変換する

エクセル名簿の表記を統一変換する方法を順にお伝えしていきます。

エクセル関数の基本的な形は「=関数名(対象のセル)」。

この記事でお伝えする関数についてはこれだけ覚えればOK。

下図のエクセル名簿で、B2セルにA2セルのデータを半角にして表示します。

半角に統一するエクセル関数は「ASC」なので、B2セルに「=ASC(A2)」と入力。
ASC関数で半角にデータを変換
数字とハイフンが全角から半角に変換されました。

B2セルの右下にマウスのカーソルを合わせてC6セルまでマウスをドラッグすると、数式が他のセルにもコピーされます。
数式をコピー
A列のデータが、すべて半角に統一変換されました。

この方法なら、データが何百件、何千件とあっても一瞬で完了しますね。

エクセル名簿のデータ表記を全角にしたい場合には、「=ASC(A2)」のASC(関数名の部分)をJISに変更してみてください。
JIS関数で全角に変換
エクセル名簿のデータを全角に統一変換できましたね。

補足ですが、ASC関数やJIS関数は「空白」に対しても利用できます。

エクセル名簿の氏名の欄で、姓と名の間に空白を入れることは多いですよね。
空白も全角半角に変換できる
A列のデータを、B列では半角に、C列では全角に統一変換しています。

文字列部分は半角にはなりませんが、姓と名の間にあった空白が半角・全角にそれぞれ変換されています。

姓と名の間に入る空白は全角でも半角でも問題ありませんが、両方混在しているよりはどちらかに揃えてしまう方が良いです。

ナル
ナル
僕はエクセル名簿を管理するとき、姓と名の間の空白は「半角」に統一しています。

スポンサーリンク


エクセルの名簿管理②大文字と小文字の表記を統一変換する

関数の入力方法はASC関数やJIS関数と同じです。

A列のデータを、それぞれ大文字・小文字・先頭のみ大文字に変換してみました。
大文字と小文字の混在

エクセル名簿のA列には、大文字と小文字の表記が混在したデータが入力されています。

B列ではUPPER関数で大文字に統一変換。

C列ではLOWER関数で小文字に統一変換。

さらにD列では、PROPER関数を利用して単語の先頭の文字のみを大文字に統一変換しています。

たとえば、「garakuta-naru」なら「Garakuta-Naru」になるイメージです。

PROPER関数は利用する機会はあまりなさそうですが、いざとなったときにのために覚えておいてくださいね。
スポンサーリンク



エクセルの名簿管理③カタカナとひらがなの表記を統一変換する

エクセル名簿のフリガナ表記を、カタカナまたはひらがなに統一します。

カタカナとひらがなの混在
名簿のフリガナにカタカナとひらがなが混在すると、見た目もよくないですね。

表記をカタカナに統一してみます。

PHONETICというフリガナを取り出す関数を使います。

関数の使い方はASC関数やJIS関数と同じ。

C2セルに「=PHONETIC(B2)」と入力。
カタカナに統一する
C列に、B列のデータをカタカナに変換したものが表示できました。
カタカナに統一された
次は、B列のデータをひらがなにしてC列に取り出します。

B2セルからB6セルを範囲選択して、数式バーの上にある「ふりがなの設定」をクリック。
A列を選択して区切り位置
ふりがなタブの種類を「ひらがな」にしてOK。
ひらがなに設定
C列の文字がひらがなに一括変換されました!
ひらがなに統一
エクセル名簿の表記を変更したくなったときは、この方法でいつでも変換できます。
スポンサーリンク


エクセルの名簿管理④郵便番号の表示形式を統一変換する

郵便番号は7桁ですが、入力する人によってはハイフンを省略する方もいます。
郵便番号にハイフンを入れる
エクセル名簿の郵便番号表記をハイフン有りに統一します。

郵便番号が入力されている範囲を選択して「右クリック」→「セルの書式設定」を選択。

表示形式タブの分類の中の「その他」を選択すると、一番上に郵便番号」という表示形式が用意されています。
郵便番号を選択
クリックしてOK。
表示形式が統一
エクセル名簿の郵便番号が、全部ハイフン入りの形式に統一変換されました。

関数を使わずにできるので初心者にも簡単です。
スポンサーリンク



エクセルの名簿管理⑤携帯電話番号の表示形式を統一変換する

エクセル名簿に入力された携帯電話の番号を、ハイフンを入れた形式に統一します。

郵便番号は書式設定で簡単に表記を変換できましたが、携帯電話番号の形式は一覧にありませんでした。
携帯電話の表示形式はない

ナル
ナル
固定電話(東京)の表示形式はあるんですけどね。

悩んだ末にできた方法が、ユーザー定義。

種類の欄に「000-0000-0000」と入力してOKを押してみてください。
ユーザー設定で入力する
セルの書式設定は工夫次第で応用できるので参考にしてくださいね。

ナル
ナル
電話番号はハイフンがないと見づらいです。

携帯電話の表示ハイフンが入った
エクセル名簿の携帯電話番号の形式がハイフン入りに統一変換されました。
スポンサーリンク


エクセルの名簿管理⑥氏名を姓と名に分ける

A列の氏名を分割して、A列に姓、B列に名を表示したいという場合です。
姓名を姓と名に分けたい
A列を選択した状態で、データタブの区切り位置をクリック。
A列を選択して区切り位置
区切り位置指定ウィザードが表示されます。

「カンマやタブなどの区切り文字によってフィールドごとに区切られたデータ」という方を選択して「次へ」。
上にチェック
区切り文字のスペースにレ点を入れて「完了」。

この設定により、空白を目印にして自動的にデータを区切ってくれます。
スペースにチェック
A列に姓、B列に名が分かれて表示されました。
姓と名が分離
この方法は、エクセル名簿の姓と名の間に「空白」がある場合にのみ有効です。
スポンサーリンク



エクセルの名簿管理⑦姓と名を結合させる

エクセル名簿で別々に入力されている姓と名を結合して、1つのセルに表示します。
姓と名を結合したい
C2セルに「=A2&B2」と入力。

A2セルとB2セルのデータがくっついた状態になります。

ポイントは「&」です。

数式で「&」を使うと2つのセルを結合することができるんです。
&でデータを結合
姓と名の間に空白が挿入したい場合には、「=A2&B2」という式を「=A2&” “&B2」に修正すればOK。

「” ”」は空白を意味しています。

姓と名の間に空白を入れて統合
姓と名を結合することができました。

スポンサーリンク


エクセルの名簿管理:補足

たとえば全角大文字のデータを半角小文字にするとしたら、まずASC関数で半角にして、次にLOWER関数で小文字にしますよね。

もちろん、それでも間違いではないのですが2段階の手間がかかります。

しかし、エクセル関数を組み合わせれば1つの数式で文字列を変換することもできます。
関数を組み合わせる
C2セルに「ASC(LOWER(A2))」と入力すればOK。

ナル
ナル
エクセル初心者の方には少しむずかしく感じるかもしれませんが、機会があれば挑戦してみてください。

もう1つ補足があります。

エクセル関数を利用してデータを変換した場合、セルは数式を含んだままの状態です。

対象のセルをクリックすると、上部のバーに数式が表示されるんですよね。
セルに数式が含まれている
このままだと、データを集計・分析などで利用する場合に正しい結果が求められない可能性があります。

そのため、数式を消す作業が必須です。

やり方は簡単で、対象のセルをコピーして同じ場所に「値の貼り付け」をするだけ。

これだけで、セルの中の数式が消えます。
値で貼り付け

※右クリック→形式を選択して貼り付け→値 でもできます。

値の貼り付けは頻繁に利用する機能なので、クイックアクセスツールバーに登録しておくと便利です。

エクセルの超便利機能!クイックアクセスツールバーとは?エクセルの超便利な機能「クイックアクセスツールバー」を利用すれば、作業効率が格段に上がります。また、自分専用にカスタマイズすることも可能です。...

スポンサーリンク



まとめ

エクセルの名簿作成や管理で、データの表記を変換統一する方法をまとめてみました。

できるだけシンプルな方法を集めたので、初心者の方も早速試してみてください。

頻繁にデータの加工をするなら、マクロを利用するのもおすすめです。2

【エクセル】マクロの記録の使い方と初心者でもすぐにできる活用法毎日決まった手順で行うエクセルの作業を自動化したいと思ったことはありませんか? 「マクロの記録」機能を使えばプラグミラングの知識がまったくない初心者でも簡単にマクロを作成することが可能です!...

エクセル名簿に限らず、データを管理するときには表記の統一感や見た目の美しさも意識すると良いですね。

この記事があなたのお役に立てたら幸いです。
スポンサーリンク