Vba excel транспонирование диапазона
Обновлено: 08.07.2024
Представляет ячейку, строку, столбец или группу ячеек, содержащую один или несколько смежных блоков ячеек или объемный диапазон.
Хотите создавать решения, которые расширяют возможности Office на разнообразных платформах? Ознакомьтесь с новой моделью надстроек Office. Надстройки Office занимают меньше места по сравнению с надстройками и решениями VSTO, и вы можете создавать их, используя практически любую технологию веб-программирования, например HTML5, JavaScript, CSS3 и XML.
Примечания
Элемент по умолчанию объекта Range направляет вызовы без параметров в свойство Value, а вызовы с параметрами — в элемент Item. Таким образом, someRange = someOtherRange соответствует someRange.Value = someOtherRange.Value , someRange(1) соответствует someRange.Item(1) и someRange(1,1) соответствует someRange.Item(1,1) .
В разделе Пример описаны следующие свойства и методы для возврата объекта Range:
Пример
Чтобы вернуть объект Range, представляющий одну ячейку или диапазон ячеек, используйте синтаксис Range ( arg ), где arg обозначает диапазон. В следующем примере значение ячейки A1 помещается в ячейку A5.
В следующем примере диапазон A1:H8 заполняется случайными числами путем задания формулы для каждой ячейки в диапазоне. При использовании без квалификатора объекта (объекта слева от точки) свойство Range возвращает диапазон на активном листе. Если активное окно не является листом, метод завершается с ошибкой.
Используйте метод Activate объекта Worksheet, чтобы активировать лист перед использованием свойства Range без явного квалификатора объекта.
В следующем примере очищается содержимое диапазона Criteria.
Если используется текстовый аргумент для адреса диапазона, необходимо указать адрес в нотации стиля A1 (нельзя использовать нотацию в стиле R1C1).
Чтобы получить диапазон, содержащий все отдельные ячейки листа, используйте свойство Cells на листе. Вы можете обращаться к отдельным ячейкам, используя синтаксис Item(строка, столбец), где строка — индекс строки, а столбец — индекс столбца. Свойство Item можно пропустить, так как вызов направляется к нему с помощью элемента по умолчанию объекта Range. В следующем примере на первом листе активной книги ячейке A1 присваивается значение 24, а в ячейке B1 — значение 42.
В следующем примере задается формула для ячейки A2.
Хотя также можно использовать Range("A1") , чтобы вернуть значение ячейки A1, иногда свойство Cells может быть удобнее, так как позволяет использовать переменную для строки или столбца. В следующем примере создаются заголовки столбцов и строк на листе Sheet1. Обратите внимание, что после активации листа можно использовать свойство Cells без явного объявления листа (оно возвращает ячейку на активном листе).
Хотя для изменения ссылок в стиле A1 можно использовать строковые функции Visual Basic, проще (и лучше при программировании) использовать нотацию Cells(1, 1) .
Используйте синтаксис_выражение_.Cells, где выражение возвращает объект Range, чтобы получить диапазон с тем же адресом, состоящий из отдельных ячеек. В таком диапазоне отдельные ячейки доступны с помощью синтаксиса Item(строка, столбец) относительно левого верхнего угла первой области диапазона. Свойство Item можно пропустить, так как вызов направляется к нему с помощью элемента по умолчанию объекта Range. В следующем примере на первом листе активной книги в ячейках C5 и D5 указывается формула.
Чтобы вернуть объект Range, используйте синтаксис Range ( ячейка1, ячейка2 ), где ячейка1 и ячейка2 — это объекты Range, указывающие начальную и конечную ячейки. В следующем примере устанавливается тип линии границы для ячеек A1:J10.
Имейте в виду, что точка перед каждым появлением свойства Cells является обязательной, если результат предыдущего оператора With нужно применять к свойству Cells. В данном случае указано, что ячейки расположены на листе один (без точки свойство Cells будет возвращать ячейки активного листа).
Чтобы получить диапазон, содержащий все строки листа, используйте свойство Rows на листе. Вы можете обращаться к отдельным строкам с помощью синтаксиса Item(строка), где строка — это индекс строки. Свойство Item можно пропустить, так как вызов направляется к нему с помощью элемента по умолчанию объекта Range.
Недопустимо указывать второй параметр свойства Item для диапазонов, состоящих из строк. Сначала нужно преобразовать их в отдельные ячейки, используя свойство Cells.
В следующем примере удаляются строки 4 и 10 первого листа активной книги.
Чтобы получить диапазон, содержащий все столбцы листа, используйте свойство Columns на листе. Вы можете обращаться к отдельным столбцам с помощью синтаксиса Item(строка) [sic], где строка — это индекс столбца в виде числа или адреса столбца в формате А1. Свойство Item можно пропустить, так как вызов направляется к нему с помощью элемента по умолчанию объекта Range.
Недопустимо указывать второй параметр свойства Item для диапазонов, состоящих из столбцов. Сначала нужно преобразовать их в отдельные ячейки, используя свойство Cells.
В следующем примере удаляются столбцы B, C, E и J первого листа активной книги.
Используйте синтаксис_выражение_.Rows, где выражение возвращает объект Range, чтобы получить диапазон, состоящий из строк первой области диапазона. Вы можете обращаться к отдельным строкам с помощью синтаксиса Item(строка), где строка — это относительный индекс строки от верхнего края первой области диапазона. Свойство Item можно пропустить, так как вызов направляется к нему с помощью элемента по умолчанию объекта Range.
Недопустимо указывать второй параметр свойства Item для диапазонов, состоящих из строк. Сначала нужно преобразовать их в отдельные ячейки, используя свойство Cells.
В следующем примере удаляются диапазоны C8:D8 и C6:D6 первого листа активной книги.
Используйте синтаксис_выражение_.Columns, где выражение возвращает объект Range, чтобы получить диапазон, состоящий из столбцов первой области диапазона. Вы можете обращаться к отдельным столбцам с помощью синтаксиса Item(строка) [sic], где строка — это относительный индекс столбца от левого края первой области диапазона, указанный в виде числа или адреса столбца в формате A1. Свойство Item можно пропустить, так как вызов направляется к нему с помощью элемента по умолчанию объекта Range.
Недопустимо указывать второй параметр свойства Item для диапазонов, состоящих из столбцов. Сначала нужно преобразовать их в отдельные ячейки, используя свойство Cells.
В следующем примере удаляются диапазоны L2:L10, G2:G10, F2:F10 и D2:D10 первого листа активной книги.
Чтобы вернуть диапазон с указанным смещением относительно другого диапазона, используйте синтаксис Offset ( строка, столбец ), где строка и столбец — это смещения строк и столбцов. В следующем примере выделяются ячейки, расположенные на три строки вниз и на один столбец вправо от ячейки в левом верхнем углу текущего выделенного фрагмента. Нельзя выбрать ячейку, которая находится не на активном листе, поэтому сначала необходимо активировать лист.
Используйте синтаксис Union ( диапазон1, диапазон2, . ) для возврата диапазонов из нескольких областей, то есть диапазонов, состоящих из двух или более смежных блоков ячеек. В следующем примере создается объект, определенный как объединение диапазонов A1:B2 и C3:D4, а затем выбирается определенный диапазон.
При работе с выделенными фрагментами, содержащими несколько областей, удобно применять свойство Areas. Оно разделяет выделенный фрагмент с несколькими областями на отдельные объекты Range, а затем возвращает объекты в виде коллекции. Вы можете использовать свойство Count в возвращенной коллекции, чтобы убедиться, что выделение содержит более одной области, как показано в следующем примере.
В этом примере используется метод AdvancedFilter объекта Range для создания списка уникальных значений, а также количества появлений этих уникальных значений в диапазоне столбца A.
Методы
Свойства
См. также
Поддержка и обратная связь
Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.
Метод WorksheetFunction.Transpose работает следующим образом: первая строка исходного диапазона (массива) становится первым столбцом нового диапазона (массива), вторая строка становится вторым столбцом и т.д.
Размерность диапазона, которому присваивается транспонированный диапазон, должна соответствовать его размерности. Количество строк нового диапазона должно соответствовать числу столбцов исходного, а количество столбцов нового – числу строк исходного.
Если транспонированный диапазон присваивается массиву, размерность определяется автоматически.
Синтаксис и параметры
| Параметры | Описание |
|---|---|
| Arg1 | Обязательный параметр. Диапазон ячеек рабочего листа или двумерный массив, который необходимо транспонировать. |
Примеры транспонирования
В примерах ниже транспонируется диапазон «A1:C8» в «A11:H13», как на изображении:
Транспонирование диапазона
Простое транспонирование диапазона:
Range ( "A11:H13" ) = WorksheetFunction . Transpose ( Range ( "A1:C8" ) )Транспонирование диапазона, когда размеры исходного диапазона неизвестны, а известно, что он начинается с ячейки «A1» и новый диапазон следует разместить ниже исходного через две пустые строки:
'Определяем количество столбцов в исходном диапазоне 'В первой половине выражения вычисляем координаты нового диапазона Range ( Cells ( r + 3 , 1 ) , Cells ( r + 2 + c , r ) ) = WorksheetFunction . Transpose ( Range ( Cells ( 1 , 1 ) , Cells ( r , c ) ) )Транспонирование массива
В этом примере двумерный массив создается на основе диапазона ячеек на рабочем листе, и новый массив выгружается на рабочий лист исключительно для наглядности, чтобы результат транспонирования можно было увидеть сразу:
Чтобы скопировать значения из диапазона ячеек в массив, необходимо объявить переменную универсального типа (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 переносятся в массив, обрабатываются и, после обработки, вставляются обратно в ту же или другую таблицу на том же или другом рабочем листе.
Простейшие примеры обмена значениями
Эти примеры составлены так, чтобы вам не пришлось совершать лишних действий, просто скопируйте их в свой модуль любой книги Excel с поддержкой макросов и запустите по-очереди на выполнение.
Пример 1
Заполнение двумерного массива значениями и и их присвоение диапазону ячеек на рабочем листе Excel:
Будучи пользователем Microsoft Excel, вы, возможно, часто использовали функцию транспонирования, которая позволяет переключаться между строками и столбцами для массива. Преобразование строк в столбец и столбцов в строки - это то, что функция транспонирования делает для вас в Excel. Количество строк становится числом столбцов и наоборот. Значит, если у вас есть 2 строки и 3 столбца в вашем массиве, после транспонирования он изменится на массив с 3 строками и 2 столбцами. В этом руководстве мы рассмотрим VBA Transpose, которая позволит вам автоматизировать метод транспонирования, который вы будете использовать в Excel.
Синтаксис транспонирования в Excel VBA
VBA Transpose имеет следующий синтаксис:
Arg1 : это обязательный аргумент, который представляет собой не что иное, как диапазон ячеек, которые мы хотели транспонировать (т.е. массив).
Первая часть синтаксиса - это не что иное, как выражение, в котором можно использовать функцию Transpose. Например, WorksheetFunction.
Как использовать Excel VBA Transpose?
Мы узнаем, как использовать Transpose с несколькими примерами в Excel VBA.
Вы можете скачать этот шаблон VBA Transpose Excel здесь - Шаблон VBA Transpose Excel
Пример № 1 - VBA транспонирование одномерного массива
Предположим, вы работаете с данными со списками (которые являются одномерным массивом), такими как имена сотрудников («Лалит», «Снеха», «Этил», «Джон», «Кори»), и вам нужен этот список быть вставленным в ячейку Excel. Посмотрим, как мы можем это сделать.
Выполните следующие шаги, чтобы использовать Transpose в VBA.
Шаг 1: Вставьте новый модуль и определите новую подпроцедуру для создания макроса в VBA.
Код:
Шаг 2: Определите новую переменную, которая может содержать ваш одномерный массив.
Код:
Шаг 3: Определите список как массив, используя функцию Array. Это будет список, который вы хотите вставить в свой лист Excel.
Код:
Шаг 4: Теперь используйте метод Range.Value, чтобы определить диапазон, в который вы хотите вставить эти значения.
Код:
Шаг 5: Используйте Application.WorksheetFunction.Transpose для данного массива, чтобы иметь возможность транспонировать список, определенный в переменной Arr1.
Код:
Здесь мы определили код, который позволит системе транспонировать данные в массиве списков с именем Arr1, а затем сохранять их в ячейке A1: A5 на активном рабочем листе.
Шаг 6: Нажмите F5 или кнопку «Выполнить» в VBE, чтобы запустить этот код и увидеть результаты в активной электронной таблице Excel.
Вы можете видеть, что данный массив списка перемещается в один столбец и сохраняется в ячейках от A1 до A5.
Пример №2 - VBA транспонирование двумерного массива
Предположим, у вас есть двумерный массив сотрудников и их зарплаты, как показано ниже:
Это двумерные данные с шестью строками и двумя столбцами. После транспонирования это будет массив с двумя строками и шестью столбцами.
Выполните следующие шаги, чтобы использовать Transpose в VBA.
Шаг 1: Определите подпроцедуру для сохранения макроса.
Код:
Шаг 2: Определите диапазон, в который вы хотите перенести эти данные. Это можно сделать с помощью функции Sheets.Range.Value. Я хотел бы сохранить транспонированный массив в ячейку от D1 до I2.
Код:
Шаг 3: Используйте WorksheetFunction.Transpose, чтобы иметь возможность назначить массив A1: B6 для транспонирования функции.
Код:
Шаг 4: Нам нужно указать аргумент для функции транспонирования. Мы хотели перенести диапазон массива A1: B6. Таким образом, используйте Range («A1: B6») в качестве аргумента.
Код:
Шаг 5: Нажмите F5 или кнопку Run, чтобы запустить этот код и увидеть результат.
Здесь диапазон массива A1: B6 транспонируется и сохраняется в диапазоне массива D1: I2 с помощью функции VBA Transpose в Excel.
Пример № 3 - VBA транспонирование массива с помощью специального метода вставки
Мы также можем транспонировать массив и вставлять как специальные, как в Excel, используя Alt + E + S. Мы можем использовать различные операции в этом специальном методе вставки.
Давайте рассмотрим те же данные, которые мы использовали в предыдущем примере.
Выполните следующие шаги, чтобы использовать Transpose в VBA.
Шаг 1: Определите подпроцедуру для сохранения макроса.
Код:
Шаг 2. Определите две новые переменные, одна из которых может содержать массив источника данных (sourceRng), а другая - диапазон выходного массива (targetRng).
Код:
Обратите внимание, что тип этих переменных определен как (Excel.Range). Потому что мы хотели транспонировать данные, которые являются диапазоном массива.
Шаг 3: Установите диапазон источника как A1: B6 (данные, которые мы хотели взять для транспонирования), используя функцию Sheets.Range.
Код:
Шаг 4: Установите целевой / целевой диапазон как D1: I2 (диапазон ячеек, в котором будет сохранен вывод) с помощью функции Sheets.Range.
Код:
Шаг 5: Теперь используйте команду Копировать, чтобы скопировать диапазон исходных данных с вашего рабочего листа.
Код:
Шаг 6: Мы собираемся использовать функцию PasteSpecial для переменной targetRng, чтобы сохранить транспонированный вывод в целевом диапазоне (D1: I2) на вашем рабочем листе.
Код:
- Вставить : позволяет вставлять значения в другом формате (например, Вставить как значения, как формулы, Как формат и т. Д.). Это похоже на Excel (мы делаем Alt + E + S, чтобы использовать разные специальные опции вставки). В нашем примере мы установили его для вставки в качестве значений.
- Операция : Существуют различные операции, которые могут быть выполнены как сложение, вычитание, умножение, деление (так же, как мы можем сделать в Excel).
- SkipBlanks : если установлено значение True, эта команда позволяет пропускать пропуски, если таковые имеются, из ваших данных при выполнении различных специальных операций вставки. Мы установили его в False, что означает, что мы хотели не пропускать пропуски.
- Транспонирование : если установлено значение True, оно позволяет выполнять транспонирование массива данных.
Шаг 7: Запустите этот код, нажав F5 или кнопку Run, и посмотрите результат.
То, что нужно запомнить
Рекомендуемые статьи
Это был путеводитель по VBA Transpose. Здесь мы обсудили, как использовать Excel VBA Transpose вместе с практическими примерами и загружаемым шаблоном Excel. Вы также можете просмотреть наши другие предлагаемые статьи -
Читайте также: