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

[µo°Ý] replace missing value (¨D±Ï)

¦^´_ 10# jj369963

³oÀɮתºªÅ®æ¹ê¦b§ä¤£¥X¨ì©³ÁôÂìƻò¬Ý¤£¨£ªº¦r¤¸¡A©Ò¥H­Y¨Ï¥ÎApplication.Counta¨ç¼Æ¥hºâ¼Æ¶q·|§ä¤£¨ìªÅ®æ
¥u¦n¨C®æ¥hÀˬd
¦A«h¸ê®Æ°Ï°ì§ïÅÜ¡AÅܼƥ綷§ïÅÜ
§â±b±KÀÉ®×»PEXCELÀÉ©ñ¦b¦P¤@¥Ø¿ý¡A¸Õ¸Õ
  1. Sub ex()
  2. Dim Rng As Range, A As Range, C As Range
  3. Set dic = CreateObject("Scripting.Dictionary")
  4. fs = ThisWorkbook.Path & "\replace_rule.txt"
  5. Close #1

  6. Open fs For Input As #1
  7. Do Until EOF(1)
  8.   Line Input #1, mystr
  9.   If InStr(mystr, "=") > 0 Then
  10.   n = Application.Match(Trim(Split(mystr, "=")(0)), Rows(1), 0)
  11.   x = Split(Replace(Replace(Replace(Replace(Split(mystr, "=")(1), "MEAN(", ""), ")", ""), ".", ""), " ", ""), ",")
  12.   For Each ky In x
  13.     dic(Trim(ky)) = n
  14.   Next
  15.   End If
  16. Loop
  17. Close #1
  18. r = 2
  19. Do Until Cells(r, 1) = ""
  20.   Set Rng = Cells(r, 8).Resize(, 88) '¦]¬°±qHÄæ¶}©l§äªÅ®æ¡A©Ò¥H§ï¬°Cells(r, 8)
  21.   For Each A In Rng
  22.   If A = "" Then
  23.   k = A.Column
  24.   v = Trim(Cells(1, k).Value)
  25.   s = dic(v)
  26.   A = Cells(r, s)
  27.   End If
  28.   Next
  29. r = r + 1
  30. Loop
  31. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2013-11-15 08:13 ½s¿è

¦^´_ 11# Hsieh


    Dearª©¥D¤j¤j:

¤£¦n·N«ä¡A¤£§÷ªº§Ú¤S¨Ó¨D±Ï¤F¡A
¨Ì±zªºvbaµ{¦¡ ½T¹ê¦³¶]¥X¨Ó¡A·P¿E
¦ý¬O´«¨ì¨ä¥¦¤@¼ËªºÀɮ׸ոաA´N·|Åã¥Ü ¡y§ä¤£­ËÀɮתº°T®§¡z¡A¦pªþÀÉ

¥t¥~ Àɮפ¤ AÄæ»PBÄæ ¦³¥Îvlookup§ä±b ±K¡A¦pªG¥i¥H¬O§_¥i¥H¼g¶iVBA¸Ì??

¸U¤À·PÁÂ

¦A·Ð½Ð«ü±Ð¤£¤~ªº§Ú

[attach]16220[/attach]

TOP

¦^´_ 11# Hsieh


   Dearª©¥D¤j¤j:

          ±z¦n¡A§Ñ°O»¡¤F¡A¬O§_¥i¥H§â¶]¥X¨Óªº¼Æ­È¦A¥|±Ë¤­¤J(round)Åܦ¨¾ã¼Æ«á¡A¦A¨ú¥Nmising ­È©O?

  
           ¦A³Â·Ð»P¤W­z°ÝÃD  ¤@¦P«ü±Ð

·PÁÂ

TOP

¦^´_ 12# jj369963


    §ä¤£¨ìÀɮתº°T®§¡A¥i¯à¬OTXTÀÉ»P¸ÓEXCELÀɮרS¦³©ñ¦b¦P¤@­Ó¸ê®Æ§¨¤º
¦]¬°§Ú´ú¸Õ®É¨ÃµL¦¹±¡ªpµo¥Í¡A¥u¬Oµo²{­Y¸ÓªÅ®æªº°Ñ·Ó¨Ã¥¼³Q¥k°¼¤½¦¡©Ò¤Þ¥Î®É´N·|¥X¿ù
¸Õ¸Õ¥H¤Uµ{¦¡½X¬Ý¬Ý
  1. Sub ex()
  2. Dim Rng As Range, A As Range, C As Range
  3. Set dic = CreateObject("Scripting.Dictionary")
  4. Set dic1 = CreateObject("Scripting.Dictionary")

  5. fs = ThisWorkbook.Path & "\replace_rule.txt"
  6. Close #1

  7. Open fs For Input As #1
  8. Do Until EOF(1)
  9.   Line Input #1, mystr
  10.   If InStr(mystr, "=") > 0 Then
  11.   n = Application.Match(Trim(Split(mystr, "=")(0)), Rows(1), 0)
  12.   x = Split(Replace(Replace(Replace(Replace(Split(mystr, "=")(1), "MEAN(", ""), ")", ""), ".", ""), " ", ""), ",")
  13.   For Each ky In x
  14.     dic(Trim(ky)) = n
  15.   Next
  16.   End If
  17. Loop
  18. Close #1
  19. With Sheet2
  20. For Each A In .Range(.[A2], .[A2].End(xlDown))
  21.    dic1(A.Value) = Array(A.Offset(, 2).Value, A.Offset(, 3).Value) '¬ö¿ý±b±K
  22. Next
  23. End With
  24. r = 2
  25. With Sheets("Sheet0")
  26. Do Until .Cells(r, 1) = ""
  27. .Cells(r, 1).Resize(, 2) = dic1(.Cells(r, 6).Value) '¶ñ¤J±b±K
  28.   Set Rng = .Cells(r, 8).Resize(, 88) '¦]¬°±qHÄæ¶}©l§äªÅ®æ¡A©Ò¥H§ï¬°.Cells(r, 8)
  29.   For Each A In Rng
  30.   If A = "" Then
  31.   k = A.Column
  32.   v = Trim(.Cells(1, k).Value)
  33.   s = dic(v)
  34.   If s <> "" Then A = Round(.Cells(r, s), 0) Else: A = "µL¤Þ¥Î" '¦pªGªÅ®æ¦³³Q¤½¦¡°Ñ·Ó«h¶ñ¤J¼Æ­È¡AµL«h¶ñ¤J"µL¤Þ¥Î"¦r¦ê
  35.   End If
  36.   Next
  37. r = r + 1
  38. Loop
  39. End With
  40. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2013-11-15 08:13 ½s¿è

¦^´_ 14# Hsieh

    Dearª©¥D¤j¤j:
                 ÁÂÁ¤j¤jªº¦^À³¡A«D±`·PÁ¡A¥Î¤F¤W­z¤½¦¡¥Î¦b¦P£¸Àɮצ¨¥\¤F¡C¯u¬Oamazing·PÁ¡C
             ¦ý¬O£¸ªi¥¼¥­¤@ªi¤S°_¡A¥Î¦A¥t¤@­ÓÀɮ׫o¤S¥X²{°ÝÃD¤F¡A¦pªþÀÉ¡C©Ò¥H¤S«pÁy¥Ö¨Ó¨D±Ï¤F¡A  ¯u¬Oªº§Ú><

       ¥t¥~¡A¦]¬°Ãþ¦üªºÀɮצ³ªñ¦Ê­Ó¡A¨ä¤¤ ¦³­«½Æ°µµª¡A©Ò¥H§xÃø«×¥[²`¤F
         1.  »Ý¥ý³B²z cell¸Ì­«½Æ§@µªªº¼Æ¦r³¡¥÷¡A­^¤å¤£¥Î
              cell¸Ìªº¼Æ¦r¡A¦pªG½Æ¿ï·|¥Î³rÂI¹j¶}¡A¦p(1,2,3,4,5) ¬O§_¥i¥H¥ý§â½Æ¿ïªºcell¥­§¡«á(1+2+3+4+5)¡A¨Ãround¥|±Ë¤­¤J¶ñ¤J¡A¤£ª¾³o¼Ë¥i¦æ¶Ü?

         2.¤§«á¦A§ämissing value ¡Aª½±µ¨Ì replace_rule.txt ¤½¦¡°µ¨ú¥N
             ¥t¥~¦pªG¥i¥H¬O§_¥i¥H¤£­n°Ñ·ÓexcelªºCR ¨ìDCÄæ¡A ª½±µ¨Ì replace_rule.txt
            ¦]¬°¨ä¹ê¡@R¡@I¡@¢Ï¡@S¡@¢Ó¡@C¡@¬OÁÙ­n­¼¥H5/3¡A¤]´N¬O(excel¤W)
             R = MEAN(²Ä4ÃD,²Ä6ÃD,²Ä19ÃD,²Ä24ÃD,²Ä27ÃD,²Ä39ÃD,²Ä43ÃD,²Ä47ÃD) *5/3
             I = MEAN(²Ä5ÃD,²Ä7ÃD,²Ä15ÃD,²Ä20ÃD,²Ä22ÃD,²Ä29ÃD,²Ä35ÃD,²Ä40ÃD) *5/3
             A = MEAN(²Ä2ÃD,²Ä9ÃD,²Ä12ÃD,²Ä18ÃD,²Ä23ÃD,²Ä28ÃD,²Ä32ÃD,²Ä36ÃD) *5/3
             S = MEAN(²Ä3ÃD,²Ä11ÃD,²Ä25ÃD,²Ä30ÃD,²Ä33ÃD,²Ä37ÃD,²Ä41ÃD,²Ä45ÃD) *5/3
             E = MEAN(²Ä8ÃD,²Ä13ÃD,²Ä16ÃD,²Ä21ÃD,²Ä31ÃD,²Ä38ÃD,²Ä42ÃD,²Ä48ÃD) *5/3
             C = MEAN(²Ä1ÃD,²Ä10ÃD,²Ä14ÃD,²Ä17ÃD,²Ä26ÃD,²Ä34ÃD,²Ä44ÃD,²Ä46ÃD) *5/3

           ¦ý¬O¦]¬°­n¹ïÀ³  ¨ú¥Nmissing value ¬O¨S¦³­¼¥H5/3¡A¤]´N¬O
             R = MEAN(²Ä4ÃD,²Ä6ÃD,²Ä19ÃD,²Ä24ÃD,²Ä27ÃD,²Ä39ÃD,²Ä43ÃD,²Ä47ÃD)
             I = MEAN(²Ä5ÃD,²Ä7ÃD,²Ä15ÃD,²Ä20ÃD,²Ä22ÃD,²Ä29ÃD,²Ä35ÃD,²Ä40ÃD)
             A = MEAN(²Ä2ÃD,²Ä9ÃD,²Ä12ÃD,²Ä18ÃD,²Ä23ÃD,²Ä28ÃD,²Ä32ÃD,²Ä36ÃD)
             S = MEAN(²Ä3ÃD,²Ä11ÃD,²Ä25ÃD,²Ä30ÃD,²Ä33ÃD,²Ä37ÃD,²Ä41ÃD,²Ä45ÃD)
             E = MEAN(²Ä8ÃD,²Ä13ÃD,²Ä16ÃD,²Ä21ÃD,²Ä31ÃD,²Ä38ÃD,²Ä42ÃD,²Ä48ÃD)
             C = MEAN(²Ä1ÃD,²Ä10ÃD,²Ä14ÃD,²Ä17ÃD,²Ä26ÃD,²Ä34ÃD,²Ä44ÃD,²Ä46ÃD)
              ©Ò¥H¦pªG¥i¥H¬O§_¥i¥H¤£­n°Ñ·ÓexcelªºCR ¨ìDCÄæ¡A ª½±µ¨Ì replace_rule.txt ¡A¤£ª¾³o¼Ë¥i¦æ¶Ü?

          3.³Ì«á±b±Kªº³¡¥÷¡A½Ð°Ý¬O§_¥i¥Hª½±µ §âvlookup¤½¦¡¼g¤J(¤£¦A¶K¤½¦¡)¡A¤]´N¬O¦bsheet1©ñ±b±K¸ê°T¡A¹ï·Ó¨ì
            sheet0ªºuser»P passwordÄæ¦ì¡A³Ì«á¦A¶i¦æ¿ý»s¡A¤£ª¾³o¼Ë¥i¦æ¶Ü?

            ¨ä¹ê°ÝÃDÁÙÆZÃøªº¡A©Ò¥H·Q°Ý¥i¦æ«×¡A·íµM¦pªG¤£¦æ¡A¤]§Æ±æ¦hµ¹«Øij(¶V¨Ó¶Vı±o¦Û¤v¤Ó«pÁy¥Ö¤F)
            ³Ì«á·PÁª©¥D¤j¤j¨C¦¸³£¶W¯Å´Á«Ý±zªº¦^«H¡A²`²`·P¿E
         
           ³Ì«á¦A³Â·Ð¦^À³«ü±Ð¡A·PÁÂ
                                               [attach]16232[/attach]

