Копирование значений из диапазона ячеек в массив и обратно с помощью VBA Excel. Простейшие примеры обмена значениями между диапазоном и массивом.
Как известно, VBA обрабатывает информацию в массивах значительно быстрее, чем в ячейках рабочего листа Excel. Поэтому, при работе с большими объемами данных, удобнее использовать массивы, чем наблюдать во время выполнения кода за мерцанием изображения на экране или просто смотреть в неизменную картинку, если обновление экрана отключено (Application.ScreenUpdating = False). Здесь обмен значениями между массивом и диапазоном ячеек будет вполне уместен.
Копирование значений из диапазона ячеек в массив
Чтобы скопировать значения из диапазона ячеек в массив, необходимо объявить переменную универсального типа (As Variant) и присвоить ей значения диапазона ячеек с помощью оператора присваивания (=):
|
1 2 |
Dim a As Variant a = Range("A1:C3") |
VBA Excel автоматически преобразует объявленную переменную в двумерный массив, соответствующий размерности диапазона ячеек, в нашем случае в массив — a(1 To 3, 1 To 3), и заполняет его значениями. Нумерация измерений массивов, созданных таким образом, начинается с единицы (1).
Можно, в этом случае, объявить сразу динамический массив, чтобы изначально указать, что эта переменная будет массивом. Так как свойством диапазона ячеек по умолчанию в VBA Excel является значение (Value), его можно в коде явно не указывать, но, при желании, можно и указать. Получится такая конструкция, аналогичная первой:
|
1 2 |
Dim a() As Variant a = Range("A1:C3").Value |
Стоит отметить, что для копирования значений из диапазона ячеек в массив можно использовать только обычную переменную или динамический массив универсального типа (Variant). VBA Excel автоматически преобразовывает их в двумерный массив. Если объявить двумерный массив с указанной заранее размерностью, использовать его не получится, будет сгенерирована ошибка с сообщением: Can’t assign to array (Нельзя назначать массив).
Копирование значений из массива в диапазон ячеек
Значения в диапазон ячеек добавляются из массива с помощью оператора присваивания (=):
|
1 2 3 4 |
Range("A6:F15") = a 'или Range("A6:F15").Value = a 'где a - переменная двумерного массива |
Обратите внимание, что вставить значения в диапазон ячеек можно только из двумерного массива. Размерность такого массива может начинаться с нуля (0). Количество элементов в измерениях массива должно совпадать с количеством строк и столбцов в диапазоне ячеек. Если вам нужно вставить значения в одну строку или в один столбец, укажите размерность единственной строки или единственного столбца как (0) или (1 To 1), если вы хотите использовать нумерацию измерений своего массива с единицы. Например, для записи десяти значений из массива в одну строку можно объявить такой массив — massiv(9, 0), или в один столбец — massiv(0, 9).
Для вставки значений в диапазон ячеек из массива идеально подойдет массив, созданный для копирования в него значений из диапазона. В этом случае, данные с рабочего листа Excel переносятся в массив, обрабатываются и, после обработки, вставляются обратно в ту же или другую таблицу на том же или другом рабочем листе.
Обмен значениями между двумя диапазонами
Обмен значениями можно осуществить в VBA Excel не только между массивом и диапазоном, но и между двумя диапазонами одинаковой размерности:
|
1 |
Range("B2:D6") = Range("G7:I11").Value |
У диапазона, являющегося источником значений, обязательно должно быть указано свойство Value.
Если диапазон ячеек, принимающий значения, по размеру меньше диапазона-источника, то он будет заполнен полностью:
|
1 |
Range("B2:D6") = Range("G5:L13").Value |
Если принимающий диапазон ячеек по размеру больше передающего, то часть его будет заполнена значениями диапазона-источника, а остальные ячейки — значениями #Н/Д:
|
1 |
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»:
|
1 2 3 4 5 |
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 |
Здравствуйте, Евгений!
Здравствуйте, коллеги!
Не пойму, как без циклов присвоить массиву значения из разорванного диапазона.
Приводит к созданию массива со вторым размером =1, C1:C3 не включаются.
прошу подсказки, куда смотреть. Спасибо.
Здравствуйте, Евгений!
Здравствуйте, коллеги!
Загрузка с листа в массив. Почему код
Sub w()Dim q() As String
q = Range("A1:A3")
End Sub
приводит к Type mismatch при любом типе A1:A3?
Лечится только заменой на As Variant, а это при миллионе строк уже влияет на память/скорость.
Есть одномерный массив 1,2,3,4 как его переписать в обратном порядке 4, 3,2,1?
Добрый день, Владислав!
Перезаписать значения элементов массива в обратном порядке можно через второй массив:
Евгений, благодарю за помощь.
Евгений, ни как не получается построить программу — свертка двух функций — два одномерных массива.
Например: задано t — текущее время, первому элементу перового и второго массива соответствует время 1,
второму — 2 и тд до значения M — это размер массива.
Второй массив представляем в обратном порядке, что Вы мне подсказали как сделать, спасибо.
Теперь второй массив «наезжает» на первый. При первом цикле (t=0) первый элемент первого массива перемножается со значением последнего элемента второго массива, значению присваивается результат перемножения.
При втором цикле значение первого элемента умножается на значение предпоследнего элемента второго массива, значение второго элемента первого массива, умножается на значение последнего элемента второго массива, результаты складывается, результат сложения (t=1) — это второй элемент свертки и так до конца. Конец — это когда первый элемент второго массива и последний элемент второго пересекутся., пересекутся. В результате тоже получается массив.
Желательно чтобы массивы можно было задавать из таблички Excel. И результат тоже бы выводился в табличку.
Сама идея приведена наглядно здесь — https://ru.wikipedia.org/wiki/Свёртка_(математический_анализ) .
Еще в Питоне есть такая функция, Ссылка на функцию свертки библиотеки NumPy в Python —
https://runebook.dev/ru/docs/numpy/reference/generated/numpy.convolve
Но не могу подружить её с Excel/
Благодарю, у самого не хватает опыта.
Обсуждение закрыто.