☰

查看銷售數據。如何確定訂單是針對特定客戶和產品的新訂單還是重新訂單？

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2016
查看銷售數據。如何確定訂單是針對特定客戶和產品的新訂單還是重新訂單？

我擁有的資料：交易日期(A:A)、客戶名稱(B:B)、銷售訂單編號(C:C)、產品名稱(D:D)、單位(E:E)、收入(F:F )

新訂單是指客戶在過去 6 個月或以前從未訂購過的任何商品。

如果客戶在過去 6 個月內購買了該特定產品，則將進行重新訂購。

我無法理解如何將這個邏輯放入 Excel 公式中。

答案1

也許我誤解了一些東西，但這似乎相當簡單。我對這個問題的理解是，如果當前行上方至少有一行與當前行具有相同的客戶名稱（列 B）、相同的產品名稱（列 D）和交易日期（欄 A) 在當前交易日期的過去六個月內。列 C,E和 F可以忽略。我假設這些行是按交易日期排序的（儘管我想我不需要做出這個假設）。

交易日期標準是「最難的」（我寬鬆地使用這個術語）。過去的日期是在過去六個月內（ A2如果是）

> EDATE(A2,-6)

因此，要計算目前行中滿足三個條件的行數，我們使用

=COUNTIFS(A$2:A2, ">" & EDATE(A2,-6), B$2:B2, B2, D$2:D2, D2)

這個A$2:A2符號很有趣。它表示從第 2 行開始到目前行結束的範圍；即，直到（並包括）當前行的所有內容。該計數始終至少為 1，因為當前行計數。如果它大於 1，則前面至少有一行也符合。所以答案是輸入

=IF(COUNTIFS(A$2:A2, ">" & EDATE(A2,-6), B$2:B2, B2, D$2:D2, D2)<=1, "New Order", "Reorder")

進入H2（或任何您想要的地方）並向下拖曳/填充。

顯示計數和計數解釋的電子表格

如果行可能亂序，我們需要搜尋整個表格並測試日期是否小於目前日期：

=IF(COUNTIFS(A$2:A$99, ">" & EDATE(A2,-6), A$2:A$99, "<" & A2,
                                           B$2:B$99, B2, D$2:D$99, D2)=0, "New Order", "Reorder")

我用來99表示資料的最後一行。我將測試從 更改為 ，<=1因為 =0 測試< A2消除了當前行。如果您的資料可能包含具有相同客戶名稱和產品名稱以及完全相同的交易日期的多行，請指定它們應如何處理。

答案2

哦，你一定會喜歡這個的。

我會將其分解為多個步驟，因為最終的公式將相當複雜。

設定

我按照您指定的格式建立了一個工作表。我添加了三列來闡明我們需要做什麼，我將在下面單獨描述。請注意，我在第一個螢幕截圖中凍結了頂行並向下滾動了一點。

工作表

第 1 步：建立搜尋範圍

首先，我們需要知道 6 個月前是什麼時候（以及該日期在電子表格中的位置）。在每筆記錄中，我們都會用它EDATE來找出答案。

在單元格中G2：

VLOOKUP(EDATE(A2,-6),A:A,1,TRUE)

這太棒了 - 這為我們提供了 6 個多月前的最後銷售記錄。請注意，如果您的銷售不頻繁（即銷售記錄之間存在較大差距），那麼我編寫此內容的方式確實意味著您可能會「捕獲」誤報，如果相關商品的最後一個訂單是6 個月一周前並且 6 個月前到一周前以及正好 6 週前根本沒有訂單。我認為這對你來說“足夠接近”了。

唯一的其他缺陷是在工作表頂部附近有一些看起來不愉快的錯誤，因為對於第一筆記錄（即 row 2）根本沒有 6 個月前的記錄。所以讓我們用一個包裹起來IFERROR：

=IFERROR(VLOOKUP(EDATE(A2,-6),A:A,1,TRUE),$A$2)

換句話說，告訴我 6 個多月前的最近一次訂單是什麼時候，除非沒有，在這種情況下，只需給我我們有記錄的第一個訂單。

這個公式抄下來。那是在這裡：

助手1

步驟 2：將範圍起始資料轉換為儲存格位置

在單元格中H2：

=MATCH(G2,A:A,0)

這個很簡單。我們可以在列中的哪個位置A找到我們在步驟 1 中建立的 6 個月前的日期？

這個公式抄下來。我們到了：

助手2

第三步：去做

讓我們跳到前面，將其放入，怎麼樣，I21這樣我們就可以看到它是如何工作的。

=INDEX(INDIRECT("A"&H21&":D"&ROW()-1),MATCH(D21,INDIRECT("D"&H21&":D"&ROW()-1),0),1)

本質上，這只是一個簡單的INDEX MATCH搜尋。但我們所做的是將這些函數的數組的單元格引用替換為INDIRECT我們剛剛播種到 column 中的值的引用H。

在我的範例資料中，行21是日期為 的銷售記錄10/1/2018。列G查找該日期之前 6 個月以上的最新銷售記錄，在我的樣本日期中恰好是4/1/2018。列H將該日期的（第一個）位置固定在A第 8 行的列中。因此表示從中 的值標識的INDIRECT("A"&H21&行開始搜尋數組。好的！的另一半，表示在我們目前所在記錄上方的一行處結束陣列。AHINDIRECT":D"&ROW()-1)D

換句話說，你可以寫

=INDEX(A1:D999,MATCH(D21,D1:D9999,0),1)

在整個銷售分類帳中查找您的產品代碼的首次出現D- 但我們不想這樣做。因此，我們將儲存格參考替換為我們使用前兩個步驟產生的動態範圍。與我一起？

所以看起來像這樣：

找到匹配項

一旦我們將該公式（包裝在另一個公式中IFERROR以用於外觀）複製到行2並返回，您將獲得：

