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

±Æ§Ç!!

±Æ§Ç!!

A             B                C            D
¼Æ¾Ç        20                ­^¤å        30,60,78,74
­^¤å        30                ¤Æ¾Ç        65,32,89
¼Æ¾Ç        40                ¦ÛµM        75,96,85
­^¤å        60                ¼Æ¾Ç        20,40,95,34
¦ÛµM        75                       
¤Æ¾Ç        65       
¼Æ¾Ç        95                       
­^¤å        78                       
¦ÛµM        96                       
¤Æ¾Ç        32                       
¼Æ¾Ç        34                       
­^¤å        74                       
¦ÛµM        85                       
¤Æ¾Ç        89                       

¦p¦ó¦bD1¿é¤J¤½¦¡Åýµª®×²£¥Í
PS AB¸ê®Æ·|¤£Â_§ó·s
Cyssuc

http://blog.xuite.net/hcm19522/twblogÃöÁä¦r  2645
Ãþ¦üÃD

TOP

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

TOP

¦^´_ 3# hcm19522

·PÁÂhcm19522«e½ú´£¨Ñ¸Ñµª,¤Q¤À­È±o¬ã¨s.

¤p§Ì¥»·Q¥t¶}·s¥DÃD´£°Ý,¦]·Q´£ªº°ÝÃD¬°VBA,À³¤£Äݤ@¯ë°Ïªº½d³ò¤º.
¦ý¦]°ÝÃD¬O·½©ó¦¹¥DÃD,¬G¥Î¦^¤å¤è¦¡,
­Y¦³¤£§´ÁٽФ£§[§iª¾,¤p§Ì¤@©w§ï¶i,¤Q¤À·PÁÂ.

¤p§Ì¬Ý¨ì¦¹¥DÃD«á¦³¸ÕµÛ¥ÎªO¤W«e½úªºµ{¦¡½X¾Õ¦Û­×§ï,¸Õ¹Ï¼g¥XÃþ¦üµ²ªG.
ªþ¤WÀɮפε{¦¡½X,§Æ±æ¯à°÷¦³©ÒÀ°§U.¦³¤£·í³B½Ð¤£§[§iª¾,¤p§Ì¤@©w§ï¶i.
¦]¥\¤O¤Ó®t,­×§ï¥X¨Óªºµ{¦¡½X«D±`¤¾ªø,¦Ó¥B¦³¤@­Ó°ÝÃD,´N¬O­n¥ý±N­ì©l¸ê®Æ¥Îexcelªº¤º«Ø¥\¯à±Æ§Ç¹L«á,µ²ªG¤~¥i¥¿±`Åã¥Ü....

­Y¦³«e½úÄ@·N©âªÅ«üÂI§ó²¼äªº¼gªk,¤p§Ì·P¿E¤£ºÉ.
  1. '¦¹µ{¦¡½X­×§ï¦Û³Â»¶®a±Ú°Q½×°Ïexcel°ª¤â²a´£³¡ªL«e½ú©Ò¼g,«D§Ú¦Û³Ð.
  2. '°Q½×°Ïºô§}:http://forum.twbts.com/index.php

  3. Sub test1()
  4. Dim arr, arr1, arr2, arr3, arr4, brr(1 To 4), k, Ar
  5. arr = Range("a2:b" & Cells(Rows.Count, 1).End(xlUp).Row)


  6. '¥¨¶°¿ý»sexcelªº±q¤p¨ì¤j±Æ§Ç¥\¯à(±Æ§ÇBÄæ)
  7.     ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
  8.     ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("B2"), _
  9.         SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
  10.     With ActiveWorkbook.Worksheets("Sheet1").Sort
  11.         .SetRange Range("A2:B" & Cells(Rows.Count, 1).End(xlUp).Row)
  12.         .Header = xlNo
  13.         .MatchCase = False
  14.         .Orientation = xlTopToBottom
  15.         .SortMethod = xlPinYin
  16.         .Apply
  17.     End With
  18. '¿ý»sµ²§ô


  19. '­pºâ¨C­Ó¬ì¥Ø¥X²{ªº¦¸¼Æ
  20. Set myD = CreateObject("scripting.dictionary")
  21. For k = 1 To UBound(arr)
  22. T = arr(k, 1)
  23. myD(T) = myD(T) + 1
  24. Next k


  25. ReDim arr1(1 To myD("­^¤å"))
  26. ReDim arr2(1 To myD("°ê¤å"))
  27. ReDim arr3(1 To myD("¼Æ¾Ç"))
  28. ReDim arr4(1 To myD("¦ÛµM"))


  29. For i = 1 To UBound(arr)

  30. If arr(i, 1) = "­^¤å" Then N = 1
  31. If arr(i, 1) = "°ê¤å" Then N = 2
  32. If arr(i, 1) = "¼Æ¾Ç" Then N = 3
  33. If arr(i, 1) = "¦ÛµM" Then N = 4

  34. brr(N) = brr(N) + 1

  35. If N = 1 Then arr1(brr(1)) = arr(i, 2)
  36. If N = 2 Then arr2(brr(2)) = arr(i, 2)
  37. If N = 3 Then arr3(brr(3)) = arr(i, 2)
  38. If N = 4 Then arr4(brr(4)) = arr(i, 2)

  39. 101:
  40. Next i


  41. [e2] = Join(arr1, "¡B")
  42. [e3] = Join(arr2, "¡B")
  43. [e4] = Join(arr3, "¡B")
  44. [e5] = Join(arr4, "¡B")

  45. End Sub
½Æ»s¥N½X
20161025-°}¦C-±Æ§Ç¬ì¥Ø¤À¼Æ.zip (12.78 KB)


¤p§Ì¤£ª¾¦¹ºØµo°Ý¤è¦¡¥H¤Î¥ÎªO¤W«e½úµ{¦¡½X­×§ï¥h¦^µªªO¤Í°ÝÃDªº¤è¦¡¬O§_«ê·í,
­Y¦³¤£§´·í³B½Ð¤£§[«ü¥¿»P§iª¾,¤p§Ì¤@©w§ï¶i,¤Q¤À·PÁÂ.

TOP

¥»©«³Ì«á¥Ñ ­ã´£³¡ªL ©ó 2016-10-27 10:37 ½s¿è

B999.gif


¢Ñ¢°¡G=B1&LOOKUP("z",IF({1,0},"",","&VLOOKUP(A1,A2:C$9999,3,)))
¢Ó¢°¡G=VLOOKUP(D1,A:C,3,)

Xl0000197-1.rar (4.33 KB)
¡@
¡@

TOP

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

µ¥¤F¤@°},
¥i¯àCyssucª©¤Í¦³ÂI¨Æ¥¼¤Î¦^À³,½Ð¤¹³\¤p§Ì¾Õ¦Û¦^À³¤@¤U,ÁöµM¤£¬O§Úµoªº¥DÃD....
¦]­Ó¤H²z¸Ñ,¦³«e½úÄ@·N´£¨Ñ¸Ñµª,¥i¥Hªº¸Ü¯à°÷¦^À³¨Ï¥Î±¡§Î¨Ãªí¥Ü·PÁ·|¬O¤ñ¸û¦nªº.
·íµM¥H¤W¶È­Ó¤H²z¸Ñ.¨¥½×¦³¤£·í³B½Ð§iª¾,¤p§Ì¤@©w§ï¶i.

«D±`·PÁ²a´£³¡ªL«e½ú´£¨Ñ¸Ñµª,ÁöµM¤£¬O­ì´£°ÝªÌ,¤]¬Oªí¹F¤@¤U·PÁÂ.ªO¤Í´£ªº°ÝÃD,¨ä¹ê¦³®É­Ô¤]·|¬O¦Û¤v·Q°Ýªº°ÝÃD©O.

TOP

·PÁ¤j®aºëÅPªº¸Ñµª¡@
¤p§Ì·P®¦¤£ºÉ
­Õ­Y¦^ÂФӱß
²`·P©êºp
¥t¦³¤@ºÃ°Ý
¥H¤U¼Æ¦C
¢µ¢¯¡A¢³¢¯¡A¢·¢¯¡A¢²¢¯¡A¢¸¢¯
¦p¦ó¤U¨ç¼Æ
¢²¢¯¡A¢³¢¯¡A¢µ¢¯¡A¢·¢¯¡A¢¸¢¯
Cyssuc

TOP

¥»©«³Ì«á¥Ñ hcm19522 ©ó 2016-11-6 10:38 ½s¿è

=IFERROR(IF(COLUMN(A1)=1,"",",")&SMALL(IFERROR(--TRIM(MID(SUBSTITUTE($B2,",",REPT(" ",99)),ROW($1:$5)*99-98,99)),FALSE),COLUMN(A1))&E2,"")
©¹¥k©Ô¤­®æ ²Ä¤@®æ¬°µ²ªG
http://blog.xuite.net/hcm19522/twblog/463182914

TOP

        ÀR«ä¦Û¦b : ¤Ñ¤W³Ì¬ü¬O¬P¬P¡A¤H¥Í³Ì¬ü¬O·Å±¡¡C
ªð¦^¦Cªí ¤W¤@¥DÃD