[µo°Ý] ½Ð°ÝEXCEL«ç»ò¨ú±o¤u§@ªíªº¼Æ¶q»P¸ê®Æ¦æ¼Æ©O(¸ó¤u§@ªí)!?ÁÂÁÂ

konkon3141

½Ð°Ý
°ÝÃD1:EXCEL«ç»ò¨ú±o¤u§@ªíªº¼Æ¶q!?
§Ú¦³ªºÀɮצ³n­Ó¤u§@ªí
§Ú»Ý­n¦b²Ä1­¶Åã¥Ün-2­Ó¤u§@ªí(¦©±¼¥Ø¿ý»P«¬¿ý)
­n¥Î¬Æ»ò¤èªk©O!?

¦pªG¦³·s¼W·|§R´î¸ê®Æªí
«h¤u§@ªí¼Æ¶q­n§ó·s

§Ú¤£·|¥ÎVBA..
ª©¥»¬O2003ªº
¦pªG¥Î¨ìVBA½Ð¤j¤j²Ó¤ß±Ð§Ú­n«ç»ò¨Ï¥Î~~


°ÝÃD2:¥H¤U¥\¯à¥h°£«e2­Ó¤u§@ªí(¦©±¼¥Ø¿ý»P«¬¿ý)
§Ú¦b²Ä3¤u§@ªí~«á­±©Ò¦³¤u§@ªí¤¤
¦bB9~¥H¤UµL­­Äæ  ¶ñ¤J¸ê®Æ®É
¦b²Ä1­¶¯àÅã¥Ü¦³¸ê®Æ¦æ¼Æ

¦p¦bB9¶ñ¤F¸ê®Æ²Ä1­¶Åã¥Ü1
B10¦pªG¤]¶ñ¤F«h²Ä1­¶Åã¥Ü2
¦pªG²Ä4­Ó¤u§@ªíB9¤]¶ñ¤F
«hÅã¥Ü3

­n¯à¥h§ìB9Äæ¥H¤U(¥]¬AB9)¦³¸ê®Æ¦æ¦³´X¦æ
¦Ó¥B­n¸ó¤u§@ªí°Oºâ(²Ä3¤u§@ªí&µL½a¤j(¦]¬°¦³®É·|·s¼W¤u§@ªí))
¸ê®Æ·|ÀHµÛ¶ñ¤J¦Ó§ó·s

§Ú¤£·|¥ÎVBA..
ª©¥»¬O2003ªº
¦pªG¥Î¨ìVBA½Ð¤j¤j²Ó¤ß±Ð§Ú­n«ç»ò¨Ï¥Î~~

tincholon

¥i¥H¨Ï¥Î¥¨¶°°Ú!

stillfish00

¦^´_ 1# konkon3141
¥ý¨ìEXCEL¿ï¶µ¤Ä¿ï¶}µo¤H­û , ¶i¤JVisual Basic
¥ª¤è¿ï²Ä¤@­Ó¤u§@ªí , ½Æ»s¥H¤U¥N½X

1. Private Sub Worksheet_Activate()
   
2.     'Q1:¦C¥X¤u§@ªí²M³æ
   
3.     Dim i
   
4.     Range(Range("A1"), Range("A1").End(xlDown)).ClearContents   '¥ý²M°£
   
5.     For i = 3 To Sheets.Count   '¤£§t«e¨â­Ó¤u§@ªí(¥Ø¿ý,«¬¿ý)
   
6.         Range("A1").Offset(i - 3, 0) = Sheets(i).Name
   
7.     Next
   
8.    
   
9.     'Q2:­pºâÁ`¼Æ
   
10.     Range("B1") = "=COUNTA(" & Sheets(3).Name & ":" & Sheets(Sheets.Count).Name & "!B:B)" & "-COUNTA(" & Sheets(3).Name & ":" & Sheets(Sheets.Count).Name & "!B1:B8)"
    
11. End Sub

½Æ»s¥N½X

konkon3141

¦^´_  konkon3141
¥ý¨ìEXCEL¿ï¶µ¤Ä¿ï¶}µo¤H­û , ¶i¤JVisual Basic
¥ª¤è¿ï²Ä¤@­Ó¤u§@ªí , ½Æ»s¥H¤U¥N½X
stillfish00 µoªí©ó 2012-10-28 01:20

¥i¯à§Ú¸ÑÄÀ°ÝÃD¤ñ¸û®z
§Ú¶Ç­Ó½d¨ÒÀÉ®×
1.rar (2.6 KB)

¤U¸ü¦¸¼Æ: 5

2012-10-28 09:04

EXCEL¦³µù¸Ñ


°ÝÃD1¬O²Ä1¤u§@ªí²Î­p¶q¤¤
¯à­pºân-2¤u§@ªí¦³´X­Ó(¥h±¼²Ä1¤u§@ªí¸ò³Ì«á¤u§@ªí)
Åã¥Ü"¼Æ¦r"¦A²Ä1­Ó¤u§@ªí


