(旧版)大量のExcelファイルをひとつにまとめる方法

(旧版)大量のExcelファイルをひとつにまとめる方法

Pocket

この記事は旧版です。改訂版をご覧ください。

改訂 2014年5月21日
内容を全面的に改訂しました。
この記事の内容は必要以上に繰り返し処理(FOR文)を使用していたため、より簡潔な内容に書き換えました。
改訂後の記事はこちらからご覧いただけます。大量のExcelファイルを一つにまとめる方法


QlikViewを使用すれば、社内に散在する大量のExcelファイルから一括でデータを取り込み、一つに統合できます。
さらに、一つにまとめたデータをQlikViewで分析したり、Excel形式やCSV形式で出力したりできます。

社内に散在するExcelファイルをどう活用するかは、どの企業にもよくある課題だと思います。
このためだけにQlikViewをダウンロードしていただいても損はないと思いますので、まだQlikViewをお持ちでない方は、ぜひこれを機にダウンロードしてみてください。
QlikViewは以下のページから無料でダウンロードできます。(要ユーザー登録)
QlikView Personal Editionのダウンロード

前回の記事では、二つのExcelファイルを一つに統合する例をご紹介しましたが、この方法だとファイル数の分だけQlikViewで指定が必要なため、大量のファイルを取り込むときは少し手間です。
前回ご紹介した方法に加え、繰り返し処理(FOR文)を使用すれば、あるディレクトリ中のファイルを一括で取り込むことなどができます。
繰り返し処理は、プログラミング(Excelで言うマクロ)の一種ですが、コマンドを数行記述するだけですので、プログラミングに苦手意識のある方でも問題なく理解できると思います。



ファイルのダウンロード

ここでは以下のファイルを使用しますので、ダウンロードしてください。
combine-many-excel-files.zip
Zip形式ですので、ダウンロード後解凍してください。
今回は使用する例ごとに、フォルダを分けてあります。
combine-many-excel-files-101

Excelファイルの統合1(あるディレクトリ中のファイルをすべて取り込む)

それではまず、一番よくありそうな例として、”あるディレクトリ中のファイルをすべて取り込む”という処理をおこなってみましょう。

ここでは「例1」のフォルダを使用しますので、フォルダを開いてみてください。
下図のようにExcelファイルが大量にあります。
combine-many-excel-files-102これらのファイルは年月ごとに分かれており、12ヶ月x3年分で計36ファイルあります。
ファイル名の末尾が年月をあらわします。たとえば「売上200801.xls」は2008年1月のデータです。
各ファイルには約350件ずつデータが格納されており、全ファイルあわせると約1万3千件(正確には13,353件)になります。

Excelファイルの内容はすべておなじ形式です。下図のように「日」「商品名」「売上金額」といったデータが格納されています。
combine-many-excel-files-103


下準備(一つのファイルを取り込む)

それではデータを取り込んでみましょう。
ファイルを新規に作成し、[ロードスクリプトの編集]画面を起動します。
combine-many-excel-files-104
はじめから繰り返し処理を使用するのは難しいため、まずはファイルを一つだけ取り込んでみます。
ロードスクリプトの最下行にカーソルをあわせます。
[相対パス]にチェックを入れて、[テーブルファイル]ボタンをクリックします。
combine-many-excel-files-105[相対パス]については後ほど補足します。

「例1」フォルダの「売上200801.xls」を選択します。
combine-many-excel-files-106
今回は単純にデータを取り込むだけなので、そのまま[終了]ボタンをクリックします。
combine-many-excel-files-107
[相対パス]にチェックを入れると、読み込むファイルの指定がファイル名のみになります。
今回の例では、ドキュメント(qvwファイル)とおなじディレクトリにある「売上200801.xls」という意味になります。
combine-many-excel-files-107-1
[相対パス]にチェックを入れなかった場合は、下図のようにフルパスの指定になります。
どちらでも問題はないのですが、今回は相対パスの方が指定しやすいため相対パスにしました。
combine-many-excel-files-109
さらに[相対パス]にチェックを入れた場合、LOAD文の上に「Directory」という文も挿入されます。
しかし、Directory文はここでは不要なので削除します。
combine-many-excel-files-108Directory文の詳細はヘルプなどでご確認ください。検索するファイルのパスを指定するためのもので、Excelファイルの統合とは直接関係ない機能です。

ロードスクリプトはひとまず完成です。[OK]ボタンをクリックします。
combine-many-excel-files-110
データを取り込んでみましょう。
[リロード]ボタンをクリックします。
combine-many-excel-files-111
ファイルを保存してください。
combine-many-excel-files-112今回は相対パスの指定にしたため、ドキュメントはExcelファイルとおなじ場所に保存してください。
「例1」フォルダを開き、名前をつけて保存します。
combine-many-excel-files-113
リロードが完了しました。383件のデータが取り込まれています。
combine-many-excel-files-114
取り込んだデータを確認するために、テーブルボックスと統計ボックスを作成してみましょう。
まずはテーブルボックスを作成します。
シートの余白を右クリックし[シートオブジェクトの追加]→[テーブルボックス]を選択します。
combine-many-excel-files-115
画面中央の[すべて追加]をクリックします。
combine-many-excel-files-116
画面右の[ロード順]ボタンをクリックして、項目の並び順をデータを取り込んだ順(Excelファイルとおなじ順番)にします。
[OK]ボタンをクリックします。
combine-many-excel-files-117
テーブルボックスが作成されました。
combine-many-excel-files-118
つづいて統計ボックスを作成します。
シートの余白を右クリックし[シートオブジェクトの追加]→[統計ボックス]を選択します。
combine-many-excel-files-119
[項目]から「売上金額」を選択し、[OK]ボタンをクリックします。
ここではデータの件数(レコード数)だけ見られればいいのですが、他の関数もそのまま残しておきます。
combine-many-excel-files-120
統計ボックスが作成されました。
データの件数は統計ボックスの「レコード数」で確認できます。383件のデータが取り込まれていることが、あらためて確認できます。
combine-many-excel-files-121


