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

[µo°Ý] 2­Ó±ø¥óªº¥[Á`

[µo°Ý] 2­Ó±ø¥óªº¥[Á`

½Ð°Ý¦U¦ì°ª¤â,

§Ú­n±N­ì©l¸ê®Æ¸Ì¨ÌÃþ§O»P¤é´Á³o¨â­Ó±ø¥ó±Nª÷ÃB¥[Á`¨ì"Á`ªí",¨Ò¦p:1/1~1/31, AÃþª÷ÃB¥[Á`«á©ñ¨ìB4Àx¦s®æ,2/1~2/28, AÃþª÷ÃB¥[Á`«á©ñ¨ìC4Àx¦s®æ,3/1~3/31, AÃþª÷ÃB¥[Á`«á©ñ¨ìD4Àx¦s®æ..¥H¦¹Ãþ±À
­ì¥»¨Ï¥Î¤u§@ªí¨ç¼ÆSUMPRODUCT, ¦ý¸ê®Æ¶q¦h®É,¤u§@ªírun­«·s­pºâ·|ªá«Ü¦h®É¶¡, ·Q§ï¥ÎVBA¤è¦¡.
¦ý§Ú¥u·|¥Î¤U¦C¼gªk,µo²{³o¼Ë¼gªk­n¼g«Ü¦hªºIF§PÂ_,·Q½Ð°Ý¦U¦ì°ª¤â ´£¨Ñ¸û¦nªº¼gªk.·PÁÂ!

Private Sub CommandButton2_Click()
Dim rowcnt As Integer
Dim i As Integer
Dim sum_A_1, sum_A_2, sum_A_3,sum_A_4,sum_A_5,sum_A_6,sum_A_7,sum_A_8,sum_A_9,sum_A_10,sum_A_11,sum_A_12 As Variant
sum_A_1= 0 '²Î­pAÃþ1¤ë¥÷ª÷ÃB
sum_A_2= 0 '²Î­pAÃþ2¤ë¥÷ª÷ÃB
sum_A_3= 0 '²Î­pAÃþ3¤ë¥÷ª÷ÃB
sum_A_4= 0 '²Î­pAÃþ4¤ë¥÷ª÷ÃB
sum_A_5= 0 '²Î­pAÃþ5¤ë¥÷ª÷ÃB

Sheets("­ì©l¸ê®Æ").Activate
With Sheets("­ì©l¸ê®Æ")
rowcnt= .Cells(1, 1).CurrentRegion.Rows.Count

For i = 1 To rowcnt
    '¥[Á`AÃþ¤@¤ë¥÷ª÷ÃB
    If .Cells(i,2) >= "2013/1/1"And .Cells(i, 2) <= "2013/1/31" And .Cells(i,1) = Sheets("Á`ªí").Range("A4") Then
        sum_A_1= sum_A_1 + (Cells(i, 3).Value)
        Sheets("Á`ªí").Range("B4") = sum_A_1
    End If
   '¥[Á`AÃþ¤G¤ë¥÷ª÷ÃB
     If .Cells(i,2) >= "2013/2/1"And .Cells(i, 2) <= "2013/2/28" And .Cells(i,1) = Sheets("Á`ªí").Range("A4") Then
        sum_A_2= sum_A_2+ (Cells(i, 3).Value)
        Sheets("Á`ªí").Range("C4") = sum_A_2   
     End If
Next

End With
End Sub

¹Ï¤G.jpg (59.32 KB)

¹Ï¤G.jpg

learner

¥»©«³Ì«á¥Ñ kimbal ©ó 2013-5-31 01:40 ½s¿è

¦^´_ 1# yliu
  1. Private Sub CommandButton2_Click()
  2. Application.ScreenUpdating = False
  3. Application.Calculation = xlCalculationManual

  4. Dim rowcnt As Long
  5. Dim i As Long

  6. Dim lngVlookupRow As Long
  7. Dim lngCurrenMonth As Long
  8. Dim lngCurrValue As Long

  9. '²M²z¼ƾÚ
  10. Sheets("Á`ªí").Activate
  11. Sheets("Á`ªí").Range("A3").CurrentRegion.Offset(1, 1).Clear


  12. Sheets("­ì©l¸ê®Æ").Activate
  13. With Sheets("­ì©l¸ê®Æ")
  14.     rowcnt = .Cells(1, 1).CurrentRegion.Rows.Count
  15.     For i = 1 To rowcnt
  16.         lngCurrenMonth = 0
  17.         If IsDate(.Cells(i, 2)) Then
  18.             lngCurrenMonth = Month(.Cells(i, 2)) '·í¦æ¤ë¥÷
  19.         End If
  20.         If lngCurrenMonth > 0 And Not (IsError(Application.Match(.Cells(i, 1), Sheets("Á`ªí").Range("A:A"), 0))) Then
  21.             lngVlookupRow = Application.Match(.Cells(i, 1), Sheets("Á`ªí").Range("A:A"), 0) '·í¦æÃþ«¬¦bÁ`ªí¦æ¼Æ
  22.             lngCurrValue = .Cells(i, 3)
  23.             With Sheets("Á`ªí").Cells(lngVlookupRow, lngCurrenMonth + 1) '©ñ¼Æ¾Ú¨ìÁ`ªí¤W
  24.                 .Value = .Value + lngCurrValue
  25.             End With
  26.         End If
  27.     Next
  28.    
  29. End With

  30. Application.Calculation = xlCalculationAutomatic
  31. Application.ScreenUpdating = True
  32. End Sub
½Æ»s¥N½X
À´±oµo°Ý,µª®×´N·|¦b¨ä¤¤

¤µ¤éの¤@¬íは  ©ú¤éにない
http://kimbalko-chi.blogspot.com
http://kimbalko.blogspot.com

TOP

¦^´_ 2# kimbal
ÁÂÁÂkimbal.
¦ý¥X²{°õ¦æ¶¥¬q¿ù»~¡¦13¡¦: «¬ºA¤£²Å¦X
°»¿ù¦b: With Sheets("Á`ªí").Cells(lngVlookupRow, lngCurrenMonth + 1) '©ñ¼Æ¾Ú¨ìÁ`ªí¤W
learner

TOP

¦^´_ 1# yliu
¦³¦Ò¼{ª½±µ¥Î¼Ï¯Ã¤ÀªRªí¶Ü?
a.gif

TOP

¦^´_ 1# yliu

¥i¥H¦Ò¼{¥Î SUMIFS ¨Ó¥N´À SUMPRODUCT¡A ¦]爲SUMIFS§ó¦³®Ä²v
ss

TOP

¦^´_ 3# yliu


    ¥i§_´£¨Ñ¤@¤UEXCEL?

¥t¥~¤]·|«Øij¥ÎPIVOTTABLE ©MSUMIFS
VBA LOOP«Ü¦h±¡ªp¤]¬O¤ñ¤½¦¡ºCªº.
À´±oµo°Ý,µª®×´N·|¦b¨ä¤¤

¤µ¤éの¤@¬íは  ©ú¤éにない
http://kimbalko-chi.blogspot.com
http://kimbalko.blogspot.com

TOP

¦^´_  kimbal
ÁÂÁÂkimbal.
¦ý¥X²{°õ¦æ¶¥¬q¿ù»~¡¦13¡¦: «¬ºA¤£²Å¦X
°»¿ù¦b: With Sheets("Á`ªí").Cells( ...
yliu µoªí©ó 2013-5-31 12:30

¦^´_ 2# kimbal
¤£¦n·N«ä¡A§Úµy·L²K¥[¤F´X¦æ¡A§Æ±æ±zµ¥¯à¯º¯Ç¡I
  1. Private Sub Ex()
  2.     Dim rowcnt As Long, i As Long
  3.     Dim lngVlookupRow As Long, lngCurrenMonth As Long, lngCurrValue As Long

  4.     Application.ScreenUpdating = False
  5.     Application.Calculation = xlCalculationManual

  6.     '  ²M²z¼ƾÚ
  7.     Sheets("Á`ªí").Activate
  8.     Sheets("Á`ªí").Range("A3").CurrentRegion.Offset(1, 1).Clear


  9.     Sheets("­ì©l¸ê®Æ").Activate
  10.     With Sheets("­ì©l¸ê®Æ")
  11.         rowcnt = .Cells(1, 1).CurrentRegion.Rows.Count
  12.         For i = 2 To rowcnt
  13.             lngCurrenMonth = 0
  14.             If IsDate(.Cells(i, 2)) Then
  15.                 lngCurrenMonth = Month(.Cells(i, 2))                                               '  ·í¦æ¤ë¥÷
  16.             End If
  17.             
  18.             If lngCurrenMonth > 0 And Not (IsError(Application.Match(.Cells(i, 1), Sheets("Á`ªí").Range("A:A"), 0))) Then
  19.                 lngVlookupRow = Application.Match(.Cells(i, 1), Sheets("Á`ªí").Range("A:A"), 0)    '  ·í¦æÃþ«¬¦bÁ`ªí¦æ¼Æ
  20.                 lngCurrValue = .Cells(i, 3)
  21.                 With Sheets("Á`ªí")
  22.                     .Cells(lngVlookupRow, lngCurrenMonth + 1).Value = .Cells(lngVlookupRow, lngCurrenMonth + 1).Value + lngCurrValue                       '  ©ñ¼Æ¾Ú¨ìÁ`ªí¤W
  23.                     .Cells(lngVlookupRow, "N") = .Cells(lngVlookupRow, "N") + lngCurrValue
  24.                     .Cells(lngVlookupRow, Chr(79 + Int(lngCurrenMonth / 4))) = .Cells(lngVlookupRow, Chr(79 + Int(lngCurrenMonth / 4))) + lngCurrValue
  25.                 End With
  26.             End If
  27.         Next
  28.     End With
  29.    
  30.     With Sheets("Á`ªí")
  31.         rowcnt = .[A3].End(xlDown).Row
  32.         For i = 2 To 18
  33.             .Cells(rowcnt, i) = WorksheetFunction.Sum(.Range(Chr(64 + i) & 4 & ":" & Chr(64 + i) & (rowcnt - 1)))
  34.         Next i
  35.     End With
  36.    
  37.     Application.Calculation = xlCalculationAutomatic
  38.     Application.ScreenUpdating = True
  39. End Sub
½Æ»s¥N½X

TOP

¥»©«³Ì«á¥Ñ c_c_lai ©ó 2013-5-31 19:55 ½s¿è

½Ð±N 4 §ï¦¨ 3.2
  1.                     .Cells(lngVlookupRow, Chr(79 + Int(lngCurrenMonth / 3.2))) = .Cells(lngVlookupRow, Chr(79 + Int(lngCurrenMonth /3.2))) + lngCurrValue
½Æ»s¥N½X

TOP

¦pªþ¥ó¡G
¨â­Ó±ø¥óªº¥[Á`.rar (20.85 KB)

TOP

¦^´_ 9# c_c_lai


   ÁÂÁÂ c_c_lai,
   ¥i¥H¨Ï¥Î¤F, ·PÁÂ~
  ¦A½Ð°Ý¤@¤U,   
.Cells(lngVlookupRow, Chr(79 + Int(lngCurrenMonth / 3.2))) = .Cells(lngVlookupRow, Chr(79 + Int(lngCurrenMonth / 3.2))) + lngCurrValue  '

°£¥H3.2 ªº§@¥Î¬O¤°»ò?
learner

TOP

        ÀR«ä¦Û¦b : §Ú­Ì­n°µ¦nªÀ·|ªºÀô«O¡A¤]­n°µ¦n¤º¤ßªºÀô«O¡C
ªð¦^¦Cªí ¤W¤@¥DÃD