作者別: it-yobi

１人１つの役割で、役割ごとに必要な人数が決まっている場合、以下の表のようだと、どこに何人割り当てたのかが分かりづらく、誤った割り当てにも気づきにくいです。

そこで今回は、１つ１つ割り当てると同時に、重複入力や人数超過の可能性のある項目が自動で色付けされる仕組みの作り方について紹介します。

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

1. 実現方法

値を入力すると同時に、ある条件を満たしたセルを色付けするには『条件付き書式』を活用します。
単純な条件でない場合は「色付けする条件」を数式で表現して、条件付き書式に設定する必要があります。

2. 条件式の用意

まずは、特定のセルについて、色付けする条件を考えていきます。

例えば、セルC4の場合、

このセルを色付けする必要があるときは、以下の複数の条件を満たしたときになります。

・セルC4は空 ・C列（セルC4～C13）に入力されている値の数がセルC3の必要人数を満たしている、もしくは、４行目（セルC4～G4）のいずれかに値がすでに入力されている

この複数の条件を数式で表現すると、以下のようになります。

=AND(C4="",OR(COUNTA(C4:C13)>=C3,COUNTA(C4:G4)>=1))

他のセルについても考えると、以下のようになります。

【セルC5】

=AND(C5="",OR(COUNTA(C4:C13)>=C3,COUNTA(C5:G5)>=1))

【セルC6】

=AND(C6="",OR(COUNTA(C4:C13)>=C3,COUNTA(C6:G6)>=1))

【セルD4】

=AND(D4="",OR(COUNTA(D4:D13)>=D3,COUNTA(C4:G4)>=1))

【セルD5】

=AND(D5="",OR(COUNTA(D4:D13)>=D3,COUNTA(C5:G5)>=1))

ここから分かることは、必要人数を確認する範囲の行番号、各行の値の有無を確認する範囲の列名は、以下のように「$」で固定する必要があるということです。

=AND(C4="",OR(COUNTA(C$4:C$13)>=C$3,COUNTA($C4: $G4)>=1))

3. 条件付き書式の設定

作成した以下の数式は、セルC4を基準にした条件式です。

=AND(C4="",OR(COUNTA(C$4:C$13)>=C$3,COUNTA($C4: $G4)>=1))

そのため、セルC4を基準にした色付けする可能性のある範囲（入力欄）を選択します。

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

条件付き書式の設定画面にて、[ルールの種類]を[数式を使用して、書式設定するセルを決定]にし、テキストボックス内に先ほどの条件式を入力します。

次に、[書式]の[塗りつぶし]タブから色付けする色を指定します。

上記のように設定して、確定することで完成です。

4. 完成

以下のように、役割を割り当てると同時に、割り当て済みの範囲が自動で色付けされます。

必要に応じて、ドロップダウンリストなどで値を入力しやすくすると良いです。

▼サンプルファイル▼

以下の営業報告書には、「確認日」という項目とチェックボックスが用意されています。

このチェックボックスをチェックすることで、チェックボックスが「実行時の日付」に置き換わり、シートがロック（保護）され、そのシートの編集ができなくなります。

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

このように、書類の確認と同時にロック（保護）する仕組みの実現方法について紹介していきます。

1. 開発準備

今回は、予め用意したシート内の特定のチェックボックスをチェックすると同時に、そのシートをロック（保護）する仕組みを実現していきます。

チェックボックスは、[挿入]タブの[チェックボックス]を使用しており、チェックボックスが配置されたセルには、「FALSE」という値が入力されています。

チェックボックスをチェックすると、セルの値は「TRUE」に変わります。

このように指定したシートのセルの値が変わると同時に、シートをロック（保護）するなどと何かしらの処理を実行するには、「シートモジュール」の「イベントプロシージャ」を活用します。
シートモジュールは、対象シートのタブ上で右クリックし、[コードの表示]を選択することで表示できます。

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

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

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

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

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

2. コードの記述

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

Private Sub Worksheet_Change(ByVal Target As Range)
    
    With Range("E5")
        If .Value = True Then
           .Value = Date
           Me.Protect
        End If
    End With
    
End Sub

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

    With Range("E5")
        '省略
    End With

「With」と「End With」で囲むことで、この中では「With」に指定した「Range(“E5”)」を省略して記述することができます。
省略する際は「.」から記述します。

        If .Value = True Then
           .Value = Date
           Me.Protect
        End If

次に、「If」でセルE5（「With」で指定したセル）が「True（チェックボックスがチェックされた状態）」かどうかを確認し、「True」の場合は、そのセルの値を実行時の日付（Date）にし、シートを保護（Me.Protect）しています。

3. 完成

以上の内容で実現できます。
対象シートのセルE5に配置されたチェックボックスをチェックすると同時に、チェックボックスは実行時の日付に置き換わり、シートがロック（保護）されます。

