エクセルで等間隔のセルをオートフィル機能として使いたい

質問

質問です。

エクセルでA1のセルには「=SEET1!$G1」、B1のセルには「=SEET1!$G36」、C1のセルには「=SEET1!$G71」というように、隣のseetのG列の35ずつずれたセルを反動させていきたいのですが、そのまま打ち込みオートフィルをかけると、この3つのセルの式を繰り返すだけになってしまいます。

このようなオートフィルは不可能なのでしょうか。

よろしくお願いします。

回答No.1

telescope · ベストアンサー

COLUMN関数で列番号を取得して計算させればできます。

A1セルに
=INDIRECT("Sheet1!$G"&(COLUMN()-1)*35+1)
列方向にフィルします。

お礼

ありがとうございます。
ちなみに、宜しければ意味を教えて頂けますか？

INDIRECT、COLUMNのそれぞれの意味は調べたので、大体分かりましたが、こう組み合わせることでの意味が理解出来ていません。

お願いします。

回答No.3

telescope

>最初、A1の場合“-1”することで0になり、そこに35を掛けても0、そして+1で1、それが&でつないだseet1のG列の行数になるって考えかと理解しました。
そのとおりです。
>その場合、次のセルはB1なので、数字的にはやはり0になってしまうので、違うのだろうと思いますが。
COLUMN() は、A列を１、B列を２、C列を３・・・・とする列番号を返します。
B1セルの場合
B列の列番号は2ですから、COLUMN()は２を返します。
=(COLUMN()-1)*35+1=(2-1)*35+1=36
B1セルというのは、B列の1行目のセルという意味です。
COLUMN関数は列番号を求める関数ですから、=COLUMN(B1)　でも　=COLUMN(B30)　でも　列番号の「２」を返します。
=COLUMN()　という式を任意のセルに入力してみてください。
A列であれば、A1でもA5でもA30でも「１」を返します。B列であれば、「２」を返します。

>『=INDIRECT("SEET1!$G"&(COLUMN()-1)*35+1)』というのが、C30に入り、そこからD3,E30とオートフィルさせたいので、教えて頂いた式だと、微妙にずれてしまいました。
C列からはじめるのであれば、(COLUMN()-3)*35+1　にすれば良いと思います。
=INDIRECT("SHEET1!$G"&(COLUMN()-3)*35+1)

お礼

出来ました！しかも理解しました。
ほんとありがとうございます。

最後になりますが、2回目の回答で頂いた
>INDIRECT関数は、文字列で指定したセルの内容を返します。
INDIRECT関数に組み込むことで、指定したセル内容が得られるというわけです。

というのは、今回の場合、参照にするのが隣のseetであったから、『SEET1』という文字列を含む為、ということでしょうか。
これが単に同じシートのほかのセルであれば、この関数は使わずに済んだのでしょうか。

意味は理解できましたが、こんなに沢山の関数を組み合わせることが浮かぶのが信じられないです、すごいです。

回答No.2

telescope

COLUMN関数は指定したセルの列番号を返します。省略すると入力されているセルの列番号を返します。
A1セルに
=COLUMN()
と入力した場合
=COLUMN(A1)
と同じでA列の列番号1を返します。
これを利用して、A1セルでは1を、B1セルでは36、C1セルでは71を求めたいので、
=(COLUMN()-1)*35+1
という式を使いました。
この式をA列に入力して列方向にフィルしてみてください。
各セルには、まったく同じ式が入力されていますが、1,36,71,106・・・と表示されています。
規則的に数値が増減する場合、オートフィルが使えないときには、列番号や行番号を利用すると便利です。
行番号を取得するには、ROW関数を使います。

Sheet1のGと組み合わせます。文字列ですから""でくくって、＆でつなぎます。
="Sheet1!$G"&(COLUMN()-1)*35+1
この式もA列に入力して列方向にフィルしてみてください。
Sheet1!$G1、Sheet1!$G36・・・と表示されると思います。ただセル番地が表示されるだけで、その内容はわかりません。

ここでINDIRECT関数の出番です。
INDIRECT関数は、文字列で指定したセルの内容を返します。
INDIRECT関数に組み込むことで、指定したセル内容が得られるというわけです。

INDIRECT関数については、こちらのサイトが参考になると思います。
http://officetanaka.net/excel/function/function/indirect.htm

お礼

度々申し訳ございません。
ご丁寧にありがとうございます。

なんとなく、意味は分かりました。
>A1セルでは1を、B1セルでは36、C1セルでは71を求めたいので、
=(COLUMN()-1)*35+1
という式を使いました。

最初、A1の場合“-1”することで0になり、そこに35を掛けても0、そして+1で1、それが&でつないだseet1のG列の行数になるって考えかと理解しました。

その場合、次のセルはB1なので、数字的にはやはり0になってしまうので、違うのだろうと思いますが。

今作成している表では、説明不足でしたが、
『=INDIRECT("SEET1!$G"&(COLUMN()-1)*35+1)』というのが、C30に入り、そこからD3,E30とオートフィルさせたいので、教えて頂いた式だと、微妙にずれてしまいました。
でも、やりたい事は出来てたので、感動しました。

もっと理解出来れば自分で応用できるのですが・・・。

ほんとすいません、教えてください。