Excel формула массива не работает

Обновлено: 07.07.2024

Office 365 ProPlus переименован в Майкрософт 365 корпоративные приложения. Для получения дополнительной информации об этом изменении прочитайте этот блог.

Сводка

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

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

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

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

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

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

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

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

Формулы максимального массива

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

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

65 472 формул массива, которые относятся к Sheet2
65 472 формул массива, которые относятся к Sheet3
65 472 формул массива, которые относятся к Sheet4

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

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

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

Excel 2007

A1: =SUM (IF(B1:B1048576=0,1,0))

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

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

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

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

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

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

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

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

101758

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

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

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

Excel 2003 г. и более ранних версий Excel

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

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

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

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

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

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

Не хватает памяти.

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

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

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

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

Виды массивов функций Excel

Массив – данные, объединенные в группу. В данном случае группой является массив функций в Excel. Любую таблицу, которую мы составим и заполним в Excel, можно назвать массивом. Пример:

В зависимости от расположения элементов различают массивы:

одномерные (данные находятся в ОДНОЙ строке или в ОДНОМ столбце);
двумерные (НЕСКОЛЬКО строк и столбцов, матрица).

Одномерные массивы бывают:

горизонтальными (данные – в строке);
вертикальными (данные – в столбце).

Примечание. Двумерные массивы Excel могут занимать сразу несколько листов (это сотни и тысячи данных).

Формула массива – позволяет обработать данные из этого массива. Она может возвращать одно значение либо давать в результате массив (набор) значений.

С помощью формул массива реально:

подсчитать количество знаков в определенном диапазоне;
суммировать только те числа, которые соответствуют заданному условию;
суммировать все n-ные значения в определенном диапазоне.

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

Синтаксис формулы массива

Используем формулу массива с диапазоном ячеек и с отдельной ячейкой. В первом случае найдем промежуточные итоги для столбца «К оплате». Во втором – итоговую сумму коммунальных платежей.

Формула после нажатия Ctrl + Shift + Enter оказалась в фигурных скобках. Она подставилась автоматически в каждую ячейку выделенного диапазона.

Если попытаться изменить данные в какой-либо ячейке столбца «К оплате» - ничего не выйдет. Формула в массиве защищает значения диапазона от изменений. На экране появляется соответствующая запись:

Рассмотрим другие примеры использования функций массива Excel – рассчитаем итоговую сумму коммунальных платежей с помощью одной формулы.

Выделяем ячейку Е9 (напротив «Итого»).
Вводим формулу вида: =СУММ(C3:C8*D3:D8).
Нажимаем сочетание клавиш: Ctrl + Shift + Enter. Результат:

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

Аргументы для функции – одномерные массивы. Формула просматривает каждый из них по отдельности, совершает заданные пользователем операции и генерирует единый результат.

Рассмотрим ее синтаксис:

Функции работы с массивами Excel

Предположим, в следующем месяце планируется увеличение коммунальных платежей на 10%. Если мы введем обычную формулу для итога =СУММ((C3:C8*D3:D8)+10%), то вряд ли получим ожидаемый результат. Нам нужно, чтобы каждый аргумент увеличился на 10%. Чтобы программа поняла это, мы используем функцию как массив.

Посмотрим, как работает оператор «И» в функции массива . Нам нужно узнать, сколько мы платим за воду, горячую и холодную. Функция: . Итого – 346 руб.
Функция «Сортировки» в формуле массива. Отсортируем суммы к оплате в порядке возрастания. Для списка отсортированных данных создадим диапазон. Выделим его. В строке формул вводим . Жмем сочетание Ctrl + Shift + Enter.
Транспонированная матрица. Специальная функция Excel для работы с двумерными массивами. Функция «ТРАНСП» возвращает сразу несколько значений. Преобразует горизонтальную матрицу в вертикальную и наоборот. Выделяем диапазон ячеек, где количество строк = числу столбцов в таблице с исходными данными. А количество столбцов = числу строк в исходном массиве. Вводим формулу: . Получается «перевернутый» массив данных.
Поиск среднего значения без учета нулей. Если мы воспользуемся стандартной функцией «СРЗНАЧ», то получим в результате «0». И это будет правильно. Поэтому вставляем в формулу дополнительное условие: 0;A1:A8))' >. Получаем:

