エクセル空白セルの計算

質問

エクセル２０００です。
データ値 　　　差値
C3 100 　　 D3
C4 150 　　　D4 　 50
C5 空白 　　 D5　　
C6 180 　　　D6 　 30・・・C6－C4の値
C7 空白 　　D7
C8 空白 　　D8
C9 300 　　　D9 　 120・・・C9－C6の値
C10 350 　　 D10 50

Ｃ行に数字を入力し、Ｄ行でC4とC3行の差値（次行との差値）を出したいです。
C行に空白がつづいても差値が出せる方法が分かりません。空白が無ければ
=IF(C4="","",C4-C3)でいいのですが・・・

ご教授よろしくお願いします。

回答No.9

某HN クロメート(Chromate)（@CoalTar） · ベストアンサー

＃8です
>２はD3セルのみで、D4セル以降オートフィル？
書き忘れましたが、その通りです。

LOOKUPの引数においてD4セルにオートフィルしたとき
複合参照が変化してしまうので、検証するならD4セルでしょう。

LOOKUP、VLOOKUP、HLOOKUP、MATCH関数の近似での検索(完全一致ではない検索方法)
にしたときに二分探索しますが、その時のExcelのクセを逆に利用したものです。

裏技的な使い方といえるので、本で調べてもなかなか見つからないと思います。
当方も100%理解しているわけではありません。
応用で文字列を検索する場合は、=MATCH("ー",C:C)といったこともできます。

ちなみに、完全一致で検索するときは順次探索となり、上から順に検索します。
複数のデータがある場合、上のものが返されるのはこのためです。

お礼

お礼が大変遅くなりました。

＞LOOKUPの引数においてD4セルにオートフィルしたとき・・・
＞・・・・複数のデータがある場合、上のものが返されるのはこのためです。

上記の内容等
ネットなどで調べてみます。

ご親切丁寧にありがとうございました。

回答No.8

某HN クロメート(Chromate)（@CoalTar）

#3さん補足および#4さん回答より
D3セルに
=IF(C3="","",MAX(0,C3-LOOKUP(10001,C2:C$3)))
下へオートフィル

個人的には D3セルに 0
D4セルに =IF(C4="","",MAX(0,C4-LOOKUP(10001,C$3:C3)))
のほうがわかりよいと思います。

補足条件がなかったら難しい関数の使い方一切なしで
D3セルに =IF(C3="","",C3-SUM(C$3,D$2:D2))
下へオートフィル、下から考えるとよいです。

補足

ご回答ありがとうございます。

画像までつけて頂きまして、分かりやすいです。希望通りの内容です。
（画像貼付の仕方が分からずで・・・）

＞D3セルに
=IF(C3="","",MAX(0,C3-LOOKUP(10001,C2:C$3)))・・・１
下へオートフィル

＞個人的には D3セルに 0
D4セルに =IF(C4="","",MAX(0,C4-LOOKUP(10001,C$3:C3)))・・・２
のほうがわかりよいと思います。

１の方でうまく出来ました。

２はD3セルのみで、D4セル以降オートフィル？

式の内容を勉強です。

回答No.7

kagakusuki

>Ｄ３にゼロの表示がご指摘関数から出来ますでしょうか？

　確認したいのですが、0の表示とするのは、D3セルや「C列の値が10000に達した行以降の行で、最初に数値が入力されている行」のD列のセルのみであり、D5、D7、D8等は空欄のままとすれば宜しいのでしょうか？

>データ値は10,000までで、それ以降は０（ゼロ）に戻り、再度、数字が上がりますので

　データ値は10000未満の範囲では、必ず下へ行くほど増え続けるという事でしょうか？
　それと、データ値は毎回必ず、10000丁度の値となってから、小さな値に戻るのでしょうか？
　それとも、10000丁度となる事は殆ど無く、10000近い値にまでなってから、10000にならずに、いきなり小さな値に戻るのでしょうか？

