ＶＢＡに関する質問です

質問

ＶＢＡの超初心者です。
現在勤めている会社で、労務管理をしております。
その業務中に以下のような状況にあり、悪戦苦闘しております。
全くの初心者ゆえ、かなり読みにくい質問となり恐縮ではございますが、何卒宜しくお願い致します。

現在、様々な種類の休業や休暇（ＥＸ．育児休業、介護休業、傷病休暇、・・・・たくさん種類があります）の取得日数を月単位で管理しています。
例えば、社員Ａが４月に育児休業（コード＝１０）を１５日取得すると、社員Ａのデータとしてコードの１０と日数の１５が管理されます。管理できる数は１月ごとに１人当たり５個のコード＆日数までです。

そこで、社員全体では以下のようなデータが取得できます。

　　　　　　Ａ列　　Ｂ列　　Ｃ列　　　Ｄ列　　Ｅ列　　・・・・・・・・・・・・・・・・・・　●列　　●列
------------------------------------------------------------------------
１行目　　　　　　４月(1)　４月(1)　４月(2)　４月(2)　・・・・・・・・・・・・・・・・・・　３月(5)　３月(5)
　　　　　　　　　　コード 　日数　　コード　　日数　　　　　　　　　　　　　　 コード　　日数
　　　　　-----------------------------------------------------------------
２行目　社員Ａ　　１０　　　２３
３行目　社員Ｂ
４行目　社員Ｃ　　１２　　　１５　　　１１　　　４　　　　　　　　　　
　　　　　　　：　
　　　　　　　：
●行目　社員Ｙ
●行目　社員Ｚ　　１５　　　１１
------------------------------------------------------------------------

わかりにくい図で誠に申し訳ございません。例えば、Ｂ列の４行目には、社員Ｃが４月にコード１２の休暇を取得したという情報が、Ｃ列の４行目にはその休暇が１５日間だったという情報があります。そして、同じ月に２種類の休暇を取得した場合は、その横の列を使ってデータが入っていきます。
そこで、上の図のようなデータを取得したのち、社員全員について（６０００名くらい）、コード１０の休暇は何日で、コード１１の休暇は何日でという値を右の列に出したいと思っております。コードは全てで２０種類くらいあります。

ＶＢＡを勉強中ということで、これを自動的に計算するマクロを作りたいと思っております。

そのＶＢＡ作成のためのテスト版として、以下のような仮のデータについて、上記のような計算を実行する場合の記述をご教授願います。

--------------------------------------------------------------
　　　　　　Ａ列　　　Ｂ列　　Ｃ列　　Ｄ列　　Ｅ列　　Ｆ列　　Ｇ列　　Ｈ列　　Ｉ列
　　　　　-------------------------------------------------------
１行目　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　１０　　１１　　１２
２行目　　１０　　　　５　　　　　　　　　　　　１１　　　１６
３行目　　　　　　　　　　　　　　　　　　　　　１２　　　２５
４行目　　１１　　　　３０　　１２　　　２８
　　：
　　：
　　：
６０００行目まで
--------------------------------------------------------------

コードは１０、１１、１２の３種類で、Ａ、Ｃ、Ｅ列はコードを管理する領域、Ｂ、Ｄ、Ｆ列はコードに対する日数を管理する領域です。
Ｇ列にはコード１０、Ｈ列にはコード１１、Ｉ列にはコード１２の日数を、それぞれの行ごとに表示させたいとかんがえております。
現在、以下のような方法でＧ列にコード１０の日数を表示させることはできたのですが、Ｈ列とＩ列にもおなじようにそれぞれコード１１と１２の日数を表示させたいと考えております。しかも、できるだけシンプルに変数などをつかって短い記述でです。まだまだ勉強過程ですが、恥を承知で質問させて頂いております。

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
sub 休業日数計算()

Dim myrng As Range
Dim g As Range
Set myrng = Range("g2:g6000")
For Each g In myrng
Dim i As Integer
For i = 1 To 3
If g.Offset(0, -8 + 2 * i).Value = Range("g1").Value Then g.Value = g.Value + g.Offset(0, -7 + 2 * i).Value
Next
Next g

