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

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

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2016 Excel для iPad Excel для iPhone Excel для планшетов с Android Excel для телефонов с Android Еще…Меньше

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

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

Ниже приведены несколько заметок, которые помогут вам в понимании и использовании формул такого типа. 

Что означает перенос?

Примечание: Более старые формулы массива, известные как устаревшие формулы массива, всегда возвращают результат фиксированного размера — они всегда переносятся в одно и то же количество ячеек.

Поведение при переносе, описанное в этом разделе, не применяется к устаревшим формулам массива.

Перенос означает, что формула привела к нескольким значениям, и эти значения были помещены в смежные ячейки. Например, =SORT(D2:D11;1,-1),, сортирующая массив в порядке убывания, возвращает соответствующий массив высотой 10 строк. Но вам нужно только ввести формулу в верхнюю левую ячейку или в данном случае F2, и она автоматически переместится в ячейку F11.

Ключевые моменты

• При нажатии на ВВОД для подтверждения формулы Excel динамически изменит размер диапазона вывода и поместит результаты в каждую ячейку в этом диапазоне.

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

• Формулы перенесенного массива не поддерживаются в самих таблицах Excel, поэтому их следует размещать в сетке за пределами таблицы. Таблицы лучше всего подходят для хранения строк и столбцов независимых данных.

• После ввода формулы перенесенного массива при выделении любой ячейки в области переноса Excel выделит рамкой границу диапазона. Граница исчезнет при выборе ячейки за пределами области.

• Редактировать можно только первую ячейку в области переноса. При выборе другой ячейки в области переноса, формула будет видна в строке формул, но текст будет «фантомным» и недоступным для изменения. При необходимости обновить формулу, необходимо выбрать верхнюю левую ячейку в диапазоне массива, изменить ее по мере необходимости. При нажатии Enter Excel автоматически обновит остальную часть области переноса.

• Перекрытие формул. Формулы массива нельзя ввести, если что-то блокирует выходной диапазон. и если это происходит, Excel возвращает ошибку #SPILL!, указывающую на наличие блокировки. Если удалить помеху, формула будет перенесена должным образом. В приведенном ниже примере выходной диапазон формулы перекрывает другой диапазон с данными и отображается с пунктирной рамкой, перекрывающей ячейки со значениями, указывающими, что он не может быть перенесен. Удалите блокирующие данные или скопируйте их в другое место, и формула перенесется, как и ожидалось.

• Устаревшие формулы массива, введенные с помощью CTRL+SHIFT+ENTER (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 для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2016 Excel для iPad Excel для iPhone Excel для планшетов с Android Excel для телефонов с Android Дополнительно… Меньше

Формулы Excel, которые возвращают набор значений, также известный как массив, возвращают эти значения в соседние ячейки. Такое поведение называется

проливом .

Формулы, которые могут возвращать массивы переменного размера, называются формулами динамического массива . Формулы, которые в настоящее время возвращают массивы, которые успешно переносятся, могут называться формулами перенесенного массива .

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

Что означает разлив?

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

Разброс означает, что формула привела к нескольким значениям, и эти значения были помещены в соседние ячейки. Например, =SORT(D2:D11,1,-1), , которая сортирует массив в порядке убывания, вернет соответствующий массив из 10 строк. Но вам нужно всего лишь ввести формулу в верхнюю левую ячейку или в данном случае F2, и она автоматически переместится в ячейку F11.

Ключевые точки

• Когда вы нажмете Enter, чтобы подтвердить свою формулу, Excel динамически изменит размер выходного диапазона и поместит результаты в каждую ячейку в этом диапазоне.

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

• org/ListItem»>

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

• После того, как вы введете формулу разнесенного массива, при выборе любой ячейки в области разброса Excel поместит выделенную рамку вокруг диапазона. Граница исчезнет, ​​когда вы выберете ячейку за пределами области.

• Доступна для редактирования только первая ячейка в области разлива. Если вы выберете другую ячейку в области разлива, формула будет видна в строке формул, но текст будет «замаскирован» и не может быть изменен. Если вам нужно обновить формулу, вы должны выбрать верхнюю левую ячейку в диапазоне массива, изменить ее по мере необходимости, тогда Excel автоматически обновит остальную часть области разлива для вас, когда вы нажмете Введите .

• Перекрытие формул — формулы массива не могут быть введены, если что-то блокирует выходной диапазон. и если это произойдет, Excel вернет #SPILL! Ошибка , указывающая на наличие блокировки. Если вы удалите блокировку, формула выльется, как и ожидалось. В приведенном ниже примере выходной диапазон формулы перекрывает другой диапазон с данными и показан с пунктирной рамкой, перекрывающей ячейки со значениями, указывающими, что он не может перетекать. Удалите блокирующие данные или скопируйте их куда-нибудь еще, и формула выльется, как и ожидалось.

• Устаревшие формулы массива, введенные с помощью CTRL+SHIFT+ENTER (CSE), по-прежнему поддерживаются по соображениям обратной совместимости, но их больше нельзя использовать. При желании вы можете преобразовать устаревшие формулы массива в формулы динамического массива, найдя первую ячейку в диапазоне массива, скопировав текст формулы, удалив весь диапазон устаревшего массива, а затем повторно введя формулу в верхней части левая ячейка. Перед обновлением устаревших формул массива до формул динамического массива вы должны знать о некоторых различиях в вычислениях между ними.

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

Нужна дополнительная помощь?

Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществе ответов.

См. также

ФИЛЬТР

Функция СЛУЧАЙ

ПОСЛЕДОВАТЕЛЬНОСТЬ

Функция СОРТИРОВКИ

СОРТИРОВАТЬ функцию

УНИКАЛЬНАЯ функция

#ПРОЛИВАТЬ! ошибки в экселе

Неявный оператор пересечения: @

90 000 динамических массивов, часть I — самое большое изменение в Excel за 90 000 годов1

В The Marquee Group мы гордимся тем, что являемся пользователями Microsoft Excel «старой школы», начиная с версий популярного программного обеспечения, выпущенных в начале 1990-х годов (хотя эти конкретные пользователи могут этого не признавать). Изменения в программном обеспечении Excel, как правило, происходят медленно, поэтапно и почти всегда обратно совместимы (одно из требований поддержки программного обеспечения, используемого сотнями миллионов людей во всем мире). Однако в последних версиях Excel (в «Current Channel» Microsoft 365 с января 2020 г.) появилась новая функция, которая принципиально меняет способ использования электронных таблиц в будущем.

Раньше вы вводили в ячейку одну формулу, и она возвращала один результат. Если вам нужны дополнительные результаты, вам нужно будет скопировать формулу вниз. Например:

 

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

 

Опытные пользователи Excel заметят, что это похоже на предыдущее поведение массива в Excel (т. е. формулы, которые вы вводили в диапазоне ячеек, нажимая CTRL + SHIFT + ENTER). Динамические массивы значительно упрощают и расширяют использование этого поведения и позволяют избежать многих недостатков классической функции массивов в Excel.

Ключом к пониманию этой новой функции является понимание того, что вы вводите формулу только в одну ячейку (в приведенном выше примере в ячейку E4). Результаты формулы «выливаются» из ячейки для создания динамического массива. Ячейки ниже E4 (например, ячейка E5) содержат информацию, но не имеют фактической формулы. Если вы выберете ячейку, вы увидите серый текст в строке формул, показывающий, что ячейка содержит результаты, «перенесенные» из формулы в другое место.

 

На первый взгляд эти формулы выглядят как альтернатива копированию по диапазону. Но Microsoft также представила ряд новых функций, которые используют это поведение (и есть несколько старых функций, которые также могут использовать эту функциональность). Ключом к использованию этих новых функций является понимание того, что размер результирующего динамического массива может автоматически измениться на на в зависимости от значений, которые вы указываете в формуле.

Новые функции Excel с использованием динамических массивов

1. UNIQUE: возвращает динамический массив со списком уникальных значений в списке или диапазоне (обычная задача, которая была намного сложнее со «стандартными» функциями Excel).  
2. ФИЛЬТР: Создать динамический массив, отфильтрованный на основе критериев, которые можно изменить.
3. SORT: Сортировка содержимого диапазона с возможностью указания столбца для сортировки в порядке возрастания или убывания.
4. SORTBY: Сортировка содержимого диапазона с возможностью сортировки по нескольким столбцам в порядке возрастания или убывания.
5. ПОСЛЕДОВАТЕЛЬНОСТЬ: Создайте динамический массив с последовательным списком чисел с возможностью установки начального значения и «шага» для каждой последовательности.
6. RANDARRAY: Создать динамический массив со списком случайных чисел (целых или десятичных чисел)

Динамические массивы с функцией OFFSET

Одним из наиболее полезных применений динамических массивов является создание списков, которые автоматически обновляются в сочетании с функцией СМЕЩ в Excel. Функция смещения Excel будет начинаться со ссылочной ячейки, перемещать определенное количество строк или столбцов в любом направлении и возвращать значение в этой ячейке.

OFFSET также имеет два дополнительных аргумента, которые позволяют вам возвращать массив значений определенной ширины или высоты. До динамических массивов эти функции OFFSET использовались с динамическими именованными диапазонами (тема для другого дня и описана в курсе Marquee Excel 2: расширенный анализ данных) или с предыдущим поведением массива Excel (CTRL + SHIFT + ENTER). Благодаря динамическим массивам большая часть возможностей OFFSET была раскрыта благодаря возможности отображать чрезвычайно настраиваемые списки из больших наборов данных. В приведенном ниже примере мы используем формулу OFFSET, которая позволит нам отобразить первые значения X в списке (7 в нашем примере). Это можно изменить на лету, и список будет автоматически обновляться.

 

НО ПОДОЖДИТЕ! Меня устраивают новые функции, но я хочу, чтобы моя функция OFFSET в Excel работала так же, как в 2003 году! Никогда не бойтесь, смотрите примечание внизу этой статьи, чтобы узнать, как это сделать.

Возможные подводные камни динамических массивов

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

1. Ошибки SPILL: изменение поведения, при котором одна формула может возвращать результаты в диапазоне ячеек, приводит к некоторым новым проблемам. Вы можете обнаружить, что Excel не может вернуть запрошенную вами информацию. Это приведет к #РАЗБИВКА! ошибка. Причины, по которым вы можете получить ошибку переноса, включают:
   — диапазон переноса уже содержит другую информацию
   — Excel не может определить размер переносимого массива, поскольку он изменчив (т. е. используется случайное число для определения размера динамического массива
   – Результат выходит за пределы рабочей книги
   – Формула находится в таблице (формулы динамических массивов не будут работать внутри таблицы, но формулы динамического массива могут ссылаться на таблицу)
   – Результат слишком велик и В Excel не хватило памяти
    – Результат переносится в объединенную ячейку (еще одна причина избегать использования объединенных ячеек в электронных таблицах)
2. Обратная совместимость: динамические массивы доступны только в версиях Microsoft 365 Excel (Current Channel) и недоступны в Excel 2019/2016 (или более ранние версии). Если вы сохраните лист с динамическими массивами и кто-то попытается использовать лист в более старой версии Excel, динамические массивы будут преобразованы в традиционные массивы. Это может привести к потере функциональности или неожиданному поведению.
3. Создание ненужных сложностей: Группа Marquee всегда считает, что для достижения желаемого следует использовать самые простые и понятные функции Excel. Хотя динамические массивы добавляют интересные новые способы анализа данных, следует подумать о том, являются ли динамические массивы самым простым возможным подходом.

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

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