☰

excel中訂單減去銷售額

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2010 vba
excel中訂單減去銷售額

在過去的幾個小時裡，我一直在試圖找到與我遇到的問題類似的東西，但目前我的耳朵裡冒煙了！

我有兩張紙，一張有命令第二個與銷售量。

我自動需要幫助減法從命令基於銷售量。

我正在嘗試開發一個貫穿每次銷售的腳本，如果地點和物品完全相同，從訂購數量中減去已售數量即可得到「新到期」數量。

會有無訂單的銷售，這不是問題，但如果這些結果可以轉到不同的選項卡，那就太好了。以下是 n00b 術語中要求的摘要。

Sheet1是賣，Sheet2是命令。

如果Sheet1 C2&F2Sheet2與(中的任何行相同A2,D2是相關欄位）減去Sheet1 G2， 從Sheet2 F2。

我添加了一個示例，您可以在此處查看如果滿足多個條件則減去 https://app.box.com/s/2m8nfjo8lieh5mfb9wgspy73f1bvzp0e

在結果標籤上，所有以黃色突出顯示的內容都是會改變的內容。未以黃色突出顯示的商品沒有銷售。綠色和紅色格式僅供參考，如果困難則不需要。 （理想的情況）H 列正在說明，如果可能的話，一旦訂單數量達到 0，腳本將繼續搜尋工作表以查找具有相同條件的下一個訂單並減去剩餘數量。任何事情都會有所幫助，我目前在周末手動執行此操作，通常每週銷售量超過 150 筆，非常耗時。

簡而言之，我試圖讓腳本遍歷每個銷售行，如果工廠和零件編號相同，則從訂單中減去數量。我將根據訂單的執行時間對訂單表進行排序，以便從正確的訂單中刪除。

謝謝你的幫忙！

答案1

我認為你不需要VBA。

看看 SUMIFS()

我無權查看您的螢幕截圖，因為我組織的反向代理不允許訪問 app.box，但我假設：

  • Sheet1 的「C」欄包含位置。
  • Sheet1 的「F」欄位包含項目。
  • Sheet1 的「G」欄位包含已售出數量。
  • Sheet2 的「A」欄包含位置。
  • Sheet2 的「D」欄位包含項目。
  • Sheet2 的「G」欄包含訂購的數量。
  • Sheet2 的「H」欄位包含到期數量

在工作表 2 上，第 2 行所列的商品和位置的總銷售數量為：

=IF(OR(A2="",D2=""),0,SUMIFS(Sheet1!G:G,Sheet1!C:C,A2,Sheet1:F:F,D2))

因此，Sheet2!H2 使用的公式只是從訂購數量中減去該值：

=G2-IF(OR(A2="",D2=""),0,SUMIFS(Sheet1!G:G,Sheet1!C:C,A2,Sheet1:F:F,D2))

除非訂單已完成，且同一位置和商品的後續訂單至少已部分完成，則總銷售量可能大於第 2 行中的訂單，並且已訂購 - 已售出將小於零！但這是一件很容易防範的事......

=MAX(0,G2-IF(OR(A2="",D2=""),0,SUMIFS(Sheet1!G:G,Sheet1!C:C,A2,Sheet1:F:F,D2)))

但這並沒有考慮當前行以上的任何訂單！當我們應該有 MIN(0, + - ) 時，我們卻得到了 MIN(0, - ThisOrderQuantity) Sheet2中當前行上方的所有訂單的總數為LocationItemQtySoldThisOrderQuantityPreviouslyOrderedQuantitiesLocationItemQtySold

=OFFSET(G2,0,0,ROW()-2, 1)

.... 好吧... 這將在第 2 行中斷。所以讓我們在第二行保護自己。

=IF(ROW()<3,0,OFFSET(G2,0,0,ROW()-2, 1))

但這是所有先前的訂單，而我們只需要相同位置和商品的先前訂單。我們需要另一個 SUMIFS()。對於 Sheet2 第 10 行，這將是

=IF(ROW()<3,0,SUMIFS(OFFSET(G2,0,0,ROW()-2, 1),OFFSET(A:A,0,0,ROW()-2,1),A10,OFFSET(D:D,0,0,ROW()-2,1),D10))

好的，現在我們可以將它們結合起來。對於 H2，可一直向下拖曳到列中，公式為：

=MAX(0,G2+IF(OR(A2="",D2=""),0,IF(ROW()<3,0,SUMIFS(OFFSET(G2,0,0,ROW()-2, 1),OFFSET(A:A,0,0,ROW()-2,1),A2,OFFSET(D:D,0,0,ROW()-2,1),D2))-SUMIFS(Sheet1!G:G,Sheet1!C:C,A2,Sheet1:F:F,D2)))

除非您有三個位置/商品訂單，並且只有第一個訂單已完成，則第三個訂單的「到期」數量將是該訂單加上第二個訂單數量！我們需要確保如果所有先前的訂單尚未完全填寫，這不會破壞我們的「到期」數量。因此，我們應該添加一個 MAX 函數，以便到期的金額永遠不會大於訂購的金額。

=MIN(G2,MIN(0,G2+IF(OR(A2="",D2=""),0,IF(ROW()<3,0,SUMIFS(OFFSET(G2,0,0,ROW()-2, 1),OFFSET(A:A,0,0,ROW()-2,1),A2,OFFSET(D:D,0,0,ROW()-2,1),D2))-SUMIFS(Sheet1!G:G,Sheet1!C:C,A2,Sheet1:F:F,D2))))

將其貼到 H2 中，將該公式拖到 H 的其餘部分中，調試我所做的事情（因為，劇透，我沒有），然後就完成了！無需 VBA。

（警告：您提到了無訂單銷售的「非問題」。請考慮在這種情況下，如果您之後有訂單，該訂單將立即完成！

2017 年 2 月 3 日更新：修復了 MIN 和 MAX 的問題；它應該是 MAX 和 MIN。

相關內容