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

¦^¶Ç¦³­ÈªºÀx¦s®æÀ³¹ï²Ä¤@¦Cªº¸ê®Æ

¦^¶Ç¦³­ÈªºÀx¦s®æÀ³¹ï²Ä¤@¦Cªº¸ê®Æ

¥Ø«e»Ý¨D¬°
²Î­p¸Ó¦C«ü©w°Ï°ì(A°Ï)¦³­ÈªºÄæ¦ì¡A¦^¶Ç²Ä¤@¦Cªº­È¡A¼g¦bdaÄæ¦ì
¥t¤@­Ó«ü©w°Ï°ì(B°Ï)¦³­ÈªºÄæ¦ì¡A¦^¶Ç²Ä¤@¦Cªº­È¡A¼g¦bdbÄæ¦ì
­Y¸I¨ì¥Ø«eA/B¨â°Ï¬ÒµL¦³­ÈÄæ¦ì¡A«h°±¤î©¹¤U°õ¦æ (¤¤¶¡·|¦³¨ä¥L»²§U¦C¡A·|¦³¤º®e¡A©Ò¥H­nÁ׶})
ªF¦è¤j·§¹³ªþ¥ó³o­Ó¼Ë¤l
A°Ï $A2:$AU2
       
B°Ï $AW:$CQ2

Book3.zip (4.8 KB)

da & Db Äæ¦ì¥i¥H¥Î¤½¦¡©Îvba¡A¥i¥Hªº¸Ü³£§i¶D§Ú«ç»ò§Ë¡C
¦]¬°¥Ø«e¸ê®Æ®w¤w¸g§Ö6000¦æ¤F¡A¥Îvba»Ý­n®É¤~°õ¦æ¬O§_·|¤ñ¤½¦¡¨C¦¸§ó°Ê´N­pºâ¤@¦¸¾Þ§@¤è«K©O?
©ÎµÛ³£©Ô§¹¤½¦¡«á¥Î¿ï¾Ü©Ê¶K¤W>­È¡AÀ³¸Ó¤]¥i¥H¡C

¨ä¹ê³Ì«á¬O·Q¤ñ¹ï¦³µL­«½Æ¦C¡A§Ë¦¨³o¼Ë¿z¿ï¤ñ¸û¦n¿z¦Ó¤w
¥u­n¿zda¸òdb¤£¥ÎA°Ï¤@­Ó¤@­Ó¿z¤@¹MB°Ï¤@­Ó¤@­Ó¿z¤@¹M

google"EXCEL°g"  blog  ©Îgoogleºô§}:https://hcm19522.blogspot.com/

TOP

¦^´_ 2# hcm19522

