Пользователи офиса 365, наверняка давно уже успели пощупать новые функции и оценить удобство динамических массивов. А для тех, кто еще не успел это сделать или не совсем понимает как это все работает (и зачем оно вообще нужно) и посвящена данная статья, в которой постараемся разобраться, что же все-таки это такое - динамические массивы, какие преимущества дают и какие есть нюансы работы с такими массивами.


 
Что такое динамические массивы
Начнем с того, что динамические массивы будут доступны пользователям следующих версий Excel:

  • 365 офис (по подписке)
  • офис 2021 (для MAC и для Windows)

 
Всем остальным пользователям динамические массивы на момент написания статьи недоступны. Будем надеяться, что со временем список поддерживаемых версий расширится.
До появления динамических массивов формулы могли возвращать результат только в те ячейки, в которых они записаны. Динамический массив отменяет такое ограничение – это формула, записанная всего в одну ячейку, но результат при этом возвращает сразу в несколько. При этом динамический массив не требует указания четкой размерности ячеек для вывода результата, а сам подстраивается под размер будущего результата. Его так же можно сравнить с формулами массива, но различия между формулой массива и динамическим массивом вполне ощутимые. Рассмотрим эти различия поближе на примере простой ссылки.
Допустим, в ячейках А1:D10 у нас ожидается поступление данных(ручной ввод пользователем или запрос из внешнего источника). Эти данные нам необходимо использовать в других ячейках. Создадим ссылку на ячейки А1:D10 при помощи формулы массива(начиная с ячейки F1). Выделяем на листе массив ячеек F1:I10. Не снимая этого выделения, в строке формул вписываем ссылку на ячейки: =А1:D10. Завершаем ввод формулы одновременным нажатием клавиш Ctrl+Shift+Enter. Видим, что значения из ячеек А1:D10 полностью повторились в нашем диапазоне F1:I10. И вот здесь и кроются нюансы: в случае с формулами массива очень важно, чтобы выделенные предварительно ячейки были точно такого же размера, что и ячейки, данные из которых хотим перенести(точно такое же количество строк и столбцов, что и в ячейках, на которые ссылаемся. В примере выше это 10 строк и 4 столбца). Если мы выделим меньшее количество строк или столбцов, чем в переносимой ссылке - перенесены будут не все данные. А если мы выделим большее кол-во строк или столбцов - лишние строки и столбцы будут заполнены значением ошибки #Н/Д(#N/A). С одной стороны все предельно просто и ясно, а с другой…
Чаще всего сложно сразу определить размер области, в которую необходимо записать результат таких ссылок, особенно если они не в самом начале листа. Например, если ячейки, на которые надо сослаться записаны в диапазоне J63:P92, а записать ссылки на них нам надо, начиная с ячейки X12? Придется потрудиться, чтобы получить «красивый» и правильный результат.
В случае же с динамическими массивами эта проблема отсутствует. Чтобы создать ссылку на те же ячейки А1:D10, начиная с ячейки F1, все, что нам необходимо, это выделить одну ячейку F1, вписать в неё ссылку =А1:D10 и нажать Enter(особо отмечу - именно Enter, а не Ctrl+Shift+Enter). Excel сам поймет, что мы ссылаемся на несколько ячеек и автоматически запишет все их значения в ячейки F1:I10. Т.е. записываем мы ссылку в одну ячейку, а Excel распространяет эту ссылку на необходимое количество ячеек уже сам. То же самое и в случае со ссылкой на J63:P92, начиная с X12. Выделяем ячейку X12, записываем в ней ссылку =J63:P92, жмем Enter и все – результат достигнут без всяких расчетов.
Конечно, пример примитивный, но он показывает основной принцип. И на данном этапе можно выделить два основных удобства динамических массивов:

  1. Не надо заранее высчитывать нужное количество строк и столбцов - достаточно записать ссылку в начальную ячейку
  2. Нет необходимости завершать ввод сочетанием трех клавиш Ctrl+Shift+Enter.

 
А теперь рассмотрим ситуацию, когда нам надо не просто записать ссылку на ячейки, а транспонировать их(т.е. строки записать в столбцы, а столбцы в строки). Скажем, транспонировать надо все те же ячейки А1:D10, начиная с ячейки F1. Для этого можно использовать встроенную функцию ТРАНСП(TRANSPOSE). И здесь все еще сложнее в плане определения результирующих ячеек: надо от ячейки F1 отсчитать 10 столбцов и 4 строки. А если записывать надо не в F1, а в X12? И диапазон использовать надо J63:P92? Вот где динамические массивы опять с большим отрывом дадут фору формулам массива: выделили одну ячейку, записали в неё функцию =ТРАНСП(J63:P92), нажали Enter и все. Excel сам определит сколько строк и столбцов заполнить значениями, в зависимости от результата работы функции. В случае же с формулами массива нам надо будет кропотливо отсчитывать нужное количество строк и столбцов. При этом выделение ячеек с запасом строк и столбцов выглядит совсем не привлекательно – функция ТРАНСП запишет значение ошибки #Н/Д(#N/A) в «лишние» строки и столбцы(как и положено формуле массива).
Как это будет работать, если вдруг необходимо изменить исходную ссылку на ячейки? Т.е. в исходной формуле у нас записана ссылка на А1:D10 и эту ссылку необходимо изменить на A2:F50. В случае с формулами массива придется сначала выделить все ячейки, в которые ранее была записана формула массива и удалить оттуда формулу(нажав клавишу Delete). Затем выделить новый диапазон ячеек для записи результата, вписать ссылку на новый диапазон A2:F50 и завершить ввод сочетанием клавиш Ctrl+Shift+Enter.
чтобы быстро выделить все ячейки единой формулы массива - надо выделить одну любую ячейку внутри массива -F5 -Выделить -Текущий массив

Примечание: удалять прежнюю ссылку требуется только в том случае, если размер новой ссылки отличается от записанной ранее. Если же количество строк и столбцов новой ссылки совпадают – достаточно просто изменить эту ссылку и нажать Ctrl+Shift+Enter

В случае же с динамическими массивами всего это делать не нужно: достаточно выделить только первую ячейку, изменить прежнюю ссылку на новую и нажать Enter. Проще некуда. Никаких удалений и новых подсчетов. Тоже самое с удалением такой формулы - выделяем только одну первую ячейку и нажимаем клавишу Delete.
Надеюсь, на данном этапе принцип работы и преимущества динамических массивов мне удалось пояснить.


 
Нюансы и другие нововведения динамических массивов
Определить, что ячейка является частью динамического массива, очень просто: когда мы выделяем ячейку внутри динамического массива - все ячейки массива подсвечиваются синей рамкой:
Подсветка динамического массива
При этом, если выделена левая верхняя ячейка(т.е. родительская ячейка с формулой, которая и "создала" этот массив), то в строке формул она будет выглядеть как обычная формула. А если выделена любая другая ячейка внутри массива ячеек - в строке формул значение будет серым шрифтом и изменение значения такой ячейки приведет к ошибке формулы динамического массива в родительской ячейке(создавшей массив).

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

  • @(Знак "at" или "собака") - называется "оператор неявного пересечения" и означает ссылку на одно значение из множества. Например, в старых версиях Excel допускалось использовать ссылку на диапазон ячеек в аргументах функций, которые не могут работать с целыми диапазонами. В этом случае функция просто брала первую(верхнюю левую) ячейку из этого массива(при указании целого столбца - значение из строки, в которой записана сама формула). Но в Excel 365 по умолчанию такие ссылки будут восприниматься именно как массив значений и результатом будет либо ошибка, либо результат будет записан не в одну ячейку, а в несколько, как динамический массив.
    Для примера запишем в ячейки A1:A5 числа от 1 до 5, а в ячейку B1 простую формулу:
    =ЕСЛИ(A1:A5>3;"больше";"нет")
    В прежних версиях Excel после записи такой формулы в качестве результата в ячейку B1 будет записано "больше", если в ячейке А1 число больше 3 и "нет", если в ячейке А1 значение равное или меньше 3. В Excel 365 по умолчанию результат будет записан сразу в 5 ячеек(В1:В5) и для каждой будет рассчитана «своя» формула: для В1 будет вычислено значение для ячейки А1, для В2 – А2, для В3 – А3 и т.д. И чтобы добиться совместимости прежних версий с Excel 365 формула должна выглядеть так:
    =ЕСЛИ(@$A$1:$A$5>3;"больше";"нет")
    Чаще всего такую запись можно увидеть в файле, который создавался в прежних версиях с такими вот огрехами(т.е. указание диапазона для аргумента, который требует только одну ячейку).
    Т.е. знак @ дает понять Excel, что использовать надо значение только одной ячейки из всего массива, а не весь массив ячеек. При этом то самое значение определяется на основании номера строки и столбца на листе, в котором записана формула. Если даже точнее – то на основании первой строки вычисляемого диапазона и строки, в которой сама формула. Иногда это порождает ошибки(именно при конвертации под старые версии). На примере все той же формулы разберем для начала простой пример:
    =ЕСЛИ(@$A$1:$A$5>3;"больше";"нет")
    Если записать её в ячейку B1, B2, B3 и т.д. то она будет вычислена без ошибок только до пятой строки. В ячейке B6 будет ошибка #ЗНАЧ!(#VALUE!), т.к. Excel в данном случае считает диапазон $A$1:$A$5 массивом из 5-ти элементов и для ячейки B1 берет первый элемент(т.к. B1 в первой строке), для B2 – второй и т.д. А вот для ячейки B6 уже нет элемента, т.к. их всего 5, а мы пытаемся получить 6-ой. И как итог ошибка.
    Но, как писал выше - на самом деле расчет идет чуть сложнее. В Microsoft не могли не учесть тот факт, что формула изначально могла быть введена не в первую строку, а в 20-ю. Поэтому отправной точкой по сути является первая ячейка массива внутри формулы. Запишем формулу чуть иначе:
    =ЕСЛИ(@$A$2:$A$6>3;"больше";"нет")
    Т.е. первая ячейка с данными у нас во второй строке. А формулу саму запишем в B1, как и раньше. В первой же ячейке формула выдаст ошибку. Почему? Все просто – разберем пошагово алгоритм работы функций с оператором пересечения.
    Сначала берется номер строки ячейки, в которой записана формула. В нашем случае для B1 это будет номер 1. Далее определяется первая строка диапазона, для которого указан оператор: это у нас диапазон $A$2:$A$6 и первая строка для него – 2. Теперь, для вычисления номера элемента в диапазоне $A$2:$A$6 применяется простая формула: строка формулы минус первая строка диапазона плюс 1. Т.е.: 1-2+1. Результат вычисления формулы равен 0. Элемента со значением 0 нет, поэтому формула возвращает ошибку. А вот для ячейки B2 результатом уже будет «нет», т.к. будет взят первый элемент массива.
    Это придется учитывать пользователям новых версий и обращать внимание на то, как задаются аргументы. При этом Excel, открывая файл с формулами, созданными в более ранних версиях, обязательно попытается все их преобразовать и везде добавить эти символы собаки. И такое автоматическое преобразование может «испортить» результаты формул, созданных в более ранних версиях.

    •  

  • #(символ решетки) - называется «оператор динамического массива». Используется для ссылки на функции, возвращающие динамический массив. Это могут быть любые функции, которые возвращают динамический массив. Например, в ячейке F1 записана формула =ТРАСП(J63:P92), которая возвращает результат в ячейки F1:AI7. Чтобы сослаться на все заполненные ячейки(F1:AI7), достаточно будет в другой функции указать ссылку на первую ячейку и оператор динамического массива: =F1#.
    Если надо выбрать минимальное значение из полученного набора: =МИН(F1#). И конечно, этот оператор очень удобно использовать в новых функциях динамических массивов типа СОРТ, УНИК, ФИЛЬТР и т.д. Плюс очень удобно использовать # в качестве диапазона для выпадающего списка. В этом случае в качестве источника надо будет просто указать ссылку на первую ячейку диапазона: =F1# (только не забываем, что в качестве диапазона для списка может использоваться только один столбец).

    Важно: для использования оператора динамического массива обязательно в качестве ссылки указывать первую(верхнюю левую) ячейку, в которую записана формула динамического массива(F1), а знак решетки обязательно должен идти сразу после адреса первой ячейки динамического массива. Если указать адрес другой ячейки(не первой) – то получим ошибку #ССЫЛКА!(#REF!).

    Оператор динамического массива может ссылаться на другие листы и книги, но не работает с закрытыми книгами. Если книга с источником будет закрыта, то при первом же пересчете формул мы получим ошибку #ССЫЛКА!(#REF!). Связано это с тем, что этот оператор всегда ссылается именно на динамический массив, который не может быть вычислен в закрытой книге.

Кстати, если кто-то чуть более активно работал с «умными» таблицами в Excel, то наверняка уже сталкивался с этими операторами. Функционал у них был там почти такой же, но применялись эти операторы менее широко и менее удобно – как правило Excel подставлял их сам, в зависимости от того на какую часть таблицы была сделана ссылка.

Что нельзя делать с динамическими массивами:

  • нельзя удалять внутри них ячейки, строки или столбцы
  • нельзя сортировать (новые функции вроде СОРТ не в счет), но можно фильтровать как обычный диапазон
  • нельзя преобразовать в умную таблицу


 
Новые типы ошибок динамических массивов

  • #ПЕРЕНОС!(#SPILL!) – может появиться по нескольким причинам и хорошая новость в том, что Excel сам подсказывает по какой именно. Чтобы увидеть причину данной ошибки необходимо выделить ячейку с этой ошибкой(только одну!), раскрыть значок ошибки и посмотреть на первую строку:
    Причина ошибки ПЕРЕНОС
    Возможные значения ошибок:

    • Диапазон для переноса данных не пустой(Spill range isn’t blank): динамический массив перекрывает собой другие ячейки с данными. Например, если ввести в ячейку A1 ссылку =B1:B10, а потом в ячейку A4 записать любое значение, то в ячейке A1 будет ошибка #ПЕРЕНОС!(#SPILL!), т.к. четвертая ячейка ссылки попадает на занятую значением ячейку.
    • Диапазон для переноса данных содержит объединенную ячейку(Spill range has merged cell): результат попадает на объединенную ячейку. Думаю, не требует дополнительных разъяснений.
    • Диапазон для переноса данных находится в таблице(Spill range in table): формула динамического массива записана в «умной» таблице(Вставка(Insert) -Таблица(Table)). Тоже не сложно: формулы динамических массивов просто не поддерживаются в умных таблицах(по крайней мере на момент написания статьи).
    • Слишком большой диапазон для переноса данных(Spill range is too big или Extends beyond the worksheet's edge): ячейки динамического массива выходят за пределы листа. Может появиться при открытии файла, который был создан в более ранних версиях и попытке исправления формулы. Выше мы рассматривали похожий пример с функцией ЕСЛИ. Здесь разберем проще: если попробовать записать в ячейку B2 формулу вида: =A:A*1, то мы получим как раз эту самую ошибку - Слишком большой диапазон для переноса данных, т.к. идет смещение на одну ячейку и для последней ячейки итогового массива просто нет данных на листе, т.к. мы начали запись формулы со 2-ой строки.
    • Диапазон для переноса данных неизвестен(Spill range is unknown или Indeterminate size): диапазон для записи результата не может быть определен, т.к. результат постоянно изменяется. Например, для создания последовательного списка значений от 1 до 100 мы можем использовать формулу динамического массива =ПОСЛЕД(1;10)(=SEQUENCE(1,10)). В данном случае никакой ошибки не будет, т.к. мы задали значения для списка с фиксированным кол-вом строк и столбцов. Но если мы захотим сделать постоянно изменяемый по кол-ву строк или столбцов список с помощью такой формулы =ПОСЛЕД(СЛУЧМЕЖДУ(1;10)) мы получим ошибку #ПЕРЕНОС!(#SPILL!), т.к. функция СЛУЧМЕЖДУ(RANDBETWEEN) пересчитывается при любом изменении на листе и Excel просто не может заранее определить достаточно ли будет в каждый момент свободных ячеек на листе для записи результата.
    • Недостаточно памяти (Out of memory): вычисление формулы привело к нехватке памяти в Excel. В этом случае можно попробовать указать в качестве исходных данных диапазон с меньшим количеством ячеек.
    • Неизвестно/Откат (Unrecognized/Fallback): Excel не может распознать точную причину ошибки. Возможно, в формуле указаны не все исходные данные или аргументы для вычисления выражения.

    Сама ошибка #ПЕРЕНОС!(#SPILL!) появляется только в первой ячейке(в которой записана непосредственно формула), а на все остальные ячейки формула уже не распространяется, хотя границы динамического массива при этом Excel все же подсвечивает(в случаях, когда этот массив может быть определен):
    Подсветка границ массива
    Чтоб определить какие именно ячейки мешают правильной работе динамического массива, надо выделить ячейку с ошибкой #ПЕРЕНОС!(#SPILL!), раскрыть значок ошибки и выбрать там Выделить препятствующие ячейки(Select Obstructing Cells):
    Выделить препятствующие ячейки
    Будут выделены ячейки, которые препятствуют распространению значений динамического массива на листе. Для чего это нужно? Не всегда даже по тексту ошибки можно определить конкретные ячейки, которые являются причиной этой ошибки. Например, когда какие-то из ячеек «на пути» динамического массива содержат только пробелы – визуально такие ячейки пустые, но фактически содержат значения. Или когда препятствующие ячейки находятся где-то далеко внизу листа.
    Пункт будет недоступен в случае ошибки из-за выхода за пределы листа - Слишком большой диапазон для переноса данных и в случае ошибки внутри «умной» таблицы - Диапазон для переноса данных находится в таблице.

  • #ВЫЧИСЛ!(#CALC!) – ошибка появляется, если нечего вычислять(например, если функция ФИЛЬТР ничего не отфильтровала и третий аргумент при этом не задан).

Важно знать: поддержка и работа динамических массивов в новых версиях встроена «внутрь» Excel. Техническим языком – в его движок. И любая формула Excel теперь может стать динамическим массивом. А это значит, что отключить их или отказаться от их использования уже не получится(если только не перейти на более старую версию Excel). По крайней мере такая возможность не поддерживалась на момент написания статьи.


 
Динамические массивы в VBA
В VBA тоже появились новые методы и свойства для работы с динамическими массивами. Их пока немного, но как минимум позволяют определить является ли ячейка частью динамического массива и какая ячейка при этом является «родительской» - т.е. какая ячейка этот массив создала(по сути ячейка с формулой динамического массива).

'---------------------------------------------------------------------------------------
' Procedure : IdentSpilledRange
' Author : Щербаков Дмитрий(The_Prist)
'          Профессиональная разработка приложений для MS Office любой сложности
'          Проведение тренингов по MS Excel
'          https://www.excel-vba.ru
'          info@excel-vba.ru
' Purpose: Процедура определения, является ли активная ячейка частью динамического массива
'---------------------------------------------------------------------------------------
Sub IdentSpilledRange()
    Dim rSpillParentCell As Range
    'SavedAsArray - свойство, которое вернет True, если активная ячейка "внутри" динамического массива
    If ActiveCell.SavedAsArray Then
        'получаем первую ячейку динамического массива - по сути ячейка с формулой
        Set rSpillParentCell = ActiveCell.SpillParent
        MsgBox "Ячейка является частью динамического массива" & vbNewLine & _
               "Адрес ячейки с формулой динамического массива: " & rSpillParentCell.Address & "'", vbInformation, "www.excel-vba.ru"
    Else
        MsgBox "Ячейка не является частью динамического массива", vbInformation, "www.excel-vba.ru"
    End If
End Sub

Позже будут статьи с более детальным разбором работы новых функций динамических массивов, таких как СОРТ, ФИЛЬТР, УНИК и т.д.

Добавить комментарий

Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.