Распространенная ошибка при работе с массивами функций – НЕ нажатие кодового сочетания «Ctrl + Shift + Enter» (никогда не забывайте эту комбинацию клавиш). Это самое главное, что нужно запомнить при обработке больших объемов информации. Правильно введенная функция выполняет сложнейшие задачи.

Проблема: Нет соответствий

Если вы считаете, что данные есть в электронных таблицах, но поиск поиску по поиску не удается найти, это может быть по причине:

Ячейка содержит непредвиденные символы или скрытые пробелы.

К ячейке применен неправильный формат данных. Например, ячейка содержит числовое значение, но отформатирована как текстовая.

РЕШЕНИЕ.Чтобы удалить непредвиденные символы или скрытые пробелы, используйте функции CLEAN и TRIM соответственно. Кроме того, убедитесь, что ячейки отформатированы как правильные типы данных.

Вы использовали формулу массива, но не нажали клавиши CTRL+SHIFT+ВВОД

При использовании массива в функции ИНДЕКС,НАЙТИВ ИЛИ сочетании этих двух функций необходимо нажать клавиши CTRL+SHIFT+ВВОД. Excel автоматически заключит формулу в фигурные скобки <>. Если вы попытаетесь ввести квадратные скобки самостоятельно, Excel отобразит формулу как текст.

Примечание: Если у вас есть текущая версия Microsoft 365 ,можно просто ввести формулу в выходную ячейку, а затем нажать ввод, чтобы подтвердить формулу как формулу динамического массива. В противном случае формула должна быть введена как формула массива устаревшей: сначала выберем диапазон вывода, введите формулу в ячейку вывода, а затем нажимая CTRL+SHIFT+ВВОД, чтобы подтвердить ее. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.

Проблема: Несоответствие типа сопоставления и порядка сортировки данных

Если match_type 1 или не указан, значения в lookup_array должны быть в порядке возрастания. Примеры: -2, -1, 0, 1, 2…; А, Б, В…; ЛОЖЬ, ИСТИНА и т. д.

Если match_type -1, значения в lookup_array должны быть упорядочены по убытию.

В следующем примере функция MATCH имеет следующий

=ПОИСКПОЗ(40;B2:B10;-1)

Решение: Измените match_type на 1 или отсортирование таблицы в формате "нисходящее". Затем попробуйте еще раз.

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

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

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

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

Не знаю, новичок вы в Excel или уже опытный пользователь, но коль судьба привела вас на эту страницу, в ходе прочтения вы:

узнаете все о формулах массива;
увидите множество примеров таких формул;
сможете начать активно их применять на практике.

Что такое формула массива

Формула массива (array formula) — это такая формула, в процессе вычисления которой создается один или несколько массивов. При этом не обязательно как результат вычисления, но и как промежуточный этап.

Будьте на 100% уверены: если формула

принимает массив на вход,
создает его как промежуточную сущность
или выводит как результат вычислений

— это формула массива.

Слишком очевидно и все еще непонятно? Не беда — читаем далее.

На деле, прежде, чем говорить о формулах массива, нужно упомянуть сами массивы и их отличие от диапазонов. Т.к. многие не до конца понимают разницу.

Массивы в Excel

Массив (array) — это фиксированный набор элементов с фиксированным порядком. Иными словами, все элементы массива, включая их положение в нем, являются его неотъемлемой частью, их порядок и количество нельзя изменить.

Отличие массивов от диапазонов в Excel

В отличие от массива, диапазон (range) — это просто метод адресации к ячейкам в Excel, никак не связанный с самим их содержимым.

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

Содержимое диапазона также можно свободно сортировать, если в нем нет объединенных ячеек.

Если же на лист Excel выведен массив, ни сортировка строк, ни вставка строк и столбцов в середину диапазона, в который он выведен, не возможна — это нарушило бы само его определение.

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

Ошибка Excel при попытке нарушить целостность массива

Размерность массивов

Как массивы, так и диапазоны в Excel могут быть одномерными (вектор), двумерными (таблица) и трехмерными (многослойная таблица).

Одномерные массивы (векторы) в свою очередь могут быть вертикальными и горизонтальными.

Одномерные и двумерные массивы в Excel

