OFFSET関数の使い方

質問

EXCELで、文字が入った行を別のワークシートで参照したいのですが、

=OFFSET(Sheet5!$C$1,0,0)
として、
その下の行は
=OFFSET(Sheet5!$C$1,1,0)
さらにその下は
=OFFSET(Sheet5!$C$1,2,0)
さらにその下は
=OFFSET(Sheet5!$C$1,3,0)
というふうに数字を１つずつ増やしたのですが、セルをコピーしてマウスで下にドラッグしても
連続して数字が増えていきません。

=OFFSET(Sheet5!$C$1,この部分,0)　→ここの部分の数字を１つずつ増やして
いくにはどうしたらいいのでしょうか？

回答No.5

tom04 · ベストアンサー

No.2です。

＞参照元のセルの文字に、色をつけているのですが、
＞参照したほう側では、それが反映されないのでしょうか？

数式によって返ってくる結果は「値」だけだと思います。
すなわち参照先の書式までは表示することはできないはずです。

他の方の補足の
＞７行ずつ、参照して次にまた残りの行列から、別の表を繰り返し作りたのですが
とあり新たに画像をアップされていますが、
元データの書式も別Sheetに7行・2列のデータにしたい！という場合は
VBAになってしまいますが一例です。
元データはSheet1のA2セル以降にあり、Sheet2に表示させるとします。

Alt＋F11キー　→　メニュー　→　挿入　→　標準モジュール　→　VBE画面のカーソルが点滅しているところに
↓のコードをコピー＆ペースト　→　Excel画面に戻りマクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）

Sub Sample1() 'この行から
Dim i As Long, cnt As Long, wS As Worksheet
Set wS = Worksheets("Sheet2")
Application.ScreenUpdating = False
wS.Cells.Clear
With Worksheets("Sheet1")
For i = 2 To .Cells(Rows.Count, "A").End(xlUp).Row Step 14
.Cells(i, "A").Resize(7).Copy wS.Cells(Rows.Count, "A").End(xlUp).Offset(2)
.Cells(i + 7, "A").Resize(7).Copy wS.Cells(Rows.Count, "B").End(xlUp).Offset(2)
Next i
End With
wS.Rows(1).Delete
For i = 1 To wS.Cells(Rows.Count, "A").End(xlUp).Row
If wS.Cells(i, "A") = "" Then
cnt = cnt + 1
wS.Cells(i, "A") = "表" & cnt
End If
Next i
Application.ScreenUpdating = True
End Sub 'この行まで

※　関数でないのでデータ変更があるたびにマクロを実行する必要があります。
書式もSheet1の書式をコピー＆ペーストしています。m(_ _)m

お礼

ありがとうございました。
さっそく試してみましたが、とても便利です。
セルの位置など変更しながら、自分なりに応用してみたいと思います。
いろいろ勉強になりました。ありがとうございました。

回答No.4

bunjii

>今回の関数を応用して行７，列２の表を繰り返し作成したのですが７行ずつ、参照して次にまた残りの行列から、別の表を繰り返し作りたのですが、２つ目の表を作るところは、どのしたらいいのでしょうか？
元データがA2から下へ入力されているものとして、C2:D8に1つ目の表を作成すし、C10:D16へ2つ目の表を作成する場合は次のようにすれば良いでしょう。

C2=OFFSET($A$1,MOD(ROW(A1),8)+INT(ROW(A1)/8)*14+(COLUMN(A1)-1)*7,0)
C2セルをC2:D8へコピー&ペーストで複写します。
次にC2:D8を選択してコピーし、C10へ貼り付けます。
更にC18へ貼り付ければ3つの表ができます。
縦に4つ目以降を貼り付けるときはC26、C32、・・・・のように等間隔で貼り付ければ目的の表になるでしょう。

別のシートに作成する場合は元データの位置はSheet1!$A$1のように変更します。
また、行番号の算出をSHEET関数を応用します。
Sheet2のC2セルは次の数式になります。
=OFFSET(Sheet1!$A$1,MOD(ROW(A1),8)+(SHEET()-2)*14+(COLUMN(A1)-1)*7,0)
SHEET()の戻り値はタブの位置で左から1、2、3、・・・・のようになります。
貼付画像はExcel 2013で元データの中に表1～表3を作成したものです。

お礼

bunji様　さっそく、ありがとうございました。
なかなか高度な内容で、まだ自分自身関数の意味を理解しきれていませんが
大変勉強になります。
これで、なんとか作成してみます。どうもありがとうございました。

回答No.3

bunjii

>セルをコピーしてマウスで下にドラッグしても連続して数字が増えていきません。
>OFFSET(Sheet5!$C$1,この部分,0)　→ここの部分の数字を１つずつ増やして いくにはどうしたらいいのでしょうか？
OFFSET関数の引数で行番号や列番号に実数を使うと定数なのでオートフィルでコピーしても変化しません。
変化させるにはセルの属性を利用しないといけません。
例えば提示の =OFFSET(Sheet5!$C$1,0,0) で行番号の0の代わりに ROW(A1)-1 とすれば下へコピーしたときA1がA2に変化しますので結果として ROW(A2)-1 → 2-1 → 1 となります。

尚、文字列に数字が付加されたデータをオートフィルでコピーすると最も右側の数字が繰り上がります。
提言の数式を文字列に置き換えても列番号は変化しますが行番号は左側の数字なので繰り上がりません。
理由は追求せずにそのような動作になっていることを認識してください。

お礼

詳しい解説ありがとうございました。いろいろ勉強したいです。

連続した文字が入っている行を参照して、きまったサイズの表を作りたいのですが、

今回の関数を応用して行７，列２の表を繰り返し作成したのですが

７行ずつ、参照して次にまた残りの行列から、別の表を繰り返し

作りたのですが、２つ目の表を作るところは、どのしたらいいのでしょうか？

（質問のページに写真をアップしました。）

回答No.2

tom04

こんにちは！

OFFSET関数に関しては
すでに的確な回答が出ていますので、余計なお世話かもしれませんが・・・

=INDEX(Sheet5!C:C,ROW(A1))
とか
=INDIRECT("Sheet5!C"&ROW(A1))
でも同じ結果が得られると思います。m(_ _)m

お礼

ありがとうございます！

補足

またまた教えて下さい。

参照元のセルの文字に、色をつけているのですが、
参照したほう側では、それが反映されないのでしょうか？
参照元の文字の一部をボールドにした場合、
別のワークシートで参照した場合、そちらに書式も同じように
参照する方法はありますか？

回答No.1

kybo

以下の様な感じでどうでしょうか。
最初のセルに「ROW(A1)-1」と答えが0になる式を入れておけばいいです。

=OFFSET(Sheet5!$C$1,ROW(A1)-1,0)

お礼

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