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

[µo°Ý] ½Ð°Ý¦p¦ó¥ý§PÂ_­þ¤@Äæ«á¦A¶i¦æ­pºâ

[µo°Ý] ½Ð°Ý¦p¦ó¥ý§PÂ_­þ¤@Äæ«á¦A¶i¦æ­pºâ

¥»©«³Ì«á¥Ñ gaishutsusuru ©ó 2023-6-2 22:51 ½s¿è

¦U¦ì«e½ú¦n¡A

·Q½Ð±Ð¤@­Ó°ÝÃD¡G

»¡©ú¡G
(1) ÄæA~ÄæD¬O¸ê®ÆÄæ¡C½Ð¤À§O³]­pI2,I3,I4,I5ªº¤½¦¡¡C
(2) I2¤½¦¡¡G±NI1ªº¦~¥÷»PC1,D1¶i¦æ§PÂ_«á¡A¦A·f°tH2ªº±ø¥ó(R1~R3)¡Cªí¥Ü¡G±N²Å¦X111¦~¥B¥N¸¹¬OR1~R3ªº¸ê®Æ¥[Á`¦^¶Ç­È60
(3) I3¤½¦¡¡G±NI1ªº¦~¥÷»PC1,D1¶i¦æ§PÂ_«á¡A¦A·f°tH3ªº±ø¥ó(2~4)¡Cªí¥Ü¡G±N²Å¦X111¦~¥B¦C¼Æ¬O2~4ªº¸ê®Æ¥[Á`¦^¶Ç­È60
(4) I4¤½¦¡¡G±NI1ªº¦~¥÷»PC1,D1¶i¦æ§PÂ_«á¡A¦A·f°tH4ªº±ø¥ó(¦Û¿ï±ø¥ó)¡Cªí¥Ü¡G±N²Å¦X111¦~¥B¡u¤â°Ê°é¿ïA2:A4¡vªº¸ê®Æ¥[Á`¦^¶Ç­È60 (³o¸Ìµy·L©â¶H¤@ÂI¡A§Ú¥i¯à·|¥Î¡uSUM(A2:A4)¡v¶i¦æ°é¿ï¡C¦ý¦]¬°­n¦A·f°tI1ªº¦~¥÷§PÂ_­n¨ú­þ¤@¦~ªº¼Æ¾Ú¡C©Ò¥H§Ú¸Õ¤F¦n¤[¼g¤£¥X¨Ó) (µù¡G¦¹³B¬O¥ýÁ|¨Ò°é¿ï³sÄòªº­È¡A¦Ó­Y§ó¶i¶¥ªº¥i¯à¬O¡uSUM(A2:A4,A7:A9¡v¶i¦æ°é¿ï)
(5) I5¤½¦¡¡G±NI1ªº¦~¥÷»PC1,D1¶i¦æ§PÂ_«á¡A¦A·f°tH5ªº±ø¥ó(R7)¡Cªí¥Ü¡G±N²Å¦X111¦~¥B¥N¸¹¬OR7ªº¸ê®Æ¥[Á`¦^¶Ç­È60

³Ì«áªþ¤WÀɮסG ¬¡­¶Ã¯1.zip (7.1 KB)

§Æ±æ«e½ú¯àÀ°¦£´£¨Ñ·Qªk¡A·PÁ±z­Ì¡C

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2023-6-5 11:54 ½s¿è

¦^´_ 1# gaishutsusuru


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

°õ¦æµ²ªG:



¤u§@ªí¼Ò²Õ:
Option Explicit 'H4¼g¤J¿ï¨úAÄ檺Àx¦s®æ
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Target
   Dim T$
   If .Columns.Count <> 1 Or .Column <> 1 Or .Row = 1 Then Exit Sub
   T = .Address(0, 1): [H4] = T
End With
End Sub
'========================================
Sub TEST()
Dim Brr, Crr, V, Y, A, R&, i&, j%, M&, T$, Tr$, V1&, V2&
Set Y = CreateObject("Scripting.Dictionary")
Brr = Range([D1], [A65536].End(3))
For i = 2 To UBound(Brr)
   For j = 3 To 4
      Y(Brr(1, j)) = j
      T = Brr(1, j) & "|" & Brr(i, 1): Tr = Brr(1, j) & "|" & i
      Y(T) = i: Y(T & "|" & "Sum") = Y(T & "|" & "Sum") + Brr(i, j)
      Y(Tr) = i: Y(Tr & "|" & "Sum") = Y(Tr & "|" & "Sum") + Brr(i, j)
   Next
Next
[I2:I65536].ClearContents
Crr = Range([I1], [H65536].End(3))
For i = 2 To UBound(Crr)
   T = Trim(Crr(i, 1)): If T = "" Then GoTo i01
   A = Split(Replace(Replace(Crr(i, 1), "$A", ""), ":", "~"), ",")
   If A(0) = "" Then GoTo i01
   For Each V In A
      V1 = Y(Crr(1, 2) & "|" & Split(V, "~")(0))
      V2 = Y(Crr(1, 2) & "|" & StrReverse(Split(StrReverse(V), "~")(0)))
      If InStr(V, "~") Then
         If V1 * V2 = 0 Then Crr(i, 2) = "": GoTo v01
         If V1 > V2 Then M = V2: V2 = V1: V1 = M
         For R = V1 To V2: Crr(i, 2) = Crr(i, 2) + Brr(R, Y(Crr(1, 2))): Next
         ElseIf Y(Crr(1, 2) & "|" & V & "|" & "Sum") <> "" Then
            Crr(i, 2) = Crr(i, 2) + Y(Crr(1, 2) & "|" & V & "|" & "Sum")
v01:  End If
   Next
i01: Next
[H1].Resize(UBound(Crr), 2) = Crr
Set Y = Nothing: Erase Brr, Crr, A
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 2# Andy2483


    ¤u§@ªí¼Ò²Õ­×¥¿¦p¤U:

Option Explicit  'H4¼g¤J¿ï¨úAÄ檺Àx¦s®æ
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Target
   Dim T$, xR As Range
   Set xR = Range([A2], [A65536].End(3))
   If .Columns.Count <> 1 Or .Column <> 1 Then Exit Sub
   If Intersect(xR, Selection.Cells) Is Nothing Then Exit Sub
   T = Intersect(xR, Selection.Cells).Address(0, 1)
   [H4] = T
End With
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 1# gaishutsusuru


    ÁÂÁ«e½ú
«á¾ÇÂǦ¹©«½m²ß¦Û­q¨ç¼Æ,¾Ç²ß¤è®×¦p¤U,½Ð«e½ú°Ñ¦Ò

¬¡­¶Ã¯1_20230605_5F.zip (16.32 KB)

°õ¦æµ²ªG:


Option Explicit
Function SumText(xC As String, xY As String)
Dim Brr, Crr, V, Y, A, R&, i&, j%, M&, T$, Tr$, V1&, V2&, Z&
Set Y = CreateObject("Scripting.Dictionary")
Brr = Range([D1], [A65536].End(3))
For i = 2 To UBound(Brr)
   For j = 3 To 4
      Y(Brr(1, j)) = j
      T = Brr(1, j) & "|" & Brr(i, 1): Tr = Brr(1, j) & "|" & i
      Y(T) = i: Y(T & "|" & "Sum") = Y(T & "|" & "Sum") + Brr(i, j)
      Y(Tr) = i: Y(Tr & "|" & "Sum") = Y(Tr & "|" & "Sum") + Brr(i, j)
   Next
Next
T = Trim(xC): If T = "" Then GoTo 102
A = Split(Replace(Replace(xC, "$A", ""), ":", "~"), ",")
If A(0) = "" Then Z = 0: GoTo 102
For Each V In A
   V1 = Y(xY & "|" & Split(V, "~")(0))
   V2 = Y(xY & "|" & StrReverse(Split(StrReverse(V), "~")(0)))
   If InStr(V, "~") Then
      If V1 * V2 = 0 Then Z = 0: GoTo 102
      If V1 > V2 Then M = V2: V2 = V1: V1 = M
      For R = V1 To V2: Z = Z + Brr(R, Y(xY)): Next
      ElseIf Y(xY & "|" & V & "|" & "Sum") = "" Then
         Z = 0: GoTo 102
      Else
         Z = Z + Y(xY & "|" & V & "|" & "Sum")
End If
v01: Next
102: If Z <> 0 Then SumText = Z Else SumText = ""
End Function
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 4# Andy2483

«D±`·PÁÂandy«e½úªºÀ°¦£¡Aexcelªº¯à¤O¯uªº¥O¤H¨ØªA¡C·Q½Ð°Ý¦pªG­n¦Û¾Ç³o¨ÇVBA¡B¦Û­q¨ç¼Æ¡A¦³­þ¨Ç±ÀÂ˪º®ÑÄy¶Ü? ·P®¦

-----

¥t¥~·Q½Ð°Ý¡G
¦pªG¤£¬O¥ÎVBA©Î¬O¥¨¶°ªº¸Ü¡A¨Ò¦p¡A§Ú±ø¥ó¬O¡uR1~R3¡vªº­È¡C¦bI2¤½¦¡¹Á¸ÕµÛ¥Î¥H¤U¤è¦¡¡AÁöµM¥i¥H§e²{¡A¦ý¯Ê¥F¤@¨Ç¼u©Ê¦Ó¥B¤¾ªø¡G
=INDEX(A:D,MATCH("R1",A:A,0),MATCH(I1,A1:D1,0))+INDEX(A:D,MATCH("R2",A:A,0),MATCH(I1,A1:D1,0))+INDEX(A:D,MATCH("R3",A:A,0),MATCH(I1,A1:D1,0))
·Q½Ð°Ý¡A³o­Ó¤½¦¡¦³¨S¦³¥i¥H§ï¶iªºªÅ¶¡©Î¬O¥t¤@ºØ¼gªk©O? ÁÂÁ±z¡C

TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2023-6-6 09:24 ½s¿è

¦^´_ 5# gaishutsusuru


    ÁÂÁ«e½ú¦^´_
1.«á¾Ç¹ï©ó¤Óªøªº¤½¦¡¾Ç±o«ÜºC,½Ð«e½ú«Ý¼F®`ªº«e½úÀ°¦£
2.«á¾Ç¾ÇExcel¤Q¦h¦~»{¬°¤W³Â»¶®a±Ú½×¾Â¾Ç²ß¬O³Ì¦nªº³~®|,¾ÇExcel¥u¦³¨C¤Ñ½m²ß¨D¶i¨B,¨S¦³±¶®|,½×¾Â¦³«Ü¦h¾Ç¤£§¹ªº½d¨Ò,¨C­Ó½d¨Ò¤S¥i¥H¥Î¦hºØ¤è®×¸Ñ¨M,¤£¥²¶R®Ñ,±`¤W³Â»¶®a±Ú½×¾Â¾Ç´N¹ï¤F
3.¥H¤U¬O½Æ²ß¬Q¤Ñªº¤è®×,µo²{«Ü¦h¦A§ï¶iªº,»P½Æ²ßªº¤ß±oµù¸Ñ,½Ð«e½ú°Ñ¦Ò,½Ð¦U¦ì«e½ú¤£§[«ü±Ð

Option Explicit'¤u§@ªí¼Ò²Õ
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'¡ô·í¤u§@ªí¤Wªº¿ï¨ú½d³òÅܧó®É·|µo¥Í¦¹¨Æ¥ó
With Target
'¡ô¥H¤U¬OÃö©óIJµoªºµ{§Ç
   Dim T$, xR As Range
   '¡ô«Å§iÅܼÆ
   Set xR = Range([A2], [A65536].End(3))
   '¡ô¥OxRÅܼƬO[A2]¨ìAÄæ³Ì«á¤@­Ó¦³¤º®eÀx¦s®æ,³o½d³òÀx¦s®æ
   If .Columns.Count <> 1 Or .Column <> 1 Then Exit Sub
   '¡ô¦pªG¿ï¨úIJµoªºÄæ¼Æ¤£¬O1Äæ,©ÎÄ渹¤£¬O1,´Nµ²§ô°õ¦æ
   If Intersect(xR, Selection.Cells) Is Nothing Then Exit Sub
   '¡ô¦pªGxRÅܼƻP¿ï¨úIJµoªºÀx¦s®æ¨S¥æ¶°,´Nµ²§ô°õ¦æ
   T = Intersect(xR, Selection.Cells).Address(0, 1)
   '¡ô¥OTÅܼƬO xRÅܼƻP¿ï¨úIJµoªºÀx¦s®æ¥æ¶°Àx¦s®æ¦ì§}(Ä渹¦³$)
   [H4] = T
   '¡ô¥O[H4]Àx¦s®æ­È¬O TÅܼÆ
End With
End Sub


Option Explicit'¤@¯ë¼Ò²Õ
Function SumText(xC As String, xY As String)
'¡ô¦Û­q¨ç¼ÆSumText(),«Å§iÅܼÆxC,xY³£¬O¦r¦êÅܼÆ
Dim Brr, Crr, V, Y, A, R&, i&, V1&, V2&, Z&, M&, j%, T$, Tr$
'¡ô«Å§iÅܼÆ(Brr,Crr,V,Y,A)¬O³q¥Î«¬ÅܼÆ,(R,i,V1,V2,Z,M)¬Oªø¾ã¼Æ,
'j¬Oµu¾ã¼Æ,(T,Tr)¬O¦r¦êÅܼÆ

Set Y = CreateObject("Scripting.Dictionary")
'¡ô¥OYÅܼƬO ¦r¨å
Brr = Range([D1], [A65536].End(3))
'¡ô¥OBrrÅܼƬO ¤Gºû°}¦C,¥HA~DÄæÀx¦s®æ­È±a¤J
For i = 2 To UBound(Brr)
'¡ô³]¶¶°j°é
   For j = 3 To 4
   '¡ô³]¶¶°j°é
      Y(Brr(1, j)) = j
      '¡ô¥O²Ä1¦Cj°j°éÄæBrr°}¦C­È·íkey,item¬Oj°j°é¼Æ(°O¦~¤Àªº°}¦CÄ渹)
      T = Brr(1, j) & "|" & Brr(i, 1): Tr = Brr(1, j) & "|" & i
      '¡ô¥OTÅܼƬO ²Ä1¦Cj°j°éÄæBrr°}¦C­È³s±µ"|",¦A³s±µi°j°é²Ä1ÄæBrr°}¦C­È,
      '¥OTrÅܼƬO ²Ä1¦Cj°j°éÄæBrr°}¦C­È³s±µ"|",¦A³s±µi°j°é¼Æ

      Y(T) = i: Y(T & "|Sum") = Y(T & "|Sum") + Brr(i, j)
      '¡ô¥OTÅܼƬOkey,item¬OiÅܼÆ,¯Ç¤JY¦r¨å¤¤
      '¡ô¥O¥HTÅܼƳs±µ"|Sum"²Õ¦¨·s¦r¦ê·íkey,item¬O
      '¬O²Ö¥[i°j°é¦Cj°j°éÄæBrr°}¦C­È

      Y(Tr) = i: Y(Tr & "|Sum") = Y(Tr & "|Sum") + Brr(i, j)
      '¡ô¥OTrÅܼƷíkey,item¬OiÅܼÆ,¯Ç¤JY¦r¨å¤¤,
      '¡ô¥O¥HTrÅܼƳs±µ"|Sum"²Õ¦¨·s¦r¦ê·íkey,item¬O
      '¬O²Ö¥[i°j°é¦Cj°j°éÄæBrr°}¦C­È

   Next
Next
T = Trim(xC): If T = "" Then GoTo 102
'¡ô¥OTÅܼƬO xCÅܼƥh°£«e«áªÅ¥Õ¦r¤¸«áªº·s¦r¦ê,
'¦pªGTÅܼƬO ªÅ¦r¤¸?´N¸õ¨ì¼Ð¥Ü102¦ì¸mÄ~Äò°õ¦æ

A = Split(Replace(Replace(xC, "$A", ""), ":", "~"), ",")
'¡ô¥OAÅܼƬOxCÅܼƦr¦ê³Q¤À³Î¦¨ªº¤@ºû°}¦C
'³QxCÅܼƳQ¤À³Î«e¥ý°µ2¦¸ªº¦r¤¸¸m´«,"$A"´«¦¨ "", ":"´«¦¨ "~"
'³Ì«á¥H³r¸¹¤À³Î¦¨¤@ºû°}¦C

If A(0) = "" Then Z = 0: GoTo 102
'¡ô¦pªG0¯Á¤Þ¸¹A°}¦C­È¬O ªÅ¦r¤¸?´N¥OZÅܼƬO0,¸õ¨ì¼Ð¥Ü102¦ì¸mÄ~Äò°õ¦æ
For Each V In A
'¡ô³]³v¶µ°j°é!¥OVÅܼƬO A°}¦C¸Ìªº¤@°}¦C­È
   V1 = Y(xY & "|" & Split(V, "~")(0))
   '¡ô¥OV1ÅܼƬOxYÅܼƳs±µ"|",
   '¦A³s±µ(VÅܼƥH"~"¤À³Î«áªº0¯Á¤Þ¸¹°}¦C­È)©Ò²Õ¦¨ªº·s¦r¦ê¬d,
   '¬dY¦r¨å¦^¶Çitem­È

   V2 = Y(xY & "|" & StrReverse(Split(StrReverse(V), "~")(0)))
   '¡ô¥O¥HV2ÅܼƬOxYÅܼƳs±µ"|",¦A³s±µ(VÅܼƦr¤¸¶¶§ÇÄA­Ë«á,
   '¥H"~"¤À³Î«áªº0¯Á¤Þ¸¹°}¦C­È°µ¦r¤¸¶¶§ÇÄA­Ë¦^¨Ó,
   '¥H¤W²Õ¦¨ªº·s¦r¦ê¬dY¦r¨å¦^¶Çitem­È

   If InStr(V, "~") Then
   '¡ô¦pªGVÅܼƸ̦³¥]§t"~" ?
      If V1 * V2 = 0 Then Z = 0: GoTo 102
      '¡ô¦pªGV1ÅܼƻPV2Åܼƪº­¼¿n¬O 0,
      '´N¥OZÅܼƬO0 , ¸õ¨ì¼Ð¥Ü102¦ì¸mÄ~Äò°õ¦æ

      If V1 > V2 Then M = V2: V2 = V1: V1 = M
      '¡ô¦pªGV1ÅܼƤj©óV2ÅܼÆ?´N¥OMÅܼƨó§UÅýV1.V2­È¤¬´«
      For R = V1 To V2: Z = Z + Brr(R, Y(xY)): Next
      '¡ô³]¶¶°j°é!±qV1ÅܼƨìV2ÅܼÆ,¥OZÅܼƬO Brr°}¦C­È,
      '(R¬O«üBrr°}¦C¦C¸¹,Y(xY)¬O«ü$I$1Àx¦s®æ­È¬dY¦r¨å°OªºÄ渹)

      ElseIf Y(xY & "|" & V & "|Sum") = "" Then
      '¡ô§_«h¦pªG¥H²Õ¦X¦r¦ê¬dY¦r¨å¸Ìitem¬OªÅ¦r¤¸?
         Z = 0: GoTo 102
         '´N¥OZÅܼƬO0 , ¸õ¨ì¼Ð¥Ü102¦ì¸mÄ~Äò°õ¦æ
      Else
         Z = Z + Y(xY & "|" & V & "|Sum")
         '¡ô§_«h´N¥OZÅܼƲ֥[ ²Õ¦X¦r¦ê¬dY¦r¨å¸Ìitem­È
   End If
Next
102: If Z <> 0 Then SumText = Z Else SumText = ""
'¡ô¦pªGZÅܼƤ£¬O0,´N¥OSumText¨ç¼Æ¦^¶ÇZÅܼÆ(ªø¾ã¼ÆÅܼÆ),
'§_«h´N¦^¶ÇªÅ¦r¤¸

End Function
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 5# gaishutsusuru

«á¾ÇªºÂ²¤Æ¼gªk¬°
=SUM(INDEX($C$2:$D$9,MATCH({"R1","R2","R3"},$A$2:$A$9,0),MATCH($I$1,$C$1:$D$1,0)))
¨Ñ±z°Ñ¦Ò°Ñ¦Ò

TOP

¦^´_ 1# gaishutsusuru

·PÁ«e½úµo°Ý
«á¾Çªº¼gªk¦p¤U
(2) =SUM(INDEX($D$2:$E$9,MATCH({"R1","R2","R3"},$B$2:$B$9,0),MATCH($J$1,$D$1:$E$1,0)))

(3) =SUM(HLOOKUP($J$1,D1:E9,{2,3,4},0))
¨ä¤¤{2,3,4}¬O«ü²Ä2¦C~²Ä4¦C

(4) =SUM((INDEX($D$2:$E$9,MATCH($B$2:$B$4,$B$2:$B$9,0),MATCH($J$1,$D$1:$E$1,0))),(INDEX($D$2:$E$9,MATCH($B$5:$B$8,$B$2:$B$9,0),MATCH($J$1,$D$1:$E$1,0))))

¶i¶¥ªº¸Ü§Ú¬O¦A§â¤½¦¡¼g¤@¦¸¡AµM«á¶i¦æ¥[Á`¡A¦p¤W¤½¦¡¬OB2:B4+B5:B8
³o³¡¤ÀÀ³¸ÓÁÙ¥i¥H²¤Æ¡A¦ý«á¾Ç¥\¤O¤£¨¬¡A¥i¯à­n¦U¦ì¤j¤jÀ°¦£¤F
p.s¤§©Ò¥H¬OBÄæ¬O¦]¬°AÄæ§Ú¥[¤F»²§UÄæ

(5)=SUM(IFERROR(VLOOKUP(ROW(A1:A8)&$I$5,A2:E9,MATCH($J$1,$D$1:$E$1)+3,0),""))
§Q¥ÎAÄ檺»²§UÄæ¨Ó¶i¦æ­«½Æ­Èªº¿z¿ï

¥H¤W¬°«á¾Çªº¼gªk¡A¤@©w¦³§ó¦nªº¼gªk¡AÅwªï¦U¦ì¤j¤j«ü¾É¡AÁÂÁ¡I

TOP

        ÀR«ä¦Û¦b : ¤f»¡¦n¸Ü¡B¤ß·Q¦n·N¡B¨­¦æ¦n¨Æ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD