Board logo

標題: [發問] 請問特定欄位的計算 [打印本頁]
作者: bridetobe    時間: 2014-7-11 09:03     標題: 請問特定欄位的計算

[attach]18680[/attach]

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

回復 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),))
向下複製
[attach]18684[/attach]
作者: bridetobe    時間: 2014-7-17 08:42

回復 2# Hsieh


不好意思 前面有點描述錯誤
應該是昨天的小計+昨天的進貨-今天的小計
在附上一次檔案
[attach]18710[/attach]

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

這樣子呢?
作者: Hsieh    時間: 2014-7-17 09:41

回復 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    時間: 2014-7-17 10:03

回復 4# Hsieh


了解 非常感激!!!
作者: bridetobe    時間: 2014-8-15 21:30

回復 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
[attach]18925[/attach]
作者: GBKEE    時間: 2014-8-16 09:12

本帖最後由 GBKEE 於 2014-8-16 11:50 編輯

回復 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
複製代碼




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