Автоматическое копирование формул при добавлении строки

[ftryrjyh]

LibreOffice 7.1
MacOS 11.2.3

Столбцы таблицы
Дата Значение1 Значение2 Вычисление

Значения в Дате должны идти по порядку. Если надо вставить строку между существующих, то в новой строке нет формул, надо копировать перетаскиванием.

Можно ли при добавлении строки автоматически подставлять формулы в новую строку?
В екселе нашел как, в либре нет

Альтернативой этому могла бы послужить автоматическая сортировка по полю Дата.

[sokol92]

А как Вы в Excel поступаете?

[eeigor]

Выделить диапазон ячеек над и скопировать Ctrl+D. Рекомендуется.
Подставлять макросом при добавлении строки.
В Excel копирование происходи в умной таблице. Здесь их нет.

[SMihail]

Я так понял, что на сегодня, способа автоматического копирования формул при добавлении новой строки нет или все таки есть?

[eeigor]

Конечно, нет. Но я добавляю макросом. Однако это решение для хорошо сработанного приложения.

Код приводится для демонстрации. Взято из рабочего приложения. Я не поясняю что и зачем, разве что...
Процедура FillFormulas достаточно демократично решает Вашу задачу, беря формулы из вышестоящей строки. В основе - метод fillAuto. Hardcode практически отсутствует. Ячейки с формулами определяются автоматически. Диапазон данных при добавлении новой записи расширяется автоматически (при заданном параметре).
oDataRange - глобальная переменная, ссылающаяся на диапазон данных (например Вашу таблицу).
Функция GetDataBodyRange() - зд. не приводится - возвращает данные без первой строки с заголовками столбцов. Вы можете её легко заменить парой строчек кода.
Вам осталось только предоставить данные, а я могу нарисовать кнопку "Добавить". Вот и всё. Ну, может ещё перевести на русский несколько сообщений... Я давно уже ничего по-русски не пишу: не удобно, поскольку на других форумах - язык английский. Будет обслуживать любую таблицу, расположенную на листе произвольно. Вы задаете ссылку на именованный диапазон, а код уже работает с ним.

