ExcelとUiPathで弥生会計税区分表を組み換える
2018.08.27
Category:Excel関係
趣味の問題なのですが、弥生会計で表示される消費税の科目別税区分表がとても見づらく感じてしまいます。
弥生会計のデータをExcelで組み換えて表示を変えることがあるのですが、何度も繰り返すのも嫌なのでUiPathを使って自動化を行ってみることにしました。
弥生会計の消費税科目別税区分表の並び方
弥生会計を使ったことのある方はご存知だと思いますが、消費税科目別税区分表を出力するとこのようなレイアウトになります（金額はすべて架空の数値です）。
このレイアウトの何が見づらいかというと、たとえば売上高について「課税売上8%：500万」と「輸出売上：200万」のように、勘定科目ごとの税区分が縦に並んでいる点です。
これが非常に見づらく感じてしまうのは、消費税申告を行う際はこれまで以下のようなレイアウトの科目別税区分表を使用していたからだと思います。
このレイアウトには以下の3つの特徴があります。
- 各科目の試算表科目の期末残高が表示されている
- 試算表残高ごとに消費税の可否区分が「横に」内訳として表示されていて各科目の残高との一致が確認できる
- 縦の列を集計すれば各税区分の合計額を確認することができる
弥生会計に搭載されている機能として表示方法を上の表のような配置に変えることができないか探したのですが、発見できませんでした。
とはいえ弥生会計の表示に慣れることもできそうにないので、Excelを使って自分の趣味に合う表示に組み換えることにしました。
なるべく毎回の手順を減らしたいので、関数を入れたExcelファイルを作製して、そこに弥生会計から出力したデータをコピー・ペーストをすれば組換えが出来るようにしています。
ただし以下のような弥生会計とExcelとの間をつなぐ作業は、通常は自分でPCを操作してマニュアルで対応する必要がありました。
- 弥生会計の関連データをcsvで出力・保存
- 保存したcsvをExcelファイルにコピー・ペースト
- Excelファイルを整形（必要に応じて）
UiPathを使えば、複数のアプリケーションを自動で操作することができます。そこで、UiPath（とVBA）を使ってこのマニュアル作業も自動で行えるようにしてみました。
Excelファイルへ側の設定
まずExcelファイル側での関数の設定ですが、大きく以下の3つの処理に対応できるようにしています。
- 弥生会計から出力した縦並びの消費税科目別税区分表のcsvファイルを所定のシートに貼り付ければ別のシートに横並びで表示させる
- 弥生会計から出力した残高試算表のcsvの各科目の期末残高が消費税科目別税区分表の税区分の合計値と一致しているかをチェックする
- 税抜課税売上・課税仕入それぞれの合計額に消費税率をかけて計算される理論上の消費税残高と、試算表上の仮受・仮払消費税の残高の差異が正常な範囲内にあるかを確認する
この件に関しては、Excelファイル側の関数の設定の方がUiPathでの設定よりも複雑になってしまいました。
その一番の原因が今回組換えファイルを作るきっかけにもなった、上記1．の「縦並び」の区分表を「横並び」に組み換える関数の設定です。
以下のように縦に並んだ弥生会計の「売上高・課税売上8%」と「売上高・輸出売上」をExcelファイル上はそれぞれ別の列に飛ばす必要があります。
この問題については、今回はINDEX関数とSUMPRODUCT関数を組み合わせて対応することができました。
どのような関数を組んだのかを売上高・課税売上8%の金額（上記の赤枠部分）を取り出す場合を例に文字で表すと、このようになります。
”＝INDEX(弥生の税区分表,SUMPRODUCT((条件１：弥生の税区分表の対象列が「売上高」)×(条件2：弥生の税区分表の対象列が「課税売上8%」)×ROW(弥生の税区分表の対象列)),6列目)”
INDEX関数は「参照範囲』の中の指定した「行」と「列」が交差するセルの値を取り出すことができます。
今回「参照範囲」となる縦並びの税区分表では同じ列に課税売上と輸出売上が記載されているので、INDEX関数で指定する「列」は固定できます（算式の「6列目」の部分です）。
問題となるのは「行」の場所の特定です。
参照範囲内の、ある列が「売上高」で他の列が「課税売上8%」という2つの条件を満たしてしている行の場所を特定しなければならないのですが、算式内の青字部分のような形でSUMPRODUCT関数を設定すると、弥生会計の税区分表内で2つの条件を満たす行番号を取得することが可能です。
「条件１」×「条件2」で、2つの条件を満たす行が参照範囲に存在するかを判定して、そこに「×Row（）」を加えるとその場所の行番号を特定できる仕組みになっています。
この方法以外でも、たとえば参照元のシートに「売上高課税売上8%」という文字列を作成する作業列を作成すればVLOOKUP関数でも同じようなことが出来ると思います。
このほかにも各種関数を使用していますが、細かい調整が多いので割愛いたします。
UiPath側の設定
あまり動きはないのですが、一応こちらがUiPathで作成したフローを実行した際の動画です。
UiPath側の設定はExcel側の設定よりも簡単です。作成したフローは以下のようになっています。
（1）ファイルの保存先などの設定
フローの左側半分は、後々使い回しができるようにファイルの保存先や弥生会計のバージョン情報などをAssignアクティビティで設定しています。
弥生会計のバージョン情報はUi Explorerを使って取り出していますが、弥生会計18AEの場合は以下の部分です。
“<wnd app=’ykaikei18.exe’ cls=’YPFrameWin_KaikeiApplication’ />”
（2）消費税科目別税区分表と残高試算表のcsvファイルの出力・保存
フローの右側の上2つのブロックで消費税科目別税区分表と残高試算表のcsvファイルをそれぞれ出力・保存しています。
弥生会計はキーボードだけで操作できるようになっているので、以下のように「ファイル名指定」以外はすべてSend hotkeyアクティビティを使って設定可能です。Enterを3回押していますが、最後のEnterは上書保存をしない場合は不要だと思います。
消費税科目別税区分表の出力・保存
「Alt+R」→「T」→「Z」→「F5」→「Alt」→「F」→「E」→「ファイル名指定」→「Enter」→「Enter」→「Enter」
残高試算表の出力・保存
「Alt+R」→「S」→「G」→「P」→「Alt」→「F」→「E」→「ファイル名指定」→「Enter」→「Enter」→「Enter」
（3）csvのデータをExcelセルに貼り付ける
フローの右側一番下のブロックでは、消費税科目別税区分表と残高試算表csvの対象範囲をRead Rangeアクティビティで読み取って、関数を設定しておいたExcelファイルにWrite Rangeアクティビティを使用してデータを張り付けるように設定をしています。
以前Excelのデータを達人シリーズへ転記する方法をご紹介した時も同様ですが、UiPathでExcelを操作するためには「UiPath.Excel.Activities」を別途インストールする必要があります。
UiPathでExcelのマクロを動かす
これも趣味の問題なのですが、上記のプロセスを組んだ時点では残高0の科目や税前利益欄など消費税の検証時には別になくてもかまわない行も表示されています。
個人的には（削除ではなく）非表示にしたかったのと、csvファイルを張り付ける前に一旦貼り付け先ファイルに残っている既存のデータをクリアしたかったので、これらの動作を行う簡単なマクロをVBAで作成して、Excel側に組み込みました。
このマクロをUiPath側で動作させられないか調べてみたところ、ExecuteMacroアクティビティというものがありました。これを使うとマクロを動かせるので、このアクティビティをデータを貼り付ける手順の前後に追加しました（赤枠部分です）。
消費税のオーバーオール
UiPathで一連の処理を行って出来上がるExcelの表がこちらです（数値は架空のものです）。各税区分の合計は縦の列で、各勘定科目の税区分内訳と試算表残高との突合は横の行で確認ができます。
同じファイルの別の場所では以下の表のように仮受・仮払消費税の帳簿残高が理論値と比べて異常な値となっていないかのチェックをしています。
税抜経理が前提となりますが、私が以前勤めていた税理士法人ではほぼ必ずこのような検証を行っていました。
呼び方は色々あるとは思いますが、この検証を行うことで「全体的に」合っていそうかどうかを確認できるという意味で、「オーバーオール」と呼ぶことが多かった気がします。
オーバーオールは一つ一つの取引を確認するわけではないため、たとえば課税と非課税が混在する勘定科目の中に、本来課税とすべき取引が非課税で計上されているような間違いを発見することはできません。
しかしオーバーオールを行うことで、あくまで全体的にですが、会社の消費税に関する経理処理が適切に行われているかどうかの確認が可能となります。そのため、とくに取引量が多い法人の消費税申告を行う場合は有効な方法です。
追伸：
弥生会計の表示について私と同じように思っていてご興味のある方がいるようであれば、今回作成したExcelファイルを差し上げます。
同業の方かどうかにかかわらず、ご必要の方はお問い合わせフォームからご連絡ください（動作保証はできかねます）。UiPathがなくてもExcelファイルのみで使用可能です。
————
※この記事は、投稿日現在の日本の税法に基づく一般的な取扱いを記載したものであり、特定の事実関係によっては、税法上の取扱が大幅に異なることがあり得ます。この記事の情報に基づき具体的な決定や行為を起こす際は、当事務所、または他の税務プロフェッショナルに相談することをお勧めいたします。
