1.今日現在の社員名簿が欲しい
私が初めて総務担当になったとき、今日現在の社員名簿がありませんでした。月に1回(月末)、社員名簿を作成することになっていたので、月末の社員名簿はありましたが、今日現在の社員名簿となると、月末から今日までの入社、退職、異動、昇格等、人の動きを落とし込む作業が必要になります。また、毎月末にきちんと名簿を作成していればいいけど、他の仕事が立て込んでいると社員名簿の更新作業を後回しにしてしまいがちです。それで、数カ月放置してしまって、数か月分をまとめて更新するなんてことも珍しくありませんでした。
ボタン1つで、社員名簿が更新されたら・・・。私がExcelマクロ(VBA)を始めたのは、社員名簿の更新作業が面倒だったからです。 その後、会社も人事システムを導入し、今日現在の社員名簿を作る必要はなくなりましたが、人事考課の評定や昇給、賞与の資料等、人事システムに入力する項目を決定する資料を作成するとき等に、Excelマクロ(VBA)で作成した社員名簿は今でも大活躍しています。
2.やりたいこと
Excelファイルを開いたら、自動的に今日現在の社員名簿(部署、役職、年齢、勤続年数、住所、電話番号、メールアドレス、学歴等)が出力されるようにしたい。
3.社員名簿の設計図
社員名簿を作成するためには、たくさんのシートを連携させることになるので簡単に整理しておきます。
まずは、組織関係のシートとして、①組織マスターシート、②組織データベースシート、③現在の組織(出力用)シートの3つ。
![](https://kuroneconote.com/wp-content/uploads/2021/06/21db5d7a7f3e56e28216c6a20d0e4fc8-800x372.jpg)
![](https://kuroneconote.com/wp-content/uploads/2021/06/46785b1249c142910c8e9f71b84bb07c-800x371.jpg)
![](https://kuroneconote.com/wp-content/uploads/2021/06/69a83cd47a682ea132c2d0910373d8cf-800x371.jpg)
次に、名簿関係のシートとして、①社員基本情報(マスター)シート、②異動データベースシート、③現在の社員名簿(出力用)シートの3つ
![](https://kuroneconote.com/wp-content/uploads/2021/06/ea85edc308dbd5e27994222da7ce0241-800x371.jpg)
![](https://kuroneconote.com/wp-content/uploads/2021/06/5f2d2840a53c98395c75f357f4c49e67-800x372.jpg)
![](https://kuroneconote.com/wp-content/uploads/2021/06/7d42e768f490003ce424fd1f73d1737b-800x371.jpg)
その他、メニュー用のシート、検索用に使用するシートの2つ。合計8つのシートを作成します。
![](https://kuroneconote.com/wp-content/uploads/2021/06/2c8cbcc8a5ca3a78ab2bd6d55987e76d-800x372.jpg)
更に、社員名簿の出力条件を抽出用フォームと社員情報の変更用フォームをユーザーフォームで作成していきます。
![](https://kuroneconote.com/wp-content/uploads/2021/06/00ef9789fadd1665153a39954afba1e1-800x439.jpg)
![](http://www.image-rentracks.com/dt30/phpschool/336_280.jpg)
4.Excelマクロ有効ブックを新規作成する
(1)デスクトップ上にExcelブックを新規作成する
それでは早速作っていきます。
①デスクトップ上にExcelワークブックを新規作成します。
デスクトップ上で右クリックして、「新規作成」→「Microsoft Excel Worksheet」を選択します。
![](https://kuroneconote.com/wp-content/uploads/2021/06/2f73f8eca1ceac34e26b1dd41bfa85b2-800x481.jpg)
②新規作成したExcelブック(.xlsx)をマクロ有効ブック(.xlsm)として名前を付けて保存する
ⅰ 新規作成したExcelワークブックを開きます
ⅱ ファイル→名前を付けて保存→デスクトップを選択
![](https://kuroneconote.com/wp-content/uploads/2021/06/fba526001e98420ac7ecd0afc7c9fff2-800x396.jpg)
ⅲ ファイル名を「人事システム」と入力し、ファイルの種類で「Excelマクロ有効ブック」を選択し、保存ボタンをクリックします。
![](https://kuroneconote.com/wp-content/uploads/2021/06/42a624cfabef16b899b81fd6a0ee90e5-1.jpg)
これでマクロ有効ブックが新規作成できました。次は組織用のシートを作成します。
![](http://www.image-rentracks.com/web/car_468-60_20090312.gif)
5.今日現在の組織一覧を出力する
(1)組織マスターシートの作成
①新規シートを作成する
シートタグの横にある「⊕」ボタンをクリックして新しいシートを作成します。
②シート名を「組織マスター」に変更する。
![](https://kuroneconote.com/wp-content/uploads/2021/06/b6542d80d38ca863d7476c8edbaf87e4.jpg)
③組織(4階層)を抽出しそれぞれに2桁のコード番号を付ける
「組織マスター」シートの1行目のA列~H列までに部署名とコード項目を4階層分作成し、現在の組織を階層別に抽出します。
抽出した各階層の組織名に対応するコードを決めます。このとき組織名の変更時、現在の組織と組織の間に新しい組織を入れることができるように、組織間の数字に余裕をもつようにします。
![](https://kuroneconote.com/wp-content/uploads/2021/06/3e3dbed552be4dfc8f2bac6f34822fb5-800x426.jpg)
以上で、組織マスターシートは完成です。次に、組織データベースシートを作成します。
(2)組織DBシートの作成
①新規シートを作成する
シートタグの横にある「⊕」ボタンをクリックして新しいシートを作成します。
②シート名を「組織DB」に変更する。
![](https://kuroneconote.com/wp-content/uploads/2021/06/e7a63e5c0e19c8865dd089154a13a170.jpg)
③1行目:A列からF列の見出しを入力
組織マスターシートで抽出した部署名の項目を見出し欄に入力します。
![](https://kuroneconote.com/wp-content/uploads/2021/06/8fd4e13b2096bc5558d161901a7d4da7-800x195.jpg)
④A列は、きりの良い直近の日付を入力します。B列は空欄のままにしておきます。
⑤C~F列には、現在存在する組織を全て順番に羅列していく
![](https://kuroneconote.com/wp-content/uploads/2021/06/8afdfcaa686723595d496ca7833bdc4c-800x523.jpg)
以上で組織データベースシートは完成です。次は組織の出力用シートを作成します。
(3)現在の組織シート(出力)の作成
①新規シートを作成する
②シート名を「現在の組織」に変更する
![](https://kuroneconote.com/wp-content/uploads/2021/06/b4737e10872a433cbe2b82787bd7c7af.jpg)
③1行目(A~F)はタイトルを入れるのでセルを結合しておく
![](https://kuroneconote.com/wp-content/uploads/2021/06/92f5653b3e9b87d9a805285182880c51-800x366.jpg)
④2行目(A~E)の見出しを入力する
![](https://kuroneconote.com/wp-content/uploads/2021/06/a33be62ceae9266c70e2bd507184c1d1.jpg)
(4)現在組織リストを出力するVBAコードを書く
①標準モジュールを開く
ⅰ 「開発」タグ → 「Visual Basic」をクリックする。
![](https://kuroneconote.com/wp-content/uploads/2021/06/a3f1bceb668b812f65834a752da0fcc9.jpg)
ⅱ 「挿入」タグ → 「標準モジュール」をクリックする。
![](https://kuroneconote.com/wp-content/uploads/2021/06/57204a9d247b852bde555e9d68218479-800x275.jpg)
②標準モジュール(Module1)に下記コードを記入(コピペ)する
![](https://kuroneconote.com/wp-content/uploads/2021/06/03cc9f0c1bd1bcb9c1c0ec1cf6c4c205-800x738.jpg)
Sub sosikikosin(d As Date)
Dim today_d As Date, str_d As Date, end_d As Date
Dim honbu As String, bu As String, ka As String, kakari As String
Worksheets("現在の組織").Activate
n = Worksheets("現在の組織").Cells(Rows.Count, 1).End(xlUp).Row
If n > 2 Then
Worksheets("現在の組織").Range(Cells(3, 1), Cells(n, 6)).ClearContents
Worksheets("現在の組織").Range(Cells(3, 1), Cells(n, 6)).Borders.LineStyle = xlLineStyleNone
End If
For r = 2 To Worksheets("組織DB").Cells(Rows.Count, 1).End(xlUp).Row
With Worksheets("組織DB")
today_d = d
str_d = .Cells(r, 1)
end_d = .Cells(r, 2)
honbu = .Cells(r, 3)
bu = .Cells(r, 4)
ka = .Cells(r, 5)
kakari = .Cells(r, 6)
End With
If (str_d <= today_d And today_d <= end_d) Or (str_d <= today_d And end_d = 0) Then
With Worksheets("組織マスター")
Set rcd_honbu = .Range("a:a").Find(honbu, lookat:=xlWhole)
Set rcd_bu = .Range("c:c").Find(bu, lookat:=xlWhole)
Set rcd_ka = .Range("e:e").Find(ka, lookat:=xlWhole)
Set rcd_kakari = .Range("g:g").Find(kakari, lookat:=xlWhole)
code = rcd_honbu.Offset(0, 1) * 1000000 + rcd_bu.Offset(0, 1) * 10000 + rcd_ka.Offset(0, 1) * 100 + rcd_kakari.Offset(0, 1)
End With
With Worksheets("現在の組織")
n = .Cells(Rows.Count, 1).End(xlUp).Row + 1
.Cells(n, 1) = r
.Cells(n, 2) = honbu
.Cells(n, 3) = bu
.Cells(n, 4) = ka
.Cells(n, 5) = kakari
.Cells(n, 6) = code
End With
End If
Next r
n = Worksheets("現在の組織").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("現在の組織").Range("A2:f" & n).Sort _
Key1:=Range("f2"), Order1:=xlAscending, _
Header:=xlYes
Worksheets("現在の組織").Range("A2:f" & n).Borders.LineStyle = xlContinuous
Worksheets("現在の組織").Range("a1") = d & "現在組織リスト"
End Sub
③現在リストを出力するトリガーの設定
②のコードを実行するタイミングですが、ブックを開いたときに常に今日現在の組織リストを出力するように設定したいと思います。
ⅰ Thisworkbookモジュールを開く
Visual Basicエクスプローラーの「ThisWorkbook」をダブルクリックするとThisWorkbookモジュールが開きます。
![](https://kuroneconote.com/wp-content/uploads/2021/06/e8f34555da264d073227d23505ca091b-800x261.jpg)
ⅱ Thisworkbookモジュールに下記コードをコピペする
Private Sub Workbook_Open()
Call Module1.syokika
End Sub
ⅲ 標準モジュール(Module1)を開く
![](https://kuroneconote.com/wp-content/uploads/2021/06/cc6cac4bd062dad5f958da7af36eb7ff-800x324.jpg)
ⅳ 標準モジュール(Module1)に下記コードをコピペします。
![](https://kuroneconote.com/wp-content/uploads/2021/06/89e1efd3485a2b198b9a89842960ba0d-800x315.jpg)
Sub syokika()
Application.ScreenUpdating = False
Dim d As Date
d = Date
Call Module1.sosikikosin(d)
Application.ScreenUpdating = True
End Sub
ⅴ ブックを保存して閉じ、再び開くと、現在の組織シートが今日現在の日付で更新されます。
![](https://kuroneconote.com/wp-content/uploads/2021/06/aa454393ad944294bd56d83baa03a949-800x431.jpg)
6.任意の日の組織リストを出力する
①menuシートを作成する
「sheet1」のシート名「menu」に変更します。
![](https://kuroneconote.com/wp-content/uploads/2021/06/473838a3056c1843a11b349f6691ff93.jpg)
②標準モジュール(Module1)に下記コードをコピペします。
![](https://kuroneconote.com/wp-content/uploads/2021/06/c3d05a12eb608683d69afa93b7335a67-800x336.jpg)
Sub ninikosin()
Application.ScreenUpdating = False
Dim d As Date
d = InputBox("基準日を入力")
Call Module1.sosikikosin(d)
Dim TargetBook As Workbook
Worksheets("現在の組織").Copy
Set TargetBook = ActiveWorkbook
ThisWorkbook.Activate
d = Date
Call Module1.sosikikosin(d)
Worksheets("menu").Activate
TargetBook.Activate
Application.ScreenUpdating = True
End Sub
③menuシートの「開発」タグ → 挿入 → フォームコントロールボタンを選択する
![](https://kuroneconote.com/wp-content/uploads/2021/06/0c7c839ebd45269cd34f0b3db9504fcc.jpg)
④カーソルが「+」に変わるのを確認して、menuシート上でドラッグすると「ボタン」が描ける
⑤適当な大きさでマウスボタンを離すと「マクロ登録」のダイヤログになるので、「ninikosin」を選んで、「OK」ボタンをクリックする。
![](https://kuroneconote.com/wp-content/uploads/2021/06/cfe3c31d9dd28d00d8ea81aa2fe81e9b.jpg)
⑥ボタンをクリックすると「基準日を入力」のダイヤログが開くので組織リストの基準日(2021/6/1)を入力して「OK」ボタンをクリックする。
![](https://kuroneconote.com/wp-content/uploads/2021/06/9dd4c27e95e6ba167c7d065ea0dc3c41-800x196.jpg)
⑦任意の日(2021/6/1)の組織リストが出力されます
![](https://kuroneconote.com/wp-content/uploads/2021/06/943d4dc46b0b5096c4ea4047f04df887-800x445.jpg)
尚、任意の日の組織リストは、「人事システム」とは別のファイルに作成します。また、ボタンの名前はボタン上で右クリックして、「テキストの編集」を選んで適当な名前に変更してください。
![](http://www.image-rentracks.com/dream-licence/468x60.jpg)
7.組織変更への対応
(1)組織変更への対応方法(やりたいこと)
組織の廃止と組織の新設に対応します。対応方法はいろいろあると思いますが、もっともシンプルに、「現在の組織」シートに出力された組織リストをたたき台として、特定組織の廃止と新設を実行したいと思います。
①組織の廃止
ⅰ 「現在の組織」シートの廃止したい組織を選択する(複数選択可)
例えば、6月10日付で「総務課 総務係」と「総務課 人事係」を廃止するとします。
![](https://kuroneconote.com/wp-content/uploads/2021/06/44795e044f61931b7fcc68fb60ab6ed7-800x510.jpg)
ⅱ 右クリックメニューを出し、「組織の廃止」を選択する
![](https://kuroneconote.com/wp-content/uploads/2021/06/0b2dbe90a4cb197ca43184de2e2a8fd0-800x508.jpg)
ⅲ 廃止日となる基準日(組織が存続する最終日)を入力して、OKボタンをクリックする
![](https://kuroneconote.com/wp-content/uploads/2021/06/e18eb0f3044e66a75fc81341a76047cf-800x422.jpg)
ⅳ 「組織DB」シートの該当する組織のB列に廃止日となる基準日が入力されています。
![](https://kuroneconote.com/wp-content/uploads/2021/06/6fdae2134138d23028a906610dca7646-800x580.jpg)
ⅴ 6月11日の組織リストを出力すると、「総務課 総務係」と「総務課 人事係」がリストから消えています。
![](https://kuroneconote.com/wp-content/uploads/2021/06/8dbe2ca0037c7aa57e4f77f17c629902-800x461.jpg)
②組織の新設
ⅰ「現在の組織」シートに新設したい組織を入力します(複数入力可)
例えば、6月11日付で「営業本部 営業企画部 総務課」と「生産本部 生産管理部 総務課」を新設します。
![](https://kuroneconote.com/wp-content/uploads/2021/06/27347b68213e199133b4c121c6c5eb8f-800x620.jpg)
ⅱ 右クリックメニューを開き、「組織の新設」を選択する
![](https://kuroneconote.com/wp-content/uploads/2021/06/49c8aeae65d2231ede839c3ac23c16bb-800x631.jpg)
ⅲ 新設する日となる基準日(組織が新設された日)を入力する
![](https://kuroneconote.com/wp-content/uploads/2021/06/54d371a5956193ff6f521cd849cd108f-800x472.jpg)
ⅳ 組織DBの最下行に新設された組織が入力されます。
![](https://kuroneconote.com/wp-content/uploads/2021/06/8934f948f49423af20acdc895da1388d-800x764.jpg)
ⅴ 6月11日の組織リストを出力すると、「営業本部 営業企画部 総務課」と「製造本部 生産管理部 総務課」がリストに追加されています。
![](https://kuroneconote.com/wp-content/uploads/2021/06/554b026432f929ff7c7e1d276902c94e-800x735.jpg)
以上の動作を実行するマクロコードを作成していきます。
![](http://www.image-rentracks.com/8624/250_250.jpg)
(2)右クリックメニューの追加
まずは右クリックメニューの中に「組織の廃止」と「組織の新設」メニューを追加します。
①「現在の組織」シートモジュールを開きます
ⅰ 開発タグ → Visual Basic → エクスプローラーの「現在の組織」をダブルクリックする
![](https://kuroneconote.com/wp-content/uploads/2021/06/c088d2a6405edb1456df7e21d12d3b11-800x464.jpg)
②「現在の組織」シートモジュールに下記コードを記入ください。
![](https://kuroneconote.com/wp-content/uploads/2021/06/fcc2202744bc544bca9ecdb4b6f55592-800x371.jpg)
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Dim cmdBar1 As CommandBarControl
For Each cmdBar1 In Application.CommandBars("cell").Controls
If cmdBar1.Caption = "組織の廃止" Or cmdBar1.Caption = "組織の新設" Then
cmdBar1.Delete
End If
Next
With Application.CommandBars("cell").Controls.Add
.Caption = "組織の廃止"
.OnAction = "org_abolish"
End With
With Application.CommandBars("cell").Controls.Add
.Caption = "組織の新設"
.OnAction = "org_built"
End With
End Sub
Private Sub Worksheet_Deactivate()
Dim cmdBar1 As CommandBarControl
For Each cmdBar1 In Application.CommandBars("cell").Controls
If cmdBar1.Caption = "組織の廃止" Or cmdBar1.Caption = "組織の新設" Then
cmdBar1.Delete
End If
Next
End Sub
「現在の組織」シートの上で右クリックすると、メニューの最下行に「組織の廃止」と「組織の新設」が表示されます。
![](http://www.image-rentracks.com/8136/200_200.png)
(3)組織の廃止を実行するコードを書く
次に組織の廃止を選択したときに実行するためのコードを作成します。
標準モジュール(Module1)に下記コードを記入ください。
![](https://kuroneconote.com/wp-content/uploads/2021/06/7_3-800x472.jpg)
Sub org_abolish()
Dim n As Range, end_d As Date, o As Long
end_d = InputBox("廃止日を入力")
For Each n In Selection
o = Worksheets("現在の組織").Cells(n.Row, 1)
If o <> 0 Then
Worksheets("組織DB").Cells(o, 2) = end_d
End If
Next n
Dim d As Date
d = Date
Call Module1.sosikikosin(d)
End Sub
(4)組織の新設を実行するコードを書く
次に組織の新設を選択したときに実行するためのコードを作成します。
標準モジュール(Module1)に下記コードを記入ください。
![](https://kuroneconote.com/wp-content/uploads/2021/06/7_4-800x537.jpg)
Sub org_built()
Dim n As Range, end_d As Date, honbu As String, bu As String, ka As String, kakari As String, m As Long, pre_row As Integer
str_d = InputBox("設置日を入力")
For Each n In Selection
If n.Row = pre_row Then GoTo 5
honbu = Worksheets("現在の組織").Cells(n.Row, 2)
Set rcd = Worksheets("組織マスター").Range("a:a").Find(honbu, lookat:=xlWhole)
If rcd Is Nothing Then
MsgBox honbu & "は、組織マスターに登録されていません。組織マスターの「本部」と「本部コード」を追加してください"
GoTo 10
End If
bu = Worksheets("現在の組織").Cells(n.Row, 3)
Set rcd = Worksheets("組織マスター").Range("c:c").Find(bu, lookat:=xlWhole)
If rcd Is Nothing Then
MsgBox bu & "は、組織マスターに登録されていません。組織マスターの「部」と「部コード」を追加してください"
GoTo 10
End If
ka = Worksheets("現在の組織").Cells(n.Row, 4)
Set rcd = Worksheets("組織マスター").Range("e:e").Find(ka, lookat:=xlWhole)
If rcd Is Nothing Then
MsgBox ka & "は、組織マスターに登録されていません。組織マスターの「課」と「課コード」を追加してください"
GoTo 10
End If
kakari = Worksheets("現在の組織").Cells(n.Row, 5)
Set rcd = Worksheets("組織マスター").Range("f:f").Find(kakari, lookat:=xlWhole)
If rcd Is Nothing Then
MsgBox kakari & "は、組織マスターに登録されていません。組織マスターの「係」と「係コード」を追加してください"
GoTo 10
End If
m = Worksheets("組織DB").Cells(Rows.Count, 1).End(xlUp).Row + 1
Worksheets("組織DB").Cells(m, 1) = str_d
Worksheets("組織DB").Cells(m, 3) = honbu
Worksheets("組織DB").Cells(m, 4) = bu
Worksheets("組織DB").Cells(m, 5) = ka
Worksheets("組織DB").Cells(m, 6) = kakari
pre_row = n.Row
5
Next n
Selection.EntireRow.Delete
Dim d As Date
d = Date
Call Module1.sosikikosin(d)
10
End Sub
![](http://www.image-rentracks.com/8061/468_60.jpg)
(5)新組織の組織マスター登録
組織の新設に際して、組織マスターに登録されていない組織を新設したときは、組織マスターに「組織名」と「組織コード」を登録してください。
例えば、6月11日付で研究開発部を新設する場合、「現在の組織」シートに「研究開発部」を追加して、右クリックメニューから「組織の新設」を選択した場合
![](https://kuroneconote.com/wp-content/uploads/2021/06/af000863cade2926086deaee3d36a336-800x653.jpg)
「組織マスター」に「研究開発部」が登録されていない為、次のようなメッセージが出ます。
![](https://kuroneconote.com/wp-content/uploads/2021/06/9057026521ff3e5ca522ed029f119862.jpg)
そこで、このような場合には、「組織マスター」の「本部名」に「研究開発部」、「本部コード」に「40」と入力して、もう一度右クリックメニューから「組織の新設」を選択してください。
![](https://kuroneconote.com/wp-content/uploads/2021/06/125d3246172227040843ae3638d796ed-800x475.jpg)
組織コードは、組織や社員名簿を並び替えたときに、意図した順番になるよう大小関係を注意して登録ください。
組織リストの出入力関係は以上です。次回は、社員名簿の出入力です。
![](http://www.image-rentracks.com/laetitien/canagancatfood-6963/300_250.jpg)
コメント