作者別: it-yobi

以下のように、一部のセルが結合されている表でデータが管理されていることがあります。

このような表では、VLOOKUP関数やXLOOKUP関数などでの値の抽出が難しそうに感じます。
実は、法則性がある場合は、数式を用いて簡単に値を抽出することができます。

今回は、以下の表の「No」で検索して、該当する「イベント」と「担当者」を抽出する方法について解説していきます。
「担当者」に関しては、カンマ区切りで複数セルをまとめて抽出していきます。

該当する「イベント」を抽出

まずは、以下の表を元に、セルF3の「No」から該当する「イベント」を抽出していきます。

結合されているセルは、その結合範囲の左上のセルに値が入力されている扱いになるので、上記の表の場合は、以下の表と同じ扱いになります。

そのため、VLOOKUP関数やXLOOKUP関数で簡単に抽出することができます。
こちらでは、XLOOKUP関数で抽出していきます。
XLOOKUP関数を活用する場合は、以下の数式で「イベント」を抽出することができます。

=XLOOKUP(F3,A:A,B:B,"")
// F3：検索値
// A:A：検索範囲
// B:B：戻り範囲（抽出範囲）
// ""：見つからない場合

該当する「担当者」を抽出

次は、以下の表を元に、セルF3の「No」から該当する「担当者」を抽出していきます。

「担当者」に関しては複数行あります。
先頭の「担当者」は、先ほどと同じ理屈で抽出できますが、それ以降に関しては、抽出することが複雑になります。
そのため、「No」の項目に関しては、セルの結合を解除し、全ての行に「No」を入力していきます。

まずは、「No」の項目の要素全体を選択します。

選択した状態で、[ホーム]タブから[セルを結合して中央揃え]を選択して、一括で結合を解除します。

次に、セルを選択している状態で、[ホーム]タブから[検索と選択]の[条件を選択してジャンプ]を選択します。

次の画面で、「空白セル」を選択して確定します。

これだけで、以下のように「No」の項目の空白セルのみを選択することができます。

後は、この選択されている状態で「=」と入力し、一つ上のセルを選択します。

この状態で、Ctrlキーを押しながらEnterキーを押すことで、「No」の項目全体に「No」を入力することができます。

※必要に応じて見た目の修正や値のみの置き換えなどを行ってください。

後は、FILTER関数を活用して、以下のような数式で「担当者」を抽出することができます。

=FILTER(D:D,A:A=F3,"")
// D:D：配列（抽出範囲）
// A:A=F3：含む（抽出条件）
// ""：空の場合

ただ、このままですと、上記のように「#スピル!」というエラーになってしまうか、複数行に渡って「担当者」が表示されてしまいます。
そのため、以下のように、TEXTJOIN関数を組み合わせて、抽出される「担当者」をカンマ区切りでセル内に表示させます。

=TEXTJOIN(",",TRUE,FILTER(D:D,A:A=F3,""))
// ","：区切り文字
// TRUE：空のセルは無視
// FILTER(D:D,A:A=F3,"")：テキスト

まとめ

一部のセルが結合されている表でも「法則性がある場合」は、少し工夫することで、数式での抽出が可能になります。

複数シートでタスクを管理している場合、他のシートのタスクの進捗状況が確認しづらいですよね。
ということで今回は、シートごとの残タスク数をそれぞれのシート名に自動で表示させる仕組みの実現方法について解説していきます。

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

1. 開発準備

こちらでは、プロジェクトごとのタスク管理表をシート分けしている以下のようなファイルを活用します。
シート名とそれぞれのシートのセルA1にはプロジェクト名を指定しています。

そして、それぞれのシートのいずれかのセルを編集すると同時に、最新の残タスク数をシート名に反映させる仕組みを実装していきます。

そのように、全てのシートに対し、セルを編集すると同時に処理を実行するには、「ブックモジュール」の「イベントプロシージャ」を活用します。
[開発]タブから[Visual Basic]を選択、もしくはAltキーを押しながらF11キーを押して、VBEの画面を表示します。

表示されたVBEの画面のプロジェクトエクスプローラーから[ThisWorkbook]をダブルクリックすることで、ブックモジュールを開くことができます。
※プロジェクトエクスプローラーが表示されていない場合は、Ctrlキーを押しながらRキーを押すことで表示することができます。

次に、セルを編集すると同時に処理を実行する特殊なプロシージャ（イベントプロシージャ）を用意します。
そのためには、ブックモジュールの左上のリストから「Workbook」を選択します。

「Workbook」を選択すると、自動で「Workbook_Open」というプロシージャが表示されます。
このプロシージャは、Excelファイルの立ち上げ時に自動で実行されるイベントプロシージャになります。

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

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

