![](http://oshiete.xgoo.jp/images/v2/pc/qa/question_title.png?5a7ff87)
大量のデータを転記して別シートに並べ替えをしています。
A1 A2 A3 A4
B1 B2 B3 B4
C1 C2 C3 C4
↓
A1
A2
A3
A4
B1
B2
B3
B4
C1
C2
C3
C4
このように並べ替えをしたいのですが、
いくつかのファイルからひとつのワークシートにまとめて一覧にする作業なので、数値だけ並べ替えるのではなくリンクの状態で数式が並べ替て、後はリンク元のファイルを変更すると一気に数値が入る というような方法を探しています。
データが大量にあるので困っています。どなたか良い方法を教えてください。
No.2ベストアンサー
- 回答日時:
#1です。
[ROW-1]の[1]の部分を「転記先の最初の行の行番号」にすればうまく行くはずです。
転記先の先頭が[Book1]Sheet2!$H$5なら、
=OFFSET([Book1]Sheet1!$A$1,INT((ROW()-5)/4),MOD(ROW()-5,4))
転記先の先頭が[Book1]Sheet2!$BB$1069なら、
=OFFSET([Book1]Sheet1!$A$1,INT((ROW()-1069)/4),MOD(ROW()-1069,4))
です。
----------------------------------------
転記先の先頭位置がちょこちょこ動くのであれば、
=OFFSET([Book1]Sheet1!$A$1,INT((ROW()-ROW([Book1]Sheet2!$H$5))/4),MOD(ROW()-ROW([Book1]Sheet2!$H$5),4))
のように転記先の先頭セルの参照を入れておいた方が、
むしろ修正しやすいかもしれません。
----------------------------------------
一般的な書き方をすれば、
=OFFSET(【ア】,INT((ROW()-ROW(【イ】))/【ウ】),MOD(ROW()-ROW(【イ】),【ウ】))
ア:元データの左上隅セルの絶対参照
イ:転記先の先頭セルの絶対参照
ウ:列数
INDEXの場合は…
=INDEX(【ア】,INT((ROW()-ROW(【イ】))/【ウ】)+1,MOD(ROW()-ROW(【イ】),【ウ】)+1)
ア:元データ全体のセル範囲の絶対参照
イ:転記先の先頭セルの絶対参照
ウ:列数
となります。
教えていただいた方法で左側作業に入っていてすっかり御礼が遅くなってしまいました。
丁寧に教えていただいてどうもありがとうございました。何とか急場をしのぐことができました。
この辺のところがしっかり身につくと、いろいろな作業が飛躍的に早くなるのでしょうね。
本当にありがとうございました。
No.4
- 回答日時:
質問者には相応しくない(VBAは判らない)、関係ないかも知れないが、データが4列(一定)と限らないケースや、リンク貼り付けのことなども考えると
VBAで無いと解決しないでしょう。
例データ
A1A2A3A4A5A6
B1B2B3
C1C2C3C4C5
コード
標準モジュールに
Sub test01()
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")
d = sh1.Range("A65536").End(xlUp).Row
' MsgBox d
k = 2
For i = 1 To d
For j = 1 To 10 '最右列データがJ列までとする
If sh1.Cells(i, j) = "" Then Exit For
sh1.Cells(i, j).Copy
sh2.Activate
sh2.Cells(k, "A").Select
ActiveSheet.Paste Link:=True
k = k + 1
Next j
Next i
End Sub
これだとSheet1のデータを変えるとSheet2の対応データは変わる。
関数では=Sheet1!A3とか式を入れることになるが、関数では式を埋め込むことは出来ない。
ーー
既出回答は>はリンク元のファイルを変更すると・・を見てないのでは?
No.1
- 回答日時:
元データが[Book1]Sheet1!$A$1以下にあるとします。
元データが4列の場合、転記先の任意の列の1行目を、
=OFFSET([Book1]Sheet1!$A$1,INT((ROW()-1)/4),MOD((ROW()-1),4))
として下方にフィル。
※実際の列数に応じて4の部分を調整してください。
※OFFSETは「閉じた状態のブック」を参照できません。
もし、転記元のブックを閉じた状態で参照する必要があるのであれば、
INDEXを使って、
=INDEX([Book1]Sheet1!$A$1:$D$3,INT((ROW()-1)/4)+1,MOD(ROW()-1,4)+1)
のようにした方が良いかもしれません。
この場合、元データの範囲全体をあらかじめ指定する必要があります。
以上ご参考まで。
この回答への補足
早速ありがとうございます!
サンプルで試すと思い通りの結果になるのですが実際のシートでうまくいきません。サンプルと実際のシートの違いから考えると
この場合元データと転記先の頭の位置が同じ行数にないといけないということでしょうか?
元データを[Book1]Sheet1!$A$1、転記先を[Book1]Sheet2!$A$1だとうまくいくのですが、
元データを[Book1]Sheet1!$A$1、転記先を[Book1]Sheet2!$H$5のような場合、どこの数字を変更すればよいのでしょうか?
またAがHに、1が5に変更になったくらいなら手で直せると思うのですが、AがBB、1が1069 などという場合に何か良い方法はあるでしょうか?
どうにも行列に関する関数が苦手で・・・
お手数ですがよろしくお願いいたします。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- その他(Microsoft Office) エクセルで串刺ししたシートの並べ替えをしたいです 4 2023/02/14 11:59
- 建設業・製造業 見積作成(エクセル)について教えて下さい。 2 2023/05/10 13:47
- Excel(エクセル) 【エクセル】並び替えからの並び替え方法 7 2022/07/22 09:46
- Excel(エクセル) 棚卸表の前月比の関数等あれば教えてください 2 2023/05/02 18:34
- Visual Basic(VBA) Excel VBA 最終行を取得しVlookup関数をコピーする方法をコーディングで教えてください。 3 2023/05/11 13:14
- C言語・C++・C# C言語プログラム変更 2 2022/12/21 15:03
- Excel(エクセル) Googleスプレッドシートの割合の関数と円グラフの並べ替えについて 1 2022/07/22 17:31
- Visual Basic(VBA) 顧客ごとに違う点検案内を作成するマクロ 4 2022/09/16 05:34
- Google Drive Googleスプレッドシートについて質問です。 今作っているデータで、 シート1→ベタ打ちでひたすら 2 2022/05/18 14:27
- Excel(エクセル) エクセルのマクロについて教えてください。 3 2023/02/07 14:47
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
在庫管理表に使うエクセルの関...
-
下記マクロでMsgBox "空白です...
-
REGEXREPLACE関数について、
-
エクセルでバーコード作成し使...
-
【マクロ】for next構文について
-
ユーザー定義関数をアドイン登...
-
職場の人から聞かれており、こ...
-
Excel関数-文字列で自動作成さ...
-
エクセルで表
-
PDFの請求明細をエクセルにしたい
-
エクセルの関数について教えて...
-
Excelデータをコピペして、ペー...
-
スプレッドシート、Excelでの数...
-
LOOKUP関数を使えばいいのでし...
-
Excel VBについての質問です。
-
【マクロ】 IFERROR関数をマク...
-
エクセル日付 文字列の関数がエ...
-
【マクロ】読取専用のファイル...
-
【マクロ】その時、その時で変...
-
時間によってファイル名が変わ...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルVBA、別ブックへ転記す...
-
エクセルでの作業計算方法について
-
時間によってファイル名が変わ...
-
【関数】適切な文字数の数字を...
-
Excelについて教えてください
-
エクセル初心者です 関数の入れ...
-
【マクロ】ファイル名の変更に...
-
UNIQUE関数が使えないバージョ...
-
エクセルの計算
-
【関数】先頭だけにある、半角...
-
Excelで、決まった行を繰り返し...
-
Excelでセルの値が同じか...
-
LOOKUP関数を使えばいいのでし...
-
Excel
-
はがきについて。
-
エクセルの条件付き書式につい...
-
エクセルのデーターが2か月前の...
-
エクセル②
-
エクセルで「-0.0」と表示さ...
-
Microsoft1Officeの互換ソフト...
おすすめ情報