Board logo

標題: [發問] 要如何改善這個檔案的效能(重算儲存格) [打印本頁]
作者: nifanliu    時間: 2013-5-18 13:41     標題: 要如何改善這個檔案的效能(重算儲存格)

各位先進好,

我自行做了一個檔案來處理根據出貨資料來計算未結數量及可能的營收.目前運作上沒有問題
但是因為檔案常常要重算儲存格. 導致效率很慢. 想請教各位看有無較好的改善意見,檔案如附件.

一共有四個SHEET.

第一個SHEET"Update Data", 是我寫一個巨集去公司網路磁碟機根據出貨日期及出貨號(DN) 去找到出貨號資料夾下的出貨資訊.包含PO 號,貨品名及數量.
然後將資料寫到第二張SHEET"Shipping PO".

第三張SHEET"PO" 則是PO資料, 其中A-H 欄是我根據收到客戶訂單後, 人工KEYIN進去, 而從I 到P欄則會根據第二張sheet"Shipping PO"的內容來做運算
I欄是利用SUMPRODUCT, 來和SHEET"Shipping PO"的每一列比對雙方的PO 號及產品號(因為有時一張PO有兩產品),然後將所有符合的QTY 加總成為已經出貨數.
J欄則是將訂單的數量(D欄)減掉已出貨數(I欄)來看還有多少未結的數量. 有時會出現負數, 表示總出貨數多過訂單數, 這是因為有時客戶收到貨時表示其中有些貨瑕疵,公司則在下次出貨時多補上些數量給客戶, 所以這部分沒關係
K欄是根據J 欄來判斷是否該訂單以完成
L欄則是和 I 欄很像, 多了一個比較條件把屬於本月出貨的數量從第二張sheet"Shipping PO"的內容中給理出來.
MNOP四欄則是根據前面計算出的數量來乘單價計算營收

第四張SHEET"SUMMARY" 則是一個整理表, 裡面有兩個表格. 第一張表格是把第三張SHEET"PO"裡, LMNOPJ六欄位的值各自加總起來來看本月已出貨,未結案, 及整體營收. 第二個表格則是針對未結案訂單, 依照產品四個型號來分類看還有多少未出貨量及淺在營收

以上是檔案內容簡述.

我覺得自己這個檔案的瓶頸, 是在於由於第三及第四張SHEET會根據第二張SHEET的內容來做出比對和計算變化, 加上SUMPRODUCT 比對時都是用整欄(例如A2:A65536),(此檔案並非只有我一個人使用, 其他人不懂EXCEL 公式,所以避免每次新增出貨資料時, 更新者忘了到第三及第四張SHEET 更新需要比對的新增列數(例如從A2:A100改成A2:A105)造成資料錯誤, 所以用比對整欄).

使得使用上常常出現重算儲存格, 造成使用停頓.另外也造成一大問題, 就是每次需要更新時,用巨集去更新資料.每打開一個DN下資料夾的XLS出貨資料就出現重算儲存格.有時資料夾裡有十幾張XLS表, 則頓到許久時間.效率十分不好

因此想請教各位看有無較好的改善意見, 能從巨集或是儲存格公式上改善第三及第四張SHEET根據第二張SHEET的內容來做出比對和計算的方式, 不會一直重算存儲格. 謝謝！


[attach]15010[/attach]
作者: GBKEE    時間: 2013-5-19 20:11

