第19回目 凝った計算式の作り方2

○第19回目　凝った計算式の作り方2

　斜めに数値を変化させていく表を作りました。(一つ横にいくと一つ下がる)

　その数値を最初から最後まで足すという式を作ります。

　一つ横にいくと一つ下がると変化させていくという計算式は簡単です。単純なコピーで出来ます。

　だが、一つずつずれている同一のグループの和となると、同じ行であれば、SUM関数で簡単ですが、一つずつずれているので、一筋縄ではいきません。

　(表には二種類の性格を持ったグループがあります。一つは最初からある、つまり最初の列にあるグループです。もう一つは、途中から発生するグループです。ここでは前者のグループのみを考えます)


　マクロを作るのも手です。

　が、ここでは計算式で作ってみます。



　考え方です。

　あるグループより下の合計を作る。

　それをコピーして、もう一つ下のグルーの合計を求める。この上下の差が、求めたい一つのグループの合計となります。



　第一番目が眼目です。

　データはB7から、L60まであります。

　最初のB列の合計は7行目から60行目まで。

　次のC列の合計は8行目から60行目まで。

　次のD列の合計は9行目から60行目まで。

　となります。この合計は、65行目に入れます。

　

　計算式の仕組みを表しますと、列記号と開始行番号が変わり、終わりの行番号が変わらないという形になります。

　

　では、単純にコピーしてみましょう。最初のセルに式を作ります。これを横にコピーするのです。

　B65は、"=sum(b7:b60)"となります。

　(これは列記号が1桁ですので単純にやっていますが、本来は、二桁にして、完成後に列を削除してください)

　あとのために、"=sum(b7:b$60)"としてください。

　これを横にコピーします。

　ここで問題になるのは、開始行です。この開始行を変えるのです。

　まず、式を文字列にして見ましょう。

　エクセルの置換で、"="を"xxx="してください。

　これを67行目に値コピーをします。

　66行目には、7から始まり+1づつしてください。隣の列は8行目から60行目の合計だからです。

　この二つのセルの内容を一部結合させます。

　それを、65行目に入れましょう。

　式は、=LEFT(B67,9) & B66 $ RIGHT(B67,6)となります。

　xxx=sum(b7:b$60)　を

　xxx=sum(b　　:b$60)に分けるのです

　 　 　　　　↑　　↑

　　　　　　　9　　右から6文字目

　これを横にコピーします。

　次のこれらを、65行に値コピーをします。

　エクセルの置換で、"xxx="を"="に修正すると出来上がりです。

　これが正しいことを確かめたら、これを66行目以降にコピーします。

　すなわち、今度は、B列では一つ上がって8行目からの合計になります。(終了行は"$"をつけているので変わりません)

　これで完成と思いきや、まだなのです。

　

　問題は、コピーすると、7行目を一つずつコピーするので、後ろの列の後ろの行になると、60行より大きなものができてしまうのです。

　

　今作った計算式を、xxxをつけて文字列にして対処してみましょう。

　またまた難しいです。不要な箇所が階段状になるからです。