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

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

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

¦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

¸É¥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

TOP

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

TOP

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

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2012-8-24 20:56 ½s¿è

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

TOP

¥»©«³Ì«á¥Ñ °²­±¶W¤H ©ó 2012-8-24 15:24 ½s¿è

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

¤À¼Æ¬O¶ñ¦bCÄæ¦ì
  1. Sub ²Î­p()

  2. a = 0
  3. b = 0
  4. c = 0
  5. d = 0
  6. e = 0
  7. f = 0
  8. myRowCount = Sheets("´Á¤¤µû¶q").Range("C1").CurrentRegion.Rows.Count '§PÂ_C1¨ì³Ì«á¤@µ§¸ê®Æ¦³¦C

  9. For i = 1 To myRowCount
  10.     myrange = Sheets("´Á¤¤µû¶q").Range("c" & i)
  11.     Select Case myrange
  12.         Case "100"
  13.             a = a + 1
  14.         Case "90" To "99"
  15.             b = b + 1
  16.         Case "80" To "89"
  17.             c = c + 1
  18.         Case "70" To "79"
  19.             d = d + 1
  20.         Case "60" To "69"
  21.             e = e + 1
  22.         Case "0" To "69"
  23.             f = f + 1
  24.     End Select
  25.         
  26. Next i

  27. Sheets("sheet1").Range("A1").Value = a '100¤ÀÁ`¼Æ
  28. Sheets("sheet1").Range("A2").Value = b '90-99Á`¼Æ
  29. Sheets("sheet1").Range("A3").Value = c '80-89 Á`¼Æ
  30. Sheets("sheet1").Range("A4").Value = d '70-79 Á`¼Æ
  31. Sheets("sheet1").Range("A5").Value = e '60-69 Á`¼Æ
  32. Sheets("sheet1").Range("A6").Value = f '0-59 ¤£¤Î®æÁ`¼Æ
  33. End Sub
½Æ»s¥N½X

TOP

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

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2012-8-25 05:55 ½s¿è

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

TOP

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

TOP

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

TOP

        ÀR«ä¦Û¦b : ¤H¥Í¨S¦³©Ò¦³Åv¡A¥u¦³¥Í©Rªº¨Ï¥ÎÅv¡C
ªð¦^¦Cªí ¤W¤@¥DÃD