Board logo

標題: Sumif 應用題 [打印本頁]
作者: hugh0620    時間: 2010-12-3 19:13     標題: Sumif 應用題

本帖最後由 Hsieh 於 2010-12-3 19:25 編輯

Dear 大大們

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

本帖最後由 Hsieh 於 2010-12-3 19:57 編輯

原問題出處http://forum.twbts.com/thread-2074-1-1.html
這種大資料量使用樞紐分析表是比較好的選擇
[attach]3915[/attach]
[attach]3916[/attach]
作者: oobird    時間: 2010-12-3 21:28

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    時間: 2010-12-3 22:38

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

各位大大~
    這個問題~ 我就暫時採取Hsieh 大大~ 用樞紐的方式來作業~
    後續有大大有更好的方式~ 再來修改~
作者: mistery    時間: 2010-12-10 18:37

本帖最後由 mistery 於 2010-12-10 18:45 編輯

回復 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    時間: 2010-12-14 10:35

回復 6# mistery


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

本帖最後由 mistery 於 2010-12-14 18:42 編輯

回復 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  上述公式內" " 的文字 其實可以直接用點選储存格方式



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