Excel vba переместить столбец

Обновлено: 05.07.2024

Хотим, упрощенно говоря, повернуть таблицу на бок, т.е. то, что располагалось в строке - пустить по столбцу и наоборот:

Способ 1. Специальная вставка

Выделяем и копируем исходную таблицу (правой кнопкой мыши - Копировать). Затем щелкаем правой по пустой ячейке, куда хотим поместить повернутую таблицу и выбираем из контекстного меню команду Специальная вставка (Paste Special) . В открывшемся диалоговом окне ставим галочку Транспонировать (Transpose) и жмем ОК.

Минусы : не всегда корректно копируются ячейки с формулами, нет связи между таблицами (изменение данных в первой таблице не повлияет на вторую).

Плюсы : в транспонированной таблице сохраняется исходное форматирование ячеек.

Способ 2. Функция ТРАНСП

Выделяем нужное количество пустых ячеек (т.е. если, например, исходная таблица была из 3 строк и 5 столбцов, то выделить обязательно нужно диапазон из 5 строк и 3 столбцов) и вводим в первую ячейку функцию ТРАНСП (TRANSPOSE) из категории Ссылки и массивы (Lookup and Reference):

После ввода функции необходимо нажать не Enter, а Ctrl+Shift+Enter, чтобы ввести ее сразу во все выделенные ячейки как формулу массива . Если раньше не сталкивались с формулами массивов, то советую почитать тут - это весьма экзотический, но очень мощный инструмент в Excel.

Плюсы : между таблицами сохраняется связь, т.е. изменения в первой таблице тут же отражаются во второй.

Минусы : не сохраняется форматирование, пустые ячейки из первой таблицы отображаются в виде нулей во второй, нельзя редактировать отдельные ячейки во второй таблице, поскольку формулу массива можно менять только целиком.

Способ 3. Формируем адрес сами

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

Функция АДРЕС(номер_строки; номер_столбца) - выдает адрес ячейки по номеру строки и столбца на листе, т.е. АДРЕС(2;3) выдаст, например, ссылку на ячейку C2.
Функция ДВССЫЛ(ссылка_в_виде_текста) - преобразует текстовую строку, например, "F3" в настоящую ссылку на ячейку F3.
Функции СТРОКА(ячейка) и СТОЛБЕЦ(ячейка) - выдают номер строки и столбца для заданной ячейки, например =СТРОКА(F1) выдаст 1, а =СТОЛБЕЦ(А3) выдаст 3.

Теперь соединяем эти функции, чтобы получить нужную нам ссылку, т.е. вводим в любую свободную ячейку вот такую формулу:

=ДВССЫЛ(АДРЕС(СТОЛБЕЦ(A1);СТРОКА(A1)))

в английской версии Excel это будет =INDIRECT(ADDRESS(COLUMN(A1),ROW(A1)))

А затем копируем (протягиваем) формулу на соседние ячейки как обычно черным крестом. В итоге должно получиться примерно следующее:

Т.е. при копировании формулы вниз по столбцу, она выдает ссылку, которая уходит вправо по строке и наоборот. Чего и требовалось.

Плюсы : сохраняются связи между таблицами, можно легко вносить изменения во вторую таблицу.

Минусы : форматирование не сохраняется, но его можно легко воспроизвести Специальной вставкой (вставить только Формат с флажком Транспонировать

Метод 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 ) ) )

Транспонирование массива

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

[/vba] Автор - Pelena
Дата добавления - 17.09.2015 в 16:28 Pelena, да, это я уже видел, но мне казалось, что тут происходит промежуточное копирование, а потом удаление столбца. по крайней мере, операция будет крайне долгой для большого кол-ва строк.
Я думал, мб бывает метод типа offset или аналог того, как листы легко можно перемещать внутри книги. Pelena, да, это я уже видел, но мне казалось, что тут происходит промежуточное копирование, а потом удаление столбца. по крайней мере, операция будет крайне долгой для большого кол-ва строк.
Я думал, мб бывает метод типа offset или аналог того, как листы легко можно перемещать внутри книги. Roman777

Roman777, здравствуйте.

Я бы сделал так, пример во вложении:

Roman777, здравствуйте.

Я бы сделал так, пример во вложении:

[/vba] Автор - Rioran
Дата добавления - 17.09.2015 в 17:36 Rioran, Подскажите вот ещё что, в Вашем коде, ArrA и ArrB объявлены как Variant, такой метод работает только при таком типе для данных переменных? Rioran, Подскажите вот ещё что, в Вашем коде, ArrA и ArrB объявлены как Variant, такой метод работает только при таком типе для данных переменных? Roman777 мы просто записываем в память данные а потом из памяти кидаем в соответствующий столбик

Значит, Вы как-то раз задействовали 10-ю строку, это нормально. Даже если Вы с 10-й строки потом значение стёрли.

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

Значит, Вы как-то раз задействовали 10-ю строку, это нормально. Даже если Вы с 10-й строки потом значение стёрли.

Мой метод работает как минимум при этом типе переменной. В момент присвоения значения эти переменные превращаются в двумерные массивы. Конечно, переменные можно объявлять как массивы, но это не всегда удобно. Rioran мы просто записываем в память данные а потом из памяти кидаем в соответствующий столбик

Значит, Вы как-то раз задействовали 10-ю строку, это нормально. Даже если Вы с 10-й строки потом значение стёрли.

Добрый день! Помогите, плиз!
Как посредством VBA поменять местами значения в столбцах?
Ситуация следующая: есть таблица диапазоном от А1:An до D1:Dn (n - некое число строк, постоянно меняется), нужно перенести все значения из столбца D в A и соответственно значения остальных сдвинуть на 1 вправо

Забыла сказать об одном условии.. это отчет, и данные столбцы нужно переставлять под шапкой, а после столбца D заполнены также столбцы E, F и т.д. - они должны остаться на месте

Последний раз редактировалось Janny; 07.11.2008 в 14:03 .

Пока что пришла к такому решению.. можно ли его как-то упростить?

Последний раз редактировалось Janny; 07.11.2008 в 14:51 .

Возник еще один вопрос.. т.к. может меняться диапазон значений в столбцах, нужно знать верхнюю и нижнюю границу и выделение должно быть только результативного диапазон.. чтоб не лазить постоянно в макрос и не менять значения.. если диапазон A4:А23, а в след. раз А4:А26.. заданый диапазон А4:А10000 не устраивает

Последний раз редактировалось Janny; 07.11.2008 в 18:06 .

Дорогие подскажите как правильно использовать VBA? у меня не получается.

нужно знать верхнюю и нижнюю границу и выделение должно быть только результативного диапазон.. чтоб не лазить постоянно в макрос и не менять значения.

А зачем знать нижнюю границу?
Если у Вас под таблицей есть данные, которые нельзя передвигать, то необходимо знать какой-нибудь признак того, что это конец таблицы (например, слово "Итого" в столбце "A", или что другое).
Следующий пример отыскивает первую пустую строку в диапазоне столбцов "A:D", начиная со строки 4 (переменная i), затем перемещает данные в этих столбцах в диапазоне строк от 4 до i. Но, если в таблице нет пустой строки, то перемещаться будут данные со строки 4 до конца данных в этих столбцах, включая и то, что под таблицей. Данные "правее" таблицы остаются неизменными.

Читайте также: