- ©«¤l
- 254
- ¥DÃD
- 6
- ºëµØ
- 0
- ¿n¤À
- 310
- ÂI¦W
- 0
- §@·~¨t²Î
- W10
- ³nÅ骩¥»
- Excel 2016
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥xÆW
- µù¥U®É¶¡
- 2019-6-16
- ³Ì«áµn¿ý
- 2024-9-23
|
¥»©«³Ì«á¥Ñ °a¤ªºµ ©ó 2020-9-12 17:39 ½s¿è
¦^´_ 62# ã´£³¡ªL
ÁÂÁ·Ǥjªº«ü¾É ¦ý³o¤è¦¡ ¥u¬O¦b¦r¨å¸Ì§R°£«½Æ¦Ó¤w ¤£¹LÀ³¸Ó·|¤ñ¸û§Ö¤@ÂIÂI ¦r¨å§PÂ_«½Æ°}¦C¤£´£¨úªº¤è¦¡ÁÙ¦b§V¤O...- Public Sub °}¦C¥[Function¥[¦r¨å½m²ß()
- Application.ScreenUpdating = False
- If [¦¨ªG!A1] <> "" Then [¦¨ªG!A1].CurrentRegion.Clear
- Crr = [¥Ø¼Ð!A1].CurrentRegion
- Brr = [®w¦s!A1].CurrentRegion
- ReDim Drr(1 To UBound(Brr, 1), 1 To UBound(Brr, 2))
- Set xD = CreateObject("Scripting.Dictionary")
- For i = 1 To UBound(Crr)
- A3 = ¤À³Î¤å¦r(Trim(Crr(i, 3)))
- A1 = Trim(Crr(i, 1))
- For N = 1 To UBound(Brr)
- B3 = ¤À³Î¤å¦r(Trim(Brr(N, 3)))
- B1 = Trim(Brr(N, 1))
- If A1 Like B1 Or A3 Like B3 And A3 <> "" Then
- xD(Brr(N, 1)) = Brr(N, 1)
- End If
- Next N
- Next i
- For E = 1 To UBound(Brr)
- If Brr(E, 1) = xD(Brr(E, 1)) Then
- G = G + 1
- For F = 1 To UBound(Brr, 2)
- Drr(G, F) = Brr(E, F)
- Next F
- End If
- Next E
- Erase Brr, Crr
- [¦¨ªG!A1].Resize(G, UBound(Drr, 2)) = ""
- [¦¨ªG!A1].Resize(G, UBound(Drr, 2)) = Drr
- Erase Drr
- Sheets(3).Activate
- Cells(1, 1).Select
- Application.ScreenUpdating = False
- End Sub
- '====================================================================
- Public Function ¤À³Î¤å¦r(A3)
- Drx = Array("-", ".")
- A7 = "": A8 = ""
- For A9 = LBound(Drx) To UBound(Drx)
- For A0 = 1 To Len(A3)
- If InStr(Mid(Right(A3, A0), 1, 1), Drx(A9)) Then
- A8 = Mid(Right(A3, A0), 1, A0)
- A7 = Mid(A3, 1, Len(A3) - Len(A8))
- Exit For
- End If
- Next A0
- Next A9
- If A7 = "" Then A7 = A3
- If A7 = "" Then Exit Function
- If Left(A7, 4) Like "####" Then
- X = Mid(A7, 1, 4)
- ElseIf Left(A7, 5) Like "####[A-Z]" Then
- X = Mid(A7, 1, 5)
- ElseIf Left(A7, 5) Like "[A-Z]####" Then
- X = Mid(A7, 1, 5)
- ElseIf Left(A7, 8) Like "???-????" Then
- X = Mid(A7, 1, 8)
- End If
- If X = "" Then X = A3
- ¤À³Î¤å¦r = X
- End Function
½Æ»s¥N½X |
|