Excel2000：複数シート同座標セルから、別シート一覧を連番で参照するには？

質問

同一のExcelファイル中に、30シートあるとします。

これらはどれも同じフォームで、全てのシートの同座標セルに連番をとりたいとします。
その連番のナンバーは、31番目のシートに一覧になっています。
この30ものシートの同座標セルから、31番目の一覧にひとつずつズレて参照させるには、どうしたらいいでしょうか。

例)
1シートA1に、31番目シートA1の「No.1-1」
2シートA1に、31番目シートA2の「No.1-2」

※ちなみに、30シート分のフォームが既にあり、後で一覧のシートを追加するという前提です。

今は一つずつのシートを開けて、31番目シートの一覧へ、一つ一つリンクさせているだけです。
でも、これをやってるとシートが多くなると面倒なんですよね。
多分一括で出来るやり方がExcelにはあると思うんです。

ただし、私はVBはまったくの素人なので、表示形式や関数で教えていただけると嬉しいです。
また、なぜそういうやり方になるのかも、お勉強のために解説していただけると尚嬉しいです。

よろしくお願い致します。

回答No.2

imogasi · ベストアンサー

（１）Ｓｈｅｅｔ３１！Ａ１をコピーして、他のシートのＡ１にコピーしても、式のＡ１のＡや１の部分は変化しません。同一シート内で式を下方向や右方向へ複写する時の番地の変化の機能が使えません。
（２）Ｓｈｅｅｔをなべてすべて指定するのはＣＴＲＬを押しながら全てのシート名のタブをクリックする方法でしょうが、それをしても、そもそも式がコピーされるわけでもありませんし、むろんＳｈｅｅｔ１！Ａ１の１の部分が２、３、・・・と変化しません。
（３）持ってくる番号はＳｈｅｅｔ３１のＡ１：Ａ３０に固まって存在しても、値を受ける（セットされる）べき方（セル）が、「各シートのＡ１」と言う風にバラバラですから、エクセルでは一気にデータ（番号）を持ってくることが出来ません。
＞多分一括で出来るやり方がExcelにはあると思うんです
－－－＞従って、上記の理由からありません。
（５）関数式は取ってくる方（セル）に設定しないといけません。
本質問では、各シートのＡ１セルです。
それに値を持ってくるのに、複写機能等が使えない以上、１つ１つ人手でセットせざるを得ません。
Ｓｈｅｅｔ３１のＡ１やＡ２のセルからＳｈｅｅｔ１のＡ１やＳｈｅｅｔ２のＡ１に値を「飛ばす」わけにいきません。
ところが、ＶＢＡではこれが出来ます。
（値を受けるセル）＝（値を渡すセル）｛右辺から左辺へ代入する｝とプログラムコードで表現出きるからです。
それと（値を受けるセル）と（値を渡すセル）を３０個書き並べるのでなく、１行だけを書き、１から３０まで変化させて、値代入を実行せよとプログラムでは表現できます。ここが便利なところです。
＞VBじゃないと難しいんですね・
－－－＞そうです。
＞教えていただいたVBで、素人感覚ながらちょっと頑張って作って見ようかと思います。
－－＞＃１のご解答の、どこにもＶＢＡのコードはないのでは。
一例はシート数を少なくして、Sheet1からSheet4までの各々のＡ１に、Sheet5のＡ１：Ａ４までに入った番号（でなくてもなんでも可）をセットする例をあげます。
Sub test01()
Dim sh As Worksheet
i = 1
For Each sh In Worksheets
If sh.Name <> "Sheet5" Then
sh.Cells(1, "A") = Worksheets("sheet5").Cells(i, "A")
i = i + 1
End If
Next
End Sub
を標準モジュールに貼りつけて実行すると良い。ただしシートタブの左からの順番により決定されます。

お礼

imogasiさん、回答ありがとうございます。

とても丁寧に詳細に教えていただけて嬉しいです。
ですが、自宅PCにOfficeが入ってないので(^^;)、ぜひ会社で検証してみようと思います。

不勉強なVBでも、なんとかなりそうなので、今後の作業が楽になれそうですよ(^^)

どうもありがとうございました。

補足

本日ためしにやってみたところ、見事出来ました！
シートは既に50に増えたんですが、ちゃんと50個のシート全てに一括ナンバリング出来ましたよ。

いまいち言語は分かりませんが、この形態だけは、どう座標を変えれば反映されるか分かったみたいです。

本当にどうもありがとうございました。

回答No.1

neKo_deux

> 1シートA1に、31番目シートA1の「No.1-1」
> 2シートA1に、31番目シートA2の「No.1-2」

シートを複製して追加していくので、「1シート!A1」「2シート!A1」には同じ式を入れたいという事だと思います。

Excelのワークシート関数で「シート名」「シート番号」を取得できるものが無いようですので、ワークシート関数、書式設定だけで対処するのは難しいです。
VBAを使って「シート名」「シート番号」を返すユーザー定義関数が作れれば対処可能なのですが…。

--
1シート!A1に1
2シート!A1に2
…

などと入っているという前提であれば、

B1:=INDIRECT("31番目シート!A"&A1)

で「31番目シートのA<A1セルの番号>の値」が参照できます。

お礼

neKo_deuxさん、回答ありがとうございます。

そうですか、VBじゃないと難しいんですね・・・
Excelなら、入力規則のリストとか、IF関数とかで何とかなるんじゃないかと期待してたんですけど(-_-;)

でも教えていただいたVBで、素人感覚ながらちょっと頑張って作って見ようかと思います。
もうどうせ既に作成済みのものだし、のんびりやってみます。

どうもありがとうございました。