小技集

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ファイルは、以下になります。

以下の表の黄色のセルには数式が入力されています。
数式の内容は、「60点以上なら合格、未満なら不合格、空白なら空白」というものです。

この表に関して、不要になった行の値を削除する際に、数式のセルを含め行全体を選択して削除しても、数式だけは消えずに残ります。
そのため、何度でも再利用することができます。

このような行ごと削除できる数式の作成方法について解説していきます。

この数式を実現するには、「スピル」という機能が必要になります。
この機能はExcel2021以降かMicrosoft365で対応しております。

スピルという機能は、数式を入力したセルから溢れ出して数式の結果を表示させるというものになります。

まずは、下記の表のセルD3に「60点以上なら合格、未満なら不合格、空白なら空白」となるような数式をIFS関数で入力していきます。

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

=IFS(論理式1, 値が真の場合1, [論理式2, 値が真の場合2, …])
// 論理式1：結果がTRUEもしくはFALSEになる式
// 値が真の場合1：論理式1がTRUEの場合に表示する値
// 論理式2以上は必要に応じて指定することができます。
// 複数の論理式がTRUEになる場合は、一番先頭の「値が真の場合」に指定した値が表示されます。

IFS関数を使用して表現したものが以下になります。
※IFS関数で「その他」を指定するには、最後の論理式にTRUEと指定します。

=IFS(C3="","",C3>=60,"合格",TRUE,"不合格")

このように表現することができましたら、数式の参照する範囲を複数行にします。
今回の場合は、セルC3の点数の参照のみならず、セルC3からC7までを指定します。

そのように指定するだけで、以下のようにセルから溢れ出て結果が表示されます。
※スピルが対応していない場合は、以下のように表示されません。

=IFS(C3:C7="","",C3:C7>=60,"合格",TRUE,"不合格")

この数式は、セルD3のみに入力がされていて、他のセルD4からD7には入力されていません。
他のセルD4からD7に関しては、値を削除しようとしても削除することができません。
そのため、４行目以降に関しては、行ごと削除しても、数式のみが残るようになります。

ただ、このままですと、３行目を削除してしまった際に、数式がすべて消えてしまいます。
そのため、次に、３行目に関しても行ごと削除できるようにしていきます。

セルD3に数式を入力している場合は、このようなことが実現できないため、まずは数式を入力する位置から変更する必要があります。

そのため、セルD3の数式を削除し、セルD2の項目名の位置に数式を入力していきます。
項目名の位置に先ほどと同じ数式を入力し、１行上になるため、行番号を変更します。

=IFS(C2:C7="","",C2:C7>=60,"合格",TRUE,"不合格")

このようにすると、項目名の位置に「合格」と表示されてしまいますが、３行目以降に関しては、行ごと削除することができるようになります。

次に、項目名が「合格」ではなく、「評価」となるように数式を修正していきます。
そのような数式を実現するには、ROW関数が活用できます。
ROW関数の使い方は以下になります。

=ROW([参照])
// 参照：指定したセルの行番号を返す（省略時は数式を入力したセルが指定される）

まずは、ROW関数を用いて、項目名に「評価」と表示し、それ以外のセルには「-」と表示する数式を作成していきます。

=IF(ROW(C2:C7)=2,"評価","-")

こちらでは、ROW関数での取得結果が２（２行目）の場合に「評価」、それ以外の場合に「-」を表示させています。

次に、先ほどの数式の「-」という表示を、IFS関数の内容に置き換えます。

=IF(ROW(C2:C7)=2,"評価",IFS(C2:C7="","",C2:C7>=60,"合格",TRUE,"不合格"))

このようにすることで、項目の位置に項目名を表示し、各行に関しては、数式の判定結果を表示することができました。
この場合、数式はセルD2にしか入力されていないため、３行目以降に関しては、行ごと削除しても数式が消えることはありません。

数式が消されないように「シートの保護」を活用することがありますが、部分的に保護すると、行ごとの削除ができなかったりと不便もあります。
そのため、スピルが使える環境の場合は、こちらのような数式で表現してみるのも良いかと思います。