回復 1# nifanliu
檔案關閉前  執行
  1. Sub Ex()   '公式轉為值:公式減少,重算儲存格時間縮短
  2.     Dim R As Integer
  3.     With Sheets("Shipping_PO").UsedRange     'Shipping_PO
  4.         For Each e In .Columns
  5.             Names.Add Chr(64 + e.Column) & "欄", e.Offset(1).Address(, , , 1)
  6.            '名稱定義 為 A欄,B欄,,C欄,D欄,E欄
  7.         Next
  8.     End With
  9.    
  10.     With Sheets("PO")
  11.         .Activate
  12.         [I2].Select
  13.         Names.Add "IA", "=!RC[-8]"    '名稱定義 相對 [I2] 於 -8欄 的位置
  14.         [I2].Select
  15.         Names.Add "IC", "=!RC[-6]"    '名稱定義 相對 [I2] 於 -6欄 的位置
  16.         [L2].Select
  17.         Names.Add "LA", "=!RC[-11]"
  18.         [L2].Select
  19.         Names.Add "LC", "=!RC[-9]"
  20.         R = .[a1].End(xlDown).End(xlDown).End(xlUp).Row
  21.         If R = 1 Then R = 2
  22.         .Range("F2:F" & R) = "=RC[-1]/RC[-2]"       '=E2/D2
  23.                            
  24.         .Range(" G2:G" & R) = "=RC[-1]/(VALUE(LEFT(RC[-4],2))*(VALUE(RIGHT(RC[-4],2)))*(0.0254^2))"
  25.                                 '=F2/(VALUE(LEFT(C2,2))*(VALUE(RIGHT(C2,2)))*(0.0254^2))
  26.                         
  27.         .Range("H2:H" & R) = "=RC[-2]*29.5*1000"       '=F2*29.5*1000
  28.                                 
  29.         .Range("I2:I" & R) = "=SUMPRODUCT((" & [B欄] & "=IA)*(" & [D欄] & "=IC)," & [E欄] & ")"
  30.                         '=SUMPRODUCT((Shipping_PO!$A$2:$A$65=A3)*(Shipping_PO!$D$2:$D$65=C3),Shipping_PO!$E$2:$E$65)
  31.             
  32.         .Range("J2:J" & R) = "=RC[-6]-RC[-1]"                           '=D2-I2
  33.             
  34.         .Range("K2:K" & R) = "=IF(RC[-1]>0,""Open"",""Close"")"            '=IF( J2>0, "Open","Close")
  35.         
  36.         .Range("L2:L" & R) = "=SUMPRODUCT((" & [B欄] & "=LA)*(" & [D欄] & "=LC)*(MONTH(" & [C欄] & ")=MONTH(TODAY()))," & [E欄] & ")"
  37.                         '=SUMPRODUCT((Shipping_PO!$B$2:$B$65536=A2)*(Shipping_PO!$D$2:$D$65536=C2)*(MONTH(Shipping_PO!$C$2:$C$65536)=MONTH(TODAY())),Shipping_PO!$E$2:$E$65536)
  38.                            
  39.         .Range("M2:M" & R) = "=RC[-1]*RC[-7]"                        '=L2*F2"
  40.             
  41.         .Range("N2:N" & R) = "=RC[-1]*29.5"                         '=M2*29.5
  42.             
  43.         .Range("O2:O" & R) = "=RC[-9]*RC[-5]"                       '=F2*J2
  44.             
  45.         .Range("P2:P" & R) = "=RC[-1]*29.5"                        '=O3*29.5
  46.         .UsedRange.Value = .UsedRange.Value
  47.         .Parent.Save
  48.     End With
  49. End Sub
複製代碼
作者: nifanliu    時間: 2013-5-20 19:27

回復 2# GBKEE

感謝GBKEE板主的回覆,
我試著在sheet "Update" 中, 建一個按鈕"CommandButton2", 按下按鈕後執行板主的程式.有兩個問題,再請板主協助指導一下.

1. 但是I和L欄(即SUMPRODUCT那兩欄) 都是0. 感覺上沒抓到值.找不出問題在哪, 是命名A欄到E欄時那段有問題嗎?

2. 另外在計算F和G欄時, 我設定的格式是小數點五位.但是好像都自動四捨五入到小數點第二位, (例如:E2/D2=0.00997,但是都會變成0.0100寫入儲存格). 不知要如何解?

請參閱附件. 謝謝

[attach]15040[/attach]
作者: stillfish00    時間: 2013-5-20 20:11

回復 3# nifanliu
超版的程式你放到模組中再試試，
你放到UpdateData的話，定義的名稱有效領域不一樣
作者: nifanliu    時間: 2013-5-20 20:35

我找到第一個問題點的原因了. 因為我忘記把板主的程式放在模組裡, 而是放在sheet1 "UpdateData"裡, 難怪找不到.

但是第二個問題,結果還是一樣,不知要如何解?
2. 另外在計算F和G欄時, 我設定的格式是小數點五位.但是好像都自動四捨五入到小數點第二位, (例如:E2/D2=0.00997,但是都會變成0.0100寫入儲存格).

另外再請教板主, 您的程式第20行
R = .[a1].End(xlDown).End(xlDown).End(xlUp).Row
和我們一般寫
.[a1].End(xlDown).Row
來算目前sheet的有資料最後一列.您使用這樣寫法有什麼不一樣的功用?

附上解決第一個問題的修改版.

[attach]15041[/attach]
作者: GBKEE    時間: 2013-5-21 08:00

回復 5# nifanliu
Q1  R = [a1].End(xlDown).End(xlDown).End(xlUp).Row 多一個End(xlDown)).End(xlUp) ,其實是多餘的.@@@@

Q2 改變 F,G欄數值格式
  1.    R = .[a1].End(xlDown).Row
  2.         If R = 1 Then R = 2
  3.          .Range("F2:G" & R).NumberFormatLocal = "G/通用格式"   '設為通用格式
  4.        '...程式碼 略過....
  5.          '...程式碼 略過....
  6.          .Range("P2:P" & R) = "=RC[-1]*29.5"                        '=O3*29.5
  7.         .UsedRange.Value = .UsedRange.Value
  8.         .Range("F2:G" & R).NumberFormatLocal = "_-$* #,##0.00000_-;-$* #,##0.00000_-;_-$* ""-""?????_-;_-@_-"
  9.                              '改回會計 格式
複製代碼
作者: nifanliu    時間: 2013-5-22 11:24

可以了. 謝謝板主. 原來是要在巨集計算裡就要把算出後的值來直接做格式設定.  

難怪我先前怎麼調儲存格格式.值都還是已經被四捨五入過後的數字.

學習到了. 謝謝板主指導.



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