エクセル セル参照位置を自動検索する方法

質問

計算自体は単純な足し引きなのですが、図のとおり赤枠の数字はA1より青枠の数字はA5よりそれぞれC列の数字を引いた物です。単純に「D1=$A$1-C1」でも良いのですが、A列及びC列の個数及び行位置が変動することから例えばセル位置の参照が
　　「D3であれば3列前A列のA3より上にある空白でないセルA1を」
　　「D12であれば3列前A列のA12より上にある空白でないセルA5を」
とできる関数をどなたかお教え願いたく御願いいたします。

回答No.5

kagakusuki · ベストアンサー

D1セルに次の数式を入力後、D2セルをコピーして、D3以下に貼り付けると良いと思います。

=IF(ISNUMBER(C1),VLOOKUP(99^9,A$1:A1,1)-C1,"")

　尚、A列に入力されている数値の中で、最も大きな数が99の9乗以上（等しい場合も含む）になる場合には、数式中の

99^9

の部分をA列に入力される筈がない程、大きな数値に変更するか、或いは

MAX(A:A)+1

に変更して下さい。

お礼

色々な方法があり大変参考になりました。
誠に有難う御座います。

回答No.4

yosifuji20

すみません訂正です。

Ｃ列の式は下記のとおりです
＝Ａ2-Ｂ2

これを次のように訂正します。

Ｄ列の式は下記のとおりです
＝Ｂ2-Ｃ2

回答No.3

yosifuji20

少し表の形を変えますが、最上列に1行挿入して空白行にします。
そしてＢ2から下に下記の式を入れます。

=IF(ISBLANK(A2),B1,A2)

Ｃ列の式は下記のとおりです
＝Ａ2-Ｂ2

要するに、Ｂ列は左のＡ列が空白ならばＢ列のすぐ上の値をとるということです。
Ａ列が空白でない所のＢ列に必ず左のＡ列の数値が入りますので、Ａ列が空白であってもＢのセルには必ずその上の有効値がセットされています。

なぜ最上列に空白を入れるかというと、Ｂ２の式では必ずその上にセルがないとこの式がエラーになるからです。

回答No.2

don9don9

A列にどのくらいまでの値が入るか分かりませんが
とりあえずD2に

=LOOKUP(10^10,$A$1:A2,$A$1:A2)-C2

と入力して下方向にコピーでどうでしょうか。
A列の値が、10^10＝10000000000以下であれば大丈夫だと思います。

回答No.1

tom04

こんにちは！
外していたらごめんなさい。

一例です。
A列にデータがある場合、その行のC列にはデータがないという前提です。

↓の画像でD2セルに
=IF(C2="","",INDEX($A$1:A1,MAX(IF($A$1:A1<>"",ROW($A$1:A1))))-C2)
これは配列数式になってしまいますので、Shift＋Ctrlキーを押しながらEnterキーで確定！
数式の前後に｛　｝マークが入り配列数式になります。

このD2セルをオートフィルで下へコピーすると画像のような感じになります。

参考になれば良いのですが・・・m(__)m