数値の相関と関数

質問文が削除されましたので、表現や視点をかえてもう一度質問させて頂きます。


増減行の数値で、『出現した変数行の数値を「0」(空白)にする』


表中の緑色の丸番号をご覧ください。

1.
変数行すべてが空白の場合で、
増減の数値-3000(AN2)が表示された時、①の-3000(AN3)を表示

2.
②の500が表示の時、
-3000(AN3)①　＋　　500(AP2)② ＝　　-2500(AN3)➂

3.
➃の-1000(AQ2)が表示の時、　
-2500(AN3)➂　＋　　-1000(AQ2)➃ ＝　　(AN3)➂
としたところで、-2500(AN3)➂の数値が「0」(空白)に向かうどころか
かえって(AN3)➂の数値が拡大して「0」(空白)から遠ざかることになるため、
-1000(AQ2)➃の数値は先送りして、その数値を変数行の(AQ3)➄に据え置く。

4.⑥の5000(AS2)が表示の時、
-2500(AN3)➂【優先順位一位】＋　-1000(AQ3)➄【優先順位二位】＋ 5000(AS2)⑥ ＝　1500(AS3)⑨
5000(AS2)⑥の数値は、-2500(AN3)➂【優先順位一位】と-1000(AQ3)➄【優先順位二位】を
『出現した変数行の数値を「0」(空白)にする』数値であり、「0」になった➆と⑧は空白。
余った数値1500は、「余り」をもたらした数値5000(AS2)⑥の変数行(AS3)⑨に置く。

5.⑩の2000(AT2)が表示の時、
　1500(AS3)⑨　＋　2000(AT2)⑩ ＝　(AS3)⑨
としたところで、1500(AS3)⑨の数値が「0」(空白)に向かうどころか
かえって(AS3)⑨の数値が拡大して「0」(空白)から遠ざかることになるため、
2000(AT2)⑩の数値は先送りして、その数値を変数行の(AT3)⑪に据え置く。

6.⑫の-3500(AS2)が表示の時、
1500(AS3)⑨【優先順位一位】＋　2000(AT3)⑪【優先順位二位】＋ -3500(AU2)⑫ ＝　0
-3500(AU2)⑫は、1500(AS3)⑨【優先順位一位】と2000(AT3)⑪【優先順位二位】を
『出現した変数行の数値を「0」(空白)にする』数値であり、「0」になった⑬と⑭は空白。


このような例題の要件を満たし変数行に設ける関数式を教えてください。

なおVBAは使用しません。

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

• 本来は1行には、1月、2月、3月・・・が表記されています。
  
  【増減】　→　実際入金額と入金予定額の差額
  
  「0」 　　　→ 月分を完納したことを指す
  「＋数値」　→　現金過入金を指す
  「－数値」　→　現金不足金を指す
  ※増減データは、別のエリアで計算されて月ごとに表示されていく
  ※【増減】の月ごとの計算時期は、月替わりの毎月１日
  
  
  【変数】　→　現金過不足の状況をあらわしたもの
  
  「0」(空白) 　　　→　過払い金や未納金がないことを指す
  「＋数値」　　　　→　過払い金があることを指す
  「－数値」　　　　→　未納金があることを指す
  ※【変数】(現金過不足)の数値は【増減】の数値と連動しているので
  　月替わりの毎月１日に反映することになります。

    補足日時：2017/02/22 12:27

• ※丸番号は、添付画像の緑色の番号
  
  ●【変数】現金過不足の数値ない状況(変数行すべて空白)
  
  つまり、過去に「過払い金」・「未納金」がない状況下で
  【増減】行での現金過入金(＋数値)、現金不足金(－数値)が
  それぞれ「過払い金」・「未納金」とみなされ、
  【変数】行には、【増減】行で発生した月の
  　現金過入金・現金不足金の額が表示される①
  
  ●【変数】現金過不足の数値ある状況(＋数値or－数値)
  仮に過去に「未納金」【変数行】ある状況下で発生した【増減】行での
  「現金過入金」は②、「過払い金」とはみなさず、「未納金」解消まで
  「未納金の清算」とみなされる➂。「未納金」解消後でなった「余剰金」
  　は、「過払い金」となる⑨。そして「過払い金」となった額を
  【変数】行に表示させる。⑨

    補足日時：2017/02/22 12:30

