☰

Excel 2013 で別のブックの範囲を使用して SUMIFS 数式を更新すると #value エラーが発生する

tag-icon • tag-icon microsoft-excel-2013
Excel 2013 で別のブックの範囲を使用して SUMIFS 数式を更新すると #value エラーが発生する

ローカル フォルダーに、キーと値のペアを含む 2 列のテーブルを含むソース ファイルと、次のようにソースのデータを使用する宛先ファイルがあります。

  1. 特定のセルへのリンク:

    ='C:\Temp[source.xlsx]Sheet1'!$B1

  2. 範囲から値を照会します。

    =VLOOKUP(A1,'C:\Temp[source.xlsx]Sheet1'!$A$1:$B$6,2,0)

  3. 範囲と条件を指定した SUMIFS 関数:

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

ソース ブックを開かずに宛先ブックを開くと、「このブックには ... へのリンクが含まれています」というメッセージと、「更新」および「更新しない」オプションが表示されます。このプロンプトの背景には、ファイルを閉じたときに保存された値が表示されます。

ソースファイルが閉じられたまま「更新」オプションを選択した場合、リンク（1）とクエリ（2）の正しい値が得られますが、＃価値！SUMIFS のエラー (3)。ソース ファイルを開くと、SUMIFS 値が正しく計算されます。

ソース ファイルを開かずに、「リンクの編集」ダイアログ (データ フォルダーから) で、ソース ファイルのステータスが最初に「不明」になり、「ステータスの確認」をクリックすると「OK」になり、「値の更新」をクリックすると #VALUE が引き続き表示されることに注意してください。

これは、仕事関連の状況で使用したテスト ケースです。SUMIFS 関数を持つファイルで、引数はソース ファイルを指しており、更新/更新しないのプロンプトが表示されたときには正しい値が表示されますが、選択したオプション (更新するかしないか) に関係なく、#VALUE! エラーに変わります。

当然の疑問:Excel 2013 でこのようなことが起こるのはなぜですか? また、これを解決するにはどうすればよいですか?

答え1

Excel は設計どおりに動作しています。閉じたブック内のデータを数式で読み取ることはできません。

この制限を回避するには、閉じたファイルからデータを取得するために VBA を使用する必要があります。また、「Excel 2013 閉じたブック データ」を検索して他の方法を見つけることもできます。

以下は、それを実現する方法の例です。Microsoft Excel の VBA を使用して閉じたブックから情報を読み取る:

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 の「プル」機能: 閉じたブックへの動的リンクの作成。

自分のニーズに合うようにこれを試してみる必要があります。

関連情報