結合したセルに参照式を・・・

質問

伝わりにくいかもしれませんが、ご容赦下さい。
sheet1にA1:A4を結合したセルを作って、そこにsheet2のA1を参照するように
=sheet2!A1と式を入れます。
A5:A8の結合セルは、sheet2のA2、A9:A12の結合セルは、sheet2のA3を参照・・・
としたいので、A1:A4の結合セルを選択して、下方向にドラッグさせたのですが、A5:A8の
セルの参照式が=sheet2!A5になってしまいます。
どうやらA2:A4もカウントしてしまっているように思います。
一つ一つ手入力で入れて行けば問題なくできますが、ここにこれから何百データと入れて行くので、
手入力は非常に面倒で避けたいです。
ドラッグでA5:A8はsheet2のA2、A9:A12はsheet2のA3・・・とさせる方法はないでしょうか?
(要は、結合した中の最段以外の行はカウントしないように)

回答No.2

keithin · ベストアンサー

オートフィルドラッグ(コピー貼り付け)がＡ１の「次」にＡ５、その「次」にＡ９のように４つ飛ばしなので、参照するセルも同じだけ移動します。
「１行ずつ下がっている」ワケではありませんね。

方法１：
Ａ１：Ａ４結合セルに
=INDEX(Sheet2!A:A,ROW(A4)/4)
と記入し、下向けにオートフィルドラッグ

＃こうやって回答すると皆さん「これこれ」と飛びつきますが、どういう具合にROW関数を組み立てたら今必要な数字の並びが出てくるのか、誰かに書いてもらってコピーしてやるんじゃなく自分で考えなきゃならないのは面倒な方法です。

方法２：
Ａ１に
=Sheet2!A1
と記入、下向けに必要数（仮に１０個）オートフィルドラッグ
→１つずつ下げているので、当初の意図通りＡ２，Ａ３…を参照する式が記入される

Ｂ１に数字の１を記入
Ｂ２に数字の２を記入
Ｂ１：Ｂ２をつまんで下向けに、Ａ列に数式を入れた数だけオートフィルドラッグ
→１から１０までの数字が並ぶ

Ｂ１：Ｂ１０をコピーし、Ｂ１１以下に「あと３回」貼り付ける
Ａ：Ｂ列をＢ列昇順で並べ替える
Ａ１：Ａ４をセル結合し、コピー、下向けに形式を選んで貼り付けの書式のみ貼り付ける

補足

ありがとうございます。
方法1のやり方凄いですね。
INDEXとROW関数勉強してみたいと
思いますが、説明頂いた式でシート2の
A1:A10に1から10の数字を入れてドラッグ
したところ、1,2,3,4,6,7,...と5が抜けてしまいます。

回答No.5

tom04

こんばんは！
横からおじゃまします。

Sheet1のA1セルに
=OFFSET(Sheet2!A$1,(ROW()-1)/4,,1)
または
=INDEX(Sheet2!A:A,(ROW()-1)/4+1)

としてオートフィルで下へコピーではどうでしょうか？m(_ _)m

お礼

お礼が遅くなりました。
ありがとうございました。

回答No.4

某HN クロメート(Chromate)（@CoalTar）

A1セルはそのまま

A5セル(結合セル)に
=INDEX(Sheet2!A:A,COUNTA(A$1:A1)+1)
下へオートフィルでもいいかも

お礼

お礼が遅くなりました。
ありがとうございました。

回答No.3

basic_gg

1)sheet1にA1:A4を結合したセルを作って
2)そこに=sheet2!A1と式を入れます。
3)式の先頭に#を入れ、#=sheet2!A1とします。
4）A1:A4の結合セルを選択して、下方向にドラッグさせます。
5）ドラッグ終了直後(ドラッグ範囲が選択されている状態で)
6)ツールバーの"編集"-"置換"をクリックし、
7)検索する文字列に#を入れ、"すべて置換"をクリックします。
(全角と半角を間違わないでください)

お礼

お礼が遅くなりました。
ありがとうございました。

回答No.1

bin-chan

先に式設定してから、セル結合を書式のコピーでやっちゃダメですか？

お礼

お礼が遅くなりました。
ありがとうございました。