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

[µo°Ý] ELSEIF»PDO WHILE±_ª¬°j°é¨Ó§PÂ_¥[Á`

[µo°Ý] ELSEIF»PDO WHILE±_ª¬°j°é¨Ó§PÂ_¥[Á`



³oµ{¦¡¥D­n¬O·Q­nÅýIF¨Ó§PÂ_ÃC¦â¤§«á¡AµM«á¨Ó¥[Á`©Ò¹ïÀ³ªº­«¶q¡C

§Úªº°j°é¬O·íÄæ¦ì(2,1)¤£¬OªÅ®æ¡A«K·|¶}©l°õ¦æ°j°é¡A
µM«á¥ÎIF§PÂ_¦pªGÃC¦â¬°"¬õ"¸ò"¬õ¬õ"¡A±N·|²Ö¥[­«¶q¦ÜÄæ¦ì(3,5)ªº¬õ¦â
¦pªGÃC¦â¬O"ºñ"©Î¬O³Æµù¦³"¤j"´N·|²Ö¥[¦ÜÄæ¦ì(3,6)ªººñ¦â
¦pªGÃC¦â¬O"ÂÅ"´N·|²Ö¥[¦ÜÄæ¦ì(3,7)ªºÂŦâ
¦pªGÃC¦â¬O"¶À"´N·|²Ö¥[¦ÜÄæ¦ì(3,8)ªº¶À¦â
¦pªG·sªºÄæ¦ì¦³"·s"«h·|²Ö¥[¦ÜÄæ¦ì(3,9)©Ò¦³ÃC¦âªº­«¶q

¸g¹L§Ú¼gªº»yªk°õ¦æ«áµo²{¡A¥u­n¬Y¤@¦Cªº±ø¥ó¦³²Å¦X¡A¨ä¼Æ­È¦³³Q²Ö¥[¹L¤§«á¡A§Ú«á­±¼gªº¥t¤@±ø¥ó§Y¨Ï²Å¦X¡A©~µM´NµLªk¦b©¹¤W²Ö¥[¤F¡C


§Úªº»yªk¦p¤U©Ò¥Ü¡G

Sub aa()

Dim x, sum1, sum2, sum3, sum4, sum5, sum6 As Long

x = 2

sum1 = 0
sum2 = 0
sum3 = 0
sum4 = 0
sum5 = 0







Do While Cells(x, 1) <> ""


   
  If Cells(x, 4) = "·s" Then
  
  sum5 = sum5 + Cells(x, 2).Value


  ElseIf Cells(x, 1) = "¬õ" Then

  sum1 = sum1 + Cells(x, 2).Value
   
  ElseIf Cells(x, 1) = "¬õ¬õ" Then
  
  sum1 = sum1 + Cells(x, 2).Value
  
  
  ElseIf Cells(x, 1) = "ºñ" Then

  sum2 = sum2 + Cells(x, 2).Value
   
  ElseIf Cells(x, 3) = "¤j" Then
  
  sum2 = sum2 + Cells(x, 2).Value
  
  
  
  ElseIf Cells(x, 3) = "ÂÅ" Then
  
  sum3 = sum3 + Cells(x, 2).Value
  
  
  ElseIf Cells(x, 3) = "¶À" Then
  
  sum4 = sum4 + Cells(x, 2).Value
  
  

  



  

  
  
  
  Else
  sum1 = sum1 + 0
  sum2 = sum2 + 0
  sum3 = sum3 + 0
  sum4 = sum4 + 0
  sum5 = sum5 + 0


  
  
  
  End If
  
  x = x + 1
  
Loop
  
  Cells(3, 5) = sum1
  Cells(3, 6) = sum2
  Cells(3, 7) = sum3
  Cells(3, 8) = sum4
  Cells(3, 9) = sum5









End Sub



¸g¹L°õ¦æ¤§«á¡A¥L¥u·|°õ¦æ²Ö¥[©Ò¦³ÃC¦â­«¶qªºµ{¦¡¡A«á­±ªº»yªk´N·|§¹¥þµL®Ä¡C

·Ð½Ð¦U¦ì¤j¤j¸Ñµª¡A·P®¦ªº¤ß~~ÁÂÁÂ

¦^´_ 1# wiemanson
  1. Option Explicit
  2. Sub Ex()
  3.     Dim x As Integer, S As Variant, AR(), AC()
  4.     With ActiveSheet
  5.         AC = Array("¬õ", "ºñ", "ÂÅ", "¶À")              'ÃC¦â°}¦C: «ü©wÃC¦â(0-3),ÃC¦â¥i¦A¼W¥[(0-?)
  6.         AR = Array("E3", "F3", "G3", "H3", "I3")  'ÃC¦â¦ì¸m°}¦C (¬õ,ºñ,ÂÅ,¶À,Á`­«¶q): Àx¦s®æªº¦ì¸m(0-4)
  7.         'Á`­«¶q¦ì¸m :UBound(AR) -> ÃC¦â¦ì¸m°}¦Cªº¯Á¤Þ­È
  8.         .Range(AR(0) & ":" & AR(UBound(AR))) = ""
  9.         x = 2
  10.         Do While .Cells(x, 1) <> ""
  11.             If .Cells(x, 4) = "·s" Then
  12.                 S = UBound(AR)                          '
  13.                 .Range(AR(S)) = .Range(AR(S)) + Cells(x, 2)
  14.             Else
  15.                 S = Mid(.Cells(x, 1), 1, 1)             '¦p¬õ¬õ®É¥iŪ¨ú"¬õ"
  16.                 If .Cells(x, 3) = "¤j" Then S = "ºñ"
  17.                 S = Application.Match(S, AC, 0)         'Match ¦p§ä¤£¨ì:¶Ç¦^¿ù»~­È
  18.                 If IsNumeric(S) Then                    'S: ¦b«ü©wÃC¦â°}¦C¤¤ªº¦ì¸m
  19.                     .Range(AR(S - 1)) = .Range(AR(S - 1)) + .Cells(x, 2)
  20.                 End If
  21.             End If
  22.             x = x + 1
  23.         Loop
  24.         S = UBound(AR)
  25.         .Range(AR(S)) = .Range(AR(S)) + Application.Sum(.Range(AR(0) & ":" & AR(S - 1)))
  26.     End With
  27. End Sub
½Æ»s¥N½X
  1. Sub aa()
  2.     Dim x As Integer, sum1 As Long, sum2 As Long, sum3 As Long, sum4 As Long, sum5 As Long, sum6 As Long
  3.     x = 2
  4.     sum1 = 0
  5.     sum2 = 0
  6.     sum3 = 0
  7.     sum4 = 0
  8.     sum5 = 0
  9.     Do While Cells(x, 1) <> ""
  10.         If Cells(x, 4) = "·s" Then
  11.             sum5 = sum5 + Cells(x, 2).Value
  12.         ElseIf Cells(x, 1) = "¬õ" Then
  13.             sum1 = sum1 + Cells(x, 2).Value
  14.         ElseIf Cells(x, 1) = "¬õ¬õ" Then
  15.             sum1 = sum1 + Cells(x, 2).Value
  16.         ElseIf Cells(x, 1) = "ºñ" Or Cells(x, 3) = "¤j" Then
  17.             sum2 = sum2 + Cells(x, 2).Value
  18.         ElseIf Cells(x, 1) = "ÂÅ" Then   'Cells(x, 3) !!!
  19.             sum3 = sum3 + Cells(x, 2).Value
  20.         ElseIf Cells(x, 1) = "¶À" Then 'Cells(x, 3) !!!
  21.             sum4 = sum4 + Cells(x, 2).Value
  22.         End If
  23.         x = x + 1
  24.     Loop
  25.     Cells(3, 5) = sum1
  26.     Cells(3, 6) = sum2
  27.     Cells(3, 7) = sum3
  28.     Cells(3, 8) = sum4
  29.     Cells(3, 9) = sum5 + sum1 + sum2 + sum3 + sum4
  30. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¥»©«³Ì«á¥Ñ yen956 ©ó 2014-4-15 09:57 ½s¿è

¦^´_ 1# wiemanson
¤£ª¾¹D§Aªº·N«ä¬O¤£¬O³o¼Ë?
°õ¦æµ²ªG:
¬õ        ºñ        ÂÅ        ¶À        All
3        5        1        5        10
¦pªG¬O, ¸Õ¸Õ¬Ý:
Sub aa()
Dim x, sum1, sum2, sum3, sum4, sum5, sum6 As Long
x = 2
sum1 = 0
sum2 = 0
sum3 = 0
sum4 = 0
sum5 = 0
Do While Cells(x, 1) <> ""
    'If .. Else ¨ã±Æ¥L, °õ¦æ¤F Cells(x, 4) = "·s", ´N¤£·|°õ¦æ¨ä¥Lªº
    If Cells(x, 4) = "·s" Then
    sum5 = sum5 + Cells(x, 2).Value
    End If
    '¦P¼Ë Cells(x, 3) = "¤j", ¤]¤£¯à¦b IF...Else ¤º
    If Cells(x, 3) = "¤j" Then
    sum2 = sum2 + Cells(x, 2).Value
    End If
   
    If Cells(x, 1) = "¬õ" Then
    sum1 = sum1 + Cells(x, 2).Value
    ElseIf Cells(x, 1) = "¬õ¬õ" Then
    sum1 = sum1 + Cells(x, 2).Value
    ElseIf Cells(x, 1) = "ºñ" Then
    sum2 = sum2 + Cells(x, 2).Value
    ElseIf Cells(x, 1) = "ÂÅ" Then
    sum3 = sum3 + Cells(x, 2).Value
    ElseIf Cells(x, 1) = "¶À" Then
    sum4 = sum4 + Cells(x, 2).Value
    Else
'¨S§@¥Î§a?
    sum1 = sum1 + 0
    sum2 = sum2 + 0
    sum3 = sum3 + 0
    sum4 = sum4 + 0
    sum5 = sum5 + 0

    End If
    x = x + 1
Loop
Cells(3, 5) = sum1
Cells(3, 6) = sum2
Cells(3, 7) = sum3
Cells(3, 8) = sum4
Cells(3, 9) = sum5
End Sub
'¦ý¤£ª¾ "ºñ","¤j" ¦P±Æ®É, §A·Q«ç»ò³B²z?
¤S, sum1, sum2, sum3, sum4, sum5, sum6 ¤@¾ã±ÆÅܼÆ,
¥iª½±µ¥Î [E3], [F3],[G3],... ¥N´À.
¸Õ¸Õ¬Ý:
  1. Private Sub CommandButton1_Click()
  2.     Dim x As Integer
  3.     x = 2
  4.     Range("E3:I3") = 0
  5.     Do While Cells(x, 1) <> ""
  6.         If Cells(x, 4) = "·s" Then
  7.             [I3] = [I3] + Cells(x, 2).Value
  8.         End If
  9.         If Cells(x, 3) = "¤j" Then
  10.             [F3] = [F3] + Cells(x, 2).Value
  11.         End If
  12.         If Cells(x, 1) = "¬õ" Or Cells(x, 1) = "¬õ¬õ" Then
  13.             [E3] = [E3] + Cells(x, 2).Value
  14.         ElseIf Cells(x, 1) = "ºñ" Then
  15.             [F3] = [F3] + Cells(x, 2).Value
  16.         ElseIf Cells(x, 1) = "ÂÅ" Then
  17.             [G3] = [G3] + Cells(x, 2).Value
  18.         ElseIf Cells(x, 1) = "¶À" Then
  19.             [H3] = [H3] + Cells(x, 2).Value
  20.         End If
  21.         x = x + 1
  22.     Loop
  23. End Sub
½Æ»s¥N½X

TOP

ÁÂÁ¦U¦ì¤j®vªº¸Ñµª¡A°£¤F¸Ñ¨M°ÝÃD¤§¥~¡A§ÚÁپǨì¤F«Ü¦h¡A¯uªº«D±`·PÁ¡C

TOP

        ÀR«ä¦Û¦b : §g¤l¬°¥Ø¼Ð¡A¤p¤H¬°¥Øªº¡C
ªð¦^¦Cªí ¤W¤@¥DÃD