OFFSET関数に関して

質問

今仕事で作業中のエクセルファイルに関して質問です。
複数のシートをもとに作業しています。

シートAの中のひとつのセルでは、シートBのあるセルが指定されています。
シートBのセルの一つ上と、二つ上のセルの数字をシートAに引用したいのですが、
OFFSETはあくまで同じシート上のものしか指定できないようです。

上記を解決する方法があれば御教授願えないでしょうか？
宜しくお願いいたします。

回答No.6

noname#204879 · ベストアンサー

[No.2補足]へのコメント、

添付図参照
シート「Document 2013」のＨ、Ｉ、Ｊの各列のデータには重複がないとすればハナシは簡単です。
シート「SheetA」のセル F12 に式 ='Document 2013'!H9008 を入力して、此れを右２列にドラッグ＆ペーストしています。

セル F14 に次式を入力して、此れを右２列にドラッグ＆ペーストすれば、夫々１行上のデータが得られます。
=INDEX('Document 2013'!H:H,MATCH(F12,'Document 2013'!H:H,0)-1)
末尾の数値 1 を 2、3 に変更すれば、夫々２行上の、３行上のデータが得られるという次第です。

お礼

ご回答有難うございます。
また添付図も頂き恐縮です。
まだ少しINDEXとMATCHを組み合わせた関数の理屈が把握し切れていないのですが、問題なく活用できているので取り急ぎこちらを使用させていただきます。
ありがとうございました。

回答No.7

toshi_2010

ANo.3 補足です。

'Document 2013'!H9008の２行上から
'Document 2013'!J9008の１行上に

固定できるのでしたら、A１セルに'Document 2013'!$H$9006：$J$9007

'Document 2013'!H9008の２行上は、=index(INDIRECT($A$1),1,1)
'Document 2013'!H9008の１行上は、=index(INDIRECT($A$1),2,1)

'Document 2013'!I9008の２行上は、=index(INDIRECT($A$1),1,2)
'Document 2013'!I9008の１行上は、=index(INDIRECT($A$1),2,2)

'Document 2013'!J9008の２行上は、=index(INDIRECT($A$1),1,3)
'Document 2013'!J9008の１行上は、=index(INDIRECT($A$1),2,3)

という方法もあります。

お礼

補足有難うございます。
INDEXとINDIRECTを組み合わせる方法もあるのですね。
今後のためにも最初に頂いた回答と併せて参考にさせていただきます。
ありがとうございました。

回答No.5

keithin

シートＡのＡ１セルに
=Sheet2!F10
のように記入されていた時に、この「数式を直接編集して」
=OFFSET(Sheet2!F10,-1,0)
のような数式を新たに書き起こせば、問題なく１つ上を取ってくることもできます。

しかし、
=Sheet2!F10
と書かれている「このセル」を利用して、その一つ上を取ってくるといった方法はありません。

＃参考
敢えて言うと、Sheet2!F10と一致する内容がシート２に「Ｆ１０セルただ一つだけ」しか絶対に無ければ、シート２からその値を検索して一つ上を取ってくることは可能です。
=INDEX(Sheet2!F:F,MATCH(A1,Sheet2!F:F,0)-1)
でも通常はそういう事が保証できる理由は何もないので、こういう方法もつかえません。

回答No.4

tsubuyuki

回答ＮＯ２の補足（１）（２）を見る限り、
ごくふつーに相対参照させてフィルやコピー貼り付けで解決できる問題だと思いますが・・・

まぁ、とりあえず、例を元に。

> F12：='Document 2013'!H9008
> G12：='Document 2013'!I9008
> H12：='Document 2013'!J9008

例えば、H12セルを基準として考えます。
別セル（今回はI12とさせて下さい）に、文字列で基点を指定します。
「J9008」ですかね。
で、INDIRECTと言う関数を使ってみます。関数の詳細はヘルプなりご覧ください。
H12セル⇒=INDIRECT("'Document 2013'!"&I12)
これで、'Document 2013'!J9008セルの中身を参照してきます。
続いて、G12セル⇒=OFFSET(INDIRECT("'Document 2013'!"&I12),0,-1)
および、F12セル⇒=OFFSET(INDIRECT("'Document 2013'!"&I12),0,-2)
これでなんとなくご希望の動きをするのではないかと。

