ＥＸＣＥＬの関数について

質問

Ａ　　　Ｂ　　　Ｃ　　　Ｄ　　　Ｅ　　　　計
１　　○　　△　　　空　　　□　　 空 　　□-△
２　　○　　空　　　△　　　空　　 空 　　△-○
３　　○　　空　　　空　　　△　　 □　　 □-○
４　　○　　空　　　空　　　空　　 空 　　　○

上記のように表を作成した時に
計の欄に行の一番右に入力された数値から
右から二番目に入力された数値を引く計算式
というのはあるのでしょうか。
空＝空欄になります。

１の行に関しては一番右に入力されている数値が
□になるので、□の次に一番右にある△を引く。

２の行に関しては、一番右にある数値が△なので
△の次に一番右にある数値○を引く。

３の行に関しては、一番右にある数値が□なので
□の次に一番右にある数値△を引く。

４の行に関してはＡの列にしか数値が入っていないので
Ａの数値をそのまま反映させたいです。
説明が下手ですみません＾＾；
どなたか親切な方教えて下さいorz

回答No.2

kagakusuki · ベストアンサー

F1セルに次の数式を入力してから、F1セルをコピーしてF2以下に貼り付けると良いと思います。

=IF(COUNT($A1:$E1)=0,"",HLOOKUP(99^9,$A1:$E1,1)-IF(COUNT($A1:$E1)=1,0,HLOOKUP(99^9,$A1:INDEX($A1:$E1,MATCH(99^9,$A1:$E1)-1),1)))

　尚、セルに入力される数値が99の9乗以上になる事が有り得る場合には、数式中の

99^9

となっている部分を、セルに入力される事が有り得ない程、大きな数値と置き換えるか、
或いは、F1セルに入力する数式を次の様に変えて下さい。

=IF(COUNT($A1:$E1)=0,"",HLOOKUP(MAX($A1:$E1)+1,$A1:$E1,1)-IF(COUNT($A1:$E1)=1,0,HLOOKUP(MAX($A1:$E1)+1,$A1:INDEX($A1:$E1,MATCH(MAX($A1:$E1)+1,$A1:$E1)-1),1)))

お礼

ありがとうございます。
コピペで使わせてもらいました＾＾
バッチリでした！

回答No.1

yosifuji20

いささか力ずくの式ですが。

元の数値はA4:F4にあり、答えはG4に表示するものとします。

G4=W5-X5
H4=IF(A5>0,1,0)
I4=IF(B5>0,2,0)
J4=IF(C5>0,3,0)
K4=IF(D5>0,4,0)
L4=IF(E5>0,5,0)
M4=IF(F5>0,6,0)

O4=RANK(H5,$H5:$M5,0)
P4=RANK(I5,$H5:$M5,0)
Q4=RANK(J5,$H5:$M5,0)
R4=RANK(K5,$H5:$M5,0)
S4=RANK(L5,$H5:$M5,0)
T4=RANK(M5,$H5:$M5,0)
U4=IF(MAX(H5:M5)>0,MAX(H5:M5,0))
V4=IF(S5=2,5,IF(R5=2,4,IF(Q5=2,3,IF(P5=2,2,IF(O5=2,1,0)))))
W4=IF(U5>0,INDEX(A5:F5,1,U5),0)
X4=IF(V5>0,INDEX(A5:F5,1,V5),0)

式の意味は、
H4からM4はそれぞれの数値がゼロより代の時は右側からのセルの位置を表します。
O4からV4はH4からM4の値の順序を示します（昇順）
U4はO4からV4の最大値、これで一番右側の数値の位置がわかります。
V4はO4からV4の2番目の値、これで右側から2番目の有効値の位置がわかります。

W4に式はA4からF4の数値でU4であらわされる位置（一番右の数値）が表示されます。
X4に式はA4からF4の数値でV4であらわされる位置（右側から2番目の数値）が表示されます。
（W4とX4がIF文になっているのは、全部空白の場合や、有効地が1個しかないときにエラーになら無いようにするためです。）

最後にG4=W5-X5で答えが出ます。

以上はVBAで関数を作れば簡単なのですが、そのロジックを関数だけで作ってみました。
答えはこれで合うのですが、実用的でしょうかね？？