エクセルで別々のシート同士の参照とコピーのしかたについてお聞きします

質問

今、シート１のA1、A2、A3にデータが入っているとします（列データ）。これをシート2のA4、B4、C4にそれぞれコピーしたい（行データ）とします。つまり、A1→A4、A2→B4、A3→C4 にコピーしたいわけです。（縦のデータを横にコピーするとでも言いましょうか）

次のようにしてみました。
(1) A4に＝シート1!A1と入力する。
(2) A4をコピペでB4、C4にコピーする

すると、B4、C4の参照先がA2、A3にならず、B1、C1になってしまいます。

つまり、B4＝シート1!B1 などとなってしまうのです。
いまはいちいち手で修正しています。

実際は1月から12月までのデータを20支店にわたって上のようにコピペするのでちょっとたいへんな手作業です。

「形式を選択して貼り付け」＞行列を入れ替える、でやってみましたがうまくいきません。

オートフィルみたいにささっとできる方法はないでしょうか？

よろしくお願いいたします。

回答No.2

sakeman · ベストアンサー

関数でする一例を挙げますので参考にしてください。

セルA4に「=OFFSET(Sheet1!$A$1,COLUMN()-1,0)」と入力する。→右にオートフィルする。

質問の趣旨と異なっていたらご容赦を。

補足

さっそくどうもありがとうございました。

>セルA4に「=OFFSET(Sheet1!$A$1,COLUMN()-1,0)」と入力する。→右にオートフィルする。

ひとつお聞きしたいのですが、↑の COLUMN()-1,のうち()と-1は何を意味するのか、教えていただけませんか？

お手数をおかけします。

回答No.8

imogasi

VBAで考えると
Sub test01()
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Set sh1 = Worksheets("sheet1")
Set sh2 = Worksheets("sheet2")

For i = 1 To 3
sh2.Cells(4, i) = sh1.Cells(i, "A")
Next i
End Sub
です。
骨子は
sh2.Cells(4, i) = sh1.Cells(i, "A")にあり、
4　　　i

i 　 "A"
と、変化するiがクロスする位置に来るところにあります。

お礼

VBAによる方法のご紹介、ありがとうございました。

残念ながらVBAは全く知りませんが、ほかの方の参考になればいいなあと思いました。

どうもありがとうございました。

回答No.7

sakeman

＃２の者です。

＞ひとつお聞きしたいのですが、↑の COLUMN()-1,のうち()と-1は何を意味するのか、教えていただけませんか？

COLUMNは列番号を返す関数です。例えば、Ａ列は１、Ｂ列は２を返します。即ち、この関数が入っている列の番号を返します。この関数は引数を必要としませんので、COLUMNの後に、「()」だけを付けます。例えばTODAY()と同じです。

OFFSET関数は、基準セル（Sheet1!$A$1）から指定した行数，列数をシフトしたセルやセル範囲を参照します。２番目の引数（COLUMN()-1）はシフトする行数を指定します。例えば、Sheet2のセルB4にはSheet1のセルA2を参照する（即ち１行下）ので、B4セルの「COLUMN()」の値は２になるので、「-1」で「1」にしています。

なお、Ｂ列，Ｃ列・・・も参照する場合は、セルC4に同様の考えで、「=OFFSET(Sheet1!$A$1,COLUMN()-1,ROW()-4)」と入力し、オートフィルしてください。

お礼

（）をなかなか理解できませんでしたが、TODAY()でああ、なるほどと思いました。

親切に教えていただきまして、ありがとうございました。

回答No.6

noname#11594

#3です。
　Σなど関数が設定されている場合でも、
「編集」→「形式を選択して貼り付け...」そして「行列を入れ替える」をチェックして[OK]ボタンを押せば、横に足してたものは、縦に足されるよう、また、縦に足してたものは、横に足されるように、式が変形されます。

補足

何度も実験していただいて感謝いたします。この件、私も、もう一度試してみます。

ありがとうございました。

回答No.5

Wendy02

こんばんは。

#1 さんの
＞>2. 「ツール」－「ワークシート分析」－「ワークシート分析モード」で数式表示に切り替え、

それは、Excel 2002 以上の仕様です。Excel 2000 にはありません。また、外部ツールのエディタを使うのでしたら、VBA でも可能です。

ですが、
＞形式を選択して貼り付け」＞行列を入れ替える、でやってみましたがうまくいきません。

ここの部分が気になります。
私も、#3 さんと同じように、それだけに関しては、問題がありません。なぜ、「うまくいかない」のですか？

