Использование уже существующей функции для новой строки

Question

У меня есть документ Excel, который содержит X столбцов и N количество строк.

Самый последний столбец строки выполняет SUM первых столбцов X-1. У меня проблема в том, что пользователь этого документа Excel постепенно добавляет строки в документ, и из-за этого функция еще не существует в последнем столбце для новых строк.

Мне нужен способ, чтобы эта функция существовала в новых строках динамически (пользователь не разбирается в Excel и не имеет возможности просто перетащить функцию вниз на строку).

Answer 1

К сожалению, пользователь, который не может делать базовые вещи, довольно распространен. Вы можете прибегнуть к чему-то, что полностью переписывает формулу (что, по общему признанию, излишне), но посмотрите, сможете ли вы адаптировать эту идею в событии Worksheet_SelectionChange. Или, возможно, включение макросов также сбивает их с толку.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 LastRow = ActiveSheet.UsedRange.Rows.Count
 Range("D2:D" & LastRow).Formula = "=SUM(INDIRECT(""A""&ROW()&"":C""&ROW()))"
End Sub

для промежуточных итогов

Range("D2:D" & LastRow).Formula = "=SUBTOTAL(9,(INDIRECT(""A""&ROW()&"":C""&ROW())))"

Answer 2

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

Answer 3

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

Например, =IF(LEN(A1)=0,"",A1+B1) покажет пустую ячейку, если A1 не содержит данных, но выполнит функцию A1+B1, если это произойдет. Если вы перетащите его ниже вашей последней строки, столбец будет пустым, пока данные не будут добавлены в следующую ячейку в столбце A.

Существует несколько других формул, которые вы можете использовать для проверки используемых ячеек, например =ISBLANK(A1) ИЛИ =ISTEXT(A1) зависимости от того, как вы хотите проверить данные. Я думаю, что с функцией LEN проще всего работать. Вы также можете сделать что-то вроде этого =IF(LEN(A1)+LEN(B1)=0,"",A1+B1) который будет запускать формулу, если в ячейке есть данные, или =IF(OR(LEN(A1)=0,LEN(B1)=0),"",A1+B1) который будет запускать формулу, только если в обеих ячейках есть данные.

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