【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】(ペースト))。

Advertisements

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

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

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

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

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

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

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

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

おまけ

【各シートの表示倍率を統一してA1セル選択した状態にするマクロ】

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

【別ブックからのリンクがある場合にリンクを削除するマクロ】

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

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

少しでもラクをする仕組みを作っておく

繰り返し発生する業務は出来る範囲で自動化するようにしている。

自動化するためには、まずタスクの処理方法を整理。プログラムを書けるかどうかは気にしない。自分の作業順に書き出してから、冷静に見てみると意外にヘンテコな手順を踏んでいることに気づいたり、いらない手順が見えてくる。
次に、自動化出来そうな部分と、手作業で手を打つ部分に区別する。毎月発生するエクセルのタスクは同じ様な作業が多いので、以前に作ったコードを再利用
で、最後に前までは出来なかったことで、今なら出来そうなことを追加しておしまい。この時、再利用のコードの見直しもしておく。

ボタンをポチッと押したら全自動で、というわけではないので、手順メモを作成しておくことが重要。(最初に手順を書き出したメモの再利用。)

同じことを繰り返していると作業自体早く出来る様になってくるけど、急にタスク量が増えて余裕がなくなってくるとミスが発生しやすくなるし、こんなちょっとした仕組みが役に立つ。
ここ数週間、突然のタスクにテンパりつつも何とかやり遂げられたのには、これまでコツコツ作ってきた仕組みがあったからかもしれない。

エラーメッセージで数時間

世の中にはこんなしょうもないことに時間をかけなくちゃいけないのか、とガックリくるようなタスクがいっぱいあるのだと、今の会社に移って嫌というほど思い知った。

例えばどのようなタスクか。

バカバカしいことこの上ないが、エクセルの行の高さを調整する、というもの。

フォーマット作成者、レポート作成者が、もう少し想像力を働かせたらこんな業務は発生しないはずだ!

行を選択してダブルクリックすれば自動調整出来る、と思うかもしれないが、それはセルの結合がない場合のみ有効な方法。敢えてセルを結合させている理由が全く分からないフォーマットというのが私をイラつかせるが、勿論、指摘をするなんて大それたことはしない。せめてレポート作成者が気を利かせて、提出前に体裁を整えてくれるなら。。。

更に面倒なのは、取りまとめ担当が提出順にランダムに報告シートをまとめるとこ。

上司からのリクエストは、「全拠点のレポート最終版ファイルが届いたら、プリントアウトしてちょ」というものだけど、額面どおりに受け取っちゃー、次の瞬間、溜め息を聞くことになる。細かいのだ。

ということで、報告書ファイルが届いたらすべきことは、

1) シートを毎回同じ順に並べかえる

2) 行の高さを調整する

3) ヘッダー・フッターを設定する

4) 印刷範囲を設定する

の4つ。

そもそも私の作業スピードの遅さもあるが、手作業だと1時間以上かかって、軽くめまいをおぼえた。行の高さ調整ってそれほど面倒。

ということで、マクロを作成。

最後に目視で確認はする必要アリのやや中途半端なものだけど、それでも10分程度で仕上がるからこれ以上は作り込まなくていいかなと。

そこそこ満足なマクロだけど、当初想定していなかった問題も出てきたりすることがある。

例えば、報告者が行の高さの上限を無視したとんでもなく大量の文字を打ち込んでいたり。

→そういう場合には、フォントサイズを小さくするようメッセージを表示。

しかし、今日のエラーは原因が全く分からず数時間。もう手作業の方が早い。

原因

シート名の最後にスペースが入っているものがいくつかあったため。

間に取りまとめ担当が入るとやや面倒なんじゃないかと思い始めている。「お忙しそうですから手伝いましょうか」と言えたらどれほどいいだろう。。。