Тема, естественно, обсуждалась и имеется в архиве . Но поскольку в указанной архивной теме я не увидел собранного воедино самодостаточного объяснения понятия формулы массива в 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 может понадобиться только для записи КОНЕЧНОГО результата формулы в диапазон (уже безотносительно отдельных массивов в памяти).
Примеры:
CSE НЕ нужен, т.к. под суммой стоит ссылка на диапазон, и массивы создавать не нужно
CSE НУЖЕН. Вообще-то можно вычислить эту формулу без промежуточных массивов, но Excel не настолько интеллектуален, чтобы до этого догадаться. Поэтому нужно разрешить ему создать массив.
CSE НЕ нужен, т.к. мы сами подсказали Excel-ю, что здесь можно обойтись без создания массивов.
CSE НУЖЕН для передачи массива от функции ЕСЛИ к функции СУММ.
CSE НЕ нужен!! Почему? Потому что функция ИНДЕКС здесь возвращает не массив, а ССЫЛКУ НА ДИАПАЗОН! И никаких массивов вне таблицы не создаётся.
Вот как-то так. Предлагаю приводить контрпримеры, НЕ укладывающиеся в описанную картину.
ВНИМАНИЕ! Попытка компактного исчерпывающего объяснения формул массива 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 НЕ нужен!! Почему? Потому что функция ИНДЕКС здесь возвращает не массив, а ССЫЛКУ НА ДИАПАЗОН! И никаких массивов вне таблицы не создаётся.
Вот как-то так. Предлагаю приводить контрпримеры, НЕ укладывающиеся в описанную картину.
Изменено: - 03.11.2016 17:31:08
(добавил предупреждение о наличии неверных утверждений в стартовом посте)
