Excel2010のプログラムで200人分のシートを簡単に表示する方法は？

質問

• Excel2010のプログラムを使用して、元帳となるシートから個人の情報を連続するシートに表示したい場合、マクロを使用せずに簡単な方法はないでしょうか？元帳の情報は３行で構成されており、同じ列に３行飛びでデータが入っています。
• 最初のシートに一人目の表示スタイルを設定し、シート参照を使用して元帳からデータを取得する方法を試しました。しかし、200人分の処理をするのは手間がかかります。同じシートであればINDIRECT関数とオートフィル機能を使用して簡単に元帳の情報を取得することができますが、連続するシートではオートフィル機能が使用できません。
• Excel2010のプログラムで200人分のシートを簡単に表示する方法が知りたいです。マクロは使用できないため、他の方法を探しています。連続するシートに個人の情報を取得する方法や、オートフィル機能を使用しない方法があれば教えてください。

回答No.5

kagakusuki

>元帳にある個人の情報は３行で構成されています。

　３行で構成されているという事は、データが存在しているのは必ずしも1列のみとは限らず、例えば1人の個人情報が、3行×5列＝15セルの範囲に入力されているという様な事になっている可能性もある訳ですね？
　そして、それらの情報を個人ごとのシートに表示する際には、必ずしも元データの表と同じ順番に表示すると限ってはいないのではないかと思います。

　それから、マクロが使えないとなりますと、ワークシート関数を使った方法でも可能な何らかの手法で、何番目のシートであるのかを判定出来る様にしなければなりません。
　そこで、シート名が多少格好悪くなりますが、コピーシートを作った際に、コピーシートのシート名が、元のシート名の後に括弧付きの数字が付いたシート名となる（例えばSheet1のコピーシートのシート名はSheet1 (2)等の様になる）事を利用して、(　)内の数字が幾つであるのかによって、何番目の人物のデータを表示させれば良いのかを判断するという方法は如何でしょうか？
　例えば、Sheet2 (4)というシート名のシートには、Sheet1の元帳の中の上から4番目に記されている人物のデータを表示する、といった具合です。
　只、例えば元帳の上から5番目に田中さんのデータが入力されているとして、その田中さんのデータを表示しているシートに、誰のデータを表示するためのシートであるのかを解り易くしようとして、「Sheet2 (5)」となっていたシート名を「田中」等の様に変更してしまいますと、括弧付きの数字が無くなってしまい、元帳の何番目のデータを表示すれば良いのかを判断出来なくなりますので、シート名は「田中(5)」の様に括弧付きの数字を残した形式としなければなりません。
　それと、同姓の人物が複数存在した場合において、区別するために、「鈴木(A)(6)」、「鈴木(I)(7)」の様に、括弧内に数字以外の文字が入っていたり、「佐藤(2)(8)」の様に「(」が複数回現れている様な形式のシート名にした場合には、括弧内の数字を判断出来なくなりますから、末尾の（）付きの数字の他にも括弧を使う場合には、「鈴木[A](6)」、「鈴木〈I〉(7)」、「佐藤【2】(8)」等の様に、( )以外の種類の括弧で囲う様にして下さい。
　因みに下の添付画像の例では、3行1組のデータの中の上から1番目の行におけるA列のセルには、通し番号が振られていますが、これはどのデータが何番目のデータなのかを、添付画像を見た人間に判り易くするために振ったものに過ぎず、この番号自体は個人ごとのデータを表示する際に何の役割も果たしてはおりませんから、例えこのセルに通し番号ではない別のデータが入力されていたとしても、動作上は何の問題もありません。

　さて、個人ごとのシートの具体的な作成方法は以下の様になります。

　今仮に、元帳の表がSheet1のA列～C列に存在していて、その5行目は項目名が入力されていて、実際のデータは6行目以下に入力されているものとします。

　まず適当な未使用のシート、例えばSheet2のシート名を

Sheet2 (1)

等の様に「(1)」を末尾に付けた名称に変更して下さい。
　尚、この場合、別に元の「Sheet2」を残す必要は無く、「(1)」が末尾に付いてさえいれば良いのですから、「Sheet2 (1)」ではなく、「個人(1)」とか「データ(1)」等の様に変えてしまっても構いません。
　次に、Sheet2 (1)シートの「データを上書きされたり、セルを削除されたりする恐れが少ないセル」（ここでは仮に、Sheet2 (1)シートのA3セルを使うものとします）に、次の関数を入力して下さい。

=IF(ISNUMBER(1/(INT(REPLACE(SUBSTITUTE(CELL("filename",A3),")"," "),1,FIND("(",CELL("filename",A3),FIND(".xls",CELL("filename",A3))),)+0)>=1)),INT(REPLACE(SUBSTITUTE(CELL("filename",A3),")"," "),1,FIND("(",CELL("filename",A3),FIND(".xls",CELL("filename",A3))),)-1)*3+ROW(Sheet1!$A$5),ROWS(A:A)+1)

　この関数は、シート名の末尾に( )付きの数字が付いている場合には、元帳の3行1組の行範囲の組の中で、上から数えて「括弧内の数字」番目の組の上端の行の1つ上の行番号を返す関数です。
　処で、上記の関数を入力したSheet2 (1)シートのA3セルは、添付画像の例では「通し番号」という項目名を表示するために使用されているセルとなっています。
　これは、表示する必要のない数値がシート上に表示されていますと見苦しいため、その様な見せなくとも良いデータは非表示にしたい処なのですが、単に非表示にしただけでは、誤ってセルを削除してしまったり、別のデータを上書きしてしまったりする恐れがありますので、項目名が表示されているセルであれば、データを上書きされたり、セルを削除されたりする恐れが少ない様に思えたため、敢えて、項目名が表示されているセルに、「上から何行目以下のデータを参照すれば良いのか」を求める関数を入力している訳です。
　それで何でSheet2 (1)シートのA3セルに行番号を示す数値ではなく、「通し番号」という項目名が表示されているのかと言いますと、セルの書式設定の表示形式を[ユーザー定義]の

"通し番号"

としているからです。
　この様な書式を設定しますと、セルに数値データが入っている場合には、その数値がどんな値であっても、「通し番号」という文字列が表示される様になります。
　因みに、もし、決まった文字列を表示させるのではなく、単純にセル内のデータを非表示とする場合には、セルの書式設定の表示形式を[ユーザー定義]の

;;;

という設定にします。

　次に、Sheet2 (1)シートにおける「元帳の3行1組のデータの中の上から1番目の行におけるA列のセルの値を表示させるセル」（添付画像の例ではB3セル）に、次の関数を入力して下さい。

=IF(ISERROR(1/(INDEX(Sheet1!$A:$A,$A$3+1)<>"")),"",INDEX(Sheet1!$A:$A,$A$3+1))

　同様に、「3行1組の中の2番目のA列のセルの値を表示させるセル」（添付画像のB5セル）に、次の関数を入力して下さい。

=IF(ISERROR(1/(INDEX(Sheet1!$A:$A,$A$3+2)<>"")),"",INDEX(Sheet1!$A:$A,$A$3+2))

　同様に、「3行1組の中の3番目のA列のセルの値を表示させるセル」（添付画像のB6セル）に、次の関数を入力して下さい。

=IF(ISERROR(1/(INDEX(Sheet1!$A:$A,$A$3+3)<>"")),"",INDEX(Sheet1!$A:$A,$A$3+3))

　同様に、「3行1組の中の1番目のB列のセルの値を表示させるセル」（添付画像のB4セル）に、次の関数を入力して下さい。

=IF(ISERROR(1/(INDEX(Sheet1!$B:$B,$A$3+1)<>"")),"",INDEX(Sheet1!$B:$B,$A$3+1))

　同様に、「3行1組の中の2番目のB列のセルの値を表示させるセル」（添付画像のB7セル）に、次の関数を入力して下さい。

=IF(ISERROR(1/(INDEX(Sheet1!$B:$B,$A$3+2)<>"")),"",INDEX(Sheet1!$B:$B,$A$3+2))

　同様に、「3行1組の中の3番目のB列のセルの値を表示させるセル」（添付画像のB8セル）に、次の関数を入力して下さい。

=IF(ISERROR(1/(INDEX(Sheet1!$B:$B,$A$3+3)<>"")),"",INDEX(Sheet1!$B:$B,$A$3+3))

　同様に、「3行1組の中の1番目のC列のセルの値を表示させるセル」（添付画像のB9セル）に、次の関数を入力して下さい。

=IF(ISERROR(1/(INDEX(Sheet1!$C:$C,$A$3+1)<>"")),"",INDEX(Sheet1!$C:$C,$A$3+1))

　同様に、「3行1組の中の2番目のC列のセルの値を表示させるセル」（添付画像のB10セル）に、次の関数を入力して下さい。

=IF(ISERROR(1/(INDEX(Sheet1!$C:$C,$A$3+2)<>"")),"",INDEX(Sheet1!$C:$C,$A$3+2))

　同様に、「3行1組の中の3番目のC列のセルの値を表示させるセル」（添付画像のB11セル）に、次の関数を入力して下さい。

=IF(ISERROR(1/(INDEX(Sheet1!$C:$C,$A$3+3)<>"")),"",INDEX(Sheet1!$C:$C,$A$3+3))

　そして最後に、Sheet2 (1)シートのコピーシートを、人数分作成して下さい。

　以上です。

お礼

どうも詳細な回答ありがとうございました。皆さん考えが深いですね。マクロを使えないと記述がより複雑になりますね。
個人の情報として20セルを使っています。各行の情報セル数はまちまちです。
私のレベルと目的では「A88No.8」さんの回答が一番向いていました。現在実施し、実用に供しています。
今後何かありましたらよろしくお願いします。