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

Статья
04/13/2023
Применяется к:

Excel 2007, Excel 2003, Excel 2002

Аннотация

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

Дополнительная информация

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

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

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

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

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

Примечание.

В microsoft Office Excel 2003 и более ранних версиях Excel в столбце содержится 65 536 ячеек. В microsoft Office Excel 2007 в столбце содержится 1 048 576 ячеек.

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

В 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: =СУММ(ЕСЛИ(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: =СУММ(ЕСЛИ(B1:B65535=0,1,0))

Формула в ячейке A1 возвращает результат 65535. Этот результат правильный.
A2: =SUM(IF(B:B=0,1,0))

Формула в ячейке A2 возвращает #NUM! Ошибка , так как формула массива ссылается на весь столбец ячеек.
A3: =СУММ(ЕСЛИ(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()
SUM(IF())
SUMPRODUCT()
TRANSPOSE()
TREND()