エクセル 大量のセルのリンク方法について

質問

初めて質問させて頂きます。
よろしくお願いいたします。

シート1にA1セルからA10セルまで
任意の値が入ってると仮定します。

A1
A2
A3
A4
A5
A6
A7
A8
A9
A10

シート2に以下の様にシート1のA1～A10の値がリンクできる様に
碁盤の目の様にコピーする簡単な方法はないのでしょうか?

A1　　A2　　A3
A4　　A5　　A6
A7　　A8　　A9
A10　A11　A12

縦と横を入れ替える方法なら
形式を選択して貼付→行列を入れ替えるをチェックする
→OKをクリックでできますが・・・

なぜ、この様な事を聞くのかというと
当方、仕事で縦に並んでいる店番号リストのシートを
別シートに碁盤の目の様に並べてリンクする必要があるためです。

店番号数が10や20程度なら、ちまちま修正しても
たいした問題ではありませんが・・・

問題は店番号が3,000以上あるので修正作業が、かなり大変なんです・・・
いつもいつも決まった列数ではないためマクロを組んでも役に立たないです。

あと店番号はお行儀良く数字が揃っていなく、ランダムで飛び番があるので
A1+1と言った計算式は無理ですね・・・

今の所、全てのシートに=Aと計算式を入力してから
Aの後に任意の数字(セル番号)を入れる様にしています。
(数字が入った状態だと数字を消す手間が増ますので。)

修正作業が終わったあと正しく入力されているか
目視で確認していますがセル数が多いので大変です。

どなたかより短時間でできる方法があれば是非とも教授願います。

回答No.4

mitarashi · ベストアンサー

よけいなお世話かもしれませんが、

>いつもいつも決まった列数ではないためマクロを組んでも役に立たないです。

やりようはある様な気がします。
下記ではお役に立てませんか？参照元（ご質問の例ではSheet1のA1～A10）を選択後、実行して下さい。

'選択範囲への参照を、複数列に分割して指定範囲に生成
Sub Sample()
Dim buf As Variant
Dim divNum As Long, cellCounter As Long
Dim myRow As Long, myColumn As Long
Dim destRange As Range, srcRange As Range, myCell As Range

Set srcRange = Selection
buf = Application.InputBox(Prompt:="分割列数を入力してください。", Type:=1)
If buf = False Then Exit Sub
divNum = buf
On Error GoTo errHandle
Set buf = Application.InputBox(Prompt:="先頭セルを選択してください。", Type:=8)
On Error GoTo 0
Set destRange = buf
cellCounter = 1
For Each myCell In srcRange.Cells
myRow = Int((cellCounter - 1) / divNum) + 1
myColumn = (cellCounter - 1) Mod divNum + 1
destRange.Cells(myRow, myColumn).Formula = "=" & srcRange.Parent.Name & "!" & myCell.Address
cellCounter = cellCounter + 1
Next myCell
destRange.Parent.Activate
Exit Sub
errHandle:

End Sub

余談
Inputメソッドのダイアログ表示中にブックをまたぐのは無理みたいですね。出来るなら組み込んでみようかと思ったのですが。
安直には、同じブック内にリンク作成後、他のブックにコピーすれば通用します。ご参考まで。

お礼

せっかくマクロを組んで頂いて申し訳ないのですが
私のスキルでは内容がさっぱり理解できないです・・・

初歩的なマクロならある程度理解できますが・・・
本当に申し訳ございません・・・

補足

当方、当初、マクロは動作確認がやっかいなので
視野に入れていませんでしたが・・・

関数を色々やっていくうちに頭が混乱してきました・・・

ワラにもすがる思いで改めてマクロを実施してみたところ
No.4さんのマクロでOKでした!

絶対参照にして頂いているんですが当方としては相対参照が
都合がよいのでどの行を変更すればよいのか教えて頂けますでしょうか?

当方この質問サイトを利用するのが初めてという事もあり
最初からお礼に入れてしまいました・・・

回答No.9

mitarashi

#4です
下記の通り、destRangeから始まる行を、まるごと差し替えてください。
画面上別の行に見えるかもしれませんが、destRangeから、myCell.Address(RowAbsolute:=False, ColumnAbsolute:=False)までは一行になります。

For Each myCell In srcRange.Cells
myRow = Int((cellCounter - 1) / divNum) + 1
myColumn = (cellCounter - 1) Mod divNum + 1
destRange.Cells(myRow, myColumn).Formula = "=" & srcRange.Parent.Name & "!" & myCell.Address(RowAbsolute:=False, ColumnAbsolute:=False)
cellCounter = cellCounter + 1
Next myCell

なお、動作すればお分かりいただけた事ですが、ご理解は反対ですので念のため記しておきます。
Absolute=True　絶対参照する。
Absolute=False　絶対参照しない。

お礼

指示の通り修正したところバッチリです!

これで仕事が、はかどります。
本当は自分で理解して組むのが一番なんですけどね・・・

マクロって奥が深いですね・・・
ここまで柔軟なマクロができるとは・・・驚きです。

>動作すればお分かりいただけた事ですが
>ご理解は反対ですので念のため記しておきます。
ご指摘ありがとうございます。　

当方の知識不足でご迷惑をおかけしました。
丁寧に解説して頂き本当に、ありがとうございました。

回答No.8

keithin

次の通り操作します。

シート２のＡ１，Ｂ１，Ｃ１に
=Sheet1!A1&""　=Sheet1!A2&""　=Sheet1!A3&""
を記入
Ａ１：Ｃ３を選んで下向けにえいやっとオートフィルドラッグします。

シート２のＡ１１，Ｂ１１，Ｃ１１，Ｄ１１，Ｅ１１に
=Sheet1!A11&""　=Sheet1!A12&""　=Sheet1!A13&"" =Sheet1!A14&""　=Sheet1!A15&""
を記入
Ａ１１：Ｅ１５を選んでから，下向けにえいやっとオートフィルドラッグします。

数式設計で苦労しているようですが，「具体的に何をしたい」のか説明がないと（または「ヒントを聞けばあとは自分で出来る」と期待して手抜きのウソ説明をしていると）いつまでたっても解決できませんよ。

回答No.7

mitarashi

#4です。売り込みを図っていたら、その間に補足をいただいていましたね．．．
気に入っていただけた様で幸いです。
さて、相対参照への変更ですが、下記の様に変更して下さい。

destRange.Cells(myRow, myColumn).Formula = "=" & srcRange.Parent.Name & "!" & myCell.Address(RowAbsolute:=False, ColumnAbsolute:=False)

見ておわかりと存じますが、???Absolute:=Trueとすれば行列一方だけの相対参照も可能です。

補足

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

ROW　行
COLUMN　列
Absolute=True　絶対参照しない。
Absolute=False　絶対参照する。

なので以下の意味だと理解しました。
RowAbsolute:=False 行方向を絶対参照に指定
ColumnAbsolute:=False　列方向に絶対指定

下から7行目のdestRange.Cells～(以下略)～の次の行に
指示されたRowAbsolute:=False, ColumnAbsolute:=Falseを
追加しょうとしましたがコンパイルエラーが出てダメです。

かといって7行目のdestRange.Cells～(以下略)～の後に
指示されたRowAbsolute:=False, ColumnAbsolute:=Falseを
追加しょうとしましたがコンパイルエラーが出てダメです・・・

下から7行目にある(myRow, myColumn)の括弧内を
指示されたRowAbsolute:=False, ColumnAbsolute:=Falseに
書き換えてもコンパイルエラーと出たので当方の判断で
RowAbsolute=False, ColumnAbsolute=Falseに修正して
マクロを走らせたところ完走するんですが正常にリンクが貼られないです・・・

他の回答にも書いていますが私自身マクロはそれ程詳しくない事もあり
意味をちゃんと理解できていないので、どうしたらいいのかわからない状態です。

お手数ですがもう一度お願い致します。

回答No.6

mitarashi

#4です。
やっていることは簡単ですが、自分でも使うかもと思って、シート名に依存しないコードにしましたので、わかり難いかもしれません。
#4のマクロの利点としては、手で入れていた時と同様、=Sheet1!A1といった簡単な式を設定しますので、行を挿入しようが列を挿入しようがエクセルが自動調整してくれる事があります。
ただ、#5さんご指摘の通り、データ量に応じてシート1が複数列になるのか、シート２の列数が増えるのか、補足を見せていただいても、よく分かりませんでした。#4は、後者として、列数を人間様が都度設定できる様にしたものです。
ご採用はともかく、一度動かしてみていただけると幸いです。複数列に分けて、印刷用紙の節約なんて用途にも使えるかもしれません。

回答No.5

pc_knight

やりたいことは、「シート1にある店番データを「シート2」に碁盤の目の様に表示させたい」ということでしょうか。間違いなく確実・迅速に行うにはマクロがお勧めです。（６万件のデータ転記でも恐らく１分とかかららないでしょう）

以下の二通りの方法を考えました。

但し、ご質問の「いつもいつも決まった列数ではないため・・」の列数がどのシートのこ列数のことなのか不明、また、「全てのシートに=Aと計算式を入力してから・・」の「全てのシート」の意味と「=Aと計算式を入力してから」の意味がよく分からないので、単に「シート１」のＡ列データを「シート２」のＡ～Ｃ列に転記することとしました。
「シート１」にて、途中に空白セルがある場合は、その空白データの転記はされず、次の行のデータが「シート２」には転記されます。

＜方法１＞
シート１の任意のセルをダブルクリックしたらマクロが自動起動、シート２クリア＆データ転記を行う。
＜方法２＞
シート１の任意のセルの内容に変更（含む、クリア・削除・挿入）が生じたらマクロを自動起動、シート２クリア＆データ転記行う。

＜方法２＞は、シート１の任意のセルの内容が変更の都度、「シート２クリア＆データ転記」が生ずるのでその処理時間中（総データ数に比例）は、次の変更を待たされる。(例えば、１０００件データだと１秒とか)
そのため、特にデータが多い場合は、転記したい時だけ起動させることができる＜方法１＞が良いでしよう。

マクロの貼り付け方法
（１） シート１のシート名タグを右リリック
（２） →コードの表示（Ｖ）を選択→コードウィンドウが表示される。
（３） 次のコードをシート1のコードウィンドウに貼り付けます。

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'**************＜方法１＞*************
Dim r, n As Double
Sheets("シート２").Cells.ClearContents
For r1 = 1 To Sheets("シート１").Range("A65536").End(xlUp).Row
If Sheets("シート１").Cells(r1, 1) <> "" Then
n = n + 1
r2 = (n - 1) \ 3 + 1: c2 = (n - 1) Mod 3 + 1
Sheets("シート２").Cells(r2, c2).Value = Sheets("シート１").Cells(r1, 1)
End If
Next r1
MsgBox "転記終了"
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
'**************＜方法２＞*************
Dim r, n As Double
Sheets("シート２").Cells.ClearContents
For r1 = 1 To Sheets("シート１").Range("A65536").End(xlUp).Row
If Sheets("シート１").Cells(r1, 1) <> "" Then
n = n + 1
r2 = (n - 1) \ 3 + 1: c2 = (n - 1) Mod 3 + 1
Sheets("シート２").Cells(r2, c2).Value = Sheets("シート１").Cells(r1, 1)
End If
Next r1
End Sub

お礼

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

>「シート1にある店番データを
>「シート2」に碁盤の目の様に表示させたい」ということでしょうか。
ええっ、その通りですが、補足させて頂きますと
碁盤の目の様に並べてリンクさせたいという事です。

>今の所、全てのシートに=Aと計算式を入力してから
>Aの後に任意の数字(セル番号)を入れる様にしています。
シート2のA1～C*のセルにシート1のA1～A*まで値を
碁盤の目の様に並べてリンクする作業です。

店の事務量により1列～5列になったりするんです。
例として店番号1～10は1列単位、11～20は3列と変化します。

この例でいくと店番号1～10は1列単位なので
単純に最初のセルに=A1と入れて
あとは計算式のコピーで問題ないです。

店番号11～20は3列単位なので
11～20の店番号のリンク先を=Aと以下の様に入力します

=A =A =A
=A =A =A
=A =A =A
・
・
・
後に正しいリンク先として
以下の様に=Aの後に正しい数字を入れていくわけです。

=A1　=A2　=A3
=A4　=A5　=A6

以上の作業後に記入欄として空欄の行を数行挿入します。
挿入する行数も店番号の列数によって変化します。

以上、説明不足だったので補足させて頂きました。
ご理解頂けましたでしょうか?

最終的にしたい表は以下になります。
□は空欄のセルという意味です。
A*はシート1のリンクという事です。

　　表題
A1　　A2　　A3
□　　□　　□
□　　□　　□
A4　　A5　　A6
□　　□　　□
□　　□　　□
A7　　A8　　A9
□　　□　　□
□　　□　　□
A10　A11　　A12
□　　□　　□
□　　□　　□
・
・
今の所、No.2さんが紹介してくれた関数INDIRECTと
COUNTBLANKを組み合わせる事を考えています。

マクロを組んで頂きありがとうございます。
私のマクロのスキルは初歩程度しか持ち合わせていないので
内容を理解するところから始めないと話にならないですね・・・

回答No.3

MarcoRossiItaly

No.2です。
行の挿入のことについて、一言。

もしも「店番号」というのが一意に付番されていて、Sheet2にも「店番号」を記載する列を作っても構わないという場合は、VLOOKUP関数を貼っていく手もありそうです。
VLOOKUPなら、「店番号」で検索してデータを拾ってくるので、行が挿入されても修正が不要かと。

例えば、Sheet1のA列に「店番号」が、B列にデータが、Sheet2のA～C列に「店番号」が入力されているとき、次の式をSheet2のD1に入力。
続いてD1をコピーして、D～F列の各セルに貼ります。

=VLOOKUP(A1,Sheet1!$A:$B,2,0)

（「$A:$B」という箇所を、代わりに「$A1:$B1000」などと行番号も書いている場合でも、行を挿入した際に、式中の参照範囲も自動修正されるので、やはり問題は発生しません）

補足

親切に回答して頂きありがとうございます。
紹介されているVLOOKUP関数については導入済ですので大丈夫です。

紹介して頂いたINDIRECT関数は色々やってみたんですが
空欄を設ける関係で混乱しています・・・

今の所、No4さんのマクロを使用する方向で考えています。
せっかく教えて頂き申し訳ないのですが・・・

色々教えて頂きありがとうございます。

回答No.2

MarcoRossiItaly

No.1さんの「方法１」は成功しそうですが、「方法２」は成功しますか…？
MOD関数か何かを組み合わせないといけないような？
また、MODを入れたとしても、数式中に「&""」を含めている場合は、空白セルが空白でなくなるので、失敗しないでしょうか？

「方法１」でも「方法２」でも、数式中の「&""」は、削除しても機能しそうです。

「Ctrl+Gで空白セル（またはそれを含む行）を削除する方法」は、今回の計算に限らず、確かに何かとお勧めです。

他の式も紹介します。

1.Sheet2のA1に次の式を入力

=INDIRECT("Sheet1!A"&((ROW(A1)-1)*3+COLUMN(A1)))

2.セルA1をコピーし、並び替えて表示したい箇所に貼り付け。
　例えばSheet2のA1:C100という範囲にSheet1のA1:A300を並び替えて表示させたいなら、Sheet2でA1をコピーした状態でA1:C100の範囲を選択し、単に貼り付けてください。
　次のように並びます。

Sheet2のA1←Sheet1のA1
Sheet2のB1←Sheet1のA2
Sheet2のC1←Sheet1のA3
Sheet2のA2←Sheet1のA4
Sheet2のB2←Sheet1のA5
Sheet2のC2←Sheet1のA6
Sheet2のA3←Sheet1のA7
・
・
・

お礼

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

確かに方法2はうまくいきませんね・・・

>「Ctrl+Gで空白セル（またはそれを含む行）を削除する方法」は
>今回の計算に限らず、確かに何かとお勧めです。
はい、何かと役に立つ技ですね。
今まで、ちまちまセルを「Ctrl+クリック」で選択していたのは
何だっんだ・・・ですね。

今後活用させて頂きます。

紹介されているINDIRECT関数ですが
No.1のお礼にも書きましたが
行を挿入する関係でそのままですと、正しく反映しないので
参考の上、反映する様に弄りたいと思います。

むしろ最初から行を空けて計算式を組む方が良さそうですね。

回答No.1

keithin

方法１：丸投げを教わってコピーするだけなら短時間だが，実際に合わせて自分で考えるのは時間が掛かる役に立たない式
シート２のＡ１に
=INDEX(Sheet1!$A:$A,(ROW(A1)-1)*3+COLUMN(A1))&""
と記入してＣ１までコピー
下にコピー。

方法２：推奨
シート２のＡ１に
=Sheet1!A1&""
シート２の「Ｂ２に」
=Sheet1!B1&""
シート２の「Ｃ３」に
=Sheet1!C1&""
と式を入れる。必要に応じて右下に必要列数分伸ばす

Ａ１：Ｃ３を選んで下向けにオートフィルドラッグ
Ａ：Ｃ列を列選択
Ctrl+Gを押す
現れたダイアログでセル選択をクリックする
現れたダイアログで空白セルにマークする
飛び飛び選択を崩さないように右クリックして「削除」を選び，上に詰めて完成。

お礼

素早い回答ありがとうございます。

方法1はおっしゃるようにそのままコピーするだけなら楽ですが
自分で理解して考えるのが大変ですね・・・

質問には書いていませんでしたが、碁盤の目の様に並び替えたあと
行を挿入する事もあり行数に応じて関数を考えないとダメですね。

推奨されている方法2ですが・・・
>シート２のＡ１に
>=Sheet1!A1&""
>シート２の「Ｂ２に」
>=Sheet1!B1&""
>シート２の「Ｃ３」に
>=Sheet1!C1&""
>と式を入れる。
と書かれていますが・・・

以下でないとちゃんとリンクしないんですが・・・
シート２のＡ１に
=Sheet1!A1&""
シート２の「Ｂ２に」
=Sheet1!A2&""
シート２の「Ｃ３」に
=Sheet1!A3&""

私のやり方が悪いのかわからないのですが
書かれている方法でやってみたところ
該当するセルが見つかりませんと怒られるのですが・・