Excel формула массива не работает
Добрый день. В приложении файлик, который был создан при непосредственной помощи форумчан. Но, что-то не так там сейчас, точнее - при добавлении строк в таблице А56:F57 не подсчитывается площадь в ячейке B52, т.е. не учитывает данные в новых добавленных строках. Что там не так, подскажите?
Добрый день. В приложении файлик, который был создан при непосредственной помощи форумчан. Но, что-то не так там сейчас, точнее - при добавлении строк в таблице А56:F57 не подсчитывается площадь в ячейке B52, т.е. не учитывает данные в новых добавленных строках. Что там не так, подскажите? Rusel
Но в формуле в ячейке В53 диапазоны формулы увеличиваются автоматически при добавлении новых строк в таблице, а в ячейке В52 это не происходит. Почему?
Но в формуле в ячейке В53 диапазоны формулы увеличиваются автоматически при добавлении новых строк в таблице, а в ячейке В52 это не происходит. Почему? Rusel
Как добавляете данные?
Если Вы протягиваете таблицу за маркер автозаполнения или вносите данные в строку под ней - то формулы (ОБЕ!) будут самостоятельно увеличивать диапазон. Если Вы просто изменяете размер области печати (тяните за синенькую рамочку), то естественно, формулы автоматически меняться не будут. ОБЕ.
Как добавляете данные?
Если Вы протягиваете таблицу за маркер автозаполнения или вносите данные в строку под ней - то формулы (ОБЕ!) будут самостоятельно увеличивать диапазон. Если Вы просто изменяете размер области печати (тяните за синенькую рамочку), то естественно, формулы автоматически меняться не будут. ОБЕ. Serge_007
Serge_007, строки в таблице добавлял путем внесения новых данных в строках ниже заполненной, а не протягиванием диапазона печати. И в этом случае иногда диапазон в формуле площади увеличивается иногда нет. Не понял почему такое происходит. Но воспользовался советом Borodы, думаю проблема решена. Спасибо всем, кто откликнулся.
Serge_007, строки в таблице добавлял путем внесения новых данных в строках ниже заполненной, а не протягиванием диапазона печати. И в этом случае иногда диапазон в формуле площади увеличивается иногда нет. Не понял почему такое происходит. Но воспользовался советом Borodы, думаю проблема решена. Спасибо всем, кто откликнулся. Rusel
Как узнать, является ли данная формула формулой массива? Что вообще она означает?
На этапе создания формула (или также функция) сама по себе не является ни формулой массива, ни обычной формулой. Это вы определяете, как Excel должен истолковать формулу, которую вы вводите. То, что формула является формулой массива – это не столько особенность самой формулы, а скорее способом, которым программа Excel введенную формулу "обрабатывает". Подтверждение формулы с помощью сочетания клавиш "Ctrl + Shitf + Enter" – это является для Excelя командой на выполнение (обработку данных) как массив вычислений. Тогда он используется в качестве аргумента функции и возвращает в качестве результата вычислений таблицу (массив данных).
Примеры формул массива и отличие от обычных формул в Excel
Некоторые функции Excelя по умолчанию в качестве аргумента принимают диапазон ячеек (массив) и в результате возвращают одно значение. Отличными примерами являются функции СУММ, СЧЕТЕСЛИ, СРЗНАЧ и т.д. Для этих функций не имеет никакого значения, вводите ли вы их как функции массива или нет. Они и так обрабатывают таблицы, и найдут выход (сработают правильно) из любой ситуации. Вот такие маленькие Excel-евские приспособленцы.
К счастью, существуют другие функции, которые работают совершенно иначе, т.е. в зависимости от вашего решения относительно их принадлежности к "функциям массива" (иногда они вообще не хотят работать). Прекрасным примером является функция ЕСЛИ.
Когда формула является формулой массива, а когда обычной?
Для начала определимся как выглядит обычный массив значений в Excel. Это значения, которые находятся внутри фигурных скобок и разделены между собой точкой с запятой. Например:
– это синтаксис массива значений в Excel. Он может быть использован в аргументах функций.
Диапазон ячеек A1:A4 – так же является массивом значений в Excel. Естественно так же используется в аргументах функций. Например сравним результаты вычислений двух формул: =СУММ(A1:A4) и =СУММ() – они идентичны:
Визуально формула массива находится так же внутри фигурных скобок, но они не должны быть введены вручную, а только лишь при помощи комбинации клавиш CTRL+SHIFT+Enter. Если ввести вручную фигурные скобки, то формула не будет выполнятся в массиве – это будет синтаксическая ошибка в Excel.
Формула массива (введенная с помощью сочитания CTRL+SHIFT+Enter) будет использоваться везде, где вы хотите, чтобы функция, которая обычно работает с отдельными значениями (ячейками), внезапно повела себя иначе и приняла в качестве аргумента и вернула в качестве результата массив значений (таблицу). Вернемся к уже упомянутой функции ЕСЛИ. В качестве аргумента она принимает логическое значение ИСТИНА или ЛОЖЬ. В классической форме:
Если значение в ячейке A1 больше нуля, в качестве аргумента функция получит значение ИСТИНА и в качестве результата вернет текстовую строку «больше». Однако, если бы вы хотели проверить несколько ячеек сразу и передать результат такой проверки другой функции, вы должны были бы использовать вышеуказанную формулу как формулу массива. Для этого при вводе нажмем сочитание клавиш CTRL+SHIFT+Enter, а не как обычно (просто Enter):
В качестве аргумента функция принимает целый диапазон $A$1:$A$4. В результате проверки каждой ячейки диапазона в памяти компьютера создается таблица значений в массиве. Схематически таблицу можно отобразить так:
А так выглядят эти значения в массиве:
Например, чтобы прочитать этот массив и получить второе значение (сделать выборку значений) воспользуемся функцией:
Тоже самое что и:
Затем создается другая таблица, значения которой зависят непосредственно от значений в первой таблице. Если элемент в первом массиве имеет значение ИСТИНА, во втором массиве он примет значение «больше». Если он имеет значение ЛОЖЬ, элемент во второй таблице примет значение «меньше». После этой операции первая таблица удаляется из памяти компьютера, и в конечном счете, функция возвращает массив . Схематически вторую таблицу можно отобразить так:
Так же ее можно прочитать функцией:
В примере с функцией ЕСЛИ была введена формула массива только в одну ячейку, поэтому в результате получили только одно значение, соответствующее первому значению в таблице. Однако достаточно ввести формулу массива в диапазон ячеек, чтобы увидеть все значения массива результатов. Для этого выделяем диапазон из нескольких ячеек, нажимаем клавишу F2 (или заново вводим формулу вручную) и жмем CTRL+SHIFT+Enter.
В примере (рисунок ниже) видно, что таблица результатов содержит ровно четыре элемента, о которых я упоминал выше.
Примеры как использовать формулу массива в Excel
Это все хорошо, но возникают некоторые вопросы: «Зачем же нужна формула массива?» или «Как или где использовать формулу в массиве?», «Чем она лучше обычной формулы?».
Разумеется, массив, возвращаемый функцией ЕСЛИ, может передаваться далее на «обработку» в качестве аргумента для другой функции.
Пример. Представим, что вы хотели бы найти сумму ячеек B7:B10, но только тех, которые имеют значение больше нуля. Конечно же, вы можете использовать функцию СУММЕСЛИ, однако в нашем примере мы хотим сделать это только с помощью формулы массива. Суммируя значения ячеек нашего диапазона, необходимо будет как-то избавиться от значения "-32". Функции СУММ необходимо передать массив, в котором содержатся только значения больше нуля. Везде там, где значение меньше нуля, мы заменяем его на ноль, что, конечно же, не повлияет на результат. Как вы уже знаете, временную таблицу с соответствующими значениями вы можете получить, используя функцию ЕСЛИ. В конечном итоге соответствующая формула будет выглядеть так:
0;$A$1:$A$4;0))' >
Вводим формулу и не забываем для подтверждения ввода нажать комбинацию клавиш CTRL+SHIFT+Enter. В результате проверки каждой ячейки диапазона $A$1:$A$4 (является ли значение больше нуля) в памяти компьютера создается массив . Затем создается очередная таблица. Если элемент в первом массиве имеет значение ИСТИНА, то во второй таблице будет отображаться значение из соответствующей ячейки. Если он имеет значение ЛОЖЬ, то элемент во второй таблице примет значение 0. После этой операции первая таблица удаляется из памяти компьютера, и в конечном итоге функция ЕСЛИ возвращает массив . Затем эта таблица передается в качестве аргумента функции =СУММ(), которая, согласно своему предназначению, возвращает сумму всех элементов в таблице. В нашем примере сумма равна 45. В завершении, посмотрите, что произойдет, если вы скажете Excelю обработать приведенную выше формулу не как формулу массива.
Как отличать формулу массива от обычной формулы
При нажатии клавиш CTRL+SHIFT+Enter для подтверждения ввода в строке формул будут отображены фигурные скобки по краям. Значит данная формула выполняется в массиве. Но что если еще на этапе создания неизвестно какой тип формул следует применять?
Правильное «распознавание», когда следует нажимать CTRL+SHIFT+Enter, а когда просто Enter полностью зависит от понимания того, как работают массивы в формулах. Когда вы это поймете, сможете сказать, что конкретную формулу следует вводить (подтверждать) сочетанием клавиш – CTRL+SHIFT+Enter.
Конечно же, не подтвержденная, а просто как формула также может возвращать КАКОЙ-ТО результат (в чем вы могли только что убедиться сами). Однако, если вы сможете прочитать формулу и понять механизм, то вы заметите, что такой результат является ОШИБОЧНЫМ. И поэтому для правильной работы формулы вам необходимо ее подтвердить "Ctr+Shift+Enter". Как и все, понимание и использование формул массива требует практики. Тем не менее, стоит некоторое время посвятить тому, чтобы во всем разобраться. Потому что формулы массива позволяют решить многие проблемы, которые на первый взгляд могут казаться неразрешимыми.
Примеры вычислений и анализа формул массива
Каким образом можно просматривать и проверять значения промежуточных результатов расчета, например, содержание массивов, созданных в памяти компьютера и используемых для выполнения последующих действий? Ничего сложного! Пример 1:
Перейдите на ячейку с формулой, а затем в строке формул выделите в первом аргументе функции ссылку на диапазон ячеек:
Нажмите клавишу F9 (или "Пересчет" в правом верхнем углу меню "Формулы"), и вы получите (в строке формулы) значения аргументов, которые используются для вычислений, как показано ниже:
- запись с использованием двоеточий означает, что мы имеем дело с элементами вертикального (столбикового) массива, элементы горизонтального (строкового) разделены стандартным символом - ";" (точкой с запятой).
Пример 2: Снова перейдите на ячейку с формулой массива, но на этот раз выделите первый аргумент функции целиком вместе со знаком сравнения «>» и значением критерия – «0»).
То есть, созданный в памяти компьютера массив:
Пример 3: Выделите ячейку формулой массива где в функцию СУММ вложена функция ЕСЛИ. Затем в строке формул выберите весь аргумент функции СУММ (вместе с функцией ЕСЛИ):
Нажмите клавишу F9 и вы получите массив итоговых результатов вычисления, которые используются для суммирования, как показано ниже:
То есть, созданный в памяти компьютера массив:
Пример 4: Просто перейдите на ячейку с формулой B1 и выберите инструмент «ФОРМУЛЫ»-«Зависимости формул»-«Вычислить формулу»
После чего нажмите на кнопку «Вычислить»:
В результате ссылка на диапазон ячеек в аргументе вложенной функции ЕСЛИ разложилась на массив значений. Снова нажмите на кнопку «Вычислить»:
Мы получили массив значений теперь уже для функции СУММ. Такой же, как и в примере 3.
Часто неопытные пользователи Excel возмущаются, что формула не работает. В конце концов оказалось, как легко догадаться, формулу в массиве вводили как обычную (просто Enter). Речь идет не о недоразумении, которое произошло, а о том факте, что у этих пользователей возникает вопрос: как избегать таких ошибок? Поэтому важно сразу во всем разобраться, чтобы в дальнейшем больше не задавать таких вопросов.
Массив функций 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» (никогда не забывайте эту комбинацию клавиш). Это самое главное, что нужно запомнить при обработке больших объемов информации. Правильно введенная функция выполняет сложнейшие задачи.
Вкратце: в моём Excel 365 (версия 1911, сборка 12228.20332, русская) под Windows 10 нет новой функциональности динамических массивов (dynamic arrays) и связанных с ними функций ФИЛЬТР, СОРТ, СОРТПО, УНИК, ПОСЛЕД и СЛМАССИВ (FILTER, SORT, SORTBY, UNIQUE, SEQUENCE и RANDARRAY). Как сделать, чтобы были?
Подробнее:
Осторожно, здесь описываются увлекательные приключения с техподдержкой Microsoft. Сейчас я специально подробно изложу предыдущие попытки решения проблемы, чтобы сэкономить ваше время. Пожалуйста, не советуйте мне того, что уже не помогло.
У меня Windows 10 Pro и Office 365 Home, всё это куплено за деньги у официальных реселлеров Microsoft, лицензионное, активированное, всё было обновлено до самых последних версий на момент обнаружения проблемы.
10 декабря 2019 мой Office автоматически обновился до сборки 12228.20364. На канале обновлений я прочитал, что ещё с предыдущей сборки 12228.20332 в Excel должны быть доступны функции FILTER, SORT, SORTBY, UNIQUE, SEQUENCE и RANDARRAY (соответственно, по-русски ФИЛЬТР, СОРТ, СОРТПО, УНИК, ПОСЛЕД и СЛМАССИВ). Но я не нашёл у себя в Excel этих функций ни в каком виде. Также я не нашёл никакой обещанной функциональности динамических массивов.
В тот же день на моём iPhone тоже обновились приложения MS Office для iOS, и там все эти возможности благополоучно появились и работают, так что я забеспокоился.
Чтобы разобраться, 11 декабря я обратился в чат поддержки. Сотрудник поддержки предложил мне установить инструмент для удалённого доступа, с помощью которого смог управлять моим компьютером и начудил всякой белиберды.
Мне пришлось выполнить восстановление системы, в результате чего Office откатился до предыдущего состояния. Однако теперь он показывает сборку 12228.20332 и больше не хочет обновляться до более новой сборки 12228.20364; при запуске обновления говорит, что «У вас установлена последняя версия Office». Функции динамических массивов в Excel по-прежнему недоступны, хотя, если верить каналу обновлений, должны быть доступны именно в той сборке 12228.20332, которая у меня сейчас.
Между тем, я пользуюсь Microsoft Office непрерывно с 1990 года и имел массу возможностей убедиться, что этот продукт невозможно начисто снести из системы и переустановить «с чистого листа». Как его ни удалять, всё равно потом всплывут какие-нибудь артефакты, которые будут конфликтовать с новой установленной версией. Сторонние uninstaller’ы чистят гораздо лучше, чем «родные» средства от Microsoft, но всё равно не обеспечивают полного удаления. Поэтому единственный надёжный способ полноценно переустановить Office — это чистая переустановка всей ОС, чего мне хотелось бы избежать.
Итак, как же, не повторяя ничего вышеизложенного, сделать так, чтобы у меня в Excel заработали динамические массивы и связанные с ними новые функции?
UPD 21.12.2019. Я нашёл решение, которое в моём случае сработало (без помощи этого сообщества), решение приводится ниже в комментарии. Жаль, что интерфейс этого сообщества не позволяет пометить свой собственный ответ как решение проблемы, и приходится проставлять ссылки вручную, как на форумах 1990-х годов.
Эта цепочка заблокирована. Вы можете просмотреть вопрос или оставить свой голос, если сведения окажутся полезными, но вы не можете написать ответ в этой цепочке.
Здравствуйте помогите разобраться, в ячейке с формулой массива в (файле помечены красным цветом) не отображаются значения хотя они есть, в чем может быть причина?
Здравствуйте помогите разобраться, в ячейке с формулой массива в (файле помечены красным цветом) не отображаются значения хотя они есть, в чем может быть причина?
Заранее спасибо. Автор - kallinin
Дата добавления - 17.09.2017 в 22:48
В формулах ссылки на другой файл.
Возможно, при вычислениях ИНДЕКС получается ошибка.
проверьте: уберите из формулы ЕСЛИОШИБКА
В формулах ссылки на другой файл.
Возможно, при вычислениях ИНДЕКС получается ошибка.
проверьте: уберите из формулы ЕСЛИОШИБКА vikttur
Наверное, где-то среди этих ячеек закралась ошибка. Пробовали применить формулу без ЕСЛИОШИБКА?
В строке формул стать на формулу, нажать F9. формула заменится результатом вычислений. Что там? Пустая строка ""? Значит, возникает ошибка внутри формулы. Откатить назад, выделить законченный фрагмент формулы, нажать F9 - выделенная часть пересчитается и покажет результат. Если порядок, откатиться (но можно и оставить результат) и продолжить проверку. Так можно определить, какой фрагмент вызывает ошибку.
Дополнение: В строке 8 ссылка на пустую ячейку E8
В строке 32 ищите с помощью F9
Наверное, где-то среди этих ячеек закралась ошибка. Пробовали применить формулу без ЕСЛИОШИБКА?
В строке формул стать на формулу, нажать F9. формула заменится результатом вычислений. Что там? Пустая строка ""? Значит, возникает ошибка внутри формулы. Откатить назад, выделить законченный фрагмент формулы, нажать F9 - выделенная часть пересчитается и покажет результат. Если порядок, откатиться (но можно и оставить результат) и продолжить проверку. Так можно определить, какой фрагмент вызывает ошибку.
Дополнение: В строке 8 ссылка на пустую ячейку E8
В строке 32 ищите с помощью F9 vikttur
В строке формул стать на формулу, нажать F9. формула заменится результатом вычислений. Что там? Пустая строка ""? Значит, возникает ошибка внутри формулы. Откатить назад, выделить законченный фрагмент формулы, нажать F9 - выделенная часть пересчитается и покажет результат. Если порядок, откатиться (но можно и оставить результат) и продолжить проверку. Так можно определить, какой фрагмент вызывает ошибку.
Дополнение: В строке 8 ссылка на пустую ячейку E8
В строке 32 ищите с помощью F9 Автор - vikttur
Дата добавления - 18.09.2017 в 00:24
Читайте также: