[µo°Ý] ¦p¦ó¦b²{¦³ªºµ{¦¡½X¥[¤Jcountif

j88141

½Ð°Ý¦pªG·Q­n¦b¤U¦Cªºµ{¦¡½X¥[¤J
Range("B8")=COUNTIF(G6,"*")+COUNTIF(G8,"*")+COUNTIF(G10,"*")+COUNTIF(G12,"*")
Range("B10")=COUNTIF(E6,"*")+COUNTIF(E8,"*")+COUNTIF(E10,"*")+COUNTIF(E12,"*")
ªº¸Ü¸Ó«ç»ò§ï¼g
¦Û¤v¸Õ¹L¤@¨Ç
¥i¬O¦n¹³¥u­n¦bÀx¦s®æ¤º¥[¤J¨ç¼Æªº¸Ü
­ì¥»ªºµ{¦¡½X´N¤£·|°õ¦æ
ÀÉ®×1.rar (13.65 KB)

¤U¸ü¦¸¼Æ: 5

2014-3-17 17:28

1. Private Sub Worksheet_Change(ByVal myRange As Range)
   
2. If Intersect(myRange, [B1:B10]) Is Nothing Then Exit Sub 'ÅÜ°ÊÀx¦s®æ¤£¦bB1:B10«h°h¥Xµ{§Ç
   
3.    Range("A1") = Application.Sum([B1:B10])     'Àx¦s®æA1 µ¥©ó  SUM(B1:B10)
   
4. 
   
5.        If Range("A1") >= Range("E1") And Range("A1") <= Range("G1") Then
   
6.        '¦pªG Àx¦s®æA1 ¤j©óµ¥©ó Àx¦s®æE1 ©M ¤p©óÀx¦s®æG1ªº¸Ü
   
7. 
   
8.             MsgBox Range("D1") & ©M& & Range("F1"), vbOKOnly
   
9.        'Åã¥Ü.....
   
10. 
    
11.        ElseIf Range("A1") < Range("E1") Then
    
12.        '¦pªGÀx¦s®æA1¤p©óÀx¦s®æE1
    
13. 
    
14.             Range("A1").Interior.ColorIndex = 3
    
15.             'Àx¦s®æÅã¥Ü¬õ¦â
    
16.   
    
17.        ElseIf Range("A1") > Range("F1") Then
    
18.        '¦pªGÀx¦s®æA1¤j©óÀx¦s®æF1
    
19. 
    
20.             MsgBox Range("E1") & "©M&" & Range("G1"), vbCritical
    
21.        'Åã¥Ü.....
    
22.       
    
23.       End If
    
24. End Sub

½Æ»s¥N½X

Hsieh

¦^´_ 1# j88141


    =COUNTIF(G6,"*")+COUNTIF(G8,"*")+COUNTIF(G10,"*")+COUNTIF(G12,"*")
§Aª¾¹D³o¼Ë¤½¦¡ºâ¥X¬Æ»ò¶Ü?

j88141

¦^´_ 2# Hsieh


  Range("B8") =­pºâÀx¦s®æG6¡BG8¡BG10¡BG12 ¤¤§t¦³¤å¦rªºÀx¦s®æ¼Æ¥Ø

¦]¬°¤£ª¾¹DÁÙ¦³­þ¨Ç¨ç¼Æ¥i¥H¨Ï¥Î
©Ò¥H¥u¦n§âÀx¦s®æ¤@­Ó¤@­Ó³o¼Ë¥[Á`°_¨Ó

Hsieh

¦^´_ 3# j88141
BÄæ¦pªG¬O­n¥Î¤½¦¡¨ú±o¼Æ­È
¨º»ò°w¹ïBÄ檺Åܰʤ£¬OIJµoWorksheet_Changeµ{§Ç
¦Ó¬OIJµoWorksheet_Calculateµ{§Ç
¦pªG¥H¥Ø«e­nÅX°ÊªºWorksheet_Change¨Æ¥ó­ì¥»¬OBÄæ
·í§ABÄæÅܦ¨¤½¦¡¡A³o¤½¦¡°Ñ·ÓªºÀx¦s®æ¤~¬OÅX°Ê¨Æ¥óªºÀx¦s®æ
¤Ï¦Ó¬OG6,G8,G10,G12,E6,E8,E10,E12¤~¬OÅX°Ê¨Æ¥óªº¥²­nÀx¦s®æ
´N±N­ì¨ÓIf Intersect(myRange, [B1:B10]) Is Nothing Then Exit Sub
§ï¦¨
If Intersect(myRange, [G6,G8,G10,G12,E6,E8,E10,E12]) Is Nothing Then Exit Sub
¶¶«K¤@´£B8¤½¦¡
=COUNTA(G6,G8,G10,G12)
§Y¥i­pºâ½d³ò¤º«DªÅ¥ÕÀx¦s®æ¼Æ¶q

j88141

¦^´_ 4# Hsieh


    ¥i¥H¤F
§Ú¤S¾Ç¨ì¤@½Ò¤F
ÁÂÁÂ

j88141

¦^´_ 4# Hsieh
    hsieh¤j
¤£¦n·N«ä¤S¨Óµo°Ý

§Ú²{¦b·Q§âB8¤½¦¡§ï¦¨      (¸ó¤u§@ªí­pºâ)
=COUNTA(¤u§@ªí2!A1,¤u§@ªí2!A3,¤u§@ªí2!A5,¤u§@ªí2!A7)
©Ò¥H§Ú´N§â­ì¥»ªº
If Intersect(myRange, [G6,G8,G10,G12]) Is Nothing Then Exit Sub
§ï¦¨
If Intersect(myRange, [¤u§@ªí2!A1,¤u§@ªí2!A3,¤u§@ªí2!A5,¤u§@ªí2!A7]) Is Nothing Then Exit Sub

¦ý¦n¹³¤£¯à³o¼Ë¤l°µ
¬O¤£¬O­n§ï­þÃ䪺¥N½X

Hsieh

¦^´_ 6# j88141

Worksheet_Change¬O°w¹ï¸Ó¤u§@ªíÅÜ°Ê¡A¸ó¤u§@ªí´N¥²¶·±Nµ{¦¡½X©ñ¦bÅܰʪº¤u§@ªí¼Ò²Õ
¦ý¦p¦¹¤@¨Ó·í§A¦b¹Lµ{¤¤­n­pºâ¨ä¥L¤u§@ªíÀx¦s®æ®É¡A´N­n«ü©ú¤u§@ªí¡C

j88141

¦^´_ 7# Hsieh

¯à§_½ÐHsieh¤j
°µ¥X¨ÓÅý§Ú¬Ý
¦]¬°¥ÎÁ¿ªº¤£¤ÓÀ´
ª½±µ°µ¥X¨Ó¥i¯à§Ú·|¤ñ¸ûÀ´
²{¦b§ÚÁÙ¥d¦b«Üªì¯Å¶¥¬q
ÁÂÁÂHsieh¤jÀ°¦£

    ÀÉ®×1.rar (14.08 KB)

¤U¸ü¦¸¼Æ: 3

2014-3-20 18:56

Hsieh

¦^´_ 8# j88141

¤U¸ü (44.06 KB)

2014-3-20 23:48

ÀÉ®×1.rar (15.23 KB)

¤U¸ü¦¸¼Æ: 3

2014-3-20 23:48

j88141

²×©ó¦¨¥\¤F
ÁÂÁ¤j®v