Сумма Google Spreadsheet, которая всегда заканчивается на ячейке выше

Ответ 1

Вы, вероятно, ищете формулу как:

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

Google Spreadsheet INDIRECT возвращает ссылку на ячейку или область, в то время как - из того, что я помню - Excel INDIRECT всегда возвращает ссылку на ячейку. Учитывая, что Google INDIRECT действительно испытывает определенные трудности, когда вы пытаетесь использовать его внутри SUM в качестве ссылки на ячейку, вам нужно заполнить SUM целым диапазоном, который будет суммироваться, например, в записи a1: "B1: BX".

Вы получите нужный адрес таким же образом, как и в EXCEL (обратите внимание, что здесь "4" для относительной строки/столбца, по умолчанию Google INDIRECT возвращает абсолютное значение):

ADDRESS(ROW()-1,COLUMN(),4)

и затем использовать его для подготовки строки диапазона для функции SUM путем объединения с начальной ячейкой.

"B1:"&

и оберните его с помощью INDIRECT, который вернет область для суммирования.

ОБРАЩАЯСЬ К НИЖЕ ОТВЕТА от Druvision (я пока не могу комментировать, я не хотел умножать ответы)

Вместо трудоемких корректировок формул каждый раз вставляется/удаляется строка, чтобы все выглядело так:

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

Вы можете сэкономить один столбец на отдельном листе для хранения переменных (пусть назовите его "def"), скажем, Z, чтобы определить начальные точки, например, в Z1 напишите "B1" в Z2, напишите "B11" и т.д., А затем используйте его как переменную в Ваша сумма с помощью INDEX:

SUM(INDIRECT(INDEX(def!Z:Z,1,1)&":"&ADDRESS(ROW()-1,COLUMN(),4))) - суммы от B1 до вычисляемой строки, поскольку в Z1 мы имеем "B1" (1,1 в INDEX(...,1,1))

SUM(INDIRECT(INDEX(def!Z:Z,2,1)&":"&ADDRESS(ROW()-1,COLUMN(),4))) - суммы от B11 до вычисляемой строки, поскольку в Z2 мы имеем "B11" (2,1 в INDEX(...,2,1))

пожалуйста, обратите внимание:

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

2. Запись "Z: Z" - целый столбец. Вы сказали, что у вас было много таких формул;)

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

Кстати, не проще ли написать собственную функцию/скрипт, суммирующую все строки над ячейкой? Если вы хотите использовать javascripting, то, насколько я помню, в электронной таблице Google теперь есть хороший редактор скриптов. Вы можете создать функцию с именем, например, sumRowsAboveMe(), а затем просто использовать ее на своем листе, например =sumRowsAboveMe() в ячейке листа.

Примечание. Возможно, вам придется заменить запятые точкой с запятой

Ответ 2

Примечание После проверки этого ответа он будет работать, только если сумма находится в другом столбце из-за ошибки круговой зависимости. В противном случае решение действительно.

Это немного алгебра, но мы можем использовать перетаскивание нижнего правого прямоугольника электронных таблиц.

=SUM(X:X) - SUM(X2:X)

Где X - столбец, с которым вы работаете, а X2 - конечная точка. Перетащите формулу вниз, а Sheets увеличит X2, изменив конечную точку.

* Вы упомянули, что у вас было десятки таких расчетов. Поэтому, чтобы соответствовать вашей конкретной потребности, мы бы вычитали ваше последнее суммирование, чтобы получить тот "средний" диапазон, который мы хотели.

например.

B1..B9 следует суммировать на B10, а B11..B19 следует суммировать на B20

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

=SUM(B:B) - SUM(B9:B) //Formula on C10 (Sum of B1..B9)
=SUM(B:B) - SUM(B19:B) - B10 // Formula on C20 (Sum of B11..B19)

Ответ 3

Это основано на @PsychoFish, вот решение:

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

Просто замените "3:" для начала строки.

@PsychoFish является правильным, но его нельзя перетаскивать и копировать, поскольку столбец является буквальным и жестко закодированным, а @Druvision - в правильном направлении, но он ошибался... в основном закончилась одна и та же проблема, связанная с повторным вводом диапазонов, а затем сползая формулы снова и снова.

Ответ 4

Общий синтаксис:

=SUM(INDIRECT(cell_reference_as_string1 &":"& cell_reference_as_string2)

например:

cell_reference_as_string1 = ADDRESS(ROW(),COLUMN(),4)
cell_reference_as_string2 = ADDRESS(ROW()-1,COLUMN(),4)

Ответ 5

Мне нравится, как @abernier описывает общее решение. До сих пор используются только алфавитные обозначения A1 (A - первый столбец, 1 - первая строка). Меня это смущает, особенно когда я думаю о количестве столбцов, оставшихся от другого столбца. Мне гораздо лучше нравится основанная на числах запись R1C1. Для использования R1C1 обозначения для КОСВЕННОГО, вам нужно передать FALSE следующим образом:

=SUM(INDIRECT("R1C"&COLUMN()&":R"&(ROW()-1)&"C"&COLUMN(), FALSE))

Я надеюсь, вы тоже найдете это полезным.

Ответ 6

Вы, ребята, делаете это сложнее, чем вам нужно. Я просто оставляю пару пустых строк выше по строке "sum" (вы можете отформатировать их, чтобы их заполнили цветом или чем-то, чтобы они не были случайно использованы), а затем просто добавьте новые строки чуть выше этих специальных строк.

Ответ 7

Ответ @PsychoFish привел меня правильно. Единственная проблема, которую мне пришлось переписать формулу снова из каждого столбца и каждой суммы. Итак, вот улучшенная формула, которая суммирует предыдущие 9 ячеек в одном столбце, без жесткого кодирования номеров столбцов или строк:

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

Единственная проблема заключается в том, что мне пришлось переписать формулы, если кто-то добавляет или удаляет строку. В этом случае я должен изменить от 9 до 10 или 8 соответственно.