Динамические массивы: примеры, уроки программирования на С++

Формулы динамического массива в Excel, не поддерживающими динамические функции

Excel для Microsoft 365 Еще…Меньше

В сентябре 2018 г. мы представили формулы динамического массива. Формулы динамического массива могут автоматически заполнять или «перетащать» в соседние пустые ячейки, устраняя необходимость в устаревших формулах массива CTRL+SHIFT+ВВОД (CSE). При открытии книги, содержащего формулы динамического массива в более ранней версии Excel, необходимо учитывать некоторые аспекты совместимости.

Примечание: Поддержка формул динамического массива была выпущена для подписчиков Microsoft 365 в Current Channel в январе 2020 г.

Подробные сведения

• При написании формулы в Excel с поддержкой динамических массивов она определяет, может ли формула возвращать несколько значений (даже если она в настоящее время возвращает только одно значение).

  Если он может вернуть несколько значений, мы сохраняем его как формулу динамического массива, которая будет отображаться в более ранних версиях Excel как устаревшая формула CSE. 

• Ниже приведены примеры функции MUNIT, введенной в виде формулы динамического массива и устаревшей формулы CSE. Обратите внимание, что устаревшие формулы массива CSE ведут себя очень аналогично формулам динамического массива. Основное различие заключается в том, что они не могут изменять размер и не имеют границы сброса. Сравнение этих двух значений см. в разделе » Формулы динамического массива и устаревшие формулы массива CSE».

Динамический массив

Устаревшая CSE

• org/ListItem»>

  Если вы знаете, что будете совместно использовать книги с динамическими формулами массива с другими пользователями Excel, не поддерживающими динамические функции, лучше избегать использования недоступных им функций. Средство проверки совместимости можно использовать для поиска формул, которые сохраняются как формулы динамического массива и будут отображаться как массивы CSE в более ранних версиях Excel. 

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

См. также

Функция ФИЛЬТР

Функция СЛУЧМАССИВ

Функция ПОСЛЕДОВ

Функция СОРТ

Функция СОРТПО

Функция УНИК

Ошибки #ПЕРЕНОС! в Excel

Поведение разлитого массива

Оператор неявного пересечения: @

Билл Джелен.

Динамические массивы в Excel

Незаметно для меня Microsoft совершил прорыв, представив в сентябре 2018 новые возможности – динамические массивы. Ниже – перевод книги Bill Jelen. Excel Dynamic Arrays Straight to the Point. Книги серии Straight to the Point предназначены для глубокого освещения одного аспекта Excel.

Билл Джелен – основатель сайта MrExcel.com и автор множества книг о Excel. Сайт бесплатно отвечает на более чем 30 000 вопросов в год. В моем блоге представлены три книги Джелена:

• Сводные таблицы в Microsoft Excel 2013,
• Всё о ВПР: от первого применения до экспертного уровня,
• Гуру Excel расширяют горизонты: делайте невозможное с Microsoft Excel.

Скачать заметку в формате Word или pdf, примеры в формате Excel

Содержание

1. Начало работы
2. Функция СОРТ
3. Функция СОРТПО
4. Функция ФИЛЬТР
5. Функция УНИК
6. Комбинирование функций
7. Функция ПОСЛЕД
8. Функция СЛМАССИВ
9. Почему формулы массива (Ctrl+Shift+Enter) такие жесткие: неявное пересечение
10. Функция ПРОСМОТРX и динамические массивы
11. Обычные функции, использующие динамические массивы
12. Динамические массивы и функции кубов

Введение

Ранее многие люди пытались изучать формулы массива и потерпели неудачу. Майк Гирвин написал отличную книгу, чтобы объяснить, как работают формулы массива. Целые главы той книги здесь будут сведены к коротким предложениям, благодаря новым функциям.

О динамических массивах было объявлено 24 сентября 2018 года, но даже в MS Excel 2019 они пока не представлены. Динамические массивы доступны только в Office 365. Я думаю, что парадигма покупки бессрочной лицензии на Office каждые три или шесть лет устарела, и рекомендую переходить на подписку.

Как организована эта книга. В главе 1 вы узнаете о концепции формулы, распространяющейся на соседние ячейки. Вы увидите, как непустая ячейка может блокировать разлив массива, и как это исправить. Вы услышите о неявном пересечении и о том, как нотация @ может решить эту проблему. Главы 2–8 посвящены каждой из шести новых функций и их комбинациям. Глава 9 основана на превосходном видео Джо Макдэйда. Из этой главы вы узнаете термины, которые использует команда Excel Calc: Подъем, Трансляция, Попарный подъем, Усечение массива и Неявное пересечение. В главе 10 рассматриваются два способа, с помощью которых динамические массивы делают новую функцию ПРОСМОТРX более мощной. В 11-й – приведены 24 примера объединения динамических массивов с рядом обычных функций Excel. В 12-й главе показано, как динамические массивы могут работать с  функциями кубов.

Оригинальные файлы с примерами можно загрузить с сайта автора. К каждой главе я приложу файл, адаптированный к настоящему переводу.

Глава 1. Начало работы

Формулы теперь могу разливаться

В этой главе рассматривается новая формула =A2:A20, ошибка #ПЕРЕНОС! и новый неявный оператор пересечения @.

Начнем с базовой формулы массива. Перейдите в ячейку Е3. Наберите =A2:C10. В более ранних версиях Excel вам пришлось бы включить этот диапазон в качестве аргумента какой-нибудь функции, или использовать формулу массива, одновременно нажав Ctrl+Shift+Enter.

Рис. 1. Формула указывает на диапазон ячеек

Теперь же достаточно нажать Enter. Excel возвращает значения в 27 ячеек, которые выбираются автоматически вправо и вниз. Посмотрите на формулу в строке формул… здесь нет фигурных скобок, а это значит, что никто не нажимал Ctrl+Shift+Enter.

Рис. 2. Одна формула вернула множество значений

Ячейка E4 содержит текст Central, и, хотя строка формул показывает формулу для этой ячейки, она отображается серым цветом. Давайте проверим с помощью VBA, что содержится в ячейках Е3 и Е4?

Рис. 3. VBA подтверждает, что в ячейке Е4 не формула

VBA показывает, что в ячейке Е3 – формула, а в Е4 – нет. Также в Excel можно ввести формулу =ЕФОРМУЛА(E4). Она вернет ЛОЖЬ. И еще одна проверка. Выберете диапазон D1:h30, и пройдите по меню Главная –> Найти и заменить –> Формулы. Будет выделена только ячейка E3.

Один из первых вопросов на YouTube в ответ на мои первые видео с динамическими массивами был: можно ли вы копировать и вставлять значения? Да! Выберите диапазон E3:G11, нажмите Ctrl+C, кликните правой кнопкой мыши на выбранную новую ячейку и выберите Специальная вставка –> Значения.

Что происходит, если формула не может пролиться?

Что произойдет, если ячейка, куда должен разлиться диапазон, будет занята?

Рис. 4. Как Excel справится с занятой ячейкой?

Excel вернет ошибку #ПЕРЕНОС! Excel сообщает, что не может вернуть массив целиком. Поэтому не вернет ни одного результата. Если выбрать контекстное меню слева от ячейки с ошибкой, можно  выделить мешающие ячейки. Возможно, их получится перенести в другое место листа.

Рис. 5. Ошибка #ПЕРЕНОС! и ее контекстное меню

Как только вы очистите ячейки, мешающие размещению массива, он автоматически разольется.

Существует несколько типов ошибки #ПЕРЕНОС! Та, что выше, называется Диапазон для переноса данных не пустой. Другие ошибки:

• Неопределенный размер. Вы не можете использовать волатильные функции, например, СЛУЧМЕЖДУ(), в качестве аргумента функции ПОСЛЕДОВ().
• Выходит за пределы листа. Вы не можете ввести функцию =СОРТ(C:C) в ячейке E2.
• Табличная формула. Вы не можете использовать функции динамического массива внутри Таблицы.
• Не хватает памяти. Вам следует ссылаться на диапазон меньшего размера.
• Разлив в объединенные ячейки. Динамический массив не может разливаться в объединенную ячейку.
• Неопознанная ошибка. Excel не может распознать ошибку.

Если ваша формула указывает на Таблицу, динамический массив будет расширяться при добавлении новых строк в Таблицу

Рис. 6. Динамический массив «отслеживает» Таблицу

Сравните с рис. 2. Вы преобразовали диапазон А1:С19 в Таблицу (Ctrl+T). Формула в ячейке Е3 изменилась на =Таблица1. Теперь, если вы добавите еще одну строку в Таблицу (А11:С11), формула в ячейке Е3 не изменится, а динамический массив автоматически расширится еще одной строкой (Е12:G12).

Хотя формулы динамического массива могут указывать на Таблицу, в самой Таблице использовать формулы динамического массива нельзя.

Использование нового типа ссылок на массив: E3#

Как вы захотите сослаться на массив E3:G12, но не знаете, какого он размера, добавьте оператор разлитого диапазона (#) после ячейки, содержащей формулу массива.

Например, =E3 вернет East, =E3# вернет весь массив, формула которого хранится в E3. Неофициально это называется ссылочной нотацией массива. Такая нотация поддерживается только при ссылке внутри одной книги.

Что такое неявное пересечение

Если вы введете =@C2:C11 в любой ячейке в строках со 2 по 10, формула вернет значение из столбца С той строки, в которой вы ввели формулу. Знак @ известен как неявный оператор пересечения.

Рис. 7. Используйте нотацию @, когда вам нужно неявное пересечение.

Динамические массивы, функции и формулы Excel

Благодаря революционному обновлению механизма вычислений Excel 365 формулы массивов стали очень простыми и понятными для всех, а не только для суперпользователей. В учебнике объясняется концепция новых динамических массивов Excel и показано, как они могут сделать ваши рабочие листы более эффективными и намного проще в настройке.

Формулы массива Excel всегда считались прерогативой гуру и экспертов по формулам.

Если кто-то скажет: «Это можно сделать с помощью формулы массива», многие пользователи немедленно отреагируют: «А нет ли другого способа?».

Введение динамических массивов — долгожданное и долгожданное изменение. Благодаря своей способности работать с несколькими значениями простым способом, без каких-либо уловок и причуд, формулы динамического массива — это то, что каждый пользователь Excel может понять и получить удовольствие от создания.

• Динамические массивы Excel
• Доступность динамических массивов
• Функции динамического массива
• Примеры формул динамического массива
• Диапазон переполнения — одна формула, несколько ячеек
• Ссылка на диапазон разлива (символ #)
• Неявное пересечение и символ @
• Преимущества динамических массивов
• Ограничения динамических массивов
• Сравнение динамических массивов с традиционными формулами массивов CSE
• Обратная совместимость
• Формулы динамического массива Excel не работают

Динамические массивы Excel

Динамические массивы — это массивы с изменяемым размером, которые автоматически вычисляют и возвращают значения в несколько ячеек на основе формулы, введенной в одну ячейку.

За более чем 30-летнюю историю Microsoft Excel претерпел множество изменений, но одно осталось неизменным — одна формула, одна ячейка. Даже с традиционными формулами массива необходимо было вводить формулу в каждую ячейку, где должен появиться результат. С динамическими массивами это правило больше не выполняется. Теперь любая формула, возвращающая массив значений, автоматически переносится в соседние ячейки, и вам не нужно нажимать Ctrl + Shift + Enter или выполнять какие-либо другие действия. Другими словами, работать с динамическими массивами становится так же просто, как с одной ячейкой.

Позвольте мне проиллюстрировать концепцию очень простым примером. Допустим, вам нужно перемножить две группы чисел, например, для вычисления разных процентов.

В преддинамических версиях Excel приведенная ниже формула будет работать только для первой ячейки, если только вы не введете ее в несколько ячеек и не нажмете Ctrl + Shift + Enter, чтобы явно сделать ее формулой массива:

=A3:A5 *B2:D2

Теперь посмотрите, что происходит, когда та же формула используется в Excel 365. Вы вводите ее только в одну ячейку (в нашем случае B3), нажимаете клавишу Enter… и вся ярость заполняется результаты сразу:

Заполнение нескольких ячеек одной формулой называется разливом , а заполненный диапазон ячеек называется диапазоном заполнения.

Важно отметить, что недавнее обновление — это не просто новый способ обработки массивов в Excel. По сути, это революционное изменение всего механизма расчета. Благодаря динамическим массивам в библиотеку функций Excel было добавлено множество новых функций, а существующие стали работать быстрее и эффективнее. В конечном итоге предполагается, что новые динамические массивы полностью заменят устаревшие формулы массивов, которые вводятся с помощью сочетания клавиш Ctrl + Shift + Enter.

Доступность динамических массивов Excel

Динамические массивы были представлены на конференции Microsoft Ignite в 2018 г. и выпущены для подписчиков Office 365 в январе 2020 г. В настоящее время они доступны в подписках Microsoft 365 и Excel 2021.

Динамические массивы поддерживаются в этих версиях:

• Excel 365 для Windows
• Excel 365 для Mac
• Эксель 2021
• Excel 2021 для Mac
• Excel для iPad
• Excel для iPhone
• Excel для планшетов Android
• Excel для телефонов Android
• Excel для Интернета

Функции динамических массивов Excel

В рамках новой функциональности в Excel 365 было введено 6 новых функций, которые изначально обрабатывают массивы и выводят данные в диапазон ячеек. Вывод всегда динамичен — когда в исходных данных происходит какое-либо изменение, результаты обновляются автоматически. Отсюда и название группы —

функций динамического массива .

Эти новые функции легко справляются с рядом задач, которые традиционно считаются крепкими орешками. Например, они могут удалять дубликаты, извлекать и подсчитывать уникальные значения, отфильтровывать пробелы, генерировать случайные целые и десятичные числа, сортировать по возрастанию или убыванию и многое другое.

Ниже вы найдете краткое описание того, что делает каждая функция, а также ссылки на подробные учебные пособия:

1. UNIQUE — извлекает уникальные элементы из ряда ячеек.
2. ФИЛЬТР — фильтрует данные на основе заданных вами критериев.
3. SORT — сортирует диапазон ячеек по указанному столбцу.
4. SORTBY — сортирует диапазон ячеек по другому диапазону или массиву.
5. RANDARRAY — генерирует массив случайных чисел.
6. ПОСЛЕДОВАТЕЛЬНОСТЬ — генерирует список порядковых номеров.
7. TEXTSPLIT — разбивает строки по указанному разделителю между столбцами и/или строками.
8. TOCOL — преобразовать массив или диапазон в один столбец.
9. TOROW — преобразовать диапазон или массив в одну строку.
10. WRAPCOLS — преобразует строку или столбец в двумерный массив на основе указанного количества значений в строке.
11. WRAPROWS — преобразует строку или столбец в двумерный массив на основе указанного количества значений в столбце.
12. TAKE — извлекает указанное количество непрерывных строк или столбцов из начала или конца массива.
13. DROP — удаляет определенное количество строк или столбцов из массива.

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

XMATCH — это более универсальный преемник функции MATCH, который может выполнять вертикальный и горизонтальный поиск и возвращать относительное положение указанного элемента.

Формулы динамического массива Excel

В современных версиях Excel поведение динамического массива глубоко интегрировано и становится родным для всех функций , даже тех, которые изначально не были предназначены для работы с массивами. Проще говоря, для любой формулы, которая возвращает более одного значения, Excel автоматически создает диапазон с изменяемым размером, в который выводятся результаты. Благодаря этой способности существующие функции теперь могут творить чудеса!

В приведенных ниже примерах показаны новые формулы динамических массивов в действии, а также влияние динамических массивов на существующие функции.

Пример 1. Новая функция динамического массива

Этот пример демонстрирует, насколько быстрее и проще можно решить решение с помощью функций динамического массива Excel.

Чтобы извлечь список уникальных значений из столбца, вы обычно используете сложную формулу CSE, подобную этой. В динамическом Excel все, что вам нужно, это УНИКАЛЬНАЯ формула в ее базовой форме:

=УНИКАЛЬНАЯ (B2:B10)

. Вы вводите формулу в любую пустую ячейку и нажимаете Enter. Excel немедленно извлекает все разные значения из списка и выводит их в диапазон ячеек, начиная с ячейки, в которую вы ввели формулу (в нашем случае D2). При изменении исходных данных результаты автоматически пересчитываются и обновляются.

Пример 2.

Объединение нескольких функций динамического массива в одну формулу

Если нет возможности выполнить задачу с помощью одной функции, объедините несколько! Например, чтобы отфильтровать данные по условию и упорядочить результаты в алфавитном порядке, оберните функцию СОРТИРОВКА вокруг ФИЛЬТРА следующим образом:

= СОРТИРОВКА(ФИЛЬТР(A2:C13, B2:B13=F1, "Нет результатов"))

Где A2:C13 — исходные данные, B2:B13 — значения для проверки, а F1 — критерий.

Пример 3. Использование новых функций динамического массива вместе с существующими

Поскольку новый механизм вычислений, реализованный в Excel 365, может легко преобразовывать обычные формулы в массивы, ничто не мешает вам объединить новые и старые функции вместе.

Например, чтобы подсчитать количество уникальных значений в определенном диапазоне, вложите функцию динамического массива UNIQUE в старый добрый COUNTA:

=COUNTA(UNIQUE(B2:B10))

Пример 4.

Существующие функции поддерживают динамические массивы

Если указать диапазон ячеек для функции TRIM в более старой версии, например Excel 2016 или Excel 2019, она вернет один результат для первой ячейки:

= TRIM(A2:A6)

В динамическом Excel одна и та же формула обрабатывает все ячейки и возвращает несколько результатов, как показано ниже:

Пример 5. Формула ВПР для возврата нескольких значений

Как всем известно, функция ВПР Функция предназначена для возврата одного значения на основе указанного вами индекса столбца. Однако в Excel 365 вы можете указать массив номеров столбцов, чтобы получить совпадения из нескольких столбцов:

=ВПР(F1, A2:C6, {1,2,3}, ЛОЖЬ)

Пример 6. Формула ТРАНСП стала проще

В более ранних версиях Excel синтаксис функции ТРАНСП не оставлял места для ошибки. Чтобы повернуть данные на вашем листе, вам нужно было подсчитать исходные столбцы и строки, выбрать такое же количество пустых ячеек, но изменить ориентацию (невероятная операция в огромных рабочих листах!), ввести формулу ТРАНСП в выбранном диапазоне и нажмите Ctrl + Shift + Enter, чтобы завершить его правильно. Фу!

В динамическом Excel вы просто вводите формулу в крайнюю левую ячейку выходного диапазона и нажимаете Enter:

=ТРАНСП(A1:B6)

Готово!

Диапазон переноса — одна формула, несколько ячеек

Диапазон переноса — это диапазон ячеек, который содержит значения, возвращаемые формулой динамического массива.

При выборе любой ячейки в диапазоне разливов появляется синяя рамка, показывающая, что все внутри нее рассчитывается по формуле в верхней левой ячейке. Если вы удалите формулу в первой ячейке, все результаты исчезнут.

Диапазон разброса — действительно замечательная вещь, которая значительно облегчает жизнь пользователям Excel. Раньше при работе с формулами массива СПП нам приходилось угадывать, во сколько ячеек их копировать. Теперь вы просто вводите формулу в первую ячейку, а Excel позаботится обо всем остальном.

Примечание. Если какие-то другие данные блокируют диапазон разлива, возникает ошибка #SPILL. Как только мешающие данные будут удалены, ошибка исчезнет.

Дополнительные сведения см. в разделе Диапазон разливов Excel.

Ссылка на диапазон разлива (символ #)

Чтобы указать диапазон разлива, поместите хэш-тег или символ решетки (#) после адреса верхней левой ячейки диапазона.

Например, чтобы узнать, сколько случайных чисел сгенерировано формулой СЛУЧАЙ в A2, укажите ссылку на диапазон разброса для функции СЧЁТЗ: диапазон, использование:

=СУММ(A2#)

Советы:

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

Подробнее см. Оператор диапазона разлива.

Неявное пересечение и символ @

В динамическом массиве Excel есть еще одно существенное изменение в языке формул — введение символа @, известного как оператор неявного пересечения .

В Microsoft Excel неявное пересечение — это поведение формулы, сводящее множество значений к одному значению. В старом Excel ячейка могла содержать только одно значение, так что это было поведение по умолчанию, и для него не требовался специальный оператор.

В новом Excel все формулы по умолчанию рассматриваются как формулы массива. Неявный оператор пересечения используется для предотвращения поведения массива, если вы не хотите его использовать в конкретной формуле. Другими словами, если вы хотите, чтобы формула возвращала только одно значение, поставьте @ перед именем функции, и она будет вести себя как формула без массива в традиционном Excel.

Чтобы увидеть, как это работает на практике, взгляните на скриншот ниже.

В C2 есть формула динамического массива, которая распределяет результаты по многим ячейкам:

=UNIQUE(A2:A9)

В E2 перед функцией стоит символ @, который вызывает неявное пересечение. В результате возвращается только первое уникальное значение:

=@UNIQUE(A2:A9)

Дополнительные сведения см. в разделе Неявное пересечение в Excel.

Преимущества динамических массивов Excel

Несомненно, динамические массивы — одно из лучших усовершенствований Excel за последние годы. Как и у любой новой функции, у них есть сильные и слабые стороны. К счастью для нас, сильные стороны новых формул динамических массивов Excel просто огромны!

Простые и более мощные

Динамические массивы позволяют создавать более мощные формулы гораздо более простым способом. Вот несколько примеров:

• Извлечение уникальных значений: традиционные формулы | функции динамического массива
• Подсчет уникальных и различных значений: традиционные формулы | функции динамического массива
• Сортировка столбцов по алфавиту: традиционные формулы | функции динамического массива

Собственный для всех формул

В динамическом Excel вам не нужно беспокоиться о том, какие функции поддерживают массивы, а какие нет. Если формула может возвращать несколько значений, она будет делать это по умолчанию. Это также относится к арифметическим операциям и устаревшим функциям, как показано в этом примере.

Вложенные функции динамического массива

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

Относительные и абсолютные ссылки менее важны.

Благодаря подходу «одна формула, много значений» нет необходимости блокировать диапазоны знаком $, поскольку технически формула находится только в одной ячейке. Таким образом, по большей части не имеет значения, использовать ли абсолютные, относительные или смешанные ссылки на ячейки (что всегда было источником путаницы для неопытных пользователей) — формула динамического массива в любом случае будет давать правильные результаты!

Ограничения динамических массивов

Новые динамические массивы — это здорово, но, как и в случае с любой новой функцией, есть несколько предостережений и соображений, о которых вам следует знать.

Результаты не могут быть отсортированы обычным способом

Диапазон разброса, возвращаемый формулой динамического массива, не может быть отсортирован с помощью функции сортировки Excel. Любая такая попытка приведет к ошибке « Вы не можете изменить часть массива ». Чтобы упорядочить результаты от меньшего к большему или наоборот, оберните текущую формулу функцией СОРТИРОВКИ. Например, так вы можете одновременно фильтровать и сортировать.

Невозможно удалить какое-либо значение в диапазоне сброса

Ни одно из значений в диапазоне сброса нельзя удалить по той же причине: нельзя изменить часть массива. Такое поведение ожидаемо и логично. Традиционные формулы массива CSE также работают таким же образом.

Не поддерживаются в таблицах Excel

Эта функция (или ошибка?) довольно неожиданная. Формулы динамического массива не работают в таблицах Excel, только в пределах обычных диапазонов. Если вы попытаетесь преобразовать диапазон разброса в таблицу, Excel сделает это. Но вместо результатов вы увидите только #РАЗЛИВ! ошибка.

Не работает с Excel Power Query

Результаты формул динамического массива не могут быть загружены в Power Query. Скажем, если вы попытаетесь объединить два или более диапазонов сброса вместе с помощью Power Query, это не сработает.

Динамические массивы по сравнению с традиционными формулами массивов CSE

С введением динамических массивов мы можем говорить о двух типах Excel:

1. Динамический Excel , который полностью поддерживает динамические массивы, функции и формулы. В настоящее время это только Excel 365 и Excel 2021.
2. Устаревший Excel , также известный как традиционный или преддинамический Excel, где поддерживаются только формулы массива Ctrl + Shift + Enter. Это Excel 2019, Excel 2016, Excel 2013 и более ранние версии.

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

Вот самые существенные отличия:

• Формула динамического массива вводится в одну ячейку и завершается обычным нажатием клавиши Enter. Чтобы завершить старомодную формулу массива, вам нужно нажать Ctrl + Shift + Enter.
• Новые формулы массива автоматически переносятся во многие ячейки. Формулы СПП должны быть скопированы в диапазон ячеек, чтобы возвращать несколько результатов.
• Размер вывода формул динамического массива автоматически изменяется при изменении данных в исходном диапазоне. Формулы CSE усекают выходные данные, если возвращаемая область слишком мала, и возвращают ошибки в дополнительных ячейках, если возвращаемая область слишком велика.
• Формулу динамического массива можно легко редактировать в одной ячейке. Чтобы изменить формулу СПП, необходимо выделить и отредактировать весь диапазон.
• Невозможно удалять и вставлять строки в диапазон формул СПП — сначала необходимо удалить все существующие формулы. С динамическими массивами вставка или удаление строк не является проблемой.

Обратная совместимость: динамические массивы в прежних версиях Excel

Когда вы открываете книгу, содержащую формулу динамического массива в старой версии Excel, она автоматически преобразуется в обычную формулу массива, заключенную в {фигурные скобки}. Когда вы снова откроете лист в новом Excel, фигурные скобки будут удалены.

В прежних версиях Excel новые функции динамического массива и ссылки на диапазоны разлива получают префикс _xlfn, чтобы указать, что эта функция не поддерживается. Знак ссылки диапазона разброса (#) заменяется функцией ANCHORARRAY.

Например, вот как выглядит УНИКАЛЬНАЯ формула в Excel 2013 :

Большинство формул динамического массива (но не все!) будут отображать свои результаты в прежней версии Excel, пока вы не внесете в них какие-либо изменения. Редактирование формулы немедленно прерывает ее и отображает одно или несколько слов #ИМЯ? значения ошибок.

Формулы динамического массива Excel не работают

В зависимости от функции могут возникнуть различные ошибки, если вы используете неправильный синтаксис или недопустимые аргументы. Ниже приведены 3 наиболее распространенные ошибки, с которыми вы можете столкнуться при использовании любой формулы динамического массива.

#РАЗЛИВ! ошибка

Когда динамический массив возвращает несколько результатов, но что-то блокирует диапазон разлива, #SPILL! возникает ошибка.

Чтобы исправить ошибку, вам просто нужно очистить или удалить все ячейки в диапазоне разлива, которые не являются полностью пустыми. Чтобы быстро определить все мешающие ячейки, щелкните индикатор ошибки, а затем нажмите 9.0003 Выберите мешающие ячейки .

Помимо непустого диапазона разлива, эта ошибка может быть вызвана еще несколькими причинами. Для получения дополнительной информации см.:

• Ошибка Excel #SPILL — причины и исправления
• Как исправить ошибку #РАЗЛИВ! ошибка с ВПР, ПОИСКПОЗ ИНДЕКС, СУММЕСЛИ

#ССЫЛКА! ошибка

Из-за ограниченной поддержки внешних ссылок между книгами динамические массивы требуют, чтобы оба файла были открыты. Если исходная книга закрыта, #ССЫЛКА! отображается ошибка.

#ИМЯ? ошибка

#ИМЯ? ошибка возникает, если вы пытаетесь использовать функцию динамического массива в более старой версии Excel. Помните, что новые функции доступны только в Excel 365 и Excel 2021.

Если эта ошибка появляется в поддерживаемых версиях Excel, дважды проверьте имя функции в проблемной ячейке. Скорее всего опечатка 🙂

Вот как использовать динамические массивы в Excel. Надеюсь, вам понравится эта фантастическая новая функциональность! В любом случае, я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

Вас также может заинтересовать

Динамические массивы в R, Python и Julia

[Эта статья была впервые опубликована на Stat Of Mind и любезно предоставлена ​​R-блогерами]. (Вы можете сообщить о проблеме с содержанием на этой странице здесь)

---

Хотите поделиться своим контентом с R-блогерами? нажмите здесь, если у вас есть блог, или здесь, если у вас его нет.

Несмотря на то, что у меня большой опыт работы со статистикой (и поэтому я в основном пользуюсь R), я обнаружил, что мои общие знания в области компьютерных наук, как правило, недостаточны. Поэтому недавно я углубился в изучение структур данных, связанных с ними АТД и того, как их можно реализовать. В то же время я использую это как возможность поиграть с более незнакомыми языками, такими как Julia и, в меньшей степени, с Python.

В части 1 из 2 этой серии я исследовал некоторые свойства динамических массивов в R, Python и Julia. В частности, мне было интересно изучить взаимосвязь между длиной массива и его размером в байтах и ​​то, как это обрабатывается разными языками. Для этого я написал чрезвычайно простые функции, которые рекурсивно добавляли целое число (для этого число 1) к вектору (или одномерному массиву) и извлекали его размер в байтах на каждом шаге.

В Python:

 из sys import getsizeof
  Размер защиты (n):
  данные = []
  для я в диапазоне (n):
    data. append(1)
    напечатать '%s,%s' % (len(данные), getsizeof(данные))
 

В Джулии:

 размер функции (n)
  данные = Int64[];
  для я = 1: п
    нажать!(данные, 1)
    @printf "%d,%d\n" i sizeof(данные)
  конец
конец
 

В R:

 'размер' <- function(n)
{
  данные <- с()
  для (я в 1:100)
  {
    данные <- c(данные, 1)
    print(sprintf('%s,%s', i, object.size(data)))
  }
}
 

Вызов каждой из этих функций с использованием n=100 дает следующий график

Результаты этого эксперимента поразительны. Python начинается с пустого массива размером 72 байта, который увеличивается до 104, как только элемент добавляется к массиву. Поэтому Python автоматически добавляет (104-72) = 32 = 4 x 8 байт. Это означает, что Python расширяет массив таким образом, что он может хранить на четыре ссылки больше, чем требуется на самом деле. К 5-й вставке мы добавили (136-72) = 64 = 8 x 8 байт. Это происходит так, что шаблон роста происходит в точках 4, 8, 16, 25, 35, 46, 58, 72, 88. Интересно, вы можете заметить, что позиция i, в которой массив расширяется, может быть связана с точками в который сам массив растет через отношение i = (number_of_bytes_added_at_i – 72) / 8.

Для R я нашел эту ссылку, которая отлично объясняет механизмы управления памятью R-векторов.

Наконец, похоже, что функция push!() в Julia не выполняет никакого упреждающего динамического перераспределения памяти. Однако я заметил, что повторный вызов push!() был быстрым (подробнее об этом позже), но не постоянным. Дальнейшее исследование привело меня к источнику C для добавления к массивам в Julia, который предполагает, что Julia время от времени выполняет экспоненциальное перераспределение буфера (хотя я не уверен, поэтому, пожалуйста, поправьте, если ошибаюсь!).

Далее я рассмотрел эффективность каждого языка при работе с динамическими массивами. В частности, меня интересовало, как быстро каждый язык может добавлять значения к существующему массиву.

Результаты ясно показывают, что Julia намного быстрее, чем Python и R. Следует отметить, что при определении функции в Julia первый проход фактически скомпилируется и запустится. Поэтому последующие вызовы обычно выполняются быстрее, чем первый (важный факт, который следует учитывать при оценке производительности). В целом кажется, что утверждения, сделанные сообществом Julia, верны в этом контексте, а именно, что Julia намного быстрее, чем Python и R (в конечном итоге будет добавлен тест C++ 🙂)

К оставьте комментарий для автора, пройдите по ссылке и оставьте комментарий в их блоге: Stat Of Mind .

---

R-bloggers.com предлагает ежедневных обновлений по электронной почте новостей R и руководств по изучению R и многим другим темам. Нажмите здесь, если вы хотите опубликовать или найти работу R/data-science.

---

Хотите поделиться своим контентом с R-блогерами? нажмите здесь, если у вас есть блог, или здесь, если у вас его нет.