Excelで抽出したデータを集計用の表へ貼り付ける際、従業員番号や商品コードなどの数字を自動的に名前や名称へ変換したいケースがあります。毎回「置換」機能を使う方法もありますが、データ量が多い場合や番号が増える場合には、Excelの関数を利用したほうが効率的です。この記事では、貼り付けるだけで自動変換できる仕組みの作り方を解説します。
Excelの置換機能を事前設定することはできるのか
Excelの「置換」機能は、その都度実行するための機能であり、シートを開いた時点で自動的に設定しておくことは基本的にはできません。
例えば、0100を山田、0200を田中、0300を佐藤へ変換したい場合、置換機能では毎回「0100を山田へ置換する」という操作が必要になります。従業員が増えたり番号が変更されたりすると管理も大変になります。
このようなデータ変換には、対応表を作成して関数で参照する方法が適しています。
VLOOKUP関数で番号を名前へ自動変換する方法
最も一般的な方法は、別の場所に番号と名前の対応表を作り、VLOOKUP関数で名前を取得する方法です。
例えば、別シートに以下のような対応表を作成します。
| 従業員番号 | 名前 |
|---|---|
| 0100 | 山田 |
| 0200 | 田中 |
| 0300 | 佐藤 |
元データのA2セルに従業員番号が入力されている場合、名前を表示したいセルには以下の数式を入力します。
=VLOOKUP(A2,社員一覧!A:B,2,FALSE)
この数式では、A2の番号を社員一覧シートのA列から探し、対応するB列の名前を表示します。番号を貼り付けるだけで自動的に名前へ変換されるため、集計作業の手間を減らせます。
XLOOKUP関数ならさらに簡単に設定できる
Microsoft 365やExcel 2021以降を利用している場合は、VLOOKUPよりもXLOOKUP関数がおすすめです。
XLOOKUPでは検索する列と表示する列を自由に指定できるため、表の位置変更にも強いというメリットがあります。
例えば以下のような式になります。
=XLOOKUP(A2,社員一覧!A:A,社員一覧!B:B,"該当なし")
この場合、A2の番号を社員一覧のA列から検索し、見つかった場合はB列の名前を表示します。番号が存在しない場合は「該当なし」と表示できます。
貼り付けと同時に変換したい場合のおすすめ設定
毎回関数を入力する手間をなくしたい場合は、集計用シートにあらかじめ変換用の列を作成しておく方法がおすすめです。
例えば、A列に抽出データを貼り付け、B列に名前表示用のXLOOKUP関数を設定しておけば、A列へデータを貼るだけでB列に自動変換された名前が表示されます。
さらにExcelのテーブル機能を利用すると、新しい行を追加した場合でも数式が自動的にコピーされるため、大量データの処理にも向いています。
マスターデータを管理すると作業ミスを防げる
従業員番号と名前のような情報は、Excel内に直接入力するよりも「社員マスター」として別管理するほうが安全です。
例えば、退職者や新入社員が発生した場合でも、対応表だけ修正すればすべての集計表に反映できます。
反対に、置換機能で毎回手作業をすると、入力間違いや変換漏れが発生する可能性があります。特に数千件以上のデータを扱う場合は、関数による自動変換のほうが正確です。
関数以外で自動変換する方法
Excelでは関数以外にも、Power Queryを利用してデータ取り込み時に変換処理を行う方法があります。
定期的に同じ形式のデータを取り込む業務では、Power Queryで番号変換の処理を一度設定しておくと、次回以降は更新ボタンだけで同じ処理を実行できます。
毎月の集計業務や大量のCSVデータ処理などでは、Power Queryを利用するとさらに効率化できます。
まとめ：Excelの自動変換は置換より対応表＋関数がおすすめ
Excelで従業員番号を名前へ変換する場合、置換機能を毎回使うよりも、番号と名前の対応表を作成してVLOOKUPやXLOOKUPで参照する方法がおすすめです。
一度仕組みを作れば、データを貼り付けるだけで自動的に名前へ変換できるため、集計作業の時間短縮や入力ミス防止につながります。
少量のデータなら置換でも対応できますが、継続的に利用する集計表では、Excelの関数やマスターデータ管理による自動化を取り入れることで、より快適に作業できます。
コメント