結合したセルのある行のコピーについて（エクセル）

質問

Excel2000を使って下のような表を作りました。
約500行ほど入力されています。

本の題名（日本語）│本の題名（英語）│値段
花　　　　　　　　　　　　　　　　　　　　　10
樹　　　　　　　　　　MOK　　　　　　　　　20
・・・　　　　　　　　　 ・・・　 　　　　　　　　　・・・

これを下のような表に作り変えたいのです。
本の題名（日本語）│本の題名（英語）│値段
花　　　　　　　　　　　　　　　　　　　　　 　10
花（貸出日）　　　　　　　　　　　　　　　　 　10
花（借りた人）　　　　　　　　　　　　　　　 　10
樹　　　　　　　　　　　　MOK　　　　　　　　　20
樹（貸出日）　　　　　　　MOK　　　　　　　　20
樹（借りた人）　　　　　　MOK　　　　　　　　20
・・・　　　　　　　　　　　 ・・・　 　　　　　　　　・・・

このように1行につき各2行増やしたいのです。
日本語の題名と値段は必ず入力されていますが、英語の題名は必ずしも入力されていません。
問題はこの各項目（本の題名（日本語）、本の題名（英語）、値段）がそれぞれ（８，８，２）個のセル結合をしていることなのです。
その表の上下との関係もあって、この結合をしないわけにはいきません。
ここの過去ログを見たのですが、（テキストファイルで入力してはりつける、.csvファイルを作るなど）、それらの方法ですとセルの結合がとかれてしまいます。
そうしますとまたひとつひとつセルの書式設定をしないといけなくなるので、その方法は避けたいと思っています。
一行ずつセルのコピーをして貼り付ける以外のやり方がありましたらご教授ください。
またどうしても無理だ,ということでしたら、そうお答えいただけると幸いです。
よろしくお願いします。

回答No.6

nishi6 · ベストアンサー

全部が分かると、かえって分かりやすいものができたのかもしれません。

コードを見やすくして、補足の要件を取り込んでみました。
試してみてください。

Sub insRow91()
　　Dim kmk As Integer　　　'項目数カウンタ（結合された7項目）
　　Dim colTTL As Integer 　'列の総数
　　Dim rw As Long　　　　　'セル

　　'B列を基準に一番下から7行目まで2行の挿入と、貼り付けを実行する
　　'　（5行目が表題。6行目が1件目なので7行目が最後の挿入行）
　　For rw = Range("B65536").End(xlUp).Row + 1 To 7 Step -1
　　　　'2行挿入
　　　　Rows(rw).Insert
　　　　Rows(rw).Insert
　　　　With Range("B" & rw)
　　　　　　For kmk = 1 To 7
　　　　　　　　'各項目のそれより前の列総数をカウントする
　　　　　　　　'　（Offsetを使用しているため、B列が0列目としてカウントする）
　　　　　　　　Select Case kmk
　　　　　　　　　　Case 1: colTTL = 0　　　　　　 'ナンバー
　　　　　　　　　　Case 2: colTTL = colTTL + 2　　'本の題名（日本語）。ナンバーの2列分追加
　　　　　　　　　　Case 3: colTTL = colTTL + 8　　'本の題名（英語）。本の題名（日本語）8列分追加
　　　　　　　　　　Case 4: colTTL = colTTL + 8　　'値段。本の題名（英語）8列分追加
　　　　　　　　　　Case 5: colTTL = colTTL + 2　　'係名。値段2列分追加
　　　　　　　　　　Case 6: colTTL = colTTL + 2　　'キー。係名2列分追加
　　　　　　　　　　Case 7: colTTL = colTTL + 2　　'備考。キー2列分追加
　　　　　　　　End Select

　　　　　　　　'結合状態で貼り付ける
　　　　　　　　'　挿入位置から、上に1行上の、colTTL列分右の結合セルをコピーする
　　　　　　　　.Offset(-1, colTTL).Range("A1").Select: Selection.Copy
　　　　　　　　'　コピーしたセルを２行に貼り付ける
　　　　　　　　ActiveCell.Offset(1, 0).Range("A1:A2").Select: ActiveSheet.Paste
　　　　　　Next
　　　　End With
　　Next
End Sub

お礼

たびたびの回答、本当にありがとうございます。
お返事が遅くなってすみませんでした。
早速試したところ、この方法で見事できました！
後は（貸出日）と（借りた人）をどんどん入れていくだけです。
最初から詳しくご説明すればよかったですね。
今はとりあえずこのコードをコピー＆ペーストして使っただけで、中身を理解できていません。
くわしくコメント等いれて頂いたので、これからじっくりコードを見たり自分で調べて中身もきちんと理解できるようがんばります。
とても助かりました。本当にありがとうございました。

回答No.5

ADEMU

再びＡＤＥＭＵです。
全てを選択した後、
エクセルのデータ（Ｄ）を選択して「並び替え」（Ｓ）を選択するとカラムが３つ出てくると思います。
最初のカラムにＡ列を選択し、２番目のカラムにＢ列を選択して並び替えを実行してみて下さい。

お礼

この後も試したんですが、結局結合セルがあると並び替えができないようで、できませんでした。
詳しく丁寧に教えてくださったのに役立てられず、申し訳ありません。
回答してくださって、ありがとうございました。

補足

たびたびの回答、本当にありがとうございます。
並び替えのやり方はよく分かりました。
早速やってみたところ、先程と同じ「この操作には同じサイズのセルが必要です」というエラーが出てしまいました。
試しに、同じデータを結合セルなしで作ってやってみたところ、きちんと思ったように並び替えができました。
ですので、並び替え用の数字とアルファベットも8つのセルを結合させて
数字、アルファベット、本の題名（日本語）、本の題名（英語）だけで並び替えさせてみたんですが、それでもやはりだめでした。
もしかして、エクセルで結合セルの並び替えはできないのでしょうか？
それともまだ私が何かやり方を間違えているんでしょうか？
ADEMU様のやり方で、後一歩というところまで来たのでとても悔しいです。
これから、もう少しいろいろやってみます。
もし何かお気づきの点でアドバイスいただければ、ありがたいです。
よろしくお願いします。

回答No.4

nintoku

う～ん。
私の頭が悪いのか、主旨がよくわからないので
まちがっていたらメンゴm(_ _)m

統合したセルを同じフォーマットで下行へコピーだけなら、
セルの右下にでるマウスポインター（＋）でドラッグすれば、
セルの統合は解かずにできますが．．．

この場合、最初に行を挿入して

本の題名（日本語）│本の題名（英語）│値段
花　　　　　　　　　　　　　　　　　　　　　 　10
花（貸出日）　　　　　　　　　　　　　　　　 　10
花（借りた人）　　　　　　　　　　　　　　　 　10
樹　　　　　　　　　　　　MOK　　　　　　　　　20
樹（貸出日）　　　　　　　MOK　　　　　　　　20
樹（借りた人）　　　　　　MOK　　　　　　　　20
・・・　　　　　　　　　　　 ・・・　 　

ここまでを作ります。後は範囲をドラックしてグループ化してから、（+）で下方へ1000セルぐらいドラッグすれば
一瞬でセルの統合は解かずに同じものがコピーされます。
ただしグループ化したデータだけすべて同じにコピーされますので、
データが全て違うのであればこの方法は出来ません。

なんか、違うような気もしますけど・・(--;)

お礼

お気づきのように、今回の場合500件全てが違うデータなので、この方法は使えないようです。
せっかく回答していただいたのに役立てられなくて、すみません。
でも回答してくださって、ありがとうございました。

回答No.3

nishi6

ソートできれば簡単と思いますが、マクロを書いてみました。
結合セルの数の問題でソートできないかもしれません。その時は参考にしてみてください。

（１）A列から８列、８列、２列の結合で、
（２）１行目は表題
（３）２行目からデータの繰り返し
（４）A列は最後まで連続して入力されている。

『本の題名（日本語）』の２、３行目は空白
『本の題名（英語）』、『値段』は元のセルを複写してそれぞれ結合しています。

Sheet1での例です。コピーしたBookでテストしてみてください。

ツール→マクロ→Visual Basic Editor でVBE画面に移り、
表示→プロジェクトエクスプローラでプロジェクトエクスプローラを表示
Sheet1をダブルクリック。でてきたコードウインドウに下記コードを貼り付けます。

シートに戻り、ツール→マクロ→マクロで『insRow』を実行します。

ここから
↓
Sub insRow()
　　Dim rw As Long 'セル

　　For rw = Range("A65536").End(xlUp).Row + 1 To 3 Step -1
　　　　'2行挿入
　　　　Rows(rw).Insert
　　　　Rows(rw).Insert
　　　　With Range("A" & rw)
　　　　　　'最初の8列×2行を結合する。本の題名（日本語）
　　　　　　.Offset(0, 0).Range("A1:H1").MergeCells = True
　　　　　　.Offset(1, 0).Range("A1:H1").MergeCells = True

　　　　　　'本の題名（英語）は元のセルをコピーする
　　　　　　.Offset(-1, 8).Range("A1").Select: Selection.Copy
　　　　　　ActiveCell.Offset(1, 0).Range("A1:A2").Select: ActiveSheet.Paste

　　　　　　'値段は元のセルをコピーする
　　　　　　.Offset(-1, 9).Range("A1").Select: Selection.Copy
　　　　　　ActiveCell.Offset(1, 0).Range("A1:A2").Select: ActiveSheet.Paste
　　　　End With
　　Next
End Sub

お礼

お返事が遅くなりました。上の補足ではわけもわからず妙な事を言っていました。
戸惑われたかと思います。申し訳ありません。

nishi6さんの方法はすごいですね。
あっという間に複数列の挿入ができてびっくりしました。
ただ質問のときに示したものより実際はたくさんの項目があるのでそのままでは使えないようです。
（実際の表では4行目までが表のタイトル等、5行目は問題の表の項目等の行、
A列は空白、B6-C6が結合してナンバーが振ってある、D6-K6が本の題名（日本語）
L6-S6が本の題名（英語）、T6-U6が値段、V6-W6が係名、X6-Y6がキー、Z6-AP6が備考、
こうした行が正確には482件分あります）

nishi6さんの方法ですと、本の題名（日本語）が空白になるので、
わけもわからず、Offset(-1, 8)をOffset(-1, 7)としてみてその式を本の題名（日本語）の下の式と入れ替えてやってみました。
そうしたら、本の題名（日本語）も元のセルをコピーして出す事ができました。
この方が、後で入力するときに便利かなとも思いました。
ただそれ以外の場所の意味が全く分かりません。
こんな状態ではマクロを使うのは無理でしょうか。
これだけ時間をかけてやっていては、もう一つ一つ手で入力した方が速いかとも思うのですが、マクロを使った方がミスがなさそうなので、マクロを使いたいと思います。
ここをこうすればいい、などのアドバイスをしていただけたらと思います。
よろしくお願いします。

補足

回答ありがとうございます。
結合セルを使っているので、多分ソートはできないと思います。
（ソートを利用した行の増やし方があるんでしょうか？）
教えていただいた方法を試したいと思います。
ただ、マクロを組むのが初めてのため、ちょっと時間がかかりそうです。
下の方の方法も試すので、明日のお返事になってしまうかと思います。
素早く回答いただいているのに、申し訳ありません。

回答No.2

ADEMU

（貸出日）と（借りた人）が別のセルであれば簡単なのですが、同じであればちょっとした技をつかわなければなりません。
まず、本の題名（日本語）のセルの前に２列増やします。
１列目に１～５００まで連続付番（できますよね）します。
次に情報の入った全てのセルを選択し、コピーします。
これを５０１番目（１行開けた方がいい）と１００１番目に２度張り付けます。そうすると同じものが３つずつできます。
次に５０１番目から１０００番目までの一番後ろのセルに（貸出日）、１００１番目から１５００番目には（借りた人）を全てコピーしてはりつけます。
その後ろのセルは本の題名（日本語）と同じだけセルを結合しておいてコピーで全て同じようにします。
そして、後ろの列に関数で＝ＣＯＮＣＡＴＥＮＡＴＥ（Ａ，Ｂ）として２つの文字を結合させます。これを全て選択してコピーして同じ場所に形式を「値」にして張り付け、更にそれを元の位置に張り付ければできあがり。
次に２列目に１～５００までに「Ａ」、５０１～１０００まで「Ｂ」、１００１～１５００までに「Ｃ」を入れます。
これで全てをならびかえて「数字」「アルファベット」の順に並びかえれば希望通りに表ができます。あとは、必要でない列を削除すれば良いのです。
ちょっと面倒でしょうか。

お礼

ADEMU様、昨日から今日にかけてやってみました。
大変分かりやすい説明のおかげで途中まではできたのですが、最後の

＞これで全てをならびかえて「数字」「アルファベット」の順に並びかえれば希望通りに表ができます。あとは、必要でない列を削除すれば良いのです。

と言うところでつまづいています。
イメージはなんとなく分かるんです。
今、
1A
2A
3A
…

1B
2B
3B
…

1C
2C
3C
…

となっているものを入れ替えて自分の望むように並べたいんですよね。
これをどうやればいいのかが分かりません。
A列だけ選択して入れ替えると後のデータの入ってるセルは動かなくて、数字だけ変わってしまいます。
かといってデータのあるセル全てを選択して並び替えをしようとすると
「この操作には同じサイズのセルが必要です」というウメッセージが出てエラーになってしまいます。
ならびかえ、というものがよく分かってないのかもしれません。
申し訳ないんですが、そこのところをもう一度ご回答いただけますでしょうか？
よろしくお願いします。

補足

回答ありがとうございます。
書き方が分かりにくくてすみません。（貸出日）と（借りた人）は別のセルになります。
－－－－－－（セルの区切り）
花
－－－－－－
花（貸出日）
－－－－－－
花（借りた人）
－－－－－－

ADEMU様はこう思われたんでしょうか。

－－－－－－（セルの区切り）
花
－－－－－－
花（貸出日）
花（借りた人）
－－－－－－

書いてくださった方法は、同じ場合を想定されているんでしょうか？
ざっと読んだだけではよく分からないので、これから試してみます。
お返事できるのが明日になるかもしれません、申し訳ありません。

回答No.1

noopee

こんにちは。

セルをコピーし、右クリックの「形式を選択して貼り付け」-「値」でできませんか？

主旨が違っていたら補足お願いします。

補足

回答ありがとうございます。
主旨は違っていないと思います。
ただそうしますと500件×2行で1000回同じことを繰り返しますよね。
もうすこし、簡単な方法がないかなと思っていたんです。
また、教えていただいた方法を試してみたんですが
既に入力してある行の下に1行挿入し、「形式を選択して貼り付け」-「値」すると
「この操作には、同じサイズの結合セルが必要です」というエラーが出てしまいました。
ただ普通に貼り付けした場合にはこのエラーは出ませんでした。
私がnoopee様の思っているのとは違う方法でやってしまっているのでしょうか？
もしまたアドバイスいただけましたら幸いです。