end sub
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

根本的に思考の出発点から間違っているかもしれませんが、そこに対するご意見なども伺えれば幸いです。

何卒宜しくお願い致します。

回答No.1

jcctaira · ベストアンサー

mont___blancさん
こんにちは。

最初の表と2番目の表の違いや意味が理解できていませんが、
とりあえずプログラムをシンプル（処理スピードを考えない等）に作成しました。

Sub 休業日数計算()
　Dim I　 As Long
　Dim J　 As Long
　Dim K　 As Long
　Range("G2:I6000").ClearContents
　For I = 2 To 6000
　　For J = 1 To 5 Step 2
　　　For K = 7 To 9
　　　　If Cells(I, J) = Cells(1, K) Then
　　　　　Cells(I, K) = Cells(I, K) + Cells(I, J + 1)
　　　　　Exit For
　　　　End If
　　　Next K
　　Next J
　Next I
End Sub

お礼

ありがとうございます！本日出社して早々にこの記述で試してみました。たしかに時間は掛かりましたが、求めていた回答が得られ助かりました。
感謝です。

回答No.3

MAXIMAX

なかなか頑張らられいるようで、応援したいところです^^。コード的な回答は出てきているようなので、最終的にもどうしてもこの表の形式でやりたいという前提は取っ払って、SE 視点での回答(レクチャー？)です。求められている答えとは違うかと思いますが、今後の参考になればと思います。最初の方の表を使います(２番目でも同様ですが)。VBA がどうこうを超えてしまってますので、ま、適当に流してください。

まず、難解にしている要因として、管理表の形が厳しいのだと思います。ちょっと変形してみます。

まず、表１としては

　　　　　　Ａ列　　Ｂ列　　Ｃ列　　Ｄ列　　Ｅ列　　Ｆ列　　Ｇ列
------------------------------------------------------------------------
１行目　　　　　　　４月　　４月　　４月　　４月　　４月　　４月
　　　　　　　　　　　　１０　　１１　　１２　　１３　　１４　　１５
　　　　　-----------------------------------------------------------------
２行目　社員Ａ　　　２３
３行目　社員Ｂ
４行目　社員Ｃ　　　　　　　　４ 　　１５　　　　　　　　　　
　　　　　　　：　
　　　　　　　：
●行目　社員Ｙ
●行目　社員Ｚ　　　　　　　　　　　　　　　　　　　　　　　１１
------------------------------------------------------------------------

表がこうであれば、単純に縦計を出せば簡単ですよね。各コードごとにカラム(横)位置は固定して日付だけ入れているようなものです。

コードと日数で管理する場合は、これはデータベースのデータとして、表２としてこんな表になる方が望ましいのだと思います。

社員　　コード　　日数
-----------------------
Ａ　　　　１０　　２３
Ｃ　　　　１２　　１５
Ｃ　　　　１１　　　４
Ｚ　　　　１５　　１１

すべての社員の１コードのデータごとに１行にするということです。こうすると、コードでソート・グループ化すれば、集計は容易ですよね(月も項目として入るべきですが)。

この２つの形式がごっちゃになってしまって、難解にしてしまっているのだと思います。
-----

なので、出来れば元のシートを一旦、このどちらかの表の形に整形してしまったほうが良いです。その後のいろいろな解析等考えると表２のデータベース形式のほうが良いのですが、目的さえ達すればいいのであれば、表１の表のほうが楽です。

表１に成形するには、ちょっと抽象的なコードで書きますが、

　　' 縦方向(社員分)のループ。社員が最大 100 だとします。2 とうのは２行目からの意味。
　　for Row = 2 to 2+100 - 1

　　　　' 横方向(コード,日数)のループ。コードが最大 20 だとします 2 というのは B 列の意味。
　　　　for Col = 2 to 2+20*2 - 1 Step 2

　　　　　　' 元のシートでコードが入っているかをチェックします
　　　　　　if 元のシート.Cells(Row, Col) <> "" then

　　　　　　　　'新しいシートに値を入れます。縦方向は同じで、横方向はコードから決め、決まった位置に日数を入れます。
　　　　　　　　新しいシート.Cells(Row, 元のシート.Cells(Row, Col)-10+1) = 元のシート.Cells(Row, Col+1)

