У меня есть таблица Excel со значениями (эта таблица создана с помощью специального скрипта Perl, но я не думаю, что проблема в этом). В ней у меня есть формула:
=sum(indirect(concatenate(address(6,column()),":",address(17,column()))))
Цель этой формулы — дать мне SUM()ячейки в текущем столбце, между строками 6 и 17. ВЭлектронная таблица Gnumeric, как только я открываю файл, это работает. Но в Excel (оба 2003и2007), при открытии файла выдаются #VALUE!ошибки в полях с этой формулой, указывающие на то, что INDIRECTвызов со значениями $B$6:$B$17приведет к ошибке.
Вот в чем загвоздка. Если я редактирую поле (через F2), и делаюнетизменения, и нажимаю Enter, значения обновляются. Также, кажется, если я сохраняю файл как .xlsx (формат Excel 2007), значения обновляются при открытии. К сожалению, я не уверен, что создание возможно xlsxс модулями, которые я использую, и многие из наших клиентов, вероятно, не смогли бы использовать его в любом случае.
Есть предложения? Редактировать более 200 файлов каждый месяц для каждого клиента нецелесообразно, поэтому если я что-то упускаю, пожалуйста, дайте мне знать.
решение1
Если вставить простую функцию Sum, будет ли она работать? То есть можно ли отследить проблему до компонента косвенной/адресной комбинации?
Вы пробовали использовать OFFSET для создания диапазона для суммирования? Например:
=SUM(OFFSET(B6,0,0,12,1))
решение2
Возможно, вы цепляетесь за соломинку, но можете попробовать ссылку на стиль R1C1
=SUM(INDIRECT("R["&7-ROW()&"]C:R["&17-ROW()&"]C",FALSE))
Или, если формулы всегда находятся в строке 19, установите стиль ссылки на R1C1.
Application.ReferenceStyle = xlR1C1
Затем вы можете вставить формулу как
=SUM(R[-13]C:R[-2]C)
Обновлять:
Вы можете избежать необходимости устанавливать ReferenceStyle, используя
<YourCellReference>.FormulaR1C1 = "=SUM(R[-13]C:R[-2]C)"
решение3
Возможно, вы могли бы принудительно пересчитать всю электронную таблицу следующим образом:
http://msdn.microsoft.com/en-us/library/bb687891.aspx
Макрос переименования, а затем повторного переименования Spreadsheet должен работать нормально. Может быть, вы могли бы выполнить его в конце PearlScript? Я действительно не слишком много знаю о Pearl, извините.
Или используйте горячую клавишу CTRL+ALT+F9 и, если это сработает, используйте VBA с этой функцией:Application.CalculateFull
решение4
В Excel Tools | Calculationsможно установить режим расчета. Если он установлен на «Автоматический», Excel должен автоматически обновлять результаты формул всех полей, в том числе при открытии файла.
Настройки расчета также можно задать для отдельных рабочих листов, поэтому, возможно, скрипт Perl устанавливает режим ручного обновления.