今日を乗り切るExcel研究所

Excel に働かされていませんか

Excel シートのミミをそろえたい

Excel シートの位置と表示倍率を整える手順と、それを自動化する Excel マクロ、WSH スクリプトを考えます。

f:id:shego:20170512010559p:plain

Excel に耳などありませんが

Excel ファイルを開いたときの状態は、最後に保存したときの状態を反映しています。

Excel はデータだけでなく、シートの状態も保存しているのです。

ファイルを開いたときに使いっぱなしのシートが表示されると、躾に厳しい日本人としては気分がよくありません。

特に、共有資料や納品文書など、他の人も見るファイルは常に整頓しておきたいものです。

そのためには、シートを一つ一つ直してファイルを保存しなおすしかないのですが、人生にそんな時間はありません。

手作業でシートを整頓するには

全シートの位置と表示倍率を初期化する手順を検討します。

全てのシートを選択状態にすれば、先頭(A1)セルの選択と表示倍率(ズーム)の設定は連動して一括操作できます。

しかし、シート内のスクロールは連動されませんので、先頭セルへのスクロールは1シートずつ操作するしかないようです。

先頭セルを選択するショートカットは Ctrl + Home です。

表示倍率を変更するショートカットは無いのですが、「100%」にするならAltWJ でアクセスできます。

シート操作関連はショートカットも少ないので、マウスなしのキー操作だけで行うのは無理ですが、Ctrlキーを組み合わせてある程度効率化できます。

【手順例】

  1. 全てのシートを選択します
    • シートタブを右クリックし、「全てのシートを選択」コマンドを実行します
  2. 「ズーム」を100% に設定します
    • キー操作: AltWJ
  3. 最後のシートを選択します
    • Ctrlを押しながらシート見出しの右スクロールボタンをクリックで最後のシートまでスクロール
    • 最後のシートのシートタブをクリックして選択(全シート選択も解除される)
  4. 各シートで、先頭セルを選択状態にします
    • Ctrlを押したまま Homeキー とPage Upキーを2本の指で交互に連打します
    • 最後には先頭シートが選択状態になります
  5. そのまま Excel ファイルを保存します

ノートパソコンのキーボードだと、Home と PageUp のトリルは辛いかもしれません。



シートの状態を初期化するマクロ

全シートの位置と表示倍率を初期化するマクロを作成しました。

下記マクロ VBA を実行すると、アクティブなブックの全シートを初期化(先頭セル(A1)選択、先頭位置表示、表示倍率100%)し、先頭のシートを開いた状態にします。

Sub シートの整頓()
    Application.ScreenUpdating = False
    
    Dim sh As Worksheet
    For Each sh In ActiveWorkbook.Worksheets
        Application.Goto sh.Range("A1"), Scroll:=True
        ActiveWindow.Zoom = 100
    Next
    ActiveWorkbook.Worksheets(1).Select
    
    Application.ScreenUpdating = True
End Sub

【使い方】

  1. 上記 VBA プログラムを標準モジュールにコピー&ペーストします
  2. マクロを実行します
  3. 全てのシートの位置と表示倍率が初期化され、先頭シートが選択状態になります

シートの状態を初期化するWSH スクリプト

整頓したいファイルが沢山あるとき、いちいちExcelファイルを開いてマクロを実行するのも手間です。

上級者向けに、複数のExcelファイルを一括では整頓する WSH スクリプトも用意しました。

下記スクリプトファイルに Excel ファイルをドラッグ&ドロップするだけで、全シートの状態が自動的に初期化(整頓)されます。

Option Explicit

dim fso
dim excel
set fso = CreateObject("Scripting.FileSystemObject")
set excel = CreateObject("Excel.Application")
excel.Visible = False

function tidyUpExcelSheets(xlsx)
    dim book
    set book = Nothing
    
    on error resume next
    set book = excel.Workbooks.Open(xlsx)
    on error goto 0
    
    if book is Nothing then
        tidyUpExcelSheets = "開けませんでした"
        exit function
    elseif book.ReadOnly or book.MultiUserEditing then
        book.Close
        tidyUpExcelSheets = "使用中か読み取り専用です"
        exit function
    end if
    
    dim sheet
    for each sheet in book.Worksheets
        excel.Goto sheet.Range("A1"), True
        excel.ActiveWindow.Zoom = 100
    next
    book.Worksheets(1).Activate

    book.Save
    book.Close
    tidyUpExcelSheets = "OK"
end function

sub main(args)
    dim file
    if args.Count = 0 then
        WScript.Echo("Excelファイルを指定されていません")
        exit sub
    end if
    for each file in args
        file = fso.GetAbsolutePathName(file)
        if not fso.FileExists(file) then 
            WScript.Echo(file & "が見つかりません")
            exit sub
        end if
        if fso.GetExtensionName(file) <> "xlsx" then
            WScript.Echo(file & "はExcelファイルではありません")
            exit sub
        end if
        
        dim msg
        msg = tidyUpExcelSheets(file)
        if msg <> "OK" then
            WScript.Echo(file & " " & msg & " スキップします")
        end if
    next
    WScript.Echo("整頓完了")
end sub

main(WScript.Arguments)

excel.Quit
WScript.Quit

【使い方】

  1. 上記 VBScript プログラムをテキストエディタにコピー&ペーストしスクリプトファイルとして保存します
    • 文字コードに「シフトJIS」を指定します。
    • ファイル名は任意ですが、拡張子を「.vbs」とします
  2. Excel ファイル(xlsx)をスクリプトファイルのアイコンにドラッグ&ドロップします
    • 複数のファイルをドラッグ&ドロップできます
  3. 各 Excel ファイル内の全てのシートが初期化された状態で保存されています
    • 初期化後に保存しています

【注意】 本スクリプトはExcelファイルを上書きします。念のため、ファイルのバックアップをしてから進めてください。

【注意】 管理者によるセキュリティ上の制限として、WSH の実行が無効化されている場合がありります

【注意】 本 WSH スクリプトは、ご自身個人の環境で改変なしで使用して下さい。本スクリプトに限らず、WSHスクリプトファイルを他の人へ譲渡、ファイル共有、メール添付送信など広める行為はしないでください

まとめ

Excel ファイルを整頓するには、各シーtの状態を整頓して上書き保存します。本記事では、その作業を自動化する マクロ と WHS スクリプトを紹介しました。

本記事の内容は、Windows 10 の Excel 2013 で動作確認しました。