　　　　　　endif

　　　　next

　　next

こんな感じで成形すると、新しいシートに表１の形式が出来上がります(ほんとに数値だけの表になりますが)。後は縦計を取れば、目的の値が出てきますので、Excel の集計関数で十分ですよね。新しいシートでなくても、同じシートでずーっと下の方だったり、横のほうだったりを作業場所に使ってもいいと思います。

ちなみにここで一つ困るのは、コードが 10 からの連番という前提になっていることです。これが前提で無い場合は、コードを渡すと横位置を返すような関数をひとつ作ってあげると、いいかもしれません。
-----

表２のほうに成形する場合は、こんな感じになるかと思います。

　　Row2 = 1 ' 新しい表の方の縦方向位置

　　' 縦方向(社員分)のループ。社員が最大 100 だとします
　　for Row = 2 to 2+100 - 1

　　　　' 横方向(コード,日数)のループ。コードが最大 20 だとします
　　　　for Col = 2 to 2 + 20*2 - 1 Step 2

　　　　　　' 元のシートでコードが入っているかをチェックします
　　　　　　if 元のシート.Cells(Row, Col) <> "" then

　　　　　　　　'新しいシートに値を入れます。縦方向はRow2を使います。
　　　　　　　　'社員、コード、日数は元の表から持ってきます
　　　　　　　　新しいシート.Cells(Row2,1) = 元のシート.Cells(Row, 1) ' 社員
　　　　　　　　新しいシート.Cells(Row2,2) = 元のシート.Cells(Row, Col)) ' コード
　　　　　　　　新しいシート.Cells(row2,3) = 元のシート.Cells(Row, Col+1)) ' 日数
　　　　　　　　Rows2 = Rows2 + 1 ' 行をひとつ進めます

　　　　　　endif

　　　　next

　　next

こんな感じになるかと思います。

表１は Excel の表として扱いやすい形、表２はデータベースとしていろいろ利用可能な形になります。表２できちんと月まで入っていれば、ピボットテーブルなどなどへの応用なども効くので、後々便利ではありますが、集計にはもうワンステップ必要ですね(ピボット作ってしまえばいいのですが)。

と、ちょっとした講義でした。求めている答えとはちょっと違っていてごめんなさいね。ちょっと自分だけで楽しんでしまいました！(笑)。

お礼

ありがとうございます！
ベストアンサーはjcctairaさんとさせて頂きましたが、データーベース自体を操作するという発想は大切にしていきたいと思いました。なにしろ、今回は日数を人ごと、コードごとに管理するという目的でしたが、上司から今後様々なデータ加工の依頼が来ると考えれば、加工しやすいデータを用意しておく必要があります。
本日、ご教授いただいた方法でデータベースの加工にチャレンジしてみます！

回答No.2

mitarashi

初心者の域を脱していると推察しますが、より簡便なコードを模索されている様なので、重くて誰も褒めてくれないと思われるコードを投稿させていただきます。ご参考まで。
Sub test()
Dim targetRange As Range, fieldNameRange As Range
Dim myRow As Range, hitCell As Range
Dim i As Long

With Sheets("Sheet1")
Set fieldNameRange = Range(.Range("G1"), .Cells(1, .Columns.Count).End(xlToLeft))
Set targetRange = .Range("A2:F6000")
For Each myRow In targetRange.Rows
For i = 1 To targetRange.Columns.Count - 1 Step 2
If myRow.Cells(i).Value <> "" Then
Set hitCell = fieldNameRange.Find(myRow.Cells(i).Value, LookAt:=xlWhole)
If Not hitCell Is Nothing Then
.Cells(myRow.Row, hitCell.Column).Value = myRow.Cells(i + 1).Value
End If
End If
Next i
Next myRow
End With
End Sub

お礼

ありがとうございます！
こんな風に他の方の記述を見るのは勉強になります。
特に、mitarashiさんのご回答は、エクセル上のデータ範囲などが変更されても修正しやすいのかな？と思いましたが、どうなのでしょう？
今から実践してみます！