【Excel VBA】年度別にカウントする転記マクロについて

はじめまして(>_<)

以前、こちらで質問させていただいた転記マクロに
機能を追加することになりました。

以前の質問↓
https://oshiete.goo.ne.jp/qa/10695921.html

現在は、「元データ」シートの一覧から「人員増減一覧」を作成する機能があります。
条件：異動元部課コード、異動先部課コードにデータがあれば、それぞれ増加、減少にフラグを立てて転記する。

ここに、「元データ」シートから「部署別増減一覧」を作成する機能を追加したいです。
条件：部署別で、「元データ」T列の年度に応じて人員の増減をカウントアップする
（例：2018年上期、2018年下期、2019年上期、2019年下期でそれぞれカウントアップする）

【部署別増減一覧】　　　　　【元データ】
A列：所属　　　　　　　　←I列orl列
B列：【今年度上期】増加　←カウントアップした数
C列：【今年度下期】減少　←カウントアップした数
D列：【来年度上期】増加　←カウントアップした数
E列：【来年度下期】減少　←カウントアップした数

現在のコードは↓です。
＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
Sub ボタン1_Click()
'ワークエリア定義
Dim i As Long
Dim cnt As Long
Dim wS As Worksheet
'処理開始メッセージ
If MsgBox("人員増減一覧を作成します", vbYesNo + vbQuestion, "AssignRole") = vbNo Then
Exit Sub
End If
'シート作成
With Worksheets("元データ")
Set wS = Worksheets("人員増減一覧")
'表題設定
wS.Range("A1:I1").Value = Array("所属", "社員番号", "氏名", "発令日", "従業員区分", "キャリアバンド", "異動理由", "増加", "減少")
cnt = 1
'全行数実施する
For i = 2 To .Cells(Rows.Count, "A").End(xlUp).Row
'異動元有の場合
If .Cells(i, "I") <> "" Then
cnt = cnt + 1
'異動元情報の転記
wS.Cells(cnt, "A") = .Cells(i, "I")
'wS.Cells(cnt, "B").Resize(, 6).Value = .Cells(i, "A").Resize(, 6).Value
.Cells(i, "A").Resize(, 6).Copy wS.Cells(cnt, "B")
wS.Cells(cnt, "H") = 0
wS.Cells(cnt, "I") = 1
End If
'異動先有の場合
If .Cells(i, "L") <> "" Then
cnt = cnt + 1
'異動先情報の転記
wS.Cells(cnt, "A") = .Cells(i, "L")
.Cells(i, "A").Resize(, 6).Copy wS.Cells(cnt, "B")
wS.Cells(cnt, "H") = 1
wS.Cells(cnt, "I") = 0
End If
Next i
End With
'所属、発令日、社員番号の昇順でソート
wS.Range("A1").CurrentRegion.Sort _
key1:=wS.Range("A1"), order1:=xlAscending, _
key2:=wS.Range("D1"), order1:=xlAscending, _
key3:=wS.Range("B1"), order1:=xlAscending, _
Header:=xlYes
'終了時メッセージ
MsgBox "人員増減一覧作成が完了しました"
End Sub
＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
元データ、人員増減一覧、部署別増減一覧の各イメージは画像にて添付いたします。

Excel VBAにお詳しい方、どうぞよろしくお願いいたします。(>_<)
※処理説明してくださると助かります。

質問者からの補足コメント

• 人員増減一覧のサンプル

  
    補足日時：2018/09/11 11:52

• 部署別増減一覧のイメージ

  
    補足日時：2018/09/11 11:53

• Sub bu_sub()
  
  'ワークエリア定義
  Dim v As Long '人員増減一覧の行
  Dim cntz3 As Long '今期下期(増加)
  Dim cntg3 As Long '今期下期(減少)
  Dim cntz4 As Long '来期上期(増加)
  Dim cntg4 As Long '来期上期(減少)
  Dim cntz5 As Long '来期下期(増加)
  Dim cntg5 As Long '来期下期(減少)
  Dim cntz6 As Long '1Quarter(増加)
  Dim cntg6 As Long '1Quarter(減少)
  Dim cntz7 As Long '2Quarter(増加)
  Dim cntg7 As Long '2Quarter(減少)

    補足日時：2018/09/18 17:22

• 【入力】算出範囲指定イメージ

  
    補足日時：2018/09/19 17:02

• 【出力】結果イメージ
  
  ※現時点のものです。列B-Cに、今期上期の欄を追加予定です。
  ※上の画像の算出範囲についても、3列目に今期上期の欄を追加します。

  
    補足日時：2018/09/19 17:05

• 現時点でのコードを追記いたします。
  
  ①異動元、異動先毎に分解して転記し、増減を表示するマクロ
  https://ideone.com/e.js/2F8zAK
  
  ②①で作成した表より、半期、クウォーター(1年を4分割)毎に増減をカウントして表示するマクロ
  https://ideone.com/e.js/M2DvWc

    補足日時：2018/09/19 17:06

No.10 ベストアンサー 回答者： tatsu99 回答日時： 2018/09/26 10:15

すみません。

No8,No9の回答は無視してください。
こちらに修正版をアップしました。
https://ideone.com/IKABFn

この回答へのお礼

お世話になっております。
お礼が大変遅くなり申し訳ございません。

頂いたマクロを実行させて頂き、無事正常な結果が作成されました。
お時間を取っていただきありがとうございました。

現在は、今年度上期、下期、来年度上期、下期、1-4Quaterの8項目入力欄を設け、結果も同じ項目で表示させるように変更させていただきました。
ユーザーに入力される方法は手間を取らせてしまうことがあり、今後年度のみ入力させる方法も検討していきたいと思います。

一旦、こちらの質問は解決いたしましたのでクローズさせていただきます。
要件が固まっていないにも関わらず、丁寧にご回答いただきまして助かりました。
この度はありがとうございました。

お礼日時：2018/10/02 13:40

No.9 回答者： tatsu99 回答日時： 2018/09/25 23:55

>ご提示頂きました「ボタン_1Click」マクロに、

>https://ideone.com/SOCA2M
>bu_sub2のマクロを以下に差し替えて実行してみたのですが、
>コンパイルエラー「SubまたはFunctionが定義されていません」とのエラーが出てしまいました。
>差し替えず、当初のものですと上手くいきます。

https://ideone.com/SOCA2Mのマクロのどの行で、
「SubまたはFunctionが定義されていません」とのエラーがでていますか？
エラーの発生している行を教えてください。

No.8 回答者： tatsu99 回答日時： 2018/09/20 21:41

すみません。

No5アップしたSub bu_sub2()のほうですが、
0のセルは０を表示するのではなく、空白にしました。
＞＞＞部署別増減一覧のみ0は非表示としていました
ということですが、この必要もなくなります。
こちらを参照ください。
https://ideone.com/4Q61XH

No.7 回答者： tatsu99 回答日時： 2018/09/20 21:03

No6の続きです。

部署別増減一覧は、添付図のようになるかと思います。（赤線が今年度、青線が来年度）（来年度４Qの減少はY列）

この形が最終形となるような気がしますがいかがでしょうか。

No.6 回答者： tatsu99 回答日時： 2018/09/20 20:54

念のため確認なのですが、あなたの会社の年度の開始月は何月からでしょうか。

例えば、開始月が４月なら
4/1～6/30・・・1Quarter
7/1～9/30・・・2Quarter
10/1～12/31・・・3Quarter
翌年　1/1～3/31・・・4Quarter
のようになるかと思います。
1Quarterが10/1なので、年度の開始月が10月なのでしょうか。
それとも、今回だけ特別に1Quarterが10/1なのでしょうか。
又は、1Quarterは各部署で好き勝手に決めたいので、「実行マクロ」のシートで日付を利用者が自由に変えられるようにされたのでしょうか。

個人的には、年度の開始月は全社同じで、部署ごとにきめるようなものではないと考えています。
従って、「実行マクロ」のシートで記入できる箇所は本年度の４桁の数字のみ（例えば今年なら2018）だけが良い考えています。
（添付図の赤線で囲んだところ）
4月1日が年度の開始であれば
2018年度の場合は
今期
2018　4/1～6/30・・・1Quarter
2018　7/1～9/30・・・2Quarter
2018　10/1～12/31・・・3Quarter
2019　1/1～3/31・・・4Quarter
今期の上期　2018 4/1～9/30
今期の下期　2018 10/1～2019 3/31

来期
2019　4/1～6/30・・・1Quarter
2019　7/1～9/30・・・2Quarter
2019　10/1～12/31・・・3Quarter
2020　1/1～3/31・・・4Quarter
来期の上期　2019 4/1～9/30
来期の下期　2019 10/1～2020 3/31

上記の範囲は内部でその情報を抱えるべきで、マクロの利用者に変更させないほうが良いと考えます。

この回答へのお礼

＞念のため確認なのですが、あなたの会社の年度の開始月は何月からでしょうか。
おっしゃる通り、開始月は4月で、
4/1～6/30・・・1Quarter
7/1～9/30・・・2Quarter
10/1～12/31・・・3Quarter
翌年　1/1～3/31・・・4Quarter
で正しいです。
提示させて頂いた画像のデータが、サンプルで作成したもので実際とは異なっていました。
分かりづらく申し訳ありません。

確かに、年度の開始月が同じなのであれば、利用者に入力させる仕様はミスも出やすいですね。。
半期で確認する部署、Quarter毎に確認する部署があるようなので、
入力させるしかないと考えてしまっていました。

ただ、最終形としては

今年度上期：B-C列
今年度下期：D-E列
来年度上期：F-G列
来年度下期：H-I列
1Quarter：J-K列
2Quarter：L-M列
3Quarter：N-O列
4Quarter：P-Q列

の順が理想です。
(時期が前後してはしまいますが、半期orQuarterはどちらもで確認ではなく、どちらかで確認する為まとまっていた方が見やすいです)


知識が足りず、ご提示いただいたマクロが身になっていませんので、自分なりに調べて理解したいと思います。
ありがとうございました。

お礼日時：2018/09/25 22:46

No.5 回答者： tatsu99 回答日時： 2018/09/20 20:30

>「設定列」の概念は目から鱗なのですが、今回のように展開する列の位置がが決まっている場合、

>シート上に持っている設定列の値ををマクロ内のコンスタント値として定義することも可能でしょうか。
>(こちらのマクロは各社、各部の担当者が使用する為、設定列の数字が変更されてマクロが動かなくなる事態を避けたい為です)

マクロ内のコンスタント値として、定義する方法です。
また、データ作成後に、罫線を引いています。（"人員増減一覧"と”部署別増減一覧”の両方です）
こちらにアップしました。
https://ideone.com/SOCA2M

この回答へのお礼

いつもお世話になっております。
この度も、ご回答いただきましてありがとうございます。
また、連休の為お礼が遅くなり大変申し訳ございません。

ご提示頂きました「ボタン_1Click」マクロに、
https://ideone.com/SOCA2M

bu_sub2のマクロを以下に差し替えて実行してみたのですが、
コンパイルエラー「SubまたはFunctionが定義されていません」とのエラーが出てしまいました。

差し替えず、当初のものですと上手くいきます。

お礼日時：2018/09/25 22:45

No.4 回答者： tatsu99 回答日時： 2018/09/19 23:03

こちらに、

