½Ð±Ð¤@¤U¡I¤U¦Cªº»yªk¥i¥Î°j°é¨Ó²¤Æ¶Ü¡H

skyutm

¦U¦ì¥ý¶i¤j®a¦n¡I¤p§Ì¬°¤F³]­p¦¨ÁZ¨t²Î¨ÌµMÄ~Äò§V¤O¤¤¡A¦ý¦b¦¹¤S¦³¤@­Ó°ÝÃD·Q½Ð±Ð¡A½Ð°Ý¤@¤U¡A¤U¦Cªº»yªk¥i¥H¥Î°j°é²¤Æ¶Ü¡HÀ³¸Ó«ç»ò¼g¡C
Sub Worksheet_Change(ByVal Target As Range) '°ê»y¦¨ÁZ'
    Dim ch1 As Integer, ch2 As Integer, ch3 As Integer, ch4 As Integer, chsc1 As Integer, chsc2 As Integer, chsc3 As Integer, chsc4 As Integer
    ch1 = Worksheets("°ò¥»³]©w").Range("e2").Value
    ch2 = Worksheets("°ò¥»³]©w").Range("f2").Value
    ch3 = Worksheets("°ò¥»³]©w").Range("g2").Value
    ch4 = Worksheets("°ò¥»³]©w").Range("h2").Value
    chsc1 = Worksheets("´Á¤¤µû¶q").Range("c3").Value
    chsc2 = Worksheets("´Á¤¤µû¶q").Range("h3").Value
    chsc3 = Worksheets("´Á¤¤µû¶q").Range("i3").Value
    chsc4 = Worksheets("´Á¤¤µû¶q").Range("j3").Value
    Worksheets("´Á¤¤¦¨ÁZ").Range("c5").Value = (chsc1 * ch1 + chsc2 * ch2 + chsc3 * ch3 + chsc4 * ch4) / 100
End Sub

http://www.FunP.Net/795776

skyutm

