Evolcom.ru

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

Как перенести каждые 5 или n строк из одного столбца в несколько столбцов?

Превращение строк в столбцы и обратно

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

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

Выберите и скопируйте исходную таблицу (правый щелчок — Копировать). Затем щелкните правой кнопкой мыши на пустой ячейке, в которую вы хотите поместить повернутую таблицу, и выберите в контекстном меню команду Вставить специальную. В появившемся диалоговом окне установите флажок Transpose и нажмите OK.

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

Преимущества: В транспонированной таблице сохраняется исходное форматирование клеток.

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

Например, если исходная таблица изначально имела три строки и пять столбцов, выделите диапазон из пяти строк и трех столбцов, а затем введите функцию ТРАНСПОЗИТ в первую ячейку:

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

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

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

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

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

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

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

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

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

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

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

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

Недостатки: форматирование не сохраняется, но может быть легко воссоздано с помощью функции Paste Special (вставляется только формат с установленным флажком Transpose).

Как перенести каждые 5 или n строк из одного столбца в несколько столбцов?

документ транспонировать каждые 5 строк 1

Предположим, у вас есть длинные данные в столбце A, и теперь вы хотите транспонировать каждую 5-ю строку столбца A в несколько столбцов, например, транспонировать A1: A5 в C6: G6, A6: A10 в C7: G7 и т.д. и т.п., как показано на скриншоте ниже. Как это можно сделать без многократного копирования и вставки в Excel?

Перенести каждые 5 или n строк из одного столбца в несколько столбцов с формулой

В Excel можно применить следующую формулу для транспонирования каждой n-ой строки столбца в несколько столбцов

1. В ячейке, куда вы хотите поместить результат, напишите C1, = INDEX ($ A: $ A; STRING (A1) * 5-5 + BOX (A1) , см. скриншот:

документ транспонировать каждые 5 строк 2

Примечание: В приведенной выше формуле A: — это ссылка на столбец, который вы хотите транспонировать, а A1 — первая ячейка используемого столбца, число 5 указывает на количество столбцов, в которые будут помещены ваши данные, вы можете изменить его по своему усмотрению. И первая ячейка в списке должна находиться в первой строке рабочего листа.

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

2. Затем перетащите маркер заполнения вправо на пять ячеек и продолжайте перетаскивать маркер заполнения вниз в диапазон ячеек, пока не отобразится 0, см. снимок экрана:

документ транспонировать каждые 5 строк 3

С помощью утилиты преобразования диапазона Kutools for Excel можно быстро преобразовать каждые 5 или n строк в несколько столбцов. Функция может использоваться для перемещения диапазона данных в одну строку или столбец. Скачайте Kutools for Excel!

документ транспонировать каждые 5 строк 10

Kutools for Excel: с более чем 300 практичными надстройками Excel, которые вы можете попробовать бесплатно в течение 30 дней без ограничений. Загрузите бесплатную пробную версию прямо сейчас.

Транспонировать каждые 5 или n строк из одного столбца в несколько столбцов с кодом VBA

Если вы не можете правильно применить формулу, можно использовать приведенный ниже код VBA.

1. Удерживая клавиши ALT и F11, вы можете открыть окно Visual Basic for Applications.

2. Добавьте следующий код в окно Modules, нажав Insert > Modules.

Код VBA: преобразовать каждую 5-ю или n-ю строку из одного столбца в несколько столбцов:

3. После вставки кода нажмите F5, чтобы запустить его, и появится диалоговое окно с напоминанием о необходимости выбрать столбец, который вы хотите транспонировать, см. скриншот :

документ транспонировать каждые 5 строк 4

4. Затем нажмите OK и выберите ячейку, в которую вы хотите поместить результат в другом всплывающем окне, см. скриншот :

документ транспонировать каждые 5 строк 5

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

документ транспонировать каждые 5 строк 6

Примечание: В приведенном выше коде вы можете заменить число 5 на другое число по вашему выбору.

Перенести каждые 5 или n строк из одного столбца в несколько столбцов с помощью Kutools for Excel

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

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

После установки Kutools for Excel выполните следующие действия.

Выберите данные в столбце и нажмите Coutulas > Range > Conversion Range, см. снимок экрана :

2. В диалоговом окне преобразования диапазона выберите опцию Один столбец для диапазона в разделе Тип преобразования, затем установите флажок Фиксированная стоимость в разделе Строки на запись, затем укажите количество столбцов, которые вы хотите перенести в поле Фиксированная стоимость, см. снимок экрана :

документ транспонировать каждые 5 строк 8

3, Затем нажмите кнопку Ok, в появившемся окне выберите ячейку для вывода результата, см. снимок экрана :

документ транспонировать каждые 5 строк 9

4. Если вы нажмете OK, данные столбца будут переставляться каждые 5 строк, как показано на этом снимке экрана:

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