Excel作業の生産性が劇的にアップ 任意のシートに瞬時に移動できるツール

はじめに

『Pythonでスラスラわかるベイズ推論「超」入門』などの著者の赤石です。
今日はAIともPythonともまったく関係ない記事を投稿します。

それは何かというと、Excelのマクロです。
先日、業務でたくさんのシートのあるExcelを扱ったのですが、Excelはシートの数が増えてくると、うしろの方のシートにアクセスするのが、どんどん大変になってきます。そもそも、シートの数が多いと、全体でどんなシートがあるのかもわからなくなりますし。そんなことで、イライラしたもので作ってしまったのが今回ご紹介するマクロです。
ほとんど瞬間芸といっていいくらい簡単なものなのですが、使って見ると意外に便利です。
シートの数の多いExcelに悩んでいる方は是非、お試し下さい。

(2024-01-22 追記)
読者から下記の操作でほぼ同じことがExcelの操作のみでできることを教えていただきました。さすがExcel、奥が深いです。
しかし、この記事で書いた方法を使うことは、メンテナンス可能な見出しページを作ることと同じです。
こちらの方が便利なこともあるので、用途によって使い分ける形になるかと思います。

簡易手順

下のリストが出てくるので、このリストを使って行く先シートを選択

資材一式

以下の話は、実際に動かしながら読んでいただくのが一番理解が早いです。
以下の３つの資材はgithubにアップしておきましたので、まずはこちらを全部ダウンロードして下さい。

https://github.com/makaishi2/samples/blob/main/data/excel_sample.xlsm
このツールの動作の様子を確認するのに利用します。

https://github.com/makaishi2/samples/blob/main/data/template.xlsm
自分で利用しているExcelに今回の仕組みを組み込むときに利用します。

https://github.com/makaishi2/samples/blob/main/data/datasets.xlsx
Excelへの組み込み手順説明のためのサンプルです。

どのように動くのか

まずは、今回の仕組みがどのような動きをするものなのか、サンプルを操作しながら確認します。
なお、筆者のPCはMacです。Windowsの場合、多少画面が違う可能性がありますが、その点はあらかじめご理解ください。

上でダウンロードした　excel_sample.xlsmをダブルクリックして開いてください。
拡張子がxlsmとなっているのは、このExcelがマクロを含んでいることを意味します。
ダブルクリックすると、次のようなパネルが出てくるはずです。

ここでは、下の「マクロを有効にする」を選んでください。
すると、次のような画面になります。
後ほど、ご紹介するように、このExcelでは、３つのマクロが用意されています。
３つのボタンは、ぞれぞれのマクロが呼び出される形に設定されています。

また、画面下の方を見ていただくとわかるとおり、ダミーではありますが、たくさんのシートが用意されています。
ここで一番右の「シート一覧作成」のボタンをクリックしてください。
次のように画面がなるはずです。

シート名から自分自身(「シート一覧」)を除いたものが、B列に一覧として作られました。
ちなみに、このシートのA列とC列は業務利用の際にこういう列があると便利だろうということで作っています。ツールとしての動作にはまったく関係がないです。

次にこのシートで、B列の特定の項目を選択した状態にします。

上の図ではB12のセルが選択されています。この状態で、「該当ページにジャンプ」のボタンをクリックします。
すると、下の図のように目指すページにジャンプできていることがわかります。

最後に上の図の「シート一覧に戻る」をクリックすると、もとの目次シートに戻ります。
たったこれだけのことですが、シートの多いExcelで使ってみると、本当に便利です。

マクロ

仕組みを作るための最初のステップとしてVBAのマクロを記載します。

Sub go_sheet()
   Sheets(ActiveCell.Value).Select
End Sub

Sub go_index()
    Sheets("シート一覧").Select
End Sub

Sub ListSheetNames()
    Dim ws As Worksheet
    Dim i As Integer

   Sheets("シート一覧").Select
    ' ワークブック内の全シート名を一覧にする
    i = 4
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name = "シート一覧" Then
            GoTo Continue
        End If
        Cells(i, 2).Value = ws.Name
        i = i + 1
Continue:
    Next ws
End Sub

go_sheetとgo_indexは、見ていただければわかるとおり、たった１行のコードです。
ちょっと複雑なのは、３つめのListSheetNamesですが、このコードのほとんどのところはChatGPTに作ってもらいました。
シート一覧で自分自身を除く部分だけ、手を加えています。
余談ですが、調べて知ったこととして、このご時世にVB Scriptはcontinueに相当する制御構造文がないのですね。どんだけ昭和な言語なんだと思ってしまいました。

Excelにマクロ機能を追加する手順

では、いよいよ、自分のExcelにこのマクロ機能を追加する手順を説明します。
説明用に冒頭でダウンロードいただいたExcelのうち、datasets.xlsxにマクロ機能を追加することします。
大まかな手順は以下の通りです。1つ1つ順番に説明します。

• 拡張子の変更
• シート一覧ページのコピー
• VBAスクリプトの貼り付け
• ボタンとマクロの関連付け
• 動作テスト
• 戻るボタンの追加

拡張子の変更

最初にdatasets.xlsxを開いたのち、メニューから「名前を付けて保存」を選択します。
ファイル形式のドロップダウンをクリックし、ファイル形式を「Excelマクロ有効ブック」に変更して保存します。

これで、このExcelでマクロが組めるようになりました。

シート一覧ページのコピー

次に、template.xlsmを開きます。「シート一覧」というシートがあるので、これを、先ほど用意した「datasets.xlsm」にコピーしてください。
ここは、Excelとしての普通の操作なので、細かい説明は省略します。
コピーの操作が終わると、datasets.xlsmは、次のような4枚のシート構成になっているはずです。

シートのコピーが終わったら、操作を簡単にするため、template.xlsmは閉じて、datasets.xlsmだけが残っている状態にしてください。

VBAスクリプトの貼り付け

VBスクリプトの開発ツールを開きます。
Windowsの手順はよくわからないですが、Macの場合、下の画面のように、最上部のメニューから「ツール」「マクロ」「Visual Basic Editor」を選びます。

こんな画面が出てきます。はじめての人はちょっとビビるかもしれませんが、一瞬で終わるので頑張って続けてください。

 

上部のメニューから「挿入」「標準モジュール」を選択します。

 

こんなエディタの画面が表示されたら、編集エリアに上で紹介したVB Scriptのコードをまるごとコピーしてください。

コピー後はこんなふうになるはずです。

上部のメニューから「ファイル」「datasets.xlsmの保存」を選びます。

これでVBAスクリプトの作業は完了です。このエディタはもう、閉じてしまってください。
残りは、Excel上の操作だけでOKです。

ボタンとマクロの関連付け

元のExcelの画面に戻ったら、左上の「シート一覧に戻る」の上にマウスポインタを付けて右クリックします。
下のようなメニューが出てくると思います。

「マクロの登録」を選びます。

上のパネルが出たら、リストから「go_index」を選択し、画面右下の「OK」をクリックします。
これで、一番左のボタンと、VB Script関数go_indexの紐付けができたことになります。

以下、同様に、真ん中のボタンとgo_sheet関数、一番右のボタンとListSheetNames関数の紐付けも行ってください。

動作テスト

これで、マクロの大枠はできたはずです。簡単な動作テストをしてみましょう。
最初に一番右の「シート一覧作成」ボタンをクリックします。
こんな結果になれば成功です。

次に、選択セルをB6の「iris_dataset」にした状態で、真ん中の「該当ページにジャンプ」をクリックします。
下の画面のように、シートが移動されれば成功です。

戻るボタンの追加

最後に各シートに「戻る」ボタンを追加しましょう。
ここはきれいに自動化する仕組みを作っておらず、手作業での対応となります。
まず、「シート一覧」のシートに戻り、一番左の「シート一覧に戻る」ボタンの上で、マウス右クリックをします。

上の画面が出てきたら、上から2つめの「コピー」を選択します。
これでクリップボードに「シート一覧に戻る」ボタンがコピーされます。
あとは、各シートに移動して、先頭2行にブランク行を挿入します。
カーソルを左上に置いて、ここで貼り付けをすると、「シート一覧に戻る」ボタンがコピーされます。
多少手間ですが、各シートにこの操作をしてください。

これで、インデックスシート付きのExcelが完成です。

最後に

自分で試してみて、今までシートの移動にどんだけ無駄な時間を使っていたのかと思ってしまいました。
皆様も、このツールを活用して快適なExcelライフを過ごしていただければ幸いです。