エクセルの複数ブックで作成した同書式データの一覧表

質問

エクセルの複数ブックで作成された同書式のデータを一箇所のシートに一覧表にする方法を教えてください。抽出する元データのブックは同フォルダにあります。具体的には各顧客に宛てたエクセル作成の請求書のデータで、注文日、額面、支払日、顧客名などを一覧表に作成したいのです。

回答No.6

k415

んー。生データを見ずに答えるのは難しいですねｗ
でもお話だと、ファイル名の置換でお悩みの様ですので以下の方法で解決できませんか？
以下参考URLサイトのマクロで"name"というシートを作成します。

そのシートには、フォルダ内のファイル名一覧が作成されています。

そして、参照する場所には
「=INDIRECT("["&name!A4&"]Sheet1!$A$1")」

などと入力します。
これで下までダーっとコピーすれば、nameシートの参照位置もずれていくので
希望通りの結果となりませんかね？

ちょっと解答がテキトーになってしまってすみません。

参考URL：

http://can-chan.com/vba/filemei-itiransakusei.html

回答No.5

yy_kd

複数のBOOK,シートに請求書が存在するのですね。いちいちBookの名前やシート名を
記述するのは結構たいへんですよね。File検索で一覧を作って、指定の条件の元に
1つのシートにデータを取りまとめるツールの作成例が【File検索＋シート合成】と
いうタイトルでhttp://blog.goo.ne.jp/mrk_kd_2007　で紹介されています。
1枚にまとめられたシートからご希望のデータを引き出すのは割と簡単にできそうですね。

回答No.4

k415

ん～。請求書がどの程度形式が決まっているかによりますが。。。

参考までにこういうこともできますよ。例えばtest.xlsというファイルとtest2.xlsがあり、
test.xlsのA1には、「1」という値が入力されています。その値をtest2.xlsに表示する場合の中身です。
わかりにくくてすみませんが、下に書いたのは、test2.xlsの中身です。
　　　　A　　B
1行　　1 test.xlsx
2行　　　　 Sheet1

ここでA1は、「=INDIRECT("["&B1&"]"&B2&"!$A$1")」と記述します。
ブック名には、B1のセルを参照し、シート名は、B2を参照するというものです。

結構便利ですので、参考になればどうぞ。
ブック名を書き込むようにすれば、おっしゃっている作業を簡略化できるかと思うのですが。

お礼

では、求めたいかなり省略した一覧表の例を示しますね。

：はセル区切り

A:B:C:D：E:F:G
1：請求書発行日時：支払い期日：請求書番号：顧客名：請求額：支払方法：発送方法
2：1月1日：1月15日：1100001：石頭誠：10020円：クレジットカード：ペリカン便
3：1月2日：1月16日：1100002：白百合桃子：205円：代引き：ペリカン便
4：1月2日：1月16日：1100003：裏西文次郎：54211円：銀行振り込み：ペリカン便
5：1月3日：1月17日：1100004：八百長シオマキ：2000円：クレジットカード：ペリカン便
6：
7：

これら元データがそれぞれ違うブックに詳細の請求書として存在します。
これらを一つの一覧表にする方法です。

今日教えてもらった方法で私がした方法は、まず2行目のセルをそれぞれ求めたい請求書から参照しました。
これは教えていただいた方法です。
3行目からはそれを一旦全てドラッグして書式をびーっと下まで適当なところまでコピーします。
そして、次に3行目を選択して、置換操作でファイル名を2行目のものと換えると、その行には必要なデータが抽出されました。今日の作業は50枚程度の請求書分でしたので30分ぐらいの作業でしたが、これが倍や3倍になるとちょっと嫌になってしまいます。

しかし、このファイル名の置換作業が何段階かプロセスを踏み、一行ごとに行う作業なので、
それを一編に出来ないかと思いました。
最後の教えていただいた方法もやはりブック名、ファイル名を一つ一つ書き込む（こぴーするにしても）必要があるんですよね。ファイル名を書き込まない方法としてはやはり一旦必要な請求書を順番に同じブックにシートとして一括してから、
連番のシート番号で参照できる方法でしょうか？ブック名は顧客の名がファイル名の一部になっていているので都合が一括指示をする場合には都合が悪いかもしれませんね。

回答No.3

k415

>それぞれのシートが別々のブックの場合はどうしたら良いのでしょうか？

もうすでに答えたと思いますが、ブック名とシート名をそれぞれ異なるように入力していけばいいのではないでしょうか？
私の解答では、ファイル名と書いてしまいましたが、同義と認識しておそらく大丈夫だと思いますけど。

>ブックは同じフォルダー内にあり、それを一括して一覧表にすることはできますか？

一覧表というのは、どういうことをしたいのかがよくわかりません。
単にシートごとにファイル（ブック）を作成しているものを1つのファイル（ブック）にまとめたいだけであれば、
それほど難しくありません。

ただ各ファイル（ブック）のシート内のここからここまでを、並べて一覧にするといった処理であれば、
それは、elikukuさんの質問だけでは、答えかねます。単純な表形式なのかデータがとびとびなのか・・・
色々想像できますので、何をしたいかがわかりません。

一応解答になりそうなサイトです。
http://okwave.jp/qa/q1285964.html
http://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q1316393363
http://www.nurs.or.jp/~ppoy/access/excel/xlM037.html
http://excel-merge.seesaa.net/article/131646342.html

お礼

私のしたかったのは、各請求書に入力されているデータの一部を抽出して、一か月分の簡略一覧表が必要でした。
よって、k415さんの方法で構わないのですが、ファイル名をそれぞれ指定するのも、
置換機能を使っても、最低でも請求書数分は手作業になります。
それを、一挙に一編に出来ないのかと思ったのです。
今月は年初めで請求書が少なく50枚程度だったので、そうした手作業は一時間以内で終わってしまいましたが、
これが100枚以上になると苦痛になります。
来月分の請求書の一覧表は教えていただいたサイトを参考にして、まず、複数ブックを一つのブックのシートにしてから、
やってみようと思います。

回答No.2

k415

=[テスト.xls]Sheet1!A1

=[ファイル名]シート名!セル名

ですね。別ブックを参照するなら。
一度作ってしまえば、ずっと使えると思うので、大変だと思いますが。。
頑張って下さいｗ

お礼

k415さん
=[ファイル名]シート名!セル名
とありましたね。
やはり、結局はファイル名は指定しないと出来ないとおもいましたので、
一覧表を作って、一番最初の項目をコピー設定して、
それぞれのファイル名は手作業で置換機能を駆使して、せっせとしました。
結局はこの方法しかないのでしょうか。

補足

K451さん、Copperさん、有難うございます。
それぞれのシートが別々のブックの場合はどうしたら良いのでしょうか？
ブックは同じフォルダー内にあり、それを一括して一覧表にすることはできますか？

回答No.1

Cupper-2

=＜シート名＞!セル番号
　=Sheet1!A1
この参照形式で 他シートのセル（この場合、Sheet1 の A1セル）を参照できます。