繰り返し処理1(二つのファイルを取り込む)

では、ここからが本題となります。
繰り返し処理を使用してみましょう。とは言え、はじめからすべてのファイルを取り込むのは難しいため、まずは二つのファイルを取り込んでみます。

[ロードスクリプトの編集]画面を起動します。
LOAD文の前後に以下の指定を追加します。
FOR Each fileName in '売上200801.xls', '売上200802.xls'
:
NEXT
combine-many-excel-files-122
このように指定すると、FORとNEXTで囲んだ範囲が繰り返し実行されます。
「in」の後ろには値を,(カンマ)区切りで指定します。ここに指定した数だけ処理が繰り返されます。
今回の指定であれば処理が二回繰り返されることになります。
combine-many-excel-files-123
現時点ではファイル名が「売上200801.xls」と、固定で指定されているため、このままではおなじファイルを二回読み込むことになります。
ファイル名の指定を以下のように変更してください。
変更前
売上200801.xls
変更後
$(fileName)
combine-many-excel-files-124
$(fileName)の指定が、「FOR Each」の後ろの「fileName」に対応しています。
$(fileName)の部分は、「in」の後ろに指定した値に置き換えられながら、処理が繰り返されます。
combine-many-excel-files-125つまり、今回の指定は以下のように、LOAD文を二回指定したのとおなじ意味になります。
LOAD 日, 
     商品名, 
     販売単価, 
     納品数量, 
     売上金額
FROM
売上200801.xls

LOAD 日, 
     商品名, 
     販売単価, 
     納品数量, 
     売上金額
FROM
売上200802.xls
リロードを実行してみましょう。
二つのファイルが読み込まれ、780件のデータが取り込まれました。
combine-many-excel-files-126combine-many-excel-files-127


繰り返し処理2(すべてのファイルを取り込む)

さきほど、”ここからが本題”と書きましたが、本当の本題はこれから説明する内容です。
ここまでで二つのファイルを読み込めました。「in」の後ろにファイル名を追記していけば、大量のファイルを一括で読み込めます。
しかし、QlikViewのFOR文には、ファイルを一括で指定する記述が用意されています。

[ロードスクリプトの編集]画面を起動してください。
FOR文の「in」の後ろを以下のように変更します。
変更前
FOR Each fileName in '売上200801.xls', '売上200802.xls'
変更後
FOR Each fileName in FileList *.xls
combine-many-excel-files-128「FileList」の後ろには検索するファイルを指定します。このときワイルドカードが使用でき、「$」で任意の一文字、「*」で任意の複数文字をあらわせます。
つまり「*.xls」ですべてのExcelファイルを一括で指定できます。

リロードを実行してください。大量のファイルが一気に読み込まれることが分かります。
combine-many-excel-files-129
統計ボックスでデータの件数を確認すると、約1万3千件になっています。
combine-many-excel-files-130
以上で繰り返し処理の基礎については終了です。


ファイル名から年月の項目を作成する

では、今回読み込んだExcelファイルをあらためて確認してみましょう。
年月のデータはExcelファイル中にはありません。
そのため、年月の値が必要であれば、ファイル名から値を抜き出して使用する必要があります。
combine-many-excel-files-131
[ロードスクリプトの編集]画面を起動してください。
ファイル名を取得するには、ファイル関数を使用します。
ファイル関数にはいくつか種類がありますが、ここではFileBaseName関数を使用するとよいでしょう。FileBaseName関数はファイル名を拡張子なしで取得します。
また、文字列の一部を抜き出すには、Mid関数を使用するとよいでしょう。

LOAD文に年と月の項目を追加してください。
LOAD Mid(FileBaseName(), 3, 4) as 年,
     Mid(FileBaseName(), 7, 2) as 月,
     日, 
     :
combine-many-excel-files-132
関数の指定はそれぞれ以下の意味になります。
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
      |--------|  |--|
          年       月
リロードを実行してください。
リロード後、テーブルボックスに「年」と「月」の項目を追加します。
combine-many-excel-files-133
[ロード順]ボタンをクリックして項目を並び替え、[OK]ボタンをクリックします。
combine-many-excel-files-134
「年」と「月」の項目が追加できました。
combine-many-excel-files-135

以上で一つ目の例は終了です。

つぎのページでは、ディレクトリが複数ある場合の例を紹介します。
Excelファイルの統合2(ディレクトリが複数ある場合)