Excelで離れたセルへのデータ貼り付け

質問

【前提条件】
Excelで、データ表があります。
横軸に年月、縦軸に品目名が入ったもので、品目は１０００品程度あります。

　，Ａ，Ｂ，Ｃ，Ｄ，Ｅ，Ｆ
１，品目，４月，５月，６月，７月，８月
２，あ，60，26，18，13，25
３，い，88，17，15，72，25
４，う，9，25，28，83，60
５，え，24，90，35，85，86
６，お，35，91，4，31，64
７，か，94，82，70，22，68
・
・

（実際には、品名は数字とアルファベットの羅列です。
　また月は１２か月分あります。）

このデータはExcelファイルになっており、アップデートされていきますが、
次の月は５月から始まって、４月で終わるというようになっています。
さらに、品名は順不同の（先月と今月では、品名の並び順が違う）データです。

月々の数値が以降１２か月分ゼロの場合は、その品目そのものがデータ表に載りません。
（先月あったものが今月無くなることがある、またその逆もある。）

また、作成日がファイル名に入るため、毎月ファイル名が異なります。

このデータを用いて、全く別のExcelファイルに下記の表を作りたいと思います。
ワークシートは何枚にも分かれており、以下のような感じです。

［Sheet 1］
，Ａ，Ｂ，Ｃ，Ｄ，Ｅ，Ｆ，Ｇ
１，品目，－，４月，５月，６月，７月，８月
２，あ，目標，60，26，18，13，25
３，　，実績，　，　，　，　，　
４，　，差異，　，　，　，　，
５，え，目標，24，90，35，85，86
６，　，実績，　，　，　，　，　
７，　，差異，　，　，　，　，
・
・

［Sheet 2］
，Ａ，Ｂ，Ｃ，Ｄ，Ｅ，Ｆ，Ｇ
１，品目，－，４月，５月，６月，７月，８月
２，い，目標，60，26，18，13，25
３，　，実績，　，　，　，　，　
４，　，差異，　，　，　，　，
５，か，目標，94，82，70，22，68
６，　，実績，　，　，　，　，　
７，　，差異，　，　，　，　，
・
・

［Sheet 3］
，Ａ，Ｂ，Ｃ，Ｄ，Ｅ，Ｆ，Ｇ
１，品目，－，４月，５月，６月，７月，８月
２，う，目標，9，25，28，83，60
３，　，実績，　，　，　，　，　
４，　，差異，　，　，　，　，
５，お，目標，35，91，4，31，64
６，　，実績，　，　，　，　，　
７，　，差異，　，　，　，　，
・
・

差異の部分には計算式が入っており、実績を記入していくと差異が計算されるようになっています。

また、品目は、ある決まったルールに基づいて、シートに分類されています。

そこで質問です。

オートフィルタを使って元のデータ表から必要な部分を表示させ
（ある法則に従って品名が付けられているので、あるシートに載せたいデータだけを
表示させることは可能）、その部分を一括でコピー＆ペーストしようとしても、
２行空けたペーストはできません。

１行１行コピー＆ペーストしていけばこの表の作成は可能なのですが、時間がかかって
仕方ありません。

一発でコピー＆ペーストできるような工夫はできませんか？

なお、ExcelのバージョンはXP（2002）です。

回答No.4

kagakusuki · ベストアンサー

次の様な方法は如何でしょうか？

　まず、「全く別のExcelファイル」に、新しいワークシートを作成して、そのワークシート名を例えば「元データ」等として下さい。
　次に、Sheet1～Sheet3において、目標値を表示させる全てのセルに、次の様な関数を入力して下さい。

=IF(OR(INDEX($A:$A,ROW())="",INDEX($1:$1,COLUMN())=""),"",IF(COUNTIF(元データ!$A:$A,INDEX($A:$A,ROW()))*COUNTIF(元データ!$1:$1,INDEX($1:$1,COLUMN())),IF(VLOOKUP(INDEX($A:$A,ROW()),元データ!$A:$M,MATCH(INDEX($1:$1,COLUMN()),元データ!$1:$1,0)-COLUMN(元データ!$A$1)+1,FALSE)="","",VLOOKUP(INDEX($A:$A,ROW()),元データ!$A:$M,MATCH(INDEX($1:$1,COLUMN()),元データ!$1:$1,0)-COLUMN(元データ!$A$1)+1,FALSE)),"-"))