Трехмерные массивы — довольно редко используемая на практике сущность.

Виды массивов

По типам элементов в них:
По типу данных —

числовой массив (массив чисел)
строковый массив (текстовые значения)
булевый массив (значения ИСТИНА-ЛОЖЬ)
смешанного типа

По изменяемости элементов —

Методы создания массивов

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

Массив констант, созданный вручную

Одномерные и двумерные массивы констант можно создавать вручную и сразу выводить на лист безо всяких дополнительных операций над ними.

При этом разделителем между элементами слева направо является точка с запятой, а разделителем строк — двоеточие.

Двумерные массивы вводятся построчно, т.е. сначала первая строка через точку с запятой, далее двоеточие, вторая строка через точку с запятой, и так далее.

Именованный массив констант

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

Массив цифр как в примере ниже, поможет быстро найти цифры в текстовых ячейках.

Это позволит больше не вводить его вручную, а обращаться к нему по его имени. Более того, Microsoft Excel помогает при их вводе всплывающими подсказками.

Используем именованный массив

С помощью функций

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

Единственное условие для создания массива с помощью функции — функция не должна обладать агрегирующим свойством. Например, СУММ, СРЗНАЧ не создадут массив при обращении к диапазону.

На этом сайте есть статья — как дописать символ, слово или текст к нескольким ячейкам. Там рассматриваются несколько способов, в том числе без создания дополнительного столбца. Но вот еще один — с помощью функции СЦЕПИТЬ и формулы массива:

Формула массива с функциями СЦЕПИТЬ и СИМВОЛ

Кавычки-ёлочки в этой формуле нам помогает создать функция СИМВОЛ.

С помощью математических операторов

Достаточно применить любую из математических операций к диапазону — и будет создан массив, аналогичный по размерам.

Массивы в Excel, полученные с помощью математических операторов

С помощью операторов сравнения

Аналогично математическим операторам, массивы создаются при сравнении диапазонов с константой или значением ячейки. Результатом операции сравнения являются значения ИСТИНА или ЛОЖЬ.

Массивы в Excel, полученные с помощью операторов сравнения

С помощью других массивов

В двух предыдущих примерах массивы создавались на основе взаимодействия диапазона ячеек и некой константы.

Но можно создать массив и по принципу наоборот — на основе одной ячейки, произведя ее взаимодействие (операторами или функциями) с массивом констант.

Размер результирующего массива в таком случае будет аналогичен размеру массива констант.

Еще раз обратите внимание на разделители строк и столбцов в массивах. Столбцы разделяются точкой с запятой, строки — двоеточием.

С помощью комбинаций указанных методов

Как вы уже догадались, возможности фантазии безграничны — можно строить какие угодно комбинации перечисленных выше методов, производя операции над массивами и диапазонами.

Вывод массива на лист

Для корректного вывода массива на лист нужно выделить диапазон ячеек эквивалентной размерности и размера, использовать метод создания массива (вручную или формулой), и нажать

На примере результирующий массив должен быть размером 5*5, но перед вводом формулы массива был выделен диапазон 6*6.

Ошибка НД при несоответствии размера диапазона и массива

Взаимодействие функций Excel с массивами

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

Однако, есть ряд функций, называемых агрегирующими. Если им на вход подается диапазон или массив, они возвращают единственное результирующее значение. Самая популярная — функция СУММ. Но есть и множество других.

Из раздела математических функций к таковым также относятся ПРОИЗВЕД, СУММПРОИЗВ, СУММЕСЛИ, СУММЕСЛИМН.

Практически все статистические функции по природе берут на вход диапазон или массив и возвращают одно число. Наиболее популярные из них — СРЗНАЧ, МИН, МАКС, СЧЁТ, СЧЁТЗ, СЧЁТЕСЛИ.

Среди логических функций агрегирующим свойством обладают функция И и функция ИЛИ.

Ну и особняком можно выделить функции поиска. Их нельзя в полной мере назвать агрегирующими, но их взаимодействие с массивами весьма похоже. Ведь они тоже:

учитывают весь массив
выводят одно значение

ВЫБОР, ВПР, ГПР, ИНДЕКС, ПОИСКПОЗ — эти функции поиска используются наиболее часто.

Формулы массива — примеры формул

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

Читайте также: