エクセルで複数のシートのデータをまとめるには

質問

エクセルで、book内にsheetが同じフォーム（検査成績書のように）で複数存在している
状態で、データが書かれているものを、同じbook内に新にsheetを作成して、
一覧表のようにして転記したいのですが、簡単にする方法はないでしょうか？
１個１個セルに転記するように番地を入力するのはわかるのですが、すごい手間が
かかるので、もっと簡単に転記する方法はないでしょうか？

回答No.5

KURUMITO · ベストアンサー

回答No3,4です。
シート1や2が縦に並んでいるのに対してお求めのまとめの表は横に並べるとのことでしたね。
回答No4のケースで横に並べる場合にはA1セルに氏名、A2セルに住所、A3セルに年齢、A4セルに性別、A5セルに電話番号と入力します。
そこでB１セルには次の式を入力して右横方向にオートフィルドラッグします。

=IF(COLUMN(A1)<=MAX(Sheet2!$C:$C),IF(COUNTIF(Sheet1!$E:$E,COLUMN(A1))>0,INDEX(Sheet1!$A:$A,MATCH(COLUMN(A1),Sheet1!$E:$E,0)),INDEX(Sheet2!$A:$A,MATCH(COLUMN(A1),Sheet2!$C:$C,0))),"")

B2セルには次の式を右横方向にオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(OR(B$1="",$A2=""),"",IF(AND(COUNTIF(Sheet1!$A:$A,B$1)>0,COUNTIF(Sheet1!$1:$1,$A2)>0),INDEX(Sheet1!$A:$D,MATCH(B$1,Sheet1!$A:$A,0),MATCH($A2,Sheet1!$1:$1,0)),IF(AND(COUNTIF(Sheet2!$A:$A,B$1)>0,COUNTIF(Sheet2!$1:$1,$A2)>0),INDEX(Sheet2!$A:$B,MATCH(B$1,Sheet2!$A:$A,0),MATCH($A2,Sheet2!$1:$1,0)),"")))

これでシート1と2のデータが項目を縦にして表示されます。

お礼

横方向に転記するほうが簡単にできたような気がするのは
気のせいでしょうか？上手くいきました有難うございました。

これを参考に複数枚の同じ帳票シートをまとめシートに
整理してみたいと思います。
出来なかったらまた質問しにきます。有難うございました。

回答No.4

KURUMITO

回答No3です。
シート1で新たにC1に年齢、D1に性別の項目名が作られたとします。
先ほどの作業列はシート1のE2セルに次の式を入力して下方にオートフィルドラッグします。

=IF(COUNTIF(A$2:A2,A2)=1,MAX(E$1:E1)+1,"")

シート2は先ほどと同じと仮定します。
そこでまとめの表のシート3では次のようにします。
A1セルは氏名、B1セルは住所、C1セルには年齢、D1セルには性別、E1セルには郵便番号と項目名を入力します。
A2セルへの入力の式は先ほどと少し違って次の式になります。

=IF(ROW(A1)<=MAX(Sheet1!$E:$E),INDEX(Sheet1!$A:$A,MATCH(ROW(A1),Sheet1!$E:$E,0)),IF(ROW(A1)<=MAX(Sheet2!$C:$C),INDEX(Sheet2!$A:$A,MATCH(ROW(A1),Sheet2!$C:$C,0)),""))

次にB1セルからD1セルまではシート1の項目になりますのでシート1からそれらのデータを自動的に表示させるための式をB2セルには次の式を入力してD2セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF($A2="","",IF(COUNTIF(Sheet1!$A:$A,$A2)=0,"",INDEX(Sheet1!$A:$D,MATCH($A2,Sheet1!$A:$A,0),MATCH(B$1,Sheet1!$1:$1,0))))

ここではVLOOKUP関数を使ってもよいのですが行や列が変わるような場合には私はINDEX関数を使うことにしています。項目の順序が必ずしもシート1と同じでなくても容易にその列を表示してくれます。例えばシート3でC1セルが性別で、D1セルが年齢となってもよいのです。

E列ののデータはシート2になりますのでE2セルには次の式を入力して下方にオートフィルドラッグします。

=IF($A2="","",IF(COUNTIF(Sheet2!$A:$A,$A2)=0,"",INDEX(Sheet2!$A:$B,MATCH($A2,Sheet2!$A:$A,0),MATCH(E$1,Sheet2!$1:$1,0))))

シート2での項目の列が多くなってもシート1と同じように式をたて入力すればよいでしょう。
これらのテクニックをマスターすればエクセルをある程度理解しているといえるでしょう。頑張ってください。

お礼

重ね重ね有難うございます。
頭をすっきり整理させてからチャレンジしてみます。

回答No.3

KURUMITO

簡単な例を示しますので参考に努力してみてください。
例えばシート1ではA1セルに氏名、B1セルに住所とあり、下方にデータがあるとします。
シート2ではA1セルに氏名、B1セルに電話番号とあり、下方にデータがあるとします。
C2シート3がまとめの表でA1セルに氏名、B1セルに住所、C1セルに電話番号と項目名が有るとします。
なお、氏名についてはシート1と2では並びの順序も違って、シート2にはシート1に無い氏名も含まれているとします。
そこでシート3に自動で氏名をすべて表示させるためにはシート1及びシート2に作業列を作って対応することにします。
シート1のC2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(COUNTIF(A$2:A2,A2)=1,MAX(C$1:C1)+1,"")

シート2ではC1セルには次の式を入力します。

=MAX(Sheet1!C:C)

C2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(AND(COUNTIF(Sheet1!A:A,A2)=0,COUNTIF(Sheet2!A$2:A2,A2)=1),MAX(C$1:C1)+1,"")

これで氏名についてはシート1及び2で出てくるすべての氏名に1から番号が付けられます。
そこでまとめのシート3ですがA2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(ROW(A1)<=MAX(Sheet1!$C:$C),INDEX(Sheet1!$A:$A,MATCH(ROW(A1),Sheet1!$C:$C,0)),IF(ROW(A1)<=MAX(Sheet2!$C:$C),INDEX(Sheet2!$A:$A,MATCH(ROW(A1),Sheet2!$C:$C,0)),""))

シート3のB2セルには次の式を入力して下方にオートフィルドラッグします。

=IF($A2="","",IF(COUNTIF(Sheet1!$A:$A,$A2)=0,"",VLOOKUP($A2,Sheet1!$A:B,2,FALSE)))

C2セルには次の式を入力して下方にオートフィルドラッグします。

=IF($A2="","",IF(COUNTIF(Sheet2!$A:$A,$A2)=0,"",VLOOKUP($A2,Sheet2!$A:B,2,FALSE)))

ここでの例を具体的に試験してみて参考になれば幸いです。

お礼

回答ありがとうございます。
頭から煙が出てきそうですが、がんばってみます。

補足

質問者です。試験的にやってみましたが、上手くいきました！すごい！
シート１や２のデータはタテ方向に入力されているものを、ヨコ方向に
転記するには同じ式では無理ですよね？どこをいじれば出来るでしょうか？
初めに説明しておけばよかった・・・。すいません

回答No.2

ponta34

同じフォームを同じ構成だと判断させていただくと
参考URLの方法はいかがでしょう

参考URL：

http://kokoro.kir.jp/excel/tougou.html

お礼

回答有難うございます。
同じフォーム構成なのですが・・・。
チャレンジしてみます。

回答No.1

keithin

しばしば寄せられるご相談ですが，そう上手い方法はありません。

一番簡単にできる方法：
各帳票の「シート名」を「計算可能なルール」に従って命名する運用を義務づける
たとえばSheet1,Sheet2,Sheet3…
たとえばNo0001,No0002,No0003…
たとえば年月日日付

マトメシートのＡ列のＡ２に
Sheet1などの１枚目の帳票シート名を記入
下向けにオートフィルドラッグしてSheet2,3,4…あるいはNo0002,0003,0004…を自動で作成させる

マトメシートのＢ列のＢ２に
=IF(ISERROR(INDIRECT($A2&"!C5")),"",INDIRECT($A2&"!C5"))
のように記入して下向けにコピーすると，各シートのＣ５セルの値を順次並べることが出来ます。

オートフィルドラッグで自動生成できないデタラメなシート名でしか作成できないときは，簡単なマクロを使ってシート名一覧をＡ列に列記させるような手を併用する手もあります。

別の方法：
ご利用のエクセルのバージョンが不明ですが，「データ追跡テンプレート付きアドイン」を追加して帳票をテンプレートとして登録しておくと，テンプレートに記入して保存する都度，組み込まれている自動マクロが勝手に一覧シートに所定のデータを記録していってくれます。

別の方法：
マクロを勉強して，データをさくっと取ってこさせることも出来ます。
サンプル：
sub macro1()
　dim w as worksheet
　dim h as range
　for each w in worksheets
　if w.name <> "まとめシート" then
　set h = worksheets("まとめシート").range("A65536").end(xlup).offset(1)
　h.range("A1") = w.range("C5")　　’C5とD10とF30のセルの値を集めてみる
　h.range("B1") = w.range("D10")
　h.range("C1") = w.range("F30")
　end if
　next
end sub

お礼

やはりマクロが手っ取り早いんですかね？
素人には難しい！勉強してみます。
有難うございました。