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

FormatConditions«ç»ò¨ú±o¤å¦rÃC¦âªº­È

FormatConditions«ç»ò¨ú±o¤å¦rÃC¦âªº­È

¦U¦ì¤j¤j¡A
§Ú·Q­n­pºâ¤U¹Ï(¹Ï1)¤¤¬õ¦â³¡¤ÀªºÁ`©M¡A
§Ú¬O¦b³o­Óªí®æ¸Ì¨Ï¥Î¤FÀx¦s®æ±ø¥ó¡A
¦ý°ÝÃD¬O§Ú¦bVBA¸ÌµLªk¤À¿ì¦bÀx¦s®æ¸Ì¬õ¦â»P¶Â¦â®t¡A
¦p§Ú¹Ï2¡A§Ú¥Î¤F«Ü¦h»yªk³£¬O±o¨ì¬Û¦Pªº­È¡A§¹¥þµLªk§PÂ_¡A
¬O§_¦³¤Hª¾¹D«ç»ò§PÂ_®t²§¶Ü??

¹Ï1


¹Ï2

¦^´_ 1# doom834
¤W¶ÇÀÉ®× ¬Ý¤@¤U FormatConditionsªº¤½¦¡¦p¦ó³]©w
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

§Ú§R°£¤F¤@¨Ç¸ê®Æ¡A¤£µMÀɮפӤj¡A­Y¦³»Ý­n§¹¾ãª©ªºÀɮקڦA­«¶Ç!!

¥Í?±±¨îªí-1.zip (107.79 KB)

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2013-10-22 17:51 ½s¿è

¦^´_ 3# doom834
¸Õ¸Õ¬Ý
  1. Option Explicit
  2. Sub Ex()
  3.     Dim Rng As Range, AR(), C As Integer, R As Integer
  4.     For Each Rng In Sheets("图ªí").[b4:I11,b15:I22,b26:I33].Areas
  5.         ReDim AR(1 To Rng.Columns.Count, 1 To Rng.Rows.Count)
  6.         For R = 1 To Rng.Rows.Count
  7.             For C = 1 To Rng.Columns.Count
  8.                 AR(R, C) = 0
  9.                 If Rng.Cells(R, C).FormatConditions.Count > 0 Then
  10.                     Rng.Cells(R, C).Select
  11.                     If Application.Evaluate(Rng.Cells(R, C).FormatConditions(1).Formula1) Then AR(R, C) = Rng.Cells(R, C).Value
  12.                 End If
  13.             Next
  14.             Rng.Cells(R, C) = Application.Sum(Application.Index(AR, R))
  15.         Next
  16.         For C = 1 To Rng.Columns.Count
  17.             Rng.Cells(Rng.Rows.Count + 1, C) = Application.Sum(Application.Index(Application.Transpose(AR), C))
  18.         Next
  19.         Rng.Cells(Rng.Rows.Count + 1, Rng.Columns.Count + 1) = Application.Sum(Rng.Columns(Rng.Columns.Count + 1))
  20.     Next
  21. End Sub
½Æ»s¥N½X
®æ¦¡¤Æ±ø¥ó¤½¦¡©Ç©Çªº°Ñ·Ó¬O§_¶]±¼¤F
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

GB¤j¤j¡A§Ú¥Î§Aªº¥N½X¶]¤£¥X¨Óµ²ªG­C~~
­pºâ¥X¨ÓªºSum³£¬O0
¥t¥~®æ¦¡¤Æ±ø¥ó¨S¦³³]¿ù¡A§Ú³]©wªºÅÞ¿è¬O²Ä1­Óªí¬O>0ªºÅã¥Ü¬õ¦r¡A
²Ä2­Óªí¬O¨Ì¾Ú²Ä1­Óªí¦³¼Æ¦rªº¤~·|¥Ü¦¨¬õ¦r¡C

¥Í?±±¨îªí-1.zip (108.36 KB)

TOP

§Ú»{¬°¬OIf Application.Evaluate(Rng.Cells(R, C).FormatConditions(1).Formula1) Then AR(R, C) = Rng.Cells(R, C).Value³o¦æ¦³ÂI°ÝÃD¡A
¦]¬°³o­Ó³£¬Ofalse¡A©Ò¥H¦s¤J°}¦CAR¸Ìªº¼Æ­È³£¬O0¡A
³Ì«á¥[Á`°_¨Óªº³£¬O0

TOP

¦^´_ 5# doom834
§AªþÀɤ¤¨S¦³Sheets("ͼ±í")³o¤u§@ªí¶·­×¥¿
  1. For Each Rng In Sheets("ͼ±í").[b4:I11,b15:I22,b26:I33].Areas
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¨º¬O¦]¬°§Ú¦b¤j³°¥Î³o¸Ìªºµ§¹q¡A
¨Æ¹ê¤W¦b§Úªº¹q¸£¤W¬Ý¬O¹Ïªí³o­ÓSheet¨S¿ù¡A
³o­Ó¬O½sâóÂà´«¤W³y¦¨ªº¶Ã½X¡C

TOP

        ÀR«ä¦Û¦b : ¬°¦Û¤v§äÂǤfªº¤H¥Ã»·¤£·|¶i¨B¡C
ªð¦^¦Cªí ¤W¤@¥DÃD