TOP

¦^´_ 14# Hsieh

Dearª©¥D¤j¤j:
      ¤£¦n·N«ä¡A¤S¨Ó¥oÂZ¡C
     ®Ú¾Ú¤W¤@½gªº
     1.  »Ý¥ý³B²z cell¸Ì­«½Æ§@µªªº¼Æ¦r³¡¥÷¡A­^¤å¤£¥Î
              cell¸Ìªº¼Æ¦r¡A¦pªG½Æ¿ï·|¥Î³rÂI¹j¶}¡A¦p(1,2,3,4,5) ¬O§_¥i¥H¥ý§â½Æ¿ïªºcell¥­§¡«á(1+2+3+4+5)¡A¨Ãround¥|±Ë¤­¤J¶ñ¤J¡A
           ¦]¬°§Úı±o¤Ó§xÃø¤F¡A§ï¦¨  §â½Æ¿ï§@µª²MªÅ·í¦¨missing­È¡A³o¼Ë¬O§_¤ñ¸û²³æ?? ¦pªGvbaµLªk½Æ¿ï¥­§¡¡A¨º¬O§_¥i¥H²MªÅ½Æ¿ï·ímissing


      2.  ¤§«á¦A§ämissing value ¡Aª½±µ¨Ì replace_rule.txt ¤½¦¡°µ¨ú¥N
             ¥t¥~¦pªG¥i¥H¬O§_¥i¥H¤£­n°Ñ·ÓexcelªºCR ¨ìDCÄæ¡A ª½±µ¨Ì replace_rule.txt ???
              ¦^ÂÐ:¦ý¬O¦pªGVBAµLªkª½±µ°Ñ·Óreplace_rule.txt¡A´N¬OÁÙ¬O»Ý­n°Ñ·ÓexcelªºCR ¨ìDCÄæ¡A¨º´N¨Ì­ì¥»ªº

        3.³Ì«á±b±Kªº³¡¥÷¡A½Ð°Ý¬O§_¥i¥Hª½±µ §âvlookup¤½¦¡¼g¤J(¤£¦A¶K¤½¦¡)¡A¤]´N¬O¦bsheet1©ñ±b±K¸ê°T¡A¹ï·Ó¨ì
            sheet0ªºuser»P passwordÄæ¦ì¡A³Ì«á¦A¶i¦æ¿ý»s¡A¤£ª¾³o¼Ë¥i¦æ¶Ü?
         
                ¦^ÂÐ:  §âvlookup¤½¦¡¼g¤J(¤£¦A¶K¤½¦¡)¡A¤]´N¬O¦bsheet1©ñ±b±K¸ê°T¡A¹ï·Ó¨ì
            sheet0ªºuser»P passwordÄæ¦ì
