エクセル関数について office2007

質問

現在表を作成しています。
　　 A　 B　　　 C　　　 　D
1　　1　佐藤　バナナ　 　５００円
2　　2　佐藤　リンゴ　　 ８００円　
3　　3　佐藤　フルーツ　１３００円
4　　4　伴場　ケーキ　　　６００円

実際は行も列もかなり大きい表です。

悩んでいる内容は
A1が1の時D3を別シートに抜き取ったりと、参照をしたいのですが

私のエクセルの能力では
VLOOKUPまでしか分からず、右にしか参照出来ません。

色々な条件で元のデーターから別シートにまとめたいのですが
現在のデーターは行数が5000列はALまで

名前別などまとめたり、条件を変え合計を出したいため絶対対象になりますとコピペでは全て手作業になってしまい
大変な事になります。というか数式から＄でずれない所を全て手作業で打ち込みするならデーターから抜き取らず手打ちした方が早いかもしれません。
SUMの時みたいにコピペをすると数式の対象もずれてくれるのが理想です。
マクロ等使わず条件が●●で＝の時●●から右に◆下に◆のセルの値を返すような数式が知りたいです。
馬鹿な私に教えて下さい。
宜しくお願い致します。

回答No.1

keithin · ベストアンサー

＞条件が●●で＝の時●●から右に◆下に◆のセルの値を返すような数式が知りたいです。

前提として，基準になる●●が具体的に何列にあるのかは既知であるとします。たとえばA列。
まずMATCH関数の勉強をして，基準位置の●●がA列の何行目に出てくるか調べます。
例：
MATCH(●●,Sheet2!A:A,0)

そこから下に△，右に□だけ動いた先のセルは，INDEX関数やOFFSET関数やこの２つを組み合わせて取ってきます。
例：
=OFFSET(Sheet2!$A$1,MATCH(●●，Sheet2!A:A,0)+△-1，□-1)
例：
=INDEX($A$1:$AL$5000,MATCH(●●,Sheet2!A:A,0)+△，□)
例：
=OFFSET(INDEX(Sheet2!A:A,MATCH(●●,Sheet2!A:A,0))，△-1，□-1)

お礼

この度は御返答頂きまして誠に有難うございます。丁寧に御指示頂きまして誠に分かりやすかったです。
又機会がありましたら宜しくお願い致します。

回答No.3

KURUMITO

例えばシート1にお示しの表があるとしてシート2のA1セルに1を入力し取り出したい行が3行下でD列の値を表示するとしたら次のような式になりますね。

=OFFSET(INDIRECT("Sheet1!A"&MATCH(A1,Sheet1!A:A,0)),3-1,4-1)

ところで実際には3行下などという式を使われるのでしょうか？お示しの表を見ますと佐藤の最終行のD列の値を取り出すといったことでしょうか？
その場合には別に作業列を用意して取り組むことが計算を重くしないで解決できる方法となるでしょう。
仮に佐藤はお示しのように連続して表示されているとした場合にその最終行を求めるために作業列としてE列のE1セルには次の式を入力して下方にオートフィルドラッグします。

その後にシート2のA1セルに1と入力し、佐藤の最終行のD列の値をB1セルに表示させるのでしたらB1セルには次の式を入力します。

=INDIRECT("Sheet1!D"&MATCH(A1,Sheet1!E:E,0))

お礼

この度は細かい説明頂きましてありがとうございました。とても勉強になります。本当に有難う御座いました。

回答No.2

mu2011

指定したセルから行・列を２次元みたいに参照できます「OFFSET関数」は如何でしょうか。
使用例などは「OFFSET関数」で検索すれば沢山ヒットしますのでご検討下さい。

お礼

御回答有難う御座います。OFFSET関数チャレンジさせて頂きます。