エクセル関数

質問

参照先のセルが空白の場合に値が入力されているセルまで列を遡って参照したいのですが、そのような時はどういった関数を使えば可能でしょうか？

具体的には添付画像のようなケースです。

A列には日付、B列にはニューヨークダウの終値が入力されています。
C列では当日終値の前営業日に対する変動率を求めます。
当日のB列が空白の場合はC列にも空白を返します。

前日の終値が空白のC3048とC3057にエラーが出ます。

C3048ではB3048のB3046に対する変動率、
C3057ではB3057のB3052に対する変動率 を求めます。

このケースではC列にどのような関数式を使えばよろしいでしょうか。

質問の文章が分かりづらくて申し訳ありませんが、よろしくお願いいたします。

回答No.3

KURUMITO · ベストアンサー

B3セルから下方にデータがあるとしてC４セルには次の式を入力して下方にオートフィルドラッグします。

=IF(B4="","",(B4-LOOKUP(10^10,B$3:B3))/LOOKUP(10^10,B$3:B3))

お礼

ご回答いただき誠に有り難うございます。
おっしゃる通りの関数式で見事に解決できました。

回答No.7

MackyNo1

参考までに

LOOKUP関数を使った方法が提示されていますが、関数を使うなら以下のような方法の方が計算の負担が少ないように思われます(該当セルの上４つのセルの中から一番下のセルを検索しています)。

=IF(B3048="","",B3048/INDEX(B:B,MAX(INDEX((B3044:B3047<>"")*ROW(B3044:B3047),)))-1)

ただし、上記の数式で検索範囲を5つにすると、逆にこの数式の方が負担が多いようですので１万行を超える行に入力するような場合は、このセル範囲を空白が続く最大範囲に合わせるようにしてください。

お礼

いろいろな解決方法があるこがわかり、とても参考になりました。
ご回答いただき誠に有り難うございました。

回答No.6

imogasi

質問のやり方について、画像も良いが、文章で論点を表現する訓練をしてほしい。
ーーー
その列でそのセルやその上が空白セルの場合、上にさかのぼって、空白で無い最初（最下）のセルの値を取りたいなら
VBAのユーザー関数を使って
標準モジュールに
Function ue(a)
Application.Volatile
ue = a.End(xlUp).Value
End Function
を貼り付ける。
ーー
使い方は、例えば
B列
B1:B7
a
g
c

c
B7の式は=ue（B7)
VBAでは、最終行をとらえるときEnd(xlUp)は良く使うものだから、すぐ思いつく。
ーー
関数では正面きってこの質問課題に対する関数がない。
LOOKUP関数を奇抜なアイデアで応用したりするのが定石になっているが、天下り的な点はぬぐえない。
でも他に無いので定石化しているといえよう。
http://hpcgi1.nifty.com/kenzo30/a_cbbs/cbbs.cgi?mode=al2&namber=99666&rev=&no=0&P=R&KLOG=487

お礼

とても参考になりました。
質問方法についてのご指摘は真摯に受け止めます。
ご回答いただき誠に有り難うございました。

回答No.5

keithin

C3048に
=IF(B3048="","",B3048/LOOKUP(1E7,$B$1:B3047)-1)
を記入，％書式（桁数表示調整）を施し，上下のセルにコピー。

お礼

ご回答いただき誠に有り難うございました。
おっしゃる通りの関数式で解決できました。

回答No.4

prius770c

マクロ組んだ方が、すっきりしますが関数のみで対応しようと思うと1行作業列を追加してください。

具体的に追加する作業列は、自セルより上にいくつ空白のセルがあるかをカウントしてその結果を表示させます。
そして、変動率を算出する際の分母となるセルについてオフセット関数を用いて作業列の結果分だけ上方向にずれるように組めば出来ます。

【作業列に入れる関数例】
=IF(C2="",D2‐1,0)　こんな感じで内容としては次のようにします。

=IF(前日の日付セル="",前日の作業列での空白カウント結果+1,空白じゃない場合は「0」)

これで、連続して休日（空白）でも前日分の作業列の結果を引き継いで計算するので結果として大型連休だろうが連休日数がカウントされます。

【変動率に入れる関数例】
=IF(C3="","",(C3/OFFSET(C2,D3,0)-1))　こんな感じです。

offsetの部分を抜き出して説明するとOFFSET(前日のセル*オフセットする時の基準となるセル,当日の作業列のセル,行方向へのオフセット*今回は必要ないので「0」)
これで、作業列が平日なら「0」が入っているのでオフセットなしで前日が分母にきます。
作業列が0以外ならば作業列分上にオフセットしたセルを分母として計算します。

if分は、当日が空白の場合は変動率のセルも空白処理です（たぶん、これは既にされていると思いますが）

こんな感じで、各関数を2行目以降に入れてコピーすれば出来ると思います。

細かい関数の書き方は、質問者様で調整いただければと思います。
あくまでも、発想だけ参考にしてもらえればと思います。

お礼

おっしゃる通りの方法で解決できました。
ご回答いただき誠に有り難うございました。

回答No.2

kybo

最終行は
MATCH(MAX(B:B)+1,B:B,1)
で取得できますので。

C3048行目に入れる数式としては以下でどうでしょうか？

=IF(B3048="","",B3048/INDEX(B:B,MATCH(MAX(B:B)+1,B$1:B3047,1))-1)

お礼

おっしゃる通りの数式で見事に解決できました。
ご回答いただき誠に有り難うございました。

回答No.1

hallo-2007

作業列を使って数値を埋める方法がわかりやすいと思います。
C列を作業列として
C4セルからとして
=IF(B4="",C3,B4) として下までコピーしておけば
B列が空白のときは、その上の値が入ります。
改めてD列に改めて変動率の式を入れてください。

お礼

ご回答いただき誠に有り難うございました。

難しい関数を使わなくても簡単に解決できるのですね。
良い発想の転換ができました。