標題: [發問] 如何算出合計 [打印本頁]

---

作者: dou10801    時間: 2025-9-22 15:54     標題: 如何算出合計

在不同條件的格子內,如何算出合計,感謝.

---

作者: hcm19522    時間: 2025-9-23 10:54

I2合併格公式=SUM(OFFSET(D2:H2,,,-LOOKUP(1,-MATCH(1=1,E3:E24<>"",{1,0}))))

---

作者: Andy2483    時間: 2025-9-23 14:50

回復 1# dou10801


    謝謝前輩發表此主題與範例,以下是學習方案,請前輩參考

執行結果:
[attach]38091[/attach]

Option Explicit
Sub TEST()
Dim Brr, Crr(1 To 1000), Drr(1 To 1000), D, i&, M%, N%, xR As Range, Ra As Range
Set Brr = Range([H2], [B65536].End(3)(1, 3))
For i = 1 To Brr.Rows.Count
   M = Brr(i, 2).MergeArea.Count
   N = N + 1
   Crr(N) = M
   i = i + M - 1
Next
Set xR = Brr(1)
For i = 1 To N
   For Each Ra In xR.Resize(Crr(i), Brr.Columns.Count)
      Drr(i) = Drr(i) + (Val(Ra.MergeArea(1)) / Ra.MergeArea.Count)
   Next
   If Drr(i) <> 0 Then xR(1, 8) = Drr(i)
   xR(1, 8).Resize(Crr(i)).Merge
   Set xR = xR(Crr(i) + 1)
Next
xR(1, 8) = "=SUM($K$2:K" & Brr.Rows.Count + 1 & ")"
End Sub

---

作者: Andy2483    時間: 2025-9-24 08:21

回復 3# Andy2483


    以下是今天練習 昨天版本再簡化的方案,請前輩們指教
Option Explicit
Sub TEST_1()
Dim Brr As Range, xR As Range, Ra As Range, i&, M%
[K:K].Clear
Set Brr = Range([H2], [B65536].End(3)(1, 3))
Set xR = Brr(1)
For i = 1 To Brr.Rows.Count
   M = Brr(i, 2).MergeArea.Count
   For Each Ra In xR.Resize(M, Brr.Columns.Count)
      xR(1, 8) = xR(1, 8) + (Val(Ra.MergeArea(1)) / Ra.MergeArea.Count)
   Next
   If xR(1, 8) = 0 Then xR(1, 8) = ""
   If M > 1 Then xR(1, 8).Resize(M).Merge
   Set xR = xR(M + 1)
   i = i + M - 1
Next
xR(1, 8) = "=SUM($K$2:K" & Brr.Rows.Count + 1 & ")"
End Sub

---

歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)