Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Формулы массивов, Попытка исчерпывающего описания этого понятия в одном посте
 
#1
01.11.2016 20:35:21
Тема, естественно, обсуждалась и имеется в архиве http://www.planetaexcel.ru/forum/index.php?PAGE_NAME=read&FID=8&TID=10828 . Но поскольку в указанной архивной теме я не увидел собранного воедино самодостаточного объяснения понятия формулы массива в Excel, не могу пройти мимо и рискну возобновить тему, предложив собственную версию объяснения, которая по крайней мере мне делает всё достаточно ясным.

ВНИМАНИЕ! Попытка компактного исчерпывающего объяснения формул массива Excel в очередной раз с треском провалилась пока откладывается. В стартовом посте содержатся НЕВЕРНЫЕ утверждения, см. обсуждение ниже по теме.

Вообще, не вижу смысла ломать копья по вопросу "что такое формула массива?". Вопрос, который нас в конечном счёте будет интересовать на практике -- нужно ли использовать ввод через Ctrl+Shift+Enter в каком-либо конкретном случае.

Касаемо формального ОПРЕДЕЛЕНИЯ формулы массива, не вижу смысла мудрить (согласен в этом с MCH, пост #43 архивной темы). Будем называть формулой массива формулу, введённую с помощью Ctrl+Shift+Enter. В справке от MS отождествляются термины "формула массива" и "CSE-формула", где CSE -- сокращенное обозначение указанной комбинации клавиш. Будем далее придерживаться такого отождествления.

Это определение, однако, не отвечает на вопросы о значении ввода формулы через CSE с точки зрения языка формул Excel (как языка программирования) и о его функциональном значении (что именно делает Excel, когда формула введена таким образом). А на эти вопросы нужно ответить для того, чтобы понять, следует ли использовать ввод через CSE в каком-либо конкретном случае (а именно этого мы в конечном итоге и хотим).

Для ответа на эти вопросы считаю важным разграничить понятия МАССИВА и ССЫЛКИ.
МАССИВ -- это нумерованное множество значений (в нашем случае -- чисел или строк), вообще говоря, не связанных с ячейками таблицы (или связанных).
ССЫЛКА -- это указание на ячейку или диапазон ячеек (или несколько диапазонов, но нам это здесь не нужно).
Заметим, что значением диапазона ячеек является массив.

Всё дальнейшее -- сугубо моё представление, основанное на опыте программирования вообще и составления формул Excel в частности. Я никак не связан с разработчиками Excel.

Итак, по моему представлению, языковое значение формулы массива состоит в том, что в такой формуле (в отличие от обычной) становятся допустимыми две вещи:
(1) массив в качестве результата формулы;
(2) массив (не сводящийся к ссылке!) в качестве промежуточного результата расчёта по формуле.

Функциональное значение ввода формулы через CSE, соответственно, -- (1) разрешение записи результата в диапазон ячеек и (2) разрешение СОЗДАНИЯ МАССИВОВ где-то в памяти (не связанных с ячейками листа!) в процессе расчёта по формуле. Это последнее обстоятельство может служить объяснением расхожему утверждению, что расчёт по формуле массива происходит медленнее, чем по обычной.

Соответственно, ввод формулы через CSE необходим, если (1) формула должна возвращать массив, и/или (2) в качестве промежуточных результатов фигурируют массивы.
Наоборот, CSE-ввод формулы НЕ НУЖЕН, если она не возвращает массив, и при расчёте по формуле используются только одиночные значения ИЛИ ССЫЛКИ НА ДИАПАЗОНЫ (!тут восклицательные знаки!), но не МАССИВЫ, которые нужно создавать в памяти отдельно от листа таблицы.
То есть ключевой вопрос состоит в том, разрешаем ли мы создавать массивы в памяти вне ячеек листа. Если разрешаем, то используем CSE. Если же мы храним все промежуточные массивы в диапазонах ячеек на листе, то отдельные массивы не создаются. В этом случае CSE может понадобиться только для записи КОНЕЧНОГО результата формулы в диапазон (уже безотносительно отдельных массивов в памяти).

Примеры:
Код
=СУММ(A1:A5)

CSE НЕ нужен, т.к. под суммой стоит ссылка на диапазон, и массивы создавать не нужно

Код
=СУММ(A1:A5*B1:B5)

CSE НУЖЕН. Вообще-то можно вычислить эту формулу без промежуточных массивов, но Excel не настолько интеллектуален, чтобы до этого догадаться. Поэтому нужно разрешить ему создать массив.

Код
=СУММПРОИЗВ(A1:A5*B1:B5)

CSE НЕ нужен, т.к. мы сами подсказали Excel-ю, что здесь можно обойтись без создания массивов.

Код
=СУММ(ЕСЛИ(A1:A5>B1:B5;B1:B5))

CSE НУЖЕН для передачи массива от функции ЕСЛИ к функции СУММ.

Код
=СУММ(ИНДЕКС(A1:D26;7;))

CSE НЕ нужен!! Почему? Потому что функция ИНДЕКС здесь возвращает не массив, а ССЫЛКУ НА ДИАПАЗОН! И никаких массивов вне таблицы не создаётся.

Вот как-то так. Предлагаю приводить контрпримеры, НЕ укладывающиеся в описанную картину.
Изменено: Oleg K - 03.11.2016 17:31:08 (добавил предупреждение о наличии неверных утверждений в стартовом посте)
 
 
 
#2
02.11.2016 01:03:13
Есть вопросы.
Цитата
=СУММ(A1:A5*B1:B5).. Вообще-то можно вычислить эту формулу без промежуточных массивов
Как здесь без промежуточных массивов?
A1:A5*B1:B5=A1*B1+A2*B2...

Цитата
=СУММПРОИЗВ(A1:A5*B1:B5) - мы сами подсказали Excel-ю, что здесь можно обойтись без создания массивов.
Да, СУММПРОИЗВ в этом случае не требует "массивного" ввода. Но как подсказали? Чем? Названием функции? Суммируем произведения. Но произведения еще нужно получить. Как? В массиве!
В строке формул выделить часть формулы (здесь выделена жирным) и нажать F9. Массив?

Уточнение
Цитата
...последнее обстоятельство может служить объяснением расхожему утверждению, что расчёт по формуле массива происходит медленнее, чем по обычной.
Часто формулы массива производят больше вычислений. Не потому, что в памяти - так они составляются.
Пример:
=СУММ((A1:A5>0)*A1:A5*B1:B5)
=СУММ(ЕСЛИ(A1:A5>0;A1:A5*B1:B5)
Вторая шустрее. Но не всегда можно сделать такую замену.
 
 
 
#3
02.11.2016 14:03:44
Цитата
vikttur написал: Как здесь без промежуточных массивов?
Суммируем произведения. Но произведения еще нужно получить.
Можно просуммировать "на лету" попарные произведения (последовательно накапливая сумму по мере их вычисления), не создавая массив в памяти. Подозреваю, что СУММПРОИЗВ так и делает.
Цитата
Но как подсказали? Чем? Названием функции?
Я именно это имел в виду.
Цитата
В строке формул выделить часть формулы (здесь выделена жирным) и нажать F9. Массив?
Ну F9 -- я бы сказал, не показатель. F9 не смотрит на всю остальную формулу. Когда же Excel видит формулу целиком, он вполне может интерпретировать аргумент функции СУММПРОИЗВ по особому.
 
 
 
#4
02.11.2016 14:11:22
Похоже, контрпример моим соображениям дают массивы констант.
Следующие формулы НЕ требуют CSE несмотря на то, что без промежуточных массивов тут кмк никак не обойтись
Код
=СУММ({1;2;3})
=СУММ({1;2;3}*{3;3;1})
=СУММ({1;2;3}*A3+{3;3;1})
=СУММ({1;2;3}*{1:2:3})
=СУММ(({1;2;3}*A3+{3;3;1})*{1:2:3})
=СУММ(ИНДЕКС(({1;2;3}*A3+{3;3;1})*{1:2:3};2;))
=СУММ( МУМНОЖ(({1;2;3}*A3+{3;3;1})*{1:2:3}; ({1;2;3}*A4+{3;3;1})*{1:2:3}) )
С другой стороны, следующие примеры ТРЕБУЮТ CSE:
Код
=СУММ(СТОЛБЕЦ($A$6:$R$6))
=СУММ({1:2:3}*A2:A4)
=СУММ(A1:A3*A2)
То есть похоже на то, что "массивные" операции, включая создание массивов в памяти, по умолчанию разрешены, если в качестве операндов выступают массивы констант. В других же случаях требуется явное разрешение таких операций через CSE.
Изменено: Oleg K - 02.11.2016 14:22:52 (была пропущена закрывающая скобка в конце формулы)
 
 
 
#5
02.11.2016 14:11:43
Можно просуммировать на лету попарные суммы - так почему СУММ() нужно вводить формулой массива? :)
"Просуммировать "на лету" попарные произведения", "мы сами подсказали Excel-ю" - это догадки, Ваши предположения, о не факт. На это я и обратил внимание - эти утверждения выпадают из общей стройности заглавного сообщения темы )

Да, с массивами констант - правильно. Массив уже создан, его не нужно вычислять.
 
 
 
#6
02.11.2016 14:20:24
Цитата
vikttur написал: это догадки, Ваши предположения
Именно так. Я именно это и заявил сразу в стартовом посте
Цитата
Всё дальнейшее -- сугубо моё представление, основанное на опыте  программирования вообще и составления формул Excel в частности. Я никак  не связан с разработчиками Excel.
Но логику найти хотелось бы, надеюсь она есть.
 
 
 
#7
02.11.2016 14:22:52
Логика вся - внутри за семью печатями. Разработчики Вам ее не объяснят. Утверждение следует принимать как аксиому: СУММПРОИЗВ при обработке массивов может работать без "массивного" ввода.
 
 
 
#8
02.11.2016 14:29:01
Цитата
vikttur написал: Массив уже создан, его не нужно вычислять.
Код
=СУММ( МУМНОЖ(({1;2;3}*A3+{3;3;1})*{1:2:3}; ({1;2;3}*A4+{3;3;1})*{1:2:3}) )
Здесь двумерный массив создается перемножением одномерных, причём есть зависимость по данным от ячейки листа (т.е. это перемножение не может быть выполнено заранее). И с этим двумерным массивом выполняются дальнейшие операции (перемножение матриц).
Изменено: Oleg K - 02.11.2016 14:46:37
 
 
 
#9
03.11.2016 13:21:23
зачем изобретать велосипеды, когда есть Майкл Гирвин:
http://baguzin.ru/wp/?p=7153#footnote3
F1 творит чудеса
 
 
 
#10
03.11.2016 14:20:10
Цитата
Максим Зеленский написал: зачем изобретать велосипеды
Спасибо за ссылку! Книга -- это конечно хорошо.
Но хотелось бы иметь компактное правило, однозначно определяющее, нужна ли формула массива в любом конкретном случае. И очень странно, что такого правила нет в документации Excel. Или есть?
Вообще, любой уважающий себя язык программирования имеет синтаксис, определенный формально (можно сказать, математически), не оставляющий неоднозначности в вопросе о допустимости любой конкретной языковой конструкции. А также определенную семантику, придающую любой допустимой конструкции определенное значение (за исключением случаев, которые явно оговорены как не определенные).
Например, хотелось бы четко понимать, в каких случаях формула, оперирующая с массивами и диапазонами, и введенная без CSE, выдаст ошибку #ЗНАЧ!, в каких случаях она выдаст "правильное" (ожидаемое) значение, а в каких она не выдаст ошибки, но результат будет отличаться от ожидаемого (например, где-то из массива будет взят только последний элемент и т.п.).
 
 
 
#11
03.11.2016 14:24:17
Цитата
хотелось бы четко понимать...
Любой начинающий писать программы четко не представляет, когда появится ошибка, как ее избегать, что применять оптимальнее... Это не говорит о том, что нет четкой структуры языка. Нужен опыт и знания, которые приходят с практикой.
 
 
 
#12
03.11.2016 14:57:45
По крайней мере, идея свести значение CSE к разрешению на создание массивов оказалась неверной.
Пока продуктивным мне представляется путь, состоящий в перечислении конкретных языковых конструкций, которые становятся допустимыми в CSE-формуле, в то время как без CSE они недопустимы или имеют другой смысл. Для начала могу предложить следующее.

В формулах CSE становятся допустимыми:
1. Массив как результат формулы.
2. Поэлементные арифметические и логические операции с массивами, заданными ссылками на диапазоны ячеек (с оговоркой, что умножение массивов, заданных ссылками на диапазоны, допустимо в аргументе функции СУММПРОИЗВ без CSE).
3. Использование ссылок на диапазоны ячеек в качестве определенных аргументов некоторых функций, а именно:
- первый аргумент функции ЕСЛИ
- аргумент функции ТРАНСП
- что ещё? наверняка ещё много чего

Важно, что пункты 2 и 3 относятся именно к ссылкам на диапазоны, тогда как массивы констант А ТАКЖЕ массивы, возвращаемые функциями, могут стоять в соответствующих позициях без CSE. Например, допустимо без CSE
Код
=СУММ(МУМНОЖ(A1:C3;A4:C6)+МУМНОЖ(A1:C3;A4:C6))
=СУММ(ЕСЛИ({ИСТИНА;ИСТИНА;ЛОЖЬ};{1;2;3}))
В частности, второй пример показывает, что загвоздка в конструкции СУММ(ЕСЛИ(...)) состоит вовсе не в передаче массива от ЕСЛИ к СУММ (как я утверждал в стартовом сообщении), а как раз в первом аргументе функции ЕСЛИ. При его замене на A1:A3 формула перестает работать без CSE.
Изменено: Oleg K - 03.11.2016 15:13:58
 
 
 
