Excel2010で動作確認。それ以降でも問題ないかと思います。
エクセルの集計表、在庫表などデータベースを作った時に、合計などを常に見えるようにしたいと思ったことはないでしょうか？
例えば、一番下に合計を計算させた行があるけど常には見えない。ウインドウ枠の固定は上で項目行に使っているので下にも使うことは出来ないし…。
合計行１行だけならそもそも項目行のすぐ上や下の１行を合計行にしてしまうか、参照で作ってしまえばウインドウ枠の固定で事足ります。
特に欲しい場合は商品名だったり顧客名簿だったり、複数の名前と合計数といった表が欲しい時。
例えばこんな具合ですね。
左が集計用データ。日々、入力していく事とします。
右が商品名ごとの在庫集計。入庫はプラス、出庫はマイナスで現在庫がいくつかをパッと見れるようにしたもの。
ただし、集計表が下の方に続いていくとスクロールで見切れます…。
別シートに合計表を作ってもいいけど、常に表示してあったほうが便利だなぁと思います。
今回はそれらをVBA（マクロ）で解決。ダウンロードして使えるようにしておくので、マクロを全く知らない人でもとりあえずは使える事と思います。（自分好みにするには多少、いじらないといけませんが。）
完成イメージはこんな感じ。右側に合計在庫数を示すウインドウが表示されています。
え、これもスクロールしたら見切れるじゃない？という話になりますが、マクロで追従するようにされています。
上図のようにセルをクリックすると、そこへウインドウが飛んで来るようになっています。ウインドウ枠の固定とも共存。
一応、動画も下に用意しておきます。
さて以下の本文は中級者向けに「ウインドウの導入方法」および、初心者向けに「ダウンロードしたサンプルを扱うための説明」の二本立てです。
ムズカシイ話は出来るだけハショってどうやったら出来るか、だけ書きますね！ぼくもやってるうちに出来ただけで専門的な知識はないから！
※当記事のエクセルおよびマクロの使用については自己責任にてお願いします。
解説
今回はエクセルのマクロを使っています。
とはいえ、ダウンロードすれば誰でも使えます。
ダウンロードして使う場合は仕様説明が必要ですので後ほど。
ウインドウの構成と導入
まずは、ウインドウの導入から説明致します。
ウインドウは単純に図形の四角です。ポイントは名前を把握すること。今回は table としています。
VBAでは図形をshapeオブジェクトとして扱って処理することが可能です。このとき、図形ごとの名前が分かってないと扱えません。クリックして自動的に名付けられた名前を使っても、覚えやすい名前をつけるのもOKです。
今回、図形のテキストに直接の文字反映をさせてもいいのですが、透明のテキストボックスを２つ用意しています。
図では以下のよう。
表
|
理由としては、商品名の文字数が違うとインデントを合わせようとしても在庫数の列がうまく揃わないことです。
また、商品名は左揃え、在庫数は右揃えにしたかったのでその点でもコチラのほうがキレイと判断しました。
ウインドウ枠の飛ばし方
以下、コードです。
Sub ウインドウ移動()
With ActiveSheet
.Shapes("table").Top = .Cells(ActiveWindow.ScrollRow + 1, ActiveWindow.ScrollColumn + 1).Top - 10
.Shapes("table_pro").Top = .Cells(ActiveWindow.ScrollRow + 1, ActiveWindow.ScrollColumn + 1).Top - 10
.Shapes("table_num").Top = .Cells(ActiveWindow.ScrollRow + 1, ActiveWindow.ScrollColumn + 1).Top - 10
End With
End Sub
図形である table と、テキストボックス２つ table_pro , table_num を全て現在のスクロール高さにあわせて表示。
後ろの -10 をいじったりすれば高さは微調整できます。
横の位置は最初に置いた位置のまま。途中で変更してもそのまま。
この移動させるコードの実行も必要です。下記を使用するシートに追加。
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Call ウインドウ移動
End Sub
「選択セルが変わったら」ウインドウ移動のプログラムを実行しますので、セルをクリックする度に今見ているスクロールに合わせてウインドウが移動するということになります。
エクセルから図形やテキストボックスへの文字反映
別シートで関数により合計を計算させておき、そのセルの数値をテキストボックスに反映させています。
別シートはコチラ。
「在庫数」のD列に関数が入っています。
例えばD2セルの「いちご の 全入庫数 ー 全出庫数」は以下の関数。
=SUMIF(在庫管理表!$D$3:$D$5000,B2,在庫管理表!$E$3:$E$5000)-SUMIF(在庫管理表!$D$3:$D$5000,B2,在庫管理表!$F$3:$F$5000)
1つ目のSUMIFでは合計対象が「在庫管理表」シートのD列「入庫数」、商品コードが一致するものに絞っています。
2つ目のSUMIFでは同様に「在庫管理表」シートのE列「出庫数」から商品コードが一致するもの。
これらを反映させるマクロプログラムの説明の前に「コード」について簡単に説明しておきます。
商品コードを使うメリットは？
慣れてない方には少し、複雑に感じるかもしれません。
もちろん、「いちご」、「みかん」といった名前をそのまま使うのもOKです。
ただし、ビジネスの事務で商品名や顧客名を扱っている場合、その名前が変わることがあります。
その際に、通し番号などで普遍的な「コード」を用いていると、名前を変えてしまっても安心です。
さて、先程の集計したセルの内容を反映させるマクロのソースコードです。
Sub 表更新()
Dim xxx, yyy As String 'xxxが商品名の出力文字列、yyyが在庫数の出力文字列
Dim i, x As Long
With Worksheets("商品一覧表") 'シート名通りに
x = .Range("A1").End(xlDown).Row '商品名の何行目までデータが有るか確認、その分の抽出を行う
xxx = "商品名" & vbCrLf '表の見出しの名前１ vbCrLfは文字列内で改行を意味する
yyy = "在庫数" & vbCrLf '表の見出しの名前２
For i = 2 To x
If .Cells(i, 5).Value = 1 Then '商品一覧表の「E」列（出力）に1が立ってる場合のみ抽出
xxx = xxx & .Cells(i, 1).Value & vbCrLf '商品名のある「A」列からデータ抽出
yyy = yyy & .Cells(i, 4).Value & vbCrLf '在庫数のある「D」列からデータ抽出
End If
Next i
End With
With ActiveSheet
.Shapes("table_pro").TextFrame.Characters.Text = xxx '左のテキストボックスに商品名を出力
.Shapes("table_num").TextFrame.Characters.Text = yyy '右のテキストボックスに在庫数を出力
End With
End Sub
単純に集計したセルを上から順に文字列変数に格納、テキストボックスのテキストに代入しているだけです。
上だけでは実行されないので、自動更新のために以下のマクロも入れます。
Private Sub Worksheet_Change(ByVal Target As Range)
Call 表更新
End Sub
ワークシート上で更新があったら起動するものです。具体的には数量を変化させたり、商品の追加があったときですね。
直接、Worksheet_Changeイベントに先程のソースコードを入れてもOKです。
マクロの注意 元に戻すが使えなくなる
注意点
今回のプログラムに限らず、マクロ処理による変更が入ると「元に戻す」ボタンが使えなくなります。
マクロ処理が１つ前の操作、にはなるのですが一括処理されるため膨大な量となります。そのためエクセル上の仕様としてはマクロ処理を保存する事を放棄し元に戻すが使えない、ということにされています。
「元に戻す」と共存するには別途マクロと共存する「元に戻す」ボタンを作る方法もありますが、非常に複雑です。Googleでそのような検索キーワードを入れると解決策を掲載している方もいらっしゃいます。
サンプルの使い方
マクロは自分で編集出来ない、という方向けに。
下記エクセルをダウンロードしていただければすぐ使えます。
・商品名、入庫数といった数字はいくらでも変更可能。商品名→得意先、入庫数→収入、出庫数→支出など。
・商品名である「いちご」、「みかん」なども勿論お好きなお名前でどうぞ。登録数はいくらでも。
・そのままだと「在庫数」の1列しか出力されません。2項目以上を表示したい場合はマクロを少し改造する必要あり。
・商品一覧は最終行をドラッグコピーで伸ばし、名前を任意のものにすれば新規に追加可能です。
追加された商品名、コードは「名前の管理」で関数を用いる工夫をしているので範囲指定をしなおしたりは必要ありません。
これについては調べていただくと先人のまとめた記事が出てくるかと思います。