Excel 2010で複数の空白セルの上のセル参照

質問

いつもとても助けられています
Excel 2010を使用しています
今、A1からA20まで、順番に1, 2, 3,,,,20と数字が入り、B1, B8, B10, B11, B17, B20に、91, 104, 105, 106, 111, 121と数が入っている時に、C8に(B8-B1)/(A8-A1)、C10に(B10-B8)/(A10-A8)、C11に(B11-B10)/(A11-A10)、C17に(B17-B11)/(A17-A11)と自動的に入れられるような関数ができるのかどうかを知りたいと思っています
そしてさらに欲を言えば、Aの列が、このような数字ではなく、日付であった場合(たとえばA1が2002/1/1、A8が2002/8/1、A10が2002/10/1といった規則的な増加の場合、あるいはA1が2002/1/1、A8が2002/6/8、A10が2004/7/9というような全く不規則な増加の場合)についても計算ができればうれしいです
要するに、とびとびの数値が入力されている場合の増加速度を求めるような関数です
C2からC7のようなセルには、IF(B2="","",●●）で回避できると思っています
何か良い方法はないでしょうか
よろしくお願いします

回答No.4

keithin · ベストアンサー

＞増加速度を求める

この目的から、Ｂ列の「最初の数字」は自分より前がいないので、自動的に計算の対象外となります。

添付図：
１行目はタイトル行
２行目からデータ

Ｃ２：
=IF(B2="","",IF(COUNT($B$1:B1),(LOOKUP(MAX($B$1:B1)+1,$B$1:B1)-B2)/(LOOKUP(MAX($B$1:B1)+1,$B$1:B1,$A$1:A1)-A2),"NA"))
ふつーにＥｎｔｅｒで入力、以下コピー。

お礼

回答ありがとうございました
回答があったらメールでお知らせが入ると思っていたので、気が付くのが遅くなってしまいました
COUNT関数をこのように使うのかということや、LOOKUP関数など、シンプルなのに、こんな使い方ができるのかと言うことに驚きました
ありがとうございました
しかも日付でこのように計算ができるということが、とてもありがたかったです
もう、完全にお手上げだったのに、こんなことができるなんて感動です。ありがとうございました。

回答No.3

noname#204879

C2: =IF(B2="","",(B2-MAX(B$1:B1))/(A2-MATCH(MAX(B$1:B1),B:B,0)))

お礼

回答ありがとうございました
回答があったらメールでお知らせが入ると思っていたので、気が付くのが遅くなってしまいました
MAX関数やMATCH関数と言うものは知りませんでしたので、勉強になりました
ありがとうございました

回答No.2

bunjii

C列の1つの数式を入力して下へ必要数コピーするためには複雑な処理が必要になります。
B列は入力されている行番号が不規則なため対象セルを検出しなければなりません。
少々長い数式になりますがC2セルにセットして下へ必要数コピーしてみることをお勧めします。

=IF(ISNUMBER(B8),(B8-INDIRECT(ADDRESS(SUMPRODUCT(MAX((B$1:B7<>"")*ROW(B$1:B7))),2)))/(A8-INDIRECT(ADDRESS(SUMPRODUCT(MAX((B$1:B7<>"")*ROW(B$1:B7))),1))),"")

IF(ISNUMBER(B2,数式,"")はIF(B2="","",数式）でも良いのですが、B列に文字列が入力されたときエラーが発生します。
直前の行番号はMAX関数で割り出しますがB列の1行目から処理行の上の行までに数値が入力された行番号の最大値で判断しています。
SUMPRODUCT関数は配列数式でも通常通りの数式入力でEnterキーのみで確定できる性質を応用するために使っていますので、煩わしいときは省いてCtrl+Shift+Enterで確定する方法でも良いでしょう。
(B8-B1)のB1と(A8-A1)のA1はINDIRECT関数とADRESS関数の組み合わせで可変にしています。

お礼

回答ありがとうございました
回答があったらメールでお知らせが入ると思っていたので、気が付くのが遅くなってしまいました
ISNUMBER関数、INDIRECT関数、ADDRESS関数、SUMPRODUCT関数、MAX関数、ROW関数と、初めてみる新しい関数ばかりで、何が書かれているのかを理解するのに時間がかかりました。こんなこともできるんですね。思ってもいない方法でした。まだマスターするのには時間がかかりそうですが、また、勉強してみたいと思います。
ありがとうございました。

回答No.1

tom04

こんばんは！

A列・B列とも不規則で構わないのですが、どちらも必ず昇順にデータが入っているという前提です。

C2セル（←C1セルではありません）に
=IF(OR(B2="",COUNT(B$1:B2)<2),"",(B2-LARGE(B$1:B2,2))/(A2-INDEX(A$1:A2,LARGE(IF(B$1:B2>0,ROW(A$1:A2)),2))))
配列数式になりますので、Ctrl＋Shift＋Enterで確定！
この画面からコピー＆ペーストする場合は
上記数式をドラッグ＆コピー　→　C2セルを選択　→　数式バー内に貼り付け　→　そのまま（編集可能なまま）
Ctrl＋Shiftキーを押しながらEnterキーで確定！
数式の前後に{ }マークが入り配列数式になります。

これをフィルハンドルで下へコピーしてみてください。m(_ _)m

お礼

回答ありがとうございました
回答があったらメールでお知らせが入ると思っていたので、気が付くのが遅くなってしまいました
LARGE関数やCOUNT回数について、勉強になりました