Excelでつくる「SmartHR データコンバータ」のレシピ！

近年、クラウドサービスが増えたおかげで手軽に色々な業務を効率化できて便利ですよね。

一方で、様々なツールを利用していると、そのシステムそれぞれに同じ情報を入力するのはメンドウですし、避けたいところ。最近では、異なるシステム間でもボタン1つでデータ連携ができるものが増えてきてはいますが、インストールして利用するシステムではこうした便利な機能が使えません。

そこで、異なるシステム間でデータをやりとりするために「Excel」や「CSV」を利用するわけですが、システムによってデータの持ち方が異なるためデータの変換が必要です

「毎回手作業で変えるのはメンドウだし、一括で変換できたらなぁ……」

SmartHRをご利用中の方のなかにも、このようなお悩みを持つ方がいらっしゃるかと思います。

そこで今回は、SmartHRをご利用の皆さまにむけ、このお悩みを解決する変換ツール「データコンバータ」の作り方レシピをご紹介します！

「SmartHR データコンバータ」とは？

作るもの

本日ご紹介するのは、『Excelでつくる！ SmartHR データコンバータ』です！

毎日使っても飽きないし、季節を問わず使えるとても万能なツールですね。

利用シーンとしては、SmartHRに入った従業員情報を他システムに連携したい場合を想定します。

それでは、早速つくっていきましょう！

材料

• Microsoft Excel （2010以降）
• Microsoft Excelが使えるPC
• SmartHR
• データ連携したいシステム

「SmartHR データコンバータ」のレシピ

（1）まず、Excelを用意してシートを3つ作ります

• シート① 出力データ貼り付け
• シート② 変換後シート
• シート③ 変換マスタ

（2）データ連携したいシステムの取込レイアウトをシート②に書き出します

次に、データ連携したいシステムを用意します。

データ連携したいシステムには多くの場合『従業員情報取込フォーマット』が存在します。

そのデータ形式を確認しながら、シート②に記入していきます。

＼ ポイント ／

• データ連携したいシステムによっては、『従業員基本情報』『従業員住所情報』『従業員家族情報』など、情報グループごとに取込フォーマットが分かれている場合もあります
• その場合は、まず『従業員基本情報』から作成してみて、そのあと他のフォーマットを作成すると良いですよ！

（3）シート①に、SmartHRからExcelデータを出力

（2）で確認した取込フォーマットのレイアウトに近い「カスタムダウンロードフォーマット」を作成して保存しておくと便利です

SmartHRヘルプセンターの「従業員リストのダウンロード方法」のとおり、データをダウンロードします。

（4）シート①に（3）で出力したExcelデータを貼り付けます

（5）シート②とシート③に関数と変換マスタを入力します

パターンA

まず、データをそのまま取り込める項目。

この場合は、データを反映させたいセルを選択し『=』を入力したあと、シート①の当該セルを選択するだけです。

例：

※ こちらの関数をコピーする場合の注意点！
「①出力データ貼り付け!C2」の「①出力データ貼り付け」はシート名を意味するため、完全に一致させる必要があります。

パターンB

ヨミガナを全角から半角に変換する場合は、全角を半角にするASC関数が便利！

データを反映させたいセルに『=ASC()』と入力しシート①からセルを選択します。

例：

パターンC

男性を「1」、女性を「2」にするなど、所定の形式に変換が必要なものはシート③と、任意の検索値からアウトプットしたいデータを取得するVLOOKUP関数を使います

まず、シート③に『変換前のデータ』と『変換後のデータ』を並べて記入します。

次に、シート③のfx欄に『=VLOOKUP()』と入力し、必要な情報を選択・入力します。

※最後の0（ゼロ）は『完全一致』を表すもので、 FALSEでもOKです！

例：

または

＼ ポイント ／

• 関数を入力したセルには、わかりやすいように文字色をつけておくのがオススメです！（完成品参照）
• VLOOKUP関数は、検索値がない場合のエラー値処理のためにIFERROR関数等を組み合わせて使用されることも多いのですが、今回の場合エラー値が出るのは元のデータが間違っている可能性があり、その気づきになるため、あえてVLOOKUP関数のみで使用するのがオススメです！

（6）全てに関数を入力し終えたら、全体へコピー

シート②3行目に関数を全て入力したら、4行目以降にコピーします。

（7）シート②の完成データをチェックし、CSVとして出力！

＼ ポイント ／

• A列や1,2行目は不要な場合が多いので、以下の手順でCSV化することをオススメします！