§A¦n¡A±N½d¨Ò·Ó§A©Ò­z
K8:B9 ©ñ¶i¤U­±¤½¦¡¡A¥X¨ÓªºªF¦è¤£¹ï
=IFERROR(IF(COLUMN(A1)=1,""," ,")&INDEX($A$1:$AU$1&":"&$A2:AUI2,SMALL(IF($A2:$AU2,COLUMN($A:$G)),COLUMN(A1)))&L8,"")
ª©¥»¬Ooffice2003
ÁÙ¦³¤½¦¡¤¤¶¡ªº
SMALL(IF($C2:$I2,COLUMN($A:$G)
¬°¦ó¬OA:G©O?ªí®æ¤º®e¤£¬OC:J¶Ü?

µM«áSmall
SMALL(array,k)
¶Ç¦^¸ê®Æ²Õ¤¤²Ä k ¤pªº­È¡C

§Úªº¤º¤å¤£¬O¼Æ¦r¡A¬O¤å¦r¡A·|¦³¼vÅT¶Ü?

TOP

¦^´_ 3# wei9133

2003ª©µLIFERROR
COLUMN(A:G)-->1~6   
SMALL(IF($C2:$I2,COLUMN($A:$G)-->$C2:$I2 C¦X¥G±ø¥ó¬°1   I¦X¥G±ø¥ó¬°6
¦X¥G±ø¥ó¨ú¦ì¸m­È «D­pºâ   ¤º¤å¬O¼Æ¦r ¤å¦r¡A¤£·|¦³¼vÅT
google"EXCEL°g"  blog  ©Îgoogleºô§}:https://hcm19522.blogspot.com/

TOP

ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«á¾ÇÂǦ¹©«½m²ßVBA§ä¥X½d³òÀx¦s®æ¤¤ªº­«½Æ¦C©ÎªÅ¦C,¾Ç²ß¤è®×¦p¤U,½Ð¦U¦ì«e½ú«ü±Ð
°õ¦æ«e:


°õ¦æµ²ªG:


Option Explicit
Sub ¿ï¨ú­«½Æ©ÎªÅ¥Õ¦C()
Dim Brr, Z, i&, j%, T$, T1$, xU As Range, C%
'¡ô«Å§iÅܼÆ
Set Z = CreateObject("Scripting.Dictionary")
'¡ô¥OZÅܼƬO ¦r¨å
Brr = Range([CQ1], Cells(ActiveSheet.UsedRange.Rows.Count, "A"))
'¡ô¥OBrrÅܼƬO±a¤J«ü©w½d³òÀx¦s®æ­Èªº¤Gºû°}¦C
C = UBound(Brr, 2): T1 = Application.Rept("/", C)
'¡ôCÅܼƬOBrr°}¦C¾î¦V³Ì¤j¯Á¤ÞÄ渹,¥OT1ÅܼƬOCÅܼƭӳsÄò"/"²Å¸¹ªº¦r¦ê
For i = 2 To UBound(Brr)
'¡ô³]¶¶°j°é!i±q2¨ì Brr°}¦CÁa¦V³Ì¤j¯Á¤Þ¦C¸¹
   For j = 1 To C: T = T & "/" & Brr(i, j): Next
   '¡ô³]¶¶°j°é!j±q1¨ì CÅܼÆ,¥OTÅܼƬO¥H"/"²Å¸¹³s±µ¦U°j°é°}¦C­Èªº·s¦r¦ê
   If Z(T) <> "" Or T = T1 Then
   '¡ô¦pªG¥HTÅܼƬdZ¦r¨å¦^¶Çitem¤£¬OªÅªº ©ÎTÅܼƦP T1ÅܼÆ?
      If xU Is Nothing Then
      '¡ô¦pªGxUÅܼƬOªÅªº?
         Set xU = Cells(i, 1)
         '¡ô¥OxUÅܼƬO i°j°é¦CAÄæÀx¦s®æ
         Else
         Set xU = Union(xU, Cells(i, 1))
         '¡ô§_«h´N¥O i°j°é¦CAÄæÀx¦s®æ¯Ç¤J xUÅܼƤ¤
      End If
   End If
   Z(T) = 1: T = ""
   '¡ô¥OZ¦r¨å¤¤ key¬°TÅܼƪº item­È¬O1,¥OTÅܼƲMªÅ
Next
If xU Is Nothing Then MsgBox "¨S¦³­«½Æ¦C": Exit Sub
'¡ô¦pªGxUÅܼÆÁÙ¬OªÅªº!´N¸õ¥X´£µøµ¡~~~,¤§«áµ²§ôµ{¦¡°õ¦æ
xU.EntireRow.Select
'¡ô¦pªGxUÅܼƤ£¬OªÅªº!´N±N¨ä©Ò¦bªº¦C¿ï¨ú
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

Sub t3()
i = Split("Provider=Microsoft.,Jet.OLEDB.4,.0;Extended Properties=Excel ,8,.0;Data Source=", ",")
If Application.Version > 12 Then i(1) = "ACE.OLEDB.12": i(3) = 12
Set cn = CreateObject("adodb.connection"): cn.Open Join(i, "") & ThisWorkbook.FullName
For i = 1 To 47
x = x & "& iif(IsNull([" & i & "]),"""",""." & i & """)"
Next
q = "select mid(b,2,999) from(" & "select " & Mid(x, 2, 99999) & " as b from "
[cv:cx].ClearContents: [CV2].CopyFromRecordset cn.Execute(q & "[sheet1$a1:au])")
[CW2].CopyFromRecordset cn.Execute(q & "[sheet1$aw1:cq])")
[CX2].CopyFromRecordset cn.Execute("select F1 &""&"" &F2 from [sheet1$CV1:CW]")
End Sub

TOP

        ÀR«ä¦Û¦b : ¯àµ½¥Î®É¶¡ªº¤H¡A¥²¯à´x´¤¦Û¤v§V¤Oªº¤è¦V¡C
ªð¦^¦Cªí ¤W¤@¥DÃD