　もし、D5、D7、D8等は空欄のままとすれば良いのでしたら、D3セルに入力する関数は次の様なものとなります。

=IF(ISNUMBER(INDEX($C:$C,ROW())),IF(COUNT($C$1:INDEX($C:$C,ROW()))=1,0,IF(INDEX($C:$C,ROW())<VLOOKUP(10001,$C$1:INDEX($C:$C,ROW()-1),1),0,INDEX($C:$C,ROW())-VLOOKUP(10001,$C$1:INDEX($C:$C,ROW()-1),1))),"")

補足

大変ご無理なことを言いましてすいませんでした。
補足回答を頂き、ありがとうございます。

＞確認したいのですが、0の表示とするのは、D3セルや「C列の値が10000に達した行以降の行で、最初に数値が入力されている行」のD列のセルのみであり、D5、D7、D8等は空欄のままとすれば宜しいのでしょうか？

・・・・ゼロ表示は、記録表の最初の行になりますので、今回の場合はD3セルと
「C列の値が10000に達した行以降の行で、最初に数値が入力されている行」のD列のセルのみになります。
空欄行は、どこにできるか分かりません。複数行、連続行等です。

＞データ値は10000未満の範囲では、必ず下へ行くほど増え続けるという事でしょうか？
　それと、データ値は毎回必ず、10000丁度の値となってから、小さな値に戻るのでしょうか？
　それとも、10000丁度となる事は殆ど無く、10000近い値にまでなってから、10000にならずに、いきなり小さな値に戻るのでしょうか？

・・・・機器のカウント数字が最大で10,000です。カウント数字を見る時期はバラバラですので、10,000丁度になっていることは無いです。カウント数字が10,000になると、機器の方で勝手にゼロに戻り、再度、低い数字から上がっていきます。
カウント数字を見た時期による数字を記録しますのでゼロ丁度もないです。

今回記録した数字と次回記録した数字の差値を出します。
C行　　　D行（差値）
２０　　　０
空白
５００　　５００
空白
空白
空白
９９５０　　９４５０
１５　　　　０
３０　　　　１５
４０　　　　１０
・　　　　　　・
・　　　　　　・
こんな感じです。
補足回答頂いた式でうまく出来ました。ありがとうございます。

回答No.6

imogasi

この質問は（お礼欄の受け答え振りから、またデータの書き方から推測して）質問者には難しすぎる課題と思う。
回答の式の意味などもわからない恐れが多い。
データ例は
C列　D列
100　？？　＜－どうする？100?
150　50
空白
180 30
空白
空白
300 120
350 50
と書けば一目でわかる。説明として同列で空白セルを飛ばして、最も近い上のセルのデータとの差を求めたいと書けば良い。
ただしこの最も近い前（上）の行を求める式は、関数の経験を相当しているものにも難しいのだ。
ーー
そこで　C列を作業列にして
B列　　　　C列　　　　D列
データ 作業列 回答列
100 100
150 150 50
150
180 180 30
180
180
300 300 120
350 350 50
C2は=B2
C3は　=IF(B3="",C2,B3)
下方向に式複写。
ーー
D３は　=IF(B3="","",C3-C2)
下方向に式を複写。
D2はどうするのか明確でないので略。
ーー
この方法もやさしくは無いが、まだ他に比較して考え付きやすいかと思ったが。

お礼

ご回答ありがとうございます。
＞この質問は（お礼欄の受け答え振りから、またデータの書き方から推測して）質問者には難しすぎる課題と思う。
回答の式の意味などもわからない恐れが多い。

簡単に考えてました。素人につき、式の意味はよく分かりませんので
ご回答頂いた式をネットで調べてる状態です。

回答No.5

mt2008

ANo.2です。
後から色々な条件を出されると先に行った回答が無駄になるので勘弁して欲しいですね。

C3が空白、C4が150の時は、D3は空白、D4は0で良いのでしょうか？
それでしたら、D3に↓を入れて下にコピー

=IF(C3="","",IF(ISNA(LOOKUP(10^10,C2:C$3)),0,C3-MIN(C3,LOOKUP(10^10,C2:C$3))))

で、いかがでしょうか。

LOOKUP(10^10,C2:C$3)の部分は直前の空白を除いたC列の値になります。
また、直前までのC列に値が無い時はエラー（#N/A）になりますので、その時は0を表示します。

MIN(C3,LOOKUP(10^10,C2:C$3))　は、自行C列の値と、直前の空白を除いたC列の値のうち、小さい方になります。

これにより、C列の値が増えている時は「直前の空白を除いたC列の値」、10,000を超えてデータ値が戻った場合はC列の値が選ばれますので、差分が表示されたり0が表示されたりします。

お礼

ご回答ありがとうございます。
いろいろなこと言いましてすいませんでした。

ご指摘の式でうまく出来ました。
ご丁寧に式の意味までつけて頂きましてありがとうございます。分かりやすいです。

回答No.4

maron--5

◆こんな方法はいかがでしょう
D3=IF(C3="","",C3-LOOKUP(10^10,C2:C$3))
★下にコピー

お礼

ご回答ありがとうございます。
画像までつけて頂きまして。

回答No.3

kagakusuki

C3が空欄となっている場合も考えられますから、IF関数の判定式の部分は、

C4<>""

だけでは不充分で、

AND(COUNT($C$1:INDEX($C:$C,ROW()))>1,ISNUMBER(INDEX($C:$C,ROW())))

とした方が良いと思います。
　ですから、まず、D3セルに次の関数を入力して下さい。

=IF(AND(COUNT($C$1:INDEX($C:$C,ROW()))>1,ISNUMBER(INDEX($C:$C,ROW()))),INDEX($C:$C,ROW())-VLOOKUP(MAX($C:$C)+1,$C$1:INDEX($C:$C,ROW()-1),1),"")

　次に、D3セルをコピーして、D4以下に貼り付けて下さい。

　これで御望みの結果が表示されると思います。
　又、この関数であれば、C列のセルに対して、削除や挿入、切り取り&貼付け、等の編集作業を行なっても、結果を正常に表示させる事が出来ます。

補足

早々のご回答ありがとうございます。
＞C3が空欄となっている場合も考えられますから、IF関数の判定式の部分は・・・
　確かに空欄の場合もあり得ます。

質問から少しはずれてるかと思いますが
データ値 　　　差値
C3 100 　　 D3　　０<-----ここが基準のゼロ表示ができればと・・・。
C4 150 　　　D4 　 50
　Ｄ３にゼロの表示がご指摘関数から出来ますでしょうか？
No.2さんの場合だと簡単にできますが・・・

データ値は10,000までで、それ以降は０（ゼロ）に戻り、再度、数字が上がりますので
そのセルをゼロ表示させたいです。
100
・
・
・
9980
・
・
20・・・ここを基準のゼロ表示に。
40

データー値は、ある機器のカウント数字です。その記録表作成になります。セルの空白は状況
により出来てしまいます。

よろしければ再度、ご教授お願いします。

回答No.2

mt2008

挙げられている例の様に、C列の値が下に行くほど大きくなるのでしたらD4セルに↓で良いと思います。

=IF(C4="","",C4-MAX(C$3:C3))

お礼

早速のご回答ありがとうございます。

C4-MAX(C$3:C3)の内容、勉強です。

回答No.1

mu2011

一例です。
D4に=IF(C4<>"",C4-INDEX(C$3:C3,MATCH(MAX(C$3:C3)+1,C$3:C3,1)),"")として下方向にコピー

補足

早速のご回答を頂きありがとうございます。

よろしければ、ご指摘の関数の内容、又は内容が分かるサイト等を教えていただけますか？