人事業務効率化お役立ちツール:Excel 「項目別にシート分割」

仕事効率化

こんにちは、管理人のtsenです。

私は10年以上人事業務を担当しています。

人事業務では単純な繰り返し作業が多く、
業務効率化のために、自分が開発したExcelマクロ(VBA)を公開したところ

「うちの部署の業務でも使いたい!」
「是非Excelマクロを教えてほしい!」

と、多くの需要があることを知り、

より多くの人の役に立てるのではないかとこのサイトを立ち上げました。

現在では、社内で様々な業務効率化ツールの開発を手掛けています。

今回はその中から、人事業務に特に多い

「データを特定の項目で分割する作業」を効率化する具体的な方法

について解説していきます。

スポンサーリンク

人事業務の「データ分割作業」を効率化しよう

人事業務の中でよくある作業の中に

・システムから落としたデータを各課へ分割して配布する
・一覧データを職場別に分割して棚卸作業をする


といったような、

「データを特定の項目で分割する」という作業があります。

オートフィルタで該当の項目を抜き出して別シートにコピペするだけなので

作業自体は難しくないのですが、

分割しなければいけない項目が大量にあると手作業で行うのは非常に大変です。

Aさん
Aさん

一覧データを職場別に分割するいい方法はないかな?

tSen
tSen

Excelマクロを使えば、一瞬で分割できますよ!

スポンサーリンク

このページを読むと効率化できる業務内容

例えば以下のようなExcel業務を効率化することが出来ます。

データを特定の項目で分割する作業イメージ
スポンサーリンク

人事業務を効率化するための事前準備

初めてマクロを立ち上げる方はまず、開発タブを表示させる必要があります。

ファイル>オプション>リボンユーザーの設定>開発にチェック

これで、開発タブが表示されます。

開発タブが開いたら
開発タブ>Visual Basic>挿入>標準モジュールで表示します。

標準モジュールに以下のコードを入れてください。

Sub 項目分割()
  Dim ws As Worksheet
  Dim 該当列 As Integer
  Dim 項目名 As String
  Dim 項目場所 As Range
  Dim シート数 As Variant
  Dim i As Integer
 Set ws = Sheets("Sheet1") 

 Do
   項目名 = InputBox("項目名を入力してください")
   If 項目名 = "" Then Exit Sub

   Set 項目場所 = ws.Range("B1:CM1").Find(項目名, LookAt:=xlWhole)
   If Not 項目場所 Is Nothing Then Exit Do
   MsgBox "項目名に[" & 項目名 & "]が見つかりません。"

 Loop
  
  If MsgBox("[" & 項目場所.Value & "]で分けますか?", vbYesNo) <> vbYes Then Exit Sub
  
  該当列 = 項目場所.Column
 Range("CN:CN").Insert
  ws.Columns(該当列).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=ws.Range("CN1"), Unique:=True
 シート数 = ws.Range("CN1", ws.Range("CN" & Rows.Count).End(xlUp))
  ws.Range("CN:CN").Delete


For i = UBound(シート数) To 2 Step -1
  Sheets.Add After:=ws
  ActiveSheet.Name = シート数(i, 1)
  ws.Range("A:CM").AutoFilter field:=該当列, Criteria1:=シート数(i, 1)
  ws.AutoFilter.Range.Copy ActiveSheet.Range("A1")
Next

   ws.AutoFilterMode = False
  
  End Sub

ここが難しい方は、超初心者向け「初めてのマクロ」を参考にしてください。

スポンサーリンク

データを特定の項目で分割するマクロコード解説

では、Excelマクロコード解説をしていきます。

tsen
tsen

コードの意味を理解しないと「こういう場合はどうすればいいの?」等、応用が出来ません。ざっくりと何をしているのかを理解しておきましょう!
Excelマクロが使えるようになると人事業務が劇的に効率化できる可能性がありますよ!

Excelマクロ:第1ブロック解説

Sub 項目分割()
  Dim ws As Worksheet
  Dim 該当列 As Integer
  Dim 項目名 As String
  Dim 項目場所 As Range
  Dim シート数 As Variant
  Dim i As Integer

Sub 項目分割() = 単純にマクロのタイトル(名前)です。

Dim ws As Worksheetとは、いったい何を意味しているんでしょうか?

マクロというものは最初に
「この文字は”こういう意味だよ”」と定義を宣言をする必要があります。

Dim ws As Worksheet「これから出てくる”ws”っていうのはWorksheet(ワークシート)のことだよ」
とコンピュータに教えてあげているんです。
宣言をしないでいきなりコンピュータに「wsを開いて!」と指示しても

PC
PC

wsとはなんですか?ワイドショーですか?
私はワイドショーは見ません!

と困ってしまうからです。
なので、最初に”ws”っていうのはWorksheet(ワークシート)のこと
と教えてあげているんです。
同様に

Dim 該当列 As Integer =”該当列”というのは「数字(Integer)」のことだよ
Dim 項目名 As String =”項目名”というのは「String(文字列)」だよ
Dim 項目場所 As Range =”項目場所”と言ったら「セル(Range)」のことだよ
Dim シート数 As Variant =”シート数”は「すべてのデータ(Variant)」を表すよ
Dim i As Integer =”i”っては「数字(Integer)」のこと

と、最初に定義を教えています。

Set ws = Sheets(“Sheet1”) というのは
”ws”にはSheet1をセットするよという意味です。

Excelマクロ:第2ブロック解説

 Do
   項目名 = InputBox("項目名を入力してください")
   If 項目名 = "" Then Exit Sub

   Set 項目場所 = ws.Range("B1:CM1").Find(項目名, LookAt:=xlWhole)
   If Not 項目場所 Is Nothing Then Exit Do
   MsgBox "項目名に[" & 項目名 & "]が見つかりません。"

 Loop

この部分は何をしているかというと、
このExcelマクロを起動すると、最初にこんな入力ボックスが出てくるようになっています。

入力ボックス
入力ボックス

この入力ボックスの処理を指示しているコードになります。
ここで、ポイントです。

「Do ~Loop」は繰り返しを表します。
DoからLoopまでの間に書かれたコードを繰り返し処理します。
ループを抜けるときは「Exit」で繰り返しを終了します。

では、具体的に処理内容を見ていきましょう。

項目名 = InputBox(“項目名を入力してください”)
If 項目名 = “” Then Exit Sub

この部分の意味は

「項目名を入力してください」という入力ボックスを出して、
入力された文字列を”項目名”とします。
もし、”項目名”が空欄(””だったら、マクロ(Sub)をそこで終了(Exit)します。

という意味です。
入力が空欄でなければ次のコードへ進みます。↓

 Set 項目場所 = ws.Range(“B1:CM1”).Find(項目名, LookAt:=xlWhole)
 If Not 項目場所 Is Nothing Then Exit Do
 MsgBox “項目名に[” & 項目名 & “]が見つかりません。”


シート1(ws)のセルB1からCM1の中からs(入力された項目)を見つけます。
見つけた文字が入っているセルの場所をrngとします。
もし、rngがなかった場合は、Do~Loopの処理を終了(Exit Do)します。
その時に「項目名が見つかりません」というメッセージボックスを出します。

という意味です。

項目名が見つかれば、次の処理へ進みます。

  If MsgBox("[" & 項目場所.Value & "]で分けますか?", vbYesNo) <> vbYes Then Exit Sub

「指定した項目」で分けますか?というメッセージ(MsgBox)と
「はい」「いいえ」(vbYesNo)というボタンを出します。
もし、「はい」以外のボタン(<> vbYes)が押されたら
マクロを終了(Exit Sub)します。

実際の画面としてはこんな感じになります。

メッセージボックス
メッセージボックス

「はい」が押されると次の処理に進みます。

Excelマクロ:第3ブロック解説

Aさん
Aさん

第2ブロックまででは、分割したい項目を指定しただけですよね?
ここからどういう処理をするんでしょうか?

tSen<br>
tSen

コード解説の前に、どんな処理をしているか
まずはイメージをしてみましょう!!

ここからの作業イメージ

最終的に、項目名別にシートを分けたいわけですから、
まず、
項目の種類と数を調べる必要がありますよね?

今回の事例だと部門名は3種類
今回の事例だと部門名は3種類

今回の事例だと、
「A部門」「B部門」「C部門」が実際の項目の種類です。
そして、数は、3種類あります。
これを調べるときに、普通ならどうやって調べますか?
所属の列をオートフィルタで確認する方法を思いつくはずです。

オートフィルタ
オートフィルタ

こうすると、種類と数を確認できますが
種類が多いと、確認は難しくなりますし、
確認できたとしても、コピペが出来なません。

しかし、エクセルマクロでは
データリストから必要なデータを抽出することが出来るんです。
それを、今回はCN列に書き出します。
つまりこういうことです。

CN列に項目名を抽出
CN列に項目名を抽出

そしたら、
この抽出されたデータを確認すれば
追加が必要なシート数は3つ、
それぞれのシート名は「A部門」「B部門」「C部門」
であるとわかることになります。

実際のコードを見てみよう!

該当列 = 項目場所.Column
Range("CN:CN").Insert
ws.Columns(該当列).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=ws.Range("CN1"), Unique:=True

ここは少し難しいのでザックリと説明すると
分割した項目名のある列(今回だとB列)をオートフィルタにして
データリストをCN列にコピペします。

 シート数 = ws.Range("CN1", ws.Range("CN" & Rows.Count).End(xlUp))
  ws.Range("CN:CN").Delete

コピペされたCN列をみて、何行あるのかカウントして”シート数”とします。
ここまでやれば、もうCN列は用済みなので消します。(Delete)

Aさん
Aさん

つまり、ブロックではB列の「所属」をオートフィルタして
CN列に抽出し、CN列を見て
シート数とシート名を認識しているということだね。

Excelマクロ:第3ブロック解説

ここまでくれば、あとは
一覧データから所属別にオートフィルタとコピペをひたすら繰り返す作業をするだけです。

For i = UBound(シート数) To 2 Step -1
  Sheets.Add After:=ws
  ActiveSheet.Name = シート数(i, 1)
  ws.Range("A:CM").AutoFilter field:=該当列, Criteria1:=シート数(i, 1)
  ws.AutoFilter.Range.Copy ActiveSheet.Range("A1")
Next

人事業務効率化のお役立ちツールを無料公開中

今回は、人事業務効率化お役立ちツールとして

「データを特定の項目で分割する作業」を効率化する具体的な方法について

解説してきました。

このサイトでは、人事業務の効率化に役立つツールをたくさん紹介しています。

主にExcelマクロを使った無料で導入できるツールになりますので是非取り入れてみてください。

tsen
tsen

ここまで読んでいただきありがとうございました。

タイトルとURLをコピーしました