エクセルにて以下のことを実現したい。

質問

A　　B　　C
1　○　 1
2 ×　 2
3 ×　 3
4 ○　 0.5
5 ×　 2
6．．．

以上の様なマトリクスにて。
A列が条件セル,B列がデータセルとします。

C列セルを計算セルに,
(1)同行のA列が○ならば「0」。
　 例：C1＝0
(2)同行のA列が×ならば,
　 そこより上段で,最も近い○からの差分を計算。
　 例：C2＝2-1＝1
　　　 C3＝3-1＝2
　　　 C5＝2-0.5＝1.5

としたいのです。

うまいやり方をご教授いただけないでしょうか？

回答No.5

MackyNo1 · ベストアンサー

No4の配列数式の回答がNo1とかぶったので、参考までに。

提示した可変のセル範囲を入力した配列数式は、表示する数が多いと数式でのメモリーを多く消費するうえ、配列数式の再計算に時間がかかるなどのデメリットもあります。

よく考えたら、配列を使わなくても以下のような数式のほうがメモリー消費や計算負荷が少ないので、はるかに勝れた数式ですね。

=IF(A2="","",(A2="×")*(B2-B1+C1))

補足

おお,これはスマート！
ありがとうございます。
ところで,これIF構文はいらずに
=(A2="×")*(B2-B1+C1)
の部分だけでいいような気がするのですが,なにか理由があるのでしょうか？

回答No.7

MackyNo1

＞ところで,これIF構文はいらずに
=(A2="×")*(B2-B1+C1)
の部分だけでいいような気がするのですが,なにか理由があるのでしょうか？

計算の基本部分とは関係ありませんが、今後のデータの追加に対応できるようにする（あらかじめA,B列が未入力のセルに数式を入力した場合に空白表示させる）ために、IF文を付けてみました（データの入力行だけに数式を入れるなら、もちろん必要ありません）。

お礼

ありがとうございます。

回答No.6

imogasi

余りエクセル関数の経験がないなら、Ａ－Ｃ以外の列に作業列を作って考えるのが良い。そういう回答が出ていますが、賛成を１票投じたい。
A列　　　　Ｂ列　　　　　Ｃ列　　　　Ｄ列（作業列）
○ 1 0 1
× 2 1 1
× 3 2 1
○ 0.5 0 0.5
× 2 1.5 0.5
Ｄ１式は＝ｂ１
Ｄ２の式は　=IF(A2="○",B2,D1)
下方向に式を複写
Ｃ１に　=B1-D1
下方向に式複写
＝＝＝
この問題の難しさは各行によって、引くデータのセルが変わることであり、どの行かはデータの有様による。
ＶＢＡなら○の行のＢ列の値を変数に記憶しておいて、次の行からその値を次の○の行まで使える。
Sub test02()
d = Range("A65536").End(xlUp).Row
m = Cells(1, "B")
For i = 1 To d
If Cells(i, "A") = "○" Then
Cells(i, "C") = 0
m = Cells(i, "B")
Else
Cells(i, "C") = Cells(i, "B") - m
End If
Next i
End Sub
＝＝＝＝＝
その行まで出一番下の丸の行のＢ列の値を使えば良いから
Ａ列でそこの行までで、　最下行の○の行番号は
=SUMPRODUCT(MAX((($A$1:A2="○")*(ROW($A$1:A2)))))
これを使って
Ｃ２に=B2-INDEX($A$1:$B$100,SUMPRODUCT(MAX((($A$1:A2="○")*(ROW($A$1:A2))))),2)　と入れて下方向に式複写。
Ｃ１は０を入れておく。
ーーー
例データ
Ａ列　　　Ｂ列　　　　　Ｃ列
○ 1 0
× 2 1
× 3 2
○ 0.5 0
× 2 1.5
× 1 0.5
× 1 0.5
○ 3 0
× 6 3
× 7 4
× 9 6
○ 6 0
× 4 -2
上記Ｃ列が結果

お礼

VBAもなれていかんとな・・・
試してみます,ありがとうございます。

回答No.4

MackyNo1

例示のレイアウトならC2セルに以下の式を入力して下方向にデータ数分オートフィルコピーしてください。

=(A2="×")*(B2-INDEX(B:B,MAX(INDEX(($A$1:A1="○")*ROW($A$1:A1),))))

お礼

ありがとうございます。

回答No.3

neKo_deux

自分だったらですが、BとCの間に作業列を作ります。

C列:最後に○があった行の数字
C1:=B1
C2:=IF(A2="○", B2, C1)
C3～:C2をコピペ

とすれば、

D列:質問の条件
D1:=IF(A1="○", 0, B2-C2)
D2～:D1をコピペ

とか。

お礼

ありがとうございます。

回答No.2

luka3

自分ならあんまり複雑にしたくないので2段構えにします。

C列に最後の○が現れたときのB値を記録
C1=B1
C2=if(A2="○",B2,C1)
以下C2をフィル

D列にご希望の「○なら0」「それ以外ならB値との差」
D1=if(A1="○",0,B1-C1)
以下D1をフィル

お礼

これはわかりやすい。
今回はこれを採用させて頂きます。
ありがとうございます。

回答No.1

mu2011

一例です。
=IF(A1<>"",B1-INDEX(B:B,MAX(INDEX(($A$1:A1="○")*ROW($A$1:A1),))),"")

お礼

ありがとうございます。