¡A³o³¡¥÷¤£»Ý­n¡A¨Ì­ì¥»ªº¿ý»s±b±K§Y¥i

                    ¥D­n¬O°w¹ï ²Ä1ÂI ªº½Æ¿ï³B²z¡A§Æ±æ¤j¤j¤©¥H¨ó§U©Î«Øij
                   ¯u¬O©êºp¦Ê¦£¥oÂZ¡A¦A½Ð¦h«ü±Ð
                           ¸U¤À·PÁÂ

TOP

¦^´_ 16# jj369963

¸Õ¸Õ¬Ý
  1. Sub Replace_Blank()
  2. Dim A As Range, Ar(), B As Range
  3. Set Upw = CreateObject("Scripting.Dictionary") '±b±K
  4. Set dic = CreateObject("Scripting.Dictionary") '±b±K
  5. fs = ThisWorkbook.Path & "\replace_rule.txt" 'TEXTÀɮצì¸m
  6. Close #1 '­Y¤w¸g¶}±Ò´N¥ýÃö³¬
  7. With Sheet1
  8. Open fs For Input As #1
  9. Do Until EOF(1)
  10.    Line Input #1, mystr
  11.    If InStr(mystr, ",") > 0 Then
  12.    s = InStr(mystr, "(")
  13.    n = InStr(s, mystr, ")")
  14.    mystr = Mid(mystr, s + 1, n - s - 1)
  15.    For Each C In Split(mystr, ",")
  16.      Set A = .Rows(1).Find(C)
  17.      ReDim Preserve Ar(i)
  18.      Ar(i) = Split(A.Address, "$")(1)
  19.      i = i + 1
  20.    Next
  21.    For Each p In Ar
  22.       dic(p) = Ar '°O¿ý¤½¦¡°Ñ·ÓÄæ¦ì
  23.    Next
  24.    Erase Ar: i = 0
  25.    End If
  26. Loop
  27. Close #1
  28. With ¤u§@ªí1
  29.   For Each A In .Range(.[A2], .[A2].End(xlDown))
  30.      Upw(CStr(A)) = Array(A.Offset(, 3).Value, A.Offset(, 2).Value) '°O¿ý±b±K
  31.   Next
  32. End With
  33. '¨ú¥N½Æ¿ï¦ì¸m
  34. Set A = .Range(.[H2], .Cells(.Rows.Count, "CQ").End(xlUp)).Find("*,*")
  35. If Not A Is Nothing Then
  36. Do
  37. ay = Split(A, ",")
  38. For i = 0 To UBound(ay)
  39. ReDim Preserve Ar(i)
  40. Ar(i) = CInt(ay(i))
  41. Next
  42.   A.Value = Round(Application.Average(Ar), 0)
  43.   Erase Ar
  44.   Set A = .Range(.[H2], .Cells(.Rows.Count, "CQ").End(xlUp)).Find("*,*", A)
  45. Loop Until A Is Nothing
  46. End If
  47. i = 0
  48. .Select
  49. For Each A In .Range(.[F2], .Cells(.Rows.Count, "F").End(xlUp))
  50.    A.Offset(, -5).Resize(, 2) = Upw(CStr(A)) '¶ñ¼g±b±K
  51.    r = A.Row
  52.    For Each B In .Range(.Cells(r, "H"), .Cells(r, "CQ"))
  53.       If B = "" Then  '§ä¨ìªÅ®æ
  54.       ay = dic(Split(B.Address, "$")(1))
  55.       If Not IsEmpty(ay) Then '¸ÓÀx¦s®æ¦³³Q¤½¦¡¤Þ¥Î
  56.          For i = 0 To UBound(ay)
  57.            ReDim Preserve Ar(i)
  58.            Ar(i) = ay(i) & r
  59.          Next
  60.         If Application.Count(.Range(Join(Ar, ","))) > 0 Then B.Value = Round(Application.Evaluate("Average(" & Join(Ar, ",") & ")*5/3"), 0)
  61.          Erase Ar
  62.       End If
  63.       End If
  64.     Next
  65. Next
  66. End With
  67. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2013-11-15 08:14 ½s¿è