• 逆に、
  仮に過去に「過払い金」【変数行】ある状況下で発生した【増減】行での
  「現金不足金」は、「未納金」とはみなさず⑫、「過払い金」解消まで
  「過払い金の清算」とみなされる。「過払い金」解消後でなった「不足金」
  　は、「未納金」となる。そして「未納金」となった額を【変数】行に
  　表示させる。
  
  ●【変数】「未納金」がある状況下での新たな現金不足金
  
  過去に【変数行】「未納金」がある状況下➂で、後に【増減】行での
  発生した現金不足金は➃、「新たな未納金」とみなされ、その現金不足金が
  発生した月に「新たな未納金」として【変数行】に表示させる➄

    補足日時：2017/02/22 12:33

• ●【変数】「過払い金」がある状況下での新たな現金過入金
  
  過去に【変数行】「過払い金」がある状況下で⑨、後に【増減】行での
  発生した現金過入金は⑩、「新たな過払い金」とみなされ、
  その現金過入金が発生した月に「新たな過払い金」として
  【変数行】に表示させる⑪
  
  ●増減行の数値で、『出現した変数行の数値を「0」(空白)にする』
  
  過去に遡って、最初に発生した「月」の「過払い金」(「未納金」)を
  優先順位にして、後の「現金過入金」(「現金不足金」)の額に応じて
  変数行の数値を「0」(空白)に向けて変えていく➆⑧　　⑬⑭

    補足日時：2017/02/22 12:34

• ●
  契約開始前・契約終了後のセルは「空白」。
  契約終了後、「契約終了」の表示は、空白に変更
  
  ●
  　年払契約者は、更新月の翌月1日にて『増減』で「現金過入・現金不足」の判定が行われ、
  　更新月以外で「未納金の納付」を実施。
  「現金過入の清算」は、「実際入金額を変更して減らす」方法を採用しているので
  　次回の更新月で実施。

    補足日時：2017/02/22 12:35

No.2 ベストアンサー 回答者： yuji3690 回答日時： 2017/02/22 22:09

なるほど。

分かりました。
とりあえず説明しながらと思ったのですが、ちょっとややこしくなりすぎたので完成したものだけ提示します。
（というか途中まで書いてたはずなんですが、何かのタイミングで更新ボタン押してしまったのか、気付くと消えてしまってたので、書き直す気になれなかったです…）
一応添付された物の通りには表示しますが、他に不具合があるかもしれません。

なお、AJ列が空白の想定で、データはZZ列まで想定しております。
（超える場合はZZの部分を修正してください）

AK3の例
=IF(AK2=0,"",IF(SUM($AJ2:AJ2)=0,IF(IF(AK2<0,-AK2,AK2)<SUMIF(AK2:$ZZ2,IF(AK2<0,">0","<0"))*IF(AK2<0,1,-1),"",AK2+SUMIF(AK2:$ZZ2,IF(AK2<0,">0","<0"))),IF(SUM($AJ2:AJ2)*AK2<0,IF(IF(SUM($AJ2:AJ2)<0,-SUM($AJ2:AJ2),SUM($AJ2:AJ2))<AK2*IF(AK2<0,-1,1),IF(SUM($AJ2:AJ2)+AK2>SUMIF(AK2:$ZZ2,IF(AK2<0,">0","<0"))*IF(AK2<0,1,-1),AK2+SUM($AJ2:AJ2)+SUMIF(AK2:$ZZ2,IF(AK2<0,">0","<0")),""),""),IF(IF(AK2<0,-1,1)*(SUM($AJ2:AJ2)+AK2)>SUMIF(AK2:$ZZ2,IF(AK2<0,">0","<0"))*IF(AK2<0,1,-1),AK2+SUMIF(AK2:$ZZ2,IF(AK2<0,">0","<0")),""))))
必要な列だけコピーしてください。

この回答へのお礼

yuji3690さま！　で、できていまーす☆彡
貴方様はとても凄すぎます。

式は、有難く重宝させて頂きます。

先週に続き、今週も大変お世話になりました。
私の愚問に貴方様の貴重なお時間を割いて頂き、
心から厚く御礼を申し上げます。

また機会がございましたら、是非ご教授お願いします。
ありがとうございました。m(_ _)m

お礼日時：2017/02/23 08:47

No.1 回答者： yuji3690 回答日時： 2017/02/22 00:56

条件が正確に書かれてない気がします。

何がどういう条件となった時何を表示するのか、を明確に書いてください。
条件に一致した時にスルーする場合でも、スルーするためには条件の判断が必要です。

