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

¨Ì¾Ú³æ§O+¼t§O,§Q¥Î¤½¦¡§PÂ_,Àx¦ì¬O§_¥¿½T

¨Ì¾Ú³æ§O+¼t§O,§Q¥Î¤½¦¡§PÂ_,Àx¦ì¬O§_¥¿½T

¨Ì¾Ú³æ§O+¼t§O,§Q¥Î¤½¦¡§PÂ_,Àx¦ì¬O§_¥¿½T
¥¿½TÀx¦ì¥i¥H¬O3­Ó¨ä¤¤¤@­Ó,¤]¥i¥H¬O2¦ì­^¤å¦r,½Ð°Ý­n¦p¦ó§Q¥Î¤½¦¡§PÂ_?
javascript:;
TEST.zip (9.08 KB)                        
³æ§O        ¼t§O        Àx¦ì        Àx¦ì»¡©ú
¥¿±`³æ        0        H.Z.L.U        =>¥i¥H¬OH©ÎZ©ÎL©ÎU
¤j¶é³æ        D        X.S.J        =>¥i¥H¬OX©ÎS©ÎJ
¨ä¥L³æ        G        C.E.F        =>¥i¥H¬OC©ÎE©ÎF
¹wÁÊ³æ        Q               
¤­Ä_³æ        V        B.I.R        =>¥i¥H¬OB©ÎI©ÎR
¨ä¥L³æ7        Y6        HKQ        =>¥²¶·¬OHKQ
¨ä¥L³æ8        Y1        HK        =>¥²¶·¬OHK
¨ä¥L³æ9        Y3        HQ        =>¥²¶·¬OHQ
½Ã¥Í¯È³æ        Y        JKQ        =>¥²¶·¬OJKQ
¤ô°s³æ        YW        JKQ        =>¥²¶·¬OJKQ
¨ä¥L        G        C.E.F        =>¥i¥H¬OC©ÎE©ÎF

¦^´_ 1# VANESSA

½Ð´ú¸Õ¬Ý¬Ý¡AÁÂÁÂ
¥t¥~Àˬd¤pªí­n¥[¤J¨ä¥L³æ¸ê°T¦p·Ó¤ù¡A±z´£¨Ñµª®×¦³2µ§¿ù»~¦p·Ó¤ù

Sub test()
Dim Arr, xD, T$, pos%
Set xD = CreateObject("Scripting.Dictionary")
Arr = Range([j5], [g65536].End(3))
For i = 2 To UBound(Arr)
    T = Arr(i, 1) & "|" & Arr(i, 2)
    xD(T) = Array(Arr(i, 3), Arr(i, 4))
Next
Arr = Range([d1], [a65536].End(3))
For i = 2 To UBound(Arr)
    T = Arr(i, 1) & "|" & Arr(i, 3)
    If xD.Exists(T) Then
        pos = InStr(xD(T)(0), Arr(i, 2))
        If pos = 0 Then
            Arr(i, 4) = "¿ù»~¡A¦]¬°" & Arr(i, 1) & "+" & Arr(i, 3) & "¡AÀx¦ì" & xD(T)(1)
        Else
            Arr(i, 4) = "¥¿½T¡A¦]¬°" & Arr(i, 1) & "+" & Arr(i, 3) & "¡AÀx¦ì" & xD(T)(1)
        End If
    End If
Next
Range("a1").Resize(UBound(Arr), 4) = Arr
End Sub
1.JPG

TOP

ÀH·NºÛ "EXCEL°g"  blog  ©Îhttps://hcm19522.blogspot.com/ EXCEL¨ç¼Æ

TOP

¦^´_ 1# VANESSA

±z´£¨Ñµª®×¦³2µ§¿ù»~>> §ó¥¿2#¡A±zªºµª®×¬O¥¿½T¡A§ó·s¦p¤U¡AÁÂÁÂ

Sub test2()
Dim Arr, xD, T$, pos%, a, i&
Set xD = CreateObject("Scripting.Dictionary")
Arr = Range([j5], [g65536].End(3))
For i = 2 To UBound(Arr)
    T = Arr(i, 1) & "|" & Arr(i, 2)
    xD(T) = Array(Arr(i, 3), Arr(i, 4))
Next
Arr = Range([d1], [a65536].End(3))
For i = 2 To UBound(Arr)
    T = Arr(i, 1) & "|" & Arr(i, 3)
    If xD.Exists(T) Then
        a = Split(xD(T)(0), ".")
        For j = 0 To UBound(a)
            If Arr(i, 2) = a(j) Then pos = 1
        Next
        If pos = 0 Then
            Arr(i, 4) = "¿ù»~¡A¦]¬°" & Arr(i, 1) & "+" & Arr(i, 3) & "¡AÀx¦ì" & xD(T)(1)
        Else
            Arr(i, 4) = "¥¿½T¡A¦]¬°" & Arr(i, 1) & "+" & Arr(i, 3) & "¡AÀx¦ì" & xD(T)(1)
            pos = 0
        End If
    End If
Next
Range("a1").Resize(UBound(Arr), 4) = Arr
End Sub

TOP

=IF(ISERR(FIND("."&B2&".",LOOKUP(,0/(A2&C2=G$6:G$16&H$6:H$16),"."&I$6:I$16&"."))),"¿ù»~","¥¿½T")
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¤Ó¼F®`¤F,·PÁÂ,

TOP

        ÀR«ä¦Û¦b : ¤ß¤¤±`¦sµ½¸Ñ¡B¥]®e¡B·P«ä¡Bª¾¨¬¡B±¤ºÖ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD