Excelの改ページをしたとき

質問

検索の仕方が悪いのか、どうしたらいいのか方法が分からないのでご存知の方はお教えください。

以下のような表があります。

使用者名|教室 |授業
――――――――――
山田花子|Ａ□|音楽
□□□□|□□|国語
□□□□|Ｂ□|数学
□□□□|□□|図工
――――――――――ここで改ページ
□□□□|□□|体育
山田太郎|Ｂ□|音楽
□□□□|□□|図工
鈴木一郎|Ａ□|図工
□□□□|□□|家庭科

両面印刷の予定なので、これでは次のページになったとき即座に誰がどの教室を使っていたか分かりません。
そこで下記のようにしたいのです。

使用者名|教室 |授業
――――――――――
山田花子|Ａ□|音楽
□□□□|□□|国語
□□□□|Ｂ□|数学
□□□□|□□|図工
――――――――――ここで改ページ
山田花子|Ｂ□|体育←直前の使用者名と教室が入力される
山田太郎|Ｂ□|音楽
□□□□|□□|図工
鈴木一郎|Ａ□|図工
□□□□|□□|家庭科

※スペースではズレてしまった為空白欄に「□」を入力しています。実際には空欄です。

上記のように改ページがあったら直前のデータが入っているセルから情報を抜き出して挿入したいのですがどのような方法を使えばよいでしょうか?
それぞれの行は固定ではなく途中で挿入が入ったりする場合があります。そうするとまた一から入力しなければならず大変手間です・・・。
関数を使えばいいのか、Excelの機能で実現できるのかマクロを組まなければならないのか皆目検討が付きません。
ヘッダーはすでに使用しています。何個も設定できるものなのでしょうか?
全体で100ページ以上になり、かつ毎月提出するものなため、便利な機能があれば知りたいです。
とても困っています。ご存知の方はぜひお教えください。
よろしくお願いします。

回答No.3

pauNed · ベストアンサー

では、未使用セルを作業エリアとして使いましょう。
ここでは、仮に Z列が空いているものとして説明します。
もし使用していたら変更してください。
また、Z列は印刷範囲から除外してください。

[条件付き書式]の数式を
=AND($Z2<>1,A2=A1)
としましょう。

下記マクロを使います。

Sub try()
　　Dim i As Long 'Loopカウント用変数

　　With ActiveSheet
　　　　.Columns("Z").ClearContents 'Z列クリア
　　　　.UsedRange.Cells(.UsedRange.Count).Select '最終セル選択
　　　　ActiveWindow.View = xlPageBreakPreview '改ページプレビュー
　　　　For i = 1 To .HPageBreaks.Count '改ページ情報をLoop
　　　　　　'ページ頭のZ列にフラグをセット（ここでは 1 ）
　　　　　　.Cells(.HPageBreaks(i).Location.Row, "Z").Value = 1
　　　　Next i
　　End With
　　ActiveWindow.View = xlNormalView 'ノーマルビューに戻す
End Sub

作業エリアに、ページ頭を示す 1 をセットする事で、[条件付き書式]の条件判断させます。

環境によってはうまくいかない場合もあります。
その時は別案として"GET.DOCUMENT(64)"を使ったマクロを提示しても良いですが、
手作業でも、Z列に 1 を入力/消去する事で、該当セルのフォント表示の制御ができます。

お礼

再度のご回答、ありがとうございます。
マクロを実行したところ、うまくZ列に1が入り条件式書式を適用することが出来ました。
元データを入力の後空白に直前の行のデータをコピーするマクロと組み合わせて使用したいと思います。
どうもありがとうございました!

回答No.4

zap35

＃０２です。不勉強でしたm(_ _)m
アクセスレポートでできる他に、エクセルマクロでも自然な改ページを取得して表を加工できました。エクセルのマクロを書きましたので試してみてください。このマクロは指定したシート（マクロの２行目に記述）をコピーした後で、改ページされた行が空白ならその上の空白でないセルの値をコピーするものです。このマクロを実行した結果を印刷すれば期待する帳票になりませんか？

以下のマクロをALT＋F11でVBE画面を開き、左上のVBA Projectでシート名を右クリックし「挿入」→「標準モジュール」で表示される画面に貼り付けて下さい。マクロの実行はワークシート画面に戻ってALT＋F8でマクロ一覧を開き、マクロ名を選択して「実行」ボタンです。

Sub Macro1()
Const bk As String = "Sheet1" '印刷対象のシート名を指定
Const col As String = "C" '最終行を判定する列
Dim idx, lastR As Long
If ActiveSheet.Name = bk Then
　Application.ScreenUpdating = False
　ActiveSheet.Copy after:=ActiveSheet
　lastR = Cells(65536, col).End(xlUp).Row
　For idx = 1 To lastR
　　If Rows(idx).PageBreak <> xlPageBreakNone Then
　　　With Cells(idx, "A") 'A列が空白なら-------
　　　　If Len(.Value) = 0 Then　　　　'↓対象列が増えたら
　　　　　.Value = .End(xlUp).Value　　'↓この範囲をコピー
　　　　End If　　　　　　　　　　　　　　 '↓して列を変更する
　　　End With　　　　　　　' ここまで------------
　　　With Cells(idx, "B")　'B列が空白なら
　　　　If Len(.Value) = 0 Then
　　　　　.Value = .End(xlUp).Value
　　　　End If
　　　End With
　　End If
　Next idx
　Application.ScreenUpdating = True
End If
End Sub

質問の例ではA列、B列に「空白がある」となっていますが、実際のシートでは違うかもしれませんね。必要に応じて１１行～１５行目をコピーして列名だけ変更すれば、対象列を増やせます。

お礼

再度ご回答いただきありがとうございました。
これは新しいsheet1に新たに表を作るということでしょうか。
#3のpauNedさんのご提案くださった方法に付け加えて使用してみたいと思います。
マクロで出来るならマクロで全てやってしまいたいので・・・(空欄を埋める&条件付書式の設定など)
Len関数とは初めて見ました。いつもemptyかnullか""で指定していたので勉強になりました。
ありがとうございます。

回答No.2

zap35

アクセスのレポート機能なら可能ですが、エクセルではできないと思います。

まずエクセルの関数では改ページを検出することはできません。マクロを使っても「挿入」→「改ページ」で明示的に指定した改ページはエクセル４マクロのGET.CELL(27)で取得できますが、そうでない普通の改ページは検出できません。ですからエクセルでは実現できません。

アクセスならエクセルデータをインポートしてレポート機能で「重複データ非表示」を指定すれば、前行と同じ値の場合は空白で印刷させることが可能です。また改ページされたら、最初の行には項目が印刷されます。（たぶん期待にそっていると思います）

ただし質問文のようなデータではなく全行にすべての列のデータが入力されている必要があります。表中で全行と同じなら値を入力しないのは入力の手間や、シートを見たときの見やすさを考慮してだと思いますが、データ処理上はこのようなデータは非常に扱いにくいです。やはりデータはすべての列を入力するのが後利用を考えた場合の基本です。

アクセスのレポート機能はここでは説明しません。質問者さまがアクセスをどの程度理解されているか分からないためです。アクセスがよく分からないなら、少し調べて別に質問されるのがよいでしょう

お礼

わわ、補足に書いてしまいました。
改めてご回答ありがとうございました。

補足

ご回答ありがとうございます。
accessはあまり使ったことが無いのでこれから勉強します(クエリがいまいちよく分からなくて・・・SQLが独特ですよね)。
ただaccessも行数が6万を超えるデータについては使用しているのですが、やはりExcelのほうがなじみがあるので皆さん使いやすいようで・・・(作った帳票を後で印刷したりする人がいる為)なるべくならExcelでやりたいのですが一人で体裁を整えていると気が狂いそうになってきます・・・。

回答No.1

pauNed

こんにちは。

使用者名|教室 |授業
――――――――――
山田花子|Ａ□|音楽
□□□□|□□|国語
□□□□|Ｂ□|数学
□□□□|□□|図工
――――――――――
山田花子|Ｂ□|体育
山田太郎|Ｂ□|音楽
□□□□|□□|図工
鈴木一郎|Ａ□|図工
□□□□|□□|家庭科

まずはこの表を

使用者名|教室 |授業
――――――――――
山田花子|Ａ□|音楽
山田花子|Ａ□|国語
山田花子|Ｂ□|数学
山田花子|Ｂ□|図工
――――――――――
山田花子|Ｂ□|体育
山田太郎|Ｂ□|音楽
山田太郎|Ｂ□|図工
鈴木一郎|Ａ□|図工
――――――――――
鈴木一郎|Ａ□|家庭科

このように、空白セルを埋めるように変更したほうが良いと思います。
その後に、[条件付き書式]を使って、フォント色を『白』にすれば印字されません。

表がセルA1から始まっているとして、空白セルを埋めるには、

1)まずA:B列を選択して
2)[Ctrl]+[g]（ジャンプ）、[セル選択]クリック、「空白セル」にチェックして[OK]
3)空白セルだけ選択されます。
4)カーソル位置が B3 だったら、B3セルに =B2 と式を入力して（直ぐ上のセル）、[Ctrl]+[Enter]。
5)選択されたセルに数式が全て入り、直ぐ上のデータを参照します。
6)数式を値化するためにA:B列を選択して、[コピー]、
7)範囲そのまま、右クリック[形式を選択して貼り付け]「値」[OK]、[Esc]キー。

次に[条件付き書式]を設定します。
前提として、１ページあたりの行数が決まっているものとします。
（上記例で、仮に４行とします）

1)A2からB列最終セルまで選択して、メニュー[書式]-[条件付き書式]。
2)[数式が▼][=AND(MOD(ROW(),4)<>2,A2=A1)]
3)[書式]「フォント」の色を白に設定して[OK]クリック。

以上です。

=AND(MOD(ROW(),4)<>2,A2=A1)
この式は、『A2からB列最終セルまで選択し、A2セルがアクティブになっている状態』の位置関係です。
わかりにくければ、
まずA2セルだけ設定してコピーし、該当範囲に[形式を選択して貼り付け]「書式」とすればよいです。

MOD(ROW(),4)<>2
は、ヘッダー１行のみ、１ページが４行の場合、
４で割って２余る行が、データ部のページ頭になるからです。

お礼

ご回答ありがとうございます。
うまくいきそうだと思ったのですが、1ページの行数は固定ではないのです・・・(質問の例の表では文字列が短いので1列なのですが、本当は備考だの必要書類の欄だのに1行には収まりきらない文字が入っている為行の幅を2行分にしたりしています)
最初に書いておかなくてすみません。
これは入力するときに長くなりそうな文章はあらかじめ2行を結合しておけばうまくいきそうですね(今度は列が数十列あってそれも大変そうですが(汗)。

別の回答を待ってみたいのでもう少し質問を続行します。