EXCEL 別ブックの特定のシートにリンクする

質問

お世話になります。田中と申します。

A.xlsxというブックがあります。
このA.xlsｘのA1セルにサーバー上にある別ブックの[売上げデータ.xlsx]の"田中"というシートのC9の数字にリンクを張るために下記の数式でリンクを張りました。
='\\XXX.XXX.XXX.X\共有\営業別\[売上げデータ.xlsx]田中'!C9

この数式の'田中'部分を、A.xlsxのD4を参照するようにしたいのですが、どうすれば出来るでしょうか？あとリンク先が存在しない場合にはブランク表示にしたいのです。

どなたかお知恵をお借りできませんでしょうか？
よろしくお願い致します。

環境　EXcel2013

回答No.1

aokii · ベストアンサー

数式の'田中'部分を選んで、A.xlsxのD4をクリック
リンク先が存在しない場合にはブランク表示にするには、=IF(ISNA(数式),"",数式)

お礼

aokiiさん、返信が遅くなりまして大変申し訳ございませんでした。
諸事情によりPCを開けない環境におりましたため、返信が出来ませんでした。
大変申し訳ございませんでした。

前述の諸事情によりましてまだ試せていないのですが、明日以降ご教授いただきました
方法にてためさせていただきます。

このたびはお礼が遅くなりまして本当に申し訳ございませんでした。

補足

aokiiさん、早速のご回答ありがとうございます。
ブランク表示はご教授いただきました通りの方法でうまくいきました！
ありがとうございました。

[数式の'田中'部分を選んで・・]はいくらD4をクリックしても数式は'田中'のままです・・
なぜなのでしょうか？？

回答No.4

real beatin（@realbeatin）

＃３です。

すみません、＃３に書かれた内容は間違いでした。
全面的に撤回します。＃３のようなやり方では出来ないってこと、
大昔から知っていた筈なんですが、自分でも謎な大チョンボでした。
投稿後すぐに気が付いていたのですが、
代替え案を検討するのに時間が掛かりました。

要は参照先シート名をＤ４セルの文字列に連動するような数式を設定したい、
という理解でいますが、＃３の方法では、参照先ブックを開かないと
正しい値を返しません。
他に何とか出来ないか考えたのですが、
数式ということだと無理なようです。

こういう答えで解決できる課題なのか、
こちらでは判らない面もあるのですが、
他にいい方法が思い付かない、ということもあって、
マクロの提案という形でお応えすることにしました。

条件がいくつかあります。
●「A.xlsxのD4」セルの値変動は数式（関数）によるものではないこと
●既存の「A.xlsx」を新たなマクロ有効ブック「A.xlsm」として保存し、
　以後（マクロ部分以外は全く同じ内容の上書き相当の）新しいブックを使うこと
●「A.xlsxのA1」セルには数式は置かず、
　専ら、マクロの処理によって値を変更すること
●[売上げデータ.xlsx]の指定するシートのＣ９セルがブランクだった場合、
　「A.xlsxのA1」セルはブランクではなく、数値 0 が返ること

以上の問題点を確認する意味でも一度、マクロ有効ブックを試して貰えれば、
と思います。
（ダメだと思ったらブックを削除するだけで、元のブックはそのままですから）

まず、
シートの下部で、対象シートのシート見出しを右クリック。
ポップアップから[コードの表示]を選択
アクティブになったＶＢＡ編集画面に以下の記述（ここから～ここまで）
をコピーして貼り付ける。

' ' /// ここから
Private Sub Worksheet_Change(ByVal Target As Range)
' ' 値を取得する外部ブックへの参照文字列（シングルクオートは無し！）
Const ブックパス = "\\XXX.XXX.XXX.X\共有\営業別\[売上げデータ.xlsx]"　'　←　◆正しく指定
' ' 外部ブックで値を取得するセルのアドレスを[A1形式]で指定
Const セル番地 = "C9"　'　←　◆"C9"の部分は書換え可能

Dim v
Dim sRef As String

' ' Ｄ４セルが入力モードを経て確定し、値が変更された時だけ以降の処理に進む
　　If Intersect(Target, Range("D4")) Is Nothing Then Exit Sub　'　←　◆"D4"の部分は書換え可能

' ' セル参照を[A1形式]から[R1C1形式]へ変換
　　sRef = Application.ConvertFormula(セル番地, xlA1, xlR1C1, xlAbsolute)

　　On Error Resume Next　'　参照先が見つからずエラーになっても中断しない
' ' Excel 4.0 マクロを介して、開いていない外部ブックの値を取得
　　v = Application.ExecuteExcel4Macro("'" & ブックパス & Target.Value & "'!" & sRef)
　　On Error GoTo Out_

' ' 以降、セルの値を変更する為、再びこのマクロが呼び出されないようイベント休止
　　Application.EnableEvents = False

' ' 取得した外部ブックの値をＡ１セルに返す
　　With Range("A1")　'　←　◆"A1"の部分は書換え可能
　　　　If VarType(v) = vbError Then　'　外部ブックの値取得に失敗した場合
　　　　　　.ClearContents　'　←　Ａ１セルの値を消去「リンク先が存在しない場合にはブランク」
　　　　Else
　　　　　　.Value = v　'　←　取得した外部ブックの値をＡ１セルに返す
　　　　End If
　　End With
Out_:
　　Application.EnableEvents = True　'　イベント再開
　　If Err.Number Then MsgBox Err & vbLf & Err.Description, vbExclamation, "エラー終了"
End Sub
' ' /// ここまで

ここまで済んだら、ＶＢＡ編集画面を閉じ、
[名前を付けて保存]ダイアログを表示させ、
ブック名には触れずに、[ファイルの種類]をクリックして、
[Excel マクロ有効ブック（*.xlsm）]を選択し、そのまま[保存]
元が「A.xlsx」であったら「A.xlsm」というのが新しいマクロ有効ブックです。

以上で準備が済んだので、テストしてみて下さい。
「A.xlsxのD4」セルに
[売上げデータ.xlsx]のシート名、または存在しないシート名、を入力して、
Ａ１セルの値の変化を確かめてください。

なお、[売上げデータ.xlsx]へのパスを始めとして、
参照を正しく修正する必要がある行に◆マークを付けてありますので、
確認してからテストするようにしてください。

私からの提案は以上です。
改めて＃３の間違いについては申し訳ないです。
この回答がお役に立てることを祈りますが、もし条件が合わないようでしたら
ごめんなさい。

失礼しました。

お礼

realbeatinさん、返信が遅くなりまして大変申し訳ございませんでした。
諸事情によりPCを開けない環境におりましたため、返信が出来ませんでした。
大変申し訳ございませんでした。

前述の諸事情によりましてまだ試せていないのですが、明日以降ご教授いただきました
方法にてためさせていただきます。
とても細かなロジックのご説明いただまして本当にありがとうございます！

このたびはお礼が遅くなりまして本当に申し訳ございませんでした。

回答No.3

real beatin（@realbeatin）

こんにちは。

=IFERROR(INDIRECT("'\\XXX.XXX.XXX.X\共有\営業別\[売上げデータ.xlsx]"&D4&"'!C9"),"")

みたいなことを意図されているのかと思います。
上記数式のシングルクオート２か所とエクスクラメーションマークの位置と書き忘れに注意。
また、D4にシート指定が必要な場合は適宜書き加えて下さい。

回答No.2

bunjii

='\\XXX.XXX.XXX.X\共有\営業別\[売上げデータ.xlsx]田中'!C9

>この数式の'田中'部分を、A.xlsxのD4を参照するようにしたいのですが、どうすれば出来るでしょうか？
A.xlsxのD4では要件を満たしていません。
仮にシート名がSheet1とすれば次のようになるでしょう。
='\\XXX.XXX.XXX.X\共有\営業別\[A.xlsx]Sheet1'!D4

またはA.xlsxを開いた状態で数式バーへ = を入力し、A.xlsxのD4セルをクリックすれば自動的に参照先が入力されます。
結果を確認してA.xlsxを閉じればフルパスが付加されます。

>あとリンク先が存在しない場合にはブランク表示にしたいのです。
Excel 2013のときはIFERROR関数が使えますので下記のようにすると良いでしょう。
=IFERROR(対象の数式,"")

お礼

bunjii さん、返信が遅くなりまして大変申し訳ございませんでした。
諸事情によりPCを開けない環境におりましたため、返信が出来ませんでした。
大変申し訳ございませんでした。

前述の諸事情によりましてまだ試せていないのですが、明日以降ご教授いただきました
方法にてためさせていただきます。

このたびはお礼が遅くなりまして本当に申し訳ございませんでした。