題 在沒有循環引用的情況下對同一列中的列求和


在Excel中,您可以將整列與 =SUM(B:B)。但是當然你不能在你想要求和的列中使用那個公式,因為你會得到一個循環引用。

是否有一種簡單的方法可以將當前單元格下方列中的所有值相加,除了諸如此類的黑客之外 =SUM(B2:B65536)

作為參考,有人在ask.metafilter上提出同樣的問題: http://ask.metafilter.com/65955/ExcelFilter-How-do-I-display-the-sum-within-that-c​​olumn-without-resulting-in-a-circular-reference-error

該線程中的建議 =SUM(B:B-B1) 基本上我正在尋找,只要它真的有效!


4
2017-08-24 09:13


起源




答案:


在您指向的同一個線程中,有一個用戶提出了一個UDF來解決問題。搜索 SUMRANGEWITHEXCEPTION 在文中。

您也可以關注Excel 2007幫助:

如果要保留循環引用,可以啟用迭代   計算,但您必須確定公式應該多少次   重新計算。當您打開迭代計算而不更改時   最大迭代或最大更改的值,Office Excel   在100次迭代後或在所有值之後停止計算   循環參考在迭代之間的變化小於0.001,   以先到者為準。但是,您可以控制最大數量   迭代和可接受的變化量。

  1. 單擊“Microsoft Office按鈕”,單擊“Excel選項”,然後單擊“公式”類別。
  2. 在“計算選項”部分中,選中“啟用迭代計算”複選框。
  3. 若要設置Office Excel將重新計算的最大次數,請在“最大迭代次數”中鍵入迭代次數   框。迭代次數越多,Excel的時間就越多   需要計算工作表。
  4. 要設置計算結果之間可接受的最大更改量,請在“最大更改”框中鍵入金額。該   數字越小,結果越準確,時間越長   Excel需要計算工作表。

2
2017-08-24 10:04



感謝那。不幸的是,我所說的“容易”也不是: - therefromhere
@therefromhere - 不幸的是,我覺得這些都很容易。 Excel不提供此功能,因此您必須自己構建(使用UDF),解決它(通過允許使用單個計算步驟的循環引用)或者只是構建根據范圍解決範圍的公式 =SUM(B2:B65536)。 - DMA57361


這將在B列的任何單元格中有效,無需修改:

=SUM(IF(ROW()=1,0,INDIRECT("$B$1:$B$"&ROW()-1,1)),
      IF(ROW()=65536,0,INDIRECT("$B$"&ROW()+1&":$B$65536")))

注意:這適用於早於2007年的Excel版本。


1
2017-08-30 20:22



您假設最大值為65536,而在較新版本的Excel中則不是這種情況。 - törzsmókus
@törzsmókus,謝謝,在筆記中編輯。我不知道我頭頂的新號碼,所以如果你這樣做,請隨意將其添加到備註中。 - Lance Roberts