Excelで配列数式を使わずにとびとびの集計をしたいのですが

質問

ご覧いただきありがとうございます。Excelで、下記のような条件で集計を行いたいと思っています。配列数式を使う方法は考えついたのですが、できれば配列数式を使わず、作業セルもマクロも使わずに普通の関数式で集計したいのです。どなたかおわかりになる方がいらっしゃいましたら、よろしくご教示をお願いいたします。

（条件）
　○４行一組のデータのそれぞれ１行目の数値を足し合わせる。
　○ただし、各組の１行目が空欄の場合は２行目の数値を使う。３行目・４行目は関係なし。
　○データの先頭はA4で、データ数は一定していない。

（考えついた式）
{=SUM(IF(MOD(ROW(A4:INDIRECT("A"&ROW()-4)),4)=0,IF(A4:INDIRECT("A"&ROW()-4)>0,A4:INDIRECT("A"&ROW()-4),OFFSET(A4:INDIRECT("A"&ROW()-4),1,0))))}

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

回答No.1

ham_kamo · ベストアンサー

データが何行目まであるかわかりませんが、仮に100行とした場合、

=SUMPRODUCT((MOD(ROW(A1:A100),4)=0)*((A1:A100)))+SUMPRODUCT((MOD(ROW(A4:A100),4)=0)*(ISBLANK(A4:A100))*(A5:A101))

でできないでしょうか。

お礼

早速のご回答ありがとうございます。はじめの２つのA1をA4にして試してみたところ、希望どおりの動きをしてくれました。
　私もこういうスマートな式をかけるようになりたいです。どうもありがとうございました。

回答No.3

Wendy02

こんばんは。

元の式を、ほとんどそのまま移植してみました。

=SUMPRODUCT((MOD(ROW($A$4:INDIRECT("A"&ROW()-4)),4)=0)*(A4:INDIRECT("A"&ROW()-4)))+=SUMPRODUCT((MOD(ROW($A$4:INDIRECT("A"&ROW()-4)),4)=0)*ISBLANK($A$4:INDIRECT("A"&ROW()-4))*($A$5:INDIRECT("A"&ROW()-3)))

ただし、
>できれば配列数式を使わず、

という条件は、どなたの式も満たしておりません。配列確定をしないだけで、SUMPRODUCT を使えば、その引数は、配列になっています。補助列を使ったほうが、修正は利き易いとは思いますが。

お礼

ご回答ありがとうございます。私が意図したのは、「Ctrl＋Shift＋Enter」なしで確定できる関数式ということなんです。
　自分以外の人にもシートを配布して使ってもらうので、「このセルをいじったら、確定のときは Ctrl＋Shift＋Enter を押してください」とか、「行挿入したら補助列の条件式をコピーして貼り付けてください」というような説明は極力少なくして、集計エラーをなくしたいのです。質問文の説明が足りず申し訳ありませんでした。

回答No.2

imogasi

A4:B15に（数字はB列です。OKWAVEではA列のようになるが注意お願い）
A列　　B列
a 2
1
1
1
b 3
2
2
2
c 4
3
3
3
とする
答えを出すセルに
=SUMPRODUCT((B4:B15)*(MOD(ROW(A4:A15)+4,4)=0))
で結果は
9
B列　X　（行数＋４の４で割ったあまりが０かーー＞Trueの行は１、False　行は０としてエクセルは計算する）＝＝＞第４行から１５行まで掛け合わせる
をやっていることになります。
質問の配列数式もMODを使うと、質問記述「考え付いた式」よりずっと簡単になります。

補足

早々のご回答ありがとうございます。お示しいただいた式で試してみましたところ、先頭の２、あるいは３、４を削除した場合の答が８になってほしいのですが、それぞれ７、６、５となりました。A列にa、b、c、B列に数値を入力して試したのですが、試し方に間違いがあるのでしょうか。