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

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

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

¥»©«³Ì«á¥Ñ j88141 ©ó 2014-3-17 17:28 ½s¿è

½Ð°Ý¦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)
  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.        If Range("A1") >= Range("E1") And Range("A1") <= Range("G1") Then
  5.        '¦pªG Àx¦s®æA1 ¤j©óµ¥©ó Àx¦s®æE1 ©M ¤p©óÀx¦s®æG1ªº¸Ü

  6.             MsgBox Range("D1") & ©M& & Range("F1"), vbOKOnly
  7.        'Åã¥Ü.....

  8.        ElseIf Range("A1") < Range("E1") Then
  9.        '¦pªGÀx¦s®æA1¤p©óÀx¦s®æE1

  10.             Range("A1").Interior.ColorIndex = 3
  11.             'Àx¦s®æÅã¥Ü¬õ¦â
  12.   
  13.        ElseIf Range("A1") > Range("F1") Then
  14.        '¦pªGÀx¦s®æA1¤j©óÀx¦s®æF1

  15.             MsgBox Range("E1") & "©M&" & Range("G1"), vbCritical
  16.        'Åã¥Ü.....
  17.       
  18.       End If
  19. End Sub
½Æ»s¥N½X

¦^´_ 1# j88141


    =COUNTIF(G6,"*")+COUNTIF(G8,"*")+COUNTIF(G10,"*")+COUNTIF(G12,"*")
§Aª¾¹D³o¼Ë¤½¦¡ºâ¥X¬Æ»ò¶Ü?
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 2# Hsieh


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

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

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2014-3-18 09:36 ½s¿è

¦^´_ 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
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 4# Hsieh


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

TOP

¥»©«³Ì«á¥Ñ j88141 ©ó 2014-3-19 19:48 ½s¿è

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

TOP

¦^´_ 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
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 7# Hsieh

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

    ÀÉ®×1.rar (14.08 KB)

TOP

¦^´_ 8# j88141


ÀÉ®×1.rar (15.23 KB)
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

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

TOP

        ÀR«ä¦Û¦b : ¦³Ä@©ñ¦b¤ß¸Ì¡A¨S¦³¨­Åé¤O¦æ¡A¥¿¦p¯Ñ¥Ð¤£¼½ºØ¡A¬Ò¬OªÅ¹L¦]½t¡C
ªð¦^¦Cªí ¤W¤@¥DÃD