【Excel】(標準機能だけ)2分で地名一覧をExcelにまとめる

年度末でいろんなタスクの〆切が一気に押し寄せてきたところに、姉からとんでもなくどうでもいいタスクがやってきた。

妹には選択肢がない

「○○市の地名データ一覧がほしいんやけど、ネットで入手する方法ってあるの?エクセルに地名を入力したいんやけど。手入力が大変なので」

「なので」何?

私にラインするより、Google先生に尋ねる方がよっぽど生産的なのでは?と思いつつも、そんなやり取りをすると私が面倒くさいことになりそうな感じもしたので、おとなしく従うことにした。

別に1つの市の地名をエクセルに入力するためだけに、ごちゃごちゃ考えずに、一覧が載ってるウェブサイトをコピーしてエクセルに貼り付けて、最後にちょちょっと整形しておしまいでいい。(今回使った方法は後述)

前にもウェブサイトのコピーっていうことやったけど、今回は段違いに簡単。フィルタとGoToSpecialだけ使う。

 

ということで、データ探しからラインへ投稿まで所要時間2分。

「自分で調べて何とかしたらいいじゃん」という返信をして、ヒビを入れるような自体を招くよりずいぶんマシな着地点だった。

翌日、またラインのメッセージ。

「めちゃくちゃ助かった。ありがとうございます。ついでに○○市と○○市と○○市があったら最高なんだけど」

「だけど」何?というような野暮な返信はせず、おとなしく更に5分ほど作業。完了。私の平和は守られた。

基本のおさらいだけでも面倒な作業は減らせる

今週は何だか疲れていたので、深く追求もしなかったけど、きっと仕事で必要になった作業だったのだろうと推測。基本的すぎて会社でどうやってするか同僚が教えてくれないのか、それとも彼女の同僚たちはまさか手入力なのだろうか。

自分も事務員なので会社で時々こういマクロを作るほどでもない作業が発生することがあることはわかる。問題は、姉のように、「かくなる上は手作業で」と思うような人もいるかもしれないということだ1。オフィスアプリケーションの基本を知っているだけで、面倒な作業が回避できる場面は結構ある。

私が、オフィスアプリを学び直したのは、残業が続く私に他のチームの先輩が、「オレらが『あーこの作業面倒くせー』って思うようなことって、極力手作業しなくて済む方法があんだよ。エクセルって意外と何でもできるからね」とアドバイスもらってから。最初はGoogle先生に聞くという方法をとって、それだけでも十分すぎるくらい助かったけど、もっとラクになったのは基本から学び直してから。標準機能でも自分で思いもつかなかったような機能があって、「これ利用したら、○○の作業が短時間で済むじゃん」というものが多数あった。

Google先生に聞くには「アレできそうだな」などと自分の想像力に頼る必要があるので、事務作業をする機会が多い人には基本をもう一度おさらいしてみることをオススメしたい。

「Wordくらい余裕」という人が、まさかまさかのスペースでタブに合わせる方式をとっていたりする2。自分で使えていると思い込んでいるだけで、意外に面倒な方法で作業をしていたりする可能性もある。自分が使い始めた頃からずいぶん機能も追加されていることを知って驚く人は、きっと相当数いると思う。

と、結びつつ、こういうことをお姉さんには言えないのだ私の情けないところ。

今回使った方法

最後に今回使った方法の解説。(Macではふりがなの部分がコピーされなかった。)

  1. 「○○市 地名一覧」などとGoogleで検索。(例では「品川区 住所一覧」で、MapFanのページに辿り着いた。)
  2.  

  3. 【Ctrl】+【A】で全選択後、【Ctrl】+【C】でコピー。
    2018-03-31_SelectAll
  4.  

  5. エクセルを開き、形式を選択して貼り付け。
    2018-03-31_PasteSpecial
  6.  

  7. 上のいらない情報を削除後、A列を【行】という単語でフィルタ。
    2018-03-31_Filter
  8.  

  9. 【あ行】から【わ行】までを選択後、GoToSpecialで可視セルを選択(もしくはショートカット【Alt】+【;】)。
  10.  

  11. 行を削除。
    DeleteRow
  12.  

  13. 最後の地名の後に残っている不要データを削除。(了)

もし、ふりがなが同じセルに表示されてしまった場合には、以下の方法でセルを分割。

  1. A列を全選択。
  2.  

  3. 【データ】タブの【区切り位置】をクリック。
  4.  

  5. 【カンマやタブなどの区切り文字によってフィールドごとに区切られたデータ】を選択して次へ。 
    2018-03-31_Delimited
  6.  

  7. 【スペース】を選択して完了をクリック。(了)
    2018-03-31_Space

実際に送ったのは、A列の左に1列追加して、あ行などと何行かわかるようにしたものだったのだけど、地名をエクセルに入力するだけのタスクなら、上記で十分だろう。


  1. うちの場合には、まず妹を使ってみる、というのオプションを行使するわけだけど。 ↩︎
  2. そして、そういう人に限って後から「どうしてもこのラインが合わないんだよー」とかって泣きついてくる残念さ。 ↩︎
Advertisements

【Excel】難しい数式や機能を覚えるよりも先に学ぶべきこと

金曜日に同僚から「過去のレポートに誤りがあり、どう修正したらいいか相談させてほしい」とのメールが送られてきた。すぐに、過去のエクセルレポートの問題の箇所を再確認。残念ながら、同僚の勘違いではなかった。すでに3ヶ月。先月もその前の月も、そのミスを引きずった報告をしていたことになる。

とりあえず、誤りの発生源は分かったが、他のデータへの影響範囲を調べた上で、修正案をいくつか考える必要がある。そこで、同僚にヒアリングをし、エクセルの数式を一つずつ見直し、可能な修正方法を練った。

今回の件で、共同作業するにはお粗末なエクセルの作法の知識しか持っていない、と改めて痛感。これまで自己流で何とかエクセル業務を遂行してきた人は、自己流を貫こうとするから、仕事はチームワークということを今一度心に刻み込んでほしい。

エクセルシートについての説明はあったのに

今回の問題は引継ぎの過程で発生した問題だった。

一般的にあるエクセル引継ぎ問題は、シートの構成やインプットセルとアウトプットセルの混在などにより引き起こされるものが多い。私も、転職して、あまりのとっちらかったエクセルファイルの数に数ヶ月頭を悩まされた1

では、今回の場合もやはりファイルに関する説明不足から引き起こされた問題だったのだろうか。答えはノー

当該エクセルファイルは本社から配布されたもので、思いつきで作られたようなものとは対極にあるようなファイル。間違いの発生を抑えるべくシートの保護なども設定されている自由度の低い作りとなっている。そして、使い方のマニュアルが別途作成され、常に最新版がイントラ上にアップロードされている。

そして、そのマニュアルには「○○には必ず☓☓と表示されなければならない」と明記されている。

今回のミスは、同僚がこの注意書きを読み落としてしまったのが原因の一つ。この点、同僚に責任はある。しかし、この○○にあたる箇所は、どの行に表れるか定まっておらず、同僚をかばうわけではないが、見落としやすいチェック方法なのだ。

ちょっとした改善案:判定セルは固定

前の会社は、こういうエクセルファイルの作り方が(今の会社と比較して)上手かったと思う。確認すべきポイントがどこあろうとも、判定セルを一番上に再掲し、【提出可】と表示されない限り提出ができなかった2

判定セルを右端に置くと、データ量によってスクロールしないと気付かないなどといったことがあるので、A1セル左端に設置するのがいいと個人的には思う。

更に言えば、担当者は、スクロールせずに全体を見ることがきる場合を除き、環境保護・経費削減などというスローガンに怯むことなく、堂々と印刷してデータを確認、シュレッダー送りにすることをやってのけるべきだ。

私の周りはみんな《非表示》が好き過ぎる

閑話休題。

今回、同僚が注意書きを見落とししてしまったということ以上に問題だったのは、前任者がデータ行を非表示にしていたことだ。

確かに、判定セルの見落としはあったが、全てのデータは証憑と突合されていた。上手く説明できないのだけれど、いくら証憑と突合しても今回の問題は起こりうる。だからこそ、判定セルがついているというわけだ3

今回の非表示は前任者が残したものだったけれど、同様ミスを防ぐため、同僚には、今後エクセルでの作業時には「非表示ではなく、グループ化」を使うように伝えた。

今の会社に入ってから特に思うのだけれど、地域本社を含め、私がよく仕事をする事務所の人達はエクセルの《非表示》機能をよく使う。これは、行列の非表示に限ったことではなく、シートの非表示も含む4

同じ会社でも文化が違う

本社とのやり取りではこんなことを思うことはない。なぜだろう。

勝手な想像だけど、彼らはもっと共同作業に慣れているのかもしれない。彼らから送られてくるエクセルファイルは、非常にシンプルなテーブル形式のものだったり、複雑な場合には保護機能で簡単に変更できないようにされている(もちろんマニュアル付き)。

一方で、地域本社以下では、というと残念ながらそんな意識は浸透していない。エクセルファイルを引き継いだら「数式を追って全体像を理解するのが当たり前」という空気が充満している。

この文化の違いは、ナレッジマネジメントに対する意識の違いから生じているのではないかと思う。

今の会社に転職して長くはないのと、やり取りしている部署が限定されているので、全体に当てはまるのか微妙だけど、意識の差が生まれた背景には、関わる人の多さと、流動性があるのではないかと推測する。

【1】日頃からいろんなバックグラウンドを持つ人たちを相手にしている

本社は、同じフロアで顔を合わせる人たちだけでなく、国内外の他の事務所とやり取りをしているから、常に、作業者が悩むポイントがないような作りを意識してファイルを作る文化が出来上がっているのではないかと思う。

【2】スタッフのローテーションのスピードが速い

地域本社以下、私が関わっている事務所のスタッフは、ほとんど異動がない。一方で、上司曰く、本社では、最近特に、ローテーションの感覚が短くなってきているのだという。若手にいろんな部署で経験を積ませて、少しでも効率よくスキルを付けさせようとしているらしい。

このため、短い期間での業務引継ぎが必要であり、マニュアルなどが整備されていたり、引継ぎやすい形のファイルが作られる素地が出来上がっているのだと思う。

「仕事で使うエクセルは全て共有物だ」という意識を持つべき

《効率化》というのは、私の会社でもよく聞く単語だ。本社では、前述のような環境の中でも効率的に業務を遂行するためにも、ナレッジマネジメントの重要性がより浸透しているたのだと思う。一方で、同じ会社なのに、残念ながら、私の周りでは《暗黙知》→《形式知》への変換の重要性は理解されていない(少なくてもエクセルについては)。

決して特別なことが要求されるわけじゃないのにどうしてだろう。以下の2つを実践するだけでも改善されるハズ。

新しくファイルを作ったら簡単な説明を付けておく

会社のPCで作るエクセルファイルは、例えそれが個人の OneDrive に保存されているものだろうと、説明のシートを一つ挟んで置けばいいのだ5。場合によっては面倒くさくても、別途マニュアルを作るべきなものもあるけど。

確かに、近視眼的な視座では、こういう作業は自分の仕事のスピードを落とすし、効率的には思えないかもしれない。でも、仕事はチームワーク。

以前、簡単な業務だしと私がやっていた業務を、突然上司からの指示で同僚に渡したことがある。シートに、データの出所やシートの構成について全部メモっていたので、引継ぎが超楽だった。

不要なヘンテコな機能は使わない

行列の非表示、シートの非表示、列の結合(エクセル方眼紙)などなど、考えなしに使わない。「絶対使うな」というわけではなく、大した理由もなく自分の好みで使うなというはなし。引き継いだ人間や作業者にとっては、結構な負担。複雑であればあるほどやる気が出るのはゲームくらいで、意味なく仕事には持ち込んじゃダメ、絶対。

私がお世話になった本3冊

エクセルの数式などの辞典も持っているが、エクセルで出来ることを紹介する本よりも好きなのが、エクセルのお作法を取り扱った本。3冊以外にもまだまだ同じような本を持っている。

「エクセルを使えるようになりたい」と真剣に考える人ほど、この系の本は選ばないかもしれない。でも、SUM関数のような超基本をおさえたら、一旦、数式などを覚えるのを休んで、お作法の本を一冊読んでみるのが良いと思う。それから、機能や関数を扱った教本とお作法の本を行ったり来たりすればいい。そうじゃないと、他人を悩ますエクセルが量産され続けてしまう。。。

さて、お作法関連の本で私が一番最初に買った本は、慎泰俊さんの『外資系金融のEXCEL作成術―表の見せ方&財務モデルの組み方』。

留学時代、ファイナンスのイロハもわからなかったときに、手にした『15歳からのファイナンス理論入門』がわかりやすく、当時よくブログも読んでいた6

ちょっと逸れるけど、ご参考まで:
15歳からのファイナンス理論入門

この本を買って、それまで作ってきた表がいかに自分勝手な汚い表だったかを認識。この本で紹介されているスタイルを覚えるまで、この本を何度も何度も見ながら表を作った。財務モデルを作る業務はないため、後半は参考となる部分が前半ほどではなかったけど、私にとっては自分のエクセル資料に対する認識を変えてくれた重要な一冊。

次に買ったのは、熊野整さんの『外資系投資銀行のエクセル仕事術』。

この本は、『外資系金融のEXCEL作成術―表の見せ方&財務モデルの組み方』よりもエクセルの作法についての説明が詳しい。表の作り方についてのルールがやや違ったりもする。この本で、非表示機能よりグループ化機能を使うことが勧められている。ショートカットなど普通のエクセル機能については、全て知っているものばかりだったが、反対に「基本が身についている」との安心感をもらうことができた。

そして、『迷惑をかけないEXCEL』。

自分への戒めのために、時々、読み返している7。「あー、こういうことが他人には迷惑になるのか」とか「わかる!これやられて相当苦労した」ということがたくさん載っている。全てをクリアすることは難しいのだけど、ファイルを時々見直して、直せると思えるところが出てきたら少しでも改善している。

最初の2冊は、外資系とある。外資系の場合、やはり従業員のバックグラウンドに多様性があるため、必然的に、わかりやすいエクセル資料を作るルールが確立されたのではないかと思う。

Amazonのレビューでは、エクセルのお作法の本は賛否両論8。お金の無駄という人もいる。そういう人たちは、エクセルのお作法がきちんと確立されている会社で働いている数少ない幸運な人たちなんだろう。心底羨ましい。


  1. 外部ファイルからのリンクがあるが、関連するファイルが一つのフォルダにまとめられているわけではなく数日間に渡る大捜索作戦を結構する羽目になったり、命名規則がハチャメチャ過ぎて、必要なファイルを見落としそうになったり。それらが全て人事評価とそれに基づくボーナス算定などに関連するファイルだったため、相当神経すり減らすことになった、という非常にビターな思い出。 ↩︎
  2. 無視してアップロードしても、エラーとなり、受け付けてもらえなかった、・・・と思う、確か。 ↩︎
  3. 厳しいことを言えば、レポートそ印刷後、電卓を叩いてダブルチェックしたら一発でわかる。 ↩︎
  4. 以前、シート数・データ量の割に重いファイルだな、と思って、非表示シートを調べたら15枚以上も非表示のシートを見つけたこともある。 ↩︎
  5. 非常にシンプルでアフォーダブルな資料の場合は除外。 ↩︎
  6. もちろん、実際のファイナンスの授業では、いろんな論文やケーススタディに取り組まなくちゃいけなかったけど、知識ゼロから入るのとは雲泥の差だった。非常に説明がわかりやすい。 ↩︎
  7. 人って忘れやすい生き物なので、読んでからしばらくすると自分勝手なエクセルファイルを作り出したりする。 ↩︎
  8. パワポの本だと使い方よりもお作法の本の方が人気な感じがする。 ↩︎

【VBA】カット・アンド・ペースト、そして一括削除

隣から同僚が「問題発生しちゃった。」と深刻そうに言ってきたから何事かと聞いてみたら、マクロである程度解決出来たというお話。

問題となったのは、前任者の引継書に手順が記載されていなかったタスク。前任者の足跡を見る限り、どこからかデータを(恐らく)CSV形式でダウンロードし、それをもとに更新作業をしていた。後任のスタッフは、データをどのように更新するかについては何となくわかるようだったが、今回の問題はその前段階のデータ取得で発生していた。

まず、彼女は、会社で契約しているデータベースにアクセスしてデータをダウンロードしたのだという。でも、結果を見ると、数十件と明らかに少ない。そこで、データベースの会社へ連絡。すると、現行の契約内容では主だったもののデータしかダウンロードできない、ウェブページに載っている情報を見て対応してほしい、と回答されたのだという。ウェブサイトを見ると、めちゃくちゃスクロールする必要があるものの、データは全部載っているとのことだった。いつもなら力技(地道に一件一件目視・・・)で頑張る彼女だけど、報告期限まで時間が迫っていて、私に相談してきた。

【リクエスト】一列に並んだデータを別の行に分けて表示させたい

とりあえず、該当ページで全選択(【Ctrl】+【A】)して、空のエクセルブックに貼ってみた。(形式を選択して貼付→Unicode text)

結果はこんな感じ。

Item A -1
xxx
(blank row)
Item A -2
xxx

理想は、こんな感じ。

Item A -1 xxx
Item A -2 xxx

【解法】VBA|カット・アンド・ペーストを使って2行に分ける

10行以内の短いマクロで解決する。

おまけ【解法】VBA|3行以上に分ける

今回は、めちゃくちゃ単純な例だったけど、これがもし、以下のような感じだったらどうするか。(括弧内の数値は行番号とする)

Item A -1
Description 1
Description 2
(blank row)
Item A -2
Description 1
Description 2

あまり良くない解法かもしれないけど、私の回答は以下。

空白行の削除を最初に入れてループの回数を減らすのが良いのかな、と。

同様に、2回目の空白行の削除をループの中に入れるのも非効率な気がするけど、もしそうするのであれば、ループは下の行から順に回すこと。

おさらい

こんなのはHelpとかにまとまっているんだけど、一応。。。

今回のおさらい

カット・アンド・ペースト

[最初のセル].Cut [移動先のセル]

空白行の一括削除

[空白行が含まれている列].Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

下の行から順にループ(1行ずつ):

For i = ループする回数 to 1(もしくはゼロ)Step -1

2行おきの場合は-2

日常業務には【Go To Special】を使った処理

私の場合、空白行を削除したいと思うデータに遭遇する機会は結構ある。今回のようにデータの移動など不要の場合には、エクセルの標準機能で対応可能。所要時間は5〜10秒。マクロを作るより断然速い!(というか、上の方法は、この標準機能をマクロにさせている。)

GoToSpecial

空白行を一括削除する方法

  1. 空白を削除したい範囲を選択
  2. 【F5】(もしくは【Ctrl】+【G】)
  3. 【Alt】+【S】
  4. 【Alt】+【K】(了)

上記手順の2以降をマウスでの操作でするのはオススメしない。めちゃくちゃ奥に埋まっているから。

【Go To Special】は空白の選択以外にも便利なので、機能を調べておいて損はないと思う。私は、空白の選択のほか、可視セルのみ選択というのもよく使う。行列が非表示になっている表のコピペの際に便利(【F5】→【Alt】+【S】→【Alt】+【Y】→【Ctrl】+【C】(コピー)→【Ctrl】+【V】(ペースト))。

マクロを作ってアドイン登録

毎月のルーティン作業で一番嫌いなのが、ある拠点に提出するエクセルファイルの更新作業。複数あるのだけれど、いずれも不必要にセル(列)の結合がされている。エクセル方眼までいかないまでも、非常に迷惑。エクセル方眼はまだ申請用紙のフォーマットに合わせるため、とかなんとか言い訳を受け入れられるけれど(それでもできるだけやめてほしい)、私が毎月格闘するエクセルレポートは明らかに作成者が無計画にフォームを作った結果だろうな、と思われるもの。慌てず、あと5分くらい設計図を考えれば、こんなフォームで私の時間が奪われずに済んだだろうに。

私は後々の面倒臭さを回避したい人間なので、エクセルでフォーマットを作れ、と言われたら、時間がかかるタイプ。私が想像するに、私の大嫌いなフォーマットの作成者は、一つ一つの作業が速くて、一つ一つコピペして、行の高さを変更して、という作業が苦じゃないタイプなんだろうと思う。(もしくは、実際の作業を想像できないか。)

セルが結合されたら発生しちゃう面倒くさい作業

行の高さの自動調整が出来ない。

一時期、この行の高さを調整するタスクが大量に舞い込んできたので、調整用マクロを作ったのだけど、そうしたら、更に「調整しておいて」と言われることが増えたので、エクセルのアドインとして登録。この他にも、各シートの表示倍率を統一してA1セル選択した状態にするマクロ(【追記】下にサンプル付けました)とか、別ブックからのリンクがある場合にリンクを削除するマクロ(【追記】下にサンプル付けました)、シートインデックスを一番左に作るマクロをアドインとして登録している。

「わざわざアドインとして登録するまでもない」と思えるような作業でも頻繁に発生するのであれば登録しておいた方がいい。コーヒー休憩に行く時間くらいは作ってくれるはずだ。

マイクロソフトが高さの自動調整の改良をしてくれることを切に願います。(変な使い方を考える人は、私を含め、減ることはないと思う。)

おまけ

【各シートの表示倍率を統一してA1セル選択した状態にするマクロ】
[code language=”vb”]
Option Explicit
Sub ZoomLevelSetting()

Dim i As Long
Dim ZoomLevel As Long ‘表示倍率

ZoomLevel = InputBox(Prompt:=”シートの表示倍率を” & vbCrLf & “半角英数字で指定してください。”, Default:=100)
If StrPtr(ZoomLevel) = 0 Then Exit Sub

For i = 1 To ActiveWorkbook.Worksheets.Count
ActiveWorkbook.Worksheets(i).Activate
ActiveSheet.Range(“A1”).Select
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 1
ActiveWindow.View = xlNormalView
ActiveWindow.Zoom = ZoomLevel
Next i

ActiveWorkbook.Worksheets(1).Activate

End Sub
[/code]

【別ブックからのリンクがある場合にリンクを削除するマクロ】
[code language=”vb”]
Option Explicit
Sub CheckExternalLink()

Dim msg As VbMsgBoxResult ‘選択したメッセージの値
Dim aryLink As Variant ‘外部リンクの格納
Dim i As Long

aryLink = ActiveWorkbook.LinkSources(xlExcelLinks)

‘他のブックへのリンクがあるかを確認し、リンクを削除するか選択する。
If IsArray(aryLink) Then
msg = MsgBox(“他のブックへのリンクがあります! ” & vbCrLf & “リンクを解除しますか?”, vbYesNo + vbQuestion)
If msg = vbYes Then
‘リンクを削除する
For i = 1 To UBound(aryLink)
ActiveWorkbook.BreakLink _
Name:=aryLink(i), _
Type:=xlLinkTypeExcelLinks
Next i
Else
MsgBox “リンクを解除していません。”
End If
Else
MsgBox “他のブックへのリンクはありませんでした。”
Exit Sub
End If

MsgBox “DONE”

End Sub
[/code]
アドインに登録する場合には、ThisWorkbookではなくActiveを使います。

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

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

作業の概要

  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)を使えば、メールを受信したら指定のフォルダに格納してくれる。あとは、集計用エクセルを開いて更新をすればおしまい。これならカップヌードルを作る時間と同じくらいまでにはなるんじゃないだろうか。。。

おまけ


Power Query の日本語情報はあまりヒットしないのだけど、英語で見つけるサイトで必ず紹介されている本、M Is for (Data) Monkey: A Guide to the M Language in Excel Power Query。本家のサイトもよく訪問するけど、何だかんだで本も購入。Kindle版だからか、書き方のせいか、私にはやや見づらい印象があるけど、結局のところ知りたい情報が網羅されているのはこの本くらいだから我慢して使っている。