作者別: it-yobi

Excel関連の何かしらの作業を自動化する際に、便利な機能としてマクロというものがあります。
このマクロを開発することができるプログラミング言語がVBAになります。
こちらでは、以下の作業を自動化するマクロを、VBAを用いて開発する方法について解説していきます。
※開発したファイルは記事に最後にて配布しています。

【作業内容】

1.以下のテンプレートシート「YYYYMMDD」のコピーを作成し、シート名を作業日の日付（YYYYMMDD形式）にする

2.コピーしたシートのセルB2に作業日の日付を入力する

では早速、自動化するマクロを開発していきます。
※開発したマクロに関しては、「Ctrl＋Shift+A」というショートカットで実行できるようにも設定していきます。

まずは、[開発]タブを選択し、その中の[マクロ]を選択します。

次に、以下の画面にて、好みの名前を入力し、[作成]を選択します。
こちらでは「シート作成」と入力しています。

表示されました以下の画面にて、プログラムを記述していきます。
※「Option Explicit」は設定内容によっては表示されません。

記述する内容は、以下になります。

こちらのプログラムに関して、１行ずつ解説していきます。

まずは、以下の内容について解説します。

Worksheets("YYYYMMDD").Copy After:=Worksheets(1)

こちらでは、「YYYYMMDD」のシートをコピーし、２枚目の位置に貼り付けています。
「Worksheets(1)」が先頭から１番目のシートを指しているため、「Copy」というコピーする機能の設定（引数）の「After」に「Worksheets(1)」を指定することで１番目のシートの後ろと指定しています。

次は、以下の内容について解説します。

With ActiveSheet
    .Name = Format(Date, "yyyymmdd")
    .Range("B2").Value = Date
End With

まずは、「With」について解説します。
「With」を用いることにより、「With」から「End With」の間に関して、「With」の後ろに指定した要素「ActiveSheet」を省略して記述することができるようになります。
省略して記述する場合は、「.」から記述します。

つまり、以下のプログラムと同じ意味になります。

ActiveSheet.Name = Format(Date, "yyyymmdd")
ActiveSheet.Range("B2").Value = Date

同じ要素（こちらでは「ActiveSheet」）に対して操作する場合は、「With」で囲むことによって、処理の塊が分かりやすくなります。

では次に、以下のプログラムについて解説します。

ActiveSheet.Name = Format(Date, "yyyymmdd")

「ActiveSheet」とは現在選択されているシートを指します。
シートをコピーして複製すると、その複製されたシートが自動で選択されるため、「ActiveSheet」は新たに追加したシートのことを指しています。
そのシートの名前「.Name」を「Format(Date, “yyyymmdd”)」で、マクロを実行した日付にしています。
「Date」で、マクロを実行した日付の情報を取得することができるのですが、シート名にはYYYYMMDD形式で設定したいため、Format関数を活用して、形式を変換しています。

では最後に、以下のプログラムについて解説します。

ActiveSheet.Range("B2").Value = Date

こちらでも先ほど同様に「ActiveSheet」を指定しています。
そのシートのセルB2「Range(“B2”)」の値「.Value」に、マクロを実行した日付「Date」を入力しています。

このようにして開発することができました。
開発したプログラムは、対象のプログラム名を指定し、実行ボタンを押すことで実行することができます。

実行することにより、以下のようにシートが作成され、セルB2に日付が入力されます。

注意点として、Excel上には同じシート名のシートを作成することができないため、同日に複数回実行すると、以下のようなエラーが表示され、処理が中断されます。

再度実行する際は、作成したシートを削除してから行う必要があります。 必要に応じて、シート名が重複した場合には「YYYYMMDD_2」のような番号を加えるようにプログラミングするのも良いかと思います。
その方法については、こちらでは省略します。

最後に、ショートカットの設定方法です。
毎回、VBAの編集画面（VBE）を開いて実行するのは手間になるため、ショートカット活用するのがオススメになります。
まずは、[開発]タブの中の[マクロ]を選択し、以下の画面を表示します。

