エクセルで見やすく

質問

複数のシートに同じ表があります。
氏名、住所とかが書いてあり、一人ずつシートを分けています。

　これを最初のページに名前と年齢だけであるとか、名前と給料だけであるとかをまとめたいと思っています。現在は一つづつ参照させていますが、ちょっと面倒くさいです。新しいシートを作ったら、自然に参照
させる方法はないでしょうか？

=Sheet1!A1と入力したあと、コピーをだーっとしたら
=Sheet1!A2・・となりますが、

これを
=Sheet2!A2、=Sheet3!A2という風に、したいというようなことです。

意味が分かりにくくて申し訳ないですが、やり方に心当たりのある方、よろしくお願いいたします。

回答No.11

ja7awu · ベストアンサー

追加で～す。

前のは、参照セルをA2と固定しましたが、これを、自由に指定できるようにしました。

使い方は、SheetX(左からのシート順を数値で指定 , 参照するセルを文字列で指定)

返り値：指定したシート順の指定セルの値を返します。

今回の例では、次のような式になります。

=IF(INFO("numfile")<ROW(),"",SheetX(ROW(),"A2"))

コードの設定方法：
１．Alt + F11 で VBE(Visual Basic Editor)を開きます。
２．VBE のメニューから[挿入] -->[標準モジュール] を指定します。
３．モジュールウィンドウに下記コードをコピーして貼り付けます。
４．Alt + Q （または、右上隅の×）でウィンドウを閉じ、シートに戻ります。
これで、SheetX_A2関数が使えます。

Function SheetX(X As Integer, StrRng As String)
　　If X > Worksheets.Count Then
　　　　SheetX = ""
　　　　Exit Function
　　End If
　　SheetX = Worksheets(X).Range(StrRng).Value
End Function

お礼

できました！ありがとうございます。
長いこと付き合ってくれて本当に感謝感謝です。

ただ、意味はほとんど分かっていませんが。
マクロってすごいですね。

回答No.10

taisuke555

失礼します。

＞シート名が数字でない場合は使えないんでしょうか

シート名の作り方にもよりますが、

シート名「田中一郎」として

一覧表で
A1セル：田中一郎
B1セル：=INDIRECT(A1&"!B1")

とすれば、シート「田中一郎」のB1セルを参照します。
該当するシートが無い場合には、「#REF!」と表示されますので、
新規にシートを作成します。
空白の時にもエラーがでてしまうので、
=IF(A1<>"",INDIRECT(A1&"!B1"),"")
としておけば、A1セルが空白の時には、空白を表示します。

質問が違う場合には、すみません。

お礼

ありがとうございます。
それだと、A1セルには一個一個セル名を入力する必要がありませんか？

回答No.9

ja7awu

> あと、シート名が数字でない場合は使えないんでしょうか？

まぁ、VBAで処理することになるでしょう。

ここでは、左からx番目のシートのA2を参照するユーザー定義関数 SheetX_A2 を
作ります。

使い方は、SheetX_A2(左からのシート順番数)

今回の実例では、例えば次のようになります。

=IF(INFO("numfile")<ROW(),"",SheetX_A2(ROW()))

コードの設定方法：
１．Alt + F11 で VBE(Visual Basic Editor)を開きます。
２．VBE のメニューから[挿入] -->[標準モジュール] を指定します。
３．モジュールウィンドウに下記コードをコピーして貼り付けます。
４．Alt + Q （または、右上隅の×）でウィンドウを閉じ、シートに戻ります。
これで、SheetX_A2関数が使えます。

Function SheetX_A2(X As Integer)
SheetX_A2 = Worksheets(X).Range("A2").Value
End Function

回答No.8

happypoint

#3,7です。

＞シート名が数字でない場合は使えないんでしょうか

回答にあった関数で解決するには、
シート名が一定の法則にしたがって並んでいる場合のみ有効です。
（例：a,b,c・・・　とか　あ、い、う・・・とか）

シート名が氏名などのような不規則な情報の場合は、
マクロ(VBA)でやるしかないです。

お礼

ありがとうございます。
シート名は数字なんですが一定の法則がないんです。
マクロですかー・・。
せっかく分かりかけてきたのに分からなくなってしまいそうです。

回答No.7

happypoint

#3です。こんにちは。

＞複数のシートあって、
＞それを最初なり最後なりの一枚にまとめたいのです。
＞その集計用のシートに入力する方法を探しています

なるほど。

たとえば、Sheet1,Sheet2,Sheet3があって、
それぞれA2セルに、個人の氏名があるとしますよね。

