ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

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

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

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

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

formular.rar (5.55 KB)

¦^´_ 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)

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2011-12-24 17:46 ½s¿è

¦^´_ 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

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2011-12-25 16:13 ½s¿è

¦^´_ 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)
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 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

TOP

¦^´_ 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)

TOP

¦^´_ 6# register313


    Thank

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

ÁÂÁ­@¤ß±Ð¾É

TOP

¦^´_ 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³ò§@¥[Á`

TOP

Thank
©ú¥Õ¤F

TOP

        ÀR«ä¦Û¦b : ¨ü¤HÂI¤ô¤§®¦¡A¶··í´é¬u¥H³ø¡C
ªð¦^¦Cªí ¤W¤@¥DÃD