税務会計ソフトのICSからExcel出力したデータはそのままではExcelのテーブル機能やピボットテーブル機能への使いまわしがしにくいです。
そこでExcelマクロで不要なデータ行などを削除し、テーブル機能などで加工しやすいようにマクロを考えてみたので記事にしておきます。
目次
Excel出力したデータはそのままではExcelでの加工が難しい。
今回想定しているのはICSの「出力」業務の「仕訳日記帳」から仕訳データをExcel出力することを前提としています。
この部分です↓
この業務画面で下記の画像のように設定したうえで「F3 Excel出力」を選択して仕訳データを出力しています。
で、Excel出力すると下記のような形式でデータが出力されます↓
このExcelへ出力したデータですが、個人的に使いにくい点が３つほどあります。
赤枠でそれぞれ囲っている部分がデータをExcelで加工していく際に邪魔なところです。
一つ目は日付のデータがぱっと見た感じ、日付のデータっぽいんですが、実は空白スペースが入っていたりしてそのままでは日付のデータとして使えません（当社調べ）
この部分は日付データとして使用するためにデータの加工が必要になります。
次に真ん中の赤枠部分ですが、これはページが切り替わるタイミングで自動で挿入されるタイトル行
やデータ項目のタイトル行なんですが、これは邪魔です。
最後にデータの末尾に出力したデータの合計金額などが記載されており、これも邪魔です。
こういった（個人的に邪魔な）データを削除しないとExcelのテーブル機能やピボットテーブル機能が使えません。
で毎回毎回手作業でデータ加工するのは面倒なのでExcelマクロを考えることとしました。
どのような動作になるかは下の動画をみていただければと思います↓
この状態まで加工できれば前よりはデータの加工・分析がしやすくなると思います。
ICSの仕訳日記帳業務から出力したExcelデータを加工するマクロ
今回このような感じでマクロを作成してみました。
Sub data_organize()
'■ICSの出力業務「仕訳日記帳」からExcel出力したデータのうち不要な行を削除するマクロ
'■変数宣言
Dim target As Range '「仕訳日記帳」と記載されたセルを格納する
Dim target2 As Range '「＊＊＊」と記載されたセルを格納する
Dim title_row(0, 9) As Variant 'タイトル行の配列
Set target = Range("d:d").Find(what:="仕訳日記帳", lookat:=xlWhole)
'■処理実行部分
'■冒頭の「仕訳日記帳」などの4行のデータを消去する
If target Is Nothing Then
MsgBox "冒頭のデータ削除完了"
Exit Sub
End If
target.Resize(5).Offset(-1, 0).EntireRow.ClearContents
Do
Set target = Range("d:d").FindNext(target)
If target Is Nothing Then
MsgBox "冒頭のデータ削除完了"
Exit Do
End If
target.Resize(5).Offset(-1, 0).EntireRow.ClearContents
Loop
'■最終データ付近にある合計金額が記載されたデータ行を削除する
Set target2 = Range("c:c").Find(what:="＊", lookat:=xlPart)
If target2 Is Nothing Then
MsgBox "冒頭のデータ削除完了"
Exit Sub
End If
target2.Resize(6).EntireRow.ClearContents
MsgBox "末尾に記載された合計金額データ削除完了"
'■ タイトル行となる配列のデータを作成する
title_row(0, 0) = "日付"
title_row(0, 1) = "伝票番号"
title_row(0, 2) = "借方科目"
title_row(0, 3) = "借方補助科目"
title_row(0, 4) = "貸方科目"
title_row(0, 5) = "貸方補助科目"
title_row(0, 6) = "金額"
title_row(0, 7) = ""
title_row(0, 8) = "摘要"
title_row(0, 9) = "消費税区分"
Range("a1", "j1").Value = title_row
'■A列全体について空白があるセルの「行」全体を削除する
Columns("a:a").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Columns("h:h").Delete
'■A列の日付セルにある余計な空白データを削除
Columns("A:A").Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
End Sub
ICSの仕訳日記帳業務から出力したExcelデータを加工するマクロの内容説明
ざっくりにはなりますが、今回作成したマクロの内容に関して説明をしておきます。
「仕訳日記帳」というキーワードを検索してその周辺のデータをクリア（※削除はしない）する。
まずは変数宣言をしておきます。
今回は「target」という変数と「target2」という変数を準備しています。
変数「target」には「仕訳日記帳」と記載されたセルを格納します。
変数「target2」には処理の後半部分で「＊（アスタリスク）」のマークが記載されたセルを格納するために使います。
次に変数「target」で「仕訳日記帳」と記載されたセルをFindを使って取得したらその取得したセルをResizeを使って取得した範囲を拡大し、その後Offsetを使って取得した範囲を移動させます 。
具体的にはresize（5）とすることでこのように範囲を拡大し
さらにoffset(-1,0)を使うことで範囲をこのよう上にセル１つ分移動させています。
この移動させた範囲の列全体をEntirerowで取得してデータをクリアしています。
この時点では列の削除はされておらず、セルの記載内容が消去・クリアされた状態になっています。
Do Loopステートメントで仕訳日記帳の周辺データをクリアする作業を繰り返す。
ここまでの作業で一番上の仕訳日記帳とその周辺エリアのデータのクリアすることができたので、このエリア以外の仕訳日記帳と記載されたセル及びその周辺エリアのデータのクリアもする必要があります。
具体的にはこの箇所のことになります。
そのために変数「target」に対してFind Nextを設定し、仕訳日記帳と記載されたセルをさらに検索させます。
そしてそのセルが見つかったらセルの周辺のデータをクリアさせるという作業をさせています。
この部分です↓
この作業をDo Loopステートメントを使って、「仕訳日記帳」と記載されたセルが無くなるまで繰り返します。
そして、もし仕訳日記帳というセルが見つからなかった場合（すべての仕訳日記帳と記載されたセル周辺のデータをクリアしきった場合）にはその時間で作業を終了するようにIfステートメントによりケアをしています。
「＊」というキーワードを検索してその周辺のデータをクリアする。
ここまでの作業で「仕訳日記帳」と記載されたセル周辺のデータに関してすべてデータの消去が完了したことになるので、もう一つ邪魔なデータである末尾データのこの部分に関してデータを消去する必要があります↓
これに関しては変数「target」と同じ考え方で変数「target2」でFindを使って「＊」が記載されたセルを取得させています。
その後、Resizeで範囲を拡大してデータを消去しています。
空白行を削除する。不要な列を削除する。
ここまでの作業が終了したらA列にある空白セルを選択して行ごと削除していきます。
また、H列に関しては列全体が空白となっていて邪魔なのでこれも削除してきます。
この部分です↓
A列の日付データの空白スペースを置換で削除する
最後にA列の日付（のようにみえる）セルのデータの中にある無駄な【空白スペース】を置換によって削除していきます。
この部分の処理に関しては【マクロの記録】により実行し、記録されたコードをそのままコピペして使っています。
以上でデータの加工が終了しましたので、あとはデータのテーブル化などをして自分の好みの分析に使っていただければと思います。
まとめ
ICSの仕訳日記帳業務からデータをExcel出力した場合に発生する使いにくいデータをマクロを使ってテーブル機能やピボットテーブル機能を使いやすい状態に加工するまでの方法を記事にしました。
Excel出力に対応しているけれども、いざ出力してみるとそのままでは使えない、使いにくい状態のデータになっていることは今回のICSに限ったことではなく、ほかの会計ソフトでもありますのでそういった場合にはこういった方法で対応してみてはいかがでしょうか。
メーカー側がExcel出力する際のデータの並び方などを利用者側に都合が良いように、つまり加工がしやすいように仕様を変更してくれると助かるのですがいつになるかわかりませんので。
【本日の近況報告】
スマートフォンを買い替えて1か月もしないのに画面にひび割れを入れてしまいました。
メーカーの修理センターに送って現在修理中です。
破損に関してはメーカー保証はないので自己負担になるそうで、痛い出費となりそうです。
【本日の１曲】
fatboyslim / ya mama
最近どこかのメーカの炭酸水のCMでこの曲が使われていたので久しぶりに耳にしました。
自分の中でブレイクビーツというとこの人になります。
