[[20150916115104]]　『飛び飛びのセルの値の差を自動的に計算したい』（ヤマメの塩焼き）　ページの最後に飛ぶ

『飛び飛びのセルの値の差を自動的に計算したい』（ヤマメの塩焼き）

同じ列の中のランダムな位置に数値があります（発生します）。
数値がないセルは「NULL」若しくは「0」になります（どちらか一方に指定できます）。
上記ランダムなセルに発生した値の直近二つの数値の差を自動的に計算させたいのですが、良い方法がありますでしょうか。
数値が何行目に発生するかは分かりません。

＜例＞
A1=5, A5=10, A22=7, A23=15, A45=9, …

この様なシートで、欲しい結果は下のB列のようになります。

          A        B
－－－－－－－－－－－－－－－－－－
 1        5        5     (←10-5)
…
 5       10       -3     (←7-10)
…
22        7        8     (←15-7)
23       15       -6     (←9-15)
…
45        9
…

＜使用 Excel：Excel2003、使用 OS：WindowsXP ＞

 図では省略されている途中のセル(A2など)にも何か入っているのでしょうか？
 入っているとしたら、それは数値でしょうか？
 数値以外の文字列でしょうか？
（カリーニン） 2015/09/16(水) 12:47

 B1=IF(A1="","",INDEX($A$1:$A$50,SMALL(IF($A$2:$A$50>0,ROW($A$2:$A$50),10^10),COUNT($A$1:A1)))-A1)
 関数音痴案
 配列数式として確定してください（Ctrl+Shift+Enter）
 例は50行まで。
 最後の45行目は、エラー処理していないので#REFエラーのままです。

 ＞数値がないセルは「NULL」若しくは「0」になります（どちらか一方に指定できます）。 
（稲葉） 2015/09/16(水) 12:51

 B1セルに
 =IFERROR(IF(A1="","",INDEX(A2:A50,MATCH(1,INDEX(1/(A2:A50<>""),0),0))-A1),"")
 と入力して下へフィルコピーではどうか？

 数字間の空きが49行分まで対応。

（ねむねむ） 2015/09/16(水) 13:13

 2003みたいっす！！＞IFERROR
（稲葉） 2015/09/16(水) 13:14

 稲葉さん、ありがとう。

 ヤマメの塩焼さんすまない、式を
 =IF(OR(A1="",COUNTIF(A2:A50,"<>")=0),"",INDEX(A2:A50,MATCH(1,INDEX(1/(A2:A50<>""),0),0))-A1)
 としてくれ。
（ねむねむ） 2015/09/16(水) 13:23

皆様、早速のご回答　本当に有り難う御座います。

○カリーニン様
省略されている途中のセル(A2など)は
「NULL」若しくは「0」
どちらか一方に指定できます。

○稲葉様
当方、「配列数式（配列定数）」なるものを全く理解しておらず、
残念ながら#VALUE!エラーで上手くいっておりません。
HELPを見ながら解析してみようと思っております。

○ねむねむ様
再送分の式で上手くいきました。
期待通りの結果です。
MATCH関数が、条件に合う複数の値のうち、
一番上の値を返すことを知りませんでした。
これで自分でも近いものを導き出せそうです。

…が、一つ質問です。
数式内のMATCHの検査範囲の引数に
「INDEX(1/(A2:A50<>""),0)」
という式が使われていますが、
調べても訳が分かりません。
もしできましたら、関数かじりかけの人間にも解るように
ご説明願えませんでしょうか。

ともかくも、皆様、有り難うごぜーますだ m(_ _)m

（ヤマメの塩焼き） 2015/09/16(水) 19:50

 >MATCH関数が、条件に合う複数の値のうち、 
 >一番上の値を返すことを知りませんでした。
 これはMATCH関数の照合の型に「0」（一致するもののみを検索）を指定した場合の動作となる。
 (1、-1を指定した場合は複数あった場合には不定となる)

 次に式の説明。

 まず、比較式（Ａ<>Ｂなど）の結果はTrue(真)かFalse(偽)の論理値となるが論理値を計算に使うとTrueを1、Falseを0
 として扱う。

 なので
 1/(A2:A50<>"")
 の結果はA列が空白の場所は「#DIV/0!」にA列に値が入っている場所は「1」になる。

 INDEX関数の役割はそのままでは「1/(A2:A50<>"")」の結果を配列として扱えないため配列として扱うために使っている。

 これに関しては下記のリンク先を参考にしてみてくれ。

http://pc.nikkeibp.co.jp/pc21/special/hr/index.shtml

 追記
 >なので
 >1/(A2:A50<>"")
 >の結果はA列が空白の場所は「#DIV/0!」にA列に値が入っている場所は「1」になる。
 自分で書いてて気が付いた。
 ここは
 (A2:A50<>"")*1
 でもいいか。
 この場合はA列が空白の部分は「0」、値が入っている場合は「1」となる。

（ねむねむ） 2015/09/17(木) 09:26

 ねむねむさん案とそない違いはありまへんが、別案っす。

 =IF(OR(COUNT(A2:A50)=0,A1=""),"",INDEX(A2:A50,MATCH(TRUE,INDEX(A2:A50<>"",),0))-A1)
　
（GobGob） 2015/09/17(木) 11:11

コメント返信：

[ 一覧(最新更新順) ]

[[20150916115104]] 『飛び飛びのセルの値の差を自動的に計算したい』（ヤマメの塩焼き） ページの最後に飛ぶ

[[20150916115104]]　『飛び飛びのセルの値の差を自動的に計算したい』（ヤマメの塩焼き）　ページの最後に飛ぶ