串刺し計算の応用

投稿者 : 初心者     投稿日時 : 2023/09/13(Wed) 18:20:45     OS : Windows 10     EXCEL : Excel 2019

---

社員毎の月別給与支給明細のシートが12枚と賞与のシートが2枚、全部で14枚あり、串刺し計算で個々の年間総支給明細額を出したいと思います。Ａ列に社員名、1行目の項目列は基本給、各種手当てですが、入社退社があるため月により社員名が異なる場合があり、各自の手当も月により変わることがあるため同じ列でも月により手当名が異なる場合があります。1年間に在籍した社員すべてと、すべての手当名も表示して1枚のシートに合計する場合のコードはどのように書けばよいでしょうか。どなたか分かるかた御教示をお願いします。

投稿者 : 初心者     投稿日時 : 2023/09/13(Wed) 18:29:26

---

■[質問] 初心者さん（2023-09-13 18:20:45）の記事
＞ 各自の手当も月により変わることがあるため同じ列でも月により手当名が異なる場合があります。1年間に在籍した社員すべてと、すべての手当名も表示して1枚のシートに合計する場合のコードはどのように書けばよいでしょうか。どなたか分かるかた御教示をお願いします。

給与制度を変えたため途中の月から手当名が一部変更になっています。

投稿者 : さんこう     投稿日時 : 2023/09/13(Wed) 19:50:21

---

集計先のシートに、すべての社員名・項目名を書いておいて、
各シートから社員名と項目名がクロスするセルの値を取得して
足していけばよろしいかと思います。


参考になれば。

＜vba シート index match＞
https://www.google.com/search?q=vba+%E3%82%B7%E3%83%BC%E3%83%88+index+match

投稿者 : てらてら     投稿日時 : 2023/09/13(Wed) 20:43:42

---

こんにちは。

列の項目名、行の名前がバラバラだと、ForとかIfが増えまくって以下のように混沌としたコードになります。
逆に、元のデータの列の項目名、行の名前を揃えて前処理しておけばForとIfは減ります。

まぁ誰もこんなのは書きたがらないでしょう。（笑)

Sub macro()
    Dim i As Long
    Dim sh
    Dim 集計sh As Worksheet
    Set 集計sh = Worksheets("集計")
    Dim lastRow As Long, lr As Long
    
    '社員の重複しないリストを作成
    lr = 2 '集計shシートの開始行
    For Each sh In Worksheets
        If sh.Name <> "集計" Then
            lastRow = sh.Cells(Rows.Count, 1).End(xlUp).Row
            sh.Range(sh.Cells(2, "A"), sh.Cells(lastRow, "A")).Copy 集計sh.Cells(lr, "A")
            lr = lr + lastRow - 1
        End If
    Next sh

    集計sh.Range("A2").CurrentRegion.RemoveDuplicates 1, xlYes


    '各社員毎、項目毎に合計していく
    Dim j As Long, ii As Long

    For i = 2 To 集計sh.Cells(Rows.Count, 1).End(xlUp).Row
        For Each sh In Worksheets
            If sh.Name <> "集計" Then
                For ii = 2 To sh.Cells(Rows.Count, 1).End(xlUp).Row
                
                    If 集計sh.Cells(i, "A") = sh.Cells(ii, "A") Then '名前が同じなら
                        '基本給、各種手当て
                        For j = 2 To 20 '20は適当に大きめの数
                            If sh.Cells(1, j) = "基本給" Then  '基本給　B列
                                集計sh.Cells(i, "B") = 集計sh.Cells(i, "B") + sh.Cells(ii, j)
                            
                            ElseIf sh.Cells(1, j) = "手当1" Then
                                集計sh.Cells(i, "C") = 集計sh.Cells(i, "C") + sh.Cells(ii, j)
                        
                            ElseIf sh.Cells(1, j) = "手当2" Then
                                集計sh.Cells(i, "D") = 集計sh.Cells(i, "D") + sh.Cells(ii, j)
                        
                            'ElseIf
                        
                            'ElseIf
                        
                        
                            End If
                        Next j
                        
                        
                    End If
                Next ii
                
            End If
            
        Next sh
    Next i
End Sub

投稿者 : 初心者     投稿日時 : 2023/09/13(Wed) 21:57:23

---

