Ref-sgain.ru

Программы и сервисы
3 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как скопировать и вставить строки или столбцы в обратном порядке в Excel?

Преобразовать «столбцы в строки» в Excel через VBA

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

«Столбцы в строки» с помощью VBA

Для тех, кто не сталкивался с такой «необходимостью», я приведу небольшой пример с двумя таблицами:

Столбцы в строки - пример

Левая таблица — исходная. В ней все данные хранятся в столбцах. Из такой таблицы крайне неудобно будет строить сводную: Excel каждый столбец будет рассматривать как отдельное поле, которое по отдельности надо будет перетаскивать при разработки структуры сводной таблицы. Это не будет большой проблемой, если таких столбцов несколько. А если 12, как в примере? Или 50, или еще больше? Вот в таком случае, максимально удобно будет использовать таблицу, у которой столбцы преобразованы в строки.

Правая таблица — искомая. Именно такой вариант таблицы максимально удобен для построения сводной. И целью моей статьи является показать, как можно преобразовать левую табличку в правую посредством языка VBA.

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

«Столбцы в строки»: разработка формы

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

Выделять диапазоны (исходный и конечный) мы будем с помощью элемента управления RefEdit (если данный элемент управления отсутствует на панели инструментов, то его необходимо добавить: щелкните по рабочей форме, затем нажмите «Tools» — «Additional Controls» и поставьте галочку рядом с «RefEdit.Ctrl»). Вводить необходимое количество обрабатываемых столбцов мы будем через элемент TextBox (его переименуем в Clmn_Count для удобства). Также добавляем CommandButton, то есть кнопку, для запуска самого основного макроса. Получается что-то вроде этого:

Столбцы в строки: рабочая форма

По нажатию на кнопку «Выполнить преобразование» будет выполняться следующий код:

Переменным s1 и s2 присваиваем значения полей RefEdit1 и RefEdit2 соответственно. Затем эти переменные проверяются: если они пустые, то уведомляем пользователя и выходим из процедуры, а если с ними все в порядке — преобразовываем их в диапазоны r1 и r2 соответственно. Проверяем второй диапазон на количество столбцов и строк: если этот диапазон не является ячейкой (т.е. строк и столбцов больше 1) — тоже уведомляем пользователя и завершаем процедуру.

Читайте так же:
Как сделать рабочий лист очень скрытым и видимым в Excel?

Если все проверки пройдены — запускаем поочередно три процедуры:

  1. Prepare — активация кода оптимизации
  2. Collapse Clmn_Count.Value, r1, r2 — запуск основной обработки, преобразующей столбцы в строки
  3. Ended — деактивация кода оптимизации

Рассмотрим каждый из них.

Prepare

Вот код оптимизации, взятый с Хабра. Отключает некоторые функции Excel и ускоряет выполнение макросов:

Collapse Clmn_Count.Value, r1, r2

Главный макрос, который просто выполняет нужную нам функцию: преобразует столбцы в строки. Он имеет три аргумента: количество столбцов для преобразования (с помощью текстового поля, которое мы называем Clmn_Count) и два диапазона (исходная ячейка и конечная ячейка). Сам макрос довольно сложен для понимания, но я постараюсь кратко объяснить, как он работает, на небольшом примере:

Столбцы в строки: таблица для примера

  1. Сначала копируем заголовки, которые у нас статичны (в нашем примере это заголовок «Клиент» и «SKU»)
  2. Затем берем новую строку исходной таблицы и выполняем определенный алгоритм 3 раза (то есть столько раз, сколько у нас столбцов):
    • Копируем строку с наименованиями «Клиента» и «SKU»
    • Затем делаем сдвиг на Z столбцов вправо (изначально Z = 1 и наращивается, в нашем случае, до 3)
    • Копируем заголовок данного столбца (сначала это «Январь»), а затем соответствующее значение
    • Наращиваем Z (для сдвига на следующий месяц), и повторяем все сначала
  3. Затем спускаемся на новую строку исходной таблицы и повторяем пункт 2 снова, до тех пор, пока не переберем все строки исходной таблицы:

На гифке показан наглядный пример выполнения данного алгоритма. После того, как выполнение макроса завершилось и все столбцы в строки преобразованы — переходим к макросу Ended.

Ended

Активация функций Excel, которые мы отключили с помощью макроса Подготовка

Заключение

В общем-то, на этом работа завершена. Принцип работы макроса очень прост: вызываем форму, выделяем исходную таблицу с заголовками(!), вводим количество столбцов, которое у нас должно быть преобразовано (как пример: если есть столбцы январь-декабрь, то вводим 12), затем выбираем ячейку, в которую мы хотим вставить исходную таблицу и нажимаем «Выполнить преобразование». И все, столбцы в строки преобразованы.

По желанию, можно связать вызов формы с какой-либо кнопкой в документе. Или шагнуть дальше — сделать свою надстройку с возможностью вызова этой формы в совершенно любом файле Excel.

Как копировать строки в таблице ворд

В режиме разметки наводите курсор на таблицу, пока не увидите, что она перемещается.

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

Перетащите таблицу на новое место.

Читайте так же:
Как скруглить углы ячеек в Excel?

Копирование таблицы с последующей вставкой в новом месте

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

В режиме проектирования указывайте на доску до тех пор, пока она не сдвинется с места.

Маркер подвижного стола можно выбрать, щелкнув по нему.

Выполните одно из следующих действий.

Нажмите CTRL+C, чтобы копировать таблицу.

Нажмите CTRL+X для вырезания таблицы.

Установите курсор в том месте, где нужно ввести новую таблицу.

Вы можете скопировать таблицу и вставить ее в новое место, нажав клавиши CTRL+V.

Как скопировать строку в таблице в Ворде?

Как перенести текст в таблице в Ворде?

Выделите текст, который нужно преобразовать, и нажмите Вставка > Таблица > Преобразовать в таблицу. В окне Текст в таблицу задайте параметры. В разделе Размер таблицы убедитесь, что указанные вами числа соответствуют количеству столбцов и строк, которые вы хотите.

Как выделить строку в таблице Ворд?

Поместите указатель мыши слева от нужного ряда, и он должен принять форму белой стрелки, направленной вправо вверх, как показано на рисунке ниже. Чтобы выделить несколько рядов, нажмите левую кнопку мыши рядом с первым из выделенных рядов и, удерживая левую кнопку мыши, перетащите указатель вниз.

Как увеличить строки в таблице Ворд?

Изменение размера строк, столбцов и ячеек

  1. Выделите таблицу. На ленте появляются всплывающие вкладки: Построитель таблиц и Макет. На вкладке Макет можно указать желаемую высоту и ширину. Чтобы изменить размер отдельной строки или столбца, щелкните в ячейке и отрегулируйте высоту или ширину.

Как двигать таблицу в ворде?

  1. В режиме компоновки наведите курсор на таблицу, пока не появится курсор перемещения таблицы.
  2. Удерживайте курсор на курсоре перемещаемого стола, пока он не примет вид крестообразной стрелки, затем щелкните его.
  3. Перетащите таблицу в новое место.

Как с Excel перенести текст в Word?

Копирование данных в Excel

  1. Выберите данные в Excel и нажмите CTRL+C.
  2. Откройте другое приложение Office, щелкните место, куда нужно вставить данные, и нажмите CTRL+V.
  3. Нажмите кнопку Параметры вставки рядом с данными и выберите нужный вариант вставки. Сохраните исходное форматирование.

Как запретить перенос строк в таблице Ворд?

  1. Щелкните в таблице.
  2. В меню Таблица выберите команду Свойства таблицы, затем перейдите на вкладку Строки. Снимите флажок Разрешить перенос строк на следующую страницу.

Как выделить ячейку несколько ячеек строку в ворде?

Чтобы выделить несколько ячеек в таблице, сначала выделите одну ячейку, а затем, не отпуская левой кнопки мыши, выделите оставшиеся ячейки.

Читайте так же:
Как скопировать несколько выделений или диапазонов в Excel?

Как выделить столбец в таблице Word?

В разделе Работа с таблицами перейдите на вкладку Макет. В группе Таблица нажмите кнопку Выбрать, а затем Выбрать столбец. Щелкните ячейку, строку или столбец, а затем, удерживая нажатой левую кнопку мыши, перетащите мышь по всем ячейкам, строкам или столбцам, которые нужно выделить.

Как сделать разные границы в таблице?

Добавление границы с помощью коллекции «Стили границ»

  1. Щелкните на таблице, а затем на маркере перемещения таблицы, чтобы выделить его. Откроется вкладка Работа с таблицами | Конструктор.
  2. Нажмите Стили границ и выберите стиль границ.
  3. Нажмите Границы и выберите место, где нужно добавить границы.

Как сделать все ячейки одинакового размера?

Задайте определенную ширину столбца.

  1. Выберите столбец или столбцы, ширину которых вы хотите изменить.
  2. На вкладке Главная в группе Ячейки нажмите кнопку Формат.
  3. Выберите ширину столбца в разделе Размер ячейки.
  4. Введите значение в поле Ширина колонки.
  5. Нажмите OK .

Как добавить несколько строк в таблицу?

Совет: Чтобы вставить несколько строк (или столбцов) одновременно, выберем нужное количество строк или столбцов, прежде чем щелкать их. Например, чтобы вставить две строки над строкой, сначала выберите две строки в таблице и нажмите кнопку «Вставить сверху».

Как правильно переносить таблицу?

Выполняется это одновременным нажатием клавиш Ctrl+Enter, при курсоре, установленном в строку, которую намереваемся перенести. Вторая часть таблицы переместится на новую страницу, перейдя на которую нажатием кнопки Enter, можно вписать «Продолжение…», вставить дополнительную строку и пронумеровать ее ячейки.

Как правильно нарисовать таблицу в ворде?

