[µo°Ý] ½Ð°Ý¦p¦ó±N³o¥y+{sum(if......,if......,))}, ¥Î¦bMacro¸Ì­±??

idsmchow

¤@­ÓWorkbook¸Ì­±¦³¨â±NSheets, ¤À§O¦W¬°"Report" & "Data".
·í¤T­Ó±ø¥óMatch ªº±¡ªp¤U, ´N±NAmount ¥[°_¨Ó.

½Ð°Ý¦p¦ó±N³o¥y+{sum(if......,if......,))}, ¥Î¦bMacro¸Ì­±??

register313

¦^´_ 1# idsmchow
ªì¾ÇªÌVBA

1. Sub xx()
   
2. Set Rng1 = Sheets("Data").Columns("A:C")
   
3. Set Rng2 = Sheets("Report").Range("A1:B2")
   
4. C = 10
   
5. Do Until Sheets("Report").Cells(C, 2) = ""
   
6.    If Sheets("Report").Cells(C, 1) = "" Then
   
7.       Sheets("Report").Range(Cells(C, 5), Cells(C, 6)) = 0
   
8.    End If
   
9.    If Sheets("Report").Cells(C, 1) = "Y" Then
   
10.       Rng2.Cells(1, 1) = "Code"
    
11.       Rng2.Cells(1, 2) = "Type"
    
12.       Rng2.Cells(2, 1) = Sheets("Report").Cells(C, 2)
    
13.       Rng2.Cells(2, 2) = "A"
    
14.       Sheets("Report").Cells(C, 5) = WorksheetFunction.DSum(Rng1, 3, Rng2)
    
15.       Rng2.Cells(2, 2) = "B"
    
16.       Sheets("Report").Cells(C, 6) = WorksheetFunction.DSum(Rng1, 3, Rng2)
    
17.    End If
    
18.    C = C + 1
    
19. Loop
    
20. Rng2.Clear
    
21. End Sub

½Æ»s¥N½X

formular.rar (9.23 KB)

¤U¸ü¦¸¼Æ: 8

2011-12-24 11:27

GBKEE

¦^´_ 1# idsmchow
¦Û°Ê¿z¿ï

1. Option Explicit
   
2. Sub Ex()
   
3.     Dim C As Integer
   
4.     C = 10
   
5.     With Sheets("Report")
   
6.         Do Until .Cells(C, "B") = ""
   
7.             If .Cells(C, "A") = "" Then
   
8.                 .Range(.Cells(C, "E"), .Cells(C, "G")) = 0
   
9.             ElseIf .Cells(C, "A") = "Y" Then
   
10.                 Sheets("Data").Cells.AutoFilter
    
11.                 Sheets("Data").Cells.AutoFilter 1, "A"
    
12.                 Sheets("Data").Cells.AutoFilter 2, .Cells(C, "B")
    
13.                 .Cells(C, "E") = Application.Sum(Sheets("Data").Columns(3).SpecialCells(xlCellTypeVisible))
    
14.                 Sheets("Data").Cells.AutoFilter
    
15.                 Sheets("Data").Cells.AutoFilter 1, "B"
    
16.                 Sheets("Data").Cells.AutoFilter 2, .Cells(C, "B")
    
17.                 .Cells(C, "F") = Application.Sum(Sheets("Data").Columns(3).SpecialCells(xlCellTypeVisible))
    
18.                 .Cells(C, "G") = .Cells(C, "E") + .Cells(C, "F")
    
19.             End If
    
20.             C = C + 1
    
21.         Loop
    
22.     End With
    
23.     Sheets("Data").AutoFilterMode = False
    
24. End Sub

½Æ»s¥N½X

Hsieh

¦^´_ 1# idsmchow

