Sumif 應用題

hugh0620

Dear 大大們

     小弟又來請教大大們的教誨~ 這次的問題是如何將每月的相同資料用sumif 加總起來 (或有更好的方式)
       1.資料比數約15000筆  (附檔中僅是舉例)
       問題一 : 在附件中的sheets(Q1)中,原本想寫成用Do Loop寫成自動將sumif換欄進行運算,但是失敗有問題  (產生定義上的問題)
       問題二 : 在附件中的sheets(Q2)中,我的原始資料中每個月都有O跟P兩筆數據
             現在的做法是用函數先完成O-P後,再使用SUMIF進行加總
             想請教大大們,在撰寫程式碼時,是否可以將每一列資料先行相減後,再將總合帶出來  (省掉O-P的步驟,而得到結果)

Hsieh

原問題出處http://forum.twbts.com/thread-2074-1-1.html
這種大資料量使用樞紐分析表是比較好的選擇

下載 (22.85 KB)

2010-12-3 19:41

SumIF應用題.zip (14.41 KB)

下載次數: 14

2010-12-3 19:57

oobird

q1模組：

1. Private Sub CommandButton1_Click()
   
2. Dim n%
   
3. [a:a] = [a:a].Value
   
4. [w:ai] = ""
   
5. Application.ScreenUpdating = 0
   
6. n = [a65536].End(3).Row
   
7.     [w6].Consolidate "R6C1: R" & n & "c21", xlSum, 1, 1
   
8. [x:ae].Delete
   
9. [w6] = [a6]
   
10. End Sub

複製代碼

q2模組：

1. Sub yy()
   
2. Dim rng, arr, d As Object, i%, j%, m%
   
3. [ai:bh] = ""
   
4. rng = Range([a6], [ag65536].End(3))
   
5. Set d = CreateObject("Scripting.Dictionary")
   
6. ReDim arr(1 To UBound(rng), 1 To 13)
   
7. arr(1, 1) = rng(1, 1)
   
8. For j = 2 To 13
   
9. arr(1, j) = Left(rng(1, 8 + c + j), 6): c = c + 1
   
10. Next
    
11. m = 1
    
12. For i = 2 To UBound(rng)
    
13. If d(rng(i, 1)) = "" Then
    
14. m = m + 1
    
15. d(rng(i, 1)) = m
    
16. arr(m, 1) = rng(i, 1): c = 0
    
17. For j = 2 To 13
    
18. arr(m, j) = rng(i, 8 + c + j) - rng(i, 8 + c + j + 1): c = c + 1
    
19. Next
    
20. Else
    
21. c = 0
    
22. For j = 2 To 13
    
23. arr(d(rng(i, 1)), j) = arr(d(rng(i, 1)), j) + rng(i, 8 + c + j) - rng(i, 8 + c + j + 1): c = c + 1
    
24. Next
    
25. End If
    
26. Next
    
27. [ai6].Resize(m, 13) = arr
    
28. End Sub

複製代碼

oobird

樞紐中插入12個計算欄位是很辛苦的，感覺還是先在表上用12個輔助欄會快些。
不知諸君可有其他更好的想法？

hugh0620

各位大大~
    這個問題~ 我就暫時採取Hsieh 大大~ 用樞紐的方式來作業~
    後續有大大有更好的方式~ 再來修改~

mistery

回復 1# hugh0620


    Q1的解法  個人建議可以用 SUM 陣列方式喔 ~ 方便快速好用 (見附檔)
     
   ={SUM(($C$1:$C$999="牛奶屋")*($D$1:$D$999="老大")*($E$1:$E$999="小弟")*($F$1:$F$999="A")*($G$1:$G$999="大支")*($I$1:$I$999=100)*(J1:J999))}

    PS 不是很懂Q2的問題重點...

hugh0620

回復 6# mistery


    Q2的問題
       1.原始資料中有O跟P的資料
       2.先將O-P的資料相減
       3.最後將O-P相減後的資料用SUMIF的方式 呈現
   與Q1的差異是多了要將O-P先完成,再用SUMIF處理

mistery

回復 7# hugh0620


    有關Q2的部份  我的做法如下
   
     =SUM(($C$7:$C$999="牛奶屋")*($D$7:$D$999="老大")*($E$7:$E$999="小弟")*($F$7:$F$999="A")*($G$7:$G$999="大支")*($I$7:$I$999=100)*((J7:J999)-(K7:K999)))

    只是有點遺憾....不能把公式直接右拉使用
    所以小小作弊一下...在統計的月份之間插入空白欄  ---> 方便公式直接右拉使用
    (之後再把空白欄刪掉  會比手動改公式內容快!)

     提供給您參考囉

    PS  上述公式內" " 的文字 其實可以直接用點選储存格方式