エクセル 複数 ファイル まとめる。 「数百のファイルを1つ1つ開くの面倒……」、複数のExcelデータを1箇所に集約するマクロ術 (1/3):働く価値を上げる“VBA/GAS”術(7)

複数のファイル(ブック)を1枚のシートに効率よくまとめるマクロ

エクセル 複数 ファイル まとめる

QlikViewを使用すれば、社内に散在する大量のExcelファイルから一括でデータを取り込み、一つに統合できます。 さらに、一つにまとめたデータをQlikViewで分析したり、Excel形式やCSV形式で出力したりできます。 社内に散在するExcelファイルをどう活用するかは、どの企業にもよくある課題だと思います。 このためだけにQlikViewをダウンロードしていただいても損はないと思いますので、まだQlikViewをお持ちでない方は、ぜひこれを機にダウンロードしてみてください。 QlikViewは以下のページから無料でダウンロードできます。 (要ユーザー登録) 改訂 2014年5月21日 内容を全面的に改訂しました。 以前の内容は必要以上に繰り返し処理(FOR文)を使用していたため、より簡潔な内容に書き換えました。 改訂前の記事はこちらからご覧いただけます。 では、二つのExcelファイルを一つに統合する例をご紹介しましたが、ファイルが大量にある場合に一つ一つ指定していくのは手間です。 このような場合は、ファイル名にワイルドカード(? ファイルのダウンロード ここでは以下のファイルを使用しますので、ダウンロードしてください。 Zip形式ですので、ダウンロード後解凍してください。 今回は使用する例ごとに、フォルダを分けてあります。 ここでは「例1」のフォルダを使用しますので、フォルダを開いてみてください。 下図のようにExcelファイルが大量にあります。 これらのファイルは年月ごとに分かれており、12ヶ月x3年分で計36ファイルあります。 ファイル名の末尾が年月をあらわします。 たとえば「売上200801. xls」は2008年1月のデータです。 各ファイルには約350件ずつデータが格納されており、全ファイルあわせると約1万3千件(13,353件)になります。 Excelファイルの内容はすべておなじ形式です。 下図のように「日」「商品名」「売上金額」といったデータが格納されています。 下準備(一つのファイルを取り込む) それではデータを取り込んでみましょう。 ファイルを新規に作成し、[ロードスクリプトの編集]画面を起動します。 まずはファイルを一つだけ取り込んでみます。 ロードスクリプトの最下行にカーソルをあわせます。 [相対パス]にチェックを入れて、[テーブルファイル]ボタンをクリックします。 [相対パス]については後ほど説明します。 「例1」フォルダの「売上200801. xls」を選択します。 今回は単純にデータを取り込むだけなので、そのまま[終了]ボタンをクリックします。 [相対パス]にチェックを入れると、読み込むファイルの指定がファイル名のみになります。 今回の例では、ドキュメント(qvwファイル)とおなじディレクトリにある「売上200801. xls」という意味になります。 [相対パス]にチェックを入れなかった場合は、下図のようにフルパスの指定になります。 どちらでも問題はないのですが、今回は相対パスの方が指定しやすいため相対パスにしました。 さらに[相対パス]にチェックを入れた場合、LOAD文の上に「Directory」という文も挿入されます。 しかし、Directory文はここでは不要なので削除します。 Directory文の詳細はヘルプなどでご確認ください。 検索するファイルのパスを指定するためのもので、Excelファイルの統合とは直接関係ない機能です。 ワイルドカードの指定(すべてのファイルを取り込む) このままだとファイル名が固定で指定されているため、一つのファイルしか取り込まれません。 ファイル名の指定をワイルドカードの指定に置き換えます。 変更前 売上200801. xls この指定により、ドキュメント(qvwファイル)とおなじディレクトリにある、すべてのExcelファイル(拡張子が. xlsのファイル)が取り込まれます。 ロードスクリプトはひとまず完成です。 [OK]ボタンをクリックします。 データを取り込んでみましょう。 [リロード]ボタンをクリックします。 ファイルを保存してください。 今回は相対パスの指定にしたため、ドキュメントはExcelファイルとおなじ場所に保存してください。 「例1」フォルダを開き、名前をつけて保存します。 ファイルが立て続けに読み込まれ、「例1」フォルダにあるすべてのExcelファイルが取り込まれました。 ファイル名から年月の項目を作成する では、今回読み込んだExcelファイルをあらためて確認してみましょう。 年月のデータがExcelファイル中にないため、単純にデータを取り込んだだけでは、どの年月のデータか分からなくなってしまいます。 このような場合は、ファイル名をもとに項目を作成できます。 [ロードスクリプトの編集]画面を起動してください。 ファイル名を取得するには、ファイル関数を使用します。 ファイル関数にはいくつか種類がありますが、ここではFileBaseName関数を使用するとよいでしょう。 FileBaseName関数はファイル名を拡張子なしで取得します。 また、文字列の一部を抜き出すには、Mid関数を使用するとよいでしょう。 LOAD文に年と月の項目を追加してください。 LOAD Mid FileBaseName , 3, 4 as 年, Mid FileBaseName , 7, 2 as 月, 日, : 関数の指定はそれぞれ以下の意味になります。 Mid FileBaseName , 3, 4 ファイル名の3文字目から4文字分を抜き出す。 Mid FileBaseName , 7, 2 ファイル名の7文字目から2文字分を抜き出す。 1 2 3 4 5 6 7 8 売 上 2 0 0 8 0 1 -------- -- 年 月 リロードを実行してください。 取り込んだデータを確認するために、テーブルボックスを作成してみましょう。 画面中央の[すべて追加]をクリックします。 画面右の[ロード順]ボタンをクリックして、項目の並び順をデータを取り込んだ順(Excelファイルとおなじ順番)にします。 [OK]ボタンをクリックします。 テーブルボックスが作成されました。 また、ファイル名から「年」と「月」の項目も作成できました。 一つにまとめたデータをExcelで使用したい場合は、テーブルボックスの機能でExcel出力すると簡単です。 テーブルボックスの右上にある[Excel出力]をクリックします。 Excelファイルに出力できました。 データが大量にある場合、Excel出力に時間がかかる場合があります。 その場合は、ロードスクリプトにSTORE文を追加して、CSVファイルに出力してください。 STORE テーブル名 into ファイル名. csv txt 今回の例であれば、ロードスクリプトの末尾に以下の指定を追加します。 csv txt 以上で一つ目の例は終了です。 複数のExcelファイルを、簡単に一つにまとめられることが、お分かりいただけたのではないでしょうか。 Excelファイルの統合2(ディレクトリが複数ある場合) 繰り返し処理(FOR文)の指定 一つ目の例では、一つのディレクトリ中のファイルをすべて取り込む例を紹介しましたが、ここからはディレクトリが複数に分かれている場合の例を紹介します。 ディレクトリが複数に分かれているときは少し指定が複雑になり、繰り返し処理(FOR文)が必要になります。 繰り返し処理は、プログラミング(Excelで言うマクロ)の一種ですが、コマンドを数行記述するだけですので、プログラミングに苦手意識のある方でも問題なく理解できると思います。 ここでは「例2」のフォルダを使用しますので、フォルダを開いてみてください。 年ごとのフォルダがあります。 各フォルダの中に月ごとに分かれたファイルがあります。 ドキュメントはさきほど作成したものをコピーして使用します。 「例1」フォルダにあるドキュメントを、「例2」フォルダにコピーしてください。 ドキュメントを開き、[ロードスクリプトの編集]画面を開きます。 複数のフォルダから一括でファイルを読み込みたい場合は、繰り返し処理(FOR文)が必要です。 まず、LOAD文の前後に以下のように「FOR」と「NEXT」の指定を追加します。 NEXT つぎに、ファイル名の指定を以下のように変更します。 xls ロードスクリプトの全文は以下のようになります。 フォントを「MS ゴシック」などの円マークが表示できるものに変更すれば、円マークで表示されます。 繰り返し処理(FOR文)の解説 まず、繰り返し処理では「FOR」と「NEXT」で囲んだ範囲が繰り返し実行されます。 このとき、指定した変数に値が繰り返し代入されていきます。 「DirName」は変数名の指定です。 フォルダのパスがこの変数に順次格納されていきます。 ここでもワイルドカードが使用できます。 つまり、今回の繰り返し処理は、以下のようにLOAD文を三回指定したのとおなじ意味になります。 各LOAD文の中では、読み込むファイル名の指定(FROM句の後ろ)だけが書き換わっています。 LOAD... 計三つのフォルダから36ファイルが読み込まれます。 年月の項目の修正 ここまでで全ファイルが取り込めました。 しかし、テーブルボックスで「年」と「月」の項目を確認すると、値がおかしくなっています。 ファイル名などが前回とは変わっているため、「年」と「月」の指定も変更する必要があります。 [ロードスクリプトの編集]画面を開きます。 「年」と「月」の指定を以下のように変更してください。 「年」を作成するには、FileDir関数でフォルダ名を取得してください。 FileDir関数はフォルダ名をフルパスで取得する関数です。 また、フォルダのパスから一部を抜き出すには、SubField関数を使用するとよいでしょう。 SubField関数は、文字列がある文字で区切られているときに、区切られた部分を取得する関数です。 「-1」が一番後ろ(後ろから一つ目)という意味です。 今回の例で言えば、「-1」と指定するのと「7」と指定するのはおなじ結果となります。 「年」と「月」が取得できました。 以上で二つ目の例も終了です。 二つ目の例では、ディレクトリが複数に分かれている場合の例をご覧いただきました。 振り返り 今回は大量のExcelファイルを一つにまとめる方法についてご覧いただきました。 以下に重要なポイントをまとめます。 [相対パス] 初期設定:無効 有効にすると、読み込むファイルの指定がファイル名のみの指定になる。 無効にすると、読み込むファイルの指定がフルパスの指定になる。 つまり、有効にした場合はドキュメント(qvwファイル)とおなじディレクトリにあるファイルが検索される。 ワイルドカード ファイル名やフォルダ名を指定する箇所では、ワイルドカードが使用できる。 繰り返し処理(FOR文) 複数のディレクトリから一括でデータを取り込む場合は、繰り返し処理(FOR文)が必要。 FORとNEXTで囲んだ範囲が繰り返し実行される。 以下の指定で複数のフォルダを一括で処理できる。 FOR Each 変数名 in DirList フォルダ名• ファイル名やフォルダ名の取得には、ファイル関数を使用する。 FileBaseName関数(拡張子なしのファイル名)、FileDir関数(フォルダのフルパス)など。 文字列から一部を抜き出すには、以下のような関数を使用する。 Mid関数(文字数を指定)、SubField関数(区切り文字で区切られた一部を取得)など。 最後に… 今回は大量のExcelファイルを一つにまとめる方法を紹介しました。 この記事を書くにあたり、そもそもExcelだけで簡単にファイルを統合できないのか、あらためて調べてみましたが、やはりマクロ(VBA)を記述する必要があるようです。 マクロを一から記述するのに比べれば、QlikViewを使用した方が簡単だと思いますが、いかがだったでしょうか。 さらにQlikViewでは、複数列に分かれたデータを一列にまとめたり、重複するデータを一意にするようなことも簡単にできます。 以下の二つの記事もぜひ見てみてください。 また、本編で紹介し切れなかった内容について、次のページにまとめてありますので、そちらもぜひご覧ください。 お疲れ様でした。 Pages: 1.

次の

複数シートのデータを一つのシートにまとめる(VBA):エクセル練習問題

エクセル 複数 ファイル まとめる

Sub bookmerge Dim b As Workbook '集計するブック Dim b1 As Workbook '集計先のブック Dim d '集計するブック内のシートのデータ数 Dim d1 '集計先のシートのデータ数 Workbooks. Name b1. Worksheets. Worksheets i. Count. End xlUp. Worksheets 1. Count. End xlUp. Row b. Worksheets i. Copy b1. Worksheets 1. まとめたいファイルはあらかじめ開いておいてください。 実際には、 ・特定のフォルダにあるExcelファイルを1つにまとめる ・特定のファイルにあるExcelファイルのうち、特定の名前が含まれるファイルをまとめる ・ファイルのシートすべてをまとめる ・ファイルのシートのうち、指定したもの(月、支店など)のみまとめる といったことができます。 dという変数に、このマクロが入っているファイルのワークシート1のA列で、行をカウントします。 Worksheets 1. Count. End xlUp. Row と表現しています。 最後の行に1を足した場所、つまり、一番下の行のその下に貼り付けるのです。 マクロを実行後、新しいブックが作られ、そのブックに、3つのファイル(新宿支店、渋谷支店、池袋支店)のデータを集めます。 サンプルはこちらです。 マクロを有効し、【集計】ボタンを押すと、そのときに開いているブックのデータを、新しいブックに集計します。 井ノ上 陽一(いのうえ よういち) 「ひとりしごと」=雇われない雇わない生き方を、時間・お金の両面でサポート。 ・株式会社タイムコンサルティング ひとり社長 ・ひとり税理士 ・アイアンマン スイム3. 8km、バイク180. 2km、ラン42. 日課は1日1新、ブログ。 2007年7月9日以来、日毎日更新中。 1972年12月18日 大阪生まれ。 宮崎育ち東京在住。 娘3歳3ヶ月。

次の

複数シートのデータを一つのシートにまとめる(VBA):エクセル練習問題

エクセル 複数 ファイル まとめる

この方法では毎回同じファイル名の単一ファイルを取り込むのであれば問題はありません。 しかし月別・部門別といった複数のデータを取り込む場合その都度クエリの追加が必要というデメリットがありました。 「フォルダごと取得する」とは。 そこで、複数のデータをまとめて取り込みたいときは、フォルダごと取得することで、フォルダ内のデータをまとめて取得し、結合したデータとして読み込むことができるのです。 まとめると・・・ では、実際にやり方を見ていきましょう。 「フォルダ取得」の使い方 前提条件 売上データが4月から7月分まで個別にcsvファイルでありました。 今回はそのうち4月~6月分のデータを取り込みたいとします。 取り込みたいデータをこんな感じで「売上データ」フォルダに格納します。 取り込むデータの様式 特に列項目)に注意。 同じでないとだめ。 取り込まないファイルは置かないこと。 パワークエリの操作方法 次にエクセルでの操作についてです。 「データ」タブ「新しいクエリ」「ファイルから」「フォルダーから」を選択します。 次に取り込みたいファイルを格納したフォルダを選択します。 フォルダーの中の取り込みたいデータが表示されますので、確認して画面右下の「データ結合と変換」を選択します。 ここでは結合するファイルの様式を確認する画面が出てくるので、「OK」で進みます。 すると、4月から6月までの売上データがこのように結合された状態で表示されますので、不要列を削除するなど編集して読込めば完了です。 完成形はこのような形になります。 4月~6月までが結合してテーブルになって読み込まれたのが分かります。 フォルダーからファイルを読み込む方法としてはここまでですが、ここからちょっと応用です。 フォルダ内にデータを追加した場合どうなるのか、やってみましょう。 フォルダ内にデータを追加した場合 こんな感じで7月の売上データをフォルダに格納します。 更新ボタン(画面右の赤枠)を押すと7月分のデータが追加されました。 このように、フォルダにデータファイルを追加しても対応できるのが便利なところです。 まとめ いかがだったでしょうか。 今回は、「複数あるファイルをまとめて取得する方法」についてまとめてみました。

次の