Excel раздражение: как сохранить формулы в бухгалтерской книге?

Question

Распространенным использованием Excel является создание электронной таблицы, которая функционирует в качестве регистра операций какого-либо рода, где одно или несколько полей в каждой строке рассчитываются путем добавления значения или значений из текущей строки к значению из предыдущей строки.

Например, если в столбце C содержится значение транзакции, а в столбце D - баланс, тогда в строке D3 формула может быть = D2+C3. Эта формула будет повторяться для каждой строки в книге.

Обычное раздражение при ведении электронных таблиц этого типа состоит в том, что если вы вставляете строку в середину такой бухгалтерской книги, эта строка может быть пропущена при расчете баланса - формулы в следующей строке должны быть обновлены вместе с формулами в вставленный ряд.

Есть ли у вас какие-либо методы, чтобы избежать этих проблем?

Answer 1

Для формулы баланса в ячейке D3 используйте =C3+OFFSET(C3,-1,1) на основе вашего примера C3, имеющего сумму, и D2, имеющего предыдущий баланс. Таким образом, когда вы вставляете строку, вы можете просто ввести новое значение и скопировать формулу баланса из одной из других строк.

Используя формулу OFFSET, вам не нужно обновлять каждую ячейку в столбце баланса.

Answer 2

Когда я добавляю новые строки, я выбираю всю строку (нажимая на фактический номер строки слева от столбца A), а затем использую CTRL-D, чтобы "продублировать" строку над ней. Затем я меняю цифры и текст, который не был основан на форуме. Но вы правы, это не поможет при добавлении строк между ними, если вы используете своего рода "промежуточное значение" или "накопленное" значение, как вы описываете. Мне понравился ответ Тока, который использует SUM (C $ 2:C3), потому что он не застрахован от этой проблемы.

Answer 3

Проблема с (хорошим) решением, использующим OFFSET, заключается в том, что если вы добавите столбец (а не строку), то ваша формула больше не будет работать. Проще говоря, в этом случае OFFSET защищает вас от добавления строки, но не от добавления столбца. В качестве альтернативы можно использовать следующую формулу в D3, которую вы можете копировать столько раз, сколько хотите ниже:

=SUM(C$2:C3)

Answer 4

У вас есть несколько вариантов, возможно, одно поле Excel содержит сумму всей строки. Если вы сейчас добавите строку, это не имеет значения.

Другое решение, которое я использую, состоит в том, что сумма получает строку номер 1, и там я записываю сумму (A1:A1000), хорошо зная, что мое текущее наибольшее число - 150. Таким образом, я могу добавить строки, и они все еще учитываются.

Решение № 3 состоит в том, чтобы проверить, какие операции в какой программе заставят электронную таблицу автоматически изменять суммы. Например, в числах поле A16 содержит: sum(A1: 15), и оно автоматически изменяется на A17, содержащее сумму (A1: 16), если я добавлю строку перед строкой 15, а не строку 16! Поэтому я всегда оставляю одну строку в конце пустой и добавляю новые строки перед этим! Я думаю, это также работает в Excel и OpenOffice ...