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

[µo°Ý] Excel§ì¨ú¦hµ§¸ê®Æ

²§°Êªí±Æ§Ç//BÄ椽¦¡...¥i°Ñ¦Ò
=-LOOKUP(,-MID(C1&"|0",LOOKUP(99,FIND({"|","#","#M","#AL"},C1&"|")+{1,1,2,3}),ROW($1:$9)))
¨S¦³²Å¦XªºÅã¥Ü0

AÄ椽¦¡¦]ª©¥»¬Ý¤£¨ì~~

TOP

¥»©«³Ì«á¥Ñ ­ã´£³¡ªL ©ó 2023-7-9 10:30 ½s¿è

¨Ó·½ÀɧPÂ_¬O§_¶}±Ò//
On Error Resume Next: Set xB = Workbooks(FN): On Error GoTo 0
If xB Is Nothing Then Set xB = Workbooks.Open(PH & "\" & FN): chk = 1
With xB.Sheets("²§°Êªí±Æ§Ç")
     Brr = Range(.[G1], .[A65536].End(3)(2))
End With
If chk = 1 Then xB.Close 0

¸ÑªR//ÀÉ®×­Y¬O¨Æ¥ý¤â°Ê¶}±Ò, ¤£Ãö³¬// ­Y¬O¥Ñµ{¦¡¶}±Ò, «h¥Ñµ{¦¡Ãö³¬
¬°¦ó:
1) Àɮצp¤w¶}±Ò, µ{¦¡´N¤£¥i¥H¦A¶}¤@¦¸, ·|³y¦¨·í¾÷
2) Àɮ׬O¤â°Ê¶}±Òªº, ¥i¯à¦³§ó§ï¤º®e¦Ó¥¼¦sÀÉ, ­Yª½±µÃö³¬¥¦, ¸ê®Æ±N¤£·|³QÀx¦s

TOP

¦^´_ 30# qaqa3296

«D±`·PÁÂqaqa3296¤j¤jªº¸Ñ´b

TOP

¦^´_ 31# ­ã´£³¡ªL

«D±`·PÁ­㴣³¡ªL¤j¤jªº¸Ñ´b

TOP

¦^´_ 31# ­ã´£³¡ªL

«D±`·PÁ­㴣³¡ªL¤j¤jªº¸Ñ´b
AÄæ¦ìªº¤½¦¡
¦pªGBÄæ¦ì¦³¥H¤Uªº¤º®e´N¦b«e­±¥[¤J«ü©wªº¤å¦r
AL#>>«e­±¥[¤JSML-¡A"M01""M02""M03">>«e­±¥[¤JSML5#
1~15+18+57+21~47+53+49~95+85+58+121~125+164~169+178~179>>«e­±¥[¤JSML3#
96~120>>«e­±¥[¤JPMP#
19+126~128+134~141+183~189>>«e­±¥[¤JSML5#
142~163+181+182>>«e­±¥[¤JSML6#
173~176+180>>«e­±¥[¤JSML7#

=IF(ISNUMBER(SEARCH("AL#",B1)),"SML-"&B1,IF(OR(B1="M01",B1="M02",B1="M03"),"SML5#"&B1,IF(ISNUMBER(B1),
IF(OR(B1=1,B1=2,B1=3,B1=4,B1=5,B1=6,B1=7,B1=8,B1=9,B1=10,B1=11,B1=12,B1=13,B1=14,B1=15,B1=18,B1=57,(B1>=21)*(B1<=47),B1=53,(B1>=49)*(B1<=95),B1=85,B1=58,(B1>=121)*(B1<=125),(B1>=164)*(B1<=169),(B1>=178)*(B1<=179)),
"SML3#"&TEXT(B1,"000"),
IF(AND(B1>=96,B1<=120),
"PMP#"&TEXT(B1,"000"),
IF(OR(B1=19,(B1>=126)*(B1<=128),(B1>=134)*(B1<=141),(B1>=183)*(B1<=189)),
"SML5#"&TEXT(B1,"000"),
IF(OR((B1>=142)*(B1<=163),B1=181,B1=182),
"SML6#"&TEXT(B1,"000"),
IF(OR((B1>=173)*(B1<=176),B1=180),
"SML7#"&TEXT(B1,"000"),
""
)
)
)
)
),
""
)))

TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2023-7-10 11:16 ½s¿è

¦^´_ 32# ­ã´£³¡ªL
¦^´_ 30# qaqa3296
¦^´_ 27# cowww


    ÁÂÁ«e½ú¦A´£¨Ñ·s½d¨Ò
ÁÂÁ ­ã´£³¡ªL«e½ú«ü¾É
ÁÂÁ qaqa3296 ¤@°_¾Ç²ß,¶W»{¯uªº¾Ç²ß¤è®×
«á¾Ç¤]´£¨Ñ¾Ç²ß¤è®×«Øij¦p¤U,½Ð«e½ú°Ñ¦Ò

°õ¦æµ²ªG:





Option Explicit
Sub ¾÷¥x±Æµ{_Click()
Application.ScreenUpdating = False
Dim Brr, Z, A$, B$, C%, Nm&, chk, Np, i&, R&, T$, T1$, PH$, FN$, xB As Workbook, Sh As Worksheet
Set Z = CreateObject("Scripting.Dictionary")
PH = ThisWorkbook.Path: FN = "²§°Êªí±Æ§Ç.xlsm"
On Error Resume Next: Set xB = Workbooks(FN): On Error GoTo 0
If xB Is Nothing Then Set xB = Workbooks.Open(PH & "\" & FN): chk = 1
With xB.Sheets("²§°Êªí±Æ§Ç")
     Brr = Range(.[G1], .[A65536].End(3)(2))
End With
If chk = 1 Then xB.Close 0
For i = 2 To UBound(Brr) - 1
   If IsError(Brr(i, 1)) Or IsError(Brr(i, 2)) Or Brr(i, 4) = "" Then GoTo i00
   '¡ô1.2Ä榳¿ù»~­È ©ÎµL¼Ò¨ã¸¹½X ²¤¹L
   T = Brr(i, 4): T1 = Brr(i, 1): A = Z(T1)
   If A = "" Then
      For R = i To UBound(Brr)
         If IsError(Brr(R, 1)) Or IsError(Brr(R, 2)) Or Brr(R, 4) = "" Then GoTo R00
         '¡ô1.2Ä榳¿ù»~­È ©ÎµL¼Ò¨ã¸¹½X ²¤¹L
         If T1 <> Brr(R, 1) Then Exit For
         For C = 4 To 7: B = B & " " & Brr(R, C): Next:
         B = Brr(R, 3) & "       " & B
         If i = R Then A = Brr(R, 1) & vbLf & B Else A = A & vbLf & B
         Z(T1) = A: B = ""
R00:  Next
   End If
   If Z(T) = "" Then
      Z(T) = Z(T1)
      ElseIf InStr(Z(T), Z(T1)) = 0 Then
         Z(T) = Z(T) & vbLf & vbLf & Z(T1)
   End If
i00: Next
Brr = Range([±M®×!Z1], [±M®×!D65536].End(3))
[Z:Z].ClearComments: [Z:Z].Interior.ColorIndex = xlNone
For i = 1 To UBound(Brr)
   T1 = Brr(i, 1)
   If Z(T1) = "" And Z(Brr(i, 23)) <> "" Then
      Cells(i, 26).Interior.ColorIndex = 38
      Np = Np + 1: GoTo i01
   End If
   If T1 = "" Or Z(T1) = "" Then GoTo i01
   If Cells(i, 26) = "" Then
      Nm = Nm + 1
      Cells(i, 26).Interior.ColorIndex = 6
   End If
   With Cells(i, 26).AddComment
      .Text Text:=Replace(Z(T1), "        " & T1, "_¡¹_" & T1)
      .Shape.TextFrame.Characters.Font.Size = 16
      .Shape.DrawingObject.AutoSize = True
   End With
i01: Next
If Nm + Np > 0 Then
   MsgBox "¦³±Æµ{ µL¼Ð¥Ü¾÷¥x: " & Nm & " ­Ó" & vbLf & vbLf & _
          "¦³¼Ð¥Ü¾÷¥x µL±Æµ{: " & Np & " ­Ó"
End If
Set Z = Nothing: Erase Brr: Set xB = Nothing: Set Sh = Nothing
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 36# Andy2483

«D±`·PÁÂAndy2483¤j¤jªº¸Ñ´b

TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2023-7-10 13:44 ½s¿è

¦^´_ 35# cowww


    «á¾Ç²q´ú«e¤è©Ò¥[¤JªºÀ³¸Ó¬O¾÷¥x¼tµP/«¬¸¹
«Øij§ï¥H»²§Uªí¦C¥X¸Ó¼tµP«¬¸¹ªº¾÷¥x©ú²Ó°µ¹ï·Ó,³]­p¥i¦Û°ÊÂX¥Rªº·s¤½¦¡,
¥H§K¾÷¥x¼W´îÁٻݭקï²{¦³³o¤è¦¡ªºA,BÄ椽¦¡

¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 38# Andy2483

«D±`·PÁÂAndy2483¤j¤jªº¸Ñ´b

³o­Ó¤èªk¦n
ÁöµM¼W¥[¾÷¥xªº¥i¯à©Ê«Ü§C
¦ý¬O¤ñ°_¶Wªøªº¤½¦¡¡A¾÷¥x©ú²Ó¹ï·Óªí«o¬O¤ñ¸û¤è«K¥B¤ñ¸û©öÀ´

TOP

        ÀR«ä¦Û¦b : ¨Ã«D¦³¿ú¾{¬O§Ö¼Ö¡A°Ý¤ßµL·\¤ß³Ì¦w¡C
ªð¦^¦Cªí ¤W¤@¥DÃD