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

Question

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

シート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の後に任意の数字(セル番号)を入れる様にしています。
(数字が入った状態だと数字を消す手間が増ますので。)

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

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

mitarashi · Accepted Answer

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

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

やりようはある様な気がします。
下記ではお役に立てませんか？参照元（ご質問の例では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メソッドのダイアログ表示中にブックをまたぐのは無理みたいですね。出来るなら組み込んでみようかと思ったのですが。
安直には、同じブック内にリンク作成後、他のブックにコピーすれば通用します。ご参考まで。

mitarashi · Answer

#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　絶対参照しない。

keithin · Answer

次の通り操作します。

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

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

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

mitarashi · Answer

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

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

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

mitarashi · Answer

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

pc_knight · Answer

やりたいことは、「シート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

MarcoRossiItaly · Answer

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」などと行番号も書いている場合でも、行を挿入した際に、式中の参照範囲も自動修正されるので、やはり問題は発生しません）

MarcoRossiItaly · Answer

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
・
・
・

keithin · Answer

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

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

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

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

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

この回答への補足

#4です

次の通り操作します。

#4です。

この回答への補足

#4です。

やりたいことは、「シート1にある店番データを「シート2」に碁盤の目の様に表示させたい」ということでしょうか。

No.2です。

この回答への補足

No.1さんの「方法１」は成功しそうですが、「方法２」は成功しますか…？

方法１：丸投げを教わってコピーするだけなら短時間だが，実際に合わせて自分で考えるのは時間が掛かる役に立たない式

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング

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