有給休暇を５日以上取得が義務化され、人事でも社員に取得フォローしなければならなくなりました。

しかし、システムから取得したデータは以下のように人ごとに複数行ある、いわゆる縦持ちのデータになっています。

ということで、システムから出力した縦持ちのデータを横持ちのデータに変更するテクニックを紹介します。

今回は、エクセルの関数を使ったテクニックとなります。

 

縦持ちのデータから横持ちに変換する実例

以下の図の左のような縦持ちのデータから、右の表のような横持ちのデータにしたいということになります。大野くんは有給休暇５日とってますね。

 

縦持ちのデータ

横持ちのデータ（この状態にしたい）

 

ここですでにピンと来ている人はなかなかのExcel信者ですねえ。やりますねえ。

 

作業手順

１．元の表に連番の列を追加する

最初からかなり重要な手順です。

ここでは、D列に連番を追記しています。

 

各人ごとに連番を振っていくということですが、こちらは数式を使いましょう。

 

この数式は、一つ前のIDと現在の列のIDを比べて、一致していなければ、「１」一致している場合は、一つ前のD列の値＋１ するという数式になります。

 

これで、IDが連続する場合の連番を振ることができます。

 

とてもよく使う数式ですので、原理を覚えていただければと思います。

 

２．元の表に連番＋IDのKeyを作成する

列を挿入して、連番＋IDのユニークなKeyの列を作成しました。

 

結論から言うと、最終的にはVlookupを用いるのですが、Vlookup関数で用いる列を差し込んだというのが手順２です。

 

３．別のセルに対象者を入力する

いよいよ、Outputとなる表を作成します。

 

まずは、IDから対象者の情報を別のセルに入力します。

 

いちいち、重複する行を削除していたら大変なことになりますので、ここでは、Excelの機能「重複の削除」を使用します。

 

まず、対象者をまるっと貼り付けます。

 

コピーしたIDと氏名をすべて選択します。

 

次に、データタブの「重複の削除」をクリックします。

 

以下のようなダイアログが表示されますので、「先頭行をデータの見出しとして使用する」にチェックをし、列「ID」にチェックして、OKをクリックします。このチェックは表の中のユニークなキーを選択する必要があります。

 

すると、選択された範囲の中から重複する値のみが削除されます。

 

さて、次の手順に移ります。

 

４．ヘッダーに連番を入力する

いよいよ、Outputとなる表を作成します。

 

ヘッダーに１から始まる連番を入力します。入力の仕方はなんでもよいですが、オートフィルで連続したデータを入力するのが良いですかね。

 

５．Vlookup関数で元のデータから日付を取得する

一番肝の部分です！あとは、以下の矢印のように、連番とIDが対応している部分にデータを入力するだけなのですが、ここでVlookup関数を用います。

 

さて、どのような関数を使うかというと・・・

 

上記のような関数になります。ヘッダーの連番とIDを用いて、Keyと一致する検索値を作り出します。あとは、通常通り、Vlookupするだけです。

 

ここで、二点注意です。

①セルの書式を変更しないとシリアル値（44321 みたいな）となってしまいます。

ということで、セルの書式を日付に変更することを忘れずに！

 

②参照設定（$）を忘れないようにしてください

このまま設定したセルをコピーすると、すべてエラーとなってしまいました。

 

これは、前述したVlookup関数の設定の際に、参照（$）をしていなかったことに起因しています。

連番とIDをKeyとしたいのに、ずれていることがわかるかと思います。

 

さて、そうならないために、元々の数式に参照を設定します。

元の数式

参照を設定した数式

 

J3 は J$3 に、H4 は $H4 となりました。コピーした際に、縦・横にあえて動かさないという設定になっています。以下の動画でもご確認ください。

 

６．IFERROR関数でエラー回避する

ほぼ、完成ですが、このままコピーしてしまうと、以下の画像のようにエラーが発生してしまいます。

こういう時に便利なのが、IFERROR関数です。

 

Vlookup関数の外側に、=IFERROR([Vlookup関数],””) と設定しています。

 

これで、Vlookup関数がエラーの場合は、ブランクを表示する。ということが可能になりました。

 

さて、これですべてのセルに貼り付けてみると・・・

 

とってもきれいな状態になりました！

 

さいごに

Vlookup関数の変わった使い方って感じですね。覚えておくとどこかで使えるはずですよー！

 

ぜひ試してみてください！