今日を乗り切るExcel研究所

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

Excel シートを素早く切り替えたい (2/2)

今回はワークシート探しの効率化を探る記事の2回目です。「名前」を活用したシート間移動ができないか検討します。また、シート名を「名前」に登録するマクロを作成します。

www.shegolab.jp

「名前の定義」で「ブックマーク」

Excel にも Web ブラウザの「ブックマーク」のような機能があればだいぶ便利です。

Excel にそのような機能自体はありませんが、「名前」がその代りとして使えるかもしれません。

Excel でいう「名前」とは、セル範囲につけられるタグのようなものです。

本来「名前」は、数式で使う参照先を、"A1:C20" のようなセル参照の代わりに、"価格"のようなデータ内容を表す名称をつけることで、数式の意味を分かり易くするために用意された機能です。

「名前」はそれだけでなく、定義済みの名前を指定してその参照先へ移動することもできるのです。 「名前」を流用すれば、シートのブックマーク的な使い方ができるはずです。

「名前」を定義するには、数式バーの左にある「名前」ボックスを使うのが簡単です。

f:id:shego:20180630192913p:plain

たとえば今、目次用シートのような、よく使うワークシートを開いていて、別のシートからすぐに戻ってこれるようにしたいとします。

そのシートの A1 セルを選択してから「名前」ボックスに簡単な名前 - たとえば "m" - と入力します。 すると、目次シートの A1 セルを参照先とする "m" という「名前」が定義されます。

f:id:shego:20180630193949p:plain

以後、どこのシートを開いていても、「名前」ボックスに再び "m" とタイプするだけで、元のシート(のA1セル)に戻れるようになります。

【手順例】

  1. ブックマークしたいシートでA1セルを選択します
    • かならずしも A1 でなくても構いません。戻ってきたときにそのセル範囲か選択状態になります
  2. 「名前」ボックスをクリックして入力状態にします
  3. 「名前」ボックスに名前を入力して Enter を押します
    • Enter を押し忘れると登録されません
    • 「名前」には日本語も使えますが、使えない文字や規則もあります。 (参考
  4. A1 セルに「名前」が定義されている状態になります
    • A1 セルを選択すると、「名前」ボックスにはアドレス(A1)の代わりに定義名が表示されるようになります

定義した名前は「名前の管理」ダイアログで確認できます。

「名前」の削除も、「名前の管理」ダイアログで行えます。

  • 「数式」タブ>「定義された名前」セクション>「名前の管理」ボタン f:id:shego:20180630193803p:plain

別のシートから名前定義したシートに移動するには、基本的に「名前ボックス」にその定義名を入力し Enter を押すだけです。

しかし、万一タイプミスで間違った名前を入力すると、意図しない新しい名前が定義され、ゴミの名前として残ります。

それを避けるには、「名前」ボックスで ▼クリックをするか Alt+ で名前のドロップダウンリストが表示されるので、そこから選択したほうが安全でしょう。

f:id:shego:20180630222520p:plain

「名前」ボックスの残念なところは、入力にどうしてもマウスクリックが必要で、キーボードのみによる操作ができないところです。

そのかわり「ジャンプ」コマンドを使う方法もあります

「ジャンプ」でも「名前」を指定した移動ができ、これならキーボードだけでも操作できます。

  • 「ホーム」タブ>「編集」セクション>「検索と選択」メニュー>「ジャンプ」 f:id:shego:20180630223757p:plain

「ジャンプ」ダイアログには、「移動先」リストに定義済みの「名前」が一覧で表示されます。 定義名を、「移動先」リストで選択(or ダブルクリック)するか、「参照先」ボックスに直接入力することで、その参照先のシートに移動できます。

「移動先」リストの先頭4件までは履歴として確保されていて、直近で移動した定義名が表示されます。 つまり、「名前」で移動している限り、「履歴」「戻る」 の機能も実現できることになります。

「移動先」リストに表示される「名前」は現在のワークブック内の名前にかぎられますが、「履歴」の4件は他のブック間の移動履歴も含まれます。

【手順例】

  • 定義名を入力して移動
    • Ctrl+G ⇒ 定義名を入力 ⇒ Enter
  • 定義名を選択して移動
    • Ctrl+GTabPageDownで選択 → Enter
  • 定義名を検索して移動(前方一致検索、英数字のみ)
    • Ctrl+GTab ⇒ 定義名をタイプ → Enter
  • 戻る(直前の名前移動)
    • Ctrl+GTabEnter

「ジャンプ」の残念な点は、「シートの選択」同様、「移動先」リストも表示件数が変更できず、しかも10件分しかないことです。 履歴が4件表示されたら最初は6件しか見えません。 「移動先」リストでも前方一致検索が有効になってはいますが、やはり日本語が使えない点も同様です。

使い勝手はともかく、これでキーボードだけでも「名前」をブックマークのようにしてシート間の移動できそうなことがわかりました。

さて、ここにきて、賢明な読者はピコーンとひらめいてしまったのではないでしょうか。

『全てのシート名をそのまま名前定義にすれば、シート名を選択して移動できるようになって便利かもしれない』 と。

それでどれだけ便利になるか、ちょっと試してみる価値はありそうです。 しかしそのために大量シートでシート名を手作業で名前定義するのは面倒でやってられませんね。

こうゆう作業こそマクロの仕事です。

 



  

シート名で「名前」を定義するマクロ

各シートのシート名を使って「名前の定義」を一括登録するマクロを作成しました。

本マクロを実行すると、全てのワークシートの A1 セルに、そのシート名をもとにした定義名を設定します。 以降、「名前」ボックスや「ジャンプ」コマンドで、シート名を指定して移動できるようになります。

用意したマクロは3つあります。

  • シート名の名前定義
  • シート名の名前定義_インデックス付き
  • シート名の名前定義を削除

「シート名の名前定義」マクロは単純にシート名を名前定義します。

f:id:shego:20180701000353p:plain

シート名が日本語でも、記号など一部の文字を除き、ほぼそのまま使われます。

大量のシートがあると、日本語の文字は順序は分かりにくいので、リスト内で探すのに手間取るかもしれません。

しかし「名前」ボックスからなら、「シートの選択」や「ジャンプ」とは違って、日本語による前方一致検索が可能なので、シート名を一部タイプするだけですぐ見つけられます。

たとえば、"商品コード管理表"というシートにアクセスするには、
「名前」ボックスをクリック ⇒ "商品"ぐらいまで入力 ⇒ Alt+(下矢印)で選択 ⇒ Enter
の操作でかなり行けるはずです。

2つ目の 「シート名の名前定義_インデックス付き」マクロは、シート名だけでなく、その頭に連番のインデックスを付加した名前で定義します。

f:id:shego:20180701000420p:plain

名前リストでの表示が日本語文字順ではなくシート順になるので、順序や位置を見当に探せるようになります。

また、日本語検索が使えない「ジャンブ」ダイアログでも、インデックスをタイプして前方一致によるリスト検索できるという利点もあります。

たとえば、100枚目のシートにアクセスするには、キーボード操作だけで Ctrl+GTab ⇒ "a100" ⇒ Enter でい けます。(インデックスが a からになっているのは、数字から始まる名前を定義できないという「名前」の制約のため、打ちやすくでなるべく上にくる文字をつけたものです)

3つめの「シート名の名前定義を削除」マクロは、これらの定義名をすべて削除するものです。

削除するのは上記マクロによるシート名での定義名だけで、元からあった既存の定義名には影響しません。

Option Explicit

Sub シート名の名前定義()
    undefSheetRefs ActiveWorkbook
    defineSheetRefs ActiveWorkbook
End Sub

Sub シート名の名前定義_インデックス付き()
    undefSheetRefs ActiveWorkbook
    Dim c As Variant
    c = ActiveWorkbook.Worksheets.Count
    defineSheetRefs ActiveWorkbook, indexFormat:="a" & String(Len(c), "0") & "_"
End Sub

Sub シート名の名前定義を削除()
    undefSheetRefs ActiveWorkbook
End Sub

Private Sub defineSheetRefs(wbk As Workbook, Optional indexFormat As String = "")
    On Error GoTo abort
    Dim sheetName As String
    Dim sht As Worksheet
    For Each sht In wbk.Worksheets
        sheetName = makeSafeName(sht, indexFormat)
        sheetName = makeUniqName(wbk, sheetName)
        
        wbk.Names.Add NameLocal:=sheetName, RefersTo:="='" & sht.Name & "'!$A$1"
        wbk.Names(sheetName).Comment = "≫ " & sht.Name
    Next
    Exit Sub
abort:
    MsgBox "シート名「" & sheetName & "」は名前に使用できませんでした。", _
    Title:="シート名で名前定義"
    Err.Clear
End Sub

Private Sub undefSheetRefs(wbk As Workbook)
    Dim nm As Name
    For Each nm In ActiveWorkbook.Names
        If nm.Comment Like "≫ *" Then nm.Delete
    Next
End Sub

Private Function makeSafeName(sht As Worksheet, indexFormat As String) As String
    Dim nm As String
    Dim ch As Variant
    For Each ch In chars(sht.Name)
        nm = nm & IIf(isValidName("_" & ch), ch, "_")
    Next
    If indexFormat <> "" Then
        nm = Format(sht.index, indexFormat) & nm
    End If
    If isAddress(nm) Then
        nm = "_" & nm
    ElseIf Not isValidName(nm & "_") Then
        nm = "_" & nm
    End If
    makeSafeName = nm
End Function

Private Function makeUniqName(wbk As Workbook, ByVal orgName As String) As String
    Dim seq As Integer
    seq = 2
    makeUniqName = orgName
    While containsName(wbk, makeUniqName)
        makeUniqName = orgName & "_" & seq
        seq = seq + 1
    Wend
End Function

Private Function containsName(wbk As Workbook, ByVal nm As String) As Boolean
    On Error Resume Next
    containsName = Not wbk.Names(nm) Is Nothing
    Err.Clear
End Function

Private Function isValidName(ByVal str As String) As Boolean
    If str Like "*[% % " & vbLf & "]*" Then Exit Function ' False
    isValidName = Not IsError(Application.ConvertFormula("=" & str, xlA1))
End Function

Private Function isAddress(ByVal str As String) As Boolean
    Dim org, cnv
    org = "=" & str
    ' アドレスでなければそのままのはず
    cnv = Application.ConvertFormula(org, xlR1C1, xlA1)
    If Not IsError(cnv) Then
        isAddress = cnv <> org
        Exit Function
    End If
    cnv = Application.ConvertFormula(org, xlA1, xlR1C1)
    If Not IsError(cnv) Then
        isAddress = cnv <> org
        Exit Function
    End If
End Function

Private Function chars(ByVal str As String) As String()
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "(?!^|$)(?![\uDC00-\uDFFF])"
        chars = Split(.Replace(str, vbTab), vbTab)
    End With
End Function

【使い方】

  1. 新規ブックをマクロ用に開き、上記VBAプログラムをその標準モジュールにコピー&ペーストします。
  2. シート名を名前定義したい対象のブックを前面に開いた状態で、本マクロを実行します
    • 「シート名の名前定義」か「シート名の名前定義_インデックス付き」
  3. 各シートのA1セルを参照する名前がシート名で定義されます
    • 「名前」に使えない文字は "_" (アンダーバー)に置き換えられます
    • 「名前」の最初の文字に使えない文字は、その前に "_" (アンダーバー)が付加されます
    • 既に同一の名前が存在していた場合、上書きしないように連番を追加して再試行します

シート数が多いと少し時間がかかります(100シートで数秒程度)。

再実行すると、いったん定義名を全削除してから再登録します。シートを追加したらそのまま再実行すればOKです。

大抵のシート名は問題ないはずですが、シート名に使っている文字によってはエラーになる可能性があります。もしエラーになったら、そのシート名を教えてください。

マクロ実行後、マクロ用ブックは破棄してかまいません。

まとめ

Excel はワークシートの操作性が悪く、大量シートを扱うと作業効率が下がります。

Excel の既存機能を使い、大量シートの操作効率を改善する方法をいくつか検討してみました。

「名前の定義」をうまく使うと、「ブックマーク」や「履歴」、「戻る」といった Web ブラウザ的な操作性を実現できそうです。

名前ボックスで「検索」できるよう、シート名を名前定義しておくというのはいい習慣になるかもしれません。

本記事の内容は Windows 10 の Excel 2013 で検証しました。