Board logo

標題: [發問] excel 判斷加總 [打印本頁]
作者: bill740615    時間: 2016-2-4 12:44     標題: excel 判斷加總

我要詢問，我的資料excel連結在http://1drv.ms/1PKFGce

需要從最底下2013/12/20開始算，E525儲存格應該加總D525:D489
也就是說，E525要先判斷從A524(往前一格)開始第2個1出現為止的是在A489
所以E525要把D525:D489全部加總起來。

再舉例，如果是E410，因為從A409開始往上判斷第2個1出現在A388
所以E410應該加總D410:D388。

請問各位大大，這樣的話，excel該如何跑? 跪求啊!!!!
作者: ML089    時間: 2016-2-4 22:00

回復 1# bill740615


E525 以陣列公式輸入 (CTRL+SHIFT+ENTER 三鍵齊按方式輸入)，再複製到其他儲存格
{=SUM(OFFSET(D525,,,LARGE(A$1:A524*ROW($1:524),2)-ROW()-1))}
作者: Hsieh    時間: 2016-2-4 23:25

回復 1# bill740615

程式區就提供一個VBA寫法
  1. Sub ex()
  2. For i = Application.CountA([C:C]) To 2 Step -1 '往上迴圈
  3. r = i: k = 0: x = Cells(i, 4) '各列初始值
  4.   Do Until k = 2 Or r <= 2  '直到A欄出現2次1或跑到第2列就跳出迴圈
  5.      r = r - 1 '往上一列
  6.      k = IIf(Cells(r, 1) = 1, k + 1, k) '如果A欄是1就增加計數
  7.   Loop
  8.   If k = 2 Then Cells(i, 5) = Application.Sum(Range(Cells(i, 4), Cells(r, 4))) Else Cells(i, 5) = "" '寫入加總值
  9. Next
  10. End Sub
複製代碼
作者: bill740615    時間: 2016-2-5 15:58

回復 2# ML089


M大您好，謝謝您，跑出來的資料沒有錯!!!
另外我想新增判斷加總條件在F欄，這次判斷稍微複雜
如果我想在每個1那一天出現前的第二個週一開始算，於F欄加總到下個1為止(如果沒有週一，就往上延至週二開始算，如果還是沒有週二，就繼續往上延至周三開始算，依此類推)

步驟舉例:
1.A508的1那日期是2014/1/15，依日子算前面第二個週一是B519的2013/12/30，所以F519儲存格要加總D519:D489，故從F519加總到F509停止，F508因為在A508有1，就不需要加了
2.依序上去的話，因為下個1出現的那一天2014/2/19前兩個禮拜的週一是2014/1/27，所以F508~F501不用加，而F500~F490就要加到D470。
3.如果是2014/6/18那一天的前兩個禮拜的週一應該是2014/6/2，但是資料沒有這一天，所以就只能從2014/6/3週二開始往上加，所以F419等於D419:D388，依序加到F409停止

請問M大，這樣的判斷該如何進行呢?
因為資料總數達5000筆，所以我需要了解該如何程式化進行
電子檔我已經先手動更新了，方便大大了解我的意思
作者: ML089    時間: 2016-2-7 18:30

回復 4# bill740615

E525 {=SUM(OFFSET(D525,,,LARGE(A$1:A524*ROW($1:524),2)-ROW()-1))}

F525 =IF(2<FREQUENCY((MOD(C$2:C525,7)<MOD(C$3:C526,7))*ROW($3:526),CHOOSE({1,2,3},ROW(),LOOKUP(1,0/A$2:A525,ROW($2:525)),0)),"",E525)

公式往上複製
作者: ML089    時間: 2016-2-8 18:46

本帖最後由 ML089 於 2016-2-10 00:27 編輯
  1. Sub ex()
  2.     Dim stauCalc%, nC%(1 To 3), n%, m%, LR%, i%, j%, k%, xR As Range
  3.     ThisWorkbook.Sheets("000").Select

  5.     stauCalc = Application.Calculation
  6.     Application.Calculation = xlCalculationManual

  8.     LR = Cells(Rows.Count, 2).End(xlUp).Row
  9.     Range("G2", "H" & LR).Clear
  10.     nC(1) = LR
  11.     For i = LR To 2 Step -1
  12.         If i = nC(1) Then
  13.             'A欄由下往上找第1個及第2個 "1"
  14.             n = 0
  15.             For j = i - 1 To 2 Step -1
  16.                 If Cells(j, 1) = "1" Then n = n + 1: nC(n) = j
  17.                 If n = 2 Then Exit For
  18.             Next
  19.             '由第1個 "1" 往下找第3個周一(最後一日)
  20.             m = 0: nC(3) = nC(1)
  21.             For k = nC(1) To i
  22.                 Set xR = Cells(k, "C")
  23.                 If Weekday(xR, 2) < Weekday(xR(2), 2) Then m = m + 1
  24.                 If m = 3 Then nC(3) = k: Exit For
  25.             Next
  26.         End If
  27.         If n <> 2 Then: Exit For
  28.         Cells(i, "G") = "=SUM(D$" & nC(2) & ":D" & i & ")"
  29.         If i <= nC(3) Then Cells(i, "H") = "=G" & i
  30.     Next
  31.     Application.Calculation = stauCalc
  32. End Sub
複製代碼




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