¸É¥R¤@¤U¡A´N¬O³o¤@¤j¦ê
Worksheets("´Á¤¤¦¨ÁZ").Range("c5").Value = (Range("c3") * ch1 + Range("h3") * ch2 + Range("i3") * ch3 + Range("j3") * ch4) / 100
    Worksheets("´Á¤¤¦¨ÁZ").Range("c6").Value = (Range("c4") * ch1 + Range("h4") * ch2 + Range("i4") * ch3 + Range("j4") * ch4) / 100
    Worksheets("´Á¤¤¦¨ÁZ").Range("c7").Value = (Range("c5") * ch1 + Range("h5") * ch2 + Range("i5") * ch3 + Range("j5") * ch4) / 100
    Worksheets("´Á¤¤¦¨ÁZ").Range("c8").Value = (Range("c6") * ch1 + Range("h6") * ch2 + Range("i6") * ch3 + Range("j6") * ch4) / 100
    Worksheets("´Á¤¤¦¨ÁZ").Range("c9").Value = (Range("c7") * ch1 + Range("h7") * ch2 + Range("i7") * ch3 + Range("j7") * ch4) / 100
    Worksheets("´Á¤¤¦¨ÁZ").Range("c10").Value = (Range("c8") * ch1 + Range("h8") * ch2 + Range("i8") * ch3 + Range("j8") * ch4) / 100
    Worksheets("´Á¤¤¦¨ÁZ").Range("c11").Value = (Range("c9") * ch1 + Range("h9") * ch2 + Range("i9") * ch3 + Range("j9") * ch4) / 100
    Worksheets("´Á¤¤¦¨ÁZ").Range("c12").Value = (Range("c10") * ch1 + Range("h10") * ch2 + Range("i10") * ch3 + Range("j10") * ch4) / 100
    Worksheets("´Á¤¤¦¨ÁZ").Range("c13").Value = (Range("c11") * ch1 + Range("h11") * ch2 + Range("i11") * ch3 + Range("j11") * ch4) / 100
    Worksheets("´Á¤¤¦¨ÁZ").Range("c14").Value = (Range("c12") * ch1 + Range("h12") * ch2 + Range("i12") * ch3 + Range("j12") * ch4) / 100
    Worksheets("´Á¤¤¦¨ÁZ").Range("c15").Value = (Range("c13") * ch1 + Range("h13") * ch2 + Range("i13") * ch3 + Range("j13") * ch4) / 100
    Worksheets("´Á¤¤¦¨ÁZ").Range("c16").Value = (Range("c14") * ch1 + Range("h14") * ch2 + Range("i14") * ch3 + Range("j14") * ch4) / 100
    Worksheets("´Á¤¤¦¨ÁZ").Range("c17").Value = (Range("c15") * ch1 + Range("h15") * ch2 + Range("i15") * ch3 + Range("j15") * ch4) / 100
    Worksheets("´Á¤¤¦¨ÁZ").Range("c18").Value = (Range("c16") * ch1 + Range("h16") * ch2 + Range("i16") * ch3 + Range("j16") * ch4) / 100
    Worksheets("´Á¤¤¦¨ÁZ").Range("c19").Value = (Range("c17") * ch1 + Range("h17") * ch2 + Range("i17") * ch3 + Range("j17") * ch4) / 100
    Worksheets("´Á¤¤¦¨ÁZ").Range("c20").Value = (Range("c18") * ch1 + Range("h18") * ch2 + Range("i18") * ch3 + Range("j18") * ch4) / 100
    Worksheets("´Á¤¤¦¨ÁZ").Range("c21").Value = (Range("c19") * ch1 + Range("h19") * ch2 + Range("i19") * ch3 + Range("j19") * ch4) / 100
    Worksheets("´Á¤¤¦¨ÁZ").Range("c22").Value = (Range("c20") * ch1 + Range("h20") * ch2 + Range("i20") * ch3 + Range("j20") * ch4) / 100
    Worksheets("´Á¤¤¦¨ÁZ").Range("c23").Value = (Range("c21") * ch1 + Range("h21") * ch2 + Range("i21") * ch3 + Range("j21") * ch4) / 100
    Worksheets("´Á¤¤¦¨ÁZ").Range("c24").Value = (Range("c22") * ch1 + Range("h22") * ch2 + Range("i22") * ch3 + Range("j22") * ch4) / 100
    Worksheets("´Á¤¤¦¨ÁZ").Range("c25").Value = (Range("c23") * ch1 + Range("h23") * ch2 + Range("i23") * ch3 + Range("j23") * ch4) / 100
    Worksheets("´Á¤¤¦¨ÁZ").Range("c26").Value = (Range("c24") * ch1 + Range("h24") * ch2 + Range("i24") * ch3 + Range("j24") * ch4) / 100
    Worksheets("´Á¤¤¦¨ÁZ").Range("c27").Value = (Range("c25") * ch1 + Range("h25") * ch2 + Range("i25") * ch3 + Range("j25") * ch4) / 100
    Worksheets("´Á¤¤¦¨ÁZ").Range("c28").Value = (Range("c26") * ch1 + Range("h26") * ch2 + Range("i26") * ch3 + Range("j26") * ch4) / 100
    Worksheets("´Á¤¤¦¨ÁZ").Range("c29").Value = (Range("c27") * ch1 + Range("h27") * ch2 + Range("i27") * ch3 + Range("j27") * ch4) / 100
    Worksheets("´Á¤¤¦¨ÁZ").Range("c30").Value = (Range("c28") * ch1 + Range("h28") * ch2 + Range("i28") * ch3 + Range("j28") * ch4) / 100
    Worksheets("´Á¤¤¦¨ÁZ").Range("c31").Value = (Range("c29") * ch1 + Range("h29") * ch2 + Range("i29") * ch3 + Range("j29") * ch4) / 100
    Worksheets("´Á¤¤¦¨ÁZ").Range("c32").Value = (Range("c30") * ch1 + Range("h30") * ch2 + Range("i30") * ch3 + Range("j30") * ch4) / 100
    Worksheets("´Á¤¤¦¨ÁZ").Range("c33").Value = (Range("c31") * ch1 + Range("h31") * ch2 + Range("i31") * ch3 + Range("j31") * ch4) / 100
    Worksheets("´Á¤¤¦¨ÁZ").Range("c34").Value = (Range("c32") * ch1 + Range("h32") * ch2 + Range("i32") * ch3 + Range("j32") * ch4) / 100
    Worksheets("´Á¤¤¦¨ÁZ").Range("c35").Value = (Range("c33") * ch1 + Range("h33") * ch2 + Range("i33") * ch3 + Range("j33") * ch4) / 100
    Worksheets("´Á¤¤¦¨ÁZ").Range("c36").Value = (Range("c34") * ch1 + Range("h34") * ch2 + Range("i34") * ch3 + Range("j34") * ch4) / 100
    Worksheets("´Á¤¤¦¨ÁZ").Range("c37").Value = (Range("c35") * ch1 + Range("h35") * ch2 + Range("i35") * ch3 + Range("j35") * ch4) / 100
    Worksheets("´Á¤¤¦¨ÁZ").Range("c38").Value = (Range("c36") * ch1 + Range("h36") * ch2 + Range("i36") * ch3 + Range("j36") * ch4) / 100
    Worksheets("´Á¤¤¦¨ÁZ").Range("c39").Value = (Range("c37") * ch1 + Range("h37") * ch2 + Range("i37") * ch3 + Range("j37") * ch4) / 100
    Worksheets("´Á¤¤¦¨ÁZ").Range("c40").Value = (Range("c38") * ch1 + Range("h38") * ch2 + Range("i38") * ch3 + Range("j38") * ch4) / 100
    Worksheets("´Á¤¤¦¨ÁZ").Range("c41").Value = (Range("c39") * ch1 + Range("h39") * ch2 + Range("i39") * ch3 + Range("j39") * ch4) / 100
    Worksheets("´Á¤¤¦¨ÁZ").Range("c42").Value = (Range("c40") * ch1 + Range("h40") * ch2 + Range("i40") * ch3 + Range("j40") * ch4) / 100
    Worksheets("´Á¤¤¦¨ÁZ").Range("c43").Value = (Range("c41") * ch1 + Range("h41") * ch2 + Range("i41") * ch3 + Range("j41") * ch4) / 100
    Worksheets("´Á¤¤¦¨ÁZ").Range("c44").Value = (Range("c42") * ch1 + Range("h42") * ch2 + Range("i42") * ch3 + Range("j42") * ch4) / 100

GBKEE

¦^´_ 2# skyutm

1. Sub nn()
   
2.     Dim I As Integer, ch1 As Integer, ch2 As Integer, ch3 As Integer, ch4 As Integer
   
3.     For I = 3 To 43
   
4.         Worksheets("´Á¤¤¦¨ÁZ").Range("c" & 5 + I - 3).Value = (Range("c" & I) * ch1 + Range("h" & I) * ch2 + Range("i" & I) * ch3 + Range("j" & I) * ch4) / 100
   
5.     Next
   
6. End Sub

½Æ»s¥N½X

skyutm

¦^´_ 3# GBKEE
·PÁª©¤j¡A§Úª¾¹D­þ¸Ì¥X°ÝÃD¤F¡A§Ú¬Q¤Ñ¬O³o¼Ë¼g
For I = 3 To 40
Worksheets("´Á¤¤¦¨ÁZ").Range(c&(I+2)).Value = (Range("ci"  ) * ch1 + Range("hi"  ) * ch2 + Range("ii" ) * ch3 + Range("ji" ) * ch4) / 100
­ì¨Ó¬A¸¹¤º­n³o¼Ë¼g
Worksheets("´Á¤¤¦¨ÁZ").Range("c" & 5 + I - 3).Value = (Range("c" & I) * ch1 + Range("h" & I) * ch2 + Range("i" & I) * ch3 + Range("j" & I) * ch4) / 100
¥t¥~¡AÁÙ·Q¦A½Ð±Ð±z¡C¦³Ãö²Î­p¦¨ÁZªº»yªk¡A´N¬O»¡¦Ò100¤Àªº´X¤H¡A¦Ò90~99¤Àªº´X¤H....
    '¦¨ÁZ²Î­p'
    Dim myrange As Range
    For Each myrange In Worksheets("´Á¤¤µû¶q").Range("c3", Range("c3").End(xlDown))
    If myrange.Value = 100 Then Worksheets("sheet1").Range("a1") = myrange.Count
    ³o¼Ëªº»yªk¬O­þ¸Ì¸Ó­×§ï¡HÁÂÁ¡C

GBKEE

¦^´_ 4# skyutm

1. Option Explicit
   
2. Sub Ex()
   
3.     Dim Rng  As String, I As Integer, ¤À¼Æ¶¡®æ As Integer, Msg As String
   
4.     ¤À¼Æ¶¡®æ = 10             '¤]¥i¥H¶¡®æ 5¤À
   
5.     Rng = Worksheets("´Á¤¤µû¶q").Range("c3", Range("c3").End(xlDown)).Address   '­pºâ½d³òªºAddress
   
6.     'Application.Min(Range(Rng)) :³Ì§C¤À-> ÁÙ­n¥[1­Ó¤À¼Æ¶¡®æ ¨Ó°õ¦æ°j°é
   
7.     For I = 100 To Application.Min(Range(Rng)) - ¤À¼Æ¶¡®æ Step -¤À¼Æ¶¡®æ '- : ­t¼Æ°j°é»¼´î
   
8.         If I = 100 Then
   
9.             Msg = "100 = " & Application.Evaluate("SumProduct((" & Rng & ">=100)*1 )")
   
10.         Else
    
11.             Msg = Msg & Chr(10) & I + ¤À¼Æ¶¡®æ - 1 & "~" & I & " = " & Application.Evaluate("SumProduct((" & Rng & "<=" & I + ¤À¼Æ¶¡®æ - 1 & ")*(" & Rng & ">=" & I & " ))")
    
12.         End If
    
13.     Next
    
14.     MsgBox Msg
    
15. End Sub

½Æ»s¥N½X

¸É¤W¥t¤@¼gªk

1. Option Explicit
   
2. Sub Ex_CountIf()
   
3.     Dim Rng As Range, ¶¡¹j As Integer, A As Integer, B As Integer, I As Integer, AR(1 To 2)
   
4.     Set Rng = Range("A1:A" & [A1].End(xlDown).Row)  '²Î­pªº½d³ò
   
5.     ¶¡¹j = 10
   
6.     For I = 100 To Application.Min(Rng) - ¶¡¹j Step -¶¡¹j
   
7.         B = Application.CountIf(Rng, ">=" & I)
   
8.         'COUNTIF ¤u§@ªí¨ç¼Æ: ­pºâ¬Y½d³ò¤º²Å¦X¬Y·j´M·Ç«hªºÀx¦s®æ­Ó¼Æ
   
9.         If B - A > 0 Then
   
10.             AR(1) = AR(1) & "," & IIf(I < 100, I + ¶¡¹j - 1 & "~", "") & I
    
11.             AR(2) = AR(2) & "," & B - A
    
12.             A = B
    
13.         End If
    
14.     Next
    
15.     AR(1) = Application.Transpose(Split(Mid(AR(1), 2), ","))
    
16.     AR(2) = Application.Transpose(Split(Mid(AR(2), 2), ","))
    
17.     [B:C] = ""
    
