В Excel, как я могу сложить все числа выше текущей ячейки?

Question

Я хочу, чтобы в Excel был столбец, состоящий из заголовка, набора чисел, а затем сумма этих чисел внизу. Я хотел бы, чтобы сумма адаптировалась к вводу новых чисел выше суммы. Что-то вроде этого:

Numbers
 1
 2
 5
10

18        Total

Если позже я добавлю 10 новых чисел в середину списка, я хочу, чтобы сумма автоматически включала их.

Я знаю, что функция SUM() может суммировать весь столбец, но если сумма также находится в этом столбце, то она жалуется на циклическую ссылку. Как я могу просто сложить числа выше суммы?

Answer 1

Функции ROW() и COLUMN() дадут строку и столбец текущей ячейки. Используйте их в функции ADDRESS() чтобы создать строку, представляющую диапазон от вершины текущего столбца до строки над итогом. Затем используйте функцию INDIRECT() чтобы превратить эту строку в реальный диапазон для функции SUM() . Формула для общей ячейки будет тогда:

=SUM(INDIRECT(ADDRESS(1,COLUMN())&":"&ADDRESS(ROW()-1,COLUMN())))

Поместите это в любую ячейку электронной таблицы, и она получит сумму всех чисел в столбце над этой ячейкой.

Answer 2

На самом деле, вы можете делать то, что вы хотите с обычным =SUM()

Предполагая, что ваш лист Excel имеет следующий дизайн:

\|  A  |  B  |
-+-----+-----+
1|  1  |     |
2|  2  |     |
3|  3  |     |
4|  4  |     |
5|  5  |     |
6|     |     |
7|  15 |Total|

Предполагая, что A7 =SUM(A1:A5) , вы можете добавить больше строк, как вам угодно, так как Excel соответственно расширит диапазон =SUM .

Теперь предположим, что я выбираю строки 2, 3 и 4 и вставляю строки. Результат будет следующим:

 \|  A  |  B  |
--+-----+-----+
 1|  1  |     |
 2|     |     |
 3|     |     |
 4|     |     |
 5|  2  |     |
 6|  3  |     |
 7|  4  |     |
 8|  5  |     |
 9|     |     |
10|  15 |Total|

A10 равно =SUM(A1:A8) . Таким образом, теперь вы можете вставлять новые номера по желанию.

Answer 3

Этот ответ относится только к Libra Office, но должен работать и для Excel.

По сути, если мы хотим явно указать строку / столбец, который должен быть заморожен, мы должны указать $ с этой строкой / столбцом.

* Существует также встроенный алгоритм, так что когда вы копируете формулу в другие ячейки, Libra Office автоматически определит, как ей следует изменить значения формулы.

Итак, давайте предположим, что вам нужно добавить все строки G1:GN =>, где N - номер текущей строки. Для этого вы хотите исправить строку первого значения и сохранить другие значения как. Так что формула будет:

= SUM(G $ 1:G1) -> для 1-й строки, а затем вы можете скопировать и вставить формулу в другие ячейки.

Libra Office автоматически сохранит G1 в качестве первого значения и динамически изменит 2-е значение на G2, G3 и т.д. Для каждой ячейки.

Answer 4

Хорошо, Indirect() является изменчивым ... так что, как только ваш лист станет больше, он будет только медленнее. Если вам это подходит, вам лучше использовать:

=Sum($A1:A1)

когда вы перетаскиваете эту формулу вниз, у вас всегда будет диапазон от A1 до текущей (или предыдущей и т. д.) ячейки. Нет летучих, очень быстрых, гораздо более простых!