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

wiemanson

¤U¸ü (86.49 KB)
³o¬O§Úªºªí®æ

2014-4-14 15:50

³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®¦ªº¤ß~~ÁÂÁÂ

wiemanson

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

yen956

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

GBKEE

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