°ÝÃD2¬O²Ä1¤u§@ªí²Î­p¶q¤¤
Åã¥Ü¨ä«á­±¤u§@ªí¤¤(¤£³ø¬A³Ì«á¤u§@ªí)
¥Í²£ªí³æ
ºû­×ªí³æ
²§°Ê¬ö¿ýªí³æ

¦@¦³¦h¤Öµ§¸ê®Æ
»Ý­n¸ó¤u§@ªí´M§ä
B9~¥H¤U
J9~¥H¤U
P9~¥H¤U
¸ê®Æ¼Æ
¦]¬°§ÚÁÙ¨S¶ñ¤J©Ò¥H³£¥ý¥´0


½d³ò¦pªG§Úµ¹°÷¤jÀ³¸Ó´N¥i¥H·í¦¨µL­­½d³òÅo§a!?

¤j¤j§Aµ¹§ÚªºVisual Basic§Ú¤£ª¾«ç»ò¨Ï¥Î­C@@
2003¥u­n«öalt+f11´N¥i¥H¶i¤JVisual Basic¤F
«ô°U¤j¤j¤F~~

stillfish00

¦^´_ 4# konkon3141
alt +F1¶i¤JVisual Basic , ¥ªÃä§ä¨ì¥Ø¿ý³o­Ó¤u§@ªíÂI¨â¤U

¤U¸ü (82.43 KB)

2012-10-28 11:21

¦b¥kÃä°Ï°ì¶K¤W³o¬qcode:

1. Private Sub Worksheet_Activate()
   
2.     'Q1:
   
3.     Range("C9") = Sheets.Count - 2
   
4.    
   
5.     'Q2:-
   
6.     Range("C10") = "=COUNTA(" & Sheets(2).Name & ":" & Sheets(Sheets.Count - 1).Name & "!B:B)" & "-COUNTA(" & Sheets(2).Name & ":" & Sheets(Sheets.Count - 1).Name & "!B1:B8)"
   
7.     Range("C12") = "=COUNTA(" & Sheets(2).Name & ":" & Sheets(Sheets.Count - 1).Name & "!J:J)" & "-COUNTA(" & Sheets(2).Name & ":" & Sheets(Sheets.Count - 1).Name & "!J1:J8)"
   
8.     Range("C14") = "=COUNTA(" & Sheets(2).Name & ":" & Sheets(Sheets.Count - 1).Name & "!P:P)" & "-COUNTA(" & Sheets(2).Name & ":" & Sheets(Sheets.Count - 1).Name & "!P1:P8)"
   
9. 
   
10. End Sub

½Æ»s¥N½X

¦h¸Õ¸Õ ¬Ý¯à¤£¯à²Å¦X§Aªº»Ý¨DÂP
Book1.zip (1.68 KB)

¤U¸ü¦¸¼Æ: 7

2012-10-28 11:17

konkon3141

¦^´_  konkon3141
alt +F1¶i¤JVisual Basic , ¥ªÃä§ä¨ì¥Ø¿ý³o­Ó¤u§@ªíÂI¨â¤U


¦b¥kÃä°Ï°ì¶K¤W³o¬qcod ...
stillfish00 µoªí©ó 2012-10-28 11:21

¤j¤j§A¦n
§Úªº¸ê®Æ¬O­nC10,C11,C12
©Ò¥H§Ú§âµ{¦¡¦ì¸m§ï¦¨C11,C12
¥L¥X²{¥H¤U¿ù»~
§Ú¥¨¶°¦³¶}§C

¤U¸ü (28.34 KB)

2012-10-28 12:02

¤U¸ü (83.06 KB)

2012-10-28 12:02

¥t¥~¤j¤j§Ú·s¼W¤u§@ªí«á
¤u¨ãªíC9ªº¤u¨ãªíÁ`¼Æ¨Ã¤£·|§ó·s­C

stillfish00

¦^´_ 6# konkon3141
®¦..§Ú¤£¾å±o¬O¤£¬Oª©¥»¤£¦P³y¦¨ªº, §Ú¥u¦³2010°õ¦æ³£¥¿±` , §A¸Õ¸Õ
1.¨Ï¥Î«e­±ªºªþ¥ó®É¯à¥¿±`°õ¦æ¶Ü?
2.Àˬd¥Ø¿ý¤u§@ªí¬O§_¦³«OÅ@?
3.µ{¦¡Range«e«ü©wSheets("¥Ø¿ý") , ¬Ý¦³¨S¦³®t²§?

1. Private Sub Worksheet_Activate()
   
2.     'Q1:
   
3.     Sheets("¥Ø¿ý").Range("C9") = Sheets.Count - 2
   
4.    
   
5.     'Q2:
   
6.     Sheets("¥Ø¿ý").Range("C10") = "=COUNTA(" & Sheets(2).Name & ":" & Sheets(Sheets.Count - 1).Name & "!B:B)" & "-COUNTA(" & Sheets(2).Name & ":" & Sheets(Sheets.Count - 1).Name & "!B1:B8)"
   
7.     Sheets("¥Ø¿ý").Range("C11") = "=COUNTA(" & Sheets(2).Name & ":" & Sheets(Sheets.Count - 1).Name & "!J:J)" & "-COUNTA(" & Sheets(2).Name & ":" & Sheets(Sheets.Count - 1).Name & "!J1:J8)"
   
8.     Sheets("¥Ø¿ý").Range("C12") = "=COUNTA(" & Sheets(2).Name & ":" & Sheets(Sheets.Count - 1).Name & "!P:P)" & "-COUNTA(" & Sheets(2).Name & ":" & Sheets(Sheets.Count - 1).Name & "!P1:P8)"
   
9. 
   
10. End Sub

½Æ»s¥N½X

konkon3141

¦^´_  konkon3141
®¦..§Ú¤£¾å±o¬O¤£¬Oª©¥»¤£¦P³y¦¨ªº, §Ú¥u¦³2010°õ¦æ³£¥¿±` , §A¸Õ¸Õ
1.¨Ï¥Î«e­±ªºªþ¥ó ...
stillfish00 µoªí©ó 2012-10-28 14:58

§Ú§â¥Ø¿ý§ï¦¨§Ú²Ä1­¶ªº¦WºÙ
ÁÙ¬OµLªk°õ¦æ­C
¨SÅv­­¤U¸üªþ¥ó..©Ò¥H¤£ª¾¹D½d¨Ò¬O§_2003¬O§_¥i¥H¶]
«ç»òÀˬd¥Ø¿ý¤u§@ªí¬O§_¦³«OÅ@©O!?