³o­Ó°ÝÃD¦b2007ª©¥»¥H«á´N«Ü²³æ¥ÎSUMIFS¨ç¼Æ¿ý»s¥¨¶°§Y¥i
2003ª©´N¼g­Ó¦h±ø¥ó¥[Á`¨ç¼Æ¨Ó¹B¥Î
¤@¯ë¼Ò²Õ

1. Function SumIIF(SumRange As Range, ParamArray OtherArgs())'¦h±ø¥ó¥[Á`¨ç¼Æ¡A¥iª½±µ¹B¥Î¨ìÀx¦s®æ
   
2. '»yªk:SumIIF(SumRange,·Ç«h1,·Ç«h½d³ò1,·Ç«h2,·Ç«h½d³ò2,·Ç«h3,·Ç«h½d³ò3...·Ç«hn,·Ç«h½d³òn)
   
3. '·Ç«h½d³ò¤j¤p¥²¶·»PSumRange½d³ò¤j¤p¬Û¦P
   
4. Dim mystr$, temp$
   
5. Set dic = CreateObject("Scripting.Dictionary")
   
6. For i = LBound(OtherArgs) To UBound(OtherArgs) Step 2
   
7.   If mystr = "" Then mystr = OtherArgs(i) Else mystr = mystr & OtherArgs(i)
   
8.   dic(i + 1) = ""
   
9. Next
   
10. For i = 1 To SumRange.Count
    
11.     For Each ky In dic.keys
    
12.       If temp = "" Then temp = OtherArgs(ky)(i) Else temp = temp & OtherArgs(ky)(i)
    
13.     Next
    
14.     If temp = mystr Then SumIIF = SumIIF + SumRange(i)
    
15.     temp = ""
    
16. Next
    
17. End Function
    
18. Sub ex()'¥Dµ{¦¡
    
19. Dim Sr As Range
    
20. With Sheets("Data")
    
21. Set Sr = .Range("C2", .[C2].End(xlDown))
    
22. Set cr1 = .Range("A2").Resize(Sr.Count, 1)
    
23. Set cr2 = .Range("B2").Resize(Sr.Count, 1)
    
24. End With
    
25. With Sheets("Report")
    
26. For Each c In .[E8:F8]
    
27. For Each a In .Range("A:A").SpecialCells(xlCellTypeConstants)
    
28.    If a = "Y" Then .Cells(a.Row, c.Column) = SumIIF(Sr, c, cr1, a.Offset(, 1), cr2)
    
29. Next
    
30. Next
    
31. End With
    
32. End Sub

½Æ»s¥N½X

formular.rar (11.79 KB)

¤U¸ü¦¸¼Æ: 12

2011-12-25 16:13

idsmchow

¦^´_ 3# GBKEE


ÁÂÁÂ(register313, GBKEE, Hsieh)¦U¦ì¦^ÂÐ.

¥t¥~ÁÙ¦³¤@­Ó°ÝÃD

.Cells(C, "G") = .Cells(C, "E") + .Cells(C, "F")  ¡¥³o¥y»yªk¯à°÷±NColumn ªºTotal ¥[°_¨Ó.

¦Ü©ó Row ªºTotal ¤S¦p¦ó°µªk? (Sub Total & Grand Total)

¦]¬°A/C Code ¦³¥i¯à¼W¥[ / ´î¤Ö, ¸ê®Æ½d³òÀH®É·|ÅÜ°Ê.

¨Ò¦p:

A/C CODE

101-1
101-2
101-3
101-4

Sub-Total

215-2018
215-2019

Sub-Total

215-3022
215-3023
215-3024
215-3025
215-3026

Sub-Total

Grand Total

register313

¦^´_ 5# idsmchow
ªì¾ÇªÌVBA

1. Sub aa()
   
2. LastCol = ActiveSheet.UsedRange.SpecialCells(xlLastCell).Column
   
3. LastRow = ActiveSheet.UsedRange.SpecialCells(xlLastCell).Row
   
4. For C = 2 To LastCol - 1
   
5.   X = 1
   
6.   For R = 2 To LastRow
   
7.       If Cells(R, 1) = "Sub Total" Then
   
8.          Cells(R, C) = Application.Sum(Cells(X + 1, C).Resize(R - X - 1, 1))
   
9.          X = R
   
10.       End If
    
11.       Cells(R, LastCol) = Application.Sum(Cells(R, 2).Resize(1, LastCol - 2))
    
12.   Next R
    
13.   Cells(LastRow, C) = Application.Sum(Cells(2, C).Resize(LastRow - 2, 1)) / 2
    
14. Next C
    
15. End Sub

½Æ»s¥N½X

budget.rar (7.68 KB)

¤U¸ü¦¸¼Æ: 12

2011-12-26 17:13

idsmchow

¦^´_ 6# register313


    Thank

¥i§_¸ÑÄÀ  LastCol - 1,  Cells(R, C) = Application.Sum(Cells(X + 1, C).Resize(R - X - 1, 1)) X = R, ³o¨â¥y?

ÁÂÁ­@¤ß±Ð¾É

register313

¦^´_ 7# idsmchow

Cells(X + 1, C).Resize(R - X - 1, 1)
'¥H«e­±¤§³æ¤¸Àx¦s®æ¬°°ò·Ç,ÂX¤jÀx¦s®æ½d³ò¤§¦C¼Æ»P¦æ¼Æ
'¥HCells(X + 1, C)³æ¤¸Àx¦s®æ¬°°ò·Ç,ÂX¤jÀx¦s®æ½d³ò¬°R - X - 1¦C»P1¦æ

Application.Sum (Cells(X + 1, C).Resize(R - X - 1, 1))
'Àx¦s®æ½d³ò§@¥[Á`

idsmchow

Thank
©ú¥Õ¤F