Подведение итогов столбца в том же столбце без циклической ссылки
В Excel можно суммировать целый столбец с =SUM(B:B). Но конечно Вы не можете использовать ту формулу в столбце, который Вы хотите суммировать, потому что Вы получите циклическую ссылку.
Есть ли простой способ суммировать все значения в столбце ниже текущей ячейки кроме взломов такой как =SUM(B2:B65536)?
Поскольку ссылка здесь - кто-то спрашивающий тот же вопрос относительно ask.metafilter: http://ask.metafilter.com/65955/ExcelFilter-How-do-I-display-the-sum-within-that-column-without-resulting-in-a-circular-reference-error
Предложение в том потоке =SUM(B:B-B1) по существу, что я ищу, если только это на самом деле работало!
2 ответа
В том же потоке Вы указали как ссылка, существует пользователь, предлагающий UDF для решения проблемы. Поиск SUMRANGEWITHEXCEPTION в тексте.
С осторожностью можно также следовать за справкой Excel 2007:
Если Вы хотите сохранить циклическую ссылку, можно включить повторяющиеся вычисления, но необходимо определить, сколько раз должна повторно вычислить формула. Когда Вы включаете повторяющиеся вычисления, не изменяя значения для максимальных повторений или максимального изменения, Office, который Excel прекращает вычислять после 100 повторений или после всех значений в изменении циклической ссылки меньше чем 0,001 между повторениями, какой бы ни на первом месте. Однако можно управлять максимальным количеством повторений и количеством приемлемого изменения.
- Нажмите Microsoft Office Button, нажмите Excel Options и затем нажмите категорию Formulas.
- В разделе опций Calculation выберите Разрешение повторяющегося флажка вычисления.
- Для установки максимального количества раз, которое повторно вычислит Office Excel введите количество повторений в поле Maximum Iterations. Чем выше количество повторений, тем больше времени, когда Excel должен вычислить рабочий лист.
- Для установки максимального количества изменения, Вы примете между результатами вычисления, ввести сумму в поле Maximum Change. Чем меньший число, тем более точный результат и больше времени, когда Excel должен вычислить рабочий лист.
Это будет работать в любой ячейке в столбце B без модификации:
=SUM(IF(ROW()=1,0,INDIRECT("$B$1:$B$"&ROW()-1,1)),
IF(ROW()=65536,0,INDIRECT("$B$"&ROW()+1&":$B$65536")))
Примечание: Это для версий Excel ранее, чем 2007.