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

¿é¤JÃöÁä¦r§Y¥i§ä¨ì¸ê®Æ

¿é¤JÃöÁä¦r§Y¥i§ä¨ì¸ê®Æ

½Ð±Ð¦U¦ì°ª¤â
¥Ñ©ó¤u§@¤WªºÃö«Y
»Ý­n¦AA2Äæ¦ì¤¤¿é¤JÃöÁä¦r
¬d¸ß¨ì»Ý­nªº¸ê®Æ¡A¦pªþ¹Ï©Ò¥Ü
¥ý«e¦³ª¦¤å¬O°µ¬Ý¬Ý¡A¦ý¤@ª½³£§Ë¤£¥X¨Ó
§Æ±æ¦U¦ì¤j¤j¥i¥Hª½±µ´£¨Ñ±Ð¾ÇÀÉ®×µ¹§Ú
·P¿E¤£ºÉ ^ ^

¹Ï¤ù1.jpg (112.49 KB)

¹Ï¤ù1.jpg

¤j·§¬O³o¼Ë
C5 =MATCH("*"&A5&"*",Sheet2!B:B,)
B5 =INDEX(Sheet2!A:A,C5)
¤½¦¡©¹¤U½Æ»s

"*"&A5&"*" ªí¥Ü MATCH ¨Ï¥Î¼Ò½k¬d¸ß¡A¥u­n¦r¦ê¤¤¦³A5´N¦æ¡A*¦r¤¸ªí¥Ü¬°¥ô·N¦r¤¸³£¥i¥H
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

ÁÂÁ ML089ªº¤èªk´£¨Ñ,
¦ýÁÙ¬O¥Î¤£¥X¨Ó­C!
¥i§_¸Ô²Ó»¡©ú©O?ÁÂÁÂ

TOP

¥»©«³Ì«á¥Ñ ML089 ©ó 2016-3-15 18:12 ½s¿è

¦^´_ 3# mj0917m

¤½¦¡Â²³æ¡AINDEX¡BMATCH¨ç¼Æ»¡©ú«öF1´N¥i¥H¤F¸Ñ
­«ÂI ¼Ò½k¬d¸ß ¨Ï¥Î *

¤£ª¾¹DÁÙ­n¤F¸Ñ¬Æ»ò?
­n¤£­n¶ÇÀɤW¨Ó¡A¬Ý¬Ý§A°µ¿ù¦b­þ¸Ì


¤£¦n·N«ä¡A§Úµ¹§Aªº¬O¤½¦¡¡A¤£¬OVBAµ{¦¡
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 1# mj0917m


    ½Ð¸Ô²Ó±Ô­z  »¡©ú °ÝÃD  ©Ò·Q­n±oµ²ªG

    µ¹§A­Ó´£¥Ü¡G
  1.    
  2. Application.DisplayAlerts = False  '´£¥Ü¨ú®ø
  3. Application.ScreenUpdating = False '¿Ã¹õ§ó·s¨ú®ø



  4. Sub A()
  5.    'str = "¤º"
  6.    'str3 = "¦~"
  7. 'Cells(1, 15) = "·j´M¥Ø¼Ð·½"
  8. 'Cells(1, 16) = "§PÂ_¬O§_§ä¨ì"
  9. 'Cells(1, 17) = "ÃöÁä¦r¦ì¸m"
  10. For asy = 2 To ActiveSheet.Range("b1").CurrentRegion.Rows.Count
  11.     Cells(asy, 15) = Cells(asy, 2)
  12.     For ahs = 94 To 99
  13.    
  14.    
  15.    
  16.     Next ahs
  17. Next asy


  18.         For i = 2 To ActiveSheet.Range("a2").CurrentRegion.Rows.Count
  19.             If Mid(Cells(i, 1), 1, 3) = "099" Or Mid(Cells(i, 1), 1, 2) = "99" Then
  20.                 If (InStr(1, ¤u§@ªí1.Cells(i, 1), "¤º") >= 1) Then
  21.                      ¤u§@ªí1.Cells(i, 7) = "find"
  22.                      ¤u§@ªí1.Cells(i, 11) = InStr(¤u§@ªí1.Cells(i, 1), "¤º")
  23.                      s = s + 1
  24.                 End If
  25.             End If
  26.         Next
  27.                ¤u§@ªí1.Cells(i, 8) = "find"




  28. End Sub
½Æ»s¥N½X

  ¦h°µ¦h·Q¦h¾Ç²ß¡A¤Ö¬Ý¤Ö¿ù¤Ö°g³~

  ¦h°µ=¦h¦h½m²ß¡A¦h¦h½s¼g¡C
  ¦h·Q=·Q·Q¬°¤°»ò¤H®aµ{¦¡­n¨º¼Ë¼g¡A¦pªG´«¦¨¦Û¤v¡A¤S·|«ç¼g¡C
  ¦h¾Ç²ß=¾Ç²ß¤H®aªºµo°Ý¨Ã¸Ñµª¡A¾Ç²ß¤H®aªº¼gªk

  ¤Ö¬Ý=¥u¬Ý¤£°µ¤]ªPµM

TOP

¦^´_ 1# mj0917m

ªí®æ.rar (7.81 KB)
¤£¦n·N«ä¡A³Ìªñ¤u§@¸û¦£¡A
¤p§Ìªº¶D¨D¦p¤U
»Ý­n¦AA2ÂŦâÄæ¦ì¤¤¿é¤JÃöÁä¦r
¤U­±Äæ¦ì§Y¥i¸õ¥X¬d¸ß¨ì»Ý­nªº¸ê®Æ¡AÀɮצpªþ¥ó
·Ð½Ð¦U¦ì¤j¤j«ü¾É¸Ó¦p¦ó»s§@©Î¬O¥i¥Hª½±µ´£¨Ñ±Ð¾ÇÀÉ®×µ¹§Ú
·P¿E¤£ºÉ ^ ^

TOP

§ÚÁÙµLªk¤U¸ü©pªºÀɮסA¥u¯à·Ó¦r­±¥h¤ÀªR©p­nªº¡A§Ú·Q¥Î³o­Ó¤½¦¡À³¸Ó¥i¥H
=IF(ISERROR(MATCH("*"&A2&"*",B:B,0)),"¬dµL¸ê®Æ",INDEX(B:B,MATCH("*"&A2&"*",B:B,0)))
¤]·|®Ú¾Ú©p¦bA2Äæ¦ì¿é¤Jªº¦r¡A¥hB:B³oÃä¥h´M§ä¦³¬Û¦P¤º§t­Èªº¡A¦ý¬O¥L¥u¯à¶]¥X²Ä¤@µ§³Q§ä¨ìªº­È¡A¤£ª¾¹D³o­Ó¬O¤£¬O©p­nªºµª®×
clio

