Копирование массива данных в эксель
Табличный редактор Excel предназначен для обработки массивов информации, представленных в виде таблиц всевозможных значений. Одним из наиболее частых видов такой обработки является копирование. Например, если имеется какой-то исходный массив данных, и необходимо сделать какие-то расчеты, требующие дополнительных столбцов или строк, не всегда удобно добавлять их прямо в исходную таблицу. Она может потребоваться и для других целей. Поэтому разумным решением будет скопировать все данные или их часть в новый лист или документ, и делать все преобразования с копией. Таким образом, исходный документ останется нетронутым. Какими способами можно это сделать?
Простое копирование без изменений
Этот способ наиболее прост в использовании, он удобен, если в исходной таблице представлены простые данные без формул и ссылок.
Обратите внимание! Простое копирование ничего не меняет в исходной информации.
Если исходная информация содержит формулы, они будут скопированы вместе с остальными данными, и здесь следует быть аккуратным – относительные ссылки при копировании начинают ссылаться на совсем другие ячейки, где могут быть расположены некорректные данные. Поэтому простое копирование данных с формулами предпочтительно только лишь в том случае, когда все источники ссылок для формул копируются одновременно. Такой метод включает следующие шаги.
- Выделение ячеек. Как правило, используется либо указание диапазона ячеек левой кнопкой мыши, либо комбинация клавиатуры «Shift+стрелка». В результате часть ячеек листа обводится черной рамкой, и они дополнительно подсвечиваются темным оттенком.
- Копирование в буфер обмена. Буфер обмена – это специальная область в памяти компьютера, предназначенная для перенесения данных внутри приложения или между приложениями. Копирование в нее воспроизводится либо нажатием клавиш «Ctrl+C», либо «Ctrl+Insert» (эти комбинации эквивалентны). Также его возможно осуществить через соответствующий пункт контекстного меню или с помощью ленты программы.
- Указание места для вставки. Переходим туда, куда мы хотим скопировать данные, и указываем курсором ячейку, которая будет верхней левой ячейкой вставляемых данных. Следует быть осторожным, если место для вставки уже содержит какие-то данные. Они могут быть стерты.
- Вставка содержимого буфера обмена в указанную область. Это делается клавишами «Ctrl+V» либо «Shift+Insert» либо соответствующим пунктом контекстного меню или ленты программы.
Если нужны только значения
Очень часто информация в ячейках является результатом вычислений, при которых используются ссылки на соседние ячейки. При простом копировании таких ячеек оно будет производиться вместе с формулами, и это изменит нужные значения.
В этом случае следует копировать только значения ячеек. Как и в прошлом варианте, сперва выбирается необходимый диапазон, но для копирования в буфер обмена используем пункт контекстного меню «параметры вставки», подпункт «только значения». Также можно использовать соответствующую группу в ленте программы. Остальные шаги по вставке скопированных данных остаются прежними. А в результате в новом месте появятся только значения нужных ячеек.
Важно! Формулы и форматы при таком способе не сохраняются.
Это может быть как удобством, так и помехой, в зависимости от ситуации. Чаще всего форматирование (особенно сложное) требуется оставить. В этом случае можно воспользоваться следующим способом.
Копирование только значений
Когда нужны и значения, и форматы
Выбор ячеек для такого способа копирования остается прежним, но оно осуществляется либо с помощью контекстного меню (пункт «Специальная вставка»), либо с помощью ленты программы. Нажатием на сам значок «Специальная вставка» можно открыть целую диалоговую панель, где предоставляется больше вариантов копирования, и появляется возможность даже комбинировать данные с помощью операций. Например, можно переносимые данные не просто вставить в указанные ячейки, а прибавить к тем, что уже имеются на листе. Иногда это очень удобно.
Также бывает, что таблица имеет большое количество столбцов различной ширины, и после копирования значений требуется много довольно кропотливой работы по установке нужных ширин. В этом случае в диалоге «Специальная вставка» есть специальный пункт «Ширина столбцов». Вставка при этом осуществляется в два этапа. Сперва вставляем только «ширину столбцов», чтобы «подготовить место», а затем копируем значения. Таблица получается точно такой же, как исходная, но вместо формул в ней остаются значения. Иногда удобно скопировать только ширину столбцов, чтобы таблица выглядела, как исходная, а значения вписывать в ячейки вручную. Кроме того, можно выбрать пункт «копирование с сохранением ширины столбцов» в контекстном меню. В результате вставка будет выполнена в один этап.
Копирование значений и форматов
Копирование в виде рисунка
Изредка требуется скопировать часть таблицы так, чтобы потом можно было бы ее без изменений поворачивать и масштабировать, не затрагивая других мест остальной таблицы. В этом случае данные разумно копировать в виде обычной картинки.
Шаги по копированию в буфер обмена не отличаются от предыдущих вариантов, а вот для вставки используется пункт «Рисунок» в меню «Специальная вставка». Этот метод используется редко, поскольку данные ячеек, скопированных таким способом, невозможно изменить простым вводом значений.
Копирование в виде рисунка
Полное копирование всего листа
Иногда требуется скопировать весь лист, и вставить его либо в тот же документ, либо в другой. В этом случае надо вызвать контекстное меню на названии листа в нижней левой части программы и выбрать пункт «переместить или скопировать».
Открывается панель, в которой задается метод копирования. В частности, можно указать, в какую книгу необходимо вставить новый лист, переместить его или копировать, и указать место среди уже имеющихся листов, в которое будет осуществлен перенос. После нажатия на кнопку «OK» в указанной книге появится новый лист со всем содержимым копируемого листа.
Полное копирование листа
Заключение
Копирование – это одно из самых востребованных действий в Excel. Для простых таблиц без формул наиболее удобен первый способ, а для таблиц, содержащих много формул и ссылок, как правило, предпочтительнее пользоваться вторым способом – копирование только значений. Прочие методы применяются реже.
На одном листе расположен список повторяющихся городов с информацией о предприятиях общепита:
Исходная таблица задания №1
Необходимо данные по каждому городу перенести в одну строку на другом листе (таблица обрезана справа):
Часть результирующего списка задания №1
Решение копированием с листа на лист
Это решение значительно проще, чем с использованием массивов, но более медленное. При больших объемах информации обработка может длиться достаточно долго. Решение достигается путем присваивания значений ячеек из таблицы первого листа ячейкам второго листа.
Переменные:
- n1 – количество строк в исходной таблице;
- n2 – номер столбца текущей ячейки исходной таблицы, к которой обращается цикл;
- n3 – номер строки текущей ячейки на втором листе;
- n4 – номер столбца текущей ячейки на втором листе;
- i1 – счетчик цикла For… Next;
- gorod – переменная с наименованием города, предназначенная для контроля за сменой текущего города, который обрабатывается циклом.
Решение с использованием массивов
Циклы в массивах работают очень быстро, но решение с ними может быть сложнее, чем при простом присваивании значений одних ячеек другим. В этом примере для упрощения кода две процедуры записаны отдельно и в нужные моменты вызываются для исполнения.
Подпрограммы Kopirovanie и Vstavka используются в цикле For. Next процедуры Resheniye2 по два раза, поэтому их коды вынесены за пределы процедуры Resheniye2 и вызываются по мере необходимости.
Переменные:
- massiv1 – его элементам присваиваются значения ячеек исходной таблицы;
- massiv2 – одномерный массив, заполняемый данными из переменной txt1;
- massiv3 – двумерный массив, заполняемый данными из одномерного массива massiv2 и используемый для вставки очередной строки на второй лист;
- txt1 – сюда копируются через разделитель значения элементов массива massiv1, предназначенные для заполнения очередной строки на втором листе;
- n1 – количество строк в исходной таблице;
- n2 – количество столбцов в исходной таблице;
- n3 – номер текущей строки на втором листе;
- n4 – количество столбцов текущей строки на втором листе (соответствует количеству элементов массива massiv2);
- i1, i2, i3 – счетчики цикла For… Next;
- gorod – переменная с наименованием города, предназначенная для контроля за сменой текущего города, который обрабатывается циклом.
Переменные, использующиеся более чем в одной процедуре, объявлены как глобальные в разделе Declarations программного модуля.
Как известно, VBA обрабатывает информацию в массивах значительно быстрее, чем в ячейках рабочего листа Excel. Поэтому, при работе с большими объемами данных, удобнее использовать массивы, чем наблюдать во время выполнения кода за мерцанием изображения на экране или просто смотреть в неизменную картинку, если обновление экрана отключено (Application.ScreenUpdating = False). Здесь обмен значениями между массивом и диапазоном ячеек будет вполне уместен.
Копирование значений из диапазона ячеек в массив
Чтобы скопировать значения из диапазона ячеек в массив, необходимо объявить переменную универсального типа (As Variant) и присвоить ей значения диапазона ячеек с помощью оператора присваивания (=):
VBA Excel автоматически преобразует объявленную переменную в двумерный массив, соответствующий размерности диапазона ячеек, в нашем случае в массив — a(1 To 3, 1 To 3), и заполняет его значениями. Нумерация измерений массивов, созданных таким образом, начинается с единицы (1).
Можно, в этом случае, объявить сразу динамический массив, чтобы изначально указать, что эта переменная будет массивом. Так как свойством диапазона ячеек по умолчанию в VBA Excel является значение (Value), его можно в коде явно не указывать, но, при желании, можно и указать. Получится такая конструкция, аналогичная первой:
Копирование значений из массива в диапазон ячеек
Значения в диапазон ячеек добавляются из массива с помощью оператора присваивания (=):
Обратите внимание, что вставить значения в диапазон ячеек можно только из двумерного массива. Размерность такого массива может начинаться с нуля (0). Количество элементов в измерениях массива должно совпадать с количеством строк и столбцов в диапазоне ячеек. Если вам нужно вставить значения в одну строку или в один столбец, укажите размерность единственной строки или единственного столбца как (0) или (1 To 1), если вы хотите использовать нумерацию измерений своего массива с единицы. Например, для записи десяти значений из массива в одну строку можно объявить такой массив — massiv(9, 0), или в один столбец — massiv(0, 9).
Для вставки значений в диапазон ячеек из массива идеально подойдет массив, созданный для копирования в него значений из диапазона. В этом случае, данные с рабочего листа Excel переносятся в массив, обрабатываются и, после обработки, вставляются обратно в ту же или другую таблицу на том же или другом рабочем листе.
Обмен значениями между двумя диапазонами
Обмен значениями можно осуществить в VBA Excel не только между массивом и диапазоном, но и между двумя диапазонами одинаковой размерности:
Сохранение одномерного массива в текстовый файл с добавлением разделителя между его элементами.
Объявляем переменную универсального типа, создаем из нее массив, заполнив ее данными с помощью функции Array:
Теперь создаем текстовый файл с помощью метода CreateTextFile. Создаем, а не открываем, для того, чтобы, если такой файл с данными уже существует, от был перезаписан новым пустым файлом.
Если переменную, предназначенную для создания нового объекта TextStream объявить явно: fl As TextStream , то станут доступны подсказки для автозаполнения свойств и методов этого объекта при написании кода.
Сохраняем информацию из массива в текстовый файл с помощью цикла For… Next:
В качестве разделителя элементов массива используется символ «;». Если точка с запятой встречается в содержимом элементов массива, следует использовать другой разделитель.
Закрываем объект TextStream и открываем созданный текстовый файл для просмотра:
Полный код процедуры VBA Excel для сохранения одномерного массива в текстовый файл:
Сохранение двумерного массива
Сохранение двумерного массива в текстовый файл с использованием в качестве разделителей точки с запятой (для элементов в одной строке) и переноса строки.
Раз у нас VBA Excel, то и заполнять двумерный массив будем данными с рабочего листа:
В результате будет создан массив с размерностью (1 to 8, 1 to 4) . Первое измерение массива соответствует строкам диапазона, в второе – столбцам.
Создаем текстовый файл (или перезаписываем существующий) и открываем связанный с ним объект TextStream для записи данных из массива:
Записываем данные из двумерного массива в текстовый файл:
Закрываем объект TextStream и открываем созданный текстовый файл для просмотра:
Полный код процедуры VBA Excel для сохранения двумерного массива в текстовый файл:
Заполнение массива из текстового файла
Заполнение одномерного массива
Обратное заполнение с помощью кода VBA Excel одномерного массива данными из созданного текстового файла "C:\test\testfile1.txt" :
Заполнение двумерного массива
Обратное заполнение с помощью кода VBA Excel двумерного массива из созданного текстового файла "C:\test\testfile2.txt" .
Копируем информацию из текстового файла в переменную myString1:
Что мы теперь имеем? Все данные из файла "C:\test\testfile2.txt" скопированы в переменную myString1 с исходной структурой: со знаком переноса строки в качестве разделителя строк и точкой с запятой в качестве разделителя столбцов. Этой информацией мы и воспользуемся для заполнения массива myArray() .
Формула массива – это один из наиболее мощных инструментов Excel, но также и один из самых сложных. Массивом в Excel называют набор элементов, который может обрабатываться как одно целое, так и как набор, т.е. каждый элемент по отдельности. В этом уроке мы познакомимся с базовыми сведениями о массивах.
Одномерные и двухмерные массивы
В Excel различают одномерные и двухмерные массивы. Одномерный массив, его еще называют вектором, может храниться в одной строке (горизонтальный массив) …
…или в одном столбце (вертикальный массив).
Двухмерный массив хранится в диапазоне ячеек, его еще называют матрицей.
В отличие от языка программирования VBA, Excel не поддерживает трехмерные массивы.
Размерность массива
Ранее мы уже отмечали, что массивы в Excel могут быть одномерными и двухмерными. В свою очередь одномерные могут быть горизонтальными и вертикальными. Теперь рассмотрим, как же разделяются элементы в массивах.
Примеры приведены для русифицированной версии Excel.
-
Элементы в одномерных горизонтальных массивах разделяются точкой с запятой (;). Например, на рисунке ниже представлен одномерный горизонтальный массив, который состоит из 5 элементов:Если ввести его на рабочий лист Excel, получим следующий результат:
Чтобы вставить такой массив в Excel, необходимо сначала выделить строку, состоящую из 5 ячеек, затем ввести формулу массива и нажать комбинацию клавиш Ctrl+Shift+Enter.
-
Элементы в одномерном вертикальном массиве разделяются двоеточием (:). Например, на рисунке ниже представлен одномерный вертикальный массив, который состоит из 4 элементов.Если ввести его на рабочий лист, получим следующий результат:
При создании двухмерного массива необходимо учитывать, чтобы все его строки имели одинаковое количество элементов. Например, следующий массив не сможет существовать, поскольку его вторая строка содержит всего 4 элемента.
Если попробовать ввести такой массив на рабочий лист, то ничего не получится. Excel не позволит Вам это сделать.
Введя данный массив на рабочий лист Excel, получим следующее:
Одноячеечные и многоячеечные формулы массива
Excel поддерживает формулы массива двух видов: многоячеечные и одноячеечные. В первом случае формула занимает несколько ячеек, т.е. возвращает в качестве результата массив:
Во втором случае формула вводится в одну ячейку и возвращает только одно значение:
Итак, в данном уроке Вы познакомились с основными терминами и определениями, касаемо формул массива в Excel. Если желаете получить еще больше информации о массивах, читайте следующие статьи:
Подпишитесь к нам в дзен-канал, для получения свежих новостей it мира:
Читайте также: