エクセルの小計（集計）を実行したデータに罫線を引く

質問

エクセルに標準装備されている小計（データ⇒小計）機能は便利でよく使用しているのですが、集計結果行（ｘｘｘ集計と表示される行）以外のデータに罫線を引きたいのですが、行数が多くて苦労する場合があります。　添付ファイルの様に集計行以外のデータ行に自動で罫線を引くマクロやＶＢＡがあればご教示願います。
※当然ですが、集計行以外のデータ行の行数は毎回変動します。

回答No.4

kagakusuki

回答No.3です。

>添付ファイルの様に集計行以外のデータ行に自動で罫線を引くマクロやＶＢＡがあればご教示願います。

との事ですので、ついでにその様な働きをするVBAの一例も回答させて頂きます。
　尚、下記のVBAでは、表の枠線が引かれている範囲がA列～H列の2行目以下のセル範囲である場合に関するもので、表の外枠を中太の実線で、内側にある線を細い点線で引きなおす様になっております。
　もし表の始まる行や列の位置、それに表の右端の列の位置が異なっている場合には、VBAの構文の冒頭辺りにある

Const FirstRow = 2 '表の枠線が引かれている最初の行
Const FirstColumn = "A" '表の枠線が引かれている最初の列
Const LastColumn = "H" '表の枠線が引かれている最後の列

の所で設定されている定数の値を適時修正して下さい。

Sub QNo9217396_エクセルの小計を実行したデータに罫線を引く()

Const FirstRow = 2 '表の枠線が引かれている最初の行
Const FirstColumn = "A" '表の枠線が引かれている最初の列
Const LastColumn = "H" '表の枠線が引かれている最後の列

Dim LastRow As Long, c As Range

LastRow = Range(FirstColumn & Rows.Count).End(xlUp).row 'データが存在している最終行の取得
If LastRow < FirstRow Then
MsgBox "処理すべきデータが見当たりませんません。" & vbCrLf _
& "マクロを終了します。", vbExclamation, "データ無し"
Exit Sub
End If

Application.ScreenUpdating = False
Application.Intersect(Range(FirstColumn & FirstRow & ":" & LastColumn _
& Rows.Count), ActiveSheet.UsedRange).Borders.LineStyle = xlNone '表の罫線消去
With Range(FirstColumn & FirstRow & ":" & LastColumn & LastRow)
With .Borders 'セル範囲の全ての罫線
.LineStyle = xlDot '点線（ドット）
.ColorIndex = xlAutomatic '色設定[自動]
.Weight = xlThin '細線
End With
With .Borders(xlEdgeTop) 'セル範囲の上端の罫線
.LineStyle = xlContinuous '実線
.ColorIndex = xlAutomatic '色設定[自動]
.Weight = xlMedium '中太線
End With
With .Borders(xlEdgeLeft) 'セル範囲の左端の罫線
.LineStyle = xlContinuous '実線
.ColorIndex = xlAutomatic '色設定[自動]
.Weight = xlMedium '中太線
End With
With .Borders(xlEdgeRight) 'セル範囲の右端の罫線
.LineStyle = xlContinuous '実線
.ColorIndex = xlAutomatic '色設定[自動]
.Weight = xlMedium '中太線
End With
For Each c In Range(FirstColumn & FirstRow & ":" & FirstColumn & LastRow)
If c.Value Like "*集計" Then 'その行の左端のセルの値が「～集計」となっている場合
With Application.Intersect(c.EntireRow, .Offset(0))
.Borders.LineStyle = xlNone 'その行の罫線消去
With .Borders(xlEdgeTop) 'その行の上端の罫線
.LineStyle = xlContinuous '実線
.ColorIndex = xlAutomatic '色設定[自動]
.Weight = xlMedium '中太線
End With
If c.row < LastRow Then 'その行が最終行ではない場合
With .Borders(xlEdgeBottom) 'その行の下端の罫線
.LineStyle = xlContinuous '実線
.ColorIndex = xlAutomatic '色設定[自動]
.Weight = xlMedium '中太線
End With
End If
End With
End If
Next c
End With

Application.ScreenUpdating = True

End Sub

回答No.3

kagakusuki

>ちなみに中に引く罫線の種類を分ける事は可能でしょうか？
>（例：外枠：実戦　内枠：点線）

　条件が満たされた際に引き直された後の罫線が、標準の太さである細線であれば、条件付き書式でも使う事が出来ますから、(ちょっと複雑になりますが)工夫次第で外枠の線が例えば中太線の実線で、内側の縦横の線が「標準の太さである細線の点線」で表示される様にする事も出来ます。
　どうするかと申しますと、はじめに内側の縦線のみが細い点線で、他の線は(内側の横線も含めて)全て中太の実線となっている様にしておいてから、2つの条件付き書式を使って「セルの上辺が表の内側になっているセルの上側の罫線」と「セルの下辺が表の内側になっているセルの下側の罫線」をそれぞれ細い点線に変え、もう1つの別の条件付き書式で「集計行」の縦線を消してしまえば良い訳です。
　その場合の設定方法は以下の様なものとなります。
※尚、下記の条件付き書式の例は、表の枠線が引かれているセル範囲の左端の隅のセルがA2セルである場合に対応するものです。(ですから、もし表がA2セル以外のセルから始まっている場合には、それに合わせて適時修正して下さい)

「表の左上の隅のセルの1の下のセル」(A3セル)～「表の右端の列で、集計行の中で最も下にある行のセル」のセル範囲を選択
　　↓
Excelウインドウの左上の辺りにある[ホーム]タブをクリック
　　↓
現れた「フォント」グループの中にある[罫線]ボタンの右側の[▼]印のボタンをクリック
　　↓
現れた選択肢の中から[その他の罫線]と記されている箇所を選択してクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[罫線]タブの「スタイル」欄の中に並んでいる様々なスタイルの線の中から中太の実線を選択してクリック
　　↓
「セルの書式設定」ダイアログボックスの[罫線]タブの[外枠]ボタンと内側の横線のボタンをそれぞれクリックし、左右両端の縦線と全ての横線を中太の実線にする
　　↓
「スタイル」欄の中に並んでいる様々なスタイルの線の中から細い点線を選択してクリック
　　↓
「セルの書式設定」ダイアログボックスの[罫線]タブの内側の縦線のボタンをクリックし、内側の縦線のみを細い点線にする
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
Excelウィンドウの[ホーム]タブの「スタイル」グループの中にある[条件付き書式]ボタンをクリック
　　↓
現れた選択肢の中にある[ルールの管理]をクリック
　　↓
現れた「条件付き書式ルールの管理」ダイアログボックスの中にある[新規ルール]ボタンをクリック
　　↓
現れた「新しい書式ルール」ダイアログボックスの「ルールの種類を選択して下さい」欄の中にある[数式を使用して、書式設定するセルを決定]をクリック
　　↓
現れた「次の数式を満たす場合に値を書式設定」欄の中に

=COUNTIFS($A3,"<>*集計",$A2,"<>*集計")

と入力
　　↓
「新しい書式ルール」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[罫線]タブをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[罫線]タブの[クリア]ボタンをクリック
(ボタンが灰色表示となっていて押す事が出来ない場合は、そのまま次の操作へ進んで下さい)
　　↓
現れた「セルの書式設定」ダイアログボックスの[罫線]タブの「スタイル」欄の中に並んでいる様々なスタイルの線の中から細い点線を選択してクリック
　　↓
「セルの書式設定」ダイアログボックスの[罫線]タブの上の横線のボタンをクリックし、上の横線のみを細い点線にする
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「新しい書式ルール」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの中にある[新規ルール]ボタンをクリック
　　↓
現れた「新しい書式ルール」ダイアログボックスの「ルールの種類を選択して下さい」欄の中にある[数式を使用して、書式設定するセルを決定]をクリック
　　↓
現れた「次の数式を満たす場合に値を書式設定」欄の中に

=COUNTIFS($A3,"<>*集計",$A4,"<>*集計")

と入力
　　↓
「新しい書式ルール」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[罫線]タブをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[罫線]タブの[クリア]ボタンをクリック
(ボタンが灰色表示となっていて押す事が出来ない場合は、そのまま次の操作へ進んで下さい)
　　↓
現れた「セルの書式設定」ダイアログボックスの[罫線]タブの「スタイル」欄の中に並んでいる様々なスタイルの線の中から細い点線を選択してクリック
　　↓
「セルの書式設定」ダイアログボックスの[罫線]タブの下の横線のボタンをクリックし、下の横線のみを細い点線にする
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「新しい書式ルール」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの中にある[新規ルール]ボタンをクリック
　　↓
現れた「新しい書式ルール」ダイアログボックスの「ルールの種類を選択して下さい」欄の中にある[数式を使用して、書式設定するセルを決定]をクリック
　　↓
現れた「次の数式を満たす場合に値を書式設定」欄の中に

=COUNTIF($A3,"*集計")

と入力
　　↓
「新しい書式ルール」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[罫線]タブをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[罫線]タブの[クリア]ボタンをクリック
(ボタンが灰色表示となっていて押す事が出来ない場合は、そのまま次の操作へ進んで下さい)
　　↓
現れた「セルの書式設定」ダイアログボックスの[罫線]タブの「スタイル」欄の中に並んでいる様々な線のスタイルの中から[なし]を選択してクリック
　　↓
「セルの書式設定」ダイアログボックスの[罫線]タブの右の縦線のボタンと左の縦線のボタンをそれぞれクリックし、左右全ての縦線を非表示にする
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「新しい書式ルール」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの中にある[新規ルール]ボタンをクリック
　　↓
現れた「新しい書式ルール」ダイアログボックスの「ルールの種類を選択して下さい」欄の中にある[数式を使用して、書式設定するセルを決定]をクリック
　　↓
現れた「次の数式を満たす場合に値を書式設定」欄の中に

=AND(COUNTIF($A3,"*集計"),$A4="")

と入力
　　↓
「新しい書式ルール」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[罫線]タブをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[罫線]タブの[クリア]ボタンをクリック
(ボタンが灰色表示となっていて押す事が出来ない場合は、そのまま次の操作へ進んで下さい)
　　↓
現れた「セルの書式設定」ダイアログボックスの[罫線]タブの「スタイル」欄の中に並んでいる様々な線のスタイルの中から[なし]を選択してクリック
　　↓
「セルの書式設定」ダイアログボックスの[罫線]タブの下の横線のボタンをクリックし、下の横線のみを非表示にする
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「新しい書式ルール」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの「適用先」欄が4箇所とも全て「最初に選択したセル範囲と同じセル範囲」になっている事を確認
※尚、「適用先」欄に設定されているセル範囲が間違っている場合には、「適用先」欄をクリックし、適用するセル範囲を入力しなおして下さい。(その際には、カーソルとマウスの左ボタンによる範囲選択が使えます）
　　↓
「条件付き書式ルールの管理」ダイアログボックスの[適用]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの[OK]ボタンをクリック

　以上です。

回答No.2

bunjii

>ちなみに中に引く罫線の種類を分ける事は可能でしょうか？
条件付き書式で設定できるのはセル単位なので中に引く罫線は定義できません。
左右上下の線の種類と線の色を個別に設定可能ですが、太さは変えられないみたいです。

回答No.1

bunjii

>添付ファイルの様に集計行以外のデータ行に自動で罫線を引くマクロやＶＢＡがあればご教示願います。
条件付き書式で十分です。
範囲を選択して条件付き書式で「数式を使用して、書式設定するセルを決定」を選択し、下記の数式を入力します。
=FIND("集計",$A1&"集計")>LEN($A1)
A列の値に"集計"という文字が含まれていない行について書式設定を升目の罫線ありにすれば良いでしょう。

お礼

ありがとうございます。　教えて頂いて事でおおよその問題が解決しました。

補足

ちなみに中に引く罫線の種類を分ける事は可能でしょうか？
（例：外枠：実戦　内枠：点線）