excel2013 計算のしかた

お忙しいところよろしくお願いいたします。

1行　1日　　　　100
2行　2日　　　　200
3行　3日　　　　空白
4行　4日　　　　空白
5行　5日　　　　空白
6行　差し引き計　100　　2行（下に移動）200－1日100（固定）

　　日付が進むと行が自動で変わるように計算したい。

質問者からの補足コメント

• 

  すいません。説明がわかりずらくて。
  月の1日から月末まで　　最後のセルに差し引きを計算させたいと思っていますが、入力は毎日しますが、最後のセルに　計算をさせたいのですが、入力の度に式を変更しなくて計算が出来ればと思っています。

    補足日時：2021/04/18 06:39

• 

  =INDEX(B4:B34,,COUNTIF(B4:B34,"<>0"))-B4
  
  これを使ってもうまくできません。なにが書式に問題かよくわかりません。
  よろしくお願いいたします。

    補足日時：2021/04/18 08:28

• 

  空白に　0　を入れて試しましたが、うまく計算されずに＃REF！が出ます。

    補足日時：2021/04/18 08:33

• 

  教えて頂いた式を入れるとこんな数字になります。
  
  年月日 保有残高
  2021年4月1日(木) 10,559,981
  2021年4月2日(金) 10,559,981
  2021年4月3日(土) 10,559,981
  2021年4月4日(日) 10,559,981
  ***
  2021年4月16日(金) 10,668,155
  2021年4月17日(土) 10,794,342
  2021年4月18日(日)
  ***
  2021年4月28日(水)
  2021年4月29日(木)
  2021年4月30日(金)
  
  計算結果 159,392,426　=SUM(B5:B34)-B4　　
  
  　　　　　10，794，342-10，559，981＝234，361になるはずです。

    補足日時：2021/04/18 10:15

No.1 回答者： banzaiA 回答日時： 2021/04/17 17:26

質問の意味がわからないのです。

No.2 回答者： banzaiA 回答日時： 2021/04/18 10:03

＞月の1日から月末まで　　最後のセルに差し引きを計算させたい

つまり、
２日から月末までの合計から１日の額を引けばいい、
ということですかね。
なら(金額が記入されるのが、b3:b33 だと仮定して）
=sum(b4:b33)-b3
ではだめですか。

No.3 回答者： banzaiA 回答日時： 2021/04/18 17:44

ああ、そういうことですか。

つまり、添付図で云えば、
最新の入力日4月17日の10,794,342から
1日の10,559,981を減算した値（234，361）を示せばいいのですね。
それなら、
=INDEX(B4:B34,LARGE(IF(B4:B34<>"",ROW(B4:B34)),1)-3,,1)-$B$4
と入力して配列数式としてください。
（Ctrl+Shift+Enter同時に押す）

この回答へのお礼

わかりにくい説明失礼をしました。
ありがとうございました。

お礼日時：2021/04/19 06:08

No.4 ベストアンサー 回答者： goomania 回答日時： 2021/04/18 23:25

No.3さんの回答が配列数式を使用したものだったので、配列数式を使用しない例を四種ほど、お示しします。

一つ目はSUMPRODUCT関数を使って、

=SUMPRODUCT((ROW($B$4:$B$34)=MAX(($B$4:$B$34
<>"")*(ROW($B$4:$B$34))))*1,$B$4:$B$34)-$B$44･･･①

という数式です。
二つ目はNo.3さんも使用されたINDEX関数を組合せて、

=INDEX($B$1:$B$34,SUMPRODUCT(MAX(($B$4:$B$34<>"")*(ROW($B$4:$B$34)))))-$B$4･･･②

という数式です。
三つ目にINDITRCT関数とAGGREGATE関数を組合せて

=INDIRECT("B"&AGGREGATE(14,6,INDEX(ROW($B$4:$B$34)*($B$4:$B$34<>"")*1,0),1))-$B$4･･･③

という数式です。
添付画像をご覧ください。
この画像の例では最終入力位置はB20です。
従ってB20の値からB4の値をマイナスするとこになりますが、B20という位置を計算するのに①②では
SUMPRODUCT(MAX(($B$4:$B$34<>"")*(ROW($B$4:$B$34))))･･･④
③では
AGGREGATE(14,6,INDEX(ROW($B$4:$B$34)*($B$4:$B$34<>"")*1,0),1)･･･⑤
という計算を行っています。
つまり組合せ方としては①、②に⑤を組合せて使う方法や、③に④を組合せて使うこともできるので、さらに種類が増えます。

なお、LOOKUP関数は検索値が見つからないときは検索範囲の最終値を返すという性質を利用すれば、見つからない値を検索値とすればよいので、四つ目として最も簡単な数式、

=LOOKUP(10^15,$B$4:$B$34)-$B$4

という数式も使えます。

この回答へのお礼

ありがとうございました。色々な計算式教えて頂き大変助かりました。
今後もよろしくお願いいたします。

お礼日時：2021/04/19 06:06