最初に参照するI12セルに元のシート名まで入れれば、INDIRECTの中身は短くできます。

でも、こちらのほうが手間じゃないです？
単純にフィルやコピーで片が付くなら、そちらを採用すべきだと思いますが・・
こればかりは好みの問題ですから、他人は何も言えませんけどね。

そんなわけで、
> OFFSETはあくまで同じシート上のものしか指定できないようです。
これは勘違いですので、この考えは捨ててやってくださいね。

お礼

ご回答有難うございます。
小生の知識不足を補っていただき有難うございます。
頂いた情報参考にさせていただきます。

回答No.3

toshi_2010

>シートAの中のひとつのセルでは、シートBのあるセルが指定されています。
>シートBのセルの一つ上と、二つ上のセルの数字をシートAに引用したいのですが、
シートＢの範囲が分かってい（特定でき）て、
ある値でシートAからシートBに検索できるようでしたら、
ＩＮＤＥＸ関数とＭＡＴＣＨ関数を使用してもできます。

>OFFSETはあくまで同じシート上のものしか指定できないようです。
そんなことはありません。OFFSETは他シートも参照できます。

＜＜ANo.1の方での補足より＞＞
>シート１の例えばB３に＝Sheet2！A１と入力されている場合、
>そのシート１のB３を使って（経由して）シート2のA１を引用することは出来ないでしょうか？

それでしたらシート１のセルから
=INDIRECT（B3)

同じくシート１のセルから
=INDIRECT("Sheet2！A１")でも参照できます。

ですので=OFFSET(INDIRECT(B3),・・・・・・・・・・・・とか試してみて下さい。

お礼

ご回答有難うございました。
OFFSETに関しては誤解しておりました。
勉強不足で、INDIRECTの存在を初めて知りました、色々と試してみます。

回答No.2

noname#204879

》 シートAの中のひとつのセルでは、シートBのあるセルが指定されています
「シートBのあるセルが指定され」るために、「シートAの中のひとつのセル」には何が書かれているのですか？つまり、「指定」の方法をお尋ねしています。

》 シートBのセルの一つ上と、二つ上のセルの数字をシートAに引用したい…
「シートAの中の」その式は、上で述べた「シートAの中のひとつのセル」を参照しなければならないのですか？

》 OFFSETはあくまで同じシート上のものしか指定できないようです
その根拠は何ですか？

補足

早速のご回答有難うございます。

(1)例を挙げると、

シートA
F12：='Document 2013'!H9008
G12：='Document 2013'!I9008
H12：='Document 2013'!J9008

とかかれています（シートB＝Document 2013）

(2)シートAはシートBの必要な情報のみを抜粋、或いはその情報をもとに利
益率等を換算するシートです、そしてシートBは関連する情報が全て掲載されたマスタシートです。今引用したい情報をシートBから一つ一つ引っ張ってくることは可能ですが、規則性無くシートBに記載されていて、またかなりの数を引用する必要があるため時間がかかります。引用したい情報は、上記の例で行くと、シートBのH9007やI9007といった、シートAで既に紐付けられているセルの一つ上であることがわかりました。そのためOFFSETが使えないかと考えた次第です。

(3)根拠はありません、この質問を出す前に色々と検索してみたのですが解決できずそのように述べた次第です。

状況はまだ把握頂けないようであれば再度ご指摘いただけますでしょうか？
お手数ですが何卒宜しくお願いいたします。

回答No.1

KURUMITO

例えばシート2のA1セルからA3セルまでに１，２，３の数値が入力されているとします。
シート1のセルで例えば　=OFFSET(Sheet2!A3,-1,0) と入力すれば2が表示されます。
また、　=OFFSET(Sheet2!A3,-2,0)　と入力すれば1が表示されます。
OFFSET関数は引数として基準、行数、列数を入力することになりますが、基準より1行上の場合には-1を、1行下の場合には１を、同じ列の場合には０を、右横列になる場合には1を、左横列の場合には-1のようにして使います。
OFFSET関数は別のシートを参照する場合でも問題なく使うことができますね。

補足

早速のご回答有難うございます。

シート１の例えばB３に＝Sheet2！A１と入力されている場合、そのシート１のB３を使って（経由して）シート2のA１を引用することは出来ないでしょうか？

お手数ですがご回答いただければ幸いです。
宜しくお願いいたします。