参照先のセルが空白の場合に値が入力されているセルまで列を遡って参照したいのですが、そのような時はどういった関数を使えば可能でしょうか?
具体的には添付画像のようなケースです。
A列には日付、B列にはニューヨークダウの終値が入力されています。
C列では当日終値の前営業日に対する変動率を求めます。
当日のB列が空白の場合はC列にも空白を返します。
前日の終値が空白のC3048とC3057にエラーが出ます。
C3048ではB3048のB3046に対する変動率、
C3057ではB3057のB3052に対する変動率 を求めます。
このケースではC列にどのような関数式を使えばよろしいでしょうか。
質問の文章が分かりづらくて申し訳ありませんが、よろしくお願いいたします。
No.7
- 回答日時:
参考までに
LOOKUP関数を使った方法が提示されていますが、関数を使うなら以下のような方法の方が計算の負担が少ないように思われます(該当セルの上4つのセルの中から一番下のセルを検索しています)。
=IF(B3048="","",B3048/INDEX(B:B,MAX(INDEX((B3044:B3047<>"")*ROW(B3044:B3047),)))-1)
ただし、上記の数式で検索範囲を5つにすると、逆にこの数式の方が負担が多いようですので1万行を超える行に入力するような場合は、このセル範囲を空白が続く最大範囲に合わせるようにしてください。
No.6
- 回答日時:
質問のやり方について、画像も良いが、文章で論点を表現する訓練をしてほしい。
ーーー
その列でそのセルやその上が空白セルの場合、上にさかのぼって、空白で無い最初(最下)のセルの値を取りたいなら
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? …
No.5
- 回答日時:
C3048に
=IF(B3048="","",B3048/LOOKUP(1E7,$B$1:B3047)-1)
を記入,%書式(桁数表示調整)を施し,上下のセルにコピー。
No.4
- 回答日時:
マクロ組んだ方が、すっきりしますが関数のみで対応しようと思うと1行作業列を追加してください。
具体的に追加する作業列は、自セルより上にいくつ空白のセルがあるかをカウントしてその結果を表示させます。
そして、変動率を算出する際の分母となるセルについてオフセット関数を用いて作業列の結果分だけ上方向にずれるように組めば出来ます。
【作業列に入れる関数例】
=IF(C2="",D2‐1,0) こんな感じで内容としては次のようにします。
=IF(前日の日付セル="",前日の作業列での空白カウント結果+1,空白じゃない場合は「0」)
これで、連続して休日(空白)でも前日分の作業列の結果を引き継いで計算するので結果として大型連休だろうが連休日数がカウントされます。
【変動率に入れる関数例】
=IF(C3="","",(C3/OFFSET(C2,D3,0)-1)) こんな感じです。
offsetの部分を抜き出して説明するとOFFSET(前日のセル*オフセットする時の基準となるセル,当日の作業列のセル,行方向へのオフセット*今回は必要ないので「0」)
これで、作業列が平日なら「0」が入っているのでオフセットなしで前日が分母にきます。
作業列が0以外ならば作業列分上にオフセットしたセルを分母として計算します。
if分は、当日が空白の場合は変動率のセルも空白処理です(たぶん、これは既にされていると思いますが)
こんな感じで、各関数を2行目以降に入れてコピーすれば出来ると思います。
細かい関数の書き方は、質問者様で調整いただければと思います。
あくまでも、発想だけ参考にしてもらえればと思います。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) Excel VBA マクロ ある列の最終行迄を参照し、別の列の空白セルに値を入力したいです 2 2023/03/05 02:44
- Visual Basic(VBA) 3つのプロシージャをまとめたら実行時エラー発生で対応不能 6 2022/05/17 01:47
- Excel(エクセル) エクセル 自動計算 1 2023/01/30 13:28
- Visual Basic(VBA) エクセルVBAについて 2 2023/01/31 16:21
- Excel(エクセル) Excel2019、2021の日付、曜日の表示について 2 2022/11/29 15:01
- Excel(エクセル) 列の複数ある空白セルを飛ばして、セルに並べて表示したい 3 2023/02/12 16:49
- Excel(エクセル) capeofdragonと申します Excel2016を使っておりまして 半角又は全角の任意文字列が 2 2022/10/31 13:51
- Visual Basic(VBA) エクセルVBAで『A列』に新規で数値を入力し『B列』から右方向の空白セルにその値を貼り付ける方法 4 2022/11/05 08:37
- Excel(エクセル) Excelについて質問です(ver2019) 1 2023/06/30 21:20
- Excel(エクセル) Excel2019 列と列(2列)の数値の重複を調べたい 1 2023/05/11 13:35
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセル初心者です 関数の入れ...
-
Microsoft1Officeの互換ソフト...
-
Excel ピボットテーブルで日付...
-
エクセル関数を教えてください
-
【マクロ】その時、その時で変...
-
【マクロ】読取専用のファイル...
-
LOOKUP関数を使えばいいのでし...
-
エクセル 白黒印刷で白線を印刷...
-
【関数】先頭だけにある、半角...
-
【関数】適切な文字数の数字を...
-
Excelのチェックボックスの使い...
-
エクセルでの作業計算方法について
-
Excelのpivotについて質問です
-
WPS OFFICEでの縦書きについて
-
時間によってファイル名が変わ...
-
エクセルのセルに同じ大きさの...
-
Aというブックの1というシート...
-
エクセルの順位別一覧表の自動...
-
西暦や和暦の表示をyyyymmdd表...
-
【マクロ】エクセルにかいてあ...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel 2019 のピボットテーブル...
-
[関数得意な方]教えて下さい・...
-
Excelにてある膨大なデータを管...
-
[関数について]わかる方教えて...
-
Excel初心者です。 詳しい方、...
-
excelの不要な行の削除ができな...
-
エクセル関数に詳しい方教えて...
-
INDIRECTを使わず excelで複数...
-
[オートフィルタ]で抽出された...
-
エクセルの神よ、ご回答を! エ...
-
エクセル関数に詳しい方、教え...
-
各ページの1番上の表示について
-
Excelで写真のような表を作った...
-
エクセルで不等号記号(≠)が上に...
-
数学 Tan(θ)-1/Cos(θ)について...
-
Excel 2019 は、SPILL機能があ...
-
Excelで全角を半角にしたいので...
-
条件付き書式を教えてください
-
Excel フィルターを掛けた状態...
-
[オートフィルタ]の適用範囲の...
おすすめ情報