作者別: it-yobi

定期的に送られてくる複数の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の表示が切り替わります。

▼サンプルファイル▼