エクセルの自動集計(ピボット)を自動で作成、perl のサンプルスクリプト

      2018/02/04

エクセルの自動集計(ピボット)を自動で作成、perl のサンプルスクリプト

スポンサードリンク

Excelにピボット(pivot) という集計機能があります。
項目名+データー部を全部ファーカス(範囲選択)して
メニュータグから、
「データ」→「ピボットテーブルとピボットグラフレポート」→「完了」の手順で
集計表が出来てしまいます。
小さな表(家計簿等)だとたいした手間ではないのですが、表が大きいと結構面倒です。
又、表が小さくても、項目を沢山指定すると、面倒です。
仕事等で、毎月同じピボット表を作成する時も、面倒です。
自動化は勿論、マクロ(VBA)でもいいのですが、perl でやってみました
又、サンプルデータも付けました。
perl のスクリプトなら、結構使いまわしが楽です。
ピボットを一度使ってからこのスクリプトを使うと、効率化や便利さが分かるでしょう。。。
さあ!試してみましょう!

スクリプトの説明

スクリプト作成の流れと条件

**Windows で、エクセルがインストールされていないと、動きません。**
**Windows で動く「ActivPerl」 がインストールされていないと、動きません。**
perlの場合、プログラムと言わず、スクリプト(script)といいます。
スクリプトは「シフトジス(shift-jis)」で書いて有ります。
*理由:Windows およびエクセルのデータは、「シフトジス(shift-jis)」で書かれているからです。
Windows で動く perl の内、activeperl(アクティブパール)を使います。
1) まず、エクセルの原図を用意しておきます。
2) 中に家計簿のデータを書いておきます。(添付在り)
3) 「月ごとに誰が何にいくら使ったかを調べたい」とします。
4) 「月に対応する項目」「誰に対応する項目」「何に対応する項目」「いくらに対応する項目」を確認しておきます。
5) 対応する項目ごとに、スクリプトの中に定義しておきます。
元になるエクセルのファイル名
データの上の「セル名(A1)等」
項目名の最左端項目
項目名の2番目~必要項目名
集計(今回は「いくら)の項目名

* 実行については、独立したフォルダーで実行してください。*
* もし、大事なデータを消しても、責任はとれません。

スクリプトの概要

1) モジュールの読込
3) エクセルの起動
3) ファイルの読み込み
8) ピボットテーブルの作成
8) ピボットテーブルのグラフ作成
9) エクセルを、終了する。
*詳細は、スクリプトの中に、だいたい分かるように書いたつもりです。。

スクリプトの実施効果

このような煩雑作業を避けるには、自動化しかありません。
自動化は、マクロ(VBA)でもいいのですが、perl でやってみました。
「いまさら perl かよ!」と思うでしょうが、 「ActivePerl 」は、エクセルワードパワーポイント」等
マイクロソフトのWindows と非常に相性がいいのです
今度のデータは簡単なので、慣れた人なら、一回10分位で出来るでしょう!
このスクリプトを起動して、終了するまで、「10秒」で終わります。
大事なことは、データや項目が多くても、いつも起動して、終了するまで、「秒単位」で終わることです。!
それも、「グラフ」を作りながらの実行結果です。

「エクセル原図」、「スクリプト」、「加工後のエクセル」のダウンロード

スポンサードリンク

・ZIPで保存してあります。解凍してください。
ZIPの中身:エクセルファイルの原図、perl のスクリプト、加工後のエクセルファイル(参考)
家計簿_perl_excel_pivot.zip

activeperl のインストール

Windows で動く perl の内、activeperl(アクティブパール)を使います。
「いまさら perl かよ!」と思われますが、 「ActivePerl 」は、「エクセル、ワード、パワーポイント」等
マイクロソフトのOS(Windows) 、ソフトと非常に相性がいいのです。

activeperl のインストール記事

標準で入っているモジュール、win32::ole でエクセルを動かします。
標準で入っているモジュール、win32::API で、GUIのメッセージを出力します。
activeperl インストール記事は、以下からどうぞ。
ActivePerl 5.24.1 2402(32bit) インストール記

スクリプトのポイント(スクリプトが簡単に応用できるようにしておく)

スクリプトのポイントは、事前に必要な数値を、変数化しておくことです。
こうすることによって、項目を変更する時に、簡単に変更できます。
簡単に変更できることで、他のエクセルデータを加工する時に、応用できます。

まとめ

「エクセルマクロ」と「ActivePerl」 について

基本的に「エクセルマクロ」も「Win32ole-excelモジュール」も同じような物です。
私もエクセルのマクロを書いたこともありました。
しかし、エクセルのマクロエディタが、常時使っているエディタより使い難いので、嫌になりました。
又、エクセルファイルごとに、いちいちマクロを複写するのも、手間でした。
「こんな事なら、perl でやってしまおう」と思ったのが、きっかけでした。
エクセルでは「工程表の編集」等、色々やってます。
「Win32関連モジュール」は「ワード」「パワーポイント」「IE」「Windoesシステム」等で
結構自動化(効率化)ができるもんです!

プログラム(スクリプト)の効果

手作業よりも、プログラムでやった方が良いのは、誰でも?分かっていると思います。
でもなぜか「業務のプログラム化」って進みません。
「そんなことが出来るなら、今頃苦労してないよ!」って事なのでしょう。。
でも、サンプルが有れば、そんなに苦労しないはずです。
しかし、ネットには「実務用のサンプル」って、余りアップされていないですよね!
今後も、私が「実務」で使っていたスクリプトを公開していきます。

スポンサードリンク


 - ソフト・プログラム , ,

%d人のブロガーが「いいね」をつけました。