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

[µo°Ý] ¦p¦ó±q¤@¬q¤å¦r¿ï¥X¦rµü

[µo°Ý] ¦p¦ó±q¤@¬q¤å¦r¿ï¥X¦rµü

¦U¦ì¦n
§Ú¦³¤@­Ó¥\¯à·Q°µ
´N¬O¨C­Ó¤H·|¦³¤@¬qµû»y¡A¤]´N¬O¤@¬q¤å¦r
¦ý¬O§Ú»Ý­nÂk¯Ç¥X­«ÂI¦rµü

¤ñ¦p¡Aµû»y¬O¡Gµ½«Ý°Êª«¦³·R¤ß¡A¹ï¤H©M®ð¡A¤Í·R¦P¾Ç¡A¸Û¹ê¤£»¡ÁÀ
¨º§Ú³]«ö¶s¡A«ö¤U«á·|¦Û°ÊÂk¯Ç¥X ¡G¦³·R¤ß¡B©M®ð¡B¸Û¹ê

Â^¨ú.JPG
2019-7-27 21:43


¦ý¬O¨C­Ó¦rµü´N­n¥Î¤@­ÓIF¥h§PÂ_
¦pªG¦³20ºØ¤£¦Pªº¦rµü¡A¤£´N­n¼g20­ÓIF ?

³W«h¥i¯à¹³§Ú·Qªº¡A
*·R* --> ¦³·R¤ß
*¸Û* --> ¸Û¹ê
*©M* --> «Ý¤H©M®ð
*¤Í* --> ¤Í·R¦P¾Ç ... µ¥µ¥

¦³¿ìªk¥ÎVLOOKUP¨ººØ¤è¦¡¡A©Î¬O¦b ¥t¤@­Ó¤u§@ªí¡A§Ë¥X³o¨Ç³W«h¦rµü
µM«áµ{¦¡·|¦Û¤v¥h°Ñ·Óªº?

¦]¬°¨C­Ó¤Hµû»y¤£¨£±o¬Û¦P
¦ý¬O§Ú´N­n³W¯Ç¦¨­«ÂI¦rµü¡AÁÙ­n¯à¦ê±µ°_¨Ó

¦A½Ð¦U¦ì¤j¤jÀ°§Ú·Q·Q
ÁÂÁÂ

¿ï¦rµü.rar (16.06 KB)
«¢Åo~¤j®a¦n§r

Private Sub CommandButton1_Click()
Dim TR, TT$, T, TX$, xR As Range
TX = "·R+¦³·R¤ß/©M+«Ý¤H©M®ð/¸Û+¸Û¹ê/¤Í+¤Í·R¦P¾Ç/§µ+§µ¶¶"
For Each xR In Range([¤u§@ªí1!A1], [¤u§@ªí1!A65536].End(xlUp))
    For Each T In Split(TX, "/")
        TR = Split(T, "+")
        If InStr(xR.Value, TR(0)) Then TT = TT & "¡B" & TR(1)
    Next
    xR(1, 3) = Mid(TT, 2): TT = ""
Next
End Sub


==================================

TOP

¹ï·Óªí©ñE:FÄæ:

Private Sub CommandButton1_Click()
Dim Arr, TT$, T, xR As Range
Arr = Range([¤u§@ªí1!E1], [¤u§@ªí1!F65536].End(xlUp))
For Each xR In Range([¤u§@ªí1!A1], [¤u§@ªí1!A65536].End(xlUp))
    For i = 1 To UBound(Arr)
        If Arr(i, 1) <> "" And InStr(xR, Arr(i, 1)) Then TT = TT & "¡B" & Arr(i, 2)
    Next i
    xR(1, 3) = Mid(TT, 2): TT = ""
Next
End Sub


======================================

TOP

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


    ·PÁª©¥Dªº¸Ñµª
    ¨âºØ¤è¦¡³£¥i¥H§@¥Î
    ¤Ó·PÁ¤F
«¢Åo~¤j®a¦n§r

TOP

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


    §A¦n¡A§Ú·Q½Ð°Ý¤@¤U
Private Sub CommandButton1_Click()
Dim Arr, TT$, T, xR As Range
Arr = Range([¤u§@ªí1!E1], [¤u§@ªí1!F65536].End(xlUp))
For Each xR In Range([¤u§@ªí1!A1], [¤u§@ªí1!A65536].End(xlUp))
    For i = 1 To UBound(Arr)
        If Arr(i, 1) <> "" And InStr(xR, Arr(i, 1)) Then TT = TT & "¡B" & Arr(i, 2)
    Next i
    xR(1, 3) = Mid(TT, 2): TT = ""
Next
End Sub

³o¬O¦]¬°³£¦b¦P¤@­Ó¤u§@ªí¤¤
Arr = Range([¤u§@ªí1!E1], [¤u§@ªí1!F65536].End(xlUp))
¦pªG§Úªº¹ï·Óªí¬O¦b ¤u§@ªí3
ª½±µ§ï¦¨
Arr = Range([¤u§@ªí3!E1], [¤u§@ªí3!F65536].End(xlUp))
¦n¹³·|¿ù£°

¥t¥~¬O

§Úªºµ{¦¡©ú©ú´N¦³ For µM«á¤¤¶¡¦³«Ü¤j¤@¬qªºµ{¦¡  ¤~¥X²{ Next
F8³v¦æ°»¿ùªº®É­Ô¡A´N¸ò§Ú»¡¥u¦³NEXT ¨S¦³ FOR
§ÖºÆ¤F
WITH ¸ò END WITH ¤]¤@¼Ë

¤£¾å±o¦³¨S¦³Ãþ¦üªº¸gÅç
¨ì©³¿ù»~¦b­þ¸Ì

ÁÂÁÂ
«¢Åo~¤j®a¦n§r

TOP

¦^´_ 5# iceandy6150


°Ý1)
Private Sub CommandButton1_Click()
Dim Arr, TT$, T, xR As Range, xS As Worksheet
Set xS = Sheets("¤u§@ªí3")
Arr = xS.Range(xS.Range("E1"), xS.Range("F65536").End(xlUp))
For Each xR In xS.Range(xS.[A3], xS.[A65536].End(xlUp))
    For i = 1 To UBound(Arr)
        If Arr(i, 1) <> "" And InStr(xR, Arr(i, 1)) Then TT = TT & "¡B" & Arr(i, 2)
    Next i
    xR(1, 3) = Mid(TT, 2): TT = ""
Next
End Sub

°Ý2)
­n¬Ý§¹¾ãªºµ{¦¡¤~¥i§PÂ_¿ù¦b­þ???

========================

TOP

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


    ÁÂÁª©¥D¸Ñµª
­ì¨ÓÁÙ­n³]©w³o¼Ë Set xS = Sheets("¤u§@ªí3")
§ÚÀ´¤F

µM«á¨º­Ó°ÝÃD2
©ú©ú¦³FOR¤]¦³NEXT
«oĵ§i¿ù»~

§Ú¤Wºô§ä¸ê®Æ
µo²{¦pªG FOR ¸ò NEXT ¤¤¶¡ªºµ{¦¡ ¦pªG¦³¥Î¨ìIF ¦ý«o§Ñ°Oµ¹ END IF
¹q¸£«o¥u·|²Â²ÂªºÄµ§i§AFOR ¸òNEXT¿ù¤F
¤£·|¥h¸Ì­±§ä¥X¯Ê¤FENDIF
«á¨Ó§Ú´N§ä¨ì§Úµ{¦¡¿ù»~ªº¦a¤è¤F

·PÁÂ
«¢Åo~¤j®a¦n§r

TOP

        ÀR«ä¦Û¦b : ¥Ç¿ù¥XÄb®¬¤ß¡A¤~¯à²M²bµL·Ð´o¡C
ªð¦^¦Cªí ¤W¤@¥DÃD