#13
03.11.2016 15:06:53
Цитата
vikttur написал:
Это не говорит о том, что нет четкой структуры языка. Нужен опыт и знания, которые приходят с практикой.
Опыт и знания относятся к субъекту. Синтаксическая структура же относится к языку программирования как к объекту, безотносительно знаний и опыта субъекта. Я не против того, что структура есть. Только где она описана? Если бы удалось увидеть формальный синтаксис языка формул Excel (хотя бы в части формул массива), то на вопросы о допустимости тех или иных конструкций можно было бы отвечать на основании формальных правил, не прибегая к опыту.
 
 
 
#14
03.11.2016 19:11:54
Ещё одна попытка. Идея такая: постановка вопроса "нужен ли CSE в некоторой заданной формуле" не вполне корректна, т.к. вообще говоря одна и та же формула может давать осмысленный результат как без CSE, так и при CSE, но интерпретироваться она будет по-разному и результат будет разный. Я теперь задаю вопрос по-другому: "Как влияет CSE на интерпретацию формулы?".

Основная проблема по моему представлению на данный момент заключается в формировании полных перечней или критериев в пунктах 3 и 4 раздела "О синтаксических позициях, чувствительных к CSE" (см. ниже). Самостоятельно найти какую-то логику (например, почему МУМНОЖ не чувствительна к CSE, а ТРАНСП чувствительна) я не сумел. Ещё вопрос -- верно ли я сформулировал исключение СУММПРОИЗВ и есть ли другие подобные исключения.

ИТАК:

А. Если результатом формулы является диапазон или массив, то:
БЕЗ CSE из диапазона выбирается одно значение (см. ниже Правило выбора из диапазона), из массива (не являющегося диапазоном) берется первое значение;
ПРИ CSE возвращается массив целиком.

Б. Если ссылка на диапазон находится в определенных синтаксических позициях, которые будем называть чувствительными к CSE (см. ниже О синтаксических позициях, чувствительных к CSE), то
БЕЗ CSE из диапазона выбирается одно значение (см. ниже Правило выбора из диапазона);
ПРИ CSE используется массив целиком, причём как правило выполняется поэлементная операция (см. ниже Правило поэлементных операций).

О синтаксических позициях, чувствительных к CSE
0. В арифметическом выражении, составляющем аргумент функции СУММПРОИЗВ (но не в аргументах функций, входящих в выражение), независимо от CSE диапазоны интерпретируются как массивы, а арифметические операции и сравнения трактуются как поэлементные (см. ниже Правило поэлементных операций).
1. В функциях выбора (ЕСЛИ, ВЫБОР) аргументы, из которых осуществляется выбор (2-й и далее), НЕ ИНТЕРПРЕТИРУЮТСЯ, то есть значением функции является сам аргумент. В частности, если на позиции этого аргумента стоит ссылка, то и результатом функции является ссылка. В этих случаях нужно снова проверить в соответствии с данными правилами позицию, в которой стоит сама функция (как дальше используется результат).
2. Во всех случаях, когда результатом функции является ссылка (например ИНДЕКС, СМЕЩ, ДВССЫЛ) также нужно проверить в соответствии с данными правилами позицию, в которой стоит сама функция (как дальше используется результат).
3. Ссылка на диапазон интерпретируется как массив независимо от CSE в функциях СУММ, ПРОИЗВ, МУМНОЖ, МОПРЕД, ...
4. Ссылка на диапазон чувствительна к CSE в 1-м аргументе функций ЕСЛИ, ВЫБОР, в арифметических операциях и сравнениях, в функции ТРАНСП, ...

Правило выбора из диапазона (без CSE)
Если диапазон является строкой, и столбец, в котором находится ячейка с РАССМАТРИВАЕМОЙ ФОРМУЛОЙ, принадлежит диапазону, то выбирается элемент диапазона из этого столбца.
Если диапазон является столбцом, и строка, в которой находится ячейка с РАССМАТРИВАЕМОЙ ФОРМУЛОЙ, принадлежит диапазону, то выбирается элемент диапазона из этой строки.
В остальных случаях возвращается ошибка #ЗНАЧ!

Правило поэлементных операций
1. Если размеры массивов-операндов совпадают, то операция последовательно выполняется с отдельными элементами массивов-операндов, расположенных на одинаковых позициях в массивах. Результат имеет тот же размер, что и операнды.
2. Если один из операндов является столбцом или строкой, и число его элементов равно соответствующему измерению остальных операндов, то операция выполняется согласно п.1, считая, что операнд-столбец или строка повторён нужное количество раз.
3. Если один из операндов является одиночным значением, то операция выполняется согласно п.1, считая, что оно повторяется нужное количество раз.
4. Если первый операнд является строкой, а остальные -- столбцами, или наоборот, то операция выполняется для каждого отдельного элемента первого операнда, и результаты объединяются в двумерный массив.
Изменено: Oleg K - 03.11.2016 21:05:12
 
 
 
Страницы: 1
Читают тему
Наверх