会計ソフトから出力したCSVデータはそのままの状態では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
B列のデータのように摘要が揺れに揺れまくっているデータについても
このマクロを実行すると
ピボットテーブルで集計しやすいデータに整形することができました。
まとめ
会計ソフトから仕訳データをCSV形式などで出力し、そのデータをExcelのピボットテーブルで利用する際のデータの整形・クレンジングの具体的なやり方を紹介しました。
今回紹介したようなやり方が面倒臭いと感じる人もいるかと思います。
その場合にはそもそもExcel（いわゆる川下・下流）でデータの整形をしないで済むように、会計ソフトへの入力する際に、「月分」は入力しないようにするであったり、摘要に「揺れ」をださないように（いわゆる川上・上流）工夫するといいのかもしれません。
【本日の近況報告】
プライベートで使っているタブレット端末の買い替えを検討しています。
当初はシャオミパッド5の購入を検討していたのですが、値上げによってipadと大差がなくなったのでこちらは断念。ipadが買い替えの第一候補に挙がっています。
ipadがそろそろ新しいモデルを発表しそうなので、それまで待つのかそれとも現行のモデルを買うか少し悩み中です。
【本日の１曲】
Gym Class Heroes/New Friend Request
先月、オリンピック金メダリストの内村航平選手のイベントに参加してきました。
内村選手といえば「体操」、体操は英語でGYM。
GYMといえばGym Class Heroesってことでこちらの1曲。正直名前は知っているけど曲はほとんど聴いたことがありません。
ですが、こんな感じのヒップホップは聴きやすくて好きです。
