エクセル 結合セルへのコピー

質問

以前の投稿でも書いてあったのですが　
シート２のＢ２～Ｂ２６を結合しています。以下同じように
Ｂ２７～Ｂ５１・・・・・以下同じように結合しています。
このＢ列にシート１のＢ２～Ｂ２００までの結合していない
データを貼り付けたい
のですが。以前の検索を見ると、ＯＦＦＳＥＴ関数と
ＲＯＷ関数で解決できると書かれてありました。見たのですが
なかなか理解できませんでした。
貼り付けるデータの量がとても多く、どうか教えていただけない
でしょうか。お願いします。

回答No.1

noname#79209 · ベストアンサー

「貼り付けたい」という意味が、文字通り貼り付けたいという意味なら、
ワークシート関数では不可能です。
そうではなく、
シート２のＢ２、Ｂ２７、Ｂ５２・・・が、それぞれ
シート１のＢ２、Ｂ３、Ｂ４・・・を参照するようにしたいなら、
シート２のＢ２に
=INDIRECT("Sheet1!B"&INT((ROW()-1)/25)+2)
で下にコピーすればいけます。
OFFSET関数はいりません。

お礼

ありがとうございます。とてもうまくいきました。
以前から、結合セルを領域の違う(範囲の違う）
セルにコピーをするときに困っていました。
とても助かりました。もしできればこの式の
意味を教えていただければ大助かりです。
また、以前、このサイトで同じような質問が
検索すると載っており、そのときには、

=OFFSET(Sheet1!$A$1,(ROW()-1)/3,)

上記のような式が回答に書かれてありました。
ヘルプや本を見て調べたのですが、どうも私の
頭では、これらの式は理解できないようです。
よろしければ、合わせてご指導願えれば
ありがたいです。

回答No.4

noname#79209

@1です。
#2で
=INDIRECT("Sheet1!B"&INT((ROW()-1)/25)+2)
の説明をしましたが、むしろ
INT((ROW()-1)/25)+2
をどうやって作り出したかの解説の方がが肝心でしたね。
B2、B27、B52・・・からB2、B3、B4・・・作り出さなくてはなりません。
B2、B27、B52は行番号が25づつ増加しています。
これをB2、B3、B4と1づつの増加に変換したいと考えたとき
2、27、52は本来1、26、51となっているべきです（1行目が表題なので）
これを25で割れば1づつの変化に変換できます。
そこでINT((ROW()-1)/25)とすると、0、1、2となるので+2をしています。

回答No.3

noname#79209

#1です。
=OFFSET(Sheet1!$A$1,(ROW()-1)/3,)
についてです。
OFFSET関数は、指定した1つのセル番地を基準として、
そこから、縦横に指定した行と列分離れたセルから、
縦横に指定した行と列分のエリアをセル範囲として返します。
言葉にするとわかりにくいのですが、
たとえばA1を基準と決めたら
=OFFSET($A$1,1,2,3,4)
は
A1から、1行下の2列右（つまりC2）から始まって、
縦に3行分、横に4列分の範囲を、セル範囲として返します。
つまり、C2:F4というセル範囲を返します。
セル範囲を返すので、OFFSET関数単体では、あまり使い道はありません。
範囲を指定する関数と組み合わせて意味が出てきます。
=SUM(OFFSET($A$1,1,2,3,4))とすれば、=SUM(C2:F4)と等価になります。
OFFSET関数の良いところは、上記の1,2,3,4に当たるところを、計算式やセル番地を指定することで、OFFSET関数の外側の計算式が参照するセル範囲をダイナミックに変化させることができる点です。
つまり、特定のセル範囲を「キメ打ち」で設定したくない時に真価を発揮します。

でも、
=OFFSET(Sheet1!$A$1,(ROW()-1)/3,)
は、あまり意味がないような．．．

回答No.2

noname#79209

#です。
=INDIRECT("Sheet1!B"&INT((ROW()-1)/25)+2)
についてです。
INDIRECT関数は、いわば「間接参照」とでもいう機能です。
括弧内の文字列や、セル参照のされたセルの中身の文字列を、
たかも直接したかのように変換してくれます。

=INDIRECT("Sheet1!A1")　は　=Sheet1!A1　と等価です。
また、B1のセルに文字列で、"Sheet1!A1"と入っていたら、
=INDIRECT(B1)　もまた　=Sheet1!A1　と等価です。
従って、
=INDIRECT("Sheet1!B"&INT((ROW()-1)/25)+2)
は
この関数が入っているセルがB2なら、行番号は「2」ですから
ROW()は当然「2」なので
=INDIRECT("Sheet1!B"&INT((2-1)/25)+2)
　↓
=INDIRECT("Sheet1!B"&INT(1/25)+2)
　↓
=INDIRECT("Sheet1!B"&INT(0.04)+2)
　↓
=INDIRECT("Sheet1!B"&(0+2))
　↓
=INDIRECT("Sheet1!B"&2)
　↓
=INDIRECT("Sheet1!B2")　つまり　=Sheet1!B2　と等価。

同様にB27にある計算式は、
=INDIRECT("Sheet1!B"&(1+2))
　↓
=INDIRECT("Sheet1!B"&3)
　↓
=INDIRECT("Sheet1!B3")　つまり　=Sheet1!B3　と等価。

お礼

vizzarさんありがとうございました。しかもとても
分かりやすく説明解説まで頂き感謝しております。
はじめて聞く関数ですが、なかなか奥の深い利用
方法がありそうですね。感謝！感謝！