Эксель массив: что такое, формула, как сделать / Skillbox Media

VBA Excel. Диапазон ячеек и массив (обмен значениями)

Копирование значений из диапазона ячеек в массив и обратно с помощью VBA Excel. Простейшие примеры обмена значениями между диапазоном и массивом.

Как известно, VBA обрабатывает информацию в массивах значительно быстрее, чем в ячейках рабочего листа Excel. Поэтому, при работе с большими объемами данных, удобнее использовать массивы, чем наблюдать во время выполнения кода за мерцанием изображения на экране или просто смотреть в неизменную картинку, если обновление экрана отключено (Application.ScreenUpdating = False). Здесь обмен значениями между массивом и диапазоном ячеек будет вполне уместен.

Копирование значений из диапазона ячеек в массив

Чтобы скопировать значения из диапазона ячеек в массив, необходимо объявить переменную универсального типа (As Variant) и присвоить ей значения диапазона ячеек с помощью оператора присваивания (=):

Dim a As Variant a = Range(«A1:C3»)

VBA Excel автоматически преобразует объявленную переменную в двумерный массив, соответствующий размерности диапазона ячеек, в нашем случае в массив — a(1 To 3, 1 To 3), и заполняет его значениями. Нумерация измерений массивов, созданных таким образом, начинается с единицы (1).

Можно, в этом случае, объявить сразу динамический массив, чтобы изначально указать, что эта переменная будет массивом. Так как свойством диапазона ячеек по умолчанию в VBA Excel является значение (Value), его можно в коде явно не указывать, но, при желании, можно и указать. Получится такая конструкция, аналогичная первой:

Dim a() As Variant a = Range(«A1:C3»).Value

Стоит отметить, что для копирования значений из диапазона ячеек в массив можно использовать только обычную переменную или динамический массив универсального типа (Variant). VBA Excel автоматически преобразовывает их в двумерный массив. Если объявить двумерный массив с указанной заранее размерностью, использовать его не получится, будет сгенерирована ошибка с сообщением: Can’t assign to array (Нельзя назначать массив).

Копирование значений из массива в диапазон ячеек

Значения в диапазон ячеек добавляются из массива с помощью оператора присваивания (=):

Range(«A6:F15») = a ‘или Range(«A6:F15»). Value = a ‘где a — переменная двумерного массива

Обратите внимание, что вставить значения в диапазон ячеек можно только из двумерного массива. Размерность такого массива может начинаться с нуля (0). Количество элементов в измерениях массива должно совпадать с количеством строк и столбцов в диапазоне ячеек. Если вам нужно вставить значения в одну строку или в один столбец, укажите размерность единственной строки или единственного столбца как (0) или (1 To 1), если вы хотите использовать нумерацию измерений своего массива с единицы. Например, для записи десяти значений из массива в одну строку можно объявить такой массив — massiv(9, 0), или в один столбец — massiv(0, 9).

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

Обмен значениями между двумя диапазонами

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

Range(«B2:D6») = Range(«G7:I11»).Value

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

Если диапазон ячеек, принимающий значения, по размеру меньше диапазона-источника, то он будет заполнен полностью:

Range(«B2:D6») = Range(«G5:L13»).Value

Если принимающий диапазон ячеек по размеру больше передающего, то часть его будет заполнена значениями диапазона-источника, а остальные ячейки — значениями #Н/Д:

Range(«B2:D6») = Range(«G7:H9»).Value

Простейшие примеры обмена значениями

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

Пример 1

Заполнение двумерного массива значениями и и их присвоение диапазону ячеек на рабочем листе Excel:

1 2 3 4 5 6 7 8 9 10 11 12 13

Sub Test1() Dim a(2, 2) As Variant a(0, 0) = «телепузик» a(0, 1) = «журналист» a(0, 2) = «ящерица» a(1, 0) = «короед» a(1, 1) = «утенок» a(1, 2) = «шмель» a(2, 0) = 200 a(2, 1) = 300 a(2, 2) = 400 Range(«A1:C3»).Value = a End Sub

В данном случае переменная массива не обязательно должна быть универсального типа (As Variant), например, если бы в нее записывались только текстовые данные, ее можно было бы объявить как строковую (As String), и все бы работало.

Пример 2

Объявление обычной переменной универсального типа, присвоение ей значений из диапазона ячеек «A1:C3», записанных кодом первого примера, и вставка этих значений из полученного двумерного массива в диапазон «D10:F12»:

Sub Test2() Dim a As Variant a = Range(«A1:C3») Range(«D10:F12») = a End Sub

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

Пример 3

Допустим, на рабочем листе «Лист1» в ячейках «A1:A5» записано количество какого-то товара, а в ячейках «B1:B5» — его цена. Необходимо к этой информации добавить сумму каждого товара, умножив количество на цену, и перенести данные на «Лист2».

1 2 3 4 5 6 7 8 9

