
我的 Excel 中有這樣的數據:
Shop | Division | Launch Year
-------+-------------+-------------
Shop 1 | Division 1 | 2005
Shop 2 | Division 1 | 2006
Shop A | Division 2 | 2005
Shop B | Division 2 | 2007
...
等等。 4個部門總共約有100家商店。我需要一張表格,顯示每個部門每年年底有多少家商店開業。透過資料透視表,我可以輕鬆顯示當年新開的商店數量,但不能顯示總數。
我知道如何將運行總計添加到資料透視表,但在我的情況下,這不起作用,因為我沒有要添加的數字 - 我需要添加運行計數。如何才能做到這一點?
我正在尋找這個最終結果:
Year | Division 1 | Division 2 | ...
-----+------------+------------+----
2005 | 1 | 1 |
2006 | 2 | 1 |
2007 | 2 | 2 |
...
答案1
運行計數是 1 秒的運行總計。
如果資料允許,只需新增一個額外的全為 1 的列,並在資料透視表中使用這些列即可得出運行總計。
答案2
如果您的表相對靜態(列和行實際上沒有太大變化),請使用這些列標題和行標題。在正文中,使用以下公式(以偽代碼形式):
=countifs('Launch Year',"<="&'row header', 'Division', 'column header')
如果使用正確的錨定,您可以建立一次公式,然後將其複製並貼上到整個正文中。
答案3
為此,您不需要資料透視表。
建立表,如所需輸出所示。使用以下數組公式填入資料區域,輸入Ctrl Shift Enter,然後橫向和向下複製:
=SUMPRODUCT(--IF($C$2:$C$5<=$E2,1,0),IF($B$2:$B$5=F$1,1,0))
根據需要更新單元格引用,注意錨定。
和積函數文件來自女士。
您使用語句 2 產生兩個大小IF
相同的匹配值數組(輪到--
和TRUE
到1
),將它們相互相乘以查找與兩個條件都匹配的記錄,然後將s 和s 的結果列表相加以獲得您的全部的。FALSE
0
1
0
編輯:回覆您的評論/問題
到目前為止,此處顯示的設定是一個用於概念驗證的小型資料和結果表。為了使其具有更全面的可擴展性,您可能會將輸入表轉換為命名表。為此,請選擇相關範圍中的任何儲存格(範例:B4
在如圖所示的設定中)並按Ctrl+ T,然後在產生的模式視窗中確認。這將使編寫單元格引用變得更容易,並在添加資料時自動更新對這些資料範圍的任何單元格引用。
您也可以使用相同的方法將結果表製作為表格。這樣,當您新增新的年份時,您所要做的就是在該表格下方的下一行中輸入年份,所有公式都會自動貼上:
請注意,這種輸出表的自動更新不適用於新增新的部門。該表將擴展以容納它,但您必須手動將公式複製SUMPRODUCT
到新欄位中。
祝你好運並享受!