Excel數據透視表反向求和技術

Excel數據透視表反向求和技術

之前一篇文章中我們給出了一個公式，能夠求出一列數值中從最后一個數值開始向上數5個數值的和，忽略其中的空格。本文給出了一個更簡潔的公式，并且可以指定求后面的X個數值之和。

如下所示，假設我們要求這列數值后面5個數值之和，即16+2+5+6+1=30，注意，空格不算在內。

如下所示，在單元格C2中指定要求和的數字的個數，在單元格C5中輸入數組公式：

=IFERROR(SUM(OFFSET($A$1,LARGE(IF($A$1:$A$15>0,ROW($A$1:$A$15)),C2)-1,0,ROWS($A$1:$A$15),1)),”沒有這么多數字”)

公式中，使用OFFSET函數確定要求和的單元格區域，其中的關鍵部分是：

LARGE(IF($A$1:$A$15>0,ROW($A$1:$A$15)),C2)-1

IF函數判斷單元格區域A1:A15中的值是否大于0，如果大于0，則返回該單元格所在的行號，否則返回FALSE，即：

LARGE({1;2;FALSE;4;5;6;7;FALSE;9;10;11;FALSE;FALSE;14;15},C2)-1

如果我們要求最后5個（單元格C2中的值）數字之和，那么可以看出，要開始求和的單元格對應的值的所在的行數從大到小排在第5位。也就是說，使用LARGE函數獲取數組中第5大的值，即對應著要開始求和的單元格所在的行號，減去1，得到OFFSET函數從單元格A1開始到達開始求和的單元格要偏移的行數。即：

LARGE({1;2;FALSE;4;5;6;7;FALSE;9;10;11;FALSE;FALSE;14;15},5)-1

其中的FALSE對應著空單元格。轉換為：

9-1

結果為：

8

代入主公式中得到：

=IFERROR(SUM(OFFSET($A$1,8,0,ROWS($A$1:$A$15),1)),”沒有這么多數字”)

轉換為：

=IFERROR(SUM(OFFSET($A$1,8,0,15,1)),”沒有這么多數字”)

轉換為：

=IFERROR(SUM($A$9:$A$23),”沒有這么多數字”)

即：

=IFERROR(SUM({16;2;5;0;0;6;1;0;0;0;0;0;0;0;0}),”沒有這么多數字”)

得到結果：

30

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。