Excel関数 足し算

質問を失礼致します。お助け下さい。
仕事でExcelの関数を使用して業務の快適化をすることになりました。
新人だからと任されましたが、学生時代からExcelの操作をあまりしなかったため、関数を扱えきれず、調べても理解ができないので質問させていただきます。

Excelで、例えば、A1～A10まで数字が1と3交互に入っていたとします(A1セルに1、A2セルに3、A3セルに1.....)。その時、上から足し算をして、合計が5以下(最大で)になった時にB列に値を表示する関数のやり方は、ありますでしょうか？
この場合、A1(1).A2(3).A3(1)セルの合計が5なので、ここで一区切り、次はA4(3).A5(1)セルの合計が4で一区切り.....というような形が理想です。

お教え下さい。よろしくお願い致します。

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

• ご回答、ありがとうございます。
  教えていただいた式を入れて見ましたが、いまいち反映されないようで.....1行目と2行目を分けるコンパクトの式とは、どのようなものでしょうか？
  差し支えなければ、教えて頂きたいです。
  よろしくお願い致します。

  No.1の回答に寄せられた補足コメントです。 補足日時：2021/01/15 11:32

• ご回答いただき、ありがとうございます。
  goomania様がおっしゃる通り、私の質問の仕方が不明確でした。
  A列には、1.3が交互ではなく、様々な数値が入ります。その中で、上から足し算をし、一定の値を超えないように区切って行きたいということです。
  ご回答頂いた内容も、今後ぜひ活用させていただきます。ありがとうございます。
  もしよろしければ、上記の内容を踏まえたお考えを教えて頂きたいです。よろしくお願い致します。

  No.2の回答に寄せられた補足コメントです。 補足日時：2021/01/15 11:37

• 最初に教えていただいた式ですが、私の入力方法に誤りがありました。もう一度打ってみたら、反映することが出来ました。また、欲しい操作をすることが出来ました。
  本当にありがとうございます。

  No.3の回答に寄せられた補足コメントです。 補足日時：2021/01/15 15:12

No.1 回答者： fujillin 回答日時： 2021/01/14 14:04

こんにちは

ご質問の意味を正しく理解できているかわかりませんが…

B１セルに以下の式をいれ
=IF(SUM(A$1:A2)-IF(ROW()=1,0,SUM(OFFSET(B$1,,,ROW()-1)))>5,SUM(A$1:A1)-IF(ROW()=1,0,SUM(OFFSET(B$1,,,ROW()-1))),"")
下方にフィルコピーでいかがでしょうか？

1行目と、２行目以降の式を別に分ければもっとコンパクトな式にできます。
全部を同じ式にしたために、自分より前の行を参照する際に、1行目がエラーになるのを回避するために式が長くなってしまっています。

No.2 回答者： goomania 回答日時： 2021/01/14 17:06

ご質問者がおっしゃる

>A1～A10まで数字が1と3交互に入っていたとします

という表現から想像できるA列の内容は
(1)A1から1,3,1,3,1,3,1,3,･････となるパターン
(2)A1から3,1,3,1,3,1,3,1,･････となるパターン
の二種類しかないと思います。(ご質問者の想定が、これと異なる場合はコメント等で追記願います)

だとすると、B列のパターンも二種類しかなく、
(1)B3が5、B5から以下一行おきに4を繰返すパターン
(2)B2から以下一行おきに4を繰返すパターン
となり、いずれの場合も、ある行以下では4を一行おきに繰返すというものになってしまいます。

こうなると数式というよりオートフィルで足りる話になり、どうもご質問者の真意を理解できていない気がします。

当然 fujillinさんの数式を利用しても、上記(1)(2)の結果が返るのですが、パターンを判別するだけでよいのでB1に
=IF((ROW()=1)+(MOD(ROW()+($A$1=1),2)),"",4+(ROW()=3))
と言う簡単な数式を記述し、下方向へコピーでも対応可能です。

しかし、前述のとおり、これだと、ご質問者の真意と異なる気がするので、ご質問者の意図するところが本当に上記(1)(2)でよいのかコメントいただきたいと思います。

No.3 回答者： fujillin 回答日時： 2021/01/15 14:03

No1です

＞いまいち反映されないようで
私の環境では反映されているようです。
（No2様の環境でも反映されている模様です）

質問者様の環境が違うのか、私がご質問の意味を取り違えているのでしょうね。

No.4 ベストアンサー 回答者： goomania 回答日時： 2021/01/15 15:19

>A列には、1.3が交互ではなく、様々な数値が入ります。

その中で、上から足し算をし、
>一定の値を超えないように区切って行きたいということです。

ということのようですので、その主旨に沿って数式を考えてみました。

添付画像をご覧ください。
まず、作業列を用意します。本当に必要な作業列は「A列の上からの合計値が最大値を超えたか」という「合計値>最大値」という表題のD列です。
しかし、これだけでは判り難いので、計算上必要ではないのですが、判り易くするため「A列の上からの合計値」を「合計値」という表題のC列と、ご質問者のいう「区切っていきたい」を実現するための「グループ番号」という表題のE列を表示してあります。

ここで、ご質問者のいう「一定の値」をG1に入力しておくことにします。
さらに、前提条件として、A列に入力される数値は「一定の値」以下の数値であるとします。

添付画像のように、D2に

=IF(SUM(C1,A2)>$G$1,1,0)

という数式を記述し、下方向へコピーし、A列の上からの合計値が最大値を超えたら1を、そうでなければ0を返すようにします。

B列に何を表示するのかという問題はありますが、せっかくE列のグループ番号も表示しているのでこれを数えてグループ内データ件数を表示してみます。B2に

=IF(D3<>1,"",COUNTIF($D$2:D2,1)+1&"グループのデータ数は"&COUNTIF(E:E,E2)&"個です")

という数式を記述し、下方向へコピーし、各グループの最終行かどうかを判定して、最終行なら「指定した内容」を表示するようにしてみました。

ご質問者のご希望が、A列の上からの合計値が一定の値を超えないようにクループ分けし、そのグループに何らかの値を表示するというものだと解釈したからです。ご質問者の真意と一致していれば幸いです。

No.5 回答者： goomania 回答日時： 2021/01/15 15:33

No.4です。

>もう一度打ってみたら、反映することが出来ました。
>また、欲しい操作をすることが出来ました。
とのことで解決おめでとうございます。
B列に表示させるのが、一定の値を超えないA列の上からの合計値ならば
前回回答のB2に記述する数式を
=IF(D3<>1,"",C2)
とすれば、同様の結果になると思います。

この回答へのお礼

質問内容を汲み取って下さり、また、丁寧なご説明、ありがとうございました。
1つの関数でまとめるか、貴方様が教えてくださったセルを分けて記載するかを検討させていただきたいと思います。
関数が楽しいと思えました！
大変勉強になりました。
本当にありがとうございました。

お礼日時：2021/01/15 15:47