☰

Ошибка #value при обновлении формул SUMIFS с диапазоном из другой книги в Excel 2013

tag-icon • tag-icon microsoft-excel-2013
Ошибка #value при обновлении формул SUMIFS с диапазоном из другой книги в Excel 2013

У меня в локальной папке есть исходный файл с таблицей из двух столбцов с парами ключ-значение и целевой файл, который использует данные из источника следующим образом:

  1. ссылка на конкретную ячейку:

    ='C:\Temp[source.xlsx]Лист1'!$B1

  2. запросить значение из диапазона:

    =ВПР(A1;'C:\Temp[исходный.xlsx]Лист1'!$A$1:$B$6;2;0)

  3. Функция СУММЕСЛИМН с диапазоном и условием:

    =СУММЕСЛИМН('C:\Temp[исходный.xlsx]Лист1'!$B$1:$B$6,'C:\Temp[исходный.xlsx]Лист1'!$A$1:$A$6,D1)

При открытии целевой книги без открытия исходной книги я получаю сообщение «Эта книга содержит ссылки на ...» с опциями «Обновить» и «Не обновлять». На заднем плане этого приглашения я вижу значения, сохраненные при закрытии файла.

Если исходный файл остается закрытым и я выбираю опцию «Обновить», я получаю правильные значения для ссылки (1) и для запроса (2), но#ЦЕНИТЬ!ошибка для SUMIFS (3). Если я сейчас открою исходный файл, то значение SUMIFS будет рассчитано правильно.

Обратите внимание, что - без открытия исходного файла - в диалоговом окне «Изменить ссылки» (из папки «Данные») я сначала получаю статус «неизвестно» для исходного файла, затем «ОК» после нажатия «проверить статус» и по-прежнему #ЗНАЧЕНИЕ после нажатия «обновить значения».

Это тестовый случай, который я использовал для рабочей ситуации: файл с функцией СУММЕСЛИМН с аргументами, указывающими на исходный файл, который показывает правильное значение при запросе на обновление/не обновление, но изменяется на ошибку #ЗНАЧ! независимо от выбранного мной варианта (обновлять или не обновлять).

Очевидные вопросы:почему Excel 2013 делает это и как это решить?

решение1

Excel работает как задумано. Он не позволяет формулам читать данные в закрытых книгах.

Чтобы обойти это ограничение, вам нужно будет использовать VBA для извлечения данных из закрытых файлов. Вы также можете выполнить поиск по запросу "excel 2013 closed workbook data", чтобы найти другие методы.

Вот пример того, как это сделать:Чтение информации из закрытой книги с помощью VBA в Microsoft Excel:

Sub GetDataFromClosedWorkbook()
Dim wb As Workbook
    Application.ScreenUpdating = False ' turn off the screen updating
    Set wb = Workbooks.Open("C:\Foldername\Filename.xls", True, True) 
    ' open the source workbook, read only
    With ThisWorkbook.Worksheets("TargetSheetName")
        ' read data from the source workbook
        .Range("A10").Formula = wb.Worksheets("SourceSheetName").Range("A10").Formula
        .Range("A11").Formula = wb.Worksheets("SourceSheetName").Range("A20").Formula
        .Range("A12").Formula = wb.Worksheets("SourceSheetName").Range("A30").Formula
        .Range("A13").Formula = wb.Worksheets("SourceSheetName").Range("A40").Formula
    End With
    wb.Close False ' close the source workbook without saving any changes
    Set wb = Nothing ' free memory
    Application.ScreenUpdating = True ' turn on the screen updating
End Sub

Вот еще один пример -Функция Excel «Pull»: создание динамических ссылок на закрытые рабочие книги.

Вам придется поэкспериментировать с этим, чтобы подобрать вариант, соответствующий вашим собственным потребностям.

Связанный контент