MATCH関数の結果に+１したときの#N/Aエラー

Question

(A) TestA.xlsx

(B) TestB.xlsx

(C) TestC.xlsx

という3つのブックがあります。3つのブックは同じフォルダにあります。

(C)にはシートAとBがあり、下図の内容です。

　

(A)は下図の内容で、B2セルにINDEXとMATCHを使って、(C)のシートAのりんごの10月の値を参照しています。

「予算」 の値を 取得するために、MATCHの結果に+1しています。

同様に(B)は下図の内容で、同じくB2セルには(C)のシートBの10月「予算」の値を取得するために、MATCHの結果に+1しています。

(A)(B)(C)全てのブックを開いているときは問題ないのですが、(A)と(B)だけ開いたときに予期しない問題が発生します。

この例では(B)のセルB2に#N/Aが表示されます。

(A)にはエラーは表示されません。

なぜでしょうか？(C)を開かなくてもエラーが出ないようにするにはどうしたら良いでしょうか？

以下、補足情報です。

• (C)のシートAとシートBの並びを入れ替えると、(B)は正常で(A)で#N/Aエラーが表示されます
• MATCH(...)の右に+1を付けなければ、問題は発生しません(+0でもエラーが出ます)
• INDEXの前に@を付けると、問題は発生しません
• (A)も(C)も開かず、(B)だけ開いたときは、 問題は発生しません
• Excelのバージョン情報→Microsoft® Excel® for Microsoft 365 MSO (バージョン 2402 ビルド 16.0.17328.20550) 64 ビット

Answer 1

キャプチャー画面を添付します。

よろしくお願いいたします。

Answer 2

テストしていただき、ありがとうございます。

念のため確認させてください。

> (A)(B)(C)全てのブックを開いているときは問題ないのですが、(A)と(B)だけ開いたときに予期しない問題が発生します。

および

> (A)も(C)も開かず、(B)だけ開いたときは、 問題は発生しません

の条件ですが、お間違いないでしょうか？

例えば、(B)だけ開いたときは問題ないのですが、その状態で(A)を開くと、私のところでは(B)にエラーが表示されます。

また、(A)(B)(C)を全て開いている状態から(C)を閉じた後も問題はありません。

Answer 3

申し訳ありません。

テストしてみました。

TestC.xlsx を開かないと下記のメッセージが表示されますが問題なく計算されます。

また、コンテンツの有効化をすると以降下記のメッセージボックスが表示されますが問題なく計算されます。

数式は下記のようになっています。

TestC.xlsx を開いている場合、

=INDEX([TestC.xlsx]A!$B3:$D4,MATCH(A2,[TestC.xlsx]A!$A3:$A4,0),MATCH(B1,[TestC.xlsx]A!$B1:$D1,0)+1)

TestC.xlsx を開いていない場合（自動的に変更されました）、

=INDEX('F:\Test[TestC.xlsx]A'!$B3:$D4,MATCH(A2,'F:\Test[TestC.xlsx]A'!$A3:$A4,0),MATCH(B1,'F:\Test[TestC.xlsx]A'!$B1:$D1,0)+1)

TestB.xlsx も同様です。

ちなみに私の環境は下記のとおりです。

Windows 11 Pro 24H2

Microsoft® Excel® 2019 MSO (バージョン 2409 ビルド 16.0.18025.20030) 64 ビット

以上です。私の環境では再現しませんでした。

追記） 2024 10/10 6:10

上記の警告エラーですが

　「セキュリティの警告」は[トラスト センター]の[信頼できる場所]にブックを置けば最初から表示されなくなりました。

　リンクの更新する／更新しないのメッセージボックスは下記のように「常に更新する」にすると表示されなくなりました。但し常に更新するために何も変更せず閉じる時でも「保存しますか？」の確認をされます。

[データ]タブ

→ [クエリと接続]グループの[ブックのリンク] 

  ブックのリンクが表示されるので[歯車]アイコンをクリックする。 

→ [常に更新する]をチェックする。 

以上で私の環境では特に問題なく動作しています。

Answer 4

motosan さま

ありがとうございます。

リンク先を読みましたが、日本語訳が不自然なためか、うまく理解できませんでした。

私が貼り付けた最後の図は、まさに

> ソース ブックが開かない場合、コピー先ブックのブック リンクにはパス全体が含まれます。

の状態でフルパスで表示されていますが、そのフルパスの場合にこそ問題が発生するのです。

> ソース ブックが開いている場合、

には問題が発生しません。

問題が発生する状況では、わざわざ「フルパスで指定してみる」必要がないのでどうしようもない状態です。

Answer 5

Janequin_217 さま

下記のドキュメントにあるようにフルパスで指定してみたらどうでしょうか？

ブック リンクを作成する - Microsoft サポート

https://support.microsoft.com/ja-jp/office/%E3%83%96%E3%83%83%E3%82%AF-%E3%83%AA%E3%83%B3%E3%82%AF%E3%82%92%E4%BD%9C%E6%88%90%E3%81%99%E3%82%8B-c98d1803-dd75-4668-ac6a-d7cca2a9b95f

この中の「別のブックへのブック リンクの外観 」に例があります。

=SUM('C:\Reports[Budget.xlsx]Annual'!C10:C25)

申し訳ありませんが私は試していません。

ご参考になれば幸いです。