EXCELでINDIRECTを使いたいのですが？

質問

一度質問させていただき、INDIRECTを使うことをおしえていただきました。
でも、SHEET1の10行3列の規定のフォーム（表）に、SHEET2の500名分のデータを差し込みたいのですが、どのようにしたらよいでしょうか？

SHEET1 は、5名までの個人データの規定フォーム（表）2行3列が1人分データ
　　A　　　　　B　　　　　C　１ページ　　　
1名前　　会社住所　　TEL　　
2　　　　　本人住所　　TEL　　　
3名前　　空白
4　　　　　本人住所　　TEL
5名前　　会社住所　　TEL
6 　　　　 本人住所　　TEL
7名前　　空白　　　　　
8　 　　　 本人住所　　TEL
9名前　　会社住所　　TEL
10 　　　 本人住所　　TEL

SHEET2は、500名分のデータ(リスト）
　　A　　B　　　　　　C　　　
1名前　　会社住所　　TEL　　
2　　　　　本人住所　　TEL　　　
3名前　　空白
4　　　　　本人住所　　TEL
5名前　　会社住所　　TEL
6 　　　　 本人住所　　 TEL
7名前　　空白　　　　　
8　 　　　 本人住所　　TEL
9名前　　会社住所　　TEL
10 　　　 本人住所　　TEL
　　　　　：
　　　　　：
　　　　 455名
どうかよろしくお願いします。

回答No.7

imogasi · ベストアンサー

補足頂いたが、良く判らない点があります。
しかしSheet2－－＞Sheet１にデータを写すのだと解釈して
やって見ました。
（データ）
Sheet2の（本当は」６列のようだが、３列に簡略化した。
A2:C12に（文字列を入れた。面倒なので氏名、住所の代わりと位置関係が判りやすいように）
a1 a2 a3
a4 a5 a6
b1 b2 b3
b4 b5 b6
c1 c2 c3
c4 c5 c6
d1 d2 d3
d4 d5 d6
e1 e2 e3
e4 e5 e6
f1 f2 f3
（関数を作成）準備作業。Sheet1で使う関数を複雑にしないために作ったユーザー関数。
VBEの標準モジュールに
Function g(r)
Select Case (r - 1) Mod 4
Case 1
g = 0
Case 2
g = 1
Case 3
g = 2
Case 4
g = 0
End Select
End Function
'-------
Function h(r)
h = Int((r - 2) / 4)
End Function
を作る。何をやっているか判っていただくために
E2セルに=g(row())といれ、F2に=h(row())
といれ下に複写すると
0 0
1 0
2 0
0 0
0 1
1 1
2 1
0 1
0 2
1 2
2 2
0 2
0 3
となります。４行でF列数字が変わる、E列は第１，４行が０で、２行目が１、３行目が２です。以上は説明のために
E,F列に出したもので、本題とは直接関係ありません。
（関数式）
Sheet1のA2に
=IF(g(ROW())=0,"",OFFSET(Sheet2!$A$1,h(ROW())*2+g(ROW()),0))
といれ、下へ複写します。
するとA2:A16に((S)は空白行を示す仮の記号）
(S)
a1
a4
(S)
(S)
b1
b4
(S)
(S)
c1
c4
(S)
(S)
d1
d4
となります。
B2には最後の引数０を１に、C2には最後の引数０を２に
して下へ複写します。
B2の式は=IF(g(ROW())=0,"",OFFSET(Sheet2!$A$1,h(ROW())*2+g(ROW()),1))
（結果）
A2:c16は

a1 a2 a3
a4 a5 a6

b1 b2 b3
b4 b5 b6

c1 c2 c3
c4 c5 c6

d1 d2 d3
d4 d5 d6
となりました。

お礼

大変ご足労おかけしてしまい、申し訳ありませんでした。
勉強させていただき、本当にありがとうございました。

回答No.6

comv

#3 #4 の　comv　です

#4の式は　１０名と最初の質問の１０行を取り違えていました！５名分なんですね

式中の
　*30　（１０人×３行）　→　*15　（５人×３行）
コピー範囲を
　５０行目　→　２５行目
に変更して下さい

－－－－訂正－－－－－－－
Sheet1
・Ａ１に
　=TEXT(INDEX(Sheet2!$A:$E,($G$1-1)*15+ROUNDUP(ROW(A1)*3/5,0),COLUMN(A1)),"#;@")
　Ｂ１：Ｅ１へ複写
　Ａ３：Ｅ４へ複写

・Ａ１：Ｅ５を選択
　２５行目まで下へフィルドラッグ

お礼

教えていただきありがとうございました。

回答No.5

imogasi

＃２です。＃２の補足について、さらに質問します。
●Sheet２の様子はわかります。
１．3行目　空白,空白,空白　,入力　,空白,空白
の入力欄には何が入っているのでしょうか。
２つ目の連絡先ですか。
２．１人２行で５００人分あると言うことは、１（見出し行）＋２＊５００行＝１００１行現在データがあると言うことですね。
これは完成したものですか。１００２行以下、今後日々月月増えないのですか。
社員番号は入ってないのですか（不思議）。
●Sheet1について
１．「6人目から、自動的に2ページと変わっていくようにしたいのです」とは
エクセルは単一の表です。本来ページの考えはありません。印刷する時にはページ区切りを入れて印刷して2ページ・３ページ・・・・とする（なる）ことが出来ますが、表の上ではページを作るとはどうするのでしょう。罫線を使ったりして、それらしく見せることは出来ますが、次のページの先頭は直下行からでなく、下余白行を置き、次ページの上余白行も置くのではと思いますが、
そうではないのですか。
２．質問はただSheet２のデータの項目を並べかえる（組みかえる）だけですか。特徴から察すると
第1行目だけは、見出しで特別扱いである。
Sheet1の２行目以下は
（A)１余白行を入れる
（B)Sheet2の１人分の上の行をそのままもってくる
（C)Sheet2の１人分の下の行をそのままもってくる
（D)１余白行を入れる
のパターンの、４行の繰り返しをすれば良いのですか。
３。Sheet1は、ある社員の情報を見たい時は、探す仕掛けが何もなく、探しにくいですがそれで良いのですか。
アクセスなどなら、Sheet２は「テーブル」ファイルに相当し、フォームは１名分だけ（または５名）見せたり・入力させる「フォーム」と検索システムが引っ付いた仕組みになると思いますが、それと随分違うようなので、想像がしにくいです。余談ながらエクセルで人事関連のシステムを作ると、ただ表にしただけになり、活用が難しいように思います。
４．最終次の段階で印刷するのですか（前回答で質問したが答えなし）

お礼

回答ありがとうございます。
連絡遅れまして申し訳ありません。

SHEET2
1.2つ目の連絡先　出身地(実家)などです。
2.はい、見出し行は、最初の行の1行目だけで、１（見出し行）＋２＊５００行＝１００１行のデータです。
リストに、人を採用すれば必然的に増え、退職すれば、減ります。
入力された完全な社員データ（社員コード）は、別のブックにあり、SHEET1に必要なデータのみコピーしたものがSHEET2にあるのです。
SHEET1
SHEET1の5人分だけが、規定のフォームで、その中に入力していかなければいけないのが現状です。
ですから、2ページ目は、フォームの入力欄の中が、変わっていくのです。
1.以前教えていただいた方の関数を使うと、
　　=INDIRECT("Sheet2!R"&($G$1-1)*5+ROW()&"C"&COLUMN(),0)
でした。
確かに、SHEET1とSHEET2の行が同じ連続したものであったなら、うまく行きましたが、実際の職場のフォームは、SHEET1が、4行分で1人のデータを入力しなければいけない様式となっていますので、上の関数のROW（　）の行に、SHEET2の一人一人のデータの行をそれぞれあてはめてみたのですが、
SHEET2のデータは、SHEET1へ入るが、SHEET1の2ページ目からSHEET2のデータの順序がすべて変わってしまいました。
2.(C)です。
3.SHEET2で、確認する予定です。
4.印刷は、1ページづつ印刷するしかない現状です。

最後に、私の会社で誰が作成したのか、このEXCELのSHEET1のフォームが、規定の様式となってしまっているのです。
せっかく、別のブックで社員全体のデータリストがあるのだから、これを何とか生かしたいと思い、思いついたのです。
ご無理を言って、ご迷惑をおかけしましたが、ご親切に本当にありがとうございました。

回答No.4

comv

再びこんにちは

要は３行単位の元表から５行飛びで取り出せれば
よいのでしょうか？
そして配置は５行単で
　１行目　→　Sheet2１行目
　３行目　→　Sheet2２行目
　４行目　→　Sheet2３行目

を前提で前回の式と可変値だけが違うだけですが

G1にページ値　として

Sheet1
・Ａ１に
　=TEXT(INDEX(Sheet2!$A:$E,($G$1-1)*30+ROUNDUP(ROW(A1)*3/5,0),COLUMN(A1)),"#;@")
　Ｂ１：Ｅ１へ複写
　Ａ３：Ｅ４へ複写

・Ａ１：Ｅ５を選択
　５０行目まで下へフィルドラッグ

で如何でしょうか

回答No.3

comv

こんにちは

なんか以前似たようなご質問にレスした記憶が
あるような(^^
INDIRECT()　も　OFFSET()　も　面白いくらい同じ処理ができる関数で
他ブック参照時は対象が開いていないと不可
自動で再計算処理をするためブックを開いて何もせず
に閉じる時でも「保存しますか？」のMsgが出るのも
共通している双子のような

て前置きが長くなりましたが
既に両関数での式は提示されているのでINDEX()での例を
Ｄ１に「何人目から」の値があるとして
セルＡ１に
=TEXT(INDEX(Sheet2!$A:$C,($D$1-1)*2+ROW(A1),COLUMN(A1)),"#;@")
必要な範囲まで複写

式中の　*2　が２行単位の意味です

補足

確かに以前教えていただきました。その節は、大変お世話になり、ありがとうございました。

教えていただいたINDIRECT関数ですが、いざ、職場のフォームであわせるとデータの順番がずれるようになってしまい、うまくいかないのです。

私の説明がわかりずらくてすみません。
実は、私の会社の社員データを入力するための入力(様式)フォームに、すでに別のシートに500名分の社員データが作成されたリストを、社員データ入力フォームに自動的に500名分を差し込みたいと思っています。
フォームは、会社の規定の様式ですから、絶対にくずすことができません。
例を挙げて教えていただこうと思ったのですが、会社のフォームを忠実にお知らせします。(EXCELを使っています。)

私の会社のフォームの様式は、SHEET1にあります。
SHEET1　
5人分社員データを入力するための入力表フォーム（様式）があり、2行から5行までが1人目の入力欄で、6～9行目が2人目、10～13行目が3人目、14～17行目が4人目、18行～21行が5人目です。ここでは、表ができないので、カンマ区切りで1列と示します。

1行目　名前,役職,所属課,連絡先,年齢,性別　1ページ
2行目　空白,空白,空白　,空白　,空白,空白　　
3行目　入力,入力,入力　,入力　,入力,入力
4行目　空白,空白,空白　,入力　,空白,空白
5行目　空白,空白,空白　,空白　,空白,空白
入力となっている箇所は、入力欄で、これが1人目の入力箇所です。SHEET1は、5人分です。6人目から、自動的に2ページと変わっていくようにしたいのです。

SHEET2は、500名のデータリストがあります。
1行目　名前,役職,所属課,連絡先,年齢,性別
2行目　入力,入力,入力　,入力　,入力,入力
3行目　空白,空白,空白　,入力　,空白,空白
2～3行（2行）で1名のデータです。また、4行目から、2行ずつ1名づつの500名分のリストです。

どうか、この説明でご理解いただけたでしょうか？
INDIRECTを利用することを、以前、教えていただいたのですが、SHEET1の2ページ目からSHEET2のデータがずれていきます。どうかお助けください。
よろしくお願いします。

回答No.2

imogasi

差しこみと言うより、抜き出したいと言うことでしょうか。Sheet２のスタートのN人目を与えて、Sheet2上で、連続した続きの５人分をSheet2に持ってきたいと言うことですか。
N人目を与えるのであればスタート行は（N-1)*2+1に変えれば良い。
INDIRECTは必要ないと思います。
スタートをN人目から５人分として、E1セルにNが入っているとして
Sheet1のA1に
=OFFSET(Sheet2!$A$1,($E$1-1)*2+ROW()-1,COLUMN(A$1)-1)
を入れ、A1をC1まで複写。
A1:C1を範囲指定し、＋ハンドルをだし、A2:C１０まで複写
する。
式の中で－１しているのは、OFFSETが「ずれ」数を指定するものなのでそうなります。３行目は１行目から、「ずれ」は２になります。
OKWEBであるなら、元の質問の番号等も掲げていただくと
判りやすい。
（質問文例）「Sheet２に２行（３列）が一組みで１人分のデータが５００人分あります。Sheet１に何人目から持ってくるかを指定し、そこから以下５人分をSheet1に持ってくる方法は」
それと多分その後に印刷作業が待っていると思うが、
そうなるとVBAを使うべき問題だと思います。

