会計ソフトから出力したCSVデータをExcelのピボットテーブルで利用しやすくするためのデータ整形・加工・クレンジングについて考えてみた件

2022年10月8日

会計ソフトから出力したCSVデータはそのままの状態ではExcelのピボットテーブルで集計しにくいと感じる時があります。

そういった時の対応策としてデータの整形方法を考えたので備忘記録代わりに記事にしておきます。

会計ソフトから出力したCSVデータをExcelのピボットテーブルで利用しやすくするためのデータの整形・加工・クレンジング方法
1. 会計ソフトの摘要欄への丁寧なデータ入力がピボットテーブルで集計するときに邪魔になることがある。
2. ピボットテーブルできれいに集計させるためにデータの置換作業が必要
置換するデータの数が多い場合にはExcelマクロで対応できるかも
まとめ

会計ソフトから出力したCSVデータをExcelのピボットテーブルで利用しやすくするためのデータの整形・加工・クレンジング方法

会計ソフトの摘要欄への丁寧なデータ入力がピボットテーブルで集計するときに邪魔になることがある。

会計ソフトの仕訳データをCSVデータやExcelなどに出力した後ピボットテーブルで集計して、報告用の資料や分析用の資料として使用することがありますが

会計ソフトに入力されたデータで家賃や水道光熱費などの毎月発生するような定期的な経費のデータについてはよく

「株式会社○○　■■代　何月分」

などと記載されていることがあります。こんな感じのデータです↓

会計ソフトへの入力としては特に問題はないのですが

ピボットテーブルで集計するとなると少し話が変わってきます。

というもの、この

「何月分」

と記載されているデータはピボットテーブルで集計する場合には全く別のデータとして認識されてしまうからです。

なのでこの状態でピボットテーブルで集計すると、下の図のように同じ会社へ支払っているにも関わらず別々のデータとして集計されてしまいます。

試しに、先ほどのデータをテーブル化してピボットテーブルで集計してみます。

まずはテーブル化します↓

次にピボットテーブルで集計してみたのがこちら↓

このように別々のデータとして認識されているため3行にわたって表示されています。

同じ会社に対して決まった金額を支払っているので、本来は下の図のように横並びに綺麗に集計してくれると見やすくなってわかりやすいのですが、それができないようになっています↓

なので、キレイに横並びで表示させたい場合には会計ソフトから引っこ抜いた仕訳データを整えてあげる（整形、クレンジングとか言われたりするんですかね？）必要があります。

ピボットテーブルできれいに集計させるためにデータの置換作業が必要

会計データから出力した仕訳データの摘要欄に

半角
全角
㈱
株式会社

といった、入力された文字について【揺れ】が無く、さきほど例に出した「何月分」だけを削除したい場合にはデータの置換機能でワイルドカードを使って処理することで一気にデータを整えることができます。

具体的にはデータを整えたい列を選択して↓

検索と置換機能を表示させて下記のように入力します↓

これを実行するとデータが整います↓

この状態でピボットテーブルで集計してあげるとキレイに横に並んだ集計表を作ることができます↓

置換するデータの数が多い場合にはExcelマクロで対応できるかも

「月分」以外にも半角や全角、㈱、株式会社、などの揺れがある場合であっても置換機能使って同じようにデータを整えることもできますが、データの数が多い場合にはこの置換作業自体が面倒になってきます。

なので、置換しないといけないデータが多数ある。

といった場合にはExcelのマクロを使って置換作業をするといいと思います。

こういった感じで置換する際のキーワードと置換後のデータを準備してあげて、

マクロのfor~nextやDo~loopなどの繰返し処理で一気にデータを整えてあげるとマシになるかと思います。

今回、マクロの記録を使ってサクッとつくったものですが、一応コードを載せておきます。
使い方に応じて変更や訂正すると実務でも使えるものになるかもしれません。

Sub Macro1()
'
'■会計データからエクスポートしたデータをピボットテーブルで
'■利用するために摘要欄のデータを整えるマクロ
 
 
Dim rename As String
Dim i As Long
Dim original As String
 
For i = 2 To 3
 
    original = "*" & Range("e" & i).Value & "*"  '■ワイルドカードを使って置換する
     
    rename = Range("f" & i).Value
 
    Columns("B:B").Replace What:=original, Replacement:=rename, LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
 
Next i
 
End Sub