作者別: it-yobi

以下のフォーマットの氏名の部分（○○ ○○）に、隣の「名簿」シートの１００人分の氏名を１つずつ入力してPDFファイルで出力する必要があるとします。

この作業を１件ずつ行うのは、かなり大変です。

ということで今回は、１００人分のPDFファイルを自動で出力する仕組みの実現方法について解説していきます。

※こちらで作成したファイルは、記事の最後にて配布しています。

1. 開発準備

以下の２つのシートを元に作成していきます。

こちらでは、「名簿」シートの各行の氏名を１つずつ、「修了証」シートの「○○ ○○」が入力されたセルに入力して、PDFファイルで保存する流れを自動化します。
出力されるPDFファイルの名前は、「（対象氏名）.pdf」とします。

まず初めに、[開発]タブを選択し、[マクロ]を選択します。

表示された以下の画面に開発する機能の名前を入力し、[作成]を選択します。
こちらでは、「一括PDF出力」と入力しています。

次に表示される以下の画面の「Sub 一括PDF出力」から「End Sub」の間にコードを記述します。
「Option Explicit」は、VBEの設定次第では表示されません。「Option Explicit」についての解説はこちらでは省略します。

2. コードの記述

以下のコードを記述します。

Sub 一括PDF出力()

    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Set ws1 = Worksheets("修了証")
    Set ws2 = Worksheets("名簿")
    
    Dim i As Long, sPath As String
    For i = 3 To 102
        sPath = "A:\出力\" & ws2.Cells(i, "B").Value & ".pdf"
        ws1.Range("C3").Value = _
            ws2.Cells(i, "B").Value
        ws1.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:=sPath
    Next i

End Sub

では、コードについて解説していきます。

    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Set ws1 = Worksheets("修了証")
    Set ws2 = Worksheets("名簿")

「ws1」という変数に「修了証」シートの情報、「ws2」という変数に「名簿」シートの情報を割り当てています。

    Dim i As Long, sPath As String
    For i = 3 To 102
        '省略
    Next i

「名簿」シートの３行目から１０２行目の氏名に対し、１つずつPDFファイルを出力する処理を行っています。

        sPath = "A:\出力\" & ws2.Cells(i, "B").Value & ".pdf"

各行の氏名に対し、「sPath」という変数に、保存先の情報とPDFファイルの名前を指定しています。
こちらでは、Aドライブの中の出力フォルダのパスに、「名簿」シートの該当行のB列の氏名を加え、最後に「.pdf」と拡張子を加えています。

保存先を変更する場合は、ここのパスを変更します。

        ws1.Range("C3").Value = _
            ws2.Cells(i, "B").Value
        ws1.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:=sPath

「修了証」シートのセルC3（セルが結合されている場合は左上のセル）に、「名簿」シートの該当行の氏名を入力しています。

そして、「修了証」シートの印刷範囲をPDFファイルで出力しています。

ws1.PrintOut

3. 完成

左上のリストから「一括PDF出力」を選択し、実行ボタンを押すことで、１００人分のPDFファイルが出力されます。

▼サンプルファイル▼

こちらでは、複数の書類テンプレートを１つのファイルで管理し、かつ上書き保存させずに、指定した書類テンプレートで簡単に新規作成することができる仕組みを作っていきます。

具体的には、以下のように各シートの書類テンプレートの上に、「このテンプレートを使用する」というボタンを配置し、そのボタンを押すことで、そのシートのみが新規ブックにコピーされるという仕組みを実現します。

新規ブックにコピーされるので、誤って上書き保存する心配がありません。

※こちらで作成したファイルは、記事の最後にて配布しています。

1. 開発準備

こちらでは、以下のファイルを元に実現していきます。

こちらのファイルは、複数の書類テンプレートをシートごとで管理したものです。
必要に応じて、シートを追加していただいても問題ございません。

今回は、上記のファイルを活用して、「選択しているシートのみを新規ブックにコピーする」という機能を実装していきます。
ファイルの用意ができたら、[開発]タブから[マクロ]を選択し、表示された設定画面に「開発する機能の名前」を入力して[作成]を選択します。
こちらでは、「CopyTemplateSheet」と入力しています。

次に表示される以下の画面の「Sub ○○」から「End Sub」の間にコードを記述します。
「Option Explicit」は、VBEの設定次第では表示されません。「Option Explicit」についての解説はこちらでは省略します。

2. コードの記述

以下のコードを記述します。

Sub CopyTemplateSheet()

    ActiveSheet.Copy
    
    Dim ws As Worksheet
    Set ws = ActiveSheet
    
    Dim i As Long
    For i = ws.Shapes.Count To 1 Step -1
        If ws.Shapes(i).Type = msoFormControl Then
            If ws.Shapes(i).FormControlType = xlButtonControl Then
                ws.Shapes(i).Delete
            End If
        End If
    Next i
    
End Sub

では、コードについて解説していきます。

    ActiveSheet.Copy
    
    Dim ws As Worksheet
    Set ws = ActiveSheet

選択中のシートを新規ブックにコピーし、そのコピーしたシートの情報を「ws」という変数に割り当てています。

    Dim i As Long
    For i = ws.Shapes.Count To 1 Step -1
        If ws.Shapes(i).Type = msoFormControl Then
            If ws.Shapes(i).FormControlType = xlButtonControl Then
                ws.Shapes(i).Delete
            End If
        End If
    Next i

コピーされたシート上の「図形の末尾の通し番号」から順番に繰り返し、指定した番号の図形がフォームコントロール（msoFormControl）であり、さらにボタン（xlButtonControl）の場合に、その図形を削除しています。

この処理は、最終的に用意する「このテンプレートを使用する」というボタンを、コピーされたシートから削除するためのものです。

末尾の通し番号から順番に繰り返している理由は、図形を削除したときに番号がずれてしまわないようにするためです。

3. ボタンの作成

コードが完成したら、実行用のボタンを用意します。

ボタンは、[開発]タブの中の[挿入]から作成することができます。
ボタンには開発した機能「CopyTemplateSheet」を割り当てます。

ボタンを１つ作成したら、そのボタンをコピーし、他のシートにも貼り付けます。

ご自身で書類テンプレートのシートを追加された際は、同様に同じボタンを貼り付けます。

4. 完成

以上の手順で完成です。
使用したい書類テンプレートのシートを選択して、そのシート上の「このテンプレ－トを使用する」のボタンをクリックするだけで、そのシートが新規ブックにコピーされます。
また、そのコピーされたシートからは「このテンプレートを使用する」のボタンのみが削除されます。

他にもボタン以外の図形が含まれる場合でも、ボタン以外の図形は削除されません。

▼サンプルファイル▼

以下の表は、商品の価格を管理したものです。
最新の価格を末尾に追加することで、各商品の価格の推移を管理しています。

この表から、数式だけで各商品の最新価格を抽出する方法について解説していきます。

※こちらで作成したファイルは、記事の最後にて配布しています。

今回紹介する数式を使えば、今後の更新も一切不要です。

1. テーブル化

数式を入力する前に、表のデータの増減に簡単に対応するため、対象の表を「テーブル」に変換します。

まずは、表の中にカーソルを移動させ、[挿入]タブから[テーブル]を選択します。

指定されている範囲が正しいことを確認し、表の先頭行が見出し行のため、[先頭行をテーブルの見出しとして使用する]にチェックした状態で確定します。

テーブルに変換すると、以下のようにデザインが変わってしまいます。

デザインは、[テーブルデザイン]タブの[テーブルスタイル]から簡単に変更することができます。
こちらでは、元の書式を生かすため、[テーブルスタイル]を[なし]にします。

フィルターボタンが不要な場合は、フィルターボタンのチェックを外しておきます。

次に、テーブル名を変更します。
テーブル名は[テーブルデザイン]タブから変更できます。
こちらでは「価格一覧」にしています。

以上の手順で、テーブルの完成です。

2. 数式の入力

次は、数式の入力です。
最新価格を表示する用の表の見出しは、数式ではなく直接用意しておきます。

見出しの用意ができたら、数式を入力していきます。

まずは、テーブルの商品名から重複を除いた一覧を数式で作成します。
指定した範囲から重複を除外するには、UNIQUE関数が便利です。

UNIQUE関数を活用する場合、以下のように入力します。

=UNIQUE(価格一覧[商品名])

数式を入力することで、以下のように商品名一覧を抽出できます。

テーブルの範囲は、構造化参照という特殊な参照方法で指定できます。
構造化参照については、以下で詳しく解説しています。
＞構造化参照とは

次は、各商品の最新価格を抽出します。
まずは、先頭の「消しゴム」の最新価格のみを抽出していきます。
商品名が指定した値と一致する行の末尾の価格のみを抽出する場合は、XLOOKUP関数が便利です。

XLOOKUP関数を活用する場合、以下のように入力します。

=XLOOKUP(F3,価格一覧[商品名],価格一覧[価格],,,-1)
// F3：検索値
// 価格一覧[商品名]：検索範囲
// 価格一覧[価格]：抽出範囲
// （省略）：見つからない場合
// （省略）：一致モード（省略時は完全一致）
// -1：検索モード（「-1」は末尾から先頭へ検索）

数式を入力することで、以下のように最新価格を抽出できます。

他の行の商品も一括で抽出する場合は、XLOOKUP関数の検索値に商品名一覧の全体を指定します。
商品名一覧は１つの数式により展開されています。
そのような数式で展開されている範囲は、数式が入力されているセルに「#」を加えることで、全体を指定できます。

=XLOOKUP(F3#,価格一覧[商品名],価格一覧[価格],,,-1)

数式を入力することで、以下のように各商品の最新価格を一括で抽出できます。

3. 完成

以上の手順で完成です。
テーブルにデータを追加すると、自動でテーブルの範囲が拡張されます。
そのため、数式を更新する必要がなく、継続的に活用することができます。

▼サンプルファイル▼

以下は、請求書のフォーマットのサンプルになります。

このフォーマットに以下の順番で値を入力するとき、毎回カーソルを移動するのは面倒になります。

そこで今回は、セルF3に値を入力して確定したらセルF4、セルF4に値を入力して確定したらセルB4と、好みの順番で遷移させる仕組みの実現方法について解説していきます。

※こちらで作成したファイルは、記事の最後にて配布しています。

1. 開発準備

以下のフォーマットを元に作成していきます。

今回は、セルを編集して確定すると同時に、予め指定したセルに遷移させる仕組みを実現します。
そのような、特定のシートのセルを編集すると同時に何かしらの処理を実行するには、「シートモジュール」の「イベントプロシージャ」を活用します。
シートモジュールは、対象シートのタブ上で右クリックし、[コードの表示]を選択することで表示できます。

選択すると、以下のエディタ画面（VBE）が表示されます。
また、対象シートのシートモジュールが表示された状態になります。
「Option Explicit」は、VBEの設定次第では表示されません。「Option Explicit」についての解説はこちらでは省略します。

次に、対象シートのセルを編集すると同時に処理が実行される特殊なプロシージャ（イベントプロシージャ）を用意する必要があります。
そのためには、シートモジュールの左上のリストから「Worksheet」を選択します。

「Worksheet」を選択すると、自動で「Worksheet_SelectionChange」というプロシージャが表示されます。
このプロシージャは、対象シートのいずれかのセルが選択されると同時に処理が実行されるイベントプロシージャになります。

ただ、今回使用するイベントプロシージャは、セルを編集すると同時に処理が実行されるものになります。
そのため、右上のリストから「Change」を選択します。

表示された「Worksheet_Change」というプロシージャを活用します。
「Worksheet_SelectionChange」というプロシージャは削除して問題ないです。

2. コードの記述

以下のコードを記述します。

Private Sub Worksheet_Change(ByVal Target As Range)

    Select Case Target.Address(False, False)
        Case "F3"
            Range("F4").Select
        Case "F4"
            Range("B4").Select
        Case "B4"
            Range("C7").Select
        Case "C7"
            Range("C8").Select
        Case "C8"
            Range("C9").Select
        Case "C9"
            Range("B14").Select
    End Select

End Sub

では、コードについて解説していきます。

Private Sub Worksheet_Change(ByVal Target As Range)

    '省略

End Sub

「Worksheet_Change」というプロシージャの引数「Target」に、編集されたセルの情報が渡されます。
複数セルを同時に編集した場合は、複数セルの情報が渡されます。

    Select Case Target.Address(False, False)
        Case "F3"
            Range("F4").Select
        Case "F4"
            Range("B4").Select
        Case "B4"
            Range("C7").Select
        Case "C7"
            Range("C8").Select
        Case "C8"
            Range("C9").Select
        Case "C9"
            Range("B14").Select
    End Select

編集されたセルのアドレスを相対参照（F3やF4など）で取得し、取得されたアドレスに対しての遷移先を指定しています。
セルF3が編集された場合は、「Case “F3″」の中が実行され、セルF4が選択されます。
セルF4が編集された場合は、「Case “F4″」の中が実行され、セルB4が選択されます。
候補にないアドレスの場合は、何も実行されません。

3. 完成

以上の内容で実現できます。
編集されたセルのアドレスが、予め指定したアドレスの場合は、指定したセルに自動で遷移します。

以下の表の中は、標準機能で簡単に移動できます。

表の中の移動については、以下の記事を参考にしてください。
＞【Excel】表へのデータ入力を快適にするテクニック

▼サンプルファイル▼