毎月発生する面倒くさい作業をする日が今月もやってきた。モノグサな性格のくせによく事務職なんて、と自分でも思うけど、営業なんてもっとできないし、この分野で頑張っていくしかない。本日もこの結論に達したところで、少し作業のフローを改良してみることにした。
Contents
作業の概要
- 毎月初旬に、本社のデータベースから吐き出されたデータ（CSV形式）が添付されたメールが2通（取引詳細と累計）自動配信される。
- レコード数は数百行程度だが、フィールド数が多く、無駄に難しそうに見える。
- 受信したデータを、取引月・部門・取引内容・通貨でクロス集計する。
- 集計データを各営業部門へ送信し、取引高に誤りがないかを確認を依頼する。
- 以前頻繁に入力漏れが発生していたため。このような定期的な確認作業により入力漏れは減少。
- データを確定し、月次レポート（定量・定性など複数で使用）にまとめ報告。（了）
前任者からの業務引継ぎ時、「CSVデータをプリントアウトして、詳細データの「＊＊＊」って入力されている行を探す。それらのデータを取り出して、このファイルの該当月の該当部門・取引部分に転記して集計。その後、それらのデータの合計額が、累計データと合っているか確認する。」という説明をされて、「このXXXってコードが○○部門を意味しているとの理解でいいですか」と質問したところ「え？XXXってコードの説明どこかにあった？コードで単純に判断しないで」と言われた。一方で、その後、同一人物から「本社からの○台って数値が大体この数値と合ってるじゃん？だから、合ってる、って判断するの」という（ある意味正解の説明なんだろう）説明をされ、こっちとしては、正確に行くべきなのかテキトーでいいのか判断がつかずに混乱した記憶。
前任者のころから続いていた、受信データの数値と本社担当部署空の報告数値の乖離が一向に縮まらず、勤続数十年のベテラン営業さんに手伝ってもらって、その部門のデータを使って、CSVデータの構成を理解。これにより、効率化を進めることができた。
（私が最初に考えたコードによる理解は、単なる番号の羅列で表面的なものではあったもの、ベテラン営業さんの説明とほぼ一致。結局、方向性は合っていたのに、何で注意されたのか未だに理解出来ない。）
効率化の前には、現状の仕組みの理解が必須！
引継ぎ時に仕組みを教えてもらえずに苦労した話はたんまりある。
【Excel】他人から渡された使いにくいファイルたちとの付き合い方：その１
処方箋
今回取り上げる解決法は、前述の概要のステップ２のデータ集計の部分のみ。
ステップの説明はたった一文が、引き継ぎ時に教えてもらったとおりのやり方で進めると結構面倒くさい。
データ数やページ設定にもよるが、5〜6ページ印刷して、目視で「＊＊＊」となっているところを探して、蛍光ペンでハイライトして、そのデータを別のエクセルファイルに転記して・・・。
面 倒 く さ い 。
更に、引継ぎ時に渡された集計用エクセルファイルは単なる検算用集計ファイルで、ハッキリ言ってゴミ。無意味とまでは言わないまでも、果てしなく無意味に近い。同じデータベースから吐き出されているのだから、詳細の合計額とデータベースが壊れていない限り、一致する。
実際に使えるデータにするには、データベースの詳細データと累計データの数値が一致するかではなく、データベースのデータと営業部門のデータのどこに乖離が生じているのかを見つけやすい形にするべき。
ではどうやって、より意味のある集計データを作成するのか。
- PowerQuery
- ピボットテーブル
これら2つで実現する。多分、マクロとかでも出来るが、この仕組を作った当時、私がPowerQueryにハマっていたので、PowerQueryを使った。
私は後述するように少し加工したが、受信データを元にしたクロス集計くらいであれば、エディタを開いてコードを追加する必要はゼロ。マクロよりも簡単。しかも、拡張子はxlsxのまま。
仕掛けの簡単な説明（改善前）
これまで半年くらい使っていたのは、以下のようなもの。
- フォルダをひとつ用意する。
- データ集計用エクセルファイルを用意する。
- Power Queryを用いて、2.のファイルに、1.で用意したフォルダのファイルを結合（Append）してデータテーブルを作る。
- テーブルは【Connection Only】と設定して、シートには表示させない。ファイルサイズも小さくて済む。
- 3.で作ったテーブルをもとに、ピボットテーブルを作成。
- デフォルトのテーブルのデザインは微妙なので、デザインをカスタマイズ。そのまま各営業チームへの報告書として使う。
作業内容
上記の仕組みを実際にどのように使うのかが以下。
- 新しいデータが届いたら、（仕掛け）1.のフォルダにファイルを保存。
- （仕掛け）2.のファイルを開き、データタブの「すべて更新」（もしくは
【Alt + Ctrl + F5】）を2回クリック。 - 出来上がったレポートをそのまま送る。(もしくは、更に加工して送る。)
今回の改善箇所
仕掛けの説明の3.の部分がこのワークフローの肝だけど、そっちは別の機会に振り返るとして、まずは今日の改善したことの記録。
作業内容の3.の「更に加工」っていう作業がなぜだか突然この上ないほど面倒な作業に感じたので、列を追加して更に大きなグループ分けをして再集計作業も作業内容の2.「すべて更新」までで終わるようにした。
手作業でしているときの集計ルールを改めてゆっくりと追いかけてみると、部門・取引の大項目の順に確認して、最後に取引内容のキーワードを拾って集計再集計していた。具体的（？）には以下のような感じ。
- 【部門】A, B, C、
- 【大項目】輸出、輸入、
- 【取引内容】国/形態/商品項目/相手先企業コードなど。
取引内容は「形態/国/コード」の場合もあれば「コード/形態/商品項目/国」もあるといった具合で、項目数も決まっていなければ、取引によって項目の並べ順もバラバラ。
再集計を希望しているのはA部門だけで、再集計の際のグループ数はかなり少ない。「A部門の輸出で○○という取引内容なら、カテゴリー１」「A部門の輸出でXXという取引内容ならカテゴリー１」と分けていって６グループ程度。そして彼らは、百万円単位で数値を把握したい種類の人達。（表示形式で百万円単位にする方法は不可。）
解決策
Power Queryで【Add Custom Column】というのを選択して、2列追加。行にカテゴリー（以下（２））、値に単位が百万円の数値（以下（1））を設定したピボットテーブルを作成して完成。
具体的な設定
以下は、Power Queryで【Add Custom Column】をクリックした後の設定。
(1) 日本円データを100万で除算
- 追加する列の名前を設定。
- 特にルールはないけど【JPY_mil】とか後で何となく思い出せるようなものにしておく。
- 100万で除算
- （元の日本円データのフィールド名がJPYであると仮定）
= [JPY] / 1000000
- （元の日本円データのフィールド名がJPYであると仮定）
- クエリのステップの名前を変更。(例：Add JPY_mil Column)（了）
(2) if関数で条件ごとにカテゴリー分け
基本ルール（１）if
if 条件 then
条件をみたす場合
else 条件を満たさない場合andやorは英語のようにそのままつなげるだけ。このあたりはExcelの数式よりも簡単。
基本ルール（2）Text.Contains
Text.Contains ([フィールド名], "文字列")エクセル関数だとワイルドカード(*)を前後に付けたようなもので、VBAだとLikeのようなもの。追加する列の名前を設定。
例：TranCategory（元の日本円データのフィールド名が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を返す。最後に、クエリのステップの名前を変更しておしまい。（例：Add TranCategory Column）
今後の課題
今回はカテゴリー数が少ないこともあってif関数だけでなんとかする、という力技だったけど、メンテナンスのしやすさを考えるならば面倒臭がらずに参照テーブルを作っておいた方がいいと思った。
今後の改善候補箇所は、受信したデータの保存自動化だろうか。フロー（MS FLOW）を使えば、メールを受信したら指定のフォルダに格納してくれる。あとは、集計用エクセルを開いて更新をすればおしまい。これならカップヌードルを作る時間と同じくらいまでにはなるんじゃないだろうか。
（追記）
自動化した。自分でする作業はマスタエクセルファイルを開くだけという恐ろしくラクな作業になった。モノグサもここまでできてしまったか、といった感じがする。
おまけ
Power Query の日本語情報はあまりヒットしないのだけど、英語で見つけるサイトで必ず紹介されている本、『M Is for (Data) Monkey: A Guide to the M Language in Excel Power Query』。
本家のサイトもよく訪問するけど、何だかんだで本も購入。Kindle版だからか、書き方のせいか、私にはやや見づらい印象があるけど、結局のところ知りたい情報が網羅されているのはこの本くらいだから我慢して使っている。Kindle版の方がずいぶん安い。