運用時は、作成したシートをテンプレートとし、シートを複製して活用するとよいです。
シートを複製すると、複製元シートのシートモジュールの内容も、複製したシートに反映されます。

▼サンプルファイル▼

特定の条件を満たしたときにメッセージを表示する例として、こちらでは以下のように、金曜日の勤怠を登録するときに「終業時刻」を入力したときのみ、メッセージを表示する方法について紹介します。

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

メッセージの表示方法

メッセージを表示するには「データの入力規則」を活用します。
データの入力規則というと、セルへの入力制限を設ける機能というイメージがありますが、実は単純にメッセージを表示したいときにも活用できます。

今回の場合、以下の表の「終業時刻」の項目がメッセージを表示する可能性のある対象になります。

そのため、「終業時刻」の範囲を選択して、[データ]タブから[データの入力規則]を選択します。

表示される以下の設定画面にて、まずは入力制限を行うのではなく、メッセージを表示する設定をします。

特定の条件を満たしたときに、メッセージを表示するには、[エラー メッセージ]タブにて、[スタイル]を[情報]にします。
そして、表示したいメッセージを[タイトル]と[エラー メッセージ]のテキストボックスに入力します。

これだけで、メッセージの設定は完了です。
後は、[設定]タブにて、メッセージを表示する条件を設定するだけです。

条件の設定

今回、メッセージを表示する条件は「金曜日の勤怠を登録したとき」になります。
具体的には、「入力したセルと同じ行のA列の日付が金曜日のとき」です。
それを、以下の[設定]タブにて設定していきます。

今回のような、他のセルの値を参照した条件を指定するには、[入力値の種類]を[ユーザー設定]にし、[数式]に条件を指定します。

[数式]に指定する条件は、選択基準のセルに対しての条件になります。
上記の場合は、セルC2が選択基準なので、「A2に入力されている日付が金曜日の場合」という条件になります。

ただ注意しないといけないのは、[数式]に設定した条件は、正しくは「メッセージを表示しないとき」の条件になります。
そのため、設定する条件は、「A2に入力されている日付が金曜日の場合」ではなく「A2に入力されている日付が金曜日でない場合」になります。
この条件を数式で表現すると、以下のようになります。

=WEEKDAY(A2)<>6
// WEEKDAY(A2)：指定した日付の曜日を1（日）～7（土）の数値で返す
// <>6：上記の結果が6でないとき＝金曜日でないとき

この条件式を[数式]に入力します。

他の選択範囲には、相対参照で反映されます。
セルC3には「A3に入力されている日付が金曜日でない場合」といった感じです。

これで確定することで完成です。

完成

A列の「日付」の項目に入力した日付が「金曜日以外」の場合は、C列の「終業時刻」の項目に値を入力しても、メッセージは表示されません。

A列の「日付」の項目に入力した日付が「金曜日」の場合は、C列の「終業時刻」の項目に値を入力して確定すると同時に、以下のようにメッセージが表示されます。

メッセージの[OK]を選択すると、メッセージが消えて入力が確定します。

▼サンプルファイル▼

以下は、タスク管理表です。

完了したタスクを選択して、「完了」ボタンを押すだけで、そのタスクが「完了」シートに移動します。

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

このような、ボタン１つで完了タスクを別シートに移動する仕組みの実現方法について紹介します。

1. 開発準備

まずは、「未完了」シートと「完了」シートを用意し、「完了」シートには空の表を用意します。

次に、[開発]タブから[マクロ]を選択し、表示された設定画面にて、開発する機能の名前を入力して、[作成]を選択します。
こちらでは、「MoveTask」と入力しています。

次に表示される以下の画面の「Sub ○○」から「End Sub」の間にコードを記述します。
「Option Explicit」は、VBEの設定内容次第では表示されません。「Option Explicit」についての解説はこちらでは省略します。

2. コードの記述

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

Sub MoveTask()
    
    Dim w1 As Worksheet
    Dim w2 As Worksheet
    Set w1 = Worksheets("未完了")
    Set w2 = Worksheets("完了")
    
    Dim r1 As Long
    Dim r2 As Long
    r1 = ActiveCell.Row
    r2 = w2.Cells(w2.Rows.Count, "A").End(xlUp).Row + 1
    
    w2.Range(w2.Cells(r2, "A"), w2.Cells(r2, "C")).Value = _
        w1.Range(w1.Cells(r1, "A"), w1.Cells(r1, "C")).Value
    w1.Rows(r1).Delete
    
End Sub

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

    Dim w1 As Worksheet
    Dim w2 As Worksheet
    Set w1 = Worksheets("未完了")
    Set w2 = Worksheets("完了")

