小技集

数量を管理している表で、よく数量に１を加えたり、１を減らしたりと作業をしている場合、数量のセルに数値を毎回、直接入力するのは大変です。
数量の項目が少ない場合は、スピンボタンなどを活用するのも１つの手ですが、項目数が多い場合は、スピンボタン１つ１つにセルを割り当てるのは大変になります。

ということで、今回は、数量の隣のセルを直接クリックするだけで、数量に１を加えたり、１を減らしたりすることができる仕組みの作り方について解説していきます。

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

1. 開発準備

今回は、数量の隣のセルを右クリックすることで、該当行の数量のセルの値を更新するという仕組みを実装していきます。

まずは、以下の左側の表の、数量の隣のセルにボタンのようなデザインのセルを用意します。

上記では、「セルの書式設定」より罫線と背景色などを設定しています。
また、E列には「＜」、F列には「＞」という文字を直接入力しています。

ボタンのデザインの用意ができましたら、右クリックすると同時に数量を更新する仕組みを実装していきます。
特定のシートの特定のセルを右クリックすることで何かしら処理を実行するには、該当するシートのシートモジュールを活用します。

シートモジュールは、該当するシートのタブ上で右クリックし、[コードの表示]を選択することで表示することができます。

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

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

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

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

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

2. コードの記述

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

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    
    If Target.CountLarge = 1 And _
        Target.Row >= 3 And _
        Target.Row <= 21 And _
        Target.Column >= 5 And _
        Target.Column <= 6 Then
        
        Cancel = True
        Select Case Target.Column
            Case 5:
                Cells(Target.Row, "D").Value = _
                    Cells(Target.Row, "D").Value - 1
            Case 6:
                Cells(Target.Row, "D").Value = _
                    Cells(Target.Row, "D").Value + 1
        End Select
        
    End If
    
End Sub

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

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    
    If Target.CountLarge = 1 And _
        Target.Row >= 3 And _
        Target.Row <= 21 And _
        Target.Column >= 5 And _
        Target.Column <= 6 Then
        
        '省略
        
    End If
    
End Sub

「Worksheet_BeforeRightClick」というプロシージャの引数の「Target」に、右クリックされたセルの情報が渡されるため、そのセルの情報から、対象の範囲内が右クリックされたかどうかを確認しています。
こちらでは、「右クリックされたセルが単体で、３行目以上２１行目以下、５列目以上６列目以下のセル」と、対象の範囲内の単体のセルが右クリックされたかどうかを確認しています。

この条件を満たした場合に関してのみ、Ifの中の処理を実行します。

        Cancel = True
        Select Case Target.Column
            Case 5:
                Cells(Target.Row, "D").Value = _
                    Cells(Target.Row, "D").Value - 1
            Case 6:
                Cells(Target.Row, "D").Value = _
                    Cells(Target.Row, "D").Value + 1
        End Select

先ほどの条件を満たした場合は、「Cancel = True」で右クリック時のメニューが表示されないようしています。

その次に、右クリックされたセルが５列目（E列）なのか、６列目（F列）なのかによって、処理を分岐しています。
５列目（E列）の場合は、その右クリックされたセルと同じ行のD列の値（数量）に対し１を引き、６列目（F列）の場合は、その右クリックされたセルと同じ行のD列の値（数量）に対し１を足しています。

3. 完成

先ほどのコードを記述したら完成です。
「＜」もしくは「＞」が入力された以下の赤枠内のセル上で右クリックすることで、対象行の数量に対し、１を加えたり、１を減らしたりすることができます。

▼サンプルファイル▼

以下の表は、セルC2に入力した値に応じてデータが抽出されるように作られています。

今回は、このセルC2の値を「A→B→C」のように好みの値に切り替えながら、このシートの印刷範囲の内容をPDFファイルにして自動で保存する仕組みを実現していきます。

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

1. 開発手順

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

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

次に表示される以下の画面の「Sub ○○」から「End Sub」の間にコードを記述します。

2. コードの記述（PDFファイル出力）

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

Sub ExportToPDF()
    
    Dim fPath As String
    fPath = ThisWorkbook.Path
    
    Worksheets("PDF出力").ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=fPath & "\Sample.pdf"
    
End Sub

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

    Dim fPath As String
    fPath = ThisWorkbook.Path

「fPath」という変数（入れ物）を用意し、現在開いているExcelファイルの保存先のフォルダのパスを「fPath」に格納しています。
そのため、取得されるパスは環境により異なります。

こちらの環境の場合は、以下のパスが取得されます。

A:\PDF出力

    Worksheets("PDF出力").ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=fPath & "\Sample.pdf"

「PDF出力」というシートの印刷範囲をPDF形式（Type:=xlTypePDF）で保存しています。
保存先とファイル名は、パスで指定します。
こちらでは以下のように、Excelファイルと同じ配下に「Sample.pdf」という名前で保存しています。

Filename:=fPath & "\Sample.pdf"

以上のコードを実行することで、以下のようにPDFファイルを作成することができます。

ただこのままでは、セルC2の値を手動で変更する必要があります。
そこで、次のように修正します。

3. コードの記述（引数の設定）

以下のように修正します。

Sub ExportToPDF(fName As String)
    
    Dim fPath As String
    fPath = ThisWorkbook.Path
    
    Worksheets("PDF出力").ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=fPath & "\" & fName & ".pdf"
    
End Sub

では、修正点について解説していきます。

Sub ExportToPDF(fName As String)
    …
        Filename:=fPath & "\" & fName & ".pdf"

ファイル名を「Sample.pdf」から「引数に指定した好みの名前」で保存できるように修正しています。
後は、セルC2の値を変更して、セルのC2の値を「ExportToPDF」の引数に指定して実行するという処理を繰り返します。

そこで、次のようなプロシージャを用意します。

4. コードの記述（差し込み実行）

以下のプロシージャを用意します。

Sub ExportAll()
    
    Dim ws As Worksheet
    Set ws = Worksheets("PDF出力")
    
    Dim val As String
    
    val = "A"
    ws.Range("C2").Value = val
    Call ExportToPDF(val)
    
    val = "B"
    ws.Range("C2").Value = val
    Call ExportToPDF(val)
    
    val = "C"
    ws.Range("C2").Value = val
    Call ExportToPDF(val)
    
End Sub

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

    Dim ws As Worksheet
    Set ws = Worksheets("PDF出力")

「ws」という変数（入れ物）を用意し、「PDF出力」シートの情報を「ws」に割り当てています。

    Dim val As String
    
    val = "A"
    ws.Range("C2").Value = val
    Call ExportToPDF(val)

「val」という変数（入れ物）を用意し、「ws」シートのセルC2に入力したい１つ目の値（仮：A）を「val」に格納しています。
そして、「val」の値を「ws」シートのセルC2に格納した上で、最初に記述した「ExportToPDF」に「val」の値を渡して実行しています。

これで、１つ目のPDFファイルが作成されます。

    val = "B"
    ws.Range("C2").Value = val
    Call ExportToPDF(val)
    
    val = "C"
    ws.Range("C2").Value = val
    Call ExportToPDF(val)

こちらは、先ほどの処理と同様に、「B」という値、「C」という値でも実行しています。

5. 完成

以上の手順で完成です。
作成した「ExportAll」を実行するだけで、以下のように複数のPDFファイルが出力されます。

繰り返し似たような処理を実行する場合は、重複する処理の内容を１つのプロシージャ（ExportToPDF）にまとめておくことで、処理全体が見やすく保守しやすいコードになります。

▼サンプルファイル▼

定期的に送られてくる複数のExcelファイルを、以下のようにまとめるのは、手作業だと大変です。

ということで、「指定したフォルダ内のExcelファイルから、必要なデータを自動で抽出してまとめる仕組み」の実現方法について解説していきます。

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

1. 開発準備

今回は、フォルダのパスをインプットボックスに入力してから実行するだけで、以下のシートに、該当する項目のデータを抽出する仕組みを、VBAを活用して実現します。

まずは、[開発]タブから[マクロ]を選択し、表示された画面の[マクロ名]に好みの名前（例：ImportSalesData）を入力して、[作成]を選択します。

以上の手順で、入力したマクロ名のプロシージャが表示されるため、そのプロシージャを活用します。
「Option Explicit」は、VBE（エディタ画面）の設定内容次第では表示されません。「Option Explicit」についての解説はこちらでは省略します。

2. コードの記述

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

Sub ImportSalesData()
    
    Dim fP As String, fN As String
    fP = InputBox("フォルダパス")
    fN = Dir(fP & "\*.xlsx")
    
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets(1)
    Dim r As Long, c As Long
    r = 3
    
    Do While fN <> ""
        With Workbooks.Open(fP & "\" & fN)
            For c = 2 To 5
                ws.Cells(r, c).Value = _
                    .Worksheets(1).Cells(c, "C").Value
            Next c
            .Close
        End With
        r = r + 1
        fN = Dir()
    Loop
    
End Sub

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

    Dim fP As String, fN As String
    fP = InputBox("フォルダパス")
    fN = Dir(fP & "\*.xlsx")

対象のフォルダのパスを格納する用の変数「fP」と、そのフォルダ内の１つのExcelファイルの名前を格納する用の変数「fN」を用意しています。

そして、「fP」には、インプットボックスに入力した値（対象のフォルダのパスを入力する想定）を格納し、「fN」には、その「fP」のフォルダの中に格納されている１つ目のファイル名を格納しています。
「fP & “\*.xlsx”」で、「フォルダパス\*.xlsx」というファイルのパスを作成し、Dirで該当するファイルの名前を取得しています。
※「*」は０文字以上の任意の文字という意味です。

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets(1)
    Dim r As Long, c As Long
    r = 3

シート情報を格納する用の変数「ws」を用意し、抽出先のファイル（コードを記述している自分自身のファイル）の先頭のシート情報を格納しています。

次に、対象のシート（ws）に、抽出内容を入力するセルの位置を指定するための行番号と列番号を格納する用の変数「r：rowの略、c：columnの略」を用意しています。
行番号を格納する用の変数「r」には、予め「3」を格納しています。

    Do While fN <> ""
        '省略
        fN = Dir()
    Loop

指定したフォルダ内に該当するファイルが見つからない場合は、「fN」が空になります。
そのため、ここでは「fN」が空でない場合に、「Do」から「Loop」の中を実行するようにしています。

最後に「fN = Dir()」と記述することで、「fN」の中に次のファイル名が格納されます。
次のファイルが存在しない場合は、ファイル名を取得できないため、「fN」が空になります。

そして、再度、「Do」に戻り、「fN」が空になるまで繰り返して実行されます。

        With Workbooks.Open(fP & "\" & fN)
            For c = 2 To 5
                ws.Cells(r, c).Value = _
                    .Worksheets(1).Cells(c, "C").Value
            Next c
            .Close
        End With
        r = r + 1

繰り返しの中で行う処理です。
「Workbooks.Open(fP & “\” & fN)」で、指定したフォルダ（fP）内の対象のファイル名（fN）のファイルを開いています。
「With」で指定することによって、「With」から「End With」の中で「.」から記述したコードは、開いたファイルの情報に対しての処理になります。

「For」から「Next」の中を、変数「c」の値を２から５まで順番に変化させて繰り返し実行しています。
「２から５」というのは、抽出先の表のB列からE列までの列番号になります。

「For」の中で、抽出先のシートの「r」行目「c」列目のセルに、開いたブックの先頭のシートの「c」行目C列にある値を入力しています。

抽出先のシートの２列目には、抽出元のシートの２行目、
抽出先のシートの３列目には、抽出元のシートの３行目、
という感じに行番号と列番号が、行と列が逆の関係にあるため、上記のような処理になります。

１つのファイルの内容の入力ができたら、次のファイルに移る前に「r = r + 1」で行番号に１を加えています。

3. 完成

以上の内容で実現できます。
実行する際は、[開発]タブの[マクロ]を選択し、表示された画面から開発したマクロ名を選択して[実行]を選択します。

次に、表示されたインプットボックスに、抽出元のファイルが格納されたフォルダのパスを入力して確定します。

これでだけで、以下のように、指定したフォルダ内のすべてのExcelファイルの内容を抽出することができます。

▼サンプルファイル▼

以下の表を例に、XLOOKUP関数を使って複数条件で検索する方法について紹介していきます。

セルF3に「日付」、G3に「商品」を入力し、左側の表から該当するデータの「売上」を、セルH3に抽出する数式を作っていきます。

複数条件で検索する

XLOOKUP関数では、複数の項目を繋げた値に対して検索することができます。
そのため、今回の表では、「日付」と「商品」を繋げて１つの値として検索させます。

具体的には、以下のような数式になります。

=XLOOKUP(F3&G3,B3:B18&C3:C18,D3:D18,"")
// F3&G3：検索値は、検索したい「日付」と「商品」を&で繋げた文字列
// B3:B18&C3:C18：検索範囲は、対象の表の「日付」と「商品」を&で繋げた文字列（18は表の最後の行番号）
// D3:D18：抽出範囲は、「売上」の項目（18は表の最後の行番号）
// ""：見つからない場合は、何も表示しない

これだけで、複数項目での検索ができ、必要な値を抽出できます。

正しく検索できない場合と対策

複数の項目を繋げた際に、重複した文字列ができる場合があります。
例えば、以下の表の「グループ」と「商品」を繋げた場合です。

この表ですと、以下の２つのデータが重複してしまいます。
・グループ「A」、商品「BA」→「ABA」
・グループ「AB」、商品「A」→「ABA」

そのため、以下の数式では、正しく検索できない場合があります。

=XLOOKUP(F3&G3,B3:B18&C3:C18,D3:D18,"")

上記の例のように、複数の項目を繋げた際に重複した文字列ができる場合は、対策する必要があります。

その対策とは、項目と項目の間に区切り文字を入れるという方法です。
以下では、「-」を加えて対策しています。

=XLOOKUP(F3&"-"&G3,B3:B18&"-"&C3:C18,D3:D18,"")

区切り文字を加えることで、「AB-A」と「A-BA」という感じに重複していた文字列が異なる文字列となり、正しく検索し、正しい値を抽出することができるようになります。

文字数制限のあるSNSの投稿文などを作成する際に便利な、文字数をチェックする機能の作り方について解説していきます。

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

仕様

今回は、以下の仕様で作っていきます。

1. 入力した文字数に応じて残りの文字数を表示
2. データバーで視覚的に残りの文字数を表示
3. 制限を超えた場合は赤色で表示
4. 「残り○文字」という形式で表示

使用するシートは以下になります。

セルB3に文字を直接入力し、セルB2に残りの文字数を表示する想定です。

では、順番に作っていきます。

1. 入力した文字数に応じて残りの文字数を表示

今回は、５０文字を制限とした文字数チェック機能を作っていきます。
まずは、残りの文字数をセルB2に表示させるため、以下のような数式を入力します。

=50-LEN(B3)
// LEN(B3)：セルB3に入力されている文字数を取得

文字数の制限を変更する場合は、数式内の「50」を変更してください。

2. データバーで視覚的に残りの文字数を表示

では次に、残りの文字数を視覚的にデータバーとして表示していきます。
データバーの設定は「条件付き書式」で行います。

先ほど数式を入力したセルを選択し、[ホーム]タブから[条件付き書式]の[新しいルール]を選択します。

表示された画面で、ルールの種類を[セルの値に基づいてすべてのセルを書式設定]にし、以下の設定を行います。

・書式スタイル：データバー
・最小値：数値「0」
・最大値：数値「50」（制限する文字数）
※色などは必要に応じて変更してください。

上記の設定で確定することで、以下のようにデータバーを表示することができます。

3. 制限を超えた場合は赤色で表示

現状の設定のままですと、文字数の制限を超えた場合は、マイナスの値が表示されるだけになります。

そのため、マイナスの値の場合（文字数の制限を超えた場合）は、セルの背景色を赤色にして目立たせていきます。

先ほど数式を入力したセルを選択し、[ホーム]タブから[条件付き書式]の[新しいルール]を選択します。

表示された画面で、ルールの種類を[指定の値を含むセルだけを書式設定]にし、以下の設定を行います。

・[セルの値]、[次の値より小さい]、「0」

次に、[書式]を選択し、以下の設定を行います。

・[塗りつぶし]タブ → 赤
・[フォント]タブ → 色 → 白

上記の設定で確定することで、以下のように文字数の制限を超えているときに、赤色にすることができます。

4. 「残り○文字」という形式で表示

最後に、数値を「残り○文字」という形式にしていきます。
数値を変えずに見た目のみを変更するには「表示形式」を活用します。

先ほど数式を入力したセルの上で右クリックし、[セルの書式設定]を選択します。

表示された画面の[表示形式]タブを選択し、[ユーザー定義]にて以下の内容を入力します。

"残り"0"文字";"残り"-0"文字"

表示形式については、以下の記事にて詳しく解説しています。
＞【1-入門04】セルの表示形式の基礎から応用

上記の設定で確定し、必要に応じて、太字や中央揃えといった設定を行います。

完成

以上の手順で完成です。
セルB3に入力された文字数に応じて、セルB2の表示が切り替わります。

▼サンプルファイル▼