Arrayformula関数について

質問

Googleスプレッドシートで、Arrayformula関数とsumifs関数を用いて計算をしたいのですが、エラーになってしまってうまくいきません。どなたか教えていただけると助かります。

　　　　(1)表　　　　　　　　　　　　　　(2)表
　Ａ　　　Ｂ　　　 Ｃ　　　　　　　Ａ　　　Ｂ　　　Ｃ
１社名　商品　　　売上　　　　　１社名　　商品　　売上
２Ａ社　りんご　　100　　　　　２Ａ社　りんご　　100
３Ｂ社　りんご　　100　　　　　３Ａ社　バナナ　　 70
４Ａ社　バナナ　　 70　　　⇒　 ４Ａ社　メロン 　　 0
５Ｃ社　メロン　　200　　　　　５　　　　計　　　170
６Ｃ社　バナナ　　 70　　　　　 ６Ｂ社　りんご　　300
７Ａ社　りんご　　200 　　　　 ７Ｂ社　 バナナ　 　 0
　　　　　　　　　　　　　　　 ８Ｂ社 メロン　 　 0
　　　　　　　　　　　　　　　 ９　　　 計 　 　 300
　　　　　　　　　　　　　　　 10Ｃ社 りんご　 　　0
　　　　　　　　　　　　　　　 11Ｃ社　バナナ　　 70
　　　　　　　　　　　　　　　 12Ｃ社　メロン　　200
　　　　　　　　　　　　　　　 13　　　 計　　　 270

(1)表のデータから(2)表を作るのですが、社名と商品の組合せでsumifs関数で(2)表の条件を絞り込み、売上額を集計しています。そしてさらに各社ごとに小計を求め、それぞれの会社ごとの集計を入れています。
それまでは、(2)表のＡ列・Ｂ列は固定で、Ｃ列の売上については
=SUMIFS('(1)表'!$C$2:$C$7,'(1)表'!$A$2:$A$7,$A2,'(1)表'!$B$2:$B$7,$B2)とし、
小計の欄(C5)には
=SUM(C2:C4)と入れて、それを最下段までコピーしておりました。

今それだとレスポンスが非常に遅くなるために、ARRAYFORMULA関数を使って、時間削減を図りたく考えているのですが、難点が二つあり、ARRAYFORMULAとSUMIFSの組合せがうまくいかないのと、途中の小計がどうしても「循環参照しています」となってしまうことです。
色々調べておるのですが、どうしてもうまくいかず、お手上げ状態です。
どなたか詳しい方いらっしゃったら教えていただきたいです。

よろしくお願いします。

回答No.1

imogasi · ベストアンサー

グ―グルのスプレッドシートを、よく知らないものだが
下記を一読してみてください。
私の心配する点は、会社名＋商品名の組み合わせは、手入力をして作るのでしょうか。
中間に出てくる(質問では、「途中の小計」と言っている行）会社別合計の行も手作業で設けるのですか。そして、そのセルの式も（式複写でなく）
個別に手で入れるのですか。
それなら、あとは関数式だけの問題になるが。
ーー
とりあえずエクセルの話での説明になるが、
下記のデータがあるとします
Ａ１：Ｃ１３
社名 商品 売上
Ａ社 バナナ 70
Ａ社 メロン 200
Ａ社 りんご 100
Ａ社 りんご 200
Ｂ社 バナナ 100
Ｂ社 メロン 101
Ｂ社 りんご 100
Ｃ社 バナナ 70
Ｃ社 メロン 200
Ｃ社 りんご 200
Ｃ社 バナナ 100
Ｂ社 メロン 200
ーー
これを所望の
Ａ社 バナナ 70
Ａ社 メロン 200
Ａ社 りんご 300
Ｂ社 バナナ 100
Ｂ社 メロン 301
Ｂ社 りんご 100
Ｃ社 バナナ 170
Ｃ社 メロン 200
Ｃ社 りんご 200
という表を作るとします。
これでも相当むつかしいです。
ーーー
まして、たとえばＡ社の商品の明細の最終の次行に
社別合計を挟むのは式の複写を利用するスプレッド、シートでの関数ではむつかしいと思いますが、グーグルでは、どうしてますか。
社名　　商品　　売上
Ａ社　りんご　　100　　　
Ａ社　バナナ　　 70　
Ａ社　メロン 　　 0　　　　　　　
計　　　　　　　170
各社の社名１つまたは毎行や商品名は文字列で、
シートの適当なところに、手入力するのでしょうか？
データ数が多く商品、会社のバラエティが多いと、
手数が、大変ですが、どうしてますか。
ーー
私のエクセルでの、方法の場合
社名 商品 売上 結合文字列 結合文字列
Ａ社 バナナ 70 Ａ社 バナナ Ａ社 バナナ 70
A社 メロン 200 Ａ社 メロン Ａ社 メロン 200
Ａ社 りんご 100 Ａ社 りんご Ａ社 りんご 300
Ａ社 りんご 200 Ａ社 りんご Ｂ社 バナナ 100
Ｂ社 バナナ 100 Ｂ社 バナナ Ｂ社 メロン 301
Ｂ社 メロン 101 Ｂ社 メロン Ｂ社 りんご 100
Ｂ社 りんご 100 Ｂ社 りんご Ｃ社 バナナ 170
Ｃ社 バナナ 70 Ｃ社 バナナ Ｃ社 メロン 200
Ｃ社 メロン 200 Ｃ社 メロン Ｃ社 りんご 200
Ｃ社 りんご 200 Ｃ社 りんご
Ｃ社 バナナ 100 Ｃ社 バナナ
Ｂ社 メロン 200 Ｂ社 メロン
Ｄ列の結合文字列は　式=A2&" "&B2
Ｅ，Ｆ列はＤ列を「区切り位置」で「スペースをもとに２列に、社名と商品名に分割した。
そしてＧ列は関数式＝ＳＵＭＩＦＳ（）を入れて、Ｇ２セルは
=SUMIFS(C$2:C$13,$A$2:$A$13,E2,B$2:B$13,F2)
このサンプルでは、データ行数が少ないので、下方向に式を複写した。
会社別の合計は出してない。
（Ａ）データ行数が多いと、シート上で、式の複写の手数も煩わしい。
これを解決してくれるのが、ＧｏｏｇｌｅのＡＲＲＹＦＯＲＭＵＬＡ関数
と思うが、どうだろう。
（Ｂ）それとエクセルにもある、配列数式の機能も併せて、ＡＲＲＹＦＯＲＭＵＬＡ関数では、実現できる、優れたものと思う。
ーー
私の方法では、関数のほかに、会社名、商品名の組を出すために、
（１）文字列結合
（２）重複のない、会社名＋商品名の組み合わせを出すために、
フィルターの「重複するレコードは無視する」を使う。
（３）「区切り位置」の
エクセルの操作で、社名と商品名を別列に分離、している。
（４）同じ社名分を、まとめるために、並べ替えを使っている。
グーグルのスプレッドにおいて、（４）以外のこういう機能はあるでしょうか？
もし無ければ、関数だけでそれを実現するのは複雑になるでしょう。
どうしていますか。

お礼

お返事ありがとうございます。
「社名」と「商品名」は固定の集計表なので、表として固定のため手打ちで作っています。
なので月度の売上の状況如何に関わらず同じ種類で固定しています。質問の(1)表は月によって違いますが、(2)表のフォーマットは売上の額以外は、社名も会社数も商品名も商品種類の数もずっと同じです。質問のサンプルだと、全部で13行だけですが、この通りです。また、会社と会社の区切りには「会社計（小計）」が必要です。元々エクセルで作っていた表で、質問の欄にあるような式で作っていました。

=SUMIFS('(1)表'!$C$2:$C$7,'(1)表'!$A$2:$A$7,$A2,'(1)表'!$B$2:$B$7,$B2)
=SUMIFS('(1)表'!$C$2:$C$7,'(1)表'!$A$2:$A$7,$A3,'(1)表'!$B$2:$B$7,$B3)
=SUMIFS('(1)表'!$C$2:$C$7,'(1)表'!$A$2:$A$7,$A4,'(1)表'!$B$2:$B$7,$B4)
=SUM(C2:C4)

の4行をセットで、C5以下のセルに複写していました。
なので以下のような感じです。

=SUMIFS('(1)表'!$C$2:$C$7,'(1)表'!$A$2:$A$7,$A5,'(1)表'!$B$2:$B$7,$B5)
=SUMIFS('(1)表'!$C$2:$C$7,'(1)表'!$A$2:$A$7,$A6,'(1)表'!$B$2:$B$7,$B6)
=SUMIFS('(1)表'!$C$2:$C$7,'(1)表'!$A$2:$A$7,$A7,'(1)表'!$B$2:$B$7,$B7)
=SUM(C5:C7)

という感じで、最後の行は
=SUM(C10:C13)
です。

これらの処理をGoogleスプレッドシートで配列を使って処理し高速化したいのですが、うまくいかなくて苦心中といったところです、、