¦p¦ó±N横¦C¸ê®ÆÂରª½¦C¨Ã½s¸¹
- ©«¤l
- 218
- ¥DÃD
- 73
- ºëµØ
- 0
- ¿n¤À
- 290
- ÂI¦W
- 0
- §@·~¨t²Î
- WIN10
- ³nÅ骩¥»
- Office2010
- ¾\ŪÅv
- 20
- ©Ê§O
- ¤k
- µù¥U®É¶¡
- 2014-5-19
- ³Ì«áµn¿ý
- 2022-11-29
|
¦p¦ó±N横¦C¸ê®ÆÂରª½¦C¨Ã½s¸¹
½Ð°Ý¦p¦ó±N¤W¤è横¦C±q¥ª¨ì¥kªºq³æ+½s¸¹¡A¥H¤½¦¡Åܦ¨¥kÃ䪽¦C±q¤W¨ì¤Uªº¤è¦¡§e²{
1. ¦p¾î¦Cq³æªº½s¸¹¦³2µ§¥H¤W¡A«hª½¦Cq³æ¦Û°Ê¥[-1¡B-2¡K¡K
¨Ò¦p¡GÀx¦s®æA5q³æIT97802521¡A¦@¦³B5¡BC5¨âÓ½s¸¹¡A¬GÂà¨ìNÄæq³æ±Æ¦C®É¡A·|¦Û°Ê¥Í¦¨IT97802521¡BIT97802521-1¨â±iq³æ¡AOÄæ«h±a¥X¸Óq³æ¨âÓ½s¸¹¡A¥H¦¹Ãþ±À¡K
2. ³æ¤@q³æªº½s¸¹¦pªG¦³«½Æ¡A«h¤£«½Æ§ì¨ú¡A¶È§ì¨ú°ß¤@ȧY¥i¡C
¨Ò¦p¡GÀx¦s®æA7q³æIT96801568¡AÁöµM¦³¤TÓ½s¸¹¡A¦ýC7¸¹F7½s¸¹«½Æ¡A¬G¥u»Ý§ì¨ú¨ä¤¤1µ§½s¸¹§Y¥i¡C¬GÂà¨ìNÄæq³æ±Æ¦C®É¡Aq³æIT96801568¥u·|¦³IT96801568¡BIT96801568-1¡A¤£·|¦³IT96801568-2¡C
Àx¦s®æA15q³æIT2D800727¥H¦¹Ãþ±À
3. ªÅ¥ÕÀx¦s®æ¤ÎYµL»Ý§ì¨ú¡C
¯S§O´£¿ô¡A¤W¤è¾î¦C¸ê®Æ¬O¥Ñ¨t²Î§ì¨ú¡Aµo²{¦n¹³ªÅ®æ¨Ã«DªÅ¥Õ®æ¡AÀ³¸Ó¬O¦³µLªk¦C¦L¦r¤¸©ÎÁôÂær¤¸§Ú²q¡A¦]¬°¿ï¨úB2:J19®É¨Ï¥Î´M§ä>¯S®í¥Ø¼Ð>ªÅ®æ¡Aµo²{¨Ã«D¥þ¬°ªÅ®æ¡C
¦ý§Ú¤£ª¾¹DÂæ·|¤£·|¼vÅT¡K¡K¡K
´ú¸ÕÀÉ.rar (10.51 KB)
|
|
*¦v¤k¤@ªTµL»~*
|
|
|
|
|
- ©«¤l
- 2025
- ¥DÃD
- 13
- ºëµØ
- 0
- ¿n¤À
- 2053
- ÂI¦W
- 0
- §@·~¨t²Î
- WIN7
- ³nÅ骩¥»
- Office2007
- ¾\ŪÅv
- 100
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥x¥_¥«
- µù¥U®É¶¡
- 2011-3-2
- ³Ì«áµn¿ý
- 2024-3-14
|
C2»PB16½s¸¹¬Û¦P
¦P¦C½s¸¹¬Û¦P¥u¨ú1Ó¡A¦ý¤£¦P¦C½s¸¹¬Û¦Pn¨ú¶Ü? |
|
{...} ªí¥Ü»Ýn¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡
|
|
|
|
|
- ©«¤l
- 218
- ¥DÃD
- 73
- ºëµØ
- 0
- ¿n¤À
- 290
- ÂI¦W
- 0
- §@·~¨t²Î
- WIN10
- ³nÅ骩¥»
- Office2010
- ¾\ŪÅv
- 20
- ©Ê§O
- ¤k
- µù¥U®É¶¡
- 2014-5-19
- ³Ì«áµn¿ý
- 2022-11-29
|
¦^´_ 2# ML089
M¤j~~~¤£¦P¦C¤¬¤£¼vÅT¡A¥H¦P¦C¬°Àˬd¬°¥D¡AÁÂÁ§A~~~~~ |
|
*¦v¤k¤@ªTµL»~*
|
|
|
|
|
- ©«¤l
- 218
- ¥DÃD
- 73
- ºëµØ
- 0
- ¿n¤À
- 290
- ÂI¦W
- 0
- §@·~¨t²Î
- WIN10
- ³nÅ骩¥»
- Office2010
- ¾\ŪÅv
- 20
- ©Ê§O
- ¤k
- µù¥U®É¶¡
- 2014-5-19
- ³Ì«áµn¿ý
- 2022-11-29
|
¦^´_ 4# Hsieh
H¤j~~~~¨º½Ð°ÝVBA¦³¸Ñ£v¡H¦]¬°§Ú¨S¦³ÀYºü¨ä¹ê |
|
*¦v¤k¤@ªTµL»~*
|
|
|
|
|
- ©«¤l
- 218
- ¥DÃD
- 73
- ºëµØ
- 0
- ¿n¤À
- 290
- ÂI¦W
- 0
- §@·~¨t²Î
- WIN10
- ³nÅ骩¥»
- Office2010
- ¾\ŪÅv
- 20
- ©Ê§O
- ¤k
- µù¥U®É¶¡
- 2014-5-19
- ³Ì«áµn¿ý
- 2022-11-29
|
¦^´_ 6# Hsieh
H¤j~~~~§A¹ê¦b¬O¤Ó¼F®`¤F!!!ÁÙ¯uªº§¹¥þ¸Ñ¨M¤F§Úªº¤@¤j°ÝÃDC!!!!¶W·PÁ§Aªº~~~~~~~~~~~~ |
|
*¦v¤k¤@ªTµL»~*
|
|
|
|
|
- ©«¤l
- 1391
- ¥DÃD
- 4
- ºëµØ
- 0
- ¿n¤À
- 1401
- ÂI¦W
- 0
- §@·~¨t²Î
- WIN
- ³nÅ骩¥»
- 2007
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2015-9-11
- ³Ì«áµn¿ý
- 2024-12-16
|
¥»©«³Ì«á¥Ñ hcm19522 ©ó 2018-3-9 13:06 ½s¿è
K2:K26{=INDIRECT(TEXT(SMALL(IF((COUNTIF(OFFSET(B$1,ROW($1:$18),,,COLUMN(A:I)),B$2:J$19)=1)*(LEN(B$2:J$19)>1),ROW(B$2:J$19)/1%+1),ROW(A1)),"!R0C00"),)&IF(T(INDIRECT(TEXT(SMALL(IF((COUNTIF(OFFSET(B$1,ROW($1:$18),,,COLUMN(A:I)),B$2:J$19)=1)*(LEN(B$2:J$19)>1),ROW(B$2:J$19)/1%+1),ROW(A1)),"!R0C00"),))=LEFT(K1,10),-COUNTIF(K$1:K1,LEFT(K1,10)&"*"),"")
¤G¬q¦³¦â¦rÅé¬Û¦P
L2:L26{=IFERROR(INDIRECT(TEXT(SMALL(IF((COUNTIF(OFFSET(B$1,ROW($1:$18),,,COLUMN(A:I)),B$2:J$19)=1)*(LEN(B$2:J$19)>1),ROW(B$2:J$19)/1%+COLUMN(B:J)),ROW(A6)),"!R0C00"),),"")
55-4156.rar (14.56 KB)
|
-
1
µû¤À¤H¼Æ
-
|
google"EXCEL°g" blog ©Îgoogleºô§}:https://hcm19522.blogspot.com/
|
|
|
|
|
- ©«¤l
- 218
- ¥DÃD
- 73
- ºëµØ
- 0
- ¿n¤À
- 290
- ÂI¦W
- 0
- §@·~¨t²Î
- WIN10
- ³nÅ骩¥»
- Office2010
- ¾\ŪÅv
- 20
- ©Ê§O
- ¤k
- µù¥U®É¶¡
- 2014-5-19
- ³Ì«áµn¿ý
- 2022-11-29
|
¦^´_ 8# hcm19522
h¤j~~~~§A¤]¦n¼F®`³á!!! |
|
*¦v¤k¤@ªTµL»~*
|
|
|
|
|
- ©«¤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-12-5
|
ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«á¾ÇÂǦ¹©«½m²ß°}¦C»P¦r¨å,¾Ç²ß¤è®×¦p¤U,½Ð¦U¦ì«e½ú«ü±Ð
°õ¦æ«e:
°õ¦æµ²ªG:
Sub TEST()
Dim ¸ê®Æ°}¦C, ªÅ°}¦C, ¦r¨å, i&, j%, ªÅ°}¦C¤w¨Ï¥Î¦C¼Æ&, ¦P¦C¤£«½Æ½s¸¹¼Æ%
Set ¦r¨å = CreateObject("Scripting.Dictionary")
¸ê®Æ°}¦C = Range([J2], [A65536].End(xlUp))
ReDim ªÅ°}¦C(1 To 10000, 1 To 2)
For i = 1 To UBound(¸ê®Æ°}¦C)
q³æ = Trim(¸ê®Æ°}¦C(i, 1))
¦P¦C¤£«½Æ½s¸¹¼Æ = 0
For j = 2 To UBound(¸ê®Æ°}¦C, 2)
If Trim(¸ê®Æ°}¦C(i, j)) = "Y" Or Trim(¸ê®Æ°}¦C(i, j)) = "" Then GoTo j01
If ¦r¨å(Trim(¸ê®Æ°}¦C(i, j)) & i) <> "" Then
GoTo j01
Else
¦r¨å(Trim(¸ê®Æ°}¦C(i, j)) & i) = "X"
End If
ªÅ°}¦C¤w¨Ï¥Î¦C¼Æ = ªÅ°}¦C¤w¨Ï¥Î¦C¼Æ + 1
¦P¦C¤£«½Æ½s¸¹¼Æ = ¦P¦C¤£«½Æ½s¸¹¼Æ + 1
If ¦P¦C¤£«½Æ½s¸¹¼Æ = 1 Then
ªÅ°}¦C(ªÅ°}¦C¤w¨Ï¥Î¦C¼Æ, 1) = q³æ
Else
ªÅ°}¦C(ªÅ°}¦C¤w¨Ï¥Î¦C¼Æ, 1) = q³æ & "-" & ¦P¦C¤£«½Æ½s¸¹¼Æ - 1
End If
ªÅ°}¦C(ªÅ°}¦C¤w¨Ï¥Î¦C¼Æ, 2) = Trim(¸ê®Æ°}¦C(i, j))
j01: Next
Next
[P2].Resize(ªÅ°}¦C¤w¨Ï¥Î¦C¼Æ, 2) = ªÅ°}¦C
End Sub |
|
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y
|
|
|
|
|