Excel неправильно пересчитывает значения

Question

У меня есть лист Excel со значениями в нем (этот лист создается с помощью специального сценария Perl, но я не думаю, что именно в этом проблема). В нем у меня есть формула:

=sum(indirect(concatenate(address(6,column()),":",address(17,column()))))

Цель этой формулы - дать мне SUM() ячеек в текущем столбце между строками 6 и 17. В Gnumeric Spreadsheet это работает, как только я открываю файл. Но в Excel (и 2003, и 2007) открытие файла дает #VALUE! ошибки в полях с этой формулой, утверждая, что вызов INDIRECT со значениями $B$6:$B$17 приведет к ошибке.

Вот излом в вопросе. Если я отредактирую поле (через F2), не внесу изменений и нажму клавишу ввода, значения обновятся. Кроме того, кажется, что если я сохраняю файл в формате .xlsx (формат Excel 2007), значения обновляются при открытии. К сожалению, я не уверен, что создание xlsx возможно с теми модулями, которые я использую, и многие из наших клиентов, вероятно, не смогут его использовать в любом случае.

Какие-либо предложения? Редактирование более 200 файлов каждый месяц для каждого клиента не представляется возможным, поэтому, если что-то мне не хватает, пожалуйста, дайте мне знать.

Answer 1

Если вы вставите ванильную функцию Sum, это сработает - т.е. можно ли отследить проблему до компонента комбинации косвенного / адреса?

Вы пытались использовать OFFSET, чтобы создать диапазон для суммирования? Например:

=SUM(OFFSET(B6,0,0,12,1))

Answer 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)"

Answer 3

Если вы используете WriteExcel для сценариев Perl, обратите внимание на это с предложением в статье. парсинг функций INDIRECT & CONCATENATE и смешивания ошибок формулы SUM() и INDIRECT()

К сожалению, анализатор Spreadsheet::WriteExcel не может обрабатывать подобные формулы. Spreadsheet::WriteExcelXML может обработать их, хотя, если вы можете использовать это.

Answer 4

Может быть, вы могли бы принудительно пересчитать всю таблицу следующим образом:

http://msdn.microsoft.com/en-us/library/bb687891.aspx

Переименование макроса, а затем снова переименование электронной таблицы должно работать нормально. Может быть, вы могли бы выполнить в конце Pearlscript? Я действительно не знаю слишком много о Перл, извините.

Или используйте горячую клавишу CTRL+ALT+F9 и, если она работает, используйте VBA с этой функцией: Приложение.CalculateFull

Answer 5

Под инструменты Excel Tools | Calculations вы можете установить режим расчета. Если установлено «Автоматически», Excel должен обновить результаты формулы всех полей автоматически, также при открытии файла.

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