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

½Ð°ÝÃö©óTRUE­Ó¼Æ§PÂ_ªº°ÝÃD¡H

½Ð°ÝÃö©óTRUE­Ó¼Æ§PÂ_ªº°ÝÃD¡H

¦U¦ì¤j¤j¦n¡A¥ý«e¦³½Ð±Ð¦U¦ì¥ý¶iÃö©ó§Q¥ÎVBA§PÂ_ªº°ÝÃD¡G§Úªþ¥óªºexcelÀɬO¤@­Ó·|¦Û°Ê·|¦bCÄæ§PÂ_BÄ檺«e¤T­Ó¦r¬O§_¬°¡§½Ð°Ñ·Ó¡¨©Î¡§¦pªþ¹Ï¡¨µ{¦¡¡A¨Ã·|¦bCÄ檺³Ì«á¥[Á`TRUEªº­Ó¼Æ¡A¦ý§Úµo²{¦bCÄ檺³Ì«á¦ü¥G¨S¦³¿ìªk¥[Á`TRUEªº­Ó¼Æ¡A¤£ª¾¬°¦ó¡H§Ú·íªì¥H¬°¬O¤½¦¡ªºÃö«Y¡A¬G±NCÄ檺TRUE©ÎFALSE­È¡A¥H½Æ»s¶K¤W­Èªº¤è¦¡¶K¤F¤@¦¸¡Aµo²{ÁÙ¬O¨S¥Î¡A½Ð±Ð¦U¦ì¤j¤j¬°¦ó¦³³o¼Ëªº°ÝÃD¡HÁÂÁ¡C

¨Ï¥Î¤èªk¡G¶}µo¤H­û¡÷¥¨¶°¡÷°õ¦æsheets.auto_count
³sµ²----------------------------
http://db.tt/Xyy7lr2R
-----------------------------------

¦^´_ 1# tku0216
¥i§ï¥ÎSUMPRODUCT
  1. Sub auto_count()
  2.     a = [a1].End(xlDown).Row
  3.     For i = 2 To a
  4.         Cells(i, "c").FormulaR1C1 = "=if(OR(left(rc[-1],3)=""¦pªþ¹Ï"",left(rc[-1],3)=""½Ð°Ñ·Ó""),""TRUE"",""FALSE"")"
  5.     Next
  6.     'Cells(a + 1, "c").FormulaR1C1 = "=countif(r[" & 1 - a & "]c:r[-1]c,TRUE)"
  7.     Cells(a + 1, "c").FormulaR1C1 = "=SUMPRODUCT((r[" & 1 - a & "]c:r[-1]c=""TRUE"")*1)"
  8.     Cells(a + 1, "d").FormulaR1C1 = "=countif(r[" & 1 - a & "]c:r[-1]c,""¿ï¶µA"")"
  9.     Cells(a + 1, "e").FormulaR1C1 = "=countif(r[" & 1 - a & "]c:r[-1]c,""¿ï¶µB"")"
  10.     Cells(a + 1, "f").FormulaR1C1 = "=countif(r[" & 1 - a & "]c:r[-1]c,""¿ï¶µC"")"
  11.     Cells(a + 1, "g").FormulaR1C1 = "=countif(r[" & 1 - a & "]c:r[-1]c,""¿ï¶µD"")"
  12.       For i = 2 To a
  13.         Cells(i, "i").FormulaR1C1 = "=if(isblank(rc[1]),"""",if(len(rc[1])>1,2,1))"
  14.       Next
  15. End Sub
½Æ»s¥N½X

TOP

½Ð°ÝG¤j¡A¤£¤Ó¤F¸Ñ±z­×§ï¨º¤@¦æ(²Ä7¦æ)«á¥b¬qªº·N«ä¡A¥i§_¸ÑÄÀ¤@¤U¡HÁÂÁ¡C

TOP

¦^´_ 3# tku0216
¤u§@ªí¨ç¼Æ  SUMPRODUCT ¶Ç¦^¦U°}¦C¤¤©Ò¦³¹ïÀ³¤¸¯À­¼¿nªºÁ`©M¡C
=SUMPRODUCT((C2:C11=TRUE)*1)
°}¦C: C2:C11=TRUE  ¦³TRUE  *1 -> ©Ò¦³¹ïÀ³¤¸¯À­¼¿nªºÁ`©M

TOP

        ÀR«ä¦Û¦b : ¤H¥Í¤£¤@©w²y²y¬O¦n²y¡A¦ý¬O¦³¾ú½mªº±j¥´ªÌ¡AÀH®É³£¥i¥H´§´Î¡C
ªð¦^¦Cªí ¤W¤@¥DÃD