科學怪人

第一行有誤報。我也將忍受這一點。

第四步：合併

I所以參考文獻中的公式H，即參考文獻G。反向展開得到最終公式：

=IFERROR(INDEX(INDIRECT("A"&MATCH(IFERROR(VLOOKUP(EDATE(A2,-6),A:A,1,TRUE),$A$2),A:A,0)&":D"&ROW()-1),MATCH(D2,INDIRECT("D"&MATCH(IFERROR(VLOOKUP(EDATE(A2,-6),A:A,1,TRUE),$A$2),A:A,0)&":D"&ROW()-1),0),1),"NEW ORDER")

看起來像：

大結局

因此，如果您願意，您可以將該公式複製到行中的“重新排序”列中2並向下複製。

注意。

  1. 您將在輸出欄位上使用格式刷，因為編寫的公式會將文字日期視為A日期序號，並傳回序號。
  2. 請注意步驟 1 中的弱點 - 您可以在helper1步驟3 中的欄位螢幕截圖中看到，在我的範例資料中，10/1 的6 個月前日期是4/1，10 的6 個月前日期是/5 是也4/1，因為 4/1 到 4/5 之間根本沒有訂單。這可能會導致誤報。
  3. 同樣，如步驟 3 所討論的，使用此方法對第一個銷售記錄也會出現誤報。

答案3

我解決這個問題的方法有點不同，因為我選擇了OP的線程，，

  1. 新訂單是指客戶在過去 6 個月或以前從未訂購過的任何商品。

  2. 如果客戶在過去 6 個月內購買了該特定產品，則將進行重新訂購。

在此輸入影像描述

  • Cell 中的數組 (CSE) 公式H41，以Ctrl+Shift+Enter。

{=IFERROR(LOOKUP(DATEDIF(IFERROR(INDEX($A$41:$A$47,MATCH(1,($B$41:$B$47=J41)*($D$41:$D$47=K41),0)),"No Match"),I41,"m"),{0,6,12},{"New Order","Order before 6 month","Order before 12 months"}),"Cust's. New Pro. Order")}

情況一：

新牽引日期：03/26/19。

顧客姓名：Bob。

產品名稱：Cake.

訂購薩塔烏斯：Order before 12 months.

在此輸入影像描述

情況2：

新牽引日期：03/26/19。

顧客姓名：Bob。

產品名稱：Milk.

訂購薩塔烏斯：New Order.

在此輸入影像描述

注意

因為舊交易日期 ( 10/01/18) 和新交易日期 ( 03/26/19) 之間的差異小於 6 個月。

情況3：

新牽引日期：03/26/19。

顧客姓名：Bob。

產品名稱：Wheat.

訂購薩塔烏斯：Order before 6 months.

在此輸入影像描述

情況4：

新牽引日期：03/26/19。

顧客姓名：Bob。

產品名稱：Fruit.

訂購薩塔烏斯：Cust's. New Pro.Order.

在此輸入影像描述

筆記：

如果您輸入新客戶的姓名以及舊產品或新產品以及日期，您將獲得Cust's . New Pro. Order狀態。

在此輸入影像描述

現在讓我解釋一下這個公式是如何運作的。

公式可以分為兩部分。

Part 1

 {=IFERROR(INDEX($A$41:$A$47,MATCH(1,($B$41:$B$47=J41)*($D$41:$D$47=K41),0)),"No Match")}

基本上它是查找Old Transaction Datefor 的2 個條件查找Customer & the Product，公式將其視為Start DateforDATEDIF公式在A41:A47..

Part 2

原文DATEDIF是，

{=LOOKUP(DATEDIF(A41:A47,I41,"m"),{0,6,12},{"New order","Order before 6 month","Order before 12 months"})}

其中A41:A47被替換為Part 1公式 as ，Start Date並且End Dateis 在單元格中I41。

兩個部分都很好地包裹著IFERROR。

注意

  • 您可以根據需要調整儲存格參考。
  • 帶有公式的訊息也可以根據您的選擇進行更改。

答案4

您可以使用以下公式：

=IF( SUM( ($B$2:$B9=A10) * ($D$2:$D9=D10) * ($A$2:$A9>($A10-183)) ) = 0, "New", "Reorder" )

它使用簡單的“range=”測試的老式技術來產生告訴您每一件事的數組（客戶的姓名是否在 A 列範圍的任何單元格中？產品名稱是否在 D 列範圍中？日期是否在列A 範圍在今天訂單的183 天內？

原始數組值是 TRUE/FALSE 值，但將它們相乘會強制 Excel 將它們更改為 1/0 值，這樣可以很好地相乘。最終結果是一個數組，其中滿足上述所有三個條件的情況為 1，不滿足的情況為 0。 Excel 不會將陣列的元素轉換回 TREU/FALSE 值，因此整個陣列都是數字。

SUM然後將它們全部匯總為一個值。如果結果為 0 以外的任何值，則在過去 183 天內至少存在一個相同的訂單。如果為 0，則不存在這樣的情況。它IF只是檢查結果並告訴您“新”或“重新排序”。

實際上，唯一感興趣的點是範圍尋址中絕對性與相對性的混合。所有範圍的起點都是完全絕對的，因此每個範圍的左上角都是固定的，不移動。範圍結束點僅使引用的列成為絕對值，以便範圍隨著您添加行而增長，但從不包括當前行...因此從頂部開始並在當前行上方的一行結束。

如果從一開始就不清楚，我會將出現在剛剛輸入的訂單行的 G 列儲存格中的資訊放入其中。但是，它可以在其他地方，甚至不在同一行或同一工作表中，並且可以用於進行條件格式測試以更改行單元格顏色以以這種方式顯示“新建/重新排序”。

相關內容