數據透視表對每個人都有好處——它們計算速度快，配置靈活，如果需要，設計可以優雅地融入其中。 但也有一些美中不足，特別是無法創建摘要，其中值區域不應包含數字，而是文本。
讓我們試著繞過這個限制，在類似的情況下想出“一對拐杖”。
假設我們公司將其產品用集裝箱運輸到我國和哈薩克斯坦的幾個城市。 集裝箱每月發送不超過一次。 每個容器都有一個字母數字編號。 作為初始數據，有一個標準表列出了交貨，您需要從中進行某種匯總，以便清楚地看到每個城市和每個月發送的集裝箱數量：
為方便起見，讓我們使用命令預先將帶有初始數據的表“智能” 主頁 – 格式為表格 （首頁——表格格式） 並給她一個名字 交付 選項卡 構造函數 （設計）. 在未來，這將簡化生活，因為。 可以直接在公式中使用表名及其列。
方法 1. 最簡單的 - 使用 Power Query
Power Query 是一個超級強大的工具，用於在 Excel 中加載和轉換數據。 該插件自 2016 年起默認內置於 Excel。如果您有 Excel 2010 或 2013，則可以單獨下載和安裝（完全免費）。
整個過程，為了清楚起見，我在以下視頻中逐步分析：
如果無法使用 Power Query，那麼您可以採用其他方式 - 通過數據透視表或公式。
方法二、輔助總結
讓我們在原始表格中再添加一列，使用一個簡單的公式計算表格中每一行的數量：
顯然，-1 是必需的，因為我們的表格中有一個單行標題。 如果您的表格不在工作表的開頭，那麼您可以使用一個稍微複雜但通用的公式來計算當前行和表格標題的數量之差：
現在，以標準方式，我們將根據我們的數據構建所需類型的數據透視表，但在值字段中，我們將刪除該字段 電話號碼 而不是我們想要的 容器:
由於我們在同一個城市沒有幾個集裝箱在同一個月內，我們的摘要實際上不會給出數量，而是我們需要的集裝箱的行號。
此外，您可以關閉選項卡上的總計和小計 構造函數 - 一般總計 и 分類匯總 （設計——總計、小計） 並在同一個地方使用按鈕將摘要切換到更方便的表格佈局 報告模型 （報告佈局）.
因此，我們已經完成了一半的結果：我們有一個表，在 city 和 month 的交匯處，源表中有一個行號，我們需要的容器代碼就在其中。
現在讓我們複製摘要（到同一張或另一張紙上）並將其粘貼為值，然後將我們的公式輸入到值區域，這將通過摘要中的行號提取容器代碼：
功能 IF （如果），在這種情況下，檢查摘要中的下一個單元格是否為空。 如果為空，則輸出一個空字符串“”，即留空單元格。 如果不為空，則從列中提取 容器 源表 交付 使用函數按行號的單元格內容 INDEX （指數）.
也許這裡唯一不太明顯的一點是雙字 容器 在公式。 這麼奇怪的寫法：
補給品[[容器]:[容器]]
… 只需要引用該列 容器 是絕對的（就像普通“非智能”表的 $ 符號引用）並且在將我們的公式複製到右側時不會滑到相鄰的列。
以後更改源表中的數據時 交付，我們必須記住通過右鍵單擊它並選擇命令來使用行號更新我們的輔助摘要 更新並保存 （刷新）.
方法 3. 公式
這種方法不需要創建中間數據透視表和手動更新，而是使用了Excel的“重武器”——函數 夏日瘦身 （總和）. 您可以使用以下公式計算它們，而不是在摘要中查找行號：
實際上，這是選擇性求和功能的標準用例 夏日瘦身A 對給定城市和月份的行號求和。 同樣，由於我們在同一個月內沒有同一個城市的多個容器，我們的函數實際上將給出的不是數量，而是行號本身。 然後是之前方法中已經熟悉的功能 INDEX 您還可以提取容器代碼：
當然，在這種情況下，您不再需要考慮更新摘要，而是在大表上，函數 蘇梅斯利 可能會明顯變慢。 然後你將不得不關閉公式的自動更新，或者使用第一種方法——數據透視表。
如果摘要的外觀不太適合您的報表，那麼您可以不直接從其中提取行號到最終表中，就像我們所做的那樣，而是使用函數 獲取數據透視表數據 （GET.PIVOT.DATA）. 如何做到這一點可以在這裡找到。
- 如何使用數據透視表創建報告
- 如何在數據透視表中設置計算
- 使用 SUMIFS、COUNTIFS 等進行選擇性計數。