スポンサードリンク

Excelでビッグデータを乗りこなせ(1.データベースでやりたいこと)

私は職場で経理を担当していて、月次の資料を作る際に

・会計ソフトから仕訳データをCSVで出力

・出力したデータをExcel VBASQLiteにインサート

Excel VBAからSQLを使用してSQLiteから集計後のデータを出力してExcelへ転記後、関数を使用してフォーマットに記載

ということを行っています。

なぜ会計ソフトがあるのにこのような事を行っているのか。

会計ソフトの集計データがつかいにくいから

試算表はともかく部署別PLなんて、データで出力するとわざと使いにくくしているのかと思わざるをえないデータレイアウトで出力されます。

これを回避するために自分で見やすい表を作成するための環境を作成しています。

使用している環境

Excel 2013

SQLite for Excel

・SQLite3(データベース)

Excelに集計したい表やSQLでの開始するためのボタンを設置しています。もちろんVBAを使用しています。

SQLiteはデータベースです。通常であればExcelに貯め込む仕訳データをこのデータベースに蓄積しています。

SQLite for ExcelExcelからSQLiteを操作するために必要なものです。Excel VBAのクラスと外部DLLで構成されています。

現在の運用の壁

上記の通り、現在でもSQLiteというデータベースを使用して資料を作っているのに、なぜこのようなネタを書いているのか?

今現在担当している会社は月間の仕訳行数が2,000件ほどしか発生していません。そのため数年間分のデータが蓄積されている状態でもデータベース内には10万件前後のデータしかありません。

ところが少し前から別の会社の担当もすることになり、今までと同じやり方で集計してやるかと思ったら壁にぶち当たりました。


データ量が全く違ったのです。


新しく担当する会社では月間の仕訳行数が50万件ほどあります。

1ヶ月だけで現在担当している会社のこれまでの総行数を余裕で超えてきます。

しかも少なくとも1年間分はデータの蓄積をしないと集計の用をなしませんので、そうなると50万件×12ヶ月で600万件のデータを扱わなければなりません。


試しに1ヶ月分のデータを従来の仕組みに取り込んでみましたが。

CSVSQLiteインサートするのが遅い。あるいは途中で止まる。

SQLiteからExcelへ転記するのが分単位でかかる。

運用ができないわけではないのですが、従来の会社ではストレスレスで運用できていた仕組みが新しい会社ではストレスフルな仕様になってしまっています。


選択肢としては2つ

1.新しい会社ではこの仕様を諦めてもうちょっとシンプルなものを用意する

2.このデータ量でも、もっとスムーズに扱える環境・コードを用意する

1.現在の仕様は最小のデータ単位である仕訳データをデータベースに取り込んで、それを集計しています。これを諦めて会計ソフトからある程度集計した後のデータを出力して、それをExcelで関数を使用してレイアウト変更を行う。

これであれば動かないという事やデータの処理に時間がかかることはなくなります。

2.現行の使用自体は気に入っています。ただ大量のデータに対応できていないだけ。であれば同じ仕様で使用できるように環境を見直せば、今まで同じようにストレスレスで業務ができます。


手っ取り早いのはもちろん1です。集計自体は会計ソフトがしてくれていますので、後はフォーマット変換の関数を用意するだけです。しかし…

おもしろくない

Excelの勉強の面白いところの1つは、知識・スキルの習得が業務の効率化に直結するところです

1の解決方法は仕事の限られた時間の中で行うにはまっとうな解答だと思いますが、自分にとっては何のスキルアップにも繋がっていません。

しかし2の方法で解決することが出来れば、今回だけでなく今後も自分のスキルとなって自分を助けてくれるでしょう。

まとめ

ということで何とかして大量のデータを扱えるようにスキルアップしていきたいと思います。

大きく出たわりには、飽きっぽいので頓挫する可能性も往々にしてありますが、ボチボチ頑張っていきます。