方法 1. 公式
讓我們從最簡單的選項——公式開始熱身。 如果我們有一個按日期排序的小表格作為輸入，那麼要在單獨的列中計算運行總計，我們需要一個基本公式：
這裡的主要特點是在 SUM 函數內對范圍進行了棘手的固定——對范圍開頭的引用是絕對的（帶有美元符號），而到末尾是相對的（沒有美元）。 因此，當將公式複製到整個列時，我們得到一個擴展範圍，我們計算的總和。
這種方法的缺點很明顯：
- 該表必須按日期排序。
- 使用數據添加新行時，必須手動擴展公式。
方法2.數據透視表
這種方法稍微複雜一些，但更令人愉快。 而且更嚴重的是，讓我們考慮一個更嚴重的問題——2000行數據的表，其中沒有按日期列排序，但有重複（即我們可以在同一天賣出幾次）：
我們將原始表格轉換為“智能”（動態）鍵盤快捷鍵 按Ctrl+T 或團隊 主頁 – 格式為表格 （首頁——表格格式），然後我們使用命令在其上構建一個數據透視表 插入 - 數據透視表 （插入 - 數據透視表）. 我們將日期放在摘要中的行區域，將銷售的商品數量放在價值區域：
請注意，如果您的 Excel 版本不太舊，則日期會自動按年、季度和月分組。 如果您需要不同的分組（或根本不需要），則可以通過右鍵單擊任何日期並選擇命令來修復它 分組/取消分組 （分組/取消分組）.
如果您想在單獨的列中查看結果總計和運行總計，則將字段放入值區域是有意義的 完售 再次獲得該字段的副本——我們將在其中打開運行總計的顯示。 為此，請右鍵單擊該字段並選擇命令 附加計算 – 累計 （將值顯示為 - 運行總計）:
在那裡，您還可以選擇以百分比形式增加總計的選項，在下一個窗口中，您需要選擇累積的字段——在我們的例子中，這是日期字段：
這種方法的優點：
- 快速讀取大量數據。
- 無需手動輸入公式。
- 更改源數據時，只需使用鼠標右鍵或使用 Data – Refresh All 命令更新摘要即可。
缺點源於這是一個摘要，這意味著您無法在其中做任何您想做的事情（插入行、編寫公式、構建任何圖表等）將不再起作用。
方法四：電源查詢
讓我們使用以下命令將包含源數據的“智能”表加載到 Power Query 查詢編輯器中 數據 – 來自表/範圍 （數據——來自表/範圍）. 順便說一句，在最新版本的 Excel 中，它被重命名了——現在它被稱為 有葉子 （來自工作表）:
然後我們將執行以下步驟：
1. 使用命令按日期列升序對錶格進行排序 排序升序 在表格標題的過濾器下拉列表中。
2. 稍後，為了計算運行總數，我們需要一個帶有序號行號的輔助列。 讓我們用命令添加它 添加列 – 索引列 – 從 1 （添加列——索引列——從 1 開始）.
3. 此外，要計算運行總計，我們需要對列的引用 完售，我們匯總的數據所在的位置。 在 Power Query 中，列也稱為列表 (list)，要獲得指向它的鏈接，請右鍵單擊列標題並選擇命令 詳圖 （查看詳細）. 我們需要的表達式會出現在公式欄中，由上一步的名稱組成 #“添加索引”，我們從哪裡獲取表格和列名 [銷售量] 來自方括號中的該表：
將此表達式複製到剪貼板以供進一步使用。
4. 刪除不必要的更多最後一步 完售 而是添加一個計算列，用於使用命令計算運行總計 添加列 - 自定義列 （添加列 - 自定義列）. 我們需要的公式如下所示：
這裡的功能 列表.範圍 獲取原始列表（列 [銷售量]) 並從中提取元素，從第一個開始（在公式中，這是 0，因為 Power Query 中的編號從零開始）。 要檢索的元素數是我們從列中獲取的行號 [指數]. 所以第一行的這個函數只返回列的第一個單元格 完售. 對於第二行 - 已經是前兩個單元格，對於第三行 - 前三個等。
那麼，函數 列表總和 對提取的值求和，我們在每一行中得到所有先前元素的總和，即累計總數：
仍然需要刪除我們不再需要的索引列，然後使用 Home – Close & Load to 命令將結果上傳回 Excel。
問題已經解決了。
速度與激情
原則上，這本可以停止，但美中不足的是——我們創建的請求以烏龜的速度運行。 例如，在我不是最弱的 PC 上，一張只有 2000 行的表在 17 秒內處理完畢。 如果有更多數據怎麼辦？
為了加快速度，您可以使用特殊的 List.Buffer 函數使用緩衝，它將作為參數提供給它的列表（列表）加載到 RAM 中，這大大加快了將來對其的訪問速度。 在我們的例子中，緩衝 #”Added index”[Sold] 列表是有意義的，Power Query 在計算 2000 行表的每一行的運行總數時必須訪問該列表。
為此，在主選項卡上的 Power Query 編輯器中，單擊高級編輯器按鈕（主頁 - 高級編輯器）以使用內置於 Power Query 的 M 語言打開我們的查詢源代碼：
然後在那裡添加一個帶有變量的行 我的清單，其值由緩衝函數返回，下一步我們用這個變量替換對列表的調用：
進行這些更改後，我們的查詢將變得更快，並且只需 2000 秒即可處理 0.3 行表！
另一件事，對吧？ 🙂
- 帕累托圖 (80/20) 以及如何在 Excel 中構建它
- 文本中的關鍵字搜索和 Power Query 中的查詢緩衝