18.     [B1].Resize(UBound(AR(1))) = AR(1)
    
19.     [C1].Resize(UBound(AR(2))) = AR(2)
    
20. End Sub

½Æ»s¥N½X

°²­±¶W¤H

¦^´_ 4# skyutm
§Ú¤]¸Õ¼g¤F¤@­Ó
§A¸Õ¬Ý¬Ý

¤À¼Æ¬O¶ñ¦bCÄæ¦ì

1. Sub ²Î­p()
   
2. 
   
3. a = 0
   
4. b = 0
   
5. c = 0
   
6. d = 0
   
7. e = 0
   
8. f = 0
   
9. myRowCount = Sheets("´Á¤¤µû¶q").Range("C1").CurrentRegion.Rows.Count '§PÂ_C1¨ì³Ì«á¤@µ§¸ê®Æ¦³¦C
   
10. 
    
11. For i = 1 To myRowCount
    
12.     myrange = Sheets("´Á¤¤µû¶q").Range("c" & i)
    
13.     Select Case myrange
    
14.         Case "100"
    
15.             a = a + 1
    
16.         Case "90" To "99"
    
17.             b = b + 1
    
18.         Case "80" To "89"
    
19.             c = c + 1
    
20.         Case "70" To "79"
    
21.             d = d + 1
    
22.         Case "60" To "69"
    
23.             e = e + 1
    
24.         Case "0" To "69"
    
25.             f = f + 1
    
26.     End Select
    
27.         
    
28. Next i
    
29. 
    
30. Sheets("sheet1").Range("A1").Value = a '100¤ÀÁ`¼Æ
    
31. Sheets("sheet1").Range("A2").Value = b '90-99Á`¼Æ
    
32. Sheets("sheet1").Range("A3").Value = c '80-89 Á`¼Æ
    
33. Sheets("sheet1").Range("A4").Value = d '70-79 Á`¼Æ
    
34. Sheets("sheet1").Range("A5").Value = e '60-69 Á`¼Æ
    
35. Sheets("sheet1").Range("A6").Value = f '0-59 ¤£¤Î®æÁ`¼Æ
    
36. End Sub

½Æ»s¥N½X

skyutm

·PÁ¨â¦ì¡I§Ú¥ý¥h¸Õ¸Õ¡A¦A¤W¨Ó³ø§i¦¨ªG¡C

GBKEE

¦^´_ 6# °²­±¶W¤H

1. 'Case "0" To "69" À³¬O-> Case "0" To "59"
   
2. '¦ý 6,7,8,9,µLªk­pºâ¨ì, ¥i¬O 101 ·|­pºâ¨ì
   
3. '«Øij­×§ï¦p¤U
   
4. For I = 1 To myRowCount
   
5.     myrange = Sheets("´Á¤¤µû¶q").Range("c" & I)
   
6.     Select Case myrange
   
7.         Case 100
   
8.             A = A + 1
   
9.         Case 90 To 99
   
10.             B = B + 1
    
11.         Case 80 To 89
    
12.             c = c + 1
    
13.         Case 70 To 79
    
14.             d = d + 1
    
15.         Case 60 To 69
    
16.             E = E + 1
    
17.         Case 0 To 59
    
18.             f = f + 1
    
19.     End Select

½Æ»s¥N½X

°²­±¶W¤H

¦^´_ 8# GBKEE

¯uªºA....
¨S´ú¸Õ¨ì6.7.8.9
­ì¨Ó¦³¥[""©M¨S¥[""®t¨º»ò¦h¡A³o¬Obug¶Ü?ÁÙ¬O?

¥t¥~
Select Case myrange.Value
³o¤@¦æ·|¥X²{¿ù»~¡A§Ú§âvalue®³±¼´NOK¤F!!

GBKEE

¦^´_ 9# °²­±¶W¤H
.Value ¤£ª¾¬°¦ó·|¥[¤Wªº,¤w§ó¥¿¤F