　その上で、基となる「データ表」をコピーして、元データシートのA1セルの所が「データ表」の左上の隅になる様に貼り付けますと、Sheet1～Sheet3の各シート上に目標値が表示されます。（この操作は、コピーしたセルの挿入ではなく、通常の貼り付けで行って下さい）
　因みに、基となる「データ表」をコピーする際に、フィルターを使用して、必要となるデータのみをコピーした方が、「全く別のExcelファイル」のデータ量の節約になるかと思います。

　この方法ならば、

>毎月ファイル名が異なります。

という問題に関しましても、前月の「全く別のExcelファイル」のコピーファイルを作成し、そのコピーファイルの元データシートの古いデータを全て消去して、今月のデータを貼り付けるだけで済む筈です。

回答No.5

satoron666

ちなみに、VBAとして回答しています。

>私の勘違いだといけないのですが、これだと「データ表からデータを貼り付ける」ではなく、
>「データ表にデータを貼り付ける」事になってしまわないですか？

>データを『可視セルにのみ貼り付け』も可能ですか？

良くわかりません。
可視セルを貼り付けることができればよかったのではないでしょうか。

オートフィルターをかけ、
可視セルのコピー
そして、2行あけて貼り付けしていけば良い話じゃないですか？

貼り付け現在位置を数え、
フィルター抽出結果が何件になったかを取得
Result=WorksheetFunction.Subtotal(３、Range(”A:A"))'A列の可視行を数えます。
Currentregonでもいいと思います（スペル合ってるか不明）

やりたいことは未だよく分かっていないのですが、
B列が毎回入っている前提として考えると

１．オートフィルターで必要な部分のみ表示
２．可視セルをコピー
３．貼り付け位置の最終行を取得　（1回目ならA1とかにする）
４．可視セルを貼り付ける
５．B列に1列挿入（1回のみ)
６．目標、実績、差異と入力

これを繰り返せば良い話では？
たぶんですが。

お礼

私自身が使うだけならば、ＶＢＡで良いのですが、
他の人が使うため、できれば数式だけで済まそうと思います。

ご回答ありがとうございました。

回答No.3

chonami

>式の参照先を置換するという事でしょうか？

はい。それでもいいと思います。問題ありますか？
日付によってファイル名が違うという事はその日付をどこかで参照させるようにしてINDIRECT関数などを使う事も可能でしょうし、元ファイルにする用のブックを作っておいてデータだけそのファイルに貼り付けるというような方法もあると思います。

工夫すればいくらでも方法はありますが。

お礼

地道に数式を入れていこうと思います。

ありがとうございました。

回答No.2

chonami

商品名に被りがないなら、VLOOKUP関数とかでできないんでしょうか？

補足

参照するためのデータ表は、まとめたいExcelブックとは別のブックです。
また、そのデータ表のファイルネームは毎月違います。

式の参照先を置換するという事でしょうか？

回答No.1

satoron666

Excel2003の場合ですが、回答します。

Selection.SpecialCells(xlCellTypeVisible).Select '可視セルの選択
Selection.Copy Sheets(Worksheet_Name).Range("A1") '可視セルコピー

こういう方法でしょうかね？
オートフィルタは出来るということですので、
この方法で良いかと思います。

ただ、可視セル全て選択してしまっているため、
見出し部分の行を削除するか、
selectする範囲を変更するかで対応いただけるかと思います。

他にいい回答が出るかもしれませんが、
参考まで＾＾

補足

私の勘違いだといけないのですが、これだと「データ表からデータを貼り付ける」ではなく、
「データ表にデータを貼り付ける」事になってしまわないですか？

データを『可視セルにのみ貼り付け』も可能ですか？