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

[µo°Ý] ¦r¦ê§R°£­«½Æ

¥»©«³Ì«á¥Ñ ­ã´£³¡ªL ©ó 2020-10-11 09:50 ½s¿è

Sub TEST_A1()
Dim Arr, xD, i&, T1$, T2$, T3$
Set xD = CreateObject("Scripting.Dictionary")
Arr = Range([¦ì¸m!A1], [¦ì¸m!D65536].End(xlUp))
For i = 2 To UBound(Arr)
    T1 = Arr(i, 1): T2 = Arr(i, 4)
    If T1 = "" Or T2 = "" Then GoTo 101
    T3 = T1 & "|" & T2:  xD(T3) = xD(T3) + 1 '¥Î¨â­ÓÃöÁä¦r²Õ¦X, ±Æ°£­«ÂÐ
    If xD(T3) = 1 Then xD(T1) = xD(T1) & "," & T2  'xD(T3) = 1 ¬°­º¦¸¥X²{ªº, ¥[¤J¦r¦ê¤¤, ¶W¹L1, ²¤¹L
101: Next i
Arr = Range([®Æ¥ó!A1], [®Æ¥ó!A65536].End(xlUp))
For i = 2 To UBound(Arr)
    Arr(i - 1, 1) = Mid(xD(Arr(i, 1) & ""), 2) '¦r¦ê­º¦r²Å¬°"³r¸¹", ¶·¥Îmid¨ú²Ä2­Ó¦r¥H«áªº¦r¤¸
Next i
[®Æ¥ó!C2].Resize(UBound(Arr) - 1) = Arr
End Sub

TOP

Sub TEST_A2()
Dim Arr, xD, i&, T1$, T2$, T3$, xR As Range
Set xD = CreateObject("Scripting.Dictionary")
Arr = Range([¦ì¸m!A1], [¦ì¸m!D65536].End(xlUp))
For i = 2 To UBound(Arr)
    T1 = Arr(i, 1): T2 = Arr(i, 4)
    If T1 = "" Or T2 = "" Then GoTo 101
    T3 = T1 & "|" & T2:  xD(T3) = xD(T3) + 1
    If xD(T3) = 1 Then xD(T1) = xD(T1) & IIf(xD(T1) = "", "", ",") & T2  '¥Î iif ÁקK¦r¦ê¶}ÀY¥X²{"³r¸¹"
101: Next i
For Each xR In Range([®Æ¥ó!A2], [®Æ¥ó!A65536].End(xlUp)) 'ª½±µ¦bÀx¦s®æ³v¤@¶ñ¤J, ³t«×ºC
    xR(1, 3) = xD(xR & "")  'ª`·N:xD(xR & "") ¤¤ªº"Âù¤Þ¸¹"¤£¥i¬Ù²¤, ©Î¥ÎxD(xR.Value)
Next
End Sub

TOP

¦r¨åªºkeyªº«¬ºA¶·©ú½T¬É©w,
"¼Æ­È"¤Î"¤å¦r"®æ¦¡, ·|µø¬°¤£¦P,
¨Ò¦p: xd(123) ¤Î xd("123") µø¬°¤£¦P,
©Ò¥H³Ì¦n¥ÎÅܼƥN´À, ¦A°µ¦r¨åkey,
dim T$ ©Î  dim T as string
T = 123
xd(T) §Yµ¥¦P xd("123")

­^¤å¤j¤p¼g¤]µø¬°¤£¦P,
¨Ò¦p: xd("ABC") ¤Î xd("Abc") µø¬°¤£¦P,
¥²­n®É¥i¥Îucase ©Î lcase ²Î¤@¤j¤p¼g, ¦A¸m¤J¦r¨å

TOP

        ÀR«ä¦Û¦b : ¤H¥Í³Ì¤jªº¦¨´N¬O±q¥¢±Ñ¤¤¯¸°_¨Ó¡C
ªð¦^¦Cªí ¤W¤@¥DÃD