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

Question

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

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

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

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

   = ВПР (A1, 'C:\Temp [source.xlsx] Лист1'!$ A $ 1: $ B $ 6,2,0)

3. Функция SUMIFS с диапазоном и условиями:

   = SUMIFS ('C:\Temp [source.xlsx] Лист1'!$ B $ 1: $ B $ 6, 'C:\Temp [source.xlsx] Лист1'!$ A $ 1: $ A $ 6, D1),

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

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

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

Это тестовый пример, который я использовал для рабочей ситуации: файл с функцией SUMIFS с аргументами, указывающими на исходный файл, который показывает правильное значение при появлении запроса на обновление / не обновлять, но изменяется на #VALUE! ошибка, независимо от того, какую опцию я выбрал (обновить или не обновлять)

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

Answer 1

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

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

Вот пример того, как выполнить это из чтения информации из закрытой книги с использованием 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»: создание динамических ссылок на закрытые книги.

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