で、これを別のシート
（とりあえずシート名を「集計」としましょうか。）
に、一覧表形式にして、氏名のリストを作りたい、
ということのようですね。

で、回答を読み直してみますと、
#1さんのやり方がそのものずばりのような気がしますが。
この例でいきますと、
「集計」シートのどこかに、以下の式を入力します。

　=INDIRECT("Sheet" & 1 & "!A2")
この式は、シートSheet1のA2セルの値を表示する式になります。

同様に
　=INDIRECT("Sheet" & 2 & "!A2")
とすると、シートSheet2のA2セルの値を表示する式になります。

これを、#1さんは
　=INDIRECT("Sheet" & ROW() & "!A2")
としました。
この式は、シート「SheetX」のA2セルの値を表示する式になります。
Xの値は、この数式を入れた行番号によって決まります。
ようするに、
この式を
　1行目に入れたときはSheet1,
　2行目に入れたときはSheet2,
　3行目に入れたときはSheet3
のシートを参照する式になります。

さらに、
　=INDIRECT("Sheet" & ROW()+1 & "!A2")
としますと、
この式を
　1行目に入れたときはSheet2,
　2行目に入れたときはSheet3,
　3行目に入れたときはSheet4
のシートを参照する式になります。

理屈がわかったでしょうか？

回答No.6

ja7awu

あっ、ごめん　何故A2に式を書いたか　ということ？

それは何処でも構いません。
10行目に書いた場合は、Sheet2から参照ですから、10-2で次の式になります。

=IF(INFO("numfile")<ROW()-8,"",INDIRECT("Sheet" & ROW()-8 & "!A2"))

式の意味を理解すれば応用できます。

補足

だんだん分かってきました。
あと、シート名が数字でない場合は使えないんでしょうか？

回答No.5

ja7awu

> これを
> =Sheet2!A2、=Sheet3!A2という風に、したいというようなことです。

各シートのA2を参照しているからA2ですよ。

> Sheet1のA2に入れるというのは、集計するシートに入れるということでしょうか？だったらなぜA2なんでしょうか？

Sheet1を集計シートとしての場合です。
新しいブックでテストして、式を理解してから実情に合わせて応用してください。

回答No.4

shiga_3

＃1の方の回答に補足を。
Sheet1のA2に入力する
=INDIRECT("Sheet" & ROW() & "!A2")
というのは、参照したいシートの名前が「Sheet*（*は1,2,3,...）」の時に、その数式の入っているセルの行番号（A2だったら2）と同じ数字の入ったシート（A2だったらSheet2）のA2のセルを参照します、という意味です。
したがって、例えばまとめのページがSheet0、個人別のページがSheet1,2,3,...で参照したいセルがA2の時に、Sheet0のセルB3から下に内容をリストアップさせたい場合は、Sheetの番号（1,2,3,...）がセルの行番号(3,4,5,...）より2少ないですから、B3に
=INDIRECT("Sheet" & ROW()-2 & "!A2")
と入力して下にコピー、ということになります。

補足

ありがとうございます。初心者にも分かりやすい説明です。ただ、まだエラーが出ます。もう少し考えてみます。

回答No.3

happypoint

こんにちは。

ちょっと質問の意味がわかりにくかったです。
こういうことでしょうか。
やってみてください。

　=Sheet1!A1
という式を入れたシートを、シートごとコピーすれば、
新しくコピーしたシートにも最初から同じ数式が入ります。

コピーの仕方は、
まずもとになるシートのシート名が書いてある部分（ワークシートタブ）を右クリックします。

ポップアップメニューから、
「移動またはコピー」を選びます。

小さいダイアログが現れますので、
「コピーを作成する」にチェックを入れてOKします。

補足

すみません。ちょっと違うようです。
複数のシートあって、それを最初なり最後なりの一枚にまとめたいのです。その集計用のシートに入力する方法を探しています。（一個一個手作業ならできますが。）

回答No.2

ja7awu

シートが未だ準備されていないときのエラー処理を考慮すると

=IF(INFO("numfile")<ROW(),"",INDIRECT("Sheet" & ROW() & "!A2"))

補足

何度もありがとうございます。
分かりません。
この式だと、どこが参照先のセル、シートを表しているのでしょうか。

回答No.1

ja7awu

Sheet1のA2に下記の式を入れ、参照するシート数分下にコピーします。

=INDIRECT("Sheet" & ROW() & "!A2")

補足

すみません。私の書き方が悪かったのですが、Sheet1のA2に入れるというのは、集計するシートに入れるということでしょうか？だったらなぜA2なんでしょうか？