1. 『名前をつけて保存』でExcelファイルとして名前をつけて保存（例：SmartHRデータ変換ツール_20190212.xlsx）
2. 保存したExcelファイル（SmartHRデータ変換ツール_20190212.xlsx）を開き、シート②で不要な行や列（A列および1,2行目）を削除
3. 『名前をつけて保存』でCSVファイルとして保存

※この手順で保存することのメリットは、Excelファイルに元の関数などが保存されているので、もし取り込みがエラーになった場合に修正が楽です。

（8）作成したCSVファイルをデータ連携したいシステムに取り込み

（7）で作成したCSVファイルをデータ連携したいシステムに取り込みましょう！

万が一エラーが出てしまったら、データ連携したいシステムから出力される取込エラー内容を見ながら、手順 （5）（6）（7）を繰り返してみましょう。

はじめは大変ですが、一度作ってしまえばこっちのものです！

よくあるエラー

「その社員は存在しません」といったエラーになる

CSVあるあるのゼロ落ちが発生している可能性があります。

例えばCSVに変換する際、Excel上では社員番号が『001』と表示されていても、CSVに変換すると『1』になってしまう現象です。

そのため、そのまま取り込むと「あれ？ そんな社員番号の人いませんよ？」とエラーになったり、異なる社員番号で社員情報が作成されたりしてしまうのです。

回避するには、まずExcelにで0（ゼロ）から始まるデータのセルを『文字列』として設定しましょう。

ちなみに、（7）で作成したCSVを開いてしまうとこのゼロ落ちが発生してしまいます。必ず、Excel版で修正したものをCSV形式で保存し、その後はCSVファイルを開かないようにしましょう！

「取込件数0件」や「カラム(列)が一致しません」といったエラーになる

これもあるあるです。

まず、Excelにてデータ取込範囲以外の空の行や列を数行削除しましょう。

そして再度CSVファイルで保存して取り込みます。

レシピを便利にするExcel関数たち

このレシピを更に便利にするExcel関数をいくつかご紹介します！

VLOOKUP 関数

マスタ化できるようなデータを変換するのに便利な関数です。

よくある使い方：性別や雇用契約、役職などの変換

例：

TEXT 関数

桁数を指定したい時や適した表示形式に指定したいときに使う関数です。

例えば社員番号は5桁にしたい場合はTEXT( [参照データ] , “00000” ) ←ゼロ5つ と指定します 。

例：A1セルが『1』の場合

→『1』が『00001』に変換されます。

そのほか、スラッシュを使った年月日データを『20190401』のようにスラッシュのない形式にする場合もTEXT関数が便利です。

例：A1に『2019/4/1』が入っている場合

→『2019/4/1』が『20190401』に変換されます。

ASC関数 ／ JIS関数

全角の値を半角にするのがASC関数、半角の値を全角にするのがJIS関数です。

例えばカナ文字などを半角でしか取り込めない場合や、住所などが全角でしか取り込めない場合などに便利です。

例：A1が『スマートエイチアール』の場合

→『スマートエイチアール』が『ｽﾏｰﾄｴｲﾁｱｰﾙ』に変換されます。

例：A1が『ｽﾏｰﾄｴｲﾁｱｰﾙ』の場合

→『ｽﾏｰﾄｴｲﾁｱｰﾙ』が『スマートエイチアール』に変換されます。

& （文字列の結合）

地味ですが、異なるセルにある文字列と文字列を結合する時に使う関数です。

例えば、分かれている住所をまとめてひとつにしたり、姓と名の間もスペースを入れてひとつの文字列にしたりというように活用できます。

例：A1が『髙森』B1が『静香』の場合

→『髙森』と『静香』が『髙森静香』に結合して変換されます。

なお、姓名の間にスペースを入れたい場合は、下記のように入力しましょう！

→『髙森』と『静香』が『髙森 静香』に結合して変換されます。

LEFT関数 ／ MID関数 ／ RIGHT関数

セルの値の「左数桁だけ」「真ん中数桁だけ」「右数桁だけ」を切り出すための関数です

例えば結合された住所の都道府県だけを切り出すというような場合に使います

一方、都道府県の場合は3桁だったり5桁だったりと可変になるので、IF関数（この場合はこうする、という条件式）やLEN関数（文字数を指定する）を活用した少し複雑な式を組むことがあります

複雑なのでここでは言及しませんが、気になった方はぜひブラウザで検索してみてください！

おわりに

今回は、SmartHRをご利用の皆さまにむけ、「Excelでつくる！ SmartHRデータコンバータ」をご紹介しました。

慣れるまでは大変なExcel。でも変換や、システムへの取り込みがうまくいった時の感動はひとしおです！

インターネットで検索すれば、Excelの使い方や便利な関数について、より詳しい解説がたくさん出てきますので、ぜひこれを機に習得してみてください。