「マクロの処理速度について」
https://oshiete.goo.ne.jp/qa/10719388.html
あなたのマクロの問題点について投稿しました。
Sub bu_sub()について、私が行うなら、このようにするというサンプルです。
前提条件として、「実行マクロ」のシートにD列に「設定列」を設けます。（添付図の赤線で囲んだところです）
これは、発令日が例えば来年度上期に該当した時、部署別増減一覧のD列＝４列に増加、５列に減少をカウントアップ
しますが、これは、その列位置を登録しています。
実行マクロの３～５行、６行～９行のどの期間に該当するかをチェックし、発令日がその期間に入れば、
その列を求め、部署別増減一覧のその列へ加算します。
Sub bu_sub2()として、作成しました。
こちらを参照ください。
https://ideone.com/uQ4p16

この回答へのお礼

お世話になっております。
ご回答頂きましてありがとうございます。

作成して頂いたコードを拝見しました。
「設定列」の概念は目から鱗なのですが、今回のように展開する列の位置がが決まっている場合、
シート上に持っている設定列の値ををマクロ内のコンスタント値として定義することも可能でしょうか。
(こちらのマクロは各社、各部の担当者が使用する為、設定列の数字が変更されてマクロが動かなくなる事態を避けたい為です)

また、プロシージャーで他のSubに引き渡す処理を作成したことが無かったので
Private Sub GetIndex(ByVal ws As Worksheet, ByVal wdate As Date, ByRef kix As Long, ByRef qix As Long)
の記述等とても勉強になりました。

お礼日時：2018/09/20 16:46

No.3 回答者： tatsu99 回答日時： 2018/09/18 18:02

No2です。

＞①確かに、年度が決め打ちでは汎用性が無く、かつ元データには基準となる年度が分かるデータはありませんでした。
＞→検討した結果、別シートに各年度を入力する欄を設け、その日付を基準に振分とカウントを行うようにしました。
このレイアウトを画像で提示していただませんでしょうか。

＞②使用しない項目になりましたが、「Scond Half（下期）2018」は単なる文字列で、2018は半角になります。
すみません。このデータを使用しない場合、
今年度なのか、来年度なのかとか、上期なのか下期なのかの判定はどうやって行うのでしょうか。
（元データのフォーマットは変わらない前提での質問です）

＞③今年度を基準に、今年度下期、来年度上期、来年度下期の3項目と、1年を4分割した1-4Quarterの、計7項目×増加、減少の件数を出力する仕様に変更しました。
①の質問と同じかも知れませんが、このレイアウトを画像で提示していただませんでしょうか。
もう１つ、疑問なのが、今年度が上期がなくなりましたが、これで良いのでしょうか。（来年困りませんか）
今年はもう下期だけしかないので、今年度下期があればよいと思いますが、
来年になれば、今年の上期（2019年度の上期）が必要になるかと思いますが・・・・。
（それとも、人事異動は必ず、下期になってから発生するのでしょうか。）

この回答へのお礼

tatsu99様

いつもお世話になっております。

＞このレイアウトを画像で提示していただませんでしょうか。

現時点での結果レイアウトを補足に追記いたしました。

＞すみません。このデータを使用しない場合、
今年度なのか、来年度なのかとか、上期なのか下期なのかの判定はどうやって行うのでしょうか。
（元データのフォーマットは変わらない前提での質問です）

マクロを実行するボタンのあるシートに、期とQuarterの開始日ー終了日を入力する欄を設けました。
こちらも画像イメージを補足に追記しました。

＞①の質問と同じかも知れませんが、このレイアウトを画像で提示していただませんでしょうか。
もう１つ、疑問なのが、今年度が上期がなくなりましたが、これで良いのでしょうか。（来年困りませんか）
今年はもう下期だけしかないので、今年度下期があればよいと思いますが、
来年になれば、今年の上期（2019年度の上期）が必要になるかと思いますが・・・・。
（それとも、人事異動は必ず、下期になってから発生するのでしょうか。）