補足

私の質問の説明がわかりずらくてすみません。
実は、私の会社の社員データを入力するための入力(様式)フォームに、すでに別のシートに500名分の社員データが作成されたリストを、社員データ入力フォームに自動的に500名分を差し込みたいと思っています。
フォームは、会社の規定の様式ですから、絶対にくずすことができません。
例を挙げて教えていただこうと思ったのですが、会社のフォームを忠実にお知らせします。(EXCELを使っています。)

私の会社のフォームの様式は、SHEET1にあります。
SHEET1　
5人分社員データを入力するための入力表フォーム（様式）があり、2行から5行までが1人目の入力欄で、6～9行目が2人目、10～13行目が3人目、14～17行目が4人分、18行～21行が5人目です。ここでは、表ができないので、カンマ区切りで1列と示します。
フィールド名に、
1行目　名前,役職,所属課,連絡先,年齢,性別　1ページ
2行目　空白,空白,空白　,空白　,空白,空白　　
3行目　入力,入力,入力　,入力　,入力,入力
4行目　空白,空白,空白　,入力　,空白,空白
5行目　空白,空白,空白　,空白　,空白,空白
入力となっている箇所は、入力欄で、これが1人目の入力箇所です。6人目から、自動的に2ページと変わっていくようにしたいです。

SHEET2は、500名のデータリストがあります。
1行目　名前,役職,所属課,連絡先,年齢,性別
2行目　入力,入力,入力　,入力　,入力,入力
3行目　空白,空白,空白　,入力　,空白,空白
2～3行（2行）で1名のデータです。また、4行目から、2行ずつ1名づつの500名分のリストです。

どうか、ご理解いただけたでしょうか？
INDIRECTを利用することを、以前、教えていただいたのですが、SHEET1の2ページ目からSHEET2のデータがずれていきます。どうかお助けください。
よろしくお願いします。

回答No.1

noname#5187

今ひとつ用途が分らないのですが・・・・

Sheet2のD1セルに何番目の人から5人分を表示するかを
指定するものと想定しての回答です

Sheet1のA1セルに以下の数式を入力してA1～C10に数式をコピーします

=INDIRECT(ADDRESS(($D$1-1)*2+ROW(A1),COLUMN(A$1),,,"Sheet2"))

お礼

教えていただきありがとうございました。