60個位の同じひな形のエクセルファイルと、
各ファイルの中のデータを一覧にした1つのファイルがあります。
一覧表のファイルに、エリアごとの行を作成し、
各ファイルの同じ場所のセルからデータを参照する式を入れているところですが、
それぞれ違うフォルダにある参照元のファイルを開いて
参照先に1つ1つ参照式を入力しているのでは
いつまで経っても終わりません。
また、東京の行に入れた「フルパス・エクセルファイル名・セル番地」の参照式をコピーして、
フォルダとファイル名をほかの地域に置換したらどうかと思ったのですが、
いちいちファイルを選ぶウィンドウも出てきてやはり手間がかかってしまいます。
シートの構成を変えるわけにもいかず、
実際は参照するためにデータを引っ張ってくるセルはもっと多いため、途方に暮れています。
何か良い方法はないでしょうか?
急ぎのため、あまり複雑ではない方法だととても助かります。
No.4ベストアンサー
- 回答日時:
>また、東京の行に入れた「
」の参照式をコピーして、フォルダとファイル名をほかの地域に置換したらどうかと思ったのですが、いちいちファイルを選ぶウィンドウも出てきてやはり手間がかかってしまいます。
ご希望のデータ収集を行うなら、マクロを利用するのが最も簡便な対応だと思われますが、「フルパス・エクセルファイル名・セル番地」のデータがどこかに入力されているなら関数などを使用して一般機能で対応することも可能です。
マクロで実行する場合を含めて、実際の一覧表のレイアウトによって、具体的な方法が異なりますので(関数で簡便に対応できる場合もあります)、一覧表のレイアウトなどを具体的に提示されたほうが皆さんから的確な回答が寄せられると思います。
ちなみに、縦方向にデータを集計するなら以下のような手順で対応することができます。
参照したいセルに以下のような数式を入力し、文字列でリンク数式を作成します。
="="&フルパスが入力されているセル番地
フルパスのセル番号が変動するような場合はフォルダ名やシート名部分だけ参照してセル番地の部分を&でつないだ数式などに工夫します。
この数式を縦方向にオートフルコピーして必要なリンク数式をすべて作成します。
このセル範囲を選択して右クリック「コピー」そのままもう一度右クリック「形式を選択して貼り付け」で「値」にして通常の文字列に変換し、さらにその範囲が選択された状態のまま「データ」「区切り位置」で「完了」すれば、すべての文字列数式が通常の数式に変換されます。
質問をする際、エクセルのシートを作成し、
それを画像にして添付していたのですが、
どうも添付が上手くいっていなかったようで結果的に具体性に欠ける質問となってしまいました。
値から数式にする手順が面白いですね。
どうもありがとうございます。
No.3
- 回答日時:
関数で「数式文字列」を作成し,最後に数式として落とし込むのが簡単かもしれません。
たとえば
c:\folder\東京\book1.xls のsheet1 の A1
c:\folder\大阪\book1.xls のsheet1 の A1
c:\folder\京都\book3.xls のsheet1 の A1
のような数式を並べたいとすれば,違う言葉(東京や大阪,book1やbook3)をデータとしてA列やB列に列記します
A B
1 東京 book1
2 大阪 book1
3 京都 book3
東京ブックから欲しい最終的な数式は
='c:\folder\東京\[book1.xls]Sheet1'!A1
です
C1セルに
="='c:\folder\" & A1 & "\[" & B1 & ".xls]Sheet1'!A1"
のように作成し,正しい「数式文字列」が出来ているか確認します。
ここで間違えてるとあなたが失敗したように「値の参照」のダイアログが延々出続けるので,実際に正しく参照できている(いままでがんばって手作りしていた)実際に機能する数式と見比べて,よく確認します。
数式の計算結果をコピーし,形式を選んで貼り付けで値のみ貼り付けます
仕上げとしてCtrl+Hで置換を出し,
=
を
=
にすべて置換します。
そういえば、違う作業をする際に、
回答いただいたことと似たようなことをしたのを思い出しました。
他の方の回答にもありましたが、数式を文字列にするのがポイントなんですね。
どうもありがとうございます。
No.2
- 回答日時:
数式を一旦文字列にして操作、作業終了後に数式に戻す方法はどうでしょうか。
詳細が不明なので雑駁な説明になります。
応用してください。
対象セル範囲を選択
Ctrl+H で「置換」ダイアログ表示
=
を
#=
に
Alt+A で「すべて置換」
Ctrl+H で「置換」ダイアログ表示
パス名[*]
を
パス名[ブック名]
に
Alt+A で「すべて置換」
Ctrl+H で「置換」ダイアログ表示
#=
を
=
に
Alt+A で「すべて置換」
手軽にできますね。
今回は違う方法で行ったのですが、一旦文字列にするという考え方、
今後の参考にさせていただきます。
ありがとうございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) 複数ファイルのデータの統合について 12 2022/05/14 12:03
- Visual Basic(VBA) エクセルのマクロについて教えてください。 1 2023/08/03 11:27
- Excel(エクセル) 【VBA】指定フォルダに格納中のテキストファイルをエクセルで処理し結果のエクセルを新規フォルダに保存 1 2022/03/25 14:19
- Excel(エクセル) フォルダ内のエクセルファイルを開かずにデータ採取する関数式 2 2022/12/22 22:15
- Visual Basic(VBA) VBAの参照先のファイル名をセルに書いて代入したい 2 2022/04/04 13:42
- Visual Basic(VBA) VBA 参照先で選んだファイルをコピーし、出力先に別名で保存したい 8 2022/05/13 20:37
- Visual Basic(VBA) エクセルのマクロについて教えてください。 1 2023/08/03 12:30
- Excel(エクセル) Excelでなぜこのような式をつかっているのでしょうか、行に1,2,3と連番を振るだけなのに 5 2023/04/08 20:00
- Visual Basic(VBA) VBAのユーザーフォームのテキストボックスに入力制限をしたい 6 2022/11/15 08:28
- Excel(エクセル) エクセルでのアクセス履歴をとりたいです 1 2022/06/13 17:30
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルで文字の入力がセルの...
-
多数の計算セルに一括で同一の...
-
Excelからテキストへのコ...
-
Excelで、半角スペースをTABに...
-
エクセルのセルの中の,よりも前...
-
Excelの入力済みセルに一括で、...
-
置換機能を使わずに先頭に「'」...
-
エクセルでセル内の一部分だけ...
-
小数点を消す方法
-
excelで可視セルのみ置換
-
【エクセル】ピボットテーブル...
-
Excelのアポストロフィーの削除
-
Excelのmatch関数エラー原因が...
-
エクセルの関数で、記号などを...
-
EXCELで"Tab"を"@"に置換したい
-
電話番号の()を一括でハイフ...
-
一括でダブルクリックした状態...
-
企業名簿で(株)や(有)を無視し...
-
エクセルの削除
-
別シートのリストから置換する方法
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルで文字の入力がセルの...
-
多数の計算セルに一括で同一の...
-
Excelからテキストへのコ...
-
excelで可視セルのみ置換
-
Excelで、半角スペースをTABに...
-
エクセルのセルの中の,よりも前...
-
Excelの入力済みセルに一括で、...
-
Excelのmatch関数エラー原因が...
-
Excelの空文字セルの削除方法を...
-
【エクセル】ピボットテーブル...
-
企業名簿で(株)や(有)を無視し...
-
置換機能を使わずに先頭に「'」...
-
エクセルでセル内の一部分だけ...
-
小数点を消す方法
-
別シートのリストから置換する方法
-
エクセルでセル内の一部を一括...
-
Excelのアポストロフィーの削除
-
エクセルの表ををメモ帳などに...
-
エクセルでスペースの記号を表...
-
Excelで特定の文字の前だけに空...
おすすめ情報