(Excel エクセル) 別シートのデータ(行)を、行数を増やして貼り付けたい

(Excel エクセル) 別シートのデータ(行)を、行数を増やして貼り付けたい

既存の表に項目を増やして新たな表を作りました。
中のデータを移行したいのですが、量(行)が膨大で躓いています。

・新規の表は3万行
・既存1行のデータが新規では3行になる
・1行毎にコピペは時間が掛かり過ぎる
・規則性が特殊でオートフィルは不可能

何か良い手はないでしょうか？

質問者からの補足コメント

• 画像を添付(修正)します。
  よろしくお願いします。

  
    補足日時：2020/11/15 12:32

No.8 回答者： mike_g 回答日時： 2020/11/16 11:28

[No.2お礼]へのコメント、

》 「シート同士の参照セルの場所(行列)が違う」の画像を
》 新たに添付しました。
》 この場合「シート2のG9」に入る数式を教えて頂けますと
》 幸いです。
Sheet2!G9: =OFFSET(Sheet1!$E$14,(ROW(A1)-1)/3,COLUMN(A1)-1)
では如何？

No.7 回答者： ずんだしらたま 回答日時： 2020/11/15 21:43

No.2です。

貼り直された表ですと、シート2のg9に下の式をコピーしてください。その後、g9を全項目にもコピペして下さい。
=OFFSET(シート1!E$13,ROUNDUP((ROW()-6)/3,1),0,1,1)

前回作成のシート名を「シート1」としています。

offset関数は基準の位置から行、列をどれだけ移動するかを指定します。
ここでは、基準となるセルをE13としています。同関数の引数については別途調べていただければと思います。

g9にこの式があった場合について説明します。
◽️基準のセル:シート1のE13
◽️行方向（下方向）への移動:1
※先頭行を基準セルから1下がった位置にしたい。g9セルの現在行位置は9です。今回は1行を3行ずつにする便宜上、g11までは同じ位置を参照したい。なので下の式を使用しています。
ROUNDUP((ROW()-6)/3,1)
これがg9にあった場合、row関数の結果は9になるので、(9-6)/3=1となります。
ちなみにg11であれば、(11-6)/3=0.333…→繰り上げで1です。
◽️列方向（右方向）への移動:0固定
◽️高さ:1セルなので今回は1
◽️幅:1セルなので今回は1


なお、これはご存知かもしれませんが、一応…ご存知でしたらすみません。
'$'をつけると絶対参照、つけないと相対参照となり、$が付いているとコピペした時に参照位置がずれません。Dの前に$はつけませんので、例えばシート2のg9をh10に貼り付けた時に下のように調整されます。
=OFFSET(シート1!F$13,ROUNDUP((ROW()-6)/3,1),0,1,1)

No.6 回答者： goomania 回答日時： 2020/11/15 20:04

まず、前回作成したシート1、今回作成するシート2ともに表の左側に作業列が必要です。

何らかの事情で作業列が確保できないときはシート1およびシート2の「値コピー」による複製シートを作成して作業してください。
ご質問者の状況として、前回作成した表の要素項目が一つ追記されたため、今回作成した表に追加した要素が反映されるよう項目追加するための作業が必要な状況だと理解しました。
さらに追加された要素項目のパターンが3パターンあるため、前回作成シートの各行をこの3パターン分に増やす必要が生じているということだと思います。
まず、前回作成したシートの表の左側に作業列として「マッチングキー」の列を作ります。
添付画像①を参照してください。この画像のようにA列を作業列とした場合、作成済みの表の要素パターンを結合したキーを作成するわけです。
添付画像①の例ではA4に
=B4&C4&D4
という数式を記述し、下方向にオートフィルでコピーします。
次に添付画像②のように、今回作成したシートの表の左側にも作業列として「マッチングキー」の列を作ります。
添付画像②の例ではA4に
=B4&C4&D4
という数式を記述し、下方向にオートフィルでコピーします。
前回作成シートと今回作成シートの、このキーが一致する部分に同じデータを埋めてしまうという作戦です。
添付画像②のF4に
=VLOOKUP($A4,前回作成!$A:$I,COLUMNS(今回作成!$A:F)-1,FALSE)
という数式を記述し、下方向と右方向にそれぞれオートフィルでコピーします。
すると、前回シートのデータが必要な部分にコピーされるはずです。
コピーが終了したら、VLOOKUPの数式を記述した全ての部分をコピーし、同じ場所に「値」で「貼り付け」して、表を加工してもコピーしたデータに影響がでないようにします。
この手続きの後、作業列を削除して完了です。

No.5 回答者： めぐみん_ 回答日時： 2020/11/15 10:08

行数の変化はちょっと不思議には思いますよね。

ところで『組』ってのは画像では『１～３』ですが実際には『１～ｎ』とかあるのでしょうかね？
そうすると１行が３行になり且つそれがｎ組であるから全体で３万行なのかなぁ～と。
『組』の最大値（組数）って幾つなのでしょうね。

ボケた初級レベルは寝ボケた回答をしてしまいました。

この回答へのお礼

ありがとうございます。

名称などは実際に作っている物ではなく仮称にしています(恥ずかしいので)
各項目の数ももう少し多いです。

ざっくりですが、シート1に設定した項目数で組み合わせた表で290行
シート2だと28000行弱となります。

「実際の組数」の画像を新たに添付しました。
この場合「シート2のG9」に入る数式を教えて頂けますと幸いです。

お礼日時：2020/11/15 12:34

No.4 回答者： Qchan1962 回答日時： 2020/11/15 00:40

こんばんは、

関数での回答はすでに出ていますが、
表組み（データ）の加工と言う事であるなら、1度処理する事で事足りて
繰り返し頻繁に処理しないと言う事なら、少し処理時間がかかりますが、
VBAで、こんな感じでどうでしょう？
ただし、添付図の例で300行が30000行になると言う事が理解できません。
添付図を見る限り、単純に3行ずつ増え300×3でないのなら、本回答は該当しませんので忘れてください。

＞別シートのデータ(行)を、とあり、添付画像と一致していないので
例は、Sheet1にあるデータをSheet2に加工するものです。書式（数式）等は考慮していません。（書式、データ加工、規則性など不明の為）
また、元データは4行目からデータとします。
Sheet2のA3セルに”前回”など使われていない場合は、上に詰められ
出力されると思います。

検証していませんので念のため、ブックはコピーなどをして、標準モジュールにプロシージャをコピペして試してください。

Sub sample()
Dim i As Long, trgRow As Long
Dim ws1 As Worksheet
Set ws1 = Sheets("Sheet1")
Application.ScreenUpdating = False
For i = 4 To ws1.Cells(Rows.Count, "A").End(xlUp).Row
With Sheets("Sheet2")
trgRow = .Cells(Rows.Count, "A").End(xlUp).Row + 1
.Cells(trgRow, "A").Resize(3, 3).Value = ws1.Cells(i, "A").Resize(, 3).Value
.Cells(trgRow, "E").Resize(3, 4).Value = ws1.Cells(i, "D").Resize(, 4).Value
.Cells(trgRow, "D") = "A"
.Cells(trgRow + 1, "D") = "B"
.Cells(trgRow + 2, "D") = "C"
End With
Next i
Application.ScreenUpdating = True
End Sub

表組み加工を行うためのVBAなので、通常業務で繰り返し行うのが目的であるなら、該当しないかも知れません。

この回答へのお礼

ありがとうございます。
名称などは実際に作っている物ではなく仮称にしています(恥ずかしいので)
各項目の数ももう少し多いです。

ざっくりですが、シート1に設定した項目数で組み合わせた表で290行
シート2だと28000行弱となります。

「シート同士の参照セルの場所(行列)が違う」の画像を新たに添付しました。
この場合「シート2のG9」に入る数式を教えて頂けますと幸いです。

お礼日時：2020/11/15 12:33

No.3 回答者： ずんだしらたま 回答日時： 2020/11/15 00:09

すみません、誤記がありました。

誤:A型のみに値を移し、B、C列は空欄に〜
正:A型のみに値を移し、B、C型は空欄に〜

No.2 回答者： ずんだしらたま 回答日時： 2020/11/15 00:05

コピペするだ気でしたら、一時的に関数を設定して、値が入った後に文字列として同じ場所にコピペすれば可能だと思います。

例えばD列をコピーしたい場合は下の式をn列にコピーすれば3行ずつコピーできます。
=OFFSET(D$3,ROUNDDOWN((ROW()-1)/3,1),0,1,1)

A型のみに値を移し、B、C列は空欄にしたいのでしたら、n4に式をペーストしてn5n6は空欄とし、n4からn6を範囲選択コピーしてから、3の倍数になるように下の列を選択してペーストすれば同様のパターンで連続ペーストできます。

お望みの値が入ったら元のシートと上で入れた式は邪魔になるでしょうから、式の入ったセルを全て選択してコピーし、同じ範囲を選択した状態で「形式を選択して貼り付け」の「値のみ」でペーストすればお望みの結果になるはずです。

この回答へのお礼

ありがとうございます。
教えて頂いた数式を画像の表にコピー＆オートフィルは上手くいったのですが
別でも使おうとすると上手く行きません

「シート同士の参照セルの場所(行列)が違う」の画像を新たに添付しました。
この場合「シート2のG9」に入る数式を教えて頂けますと幸いです。

※厚顔なお願いで恐縮ですが、数式のどの部分を弄ると、どこが変化するかも教えて頂けると非常にありがたいです。
(せっかく教えて頂いているので出来る限り理解したいと思っています）

お礼日時：2020/11/15 12:33

No.1 回答者： ppp2122 回答日時： 2020/11/14 21:09

規則性が特殊とあるが

特殊であっても規則性があるなら
VBAで可能