Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Home : Excel 2010)
月ごとの集計転記
投稿日時: 17/12/29 23:35:13
投稿者: ヤングコーン

営業課のシートが1課から8課まであります。
各シートはC列を4月、D列を5月としN列を3月の順で業績を表示しています。
別に集計シートを作成し、営業課の合計を転記するように下記マクロを作成
しました。
Dim SHDATA as Worksheet
Dim EADATA as Worksheet
Dim EBDATA as Worksheet
 
Set SHDATA = Worksheets("集計")
Set EADATA = Worksheets("営業1課")
Set EBDATA = Worksheets("営業2課")
 
  With SHDATA
     Range("C4")=EADATA.cells(10,3)    '営業1課の売上
     Range("D4")=Worksheetfunction.SUM(EADATA.Range("C197,C208,C213,C218") '営業1課の支払
     Range("H4")=EADATA.cells(1105,3)   '営業1課の営業利益
     Range("C5")=EBDATA.cells(10,3) '営業2課の売上
     Range("D5")=Worksheetfunction.SUM(EBDATA.Range("C197,C208,C213,C218") '営業2課の支払
     Range("H5")=EBDATA.cells(1105,3)   '営業2課の営業利益
             :
             :
  End WITH
End SUB
 
これでは4月の業績転記しかできないので、集計シートのセルA1に月を指定すると該当月の業績が転記できるようにしたいと考えております。(セルA1に8を入れると各課の8月実績(G列)を転記)
ご存知の方はご教示願います。

回答
投稿日時: 17/12/30 09:12:14
投稿者: simple

> 集計シートのセルA1に月を指定すると
> 該当月の業績が転記できるようにしたいと考えております。
> (セルA1に8を入れると各課の8月実績(G列)を転記)

ポイントはここですか? 月に応じて、転記先の列を得る。
 
例えば、
    Dim ary
    ary = Array("4月", "5月", "6月")
    Debug.Print 2 + Application.Match("5月", ary)
のようにすれば、月に相当する列を得ることができます。
 
(集計シートのセルA1に入っている情報に応じて、
  つまり、"数値だけが入力され、表示で月に対応か、月も入った文字列なのか"によって、
  必要なら修正してください。
  Match利用は変えなくてもよいと思います。
  もちろんMatchを使わない方法もありますが、それはどなたかが書かれるでしょう。)
 
このほか、
・Withの使い方が間違っています。ドットがありません。
・5月以降のデータ転記先が不明(まあ、それは今回のテーマではないのでしょう)
・組織についても、列挙ではなく、繰り返し処理に持ち込めると思います。
  (これも次のステップで)
   

回答
投稿日時: 17/12/30 12:21:09
投稿者: simple

指定した月だけでよかったんですね。
こんな書き方でどうですか。参考にしてみてください。
 

Sub test()
    Dim SHDATA  As Worksheet
    Dim ws      As Worksheet
    Dim m       As String
    Dim m_ary   As Variant
    Dim c       As Long
    Dim d_ary   As Variant
    Dim div     As Variant
    Dim r       As Long

    Set SHDATA = Worksheets("集計")

    '転記対象の月を元に、転記元の列を算定
    m = CStr(SHDATA.Range("A1").Value)
    m_ary = Split("4 5 6 7 8 9 10 11 12 1 2 3", " ")
    c = Application.Match(m, m_ary) + 2     ' 転記元の列

    '組織名
    d_ary = Split("営業1課,営業2課", ",")

    '転記作業
    r = 4       '転記先の行番号の初期値
    For Each div In d_ary
        With Worksheets(div)
            SHDATA.Cells(r, "B").Value = div                    '組織名
            SHDATA.Cells(r, "C").Value = .Cells(10, c).Value    '売上
            '支払
            SHDATA.Cells(r, "D").Value = .Cells(197, c).Value + .Cells(208, c).Value _
                                       + .Cells(213, c).Value + .Cells(218, c).Value
            SHDATA.Cells(r, "H").Value = .Cells(1105, c).Value   '営業利益
            r = r + 1
        End With
    Next
End Sub

回答
投稿日時: 17/12/30 21:59:31
投稿者: baoo

4月が最初ということで4を足すと12で割り切れる8を元にします。
4月の場合は8+4を12で割った余りが0です。
そしてその時の列が3ですから
(月番号+8) mod 12 +3が求める列番号になります。
課の数が分かりませんが10個あったとして下記のようになります。
 
動かしてませんが、下記のようになると思います。

Private Sub SampCreateSummary()
 
    Dim lngCol As Long      '月番号
    Dim lngN As Long        '課の番号
    
    Dim SHDATA As Worksheet
    Dim EADATA As Worksheet
    
    Set SHDATA = Worksheets("集計")
    With SHDATA
        
        '月から列番号取得
        If IsNumeric(.Cells(1, 1).Value) Then
            If .Cells(1, 1).Value < 1 Or .Cells(1, 1).Value > 12 Then
                Debug.Print "月番号が1-12の範囲に無い"
                Exit Sub
            End If
        Else
            Debug.Print "数値を入力"
            Exit Sub
        End If
        lngCol = (8 + .Cells(1, 1).Value) Mod 12 + 3
        
        '集計結果出力
        For lngN = 1 To 10    '課が10個あった場合
            Set EADATA = Worksheets("営業"& lngN &"課")

            '営業N課の売上
            .Cells(3 + lngN, 3) = EADATA.Cells(10, lngCol).Value
            
            '営業N課の支払
            .Range(3 + lngN, 4) = EADATA.Cells(197, lngCol).Value + _
                                  EADATA.Cells(208, lngCol).Value + _
                                  EADATA.Cells(213, lngCol).Value + _
                                  EADATA.Cells(218, lngCol).Value
                
            '営業N課の営業利益
            .Range(3 + lngN, 8) = EADATA.Cells(1105, lngCol).Value
        Next lngRow
    End With

End Sub

投稿日時: 18/01/01 18:00:31
投稿者: ヤングコーン

simple様
 
ご教示頂きありがとうございます。
早速試してみたところ、セルA1に4〜9までの数字(月)を入れた場合は
求めていた結果が得られましたが、10〜3までの数字(月)を入れると
「型が一致しません」というエラーが出て
 
 c = Application.Match(m, m_ary) + 2 ' 転記元の列
 
のところが黄色になります。
変数cやmのデータ型をLong型やVariant型に変えて試しみても結果は
同じでした。
どこがエラーなのかわからないのでご教示頂けないでしょうか。
宜しくお願いいたします。
 

回答
投稿日時: 18/01/01 19:08:50
投稿者: simple

年明け早々失礼しました。確認していませんでした。
 
Application.Match(m, m_ary)
ではなく
Application.Match(m, m_ary, 0)
と照合の型を指定してください。

投稿日時: 18/01/02 00:26:57
投稿者: ヤングコーン

simple様
 
ご教示ありがとうございました。
おかげさまで思っていた結果が出せるようになりました。
 
今回教えて頂いたのは単月のケースですが、累計(from ○月 to ○月)を出したい場合、
教えて頂いたコードに追記し
 
 '転記対象の月を元に、転記元の列を算定
    m = CStr(SHDATA.Range("A1").Value)
    m_ary = Split("4 5 6 7 8 9 10 11 12 1 2 3", " ")
    c = Application.Match(m, m_ary, 0) + 2 ' 転記元の列(from)
 
 Dim n as string
 Dim n_ary as variant
 Dim d as long
 
    n = CStr(SHDATA.range("B1").Value)
    n_ary = Split("4 5 6 7 8 9 10 11 12 1 2 3", " ")
    d = Application.Match(n, n_ary, 0) + 2 '転記元の列(to)
 
       r = 4 '転記先の行番号の初期値
    For Each div In d_ary
        With Worksheets(div)
             SHDATA.Cells(r, "B").Value = div '組織名
             SHDATA.Cells(r, "M").Value = "=-sum(.Cells(10, c).Value:.cells(10,d).value" '売上"
といったやり方になるのでしょうか。
なお、累計売上はM列に転記し、各課のシートの4月から指定した月までの合計を出したいと考えて
おりますが、上記コードでは当然のようにエラーとなりました。
cellsプロパティ使用時のSum関数のやり方が特にわかりません。
ご教示のほどよろしくお願い致します。
 

回答
投稿日時: 18/01/02 08:22:33
投稿者: simple

少しお尋ねします。
> SHDATA.Cells(r, "M").Value = "=-sum(.Cells(10, c).Value:.cells(10,d).value"
ですが、これは計算式を入れようとしているのですか? それとも計算結果?
 
SUMの前のマイナス記号の意味は?
 
""で囲ったものは文字列ですから、その文字列をセルに入れることになります。
 
":"というのは範囲指定ですが、.Value と.Valueを対象にする意味がわかりません。
 
意味を考えながらトライしたほうがよいと思います。
適当なものを書いて置いて修正してもらおう、という感じなんですか?
 
計算結果をセルに入れる場合の例を書きます。
B1:D1の合計値をA1に入れる場合です。色々な書き方があります。

    Range("A1").Value = WorksheetFunction.Sum(Range("B1:D1"))
    Range("A1").Value = WorksheetFunction.Sum(Range(Cells(1, 2), Cells(1, 4)))
    Range("A1").Value = WorksheetFunction.Sum(Cells(1, 2).Resize(1, 3))
参考にしてみてください。
(このほかの方法もあると思いますが、まずは基本形を。)

回答
投稿日時: 18/01/02 16:14:52
投稿者: もこな2

横から口出し失礼します。
最初の質問文を見ての疑問なのですが、Withステートメントは、simpleさんの指摘のとおりとして、そもそもVBAを使う理由はなんでしょう?
 
理解違いでればごめんなさいですけど、やりたいことは営業1課〜営業8課までの売上、支払、営業利益を集計シートに月ごとというか、月を可変にして転記したいということだと思われます。
 
もちろん、それぞれのシートの詳細なレイアウトが解らないですけど、

引用:
各シートはC列を4月、D列を5月としN列を3月の順で業績を表示しています。
とのことですから、一定の規則はあるのだろうと推測でき、この場合、一般機能であるMATCH関数やINDEX関数で対応が可能と思われます。
 
もちろん、学習のためVBAで行うというのもあるかと思いますが、一般機能で出来ないと思い込んでの質問かな?とおもいましたので、ちょっと口出しさせていただきました。
一般機能での対応を検討された上での質問であれば、お目汚し失礼しました。
 
※別掲示板で似たような質問に回答しましたので、リンクを置いておきます。
http://www.excel.studio-kazu.jp/kw/20171228145612.html
 
このほか、
引用:
セルA1に8を入れると各課の8月
とのことですが、入力規則を使ってプルダウンから選択することも出来るようにしておくといいとおもいます。(誤入力防止にもなりますし)

投稿日時: 18/01/03 22:51:36
投稿者: ヤングコーン

simple様
 
ご指摘ありがとうございます。
VBA初心者として深く考えもせず手探りで進めており軽率な質問をしたことお詫びいたします。
単月集計における拙い説明にも拘らず期待通りの回答を頂いた為、つい調子に乗って累計の場
合のコードも教えてもらおうと手を抜いてしまいました。
教えて頂いた参考例をもとに累計の出し方を考えてみます。
 
また、もこな2様のご指摘の通り、VBAの勉強の一環として今回質問させて頂きましたが、実際
のところVBAと一般関数のどちらで進めればよいのか判断しかねております。まったく発想にな
かったMATCH関数やINDEX関数を用いる方法で進めていけばVBAと同じ結果が得られると思います
のでこちらの方法でも進めてみたいと思います。
 
VBA,一般関数それぞれで疑問点が出た際は改めて質問させて頂くとして、今回はとりあえず
解決とさせて頂きます。
ありがとうございました。