Excelを使って仕事を工夫できないか？と考えるようにしています。
今回の事例は、Excelデータから支払い処理用のCSVファイルを生成するというものです。
入力を減らすには？
仕事をする中で、入力を減らすということは意識しておきたいものです。
その手段としてはいくつかあると思うのですが、わたしが考えるのは次のようなものです。
- ネットから取引データをダウンロードする
- Excelを使って、インポートする
- RPAに入力をお願いする
ただ、ネットに連携できるデータがない取引もあります。
ネットで利用できるサービスの中には、CSVファイルを読み込むことができるものが多く、結果として入力を減らせます。となると、Excelは使いたいところです。
もとのデータがあれば、関数を使ってデータをつくることもできますし、マクロをつかってCSVファイルを自動で生成することもできます。
楽天銀行からの支払データを生成
会計ソフトでは、支払い処理につながるデータをつくることはできません。（わたしの知る限りですが。）
振込データをつくるとなると、銀行ソフトに入力する方法がメインですが、いわゆる入力画面から1件ずつしか入力できないケースが多いです。
ある程度の件数あると、それなりに大変かと。
ただ、銀行によっては、支払いデータをCSVファイルでインポートすることができます。
今回の事例は、会計データをインポートしているExcelデータがあり、それをもとに、支払いデータを準備して、ネットバンクでインポートするという流れです。
楽天銀行の法人口座で【WEB-FB】総合振込(基本料無料)というサービスを利用する前提です。こちらは事前の申込みが必要です。（個人口座にはないサービスです。）
必要なCSVデータの仕様は、こういったものです。
楽天銀行HPより抜粋
CSVファイルの作成方法（WEB-FB総合振込）
この順番にデータを並べる必要があります。じっさいに並べてみるとこういう感じになります。
左のデータは、会計のインポート用に使っているデータ（一部省略）で、その隣に支払い用のデータをつくっていきます。
発生日から支払日へ
当月末締めの翌月末支払いなら、＝EOMONTH(請求書$A4,1)とすると表示できます。
翌々月末なら＝EOMONTH(請求書$A4,2)という感じです。
ただ、今回設定したいのが、支払日が翌々月15日、かつ、休日の場合にはその翌日払いというもの。
それを、WORKDAYという関数を使って次のように設定しました。祝日については祝日リストをつくりそこからデータを指定しています。
|
1 |
=WORKDAY(EOMONTH(請求書!$A4,1)+14,1,祝日リスト!$AE$2:$AE$51) |
ちょっと複雑になるのですが、設定は最初だけですので、ここは頑張って設定しましょう。
さらに、難関はあり、実行日のデータを通常の日付データではなく、月日の4桁だけにしないといけません。
たとえば、2020年11月16日なら「1116」が必要ですし、2020年8月10日なら「0810」というようにです。
これは、I列でTEXT関数で「=TEXT(請求書!$G4,”mmdd”)」月日だけ表示するようにしました。
「mm」が月、「dd」が日を意味しています。これをTEXT関数で文字列にしています。
日付をこんなややこしくしなくても…とは思うのですが。
口座情報
あとは、取引先の振込口座の情報。これはネットバンクを使っていたら、そこから振込先データをダウンロードできる場合があります。
イチから入力しないで、そのデータを利用しましょう。
今回の事例では、ネットバンクから振込先のデータをダウンロードして、別のシートに貼り付け。
銀行コードのシートです。
その別のシート「銀行コード」からXlookupを使って、会社名をキーに引用しています。（会社名でもコードでもお好きな設定で。）
たとえば、J列は次のようにしています。
|
1 |
=XLOOKUP(請求書!$B4,銀行データ!$A$2:$A$6,銀行データ!$C$2:$C$6) |
あとは、これをCSVファイルで保存して、楽天銀行でインポートするという流れです。
今回は紙面の都合で紹介しませんが、実際にはマクロを使って指定する月だけをオートフィルターで抽出するという流れにしています。
支払先がいくつかあるという場合、ネットバンクを利用して試してみていただければ。他のネットバンクでも同じようなサービスがあると思うので。
Excelを使ってみる
会計ソフトもデータ連携ができるなど、便利な面はあるのですが、
- データ連携できない取引も多い
- 支払い処理につながらない（MFには債務管理という機能がありますがイマイチ…。）
- 入力は1件ずつ
など汎用性がないという点もあります。
それをカバーするなら、現状はExcelかなというイメージです。
マクロやUiPathを使えば、さらに効率化につながります。
他の事例にも応用がききますし、仕様変更があっても、すぐに対応することができます。
そして、じぶんの好みで設計できるというのも魅力です。
今回のようなデータをつくってみるとExcel利用の幅も広がり、勉強になります。CSVファイルを利用できるツールは多いので、ご興味あれば試してみてはいかがでしょうか。
【編集後記】
昨日はオフ。長女(11)の棚をDIYでした。あと1つ控えていて、DIYがもう少しつづきます。AppleのマップでLookAround（使える場所はまだ一部）を使ってみました。どこにもいけないので、海ぼたるをわたってみたり。トンネルもリアルに再現されていて楽しめました。途中のSAに寄れないのは残念でしたが、使いやすいかなと。
【昨日の1日1新】
※「1日1新」→詳細はコチラ
Apple Mapアプリ LookAround
