曾幾何時，它是 資料透視表是Excel中最突出的功能。但就像大多數工具和技巧一樣，這種情況已經改變了。以前，你需要建立資料透視表、修改佈局，並在每次新增或替換數字時進行更新，而現在，你只需編寫一個公式，就能以更少的精力和時間獲得相同的結果。
使用 PIVOTBY，您可以像使用資料透視表一樣，跨兩個軸對匯總資料進行分組並聚合值。但是，由於所有內容都包含在公式中，因此匯總資料將自動更新，從而可以更輕鬆快速地識別資料趨勢。
PIVOTBY 不僅僅是另一個資料透視表
這是一個可以立即產生完整摘要的簡單公式。
儘管輸出結果類似，但 PIVOTBY 與 Excel 傳統的透視表功能無關。它是一個動態陣列公式，適用於 Microsoft 365、Excel 2024 和 Excel 2021（包括 Mac 版本），可一步完成資料的分組、聚合、排序和篩選。您只需輸入一個公式，然後按 Enter 鍵，Excel 即可直接在工作表中輸出完整的總表，無需任何其他配置。
句子結構如下：
=PIVOTBY(row_fields, col_fields, values, function, [field_headers], [row_total_depth], [row_sort_order], [col_total_depth], [col_sort_order], [filter_array], [relative_to]
您只需要四個參數：要作為行的列（row_fields）、要放在頂部的列（col_fields）、要分組的資料（values）以及分組方法。分組方法可以是常見的 SUM、AVERAGE 或 COUNT，也可以是更高階的方法，例如： 自訂 LAMBDA 函數其他所有功能均為可選，如果您選擇使用，則可以獲得更多控制權。
例如，假設您想要按商品類型（行）和銷售管道（列）匯總總利潤，並按利潤從高到低排序。公式如下圖所示：
=PIVOTBY(C2:C5000, D2:D5000, N2:N5000, SUM,,,-2)
在本例中，C 欄包含商品類型，D 欄位包含銷售管道，N 欄位包含利潤。排序參數中的 -2 指示 Excel 依降序排列結果。您還會注意到它前面有三個逗號；這是因為 row_sort_order 參數位於第七位，而 Excel 需要為選擇跳過的參數添加佔位符。
與傳統的透視表一樣，您也可以組合多個行和列組：
=PIVOTBY(HSTACK(YEAR(F2:F5000), C2:C5000), D2:E5000, N2:N5000, SUM)
在這裡，我將 F 列中的訂單年份和 C 列中的商品類型作為行類別，同時將 D 列和 E 列中的銷售管道和訂單優先順序作為列類別。由於行類別來自不同的列，所以我使用了 HSTACK 函數 為了將它們合併成一個數組，同時由於列分組相鄰，我只擴展了範圍。我還將訂單日期範圍包含在 YEAR 函數中，以便 Excel 按年份而不是按單一日期分組。
現在您已經了解了此函數如何模擬資料透視表的功能，它們之間的差異就更加清晰了。首先，PIVOTBY 會在來源資料變更時自動更新，因此您無需再手動更新。其次，由於 PIVOTBY 本質上是一個公式，您可以像處理工作表中的其他部分一樣處理其輸出，這意味著您可以將其連接到下拉列表、應用格式或建立互動式儀表板，而無需擔心會破壞佈局。第三，由於它支援 LAMBDA 函數，您可以建立超越內建選項的自訂分組邏輯，從而實現傳統資料透視表無法實現的分析。
PIVOTBY 如何融入我的工作流程？
直接從公式快速靈活地產生摘要
PIVOTBY 非常適合需要快速產生自動化報告且維護量極少的場景。由於它是一個公式，因此不會出現匯總或結果過時的風險；您看到的內容始終反映資料的最新狀態。我發現，當我已經知道所需的聚合和分組時，它尤其有用，因為我可以預先定義所有內容，而無需手動重新排列欄位。
然而，傳統的透視表仍有其用武之地。如果您與沒有 Microsoft 365、Excel 2024 或 Excel 2021 的同事共用工作簿，PivotBy 功能將無法在他們那裡使用。在協作環境中，並非每個人都熟悉公式編輯，經典透視表的拖放介面使維護更加便利。透視表在快速探索性分析和可擴展的層級結構方面仍然具有優勢，允許其他人以互動方式深入挖掘資料。
大多數情況下，PIVOTBY 都能無縫融入我的工作流程。我經常用它來匯總單一資料集，因為它產生結果的速度更快，而且無需額外設定。但它的功能遠不止於單一工作表。透過將 VSTACK 整合到 PIVOTBY 中，您可以幾乎立即將多個工作表中的資料合併到一個統一的匯總表中。例如，如果您有三位員工 Mercy、Mike 和 Mitchell 的銷售資料分別儲存在不同的工作表中，並且想要按地點和員工職位匯總收入，您可以編寫以下程式碼：
=PIVOTBY(VSTACK(Mercy!C2:C5, Mike!C2:C5, Mitchell!C2:C5), VSTACK(Mercy!D2:D5, Mike!D2:D5, Mitchell!D2:D5), VSTACK(Mercy!B2:B5, Mike!B2:B5, MitchellB2:B5: BUM SUM)
您也可以透過將 filter_array 參數附加到下拉清單來使匯總結果具有互動性。例如，以下公式按國家/地區和優先順序計算訂單，但僅限於儲存格 V2 中指定的商品類型：
=PIVOTBY(B2:B5000, E2:E5000, G2:G5000, COUNT,,,,,,C2:C5000=V2)
這個表達式有效 C2:C5000=V2 作為邏輯掩碼，它會評估每一行並僅傳回符合的記錄。如果您希望當 V2 包含「all」時，公式顯示所有請求，只需在 IF 語句中包含 filter_array 參數即可：
=PIVOTBY(B2:B5000, E2:E5000, G2:G5000, COUNT,,,,,IF(V2="all", TRUE, (C2:C5000=V2)))
如果您想將匯總資料視覺化，可以將標準圖表直接連結到 PIVOTBY 溢位圖，確保視覺化元素會隨著資料的變化自動更新。因此，您也不應忽視透視圖。
更快捷的資料匯總方法
在所有情況下，PIVOTBY 都無法完全取代資料透視表，但在建立動態儀表板、合併多個工作表中的資料或簡化重複的設定步驟時，它的優勢顯而易見。由於所有操作都由單一公式驅動，我可以產生自動更新且易於與工作表其他部分整合的匯總資料。這使得整個報表流程更加高效，並減少了對人工幹預的依賴。
熟悉 PIVOTBY 函數的最佳方法是，用你現有的一個資料透視表，並使用該公式重建它。你很可能會驚訝地發現，重建過程竟然如此輕鬆快速。
