[發問] 請問特定欄位的計算

bridetobe

存貨.rar (34.12 KB)

下載次數: 9

2014-7-11 08:57

想請問一下
如果我想在小計欄後面新增一條使用量
就是比如將7/9的"#702"小計欄的數字抓出來
然後用7/10的"#702"小計欄數字減去7/9的數字
如果前一天抓不到 就顯示0
因為資料排序會變動(EX:以小計遞增排序或是以日期排序或是以品名排序)
無法用特定欄位相減
有變動的資料
這個部份有辦法作起來嗎

Hsieh

回復 1# bridetobe


    只計算前一天的用量嗎?
試試看
H2公式
=SUMPRODUCT((OFFSET($A$2,,,COUNT(A:A),)=A2-1)*(OFFSET($B$2,,,COUNT(A:A),)=B2)*OFFSET($G$2,,,COUNT(A:A),))
向下複製

下載 (31.55 KB)

2014-7-11 10:53

bridetobe

回復 2# Hsieh


不好意思 前面有點描述錯誤
應該是昨天的小計+昨天的進貨-今天的小計
在附上一次檔案
存貨管理(20140716).rar (41.07 KB)

下載次數: 7

2014-7-17 08:42

7/4的#702使用量就是昨天的小計24+昨天的進貨34-今天的小計34=8
這樣子
如果計算出來是負數 就寫0

這樣子呢?

Hsieh

回復 3# bridetobe
H2公式
=MAX(0,(SUMPRODUCT(($B$2:$B$1063=B2)*($A$2:$A$1063=A2-1)*$C$2:$C$1063)+SUMPRODUCT(($B$2:$B$1063=B2)*($A$2:$A$1063=A2-1)*$G$2:$G$1063))-SUMPRODUCT(($B$2:$B$1063=B2)*($A$2:$A$1063=A2)*$G$2:$G$1063))

bridetobe

回復 4# Hsieh


了解 非常感激!!!

bridetobe

回復 4# Hsieh


版大 不好意思 我想請問一下
因為後來使用上有兩個很大的問題
不知道如何解決
1.因為如果遇到星期一 就是以星期五當做前一天 但是預設是減一天 所以星期一的就無法計算
ex 7/21的#722小計是10
     7/18的#722小計是11
照這樣來說 使用量應該是1
但是因為遇到六日 沒辦法計算
這有辦法解決嗎?
2.由於資料會一直新增
H2公式=MAX(0,(SUMPRODUCT(($B$2:$B$1063=B2)*($A$2:$A$1063=A2-1)*$C$2:$C$1063)+SUMPRODUCT(($B$2:$B$1063=B2)*($A$2:$A$1063=A2-1)*$G$2:$G$1063))-SUMPRODUCT(($B$2:$B$1063=B2)*($A$2:$A$1063=A2)*$G$2:$G$1063))

如此一來 紅字部分就必須再每天修改後 再去一一修改參照到最下面那列 然後在向下複製
不知道這部分有沒有辦法解決

最主要的問題是第1點
如果函數部分沒辦法解決
是否有其他較好的方法可以算出使用量?

謝謝
附上說明檔案說明上面的ex
塗料存貨管理(20140808).rar (68.86 KB)

下載次數: 6

2014-8-15 21:29

GBKEE

回復 6# bridetobe
試試看

1. Option Explicit
   
2. Sub 讀出()
   
3.     Dim a_date, Rng(1 To 2) As Range
   
4.     Application.Calculation = xlCalculationManual  '手動計算
   
5.     Application.StatusBar = False
   
6.     With Sheets("操作")
   
7.         Set Rng(1) = .[A2:F2]
   
8.         Set Rng(2) = .Cells(1, .Columns.Count)
   
9.        .Range(Rng(1)(2, 1), Rng(1).End(xlDown)).Resize(, 7) = ""
   
10.         .[B1].Value = InputBox("輸入日期(例2014/7/1):", , "2014/7/1")
    
11.         Rng(2) = "日期"
    
12.         Rng(2).Offset(1) = .[B1]
    
13.         'AdvancedFilter 進階篩選
    
14.       '進階篩選:的準則範圍 -> Rng(2).Resize(2)
    
15.       Sheets("存貨資料").UsedRange.AdvancedFilter xlFilterCopy, Rng(2).Resize(2), Rng(1)
    
16.         Rng(2).EntireColumn = ""
    
17.         If Rng(1).End(xlDown).Row <> .Rows.Count Then
    
18.             Set Rng(2) = Rng(1).Cells(2, 1)
    
19.             Do While Rng(2) <> ""
    
20.                 Rng(2).Cells(1, "G") = 上一個小計(.[B1], Rng(2).Text) - Rng(2).Cells(1, "F")
    
21.                 If Rng(2).Cells(1, "G") < 0 Then Rng(2).Cells(1, "G") = 0
    
22.                  Set Rng(2) = Rng(2).Cells(2)
    
23.             Loop
    
24.             MsgBox "Ok"            
    
25.         Else
    
26.             MsgBox "沒有資料"
    
27.         End If
    
28.     End With
    
29.    ' Application.Calculation = xlAutomatic            '自動計算
    
30.     '自動篩選後會有工作表重算的動作,程式會等候重算完畢,再繼續執行下去.
    
31. End Sub
    
32. 'AutoFilter: 自動篩選
    
33. Private Function 上一個小計(日期 As String, 品名 As String) As Integer
    
34.     Dim Rng As Range
    
35.     With Sheets("存貨資料").UsedRange
    
36.         '自動計算:當自動篩選後會有工作表重算的動作,影響程式執行的速度
    
37.         '自動篩選 指定欄位,準則
    
38.         .AutoFilter 1, ">" & 日期
    
39.         .AutoFilter 2, "=" & 品名        
    
40.         Set Rng = .SpecialCells(xlCellTypeVisible)
    
41.         If Rng.Areas(1).Rows.Count > 1 Then
    
42.             上一個小計 = Rng.Areas(1).Cells(2, "g")
    
43.         Else
    
44.             上一個小計 = Rng.Areas(2).Cells(1, "g")
    
45.         End If
    
46.     End With
    
47. End Function

複製代碼