[µo°Ý] ½Ð¦U¦ì¤j­ô«e½ú«ü¥¿¦Û©w¨ç¼Æ¿ù»~ÂI

lamihsuen

½Ð¦U¦ì¤j­ôÀ°¦£¤U¦C»yªk¨º¸Ì¥X¿ù?¦]¬°¶Ç¦^ªº­È¬°0
Worksheets(wr) .Range("I3").Value = E178(.Range("A3").Value)  


Public Function E178(TN As Integer) As Double
Dim ei As Integer
ei = 1
For ei = 1 To TN
If  Worksheets(2).Cells(ei, 1).Value = TN Then
E178  = Worksheets(2).Cells(ei, 2).Value
End If
Next ei
End Function

oobird

1. Public Function EX(TN As Integer) As Double
   
2. Dim c  As Range
   
3. Set c = Sheets(2).[a:a].Find(TN, , , 1)
   
4. If Not c Is Nothing Then
   
5. EX = c(1, 2).Value
   
6. End If
   
7. End Function

½Æ»s¥N½X

§ï³o¼Ë¸Õ¸Õ¡C

GBKEE

¦^´_ 1# lamihsuen
§A³o¦Û­q¨ç¼Æ ¬O¨S¦³¿ù»~ªº
For ei = 1 To TN
If Worksheets(2).Cells(ei, 1).Value = TN Then   ³o¬O¦b Worksheets(2).AÄ椤¤ñ¹ï
E178 = Worksheets(2).Cells(ei, 2).Value         E178 ¶Ç¦^ Worksheets(2).BÄ檺­È: §A­nÀˬd³oBÄ檺­È¬O§_¬O§A´Á«Ý
End If
Next ei

lamihsuen

¦^´_ 3# GBKEE


    For ei = 1 To TN
If Worksheets(2).Cells(ei, 1).Value = TN Then   ³o¬O¦b Worksheets(2).AÄ椤¤ñ¹ï
E178 = Worksheets(2).Cells(ei, 2).Value         E178 ¶Ç¦^ Worksheets(2).BÄ檺­È: §A­nÀˬd³oBÄ檺­È¬O§_¬O§A´Á«Ý
End If
Next ei

¦p¤j¤j¤W¦C©Ò¨¥¦ýWorksheets(2).BÄæ ªº­È,¤£¬O0,±q°Ï°ìÅܵøµ¡Æ[¹îe178ªº­È©l²×¬O0.tnÅܼƬO¥¿½Tªº,¤U¦C¬OE178ªº­È¸òÄæ¦ì,¤£ª¾¬O§_¦³¨º¸Ì³]©w¤£¹ï?
A(Äæ)        B
1        
2        
3         1.153
4         1.463
5         1.672
6         1.822
7         1.938
8         2.032
9         2.110
10         2.176
11         2.234
12         2.285
13         2.331

GBKEE

¦^´_ 4# lamihsuen
¤W¶ÇÀɮ׬ݬÝ

lamihsuen

:'( :D ¦^´_ 2# oobird

·PÁ¤j¤j,¸Õ¥Î§Aªºµ{¦¡¤w¥i¶Ç¦^¥¿½Tªº­È,¦ýÁÙ¬O¤£À´§Úªºµ{¦¡°ÝÃD¥X¦b¨º¸Ì?

lamihsuen

[code][/code]¦^´_ 5# GBKEE

¹ï¤£°_­n¤W¶ÇÀɮ׮ɥX²{: ¤£¤ä«ù¤W¶Ç¦¹ÃþÂX®i¦Wªºªþ¥ó, ¥u¦nªþ¤Wµ{¦¡
Option Explicit

Sub °õ¦æ¤ÀªRµ²ªG()
Dim oi As Integer '³]outline­p¼Æ¾¹
Dim oj As Integer '­pºâ"NG"ªº®a¼Æ
Dim ei As Integer ' §äE178­Èªº­p¼Æ¾¹
Dim wr, xlRow As Integer '³]©wCOPY¤u§@ªí¼Æ¥Ø­p¼Æ¾¹
Dim an As Integer  'outline ¤ÀªR¦¸¼Æ .¨ÃÅã¥Ü¦b¼ÐÃD¦C(b4)
'³]©w¨C¤@­Ó¤u§@ªí¨C¤@¦³¤½¦¡ªºÀx¦s®æ,¶¶§Ç±q(²Ä1 ­Ó¤¸¯À)¶}©l
an = 1 ' ¶}©loutline ¤ÀªR¦¸¼Æ .
       For wr = 4 To Worksheets.Count
          With Worksheets(wr)
              xlRow = .Range("B" & .Rows.Count).End(xlUp).Row
                 '"A3"Àx¦s®æ NO.OF.RESULT­È
                 .Range("A3").Formula = "=COUNT(B6:B" & xlRow & ")"
                 '"B3"Àx¦s®æ¤ÀªR­È¤¤¶¡­È(MEDIAN)
                .Range("B3").Formula = "=MEDIAN(B6:B" & xlRow & ")"
                'C3Àx¦s®æIRQ´Ó
                .Range("C3").Formula = "=(QUARTILE(B6:B" & xlRow & ",3) -QUARTILE(B6:B" & xlRow & ",1))*0.7413"
                '"E3"Àx¦s®æROBUS CV­È
                .Range("E3").Formula = "=  C3 / B3 *100"
                '©w¸q"j3"Àx¦s®æ¬°¤ÀªR­È¥­§¡­È
                .Range("J3").Formula = "=AVERAGE(B6:B" & xlRow & ")"
                '"F3"Àx¦s®æ¤ÀªR­È¤¤³Ì¤Ö­È
                .Range("F3").Formula = "=MIN(B6:B" & xlRow & ")"
                '"G3"Àx¦s®æ¤ÀªR­È¤¤³Ì¤j­È
                .Range("G3").Formula = "=MAX(B6:B" & xlRow & ")"
                 '"H3"Àx¦s®æRANGE­È
                .Range("H3").Formula = "=G3-F3"
                '©w¸q"k3"Àx¦s®æ¬°stdv
                .Range("k3").Formula = "=STDEV(B6:B" & xlRow & ")"
                .Range("B4").Value = an
                '"C"Äæ Z-SCORE ­È
                .Range("C6:C" & xlRow & "").Formula = "=(B6-$B$3)/$C$3"
                ' "D"ÄæOUTLINE­È
                .Range("D6:D" & xlRow & "").Formula = "=(B6- $J$3)/$K$3"
                .Range("I3").Value = E178(.Range("A3").Value)
               
         End With
  
'Worksheets(wr).Range("I3")©w¸qE178­È¦bI3Àx¦s®æ
'For ei = 1 To 102
'If Worksheets(2).Cells(ei, 1).Value = Worksheets(wr).Range("A3").Value Then
'Worksheets(wr).Range("I3").Value = Worksheets(2).Cells(ei, 2).Value
'Exit For
'End If
'Next ei
'"E'Äæ§P§OOUTLINE,true="ok"FALSE="NG"

Worksheets(wr).Range("L3").Value = 0 '"NG"®a¼Æ°_©l­È
For oi = 6 To Worksheets(wr).Range("A3").Value + 5 '¶}©l§P§O±q²Ä6¦C¶}©l
'¤ñ¹ïD6¬O§_<   E178­È(I3)Äæ

If Worksheets(wr).Range("D" & oi).Value < Worksheets(wr).Range("I3").Value Then
'­È=TRUE®ÉEÄæ°O¿ý""OK"
Worksheets(wr).Range("E" & oi).Value = "OK"
Else
With Worksheets(wr)
     '­È= FLACE®ÉEÄæ°O¿ý"NG"
     .Range("E" & oi).Value = "NG"
     '³]"NG"FONT.COLOR¬°¬õ¦â
     .Range("E" & oi).Font.Color = vbRed
      '"NG"®a¼Æ+1
     .Range("L3").Value = .Range("L3").Value + 1
     End With

End If
Next oi
Next wr

End Sub


Public Function E178(TN As Integer) As Double
Dim ei As Integer
ei = 1
For ei = 1 To TN
If Worksheets(2).Cells(ei, 1).Value = TN Then
E178 = Worksheets(2).Cells(ei, 2).Value
End If
  Next ei
   
End Function

oobird

¦^´_ 6# lamihsuen

­«ÂI¦b¦Û­q¨ç¼Æªº©R¦W¤W
¤£­n¦³¼Æ¦r¡I
ÁÙ¦³¤W¶Çªþ¥ó­n¥ýÀ£ÁY¡I

lamihsuen

¦^´_ 8# oobird

¦ý¬O§Ú±N§Aªºex©R¦W§ïE178ÁÙ¬O¥i¥H,¤§«e§Ú¥Î¤U¦C¤è¦¡¤]¥i¶Ç¦^¥¿½T­È
    'Worksheets(wr).Range("I3")©w¸qE178­È¦bI3Àx¦s®æ
For ei = 1 To 102
If Worksheets(2).Cells(ei, 1).Value = Worksheets(wr).Range("A3").Value Then
Worksheets(wr).Range("I3").Value = Worksheets(2).Cells(ei, 2).Value
Exit For
End If
Next ei