2. コードの記述

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

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    
    If Sh.Range("A2").Value = "ID" Then
        
        Dim taskCnt As Long
        Dim compCnt As Long
        
        taskCnt = _
            WorksheetFunction.CountA(Sh.Range("A3:A10000"))
        compCnt = _
            WorksheetFunction.CountIf(Sh.Range("D3:D10000"), "完了")
            
        If taskCnt = compCnt Then
            Sh.Name = Sh.Range("A1").Value
        Else
            Sh.Name = Sh.Range("A1").Value & _
                "(" & (taskCnt - compCnt) & ")"
        End If
        
    End If
    
End Sub

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

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    
    If Sh.Range("A2").Value = "ID" Then
        
        '省略
        
    End If
    
End Sub

「Workbook_SheetChange」というプロシージャの引数の「Sh」に編集されたシートの情報、「Target」に編集されたセルの情報が渡されます。
そのため、「Sh」を活用して、編集されたシートにタスク管理表が含まれているかどうかを確認しています。
具体的には、編集されたシートのセルA2に「ID」という文字が入力されている場合にタスク管理表が含まれたシートと判断しています。

タスク管理表が含まれたシートと判断された場合に、Ifの中の処理を実行します。

        Dim taskCnt As Long
        Dim compCnt As Long
        
        taskCnt = _
            WorksheetFunction.CountA(Sh.Range("A3:A10000"))
        compCnt = _
            WorksheetFunction.CountIf(Sh.Range("D3:D10000"), "完了")

全体のタスクの件数を格納する用の変数「taskCnt」と、完了したタスクの件数を格納する用の変数「compCnt」を用意し、それぞれの件数を格納しています。

全体のタスクの件数（taskCnt）に関しては、セルA3からA10000の中で値が入力されているセルの数を格納し、完了したタスクの件数（compCnt）に関しては、セルD3からD10000の中で「完了」という値が入力されているセルの数を格納しています。

        If taskCnt = compCnt Then
            Sh.Name = Sh.Range("A1").Value
        Else
            Sh.Name = Sh.Range("A1").Value & _
                "(" & (taskCnt - compCnt) & ")"
        End If

先ほど取得した全体のタスクの件数（taskCnt）と完了したタスクの件数（compCnt）が一致している場合は、未完了のタスクが存在しないとして、シート名をセルA1のプロジェクト名にしています。

一致しない場合は、未完了のタスクが存在するとして、シート名をセルA1のプロジェクト名に「(残タスク数)」という文字列を加えた名前にしています。
残タスク数は、全体のタスクの件数（taskCnt）から完了したタスクの件数（compCnt）を引くことで求めています。

3. 完成

以上の内容で実現できます。
タスク管理表が含まれるシートのいずれかのセルを編集すると同時に、そのシートのシート名に残タスク数が反映されます。
また、残タスク数が０件の場合は、残タスク数は反映されません。

▼サンプルファイル▼

以下の表の「性別」と「生年月日」の項目のように、通常は「***」が表示されているが、セルを選択した時のみ、そのセルの値が表示される仕組みの実現方法について解説していきます。

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

1. 条件付き書式の活用

このような仕組みを実現するには、条件付き書式を活用します。
条件付き書式とは、指定した条件を満たしている時に、指定したセルの書式を変更するという機能になります。
今回の場合は、以下の表の「性別」と「生年月日」の項目に対して、通常は「***」を表示させ、選択時のみセルの値を表示させます。

セルの値を変えずに見た目のみを「***」にするには、表示形式を活用します。
表示形式で文字列や数値を含むセルの値を「***」にする設定は、以下になります。

"***";"***";"***";"***"

今回は、特定の条件を満たした時に、先ほどの表示形式に切り替えることによって、表示を「***」にしていきます。
その条件は以下のようになります。

・「対象のセルの行番号が、現在選択しているセルの行番号と一致しない場合」もしくは「対象のセルの列番号が、現在選択しているセルの列番号と一致しない場合」

では、この条件で条件付き書式の設定をしていきます。

---

設定手順

---

１．条件付き書式を設定する対象のセルを選択（こちらでは、「性別」と「生年月日」のセル全体を選択します）

２．[ホーム]タブの中の[条件付き書式]から[新しいルール]を選択

３．表示された画面にて、[ルールの種類]を[数式を使用して、書式設定するセルを決定]にし、以下の条件式を入力

=OR(ROW(C2)<>CELL("row"),COLUMN(C2)<>CELL("col"))
// セルC2は現在の選択基準のセルのアドレス
// ROW(C2)：対象のセルの行番号
// CELL("row")：現在選択しているセルの行番号
// → 対象のセルの行番号が、現在選択しているセルの行番号と一致しない
// COLUMN(C2)：対象のセルの列番号
// CELL("col")：現在選択しているセルの列番号
// → 対象のセルの列番号が、現在選択しているセルの列番号と一致しない

４．[書式]を選択し、[表示形式]タブの[ユーザー定義]にて、以下の内容を設定して確定

"***";"***";"***";"***"

---

以上の設定で、選択しているセル（アクティブセル）の値のみを表示することができます。

ただ、条件付き書式の設定のみですと、セルを編集するか数式を再計算しない限り、設定が更新されません。

その理由は、CELL関数で取得される値は、数式が再計算されたタイミングに、値が更新されるためです。
そのため、対象の範囲が選択された場合は、自動で数式を再計算する仕組みを実現する必要があります。

2. 自動で数式を再計算する

今回は、セルを選択すると同時に数式を再計算させる仕組みを実現していきます。
特定のシートのセルを選択すると同時に、何かしらの処理を実行するには、該当するシートのシートモジュールを活用します。

シートモジュールは、該当するシートのタブ上で右クリックし、[コードの表示]を選択することで表示することができます。

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

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

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

このプロシージャの中に、「数式を再計算する」という以下の処理を記述します。

Application.Calculate

3. 完成

以上の手順で完成です。
セルを選択すると同時に、数式が再計算され、選択しているセルの値が表示されるようになります。

▼サンプルファイル▼

「入力後のセルを誤って上書きしてしまった…」そんな経験はありませんか？
今回は、一度入力したセルを自動で編集不可にする仕組みの開発方法について解説していきます。

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

1. はじめに

Excelでデータを入力した後、うっかり編集されることを防ぐ方法として、セルを自動で編集不可にする仕組みが有効です。
特に業務で扱うシートでは、入力後のデータを守る必要がある場合が多いです。今回紹介する方法を使えば、誤って編集されることなく、データの整合性を保つことができます。

2. シート側の設定

まずは、入力を許可するセルを選択し、そのセルのロック設定を解除します。
これにより、シートを保護した際に、ロックが解除されたセルのみ編集可能になります。

こちらでは、以下のシートのB列にある「受付済みID」を入力する項目に対して設定していきます。

まずは、B列全体を選択し、Ctrlキーを押しながらセルB1～B6を選択することで、一部の選択を解除します。

次に、選択範囲内で右クリックし、「セルの書式設定」を選択します。

書式設定の画面の[保護]タブを選択し、ロックのチェックを解除します。

以上で、シートの設定は完了です。
この設定を行うことで、シートを保護した際に、ロックを解除したセルだけが編集可能になります。

3. 開発準備

次に、Excel VBAを使って、セルに値を入力した後、そのセルが自動的に編集不可になるように設定します。

このように、特定のシート内の特定のセルが編集された時に処理を実行する仕組みを実現するには、「シートモジュール」を使用します。

シートモジュールは、該当するシートのタブ上で右クリックし、[コードの表示]を選択することで表示することができます。

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

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

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

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

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

4. コードの記述

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

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Me.Protect UserInterfaceOnly:=True

    Dim r As Range
    For Each r In Target
        If r.Value <> "" Then
            r.Locked = True
        End If
    Next r
    
End Sub

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

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Me.Protect UserInterfaceOnly:=True

    '省略
    
End Sub

「Worksheet_Change」というプロシージャの引数の「Target」に、編集されたセルの情報が渡されます。
この「Target」は後に使用します。
まず初めに、シートを保護する処理（Me.Protect）を記述しています。
「Me」は自身のシートモジュールのシート情報を指します。
また、保護する際に、「UserInterfaceOnly」の引数を「True」にすることで、シートが保護されたセルに対しても、VBAからは操作ができるようになります。

    Dim r As Range
    For Each r In Target
        If r.Value <> "" Then
            r.Locked = True
        End If
    Next r

実行時に渡される編集されたセルの情報（Target）には、複数のセルの情報が入っている可能性があります。
（コピー＆ペーストなどで複数セルに同時に値を入力した場合など）
その際は、１つ１つのセルに対して、値の有無を確認し、値が入力された場合に関してのみ、セルをロックする必要があります。
そのため、「For Each」を使用して、「Target」の情報を１つ１つ繰り返して実行しています。

「For Each」から「Next」の中で、１つ１つのセルの情報（r）に対し、「If」で空でない場合のみにロックをする（r.Locked = True）という内容を記述しています。

5. 完成

以上の内容で実現できます。
B列の表に値を入力して確定すると同時に、入力されたセルが保護され編集ができなくなります。

どうしても編集したい場合は、[校閲]タブの[シート保護の解除]を選択して「シートの保護」を解除する必要があります。

解除後は、対象シートのいずれかのセルを編集すると同時に、自動でシートが保護されます。

▼サンプルファイル▼