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

[µo°Ý] ¥ÎVBA °õ¦æCOUNTIF «á¡A¶K­È¤Î¤u§@­¶ªººÃ°Ý¤Î²¤ÆVBA

[µo°Ý] ¥ÎVBA °õ¦æCOUNTIF «á¡A¶K­È¤Î¤u§@­¶ªººÃ°Ý¤Î²¤ÆVBA

¦³½Ð°Ýª©¤Wªº°ª¤â¡C
COUNTIF -TEST.zip (41.72 KB)
§Ú¥Î¿ý»s¥¨¶°ªº¤è¦¡·Q²Î­p¥X ª÷¿Ä/¹q¤l/¶Ç²£ªº¤º¥~½L¨Ã°µ¦¨¶ê»æ¹Ï·Q­n¤è«KÆ[¹î¡C
¦ý¬O¹J¨ì´X­Ó°ÝÃD¡C
1-¥Øªº·Q­n¨C10¬íÄÁ°õ¦æ¥¨¶°«á¹Bºâ¤@¦¸µM«áµe¶ê»æ¹Ï ¡A ¦ý¥¨¶°¤@°õ¦æ¡A¤u§@ªí1ªºABC¦æªºRAND ­È³£¤£·|¸õ°Ê¤F(¥¨¶°¨S°õ¦æ®É·|¦Û¤w¸õ°Ê)  ¡C§Ú¤]¨S¿ëªk¤Á¨ì¨ä¥¦¤u§@ªí°µ¨ä¥¦¨Æ
     ¦³¿ëªkÅý¹Bºâ¥u¦b¤u§@­¶1 ¦Û¤w­I´º°õ¦æ §ÚÁÙ¥i¥H°µ¨ä¥¦Àx¦s®æªºKEY IN¶Ü¡H
2-ªþ¥ó¬O·Q­n§Q¥ÎVBA¦b$E$1 °µ§¹COUNTIF ¹Bºâ«á ¦A¶K"­È¨ì¤U¤è ¡C ·Q»¡³o¼Ë¥i¥H¸`¬ÙEXCEL ¦bª÷¿Ä/¶Ç²£/¹q¤l/¤º¥~½L12®æ¤º¼g¤@¼Ëªº¤½¦¡®ö¶OEXCEL¹Bºâ®É¶¡¡C
     ¥i¬O¿ý»s¥X¨ÓªºVBA ·Pı¤]¬O¶K¤F12¦¸¤@¼Ëªº¹Bºâ ¡H  ¥i¥H¦³°ª¤âÀ°¦£Â²¤Æ¶Ü¡H
3- ¦]¬°·Q­n¦bEXCEL¤@¥´¶}«á ´N¤£ºÞ³o­ÓCONUTIF Åý¥¦¦Û¤w¦b¤u§@­¶1¦Û¤w­I´º°õ¦æ ¡C EXCEL ÁY¤p©Î¤Á´«¨ì¨ä¥¦ªº  EXCEL Àɮר䣼vÅT³o­ÓCOUNTIF ¤u§@­¶ªº°õ¦æ
      ³o»yªkÁٻݰµ¤°»ò³]©w¶Ü¡H ÁÙ¬O§Ú»Ý­n§âVBAµ{¦¡¶K¦b¤u§@ªí1¤º¡AµM«á¤u§@ªí1³]©w¦¨worksheet   «Å§i¦¨calculation ©O¡H
4- ¤@ª½·d¤£À´¥¨¶°°õ¦æ«á ­n«ç»ò§â¥¨¶°°±¤î¡C¬O§_¦³»yªk¥i¥H§Ö³t¤¤Â_vba¡H
½Ð°ª¤âÀ°À°¦£¸Ñµª¡C·PÁÂ



Sub ¥Æض¢X10sec()
'
'

'
'¤º¥~½L
    Range("E1").FormulaR1C1 = "=COUNTIF(R[4]C[-4]:R[935]C[-4],1)"
    Range("E1").Copy
    Range("E5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
            
    Range("E1").FormulaR1C1 = "=COUNTIF(R[4]C[-4]:R[935]C[-4],-1)"
    Range("E1").Copy
    Range("F5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Range("E1").FormulaR1C1 = "=COUNTIF(R[4]C[-4]:R[935]C[-4],0)"
    Range("E1").Copy
    Range("G5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
      
        
'¶Ç²£
    Range("E1").FormulaR1C1 = "=COUNTIF(R[4]C[-3]:R[257]C[-3],1)"
    Range("E1").Copy
    Range("E7").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
            
    Range("E1").FormulaR1C1 = "=COUNTIF(R[4]C[-3]:R[257]C[-3],-1)"
    Range("E1").Copy
    Range("F7").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Range("E1").FormulaR1C1 = "=COUNTIF(R[4]C[-3]:R[257]C[-3],0)"
    Range("E1").Copy
    Range("G7").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
              
'ª¡Ò¿Ä
    Range("E1").FormulaR1C1 = "=COUNTIF(R[461]C[-3]:R[492]C[-3],1)"
    Range("E1").Copy
    Range("E9").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
            
    Range("E1").FormulaR1C1 = "=COUNTIF(R[461]C[-3]:R[492]C[-3],-1)"
    Range("E1").Copy
    Range("F9").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Range("E1").FormulaR1C1 = "=COUNTIF(R[461]C[-3]:R[492]C[-3],0)"
    Range("E1").Copy
    Range("G9").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
'¹q¤l
    Range("E1").FormulaR1C1 = "=COUNTIF(R[258]C[-3]:R[397]C[-3],1)+COUNTIF(R[508]C[-3]:R[562]C[-3],1)"
    Range("E1").Copy
    Range("E11").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
            
    Range("E1").FormulaR1C1 = "=COUNTIF(R[258]C[-3]:R[397]C[-3],1)+COUNTIF(R[508]C[-3]:R[562]C[-3],-1)"
    Range("E1").Copy
    Range("F11").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Range("E1").FormulaR1C1 = "=COUNTIF(R[258]C[-3]:R[397]C[-3],1)+COUNTIF(R[508]C[-3]:R[562]C[-3],0)"
    Range("E1").Copy
    Range("G11").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
        Call Second

End Sub


Sub Second()
'
'
Application.OnTime Now + 10 / 86400#, "¥Æض¢X10sec"

End Sub

COUNTIF -TEST1.zip (41.72 KB)

¦^´_ 1# ppppssss
¤u§@ªí1ªºABC¦æªºRAND ­È³£¤£·|¸õ°Ê¤F(¥¨¶°¨S°õ¦æ®É·|¦Û¤w¸õ°Ê)  

RAND ­È³£¤£·|¸õ°Ê   Àx¦s®æªº¨ç¼Æ»Ý¤u§@ªí¦³§@­«ºâªº°Ê§@,¤~·|­«°µ­pºâ²£¥Í·sªº¼Æ­È
¥¨¶°¨S°õ¦æ®É·|¦Û¤w¸õ°Ê  ¬O¤u§@ªí¤¤Àx¦s®æ¦³½s¿èªº°Ê§@,¤Þ°_­«ºâªº°Ê§@

ªþÀÉ¥i°Ñ¦Ò¬Ý¬Ý


COUNTIF -TEST1 (¦Û°ÊÀx¦s).zip (50.26 KB)
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 2# GBKEE



«z¶ë¡A GBKEE ¤j¤j¡A¶W¥G§Úªº·Q¹³¡C   «Ü·PÁ±zªº½ç±Ð¡C  §¹¥þ¨S·Q¨ìµ{¦¡¥i¥H¦p¦¹ªºÂ²¼ä©ú¥Õ¡C
¥i¥H¦A½Ð±Ð¤@¬q      Run ("'""" & thisworkbool.Name & "!" & µ{¦¡.¥¨¶°10sec & """'")       ³o­Óthisworkbool   ¸òthisworkboo"k" ·|¦³¤£¤@¼Ë°µ¥Î¶Ü¡H
¸ÕµÛ­×§ï¦¨thisworkbook ·Pıµ{¦¡¤]¨S¤°»ò¼vÅT¡C

³o²©úªºµ{¦¡ ¤S­n¦n¦nªº¬ã¨s¤@¤U¤F ¡C ÁÂÁ¤j¤j¡C

TOP

¦^´_ 3# ppppssss
³o­Óthisworkbool   ¸òthisworkboo"k" ·|¦³¤£¤@¼Ë°µ¥Î¶Ü¡H
¸ÕµÛ­×§ï¦¨thisworkbook ·Pıµ{¦¡¤]¨S¤°»ò¼vÅT¡C
thisworkbool VBA¨S³oÃöÁä¦r
ThisWorkbook ¬O³o¬¡­¶Ã¯¼Ò²Õªºª«¥ó
   
·Pıµ{¦¡¤]¨S¤°»ò¼vÅT¡C  ªþ¤W§AªºÀɮ׬ݬÝ
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_  ppppssss
³o­Óthisworkbool   ¸òthisworkboo"k" ·|¦³¤£¤@¼Ë°µ¥Î¶Ü¡H
¸ÕµÛ­×§ï¦¨thisworkbook ·Pı ...
GBKEE µoªí©ó 2019-5-27 09:06


COUNTIF -TEST1 (¦Û°ÊÀx¦s).zip (50.26 KB)

hi GBKEE ¤j ¡C
     §Ú¬O¤U¸ü§A´£¨ÑªºÀÉ®×·Q­n°Ñ·Óªº¾Ç²ß ¨Ã­×§ï ¡C ¬Ý¨ì¤@¨Ç¤£À´ªº¦a¤è¨Ã½Ð±Ð ¡C
     1-  ¬O¦b thisworkbook ªºµ{¦¡½X¤¤  :Run ("'""" & thisworkbool.Name & "!" & µ{¦¡.¥Æض¢X10sec & """'")
     2- ¬O¦b"ªí³æ"ªºuserform1¤¤ ¦³«Å§ioption Explicit  ¦ý"µ{¦¡.msg"   ¡A¦pªG¨Ï¥Î¥¨¶°¥h°õ¦æauto_open ¡A·|¦b"µ{¦¡"³o¨â­Ó¦r¥X²{¶À¼Ð ; µM«á¸õ¥Xµøµ¡»¡ Åܼƥ¼©w¸q ¡C§Ú¬O¶K¨ì§Ú¥t¤@­Ó¾É¤JRTDªºEXCEL¤¤ ¥X²{³o¼Ëªºª¬ªp¡C

ÁÂÁ¡C
123.JPG
2019-5-27 16:15

TOP

¦^´_ 5# ppppssss
§Aªºoffice ¬OÁcÅ骩ªº¶Ü!
§Úµ¹ªº¬O Run "'" & ThisWorkbook.Name & "'!­«ºâ"
¤£¬O Run ("'""" & thisworkbool.Name & "!" & µ{¦¡.¥Æض¢X10sec & """'")
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_  ppppssss
§Aªºoffice ¬OÁcÅ骩ªº¶Ü!
§Úµ¹ªº¬O Run "'" & ThisWorkbook.Name & "'!­«ºâ"
¤£¬O Ru ...
GBKEE µoªí©ó 2019-5-28 05:44


§Úªºoffice ¬OÁcÅ骩ªº ¡C
ÁÂÁÂGBKEE¤jªá®É¶¡À°¦£§Ë¤F³o­Óµ{¦¡¡C ¥i¬O³o¨â¤Ñ§âCODE COPY ¶i§Úªº¥Dµ{¦¡«á ¡A µo²{­ì¥ý·Q­nªº¥Î·N¦n¹³Â÷ÃD¤F ¡C
¦]¬°  EXCEL ªºÀx¦s®æ¤º ¦pªG¼g¤U¤½¦¡  =COUNTIF(A5:A936¡A1) ¤]·|¦Û¤wºâ ¦]¬°1;-1;0 ·|¦Û¤w¹BºâÅÜ¤Æ ¡C
­ì¥ýªº¥Î·N ·Q»¡¦bE5ªº®æ¤l ¥ÎVB ªºµ{¦¡¨Ó¹BºâÀ³¸Ó¬O·|¤ñEXCEL ªºÀx¦s®æ¨ç¼Æ¹Bºâ¨Óªº§Ö§a  
©Ò¥H·Q¦bE5®æ ºâ§¹«á  ¶K"­È"  ¨ì¨ä¥¦Àx¦s®æ¤ºµe¹Ï ³o¼Ë·|¤ñ  ¤U¤è¤º¥~½L/ª÷¿Ä/¶Ç²£ .....µ¥  12®æ³£¦³¨ç¼Æ¤½¦¡ÁÙ­n­pºâªº§ó§Ö¤W¼Æ­¿ ¡C

¤£ª¾³o¼Ëªº·Qªk¥¿½T¶Ü¡H

TOP

¦^´_ 7# ppppssss
  1. Sub ¥¨¶°10sec()   
  2.    Application.Calculation = xlCalculationSemiautomatic  '¤â°Ê­«ºâ   ¥i°±¤î¬¡­¶Ã¯ªº¹Bºâ
  3.     With ¤u§@ªí1
  4.         .Range("E5:G5") = Array("=COUNTIF(A5:A936,1)", "=COUNTIF(A5:A936,-1)", "=COUNTIF(A5:A936,0)")
  5.         .Range("E7:G7") = Array("=COUNTIF(B5:B258,1)", "=COUNTIF(B5:B258,-1)", "=COUNTIF(B5:B258,0)")
  6.         .Range("E9:G9") = Array("=COUNTIF(B462:B493,1)", "=COUNTIF(B462:B493,-1)", "=COUNTIF(B462:B493,0)")
  7.         .Range("E11:G11") = Array("=COUNTIF(B259:B398,1)+COUNTIF(B509:B563,1)", "=COUNTIF(B259:B398,1)+COUNTIF(B509:B563,-1)", "=COUNTIF(B259:B398,1)+COUNTIF(B509:B563,0)")
  8.     End With
  9.     Application.Calculation = xlCalculationAutomatic   µ{¦¡½X°õ¦æ«á «ì´_¬¡­¶Ã¯ ¦Û°Ê­«ºâ
  10. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_  ppppssss
GBKEE µoªí©ó 2019-5-29 13:54



    ÁÂÁÂGBKEE¤j¤jªºÀ°¦£   ³Ìªñ¤½¥q¨Æ±¡¤ñ¸û¦h  §Ú¦³®É¶¡¦A¸Õ¸Õ§i¶D§Aµ²ªG¡C

TOP

        ÀR«ä¦Û¦b : ¡i¬°µ½Ävª§¡j¤H¥Í­n¬°µ½Ävª§¡A¤À¬í¥²ª§¡C
ªð¦^¦Cªí ¤W¤@¥DÃD