1. Private Sub Worksheet_Activate()
   
2.     'Q1:
   
3.     Sheets("¥Ø¿ý&¼Ò¨ã«~¦W").Range("C9") = Sheets.Count - 2
   
4.    
   
5.     'Q2:
   
6.     Sheets("¥Ø¿ý&¼Ò¨ã«~¦W").Range("C10") = "=COUNTA(" & Sheets(2).Name & ":" & Sheets(Sheets.Count - 1).Name & "!B:B)" & "-COUNTA(" & Sheets(2).Name & ":" & Sheets(Sheets.Count - 1).Name & "!B1:B8)"
   
7.     Sheets("¥Ø¿ý&¼Ò¨ã«~¦W").Range("C11") = "=COUNTA(" & Sheets(2).Name & ":" & Sheets(Sheets.Count - 1).Name & "!J:J)" & "-COUNTA(" & Sheets(2).Name & ":" & Sheets(Sheets.Count - 1).Name & "!J1:J8)"
   
8.     Sheets("¥Ø¿ý&¼Ò¨ã«~¦W").Range("C12") = "=COUNTA(" & Sheets(2).Name & ":" & Sheets(Sheets.Count - 1).Name & "!P:P)" & "-COUNTA(" & Sheets(2).Name & ":" & Sheets(Sheets.Count - 1).Name & "!P1:P8)"
   
9. 
   
10. End Sub

½Æ»s¥N½X

¥X²{¥H¤Uµe­±

¤U¸ü (122.08 KB)

2012-10-28 15:56

¤U¸ü (30.12 KB)

2012-10-28 15:56

¤U¸ü (175.18 KB)

2012-10-28 15:56

«ô°U¤j¤j¤Fq.qÁÂÁÂ^^~

konkon3141

§Ú­è¦A¸Õ¤@¤U¤u§@ªí·|ÀHµÛ·s¼W¦Ó§ïÅܼƶq¤F

¦ý¬OC10~C12ÁÙ¬OµLªkÅã¥Ü...
¤j¤j¯à¸ò§Úµy·L¸ÑÄÀ¤@¤Uµ{¦¡½X¤º®e¬O¬Æ»ò¶Ü!!?
§Ú¬Ý¬Ý¬O¤£¬O¤u§@ªí¥X°ÝÃD~~
«ô°U¤j¤j¤F

stillfish00

¦^´_ 9# konkon3141
¤º®e´N¬O¦bC10 ¶ñ¤J¤½¦¡
=COUNTA(¶}©l¤u§@ªí:µ²§ô¤u§@ªí!B:B)-COUNTA(¶}©l¤u§@ªí:µ²§ô¤u§@ªí!B1:B8)
§Y­pºâ  ©Ò¦³¶}©l¤u§@ªí~µ²§ô¤u§@ªí BÄæ «DªÅ¥Õ³æ¤¸®æÁ`¼Æ
    ´î¥h  ©Ò¦³¶}©l¤u§@ªí~µ²§ô¤u§@ªí B1¨ìB8 «DªÅ¥Õ³æ¤¸®æÁ`¼Æ

¦]¬°¤u§@ªí¥i¯à¼W¥[ , ¤£¬O©T©w¦P¤@­Ó , ©Ò¥H¥ÎVBA ,
Sheets(Sheets.Count - 1).Name ¥h§ä¨ì³Ì«á¤@­Ó¤u§@ªí¦W¦r

C11, C12¨Ì¦¹Ãþ±À , §Ú¤]¤£©ú¥Õ­þ¸Ì¦³°ÝÃD