【エクセル】複数シートの値を参照して連続で表示する（一覧シートを作る）には

質問

いつもお世話になってます。
エクセルで教えて下さい。

Sheet佐藤
　　　品名　　　単価　　数量　　価格
1　　いちご　　100　　　　5　　　500
2　　ばなな　　200　　　　3　　　600

Sheet鈴木
　　　品名　　　単価　　数量　　価格
1　　めろん　　500　　　　1　　　500
2　　ばなな　　200　　　　5　　1000

と入力されていて、これを元に、

Sheet一覧
　　　氏名　　品名　　　単価　　数量　　価格
1　　佐藤　　いちご　　100　　　　5　　　500
2　　佐藤　　ばなな　　200　　　　3　　　600
3　　鈴木　　めろん　　500　　　　1　　　500
4　　鈴木　　ばなな　　200　　　　5　　1000

と表示させたい。
Sheet佐藤・鈴木はそれぞれ行の追加・挿入・削除されることがあり、
その都度Sheet一覧に反映させられるようにしたいのですが、どのようにしたら可能でしょうか？
（例えばSheet佐藤の2行目を削除した場合、
Sheet一覧
　　　氏名　　品名　　　単価　　数量　　価格
1　　佐藤　　いちご　　100　　　　5　　　500
2　　鈴木　　めろん　　500　　　　1　　　500
3　　鈴木　　ばなな　　200　　　　5　　1000
となるように）

また、タイトルとは質問が反れますが、
「価格」の列に「=B2（単価）*C2（数量）」という式が入っている場合、
1：式を変更されたくないので保護をかけたい。
2：保護をかけると行削除が出来ない。
3：行を挿入したら自動的に同じ式が入るようにしたい。
という希望を満たすにはマクロしかないでしょうか？
（マクロは初心者でほとんどわからないので・・・）

どうぞよろしくお願いします。

回答No.3

imogasi

質問は、ＶＢＡでやる部類の問題だと思います。結局下へ継ぎ足した続けたデータを作るということでしょう。
関数式でなんて考えてないでしょうね。下記に紹介するが、式だらけで、馬鹿げているように思う。
データ例
シート名：東京　Ａ２：Ａ７に
a
b
c
v
f
g
シート名：Ｓｈｅｅｔ２　Ａ２：Ａ１０
1
w
e
r
t
y
s
d
f
シート名：Ｓｈｅｅｔ３　Ａ２：Ａ５
23
11
23
w
統合したデータを置くシート名：Ｓｈｅｅｔ４
Ｈ１：Ｈ３に
東京
sheet2
sheet3
Ｆ２に=COUNTA(INDIRECT(H1&"!$A$2:$A$10"))と入れて
Ｆ４まで式を複写する。
6
9
4
となる。
Ｇ１に０
Ｇ２に=SUM($F$2:F2)+1と入れて
Ｇ３以下に式を複写する。
0
7
16
20
となる。
Ｓｈｅｅｔ４のＩ２に
=IF(A1=A2,I1+1,1)と入れて下方向に式を複写する。
Ｓｈｅｅｔ４のＡ２に
=INDIRECT(VLOOKUP(ROW()-1,$G$1:$H$4,2,TRUE)&"!A"&IF(A1=A2,I1+1,1)+1)
といれ
Ａ２０まで式を複写する。
a
b
c
v
f
g
1
w
e
r
t
y
s
d
f
23
11
23
w
となる。
Ｂ列は
=INDIRECT(VLOOKUP(ROW()-1,$G$1:$H$4,2,TRUE)&"!B"&IF(A1=A2,I1+1,1)+1)
とし、式を下方向に複写する。

お礼

ご回答ありがとうございます。
マクロは初心者な為、関数式で出来ないかと思ったのですが、
どうやら考え方が甘かったようです・・・。
データ量が多いですし、あんまり式だらけにしても重くなるだけですよね。

回答No.2

134

たくさんの要素を含んでいますね。

「一覧」を作るためには、「マクロ」にて

1.「一覧シート」をクリアする
2.「一覧シート」以外の必要なシートを数える
3.for～nextにより、とりまとめるべき回数繰り返しつつ
4.「必要なシート」を選択
5.「データ範囲」をコピー
6.「一覧シート」のデータのない行に貼り付け

というプログラムが必要そうです。
シートの並びや名前等がわかりませんので、具体的なものは控えます。

保護について
「編集」→「ジャンプ」→「セル選択」→「数式」で選択可能かもしれません。事前にセル範囲を全指定し、右クリックから「セルの書式設定」→「保護」により保護の解除をし、数詞キセル選択後に、同じ操作で保護を設定する必要がありますけど

　excl2003以降だと行削除を許可するかどうかは、設定可能のようです。
　関数で設定するなら、indirect文を使うか、削除せずにクリア→並び替えでクリアするようかもしれません。

　行挿入についても、indirect文を使用するか、コピー→コピーした行を挿入
が有効そうです。

　あとは、SuperMildさんの例示の通り、イベントプロシージャを活用したマクロを作るようかもしれません。

お礼

ご回答ありがとうございます。
indirect文というのを使ったことがなく
あまり良く理解出来ませんでしたので
勉強したいと思います。

回答No.1

noname#123709

汚いけど、これでいけるかな？
Sheet一覧のシートモジュールとしてSheet一覧がアクティブになると動きます。
もっとスマートなやりかたを誰かが書いてくれるはず！
Private Sub Worksheet_Activate()
Dim i As Long, j As Long, k As Long
Dim Sh As Worksheet
With Sheets("sheet一覧")
Rows("2:1000").ClearContents
For Each Sh In Worksheets
For i = 2 To Sh.Range("A65536").End(xlUp).Row
For j = 1 To 3
.Range(Cells(65536, j), Cells(65536, j)).End(xlUp).Offset(1).Value _
= Sh.Cells(i, j).Value
Next j
k = .Range("D65536").End(xlUp).Offset(1).Row
.Range("D65536").End(xlUp).Offset(1).Value = "=B" & k & "*C" & k
Next i
Next
End With
End Sub

保護を掛けるのは簡単ですが、懸念されている通り、行削除はできません。
自動で数式をいれるのはできるが、そのタイミングとして「行挿入したら」
は無理だと思う。

補足

ご回答ありがとうございます。
早速試してみたのですが、どうやら2回繰り返されている様なのです。
（各シートを2回ずつ参照している？）
それからもう1点、例ではD列に=B*Cという計算式を・・・としていますが、
実は列はもっとたくさんありまして、D列以降Z位まで（もっとかも）あります。
マクロはほとんどわからない為、どこをどう修正するといいのかわからなくて・・・。
補足いただけると大変助かります。よろしくお願いします。