ご指摘のとおりです。当初は今期のみ使用する予定だった為決め打ちで良かったのですが、
来期以降も汎用的に使用できるよう変更したいということと、増減をある部署では期で確認したい、
ある部署ではQuarterで確認したい(どの時期を確認するかがバラバラ)という要件が追加されました。

現在は、期については今年度下期、来年度上期、来年度下期しかないのですが、
今年度下期の前に今年度上期の列も追記する予定です。

現時点でのコードを、教えて頂いたサイトで公開し補足に追記しました。
特にサブルーチンのマクロで、コードがやぼったくなってしまっているのですが、
より効率的な処理等ご存知でしたら教えて頂きたいです。

お礼日時：2018/09/19 17:11

No.2 回答者： tatsu99 回答日時： 2018/09/13 10:34

補足要求です。

①
2018年が今年度で、2019年が来年度でしょうか。そうだとすると
来年は、2019年が今年度で、2020年が来年度になります。
今、マクロを作るときに、2018年度が今年度と、決め打ちすると、来年は使えなくなります。
来年度になったことをマクロが知るすべはありません。
（マクロ実行時の現在の時間が2018年なら今年度が2018年という考え方もありますが危険です）
表のどこかに、今年度を示す年度が必要かと思いますがいかがでしょうか。
（例 U1のセルに2018と記入されていれば、今年度は2018年とする。来年の分を作る場合は、U1へ2019と記入する）
（元データに記入の余地がない場合は、部署別増減一覧の1行目の空いている列でも問題ありません）

②T2に「Scond Half（下期）2018」
とありますが、これは、単なる文字列ですか。それとも、書式設定で、このように表示されているのでしょうか。
又、2018は半角の数字でしょうか。


③今年度は2018年なので、T列の各セルの年度を示す数値は2018と2019の２つだけかと思いますが、もし、来年度になった場合でも
2019と2020の２つだけと考えてよいでしょうか。前年度の2018などの数値が残っていることは考慮しませんがそれで良いでしょうか。
そのような場合は、処理を打ち切りますが、よろしいでしょうか。

この回答へのお礼

tatsu99様

お世話になっております。
この度もご回答頂きましてありがとうございます。
また、お礼が遅くなりまして申し訳ございません。

①確かに、年度が決め打ちでは汎用性が無く、かつ元データには基準となる年度が分かるデータはありませんでした。
→検討した結果、別シートに各年度を入力する欄を設け、その日付を基準に振分とカウントを行うようにしました。

②使用しない項目になりましたが、「Scond Half（下期）2018」は単なる文字列で、2018は半角になります。

③今年度を基準に、今年度下期、来年度上期、来年度下期の3項目と、1年を4分割した1-4Quarterの、計7項目×増加、減少の件数を出力する仕様に変更しました。

上記内容を盛り込み、作成したマクロを質問に補足させていただきました。素人のコードなので、より効率的なコードや気になる箇所がありましたら、ご指摘頂けないでしょうか。
（元のマクロの最後にサブルーチンbu_sub）として作成しました。

お礼日時：2018/09/18 17:20

No.1 回答者： 銀鱗 回答日時： 2018/09/11 12:12

んー…ご自身で考えることは諦めて「代わりに作ってください」を貫き通しますか？

それともマクロのコマンドや構造を理解して自身で作れるように（修正できるように）したいのですか？

代わりに作っていただくことは、もちろん目の前にある「問題」を解決することになりますが、目の前にある「疑問」は解決しませんよね。
中途半端な解決ではなく根本的な解決を目指すようにしてはいかがでしょう。

・・・

そんなわけで、示されたマクロの中で何が分からなくて解決できないのでしょうか。
それを明らかにしてアドバイスを受けるようにすると良いと思います。

答えを示されて「わかったつもり」になることが一番悪いことであることは受験勉強をしているときに理解されていると思うのですが...。