スプレッドシートのARRAYFORMULA関数の循環エラーを解決したい

Question

前提・実現したいこと

Googleスプレッドシートを利用しています。

元の数式が
=if(C2<'設定'!$A$1,D3+1,0)
となっております。

これをARRAYFORMULA関数にしたいのですが、
=ARRAYFORMULA(if(C2:C200001<'設定'!$A$1,D3:D200000+1,0))
このような式にすると

循環依存が検出されました。
反復計算を解消するには、[ファイル] > [スプレッドシートの設定] をご確認ください。

というエラーが出てしまいます。

試したこと

スプレッドシートの設定から反復計算をオンにしましたが、
最大反復回数やしきい値をどのように設定したらいいのかが分からず、
循環エラーが解決できません。

知りたいこと

=ARRAYFORMULA(if(C2:C200001<'設定'!$A$1,D3:D200000+1,0))
この数式を改善すれば循環エラーが解決するのか、
それとも反復計算の設定を適正なものにすれば解決するのかが、
調べても試してもわかりません。

お知恵をお貸しいただければ幸いです。
よろしくお願いいたします。

Answer 1

=ARRAYFORMULA(if(C2:C200000<'設定'!$A$1,D3:D200001+1,0))

セルの最終行がD列のほうが1つ多くなるはずかと思います。
また実際のその行にどの数値が入っているか、あるいは行数を減らして確認してみてください。

循環参照のエラーは、登場する列がどこかで参照しあってるのではないですかね。
これも行数を減らしてエラーが発生しないのを確認してから徐々に行数を拡大して特定してください。

Comments

[kikuchi33]

ご回答ありがとうございます。
ご指摘の通りD列のほうが1つ多くなるはずですね。

行数を減らしてテストをしております。

`=ARRAYFORMULA(if(C2:C30<'設定'!$A$1,D3:D31+1,0))`
こちらで試しましたが未だ同様のエラーが出ております。

C列は値のみが入力された列なので数式などは含まれておりません。

また、こちらの説明が不足しておりました。
大変申し訳ございません。

今回の数式はD2セルに入力しております。

そのためD列同士で循環されてしまっていることが考えられます。

この場合はARRAYFORMULAは使えないのでしょうか。

行数が何分多く、他列でテストしたところ
同じ数式を数万行入れるよりもARRAYFORMULAを使ったほうが処理が早かったので
ARRAYFORMULAに変えたいと考えております。

引き続きよろしくお願いいたします。

[gas.engine]

D列以外であれば正常に動くと思いますよ。
ArraformulaはD2以下のセルでも同じ計算をしてそのセルに表示するので、内部のD3:D31と干渉していると思われます。

[kikuchi33]

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

=ARRAYFORMULA(if(C2:C30<'設定'!$A$1,D3:D31+1,0))

この数式をD列で利用する方法があれば知りたいと思い、
書き込みさせていただいております。

ご存じの方がいらっしゃいましたら
お知恵をお貸しいただければ幸いです。

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

[sawa]

>この数式をD列で利用する方法
ありません。できたとしても循環参照は再計算がかなり増えるので、スピードアップには繋がりません。

たぶん解決済みの1つ前の質問の続きだと思いますが、Arrayformulaを諦めるか、Arrayformulaを使うことを前提として、そもそもの式を自分の列を参照しない別のものにするしかないです。

[kikuchi33]

お忙しいところご回答いただき誠にありがとうございます。

仕組みとしてできないのですね。ありがとうございます。

おっしゃるとおり1つ前の質問の続きとなります。

技術的に不可ということが分かったので諦めます。

いつもお知恵をお貸しいただき、ありがとうございます。