¦^¶Ç¦³ÈªºÀx¦s®æÀ³¹ï²Ä¤@¦Cªº¸ê®Æ
- ©«¤l
- 58
- ¥DÃD
- 9
- ºëµØ
- 0
- ¿n¤À
- 117
- ÂI¦W
- 0
- §@·~¨t²Î
- XP
- ³nÅ骩¥»
- 2003
- ¾\ŪÅv
- 20
- µù¥U®É¶¡
- 2017-12-2
- ³Ì«áµn¿ý
- 2021-12-16
|
¦^¶Ç¦³ÈªºÀ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©Ò¥HnÁ׶})
ª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¦¸§ó°Ê´Npºâ¤@¦¸¾Þ§@¤è«K©O?
©ÎµÛ³£©Ô§¹¤½¦¡«á¥Î¿ï¾Ü©Ê¶K¤W>È¡AÀ³¸Ó¤]¥i¥H¡C
¨ä¹ê³Ì«á¬O·Q¤ñ¹ï¦³µL«½Æ¦C¡A§Ë¦¨³o¼Ë¿z¿ï¤ñ¸û¦n¿z¦Ó¤w
¥un¿zda¸òdb¤£¥ÎA°Ï¤@Ó¤@Ó¿z¤@¹MB°Ï¤@Ó¤@Ó¿z¤@¹M |
|
|
|
|
|
|
- ©«¤l
- 1388
- ¥DÃD
- 4
- ºëµØ
- 0
- ¿n¤À
- 1398
- ÂI¦W
- 0
- §@·~¨t²Î
- WIN
- ³nÅ骩¥»
- 2007
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2015-9-11
- ³Ì«áµn¿ý
- 2024-11-29
|
|
google"EXCEL°g" blog ©Îgoogleºô§}:https://hcm19522.blogspot.com/
|
|
|
|
|
- ©«¤l
- 58
- ¥DÃD
- 9
- ºëµØ
- 0
- ¿n¤À
- 117
- ÂI¦W
- 0
- §@·~¨t²Î
- XP
- ³nÅ骩¥»
- 2003
- ¾\ŪÅv
- 20
- µù¥U®É¶¡
- 2017-12-2
- ³Ì«áµn¿ý
- 2021-12-16
|
¦^´_ 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¶Ü? |
|
|
|
|
|
|
- ©«¤l
- 1388
- ¥DÃD
- 4
- ºëµØ
- 0
- ¿n¤À
- 1398
- ÂI¦W
- 0
- §@·~¨t²Î
- WIN
- ³nÅ骩¥»
- 2007
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2015-9-11
- ³Ì«áµn¿ý
- 2024-11-29
|
¦^´_ 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È «Dpºâ ¤º¤å¬O¼Æ¦r ¤å¦r¡A¤£·|¦³¼vÅT |
|
google"EXCEL°g" blog ©Îgoogleºô§}:https://hcm19522.blogspot.com/
|
|
|
|
|
- ©«¤l
- 1447
- ¥DÃD
- 40
- ºëµØ
- 0
- ¿n¤À
- 1471
- ÂI¦W
- 0
- §@·~¨t²Î
- Windows 7
- ³nÅ骩¥»
- Excel 2010 & 2016
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥xÆW
- µù¥U®É¶¡
- 2020-7-15
- ³Ì«áµn¿ý
- 2024-11-29
|
ÁÂÁ½׾Â,ÁÂÁ¦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
|
|
|
|
|
- ©«¤l
- 354
- ¥DÃD
- 5
- ºëµØ
- 0
- ¿n¤À
- 387
- ÂI¦W
- 0
- §@·~¨t²Î
- windows7
- ³nÅ骩¥»
- vba,vb,excel2007
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2017-1-8
- ³Ì«áµn¿ý
- 2024-8-2
|
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 |
|
|
|
|
|
|