- ベストアンサー
エクセルで大量のセルをリンクする方法
- エクセルで大量のセルのリンク方法について教えてください。シート1にA1セルからA10セルまで任意の値が入っている場合、シート2でそれらの値をリンクする方法はありますか？また、縦と横を入れ替える場合はどのようにすれば良いですか？
- 当方、縦に並んでいる店番号リストを碁盤の目のように並べてリンクする必要があります。しかし、店番号が3,000以上あり、手作業で修正するのは非常に困難です。また、店番号はランダムに並んでおり、計算式を用いて計算することもできません。効率的かつ短時間で修正する方法がありますか？
- エクセルで大量のセルのリンク方法について教えてください。また、縦と横を入れ替える場合はどのようにすれば良いですか？当方、縦に並んでいる店番号リストを碁盤の目のように並べてリンクする必要がありますが、店番号が3,000以上あり、作業が非常に困難です。どなたか効率的な方法を教えていただけないでしょうか？
- みんなの回答 （9）
- 専門家の回答
質問者が選んだベストアンサー
その他の回答 (8)
- mitarashi
- ベストアンサー率59% (574/965)
#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
- ベストアンサー率66% (5278/7941)
- mitarashi
- ベストアンサー率59% (574/965)
#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に修正して マクロを走らせたところ完走するんですが正常にリンクが貼られないです・・・ 他の回答にも書いていますが私自身マクロはそれ程詳しくない事もあり 意味をちゃんと理解できていないので、どうしたらいいのかわからない状態です。 お手数ですがもう一度お願い致します。
- mitarashi
- ベストアンサー率59% (574/965)
- pc_knight
- ベストアンサー率66% (52/78)
やりたいことは、「シート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を組み合わせる事を考えています。 マクロを組んで頂きありがとうございます。 私のマクロのスキルは初歩程度しか持ち合わせていないので 内容を理解するところから始めないと話にならないですね・・・
- MarcoRossiItaly
- ベストアンサー率40% (454/1128)
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さんのマクロを使用する方向で考えています。 せっかく教えて頂き申し訳ないのですが・・・ 色々教えて頂きありがとうございます。
- MarcoRossiItaly
- ベストアンサー率40% (454/1128)
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のお礼にも書きましたが 行を挿入する関係でそのままですと、正しく反映しないので 参考の上、反映する様に弄りたいと思います。 むしろ最初から行を空けて計算式を組む方が良さそうですね。
- keithin
- ベストアンサー率66% (5278/7941)
方法１：丸投げを教わってコピーするだけなら短時間だが，実際に合わせて自分で考えるのは時間が掛かる役に立たない式 シート２のＡ１に =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&"" 私のやり方が悪いのかわからないのですが 書かれている方法でやってみたところ 該当するセルが見つかりませんと怒られるのですが・・
お礼
せっかくマクロを組んで頂いて申し訳ないのですが 私のスキルでは内容がさっぱり理解できないです・・・ 初歩的なマクロならある程度理解できますが・・・ 本当に申し訳ございません・・・
補足
当方、当初、マクロは動作確認がやっかいなので 視野に入れていませんでしたが・・・ 関数を色々やっていくうちに頭が混乱してきました・・・ ワラにもすがる思いで改めてマクロを実施してみたところ No.4さんのマクロでOKでした! 絶対参照にして頂いているんですが当方としては相対参照が 都合がよいのでどの行を変更すればよいのか教えて頂けますでしょうか? 当方この質問サイトを利用するのが初めてという事もあり 最初からお礼に入れてしまいました・・・