Andy2483

¦^´_ 1# bhsm


    ÁÂÁ½׾Â,ÁÂÁ«e½úµoªí¦¹¥DÃD»P½d¨Ò
«á¾ÇÂǦ¹©«½m²ß¦r¨å»PIJµo,¾Ç²ß¤è®×¦p¤U,½Ð«e½ú°Ñ¦Ò

°õ¦æ«e:

¤U¸ü (35.86 KB)

2023-4-10 09:33

[F4]¿é¤J7 °õ¦æµ²ªG:

¤U¸ü (34.89 KB)

2023-4-10 09:33

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
   If .Address = "$E$4" Or .Address = "$F$4" Then
      Dim Y, i%, j%, T$, S
      Set Y = CreateObject("Scripting.Dictionary")
      For i = 4 To 5
         For j = 4 To 6
            T = Cells(i, 2) & "|" & Cells(j, 3)
            S = Cells(i + j + (3 ^ (i - 4)), 6)
            If Not Y.Exists(T) Then
               Y(T) = S
               ElseIf Y(T) <> S Then
                  Y(T) = "µLªk¿ëÃÑ"
            End If
         Next
      Next
      [F5] = Y([E4] & "|" & [F4])
      If [F5] = "µLªk¿ëÃÑ" Then MsgBox "±Æ¦C²Õ¦X¤l­«½ÆµLªk¿ëÃÑ"
      Set Y = Nothing
   End If
End With
End Sub

Andy2483

¦^´_ 7# bhsm


    ÁÂÁ«e½ú¦^´_
«á¾ÇÂǦ¹©«½m²ß¤½¦¡,¾Ç²ß¤è®×¦p¤U,½Ð«e½ú°Ñ¦Ò

F9~F14¤½¦¡:
=IFERROR(IF(E$4=INDIRECT("B"&(INT(ROW()/12)+4)),E$4,"")+IF(F$4=INDIRECT("C"&MOD(ROW(),3)+4),F$4,""),"")

¤U¸ü (67.15 KB)

2023-4-10 13:44

PS:5¼Ó¤è®×ªºVBAµ{¦¡½X»Ý§R°£

Andy2483

¦^´_ 9# bhsm

¦pªG§Æ±æ§ïÅÜE4©ÎF4®É¤§«eªº¼Æ­È¤]­n«O¯d¸Ó¦p¦ó³B²z?
¨Ò¦p:E4=5¡BF4=9«hF9=14,¦ý¦pªG§ó§ïE4=10,F4=7®É,§Æ±æF9=14³Q«O¯d¤£³Q²M°£±¼,¦P®ÉÅã¥ÜF9=14.F13=17------

    ÁÂÁ«e½ú¦A¦^´_
«á¾ÇÂǦ¹©«¬ã¨sVBA¤è®×¦p¤U,½Ð«e½ú°Ñ¦Ò

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
   If .Address = "$E$4" Or .Address = "$F$4" Then
      Dim Y, i%, j%, T$, S, Brr
      Set Y = CreateObject("Scripting.Dictionary")
      Brr = [F9].Resize(6, 1)
      For i = 4 To 5
         For j = 4 To 6
            T = Cells(i, 2) & "|" & Cells(j, 3)
            S = Val(Cells(i, 2)) + Val(Cells(j, 3))
            N = N + 1: Y(T) = S
            If T = [E4] & "|" & [F4] Then Brr(N, 1) = Y(T)
         Next
      Next
      [F9].Resize(6, 1) = Brr
      Set Y = Nothing: Erase Brr
   End If
End With
End Sub

Andy2483

¦^´_ 11# bhsm

ÁÂÁ«e½ú,¥H¤U¤ß±oµù¸Ñ½Ð°Ñ¦Ò
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
'¡ô·í¨Ï¥ÎªÌ©Î¥~³¡³sµ²Åܧó¤u§@ªí¤¤ªºÀx¦s®æ®É·|µo¥Í¦¹¨Æ¥ó
With Target
'¡ô¥H¤U¬OÃö©óIJµo¨Æ¥óªºµ{§Ç
   If .Address = "$E$4" Or .Address = "$F$4" Then
   '¡ô¦pªGIJµoÀx¦s®æ¬O[E4] ©Î[F4]ªº¦ì§}
      Dim Y, S, Brr, i%, j%, T$, N&
      '¡ô«Å§iÅܼÆ:(Y,S,Brr)¬O³q¥Î«¬ÅܼÆ,(i,j)¬Oµu¾ã¼Æ,T¬O¦r¦êÅܼÆ
      'N¬Oªø¾ã¼Æ
      Set Y = CreateObject("Scripting.Dictionary")
      '¡ô¥OY³o³q¥Î«¬ÅܼƬO ¦r¨å
      Brr = [F9].Resize(6, 1)
      '¡ô¥OBrr³o³q¥Î«¬ÅܼƬO ¤Gºû°}¦C,¥H[F9]¦V¤UÂX®i6®æªºÀx¦s®æ­È±a¤J
      For i = 4 To 5
      '¡ô³]¶¶°j°é!i±q4¨ì 5
         For j = 4 To 6
         '¡ô³]¶¶°j°é!j±q4¨ì 6
            T = Cells(i, 2) & "|" & Cells(j, 3)
            '¡ô¥OT³o¦r¦êÅܼƬO i°j°é¦CBÄæÀx¦s®æ­È³s±µ "|",
            '¦A³s±µ j°j°é¦CCÄæÀx¦s®æ­È¤§«áªº·s¦r¦ê
            S = Val(Cells(i, 2)) + Val(Cells(j, 3))
            '¡ô¥OS³o³q¥Î«¬ÅܼƬO i°j°é¦CBÄæÀx¦s®æ­ÈÂà¤Æ¬°¼Æ¦r­È,
            '¥[¤Wj°j°é¦CCÄæÀx¦s®æ­ÈÂà¤Æ¬°¼Æ¦r­È¤§«áªº·s¼Æ­È
            N = N + 1: Y(T) = S
            '¡ô¥ON³oªø¾ã¼ÆÅÜ¼Æ ²Ö¥[1
            '¥O¥HTÅܼƬ°key,item¬OSÅܼÆ,¯Ç¤JY¦r¨å
            If T = [E4] & "|" & [F4] Then Brr(N, 1) = Y(T)
            '¡ô¦pªGTÅܼÆ(¦r¦ê)¦P
            '[E4]Àx¦s®æ­È³s±µ"|" ¦A³s±µ[F4]Àx¦s®æ­È²Õ¦¨ªº·s¦r¦ê??
            '´N¥ONÅܼƦC²Ä1ÄæBrr°}¦C­È¬O ¥HTÅܼƬdY¦r¨åªºitem­È
         Next
      Next
      [F9].Resize(6, 1) = Brr
      '¡ô[F9]¦V¤UÂX®i6®æªºÀx¦s®æ­È¥H Brr°}¦C­È±a¤J
      Set Y = Nothing: Erase Brr
      '¡ôÄÀ©ñÅܼÆ
   End If
End With
End Sub

Andy2483

¦^´_ 13# bhsm


    ÁÂÁ«e½ú¤£¶û±ó,½Ð«e½ú±`¤W½×¾Â¤@°_¾Ç²ß
¯¬ ¶¶¤ß±`¼Ö

Andy2483

¦^´_ 15# bhsm


    ÁÂÁ«e½ú
1.­Y¤£¦P¥DÃD,½Ð¥tµo¥DÃDµ¹«e½ú­ÌÀ°¦£
2.¤U¤È­è±µ¨ì¤@¥÷«æ»°ªº¤u§@»Ý¥ß°¨³B²z,¦A¼·ªÅ¬ã¨s«e½úªº½d¨Ò

Andy2483

¦^´_ 15# bhsm


    ÁÂÁ«e½ú
¥H¤U¬O¾Ç²ß¤è®×,½Ð«e½ú°Ñ¦Ò

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
   If .Address = "$F$3" Or .Address = "$H$3" Then
      If [G7] = "" Then
         [G7] = [G4]
         Else
            [G65536].End(3).Item(2) = [G4]
      End If
   End If
End With
End Sub

Andy2483

¦^´_ 19# bhsm


    ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú,ÁÂÁ«e½ú¦A¦^´_
«á¾Ç±µ¨ìªº¤u§@«Ü§Ö´N§¹¦¨¤F,¾Ç²ß¤F°}¦C»P¦r¨å,µ{¦¡³]­pªºÅÞ¿è§ó²M·¡,¤u§@®Ä²v´£¤É¦h­¿,
³]­pªºµ{¦¡°õ¦æªº®Ä²v´£¤É¤W¦Ê­¿,¥H«e°õ¦æ30¤ÀÄÁ,²{¦b¥u­n10¬íÄÁ
¶Ô½m²ß¬O«Ü­«­nªº,ÁÂÁ«e½ú¤@°_¤W½×¾Â¾Ç²ß

Private Sub Worksheet_Change(ByVal Target As Range)
'¡ô·í¨Ï¥ÎªÌ©Î¥~³¡³sµ²Åܧó¤u§@ªí¤¤ªºÀx¦s®æ®É·|µo¥Í¦¹¨Æ¥ó
With Target
'¡ô¥H¤U¬OÃö©óIJµo¨Æ¥óªºµ{§Ç
   If .Address = "$F$3" Or .Address = "$H$3" Then
    '¡ô¦pªGIJµoÀx¦s®æ¬O[F3] ©Î[H3]ªº¦ì§}
      If [G7] = "" Then
      '¡ô¦pªG[G7]Àx¦s®æ­È¬OªÅ¦r¤¸
         [G7] = [G4]
         '¡ô¥O[G7]Àx¦s®æ­È¬O [G4]Àx¦s®æ­È
         Else
            [G65536].End(3).Item(2) = [G4]
            '¡ô§_«h¥OGÄæ³Ì«á¤@­Ó¦³¤º®eÀx¦s®æªº¤U¤è¤@®æ­È¬O [G4]Àx¦s®æ­È
      End If
   End If
End With
End Sub

Andy2483

¦^´_ 21# bhsm


        ÁÂÁ«e½ú¦^´_
ÅÞ¿è¬O¥i¥H°V½mªº,¨Ò¦p «pÁy¥Öªº¾Ç¥Í ´N«Ü²Â¾Ç¤F¤Q´X¦~ÁÙ«Ü»{¯uªº¾Ç,¤£©ñ±ó
³o»òÀu½èªº¾Ç²ß¥­¥x«ç±Ë±oÅý ¯uªº¡¨¤£·|´N¬O¤£·|¡¨¦¨¬°¨Æ¹ê©O?
¤@¤Ñ¶i¨B¤@ÂIÂI´N¥i¥H¤F

Andy2483

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


    ÁÂÁ½׾Â,ÁÂÁ«e½ú«ü¾É
«á¾Ç¾Ç²ß¤ß±o¦p¤U,½Ð«e½ú¦A«ü¾É

'§Q¥Î index ­È¶ñ¥R//
https://learn.microsoft.com/zh-t ... sheetfunction.index
Private Sub Worksheet_Change(ByVal Target As Range)
'¡ô·í¨Ï¥ÎªÌ©Î¥~³¡³sµ²Åܧó¤u§@ªí¤¤ªºÀx¦s®æ®É·|µo¥Í¦¹¨Æ¥ó¡C
Dim j%, k%
'¡ô«Å§iÅܼÆ:(j,k)¬Oµu¾ã¼Æ
With Target
'¡ô¥H¤U¬OÃö©óIJµoªºµ{§Ç
     If .Address <> "$E$4" And .Address <> "$F$4" Then Exit Sub
     '¡ô¦pªGIJµo®æªº¦ì§}¤£¬O "$E$4"¦Ó¥B¤]¤£¬O "$F$4" ´Nµ²§ôµ{¦¡°õ¦æ
     j = Abs(([e4] = [b4]) + ([e4] = [b5]) * 4)
     '¡ô¥Oj³oµu¾ã¼Æ¬O µ´¹ï­È(¥¬ªL­È + ¥¬ªL­È *4)
     '¦pªG[e4] = [b4] ¨ä¥¬ªL­È¬O1,¦A¦pªG[e4] = [b5]¥¬ªL­È¬O0*4=0 ,µ²ªG­È¬O1
     '¦pªG[e4] = [b4] ¨ä¥¬ªL­È¬O0,¦A¦pªG[e4] = [b5]¥¬ªL­È¬O1*4=4 ,µ²ªG­È¬O4
     k = Abs(([f4] = [c4]) + ([f4] = [c5]) * 2 + ([f4] = [c6]) * 3)
     '¡ô¥Ok³oµu¾ã¼Æ¬O µ´¹ï­È(¥¬ªL­È + ¥¬ªL­È *2)+(¥¬ªL­È *3)
     '[f4]=[c4]_¨ä¥¬ªL­È¬O1,[f4]=[c5]_¥¬ªL­È¬O0*2=0,[f4]=[c6]¬O0*3=0,µ²ªG­È¬O1
     '[f4]=[c4]_¨ä¥¬ªL­È¬O0,[f4]=[c5]_¥¬ªL­È¬O1*2=2,[f4]=[c6]¬O0*3=0,µ²ªG­È¬O2
     '[f4]=[c4]_¨ä¥¬ªL­È¬O0,[f4]=[c5]_¥¬ªL­È¬O0*2=0,[f4]=[c6]¬O1*3=3,µ²ªG­È¬O3
     If j * k Then [f9].Cells(j + k - 1) = [f5]
     '¡ô¦pªGjÅܼÆ*kÅܼƤ£¬O 0,´N¥O[f9]¬Û¹ï¦ì¸mÀx¦s®æ­È¬O [f5]
End With
End Sub
µ´¹ï­È¬A©·¸Ìªº­È¬Ý°_¨Ó³£¬O¥¿¼Æ,ÁöµM©|¤£¤F¸Ñ¬°¦ó­n¥[Abs,«á¾Ç¦pªGª¾¹D¤F·|¦^¨Ó¸É¥R
ÁÂÁ«e½ú