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

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

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

½Ð°Ý
°ÝÃ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«ç»ò¨Ï¥Î~~

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

TOP

¥»©«³Ì«á¥Ñ stillfish00 ©ó 2012-10-28 01:21 ½s¿è

¦^´_ 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

TOP

¥»©«³Ì«á¥Ñ konkon3141 ©ó 2012-10-28 09:06 ½s¿è
¦^´_  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)
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~~

TOP

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


¦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. End Sub
½Æ»s¥N½X
¦h¸Õ¸Õ ¬Ý¯à¤£¯à²Å¦X§Aªº»Ý¨DÂP
Book1.zip (1.68 KB)

TOP

¥»©«³Ì«á¥Ñ konkon3141 ©ó 2012-10-28 12:15 ½s¿è
¦^´_  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




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

TOP

¥»©«³Ì«á¥Ñ stillfish00 ©ó 2012-10-28 14:59 ½s¿è

¦^´_ 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. End Sub
½Æ»s¥N½X

TOP

¥»©«³Ì«á¥Ñ konkon3141 ©ó 2012-10-28 15:58 ½s¿è
¦^´_  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. End Sub
½Æ»s¥N½X
¥X²{¥H¤Uµe­±




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

TOP

§Ú­è¦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

TOP

¥»©«³Ì«á¥Ñ stillfish00 ©ó 2012-10-28 19:16 ½s¿è

¦^´_ 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

TOP

        ÀR«ä¦Û¦b : ¥@¤W¦³¨â¥ó¨Æ¤£¯àµ¥¡G¤@¡B§µ¶¶ ¤G¡B¦æµ½¡C
ªð¦^¦Cªí ¤W¤@¥DÃD