1. たくさんのセルで数式を呼び出すと、数式数制限が表示される
Google スプレッドシートで、カスタム関数を多くのセルで呼び出すとエラーが表示される。
これは Google スプレッドシートに制限が設けられているため。できるだけ回避するには、カスタム関数を「配列数式」に変更する必要がある。ただし、新しい Google スプレッドシートでは、この制約が緩和されている。
同じように、演算子・組み込み関数をたくさんのセルで呼び出すと、「数式数制限」の通知がされる。
このスプレッドシートは数式制限に達しました。
Google スプレッドシートのサイズと複雑さの制限 - ドライブ ヘルプ によると、
Google スプレッドシートには複雑さの制限もあります。セルが更新されるたびに、それを参照するセルも再計算されます。数式が複雑になりすぎたり、計算に時間がかかりすぎたりすると、計算中にタイムアウトになります。
スプレッドシートを複雑にする数式には次のものがあります。
- VLOOKUP、QUERY、SUMIF、入力範囲セルが広範なその他の同様の数式。
- NOW、RAND、OFFSET、INDIRECT など、スプレッドシートが変更されるたびに再計算される揮発性関数。揮発性関数を含むセルに依存する数式が多いと、編集するたびに再計算されるため、スプレッドシートの表示速度が低下することがあります。
- IMPORTRANGE などのインポートを使った数式。定期的に計算されるので複雑さが増します。
2. 「数式の繰り返し」を「配列数式」に変更する
* 乗算を繰り返したときの限界
例えば、A 列に入力した値を B 列で 2 倍する計算について考える。
A1 のセルの値を 2 倍するには、B1 セルに
=A1 * 2
と入力する。A2 以降、繰り返し計算を行うには、「下方向へと式をコピー」する。
この場合、19,000 行までは、数式制限の通知はされなかった。
20,000 行では数式制限が通知された。
ARRAYFORMULA で「数式の繰り返し」を「配列数式」へ
上記のような、演算子や組込みの関数を用いた「数式の繰り返し」を、一つのセルに入力しただけで済ませるには、組み込み関数である
- ARRAYFORMULA
を利用する。これにより、「値を返す演算子や組み込み関数」を「配列数式」の形にできる。
ARRAYFORMULA - Drive Help によると、
Enables the display of values returned from an array formula into multiple rows and/or columns and the use of non-array functions with arrays.
上例の「数式の繰り返し」と同じ結果を得るには、B1 セルに、以下のように入力する。
=ARRAYFORMULA(A:A * 2)
一見、ARRAYFORMULA の引数が分かりにくい。 (@_@;
A:A * 2
元の式は、以下の通り。
=A1 * 2
数式の繰り返しを ARRAYFORMULA で記述するには、
[セル] 演算子 値
という形を
[範囲] 演算子 値
にする。つまり、計算対象である単一のセルを「範囲」に変更する。
この式により、「A 列の各値に対して、2 倍する」計算が行われる。
「配列数式」を用いる場合、37,000 行まで、数式制限は表示されなかった。
38,000 行では数式制限が通知された。演算子を繰り返したときと比較すると、約 2 倍の数を計算できた。
カスタム関数を用いたときと同じように、「配列数式」で表現した方が計算の負担が少ないようだ。
3. 「新しい Google スプレッドシート」（2013/12 以降）では「数式の繰り返し」と「配列数式」が計算できる数に差がなさそう？
ただし、カスタム関数のときと同様に、2013/12 以降の「新しい Google スプレッドシート」では、この制約が緩和されている。
数式を繰り返しコピーしても、上記よりはるかにたくさんの計算を行うことができる。また、数式のコピーと ARRAYFORMULA を比較すると、計算できる量に違いがないように見える。
Google スプレッドシートのサイズと複雑さの制限 - ドライブ ヘルプ によると、
新バージョンでは、データが含まれているセルが 2 百万個までサポートされますが、サイズが非常に大きい、または複雑なスプレッドシートは動作が遅くなる可能性があります。
どちらの方法でも 1,000,000 セルまで計算できた。ただし、それ以上は動作が遅くなり、どこまで計算できるのか確認できなかった。
0コメント:
コメントを投稿