①③⑦という風に同じセルで何度も表示が変わっているので、条件が多数分かれていることは分かりますが、何を判断基準としているのかがはっきりしません。
②に500が入ったから-3000+500＝-2500を表示する。というのは分かりますが、
②というのはセルの場所が決まっているのか？APじゃなくてAOに500と入っていたらどうなるのか？
そういうことがさっぱり分かりません。

そもそも②④⑥⑩といったセルはどう表示されているのですか？手入力ですか？
②がAOじゃなくてAPなのは1列あけたかったからあけただけですか？

仮にAP2~ZZ2に数字を入れてすべて0から遠ざかる場合、
AN3は|AN2|<|AN2+AP2|、|AN2|<|AN2+AP2+AQ2|、|AN2|<|AN2+AP2+AQ2+AR2|、…
|AN2|<|SUM(AN2:ZZ2)|
と比較し続けるわけでしょうか？
であるなら、VBAや作業用セルを使わずにやることは不可能でしょう。
作業用セルを使ったところで、AN4に|AN2|<|AN2+AP2|ならAN5、違うならAN2+AP2、
AN5に|AN2|<|AN2+AP2+AQ2|ならAN6、違うならAN2+AP2+AQ2、
と無限（エクセルでやる以上列及び行に限界はあるが）にセルが必要となる。
ある程度で必ず0に近付くとして、AN4に表示されたものをAN3に表示すれば目的の表示となる気がします。

この補助列によるデータを用いたとして、
その行にはそのセルより左に数字が無くて、上の行にはそのセルより右に0以外の数字が無い場合、上のセルを表示…かと思いきや、AU3を見るとそういうわけでもない。
変数の行の右端の数字を見ると、増減の列の合計-そのセルより左のセルの合計を表示しているということでしょうか。
いや、それだとAP3の時も表示されてしまうので…
そもそもAP3に表示されてないのは代わりにAN3が変化したからで…

Ⅰそのセルより左に数字がなく、その上のセルより右にも数字がないとき、上のセルの数字を表示。
Ⅱその上のセルより右に数字がある時は…

何かよく分からなくなってきたのでまた時間できたら考えます。
具体的な数値が入った時の限定的な説明ではなく、どういう状況の時にどういう値を表示するのか、といったことを説明して貰えれば、それに合わせた式の作成もできると思うのですが…

この回答へのお礼

yuji3690さま、こんにちは。前回の質問の際はありがとうございました。
表と関数
https://oshiete.goo.ne.jp/qa/9636781.html

今回、この質問を投稿する前に
表と関数(1) --- 【長文ご容赦m(_ _)m】
表と関数(2) --- 【長文ご容赦m(_ _)m】

と投稿したのですが、2回に分けて投稿したのが原因なのか、それとも質問内容に問題があったのか
削除されてしまいました。TT
そのため、今回の質問おいて慎重になり過ぎて抽象的な質問内容になってしまいました。

今回の質問内容は、前回の質問の際、yuji3690さまが指摘して下さった「未納金の納付」などを含んだ
ものでした。

この欄も字数制限があるため、「補足コメント」をご覧ください。


＞①③⑦という風に同じセルで何度も表示が変わっているので、・・・
＞②というのはセルの場所が決まっているのか？APじゃなくて・・・

「補足コメント」を既にご覧になったという前提で・・・

①は、
過去に「過払い金」・「未納金」がない状況下で
【増減】行での現金不足金(－数値)が、「未納金」とみなされ
【変数】行には、発生した月にその数値を表示させたものです。

(AN1)(AO1)(AP1)を4月、5月、6月・・・

0(AO2)　→　5月は「月定分」だけ納め、前月の未納を清算するには至りません

②は、6月には、500(AP2)の現金過入ですが、過去の4月に未納金-3000
があるため、「過払い金」とはみなさず「未納金の清算」とみなされ、
500分だけ清算を行いました。(-2500)➂

＞そもそも②④⑥⑩といったセルはどう表示されているのですか？手入力ですか？

　実際入金額と入金予定額の差額という意味で
増減データは、別のエリアで計算されたものです。


＞であるなら、VBAや作業用セルを使わずにやることは不可能でしょう。

VBAは無理ですが、作業用セルの使用は構いません。


yuji3690さま、申し訳ございません。
宜しくお願い致します。m(_ _)m

お礼日時：2017/02/22 12:36