Это перевод главы книги Bill Jelen. Excel Dynamic Arrays Straight to the Point. К содержанию.
Почему формулы массива (Ctrl+Shift+Enter) были так сложны для понимания?
Если вы когда-либо вводили Ctrl+Shift+Enter, эта глава прольет свет, почему формулы массива были столь трудными в использовании в предыдущих версиях Excel. Для начала несколько вопросов о работе прежних версий Excel. Я не ожидаю, что все ваши ответы будут правильными. Попробуйте угадать. Далее по ходу главы я внесу ясность в эти вопросы.
Вопрос 1. Если в ячейку D8 вы введете =B4:B14, каков будет результат?
- 2047, сумма значений B4:B14.
- 1, значение первой ячейки в диапазоне.
- 16, значение в той же строке диапазона.
- Ошибка #ЗНАЧ! синтаксис формулы неверный
Рис. 44. Что вы получите, если в старом Excel в одной ячейке попытаться вернуть много значений?
Скачать заметку в формате Word или pdf, примеры в формате Excel
Вопрос 2. Если вы введете =B2:B14 в ячейку D16. Что вы получите? Ответ такой же, как и для вопроса 1? Или иной?
Вопрос 3. Что вернет формула =СУММ(C2:E2*B5:B7)?
- 6, потому что 2 умножить на 3 = 6.
- 18, потому что каким-то волшебным образом выполнится 2*3 + 2*3 + 2*3 = 18.
- 54, потому что вы только что приняли ЛСД.
- Ошибка #ССЫЛКА! потому что такая запись недопустима.
Рис. 45. Сумма произведений двух массивов
Вопрос 4. Чему равно =СУММ(ДЛСТР(A4:A15))? Будут ли результаты разными, если:
- ввести формулу и нажать Enter;
- ввести формулу и нажать Ctrl+Shift+Enter;
- ввести в ячейку С4 формулу =ДЛСТР(A4), скопировать ее вниз до С15, суммировать значения в диапазоне С4:С15.
Рис. 46. Функция ДЛСТР ожидает в качестве аргумента одну ячейку
В прошлом я мог взять сложную формулу массива из книги Майка Гирвина Ctrl+Shift+Enter и использовать ее. Я иногда мог объяснить, как она работает. Для этого я открывал диалоговое окно Вычисление формулы на вкладе Формулы. Однажды я даже целый час говорил о формулах массива на мероприятии в Люцерне, Швейцария. Экхард Пфайффер присутствовал при этом и даже кивнул мне, подтверждая, что я не полностью ошибаюсь в своем понимании. Правда, я говорил по-английски для немецкоговорящей аудитории, так что, возможно, я ошибался довольно часто, но слушатели были слишком вежливы, чтобы указать мне на это.
Но я никогда по-настоящему не понимал формулы массивов, пока не посмотрел презентацию Джо Макдэйда о формулах массивов в прежних версиях Excel.
Новые формулы массива настолько просты, что кажется странным поместить этот сложный материал в книге. Я размещаю его ближе к концу книги, поэтому многие люди его не прочтут. Я надеюсь, что вы будете исключением.
Краткий глоссарий
- Старый Excel: Excel, который не поддерживает формулы динамических массивов.
- Скалярный: одно значение или одна ячейка.
- Массив или вектор: много значений или много ячеек прямоугольного диапазона.
Старый Excel использовал массивы гораздо чаще, чем мы думаем
В старом Excel вы использовали массивы в следующих случаях:
- Когда имя не было диапазоном.
- При использовании формулы в условном форматировании.
- При нажатии клавиши F9 в то время, как были выделены некоторые символы в строке формул. Даже обычные формулы демонстрировали поведение массивов.
В следующих разделах описываются пять типов поведения при работе с массивами:
- Неявное пересечение (Implicit Intersection)
- Подъем (Lifting).
- Попарный подъем (Pairwise Lifting).
- Трансляция (Broadcasting).
- Усечение массива (Array Truncation).
Неявное пересечение
Неявное пересечение возникает, когда диапазон передается в формулу, ожидающую скаляр (одно значение), и Excel выбирает значение в той же строке или столбце, что и формула. Если в старом Excel вы введете =ДЛСТР($A$2:$A$7)[1] в ячейке В2, формула вернет значение 5, т.е. длину строки в ячейке А2.
Рис. 47. Неявное пересечение приводит к тому, что ответ основан на ячейке A2
Если вы введете эту же формулу в ячейке В4, ответ будет основан на ячейке А4:
Рис. 48. Скопируйте формулу в ячейку В4, и ответ будет основан на A4
Неявное пересечение работает и с горизонтальными диапазонами. Если ввести формулу =ДЛСТР(@$E$1:$J$1) в ячейку Е3, ответом будет длина строки Е1:
Рис. 49. ДЛСТР работает и с горизонтальными диапазонами
Неявное пересечение всегда работало в старом Excel. Мы просто не знали о нем, и поэтому не использовали. Однажды я выдал приз Гуру Excel за следующую формулу: =ВПР($A$2:$A$10;$F$2:$H$30;3;ЛОЖЬ). Эта формула одинакова во всем диапазоне D2:D10. Единственная причина, по которой она работает, заключается в том, что неявное пересечение указывает для формулы в D2 только на A2 из всего диапазона A2:A10… В D3 на А3, и т.д.
Рис. 50. В старом Excel можно использовать ВПР как функцию массива благодаря неявному пересечению
Нарушение неявного пересечения
Неявное пересечение терпит неудачу, когда вы вводите формулу в ячейку, которая не пересекает данные. Например, в ячейке G3:
Рис. 51. Если пересекаться не с чем, формула возвращает ошибку
Ошибка вернется, и когда Excel не сможет решить, с какой именно ячейкой использовать неявное пересечение. С В8, В9 или В10?
Рис. 52. Ошибка возвращается также, если формула пересекается с более чем одной ячейкой диапазона
В старом Excel формула =СУММ(В2:В6*С2:С6) в зависимости от того, в какой ячейке она вводилась, либо возвращала ошибку, либо выдавала результат перемножения двух значений с использованием неявного пересечения:
Рис. 53. Там, где в старом Excel не справляется функция СУММ на выручку приходит СУММПРОИЗВ
Если заменить СУММ на СУММПРОИЗВ, результат будет адекватным. Это связано с тем, что СУММПРОИЗВ игнорирует неявное пересечение.
Неявное пересечение – это то, что иногда приводило к ошибкам. Более того, часто вы не понимали, что виновато неявное пересечение.
Когда формула ожидает одно значение, а поступает массив, происходит подъем (lifting)
Что происходит, когда старый Excel ожидает одно значение, а вы предоставляете диапазон значений? Excel сформирует из этих значений массив, и функция вернет массив того же размера. Это называется подъем – подъем (или увеличение размерности) от скалярной величины к вектору.
Рис. 54. Функция ДЛСТР ожидает скаляр
Когда вы подаете на вход функции диапазон значений (вектор), Excel поднимает функцию и вычисляет в оперативной памяти 12 значений. Их можно увидеть, если выделить формулу в строке формул, и нажать F9, заменяя формулу значениями:
Рис. 55. В строке формул показан массив из 12 результатов работы функции ДЛСТР(А1:С4)
Усечение массива
Если вы попытаетесь использовать =ДЛСТР(A1:C4) в старом Excel, и введете формулу, нажимая Ctrl+Shift+Enter, Excel применит к функции подъем и вычислит 12 результатов. Но вернет только одно значение, соответствующее верхней левой ячейке диапазона A1:C4, или 3, потому что ДЛСТР("Joe") = 3. Это иллюстрирует концепцию усечения массива.
Прим. Багузина. Массив может усекаться не до одной ячейки, о до нескольких. Например, выделите ячейки Е6:F7 и введите формулу массива {=ДЛСТР(A1:C4)}. 12 значений, хранящиеся в памяти функции =ДЛСТР(A1:C4) будут представлены четырьмя результатами, соответствующими диапазону А1:В2.
Рис. 56. Усечение массива из 12 значений до четырех
Использование функции-оболочки в старом Excel
Распространенным подходом в старом Excel было использование функции массива ДЛСТР(A1:C4) внутри функции-оболочки. Например, вводя =СУММ(ДЛСТР(A1:C4)) и нажимая Ctrl+Shift+Enter в любом месте листа вы получите правильный результат – 67. Нажатие Ctrl+Shift+Enter блокирует неявное пересечение.
Рис. 57. Формула массива работает в старом Excel в любом месте листа
Ctrl+Shift+Enter также управляет размером выходных данных. Если вы предварительно выделите диапазон «правильного» размера, введете формулу, возвращающую массив, и нажмете Ctrl+Shift+Enter, вы заполните все ячейки в предварительно выделенном диапазоне.
Пять функций в старом Excel блокировали неявное пересечение и без использования Ctrl+Shift+Enter. Это: СУММПРОИЗВ, МУМНОЖ, МОБР, ЧСТРОК и ЧИСЛСТОЛБ.
Старый Excel был способен поднять функцию и вернуть множество результатов. Проблема заключалась в том, что неявное пересечение часто блокировало ответ. Если бы неявное пересечение не мешало, то усечение массива позволяло вернуть ответ для первого элемента массива.
Благодаря динамическим массивам вам больше не нужно беспокоиться о том, что неявное пересечение испортит результаты. Формула =СУММ(ДЛСТР(A1:C4)), введенная в любом месте листа без Ctrl+Shift+Enter, вернет правильный результат:
Рис. 58. Формула динамического массива в новом Excel не требует Ctrl+Shift+Enter
От подъема к попарному подъему
Если Excel ожидает два скалярных выражения, а вы предоставляете ему два массива, Excel применит к ним попарное поднятие. Например, в старом Excel нельзя получить корректный результат, просто введя формулу ={1:2:3:4}+{1:1:1:1}
Рис. 59. Два массива имеют одинаковое количество строк (четыре), но для их сложения нужно поместить векторы внутрь функции
А. Если вы введете формулу ={1:2:3:4}+{1:1:1:1} в ячейку А1, и нажмете Enter или Ctrl+Shift+Enter, результат будет одинаковым = 2. Т.е., сумма двух первых элементов каждого массива. Б. Если выделить формулу ={1:2:3:4}+{1:1:1:1} в строке формул, и нажать F9, формула вернет массив {2:3:4:5}. В. Если выбрать диапазон С1:С4, ввести формулу ={1:2:3:4}+{1:1:1:1} и нажать Ctrl+Shift+Enter, вычисленный в Б. диапазон отразится в С1:С4. Г. Корректное суммирование двух массивов можно получить, если поместить исходную формулу внутрь функции СУММ.
Трансляция приводит все массивы к одному размеру
Когда Excel имеет дело с массивом и скаляром, он расширяет скаляр до размерности массива. Просто размножая значение скаляра. Предположим, что у вас есть массив из трех строк по одному столбцу. Чтобы умножить его на скаляр (или массив из одной строки и одного столбца), Excel расширит этот скаляр теми же значениями до размера первого массива:
Рис. 60. Трансляция (или расширение) приводит два массива к одному размеру
Если поместить произведение массива и скаляра внутрь функции суммирования =СУММ({1;2;3}*3), вы получаете верный ответ = 18. Как обычно, вы можете ввести формулу ={1;2;3}*3, и в строке формул нажать F9, чтобы увидеть, что Excel возвращает массив ={3;6;9}.
Кстати о массивах констант. Они состоят из фигурных скобок со значениями внутри. Элементы в одной строке и разных столбцах разделяются точкой с запятой. Для перехода к следующей строке используется двоеточие. Например, массив {1;2;3:4;5;6} соответствует:
Рис. 61. Синтаксис массива констант
Любопытно, что если вы попытаетесь умножить массив {1;2;3} на {3;3}, Excel не будет знать, что делать с третьей ячейкой во втором массиве. Поэтому промежуточное вычисление вернет СУММ(3;6;#Н/Д), и общий результат будет ошибочным #Н/Д.
Рис. 62. В случае массивов разных размеров формула может вернуть ошибку
Как описанное выше поведение влияет на динамические массивы?
Неявный оператор пересечения @ делает то, что уже было возможно и в старом Excel. Но оператор явно указывает на пересечение, что делает ваши действия более осмысленными. В старом Excel формулы массива работали только тогда, когда вы совершали какую-то магию. Например, Ctrl+Shift+Enter или СУММПРОИЗВ / МУМНОЖ. Последние позволяли отключить неявное пересечение. С появлением динамических массивов неявное пересечение отключено в Excel по умолчанию. Если всё же вам нужно использовать пересечение (ставшее явным) добавьте оператор @.
Почему Excel иногда добавляет оператор пересечения?
Если вы откроете какую-нибудь старую книгу Excel, вы можете заметить, что в начале формулы или перед аргументом появился знак @. В. Начните редактировать формулу и появится сообщение:
Рис. 63. Excel добавляет символ @, когда формула может использовать неявное пересечение
За те 15 месяцев, пока динамические массивы проходили бета-тестирование было много вопросов, почему Excel добавил @ к той или иной формуле. Джо Макдэйд вступал в долгую дискуссию о том, почему в определенные дни, когда Луна в такой-то фазе эта формула может вызвать неявное пересечение. Эти повторяющиеся дискуссии заставили меня осознать, что неявное пересечение в старом Excel происходило существенно чаще, чем я подозревал.
На рис. 64 формула в B2 не может принять вид =ИНДЕКС(B4:F15;0;2) или =ИНДЕКС(B4:F15;2;0).[2] Проверка данных в ячейках С1 и F1 предотвращает это. Но если кто-то введет 0 в A2, а затем скопирует A2 и вставит в F1, то =ИНДЕКС(B4:F15;2;0) вернет массив {2;4;8;16;32}. Поскольку формуле в В2 некуда разлиться, Excel вернет ошибку #ПЕРЕНОС! Неважно, что этого никогда не произойдет. Excel понял, что это может произойти, и добавил @ к формуле. Этот оператор подавит динамический диапазон, и выведет только одно значение в соответствии с неявным пересечением (ставшим в такой нотации явным).
Рис. 64. Джо и команда Calc рассматривают массу сценариев, в которых может возникнуть неявное пересечение
Ответы на вопросы в начале главы
Вопрос 1. Если в ячейку D8 вы введете =B4:B14, каков будет результат?
- 2047, сумма значений B4:B14.
- 1, значение первой ячейки в диапазоне.
- 16, значение в той же строке диапазона.
- Ошибка #ЗНАЧ! синтаксис формулы неверный.
Рис. 65. В старом Excel в результате неявного пересечения вы получите 16 в строке 8 и #ЗНАЧ! в строке 16
Вопрос 2. Если вы введете =B2:B14 в ячейку D16. Что вы получите? Ответ такой же, как и для вопроса 1? Или иной?
См. рис. 65. Ответ иной. Вы получите ошибку #ЗНАЧ! так как неявное пересечение происходит за пределами исходного массива.
Вопрос 3. Что вернет формула =СУММ(C2:E2*B5:B7)?
- A. 6, потому что 2 умножить на 3 = 6.
- B. 18, потому что каким-то волшебным образом выполнится 2*3 + 2*3 + 2*3 = 18.
- C. 54, потому что вы только что приняли ЛСД.
- D. Ошибка #ССЫЛКА! потому что такая запись недопустима.
Благодаря трансляции Excel расширит массив {2;2;2} до {2;2;2:2;2;2:2;2;2}, а массив {3:3:3} до {3;3;3:3;3;3:3;3;3}. У вас будет два массива 3х3, и их перемножение даст массив 3х3 в каждой ячейке которого будет шестерка. А девять по шесть вернет 54:
Рис. 66. Произведение двух зеленых массивов даст 54
Вопрос 4. Чему равно =СУММ(ДЛСТР(A4:A15))? Будут ли результаты разными, если:
- ввести формулу и нажать Enter;
- ввести формулу и нажать Ctrl+Shift+Enter;
- ввести в ячейку С4 формулу =ДЛСТР(A4), скопировать ее вниз до С15, суммировать значения в диапазоне С4:С15.
Рис. 67. В старом Excel используйте Ctrl+Shift+Enter, чтобы предотвратить неявное пересечение
В старом Excel, если ввести формулу =СУММ(ДЛСТР($A$4:$A$15)) в ячейки диапазоне С4:С15 и нажать Enter, то, из-за неявного пересечения они вернут длину той же строки из диапазоне А4:А15. Если ввести =СУММ(ДЛСТР(A4:A15)) в любом месте листа и нажать Ctrl+Shift+Enter, формула вернет 113. ДЛСТР ожидает один аргумент, но подъем заставляет функцию вычислить длину строки 12 раз. Если поместить ДЛСТР(A4:A15) внутрь функции СУММ, эти 12 значений будут суммированы. Варианты b) и c) идентичны.
[1] Поскольку у меня на ПК установлен новый Excel, при вводе формулы =ДЛСТР($A$2:$A$7) в ячейку В2 он разольет массив на диапазон В2:В7. Я использую оператор неявного пересечения @ в аргументе функции, чтобы подавить работу функции динамического массива, и вывести только одно значение. – Здесь и далее прим. Багузина.
[2] Ноль во втором или третьем аргументе означает, что формула вернет массив, включающий весь столбец или всю строку диапазона B4:F15.