Excelで行数が増えた際の計算

質問

いつも、お世話になります。
色々考えましたが、解決できませんので、皆様の知恵を拝借願います。

あるExcelに入力した数値は以下の式にて合計値を求めます。

=sum(a1,a3,a5・・)
=sum(b2,b4,b6・・)
=sum(c1,c4,c7・・）

　合計値は一定間隔にある数値を取得し、合計値を出力します。
悩んでいるのは、行が追加された場合には、合計値の計算式も手動にて直さなければいけないため、とても手間がかかっている点です。

　この合計値の式を行が追加された際に自動的に、訂正を行うようにすることはできないでしょうか？

よろしくお願いいたします。

回答No.4

fmajin · ベストアンサー

それぞれの列の1行から10行目まで数値が入力されているとすると、それぞれ以下のような数式で求めることができます。

=SUMPRODUCT((MOD(ROW(A1:A10),2)=1)*1,A1:A10)
=SUMPRODUCT((MOD(ROW(B1:B10),2)=0)*1,B1:B10)
=SUMPRODUCT((MOD(ROW(C1:C10),3)=1)*1,C1:C10)

この数式は、配列数式というテクニックを使用したもので、説明し出すと長くなるので、かいつまんで解説しておきます。
各セル範囲の違いはもちろんですが、ポイントは
「MOD(ROW(セル範囲,2)=1などの部分です。ROW関数は行番号を返す関数で、MOD関数は割り算の余りを返す関数です。この場合は、2で割った余りが1の場合ですから、「奇数行」だけを対象に足すことができます。
3番目のC列を対象にしたのは、3行ごとに加算するようですから、3で割った余りが1の場合という数式にします。
「((MOD(ROW(A1:A10),2)=1)*1」で「(条件式)*1」のようにカッコで囲んで「*1」とするのは、省略できないのでご注意ください。

なお、行を増やす場合、たとば参照範囲の端になっている10行目で一行挿入すると、数式も変更しなければなりませんが、9行目などで挿入しておいて、10～11行目をコピーして9～10行目に貼り付け、11行目の値をクリアするという手順にすると数式のほうは自動的に参照範囲が広がりますので数式のほうは変更する必要がありません。

お礼

ありがとうございます。
とても参考になりました。

回答No.6

noname#204879

上から順に
=SUMPRODUCT((MOD(ROW(1:99),2)=1)*(A1:A99))
=SUMPRODUCT((MOD(ROW(1:99),2)=0)*(B1:B99))
=SUMPRODUCT((MOD(ROW(1:99),3)=1)*(C1:C99))

回答No.5

Wendy02

こんばんは。

私には、行が増えたときに、どのようにしなければならないのか、求める結果が見えていないのですが、みなさんの解答に合わせます。

あえて、重複回答をしてもしょうがないので、

例えば、L1 に　セルの最終行の番号を置いて、

L1:10

=SUMPRODUCT((MOD(ROW(INDIRECT("A1:A"&L1)),2)=1)*INDIRECT("A1:A"&L1))

=SUMPRODUCT((MOD(ROW(INDIRECT("B1:B"&L1)),2)=0)*INDIRECT("B1:B"&L1))

=SUMPRODUCT((MOD(ROW(INDIRECT("C1:C"&L1)),3)=1)*INDIRECT("C1:C"&L1))

とすればよいと思います。

なお、MOD(ROW(A1:A10)) のセルの範囲の引数は、特に、計算するセルにあわせなくてもよいのですが、便宜的に、(MOD(ROW(B1:B10),2)=0)*(B1:B10)　という表現をしているだけで、(MOD(ROW(A1:A10),2)=0)*(B1:B10) でも、可能です。

回答No.3

mshr1962

=SUMPRODUCT((MOD(ROW(A1:A100),2)=1)*(A1:A100))
これでA1:A100の範囲の奇数行（行数を2で割った余りが1）のセルの数値を合計できます。
同様に
=SUMPRODUCT((MOD(ROW(B1:B100),2)=0)*(B1:B100))=sum(b2,b4,b6・・)
=SUMPRODUCT((MOD(ROW(C1:C100),3)=1)*(C1:C100))=sum(c1,c4,c7・・）

回答No.2

oyaji-man

例えＡ列のa1 a3 a5・・・
ならばＡ列の奇数行合計ですよね？
だとしたら
=SUM(IF(MOD(ROW(A1:A？),2)=1,A1:A？,0))
でお好みの範囲まで指定してください。

注意: 上の数式を入力しても答えはただの合計となります。
　　　偶数・奇数行の合計をするために配列数式として数式を入力する必要があります。
　　　方法は、数式を確定するときに [Ctrl] + [Shift] + [ENTER] キーを押してください。

ちなみに偶数行なら
=SUM(IF(MOD(ROW(A1:A？),2)=0,A1:A？,0))
です。
これならどこに行を挿入・削除しても常に偶数行・奇数行の合計を返します。

回答No.1

NIWAKA_0

私もこれには悩まされていたのですが…

入力セルと別に、計算させるかどうかの判定セルを作成し、
SUMIFを使って合計させる、というのはどうでしょうか。

具体的には（お示しの例とは異なるのですが）
Ａ列に値を入力し、Ｂ列に例えば１なら１と入力します。
　Ａ　　Ｂ
　２０　　１　←合計する
　３２　　　　　　　←合計しない
　１５　　１　←合計する
　：　　　：
　：　　　：
こんなかんじ。
で合計欄には
=SUMIF($B$1:$B$8,1,$A$1:$A$8)
とすれば、Ｂ列に"1"が入力されている行だけ合計されます。

行を追加するときは、合計範囲の上端と下端以外、上の例なら２行目から７行目の間に入れ、Ｂ列の判定セルに忘れずに"1"を入力します。

どうでしょう。

お礼

すばやいヘルプありがとうございます。
可能な限り入力箇所は少なく行いたいので、他の方からご教示いただいた、方法を試してみます。