Описание ограничений для работы с массивами в Excel

Сводка

В версиях Microsoft Excel, перечисленных в разделе "Применимо к", в разделе "Спецификации вычислений" перечислены ограничения для работы с массивом. В этой статье описаны ограничения массивов в Excel.

Дополнительные сведения

В Excel массивы на листах ограничены доступной памятью случайного доступа, общим числом формул массива и правилом "весь столбец".

доступная память;

Версии Excel, перечисленные в разделе "Применимо к", не накладывают ограничения на размер массивов листов. Вместо этого вы ограничены только доступной памятью на компьютере. Таким образом, можно создавать очень большие массивы, содержащие сотни тысяч ячеек.

Правило "весь столбец"

Хотя в Excel можно создавать очень большие массивы, нельзя создать массив, использующий целый столбец или несколько столбцов ячеек. Так как пересчет формулы массива, использующего целый столбец ячеек, занимает много времени, Excel не позволяет создать этот тип массива в формуле.

Максимальное число формул массива

В Excel 2003 и более ранних версиях Excel один лист может содержать не более 65 472 формул массива, ссылающихся на другой лист. Если вы хотите использовать больше формул, разделите данные на несколько листов, чтобы на один лист было меньше 65 472 ссылок.

Например, в листе Лист1 книги можно создать следующие элементы:

• 65 472 формулы массива, ссылающиеся на Лист 2
• 65 472 формулы массива, ссылающиеся на Лист 3
• 65 472 формулы массива, ссылающиеся на Лист 4

При попытке создать более 65 472 формул массива, ссылающихся на определенный лист, формулы массива, которые вы вводите после номера формулы массива 65 472, могут исчезнуть при их вводе.

Примеры формул массива

Ниже приведен список примеров формул массива. Чтобы использовать эти примеры, создайте новую книгу, а затем введите каждую формулу в виде формулы массива. Для этого введите формулу в строке формул, а затем нажмите клавиши CTRL+SHIFT+ВВОД, чтобы ввести формулу.

Excel 2007

• A1: =SUM(IF(B1:B1048576=0;1;0))

  Формула в ячейке A1 возвращает результат 1048576. Это правильный результат.

• A2: =SUM(IF(B:B=0,1,0))

  Формула в ячейке A2 возвращает результирующий 1048576. Это правильный результат.

• A3: =SUM(IF(B1:J1048576=0,1,0))

  Формула в ячейке A3 возвращает результат 9437184. Это правильный результат.

  Примечание.

  Вычисление результата формулы может занять много времени, так как формула проверяет более 1 миллиона ячеек.

• A4: =SUM(IF(B:J=0;1;0))

  Формула в ячейке A4 возвращает результирующий 9437184. Это правильный результат.

  Примечание.

  Вычисление результата формулы может занять много времени, так как формула проверяет более 1 миллиона ячеек.

• A5: =SUM(IF(B1:DD1048576=0,1,0))

  При вводе этой формулы в ячейку A5 может появилось одно из следующих сообщений об ошибке:

  При попытке вычислить одну или несколько формул в Excel иссякло ресурсов. В результате эти формулы не могут быть оценены.

  Чтобы определить уникальный номер, связанный с получаемым сообщением, нажмите клавиши CTRL+SHIFT+I. В правом нижнем углу этого сообщения отобразится следующее число:

  101758

  В этом случае размер массива листа слишком велик для доступной памяти. Поэтому формула не может быть вычислена.

  Кроме того, Excel может перестать отвечать на несколько минут. Это связано с тем, что другие введенные формулы должны пересчитывать свои результаты.

  После пересчета результатов Excel отвечает должным образом. Формула в ячейке A5 возвращает значение 0 (ноль).

Excel 2003 и более ранние версии Excel

• A1: =SUM(IF(B1:B65535=0,1,0))

  Формула в ячейке A1 возвращает результат 65535. Это правильный результат.

• A2: =SUM(IF(B:B=0,1,0))

  Формула в ячейке A2 возвращает #NUM! ошибка, так как формула массива ссылается на целый столбец ячеек.

• A3: =SUM(IF(B1:J65535=0;1;0))

  Формула в ячейке A3 возвращает результат 589815. Это правильный результат.

  Примечание.

  Вычисление результата формулой может занять много времени, так как формула проверяет почти 600 000 ячеек.

• A4: =SUM(IF(B:J=0;1;0))

  Как и формула в ячейке A2, формула в ячейке A4 возвращает #NUM! ошибка, так как формула массива ссылается на целый столбец ячеек.

• A5: =SUM(IF(B1:DD65535=0,1,0))

  При вводе формулы в ячейку A5 может появилось одно из следующих сообщений об ошибке:

  Недостаточно памяти. Продолжить без отмены?

  Недостаточно памяти.

  В этом случае размер массива листа слишком велик для доступной памяти. Поэтому формула не может быть вычислена.

  Кроме того, Excel может перестать отвечать на несколько минут. Это связано с тем, что другие введенные формулы должны пересчитывать свои результаты.

  После пересчета результатов Excel отвечает должным образом. Формула в ячейке A5 возвращает значение 0 (ноль).

Обратите внимание, что ни одна из этих формул не работает в более ранних версиях Excel. Это связано с тем, что массивы листов, созданные формулами, превышают максимальные ограничения в более ранних версиях Excel. Ниже приведен список некоторых функций Excel, использующих массивы.

• LINEST()
• MDETERM()
• MINVERSE()
• MMULT()
• СУММ(ЕСЛИ())
• SUMPRODUCT()
• TRANSPOSE()
• TREND()