帖子: 14
主題: 5
精華: 0
積分: 63
點名: 221
作業系統: win7
軟體版本: home
閱讀權限: 20
性別: 男
註冊時間: 2016-2-4
最後登錄: 2024-11-23

2023年全勤獎章 2022虎年全勤獎章

1^# 跳轉到 » 倒序看帖

字體大小: tT

發表於 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該如何跑? 跪求啊!!!!

000.rar (27.25 KB)

excel 判斷加總

樓主熱帖

ML089

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 2025
主題: 13
精華: 0
積分: 2053
點名: 0
作業系統: WIN7
軟體版本: Office2007
閱讀權限: 100
性別: 男
來自: 台北市
註冊時間: 2011-3-2
最後登錄: 2024-3-14

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

2^#

發表於 2016-2-4 22:00 | 只看該作者

回復 1# bill740615

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

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

Hsieh

超級版主 Rank: 8 Rank: 8

帖子: 4901
主題: 44
精華: 24
積分: 4916
點名: 254
作業系統: Windows 7
軟體版本: Office 20xx
閱讀權限: 150
性別: 男
來自: 台北
註冊時間: 2010-4-30
最後登錄: 2024-11-23

2023年全勤獎章 2022虎年全勤獎章 2021牛年全勤獎章 2020鼠年全勤獎章 2019 豬年全勤獎章 2018 狗年全勤獎章 2017 雞年全勤獎章 2016猴年全勤獎章 2015羊年全勤獎章 2014馬年全勤獎章 2013蛇年全勤獎章 2012龍年全勤獎章論壇貢獻勳章熱心公益勳章 2011兔年全勤獎章

3^#

發表於 2016-2-4 23:25 | 只看該作者

回復 1# bill740615

程式區就提供一個VBA寫法

Sub ex()
For i = Application.CountA([C:C]) To 2 Step -1 '往上迴圈
r = i: k = 0: x = Cells(i, 4) '各列初始值
Do Until k = 2 Or r <= 2 '直到A欄出現2次1或跑到第2列就跳出迴圈
r = r - 1 '往上一列
k = IIf(Cells(r, 1) = 1, k + 1, k) '如果A欄是1就增加計數
Loop
If k = 2 Then Cells(i, 5) = Application.Sum(Range(Cells(i, 4), Cells(r, 4))) Else Cells(i, 5) = "" '寫入加總值
Next
End Sub

複製代碼

學海無涯_不恥下問

TOP

bill740615

中學生 Rank: 2

帖子: 14
主題: 5
精華: 0
積分: 63
點名: 221
作業系統: win7
軟體版本: home
閱讀權限: 20
性別: 男
註冊時間: 2016-2-4
最後登錄: 2024-11-23

2023年全勤獎章 2022虎年全勤獎章

4^#

發表於 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筆，所以我需要了解該如何程式化進行
電子檔我已經先手動更新了，方便大大了解我的意思

000-2.rar (29.92 KB)

判斷加總(F欄)

TOP

ML089

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 2025
主題: 13
精華: 0
積分: 2053
點名: 0
作業系統: WIN7
軟體版本: Office2007
閱讀權限: 100
性別: 男
來自: 台北市
註冊時間: 2011-3-2
最後登錄: 2024-3-14

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

5^#

發表於 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)

公式往上複製

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

ML089

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 2025
主題: 13
精華: 0
積分: 2053
點名: 0
作業系統: WIN7
軟體版本: Office2007
閱讀權限: 100
性別: 男
來自: 台北市
註冊時間: 2011-3-2
最後登錄: 2024-3-14

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

6^#

發表於 2016-2-8 18:46 | 只看該作者

本帖最後由 ML089 於 2016-2-10 00:27 編輯

Sub ex()
Dim stauCalc%, nC%(1 To 3), n%, m%, LR%, i%, j%, k%, xR As Range
ThisWorkbook.Sheets("000").Select
stauCalc = Application.Calculation
Application.Calculation = xlCalculationManual
LR = Cells(Rows.Count, 2).End(xlUp).Row
Range("G2", "H" & LR).Clear
nC(1) = LR
For i = LR To 2 Step -1
If i = nC(1) Then
'A欄由下往上找第1個及第2個 "1"
n = 0
For j = i - 1 To 2 Step -1
If Cells(j, 1) = "1" Then n = n + 1: nC(n) = j
If n = 2 Then Exit For
Next
'由第1個 "1" 往下找第3個周一(最後一日)
m = 0: nC(3) = nC(1)
For k = nC(1) To i
Set xR = Cells(k, "C")
If Weekday(xR, 2) < Weekday(xR(2), 2) Then m = m + 1
If m = 3 Then nC(3) = k: Exit For
Next
End If
If n <> 2 Then: Exit For
Cells(i, "G") = "=SUM(D$" & nC(2) & ":D" & i & ")"
If i <= nC(3) Then Cells(i, "H") = "=G" & i
Next
Application.Calculation = stauCalc
End Sub

複製代碼

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP