excel 串刺しセルの値を列に並べる

質問

excel2000を利用しています。

シートが１０枚以上あるexcelブックがあるのですが、これらのシートのフォーマットは全て同じで、値がそ
れぞれ独自に入っています。

一番左側のsheet1は無地です。

そして、左から2sheet目以降の全てのシートで、それぞれ 指定セル を　sheet１のA列に縦にデータを保管したいです。

そして、同様にまた違う指定セルをＢ列、また違うセルをＣ列・・・・

といった具合で全シートの複数の項目をsheet1にリスト形式でまとめたいです。

どうかご教授をお願いいたします。

回答No.3

merlionXX · ベストアンサー

VBAの質問でしょうか？
各シートの指定セルが仮にA1、B1、C1だとしたら

Sub　test01()
　　　　Dim　i　As　Long
　　　　For　i　=　2　To　Sheets.Count
　　　　　　　　With　Sheets(1)
　　　　　　　　　　　　.Cells(i　-　1,　"A").Value　=　Sheets(i).Range("A1")
　　　　　　　　　　　　.Cells(i　-　1,　"B").Value　=　Sheets(i).Range("B1")
　　　　　　　　　　　　.Cells(i　-　1,　"C").Value　=　Sheets(i).Range("C1")
　　　　　　　　End　With
　　　　Next　i
End　Sub

で大丈夫だと思います。
該当のBOOKの標準モジュールに書いてくださいね。

お礼

回答ありがとうございました。

とても複雑なコードを予想していましたが、意外にシンプルで驚きました。

おかげさまでやりたいことが出来そうです。ありがとうございました。

回答No.5

kagakusuki

>しかし、やはりシート名がsheet()で限定されるというのが、関数の限界でしょうか。

　例えば、SheetA、SheetB、SheetC、SheetD、SheetE、SheetF、SheetG、SheetH、SheetIという名称のシートがあり、Sheet1のA2セルにはA列に表示するデータの項目名が入力さていて、Sheet1のA3以下に、SheetA～SheetIのA1セルの値を自動的に表示させるものとします。

　まず、適当な空きシートにSheetA～SheetIの各シートのシート名の一覧表を作成します。
　空きシートが無ければ、新たにシートを作成して下さい。
　ここでは仮に、「補助シート」という名称のシートを作成するものとします。
　そして、補助シートのA2以下にSheetA～SheetIの各シートのシート名を、空欄を空けずに並べて下さい。

　次に、Sheet1のA3セルに、次の関数を入力して下さい。

=IF(ISERROR(INDIRECT(INDEX(補助シート!$A:$A,ROWS($2:3))&"!A1")),"",INDIRECT(INDEX(補助シート!$A:$A,ROWS($2:3))&"!A1"))

　次に、Sheet1のA3セルをコピーして、Sheet1のA4以下に貼り付けて下さい。

お礼

回答ありがとうございました。
恐れ入りました。
勉強になりました、ありがとうございます。

回答No.4

keithin

＞excel2000を利用しています。

では，ちょっとタイプの違うマクロで。

手順：
挿入メニューの名前の定義で
　名前　シート一覧
　参照範囲　=GET.WORKBOOK(1)
と登録する

＞2sheet目以降の全てのシート

シート１に
=INDIRECT(INDEX(シート一覧,ROW(A2))&"!C5")
のように記入し，下向けにシートの枚数だけコピーする

＃面倒でなければ
=IF(ROW(A2)>COUNTA(シート一覧),"",INDIRECT(INDEX(シート一覧,ROW(A2))&"!C5"))
などのように一手間入れても勿論構いません。

＃シート名の配列を取得するので，シート名が「１，２，３」の順になってなくてもシートのある順で結果を得ます。

もっともふつーのマクロでシート名一覧だけを例えばＡ列セルに書き出させて，INDIRECT関数を使った方が簡単は簡単です。

Ａ２以下に
Sheet2
のように書き出してあれば
ついでにＢ１以右に欲しいセル番地を「B2」とか「C5」のように列記しておけば
Ｂ２：
=INDIRECT($A2&"!"&B$1)
を下に右にコピー
といった具合に。

オマケ：
sub macro1()
　dim i
　for i = 2 to worksheets.count
　　worksheets(1).cells(i, "A") = worksheets(i).name
　next i
end sub

お礼

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

すごいですね。そんな機能があったなんて知りませんでした。

絶句してしまいます。excelの世界がいかに深くて、自分がとても未熟だと思い知らされます。

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

回答No.2

tom04

こんばんは！
各Sheet名はSheet2・Sheet3・・・のようになっているとします。

仮に各SheetのA1セルをA列に表示する場合は
Sheet1のA1セルに
=INDIRECT("Sheet"&ROW(A2)&"!A1")
としてオートフィルで下へコピーではどうでしょうか？m(_ _)m

お礼

ありがとうございました。
偶然にもNO1の方と似ている式ですがROWの部分で若干異なりますが、
セルを単一で指定しているので分かりやすいです。

勉強になりました。ありがとうございました。

回答No.1

MackyNo1

例えば、Sheet2以下のシートのD2セルを一覧表示するなら以下のような数式を入力して下方向にオートフィルしてください。

=INDIRECT("Sheet"&ROW(2：2)&"!D2")

お礼

ありがとうございます。

関数でできるなんて思いもよらなかったので驚きです。

しかし、やはりシート名がsheet()で限定されるというのが、関数の限界でしょうか。

勉強になりました。ありがとうございました。