- ©«¤l
- 2035
- ¥DÃD
- 24
- ºëµØ
- 0
- ¿n¤À
- 2031
- ÂI¦W
- 0
- §@·~¨t²Î
- Win7
- ³nÅ骩¥»
- Office2010
- ¾\ŪÅv
- 100
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2012-3-22
- ³Ì«áµn¿ý
- 2024-2-1
|
¦^´_ 8# lamihsuen
½Ð±N쥻 Module3 ¤ºªºµ{¦¡½X¥þ³¡§ó´«¦¨¥H¤U¤§µ{¦¡½X- ' *********************************************************************
- ' Module3 (½Ð±N쥻 Module3 ¤ºªºµ{¦¡½X¥þ³¡§ó´«¦¨¥H¤U¤§µ{¦¡½X)
- ' *********************************************************************
- Option Explicit
-
- Dim sPos(1 To 4)
- Dim xText As String
- Dim Chart_Source As Variant
- Dim StartKBarRow, EndKBarRow As Long
-
- Public Sub ¦A¤ÀªRµ²ªG()
- Dim wr As Integer, an As Integer ' ³]©wCOPY¤u§@ªí¼Æ¥Øp¼Æ¾¹
- Dim xlRow As Long
-
- ' wr = 4 ¥Ø«e¹w³]±q c ¨ì sn¡A Á`¦@¦³ 12 Ó¤u§@ªí³æ
- For wr = 4 To Worksheets.Count
- ' ¤U¦¸°_©l ¦C °_ÂI
- Dim angin_sr As Integer
- Dim sr As Integer ' ©w¸q²Ä¤@¦¸°_©l¦C¼Æ
- sr = 6
- an = 1
- ' pºâ"NG"ªº®a¼Æ
- Dim NGVALUE As Integer
- NGVALUE = 0
- ' ¤u§@ªí±q4 °j°é¶}©l°õ¦æ¦A¤ÀªRµ²ªG¨ì¤u§@ªíªº³Ì«á
- Do Until Worksheets(wr).Cells((sr - 3), 12).Value = 0
- ' outline ¤ÀªR¦¸¼Æ+1.¨ÃÅã¥Ü¦b¼ÐÃD¦C(B4)Àx¦s®æ
- an = an + 1
-
- ' angin_sr=¦A¦¸¤ÀªR°_©l¦C¬°:²Ä¤@¦¸°_©l¦C¼Æ+²Ä¤@¦¸®a¼Æ+6®æªÅ®æ
- angin_sr = sr + (Worksheets(wr).Cells((sr - 3), 1).Value + 6)
-
- ' §P§O ¤W¦¸¤ÀªR¦³"NG"ªº¥h°£,"OK"ªºCOPY¨ì¥»¦¸ªí®æ
- Dim again_oi, again_oj As Integer ' ¤W¦¸ªí®æ¦C,¦æ¼Æp¼Æ¾¹
- Dim again_ai, again_aj As Integer ' ¥»¦¸ªí®æ¦C,¦æ¼Æp¼Æ¾¹
-
- again_oi = sr
- again_ai = angin_sr
- ' °j°é§P§O"ok"»P"ng"±q«e¦¸°_©l¦ì¸m¶}©l(sr)¦Ü«e¦¸ªí®æ³Ì«á(sr+«e¦¸ªí®æ"a"3ªºÈ
- For again_oi = again_oi To sr + Worksheets(wr).Cells((sr - 3), 1).Value
- ' ¦pªGȬ°"ok"¸Ó¦Ccopy ¨ì¥»¦¸ªí®æ.¦pªGȬ°"ng"«h¤£³B²z
- If Worksheets(wr).Cells(again_oi, 5).Value = "OK" Then
- With Worksheets(wr)
- .Cells(again_ai, 1).Value = .Cells(again_oi, 1).Value
- .Cells(again_ai, 2).Value = .Cells(again_oi, 2).Value
- End With
- again_ai = again_ai + 1
- End If
- Next again_oi
-
- ' ±N«e¦¸²Ä¤T¦C¼ÐÃD¦C¼ÐÃDcopy ¦Ü¥»¦¸ªí®æ²Ä¤T¦C¼ÐÃD¦C¼ÐÃD
- again_aj = 1
- For again_oj = 1 To 12
- Worksheets(wr).Cells(angin_sr - 4, again_aj).Value = Worksheets(wr).Cells(sr - 4, again_oj).Value
- again_aj = again_aj + 1
- Next again_oj
-
- ' §Q¥ÎÅܼƨD¥X¥»¦¸ªí®æ³Ì«á¦C¼Æ¥Øªº¥Î©ó¨D¥X²Ä¤T¦C¤½¦¡ªº³Ì«á½d³ò
- xlRow = Worksheets(wr).Range("B" & angin_sr).End(xlDown).Row
- With Worksheets(wr)
- ' pºâ¥»¦¸"A3"Àx¦s®æ NO.OF.RESULTÈ(¤ÀªRÈ®a¼Æ)±q¥»¦¸°_©l¦C¦Ü(angin_sr)¦Ü¥»¦¸³Ì«á¦C¼Æªº¼Æ¶q
- .Cells(angin_sr - 3, 1).Formula = "=COUNT(B" & angin_sr & ":B" & xlRow & ")"
- ' ¥»¦¸"B3"Àx¦s®æ¤ÀªRȤ¤¶¡È(MEDIAN)
- .Cells(angin_sr - 3, 2).Formula = "=MEDIAN(B" & angin_sr & ":B" & xlRow & ")"
- ' ¥»¦¸C3Àx¦s®æIRQ´Ó
- .Cells(angin_sr - 3, 3).Formula = "=(QUARTILE(B" & angin_sr & ":B" & xlRow & ",3) -QUARTILE(B" & angin_sr & ":B" & xlRow & ",1))*0.7413"
-
- ' ¥»¦¸"E3"Àx¦s®æROBUS CVÈ
- .Cells(angin_sr - 3, 5).Formula = "= C3 / B3 *100"
- ' ¥»¦¸"F3"Àx¦s®æ¤ÀªRȤ¤³Ì¤ÖÈ
- .Cells(angin_sr - 3, 6).Formula = "=MIN(B" & angin_sr & ":B" & xlRow & ")"
- ' ¥»¦¸"G3"Àx¦s®æ¤ÀªRȤ¤³Ì¤jÈ
- .Cells(angin_sr - 3, 7).Formula = "=MAX(B" & angin_sr & ":B" & xlRow & ")"
- ' ¥»¦¸"H3"Àx¦s®æRANGEÈ
- .Cells(angin_sr - 3, 8).Formula = "=G3-F3"
- ' ¥»¦¸©w¸q"I3"Àx¦s®æ¬°E178È
- .Cells(angin_sr - 3, 9).Value = E178(.Cells(angin_sr - 3, 1).Value)
- ' ¥»¦¸©w¸q"j3"Àx¦s®æ¬°¤ÀªRÈ¥§¡È
- .Cells(angin_sr - 3, 10).Formula = "=AVERAGE(B" & angin_sr & ":B" & xlRow & ")"
- ' ¥»¦¸©w¸q"k3"Àx¦s®æ¬°stdv
- .Cells(angin_sr - 3, 11).Formula = "=STDEV(B" & angin_sr & ":B" & xlRow & ")"
- ' ¥»¦¸"NG"®a¼Æ È
- .Cells(angin_sr - 3, 12).Value = NGVALUE
- ' ¥»¦¸¼ÐÃD¦C"°õ¦æ²Ä"¦r¦êª½±µ±q«e¦¸Àx¦s®æ¦ì¸mcopy
- .Cells(angin_sr - 2, 1).Value = .Cells(sr - 2, 1).Value
- ' ¥»¦¸¼ÐÃD¦CÅã¥Ü²Äan¦¸¤ÀªR
- .Cells(angin_sr - 2, 2).Value = an
- ' ¥»¦¸¼ÐÃD¦C"ouline"¦r¦êª½±µ±q«e¦¸Àx¦s®æ¦ì¸mcopy
- .Cells(angin_sr - 2, 3).Value = .Cells(sr - 2, 3).Value
- ' ¥»¦¸©w¸q¹êÅç«Ç½s¸¹¼ÐÃD¦Cª½±µ±q«e¦¸Àx¦s®æ¦ì¸mcopy
- .Cells(angin_sr - 1, 1).Value = .Cells(sr - 1, 1).Value
- ' ¥»¦¸©w¸q¹êÅç«Ç¤ÀªRȼÐÃD¦Cª½±µ±q«e¦¸Àx¦s®æ¦ì¸mcopy
- .Cells(angin_sr - 1, 2).Value = .Cells(sr - 1, 2).Value
- ' ¥»¦¸©w¸qz-scoreȼÐÃD¦Cª½±µ±q«e¦¸Àx¦s®æ¦ì¸mcopy
- .Cells(angin_sr - 1, 3).Value = .Cells(sr - 1, 3).Value
- ' ¥»¦¸©w¸qoutline¼ÐÃD¦Cª½±µ±q«e¦¸Àx¦s®æ¦ì¸mcopy
- .Cells(angin_sr - 1, 4).Value = .Cells(sr - 1, 4).Value
- ' ©w¸q§P©wµ²ªGȼÐÃD¦Cª½±µ±q«e¦¸Àx¦s®æ¦ì¸mcopy
- .Cells(angin_sr - 1, 5).Value = .Cells(sr - 1, 5).Value
- ' ¥»¦¸"C"Äæ Z-SCORE Èpºâ
- .Range("C" & angin_sr & " :C" & xlRow).Formula = "=(b" & angin_sr & "-$B$" & angin_sr - 3 & ") /$C$" & angin_sr - 3
- ' ¥»¦¸ "D"ÄæOUTLINEÈpºâ
- .Range("d" & angin_sr & " :d" & xlRow).Formula = "= (B" & angin_sr & " -$J$" & angin_sr - 3 & ")/$K$" & angin_sr - 3
-
- End With
-
- ' ¥»¦¸"E' Äæ§P§OOUTLINE, true="ok" FALSE="NG"
- ' "NG"®a¼Æ°_©lÈ ,¥Øªº"±N¦³"ng"®a¼Æ°O¿ý¦b"L3"Äæ¦ì¥Î©ó¬O§_Ä~Äò§P§OOUTLINE
-
- Worksheets(wr).Range("L" & angin_sr - 3).Value = 0
-
- ' ¶}©l§P§O±q¥»¦¸°_©l¦C¶}©l
- again_ai = angin_sr
-
- For again_ai = again_ai To ((angin_sr) + Worksheets(wr).Range("A" & angin_sr - 3).Value - 1)
- ' ¤ñ¹ïD6¬O§_< E178È(I3)Äæ
- If Worksheets(wr).Range("D" & again_ai).Value < Worksheets(wr).Range("I" & angin_sr - 3).Value Then
- ' È=TRUE®ÉEÄæ°O¿ý""OK"
- Worksheets(wr).Range("E" & again_ai).Value = "OK"
- Else
- ' È= FLACE®ÉEÄæ°O¿ý"NG"
- Worksheets(wr).Range("E" & again_ai).Value = "NG"
- ' ³]"NG"FONT.COLOR¬°¬õ¦â
- Worksheets(wr).Range("E" & again_ai).Font.Color = vbRed
- ' "NG"®a¼Æ+1
- Worksheets(wr).Range("L" & angin_sr - 3).Value = Worksheets(wr).Range("L" & angin_sr - 3).Value + 1
- End If
- Next again_ai
-
- ' ¦pªGÁÙ¦³"NG"ÈÄ~Äò°õ¦æOUTLINE
- ' ±N¥»¦¸ªí®æ¦C¼Æȳ]©wµ¹¤W¦¸ªí®æ¦C¼Æ¥Øªº±N¥»¦¸ªí®æ¦C¼Æ§@¬°¤U¦¸ªí®æpºâ°ò¦
- sr = angin_sr
- Loop
-
- ' ********¦¹³B¬°Worksheets(wr).Cells((sr - 3), 12).Value =0."NG" ®a¼Æ=0 ¥þ³¡""¥i°õ¦æZ-SCORE
- ' ©w¸qz_sr¬°z_score°_©lªí®æ¦C
- Dim z_sr As Integer
- angin_sr = sr ' ±NsrȦ^´pµ¹angin_sr¥Øªº¬°§â³Ì«á¤@¦¸outline "sr=angin_sr"¦^´p¦^¨Ó
- sr = 6 ' ±Nsr¦^´p¨ì²Ä¤@¦¸ªí®æ°_©l¦ì¸m
-
- ' z_sr=°õ¦æz-score°_©l¦C¬°:³Ì«á¤@¦¸°_©l¦C¼Æ+³Ì«á¤ÀªR®a¼Æ+6®æªÅ®æ
- z_sr = angin_sr + (Worksheets(wr).Cells((angin_sr - 3), 1).Value + 6)
-
- ' ³]©w¤¸¯Àz_score¼ÐÃD¦C¼ÐÃD(z_scoreªº²Ä¤T¦C)
- Worksheets(wr).Cells((z_sr - 2), 1).Value = "°õ¦æ"
- Worksheets(wr).Cells((z_sr - 2), 2).Value = Worksheets(wr).Name
- Worksheets(wr).Cells((z_sr - 2), 3).Value = "z_score"
-
- ' ±N²Ä¤@¦¸ªí®æ"¹êÅç«Ç½s¸¹"(²Ä¤@¦æ¸¹)»P"¤ÀªRÈ"(²Ä¤G¦æ)copy¦Üz_scoreªí®æ¦]¬°n¥þ³¡"¹êÅç«Ç½s¸¹"»P"¤ÀªRÈ"
-
- Dim z_oi, z_oj As Integer ' ²Ä¤@¦¸ªí®æ¦C,¦æ¼Æp¼Æ¾¹
- Dim z_ai, z_aj As Integer ' z-scoreªí®æ¦C,¦æ¼Æp¼Æ¾¹
- z_oi = sr ' z_oi©w¸q¬°²Ä¤@¦¸°_©l®æªí®æ¶}©lp¼Æ
- z_ai = z_sr ' z_ai©w¸q¬°z_score°_©lªí®æ¶}©lp¼Æ
-
- For z_oi = z_oi To sr + Worksheets(wr).Cells((sr - 3), 1).Value
- With Worksheets(wr)
- .Cells(z_ai, 1).Value = .Cells(z_oi, 1).Value
- .Cells(z_ai, 2).Value = .Cells(z_oi, 2).Value
- End With
- z_ai = z_ai + 1
- Next z_oi
½Æ»s¥N½X
|
|