大家好，Excel 比我更好的人！我感謝您提供的任何幫助。我會盡量簡短：
我有一個 3 列表。月份（如 1、2、3 等）、銷售人員姓名及銷售額。每行表示銷售人員當月的銷售額是多少美元。
它的排列方式如下：前 1000 行都是 1 月 1 日，接下來的 1000 行都是 2 月 2 日，等等。
我需要取得每位銷售人員過去 3 個月的平均銷售金額。但是——如果最後 3 個月中有一個是空白的，我仍然需要 3 個月的平均值。
因此，我需要公式來識別，例如，9 月的空白結果，然後獲取下一個最新結果（8 月、7 月，無論是什麼），因此它總是對該銷售人員最近3 個月的結果進行平均，並且不僅僅是 1 或 2 個結果。
現在我有一個非常簡單的資料透視表。在我意識到這個問題之前，它一直非常適合我的目的:(
有什麼方法（資料透視表與否）可以做到這一點？有超過 1000 名銷售人員，因此手動是不可行的。我有很多人由於某種原因一個月沒有數據，所以我需要圍繞這個進行計算。
謝謝你的幫忙！如果我可以添加其他內容或不清楚的話，請告訴我。
答案1
我不知道有什麼好方法來平均最近三個月的非空白月份，但這裡有一個非常駭客的方式來做到這一點。這可能會激發一些更好的方法來實現這一目標。
在原始資料中，建立一個表，然後按月份（降序）和人員排序。
接下來，過濾“銷售額”列並取消選擇空白。然後將這些內容複製到另一個區域並貼上。
然後建立一個“Rank”列。儲存格 D2 的公式，每個月對每個人進行排名（最近的為 1 等）。
=IF(B2=B1,D1+1,1)
然後在另一個區域中顯示您的不同人員（F 列）以及他們最近 3 個月的佔位符列（標記為 1、2 和 3 的列）。
G2單元格公式：
=SUMIFS($C:$C,$B:$B,$F2,$D:$D,G$1)
將其拖曳至列標題 3 並向下。最後，建立平均公式。
答案2
採用不同的方法，保持原始資料完整，無需重新排序、過濾或複製，將 3 個輔助列新增至資料清單中，如下所示。
柱子排是一個從 1 列開始的簡單索引系列人物2是相同的人但使用 IF 函數在零行中插入空字串銷售量。最大計數提供一個計數，透過人，非零的月份銷售量。
最大計數與結果列表交互，其中每個銷售人員包含一行，如下所示。
在結果清單中，列幾個月只需計算每個銷售人員的資料清單中銷售非零的月份數。單元格 I2 中的公式為
=COUNTIFS(A$2:A$21,H2,$C$2:$C$21,">0")
M1行顯示了排資料清單中每個銷售人員銷售額非零的第一個月對應的值。單元格 J2 中的公式為
=MATCH(H2,$E$2:$E$21,0)
列第 1 個月,第二個月和第三個月是列中的月份數字最大計數（數據清單中）對應於用於計算 3 個月平均值的第一、第二和第三個月，同時銷售1,銷售2和銷售3是這 3 個月的銷售額。平均的是計算出的 3 個月平均值。
單元格 K2、L2 和 M2 的公式分別為
=I2-1
=K2+1
=L2+1
單元格 N2 中的公式為
=SUMIFS($C$2:$C$21,$E$2:$E$21,$H2,$F$2:$F$21,K2)
並依賴中的值最大計數資料清單的欄位（範圍$F$2:$F$21，見下文）。單元格 N2 被複製到單元格 O2 和 P2。
範圍 I2:Q2 可以複製到結果清單的所有後續行。
資料列表-列最大計數
單元格 F2 中的公式為
=IF(C2>0,1,0)
這會將 0 或 1 放入儲存格中。 0 表示儲存格 E2 中的人員在儲存格 中顯示的月份中沒有銷售B2，而 1 表示這是該人員的第一個月進行銷售。
單元格 F3 中的公式要複雜得多，為
=IF(LEN(E3)=0,0,IF(VLOOKUP(E3,H$2:J$3,3,FALSE)=D3,1,1+MAXIFS(F$2:F2,E$2:E2,E3)))
如果該行對應於，則第一個 IF 的 TRUE 部分簡單地傳遞 0銷售量為零。
否則，銷售量非零且第二個 IF 被觸發。在這第二個 IF 中人在結果表中尋找儲存格 E3 中的內容，如果 M1行值（資料清單中對應於第一個月銷售的行號）與排儲存格 D3 中的值，這表示第 3 行對應於儲存格 E3 中標識的人員的非零銷售額第一行。
如果M1行和排由於數值不同，資料表中的第 3 行對應於儲存格 E3 中人員的後續月份（第一個月之後）的銷售額。在本例中，透過將資料清單前幾行的儲存格 E3 中銷售人員的 F 欄位中的最大值加 1 來獲得月份數。 MAXIFS 函數用於確定該最大值。
儲存格 F3 中的公式將複製到資料清單的後續行。在公式中適當使用相對和絕對尋址可確保 MAXIFS 函數在副本中使用適當的儲存格範圍。
筆記
- 使用 MAXIFS 需要 Excel 2019 或更高版本
- 假設 (i) 資料依時間順序排列，且 (ii) 每位銷售人員和月份組合最多有一行非零銷售資料。
- 在圖像中，藍色字體表示公式，黑色字體表示靜態值。