TOP

¦^´_ 6# mj0917m


ALT+F11  ²¾¨ì¤u§@ªí¤@ªºµ{¦¡¤º  §â¥H¤Uµ{¦¡½X¶K¤W
  1. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  2. Dim ss As String: ss = [a2]
  3.     For iui = 5 To 11
  4.         
  5.             If Range("a" & iui).Value = ss Then [b2:d2] = Range("b" & iui, "d" & iui).Value
  6.         
  7.     Next

  8. End Sub
½Æ»s¥N½X

  ¦h°µ¦h·Q¦h¾Ç²ß¡A¤Ö¬Ý¤Ö¿ù¤Ö°g³~

  ¦h°µ=¦h¦h½m²ß¡A¦h¦h½s¼g¡C
  ¦h·Q=·Q·Q¬°¤°»ò¤H®aµ{¦¡­n¨º¼Ë¼g¡A¦pªG´«¦¨¦Û¤v¡A¤S·|«ç¼g¡C
  ¦h¾Ç²ß=¾Ç²ß¤H®aªºµo°Ý¨Ã¸Ñµª¡A¾Ç²ß¤H®aªº¼gªk

  ¤Ö¬Ý=¥u¬Ý¤£°µ¤]ªPµM

TOP

²q·Q¼Ó¥D¬O·Q§âA2ªºÄæ¦ì·í¦¨©_¼¯¶®ªêªº·j´M"²£«~¦W"¡A¦Aoutput¦¨msgbox("½s¸¹")("©ñ¸m¦ì¸m")("³Æµù")

TOP

¤pªº´£¨Ñ³o­Ó¨ç¼Ælookupµ¹¼Ó¥D°Ñ¦Ò¡A¨Ã´£¨Ñ¬ÛÃö±Ð¾Ç¼v¤ù¦p¤U¡AÁÂÁ¡C

https://www.youtube.com/watch?v=Q32syItTv8Q

¥t¥~¤è¤~´£¨Ñªºmsgbox·Qªk¡A­n¨Ï¥Î¨ìVBAµ{¦¡½X¡A³o¥i¯à­n½Ð¼Ó¤W¦U¦ì¤j¤j¸Ñ´b¤F¡A¤pªº¤]·Q¹Á¸Õ¼g¬Ý¬Ý¡A¥u¬O­n¤@¬q®É¶¡¡A¤~­è°_¨B¾Ç¡A¤£¦n·N«ä°Õ¡C

TOP

        ÀR«ä¦Û¦b : ¤â¤ß¦V¤U¬O§U¤H¡A¤â¤ß¦V¤W¬O¨D¤H¡F§U¤H§Ö¼Ö¡A¨D¤Hµh­W¡C
ªð¦^¦Cªí ¤W¤@¥DÃD