![](http://oshiete.xgoo.jp/images/v2/pc/qa/question_title.png?e8efa67)
参照先のセルが空白の場合に値が入力されているセルまで列を遡って参照したいのですが、そのような時はどういった関数を使えば可能でしょうか?
具体的には添付画像のようなケースです。
A列には日付、B列にはニューヨークダウの終値が入力されています。
C列では当日終値の前営業日に対する変動率を求めます。
当日のB列が空白の場合はC列にも空白を返します。
前日の終値が空白のC3048とC3057にエラーが出ます。
C3048ではB3048のB3046に対する変動率、
C3057ではB3057のB3052に対する変動率 を求めます。
このケースではC列にどのような関数式を使えばよろしいでしょうか。
質問の文章が分かりづらくて申し訳ありませんが、よろしくお願いいたします。
![「エクセル関数」の質問画像](http://oshiete.xgoo.jp/_/bucket/oshietegoo/images/media/0/24253009_5497bfff71032/M.jpg)
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も見ています
-
性格の違いは生まれた順番で決まる?長男長女・中間子・末っ子・一人っ子の性格の傾向
同じ環境で生まれ育っても、生まれ順で性格は違うものなのだろうか。家庭教育研究家の田宮由美さんに教えてもらった。
-
Excel-参照セルが空白の場合、別のセルを参照するには。
Excel(エクセル)
-
エクセル もし、セルが空欄なら、その上のセルの値を入力する
Excel(エクセル)
-
Excelで空白セル直前のセルデータ値を取得したい
Visual Basic(VBA)
-
-
4
セルの文字を「印刷時だけ非表示」にしたいです。
Excel(エクセル)
-
5
エクセルの質問。 対象範囲の中で、一番下に位置する値を返す関数。
Excel(エクセル)
-
6
結合したセルを含む列内で、条件付き書式として塗りつぶしたい
Excel(エクセル)
-
7
空白セルから空白セル間の計算方法
その他(Microsoft Office)
-
8
エクセル 空白を飛ばして前の数字と同じ数字を入力する数式
Excel(エクセル)
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
IFとIFS関数
-
Excelを無料で使うには? パソ...
-
セルの数を求めたい
-
Excelの表示についての質問
-
再質問です。マクロの修正箇所...
-
データチェックを行うエクセル...
-
エクセルで「ページレイアウト...
-
エクセルの数式バーのフォント...
-
エクセルの関数について教えて...
-
エクセルでセルに標準で入力さ...
-
Microsoft1Officeの互換ソフト...
-
シフト表をエクセルで作るとき...
-
ユーザー定義関数をアドイン登...
-
Excel VBAで全ての矢印を赤色に...
-
VBA Private Sub Worksheet_Cha...
-
2列に入っているデータを1列...
-
Excelに詳しい方! B列が「日...
-
excel2013 MonthDays 関数が使...
-
エクセルのクイックアクセスツ...
-
Excelの数式について教えてくだ...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
半角カタカナをヘボン式ローマ...
-
(マクロ)vlookupの元データを同...
-
エクセルで上位バイトのセルと...
-
exselの質問です
-
Excel 大小比較演算子による「...
-
Excel VBについての質問です。
-
エクセルの問題です。絶対値の...
-
非表示列の再表示に失敗
-
職場の人から聞かれており、こ...
-
Excel関数-文字列で自動作成さ...
-
Excelデータをコピペして、ペー...
-
ユーザー定義関数をアドイン登...
-
【マクロ】for next構文について
-
エクセルの日付を編集する
-
【マクロ】VLOOKUPにて参照元に...
-
exselで最小数で並び替える関数
-
libre 表計算ソフトの計算がう...
-
エクセルで表
-
エクセルの表で1年間の曜日を...
-
西暦和暦
おすすめ情報