Excel 複数ファイルの参照

質問

説明が難しいのですが、
例えば、デスクトップに[1.xls], [2.xls], [3.xls] ・・・
と連続したエクセルファイルがあり、
新規作成で以下のようなシートを作製して、
　　A　B　C　D　E　F
１　1
２　2　
３　3
４　4
５　5
６　6

B列にデスクトップ上の各ファイルのセルA1の値を入力したい場合、
数式を「='C:\…\デスクトップ\[***.xls]Sheet1'!$A$1」
として、***部分にセルA1～の数値を代入できるようにしたいのですが、可能でしょうか。

回答No.2

noname#70958 · ベストアンサー

●Ａ案
参照先のブック群が開いているという前提であれば、
INDIRECT関数を使って、A列の値に応じて参照先を切り替えることができます。

B1セルを
　=INDIRECT("'C:\Documents and Settings\Kater_Kurz\デスクトップ\["&A1&".xls]Sheet1'!$A$1")
として下方にフィル

ただし、INDIRECT関数では閉じているブックへの参照を作ることはできませんから、
この方法では、参照先のブックが閉じている場合は#REF!が返ります。

●Ｂ案
参照先のブック群が固定である場合は、
目的のセルを参照する数式を【いったん文字列として生成】し、
置換機能をつかって数式化するのがベターかと思います。

１．B1セルを
　="☆'C:\Documents and Settings\Kater_Kurz\デスクトップ\["&A1&".xls]Sheet1'!$A$1"
として下方にフィル
２．B列をコピー　⇒　形式を選択して貼り付け　⇒　値
３．B列について、"☆"を"="に置換する

この方法であれば、参照先が閉じている場合も値が返りますが、
当然ながらA列の値が変わってもB列の数式はそのままです。

●Ｃ案
「参照先が閉じた状態で値を得たい」「A列の値が追加・変更されうる」という場合は、
「A列の値が変更されるたびに数式を貼り直すマクロ」を設置するという選択肢があります。

例えば…
　$A$1:$A$999の値が変更される都度、
　='C:\Documents and Settings\Kater_Kurz\デスクトップ\[【BookName】.xls]Sheet1'!$A$1
　の【BookName】の部分をA列の値で置き換えた数式を、右隣（B列）のセルに設定する。
'--------------------------------↓　ココカラ　↓--------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
　Set myRng = Intersect(Range("$A$1:$A$999"), Target)
　If myRng Is Nothing Then Exit Sub
　myStr = "='C:\Documents and Settings\Kater_Kurz\デスクトップ\[【BookName】.xls]Sheet1'!$A$1"
　For Each myCell In myRng
　　myCell.Offset(0, 1).Formula = Replace(myStr, "【BookName】", myCell.Value)
　Next myCell
End Sub
'--------------------------------↑　ココマデ　↑--------------------------------

以上ご参考まで。長乱文陳謝。

回答No.1

O_O

可能ですね（今、実際にやってみた）。

式は「=[***.xls]Sheet1!$A$1」こんな感じ。

質問とは関係ないが、一応、気になったので。
誤）作製　→　正）作成