#2 さんの回答とは違う方法としては、

B4:
=INDIRECT("Sheet1!A"& Column())

後は、フィルハンドル・ドラッグコピーで出来ます。

お礼

>私も、#3 さんと同じように、それだけに関しては、>問題がありません。なぜ、「うまくいかない」のですか？

別のところで申したように私の間違いでした。すみません。

INDIRECT関数は初めて知りました。いろんなアプローチがあることに驚いています。

ご教示を感謝いたします。

回答No.4

buihyaku

#1です。

>>1. 12月まで縦にシート1!A1, シート1!A2...となるよう並べる

>というのはどこに並べるのですか？エクセルのシートで空いている部分でしょうか、

エクセルのシートのどこか空いてるところです。

>等号＝は不要ですね。それと、一つ一つ手で入力するわけですね。オートフィルでまとめて作るというわけにはいきませんよね。

ええと、ですね。たとえばシート２のH１が空いていたとしたらそこに「=Sheet1!A1」というようにシート１の1月？への参照を入力します。
あとはそのセルを選択して右下をつかんでずるずると下に12か月分コピーします。
そうすると勝手に「=Sheet1!A2」「=Sheet1!A3」。。というようにコピーされるはずです。

＞ 「ツール」－「ワークシート分析」－「ワークシート分析モード」

このコマンドを実行しただけで先に作ったセルが数式表示になりませんか？
そうしたらその縦に並んだ12か月分を選択してコピーしてテキストファイルへ持っていく、という感じになります。

こちらの環境は2002ですので、もし2000だとこのコマンドがないのであれば、このやりかたは無理だとおもいます。ごめんなさい。

お礼

2000だと「ワークシート分析」でセルの数式表示はできないようですね。残念。

ということで、＝抜きでオートフィルで縦長の文字列を作り、あとはおっしゃるようにテキストエディターで作業しました。

まず、＝とタブをつけてEXCELに戻してやりました。別の支店名を入力するためにテキストエディターの「文字列の置換」を使うとあっという間にシート名を別名にできました（シート名は支店名になっています）。作業時間が私の当初のものより半分に短縮できました。この方法だと参照先がはっきりわかるので初心者には安心できると思いました。

色々親切に教えていただいてありがとうございました。

回答No.3

noname#11594

手元でやってみましたが
「形式を選択して貼り付け」＞行列を入れ替える
でうまくいきましたよ？

シート１のA1、A2、A3を選択して「編集」→「コピー」
シート2のA4のみを選択して「編集」→「形式を選択して貼り付け...」そして「行列を入れ替える」をチェックして[OK]ボタンを押すと、A4,B4,C4にA1,A2,A3の内容がコピーできました。

お礼

その通りです。コピーできることを確認しました。お手数をおかけしました。

今困っているのはシート１のA1、A2、A3 にΣなど関数が設定されている場合です。質問の時、内容を簡素にするために数字データがA1、A2、A3 に入っている場合を想定したものです。

どうもありがとうございました。

回答No.1

buihyaku

そのものずばりのやり方はわかりませんが、一度縦に数式をならべておいて、それをコピーしてテキストエディタで横に並べ替えてから貼り付けしなおしてはどうでしょうか。

1. 12月まで縦にシート1!A1, シート1!A2...となるよう並べる
2. 「ツール」－「ワークシート分析」－「ワークシート分析モード」で数式表示に切り替え、数式をまとめてコピー
3. テキストエディタに貼り付けると改行で区切られてずらりと並びますので、改行をタブに置換
（たとえば秀丸ですと 「\n」を「\t」に正規表現で置換します。）

4. タブで区切ってずらりとならんだデータをコピーしてエクセルに改めてはりつけると横に並んで貼り付けられます。

補足

さっそくのアドバイス、感謝いたします。色々わからないところが出てきました。すみませんが教えてください。

>1. 12月まで縦にシート1!A1, シート1!A2...となるよう並べる

というのはどこに並べるのですか？エクセルのシートで空いている部分でしょうか、それともテキストエディターに、でしょうか？また、等号＝は不要ですね。それと、一つ一つ手で入力するわけですね。オートフィルでまとめて作るというわけにはいきませんよね。

>2. 「ツール」－「ワークシート分析」－「ワークシート分析モード」で数式表示に切り替え、

私のはEXCEL2000なのですが、どうも数式表示というのができないのですが、範囲の選び方とかがまずいのでしょうか？

よろしくお願いいたします。