EXCEL、ブロック単位で列方向に並ぶ表データを最左端ブロックの下行に移動したい

質問

ファイルメーカーデータベースの繰り返しフィールドからエクスポートした下表のようなデータがあります。（例題は簡略化しておりますが、実際の表は「A2：C4」を１ブロックとすると、ブロックの数は列方向に20あり、１ブロックの中の列数は５、行数は４０あります。）この表の「D2:F4」を「A5:C7」に、「G2：I4」を「A8：C10」に、というようにA：C列の下行に順次移動（又はコピー）したいのですが、A5：C5及びA8：C8に数式を入力し、行方向にコピーすることで一挙に実現したいけど数式がなかなか思い浮かびません。どなたかご教示をお願いします。できればマクロでなくワークシート関数で実現したいのですが、マクロの方がより簡単なときは併せてご教示ください。
　　 A 　　B 　　 C 　　 D 　　 E 　　 F　　 G 　　 H 　　 I
　　名前 種類 個数 名前 種類 個数 名前 種類 個数
２ 　山田　 D 　８ 　　鈴木 　G 　　９ 　木下　 H 　　７
３ 　鈴木 　F 　６ 　　木下 　A 　　５ 　吉田　 B 　　７
４ 　佐藤 　K 　４ 　　池田 　D 　　５ 　徳永　 N 　　４

回答No.5

ham_kamo · ベストアンサー

おかしいですね、私のテストシートではうまくいったのですが。
すみません、ちょっと不明なところがあるので補足願いたいのですが、
No,3の補足で、

年月日 　 →C3：V50
作業者　　 →W3：AP50
時間内額　 →AQ3：BJ50
時間外額　 →BK3：CD50
深夜早朝額 →CE3：CX50

となっているので、データは3行目から50行目までの48行入っていると思って、マクロをそのように書きました。C列を転記したあと、D3が空欄でなかったらD列もコピーするが、空欄であればその列は空行なのでW列に移る、という制御を行っています。2列目以降がうまくコピーされないのは、D3（及びX3など各項目2列目の3行目）が空欄の可能性があります。
D列以降のデータも3行目から始まっているかが一番気になるのですが、その点の補足をお願いします。

なお、同一シート内で関数でやる方法も一応やってみたらできたので、あわせてご紹介しておきます。マクロがうまくいかなかったら、こちらも試していただけますか？

手順：
・Ｃ列の左に５列挿入します。データはＨ列から始まり、Ｃ～Ｇ列がデータを転記する列になります。
・左上の名前ボックスに「C3:C962」と入力してEnterを押します。
・F2キーを押し、以下の式を貼り付けて「Ctrl+Enter」を押します。

=IF(OFFSET($H$3,MOD(ROW()-3,48),(COLUMN()-3)*20+INT((ROW()-3)/48))=0,"",OFFSET($H$3,MOD(ROW()-3,48),(COLUMN()-3)*20+INT((ROW()-3)/48)))

これで年月日がC列に入ったはずです。

・次に、名前ボックスにD3:G962と入力してEnterを押します。
・F2キーを押し、以下の式を貼り付けて「Ctrl+Enter」を押します。

=IF(C3="","",OFFSET($H$3,MOD(ROW()-3,48),(COLUMN()-3)*20+INT((ROW()-3)/48)))

これで残りの列が入ります。

私の理解しているフォーマットと元データが異なっていると、この関数式もうまくいかない可能性がありますが…。

お礼

何度も質問と補足を繰り返し、その都度懇切なご回答ありがとうございました。
あなた様のご回答を基に、その後なんとか自分で修正してやっとすべてうまく行くようになりました。
おかげで助かりました。

補足

NO.5回答への補足
お手数をかけてほんとに申し訳ありません。
　実はNO.3回答への補足の中で、「この５要素を１単位として、スポットによっては最高で延べ20日、１日で20人になることもあるし、１日だけ１人ということもあります。」と書いておりましたが、正に今回のデータの１行目（C3：CX3）がたまたま１日だけ一人だけのデータでして、そのことを申し上げてなかったのが私の手落ちでした。
　因みに２行目（C4：CX4）は３日で３人、３行目（C5：CX5)が１７日で１７人、４行目(C6：CX6）になって初めて２０日・20人の満杯の行でした。このようにスポットによって全くランダムにデータが存在しているのです。
　だから「D3が空欄であればその列は空行（空列？）なのでW列に移る」でなく、（構文がどうなるかさっぱり分かりませんが）D3が空欄でもC3の次にD3をコピーし、V3まで行ったらD3に移る、という制御であればベストのような気がします。補足説明不十分ですみません。

　それからご教示いただいた関数の方ですが、おかげさまで答えは正しく出ました。
　因みに数式中「48」という数値を、年間分の総データ641行の「641」に置き換えて試してみたところ641×20＝12820行まで全データをコピーすることが出来ました。

　ただ一つ、折角お考えいただいた数式に難を唱える訳ではありませんが、本来希望するコピー結果は、H列（列挿入後の呼称、以下同じ。）全データのコピー完了後I列に移るということでなく、H3の次の行にI3……→AA3までの（空欄を含めて）20行がコピーされ、その下行にH4：AA4というように、スポットのレコード順序が優先されるのがベストでした。

　しかしこうなるためには、数式を更に複雑で長たらしく変更する必要がありそうだし、また希望の順序でなくても年月日順にソートすれば仕事上の目的は達成出来るので、これ以上望むつもりはありません。
　もしお暇があってそれがそう難しくなく出来そうなときはお教えいただければ幸甚です。

　本当に縁もゆかりもない私のためにお時間を割いて貴重なノウハウをお考えかつご教示いただき感謝にたえません。
　ありがとうございました。

回答No.4

ham_kamo

補足を拝見しました。仕様はわかりました。
今回はデータの個数も変動する上、移動前のデータが入っている列にデータが移動されることもあり、関数では難しいのでマクロを組みました。それでも同じシートに組み替えるのは難しかったので、マクロでは別シートに抽出しています。

Alt+F11でVBAの画面を起動し、「挿入」＞「標準モジュール」を選択し、右の画面に以下のマクロを貼り付けてください。

このマクロではSheet1に元データがありSheet2のC3セルから整形したデータを出力されます。マクロ中に"Sheet1"と"Sheet2"というシート名が出てくるので、それを実際のシート名に置きかえてください。

マクロを実行してうまくいかなかったら、また補足をお願いします。

Sub Macro1()
　　Dim WS1 As Worksheet, WS2 As Worksheet
　　Dim CopyTo As Range, CopyFrom As Range, CopyRegion As Range
　　Dim CopyLines As Integer, i As Integer
　　Set WS1 = Worksheets("Sheet1") '元のデータがあるシート
　　Set WS2 = Worksheets("Sheet2") '整形したデータを転記するシート
　　WS2.Range("C3").CurrentRegion.Offset(1, 0).Clear
　　For i = 0 To 4
　　　　Set CopyFrom = WS1.Range("C3").Offset(0, i * 20)
　　　　Set CopyTo = WS2.Range("C3").Offset(0, i)
　　　　Do While CopyFrom.Value <> "" And CopyFrom.Column < 3 + (i + 1) * 20
　　　　　　CopyLines = WS1.Cells(65536, CopyFrom.Column).End(xlUp).Row - CopyFrom.Row + 1
　　　　　　Set CopyFrom = CopyFrom.Resize(CopyLines, 1)
　　　　　　CopyFrom.Copy CopyTo
　　　　　　Set CopyFrom = WS1.Cells(3, CopyFrom.Column + 1)
　　　　　　Set CopyTo = CopyTo.Offset(CopyLines, 0)
　　　　Loop
　　Next
End Sub

補足

何回もお手数をおかけして申し訳ありません。早速お示しのマクロを実行して見ました。その結果確かにSheet2のC3から転記されたデータの一部は貼り付けられました。
　しかし「一部」という意味は各項目毎に20列あるデータの最左端の列、つまり「C、W、AQ、BK、CE」列だけが貼り付けられて、それ以外の19列×5（D：V，X：AP，AR：BJ，BL：CD，CF：CX）の列データは貼り付けられなかったのです。
　これ以上お願いするのはほんとに心苦しいのですが、何分にも私はマクロの超初心者で自ら構文を構築する能力をほとんど持ち合わせていません。
　何度も何度も誠に恐縮ですが、もし差し支えなければもう一度ご検討の上ご教示いただきたくよろしくお願いいたします。

回答No.3

ham_kamo

No.1です。
まず最初の表の式ですが、実際のブロックがC4から始まって、１ブロックが５列４０行ということなので、C44に入る式が以下のようになりました。（実はどのセルも同じ式なのですが）

=OFFSET($C$4,MOD(ROW()-4,40),INT((ROW()-4)/40)*5+MOD(COLUMN()-3,5))

これを縦横にコピーしてもいいのですが、40行が20ブロックもありドラッグしてコピーするのも大変だと思うので、簡単に入力する方法をご紹介します。

・名前ボックス（左上のA1とか書いているところ）に、C44:G803と入力してEnterを押す
・入力する範囲が選択されるので、F2キーを押す。
・上の式をコピーして貼り付け、Ctrl+Enterを押す。

これで選択した範囲全体に式が貼り付けられるはずです。

もう一つの補足については、書かれているとおりそのままではＢ列、Ｃ列が上書きされるので、何か工夫が必要です。あらかじめＡ列の右に空行を挿入するよりは、Ａ列の左に空行を挿入する方が関数式としては簡単となります。（Ｂ列の前に空行だと、元のデータがイレギュラーなフォーマットになるので）

作ろうと思えばすぐに関数はできると思いますが、これも先の質問のように、例は簡略化されていて、左上のセル番地が違っていたり、実際のデータの個数はもっと多いのでしょうか。その辺が気になるので、以下の点を補足していただけますでしょうか。

・現在実際にデータが入っている左上のセル番地
・「名前」「種類」「個数」などの「項目の種類」の数（実際には「単価」もあって実は４個です、とか）
・実際のデータの行数（例ではB2:B4→A5:A7となっていますが、データが３行以上あれば、A5でなくてもっと下になりますよね）

補足

早速のご回答心からお礼申し上げます。
　さて、お礼の欄に書きました実際の表につきましては、ご教示頂いた数式を適用して期待どおりの結果を出すことが出来ました。おかげでほんとに助かりました。ありがとうございました。
　補足欄に書きましたもう一つの表ですが、そもそもこの表の元となるデータファイルは、スポット単位で人材を派遣する業務の１スポットをファイルメーカーの１データベースとして月間分をまとめたものです。　そのファイルをHTML形式ファイルにエクスポートし、更にそれをEXCELで読み込んだデータでして、データの項目は「年月日、作業者名、時間内額、時間外額、深夜早朝額」の５要素で成り立っております。
　この５要素を１単位として、スポットによっては最高で延べ20日、１日で20人になることもあるし、１日だけ１人ということもあります。
　これが一月で最高で40～50スポット程度（年間で500～６00スポット）になります。これらの膨大なデータを月ごとに１表にまとめて個人毎や月日毎の実績を把握・集計する必要があるのです。
　ファイルメーカーでその作業ができないのは、この5要素がすべて繰り返しフィールドに入っていて個人毎や月日毎に検索した場合、繰り返しフィールドの最初のデータしか検索されないからです。
　そのためデータベースの仕組みを変えない限り、今のところEXCELに変換するしかないのです。
　さて、１か月分の表のセル範囲ですが、スポットが最も多い月で下記のとおりとなっております。
年月日 　 →C3：V50
作業者　　 →W3：AP50
時間内額　 →AQ3：BJ50
時間外額　 →BK3：CD50
深夜早朝額 →CE3：CX50
　この表のデータは補足で説明したように、１列目（C列）は21列目（W列）、41列目（AQ列）、61列目（BK列）、81列目（CE列）のように順次規則正しく対応しています。だから１スポットが１日・１人だけのときは2～20、22～40、42～60、62～80、82～100の各列目は空白セルになります。
　この５要素のデータをすべてC：G列に移動（コピー）するためには、ご教示のとおりあらかじめC：Gに空白列を設けておき、右にずれてＨ3：DC50となったデータをC：G列に転記するということになると思います。空白セルのエラー表示防止も必要かと思います。
　最初は年間分をすべて１Sheetにまとめようかと思いましたが、万を超える行数となり、作業がし辛いので月毎及び集計Sheetを設けることにしました。
　以上の説明でもまだわかりにくい点があるかも知れませんが、何分よろしくお願いいたします。

回答No.2

imogasi

VBAで
例データ（A1:I5、わざと最下行は、データ行数を変え、でこぼこにしている）
　山田　 D 8 鈴木 G 9 木下 H 7
　鈴木 F 6 木下 A 5 吉田 B 7
　佐藤 K 4 池田 D 5 徳永 N 4
河西 R 12 木村 Y 23
加古 Q 4
OKWAVE画面では崩れるが、河西、加古はD列に入れてます。
コード
VBE（ALT+F11で出る)画面の標準モジュールにコピーして実行（F５)

Sub test04()
cr = Range("IV1").End(xlToLeft).Column '最右端列番号
MsgBox cr
retu = 3 '単位列数
For j = 4 To cr Step retu
k = Range("A65536").End(xlUp).Row 'A列下端行数
MsgBox k
m = Cells(65536, j).End(xlUp).Row '単位列での最下端行数
MsgBox m
'--単位列をコピーしてA列下部に貼り付け
Range(Cells(1, j), Cells(m, j + 2)).Copy (Cells(k + 1, "A"))
Next j
End Sub
上記のMsgboxは確認のため入れているが、納得したら削除すること。
結果　A,B,C列のみ掲載
　山田　 D 8
　鈴木 F 6
　佐藤 K 4
鈴木 G 9
木下 A 5
池田 D 5
河西 R 12
加古 Q 4
木下 H 7
吉田 B 7
徳永 N 4
木村 Y 23
ーーーー
関数でもできるが、ロジックの理解が難しい。式が相当長くなる。表の組み換えは関数は苦手な問題。

お礼

ご教示ありがとうございました。
早速例題で試してみたところうまく行きました。
ところで質問にも書いていたように現実の表はブロックが20、ブロック内の列数が5列、行数が40行なので、実際に作業するときは一部修正の必要があるかと思いますが、これから徐々に勉強してやってみたいと思います。
それとこれはNo1ham_kamoさんへの補足に記入しておりますが、厄介なことにも一つ配列が異なる似たような表があり、こちらはADGの名前に相当するデータがABC列に、BEHの種類に相当するデータがDEFに、CFIの個数に相当するデータがGHIにあり（実表はそれぞれ20列×5でCX列まで40行ある。）それぞれA→D→G、B→E→H、C→F→Iのように対応しております。これを最初の質問と同じ結果になるように列を移動（又はコピー）しなければなりません。（詳細はham_kamoさんへの補足をご覧ください。）
そこで厚かましいお願いで恐縮ですが、この場合の構文はどのようになるのでしょうか。お差し支えなかったらついでにお教えいただければ助かります。よろしくお願いします。

回答No.1

ham_kamo

A5に
=OFFSET($A$2,MOD(ROW()-2,3),INT((ROW()-2)/3)*3+MOD(COLUMN()-1,3))
という式を入れて、それを縦横にコピーしてみてください。

お礼

早速のご回答ありがとうございました。
直ちにご教示の数式を例題の表で試してみたらうまく行きました。
ところが実際の表は質問の際に記載したとおりブロックの数は20、１ブロックの列数は5列、行数は40行なので、この式の数値をいろいろと修正して試したところどうしてもエラーが出るのです。その原因はおぼろげながら分かったつもりだった数式の私の理解がいまいち不十分だからでしょう。
そこでお願いですが、実際の表（最初の左端のブロックが「C4：G43」で表全体は「C4：CX43」となっています）に当てはめた場合、数式（の数値）をどう変えたらいいのか、再度教えていただけませんでしょうか。式の数値が変わればより一層理解出来ると思いますので、厚かましいお願いで恐縮ですがよろしくお願いします。

補足

厚かましいついでにもう一つ質問させていただいてよろしいでしょうか。実は例題の表の別に配列が異なる似たような表があります。その表は下のとおりで、A列はD・G列に、B列はE・H列に、C列はF・I列にそれぞれ対応しております。そこでこれを次のようにセルを移動（コピー）し配列を変えたいのです。
B2：B4→A5：A７
C2：C4→A8：A10
D2：D4→B2：B4
E2：E4→B5：B７
F2：F4→B8：B10
G2：G4→C2：C4
H2：H4→C5：C７
I 2：I 4 →C8：C10
以上のように移動するとB2：C4部分が上書きされることになるので、あらかじめB列の左に２列空白列を挿入して置いて移動しなければならないかも知れません。
何度もお手数をかけて誠に申し訳ありませんが、お差し支えなければ是非ご教示をお願いします。

　　 A 　　B 　　 C 　　D 　　 E 　　F　　 G 　　 H 　　 I
　　名前 名前 名前 種類 種類 種類 個数 個数 個数
２ 　山田 鈴木 木下　D 　　G 　　H　　 ８ 　　 ９ 　　 ７
３ 　鈴木 木下 吉田　F 　　A　　　B 　　 ６ 　　５ 　　 ７
４ 　佐藤 池田 徳永　K 　　D　　 N 　 　４ 　　 ５ 　　 ４