Sub Test3() Dim a As Variant, i As Long   a = Лист1.Range(«A1:C5»)     For i = 1 To 5       a(i, 3) = a(i, 1) _       * a(i, 2)     Next   Лист2.Range(«A1:C5») = a End Sub

Массив создан сразу с размерностью 5×3 с элементами под суммы. Даже если на первом листе в ячейках «C1:C5» есть какие-то значения, в массиве они будут перезаписаны результатами вычислений.

Копирование значений из массива в массив

Этот пример показывает, как в VBA Excel можно скопировать значения из одного массива в другой:

1 2 3 4 5 6

Sub Test4() Dim arr1, arr2     arr1 = Range(«G7:I11»)     arr2 = arr1     Range(«B2:D6») = arr2 End Sub

---

Содержание рубрики VBA 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 — формулы массива

Также известны как формулы CSE или (Ctrl + Shift + Enter) Формулы.
Формула массива — это формула особого типа, специально предназначенная для работы с массивами.
Формула массива может давать один результат или несколько результатов , где каждый результат отображается в отдельной ячейке.
Формула массива может занимать одну ячейку или диапазон ячеек.
Массив — это просто набор данных одного типа, которые можно рассматривать как единый объект. Это хранится в памяти.
Для тех из вас, кто не знаком с концепцией массива, это просто набор значений (в данном случае обычно чисел).
Excel автоматически заключает фигурных скобок {} вокруг любых формул массива. Эти скобки нельзя ввести вручную .

Пример 1 — В таблице ниже показано, как можно использовать константу массива.

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

Пример 2 — В таблице ниже показано больше констант массива. Пример 3

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

Преимущества использования формул массива

Формулы массива гарантируют, что все формулы одинаковы, и это хороший способ обеспечить согласованность ваших формул.
Использование формул массива может сократить время, затрачиваемое на вычисление формул, поскольку массивы можно хранить в памяти.
Рабочая книга, содержащая формулы массива, будет означать, что формулы будут более эффективными и будут использовать меньше памяти, что поможет уменьшить размер файла.
Формулы массива помогают защитить ваши формулы, поскольку вы не можете изменять, очищать или перемещать отдельные ячейки, являющиеся частью блока массива. Невозможно изменить содержимое одной ячейки в формуле массива. Единственный способ изменить отдельные ячейки — рассматривать весь блок как единое целое и изменять весь блок одновременно.
Вы можете удалить формулу массива, только выбрав сначала весь блок массива. Вы не можете удалить его часть.
Использование формулы массива почти всегда будет быстрее, чем определение пользовательской функции с помощью VBA.
Рабочий лист, содержащий формулы массива, будет труднее понять, а это означает, что новички с меньшей вероятностью изменят формулы, особенно если они не понимают, как они работают.
Формулы массива позволяют выполнять некоторые вычисления, которые в противном случае были бы невозможны с использованием традиционных формул.

Недостатки использования формул массива

Следует избегать использования большого количества формул массива в одной книге. Это снизит производительность вашей рабочей книги, а также сделает ваши формулы кошмаром для понимания.
Для многих формул массива замедляется пересчет, сохранение, открытие и закрытие.
Формулы массива используют меньше памяти, и они могут устранить необходимость в промежуточных формулах, хотя они могут замедлить время пересчета вашей электронной таблицы и сделать ее более сложной.
В формулах массива нельзя использовать ссылки на столбцы (например, «A:A» или «D:D»).
Вы всегда должны вводить их с помощью (Ctrl + Shift + Enter).

Важно

Использование большого количества формул массива в одной книге увеличивает время, необходимое для пересчета.
Большинство вычислений, которые можно выполнить с помощью формул массива, также можно выполнить с помощью обычных формул. Хотя есть несколько исключений.
Если вы случайно введете формулу массива, не нажимая (Ctrl + Shift + Enter), формула либо вернет неверное значение, либо #ЗНАЧ! будет возвращен.
Для быстрого выбора ячеек, содержащих блок массива, вы можете использовать горячую клавишу (Ctrl + / ) .
Группа ячеек, занятая одной и той же формулой массива, часто называется Блок массива или диапазон массива.
Вы не можете изменить ни одну из отдельных ячеек в блоке массива.

В формулах массива нельзя использовать ссылки на столбцы (например, «A:A» или «D:D»).
Количество строк всегда должно быть одинаковым во всех диапазонах ячеек. Это делается для того, чтобы все используемые временные массивы имели одинаковую длину.

---

© 2023 Better Solutions Limited. Все права защищены. © 2023 Better Solutions LimitedTopPrevNext

Динамические массивы, часть I. Самое большое изменение в Excel за последние 9 лет0001

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

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

 

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

 

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

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

 

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

Новые функции 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 Group всегда считает, что для достижения желаемого следует использовать самые простые и понятные функции Excel. Хотя динамические массивы добавляют интересные новые способы анализа данных, следует подумать о том, являются ли динамические массивы самым простым возможным подходом.

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

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