シート情報を割り当てる用の変数（w1とw2）を用意し、それぞれに「未完了」シートと
「完了」シートを割り当てています。

    Dim r1 As Long
    Dim r2 As Long
    r1 = ActiveCell.Row
    r2 = w2.Cells(w2.Rows.Count, "A").End(xlUp).Row + 1

数値を格納する用の変数（r1とr2）を用意し、「r1」にアクティブセル（現在選択している基準のセル）の行番号、「r2」に「完了」シートの表の末尾の行番号を格納しています。

「r2」について詳しく解説すると、「完了」シートのA列の末尾を選択し、Ctrlキーを押しながらカーソルキーの「↑」を押して止まった位置のセルの行番号に１を加えた数値を格納しています。

    w2.Range(w2.Cells(r2, "A"), w2.Cells(r2, "C")).Value = _
        w1.Range(w1.Cells(r1, "A"), w1.Cells(r1, "C")).Value
    w1.Rows(r1).Delete

「完了」シートの表の末尾のA列からC列に、「未完了」シートのアクティブセルの行のA列からC列の値を格納し、「未完了」シートのアクティブセルの行を削除しています。

3. ボタンの作成

コードが完成したら、実行用のボタンを用意します。

ボタンは、[開発]タブの中の[挿入]から作成することができます。
ボタンには開発した機能「MoveTask」を割り当てます。

4. 完成

以上の手順で完成です。
完了したタスク（同じ行のいずれかのセル）を選択し、「完了」ボタンを押すことで、その行のタスクが「完了」シートに移動します。

注意点として、今回のコードには、移動対象のタスクの行の範囲を指定していません。
そのため、１行目を選択して「完了」ボタンを押してしまうと、見出しが移動してしまいます。

そのため、必要に応じて、コード内で「行番号が２以上のとき」などといった条件分岐を行うとよいです。

▼サンプルファイル▼

以下のように、メールなどで送られてきた文章（B2:D16）から特定の値を表形式で抽出（F3:G7）する方法について紹介していきます。

簡易的な文章から表形式で抽出

元々の文章が以下の内容の場合、抽出対象（商品名：数量）以外にも不要な文章が含まれています。（赤文字）

〇〇様 お世話になっております。 今月の弊社商品在庫状況を以下の通りご報告いたします。 サウンドビー：5 タイムウォッチ：8 タブレットプロ：15 ビートスタジオ：6 ペンマスター：18 ご確認のほど、よろしくお願いいたします。 何かご不明点や追加のご要望がございましたら、お気軽にお知らせください。

そのため、まずは抽出対象の文章のみをシートに貼り付けて、そこから表形式になるように数式で抽出していきます。

文章を表形式で抽出する場合、抽出するルールを決める必要があります。
今回の場合は、以下のようなルールになります。

・「：」で列を区切る ・「（改行）」で行を区切る

このルール通りに抽出するためには、TEXTSPLIT関数を活用します。
実際に活用した数式は、以下になります。

=TEXTSPLIT(B2,"：",CHAR(10))
// B2：抽出元の文章が入力されたセル
// "："：列の区切り文字
// CHAR(10)：行の区切り文字（改行はCHAR関数を用いて表現）

上記の数式を抽出先の表の先頭セル（E3）に入力することで、以下のように表形式で抽出することができます。

とはいえ、メールなどの文書から特定の文章を毎回抽出するのは面倒です。
そのため、文章を全て貼り付けるだけで、対象の値を表形式で抽出する方法についても紹介していきます。

本文全体から対象のみを表形式で抽出

本文全体が以下のようにパターン化している場合（赤文字）は、簡単な数式を加えるだけで、対象の範囲の抽出までを自動で行うことができます。

〇〇様 お世話になっております。 今月の弊社商品在庫状況を以下の通りご報告いたします。 サウンドビー：5 タイムウォッチ：8 タブレットプロ：15 ビートスタジオ：6 ペンマスター：18 ご確認のほど、よろしくお願いいたします。 何かご不明点や追加のご要望がございましたら、お気軽にお知らせください。

まずは、この本文のままシートに貼り付けて、先ほどの数式で抽出してみます。

=TEXTSPLIT(B2,"：",CHAR(10))

そうすると、以下の赤枠の箇所が不要になります。

抽出された表の上から５行、下から３行が不要になるので、この不要な行を数式で除外します。

表から不要な行を除外する場合、DROP関数を活用すると良いです。
実際に活用した数式は、以下になります。

=DROP(DROP(TEXTSPLIT(B2,"：",CHAR(10)),5),-3)
// DROP(…,-3)：「…」の末尾から3行を除外
// DROP(…,5)：「…」の先頭から5行を除外

上記の数式を抽出先の表の先頭セル（E3）に入力することで、以下のように不要な行を除外した表形式で抽出することができます。