- ©«¤l
- 4901
- ¥DÃD
- 44
- ºëµØ
- 24
- ¿n¤À
- 4916
- ÂI¦W
- 62
- §@·~¨t²Î
- Windows 7
- ³nÅ骩¥»
- Office 20xx
- ¾\ŪÅv
- 150
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥x¥_
- µù¥U®É¶¡
- 2010-4-30
- ³Ì«áµn¿ý
- 2025-3-13
               
|
¦^´_ 18# jj369963
¤£À´§A©Ò»¡¥t¥~ ¡@ ®M¤J¨ú¥Nmissing ªº R¡@I¡@¢Ï¡@S¡@¢Ó¡@C¬O¤£n*5/3¦ý¥|±Ë¤¤J¡A
¦ÓCRÄæ¨ìCWÄæ ªº R¡@I¡@¢Ï¡@S¡@¢Ó¡@C¡@¬On¼¥H5/3 µ{¦¡½X°õ¦æ¤w¸g¤£»Ýn¦³CR:DCÄæ¦ìªº¤½¦¡¡A§AÅçºâ¬Ý¬Ý®t²§¦bþ?- Sub Replace_Blank()
- Dim A As Range, Ar(), B As Range
- Set Upw = CreateObject("Scripting.Dictionary") '±b±K
- Set dic = CreateObject("Scripting.Dictionary") '°Ñ·Ó
- fs = ThisWorkbook.Path & "\replace_rule.txt" 'TEXTÀɮצì¸m
- Close #1 'Y¤w¸g¶}±Ò´N¥ýÃö³¬
- With Sheets("Sheet0")
- Open fs For Input As #1
- Do Until EOF(1)
- Line Input #1, mystr
- If InStr(mystr, ",") > 0 Then
- s = InStr(mystr, "(")
- n = InStr(s, mystr, ")")
- mystr = Mid(mystr, s + 1, n - s - 1)
- For Each C In Split(mystr, ",")
- Set A = .Rows(1).Find(C)
- ReDim Preserve Ar(i)
- Ar(i) = Split(A.Address, "$")(1)
- i = i + 1
- Next
- For Each p In Ar
- dic(p) = Ar '°O¿ý¤½¦¡°Ñ·ÓÄæ¦ì
- Next
- Erase Ar: i = 0
- End If
- Loop
- Close #1
- With Sheets("Sheet1")
- For Each A In .Range(.[A2], .[A2].End(xlDown))
- Upw(CStr(A)) = Array(A.Offset(, 3).Value, A.Offset(, 2).Value) '°O¿ý±b±K
- Next
- End With
- '¨ú¥N½Æ¿ï¦ì¸m
- Set A = .Range(.[H2], .Cells(.Rows.Count, "CQ").End(xlUp)).Find("*,*")
- If Not A Is Nothing Then
- Do
- ay = Split(A, ",")
- For i = 0 To UBound(ay)
- ReDim Preserve Ar(i)
- Ar(i) = Val(ay(i))
- Next
- A.Value = Round(Application.Average(Ar), 0)
- Erase Ar
- Set A = .Range(.[H2], .Cells(.Rows.Count, "CQ").End(xlUp)).Find("*,*", A)
- Loop Until A Is Nothing
- End If
- i = 0
- .Select
- For Each A In .Range(.[F2], .Cells(.Rows.Count, "F").End(xlUp))
- A.Offset(, -5).Resize(, 2) = Upw(CStr(A)) '¶ñ¼g±b±K
- r = A.Row
- For Each B In .Range(.Cells(r, "H"), .Cells(r, "CQ"))
- If B = "" Then '§ä¨ìªÅ®æ
- ay = dic(Split(B.Address, "$")(1))
- If Not IsEmpty(ay) Then '¸ÓÀx¦s®æ¦³³Q¤½¦¡¤Þ¥Î
- For i = 0 To UBound(ay)
- ReDim Preserve Ar(i)
- Ar(i) = ay(i) & r
- Next
- If Application.Count(.Range(Join(Ar, ","))) > 0 Then B.Value = Round(Application.Evaluate("Average(" & Join(Ar, ",") & ")*5/3"), 0)
- Erase Ar
- End If
- End If
- Next
- Next
- End With
- End Sub
½Æ»s¥N½X |
|