エクセルで複数ファイルからデータを参照する時

質問

６０個位の同じひな形のエクセルファイルと、
各ファイルの中のデータを一覧にした１つのファイルがあります。

一覧表のファイルに、エリアごとの行を作成し、
各ファイルの同じ場所のセルからデータを参照する式を入れているところですが、
それぞれ違うフォルダにある参照元のファイルを開いて
参照先に１つ１つ参照式を入力しているのでは
いつまで経っても終わりません。

また、東京の行に入れた「フルパス・エクセルファイル名・セル番地」の参照式をコピーして、
フォルダとファイル名をほかの地域に置換したらどうかと思ったのですが、
いちいちファイルを選ぶウィンドウも出てきてやはり手間がかかってしまいます。

シートの構成を変えるわけにもいかず、
実際は参照するためにデータを引っ張ってくるセルはもっと多いため、途方に暮れています。
何か良い方法はないでしょうか？
急ぎのため、あまり複雑ではない方法だととても助かります。

回答No.4

MackyNo1 · ベストアンサー

>また、東京の行に入れた「
」の参照式をコピーして、フォルダとファイル名をほかの地域に置換したらどうかと思ったのですが、いちいちファイルを選ぶウィンドウも出てきてやはり手間がかかってしまいます。

ご希望のデータ収集を行うなら、マクロを利用するのが最も簡便な対応だと思われますが、「フルパス・エクセルファイル名・セル番地」のデータがどこかに入力されているなら関数などを使用して一般機能で対応することも可能です。
マクロで実行する場合を含めて、実際の一覧表のレイアウトによって、具体的な方法が異なりますので（関数で簡便に対応できる場合もあります）、一覧表のレイアウトなどを具体的に提示されたほうが皆さんから的確な回答が寄せられると思います。

ちなみに、縦方向にデータを集計するなら以下のような手順で対応することができます。

参照したいセルに以下のような数式を入力し、文字列でリンク数式を作成します。

="="&フルパスが入力されているセル番地

フルパスのセル番号が変動するような場合はフォルダ名やシート名部分だけ参照してセル番地の部分を＆でつないだ数式などに工夫します。

この数式を縦方向にオートフルコピーして必要なリンク数式をすべて作成します。

このセル範囲を選択して右クリック「コピー」そのままもう一度右クリック「形式を選択して貼り付け」で「値」にして通常の文字列に変換し、さらにその範囲が選択された状態のまま「データ」「区切り位置」で「完了」すれば、すべての文字列数式が通常の数式に変換されます。

お礼

質問をする際、エクセルのシートを作成し、
それを画像にして添付していたのですが、
どうも添付が上手くいっていなかったようで結果的に具体性に欠ける質問となってしまいました。

値から数式にする手順が面白いですね。
どうもありがとうございます。

回答No.3

keithin

関数で「数式文字列」を作成し，最後に数式として落とし込むのが簡単かもしれません。

たとえば
c:\folder\東京\book1.xls　のsheet1　の　A1
c:\folder\大阪\book1.xls　のsheet1　の　A1
c:\folder\京都\book3.xls　のsheet1　の　A1

のような数式を並べたいとすれば，違う言葉（東京や大阪，book1やbook3）をデータとしてＡ列やＢ列に列記します
　　Ａ　　　Ｂ
１　東京　book1
２　大阪　book1
３　京都　book3

東京ブックから欲しい最終的な数式は
='c:\folder\東京\[book1.xls]Sheet1'!A1
です

Ｃ１セルに
="='c:\folder\" & A1 & "\[" & B1 & ".xls]Sheet1'!A1"
のように作成し，正しい「数式文字列」が出来ているか確認します。
ここで間違えてるとあなたが失敗したように「値の参照」のダイアログが延々出続けるので，実際に正しく参照できている（いままでがんばって手作りしていた）実際に機能する数式と見比べて，よく確認します。

数式の計算結果をコピーし，形式を選んで貼り付けで値のみ貼り付けます
仕上げとしてCtrl+Hで置換を出し，
=
を
=
にすべて置換します。

お礼

そういえば、違う作業をする際に、
回答いただいたことと似たようなことをしたのを思い出しました。
他の方の回答にもありましたが、数式を文字列にするのがポイントなんですね。

どうもありがとうございます。

回答No.2

xls88

数式を一旦文字列にして操作、作業終了後に数式に戻す方法はどうでしょうか。
詳細が不明なので雑駁な説明になります。
応用してください。

対象セル範囲を選択
Ctrl＋H で「置換」ダイアログ表示
=
を
#=
に
Alt＋A で「すべて置換」
Ctrl＋H で「置換」ダイアログ表示
パス名[*]
を
パス名[ブック名]
に
Alt＋A で「すべて置換」
Ctrl＋H で「置換」ダイアログ表示
#=
を
=
に
Alt＋A で「すべて置換」

お礼

手軽にできますね。

今回は違う方法で行ったのですが、一旦文字列にするという考え方、
今後の参考にさせていただきます。
ありがとうございました。

回答No.1

yasuto07

ファイルメーカーヲルカウベキナンデスケドネ。

お礼

なるほど、それがあればうまくいきそうですね。
しかし、仕事においては現在あるものしか使えない状況もあるのです、残念ながら。