カップヌードルを作る時間くらいで作業を終わらせたい。

毎月発生する面倒くさい作業をする日が今月もやってきました、今日。こんなものぐさな性格のくせによく事務なんて、と思うけど、営業なんてもっとできないし、この分野で頑張っていくしかないな〜、という考えで着地。本日も同じ終着点にたどり着いたところで、少し作業を改良してみることにした。

作業の概要

  1. 本社のデータベースから吐き出されたデータ(CSV形式)が添付されたメールが自動配信される。
  2. レコード数は数百行程度だけど、フィールド数が多くて無駄に難しそうに見える。(※)複数の部門のデータが混在しているので、部門ごと、取引内容ごとに日本円と米ドルベースで集計する(ときどき他通貨の記載もあるけど、日本円と米ドル換算額どのレコードにも絶対入力されている)。
  3. 当月だけじゃなく前月の数値も並べて表にまとめる。
  4. 違う粒度の集計を希望するチームには、そのチームから指示されたテンプレへ転記。(転記時に複数のグループを合算)
  5. 各チームで乖離があった場合には、原因を探す。(だいたいは期ズレ。たまに計上漏れ。)
  6. 乖離がなければ、事務所の月次レポートに確認済みの数値を記載して終了。

※ 引き継いだ当初、各フィールドが何を意味しているのか不明だった。(前任者に聞いたものの「知らん」と一周された。実際にはもっとやんわりとだけど、確実にその意図するとこは「知らん。聞くな。同じようにやっときゃ問題ねー」だった。。。)
年度末に発生する数値の乖離があまりにも大きくて、さすがに営業チームに数十年いる同僚に助けを求めてなんとか重要な項目を理解。
データを加工しようとすれば、そのパターンやデータの意味を理解しようとするから、もっとまともなフローが出来上がってたと思うんだけどな〜。とは言え、解決できたからいい。

処方箋

当時、Power Queryに魅了されてたという非常に勝手な理由で、Power Query機能を使用。でも、結果的に、メンテも簡単な便利なフローにできたと思う。

  • Power Query
  • ピボットテーブル

仕掛けの簡単な説明(改善前)

これまで半年くらい使っていたのは、以下のようなもの。

  1. フォルダをひとつ用意する。
  2. データ集計用エクセルファイルを用意する。Power Queryで、1.で用意したフォルダのファイルを結合(Append)してデータテーブルを作る。(テーブルは【Connection Only】と設定して、シートには表示させない。)
  3. 2.で作ったテーブルをもとに、ピボットテーブルを作成。(デフォルトのテーブルのデザインは大嫌いなので、デザインはもちろんカスタマイズしておく。)

作業内容

  1. 新しいデータが届いたら、1.のフォルダにファイルを保存。
  2. データ集計用エクセルファイルを開き、データタブの「すべて更新」(もしくは【Alt + Ctrl + F5】)を2回クリック。
  3. 出来上がったレポートをそのまま送る。もしくは、更に加工して送る。

今回の改善箇所

仕掛けの説明の2.の部分がこのワークフローの肝だけど、そっちは別の機会に振り返るとして、まずは今日の改善したことの記録。

作業内容の3.の「更に加工」っていう作業がなぜだか突然この上ないほど面倒な作業に感じたので、列を追加して更に大きなグループ分けをして再集計作業も「すべて更新」で終わっちゃうようにした。

手作業でしているときの集計ルールを改めてゆっくりと追いかけてみると、部門・取引の大項目の順に確認して、最後に取引内容のキーワードを拾って集計再集計していた。具体的(?)には以下のような感じ。
【部門】A, B, C、
【大項目】輸出、輸入、
【取引内容】国/形態/商品項目/相手先企業コードなど。
取引内容は「形態/国/コード」の場合もあれば「コード/形態/商品項目/国」もあるといった具合で、項目数も決まっていなければ、取引によって項目の並べ順もバラバラ。

再集計を希望しているのはA部門だけで、再集計の際のグループ数はかなり少ない。「A部門の輸出で○○という取引内容なら、カテゴリー1」「A部門の輸出でXXという取引内容ならカテゴリー1」と分けていって6グループ程度。そして彼らは、百万円単位で数値を把握したい種類の人達。(表示形式で百万円単位にする方法は不可。)

解決策

Power Queryで【Add Custom Column】というのを選択して、以下の2列を追加。

  1. 日本円データを100万で除算
  2. If関数で条件ごとにカテゴリー分け

行にカテゴリー(2.)、値に単位が百万円の数値(1.)を設定したピボットテーブルを作成して完成。

具体的な設定

以下は、Power Queryで【Add Custom Column】をクリックした後の設定。

1. 日本円データを100万で除算

  1. 追加する列の名前を設定。
    特にルールはないけど【JPY_mil】とか後で何となく思い出せるようなものにしておく。
  2. (元の日本円データのフィールド名がJPYであると仮定)
    = [JPY] / 1000000
  3. クエリのステップの名前を変更しておしまい。
    例:Add JPY_mil Column

2. if関数で条件ごとにカテゴリー分け

基本ルール(1)ifif 条件 then 条件をみたす場合 else 条件を満たさない場合
andorは英語のようにそのままつなげるだけ。このあたりはExcelの数式よりも簡単。
基本ルール(2)Text.ContainsText.Contains ([フィールド名]), “文字列”)
エクセル関数だとワイルドカード(*)を前後に付けたようなもので、VBAだとLikeのようなもの
  1. 追加する列の名前を設定。
    例:TranCategory
  2. (元の日本円データのフィールド名がTransactionであると仮定)
    = if Text.Contains([Transaction], “JPN”) and Text.Contains([Transaction], “Parts”)
    then “Category_A”
    else if Text.Contains([Transaction], “USA”) and Text.Contains([Transaction], “Parts”)
    then “Category_B”
    else “Category_C”
    訳:Transactionの列にJPNとPartsという文字が含まれていればCategory_A、USAとPartsが含まれていればCategory_B、それ以外はCategory_Cを返す。
  3. クエリのステップの名前を変更しておしまい。
    例:Add TranCategory Column

今後の課題

今回はカテゴリー数が少ないこともあってif関数だけでなんとかする、という力技だったけど、メンテナンスのしやすさを考えるならば面倒臭がらずに参照テーブルを作っておいた方がいいとおもった。

今後の改善候補箇所は、受信したデータの保存自動化かな。フロー(MS FLOW)を使えば、メールを受信したら指定のフォルダに格納してくれる。あとは、集計用エクセルを開いて更新をすればおしまい。これならカップヌードルを作る時間と同じくらいまでにはなるんじゃないだろうか。。。

 

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s