■[返信 3] てらてらさん（2023-09-13 20:43:42）の記事
＞ こんにちは。
＞
＞ 列の項目名、行の名前がバラバラだと、ForとかIfが増えまくって以下のように混沌としたコードになります。
＞ 逆に、元のデータの列の項目名、行の名前を揃えて前処理しておけばForとIfは減ります。
＞
＞ まぁ誰もこんなのは書きたがらないでしょう。（笑)
＞
＞ Sub macro()
＞ Dim i As Long
＞ Dim sh
＞ Dim 集計sh As Worksheet
＞ Set 集計sh = Worksheets("集計")
＞ Dim lastRow As Long, lr As Long
＞
＞ '社員の重複しないリストを作成
＞ lr = 2 '集計shシートの開始行
＞ For Each sh In Worksheets
＞ If sh.Name <> "集計" Then
＞ lastRow = sh.Cells(Rows.Count, 1).End(xlUp).Row
＞ sh.Range(sh.Cells(2, "A"), sh.Cells(lastRow, "A")).Copy 集計sh.Cells(lr, "A")
＞ lr = lr + lastRow - 1
＞ End If
＞ Next sh
＞
＞ 集計sh.Range("A2").CurrentRegion.RemoveDuplicates 1, xlYes
＞
＞
＞ '各社員毎、項目毎に合計していく
＞ Dim j As Long, ii As Long
＞
＞ For i = 2 To 集計sh.Cells(Rows.Count, 1).End(xlUp).Row
＞ For Each sh In Worksheets
＞ If sh.Name <> "集計" Then
＞ For ii = 2 To sh.Cells(Rows.Count, 1).End(xlUp).Row
＞
＞ If 集計sh.Cells(i, "A") = sh.Cells(ii, "A") Then '名前が同じなら
＞ '基本給、各種手当て
＞ For j = 2 To 20 '20は適当に大きめの数
＞ If sh.Cells(1, j) = "基本給" Then '基本給　B列
＞ 集計sh.Cells(i, "B") = 集計sh.Cells(i, "B") + sh.Cells(ii, j)
＞
＞ ElseIf sh.Cells(1, j) = "手当1" Then
＞ 集計sh.Cells(i, "C") = 集計sh.Cells(i, "C") + sh.Cells(ii, j)
＞
＞ ElseIf sh.Cells(1, j) = "手当2" Then
＞ 集計sh.Cells(i, "D") = 集計sh.Cells(i, "D") + sh.Cells(ii, j)
＞
＞ 'ElseIf
＞
＞ 'ElseIf
＞
＞
＞ End If
＞ Next j
＞
＞
＞ End If
＞ Next ii
＞
＞ End If
＞
＞ Next sh
＞ Next i
＞ End Sub

ご丁寧に御教示頂きまして有り難うございます。
まったくお手上げでした。

手当項目の数も結構あるので頭がいたいです。
確かに行項目と列項目を事前になんとかすべて表示してから
考えた方が良いですね。
本当に有り難うございました。

投稿者 : 初心者     投稿日時 : 2023/09/13(Wed) 22:09:01

---

■[返信 2] さんこうさん（2023-09-13 19:50:21）の記事
＞ 集計先のシートに、すべての社員名・項目名を書いておいて、
＞ 各シートから社員名と項目名がクロスするセルの値を取得して
＞ 足していけばよろしいかと思います。
＞
＞
＞ 参考になれば。
＞
＞ ＜vba シート index match＞
＞ https://www.google.com/search?q=vba+%E3%82%B7%E3%83%BC%E3%83%88+index+match
＞
有り難うございます。
ヒントを参考に考えてみます。
明かりが見えてきました。

投稿者 : tek     投稿日時 : 2023/09/13(Wed) 23:22:50

---

＞確かに行項目と列項目を事前になんとかすべて表示してから考えた方が良いですね。
そんな必要はありません。
社員毎の項目毎の集計ならピボットテーブルで行うのが簡単です。
マクロの記録を使用すればサンプルコードが入手できるので手直しすれば良いと思います。

投稿者 : 初心者     投稿日時 : 2023/09/15(Fri) 06:39:49

---

■[返信 6] tekさん（2023-09-13 23:22:50）の記事

＞ 社員毎の項目毎の集計ならピボットテーブルで行うのが簡単です。
＞ マクロの記録を使用すればサンプルコードが入手できるので手直しすれば良いと思います。
＞
返信が遅くなり失礼しました。

ピボットテーブルは昔少し使ったことがあります。、
マクロの記録でサンプルコード。なるほど。
有り難うございます。今度やってみます。

その方が早いかもしれませんね。項目名をすべて調べるだけでも大変な手間ですから。
ヒントを頂きありがとうございました。