Excel2010のプログラム

質問

あるシートが元帳となっていて個人の情報（含む計算値）が入っています。このシートに続く、連続するシート（約200）に個人の情報を元帳より取ってきて（参照）表示したいのです。元帳にある個人の情報は３行で構成されています。したがって、個人の情報は３行飛びで同種のデータが入っています（同列）　私はマクロは使えません。最初のシートに一人目の表示スタイルを設定してして、シート参照でデータを元帳からとってくるようにしました。二人目は一人目のシートをコピーした後、シートのセル参照の値をすべて3飛びの行に直して使ってみました。機能はしましたが、とても200人分この処理をする気にはなれません。同一のシートであればINDIRECT関数とオートフィル機能で簡単に元帳の情報がとれるのですが、各シート（連続するシート）ではオートフィル機能が使えません。
どなたか簡単に200人分のシートが表示できる方法を教えてください。

回答No.3

A88No8 · ベストアンサー

こんにちは
＃２の補足です。
回答をアップしてから確認したら、画像では式が見えませんね(^^;
なので式を転記しておきますm(..;m

・3つ組み1番目
=INDEX(data!$A$2:$A$16,($A$1*3-2))
・3つ組み2番目
=INDEX(data!$A$2:$A$16,($A$1*3-2)+1)
・3つ組み3番目
=INDEX(data!$A$2:$A$16,($A$1*3-2)+2)

お礼

ご回答ありがとうございます。いろいろな考え方があるものだと感心しています。当方理解力が不足していますので、確認させてください。

Sheet1が既存の元帳とします。
1.Sheet2ではINDEX関数を使ったセル参照式を必要な列の分だけ入力する。質問ですが、貴殿のセル参照式（INDEX関数）にはSheet を参照（この場合Sheet1)する記述がありませんが？
2.Sheet2をSheet3～Sheet202にコピーする。（200件を扱う場合）
3.各Sheet の$A$1に数字（1～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」さんの回答が一番向いていました。現在実施し、実用に供しています。
今後何かありましたらよろしくお願いします。

回答No.4

A88No8

こんにちは
＃３の補足です。

>Sheet1が既存の元帳とします。
　データのあるシート名称ですね。

>1.Sheet2ではINDEX関数を使ったセル参照式を必要な列の分だけ入力する。質問ですが、貴殿のセル参照式（INDEX関数）にはSheet を参照（この場合Sheet1)する記述がありませんが？
　画像1のデータシート名称は、（「Sheet1」を）「data」に変更している前提で書きました（「…画像１がdataシートの様子…」）。
　そのため INDEX関数のパラメータが「data!$A$2:$A$16」になっています。
　データのあるシート名称が「Sheet1」ならば、「Sheet1!$A$2:$A$16」となるでしょう(^^)

>2.Sheet2をSheet3～Sheet202にコピーする。（200件を扱う場合）
　はい、式そのものは変わらないので、上記のようにコピーするだけです。

>3.各Sheet の$A$1に数字（1～200）を入力して、参照する元帳のセルの行数を指定する。
　はい、その通りです(^^)

お礼

「A８８No.８」さんの提示された内容を実施し、実用に供しています。
各個人の情報セルとして２０セル使っていますが、式が同じなのでコピーし、列指定のみ修正して楽に記述できました。
行指定のみでは$A$1に数字を入れないとき、上の行のタイトルが表示されるのでIF関数を追加して解決し、問題なく動作しています。
ありがとうございました。

回答No.2

A88No8

こんにちは
内容が良く理解できていないので外していたらごめんなさい。

以下のように把握したとの想定での助言です。

・最初のシート（以下「data」シート）のある列に3行単位でデータが入っている
・他のシートに3行単位のデータを（例えば列方向に3つのデータとして）楽に転記（リンク）したい。

添付の画像は以下の内容です。
画像１がdataシートの様子、画像2が1番目の3行一組のデータを取り出した状態とその式、画像3が2番目の3行一組のデータを取り出した状態とその式。

肝は、3つ一組のデータの最初はどこから始まっているかを求めることだと思いました。
画像4は$a$1に記録した3つ組みデータの順序数に対して最初のデータは何番目になるかを求めた結果です。
求める式は 「$a$1*3-2」だけです。

後は求めた値と「INDEX関数」を使って「data」シートのデータを読み出しました。
従ってシートは数式の入った元シートをコピーするだけでOK。操作はコピーしたシートの $a$1 に１から200までの順序数を入れて上げればOK。

お役に立てれば幸いです(*^_^*)

回答No.1

suzuko

マクロが使えないということでしたら、

元帳シートの余白に「＝A1」のように個人詳報のセルを読み込み、下にオートフィル。

3行ごと切り取り、最初のシートに張り付け。後は「切り取り、貼り付け」の繰り返し。

「セル参照の値」を入れ直すよりは簡単かと…

お礼

早速の回答ありがとうございました。うーん、やはりSheet毎に「切り取り、貼り付け」の繰り返しになりますか。一応１０人分ほどこの方法を試してみました。私のやり方より効率がよく直しミスが発生しないので良い方法だと思いました。２００人分くらいなのでこの方法で行けるでしょう。今少し待ってみて、良い提案がなければこの方法で行きたいと思います。ありがとうございました。