[µo°Ý]¦p¦ó±N½d³ò¤º²Å¦X±ø¥óªº¦r¦ê¦X¨Ö

chthonicfreddy

§Ú¦bºô¸ô¤W°Ñ¦Ò¤F¥H¤U½d¨Ò
Ãþ¦üvlookup¥\¯à¡A§Æ±æ¯à­×§ï±N½d³ò¤º²Å¦X±ø¥óÀx¦s®æ¤ºªº¦r¦ê¦X¨Ö¬°¤@­Ó¦r¦ê
·PÁ¦U¦ì¤j¤jªº«ü¾É   
test.rar (12.41 KB)

test
¤U¸ü¦¸¼Æ: 4

2016-11-29 11:46

Function WLOOKUP(X As Variant, M As Range, A As Byte, B As Integer)
'X ±ø¥ó
'M ­n¬d¸ßªº³æ¦C½d³ò
'A ªð¦^²Än­Ó²Å¦X±ø¥óªºµ²ªG(255)                                
'B ªð¦^µ²ªGªº¦C¯Á¤Þ¡A¥i¥H¬O0©Î­t¼Æ
'  ¥Î©ó¼Æ¾Ú¬d¸ßªº³æ¦C½d³òM©Ò¦b¦Cªº­È¬°1¡A¥Nªí²Ä1¦C
'  ¥k°¼²Ä¤@¦C¬°2,3,4.....;¥ª°¼²Ä¤@¦C¬°0,-1,-2.....
    Dim i As Integer, mr As Range, y As Integer
    i = Application.WorksheetFunction.CountIf(M, X)
    'Set M =Intersect(M.Parent.Usedrange,M)
    For Each mr In M
        If mr.Value = X Then
            y = y + 1
            If y = A Then
                WLOOKUP = mr.Offset(0, B - 1).Value
                Exit Function
            End If
        End If
    Next mr
    WLOOKUP = ""
End Function

­ã´£³¡ªL

Function WLOOKUP(X, xA As Range, xB As Range, SP$) As String
Dim i&, T$
For i = 1 To xA.Rows.Count
¡@¡@If xA(i) = X Then T = Trim(T & " " & xB(i))
Next i
WLOOKUP = Replace(T, " ", SP)
End Function

¤½¦¡¡G
=wlookup("A",C$2:C$11,A$2:A$11," ")
=wlookup(¤ñ¹ï­È,¤ñ¹ï½d³ò,¨ú­È½d³ò,¤À¹j²Å¸¹)
¡@
Xl0000027.rar (11.15 KB)

¤U¸ü¦¸¼Æ: 8

2016-11-29 12:40

¡@
¡@

chthonicfreddy

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


    ·PÁ¤j¤jªº¦^ÂСA¹B¦æµL»~!!
·s¤â¤Jªù¡A·Q¦A½Ð±Ð±z²³æªº°ÝÃD
For i = 1 To xA.Rows.Count   
¡@¡@If xA(i) = X Then T = Trim(T & " " & xB(i))

¤§«e¦bºô¸ô·j´M¸ê®Æªº®É­Ô¦³¬Ý¨ì
Function MyConcat(Arr As Variant) As String  
Dim A As Variant
For Each A In Arr
MyConcat = MyConcat & A
Next

MyConcat = MyConcat & A  »P¤j¤jªº T = Trim(T & " " & xB(i))  ¬Ý°_¨Ó«ÜÃþ¦ü
¤]¦³¹Á¸Õ±q³o­Ó¤è¦V¥h§äµª®×¡A¥u¬O¤~²¨¾Ç²L¡AÁÙ¬O·d¤£¤Ó²M·¡¤W­±³o¬qªº¨Ï¥Î

hcm19522

http://blog.xuite.net/hcm19522/twblog/472591392