こちらの画面より、開発したプログラム名を選択し、[オプション]を選択します。

以下の画面の[ショートカットキー]のテキストボックスを選択し、Shiftキーを押しながらAキーを押すことで、「Ctrl+Shift+A」というショートカットを設定することができます。

設定後は、[OK]を選択して確定し、設定画面を全て閉じることで、CtrlキーとShiftキーを押しながらAキーを押すして、開発したマクロを実行することができるようになります。

こちらで開発したファイルは、以下から取得できます。

今回の内容は、VBAを用いて繰り返し作業を自動化する一例になります。
ぜひ、日々の繰り返し作業の自動化に、VBAを活用してみてください。
※理解していないプログラムの実行は、情報漏洩やデータの紛失などの大きな問題につながる可能性があるため、理解した上で実行してください。

セルに入力されている文字から特定の文字の数を数式で求める方法について解説していきます。
こちらでは、以下の黄色のセルから「A」と「B」のそれぞれの数を数式で求めていきます。

・求め方

まずは、求める方法について考えていきます。
Excelには、セル内の特定の文字を数えるという機能は備わっていません。
そのため、数える仕組みを自身で作成する必要があります。
こちらでは、１つの方法について解説していきます。

こちらで紹介する方法は、「セル内の数えたい対象の文字を削除した文字列の文字数」と「元々の文字列の文字数」の差分を計算することで、対象の文字の数を求めます。

・数式の作成

では実際に、先ほどの仕組みを数式で求めていきます。
数式で求めるには、「特定の文字を消すための関数」と「文字の数を求める関数」を活用する必要があります。

「特定の文字を消すための関数」はExcelには用意されていないため、文字を置換するSUBSTITUTE関数を活用して求めていきます。

SUBSTITUTE関数の使い方は、以下の通りです

=SUBSTITUTE(文字列, 検索文字列, 置換文字列, [置換対象])
// [文字列]の中の[検索文字列]を[置換文字列]に置換する
// 文字列：対象の文字列
// 検索文字列：置換したい文字列
// 置換文字列：置換する文字列
// [置換対象]：検索文字列が複数存在する場合に、先頭から何番目の対象の文字を置換するのかを数値で指定
// ※省略時は全ての検索文字列を置換

SUBSTITUTE関数を用いて特定の文字を消すためには、以下のように活用します。

=SUBSTITUTE(B2,"A","")

「文字の数を求める関数」は、LEN関数になります。

LEN関数の使い方は、以下の通りです。

=LEN(文字列)
// 指定した[文字列]の文字の数を返す
// 文字列：対象の文字列

以下のように活用します。

=LEN(B2)

SUBSTITUTE関数とLEN関数を用いて、特定の文字の数を求めていきます。

「セル内の数えたい対象の文字を削除した文字列の文字数」は、以下のように求めることができます。

=LEN(SUBSTITUTE(B2,"A",""))
// 「A」の文字を削除した場合の文字数

そのため、以下のように、元々の文字数から上記の文字数を引くことで、特定の文字の数を求めることができます。

=LEN(B2)-LEN(SUBSTITUTE(B2,"A",""))

上記の内容は「A」の文字数になります。
「B」の文字数に関しても、以下のように求めることができます。

=LEN(B2)-LEN(SUBSTITUTE(B2,"B",""))

今回のように、関数を組み合わせることによって、好みの値を求めることができます。
特定の文字の数ではなく、他の値を求める場合などで、関数を組み合わせても求めることができない場合、もしくは、かなり複雑な数式になってしまう場合は、VBAを活用するのが良いかと思います。
（VBAとは、「Visual Basic for Applications」の略称で、ExcelやWordなどといったMicrosoft Officeのアプリケーションの機能を拡張することができるプログラミング言語になります。）

「文字を入力するだけで、その文字を含む名前のデータのみを瞬時に抽出することができる検索機能」を数式のみで実現する方法について解説していきます。
以下の画像では、セルH3に入力した文字を含む名前のデータを左の表から右の表に抽出しています。

こちらでは、完全一致検索と部分一致検索の２通りについて順番に解説していきます。
※使用したファイルは、記事の最後にて配布しています。

・完全一致検索の実現

特定の条件を満たすデータのみを抽出するには、FILTER関数が便利です。
FILTER関数は、365もしくはExcel2021以降で対応している関数になります。
FILTER関数の使い方は以下の通りです。

=FILTER(配列, 含む, [空の場合])
// 条件を満たすデータのみを抽出する
// 配列：対象の表
// 含む：抽出条件
// [空の場合]：対象が1件もない時に表示する値（省略時はエラーを表示）

この関数を使用して、以下のように記述することで実現することができます。

=FILTER(B3:E1000,C3:C1000=H3,"")
// B3:E1000：表の範囲はデータの増減に対応するため大きめに指定する
// C3:C1000=H3：検索対象の項目の行数は表の範囲と同じにする必要がある

この１つの数式だけで、以下のように対象のデータを抽出することができます。

こちらの場合は、対象が１件のみのため、１件のみが抽出されました。

・部分一致検索の実現

部分一致検索を実現するには、先ほどの以下の数式の「C3:C1000=H3」という条件式について理解する必要があります。

=FILTER(B3:E1000,C3:C1000=H3,"")

条件式について理解するために、まずは「C3:C1000=H3」という数式のみを入力してみます。

= C3:C1000=H3

入力して確定すると、以下のようにTRUEもしくはFALSEという値が表示されます。

TRUEもしくはFALSEという値は、指定した表のデータの数分表示されます。
このTRUEにあたるデータが条件を満たしていて抽出する対象のデータになります。

そのため、部分一致の検索を実現するには、検索欄「セルH3」の値が含むデータのみにTRUEと表示するような条件式を考える必要があります。

そのような条件式を実現するには、FIND関数とIFERROR関数を活用します。
FIND関数の使い方は、以下の通りです。

=FIND(検索文字列, 対象, [開始位置])
// [対象]に[検索文字列]が含まれる場合に、その文字列の先頭からの位置情報を返す
// 検索文字列：検索する文字列
// 対象：検索する対象の文字列
// ※[開始位置]に関しては、今回は使用しないため省略する

実際に活用した例は、以下になります。

=FIND(H3,C4)

入力して確定すると、以下のように見つかった位置の情報を返します。

ちなみに、検索文字列が見つからない場合は、エラーになります。

=FIND(H3,C3)

条件式では、0以外の数値がTRUE、0がFALSEとして扱われるため、エラーの値に関してIFERROR関数を用いて、0と表示していきます。
IFERROR関数の使い方は、以下の通りです。

=IFERROR(値, エラーの場合の値)
// 指定した[値]がエラーの場合に、[エラーの場合の値]を返す
// エラーでない場合は、指定した[値]をそのまま返す
// 値：エラーを検証する対象の値
// エラーの場合の値：エラーの場合に返す値

先ほどの数式にIFERROR関数を組み合わせると、以下のようになります。

=IFERROR(FIND(H3,C3),0)

入力して確定することで、以下のようにエラーの場合に0とすることができます。

後は、必要な行数分表示させる必要があるため、FIND関数の引数[対象]に複数の範囲を指定します。

=IFERROR(FIND(H3,C3:C1000),0) 

これだけで、必要なデータの数分のTRUEもしくはFALSEを意味する値を表示することができるため、こちらの条件式をFILTER関数に設定します。

=FILTER(B3:E1000,IFERROR(FIND(H3,C3:C1000),0),"")

入力して確定することで、部分一致の検索機能を実現することができます。

FILTER関数を用いて検索機能を実現することによって、元の表を触らずに対象のデータの抽出ができます。
検索した内容を残したい場合は、同じような検索機能をコピーして作成すると良いです。
コピーして作成した場合に関しても、元のマスタとなる表は１つのため、最新の表が分からなくなるというような事態を防ぐことができます。

こちらで作成した検索機能のExcelファイルは、以下になります。