返回列表 上一主題 發帖

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

bridetobe

帖子
28 
主題
6 
精華
0 
積分
50 
點名
0  
作業系統
Windows7 
軟體版本
Office2003 
閱讀權限
20 
性別
女 
來自
新北市 
註冊時間
2014-7-2 
最後登錄
2014-8-25 
1# 跳轉到 » 正序看帖
打印
tT
發表於 2014-7-11 09:03 | 只看該作者

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

存貨.rar (34.12 KB)

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

GBKEE

帖子
5923 
主題
13 
精華
1 
積分
5986 
點名
0  
作業系統
win10 
軟體版本
Office 2010 
閱讀權限
150 
性別
男 
來自
台灣基隆 
註冊時間
2010-5-1 
最後登錄
2022-1-23 

2017 雞年全勤獎章2016猴年全勤獎章2015羊年全勤獎章2014馬年全勤獎章2013蛇年全勤獎章2012龍年全勤獎章論壇貢獻勳章熱烈參與勳章2011兔年全勤獎章
7#
發表於 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
複製代碼
感恩的心......(在麻辣家族討論區.用心學習會有進步的)
但資源無限,後援有限,  一天1元的贊助,人人有能力.

TOP

bridetobe

帖子
28 
主題
6 
精華
0 
積分
50 
點名
0  
作業系統
Windows7 
軟體版本
Office2003 
閱讀權限
20 
性別
女 
來自
新北市 
註冊時間
2014-7-2 
最後登錄
2014-8-25 
6#
發表於 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
塗料存貨管理(20140808).rar (68.86 KB)

TOP

bridetobe

帖子
28 
主題
6 
精華
0 
積分
50 
點名
0  
作業系統
Windows7 
軟體版本
Office2003 
閱讀權限
20 
性別
女 
來自
新北市 
註冊時間
2014-7-2 
最後登錄
2014-8-25 
5#
發表於 2014-7-17 10:03 | 只看該作者
回復 4# Hsieh


了解 非常感激!!!

TOP

Hsieh

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

2023年全勤獎章2022虎年全勤獎章2021牛年全勤獎章2020鼠年全勤獎章2019 豬年全勤獎章2018 狗年全勤獎章2017 雞年全勤獎章2016猴年全勤獎章2015羊年全勤獎章2014馬年全勤獎章2013蛇年全勤獎章2012龍年全勤獎章論壇貢獻勳章熱心公益勳章2011兔年全勤獎章
4#
發表於 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))
學海無涯_不恥下問

TOP

bridetobe

帖子
28 
主題
6 
精華
0 
積分
50 
點名
0  
作業系統
Windows7 
軟體版本
Office2003 
閱讀權限
20 
性別
女 
來自
新北市 
註冊時間
2014-7-2 
最後登錄
2014-8-25 
3#
發表於 2014-7-17 08:42 | 只看該作者
回復 2# Hsieh


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

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

這樣子呢?

TOP

Hsieh

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

2023年全勤獎章2022虎年全勤獎章2021牛年全勤獎章2020鼠年全勤獎章2019 豬年全勤獎章2018 狗年全勤獎章2017 雞年全勤獎章2016猴年全勤獎章2015羊年全勤獎章2014馬年全勤獎章2013蛇年全勤獎章2012龍年全勤獎章論壇貢獻勳章熱心公益勳章2011兔年全勤獎章
2#
發表於 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),))
向下複製
學海無涯_不恥下問

TOP

        靜思自在 : 閒人無樂趣，忙人無是非。
返回列表 上一主題