Как перенести каждые 5 или n строк из одного столбца в несколько столбцов?
Предположим, у вас есть длинные данные в столбце A, и теперь вы хотите транспонировать каждые 5 строк из столбца A в несколько столбцов, например, транспонировать A1: A5 в C6: G6, A6: от A10 до C7: G7 и так далее, как показано на следующем снимке экрана. Как вы могли бы справиться с этой задачей без многократного копирования и вставки в Excel?
Транспонировать каждые 5 или n строк из одного столбца в несколько столбцов с помощью формулы
Транспонировать каждые 5 или n строк из одного столбца в несколько столбцов с кодом VBA
Транспонировать каждые 5 или n строк из одного столбца в несколько столбцов с помощью Kutools for Excel
Транспонировать каждые 5 или n строк из одного столбца в несколько столбцов с помощью формулы
В Excel вы можете применить следующую формулу, чтобы транспонировать каждые n строк из одного столбца в несколько столбцы, сделайте следующее:
1 . Введите следующую формулу в пустую ячейку, в которую вы хотите поместить результат, C1, например, = INDEX ($ A: $ A, ROW (A1) * 5-5 + COLUMN (A1)) , см. снимок экрана:
Примечание . В приведенной выше формуле A: A — это ссылка на столбец, которую вы хотите транспонировать, а A1 — это первая ячейка в используемом столбце число 5 указывает количество столбцов, в которых будут размещаться ваши данные, вы можете изменить их по своему усмотрению. Первая ячейка списка должна располагаться в первой строке рабочего листа.
2 . Затем перетащите маркер заливки вправо к пяти ячейкам и продолжайте перетаскивать маркер заливки вниз до диапазона ячеек, пока не отобразится 0, см. Снимок экрана:
Чтобы преобразовать каждые 5 или n строк из одного столбца в несколько столбцов, Kutools Утилита Transform Range в Excel может помочь вам решить эту задачу как можно быстрее. Это также может помочь вам перенести диапазон данных в одну строку или столбец. Нажмите, чтобы загрузить Kutools for Excel!
Kutools for Excel : с более чем 300 удобными надстройками Excel, можно бесплатно попробовать без ограничений в течение 30 дней. Загрузите и бесплатная пробная версия сейчас!
Транспонировать каждые 5 или n строк из один столбец в несколько столбцов с кодом VBA
Если вы не можете правильно применить формулу, следующий код VBA также может вам помочь.
1 . Удерживая нажатыми клавиши ALT + F11 , откройте окно Microsoft Visual Basic для приложений .
2 . Нажмите Вставить > Module и вставьте следующий код в окно Module .
Код VBA: переносить каждые 5 или n строк из одного столбца в несколько столбцов:
3 . После вставки кода нажмите клавишу F5 , чтобы запустить его, и появится окно подсказки, напоминающее вам о выборе столбца, который вы хотите транспонировать, см. Снимок экрана:
4 . Затем нажмите OK и выберите одну ячейку, в которую вы хотите поместить результат, в другое всплывающее окно, см. Снимок экрана:
5 . И нажмите OK , данные в столбце были преобразованы в пять столбцов, которые вам нужны, см. Снимок экрана:
Примечание : в приведенном выше коде вы можете изменить число 5 на другой номер, который вам нужен.
Транспонируйте каждые 5 или n строк из одного столбца в несколько столбцов с помощью Kutools for Excel
Если у вас есть Kutools for Excel , с его утилитой Transform Range вы можете быстро перенести один столбец или строку в несколько столбцов и строк.
После установки Kutools for Excel , пожалуйста, сделайте следующее:
1 . Выберите данные в столбце, а затем нажмите Kutools > Range > Диапазон преобразования , см. Снимок экрана:
2 . В диалоговом окне Диапазон преобразования выберите параметр Один столбец для диапазона в разделе Тип преобразования , а затем установите флажок Фиксированное значение в поле Строк на запись , затем укажите количество столбцов, в которые вы хотите выполнить транспонирование, в поле Фиксированное значение . , см. снимок экрана:
3 . Затем нажмите кнопку Ok , в появившемся окне выберите ячейку для вывода результата, см. Снимок экрана:
4 . Затем нажмите кнопку OK , и данные столбца будут транспонированы каждые 5 строк, как показано на следующем снимке экрана:
Загрузите бесплатную пробную версию Kutools for Excel прямо сейчас!
Excel: как сравнить 2 таблицы и подставить данные из одной в другую автоматически
Вопрос от пользователя
Здравствуйте!
У меня есть одна задачка, и уже третий день ломаю голову — не знаю, как ее выполнить. Есть 2 таблицы (примерно 500-600 строк в каждой), нужно взять столбец с названием товара из одной таблицы и сравнить его с названием товара из другой, и, если товары совпадут — скопировать и подставить значение из таблицы 2 в таблицу 1. Запутанно объяснил, но думаю, по фотке задачу поймете ( прим. : фотка вырезана цензурой, все-таки личная информация) .
Заранее благодарю. Андрей, Москва.
Доброго дня всем!
То, что вы описали — относится к довольно популярным задачам, которые относительно просто и быстро решать с помощью Excel. Достаточно загнать в программу две ваши таблицы, и воспользоваться функцией ВПР . О ее работе ниже.
Пример работы с функцией ВПР
В качестве примера я взял две небольших таблички, представлены они на скриншоте ниже. В первой таблице (столбцы A, B — товар и цена) нет данных по столбцу B; во второй — заполнены оба столбца (товар и цена). Теперь нужно проверить первые столбцы в обоих таблицах и автоматически, при найденном совпадении, скопировать цену в первую табличку. Вроде, задачка простая.
Две таблицы в Excel — сравниваем первые столбцы
Как это сделать.
Ставим указатель мышки в ячейку B2 — то бишь в первую ячейки столбца, где у нас нет значения и пишем формулу:
=ВПР( A2 ; $E$1:$F$7 ; 2 ; ЛОЖЬ )
где:
A2 — значение из первого столбца первой таблицы (то, что мы будем искать в первом столбце второй таблицы);
$E$1:$F$7 — полностью выделенная вторая таблица (в которой хотим что-то найти и скопировать). Обратите внимание на значок «$» — он необходим, чтобы при копировании формулы не менялись ячейки выделенной второй таблицы;
2 — номер столбца, из которого буем копировать значение (обратите внимание, что у нас выделенная вторая таблица имеет всего 2 столбца. Если бы у нее было 3 столбца — то значение можно было бы копировать из 2-го или 3-го столбца);
ЛОЖЬ — ищем точное совпадение (иначе будет подставлено первое похожее, что явно нам не подходит).
Какая должна быть формула
Собственно, можете готовую формулу подогнать под свои нужды, слегка изменив ее. Результат работы формулы представлен на картинке ниже: цена была найдена во второй таблице и подставлена в авто-режиме. Все работает!
Значение было найдено и подставлено автоматически
Чтобы цена была проставлена и для других наименований товара — просто растяните (скопируйте) формулу на другие ячейки. Пример ниже.
Растягиваем формулу (копируем формулу в другие ячейки)
После чего, как видите, первые столбцы у таблиц будут сравнены: из строк, где значения ячеек совпали — будут скопированы и подставлены нужные данные. В общем-то, понятно, что таблицы могут быть гораздо больше!
Значения из одной таблицы подставлены в другую
Примечание : должен сказать, что функция ВПР достаточно требовательна к ресурсам компьютера. В некоторых случаях, при чрезмерно большом документе, чтобы сравнить таблицы может понадобиться довольно длительное время. В этих случаях, стоит рассмотреть либо другие формулы, либо совсем иные решения (каждый случай индивидуален).
Как правильно определить номер столбца в Excel
Для загрузки товаров в накладную или в каталог из Excel необходимо правильно определить номера столбцов с данными в документе.
В некоторых случаях может оказаться, что данные находятся не в том столбце, в котором кажется.
На примере выше может показаться, что стоимость товара указана в столбце 33, но это не так. Это связано с тем, что несколько ячеек в строке объединены в одну. Для того, чтобы правильно определить, в каком столбце находится информация, кликните на ячейку с данными (она будет выделена зеленым прямоугольником) и посмотрите адрес в информационной строке.
Excel может отображать номера столбцов в разных форматах, разберем ситуация для каждого из них:
Цифровая нумерация столбцов
Адрес ячейки указан как R19C30:
- R, значит “Row” (строка) – 19
- C, значит “Column” (Столбце) – 30
Т.е. в рассматриваемом примере столбец с ценой имеет номер 30. Именно его и необходимо указать.
Буквенная нумерация столбцов
В данном случае адрес ячейки имеет вид: AD19
- AD – номер столбца
- 19 – номер строки
Нас интересует значение номера столбца, именно его и необходимо заполнить. В программе при вводе номера в цифровом значении отображается и буквенный эквивалент:
Как переключить вид нумерации
В Excel можно переключить вид отображения адреса ячейки. Сделать это можно, открыв Файл-Параметры, вкладка “Формулы”: