作者別: it-yobi

以下のような更新履歴表に関して、「更新」ボタンを押して更新内容を入力するだけで、表の最終行に「更新日、更新者（パソコンにログオン（ログイン）したユーザー名）、更新内容（インプットボックスに入力した値）」が自動で入力される仕組みを開発していきます。

VBAを活用することで、簡単にパソコンに記録されている情報（パソコンにログオンしたユーザー名など）を取得することができます。
今回は、更新者としてパソコンにログオンしたユーザー名を取得していますが、他の値を取得する方法についても、確認用のマクロを配布して解説しています。

機能を開発するために、まずは更新履歴表を用意します。
今回こちらでは、以下のような表を用意しています。

５行目以降に関しては、罫線のみで値を入力していない状態にしておきます。
※用意した表のレイアウト（項目の位置など）によっては、開発するプログラムの内容が異なります。

次に、[開発]タブの中の[マクロ]を選択します。

※[開発]タブが表示されていない場合は、以下の手順をご確認ください。

＞マクロを開発する準備

以下の画面が表示されましたら、開発する機能の名前（こちらでは「更新」）を入力し、[作成]を選択します。

以下の画面が表示されましたら、「Sub 更新()～End Sub」の間にプログラムを入力していきます。
※環境によっては、「Option Explicit」が表示されていないことがありますが、今回はなくても問題ございません。

では次に、以下のようにプログラムを入力します。
※こちらを実装したExcelファイルは、記事の最後にて配布しています。

Sub内はTabキーにてインデント（字下げ）しています。
インデントの有無では処理に影響はないのですが、処理の見やすさの観点からインデントする癖をつけておいた方が良いです。

プログラムの内容について簡単に確認していきます。

まずは、以下の内容です。

「Dim」と書かれているのは、一時的に値を格納する部屋（変数）を用意する宣言（変数宣言）になります。
「As」の後に部屋の種類を記述しています。（「String」は文字専用の型）

次に、用意した「detail」という変数にインプットボックス「InputBox」に入力した値を格納します。

「InputBox」の括弧の中の文字は、インプットボックスに表示される文字になります。
イメージは以下になります。

次に以下の内容です。

「Dim」は先ほど同様に変数宣言になります。（「Long」は数値専用の型）

「Rows.Count」はシートに存在している最大の行番号になるため、「Cells(Rows.Count,”B”)」でシート上のB列の一番下のセルを指定しています。

このセルから、Ctrlキーを押しながら上に移動「.End(xlUp)」し、止まった位置の行番号「.Row」に１を加えた数を取得しています。
→ B列の下から上に向かっていく中で、次に文字が入力されているセルの一つ下の行番号（5）を取得

次に以下の内容です。

先ほど取得した行番号のB列のセルに実行した時の日付「Date」を入力し、C列のセルにパソコンにログオンしたユーザー名を入力、D列のセルにインプットボックスに入力した値を入力しています。 ユーザー名はEnviron関数を用いて取得しています。
※Environ関数で取得できる値については、最後に確認用マクロを配布して解説しています。

最後に以下の内容にて、Excelファイルを保存しています。

これで機能としては完成しているため、後はボタンを作成し、そのボタンに開発した機能を割り当てれば完成です。

ボタンは、[開発]タブの中の[挿入]から作成すると良いです。

上記のボタンを選択後、図形の作成時と同様にボタンを作成すると、[マクロの登録]画面が表示されます。
そこでは、開発したマクロの名前を選択することで、マクロを割り当てたボタンを用意することができます。
ボタンの表示名などは必要に応じて変更してください。
※作成したボタンを編集する場合は、Ctrlキーを押しながら選択します。

このようにして、ボタンを押してインプットボックスに更新内容を入力するだけで、表の最終行に値が入力されます。

・Environ関数について

最後に、Environ関数で取得できる値について、確認用のファイルを配布して解説していきます。

Environ関数で取得できる値は以下になります。

Environ関数の引数に上記の左側にある環境変数名を設定することで、その値を取得することができます。
先ほどのプログラムで使用した値は「USERNAME」になります。

どのような値が取得できるのかは、実際に試された方がイメージしやすいかと思いますので、各々で確認できるファイルを配布します。
配布ファイルのマクロの実行を許可し、「出力」ボタンを押すだけで、値を確認することができます。

今回開発した「更新履歴を自動で入力する機能のExcelファイル」と「Environ関数の出力確認用のExcelファイル」は以下になります。

ぜひ試してみてください。

今回は、データの増減に対応させた罫線を設定する方法について解説していきます。
また、以下のようにカテゴリーなどの特定の項目の値を元に、罫線の種類を変更する方法についても解説していきます。

①表の増減に対応させた罫線を設定

まずは、以下の表に対し、データの増減に対応させた罫線を設定します。
自動で表を拡張する機能として「テーブル」がありますが、こちらではテーブルを使わずに設定していきます。

特定の条件を満たしている時に、罫線などの書式を変更するには、「条件付き書式」を活用します。
今回の場合、以下の条件を元に罫線を設定していきたいと思います。

「Noの項目に値が入力されている場合」

まずは、設定する対象のセルを選択する必要があります。
今回の場合は、セルA3からC列の一番下までのセルに設定していきます。

その場合、AからC列を全選択し、Ctrlキーを押しながら不要なセルを選択して、選択を解除することで、以下のように対象のセルのみを選択します。

選択できましたら、[ホーム]タブの中の[条件付き書式]の[新しいルール]を選択します。

以下の画面にて、[ルールの種類]を[数式を使用して、…]にし、テキストボックス内に以下の数式を入力します。

=$A3<>""

テキストボックス内に設定する数式は、現在の選択基準のセル（A3）に対しての数式である必要があります。
選択基準のセルに罫線を設定する時は、選択基準のセルに値が何かしら入力されている場合になるため、こちらでは、「選択基準のセルが空白でない場合（=$A3<>””）」と入力しています。

他の選択範囲に関しては、入力した数式がコピーされて設定されます。
そのため、相対参照や絶対参照を意識した数式を入力する必要があります。
こちらの場合は、値の有無を確認するのは必ずA列になるため、「$A3」とA列のみに「$」を加え固定しています。

数式の入力ができましたら、[書式]より罫線の設定を行います。

こちらで設定した内容は、「点線の下の罫線」になります。
※罫線の設定画面の予め表示されている太い薄い線は、未設定という意味になります。

この設定で確定することによって、以下のようにデータの増減に対応させて罫線を設定することができました。

②特定の項目の値を元に罫線を設定

次は、特定の項目の値を元に罫線を設定する方法について解説していきます。
こちらでは、カテゴリーの項目の値を元に、以下のように罫線を設定し、同じカテゴリーのデータを目立たせていきます。

このような設定を行う場合に関しても、「条件付き書式」を活用することで実現できます。

まずは、条件を考える必要があります。
今回の場合は、以下のような条件になります。

「Noの項目に値が入力されている場合、尚且つ、カテゴリーの項目の値が１つ下の値と異なる場合」

まずは、設定する対象のセルを選択する必要があります。
先ほどの同じ範囲を選択し、[ホーム]タブの中の[条件付き書式]の[新しいルール]を選択します。

現在の選択基準のセルは、先ほどと同様にセルA3になるため、セルA3に対する条件を数式で表現していきます。

「Noの項目に値が入力されている場合、尚且つ、カテゴリーの項目の値が１つ下の値と異なる場合」という条件を数式で表現すると、以下のようになります。

=AND($A3<>"",$B3<>$B4)

こちらでは、AND関数で「Noの項目に値が入力されている場合（$A3<>””）」と「カテゴリーの項目の値が１つ下の値と異なる場合（$B3<>$B4）」を設定しています。

この条件式を、[数式を使用して、…]のテキストボックス内に設定し、[書式]より罫線を設定します。

これで確定することにより、以下のようにカテゴリー単位でも罫線を設定することができました。

[補足]

複数の条件付き書式を設定しているセルで、複数の条件を満たしている場合、基本は最後に設定した条件付き書式が優先されて反映されます。
優先順位を変更したい場合は、[ホーム]タブの中の[条件付き書式]の[ルールの管理]より行えます。

FILTER関数は、以下のように指定した表のデータから指定した条件に合ったデータのみを抽出する時に活用する関数になります。

実は、必要な項目のみを抽出する際にも、FILTER関数を活用することができます。
今回は、指定した項目をFILTER関数で抽出する方法について解説していきます。

抽出する方法はいくつかあるため、順番に解説していきます。

FILTER関数の使い方は次の通りです。

=FILTER(配列, 含む, [空の場合])
// 配列：絞り込み対象の表の範囲など
// 含む：絞り込み条件
// [空の場合]：条件を満たす対象のデータが見つからない時に表示する内容（省略時はエラーが表示される）

この含むという条件ですが、通常は以下のように指定します。

=FILTER(B3:F12,E3:E12="男")
// 性別の項目が「男」の場合

ここで設定されている条件式は「E3:E12=”男”」になります。
この条件式は、「E3:E12」の１列に対しての式になります。

実は、ここで指定する式を行方向にすることで、行に対しての絞り込みができます。
行に対しての絞り込みを行うことによって、必要な項目のみを抽出することができます。

まずは簡単な方法から確認していきます。
必要な項目のみを抽出する場合、その対象の項目名の上に「・」などの任意の文字を入力します。

次に、その項目名の上の文字を条件式に加えます。
例えば、上記の画像のように「・」という文字を入力している場合は、抽出する条件式は「B1:F1=”・”」になります。

実際に、その条件式を用いて数式を作成していきます。

=FILTER(B3:F12,B1:F1="・")

このように必要な項目のみを抽出することができました。

抽出した項目に対して、更にデータの絞り込みを行いたい場合は、このFILTER関数をFILTER関数の配列の引数に設定するという方法で実現します。

=FILTER(FILTER(B3:F12,B1:F1="・"),E3:E12="男")

ただ、この方法ですと、項目名の上に「・」などといった文字を入力する必要があります。
そのため、次に「・」などといった文字を入力せずに項目を抽出する方法について解説していきます。

そのためには、「B1:F1=”・”」というような条件式が何を表しているのかを理解する必要があります。

試しに、「B1:F1=”・”」という条件式を単体で入力してみます。

= B1:F1="・"

入力すると、一行のTRUEとFALSEで構成された値が表示されるかと思います。

つまり、FILTER関数の条件式に設定する値とは、「抽出したい位置がTRUEになり、それ以外がFALSEとなるような式」であれば良いということが分かります。

直接、TRUEとFALSEで構成された数式を入力するには、カンマ区切りの値を「{」と「}」で囲みます。

={TRUE,FALSE,FALSE,TRUE,TRUE}

このように直接入力することができます。

また、TRUEは0以外の数値、FALSEは0としても表現することができます。
実際に、1と0に置き換えてみると以下のようになります。

={1,0,0,1,1}

この式をFILTER関数に設定することで、項目名の上に「・」などといった文字を入力しなくても、好みの項目のみを抽出することができます。

=FILTER(B3:F12,{1,0,0,1,1})

このように好みの項目のみを抽出することができました。

ただ、「{1,0,0,1,1}」などといった値を入力するのは、若干の手間になります。
1と0を入力している時に、誤って１つずつずれてしまう可能性もあります。

そのため、以下のようにIF関数やSWITCH関数などを用いて表現するのも良いかと思います。

IF関数の場合は、以下のようになります。

=IF((B2:F2=B2)+(B2:F2=E2)+(B2:F2=F2),1,0)
// 抽出したい項目を「(B2:F2=B2)」のように「＋」で並べる
// 「+」はORという意味なる

=FILTER(B3:F12,IF((B2:F2=B2)+(B2:F2=E2)+(B2:F2=F2),1,0))

ちなみに、以下のようにIF関数を使わなくても表現することができます。

=FILTER(B3:F12,(B2:F2=B2)+(B2:F2=E2)+(B2:F2=F2))

SWITCH関数の場合は、以下のようになります。

=SWITCH(B2:F2,B2,1,E2,1,F2,1,0)
// 抽出したい項目を「B2,1」のように必要な分を並べる
// 「B2」などと指定された項目名の場合は1、それ以外は0が返される

=FILTER(B3:F12,SWITCH(B2:F2,B2,1,E2,1,F2,1,0))

これらは例になるため、他の方法を考えてみるのもよいかと思います。