【Excel】他人から渡された使いにくいファイルたちとの付き合い方:その2

前回、ゴミのようなエクセルを渡される可能性が高い状況について書いた。

  • 前任者が全体像を把握していない
  • 途中で、エクセルに疎い人がかんでいる
  • 属人化を促すコーポレートカルチャー

極力、こういう状況の中に放り込まれないよう、日々注意しておくのがいいが、どんなにやりたくない、と思っても、ハズレを引くこともある。

不幸にもハズレを引いたときの対処法を、経験を元にまとめたい。前回の逆をやっていけばいいだけだ。今回は、全体像の把握方法について。

業務の全体像の把握に力を注ぐ

「全体像を把握するって、あるブックのシート構成から見ていくのがいいのか、それとも関連するブックから見ていくのがいいのか」といったことで悩める人は幸せだ。私は、一体いくつブックがあるのかということすら分からない状況からスタートするしかなかった。

多くのブックが一つのブックにまとまる、という場合には、ブックを特定するのは簡単だが、複数の情報が様々なブックを経由して集約される場合、やや面倒くさい。

ファイルの2つのパターン

file_patterns

後者の場合、最初の問いへの答えはどちらかを選ぶものとはならない。答えは「どっちもちょっとずつする」だ。 そして、ゴールは詳細に把握することではない。矛盾しているようだが、「ヌケモレない程度にざっくりと」全体像を把握するのがゴール。

1. 最終成果物に近いファイルを見つける

こんなレベルの作業から始めることは、多くの場合ないと思うが、まず、最終的な成果物(PDFや、ハードコピーで残っている場合がほとんど)を確認し、最終版に近いファイルにあたりを付ける。

見つけたファイル名に近いものを、同一フォルダにまとめる。この段階でのフォルダへの格納は、ラベリングに近い作業なので、あまり気負う必要はない。

2. 1.で作ったフォルダに格納したファイルをチェックする

絞り込めたら、一つづつファイルを開いて、前回の成果物と照らし合わせ、同じデータファイルを確定する。

この際に、《Final》というファイル名を信用しないこと。また、最終更新日も信用しない方がいい。最終更新日は新しいのに、結局一つ前のバージョンが採用される場合もある。

ファイル名は似ていたけど、内容は全然違ったというファイルは、元のフォルダに戻す。

そして、前時代的なフォルダ構成と笑われようが関係ない。ここで私は、《Old》というフォルダを作成する。そして、最終版と合致しなかったデータをブチ込む。もちろん本音は、消去したくてたまらない。が、全体像が見えないうちは我慢、我慢。

3. 最終版ファイルを確定する(スタート地点)

ひたすら目視という気の遠くなる作業の先にやっとスタート地点が見える。ここでやっと本来の作業を開始できる。

2.までの作業は、まるで、ネックレスの写真を渡されて、ゴミ屋敷に入って実物を探してこい、と言われたようなもの。テレビのお片付け企画のような、ちょっと整理が苦手な主婦○○さんのおウチでのネックレス探しはもっとずっと簡単。すぐにスタート地点に立てる。

私の経験したような状況はレアだと思う。

4. 最終ファイルのシートの構成を把握する

また、レアケースの話になるが、目に見えるシートだけが最終ファイルを構成しているシートではない

「シート数の割にファイルサイズ大きいな〜。数式やリンクが多いのかな〜」と思っていたら、何十枚もシートが非表示化されていたという恐ろしいファイルに遭遇した経験もある。

「非表示化されている」≒「いらない情報」

ユーザーとして情報を入力して提出するとかであれば非表示化されていることを考える必要はないだろうが、当該教務の全体像を把握するためには、何がどういう意図で非表示設定されているのかについて、仮説を立てておく。

【VBA】非表示シートを一括再表示

シートを再表示させるには、シートタブの上で右クリック→再表示→一覧から再表示させたいシートを選択、という手順。

非表示になっているのが1〜2シート程度であればこの方法でいいが、何十枚もある場合には面倒臭すぎる。そこで、VBAで解決する。

シートを表示させる

一番左のシートを表示する 
Worksheets(1).Visible = True 

ブックのシート全部を表示させたいので、以下のようになる。

Sub UnhideAllSheets() 
Dim ws = Worksheet 
For Each ws in ThisWorkbook.Worksheets 
ws.Visible = True Next ws 
End Sub 

【VBA】再表示させる前にシート一覧を作成してシート枚数を把握する

一括再表示させる前に、まずどんなシートがあるのかを一覧表示させたい、という場合にもVBAで解決可能。

以下、一番左にシート一覧を挿入するマクロ。

Sub CreatingTOCSheet()
Dim bkTarget As Workbook 'subject workbook
Dim shTOC As Worksheet 'TOC worksheet
Dim sh As Worksheet 'worksheets to be listed
Dim flag As Boolean 'boolean to check if a new worksheet is necessary
Dim finalRow As Integer
Dim i As Integer
Dim msgAnswer As VBA.VbMsgBoxResult 'store the answer of the message box
Dim shName As String
Dim tocRange As Integer
Set bkTarget = ActiveWorkbook
Application.ScreenUpdating = False
With bkTarget
'Set the worksheet named "TOC" as "TOC" sheet.
'If there isn't, add a new sheet.
For Each sh In .Worksheets
shName = sh.Name
If shName = "TOC" Then flag = True
Next sh
If flag = True Then
'If the "TOC" sheet already exists, ask if it is okay to overwrite the list.
msgAnswer = VBA.MsgBox("You already have TOC sheet." & vbNewLine & _
"The TOC could be overwritten. " & vbNewLine & _
"Would you like to continue?", vbOKCancel + vbDefaultButton1)
If msgAnswer = vbCancel Then Exit Sub
Else
.Worksheets.Add(before:=Worksheets(1)).Name = "TOC"
End If
Set shTOC = .Worksheets("TOC")
End With
'Clear cell value & format
With shTOC.Cells
.Clear
.VerticalAlignment = xlCenter
.Font.Color = 2500134
End With
'List up the visible sheets on TOC sheet
For i = 2 To bkTarget.Worksheets.Count
Set sh = bkTarget.Worksheets(i)
shName = sh.Name
If ActiveSheet.Name <> shName Then
If sh.Visible = xlSheetVisible Then
ActiveSheet.Hyperlinks.Add _
Anchor:=shTOC.Cells(i + 3, 3), _
Address:="", _
SubAddress:="'" & shName & "'!A1", _
TextToDisplay:=shName
End If 'sheet is visible
End If ' sheet is not activesheet
Next i
'—————
'Format TOC sheet
'—————
'Format sheet title
With shTOC.Cells(2, 2)
.Value = "Table of Contents"
.Font.Size = 16
.Font.Bold = True
End With
'Format table headers
With shTOC.Rows(4)
.Font.Size = 8
.Font.Bold = True
.VerticalAlignment = xlBottom
End With
shTOC.Columns("A:B").ColumnWidth = 3
shTOC.Columns("C:D").IndentLevel = 1
finalRow = shTOC.Cells(shTOC.Rows.Count, 3).End(xlUp).Row
If finalRow <= 5 Then
tocRange = 1
Else
tocRange = finalRow 4
End If
With shTOC.Range("C5").CurrentRegion.Resize(tocRange, 2)
'Table lines
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
End With
With .Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlHairline
End With
End With
'Add the headers
With shTOC
.Cells(4, 3).Value = "Sheet Name"
.Cells(4, 4).Value = "Remarks"
End With
With shTOC
'Autofit the column width
If .Columns("C").ColumnWidth > 25 Then
.Columns("C").AutoFit
Else
.Columns("C").ColumnWidth = 25
End If
.Columns("D").ColumnWidth = 40
.Columns(5).ColumnWidth = 3
'Set row height
.Rows.RowHeight = 18
.Rows(2).RowHeight = 25 'title row
End With
With shTOC.Range("B3:C3")
.Merge
.Font.Size = 8
.Font.Italic = True
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.Value = VBA.DateTime.Date
.NumberFormat = """(as of ""dd mmmm yyyy"")"""
End With
'Page setup
With shTOC.PageSetup
.PrintArea = Range(Cells(2, 2), Cells(finalRow, 5)).Address
.Orientation = xlPortrait
.Zoom = False
.FitToPagesTall = 1
.FitToPagesWide = 1
End With
For Each sh In ActiveWorkbook.Worksheets
sh.Activate
sh.Range("A1").Select
With ActiveWindow
.ScrollColumn = 1
.ScrollRow = 1
.View = xlNormalView
.DisplayGridlines = False
End With
Next sh
shTOC.Select
Application.ScreenUpdating = True
MsgBox ("DONE")
End Sub

(かなり昔に作ったシートの目次を作るマクロ。後半、当時の私の好みで表のスタイルを変更している。不要なので、消してしまって問題ない。)

恐らくPower Queryを使えば、コードを書く必要はないし、速いと思う。ただ、やったことがないので、やり方は次回やってみてから。

5. 各シートの関連性を確認する

最終成果物シートから順にシートの内容をやや細かく見ていく。

  • 当該シートのデータの内容は?(入力用・出力用?マスタテーブル?メモ?)
  • 同一ブック内のほかシートからのリンクはないか。
  • 外部リンクはないか。

データがベタ打ちされたものなのか、数式なのかは、GoToSpecialの機能を使ってもいいし、エクセルのオプションで設定してもよい。

数式表示切り替えキーボードショートカット

Ctrl + Shift + @ 

他にも、参照先・元のトレース機能を使ったりしてデータの出自を明らかにしていく。 外部データからのリンクを一覧化するには以下のマクロ。

view raw
ListLinks.bas
hosted with ❤ by GitHub

落書きのような感じで十分なので、各シートの関係を図にまとめておくといい。

6. 登録されている名前とその範囲を確認する

私が引き継いだファイルはここに腐るほどのジャンクデータが詰まっていた。名前の管理機能を知らず、無視してテーブルを削除したり、数式を変更した結果のようだった。

この状態から脱却するためには、まず登録されている名前を把握する。外部データの一覧化はマクロを使う必要がなく簡単。

参照の切れている名前に関しては、理解を妨げるものでしかないので、思い切って削除してしまう。

名前を付けた方が作業がしやすいのだけど、この機能を伝えていかないとただのジャンクとなる可能性が高い。事実、私の前任者は尽く無視していた。(前任者が悪いわけではなく、きちんとシートやテーブルの相関図を残しておかなかった作成者などにも問題があるとは思う。)

全体像がつかめた時点で、簡単に相関図を作成

これらをすべてのファイルで繰り返すことで、ファイルの関係性が見えてくるし、業務の全体像がクリアになる。ただし、殊の外、時間がかかるので、やはりベストなのは引き継ぐ際にしっかり説明をしてもらうこと。

そして、自分が理解したところで終わりにすると、次の人が同じように苦しい時間を過ごすことになるので、各シートの相関図を残しておいてあげること。ここで、あまり踏み込むと余計にわかりにくくなるので、最低限の図解と名前のリストに説明を付けたくらいにしておくのがいいと思う。

(余力があれば)次のステップ:大胆に改善してしまおう

ここまできて余裕ができたら、不必要に複製されているシートやブックをまとめる。

私は、これをするのに1年以上かかってしまった。作業が面倒くさくても機能している場合、改良の作業は放置してしまいがちだ。

私の場合は、幸運なことに元々のファイル作成に関わった人を探し当てることができたので、分担して改良することができた。相当、文句言いまくったので、相手には、嫌われていると思う。ただ、手伝ってくれた地域本社の人間もここまでぐちゃぐちゃになって引き継がれているとは思いもしなかったようだ。

二人で作業してみて面白いと思ったのは、私達の性格なんだろうけど、それぞれお気に入りの数式があって譲り合う精神が皆無。 私は彼のINDIRECTとか使いまくるのを、VLOOKUPの組み合わせで封じ、彼は私のSUMPRODUCTをVLOOKUPの組合わせで封じる、といった具合。

結局、この業務のキモはマスタテーブルからデータを取得できるかということだった。

結果

最終的には、ファイル数を4つにまで減らし、何十枚もあったシートも、半分くらいに減った。ブックを跨ぐリンクは全部切った。リンクを切った方が業務のフローがわかりやすくなった。「ファイル1」を完成させたら「ファイル2」にデータを貼付する、といった具合。

当初はきっと、「リンクさせることにより、データの更新更新漏れを防ぐ」という意図があったのだろうけど、途中でそれが、「リンクをたどって業務を把握して」という引継書を作っていないことの言い訳にされていたように思う。

リンクがあろうがなかろうが、「どういう理由でどことリンクしている」ということは明確にさせておかないと、何かの拍子にリンクが誤ったときに誰も気づけないということすら起こりかねない。

リンクをたどって云々」という引き継ぎのみで済ませようとする人が少しでも減ってくれることを望む。

This site uses Akismet to reduce spam. Learn how your comment data is processed.