¦^´_ 17# Hsieh

Dear  Hsieh ª©¤j¡G

  ¤Ó·P¿E±zªº¦^ÂСA²\©b¡A¤]¦hÁ±zªº¶O¤ß

but ¤S­n³Ò·Ð±z¬Ý¤@¤U¡Arunªº®É­Ô¥X²{¿ù»~¤F

¸õ¥Xªº¿ù»~µøµ¡¦pªþÀÉ

§Úª¾¹D¦pªGcell¦³  #N/A ¡A¦A¶]vba¦n¹³³£·|¦³¿ù»~µøµ¡¸õ¥X

¥Ø«e¤w§â·|¥X²{  #N/A ³]¬°ªÅ¥Õ¡A¦ý¬O ¶]vbaÁÙ¬O¤S¥X¿ù¤F

¥t¥~ ¡@ ®M¤J¨ú¥Nmissing ªº R¡@I¡@¢Ï¡@S¡@¢Ó¡@C¬O¤£­n*5/3¦ý¥|±Ë¤­¤J¡A
                  ¦ÓCRÄæ¨ìCWÄæ ªº  R¡@I¡@¢Ï¡@S¡@¢Ó¡@C¡@¬O­n­¼¥H5/3

¤£¹L³Ì¥D­nªºÁÙ¬O§â½Æ¼ÆÃD Åܦ¨missing­È ¡A¦A¨Ì³W«h¨ú¥N

¤Tµf¨â¦¸¥´ÂZ¯u¬O¤£¦n·N«ä

¦A³Â·Ð±z«ü±Ð¤F

ÁÂÁÂ

[attach]16267[/attach]

TOP

¦^´_ 18# jj369963

¤£À´§A©Ò»¡
¥t¥~ ¡@ ®M¤J¨ú¥Nmissing ªº R¡@I¡@¢Ï¡@S¡@¢Ó¡@C¬O¤£­n*5/3¦ý¥|±Ë¤­¤J¡A
                  ¦ÓCRÄæ¨ìCWÄæ ªº  R¡@I¡@¢Ï¡@S¡@¢Ó¡@C¡@¬O­n­¼¥H5/3
  µ{¦¡½X°õ¦æ¤w¸g¤£»Ý­n¦³CR:DCÄæ¦ìªº¤½¦¡¡A§AÅçºâ¬Ý¬Ý®t²§¦b­þ?
  1. Sub Replace_Blank()
  2. Dim A As Range, Ar(), B As Range
  3. Set Upw = CreateObject("Scripting.Dictionary") '±b±K
  4. Set dic = CreateObject("Scripting.Dictionary") '°Ñ·Ó
  5. fs = ThisWorkbook.Path & "\replace_rule.txt" 'TEXTÀɮצì¸m
  6. Close #1 '­Y¤w¸g¶}±Ò´N¥ýÃö³¬
  7. With Sheets("Sheet0")
  8. Open fs For Input As #1
  9. Do Until EOF(1)
  10.    Line Input #1, mystr
  11.    If InStr(mystr, ",") > 0 Then
  12.    s = InStr(mystr, "(")
  13.    n = InStr(s, mystr, ")")
  14.    mystr = Mid(mystr, s + 1, n - s - 1)
  15.    For Each C In Split(mystr, ",")
  16.      Set A = .Rows(1).Find(C)
  17.      ReDim Preserve Ar(i)
  18.      Ar(i) = Split(A.Address, "$")(1)
  19.      i = i + 1
  20.    Next
  21.    For Each p In Ar
  22.       dic(p) = Ar '°O¿ý¤½¦¡°Ñ·ÓÄæ¦ì
  23.    Next
  24.    Erase Ar: i = 0
  25.    End If
  26. Loop
  27. Close #1
  28. With Sheets("Sheet1")
  29.   For Each A In .Range(.[A2], .[A2].End(xlDown))
  30.      Upw(CStr(A)) = Array(A.Offset(, 3).Value, A.Offset(, 2).Value) '°O¿ý±b±K
  31.   Next
  32. End With
  33. '¨ú¥N½Æ¿ï¦ì¸m
  34. Set A = .Range(.[H2], .Cells(.Rows.Count, "CQ").End(xlUp)).Find("*,*")
  35. If Not A Is Nothing Then
  36. Do
  37. ay = Split(A, ",")
  38. For i = 0 To UBound(ay)
  39. ReDim Preserve Ar(i)
  40. Ar(i) = Val(ay(i))
  41. Next
  42.   A.Value = Round(Application.Average(Ar), 0)
  43.   Erase Ar
  44.   Set A = .Range(.[H2], .Cells(.Rows.Count, "CQ").End(xlUp)).Find("*,*", A)
  45. Loop Until A Is Nothing
  46. End If
  47. i = 0
  48. .Select
  49. For Each A In .Range(.[F2], .Cells(.Rows.Count, "F").End(xlUp))
  50.    A.Offset(, -5).Resize(, 2) = Upw(CStr(A)) '¶ñ¼g±b±K
  51.    r = A.Row
  52.    For Each B In .Range(.Cells(r, "H"), .Cells(r, "CQ"))
  53.       If B = "" Then  '§ä¨ìªÅ®æ
  54.       ay = dic(Split(B.Address, "$")(1))
  55.       If Not IsEmpty(ay) Then '¸ÓÀx¦s®æ¦³³Q¤½¦¡¤Þ¥Î
  56.          For i = 0 To UBound(ay)
  57.            ReDim Preserve Ar(i)
  58.            Ar(i) = ay(i) & r
  59.          Next
  60.         If Application.Count(.Range(Join(Ar, ","))) > 0 Then B.Value = Round(Application.Evaluate("Average(" & Join(Ar, ",") & ")*5/3"), 0)
  61.          Erase Ar
  62.       End If
  63.       End If
  64.     Next
  65. Next
  66. End With
  67. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2013-11-15 08:14 ½s¿è

¦^´_ 19# Hsieh


    Dear  Hsieh ª©¤j¡G

    ·PÁ±zªº¦^ÂСAit works«D±`ªº·P°Ê.  §Ú´ú¸Õ«á¡Aµ{¦¡½X°õ¦æ½T¹ê¤w¸g¤£»Ý­n¦³CR:DCÄæ¦ìªº¤½¦¡¡C

    but ­pºâªºµ²ªG¦³ÂI¤p°ÝÃD  ²Ä1ÃD ¨ì ²Ä48ÃD ¥u¯à¬O1©Î2©Î3          ©Ò¥H¥­§¡¥X¨Ó¤£¥i¯à¬O4©Î5 (¦ý¬O²{¦brunªºµ²ªG«o¶]¥X4©Î5)

³W«h¬O¡G   
MEAN(²Ä4ÃD,²Ä6ÃD,²Ä19ÃD,²Ä24ÃD,²Ä27ÃD,²Ä39ÃD,²Ä43ÃD,²Ä47ÃD)
MEAN(²Ä5ÃD,²Ä7ÃD,²Ä15ÃD,²Ä20ÃD,²Ä22ÃD,²Ä29ÃD,²Ä35ÃD,²Ä40ÃD)
MEAN(²Ä2ÃD,²Ä9ÃD,²Ä12ÃD,²Ä18ÃD,²Ä23ÃD,²Ä28ÃD,²Ä32ÃD,²Ä36ÃD)
MEAN(²Ä3ÃD,²Ä11ÃD,²Ä25ÃD,²Ä30ÃD,²Ä33ÃD,²Ä37ÃD,²Ä41ÃD,²Ä45ÃD)
MEAN(²Ä8ÃD,²Ä13ÃD,²Ä16ÃD,²Ä21ÃD,²Ä31ÃD,²Ä38ÃD,²Ä42ÃD,²Ä48ÃD)
MEAN(²Ä1ÃD,²Ä10ÃD,²Ä14ÃD,²Ä17ÃD,²Ä26ÃD,²Ä34ÃD,²Ä44ÃD,²Ä46ÃD)
MEAN(²Ä74ÃD,²Ä76ÃD,²Ä78ÃD,²Ä80ÃD,²Ä82ÃD)
MEAN(²Ä75ÃD,²Ä77ÃD,²Ä79ÃD,²Ä81ÃD,²Ä83ÃD)
MEAN(²Ä84ÃD,²Ä85ÃD,²Ä86ÃD,²Ä87ÃD,²Ä88ÃD)
MEAN(²Ä52ÃD,²Ä60ÃD,²Ä64ÃD)
MEAN(²Ä57ÃD,²Ä65ÃD,²Ä68ÃD)
MEAN(²Ä56ÃD,²Ä66ÃD,²Ä73ÃD)

¤]´N¬O ²Ä4ÃDmissing ´N¥­§¡ ²Ä6ÃD,²Ä19ÃD,²Ä24ÃD,²Ä27ÃD,²Ä39ÃD,²Ä43ÃD,²Ä47ÃD¡A¨ú¥|±Ë¤­¤J
              ²Ä6ÃD,²Ä19ÃD missing ´N¥­§¡²Ä4ÃD,²Ä24ÃD,²Ä27ÃD,²Ä39ÃD,²Ä43ÃD,²Ä47ÃD¡A¨ú¥|±Ë¤­¤J

´N¬O¤@²Õ¸Ì¡A¦pªG¦³missing­È´N¥­§¡¨ä¥L­È¡A¦A¨ú¥N

¥Ø«e§Ú´ú¸Õ ²Ä1ÃD missing  
                      ²Ä10ÃD=2
                       ²Ä14ÃD=2
                       ²Ä17ÃD=2
                        ²Ä26ÃD=1
                       ²Ä34ÃD=1
                        ²Ä44ÃD =2
                        ²Ä46ÃD=2
©Ò¥H²Ä¤@ÃD=round(average(2,2,2,1,1,2,2)=1.714285714=2

¦ý¬O°õ¦ævba¶]¥X¨Ó¬O  ²Ä¤@ÃD= 3

²{¦b¶]¥X¨Ó²Ä1ÃDH2=3¡A ²Ä20ÃD AA10=4 ¡A  ²Ä30ÃDAK27=5

©Ò¥H­pºâµ²ªG¼Æ­È¬O¦³°ÝÃDªº

¥i¥H¦A³Â·Ð±z¬Ý¤@¤U¶Ü?  ¯u¬O©êºp¤@ª½¥´ÂZ¡A¦A³Â·Ð±z«ü±Ð
ÁÂÁÂ

ªþµù¡G±z¤§«eªº¦^ÂСy¦^´_ 12¡z¡A­pºâ¥X¨Óªºµ²ªG¬O¹ïªº

[attach]16273[/attach]

TOP

        ÀR«ä¦Û¦b : °µ¸Ó°µªº¨Æ¬O´¼¼z¡A°µ¤£¸Ó°µªº¨Æ¬O·Mè¡C
ªð¦^¦Cªí ¤W¤@¥DÃD