[スプレッドシート]ARRAYFORMULAを用いて複数条件の集計をまとめたい

Question

##前提
同一スプレッドシート上に「LOGシート」と「SUMシート」があります。
現状、SUMシート上、E2以降に以下の数式を設定しています。
LOGシートからSUMシート上に複数条件のもと集計を行うのですが、実際にはLOGのデータ数が数千行となるため、ARRAYFORMULAを使用して数式をまとめたいと思っています。

if($C2="R",sumif('LOG'!$B2:$B,$A2,'LOG'!$E$2:$E)*$E2, if($C2="M",sumifs($E2:$E,$A$2:$A,$A2,$C2:$C,"R"),0))

##トラブル内容
サンプルでは簡略化のため必要最低限の列数にしていますが、本環境ではその他の数式が設定されている項目（列）も存在します。
その為、処理パフォーマンスの向上を考慮し、E2にARRAYFORMULAを使った以下の数式を入力してみましたが、C列がMの行が上手く集計されずに困っています。

ARRAYFORMULA(IF($C$2:$C="R",SUMIF(LOG!$A$2:$A,INDIRECT("$A"&ROW()&":$A"),LOG!$B$2:$B)*INDIRECT("$D"&ROW()&":$D"),IF($C2:$C="M",SUMIFS($E$2:$E,$A$2:$A,INDIRECT("$A"&ROW()&":$A"),$C$2:$C,"R"),0)))

[修正] SUMIFSの合計範囲の指定が間違っていましたので「$D$2:$D」から「$E$2:$E」へ修正しました。
ただし、この場合、循環参照エラーとなります。（本環境では合計範囲その他のミスはなく、エラーもございません）

集計内容は以下のようになります。

1. SUMシート上のC列（role）が「R」の場合、LOGシートからA列(title)が同一の値(point)を取得し合計値を得たのち、各行、D列の値(rate)を掛ける。
2. SUMシート上のC列（role）が「M」の場合、SUMシートからA列(title)が同一及びC列(role)が「R」の値(point)の合計値を取得する。

ARRAYFORMULAを使用しなくても、結果としてE2への数式入力のみで望む結果が得られるなら問題ございません。
エクセルやスプレッドシートの関数に詳しい方、お知恵をいただければと思います。
うまい解決方法がない場合には、GASにてカスタム関数を作成しようかと思っています。
よろしくお願いいたします。

[修正]補足漏れでした。
別途列追加や別シートへ計算用のデータ展開する等の解決策ではなく、あくまでもE2への数式入力のみとなります。

※スプレッドシートのタグが見当たらなかったため、EXCELとしていますが、環境はGoogle Spread Sheetです。

Answer 1

すでに自己解決されたかもしれませんが、いちおううまくいったので書いておきます。途中途中にIFERROR()をかましてもよいでしょう。ちなみに数千行は試していません。

=ARRAYFORMULA(IF(C2:C="R",VLOOKUP(A2:A,QUERY(LOG!A2:B,"select A,sum(B) group by A"),2)*D2:D,IF(C2:C="M",VLOOKUP(A2:A,QUERY(LOG!A2:B,"select A,sum(B) group by A"),2)*VLOOKUP(A2:A,QUERY(A2:D,"select A,sum(D) where C!='M' group by A"),2),)))

(参考サイト)
https://productforums.google.com/forum/#!topic/docs/2XVQQFi1zXs;context-place=topicsearchin/docs/arrayformula$20query

ただ、ロジック的には１行１行QUERY関数を発行しているのでかなり非効率的であることは考えたほうがいい（分離したほうが本当はいい）とは思いますね。

Comments

[true]

何度もすみません、
where C!='M' はwhere C='R'
のほうが自然ですね。

[stcamp]

trueさま

ありがとうございました！
テストデータで試したところ、上手くいきました。

※なにが原因かなんのバグかわかりませんが、最初に数式をセットした際、延々と行を追加し続けて無限ループに陥ってしまいました。

とにもかくにもご回答頂きありがとうございました。

Answer 2

失礼、E2に、これです。

=ARRAYFORMULA(IF(C2:C="M",VLOOKUP(A2:A,G2:H,COLUMNS(G2:H),FALSE),IF(C2:C="R",VLOOKUP(A2:A,G2:H,COLUMNS(G2:H),FALSE)*D2:D,"")))

Comments

[stcamp]

trueさま

回答ありがとうございます。
早速試してみました。

残念ですが、SUMシートのroleがMの場合の集計結果が求めている内容と異なっていました。（集計内容の２）
具体的には、E2=7,E5=746.7としたいのです。

Rのrate合計を掛けるのもありかなと、再度試行錯誤しています。

Answer 3

一度QUERY関数などでLOGシートを集計し、そのうえでSUMシートに回したほうがよいのではないかと思います。

具体的には、SUMシートのG1あたりに

=QUERY(LOG!A2:B,"select A,sum(B) group by A")

としておき、

E2に、

IF(C2:C="M",VLOOKUP(A2:A,G2:H,COLUMNS(G2:H),FALSE),IF(C2:C="R",VLOOKUP(A2:A,G2:H,COLUMNS(G2:H),FALSE)*D2:D,"")))

です。

ただ、個人的にVLOOKUPはパフォーマンスが微妙だと思っていて、

=IF(C2="M",IFERROR(INDEX(H:H,MATCH(A2,G:G,0)),),IF(C2="R",IFERROR(INDEX(H:H,MATCH(A2,G:G,0))*D2),))

を下方にコピペのほうが勝るんじゃないかなあと（試していませんが）思っています。ちなみにindex&matchはarrayformulaと組み合わせ不可です。

Comments

[stcamp]

trueさま

回答ありがとうございます。

ただ、補足漏れで求めているものとは異なりますが、QUERYの使用は考えていませんでしたので今後の参考になります。

> 個人的にVLOOKUPはパフォーマンスが微妙だと思っていて、下方にコピペのほうが勝るんじゃないかなあと

同感です。
ただこちらも本環境では数千行へのコピペになりますので、本来の目的である「まとめる」から外れてしまいます。