Код написан для реального приложения, поэтому выглядит объемным. В реальности, всю работу делает пара строчек:
   oDataRange.insertCells(oAddress, com.sun.star.sheet.CellInsertMode.DOWN  'вставляет новую строку
   oRange.fillAuto(com.sun.star.sheet.FillDirection.TO_BOTTOM, 1)  'заполняет формулы из вышележащей строки в новую строку
Обратите внимание:
  If oCell.Type = com.sun.star.table.CellContentType.FORMULA Then...
То есть ячейки с формулами определяются без Вашего участия.
Процедура InitObjects инициализирует глобальную переменную при открытии файла.
В примере "YourData" - это имя ваше таблицы, которое Вы определяете сами.

Код Выделить Развернуть


Const DATA = "YourData"
Global oDataRange As Object


Sub InitObjects()          
''' Called by: Document.Document_OnLoad

On Local Error GoTo HandleErrors
With ThisComponent.NamedRanges
oDataRange = .getByName(DATA).ReferredCells
End With
Exit Sub

HandleErrors:
Msgbox "Error " & Err & " in line " & Erl & ": " & Error _
, MB_ICONSTOP, "macro:InitObjects"
End Sub

Sub AddNew()
''' Add a new record to the DATA range and set 1st cell of the added row active.
''' Called by: cmdAddNew_actionPerformed
''' Calls: ActivateCell, FillFormulas, Utils.GetDataBodyRange()
''' Requires: Document.PASSWORD

On Local Error GoTo HandleErrors
Dim nRow&  'new row index
Dim oSheet As Object, oCell As Object
Dim oAddress As New com.sun.star.table.CellRangeAddress
Dim bProtected As Boolean

oSheet = ThisComponent.CurrentController.ActiveSheet
nRow = oDataRange.RangeAddress.EndRow + 1
With oAddress: .Sheet = oSheet.RangeAddress.Sheet
.StartColumn = oDataRange.RangeAddress.StartColumn
.StartRow = nRow
.EndColumn = oDataRange.RangeAddress.EndColumn
.EndRow = .StartRow
End With
With oSheet
bProtected = .isProtected()
If .isProtected() Then .unprotect(PASSWORD)
.insertCells(oAddress, com.sun.star.sheet.CellInsertMode.DOWN)
Call FillFormulas(oAddress)

With Utils.GetDataBodyRange(oDataRange)
' If the 2nd record was inserted, expand the reference to DATABODY.
If .Rows.Count = 2 Then
ThisComponent.NamedRanges.getByName(DATABODY).Content = .AbsoluteName
End If
End With

If bProtected Then .protect(PASSWORD)
oCell = .getCellByPosition(oDataRange.RangeAddress.StartColumn, nRow)
End With
Call ActivateCell(oCell)
Exit Sub

HandleErrors:
Msgbox "Error " & Err & " in line " & Erl & ": " & Error _
, MB_ICONEXCLAMATION, "macro:AddNew"
End Sub

Sub FillFormulas(oAddress As Object)
''' Transfer only formulas from the record above.
''' If this is the first record, display the message and do nothing.
''' Called by: AddNew

Dim oSheet As Object, oRange As Object, oCell As Object
Dim oPrevDataRow As Object
Dim j%

oSheet = ThisComponent.CurrentController.ActiveSheet
' Get the previous record (must be filled in).
With oAddress: oSheet = ThisComponent.Sheets(.Sheet)
If .StartRow = oDataRange.RangeAddress.StartRow + 1 Then
MsgBox "This is the first record added. If the record contains" _
& " calculated fields, insert the appropriate formulas into them" _
& " using relative (mixed) references. Then these formulas" _
& " will be automatically transferred from the last record" _
& " to the new one when it is added." & Chr(10) _
& Chr(10) & "This message will not appear later.", , "Adding 1st Record"
Exit Sub
End If
oPrevDataRow = oSheet _
.getCellRangeByPosition(.StartColumn, .StartRow - 1, .EndColumn, .StartRow - 1)
End With
' Fill in the formulas in the last record (just added) from the previous one.
For j = 0 To oPrevDataRow.Columns.Count - 1
oCell = oPrevDataRow.getCellByPosition(j, 0)
If oCell.Type = com.sun.star.table.CellContentType.FORMULA Then
With oCell.CellAddress
oRange = oSheet _
.getCellRangeByPosition(.Column, .Row, .Column, .Row + 1)  'block of 2 cells vertically
oRange.fillAuto(com.sun.star.sheet.FillDirection.TO_BOTTOM, 1)  'nSourceCount:=1
End With
End If
Next
End Sub  'FillFormulas

Вообще, в Calc'е работать гораздо сложнее, чем в Excel: квалификация должна быть выше. Однозначно!

На скриншоте три поля справа заполнены процедурой выше.

[SMihail]

Спасибо, попробую разобраться!!!

[eeigor]

Там ещё есть вызов (выбирает первую ячейку, но не выделяет её):

Код Выделить Развернуть

Sub ActivateCell(oCell As Object)
''' Called by: AddNew

Dim oRanges As Object
With ThisComponent
.CurrentController.select(oCell)
oRanges = .createInstance("com.sun.star.sheet.SheetCellRanges")
.CurrentController.select(oRanges)
End With
End Sub  'ActivateCell

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

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

Диапазон данных при добавлении новой записи расширяется автоматически (при заданном параметре).

Referred to the Calc Guide v. 7.1 (p. 477):

EN: Expand references when new columns/rows are inserted specifies whether to expand references when inserting columns or rows adjacent to the reference range. This is only possible if the reference range, where the column or row is inserted, originally spanned at least two cells in the desired direction.

RU: Параметр "Расширять ссылки при вставке новых столбцов/строк" указывает, следует ли расширять ссылки при вставке столбцов или строк, смежных с данным диапазоном. Это возможно только в том случае, если этот диапазон, в который вставлен столбец или строка, изначально охватывает не менее двух ячеек в желаемом направлении.

Код Выделить Развернуть

   With Utils.GetDataBodyRange(oDataRange)
       ' If the 2nd record was inserted, expand the reference to DATABODY.
       If .Rows.Count = 2 Then
           ThisComponent.NamedRanges.getByName(DATABODY).Content = .AbsoluteName
       End If
   End With

Ну, вот: увидел ссылку на константу DATABODY, о которой не сказал.
Присылайте файл, как я написал.

В моём примере (и в жизни тоже) ситуация усложняется ещё и тем, что использование расширенного фильтра требует наличия строки заголовков полей в именованном диапазоне данных, а в именованный диапазон, который традиционно обрабатывают формулами, наоборот, не надо включать строку заголовков. Это тоже надо учитывать. Отсюда у меня: DATA - данные с заголовками и DATABODY - тело данных без заголовков.
Но я уже не могу представить "серьёзную" таблицу данных без диапазона критериев расширенного фильтра сверху. Значит, заголовки то нужны, то нет.
Откровенно говоря, я не уверен, что Вы вполне понимаете меня...

[eeigor]

Вот готовый пример. Пользуйтесь.
В модуле файла задана константа:

Const DATA = "Цветы"

Если Вы измените имя диапазона данных на близкое Вам по тематике, то измените значение константы DATA.
Но сначала изучите пример.
Если Вы перетащите диапазон данных в другое место, то всё будет по-прежнему работать. Решение универсальное. Код модуля является базовым (необходимым и достаточным для решения задачи). Дальше можно наращивать код "под себя".
Защита листа закомментирована (см. процедуру Document_OnLoad).

[SMihail]

ОГРОМНОЕ СПАСИБО!!!    РАЗБИРАЮСЬ!!!

[SMihail]

Немного разобрался (если так можно сказать), но не получилось поставить макрос на панель задач (при выполнении выдает ошибку). ИЛИ если можно, чтоб кнопка добавления строки автоматическим смещалась вниз (идеальный вариант), при добавлении новой строки.

[eeigor]

Ну, так переместите кнопку под таблицу: при добавлении новой записи (путём вставки диапазона ячеек) всё, что ниже, будет смещаться вниз на одну строку.
И ещё. Надо ставить защиту листа, чтобы не затереть формулы.
Я в примере кое-что упустил.
Выделите в диапазоне данных все ячейки, кроме строки заголовков столбцов, а также кроме ячеек с формулами, и снимите с них защиту, чтобы можно было вводить данные на защищённом листе.
После этого раскомментируйте строку в процедуре Document_OnLoad, чтобы защита листа всегда устанавливалась при открытии файла. Вы всегда можете снять защиту листа вручную в "текущем сеансе" работы с данными и установить обратно (без пароля).
Добавляемая строка будет копировать формат сверху: одни ячейки без защиты, ячейки с формулами – с зашитой.


UPDATED:
Пример с учётом вышесказанного прилагается...

Файл обновлён повторно. Загрузите ещё раз. Переведено на русский полностью. Исправлены ошибки. Хорошая заготовка для старта.

[eeigor]

Подключил два диапазона данных, расположенных на разных листах, к одной процедуре добавления новой записи.
Оба именованных диапазона объявлены с глобальной областью видимости, но их также можно сделать и локальными (на листах), поскольку процедура добавления записи работает с текущим листом.
См. диалог "Управления именами".

[SMihail]

  Вы прочитали мои мысли !!! Только хотел это Вам написать, захожу на форум, а вы уже предложили файл. Спасибо!!!

[Дмитрий 02]

Добрый день! Проблема в последующем, почему по двум позициям,после изменения %, первая строчка правильно показывает, а у остальных суммы прежние,как сделать так, чтобы и в остальныз строчках сумма поменялась? Спасибо