Нарисуйте свою собственную таблицу

  1. Нажмите Вставка > Таблица > Нарисовать таблицу. Указатель меняется на карандаш.
  2. Чтобы создать границы таблицы, нарисуйте прямоугольник, а затем проведите внутри него линии столбцов и строк. Чтобы стереть строку, на вкладке Макет нажмите кнопку Ластик и щелкните строку, которую нужно стереть.

Как поместить большую таблицу на одной странице в ворде?

Сокращая до одной страницы

  1. Откройте вкладку Sidelayout. …
  2. Выберите вкладку Страница в диалоговом окне Настройка страницы.
  3. Установите флажок Не размещать больше, чем в масштабе.
  4. Чтобы напечатать документ с одной стороны, введите 1 в поля Ширина и Высота.

Перемещение и копирование строк, столбцов, ячеек и их содержимого

Рекомендуется настроить эти операции перед тем, как продолжить:

вкладка ГлавнаяГруппа Инструментов Абзац— кнопкаОтобразить все знаки —маркеры конца строки в ячейке станут видимыми.

Чтобы переместить целые строки и столбцы на новое место в таблице, перетащите их. Это тот же алгоритм копирования, но при этом необходимо удерживать нажатой клавишу Ctrl. При выделении строк таблицы необходимо включить в выделение маркеры конца строки (см. рис. В противном случае будет скопировано или перемещено только содержимое ячеек.

Читайте так же:
Как скопировать правила условного форматирования на другой лист / книгу?

Рис. 11 Маркеры строк конца таблицы

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

Удерживайте Ctrl при перетаскивании для копирования содержимого таблицы.

Внимание.

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

Как сделать столбцы строками в Excel

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

К счастью, это не так, и задача значительно упрощается. Ниже мы покажем вам, как преобразовать столбцы в строки в Excel с помощью нескольких щелчков мыши. Материал будет актуален для всех современных версий Excel, включая Excel 2007, 2010, 2013 и 2016.

Метод №1. Транспонирование копирование и вставка.

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

Чтобы воспользоваться этим методом, выделите область листа Excel, строки которой вы хотите преобразовать в столбцы, и скопируйте эту область с помощью комбинации клавиш Ctrl-C или перейдя в контекстное меню и выбрав «Копировать» (как показано на скриншоте ниже).

копирование области листа Excel

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

вставка данных с транспонированием

В результате в выбранном месте появится область, содержащая ваши данные. Это преобразует ваши строки в столбцы и наоборот (как на скриншоте ниже). Обратите внимание, что нельзя вставить данные в одно и то же место, но это ограничение можно обойти, создав новый лист Excel.

Читайте так же:
Как скопировать лист и переименовать на основе значения ячейки в Excel?

область с преобразованными данными

Также обратите внимание, что этот метод не работает, если область данных оформлена в виде таблицы в Excel (кнопка «Таблица» на вкладке «Вставка»).

таблица Excel

Чтобы транспонировать таблицу Excel, ее необходимо сначала преобразовать в диапазон. Это делается с помощью кнопки «Преобразовать в диапазон» на вкладке «Конструктор электронных таблиц».

кнопка Преобразовать в диапазон

Кроме того, таблицу Excel можно транспонировать с помощью функции ТРАНСП, которая будет описана позже.

Метод 1 Используйте формулу «TRANSP».

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

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

Сначала необходимо выбрать область таблицы, в которую будут помещены транспонированные данные. Площадь должна быть такой же, как у исходных данных, но количество столбцов и строк должно быть обратным. Например, если ваша исходная область имеет 30 столбцов и 1 строку, область для транспонированных данных должна иметь 30 строк и 1 столбец.

выделение области таблицы

После выбора области нужного размера можно приступать к вводу формулы, которая будет преобразовывать столбцы в строки и наоборот. Для этого нажмите клавишу Enter, введите знак «=», введите имя формулы «ТРАНСП» и раскройте круглые скобки.

ввод формулы Excel

Определите область с исходными данными, выбрав ее с помощью мыши или введя ее адрес вручную.

ввод адреса массива

После ввода адреса массива закройте скобки и нажмите Ctrl-Shift-Enter. Необходимо нажать Ctrl-Shift-Enter, а не просто Enter, потому что эта формула работает с массивом.

завершение ввода формулы

При нажатии Ctrl-Shift-Enter вы попадете в новую область таблицы, где столбцы были преобразованы в строки и наоборот.

новая область с преобразованными данными

Кроме того, все ячейки новой области были связаны вместе с помощью формулы ТРАНСП. Данные в исходной области изменятся и в новой преобразованной области, если вы измените данные в ней.

  • Как создать график в Excel
  • Как улучшить диапазон в Excel
  • Как открыть XLS
  • Как открыть XLSX
  • Как вставить формулу в Word

Создатель сайта comp-security.net, автор более 2 000 статей по ремонту компьютеров, работе с программным обеспечением и установке операционных систем.

Если у вас возник вопрос, задайте его в разделе комментариев или на странице «Задать вопрос».

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector