匍匐前進日記

行政職で森林・林業にかかわってます。浅いIT知識(趣味の範囲)を使って楽しようと思ってます。So-netから移行してきました(20160620)。移行前のは20100107~。記事は時々加筆修正することがあります。

役所の帳票を二次元表形式にする

(200116追記)

お役所が大好きなエクセルをワープロ的に使う様式。
1ページ=1シートで作ってたりします。

こんなの↓。

f:id:haruchin-puripuri:20200115003619p:plain

どう考えても検索するのに適してないデータですが、「電子化してるか」と聞かれると「電子化してます」って答えてるかもしれない。

データベースに読み込ませて検索をしやすくするにはいったん二次元表にしておくべきだと思うんですが、意外とめんどくさかったので備忘録として記録する。

 

1.ひとまとめにしたいエクセルブック(複数)を一つのブックにまとめる。

ファイルの数がたくさんある場合、一つのエクセルブックファイルにまとめるのにVBAを使うのもアリです。というか使わないとしんどいです。

例えばコチラ↓が参考になります。

www.moug.net

2.シート名を連番にする。

連番にするVBAを使います。

コチラ↓が参考になります。

www.relief.jp

3.統合後のエクセルブックの末尾に二次元表用のシートを追加する。

4.シート名「1」の様式から二次元表のシートにリンク貼り付けする。

この作業が二次元表に変換する骨組みになります。

5.INDIRECT関数を使ってシート名+セル参照で複数シート→二次元表に変換

4.でできてるリンク元の参照式をINDIRECT関数を使ってシート番号を使って任意のシート番号から参照できるようにします。

例えば、「=1!a1」→「=INDIRECT(a10&"!$A$1")」

※a10は参照させたいシート名を指定するセルのアドレスです。

※$記号で参照したいセルのアドレスを絶対指定すれば数式コピーしてもアドレスがずれない。↓のようにまとめたいシート数に合わせて行コピー。

f:id:haruchin-puripuri:20200115005951p:plain

 

6.シートをコピーして値貼り付け

5.でできたシートは単なる参照式のあつまりで、そのままではデータベースに読み込ませることはできないので、別のシートにコピーして値貼り付けする。

これでとりあえず2次元表には変換できますが、1つのセルに複数の情報が入ってる場合は、これを分割したり、文字を数値に変換したりといった手間が必要になります。