【Excel】全シートのハイパーリンクを一括作成

Excelでシート数の多い資料を作成したとき、目次シートを用意して各シートへのハイパーリンクを作成することがあると思います。

シート数が多いと1つずつハイパーリンクを作成するのも大変なため、一括で全シートへのハイパーリンクを作成できるようにしてみました。

このエクセルファイルの作り方

①マクロを使えるようにします。(既に設定済の方は②に進んでください)
※「マクロを使えるようにする」を参照

②「Alt + F11」でVBEを開きます。

③「挿入」から「標準モジュール」を選択します。

④以下のコードをコピー&ペーストします。

Sub toc()
    For i = 1 To Sheets.Count
    Cells(Selection(1).Row + i - 1, Selection(1).Column).Value = "=HYPERLINK(""#'" & Worksheets(i).Name & "'!A1"",""" & Worksheets(i).Name & """)"
    Next i
End Sub

⑤ファイルの種類で「Excelアドイン(*.xlam)」を選択して保存します。

⑥「Excelアドイン」ボタンから、作成したアドイン(Toc)を有効にします。

⑦「ファイル」を押します。

⑧「オプション」を押します。

⑨「クイックアクセスツールバー」に今回作成した「Table」を追加します。

⑩「クイックアクセスツールバー」のボタンから、テーブルを自動作成できるようになりました。

【Excel】アドインを作ってみる

Excelのアドイン機能を活用することで、普段よく行う操作をワンクリックでできるようになります。
今日は、テーブルの自動作成アドインを作っていきたいと思います。

このエクセルファイルの作り方

①マクロを使えるようにする(既に設定済の方は②に進んでください)
マクロを使えるようにする」を参照

②「マクロの記録」ボタンを押します。(マクロ名は何でもいいですが、今回は「Table」とします)

③テーブルを作成します

④「記録終了」を押します。

⑤「Alt + F8」を押して、「編集」を押します。(ソースコードが表示されます)

⑥ソースコードを微調整していきます。以下のソースをペーストします。

Sub Table()
    '特定のCellではなく選択範囲に罫線を描画する(Selectionというのが選択範囲)
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    'タイトルを塗りつぶし(選択範囲の先頭行のみ)
    Range(Selection(1), Selection(1).Offset(0, Selection.Columns.Count - 1)).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = -0.249977111117893
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    'タイトルを描画(選択範囲の先頭行のみ)※これはなくてもいいです
    For col = Selection(1).Column To Selection(1).Offset(0, Selection.Columns.Count - 1).Column
        Cells(Selection(1).Row, col) = "Col" & col - Selection(1).Column + 1
    Next col
End Sub

⑦ファイルの種類で「Excelアドイン(*.xlam)」を選択して保存します。

⑧「Excelアドイン」ボタンから、作成したアドイン(Table)を有効にします。

⑨「ファイル」を押します。

⑩「オプション」を押します。

⑪「クイックアクセスツールバー」に今回作成した「Table」を追加します。

⑫「クイックアクセスツールバー」のボタンから、テーブルを自動作成できるようになります。

【Excel】シートの一覧表示

Excelのシートが数十枚になってくると探すのが大変です。

今日はシートの一覧を表示し、素早くシート間移動する方法を紹介させて頂きます。

以下のように、Excelの左下部分を右クリックすることでシートの一覧を表示できます。

【Excel】当日分のシフトを自動で抽出

通常、シフト表は横軸に日付、縦軸にスタッフ名を記載したマトリクスにすることが多いです。

1ヵ月の期間などでシフトを把握するのにマトリクスは有効ですが、今日出勤のスタッフを確認したい場合はマトリクスは不向きです。

今回は、Excel関数のみで今日出勤のスタッフを抽出できるようにしてみました。

このエクセルファイルの作り方

①TODAY関数で本日日付を取得

②以下の関数で、今日のシフト毎のスタッフを表示

IFERROR(TEXTJOIN(“、”,TRUE,FILTER($B$4:$B$15,INDIRECT(ADDRESS(4, MATCH($C$17,$3:$3)) & “:” & ADDRESS(15, MATCH($C$17,$3:$3)))=$B18)), “なし”)

以下、各関数の解説

1. MATCH(検査値, 検査範囲)で検査値を5/3、検査範囲を3行目とし、5/3のある列(5)を取得

2.ADDRESS(行番号, 列番号) で行番号には表の範囲である「4」と「15」を指定し、セルの座標「$E$4」「$E$15」をそれぞれ取得

3.INDIRECT(参照文字列) で、参照文字列にはADDRESSで取得した「$E$4」「$E$15」と「:」を連結した文字列「$E$4:$E$15」を指定

4.FILTERで「$E$4:$E$15」の中で「$B18(早番)」に該当する行と一致する「$B$4:$B$15」内の値「久保、前田、三宅」を取得

5.TEXTJOINで「久保」「前田」「三宅」を「、」で区切り連結

6.IFERRORで該当データがない場合(早番がいない場合)は「なし」と表示