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

[µo°Ý] ³sÄòNµ§¸ê®Æ§P§O

[µo°Ý] ³sÄòNµ§¸ê®Æ§P§O

§Ú·Q¼g¤@­Ó§PÂ_«~½èªºµ{¦¡
¤j·§¤º®e¬°±N¼Æ­È»P¤¤¤ß­ÈCL§PÂ_
¦bCÄæ
> CL =1
<CL =0
¥Î¦¹CÄæ§PÂ_
µM«á¦b
DÄæ¦ì¼Ðµù¬O§_³sÄò7µ§¸ê®Æ³£¬O>CL
EÄæ¦ì§PÂ_¬O§_³sÄò7µ§³£¬O³£¬O<CL

=========================µ{¦¡½X==================
Dim side%, QQ%, QQ1%, CL As Variant
k = Cells(Rows.Count, 1).End(xlUp).Row
CL = Range("C1").Value
Range("C3:E" & k).ClearContents


If CL <> "" And k - 1 > 0 Then
    For i = 3 To k
     
     If Range("A" & i) > CL Then
     Range("C" & i) = 1
     Else
     Range("C" & i) = 0
     End If
      
    Next
End If
'==§PÂ_¬O§_³sÄò7µ§¤j©ó©Î¤p©óCL========
For i = 3 To k
        If (Range("C" & i) = 1 And Range("C" & i + 1) = 1 And Range("C" & i + 2) = 1 And Range("C" & i + 3) = 1 And Range("C" & i + 4) = 1 And Range("C" & i + 5) = 1 And Range("C" & i + 6) = 1) Then
        Range("D" & i + 6) = 1
        
        ElseIf (Range("C" & i) = 0 And Range("C" & i + 1) = 0 And Range("C" & i + 2) = 0 And Range("C" & i + 3) = 0 And Range("C" & i + 4) = 0 And Range("C" & i + 5) = 0 And Range("C" & i + 6) = 0) Then
        Range("E" & i + 6) = 1
    End If
Next



QQ = Application.WorksheetFunction.Sum(Range("D3:D" & k))
QQ1 = Application.WorksheetFunction.Sum(Range("E3:E" & k))

If QQ >= 1 Then
MsgBox "³sÄò7ÂI¦b¤¤¤ß½u¦P°¼(¤j©ó)"
End If

If QQ1 >= 1 Then
MsgBox "³sÄò7ÂI¦b¤¤¤ß½u¦P°¼(¤p©ó)"
End If

================================================================
§Ú¦b·Q¬O§_¦³§ó¦nªº¼gªk¥Î¨Ó§PÂ_
³sÄò7µ§¸ê®Æ>CL©Î<CL

½Ð¨D«ü¾É
ÁÂÁ¦U¦ì¥ý¶i

Q test.zip (21.42 KB)

²öÅý¥L¤H¿ù»~¶Ë®`¦Û¤v

³oºØ¤ñ¸û²³æªº·§©À§Ú´N¥N³Ò¤@¤U:
µ{¦¡¶}ÀY´N©w¸q¤F T% ªí¥Ü¥¦¬O­Ó¾ã¼Æ,
¤@¶}©l¨S¯S§O¥h½á­È®É¹w³]­È¬O ...
luhpro µoªí©ó 2021-1-6 23:32



  Brr(R, 1) = IIf(Arr(R, 1) > CL, 1, -1)
  S = S + Brr(R, 1) : MsgBox S
'If S = N Then ³sÄò¤j = True: S = S - 1: Brr(R, 2) = 1  
'If S = -N Then ³sÄò¤p = True: S = S + 1: Brr(R, 3) = 1 '
  If Brr(R, 1) <> T Then T = Brr(R, 1) : MsgBox T: S = T  :MsgBox S

¬Q¤Ñ¥Î³oºØ¤è¦¡
²×©ó·dÀ´¤F³Ì«á¤@¦æªº·N¸q....

ÁÂÁ±zªº¨ó§U½s¼gµù¸Ñ
²öÅý¥L¤H¿ù»~¶Ë®`¦Û¤v

TOP

¥»©«³Ì«á¥Ñ luhpro ©ó 2021-1-6 23:57 ½s¿è
If Brr(R, 1)  T Then T = Brr(R, 1): S = T
If Brr(R, 1) <> T Then T = Brr(R, 1): S = T
³o¤@¦æ§ÚŪ¤£À´
´ú¸Õ¦pªG§â¥¦²¾°£¡A´N¤£·|¥X²{"³sÄò¤p" ªº¼Ð°O¸ò°T®§
Brr(R, 1) <> T ( T¬O±q¦ó¦Ó¨Ó?
y54161212 µoªí©ó 2021-1-6 14:43

³oºØ¤ñ¸û²³æªº·§©À§Ú´N¥N³Ò¤@¤U:
µ{¦¡¶}ÀY´N©w¸q¤F T% ªí¥Ü¥¦¬O­Ó¾ã¼Æ,
¤@¶}©l¨S¯S§O¥h½á­È®É¹w³]­È¬O0,
¤]´N¬O»¡²Ä1¦¸¬O¸ò0¤ñ¸û.

  Brr(R, 1) = IIf(Arr(R, 1) > CL, 1, -1) ' ¤ñ¸û«á«ü©w¥[´î¼Æ
  S = S + Brr(R, 1) ' ¤j©ó®É + 1, ¤p©ó®É - 1, ¥[¨ì +8 ©Î -8 ®É´N¬O³sÄò8¦¸ ¤j©ó ©Î ¤p©ó ¤F
  If S = N Then ³sÄò¤j = True: S = S - 1: Brr(R, 2) = 1  ' ³o¸Ì¬O "¤j©ó" °Ï S=8«á S=S-1 ´NÅܦ^ 7, ¤U¦¸¦A¥[1´N¤S=8 ¥i¦A¦¸Ä²µo¥»¦æªº±ø¥ó
  If S = -N Then ³sÄò¤p = True: S = S + 1: Brr(R, 3) = 1 ' "¤p©ó" °Ï, ­ì²z¦P¤W.
  If Brr(R, 1) <> T Then T = Brr(R, 1): S = T  ' ¨ä¹ê´N¬O«O¦s¥»¦¸ªº­È¥H³Æ¤U¤@¦¸¤ñ¸û¬O¤£¬O­È¤£¦P¤F¥Î.

³o¸Ì¶¶«K¤]´£¨Ñ¤@¤U§Ú·í®É­×§ï«áªº¸Ñªk, (¥H3#°µ¥Àª©­×§ï)
¹Á¸Õ§ï¦¨Åã¥Ü 0 ¦Ó¤£¬O -1 §@¹ê²{.
  1. Sub §PÂ_«~½è²§±`()
  2. Const N = 7   '³]©w³sÄò¦¸¼Æ
  3. Dim Rn&, R%, S%, T%, CL!
  4. Dim ³sÄò¤j As Boolean, ³sÄò¤p As Boolean
  5. Rn = Cells(Rows.Count, 1).End(xlUp).Row - 2
  6. If Rn < 1 Then Exit Sub
  7. CL = [C1]
  8. With [C3].Resize(Rn, 3)
  9.   Arr = .Offset(, -2).Resize(, 5).Value
  10.   .ClearContents
  11. End With
  12. For R = 1 To Rn
  13.   Arr(R, 3) = IIf(Arr(R, 1) > CL, 1, 0)
  14.   S = S + (Arr(R, 3) = 0) * 2 + 1
  15.   If S = N Then S = S - 1: ³sÄò¤j = True: Arr(R, 4) = 1
  16.   If S = -N Then S = S + 1: ³sÄò¤p = True: Arr(R, 5) = 1
  17.   If Arr(R, 3) <> T Then T = Arr(R, 3): S = T
  18. Next R
  19. [A3].Resize(Rn, 5) = Arr
  20. If ³sÄò¤j Then MsgBox "³sÄò7ÂI¦b¤¤¤ß½u¦P°¼(¤j©ó)"
  21. If ³sÄò¤p Then MsgBox "³sÄò7ÂI¦b¤¤¤ß½u¦P°¼(¤p©ó)"
  22. End Sub
½Æ»s¥N½X

TOP

¦^´_  ­ã´£³¡ªL

·PÁ·ǤjªÈ¥¿XD

¨ä¹ê§Ú¤wµo²{°ÝÃD¤F¡A¥u¬OÃiªº§ï~

·Q»¡Åª¸ê®Æ»P¼g¸ê®Æ³£¥Î¦P¤@­Ó ...
n7822123 µoªí©ó 2021-1-1 16:33



If Brr(R, 1) <> T Then T = Brr(R, 1): S = T
³o¤@¦æ§ÚŪ¤£À´
´ú¸Õ¦pªG§â¥¦²¾°£¡A´N¤£·|¥X²{"³sÄò¤p" ªº¼Ð°O¸ò°T®§
Brr(R, 1) <> T ( T¬O±q¦ó¦Ó¨Ó?
²öÅý¥L¤H¿ù»~¶Ë®`¦Û¤v

TOP

¨ä¹ê¦³¨Ç¼gªk§Ú¯uªº¬Ý¤£À´
¦ý§Ú³£¥Î´ú¸Õªº¤è¦¡¨Ó²z¸Ñ¦U¦ìªº»y¨¥
VBA¯u¬O¤@±ø¤£Âk¸ô (»~
²öÅý¥L¤H¿ù»~¶Ë®`¦Û¤v

TOP

¥»©«³Ì«á¥Ñ n7822123 ©ó 2021-1-1 16:44 ½s¿è

¦^´_ 9# ­ã´£³¡ªL

·PÁ·ǤjªÈ¥¿XD

¨ä¹ê§Ú¤wµo²{°ÝÃD¤F¡A¥u¬OÃiªº§ï~

·Q»¡Åª¸ê®Æ»P¼g¸ê®Æ³£¥Î¦P¤@­Ó°}¦C¸Ñ¨M´N¦n

¦ý¬O·|§â«e¤@¦¸ªº§PÂ_­È¤]¼g¤JArr (·|«O¯d«e¤@¦¸§PÂ_¹Lµ{)

¬JµM·Ç¤jªÈ¥¿¤F¡A¨º§ÚÁÙ¬O©î¦¨2­Ó°}¦C¦n¤F~

¥t¥~¡A¯¬ ·Ç¤j ·s¦~§Ö¼Ö ^.^


Sub §PÂ_«~½è²§±`()
Const N = 8   '³]©w³sÄò¦¸¼Æ
Dim Rn&, R%, S%, T%, CL!, Arr, Brr
Dim ³sÄò¤j As Boolean, ³sÄò¤p As Boolean
Rn = Cells(Rows.Count, 1).End(xlUp).Row - 2
If Rn < 1 Then Exit Sub
CL = [C1]
Arr = [A3].Resize(Rn)
[C3].Resize(Rn, 3).ClearContents
ReDim Brr(1 To Rn, 1 To 3)

For R = 1 To Rn
  Brr(R, 1) = IIf(Arr(R, 1) > CL, 1, -1)
  S = S + Brr(R, 1)
  If S = N Then ³sÄò¤j = True: S = S - 1: Brr(R, 2) = 1
  If S = -N Then ³sÄò¤p = True: S = S + 1: Brr(R, 3) = 1
  If Brr(R, 1) <> T Then T = Brr(R, 1): S = T
Next R
[C3].Resize(Rn, 3) = Brr
If ³sÄò¤j Then MsgBox "³sÄò" & N & "ÂI¦b¤¤¤ß½u¦P°¼(¤j©ó)"
If ³sÄò¤p Then MsgBox "³sÄò" & N & "ÂI¦b¤¤¤ß½u¦P°¼(¤p©ó)"
End Sub
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

¦^´_ 8# n7822123


With [A3].Resize(Rn, 5)
  Arr = .Value '¤­Äæ¸ê®Æ¦s¤J°}¦C
  .ClearContents '²M°£­ì¦³¸ê®Æ, ¦ý­ì¸ê®ÆÁÙ¦b°}¦C¤¤, ·í°}¦C¶K¦^®É, ÁÙ·|¦³´Ý¯d, ¥i­×§ïaÄæ°Ñ¼Æ¤£§ó°Ê¨ä¥¦¦C¸ê®Æ¸Õ¸Õ
End With

¥t¥~, ¾¨¶q¤£­n²MªÅ­ì¼Æ¾Ú, ÁקKµ{¦¡¦]¿ù»~¤¤Â_¦Ó¿ò¥¢¸ê®Æ,
¦Ó¥B, ¶K¦^®É, ¤]À³­­¨î¦bÅÜ°Ê°Ï, ¦ÓÁקK¥þ°Ï¶K¦^, ­Y¸ê®ÆÃe¤j«h¶·ªá¶O¦^¼g¶ñº¡ªº®É¶¡


===============================

TOP

¥»©«³Ì«á¥Ñ n7822123 ©ó 2021-1-1 14:20 ½s¿è

¦^´_ 4# y54161212

¤W­±¨â¦ì¯uªº¬O¤Ó¯«¤F
§Ú§¹¥þ¨S¿ìªk§l¦¬¡]¯º

§Úı±o§Ú¼gªº«Üª½Ä±ªü¡A´N§PÂ_¦¨1 , -1 ¦b¥[Á`¦Ó¤w

ikboy ªº¥¿³Wªí¥Üªk §Ú¤]¤£¬O«ÜÀ´XD

§A¤]¥i¥H°Ñ¦Ò·Ç¤jªº¡A¥L¬O¥Î¨â­ÓÅܼƤÀ§O¥[Á`¡@

N(1)¬ö¿ý¤j©óCL¦¸¼Æ
N(0)¬ö¿ý¤p©óCL¦¸¼Æ



j§Úªºµ{¦¡¡A¦pªG­nÂX®i¨ì8¦¸¡B9¦¸¡A§ïN­È§Y¥i

¨q¥Xªº°T®§¨S§ï¨ì¡A­×§ï¦p¤U


Sub §PÂ_«~½è²§±`()
Const N = 7   '³]©w³sÄò¦¸¼Æ
Dim Rn&, R%, S%, T%, CL!
Dim ³sÄò¤j As Boolean, ³sÄò¤p As Boolean
Rn = Cells(Rows.Count, 1).End(xlUp).Row - 2
If Rn < 1 Then Exit Sub
CL = [C1]
With [A3].Resize(Rn, 5)
  Arr = .Value
  .ClearContents
End With
For R = 1 To Rn
  Arr(R, 3) = IIf(Arr(R, 1) > CL, 1, -1)
  S = S + Arr(R, 3)
  If S = N Then ³sÄò¤j = True: S = S - 1: Arr(R, 4) = 1
  If S = -N Then ³sÄò¤p = True: S = S + 1: Arr(R, 5) = 1
  If Arr(R, 3) <> T Then T = Arr(R, 3): S = T
Next R
[A3].Resize(Rn, 5) = Arr
If ³sÄò¤j Then MsgBox "³sÄò" & N & "ÂI¦b¤¤¤ß½u¦P°¼(¤j©ó)"
If ³sÄò¤p Then MsgBox "³sÄò" & N & "ÂI¦b¤¤¤ß½u¦P°¼(¤p©ó)"
End Sub
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

D3//¥k©Ô/¤U©Ô
=IF(COUNTIF(OFFSET($A3,,,-MIN(ROW(A1),7)),IF(COLUMN(A1)=1,">","<=")&$C$1)>6,1,"")

TOP

¥»©«³Ì«á¥Ñ ­ã´£³¡ªL ©ó 2021-1-1 11:20 ½s¿è

Sub §PÂ_«~½è²§±`_A()
Dim Arr, Brr, R&, C%, N%(1), X%(1), i&, CL!
R = Cells(Rows.Count, 1).End(xlUp).Row - 2
If R < 1 Then Exit Sub
CL = [C1]
Arr = [A3].Resize(R)
ReDim Brr(1 To R, 1 To 4)
For i = 1 To R
    C = -(Arr(i, 1) > CL): Brr(i, 1) = C
    N(C) = N(C) + 1: N(1 - C) = 0
    If N(C) >= 7 Then Brr(i, 3 - C) = 1: X(C) = 1
Next i
[C3].Resize(R, 4) = Brr
If X(1) Then MsgBox "³sÄò7ÂI¦b¤¤¤ß½u¦P°¼(¤j©ó)"
If X(0) Then MsgBox "³sÄò7ÂI¦b¤¤¤ß½u¦P°¼(¤p©ó)"
End Sub


'================================

TOP

        ÀR«ä¦Û¦b : ¸Ü¦h¤£¦p¸Ü¤Ö¡A¸Ü¤Ö¤£¦p¸Ü¦n¡C
ªð¦^¦Cªí ¤W¤@¥DÃD